1.B Trees and B+ What's the difference between trees

B Trees

    B Trees ： Each node can store multiple elements , Element storage is sorted , The nodes on the right are larger than those on the left .

B+ Trees

    B+ Trees ： And B Trees are compared to ,B+ The most obvious difference between trees is B+ The leaf nodes of the tree have pointers . Nodes inside can also store more than one element .B+ The non leaf nodes of the tree are redundant on the leaf nodes .B+ Trees are equivalent to B Tree upgrade . therefore

1. There are multiple elements in a node .
2. Elements are stored in order , The left node is smaller than the right node .
3. There are pointers between leaf nodes .
4. The elements on non leaf nodes are redundant on leaf nodes . let me put it another way , stay B+ The elements stored on the tree will be on the leaf node , And the pointer is connected 、 Orderly .

2. The height is 3 Of B+ How much data can a tree store

     The height is 2 Of B+ Trees ： A page is a node of a tree , Leaf nodes store data , The root node mainly stores primary keys + The pointer , A page can store 16k, Suppose that each record of a table is about 1kb, A page can store 16kb/1kb. Two tier B+ Trees can store 1638*16=26208 Data .（16kb/10b=1638 int type 4 Bytes , A pointer 6 Bytes , The total of this pair adds up 10b,16kb Is the number of pages , This page stores the contents . So you can manage 1638 page .）
     The height is 3 Of B+ Trees ：B+ Only leaf nodes in the tree store element values , So the first two layers can be understood as storing directories . There is one more node at the top , This node can also manage 1638 A catalog , So it is 1638*1638*16 == 42928074,4000 More than 10000 pieces of data .

3.Innodb How to support range search and index

    select * from t1 where a > 6 It's to execute first a=6 The operation of , First find a=6 after , And then a>6 Then all the data will be returned .

4. Return to the table for query & Why should we follow the leftmost prefix principle to use indexes

     Create a bcd Joint index of create index idx_t1_bcd on t1(b,c,d), Then he will also generate a B+ Trees , So this b+ How is the tree generated ？ Our primary key index corresponds to B+ Trees are Sort by the element corresponding to the primary key lock , Then generate B+ Trees . that bcd The index corresponds to B+ Trees are also a truth , also Is to put these data according to bcd Three fields to sort That's all right. . But leaf nodes ？ If the leaf node stores all the data , Every time you add, delete, modify and check data, you have to face two B+ Tree to operate , Considerable trouble , All on the leaf nodes of the joint index , Do not store the values of other elements , Only exist bcd The value of the three fields and the index of the primary key . Because if we borrow bcd All to query , The only elements found are bcd, You also need a primary key , Conduct Back to table operation , According to the primary key value in the primary key B+ The tree looks for complete information .

     Leftmost prefix principle ：where Conditions , It has nothing to do with the index order you give （ The bottom layer will optimize your sorting ）, But it must contain the leftmost field . For example, this query will invalidate the index select * from t1 where c = 1 and t = 1, Not used a Field . In this case The equivalent condition is * 1 1, Want to go bcd The index of , Then follow 111 and 322 Compare , Look left or right .
     There is no way to compare , Because the leftmost field of the condition is not given . But if the condition is b=1 and d=1 Can walk , This is like comparison. 1 * 1, This is comparable , The data must be on the left , Because the one on the left a Than the one on the right a Small .

5. Analysis on the principle of index failure caused by range search

     If it is still bcd Joint index , The query condition this time is b>1, The index will fail , Go to full table scanning . Because I found b=1 after , Then go to find the following data , What we want is complete data , So you have to take the stored primary key index Return to the table for query , There is too much data behind It's very troublesome , It takes less time than full table scanning .

6. The underlying principle of overlay indexing

    explain select b from t1 where b > 1; This can be indexed , Because this only needs to be found b, With bcd For index B+ Trees contain b Value , So there is no need to query back to the table . This is called Overlay index ,sql The fields queried are right at the current sql Index currently used , There is this field above , You don't have to go back to the table .

7. Underlying principle of index scanning

    explain select b from t1 This sql Also can walk bcd Indexed . If it is a full table scan , Is to traverse from the leaf node of the primary key index , Put the b Take out the field .b The value of the field will be stored in the primary key index , There will be bcd Indexed B+ In the tree . If it's in bcd Leaf node to traverse , The traversal data is not as complete as the data of the primary key index , But they all include b Value of field , The speed of query must be that incomplete data is faster than complete , So it will be bcd Indexes

8.order by Why does it invalidate the index ？

    explain select * from t order by b,c,d; This sql yes You can't Indexed . although bcd Of B+ The tree is according to bcd sorted , We just need to get these data in order . however , Because we want to The data queried is *,bcd The stored data is incomplete , Need to return to the table to query , How much data is saved, you need to return the table several times .
     If it is a full table scan , Although this does not need to be returned , But there are additional sorting operations
     But why do you want to scan the whole table instead of bdc Index? ？ Because The sorting operation is actually carried out in memory , Memory operation ratio io Much faster .

