Catalog

One 、MySQL in SQL Statement execution procedure

Two 、MySQL Optimizer and classification

3、 ... and 、 Implementation plan - Explain

 1. What is execution plan ？

 2. The role of implementing the plan

  3. Information contained in the implementation plan

Four 、 Use index correctly

One 、MySQL in SQL Statement execution procedure

MySQL in SQL The statement execution process is shown below ：

Explanation of words ： 

  1. client Send a query to The server .
  2. Server first Check the query cache , If the cache is hit , Returns the result stored in the cache immediately . Otherwise move on to the next stage .
  3. On the server side SQL analysis 、 Preprocessing , Then the optimizer generates the corresponding execution plan .
  4.MySQL According to the execution plan generated by the optimizer , Call the storage engine again API To execute the query .
  5. Returns the result to the client .

Two 、MySQL Optimizer and classification

The optimizer in traditional relational database is divided into CBO and RBO Two kinds of .

 1）RBO--- Rule_Based Potimizer Rule based optimizer :

  RBO :RBO The judgment rules used are a set of built-in rules , These rules are hard coded in the database ,RBO Will follow these rules from SQL Choose one of many paths as the implementation plan （ For example RBO Inside , There is such a rule ： There is an index, use the index . Then all tables with indexes will follow the index in any case ） therefore ,RBO Now abandoned by many databases （oracle The default is CBO, But still keep RBO Code ,MySQL Only CBO）

      RBO The big question is A series of fixed rules hard coded in the database , To decide on the implementation plan . and Didn't consider the goal SQL The actual number of objects involved in , Distribution of actual data , In this way, once the rule does not apply to the SQL, Then it is likely that the selected execution plan is not the optimal execution plan .

2）CBO---Cost_Based Potimizer Cost based optimizer :

      CBO :CBO Among the execution paths with many targets, the execution path with the lowest cost will be selected as the execution plan . The cost here actually represents MySQL Calculate the target according to relevant statistical information SQL Corresponding steps IO,CPU Equal consumption . That means that the cost in the database is actually for the execution target SQL Required IO,CPU An estimate of such resources . The cost value is based on the index , surface , The statistics of the row are calculated .( The calculation process is complicated )    

3、 ... and 、 Implementation plan - Explain

 1. What is execution plan ？

      Use EXPLAIN Keyword can simulate optimizer execution SQL Query statement , So they know MYSQL How to deal with you sql Of the statement . Analyze the performance bottleneck of your query statement or table structure .（ grammar : Explain + sql）

 2. The role of implementing the plan

     1) Read order of tables
     2) Operation type of data read operation
     3) Which indexes can be used
     4) Which indexes are actually used
     5) References between tables
     6) How many rows per table are checked by the optimizer   

  3. Information contained in the implementation plan

   3.1 id - obtain select Operation table order of clause , There are several situations

     1) id In the same case, the execution sequence is From top to bottom .
     2) id The higher the priority, the higher , If it's a subquery ,ID The serial number will increase ,id The bigger the value is. , The higher the priority , Execute first .
     3) id Same but different , Those with large serial numbers will execute first , Then perform the same from top to bottom .

Example ： 

#1）id identical , From top to bottom 
# Left outreach 
explain select * from t_user t1 left join t_role t2 on t1.roleid = t2.roleid;

# Right outreach 
explain select * from t_role t1 right join t_user t2 on t1.roleid = t2.roleid;

  Left outreach

  Right outreach

  Be careful ： adopt left join and right join verification ;id equally （ Pay attention to the implementation of the plan table Column ）,left join Scan first a surface , Scan again b surface ;right join Scan first b surface , Scan again a surface

3.2 select_type - Type of query , It is mainly used to distinguish ordinary queries , The joint query , Complex queries such as subqueries

     1) simple: ordinary select Inquire about , No subqueries or union
     2) primary: The query contains any complex subparts , The outermost query is marked
     3) subquery: stay select perhaps where The list contains subqueries
     4) derived: stay from The list contains subqueries marked with derived Mysql These subqueries will be executed recursively , Put the results in a temporary table
     5) union: If in the second select It appears that union after , Is marked as union if union Included in from Clause , Outer layer select Will be marked as derived
     6) union result: from union Table to get the result SELECT

Example ：

