MySQL 性能 相关

 

EXPLAIN Join Types

The type column of explain output describes how table are joined. The following list describes the join types, ordered from the best type to the worst:

system: The table has only one row (=system table）.This is a special case of the const join type.

const:The table has at most one matching row , which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast becaust they are read only once.

      SELECT * form xxx where primary_key = 1;  (直接把SELECT结果当常量）

       只有一个且是定死的（通过index找到一个）

eq_ref: One row is read from this table for each combination of rows from the previous tables.

           在遍历第一个表时，每遍历第一个表的一个元素， 在第二个表（当前表，this table）中只有一行记录

           It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

           什么情况下是这种连接方式呢（join type）？当这个表中的某个索引的所有部分都被使用了，并且这个索引是主键或者唯一索引时。

       只有一个但不是定死的（通过index找到一个）

ref: All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index ( in other words, if the join cannot select a single row based on the key value). if the key that is used matches only a few rows, this is a good join type.

       有多个（通过index找到多个）

 

fulltext：

      The join is performed using a FULLTEXT index.

 

ref_or_null: This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table

     SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge: This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.

 

unique_subquery: This type replaces eq_ref for som IN subqueries of the following form:

        value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery: This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

          value IN (SELECT key_column FROM single_table WHERE some_expr)

 

range: Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

range can be used when a key column is compared to a constant using any of the  =, <>,>,>=,<,<=,IS NULL,<=>,BETWEEN, or IN() operators:

    SELECT * FROM tbl_name WHERE key_column = 10;

    SELECT * FROM tbl_name WHERE key_column  BETWEEN 10 and 20;

 

index: The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

     1)If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column syas Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

2)A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

 

all: A full table scan is done for each combination of rows from the previous tables.

    对于前一个表的每一行记录，都要对当前表做一次全表的扫描。

    This is normally not good if the table is the first table not marked const, and usually very bad in all other cases.

   Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column vlaues from earlier tables;

 

   

 

 

 

 

explain命令 

 

 

 

 

KEY字段：

key列指出优化器选择使用的索引。一般来说SQL查询中的每一个表都仅使用一个索引。也存在索引合并的少数情况，如给定表上用到了两个或者更多索引。

 

POSSIBLE_KEYS:

possible_keys列指出优化器为查询选定的索引。

一个会列出大量可能的索引（例如多于3个）的执行计划意味着备选的索引数量太多了，同时也可能提示存在一个无效的单列索引。

 

rows字段：

 

 

 

type字段：连接操作的类型

type列反应了访问类型。访问类型有很多种， 从全表扫描到索引扫描，范围扫描，唯一索引查询，常数引用等。

1. system

2. const

3. eq_ref

4. ref

5. fulltext

6. ref_or_null

7. index_merge

8. unique_subquery

9. index_subquery

10. range

11. index

12. all （全表扫描）

 

Extra字段

1. “Using Where"表示MySQL将通过where条件来筛选存储引擎返回的记录

2. “Using index”使用索引覆盖扫描来返回记录

 

 

 

 

 

使用profile功能查看SQL语句的执行时间：

show variables like '%pro%';

set profiling=1 ;  // 打开

show profiles;

show profile for query ？;

 

 

 

<if test="key != null and key != ''">
    AND (activity_id like CONCAT('%',#{key},'%') OR name like CONCAT('%',#{key},'%'))
</if>

 

https://mp.weixin.qq.com/s/OUWW9_4Biudi7zYKwNNAhw

 

https://segmentfault.com/a/1190000004690721

讲述key_len:

https://www.cnblogs.com/gomysql/p/4004244.html