Indexing principle and slow query optimization

Time:2021-10-24

introduce

This blog will introduce the following knowledge:

  • Index introduction
  • Indexing principle
  • Data structure of index (binary tree — > balanced binary tree — > b tree — > b + tree)
  • Clustered index and secondary index
  • MySQL index management
  • Syntax for creating and deleting indexes
  • Test after index creation (change in query speed)
  • How to use index correctly
  • Back to table
  • Overlay index
  • Joint index
  • Leftmost prefix matching
  • Index push down
  • MySQL query optimization: explain
  • Basic steps of slow query optimization
  • Slow log management

1、 Index introduction

1. What is an index

  • Index isSorting the values of one or more columns in a database tableA data structure, you can use indexes to quickly access specific information in database tables
  • Indexing a database is like cataloguing a book

2. Why index

  • Optimize data query efficiency

The data of the database is generally stored in the disk. Compared with the memory, the access speed of the disk is slower. The index is a data structure that can help the database quickly find the data from the disk

  • Note: creating an index will reduce the efficiency of adding, deleting and modifying

Although it will be reduced, the read-write ratio of general application systems is about 10:1, and there are few performance problems in insert operations and general update operations. Some complex query operations are the most likely to encounter the most problems, so the optimization of query statements is obviously the top priority

3. Is it better to create more indexes for the table?

  • In most cases, we know that indexing can improve query efficiency, but too much will also affect application efficiency. How to add is the key
  • In the design of an application, too many or too few indexes on data will lead to the efficiency of the application, so we need to find a balance
  • When the table has a large amount of data, the speed of index creation will be very slow, and the performance for writing will be greatly reduced

4. When should the index be added

  • Any software has its bright spots that attract users. Behind the bright spots correspond to thermal data. Undoubtedly, developers should find out what database fields corresponding to thermal dataIn the process of developing software, the corresponding fields are indexed in advanceInstead of waiting for the DBA to find slow SQL queries after the software goes online

reason:

1. A slow software will affect the user experience, but there are many reasons. You can’t immediately determine that it is the SQL problem. It has been a long time since you locate the SQL problem, and the problem has not been solved in time

2. Most DBAs are management DBAs rather than development DBAs, so even if DBAs see slow query SQL from the log, it will be difficult to analyze the reasons for the slow query because they do not understand the business

2、 Indexing principle

1. Principle of index

  • Filter out the final results by constantly narrowing the data range you want to query

Like buying train tickets(No index): if there is no 12360 train ticket ordering software, there are thousands of trains in front of us. The conditions for selecting that one include train type, departure and destination, time, etc. we need to compare our screening conditions one by one. The first train is the train to be found if we are lucky, and the 1000th train is the train to be found if we are not lucky

Join index: now we only need to select high-speed rail on 12360 software to screen out trains that are not high-speed rail, narrowing the query scope; Then input the starting point and end point to narrow the query range; Then input the time, reduce the range, and finally find the train number you need,From non fixed query times to very small fixed query times

2. Disk I / O and read ahead

  • I \ o latency

IO delay = average seek time + average delay time (generally 9ms) – > example: assuming that the current rotation speed of the hard disk shaft (disk) is 7200 / min, that is, 120 / s, it takes 1 / 120 ≈ 8ms for one revolution, that is, 4ms for half a revolution (assuming that it takes half a revolution to find data)

9msAbout is very short for us, but it can be executed per second for a 500-mips machine500 millionAn instruction, in other words, executesPrimary IOThe time can be executed400 thousandIt’s a disaster that the database has hundreds of thousands of millions or even tens of millions of data in 9 milliseconds each time

  • read-ahead

Considering that disk IO is a very expensive operation, the computer operating system has made some optimization. During an IO, not only the data of the current disk address, but also the adjacent data are read into the memory buffer, because the principle of local pre reading tells us that when the computer accesses the data of an address, the adjacent data will also be accessed soon. The data read by each IO is called a page. The specific data size of a page is related to the operating system, generally 4K or 8K, that is, when we read the data in a page, there is actually only one io. This theory is very helpful for the data structure design of the index