• SIMPLE( Simple SELECT, Don't use UNION Or subquery, etc )

explain select * from t_user where username = ' Zhang San ';

•  PRIMARY( If the query contains any complex sub parts , The outermost select Marked as PRIMARY)SUBQUERY( First in subquery SELECT)

explain select * from t_user t1 where t1.roleid = (select roleid from t_role t2 where t2.roleid = 1);

•  UNION(UNION The second or later of SELECT sentence )

explain
select * from t_user t1 where t1.id = 1 
union all
select * from t_user t2 where t2.id = 2;

•  DERIVED( The derived / Derivative statements SELECT, FROM A subquery of a clause )

explain select tmp.* from (
	select * from t_user t1 where t1.id = 1 
	union all
	select * from t_user t2 where t2.id = 2
) tmp;

 3.3 table - The data showing this row is about that table

 3.4 type - It shows the type of access  

type Is a more important indicator , The result value from the best to the worst is ：（  notes ： Generally speaking , Make sure that the query is at least range Level , It's better to achieve ref）

  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery 
     > index_subquery > range > index > all

    1) system: There is only one line in the table （ It's equal to the system table ）, This is a const Type of specials , Not usually , This is negligible ;
     2) const:   Indicates that it is found through index once ,const For comparison primary key perhaps unqiue Indexes , Because only one piece of data is matched , So soon , For example, place the primary key in where In the condition ,Mysql You can convert the query to a constant ;
     3) eq_ref: Unique index scan , For each index key , Only one record in the table matches it , Common with primary key or unique index scanning ;
     4) ref:     Non unique index scan , Returns all rows that match a single value , It is common to use non unique search , Composite index query ;
     5) range:   Retrieve only rows in the given range , Use an index to select rows ,key Column shows which index is used ,  It's usually in your where In the sentence between ,<,>,in Such range scanning as query is better than full table scanning ,  Because it only needs to start with a certain point of the index , And ending with another point , Don't scan all indexes .
     6) ALL：   Full table scan ;
     7) index： Scan all index trees ;
     8) NULL：  MySQL Decompose statements during optimization , Execute without even accessing tables or indexes ;

   3.5 possible_keys

     Pointed out that MySQL Which index can I use to find rows in this table . If there is an index on the field involved in the query , Then the index will be listed , But not necessarily used by queries . If it's empty , There is no relevant index . This is the time to improve performance , It can pass the test WHERE Clause , See if some fields are referenced , Or check that the field is not suitable for indexing .（ In short ： Possible use key（ Indexes ））

3.6 key 

    Index actually used , If the index is not used , Then for NULL, If an overlay index is used in the query , Then the index and query select Fields overlap .

3.7 key_len

    Show MySQL Determine the key length to use . Represents the number of bytes used in the index , You can use this column to calculate the length of the index used in the query . If the key is NULL, Length is NULL. The document prompts you to pay special attention to this value. You can get a multiple primary key mysql Which part is actually used .

3.8 ref

Show which field or constant is related to key Used together
3.9 rows

      This number represents mysql How much data does it take to traverse to find , Express MySQL According to table statistics and index selection , Estimated number of rows to read to find the required record , stay innodb It may be inaccurate .

3.10 Extra

Contains additional information that is not suitable for display in other columns but is very important ：

     1) Using index     This value represents mysql Will use overlay index , To avoid accessing tables .
     2) Using where    mysql The storage engine will retrieve the rows before filtering , many where The condition refers to the columns in the index , When ( And if the ) When it reads the index , Can be tested by the storage engine , So not all belts where The query in clause will show “Using where”. Sometimes “Using where” The appearance of is a hint ： Queries can benefit from different indexes .
     3) Using temporary    mysql The temporary table is used to sort the query results .
     4) Using filesort    mysql An external index will be used to sort the results , Instead of reading rows from the table in index order .mysql There are two file sorting algorithms , Both sorts can be done in memory or on disk ,explain I won't tell you mysql What sort of file will be used , It will not tell you whether the sorting will be done in memory or on disk .

Four 、 Use index correctly

Example 1 ： Use like When the sentence is ,% The index is only used on the right

# It works 
explain select * from t_user t where t.username like ' Zhang %';

# Invalid 
explain select * from t_user t where t.username like '% Zhang %';

front ： It works     after ： Invalid

Example 2 ：or The index will become invalid only if there is an unindexed column in the condition

# It works 
explain select * from t_user t where t.username = ' Zhang San ' and id =1;

# Invalid 
explain select * from t_user t where t.username = ' Zhang San ' or id = 1;

front ： It works     after ： Invalid

Example 3 ：  The type of condition is inconsistent

# It works 
explain select * from t_user t where t.username = '12345';

# Invalid 
explain select * from t_user t where t.username = 12345;

  front ： It works     after ： Invalid

  Example 4 ：!= Number （ exception ： If it's a primary key , The index will go ）

# Invalid 
explain select * from t_user t where t.username != ' Zhang San ';
# It works 
explain select * from t_user t where t.id != 1; 

explain select * from t_user t where t.id in (1,2,3);

Check according to the code above

  Example 5 ： Composite index （ Follow the leftmost prefix ）

# It works 
explain select * from t_user t where t.username like ' Li %' and t.idcard = '430104200111134321';
# Invalid 
explain select * from t_user t where t.username like ' Li %';

It works