Many interviews will ask what storage engines MYSQL has and their strengths and weaknesses. MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), MyISAM and InnoDB are the two most common storage engines.

This section describes common storage engines of MySQL

1.InnoDB engine (default after MySQL5.5)

The default storage engine in MySQL 5.5 and later has the following advantages:

Disaster recovery is good

Support transactions

Use row-level locking

Foreign key association is supported

Support hot backup

For tables in InnoDB engine, the physical organization of data is Cluster Table, primary key index and data are together, and data is physically distributed according to the order of primary key

Realize buffer management, not only can buffer index can buffer data, and will automatically create hash index to speed up data acquisition

Support hot backup

2. MyISAM engine

Features are as follows:

Transactions not supported

Table level locks are used for poor concurrency

After host downtime, MyISAM table is easily damaged and disaster recovery is poor

It can work with locks to implement copy, backup, and migration in the operating system

Only indexes are cached, and data is cached using the operating system buffer. May cause too many system calls and be inefficient

Data is stored compact, resulting in smaller indexes and faster full table scan performance

3.MEMORY engine

Memory tables are provided, and transactions and foreign keys are not supported. Significantly speed up data access and can be used to cache frequently accessed, reconfigurable data, computed results, statistics, and intermediate results.

Disadvantages are as follows:

Use table-level locks. Although memory access is fast, table-level locks can become a bottleneck if frequent reads and writes are used

Only fixed-size rows are supported. Varchar fields are stored as fixed-length Char, wasting space

The TEXT and BLOB fields are not supported. When some queries need to use temporary tables (also using MEMORY storage engines), TEXT and BLOB fields in the table will be converted to disk-based MyISAM tables, severely degrading performance

Due to the high cost of memory resources, it is not recommended to set a large memory table. If the memory table is full, you can clear data or adjust parameters of the memory table to avoid errors

Data will be lost after the server restarts. Therefore, be careful during replication and maintenance

MySQL storage engine MyISAM vs. InnoDB

1. The general differences between the two storage engines are as follows:

1) InnoDB supports transactions, MyISAM does not, which is very important. Transaction is a kind of advanced processing mode, such as in some column add, delete and change as long as which error can also be rolled back to restore, but MyISAM can not.

2) MyISAM is suitable for query-oriented and insert-oriented applications, while InnoDB is suitable for frequently modified and security-related applications

3) InnoDB supports foreign keys, MyISAM does not

4) InnoDB is the default engine since MySQL5.5.5

5) InnoDB does not support FULLTEXT indexes

6) InnoDB does not save the number of rows in a table. For example, when select count(*) from table, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads the number of rows saved. Note that MyISAM also needs to scan the entire table when the count(*) statement contains the WHERE condition.

7) For self-growing fields, InnoDB must contain an index for that field only, but in MyISAM tables it is possible to create a joint index with other fields.

8) InnoDB deletes the entire table row by row, which is very slow. MyISAM will rebuild the table.

Update table set a=1 where user like ‘%lee%’

Some people say that MYISAM can only be used for small applications, but this is just a bias.

If there is a large amount of data, it is necessary to upgrade the architecture to solve the problem, such as separate tables and libraries, and separate read and write, rather than relying solely on the storage engine.

Now InnoDB is generally used, mainly due to MyISAM full table lock, read and write serial problems, concurrent efficiency lock table, low efficiency, MyISAM for read and write intensive applications generally will not be used.

In a word:

1. The MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does.

2.MyISAM tables emphasize performance and execute faster than InnoDB tables, but do not provide transaction support, while InnoDB provides advanced database features such as transaction support and external keys.

The following is a list of advanced architecture materials required to take the free course.