The difference and implementation of MyISAM and InnoDB in MySQL

Mysql in MyISAM and InnoDB The difference and implementation of :

difference ：

1. InnoDB Support transactions ,MyISAM Unsupported transaction . This is a MySQL Set the default storage engine from MyISAM become InnoDB One of the important reasons .
2. InnoDB Support foreign keys , and MyISAM I won't support it . For one containing a foreign key InnoDB Table to MyISAM Will fail .
3. InnoDB The smallest lock granularity is row locks ,MyISAM The smallest lock granularity is table locks . An update statement locks the entire table , As a result, other queries and updates will be blocked , So concurrent access is limited .
4. Execute a lot of select,MyISAM Is the best choice ; Perform a lot of update and insert It is best to InnoDB. InnoDB Do not save the specific rows of the table , perform select count(*) from table You need a full scan . and MyISAM A variable is used to save the number of rows in the whole table , You only need to read the variable when executing the query statement , fast .
5. MyISAM There are three files stored on disk .frm( Storage table definition ) .myd（ Store table data ） .myi（ Storage table index ）; InnoDB What is stored on disk are tablespace data files and log files ,innodb The size of the table is limited only by the size of the operating system .
6. delete from table when , InnoDB Will be deleted line by line .
7. MyISAM Use nonclustered indexes , Index and data are separated , Cache index only ,InnoDB Using a clustered index , Index and data exist in a file .

Query implementation :

MyISAM:
     This index is created by B+ Trees make up , When executing the query operation, you will first check B+ Query the tree , If the corresponding leaf node is found , Then the value of the leaf node will be taken （ Address ）, Find the whole line of data through the address .

Innodb

When searching the main index
     The picture below is Innodb Schematic diagram of main index lookup , It can be seen that the leaf node contains complete data records , This indexing method is called clustered indexing . because InnoDB The data file itself is aggregated according to the primary key , Indexes InnoDB The table must have a primary key （MyISAM There can be no ）, If not specified , be MySQL The system will automatically select a column that can uniquely identify the data record as the primary key , If there is no such column , be MySQL Automatically for InnoDB Table generates an implicit field as the primary key , Type is long long The length of this field is 6 Bytes .

Secondary index search
     It's in English ASCII Code as a comparison criterion . The implementation of clustered index makes the search by primary key very efficient , But the secondary index search needs to retrieve the index twice ： First retrieve the secondary index to get the primary key , Then use the primary key to retrieve the record in the primary index .