takeaway

As a back-end programmer, you can’t get away from the database, especially the ability to write SQL. If you’ve been to multiple interviews, you’re sure to find that the interviewers are always looking at SQL optimization.

The MOST common problem I encountered in many interviews before is SQL optimization, whether it is a large or small factory. After all, I also worked in CRUD for two or three years, and I wrote a lot of SQL. I also encountered some extremely long SQL with extremely long execution time. Therefore, I often sacrificed my nap time to produce statements for customers.

Sql optimization, first is to look at the Sql execution plan, and then according to the execution plan corresponding to the execution of the modification, such as the build index build index, and then balabala….

In fact, I have never learned how to look at execution plans before, just based on years of experience writing SQL nonsense. MySqL > MySqL > MySqL > MySqL > MySqL > MySqL > MySqL > MySqL > MySqL > MySqL

SQL execution plan (MySQL as an example)

1, How to view the execution plan of SQL

Add a keyword “EXPLAIN” before the query SQL that needs to be executed

As can be seen from the figure above, there are 12 fields in the execution plan. First, let’s briefly see the function of each column:

Idselect Query sequence number, containing a set of numbers, Select_type Query type Matching partition of table partitions accessed by TABLE Type Access type Possible_keys Possible index key Actual used index. If the value is NULL, Ref Show which column of the index is used in the rows estimate Find the desired data Number of rows to read filtered table rows % extra contains important information that is not suitable for showing in other columns. For example, whether to use index sort or file sort

2. Columns in EXPLAIN

(1) id

Select id, id, id, id, id, id, id, id, id, id, id, id

2. The larger the ID, the earlier the execution

3. If the IDS are the same, run the command from top to bottom

4. Last execution with id NUll

For example :(1) if the ids are the same, run the commands from top to bottom

explain 
select * from student,class,class_student;
Copy the code

(2) Different ids. A larger ID indicates a higher execution priority

explain 
select s.id,s.name,(select 1 from class) from student s;
Copy the code

(3) If the ID is the same but different, the larger the ID, the earlier the execution, the same ID from the top down

explain 
select id,name from student where id <5 union select s.id,s.
name from student s,class_student cs where cs.stu_id = s.id and cs.class_id = 4;
Copy the code

(2) select_type

Shows whether the row is SIMPLE or complex SELECT. The SIMPLE value indicates that there is no subquery or Union. If there is a subquery, the outermost layer is marked as Primary

Select * From SUBQUERY; select * From SUBQUERY;

explain 
select id,name,(select class_id from class_student) from student;
Copy the code

(2) Subqueries that are DERIVED From From are executed recursively by MySQL and put into a temporary table called a DERIVED table, DERIVED From the subquery.

The second and subsequent select in the UNION are marked as UNION. The first SELECT is marked as an external query to execute, and if the UNION is contained by a subquery in the From clause, its first SELECT is marked as DERIVED.

explain 
     select id,name from student where id>5
     union
     select id,name from student where id>15;
Copy the code

Example of UNION being included in the From clause:

explain
     select * from (
     select id,name from student where id>15
     union
     select id,name from student where id<4
     ) a;
Copy the code

The select used to retrieve the RESULT from the UNION’s anonymous temporary table is marked as UNION RESULT. You can see that in the example above

(5) DEPENDENT select depends on the data found in the outer query.

explain
     select s.id,s.name,(select class_id from class_student cs where cs.stu_id = s.id) from student s;
Copy the code

(6) Certain features in UNCACHEABLE SELECT prevent results from being cached in an Item_cache.

(3) table

Shows which table the data for the current row comes from

(4) type

The result values are in good order: NULL>system>const>eq_ref>ref>range>index>ALL

In general, make sure the query reaches at least range and preferably ref.

(1) there is only one row in the system table.

(2) Const indexes that can be found once, such as the primary key and the union key. The primary key in the WHERE condition converts the query to a constant. For example, id is the primary key in student

explain
   select * from student where id = 1;
Copy the code

(3) eQ_ref unique index scan. For each index key, only one record in the table matches it. Common for primary key or unique index scans.

explain
   select * from student s left join (select * from class_student where stu_id > 10)cs on cs.stu_id = s.id where cs.class_id = 4;
Copy the code

Select * from student where age is a normal index

explain 
select * from student where age = 20;
Copy the code

(5) range

Retrieves only the rows of a given range, using an index to select the rows, and the key column shows which index is used

2, it is commonly appear in the where clause for “>”, “<“, “between” and “in” conditions

3. This range scan index scan is better than a full table scan because it only needs to start at one point in the index and end at another point without scanning the whole index.

Such as:

explain
select * from student where id > 5;
Copy the code

(6) All full table scan, without any condition or index. For example, the score field in the student table is not indexed.

(5) possible_keys

Keys that might be used

(6) key

The actual key used

(7) key_len

The number of bytes used in an index that can be used to calculate which columns are used. The shorter the length, the better. The maximum possible length of the index field is displayed, not the actual used length, calculated from the table definition, not from an in-table search.

Note that: 1, char field one character in the best of 3 bytes in utf8 encoding, variable length field need extra two bytes record length, plus the need to deposit a null values, a null is a byte 2, composite index is characteristic of most left prefix, if a composite index can all use, is the sum of index of composite index field length, This can also be used to determine whether a composite index is used in part or in whole.

For example, the id of student is int, which is four bytes, so key_len is 4

explain
select * from student where id > 5;
Copy the code

The name field is vARCHar (20), so ken_len=20*3+2+1=63

EXPLAIN select * from student where name = 'c '; EXPLAIN select * from student where name =' c ';Copy the code

(8) ref

Shows which column of the index is used, if possible, as a constant. Which columns or constants are used to find values on index columns.

explain
select * from student s,class_student cs where cs.stu_id=s.id and cs.class_id = 4;
Copy the code

Stu_id (ken_len = 4 and ref = test.cs.stu_id)

(9) rows

Based on table statistics and index selection, the number of rows that need to be read to find the desired record is roughly estimated.

Extra (10)

Using filesort Indicates that mysql uses an external index sort for data, instead of reading data in the order of the indexes in the table. MySQL uses temporary tables to hold intermediate results. MySQL uses temporary tables to sort query results. Common in sort order by and group by queries. Using index indicates that the corresponding select operation Using index (Covering index), avoid access to the table row, efficiency is good! Using WHERE indicates that the index is used to perform a key lookup. If using WHERE is not present simultaneously, Using where uses the where condition. Using join buffer uses the join cache impossible The value of the whereWhere clause is always false and cannot be used to get any element distinct

Using filesort (Extra) : select * from Extra; select * from Extra;

Here’s a complete mind map:

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

I’m Liusy, a programmer who likes to work out.

For more dry goods and the latest news, please follow the official account: Ancient False God

If it is helpful to you, a point of attention is my biggest support!!

End small surprise: public account reply “MySQL”, send you a whole mind map of MySQL.