This article has participated in the third “topic writing” track of the Denver Creators Training Camp. For details, check out: Digg Project | Creators Training Camp third is ongoing, “write” to make a personal impact

preface

As the name implies, slow SQL execution time is too long) is an SQL statement, that if we exist in the system a lot long slow SQL execution time, not only causes the request RT is too high, if request traffic increased in a short period of time, also because of the slow SQL database connection time is too long, lead to the new request for less than the database connection error.

If you mention slow SQL, you may not use the first impression of index, index failure, explain to look at the SQL execution plan and so on, so the slow SQL is basically linked to the index, so this article from the index classification, index failure, SQL execution plan classification to discuss how to avoid writing slow SQL in the development stage.

Of course, if the magnitude of data reaches a certain level, it is not necessary to find a suitable and efficient index to achieve the expected execution time, and then we may need to deal with the database and table.

Index and Classification

Indexes are data structures that help MYSQL efficiently retrieve data -> quickly order lookup data structures.

Light blue blocks –> Disk blocks

Yellow block ——> pointer

Dark blue block —–> data item

P1 indicates disk blocks smaller than 17, P2 indicates disk blocks between 17 and 35, and P3 indicates disk blocks larger than 35. Real databases are stored on leaf nodes. Non-leaf nodes do not store real data, but only data items that guide search directions

Search process: if data item 29 is to be searched, disk block 1 is first loaded from disk to memory. At this time, an I/O occurs. In memory, the search continues to make sure that 19 is between 17 and 35, and the P2 pointer of disk block 1 is locked. The P2 pointer of disk block 3 is locked. The P2 pointer of disk block 3 is loaded into the memory using the disk address of the P2 pointer of disk block 1. The second I/O occurs

The primary key index

The creation method is as follows:

ALTER table table_name add primary key(col_name);
Copy the code

The only index

The creation method is as follows:

CREATE UNIQUE INDEX index_name ON table_name(col_name);
Copy the code

Normal index

The creation method is as follows:

REATE INDEX index_name ON table_name(col_name);
Copy the code

Joint index

The creation method is as follows:

CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
Copy the code

Explain To view SQL execution plans

Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL queries to see how MYSQL processes your SQL statements. Analyze performance bottlenecks in your query or table structure.

explain sql;
Copy the code

Index Failure Scenario

1. The best left-prefix rule: If you index multiple columns (compound indexes), follow the left-most prefix rule. The query starts at the leftmost column of the composite index and does not skip columns in the composite index (in case of index invalidation)

2. Do not perform any operation (calculation, function, (automatic or manual) type conversion) on the index column, which will cause the index to fail and turn to full table scan

3. The storage engine cannot use the column to the right of the range condition in the index. That is, if a column in a composite index participates in a range condition, all columns after that column are invalidated

For example, compound index –>(column1,column2,column3). If you can query where cloumn1 = value1 and cloumn2>1 and column3=values3,column3 is invalid

4. Use overridden indexes (queries that access only the index (the index column is the same as the query column) instead of select *

5.. Mysql in use does not equal (! = or <>) will cause index invalidation and full table scan

6. Is NULL or IS not NULL invalidates the index

7. Like start with wildcard character (‘% ABC %’)MySQL index will be invalid, resulting in full table scan. But wildcards appearing only on the right side will not invalidate the index.

Resolve the like ‘% string….. Index failure problem: Use an overwrite index, or query the middle column of a composite index

8. If the string is not quoted, the index will be invalid