Extract 、 Transferred from a Xiu

1、innodb Why use self increasing id A primary key

Use self increasing id A primary key ：
Every time you insert a new record , It will be added to the subsequent positions of the current inode in sequence , When a page is full , Will open a new page
Use non self increasing id A primary key ：
Not self increasing id( Student number 、 ID number ), The value of each inserted primary key is approximately random , So every time a new record is inserted into the middle of the existing index page . Frequent movement 、 Paging caused a lot of fragmentation , The index structure is not compact enough , I have to go through OPTIMIZE TABLE To rebuild the table and optimize the page population

2、MySQL structure , Service tier and storage engine tier

contain Service layer and Storage engine layer ,
Service layer ：
The connector ： Verify user identity , Give authority
The query cache ： If there is a cache, it directly returns , If it does not exist, follow-up operations will be performed
analyzer ： Yes SQL Do lexical and grammatical analysis
Optimizer ： Mainly for the implementation of SQL Optimize and select the best execution scheme
actuator ： The actuator will first check whether the user has execution permission , You can use the interface provided by this engine

Storage engine layer ：
Responsible for data storage and extraction , Provide read-write interface

3、drop、delete、truncate Common ground and difference of

delete： Delete all or data rows of the table , perform delete after , Users need to submit (commite) To perform the deletion , Or rollback (rollback) To undo the deletion ; Will trigger all of this table delete trigger
truncate： Delete all data in the table , Cannot be rolled back , It will not start the trigger of this table ,truncate Than delete faster , Take up less space
drop： Delete tables from the database , All the data lines 、 Indexes and permissions will be deleted , be-all DML Trigger will not be triggered , Cannot be rolled back

delete and truncate Delete only data