Knowledge reserve

In-memory database : Fast , The high cost , Indexes ：Map、 Balance tree 、T Trees , application Redis、Memchached
Disk database : Faster , The cost is moderate （ virtual machine , The physical machine ）, Index hash ,B+ , application ：MySQL、Oracle
Distributed database : Slower , Large capacity , Storage ： Vertical table 、HDFS

SQL Execution phase

1. The client sends a query to the server , arrive The connector .
2. The server checks the cache first , If the cache is hit , Return results immediately .
3. arrive analyzer On the server side SQL Parsing , Generate parse tree .
4. Again by Optimizer Generate the corresponding execution plan .
5. According to the execution plan , Call the specific storage engine （InnoDB） Returns the result to the client .

InnoDB Why should the engine be used B+ data
1、 Every node （ Data pages ） by 16K, Leaf nodes store data .
2、 Multiforked tree , Reduce floor height , Reduce IO, Generally, it only needs 3~4 Time IO that will do .
3、 There are order pointers between leaf nodes .
4、B+ The main index stores all data ,B The secondary index of the tree only stores the primary key ID.

What are the benefits of using indexes ?
1、 Reduce the amount of data scanned by the server , Improve query efficiency
2、 Servers avoid sorting and temporary tables

Analyze the causes and solutions

Explain usage

• table : Which table to query
• rows ： Estimate the number of lines to scan
• ref ： Which field and key Use it together
• key ： Index used
• possible_keys ： Available indexes , More than one
• key_len ： The number of bytes used in the index , The shorter the length, the better
• type ： ALL( Scan the whole table )、index( Scan full index )、range( Range index )、ref( Indexes , Match single value )、const( Primary key or unique key , Match only one line )
• filtered ： Percentage of data filtered by this query criteria
• extra： Additional information eg: Using filesort( The external index sort is not read according to the index order in the table )、Using index( Use overlay index )、Using index condition ( Prerequisite filtering , Check the data again )、Using where（ Use where Statement to process the result ） 、Using Temporary（ A temporary table , Common in order by and group by）

Slow query management ideas

Index level

1、 There is no index
2、 Index failure

select * from ShopTable where shopid > 10000000 VS where shopid > 50000000

Although give to shopid Added general index , But enforcement where shopid > 10000000 Indexes are not used , Back to the table ( That is, find out which line it is from the index , Then go back to the table, take out all the fields and return ) This leads to a full table scan when the amount of data that needs to be returned to the table is greater than 20%,MySQL I think the full table scan will be faster , So don't go to the index .
Suggest ：

• Reduce use select * , The index overrides the fields that need to be returned , That is, the index column is consistent with the query column
• Reduce the amount of data , Consider paging when the amount of data is too large

3 、 The index is not comprehensive
where shopid > 234512 and update_days > -1 and buy_time > 300
Only for shopid The index of , Inefficient indexing , After the query, you need to go back to the table and filter , Resulting in a serious decline in efficiency
Suggest ：
increase update_days and buy_time Index of field

4、Order By /Group By /Distinct Avoid temporary tables

5、 It is recommended to use the auto increment column as the primary key
create table test(idint(11) NOT NULL PRIMARY KEY (id))

6、 The primary key is set to int unsigned or bigInt unsigned

7、 There are too many indexes in the table

8、 Unique index: check the uniqueness of data , If the data is not unique, it is possible to automatically delete duplicate columns when adjusting the online table structure , This may lead to data loss

9、

SQL level

Avoid incorrect or inefficient writing 、 Avoid complex SQL

1、limit 6000000 , 10
limit Too big ,limit A,B Can scan X+Y Data , When X When it is too large , A large number of scanning lines leads to slow queries
Suggest ：
Use as much as possible id > xx LIMIT 10, It is recommended to use ID

2、
General index KEY shopid
General index KEY poiid
Composite index KEY combined(shopid,poiid)
Only the index is triggered when using merge i2 && KEY combined
Suggest ：
Delete Key shopid Indexes , Reduce index space
3、 A complex SQL Or more simple SQL

4、 It is not recommended to use the preceding wildcard to find
select c1,c2,c3 from tbl where name like '%foo';

5、 There are no wildcards LIKE Inquire about
select c1,c2,c3 from tbl where name like 'foo';