9. mysql Is there anything to pay attention to in data type conversion ？

     stay varchar Type in the , If characters that are not numbers are converted into numbers, they will all be converted into 0, such as '123' = 123 , 'abc' = 0

10. The principle of index invalidation caused by field operation

     Any operation on a field will invalidate the index , such as select * from t1 where a+1 = 1, Change b+ Trees , It will this field , This field , Other queries cannot continue to run this index , Therefore, the fields of the index must not be changed .

11.Mysql What are the storage engines in

Innodb engine

    Innodb yes Mysql Default transactional engine , And the most important 、 The most widely used engine .

MyISAM engine

    Myisam stay Mysql5 It was the default engine . however Mysql Transactions and row locks are not supported , And it's not safe to recover from a crash . meanwhile Myisam Lock the whole watch , It is easy to cause performance problems due to table lock problems .

Memory engine

    Memory The efficiency ratio of the engine Myisam An order of magnitude faster , Data files are stored in memory . Its table structure will be preserved after restart , But the data will be lost .

Archive engine

    Archive Only support insert and select operation . Will cache all writes and make use of Zlib Compress the inserted data . So than MyIsam The disk of the table IO Will be less . But every time select Will scan the whole table . So it is very suitable for logging and data collection .

CSV engine

    CSV The engine can make ordinary CSV File as Mysql To deal with , But index is not supported . So it is more suitable to be the engine of data exchange .

12.MyIsam and Innodb What's the difference ？

1. Innodb Support transactions and MyIsam Unsupported transaction .
2. Innodb Support foreign keys and MyIsam I won't support it .
3. Innodb yes Cluster index ,MyIsam It's a non clustered index .
   The files of the cluster index are stored on the leaf node of the primary key index , therefore Innodb Must have primary key .
4. Innodb Do not save the total number of rows of the table , The efficiency of querying the total number of rows is not as good as Myisam.
5. Innodb The smallest granularity lock is row lock , and MyIsam It's a watch lock . An update statement locks the entire table , As a result, other queries and updates are blocked . This is also MYSQL Set the default storage engine from MyIsam Change it to Innodb The main reason is .

13. Data table design , How would you choose ？

     Priority of field ： plastic ＞date、time＞enum、char＞varchar＞blob、text

     You can choose integer instead of string
     Avoid using NULL

14.varchar How much data can be stored at most

     about Varchar() Columns of type can store up to 65535 Bytes .

15. Please talk about the basic characteristics of transactions

     Transactions are in multiple sql A complete operation on the database for a whole unit . Transactions should have four characteristics . Atomicity 、 Isolation, 、 Persistence and Uniformity .

16. What problems may be caused by transaction concurrency

    1. Dirty reading ,2. It can't be read repeatedly ,3. Fantasy reading . Not here to explain every concept

17. Brief description Mysql Various indexes of

1. primary key , A table can only have one primary key index .
2. unique index , Based on the UNIQUE The index on a field is called a unique index , A table can have multiple unique indexes .
3. General index , An index based on a common field .
4. Prefix index , For the first few characters of a character type field or for the first few characters of a binary type field bytes Index established , Instead of building on the whole field . The prefix index can be built on the type char、varchar、binary、varbinary On the list of .
5. Full-text index , Only in text type CHAR,VARCHAR,TEXT Create full text index on type field . When the field length is large , If you create a normal index , It's going on like The efficiency of fuzzy query is low , At this point, you can create a full-text index . MyISAM and InnoDB Full text index can be used in .

18. What is the Samsung index

     For queries , Samsung index It can be the best index , If the query uses Samsung index , A query only needs to be done once IO operation , Greatly reduce the time .

     If the index rows related to a query are adjacent or at least close enough, one star will be obtained
     If the data sorting of the index is consistent with the sorting of the search , Get two stars
     If the columns in the index contain what is required in the query, then all columns , Get three stars .

19. How to improve Insert Performance of

1. Merge multiple insert For a line . Mainly multiple insert After the merger , The amount of logs will be reduced a lot , Thereby reducing io Operation to improve efficiency .
2. Modify the parameters bulk_insert_buffer_size, Adjust the cache for maximum batch insertion .
3. Set up Innodb_flush_log_at_trx_commit=0
   If the parameter is 0, be log buffer The data in will be written to log file, It will also trigger file to disk synchronization .
   If the parameter is 1, At each transaction commit time log buffer All data in is written to log file, It also triggers file system to disk synchronization
   If the parameter is 2, Transaction commit triggers log buffer To log file Refresh of , But it doesn't trigger disk file system to disk synchronization . Besides , There will be a file system to disk synchronization every second .