3、 Indexed data structure

The data structure of the index isB + treeB + tree evolved from binary sort tree to binary balance tree, then to B tree, and finally to B + tree. The following is a brief introduction:

1. Binary sort tree (binary lookup tree)

  • The top node is called the root node, and the node without child nodes is called the leaf node (the bottom row)

For a list of numbers: 5, 6, 7, 8, 9, 10

image-20210224174432982

  • If we need to find a node with key = 9, first compare 9 with the root node, which is larger than the root node, and then look to the right; Continue to compare with the 10 on the right, less than, so look to the left and just find nine

Using binary sort tree, we only need 3 times to find the matching data; If we search one by one in the number column, we need 5 times to find it

2. Balanced binary tree (AVL tree)

  • Balanced binary tree can be said to be an improved version of binary sort tree, which is a special binary sort tree

Above, we explained that using binary sort tree can quickly find data; However, if the above binary sort tree is constructed as follows:

image-20210224175721158

The average search length is 3. If we adjust the sequence of keywords

image-20210224180204999

After adjustment, the average search length is 2.2. From the above, we can see that the average search length is related to the height of the number. The smaller the average search length, the faster the search speed. Therefore, we should make the tree as short as possible

  • How to judge whether a binary tree is a balanced binary tree?

The concept of balance factor is introduced here. The height of the left subtree minus the height of the right subtree is the balance factor. If the absolute value of the balance factor is less than or equal to one, it is a balanced binary tree, and if it is greater than one, it is an unbalanced binary tree. As shown in the figure below, if the balance factor is 4, it is an unbalanced binary tree

image-20210224181012776

Let’s adjust the keyword sequence. If the absolute value of each sub number balance factor is less than or equal to 1, then this is a balanced binary tree

image-20210224181425555

3. Balanced tree

  • We know that each node of balanced binary tree can only store one key value and data

What if we want to store massive amounts of data? It can be imagined that there will be many nodes in the binary tree, and the height will be extremely high. We will also perform disk IO many times when looking for data, and the efficiency of looking for data will be very low

  • In order to solve the disadvantage of balanced binary tree, we should look for a balanced tree in which a single node can store multiple key values and data, that is, B tree

img

As can be seen from the above figure, compared with the balanced binary tree, each node of the B tree (the node in the B tree is called page) stores more keys and data, and each node has more child nodes. The number of child nodes is generally called order. The B tree in the above figure is a third-order B tree, and the height will be very low. Based on this feature, the number of times the B-tree looks up data and reads the disk will be few, and the data search efficiency will be much higher than that of the balanced binary tree

Assuming that each node can store two values (not only two), we find 75:

  • Compare with page 1, find P3 on the right of 35, and locate the pointer to page 4
  • Compared with the index in page 4, find the pointer P2 between 65-87 and locate page 10
  • Compare with the index on page 10 and find the corresponding 75

4. B + tree

  • B + tree is a further optimization of B tree

