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
Sequential Access
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

©2019-2020 Toolsou All rights reserved,
( Essence )2020 year 8 month 9 day C# Basic knowledge reflex vs2017, establish C++Win32 Windows Forms Application Python realization switch method Mybatis Mapping file Mapper.xml in # and $ The difference between ( Essence )2020 year 6 month 26 day C# Class library model PageInput The project followed for a year , The customer finally said no Front end to background 5 Summary of different ways vue vue-element-admin Summary of the project Hackbar Using the tutorial mysql Modify primary key