6、 Parameter comparison contains implicit conversions , Index not available
SELECT * FROM sakila.film WHERE length >= '60';

7、IN Use with caution , Too many elements will cause a full table scan , It is recommended to use between X and Y Instead of

8、 To avoid the WHERE Use functions or other operators in conditions

Table library layer

1、 Check the status of the table show table status like ‘table_shop’
Rows : 180000000
Data_length : 30G
The amount of data in a single table is too large , It is suggested that the single table should not exceed 1 Ten million lines

2、 Whether it is necessary to go to the main warehouse
Master-Slave framework , Main warehouse single node , It is easy to become a resource bottleneck

3、 normal form ： Make sure that each column is atomic （ It can't be broken down ）、 Uniqueness
Anti paradigm ： Through redundant fields , Improve query efficiency

application layer

Change the storage engine 、 cache
Business logic optimization

The specification

1. Use InnoDB Storage engine ： Support transactions 、 Row-level locks 、 Better concurrency
2. Data sheet 、 Data fields must be annotated in Chinese , Over time, you will forget the exact meaning of the field
3. Stored procedures are not allowed , View , trigger ,Event, The purpose of the database is to store and index , Computing logic should be put into the service layer
4. It is forbidden to store large files and photos , Stored in the file system , Database storage URI that will do

Build a table to regulate

1. The field is designed as NOT NULL,NULL The column of / The index statistics / Value comparisons are more complex , Large storage space , You need to use is null 、is not null
2. Must have primary key , Increasing the primary key improves the efficiency of data row writing , And the primary key selects a shorter data type , It can reduce the index disk space , Suggest adding xxx_create ,xxx_update Field
3. Do not use foreign keys , Foreign keys cause coupling between tables , to update 、 The deletion operation will involve the associated table , It not only affects the performance, but also may cause deadlock
4. Use varchar(20) Store phone number , Because there will be symbols when it comes to countries （±）,varchar Support fuzzy query ‘like 173’
5. The size of a single table should not exceed 1 Ten million lines

The index specification

1. Single table index quantity control , Generally in 5 Within a
2. Joint index and overlay index are two aspects , A federated index is an index type , Coverage index is an optimal case , You don't have to go back to the table
3. A union index requires a leading column , Overlay index is to index both data , The index may only use one field

Joint index

Yes (a,b,c) Conduct joint indexing , Determine whether to go to the index

a = 1            //true
a = 1 and b = 2  //true
b = 2 and a = 1  //true,Mysql There is an optimizer that will automatically adjust a,b The order of is consistent with the index order , Pay attention to put the fields with high discrimination in front 
b = 1            //false,
a = 1 and b = 2 and c > 3 and d = 4 //a,b,c Three fields can be indexed , and d It doesn't match , The leftmost matching principle stops matching when it encounters a range query 

​
How to build a joint search problem

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;     // (a,b,c) (b,a,c) (c,a,b) Fine , Put the fields with high discrimination in the front 

SELECT * FROM table WHERE a > 1 and b = 2;               // (b,a) Index , The optimizer will help us adjust where after a,b The order of 

SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;   // (b,a)  or （b,c） Can the 

SELECT * FROM `table` WHERE a = 1 ORDER BY b;            //  Yes (a,b) Index building 

SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;      // Yes (a,b) Index , because IN It can be regarded as equivalent reference here , Index matching is not aborted 

​
Which situations are not suitable for indexing

1. Prohibit frequent updates 、 Build an index on the attribute field with low discrimination , Updates will change B+ Tree structure
2. “ Gender ” It is meaningless to index such attributes with low discrimination
3. Where Fields that cannot be used by the condition are not indexed
4. There are too few records
5. Frequently added and deleted tables

SQL matters needing attention

• Do not use where Use the function on the column name of the condition 、 Calculation 、 Type conversion
• It is forbidden to use left fuzzy or full fuzzy queries , Such as like ‘%abc’ ,like ‘%abc%’
• To use less or
• is null ,is not null You can't use the index
• It is forbidden to use large offset limit Pagination
• prohibit 3 Above the table join

Recommend learning ：
https://ipu.sankuai.com/app/course/detail/1927
High performance SQL and MySQL Official documents (https://www.mysql.com)