img

  • Let’s compare the difference between B + tree and B tree through the above figure:
  • B + treeData is not stored on non leaf nodes, only key values are storedThe B tree node stores not only key values, but also data

  • The reason for this is that the size of pages in the database is fixed, and the default size of pages in InnoDB is 16kb. If the data is not stored, more key values will be stored, the order of the corresponding tree (the child node tree of the node) will be larger, and the tree will be shorter and fatter. In this way, the IO times of finding data for disk will be reduced again, and the efficiency of data query will be faster

  • The order of B + tree is equal to the number of key values. If one node of our B + tree can store 1000 key values, then the three-tier B + tree can store 1000 × one thousand × 1000 = 1 billion data.

  • commonlyThe root node is memory residentTherefore, generally, we only need 2 disk IO to find 1 billion data

  • The three-tier B + tree can represent millions of data. If millions of data searches only need two IO, the performance improvement will be huge. If there is no index and each data item needs one IO, a total of millions of IO are required. Obviously, the cost is very high

  • Two properties of B + tree
  • The index field should be as small as possible: the size of the disk block, that is, the size of a data page, is fixed. If the smaller the space occupied by data items, the more data items, the lower the height of the tree, and the fewer IO times queried. This is why each data item, that is, the index field, should be as small as possible. For example, int occupies 4 bytes, which is half less than bigint8 bytes. This is why the B + tree requires that the real data be placed in the leaf node instead of the inner node. Once placed in the inner node, the data items of the disk block will decrease significantly, and the decrease will lead to less data that can be stored in each layer. Because the disk block is fixed, it is necessary to increase the level, resulting in the increase of the tree. The increase of the tree means the increase of IO times to find the underlying data, This results in a significant decrease in query speed
  • Leftmost matching feature of the index: when the data items of the B + tree are composite data structures, such as (name, age, sex), the B + number establishes the search tree from left to right. For example, when data such as (Zhang San, 20, f) is retrieved, the B + tree will give priority to comparing name to determine the search direction in the next step. If the name is the same, then compare age and sex in turn, Finally, the retrieved data is obtained. However, when data such as (20, f) without name comes, the B + tree does not know which node to query next, because name is the first comparison factor when establishing the search tree. You must search according to name first to know where to query next. For example, when retrieving data such as (Zhang San, f), the B + tree can use name to specify the search direction, but the next field age is missing, so we can only find all the data whose name is equal to Zhang San, and then match the data whose gender is F. this is a very important property, that is, the leftmost matching feature of the index

5. Summarize the advantages of B + tree

  • Further optimization is made on the basis of binary tree, balanced binary tree and B tree. Only leaf nodes put real data, which means that the height of B + tree is the lowest on the premise of equal amount of data
  • The leaf nodes of B + are arranged in order, which means that the B + tree is faster than the B tree in range query. Once a leaf node is found, there is no need to check from the tree root

4、 Clustered index and secondary index

The B + tree index in the database can be divided into clustered index and secondary index. The same thing about clustered index and secondary index is that whether it is clustered index or secondary index, it is in the form of B + tree, that is, the height is balanced. The difference is:

The leaf node of the clustered index stores a whole row of complete information, while the leaf node of the auxiliary index stores incomplete information (described below)

1. Clustered index

The leaf node of the InnoDB clustered index stores row records, so InnoDB must have and only have one clustered index

  • If the table defines pK (primary key), PK is a clustered index

  • If the table has no PK defined, the first column that is not empty and unique (not null unique) is the clustered index

  • Otherwise, InnoDB will create another hidden ROWID as the clustered index

Because this mechanism directly locates row records, the query speed based on PK is very fast

2. Secondary index

All indexes in the table except clustered indexes are secondary indexes (also known as nonclustered indexes)

  • The difference from the clustered index is that the leaf node of the secondary index does not contain all the data of the row record. In addition to the key value, the index row in each leaf node also contains a bookmark. This bookmark is used to tell the InnoDB storage engine where to find the row data corresponding to the index

  • The existence of secondary indexes does not affect the organization of data in the clustered index. Therefore, there can be multiple secondary indexes on each table, but only one clustered index

  • When searching for data through the auxiliary index, the InnoDB storage engine will traverse the auxiliary index, obtain the primary key pointing to the primary key index through the leaf level pointer, and then find a complete row record through the primary key index

5、 MySQL index management

1. Function

  • The function of index is to speed up search
  • The primary key, unique and unique indexes in MySQL are also indexes. These indexes not only speed up the search, but also have the function of constraints

2. Common indexes (keys) in MySQL

  • General index: index to speed up search
  • Unique index:
    • Primary key index: primary key accelerated search + constraint
    • Unique index: unique key accelerated lookup + constraint
  • Federated index:
    • Joint primary key index: primary key
    • Joint unique index: unique (field 1, field 2,…)
    • Joint general index: unique (field 1, field 2,…)

