1. MySQL What are the common storage engines ? What is the difference ?
Common ones are MyISAM and InnoDB, difference ：MyISAM
Transaction not supported , Suitable for applications with frequent queries , Table lock support , No deadlock , Suitable for small data , Low concurrency ;InnoDB
Support transaction processing , Suitable for applications with frequent insertion and update , Support row lock , Suitable for big data , High concurrency .
2. Mysql What are the types of database tables ?
BDB：Berkeley DB(BDB) Table is a transaction table , It provides transaction control .
HEAP：HEAP The watch is MySQL Table with the fastest access data in . This is because they use a hash index stored in dynamic memory , But if MySQL Or server crash , This memory data will be lost .
ISAM：ISAM Watch is early MySQL Default table type for version , until MyIASM Developed . Do not use it again .
MERGE： One MERGE The watch is actually another MyISAM Set of tables , A combined table , Mainly for efficiency , Because it doesn't just speed things up , Search efficiency , Repair efficiency and save disk space .
MyISAM：MyIASM Based on IASM code , It should be IASM Derivatives of , Many useful extensions have been added . It's MySQL Default data table type for , Based on the traditional ISAM type ,ISAM yes Indexed
Method（ Indexed sequential access method ） Abbreviation of , generally speaking , It's a standard way to store records and files . Compare with other storage engines ,MyISAM Most tools with check and repair forms .ISAM Tables can be compressed , And they support full-text search , But they're not safe , And it doesn't support foreign keys . If the transaction is rolled back, it will cause incomplete rollback , So there is no atomicity . So if you ignore transactions and access concurrency , And a lot of SELECT To retrieve a statement ,MyISAM Will be the best choice .
InnoDB：InnoDB yes MySQL
4.0 A relatively new type of data table introduced later , This type is transaction safe . It is associated with BDB Types have the same properties , They also support foreign keys .InnoDB The speed of the table is faster than BDB Rich features , So if you need a transaction safe storage engine , It is recommended . If your data performs a lot of INSERT or UPDATE, For performance reasons , The same should be used InnoDB surface . For transaction supported InnoDB For a table of type , The main reason that affects the speed is AUTOCOMMIT The default setting is on , And the program does not call explicitly BEGIN
Start transaction , Cause each inserted entry to be submitted automatically , Seriously affected the speed . Can be executed at sql Pre invocation begin, Multiple sql Form a thing （ even if autocommit It can also be opened ）, Will greatly improve performance .
Gemeni：Gemeni surface , It's also said that MySQL 4.0 Later , But up to now , There is very little introduction to it , There are fewer applications , We will not introduce it for the time being .
3. What is? MVVC, What is the realization mechanism ?
Is a multi version concurrency control protocol , Only in InnoDB Under the engine .MVCC To achieve transaction isolation , Pass version number , Avoid the competition of the same data between different transactions , You can think of it as an optimistic lock based on multi version number . of course , This optimistic lock is only valid for transaction level read committed and repeatable reads .MVCC The biggest benefit ： Read without lock , No conflict between reading and writing . In the application of reading more and writing less , It is very important that there is no conflict between reading and writing , Greatly increased the concurrent performance of the system .
Realization mechanism ：InnoDB Add three hidden fields to each row of data , A unique line number , Version number of a record creation , Version number of a record rollback . In multi version concurrency control , In order to ensure the data operation in the multithreaded process , Mechanism to ensure transaction isolation , Reduce the pressure of lock competition , Ensure high concurrency . Every time a transaction is opened , The version number of a transaction will be generated , The data to be manipulated will generate a new data row （ temporary ）, But not visible to other transactions before commit , Update of data （ Including addition, deletion and modification ） Operation successful , This version number will be updated to the row of data , Transaction submitted successfully , Update the new version number to this data row , This ensures the data of each transaction operation , They don't affect each other , There's no problem with locks .
4. SQL What is a neutron query ?" Related subquery " and " Unrelated subquery " What's the difference ?
Subquery ： Queries nested in queries , Also known as internal query , And the statement containing subquery is called external query （ Also known as main query ）.
All subqueries can be divided into two types , That is, related sub query and non related sub query
1） The execution of related subqueries depends on the data of external queries , External query execution line , Sub query is executed once .
2） Uncorrelated subqueries are subqueries that are independent of external queries , Sub query executed once in all , Pass value to external query after execution .
5. MySQL in char and varchar The difference between ?
char Is a fixed length type ,varchar Is a variable length type .
char(number) In the data column of type , Each value takes up number Bytes , If a length is less than number,MySQL It will be complemented with a space character to the right （ In the retrieval operation, the space characters filled in will be removed ）; stay varchar(number) In the data column of type , Each value takes just enough bytes plus a byte to record its length （ That is, the total length is L+1 byte ）.
varchar Applicable scenarios of ： The maximum length of a string column is much larger than the average length ; Strings are rarely updated , Prone to storage fragmentation ; Save strings using multibyte character sets
Applicable scenarios of ： Storage has approximate length （md5 value , ID , cell-phone number ）, Short string （ because varchar Extra space required to record string length ）, More suitable for frequently updated strings , Page splitting will not occur when updating , Avoid storage fragmentation , Get better io performance