The difference between MyISAM and InnoDB

  1. InnoDB supports transaction, MyISAM does not support, for InnoDB each SQL language encapsulated as a transaction, automatic commit, this will affect the speed, so it is best to put multiple SQL languages between begin and commit, constitute a transaction;

  2. InnoDB supports foreign keys, while MyISAM does not. Converting an InnoDB table with foreign keys to MYISAM will fail;

  3. InnoDB is a clustered index, uses B+Tree as the index structure, data files are tied together with (primary key) index (table data files themselves are organized by B+Tree index structure), must have a primary key, through the primary key index efficiency is very high. But secondary indexes require two queries, first to the primary key and then to the data through the primary key. Therefore, the primary key should not be too large, because if the primary key is too large, the other indexes will be too large.

MyISAM is a non-clustered index that uses B+Tree as its index structure. The index is separated from the data file, and the index stores the pointer to the data file. Primary and secondary indexes are separate.

In other words: InnoDB B+ tree primary key index leaf nodes are data files, secondary index leaf nodes are primary key values; The leaf nodes of MyISAM’s B+ tree primary key index and secondary index are address Pointers to data files.

 

4. InnoDB does not store the exact number of rows in a table. Select count(*) from table requires full table scan. MyISAM uses a variable to store the number of rows in the entire table, which is very fast (note that no WHERE conditions can be added).

So why doesn’t InnoDB have this variable?

Because of the transaction nature of InnoDB, the number of rows in the same schedule is different for different transactions, so the count count counts the number of rows that can be counted for the current transaction, rather than storing the total number of rows for quick lookup. InnoDB tries to traverse as small an index as possible unless the optimizer prompts you to use another index. If the secondary index does not exist, InnoDB will try to traverse other clustered indexes. If the index is not fully in the Buffer Pool InnoDB maintains, the count operation can be time-consuming. You can create a table of the total number of rows and have your program update the data during INSERT/DELETE. As with the problem mentioned above, this scenario is not very useful if there are multiple transactions. If the approximate row value is sufficient, try SHOW TABLE STATUS

  1. Innodb does not support full-text indexing, while MyISAM supports full-text indexing. MyISAM is faster in terms of query efficiency when it comes to full-text indexing. PS: After 5.7 InnoDB support full text indexing

  2. MyISAM tables can be compressed for query operations

  3. InnoDB supports table and row (default) level locking, while MyISAM supports table level locking

InnoDB row locks are implemented on indexes, not on physical row records. The subtext is that if the access does not hit the index and the row lock cannot be used, it will degenerate to a table lock.

Such as:

t_user(uid, uname, age, sex) innodb; Update t_user set age=10 where uid=1; Hit index, row lock. update t_user set age=10 where uid ! = 1; Index not hit, table lock. update t_user set age=10 where name='chackca'; No index, table lock.Copy the code

InnoDB tables must have unique indexes (such as primary keys) (if not specified, users will find/produce a hidden column Row_id as the default primary key), while Myisam can not

9. Innodb stores FRM and IBD files, while Myisam is FRM, MYD and MYI

Innodb: FRM is a table definition file, IBD is a data file

Myisam: FRM isa table definition file, myd isa data file, and myi is an index file

How to choose: 1. Whether to support transactions, if yes, select InnoDB, if not, consider MyISAM;

2. If most of the tables are just read queries, consider MyISAM. If you have both read and write queries, use InnoDB.

3. After system crash, it is more difficult for MyISAM to recover. Is it acceptable?

Innodb has become the default Mysql engine (previously MyISAM), showing its advantages are obvious, if you don’t know what to use, use Innodb, at least not bad.

Why InnoDB recommends using auto-increment ID as primary key?

A: The auto-increment ID ensures that the B+ index expands from the right on each insert, avoiding B+ trees and frequent merges and splits (compared to using UUID). If you use string primary keys and random primary keys, data will be randomly inserted, which is inefficient.

Four features of the InnoDB engine

Insert Buffer, Double Write, Adaptive hash index (AHI), Read Ahead

Original link: blog.csdn.net/qq_35642036…

Isolation level

MySQL transaction

MySQL transactions refer to InnoDB engine, MyISAM engine does not support transactions. Transactions have Atomicity, Consistency, Isolation and Durability (ACID), they cannot be missing either. Today is about isolation. Dirty read Dirty read refers to read data that has not been committed by another transaction. Uncommitted means that the data may be rolled back, that is, may not end up in the database, that is, the data does not exist. Dirty reads are those that read data that must eventually exist. Repeatable read Repeatable read means that the data read at the beginning of a transaction is the same as the data read at any time until the end of the transaction. Usually for data UPDATE operations. Non-repeatable read compares with repeatable read. Non-repeatable read means that the same batch of data may be read at different times in the same transaction and may be affected by other transactions. For example, other transactions change the batch of data and commit it. Usually for data UPDATE operations. Phantom reads Phantom reads are for INSERT operations. Assume that the transaction for some lines for A change, but has not been submitted and the transaction B insert with the transaction before A change of record of the same line, and the transaction is A submit before you submit, and then, in A transaction in A query, will find that just change for some of the data did not play A role, but in fact is the transaction B just inserted in, let the user feel very magical, The feeling appears the illusion, this is called the illusion read. Transaction Isolation Level Four isolation levels are defined for each of these situations, all of which are supported by MySQL. The four isolation levels are: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE Transaction isolation is designed to solve the problems mentioned above, such as dirty reads, unrepeatable reads, and phantom reads. The following shows how the four isolation levels solve these three problems.