For the above three indexes, the unique index has constraints in addition to increasing the query speed, while the ordinary index key has no constraints and is only used to help you quickly query data

Note: the joint index is not used to speed up the query and is not within the scope of our research

3. Two types of indexes

  • We can specify the index type (two types) when creating an index
Hash type index: single query is fast and range query is slow
BTREE type index: B + tree. The more layers, the more data volume grows exponentially (we use it because InnoDB supports it by default)
  • Different storage engines support different index types
InnoDB supports transactions, row level locking, B-tree, full text and other indexes, but does not support hash indexes
MyISAM does not support transactions, table level locking, B-tree, full text and other indexes, and hash indexes
Memory does not support transactions, table level locking, B-tree, hash and other indexes, and does not support full text indexes
NDB supports transactions, row level locking, hash indexes, but not B-tree, full text and other indexes
Archive does not support transactions, table level locking, and indexes such as B-tree, hash, and full text

6、 Create and delete index syntax

1. Three methods of creating

  • grammar
Method 1: create an index when creating a table
Create table [table name](
    [unique|fulltext|spatial] [index|key] [index name] [field name (length)] [asc|desc]
    );
    
Method 2: create an existing table
Create [unique|fulltext|spatial] index [index name]
    On [table name] [field name (length)] [asc|desc];

Method 2: alter creates an index on an existing table
Alter table [table name] add [unique|fulltext|spatial] index 
    [index name] [field name (length)] [asc|desc];
  • Sample code
Mode 1
create table t01(
    id int,
    name char(10),
    age int,
    sex enum("male","female"),
    unique key unique_id(id),
    index index_ Name (name) # index has no key
);

Mode II
create index index_age on t01(age);

Mode III
alter table t01 add index index_sex(sex);

2. Delete index

Drop index [index name] on T01# grammar
drop index index_ age on t01;  #  Example

7、 Index test

1. Prepare a table and insert a large amount of data

Create table
create table t01(
    id int,
    name varchar(10),
    sex enum("male","female"),
    email varchar(18)
);

Create a stored procedure to automatically insert records
delimiter %%%
create procedure p01()
begin
    declare i int default 1;
    while(i<3000000)do
        insert t01 value(i,"shawn","male",concat("shawn",i,"@163.com"));
        set i=i+1;
    end while;
end %%%
delimiter ;

Viewing stored procedures
Show create procedure P01 \ g # \ g display results vertically

Call stored procedure
call p01();  #  Windows runs the test for about an hour and a half, with 3 million records and more than 200 m

Delete stored procedure
drop procedure p01;

image-20210225095534649

2. Test the query speed without establishing an index

select * from t01 where id=3000000;

There is no index. MySQL does not know whether there is this record, so it traverses the record from beginning to end. It needs to do as many I \ o as there are disk blocks, and the speed is very slow

image-20210225095645650

3. Create an index for a field of the table (the table already has a large number of records, and the creation speed will be very slow)

create index index_ id on t01(id);  #  General index for ID field

Observe that the data file size of T01 table in the data folder has increased

image-20210225100323340

image-20210225110724526

4. Set the indexed fields as criteria for query

select * from t01 where id=3000000;  #  A significant increase in speed can be observed

image-20210225101246174

5. Step analysis

  • MySQL goes to the index table first. According to the search principle of B + tree, it quickly searches for records with ID equal to 3000000, directly hits the index, greatly reduces IO, and thus significantly improves the speed

  • We can find that the speed is still very slow when we query the fields without index

    select * from t01 email=" [email protected] ";  #  And the larger the record, the slower the query

    image-20210225110358350

  • Try indexing the email field

create index index_ email on t01(email);  #  The larger the field data, the longer the establishment time (so it is recommended not to use the field with large data to establish the index. This is just an experiment)
select * from t01 where email=" [email protected] ";  #  If you query again, you can find that the speed is an order of magnitude increase