4. Set transaction commit as Manual submission , because MySQL Transactions are automatically committed by default , So every piece of data inserted , It's all done once commit, So commit the transaction manually , It can reduce the consumption when creating transactions . Generally, it can be set to 1000 strip insert Submit once .

20. What is a global lock 、 Shared lock 、 Exclusive lock

Global lock

     Global locking is to lock the entire database . His typical use scenario is to make a full database logical backup . This command can make the library read-only , Any other command will block .

Shared lock

     Shared lock is also called read lock , Is a lock created by a read operation . Other users can read data concurrently , But no transaction can modify his data , Until all shared locks are released . If the transaction modifies the read lock , It's easy to cause deadlock .

Exclusive lock

     Exclusive lock , Also known as write lock . If a transaction has an exclusive lock on a row , that Only this transaction can read and write it . Other business 、 No process can do anything to it , Including reading operations .

21. To talk about Mysql Deadlock in

     A deadlock is when two or more processes or threads are executing , A phenomenon of waiting for each other because of competing for resources . If there is no external force to operate , We can't move forward . At this time, the system is in a deadlock state .

How to view deadlocks

     Use command show engine innodb status Check the latest deadlock .
     You can also use Innodb Lock Monitor Turn on Monitoring , Every time 15s Output a log . It is recommended to close after use , Will affect performance .

How to deal with deadlock

1. adopt Innodbblockwait_timeout To set the timeout , Wait until the timeout .
2. Initiate deadlock detection , After deadlock is found , Actively rollback to a transaction in the deadlock , Let other business continue .

22.Mysql How to realize the separation of reading and writing

     Concept ： Will be Mysql The data is copied into multiple copies , Write about others mysql On the server . The original Mysql The database is responsible for writing , It is called the main library ; Other Mysql The database is only responsible for reading , Called slave library , Because read more and write less .

     principle ： The core of the read-write separation principle depends on a log , It's called binlog. It's a binary file . If you are right about mysql Write any data , It will write to this file , But queries won't . In the main library, he will start a Asynchronous thread . This asynchronous thread will put binlog Copy to the slave Library , After receiving data from the library , It will write a book called realy log Log , It is called relay log . Finally, the library will start a SQL Threads , This thread is to execute the log insert and update That's all right. . So the data in the library will be synchronized . That's it Mysql Separation of reading and writing .

advantage ：

1. Don't worry about one Mysql Downtime ,
2. The project performance will be higher .

shortcoming ：

1. The mechanism of read-write separation is asynchronous , In very extreme cases ,mysql The main database of is down ,binlog I haven't had time to copy , Then it will lead to data loss . But the probability is very low , It's tolerable .

23.Mysql How to realize sub database and sub table

     Split the database and data table horizontally . The focus of vertical splitting is business , Horizontal splitting is to split a single data table into multiple databases or multiple data tables according to certain rules . His focus is on the characteristics of data .
     For example, we can divide the library into 16 A watch , Then to the user id Conduct hash(), take idd Try to break up , Yes id Take the mold . This is a typical horizontal split .

24. The fundamentals of indexing

     Index is to quickly find those records with specific values .

The principle of indexing ：

1. Sort the contents of the columns that are indexed .
2. Generate reverse sort for the sorted results .
3. Splice the data address chain on the inverted content
4. At query time , Get the contents of the reverse sort table first , Then take out the data link , So we can get the specific data .

25.Mysql The difference between clustered index and non clustered index

     Both clustered and non clustered indexes are B+ Trees .

• Cluster index ： Put the data store and index together 、 And organized in a certain order , Find the index and find the data . The physical storage order of data is consistent with the index order .
• Nonclustered index ： Leaf nodes do not store data 、 Stored data address . That is, find the position of the data row according to the index, and then get the disk to find the data .

advantage ：

1. Query can directly obtain data through clustering index , Compared with non clustered index, the second query is more efficient .
2. Clustered indexes are highly efficient for ranges , Because its data is arranged by size
3. Clustered index is very suitable for sorting , Non clustered indexes are not appropriate .

Inferiority ：

1. Maintaining indexes is expensive , Especially when a new row is inserted or the primary key is updated, resulting in paging .
2. Watch because of the use of UUID（ Random ID） A primary key , Make data storage sparse , As a result, clustered indexes may be slower than full table scans , So it is recommended to use int Of auto_incurment A primary key .
3. If the primary key is large , Then the secondary index will become larger , Because the leaf of the secondary index stores the primary key value . Too long primary key value will cause non leaf nodes to occupy more additional space .

26.Mysql Index structure of , Their advantages and disadvantages

27. The design principle of index

28.mysql What are the types of locks

–

29.mysql What do you think of your implementation plan