image-20210225111132774

8、 Use index correctly

It doesn’t mean that creating an index can speed up the query. In some cases, the index may not have a good speed-up effect. Let’s test various situations (if you don’t want to see the process, you can directly see the conclusion at the end of the summary)

1. Range query: or the conditions are not clear. The conditions include: >, > =, <, < =,! = between…and…、like

  • Size: ><

image-20210225145700684

  • Not equal to:=

image-20210225145844390

  • between…and…

image-20210225150055303

  • Fuzzy matching: like

like

2. Discrimination: discrimination indicates the proportion of fields that are not repeated. The greater the discrimination, the faster the scanning speed. For example, the primary key is unique, while the discrimination of gender fields is very low

image-20210225153807708

The above situation occurs because the field discrimination is too low. In the B + tree, the size of these fields cannot be compared because the values are equal. There is no doubt that the height of the tree can only be increased to ensure the storage of these data. The higher the height of the tree, the slower the query speed

3. = and in can be out of order. For example, a = 1 and B = 2 and C = 3. You can establish (a, B, c) indexes in any order. The MySQL query optimizer will help you optimize them into a form that can be recognized by the index

4. The index column should not be involved in calculation or function, such as salary multiplication and 12: do not use field multiplication (salary * 12 = 10000), but use the value corresponding to the field to multiply (salary = 10000 * 12)

image-20210225155533918

5. Index push down technology

The logic of "and" and "or"
    [condition 1] and [condition 2]: all conditions are true, but if one condition is not true, the final result is not true
    [condition 1] or [condition 2]: as long as one condition is true, the final result is true

How "and" works
    Conditions:        
        a = 10 and b = 'xxx' and c > 3 and d =4    
    Indexes:        
        Make joint index (D, a, B, c)    
    working principle:        
        For consecutive and: MySQL, a highly differentiated index field will be found from left to right according to the joint index (so that a small range can be quickly locked) to speed up the query, that is, in the order of D - > A - > b - > C

Working principle of "or"
    Conditions:        
        a = 10 or b = 'xxx' or c > 3 or d =4    
    Indexes:        
        Make joint index (D, a, B, c)            
    working principle:        
        For consecutive or: MySQL will judge from left to right according to the order of conditions, that is, a - > b - > C - > D

6. Leftmost prefix matching principle

7. Summary (how to build an index to improve query efficiency)

  • Index fields that are highly differentiated and take up less space
  • The range query hits the index. If the range is large and the query efficiency is still low, how to solve it
    • Or narrow it down
    • Or take values in sections, one by one, and finally get a large range
  • Do not put query fields into functions or participate in operations
  • With the index push down technology, MySQL automatically selects the statement with the best query speed (enabled by default)
  • Index override (described below)
  • Leftmost prefix matching principle (described below)

8. Other precautions

  • Avoid using select*
  • Count (1) or count (column) instead of count (*)
  • When creating tables, try to replace varchar with char
  • The field order of the table is fixed, and the field with fixed length takes precedence
  • Composite index replaces multiple single column indexes (when multiple conditional queries are often used)
  • Try to use short indexes
  • Use join instead of sub queries
  • When connecting tables, pay attention to the consistency of condition types
  • Index hash value (less repetition) is not suitable for indexing, for example, gender is not suitable

9、 Back to table, overlay index, union index, leftmost prefix

Matching principle, index push down

1. Create a table and insert records

create table user(
    -> id int not null auto_increment,
    -> name char(16) not null,
    -> age int not null,
    ->Primary key (ID), # ID is the primary key and set the index (clustered index)
    -> index index_ name(name));    #  Name field set index (secondary index)

insert user(name,age) value
    -> ("shawn",23),
    -> ("song",22),
    -> ("hai",20),
    -> ("xing",18),
    -> ("yanxi",45),
    -> ("zichen",25);

image-20210225170144065

2. Return table

select * from user where id=2;

The above is the primary key query method, that is, the complete record with ID 2 can be found through the clustered index

select * from user where name="song";

The above is the auxiliary index query method. First search the name index tree to get the ID value corresponding to song as 2, and then search the ID index tree,This process is called back to table

  • Conclusion: therefore, the query based on auxiliary index needs to scan one more index tree. Therefore, we should try to use clustered indexes in our applications

3. Overlay index

select id from user where name="hai";

The query condition of the above statement is the name field. The name field has an index tree, and the values of name and ID are saved on it. The query results can be directly provided without back table operation. In other words, in this query, the index name has covered the requirements of the ID field we want to query,This is called overlay index

select age from user where name="xing";

The above statement finds the “Xing” and ID values corresponding to the name field through the name index tree, but there is no age field information, so it performs a back table operation through the ID field to find the data that meets the conditions

  • The operation of returning the table will undoubtedly reduce the efficiency. We can index the age again to avoid the efficiency problems caused by too many indexes, or we can establish a joint index for it

4. Joint index and leftmost matching principle

🔰Joint indexIt refers to the creation of an index for multiple columns in a table. The creation method of a joint index is the same as that of a single index, except that there are multiple index columns

🔰Leftmost prefix matchingPrinciple is a very important principle. MySQL will match from left to right

  • First delete the index of the name field, and then create a federated index with age
drop index index_name on user;
create index index_ name_ age on user(name,age);  #  In practical application, the most commonly used fields should be placed on the far left
  • When the following fields appear in the query criteria, the union index can be hit because it conforms to the leftmost prefix principle
select name,age from user where name="song";  #  Condition field name
select name,age from user where name="song" and age>18;  #  Condition field name + age
  • Only one age field appears in the query criteria and cannot hit the union index
select name,age from user where age=2;  #  Condition field age (no union index)

5. Index push down (MySQL is automatically enabled)

Index push down(index condition pushdown) referred to as ICP, inMysql5.6For optimizing queries

Using the leftmost prefix matching principle + joint query can speed up the query speed. If our conditions have range query, how does the SQL statement run?

select * from user where name like "s%" and age=22;

image-20210225180632714

As shown in the above table, there are two records beginning with “s”

  • Before MySQL 5.6There is no index recursion optimization
InnoDB will ignore the age field and query directly through the name. It will find two results on the (name, age) joint index, and then get the ID of 1 and 2 to "query back to the table twice"
  • MySQL 5.6 and laterAdded index push down optimization
InnoDB does not ignore the age field, but determines whether the age is equal to 22 in the index. Records that are not equal to 22 are skipped directly
Therefore, only one record is matched in the (name, age) joint index. At this time, you need to "return to the table once" to return all data with this ID

10、 MySQL query optimization: explain

Official documents: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

ps: it is emphasized that rows is the core indicator, and most small rows statements must execute quickly. Therefore, the optimization statements are basically optimizing rows

1. What is explain

Explain is short for viewing the execution plan. Use the explain keyword to simulate the optimizer to execute SQL query statements, so as to know how MySQL handles SQL statements

2. How to use explain

Syntax:Explain + [SQL statement]

3. Function of explain

  • Read order of table
  • Operation type of data read operation
  • Which indexes can be used
  • Which indexes are actually used
  • References between tables
  • How many rows per table are queried by the optimizer

4. Field information contained in the execution schedule

explain select * from t01;

image-20210225183254836

5. Schedule field description

  • idfield
field explain
id The serial number of the query in the selected execution plan by MySQL query optimizer. Indicates the order in which the select clause or operation table is executed in the query. The larger the ID value, the higher the priority, and the earlier it is executed; If the IDs are the same, the execution sequence is from top to bottom
  • select_typefield
select_ Type query type explain
SIMPLE Simple select query without Union and sub query
PRIMARY Outermost select query
UNION The second or subsequent select query in the union does not depend on the result set of the external query
DEPENDENT UNION The second or subsequent select query in the union depends on the result set of the external query
SUBQUERY The first select query in the subquery does not depend on the result set of the external query
DEPENDENT SUBQUERY The first select query in the subquery depends on the result set of the external query
DERIVED Used when there are subqueries in the from clause. MySQL will recursively execute these sub queries and put the results in a temporary table
UNCACHEABLE SUBQUERY A subquery whose result set cannot be cached must be re evaluated for each row of the outer query
UNCACHEABLE UNION The second or subsequent select query in the union is a non cacheable subquery
  • tablefield
field explain
table Table referenced by output row
  • typefield

Very important items, showing the types used by the connection, sorted from the best to the worst

Type: connection type explain
system The table has only one row (= system table). This is a special case of const connection type
const Const is used when comparing primary keys with constant values. When the query table has only one row, use system
eq_ref Const is used when comparing primary keys with constant values. When the query table has only one row, use system
ref The connection cannot select a single row based on the keyword, and multiple qualified rows may be found. It is called ref because the index is compared with a reference value. This reference value is either a constant or a result value from a multi table query in a table
ref_or_null Like ref, MySQL must find null entries in the results of the initial search, and then perform a secondary search.
index_merge Description Index merge optimization is used
unique_subquery This type is used in some in queries instead of the regular Ref: value in (select primary_key from single_table where some_expr)
index_subquery This type is used in some in queries, similar to unique_ Subquery is similar, but the query is a non unique index: value in (select key_column from single_table where some_expr)
range Retrieve only rows in a given range, using an index to select rows. The key column shows which index is used. When using =, < >, >, > =, <, < =, is null, < = >, between or in operators to compare keyword columns with constants, you can use range
index Full table scanning is only performed according to the index order rather than rows when scanning the table. The main advantage is that sorting is avoided, but the overhead is still very large
all In the worst case, full table scanning from beginning to end
  • possible_keysfield
field explain
possible_keys Indicate which indexes MySQL can use in the table to help query. If it is empty, there is no index available
  • keyfield
field explain
key MySQL is actually from possible_ Key select the index to use. If NULL, no index is used. In rare cases, MySQL will choose indexes that are not optimized enough. In this case, use index (indexname) can be used in the select statement to force an index to be used, or ignore index (indexname) can be used to force Mysql to ignore an index
  • key_lenfield
field explain
key_len The length of the index used. The shorter the length, the better without losing accuracy.
  • reffield
field explain
ref Displays which column of the index is used
  • rowsfield
field explain
rows The number of rows that MySQL considers necessary to check to return the requested data
  • Extrafield
Extra item explain
Using filesort It means that MySQL will sort the results using an external index instead of reading the relevant contents from the table according to the index order. It may be sorted in memory or on disk. The sort operation that cannot be completed by index in MySQL is called “file sort”
Using temporary Indicates that MySQL uses temporary tables when sorting query results. It is common in sorting order by and grouping query group by

6. Examples

explain select * from t01 where id=100000;
explain select * from t01 where id>10000 and id<20000;
explain select * from t01 where id>20000;

image-20210225185120221

11、 Basic steps of slow query optimization

  • Run it first to see if it is really slow. Pay attention to setting SQL_ NO_ CACHE
  • Query the where condition sheet table to lock the minimum return record table. The meaning of this sentence is to apply the where of the query statement to the table with the smallest number of records returned in the table. Start to query each field of a single table to see which field has the highest discrimination
  • Explain to check whether the execution plan is consistent with 1 expectations (query from the table with few locked records)
  • The SQL statement in the form of order by limit gives priority to the sorted table
  • Understand the usage scenarios of the business party
  • Several principles of building indexes when adding indexes
  • The observation results are not as expected. Continue to analyze from the first step

12、 Slow log management

Slow log management: https://www.cnblogs.com/songhaixing/p/14448814.html