索引

创建索引

和创建约束一样，创建索引也有两种方式：

在建表时
在建表后，又有两种方法：ALTER TABLE和CREATE INDEX。

在建表时

隐式的方式

隐式的方式：在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。

CREATE TABLE dept
(
    dept_id   INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(20)
);

显式的方式

语法格式：

CREATE TABLE table_name
[col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL]
[INDEX | KEY]
[index_name] (col_name [(length)])
[ASC | DESC]

解释说明：

UNIQUE、FULLTEXT和SPATIAL，分别表示唯一索引、全文索引和空间索引。
INDEX与KEY为同义词，两者的作用相同，用来指定创建索引。
index_name指定索引的名称，如果不指定，那么MySQL默认col_name为索引名。
col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择。
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC或DESC指定升序或者降序的索引值存储。

创建普通索引

在book表中的year_publication字段上建立普通索引。示例代码：

CREATE TABLE book
(
    book_id          INT,
    book_name        VARCHAR(100),
    authors          VARCHAR(100),
    info             VARCHAR(100),
    comment          VARCHAR(100),
    year_publication YEAR,
    INDEX (year_publication)
);

我们可以SHOW CREATE TABLE，看一下。
示例代码：

1	SHOW CREATE TABLE book;

运行结果：

+-----+----------------------------------------------+
|Table|Create Table                                  |
+-----+----------------------------------------------+
|book |CREATE TABLE `book` (                         |
|     |`book_id` int DEFAULT NULL,                   |
|     |`book_name` varchar(100) DEFAULT NULL,        |
|     |`authors` varchar(100) DEFAULT NULL,          |
|     |`info` varchar(100) DEFAULT NULL,             |
|     |`comment` varchar(100) DEFAULT NULL,          |
|     |`year_publication` year DEFAULT NULL,         |
|     |KEY `year_publication` (`year_publication`)   |
|     |) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3       |
+-----+----------------------------------------------+

索引名，year_publication，印证了我们上文说的，index_name指定索引的名称，如果不指定，那么MySQL默认col_name为索引名。

此外，还可以SHOW INDEX看一下。
示例代码：

1	SHOW INDEX FROM book;

运行结果：

+-----+----------+----------------+------------+----------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|Table|Non_unique|Key_name        |Seq_in_index|Column_name     |Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
+-----+----------+----------------+------------+----------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|book |1         |year_publication|1           |year_publication|A        |0          |NULL    |NULL  |YES |BTREE     |       |             |YES    |NULL      |
+-----+----------+----------------+------------+----------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+

创建唯一索引

除了用添加唯一约束的方式，隐式的创建唯一索引，还有显式的创建唯一索引。

示例代码：

CREATE TABLE test_unique
(
    id   INT         NOT NULL,
    name varchar(30) NOT NULL,
    UNIQUE INDEX uk_idx_id (id)
);

SHOW INDEX 看一下。示例代码：

1	SHOW INDEX FROM test_unique;

运行结果：

+-----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|Table      |Non_unique|Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
+-----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|test_unique|0         |uk_idx_id|1           |id         |A        |0          |NULL    |NULL  |    |BTREE     |       |             |YES    |NULL      |
+-----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+

Non_unique，非唯一性，0代表FALSE，所以是唯一性的。
即：声明有唯一索引的列，其数据不能重复。

创建联合索引

在讨论创建联合索引之前，我们先看看创建单列索引。

CREATE TABLE test_single
(
    id   INT      NOT NULL,
    name CHAR(50) NULL,
    INDEX single_idx_name (name(20))
);

SHOW INDEX，看一下，示例代码：

1	SHOW INDEX FROM test_single

运行结果：

+-----------+----------+---------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|Table      |Non_unique|Key_name       |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
+-----------+----------+---------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|test_single|1         |single_idx_name|1           |name       |A        |0          |20      |NULL  |YES |BTREE     |       |             |YES    |NULL      |
+-----------+----------+---------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+

注意：Seq_in_index，值是1。

创建表test_multi，在表中的id、name和age字段上建立联合索引，示例代码：

CREATE TABLE test_multi
(
    id   INT(11)  NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT(11)  NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx (id, name, age)
);

SHOW INDEX，看一下，示例代码：

1	SHOW INDEX FROM test_multi;

运行结果：

+----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|Table     |Non_unique|Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
+----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
|test_multi|1         |multi_idx|1           |id         |A        |0          |NULL    |NULL  |    |BTREE     |       |             |YES    |NULL      |
|test_multi|1         |multi_idx|2           |name       |A        |0          |NULL    |NULL  |    |BTREE     |       |             |YES    |NULL      |
|test_multi|1         |multi_idx|3           |age        |A        |0          |NULL    |NULL  |    |BTREE     |       |             |YES    |NULL      |
+----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+

现在有了三行；同时我们注意Seq_in_index，依次是id、name和age。
在《4.结构》，我们还提到了联合索引，上述例子的联合索引会先排id，再排name，最后排age。

创建全文索引

全文素引，也称全文检索，利用分词分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。(类似于ElasticSearch的倒排索引，我们在《ElasticSearch实战入门(6.X)：1.工具、概念、集群和倒排索引》有过讨论。)
查询数据量较大的字符串类型的字段时，使用全文素引可以提高查询速度。但，也因为分词的存在，全文索引，不等同于模糊查询LIKE。

InnoDB引擎，从5.7版本的MySQL开始，支持全文索引。

为test_fulltext表的info字段上建立全文索引，示例代码：

CREATE TABLE test_fulltext
(
    id   INT      NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT      NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX futxt_idx_info (info)
)

为papers表的title和content字段，创建全文索引。
示例代码：

CREATE TABLE `papers`
(
    `id`      int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title`   varchar(200) DEFAULT NULL,
    `content` text,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `title` (`title`, `content`)
);

那么，怎么查询呢？
不是LIKE，不是LIKE '%查询条件%';，要用MATCH + AGAINST的方式进行查询，示例代码：

1
2
3

SELECT *
FROM papers
WHERE MATCH(title, content) AGAINST('查询字符串');

创建主键索引

创建主键索引，需要通过主键约束的方式，添加主键索引。

示例代码：

CREATE TABLE test_primary
(
    id           INT(10) UNSIGNED,
    student_no   VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY (id)
);

那么，怎么删除主键索引呢？
示例代码：

1 2	ALTER TABLE test_primary DROP PRIMARY KEY;

不需要索引的名字，因为主键只有一个。

如果需要修改主键索引的话，只能通过先删除，再重新创建的方式。

在建表后

在建表后，再创建索引有两种方法：

ALTER TABLE
CREATE INDEX

ALTER TABLE

语法格式：

ALTER TABLE table_name 
ADD 
[UNIQUE | FULLTEXT | SPATIAL]
[INDEX | KEY]
[index_name] (col_name[(length)],...)
[ASC | DESC]

例如，为book表的comment创建索引，示例代码：

1 2	ALTER TABLE book ADD INDEX inx_cmt (comment);

CREATE INDEX

语法格式：

CREATE
[UNIQUE | FULLTEXT | SPATIAL]
INDEX
index_name 
ON table_name (col_name[length],...)
[ASC | DESC]

例如，为book表的book_name创建索引，示例代码：

1	CREATE UNIQUE INDEX uk_idx_book_name ON book (book_name);

删除索引

删除索引，有两种方法：

ALTER TABLE
DROP INDEX

ALTER TABLE

语法格式：

1	ALTER TABLE table_name DROP INDEX index_name;

例如，有一张表如下：

CREATE TABLE `book`
(
    `book_id`          int NOT NULL AUTO_INCREMENT,
    `book_name`        varchar(100) DEFAULT NULL,
    `authors`          varchar(100) DEFAULT NULL,
    `info`             varchar(100) DEFAULT NULL,
    `comment`          varchar(100) DEFAULT NULL,
    `year_publication` year         DEFAULT NULL,
    PRIMARY KEY (`book_id`),
    UNIQUE KEY `uk_idx_book_name` (`book_name`),
    KEY `year_publication` (`year_publication`),
    KEY `inx_cmt` (`comment`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3

我们删除普通索引inx_cmt，示例代码：

1 2	ALTER TABLE book DROP INDEX inx_cmt;

删除唯一索引uk_idx_book_name，示例代码：

1 2	ALTER TABLE book DROP INDEX uk_idx_book_name;

删除主键索引，不需要索引名称，因为主键只有一个。
示例代码：

1 2	ALTER TABLE book DROP PRIMARY KEY;

运行结果：

1	[42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key

报错了！
因为AUTO_INCREMENT，所作用的字段，要么有主键约束，要么有唯一约束；如果我们把主键约束删掉，AUTO_INCREMENT就无法成立了。如果一定要这么做，可以先删除AUTO_INCREMENT，再删除主键约束。

示例代码：

1 2	ALTER TABLE book MODIFY book_id int not null;

1 2	ALTER TABLE book DROP PRIMARY KEY;

DROP INDEX

语法格式：

1	DROP INDEX index_name ON table_name;

8版本中的新特性

在8版本中，索引有两大新的特性：

降序索引
隐藏索引

降序索引

降序索引以降序存储键值。8版本开始支持降序索引(仅限于InnoDB存储引擎)

例如，创建表ts1和联合索引idx_a_b，其中a升序，b降序。

CREATE TABLE ts1
(
    a int,
    b int,
    index idx_a_b (a ASC, b DESC)
);

在8版本中，查看表ts1的表结构，示例代码：
示例代码：

1	SHOW CREATE TABLE ts1;

运行结果：

+-----+------------------------------------------+
|Table|Create Table                              |
+-----+------------------------------------------+
|ts1  |CREATE TABLE `ts1` (                      |
|     |`a` int DEFAULT NULL,                     |
|     |`b` int DEFAULT NULL,                     |
|     |KEY `idx_a_b` (`a`,`b` DESC)              |
|     |) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3   |
+-----+------------------------------------------+

在5版本中，如下：

+-----+------------------------------------------+
|Table|Create Table                              |
+-----+------------------------------------------+
|ts1  |CREATE TABLE `ts1` (                      |
|     | `a` int(11) DEFAULT NULL,                |
|     | `b` int(11) DEFAULT NULL,                |
|     | KEY `idx_a_b` (`a`,`b`)                  |
|     |) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4   |
+-----+------------------------------------------+

虽然指定了b为降序，但是在5版本中，b依然是升序。

然后，我们还可以看看ORDER BY的执行计划，示例代码：

1	EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

运行结果：

+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+-----------+
|id|select_type|table|partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra      |
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+-----------+
|1 |SIMPLE     |ts1  |NULL      |index|NULL         |idx_a_b|10     |NULL|5   |100     |Using index|
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+-----------+

在5版本中，如下：

+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+----------------------------+
|id|select_type|table|partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra                       |
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+----------------------------+
|1 |SIMPLE     |ts1  |NULL      |index|NULL         |idx_a_b|10     |NULL|800 |100     |Using index; Using filesort | 
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+----------------------------+

在5版本中，执行计划中扫描数为800，用到了Using filesort，这是MySQL中一种速度比较慢的外部排序。
在8版本中，执行计划中扫描数为5，没有使用Using filesort。

隐藏索引

简介

从8版本开始，对于计划删除的索引，我们可以先将其隐藏，隐藏之后，查询优化器不再使用这个索引，如确认无影响再删除。
但，需要注意的是，对于被隐藏的索引，仍然和正常索引一样实时更新的，所以同样会影响插入、更新和删除的性能。
主键不能被设置为隐藏索引。

创建表同时，创建

我们可以在创建表时，通过关键字INVISIBLE，创建隐藏索引，

示例代码：

CREATE TABLE test_invisible
(
    id   INT         NOT NULL,
    name varchar(30) NOT NULL,
    INDEX (name) INVISIBLE
);

1	EXPLAIN SELECT * FROM test_invisible WHERE name = 'xxx';

运行结果：

+--+-----------+--------------+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table         |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
+--+-----------+--------------+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE     |test_invisible|NULL      |ALL |NULL         |NULL|NULL   |NULL|1   |100     |Using where|
+--+-----------+--------------+----------+----+-------------+----+-------+----+----+--------+-----------+

注意，possible_keys是NULL，并没有利用索引。

创建表之后，创建

ALTER TABLE

1 2	ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;

CREATE INDEX

1	CREATE INDEX indexname ON tablename (propname[(length)]) INVISIBLE;

切换索引可见状态

切换成隐藏索引，示例代码：

1 2	ALTER TABLE tablename ALTER INDEX index_name INVISIBLE;

切换成非隐藏索引，示例代码：

1 2	ALTER TABLE tablename ALTER INDEX index_name VISIBLE;

示例代码：

1 2	ALTER TABLE test_invisible ALTER INDEX name VISIBLE;

1	EXPLAIN SELECT * FROM test_invisible WHERE name = 'xxx';

运行结果：

+--+-----------+--------------+----------+----+-------------+----+-------+-----+----+--------+-----+
|id|select_type|table         |partitions|type|possible_keys|key |key_len|ref  |rows|filtered|Extra|
+--+-----------+--------------+----------+----+-------------+----+-------+-----+----+--------+-----+
|1 |SIMPLE     |test_invisible|NULL      |ref |name         |name|92     |const|1   |100     |NULL |
+--+-----------+--------------+----------+----+-------------+----+-------+-----+----+--------+-----+

possible_keys是name，key也是name，利用了索引name。

优化案例

创建索引的案例

WHERE条件的字段

如果某个字段在WHERE条件(包括SELECT、UPDATE和DELETE)中经常被使用到，那么可以考虑给这个字段创建索引。

ORDER BY和GROUP BY的字段

ORDER BY和GROUP BY的字段适合创建索引，这个也很好理解，和WHERE一样，都是为了起到定位作用。

DISTINCT字段

这个也很好理解，毕竟直接从索引去重，一定会比全表扫描去重更快。

我们还可以看这么一个现象。

如果student_id字段上没有索引，示例代码：

1 2	SELECT DISTINCT(student_id) FROM student;

运行结果：

+----------+
|student_id|
+----------+
|48208     |
|89247     |
|183502    |
|105262    |
|95296     |

【部分运行结果略】

如果student_id字段上有索引，示例代码：

1 2	SELECT DISTINCT(student_id) FROM student;

运行结果：

+----------+
|student_id|
+----------+
|1         |
|2         |
|3         |
|4         |
|5         |

【部分运行结果略】

居然还排序了，这是因为这时候我们利用了索引，而索引就是有序的。
具体，我们可以通过执行计划印证一下，示例代码：

1
2
3

EXPLAIN
SELECT DISTINCT(student_id)
FROM student;

运行结果：

+--+-----------+-------+----------+-----+-------------+----------+-------+----+------+--------+------------------------+
|id|select_type|table  |partitions|type |possible_keys|key       |key_len|ref |rows  |filtered|Extra                   |
+--+-----------+-------+----------+-----+-------------+----------+-------+----+------+--------+------------------------+
|1 |SIMPLE     |student|NULL      |range|student_id   |student_id|4      |NULL|198774|100     |Using index for group-by|
+--+-----------+-------+----------+-----+-------------+----------+-------+----+------+--------+------------------------+

JOIN连接的字段

对于JOIN连接的字段，需要建立索引，而且该字段在多张表中的数据类型必须一致。

没创建索引的话，我们需要全表扫描，具体看执行计划，示例代码：

EXPLAIN SELECT *
FROM student
         JOIN course ON student.course_id = course.course_id
WHERE student.name = 'EuiFEg';

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+------------------------------------------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows   |filtered|Extra                                     |
+--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+------------------------------------------+
|1 |SIMPLE     |course |NULL      |ALL |NULL         |NULL|NULL   |NULL|100    |100     |NULL                                      |
|1 |SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|5953512|1       |Using where; Using join buffer (hash join)|
+--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+------------------------------------------+

解释说明：

两张表都需要全表扫描。
course作为了驱动表，而student作为了被驱动表。

如果我们在其中一张表，student表的course_id字段创建索引的话，示例代码：

1	CREATE INDEX inx_course_id ON student (course_id);

EXPLAIN
SELECT *
FROM student
         JOIN course ON student.course_id = course.course_id
WHERE student.name = 'EuiFEg';

运行结果：

+--+-----------+-------+----------+----+-------------+-------------+-------+------------------+-----+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key          |key_len|ref               |rows |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+-------------+-------+------------------+-----+--------+-----------+
|1 |SIMPLE     |course |NULL      |ALL |NULL         |NULL         |NULL   |NULL              |100  |100     |NULL       |
|1 |SIMPLE     |student|NULL      |ref |inx_course_id|inx_course_id|4      |i.course.course_id|58367|10      |Using where|
+--+-----------+-------+----------+----+-------------+-------------+-------+------------------+-----+--------+-----------+

解释说明：student利用了inx_course_id。

如果再给student表的course_id字段创建索引的话，这时候两张表都有索引，示例代码：

EXPLAIN
SELECT *
FROM student
         JOIN course ON student.course_id = course.course_id
WHERE student.name = 'EuiFEg';

运行结果：

+--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key          |key_len|ref                |rows   |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |inx_course_id|NULL         |NULL   |NULL               |5953512|10      |Using where|
|1 |SIMPLE     |course |NULL      |ref |inx_course_id|inx_course_id|4      |i.student.course_id|1      |100     |NULL       |
+--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----------+

为什么这时候student表反而没有利用索引？
因为对于student表，还有一个条件student.name = 'EuiFEg'，查询优化器在权衡之后，认为还是不要利用索引，直接全表扫描。
如果我们把where条件去除的话，示例代码：

EXPLAIN
SELECT *
FROM student
         JOIN course ON student.course_id = course.course_id

运行结果：

+--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----+
|id|select_type|table  |partitions|type|possible_keys|key          |key_len|ref                |rows   |filtered|Extra|
+--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----+
|1 |SIMPLE     |student|NULL      |ALL |inx_course_id|NULL         |NULL   |NULL               |5953512|100     |NULL |
|1 |SIMPLE     |course |NULL      |ref |inx_course_id|inx_course_id|4      |i.student.course_id|1      |100     |NULL |
+--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----+

区分度较高的字段

比如，性别，就不适合创建索引，因为区分度较低，效果有限。

关于如何衡量区分度等，我们会在下文讨论"对于较长的字段建立前缀索引"，进行讨论。

占用字节较小的字段

占用字节较小的字段更适合创建索引。
在《4.结构》，我们讨论了索引的结构，根据这个，我们就很容易理解为什么占用字节较小的字段更适合创建索引。
因为数据类型越小，索引占用的空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘的IO，提高效率。

这也是为什么主键占用的空间小一些好，也是为什么TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不允许作为主键，而且主键，不仅仅存在于聚簇索引中，在二级索引中也会有。

对于较长的字段建立前缀索引

那么，如果一个字段确实较长，而且确实需要创建索引，怎么办呢？

我们可以通过截取宇段的前面一部分内容建立索引，这个就叫前缀索引。
这样在查找记录时里然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。
通过这种方法，试图在时间和空间上寻找一个平衡。

那么，问题来了，截取多少呢？截取多了，达不到节省索引存储空间的目的；截取少了，重复内容太多，字段的散列度会降低，索引效果有限。

假设，存在一张表如下：

create table shop
(
    address varchar(120) not null
);

我们可以通过count(distinct left(列名, 索引长度))/count(*)，来统计其区分度，示例代码：

select count(distinct left(address, 10)) / count(*) as sub10,
       count(distinct left(address, 15)) / count(*) as sub11,
       count(distinct left(address, 20)) / count(*) as sub12,
       count(distinct left(address, 25)) / count(*) as sub13
from shop;

区分度的值，越大越好，最大为 $1$ 。
一般超过 $33\%$ ，就算是比较高效的索引了。

在根据区分度，确定索引的长度后，我们就可以建立前缀索引了，示例代码：

1	alter table shop add index(address(20));

有些资料会说，前缀索引，会影响排序的准确性。
这个是不对的，前缀索引，不会影响排序的准确性，但会影响排序的效率。

我们可以看个例子。

首先，创建表，示例代码：

CREATE TABLE `test_prefix`
(
    `c1` int        DEFAULT NULL,
    `c2` varchar(5) DEFAULT NULL,
    KEY `c2_1` (`c2`(1))
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci

然后插入数据，示例代码：

INSERT INTO i.test_prefix (c1, c2) VALUES (1, 'a0001');
INSERT INTO i.test_prefix (c1, c2) VALUES (2, 'a0002');
INSERT INTO i.test_prefix (c1, c2) VALUES (3, 'a0003');
INSERT INTO i.test_prefix (c1, c2) VALUES (4, 'a0004');
INSERT INTO i.test_prefix (c1, c2) VALUES (5, 'a0005');
INSERT INTO i.test_prefix (c1, c2) VALUES (6, 'a0000');

创建索引，示例代码：

1 2	ALTER TABLE test_prefix ADD INDEX c2_1 (c2(1));

查看排序，示例代码：

1
2
3

SELECT *
FROM test_prefix
ORDER BY c2;

运行结果：

+--+-----+
|c1|c2   |
+--+-----+
|6 |a0000|
|1 |a0001|
|2 |a0002|
|3 |a0003|
|4 |a0004|
|5 |a0005|
+--+-----+

我们看到，排序的准确性，并没有受到影响。

查看执行计划，示例代码：

EXPLAIN
SELECT *
FROM test_prefix
ORDER BY c2;

运行结果：

+--+-----------+-----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table      |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
+--+-----------+-----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE     |test_prefix|NULL      |ALL |NULL         |NULL|NULL   |NULL|6   |100     |Using filesort|
+--+-----------+-----------+----------+----+-------------+----+-------+----+----+--------+--------------+

解释说明：用到了Using filesort，排序的效率会受到影响。

再举一个例子，现在有一个市的公民信息系统，注意是一个市的，根据我国的身份证编码规则，这些身份证的前六位绝大部分是一样的。
这时候，我们可能需要创建长度为12以上的前缀索引，才能够满足区分度要求。
换一个思路，还是利用前缀索引，我们在存储身份证号的时候，把身份证号前后颠倒，倒序存储。
查询的时候，用类似如下的SQL进行查询，示例代码：

1
2
3

select field_list
from t
where id_card = reverse('【输入的身份证号】');

这样效率就能高很多了。

联合索引的创建

联合索引，是为多个字段建立索引，那么这时候先后顺序就有讲究了。
大致是：

区分度高的列放前面
使用频繁的列放前面
联合索引优于单值索引
GROUP BY的列放在ORDER BY的列的前面

区分度高的列放前面

例如，假如在student表中，student_id比course_id的区分度更高的话，那么针对如下的SQL

SELECT *
FROM student
WHERE student_id = 10013
  AND course_id = 100;

建立联合索引的时候，应该student_id在course_id的前面。

那么，我们WHERE条件需要区分度高的写前面吗？
不用，优化器会优化。

使用频繁的列放前面

但是，对于如下的SQL

1
2
3

SELECT *
FROM student
WHERE course_id = 100;

不会利用student_id在course_id的前面的联合索引，我们可以看看执行计划，示例代码：

1
2
3

EXPLAIN SELECT *
FROM student
WHERE course_id = 100;

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows   |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|5953512|0.98    |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+-----------+

所以，经常根据course_id = 100查询的话，可以考虑将course_id放在前面，或者为course_id，单独建立索引。

联合索引优于单值索引

例如，我们为student表的student_id和course_id分别建立索引，然后同时根据student_id和course_id进行查询，看执行计划，示例代码：

EXPLAIN SELECT *
FROM student
WHERE student_id = 10013
  AND course_id = 100;

运行结果：

+--+-----------+-------+----------+----+----------------------------+-------------+-------+-----+----+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys               |key          |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-------+----------+----+----------------------------+-------------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |student|NULL      |ref |inx_course_id,inx_student_id|inx_course_id|4      |const|1   |5       |Using where|
+--+-----------+-------+----------+----+----------------------------+-------------+-------+-----+----+--------+-----------+

解释说明：possible_keys是inx_course_id,inx_student_id，最后使用的key是inx_course_id，而且Extra有Using where。

但，如果我们建立联合索引的话，效率会更高，示例代码：

EXPLAIN SELECT *
FROM student
WHERE student_id = 10013
  AND course_id = 100;

运行结果：

+--+-----------+-------+----------+----+------------------------+------------------------+-------+-----------+----+--------+-----+
|id|select_type|table  |partitions|type|possible_keys           |key                     |key_len|ref        |rows|filtered|Extra|
+--+-----------+-------+----------+----+------------------------+------------------------+-------+-----------+----+--------+-----+
|1 |SIMPLE     |student|NULL      |ref |inx_student_id_course_id|inx_student_id_course_id|8      |const,const|1   |100     |NULL |
+--+-----------+-------+----------+----+------------------------+------------------------+-------+-----------+----+--------+-----+

`GROUP BY`的列放在`ORDER BY`的列的前面

GROUP BY的列放在ORDER BY的列的前面，因为GROUP BY会比ORDER BY先执行。

不创建索引的案例

为什么不创建索引

因为索引的数目不是越多越好，我们需要限制每张表上的索引数量，建议单张表索引数量不超过 $6$ 个。

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
素引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新。
优化器在选择如何优化查询时，会根据统一信息，对每—个可以用到的索引来进行评估，以生成出一个最好的计划。如果有多个索引可以查询，会增加优化器生成执行计划的时间，降低查询性能。

起不到定位作用的字段

例如，WHERE、GROUP BY和ORDER BY用不到的字段。

注意！并不是说这些字段一定不创建索引，在下文我们会讨论覆盖索引，有些字段虽然起不到定位作用，创建索引可以少一次回表的操作。

有大量重复数据的列

因为有大量重复数据的列，区分度不高，索引的效果有限。

避免对频繁更新的字段创建索引

因为在对数据进行更新的时候，还需要对索引进行更新。如果某个字段会频繁更新，会影响效率。

不建议用无序的值作为索引

例如，UUID、MD5、HASH、无序长字符串等，这些索引在插入时可能会频繁的进行页分裂。

不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。这时候应该将它们删除，从而减少索引对更新操作的影响。

不要创建冗余索引

冗余索引，比如，index(a,b,c)，就相当于index(a)、index(a,b)和index(a,b,c)。

再举一个例子，假设存在一张表，如下：

CREATE TABLE person_info
(
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name         VARCHAR(100) NOT NULL,
    birthday     DATE         NOT NULL,
    phone_number CHAR(11)     NOT NULL,
    country      varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10))
);

我们通过idx_name_birthday_phone_number索引就可以对name列进行快速搜索，再创建一个专门针对name列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处，甚至会因为优化器的选择最佳方式，影响效率。

不要创建重复索引

假设存在一张表如下：

CREATE TABLE repeat_index_demo
(
    col1 INT PRIMARY KEY,
    col2 INT,
    UNIQUE uk_idx_c1 (col1),
    INDEX idx_c1 (col1)
);

col1既是主键、又定义了一个唯一索引，还定义了一个普通索引，定义的唯一索引和普通索引是重复的，这种情况要避免。

数据量小的表

因为数据量小的表，创建索引的意义不大。

索引失效案例

最左前缀法则

对于联合索引，在检索数据时，会从最左边开始匹配，如果最左边的用不上，其他的也不会用到。

假设，我们创建了一个联合索引idx_key_part(key_part1,key_part2,key_part3)，然后我们根据key_part2和key_part3进行查找，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key_part2 = 'Oefqm'
  and key_part3 = 'nFloOaMJjS'

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|1       |Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+

解释说明：没有利用索引，type为ALL，说明进行了全表扫描。

函数和计算

假设student表的NAME字段类型是VARCHAR，且有索引。

示例代码：

EXPLAIN
SELECT *
FROM student
WHERE student.name LIKE 'abc%';

运行结果：

+--+-----------+-------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|id|select_type|table  |partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                |
+--+-----------+-------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |student|NULL      |range|idx_name     |idx_name|63     |NULL|29  |100     |Using index condition|
+--+-----------+-------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+

这个没问题。

但，假如我们在字段上，添加函数呢？示例代码：

EXPLAIN
SELECT *
FROM student
WHERE LEFT(student.name, 3) = 'abc';

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|453390|100     |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

解释说明：没有利用索引，type为ALL，说明进行了全表扫描。

这种也不可以，示例代码：

1	EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|453390|100     |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

假设student表的stuno字段类型是int，且有索引。

对列进行了计算，示例代码：

EXPLAIN
SELECT id, stuno, NAME
FROM student
WHERE stuno + 1 = 900001;

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|453390|100     |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

解释说明：没有利用索引，type为ALL，说明进行了全表扫描。

类型转换

假设student表的NAME字段类型是VARCHAR，且有索引。

示例代码：

EXPLAIN
SELECT *
FROM student
WHERE name = 123;

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |idx_name     |NULL|NULL   |NULL|453390|10      |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

解释说明：没有利用索引，type为ALL，说明进行了全表扫描。

但，如果我们对查询条件加上单引号呢？示例代码：

EXPLAIN
SELECT *
FROM student
WHERE name = '123';

运行结果：

+--+-----------+-------+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table  |partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-------+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |student|NULL      |ref |idx_name     |idx_name|63     |const|1   |100     |NULL |
+--+-----------+-------+----------+----+-------------+--------+-------+-----+----+--------+-----+

解释说明：这样就利用了索引。

因为name=123发生了类型转换，索引失效。
不过，比较神奇的是，如果字段的类型是数字，我们的查询条件加上单引号，即用字符串去查询，不会导致索引失效。

加上单引号去查询，示例代码：

EXPLAIN
SELECT *
FROM student
WHERE stuno = '100009';

运行结果：

+--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table  |partitions|type|possible_keys|key    |key_len|ref  |rows|filtered|Extra|
+--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+-----+
|1 |SIMPLE     |student|NULL      |ref |idx_sno      |idx_sno|4      |const|1   |100     |NULL |
+--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+-----+

范围条件的右边的列

示例代码：

1	CREATE INDEX idx_age_name_classid ON student(age,classid,name);

假设，我们创建了一个联合索引idx_age_name_classid(age,classid,name)，进行查找，示例代码：

EXPLAIN
SELECT  *
FROM student
WHERE student.age = 30
  AND student.classId > 20
  AND student.name = 'abc';

运行结果：

+--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+
|id|select_type|table  |partitions|type |possible_keys       |key                 |key_len|ref |rows |filtered|Extra                |
+--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+
|1 |SIMPLE     |student|NULL      |range|idx_age_name_classid|idx_age_name_classid|10     |NULL|18200|10      |Using index condition|
+--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+

注意，key_len的值是10，即联合索引的name没用上。

如果我们将范围查询条件放置语句最后，示例代码：

EXPLAIN
SELECT  *
FROM student
WHERE student.age = 30
  AND student.name = 'abc'
  AND student.classId > 20;

运行结果：

+--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+
|id|select_type|table  |partitions|type |possible_keys       |key                 |key_len|ref |rows |filtered|Extra                |
+--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+
|1 |SIMPLE     |student|NULL      |range|idx_age_name_classid|idx_age_name_classid|10     |NULL|18200|10      |Using index condition|
+--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+

key_len的值还是10。

那么怎么办呢？
修改顺序，范围字段放最后。
示例代码：

1	CREATE INDEX idx_age_name_classid ON student (age, name, classid);

不等于

不等于，包括!=和<>，会导致索引失效。
这个很好理解，等于的话，还可以遍历索引，不等于只能全表扫描了。

示例代码：

EXPLAIN
SELECT  *
FROM student
WHERE name != 'abc';

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |idx_name     |NULL|NULL   |NULL|453390|50.16   |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

IS NOT NULL

这个和"不等于"的原因一样。
IS NULL，还可以遍历索引。IS NOT NULL，只能全表扫描了。

示例代码：

EXPLAIN
SELECT *
FROM student
WHERE age IS NOT NULL;

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |idx_age      |NULL|NULL   |NULL|453390|50      |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

示例代码：

EXPLAIN
SELECT *
FROM student
WHERE age IS NULL;

运行结果：

+--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+---------------------+
|id|select_type|table  |partitions|type|possible_keys|key    |key_len|ref  |rows|filtered|Extra                |
+--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |student|NULL      |ref |idx_age      |idx_age|5      |const|1   |100     |Using index condition|
+--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+---------------------+

OR条件中存在非索引的列

这个很好理解，都已经"OR"非索引的列了，反正是要全表扫描，也不折腾用索引了。示例代码：

EXPLAIN
SELECT *
FROM student
WHERE age = 10
   OR classid = 100;

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |idx_age      |NULL|NULL   |NULL|453390|11.88   |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

但是AND不会，示例代码：

EXPLAIN
SELECT *
FROM student
WHERE age = 10
   AND classid = 100;

运行结果：

+--+-----------+-------+----------+----+-------------+-------+-------+-----+-----+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key    |key_len|ref  |rows |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+-------+-------+-----+-----+--------+-----------+
|1 |SIMPLE     |student|NULL      |ref |idx_age      |idx_age|5      |const|10007|10      |Using where|
+--+-----------+-------+----------+----+-------------+-------+-------+-----+-----+--------+-----------+

如果OR的条件，两边都有索引呢？

示例代码：

EXPLAIN
SELECT *
FROM student
WHERE age = 10
   OR name = 'Abel';

运行结果：

+--+-----------+-------+----------+-----------+----------------+----------------+-------+----+-----+--------+------------------------------------------+
|id|select_type|table  |partitions|type       |possible_keys   |key             |key_len|ref |rows |filtered|Extra                                     |
+--+-----------+-------+----------+-----------+----------------+----------------+-------+----+-----+--------+------------------------------------------+
|1 |SIMPLE     |student|NULL      |index_merge|idx_name,idx_age|idx_age,idx_name|5,63   |NULL|10008|100     |Using union(idx_age,idx_name); Using where|
+--+-----------+-------+----------+-----------+----------------+----------------+-------+----+-----+--------+------------------------------------------+

解释说明：使用索引合并的方式来执行，即可以对索引到的内容进行合并。

like以通配符%开头

示例代码：

EXPLAIN
SELECT *
FROM student
WHERE name like '%ab';

运行结果：

+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|453390|11.11   |Using where|
+--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+

分析工具

查看系统性能

通过SHOW STATUS可以查询MySQL数据库服务器的性能参数、执行频率等。
语法格式：

1
2
3

SHOW
[GLOBAL|SESSION]
STATUS LIKE '【参数】';

常用参数有：

last_query_cost：最近一次查询的成本。
Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Slow_queries：慢查询的次数。
Innodb_rows_read：Select查询返回的行数(Innodb引擎)。
Innodb_rows_inserted：执行INSERT操作插入的行数(Innodb引擎)。
Innodb_rows_updated：执行UPDATE操作更新的行数(Innodb引擎)。
Innodb_rows_deleted：执行DELETE操作删除的行数(Innodb引擎)。
Com_select：查询操作的次数。
Com_insert：插入操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。

例如，我们查询last_query_cost，最近一次查询的成本。
示例代码：

1
2
3

SELECT *
FROM student
WHERE id = 135940;

1	SHOW STATUS LIKE 'last_query_cost';

运行结果：

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

解释说明：Value是所需要读取的页的数量。

示例代码：

1
2
3

SELECT *
FROM student
WHERE id BETWEEN 0 AND 100000;

1	SHOW STATUS LIKE 'last_query_cost';

运行结果：

+-----------------+--------------+
| Variable_name   | Value        |
+-----------------+--------------+
| Last_query_cost | 41344.253201 |
+-----------------+--------------+

开启慢查询日志

作用

我们可以通过慢查询日志，定位慢SQL。

参数

slow_query_log

首先，我们我通过slow_query_log，查看慢查询日志是否已经开启，示例代码：

1	SHOW VARIABLES LIKE '%slow_query_log'

运行结果：

+--------------+-----+
|Variable_name |Value|
+--------------+-----+
|slow_query_log|ON   |
+--------------+-----+

还可以查看慢查询日志的路径，示例代码：

1	SHOW VARIABLES LIKE '%slow_query_log%'

运行结果：

+-------------------+-------------------------+
|Variable_name      |Value                    |
+-------------------+-------------------------+
|slow_query_log     |OFF                      |
|slow_query_log_file|/var/lib/mysql/8-slow.log|
+-------------------+-------------------------+

注意，这次查询的是'%slow_query_log%'，比上一次多了一个百分号。

开启慢查询日志的方法如下：

1	SET GLOBAL slow_query_log = 'ON';

注意，如果不加GLOBAL会报错，因为必须是一个全局的变量。

示例代码：

1	SET slow_query_log = 'ON';

运行结果：

1	Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

long_query_time

慢查询，即响应时间超过了某个值的查询。而这某个值，就是由long_query_time定义，默认是10。

查看long_query_time，示例代码：

1	show variables like '%long_query_time%';

运行结果：

+---------------+---------+
|Variable_name  |Value    |
+---------------+---------+
|long_query_time|10.000000|
+---------------+---------+

需要注意的是，long_query_time，既是GLOBAL级别的，又是SESSION级别的。所以，如果需要修改的话，建议GLOBAL和SESSION都改。
因为，设置GLOBAL的方式对当前SESSION的long_query_time无效，只对新连接的客户端有效。

修改long_query_time，示例代码：

1	SET GLOBAL long_query_time = 1;

1	SET long_query_time = 1;

min_examined_row_limit

min_examined_row_limit，这个参数一般不用，含义是，查询扫描过的最少记录数，默认为0。

如果某个查询扫描过的记录数大于min_examined_row_limit 且时间大于long_query_time，才会就被记录到慢查询日志中。

示例代码：

1	SHOW VARIABLES LIKE '%min_examined_row_limit%'

运行结果：

+----------------------+-----+
|Variable_name         |Value|
+----------------------+-----+
|min_examined_row_limit|0    |
+----------------------+-----+

持久化

需要注意的是，上述都是临时的，MySQL重启就失效了，如果想持久化的，可以修改配置文件。
修改my.cnf，在[mysqld]下新增long-query_time、slow-query-log和slow_query_1og_file，然后重启MySQL服务器。
例如：

[mysqld]
slow_query_log=0N
slow_query_log_file=/var/lib/mysql/kaka-slow.log
long_query_time=3
log_output=FILE

查看慢查询数目

查询当前系统中有多少条慢查询记录，示例代码：

1	SHOW GLOBAL STATUS LIKE '%Slow_queries%';

运行结果：

+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|Slow_queries |0    |
+-------------+-----+

删除慢查询日志

我们可以通过命令mysqladmin flush-logs来重新生成查询日志文件，以这种方式删除慢查询日志。
示例代码：

1	mysaladmin -uroot -p flush-logs slow

必须要有slow参数！否则很多日志都会被重置！

有些资料说通过rm命令即可，我担心rm之后，文件不会被恢复。

分析慢查询日志

慢查询日志分析工具是mysqldumpslow。
注意，这个不是在SQL中执行，而是在MySQL所在的服务器上执行。

查看帮助

查看mysqldumpslow的帮助信息。

示例代码：

1	mysqldumpslow --help

运行结果：

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

部分参数解释：

s，按照何种方式排序：
- c，访问次数
- l，锁定时间
- r，返回记录
- t，查询时间
- al，平均锁定时间
- ar，平均返回记录数
- at，平均查询时间(默认方式)
- ac，平均查询次数
t，返回前面多少条的数据
a，不将抽象数字和字符串(数字会被抽象成N，字符串会呗抽象成S)。
g，后边搭配一个正则匹配模式

例如，我们想要按照查询时间排序，不要抽象数字和字符串，查看前五条语句，示例代码：

1	mysqldumpslow -a -s t -t 5 /var/lib/mysql/8-slow.log

运行结果：

Reading mysql slow query log from /var/lib/mysql/8-slow.log
Count: 1  Time=438.85s (438s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[101.85.38.14]
  /* ApplicationName=DataGrip 2021.2.2 */ CALL insert_stu(5000000)

Count: 1  Time=34.32s (34s)  Lock=0.00s (0s)  Rows=5905622.0 (5905622), root[root]@[101.85.38.14]
  /* ApplicationName=DataGrip 2021.2.2 */ SELECT * FROM student where student_id > 3141

Count: 1  Time=1.74s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[101.85.38.14]
  /* ApplicationName=DataGrip 2021.2.2 */ SELECT COUNT(*) FROM student

Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.

常用参考

返回记录集最多的10个SQL

1	mysqldumpslow -s r -t 10 /var/lib/mysql/kaka-slow.log

访问次数最多的10个SQL

1	mysqldumpslow -s c -t 10 /var/lib/mysql/kaka-slow.log

按照时间排序的前10条里面含有左连接的查询语句

1	mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/kaka-slow.log

另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况

1	mysqldumpslow -s r -t 10 /var/lib/mysql/kaka-slow.log \| more

关于|和more这两个命令，我们在《Linux操作系统使用入门：2.命令》有过讨论。

sys schema

在MySQL5.7.7版本中新增sys schema，将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为"视图"

sys schema视图摘要

主机相关：以host_summary开头，主要汇总了IO延迟的信息。
Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
I/O相关：以io开头，汇总了等待I/O、I/O使用量情况。
内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
连接与会话信息：processlist和session相关视图，总结了会话相关信息。
表相关：以schema_table开头的视图，展示了表的统计信息。
索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
等待事件相关信息：以wait开头，展示等待事件的延迟情况。

索引相关

查询冗余索引

1 2	SELECT * FROM sys.schema_redundant_indexes;

查询未使用过的索引

1 2	SELECT * FROM sys.schema_unused_indexes;

查询索引的使用情况

示例代码：

1
2
3

SELECT index_name, rows_selected, rows_inserted, rows_updated, rows_deleted
FROM sys.schema_index_statistics
WHERE table_schema = 'e';

运行结果：

+------------+-------------+-------------+------------+------------+
|index_name  |rows_selected|rows_inserted|rows_updated|rows_deleted|
+------------+-------------+-------------+------------+------------+
|PRIMARY     |400000       |0            |0           |0           |
|idx_key2    |200000       |0            |0           |0           |
|idx_key1    |501          |0            |0           |0           |
|idx_key_part|3            |0            |0           |0           |
|PRIMARY     |0            |0            |0           |0           |
|idx_key2    |0            |0            |0           |0           |
|idx_key3    |0            |0            |0           |0           |
|idx_key1    |0            |0            |0           |0           |
|idx_key3    |0            |0            |0           |0           |
|idx_key_part|0            |0            |0           |0           |
|PRIMARY     |0            |0            |0           |0           |
+------------+-------------+-------------+------------+------------+

表相关

查询表的访问量

select table_schema, table_name, sum(io_read_requests + io_write_requests) as io
from sys.schema_table_statistics
group by table_schema, table_name
order by io desc;

查询占用bufferpool较多的表

select object_schema, object_name, allocated, data
from sys.innodb_buffer_stats_by_table
order by allocated
limit 10;

查看表的全表扫描情况
示例代码：

1
2
3

select *
from sys.statements_with_full_table_scans
where db = 'e';

运行结果：

+-----------------------------------------------------------------+--+----------+-------------+-------------------+------------------------+-----------------+---------+-------------+-------------+-----------------+--------------------------+--------------------------+----------------------------------------------------------------+
|query                                                            |db|exec_count|total_latency|no_index_used_count|no_good_index_used_count|no_index_used_pct|rows_sent|rows_examined|rows_sent_avg|rows_examined_avg|first_seen                |last_seen                 |digest                                                          |
+-----------------------------------------------------------------+--+----------+-------------+-------------------+------------------------+-----------------+---------+-------------+-------------+-----------------+--------------------------+--------------------------+----------------------------------------------------------------+
|SELECT `t` . * FROM `e` . `s2` `t` LIMIT ?                       |e |1         |822.38 us    |1                  |0                       |100              |501      |501          |501          |501              |2022-05-29 18:56:11.929747|2022-05-29 18:56:11.929747|7acb424044b7b92baa6541faa1d661ab3f8e4f08e81edc691cf21ea20d7c8d9a|
|SELECT `object_schema` , `obje ... ` ORDER BY `allocated` LIMIT ?|e |1         |76.39 ms     |1                  |0                       |100              |10       |47           |10           |47               |2022-05-29 21:03:21.764080|2022-05-29 21:03:21.764080|ec43cf83a2f6859edcbb99d1ee15b3f501b042d62acc557ce3abefd8b12cee1b|
|EXPLAIN SELECT * FROM `s1` WHE ... s1` . `key1` = `s2` . `key1` )|e |2         |759.21 us    |2                  |0                       |100              |4        |0            |2            |0                |2022-05-29 19:33:04.425925|2022-05-29 19:34:23.606606|1ec9636e3dc7e6bfaceee973d7a5eadf94d92cd641dbbfb59c8a5f58168b9386|

【部分运行结果略】

|SELECT `t` . * FROM `e` . `tt` `t` LIMIT ?                       |e |1         |1.70 ms      |1                  |0                       |100              |0        |0            |0            |0                |2022-05-29 18:16:25.361247|2022-05-29 18:16:25.361247|1f570769802623cb2a0cd5a912fd6a7cb9eb44a20790ed96d11c9ed0731cf242|
|EXPLAIN SELECT * FROM `s1` WHE ... s2` WHERE `common_field` = ? )|e |3         |1.61 ms      |3                  |0                       |100              |6        |0            |2            |0                |2022-05-29 17:37:59.473037|2022-05-29 17:42:08.484238|4a0e4cbec5060902fed3302a24ec328a836660c805eb945b85b53ac6aa89da09|
|SELECT `index_name` , `rows_se ... tics` WHERE `table_schema` = ?|e |1         |1.32 ms      |1                  |0                       |100              |11       |176          |11           |176              |2022-05-29 21:02:03.107655|2022-05-29 21:02:03.107655|b3ede7d1b1eb9a8b622a9ba648595f201b0bdf9ed0455e908369a644753c6aa4|
+-----------------------------------------------------------------+--+----------+-------------+-------------------+------------------------+-----------------+---------+-------------+-------------+-----------------+--------------------------+--------------------------+----------------------------------------------------------------+

语句相关

监控SQL执行的频率

示例代码：

1
2
3

select db, exec_count, query
from sys.statement_analysis
order by exec_count desc;

运行结果：

+---------+----------+-----------------------------------------------------------------+
|db       |exec_count|query                                                            |
+---------+----------+-----------------------------------------------------------------+
|NULL     |27174     |SHOW WARNINGS                                                    |
|NULL     |13932     |SET `net_write_timeout` = ?                                      |
|NULL     |551       |SELECT TABLE_NAME , `table_typ ... bles` WHERE `table_schema` = ?|

【部分运行结果略】

|e        |1         |SELECT `index_name` , `rows_se ... tics` WHERE `table_schema` = ?|
|e        |1         |SELECT `object_schema` , `obje ... ` ORDER BY `allocated` LIMIT ?|
|e        |1         |SELECT * FROM `sys` . `stateme ... ll_table_scans` WHERE `db` = ?|
+---------+----------+-----------------------------------------------------------------+

监控使用了排序的SQL

示例代码：

1
2
3

select db, exec_count, first_seen, last_seen, query
from sys.statements_with_sorting
limit 1;

运行结果：

+--+----------+--------------------------+--------------------------+-----------------------------------------------------------------+
|db|exec_count|first_seen                |last_seen                 |query                                                            |
+--+----------+--------------------------+--------------------------+-----------------------------------------------------------------+
|i |2         |2022-05-29 13:19:24.156032|2022-05-29 13:21:22.422684|SELECT `student_id` , COUNT (  ... ORDER BY `create_time` LIMIT ?|
+--+----------+--------------------------+--------------------------+-----------------------------------------------------------------+

监控使用了临时表或者磁盘临时表的SQL

示例代码：

select db, exec_count, tmp_tables, tmp_disk_tables, query
from sys.statement_analysis
where tmp_tables > 0
   or tmp_disk_tables > 0
order by (tmp_tables + tmp_disk_tables) desc;

运行结果：

+----+----------+----------+---------------+-----------------------------------------------------------------+
|db  |exec_count|tmp_tables|tmp_disk_tables|query                                                            |
+----+----------+----------+---------------+-----------------------------------------------------------------+
|NULL|509       |1527      |0              |SELECT `grantee` , TABLE_NAME  ... , `grantee` , `privilege_type`|
|NULL|551       |551       |0              |SELECT `ordinal_position` , CO ... ABLE_NAME , `ordinal_position`|
|NULL|509       |509       |0              |SELECT TABLE_NAME , `index_nam ...  `index_name` , `seq_in_index`|

【部分运行结果略】

|i   |1         |1         |0              |SHOW INDEX FROM `course`                                         |
|e   |1         |1         |0              |EXPLAIN SELECT * FROM `s1` WHE ... s2` . `key1` = `s1` . `key1` )|
|j   |1         |1         |0              |SHOW GLOBAL STATUS LIKE ?                                        |
+----+----------+----------+---------------+-----------------------------------------------------------------+

IO相关

查看消耗磁盘IO的文件

示例代码：

select file, avg_read, avg_write, avg_read + avg_write as avg_io
from sys.io_global_by_file_by_bytes
order by avg_read
limit 10;

运行结果：

+---------------------------------+----------+----------+------+
|file                             |avg_read  |avg_write |avg_io|
+---------------------------------+----------+----------+------+
|@@datadir/kakadb/countries.ibd|   0 bytes|   0 bytes|0     |
|@@datadir/#innodb_temp/temp_4.ibt|   0 bytes|16.00 KiB |16    |
|@@datadir/e/t.MYD                |   0 bytes|   0 bytes|0     |
|@@datadir/#innodb_temp/temp_5.ibt|   0 bytes|16.00 KiB |16    |
|@@datadir/#innodb_temp/temp_3.ibt|   0 bytes|16.00 KiB |16    |
|@@datadir/#innodb_temp/temp_2.ibt|   0 bytes|16.00 KiB |16    |
|@@datadir/#innodb_temp/temp_1.ibt|   0 bytes|16.00 KiB |16    |
|@@datadir/ib_logfile1            |   0 bytes|   0 bytes|0     |
|@@datadir/#innodb_temp/temp_7.ibt|   0 bytes|16.00 KiB |16    |
|@@datadir/#innodb_temp/temp_6.ibt|   0 bytes|16.00 KiB |16    |
+---------------------------------+----------+----------+------+

Innodb 相关

行锁阻塞情况

1 2	select * from sys.innodb_lock_waits;

风险提示

通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。
建议生产上不要频繁的去查询sys或者performance_schema、information_schema，即使是为了完成监控、巡检等工作。

SHOW PROFILE

在《4.结构》，我们就已经讨论过SHOW PROFILE，当时用于查看SQL的执行过程，关于profiling的开启和使用，当时都讨论了，这里主要做点补充。

常用查询参数

SHOW PROFILE的常用查询参数：

ALL：所有的开销
BLOCK IO：显示块IO开销
CONTEXT SWITCHES：上下文切换开销
CPU：CPU开销
IPC：发送和接收的开销
MEMORY：内存开销
PAGE FAULTS：页面错误开销
SWAPS：交换次数开销

过程的关注

示例代码：

1	show profile;

运行结果：

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.005181 |
| Executing hook on transaction  | 0.005889 |
| starting                       | 0.000027 |
| checking permissions           | 0.000632 |
| Opening tables                 | 0.000063 |
| init                           | 0.000007 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000019 |
| statistics                     | 0.000094 |
| preparing                      | 0.000026 |
| executing                      | 1.254027 |
| end                            | 0.000023 |
| query end                      | 0.000006 |
| waiting for handler commit     | 0.000011 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000062 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+

解释说明：如执行结果所示，主要时间在执行上。

需要注意的是，如果show profile中，出现了下述4条的任何一条，则需要优化：

converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
copying to tmp table on disk：把内存中临时表复制到磁盘上。
locked

那么，怎么优化呢？EXPALIN。

EXPLAIN

概述

EXPLAIN，也是分析工具的一种，鉴于EXPLAIN的重要性，我们作为了单独的一节。

EXPALIN，可以告诉我们：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

语法格式

语法格式：

1	EXPLAIN SQL语句

示例代码：

1	EXPLAIN SELECT 1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |No tables used|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+

接下来，我们讨论每一列的作用。

各列作用

重点是：type、key_len、rows、Extra。

table

不论我们的查询语句有多复杂，包含了多少个表，到具体的步骤，一定是单表访问的。
EXPLAIN语句输出的每条记录都对应着某张表的访问方法，而table就是表名(有时不是真实的表名字，可能是简称)。

示例代码：

1
2
3

EXPLAIN
SELECT *
FROM s1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+

示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra                        |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL                         |
|1 |SIMPLE     |s2   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |Using join buffer (hash join)|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+

解释说明：运行结果第一行是驱动表，第二行是被驱动表。

id

每一个id，代表一趟查询。

在一次查询中，id越大，优先级越高，越先执行。
id如果相同，则认为是一组，从上往下顺序执行。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+

示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra                        |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL                         |
|1 |SIMPLE     |s2   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |Using join buffer (hash join)|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+

解释说明：虽然有两张表，但是只有一个SELECT，只有一次查询，所以只有一个id。

但，这个就会有两个id，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 IN (SELECT key1 FROM s2)
   OR key3 = 'a';

运行结果：

+--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+
|id|select_type       |table|partitions|type          |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
+--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+
|1 |PRIMARY           |s1   |NULL      |ALL           |idx_key3     |NULL    |NULL   |NULL|99640|100     |Using where|
|2 |DEPENDENT SUBQUERY|s2   |NULL      |index_subquery|idx_key1     |idx_key1|303    |func|1    |100     |Using index|
+--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+

我们再来看看UNION和UNION ALL的执行计划的区别。
UNION，示例代码：

EXPLAIN
SELECT *
FROM s1
UNION
SELECT *
FROM s2;

运行结果：

+----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+
|id  |select_type |table     |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra          |
+----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+
|1   |PRIMARY     |s1        |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL           |
|2   |UNION       |s2        |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL           |
|NULL|UNION RESULT|<union1,2>|NULL      |ALL |NULL         |NULL|NULL   |NULL|NULL |NULL    |Using temporary|
+----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+

解释说明：UNION RESULT：去重；Extra为Using temporary，即采用了临时表；table为表名，<union1,2>。

UNION ALL，示例代码：

EXPLAIN
SELECT *
FROM s1
UNION ALL
SELECT *
FROM s2;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|1 |PRIMARY    |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL |
|2 |UNION      |s2   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+

解释说明：比UNION少了一步"去重"，这就印证了我们在《2.DQL(SELECT)》说的，UNION ALL比UNION效率更高。

那么，是不是一个SQL，就几个SELECT关键字，就会有几个id？
不准确。
示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

运行结果：

+--+-----------+-----+----------+------+-------------+--------+-------+---------+-----+--------+----------------------------------+
|id|select_type|table|partitions|type  |possible_keys|key     |key_len|ref      |rows |filtered|Extra                             |
+--+-----------+-----+----------+------+-------------+--------+-------+---------+-----+--------+----------------------------------+
|1 |SIMPLE     |s1   |NULL      |ALL   |idx_key1     |NULL    |NULL   |NULL     |99640|100     |Using where                       |
|1 |SIMPLE     |s2   |NULL      |eq_ref|idx_key2     |idx_key2|5      |e.s1.key1|1    |10      |Using index condition; Using where|
+--+-----------+-----+----------+------+-------------+--------+-------+---------+-----+--------+----------------------------------+

解释说明：查询优化器可能会查询语句进行重写，这里的子查询被重写为了关联查询。

我们可以通过TRACE看一下。
首先，执行要查询的SQL，示例代码：

1	SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

然后，查看TRACE，示例代码：

1	SELECT * FROM information_schema.optimizer_trace\G

运行结果：

*************************** 1. row ***************************
QUERY: SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a')
TRACE: {
  "steps": [
    {

【部分运行结果略】

          {
            "transformations_to_nested_joins": {
              "transformations": [
                "semijoin"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `s1`.`id` AS `id`,`s1`.`key1` AS `key1`,`s1`.`key2` AS `key2`,`s1`.`key3` AS `key3`,`s1`.`key_part1` AS `key_part1`,`s1`.`key_part2` AS `key_part2`,`s1`.`key_part3` AS `key_part3`,`s1`.`common_field` AS `common_field` from `s1` semi join (`s2`) where ((`s2`.`common_field` = 'a') and (`s1`.`key1` = `s2`.`key2`))"
            } /* transformations_to_nested_joins */
          }

【部分运行结果略】

解释说明：如TRACE的transformations_to_nested_joins的expanded_query所示，被转化为了JOIN。

关于TRACE，会在下文进行更多的讨论。

select_type

select_type，表示一个小查询在大查询中扮演了一个什么角色。

名称	描述
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
UNION RESULT	Result of a UNION
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
DERIVED	Derived table
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

我们举几个例子。

SIMPLE

不包含UNION，或者子查询的查询，一般都算作SIMPLE，示例代码：

1
2
3

EXPLAIN
SELECT *
FROM s1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+

包括关联查询，也是SIMPLE，示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra                        |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL                         |
|1 |SIMPLE     |s2   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |Using join buffer (hash join)|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+

PRIMARY、UNION、UNION RESULT

对于包含UNION或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，最左边的是PRIMARY。

示例代码：

EXPLAIN
SELECT *
FROM s1
UNION
SELECT *
FROM s2;

运行结果：

+----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+
|id  |select_type |table     |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra          |
+----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+
|1   |PRIMARY     |s1        |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL           |
|2   |UNION       |s2        |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL           |
|NULL|UNION RESULT|<union1,2>|NULL      |ALL |NULL         |NULL|NULL   |NULL|NULL |NULL    |Using temporary|
+----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+

DEPENDENT SUBQUERY

如果包含子查询的查询语句，不能被优化为关联查询的形式，那么，子查询就是DEPENDENT SUBQUERY，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 IN (SELECT key1 FROM s2)
   OR key3 = 'a';

运行结果：

+--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+
|id|select_type       |table|partitions|type          |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
+--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+
|1 |PRIMARY           |s1   |NULL      |ALL           |idx_key3     |NULL    |NULL   |NULL|99640|100     |Using where|
|2 |DEPENDENT SUBQUERY|s2   |NULL      |index_subquery|idx_key1     |idx_key1|303    |func|1    |100     |Using index|
+--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+

DERIVED，派生表

示例代码：

EXPLAIN
SELECT *
FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1
where c > 1;

运行结果：

+--+-----------+----------+----------+-----+-------------+--------+-------+----+-----+--------+-----------+
|id|select_type|table     |partitions|type |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
+--+-----------+----------+----------+-----+-------------+--------+-------+----+-----+--------+-----------+
|1 |PRIMARY    |<derived2>|NULL      |ALL  |NULL         |NULL    |NULL   |NULL|99640|100     |NULL       |
|2 |DERIVED    |s1        |NULL      |index|idx_key1     |idx_key1|303    |NULL|99640|100     |Using index|
+--+-----------+----------+----------+-----+-------------+--------+-------+----+-----+--------+-----------+

partitions

partitions，代表分区表中的命中情况，非分区表，为NULL。

我们可以创建一个分区表，来看看。
首先，创建分区表，示例代码：

CREATE TABLE user_partitions
(
    id   INT auto_increment,
    NAME VARCHAR(12),
    PRIMARY KEY (id)
) PARTITION BY RANGE (id)( PARTITION p0 VALUES less than (100), PARTITION p1 VALUES less than MAXVALUE );

解释说明：id<100为p0分区，其他为p1分区。

EXPLAIN，看看，示例代码：

EXPLAIN
SELECT *
FROM user_partitions
WHERE id > 200;

运行结果：

+--+-----------+---------------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
|id|select_type|table          |partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra      |
+--+-----------+---------------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
|1 |SIMPLE     |user_partitions|p1        |range|PRIMARY      |PRIMARY|4      |NULL|1   |100     |Using where|
+--+-----------+---------------+----------+-----+-------------+-------+-------+----+----+--------+-----------+

解释说明：查询id > 200的记录，partitions是p1。

type

type，对表的执行查询时的访问方法，也称访问类型。

从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

system

当表中只有一条记录，且该表使用的存储引擎是MyISAM或Memory，那么对该表的访问方法就是system。

例如，我们创建一张MyISAM表，并插入一条记录，然后EXPLAIN一下。
示例代码：

CREATE TABLE t
(
    i int
) Engine = MyISAM;

1 2	INSERT INTO t VALUES (1);

1
2
3

EXPLAIN
SELECT *
FROM t;

运行结果：

+--+-----------+-----+----------+------+-------------+----+-------+----+----+--------+-----+
|id|select_type|table|partitions|type  |possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+------+-------------+----+-------+----+----+--------+-----+
|1 |SIMPLE     |t    |NULL      |system|NULL         |NULL|NULL   |NULL|1   |100     |NULL |
+--+-----------+-----+----------+------+-------------+----+-------+----+----+--------+-----+

但，假如表中的数据不止一条，又没有索引的话，示例代码：

1
2
3

EXPLAIN
SELECT *
FROM t;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
|1 |SIMPLE     |t    |NULL      |ALL |NULL         |NULL|NULL   |NULL|2   |100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+

解释说明：ALL，全表扫描。

如果是InnoDB的表，当然也不会是system，示例代码：

CREATE TABLE tt
(
    i INT
) ENGINE = InnoDB

1 2	INSERT INTO tt VALUES (2);

1
2
3

EXPLAIN
SELECT *
FROM tt;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
|1 |SIMPLE     |tt   |NULL      |ALL |NULL         |NULL|NULL   |NULL|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+

const

当我们根据"主键"或"具有唯一约束的二级索引列"与常数进行等值匹配时，对单表的访问方法就是const。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE id = 100002;

运行结果：

+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |const|PRIMARY      |PRIMARY|4      |const|1   |100     |NULL |
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+

如果等值匹配没有匹配上，不会有const，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE id = 1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                         |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |no matching row in const table|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------+

注意Extra，no matching row in const table。

如果相关索引是联合索引的话，则联合索引的所有列都需要进行等值匹配。
假设，存在索引如下：

Table	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Visible	Expression
s1	idx_key_part	1	key_part1	A	99640	NULL	NULL	YES	BTREE	YES	NULL
s1	idx_key_part	2	key_part2	A	99640	NULL	NULL	YES	BTREE	YES	NULL
s1	idx_key_part	3	key_part3	A	99640	NULL	NULL	YES	BTREE	YES	NULL

示例代码：

EXPLAIN SELECT *
FROM s1
WHERE key_part1 = 'JgCshJKFXV'
  and key_part2 = 'Oefqm'
  and key_part3 = 'nFloOaMJjS'

运行结果：

+--+-----------+-----+----------+-----+-------------+------------+-------+-----------------+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key         |key_len|ref              |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+------------+-------+-----------------+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |const|idx_key_part |idx_key_part|909    |const,const,const|1   |100     |NULL |
+--+-----------+-----+----------+-----+-------------+------------+-------+-----------------+----+--------+-----+

但，如果联合索引的，没有所有的列都进行等值匹配的话，则不是const。示例代码：

EXPLAIN SELECT *
FROM s1
WHERE key_part1 = 'JgCshJKFXV'
  and key_part2 = 'Oefqm'

运行结果：

+--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key         |key_len|ref        |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key_part |idx_key_part|606    |const,const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+

eq_ref

在连接查询时，如果被驱动表是通过"主键"或者"具有唯一约束的二级索引"列等值匹配的方式进行访问的，对该被驱动表的访问方法就是eq_ref。
同样，如果是联合素引的话，所有的索引列都必须进行等值匹配。

示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2 ON s1.id = s2.id;

运行结果：

+--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+
|id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref    |rows |filtered|Extra|
+--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ALL   |PRIMARY      |NULL   |NULL   |NULL   |99640|100     |NULL |
|1 |SIMPLE     |s2   |NULL      |eq_ref|PRIMARY      |PRIMARY|4      |e.s1.id|1    |100     |NULL |
+--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+

ref

如果不是具有唯一约束的二级索引(普通的二级索引)，进行等值匹配的话，会是ref。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+

ref_or_null

如果不是具有唯一约束的二级索引(普通的二级索引)，进行等值匹配且可以为NULL的话，是ref_or_null。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a'
   OR key1 IS NULL;

运行结果：

+--+-----------+-----+----------+-----------+-------------+--------+-------+-----+----+--------+---------------------+
|id|select_type|table|partitions|type       |possible_keys|key     |key_len|ref  |rows|filtered|Extra                |
+--+-----------+-----+----------+-----------+-------------+--------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |ref_or_null|idx_key1     |idx_key1|303    |const|2   |100     |Using index condition|
+--+-----------+-----+----------+-----------+-------------+--------+-------+-----+----+--------+---------------------+

index_merge

index_merge，使用索引合并的方式来执行，即可以对索引到的内容进行合并。
示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a'
   OR key3 = 'a';

运行结果：

+--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+
|id|select_type|table|partitions|type       |possible_keys    |key              |key_len|ref |rows|filtered|Extra                                      |
+--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+
|1 |SIMPLE     |s1   |NULL      |index_merge|idx_key1,idx_key3|idx_key1,idx_key3|303,303|NULL|2   |100     |Using union(idx_key1,idx_key3); Using where|
+--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+

那么，可以如果对索引到的内容取交集，会是index_merge吗？
当然不是，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a'
   AND key3 = 'a';

运行结果：

+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys    |key     |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1,idx_key3|idx_key1|303    |const|1   |5       |Using where|
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+

解释说明：possible_keys可能使用的索引有idx_key1,idx_key3，最后只用了idx_key1。

unique_subquery

优化器替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)，就会出现unique_subquery。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1)
   OR key3 = 'a';

运行结果：

+--+------------------+-----+----------+---------------+----------------+-------+-------+----+-----+--------+-----------+
|id|select_type       |table|partitions|type           |possible_keys   |key    |key_len|ref |rows |filtered|Extra      |
+--+------------------+-----+----------+---------------+----------------+-------+-------+----+-----+--------+-----------+
|1 |PRIMARY           |s1   |NULL      |ALL            |idx_key3        |NULL   |NULL   |NULL|99640|100     |Using where|
|2 |DEPENDENT SUBQUERY|s2   |NULL      |unique_subquery|PRIMARY,idx_key1|PRIMARY|4      |func|1    |10      |Using where|
+--+------------------+-----+----------+---------------+----------------+-------+-------+----+-----+--------+-----------+

index_subquery

类似于unique_subquery，同样是替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1)
   OR key3 = 'a';

运行结果：

+--+------------------+-----+----------+--------------+-----------------+--------+-------+----+-----+--------+-----------+
|id|select_type       |table|partitions|type          |possible_keys    |key     |key_len|ref |rows |filtered|Extra      |
+--+------------------+-----+----------+--------------+-----------------+--------+-------+----+-----+--------+-----------+
|1 |PRIMARY           |s1   |NULL      |ALL           |idx_key3         |NULL    |NULL   |NULL|99640|100     |Using where|
|2 |DEPENDENT SUBQUERY|s2   |NULL      |index_subquery|idx_key1,idx_key3|idx_key3|303    |func|1    |10      |Using where|
+--+------------------+-----+----------+--------------+-----------------+--------+-------+----+-----+--------+-----------+

range

只检索给定范围的行，使用一个索引来选择行。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 IN ('a', 'b', 'c');

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |range|idx_key1     |idx_key1|303    |NULL|3   |100     |Using index condition|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 > 'a'
  AND key1 < 'b';

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |range|idx_key1     |idx_key1|303    |NULL|3866|100     |Using index condition|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+

index

index：可以使用索引覆盖，但需要扫描全部索引记录，示例代码：

EXPLAIN
SELECT key_part2
FROM s1
WHERE key_part3 = 'a';

运行结果：

+--+-----------+-----+----------+-----+-------------+------------+-------+----+-----+--------+------------------------+
|id|select_type|table|partitions|type |possible_keys|key         |key_len|ref |rows |filtered|Extra                   |
+--+-----------+-----+----------+-----+-------------+------------+-------+----+-----+--------+------------------------+
|1 |SIMPLE     |s1   |NULL      |index|idx_key_part |idx_key_part|909    |NULL|99640|10      |Using where; Using index|
+--+-----------+-----+----------+-----+-------------+------------+-------+----+-----+--------+------------------------+

ALL

ALL，全表扫描。
示例代码：

1
2
3

EXPLAIN
SELECT *
FROM s1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+

possible_keys和key

possible_keys：可能会使用的索引.
key：最后选择的索引。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 > 'z'
  AND key3 = 'a';

运行结果：

+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys    |key     |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1,idx_key3|idx_key3|303    |const|1   |5       |Using where|
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+

key_len

实际使用到的索引长度，单位是字节。

长度的计算

varchar(10) 可以为NULL
key_len的长度计算：

$10 \times \text{单位字节数} + 1 + 2$

在UTF-8编码中，单位字节数为 $3$ ；在GBK编码中，单位字节数为 $2$ 。
关于"字符集编码"，我们在《基于Java的后端开发入门：5.IO流》也有讨论。
$1$ ：需要一个字节代表NULL(因为可以为NULL)。
$2$ ：需要两个字节记录长度。

varchar(10) 不可以为NULL
key_len的长度计算：

$10 \times \text{单位字节数} + 2$

char(10) 可以为NULL
key_len的长度计算：

$10 \times \text{单位字节数} + 1$

因为char(10)是固定长度，所以不需要专门用两个字节来记录长度。

再来看几个具体的例子。

假设id为int类型，且是主键，示例代码：

1	EXPLAIN SELECT * FROM s1 WHERE id = 100005;

运行结果：

+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |const|PRIMARY      |PRIMARY|4      |const|1   |100     |NULL |
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+

解释说明：为什么是4，因为id是int类型，int类型，4个字节。

假设key2为int类型，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key2 = 100126;

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |const|idx_key2     |idx_key2|5      |const|1   |100     |NULL |
+--+-----------+-----+----------+-----+-------------+--------+-------+-----+----+--------+-----+

为什么长度是 $5$ ？
因为这时候key2可以为空，所以多占一个字节。

假设key1为VARCHAR(100)，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+

为什么是 $303$ ？
VARCHAR(100)，UTF8编码，就是占据的字节数： $100 \times 3 = 300$ ，然后需要一个字节空，两个字节记录长度，所以是 $303$ 。

在联合索引中的应用

在联合索引中，我们可以通过key_len检查是否充分利用了索引。

假设key_part1、key_part2和key_part2都是varchar(100)，且可以为NULL。
我们创建了一个联合索引idx_key_part(key_part1,key_part2,key_part3)。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key_part1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key_part |idx_key_part|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----+

解释说明：key_len为303，说明只用了联合索引的第一列。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key_part1 = 'a'
  AND key_part2 = 'b';

运行结果：

+--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key         |key_len|ref        |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key_part |idx_key_part|606    |const,const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+

解释说明：key_len为606，说明只用了联合索引的前两列。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key_part1 = 'a'
  AND key_part2 = 'b'
  AND key_part3 = 'c';

运行结果：

+--+-----------+-----+----------+----+-------------+------------+-------+-----------------+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key         |key_len|ref              |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+------------+-------+-----------------+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key_part |idx_key_part|909    |const,const,const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+------------+-------+-----------------+----+--------+-----+

解释说明：key_len为909，说明只用了联合索引的前三列。

那么，如果我们只根据key_part1和key_part3进行查询呢，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key_part1 = 'a'
  AND key_part3 = 'c';

运行结果：

+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+---------------------+
|id|select_type|table|partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra                |
+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key_part |idx_key_part|303    |const|1   |10      |Using index condition|
+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+---------------------+

解释说明：key_len为303，说明只用了联合索引的第一列。

ref

当使用普通索引列进行等值匹配时，与索引列进行等值匹配的对象信息。
比如：常数、列、函数。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+

解释说明：ref列的值是const，表示常量。

示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2 ON s1.id = s2.id;

运行结果：

+--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+
|id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref    |rows |filtered|Extra|
+--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ALL   |PRIMARY      |NULL   |NULL   |NULL   |99640|100     |NULL |
|1 |SIMPLE     |s2   |NULL      |eq_ref|PRIMARY      |PRIMARY|4      |e.s1.id|1    |100     |NULL |
+--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+

解释说明：ref列的值是e.s1.id，库e的表s1的列id。

示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+----+-----+--------+---------------------+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref |rows |filtered|Extra                |
+--+-----------+-----+----------+----+-------------+--------+-------+----+-----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL    |NULL   |NULL|99640|100     |NULL                 |
|1 |SIMPLE     |s2   |NULL      |ref |idx_key1     |idx_key1|303    |func|1    |100     |Using index condition|
+--+-----------+-----+----------+----+-------------+--------+-------+----+-----+--------+---------------------+

解释说明：ref列的值是func，函数。

rows

rows，预估需要读取的记录数，越小越好。因为越小，越可能在一个页中。

示例代码：

1	EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |range|idx_key1     |idx_key1|303    |NULL|3977|100     |Using index condition|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+

filtered

filtered，某个表经过搜索条件过滤后剩余记录条数的百分比。
注意！是一个不精确的大概值！

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 > 'z'
  AND common_field = 'a';

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+----------------------------------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                             |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+----------------------------------+
|1 |SIMPLE     |s1   |NULL      |range|idx_key1     |idx_key1|303    |NULL|3977|10      |Using index condition; Using where|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+----------------------------------+

解释说明：满足key1 > 'z'，有3977，再满足common_field = 'a'，大概只有10%。

Extra

Extra，额外信息。

但有时候会有和优化非常有关的信息。

No tables used

No tables used：没用到表。

示例代码：

1	EXPLAIN SELECT 1

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |No tables used|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+

Impossible WHERE

Impossible WHERE，不可能的过滤。
示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE 1 != 1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra           |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |Impossible WHERE|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+

Using where

Using where，用到了过滤。
那么，什么情况下会Using where呢？
当WHERE条件中，有字段没有索引时。

假设common_field字段上没有任何索引，那么这时候就会做全表扫描，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE common_field = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|10      |Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+

假设WHERE条件有两个，key1和common_field，但只有key1上有索引，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a'
  AND common_field = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |10      |Using where|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+

假设WHERE条件只有一个，为key1，且key1上有索引，示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a'

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+

解释说明：没有Using where，直接用索引。

Using index
当我们要查询的字段，在索引中已经有了，不需要再进行回表的话，会有Using index。
这时候的索引，也被称为覆盖索引。

例如，索引字段是key1，而我们的查询字段也是key1，示例代码：

EXPLAIN
SELECT key1
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+

那么，如果索引字段是key1，我们同时查询主键id和索引key1呢？
根据在《4.结构》中，我们关于二级索引结构的讨论，这时候也是不需要回表的，所以也会有Using index。
示例代码：

EXPLAIN
SELECT key1, id
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+

示例代码：

EXPLAIN
SELECT key1, id, key2
FROM s1
WHERE key1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key1     |idx_key1|303    |const|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+

假设，我们创建了一个联合索引idx_key_part(key_part1,key_part2,key_part3)，然后我们查询字段是id,key_part1,key_part2,key_part3，这时候不需要回表，会有Using index。
示例代码：

EXPLAIN
SELECT id,key_part1,key_part2,key_part3
FROM s1
WHERE key_part1 = 'a';

运行结果：

+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |ref |idx_key_part |idx_key_part|303    |const|1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----------+

这就是覆盖索引
也是我们在上文，"不创建索引的案例：起不到定位作用的字段"中说的，并不是说起不到定位作用的字段一定不创建索引。

Using index condition

Using index condition，当出现了这个，说明使用了索引条件下推(Index Condition Pushdown)。

假设存在一个SQL，如下：

SELECT *
FROM s1
WHERE key1 > 'z'
  AND key1 LIKE '%a';

其中key1 > 'z'可以使用索引，但是key1 LIKE '%a'，无法使用索引。
在5.6之前的版本中，首先找到key1 > z，获取其二级索引的记录，然后回表，再筛选出key1 LIKE '%a'的。
在5.6及之后的版本中，首先找出key1 > z的二级索引记录，然后对二级索引记录进行筛查，找出其中key1 LIKE '%a'的，最后进行回表。MySQL把这个改进称之为索引条件下推(Index Condition Pushdown)

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 > 'z'
  AND key1 LIKE '%a';

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra                |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |s1   |NULL      |range|idx_key1     |idx_key1|303    |NULL|3977|100     |Using index condition|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+

Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join bufter的内存块来加快查询速度，也就是所谓的"基于块的嵌套循环算法"。

示例代码：

EXPLAIN
SELECT *
FROM s1
         INNER JOIN s2 ON s1.common_field = s2.common_field;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+------------------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra                                     |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+------------------------------------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |NULL                                      |
|1 |SIMPLE     |s2   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|10      |Using where; Using join buffer (hash join)|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+------------------------------------------+

Not exists

当我们使用左连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又不能为空时，那么在该表的执行计划的Extra列就会提示Not exists，表示不存在。

示例代码：

EXPLAIN
SELECT *
FROM s1
         LEFT JOIN s2 ON s1.key1 = s2.key1
WHERE s2.id IS NULL;

运行结果：

+--+-----------+-----+----------+----+-------------+--------+-------+---------+-----+--------+-----------------------+
|id|select_type|table|partitions|type|possible_keys|key     |key_len|ref      |rows |filtered|Extra                  |
+--+-----------+-----+----------+----+-------------+--------+-------+---------+-----+--------+-----------------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL    |NULL   |NULL     |99640|100     |NULL                   |
|1 |SIMPLE     |s2   |NULL      |ref |idx_key1     |idx_key1|303    |e.s1.key1|1    |10      |Using where; Not exists|
+--+-----------+-----+----------+----+-------------+--------+-------+---------+-----+--------+-----------------------+

Using intersect(…)、Using union(…) 和 Using sort_union(…)

Using intersect(...)、Using union(...)和Using sort_union(...)，说明计划使用索引合并的方式来执行，即可以对索引到的内容进行合并。

示例代码：

EXPLAIN
SELECT *
FROM s1
WHERE key1 = 'a'
   OR key3 = 'a';

运行结果：

+--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+
|id|select_type|table|partitions|type       |possible_keys    |key              |key_len|ref |rows|filtered|Extra                                      |
+--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+
|1 |SIMPLE     |s1   |NULL      |index_merge|idx_key1,idx_key3|idx_key1,idx_key3|303,303|NULL|2   |100     |Using union(idx_key1,idx_key3); Using where|
+--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+

Using filesort

使用到了文件排序的时候，会有Using filesort，这是一种我们要尽量避免的情况。

假设common_field没有索引，根据common_field进行排序，就会利用文件排序，示例代码：

EXPLAIN
SELECT *
FROM s1
ORDER BY common_field
LIMIT 10;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+--------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra         |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+--------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |Using filesort|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+--------------+

假设key1上有索引，根据key1进行排序，就不会利用文件排序，示例代码：

EXPLAIN
SELECT *
FROM s1
ORDER BY key1
LIMIT 10;

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----+
|1 |SIMPLE     |s1   |NULL      |index|NULL         |idx_key1|303    |NULL|10  |100     |NULL |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----+

解释说明：这时候，可以利用idx_key1索引直接取出key1列的10条记录，然后再进行回表操作。

Using temporary

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能。比如：去重、排序之类的。
在执行许多包含、DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。
是一种需要尽量避免的情况。

假设common_field没有索引，DISTINCT common_field，示例代码：

1
2
3

EXPLAIN
SELECT DISTINCT common_field
FROM s1;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra          |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |Using temporary|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+

假设key1上有索引，DISTINCT key1，示例代码：

1
2
3

EXPLAIN
SELECT DISTINCT key1
FROM s1;

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |index|idx_key1     |idx_key1|303    |NULL|99640|100     |Using index|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+

假设common_field没有索引，GROUP BY common_field，示例代码：
示例代码：

EXPLAIN
SELECT common_field, COUNT(*) AS amount
FROM s1
GROUP BY common_field;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra          |
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+
|1 |SIMPLE     |s1   |NULL      |ALL |NULL         |NULL|NULL   |NULL|99640|100     |Using temporary|
+--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+

假设key1上有索引，GROUP BY key1，示例代码：
示例代码：

EXPLAIN
SELECT key1, COUNT(*) AS amount
FROM s1
GROUP BY key1;

运行结果：

+--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+
|id|select_type|table|partitions|type |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+
|1 |SIMPLE     |s1   |NULL      |index|idx_key1     |idx_key1|303    |NULL|99640|100     |Using index|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+

Zero limit

LIMIT 0，示例代码：

EXPLAIN
SELECT *
FROM s1
LIMIT 0;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra     |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |Zero limit|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------+

四种输出格式

EXPLAIN有四种格式：

传统格式
JSON格式
TREE格式
可视化输出

传统格式

传统格式，输出是一个表格形式，概要说明查询计划。
我们上文的都是传统格式。

JSON格式

JSON格式是四种格式里面输出信息最详尽的格式，包含了执行的成本信息。
使用方法问，在EXPLAIN关键字和查询语句中间加上FORMAT=JSON。

示例代码：

EXPLAIN FORMAT = JSON
SELECT *
FROM s1
         INNER JOIN s2 s on s1.key2 = s.key2
WHERE s1.common_field = 'a'

运行结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13587.65"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "ALL",
          "possible_keys": [
            "idx_key2"

【部分运行结果略】

            "prefix_cost": "13587.65",
            "data_read_per_join": "17M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}

需要注意的是，这里的成本，并不是只考虑了时间的成本，而且查询优化器根据某种方法，综合得出的成本。

TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

示例代码：

EXPLAIN FORMAT=TREE
SELECT *
FROM s1
         INNER JOIN s2 s on s1.key2 = s.key2
WHERE s1.common_field = 'a'

运行结果：

-> Nested loop inner join  (cost=13587.65 rows=9964)
   -> Filter: ((s1.common_field = 'a') and (s1.key2 is not null))  (cost=10100.25 rows=9964)
       -> Table scan on s1  (cost=10100.25 rows=99640)
   -> Single-row index lookup on s using idx_key2 (key2=s1.key2)  (cost=0.25 rows=1)

阅读顺序：

从右到左：没有遇到并列的迭代器之前，都是从右边开始执行。
从上到下：遇到并列的迭代器，都是上边的先开始执行。

即，我们先看Nested loop inner join ...，然后看Table scan on s1 ...，再看Filter: ((s1.common_field = 'a') ...，最后看Single-row index lookup ...。

可视化输出

可视化输出，需要借助MySQL的官方工具MySQL Workbench。

我们不作太多讨论。

TRACE

分析优化器执行计划：TRACE，5.6版本引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
通过查询INFORMATION_SCHEMA.OPTIMIZER_TRACE，我们能更好的明白，EXPLAIN的结果是怎么来的。

该功能默认关闭，需要手动开启，并设置格式为JSON，示例代码：

1	SET optimizer_trace = "enabled=on",end_markers_in_json = on;

同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示，示例代码：

1	SET optimizer_trace_max_mem_size = 1000000;

开启后，可分析如下语句：SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IFRETURN、CALL。

示例代码：

1	SELECT * FROM information_schema.optimizer_trace\G

查询优化

关联查询

现象

假设存在表如下：

CREATE TABLE t(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    card INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE b(
    bookid INT UNSIGNED NOT NULL AUTO_INCREMENT,
    card INT UNSIGNED NOT NULL,
    PRIMARY KEY (bookid)
);

其中t表有记录20条，b表有记录40条。

外连接

我们以左外连接为例。

如果没有任何索引，EXPLAIN，示例代码：

EXPLAIN
SELECT *
FROM t
         LEFT JOIN b on t.card = b.card;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                                     |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------------------+
|1 |SIMPLE     |t    |NULL      |ALL |NULL         |NULL|NULL   |NULL|20  |100     |NULL                                      |
|1 |SIMPLE     |b    |NULL      |ALL |NULL         |NULL|NULL   |NULL|40  |100     |Using where; Using join buffer (hash join)|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------------------+

解释说明：两张表都是全表扫描。

我们先给被驱动表添加索引，示例代码：

1 2	ALTER TABLE b ADD INDEX Y (card);

EXPLAIN
SELECT *
FROM t
         LEFT JOIN b on t.card = b.card;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref     |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+
|1 |SIMPLE     |t    |NULL      |ALL |NULL         |NULL|NULL   |NULL    |20  |100     |NULL       |
|1 |SIMPLE     |b    |NULL      |ref |Y            |Y   |4      |j.t.card|2   |100     |Using index|
+--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+

解释说明：被驱动表的type变为了ref，rows获得了明显的优化。这是由LEFT JOIN特性决定的，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。

我们再给驱动表建立索引，示例代码：

1 2	ALTER TABLE t ADD INDEX X (card);

EXPLAIN
SELECT *
FROM t
         LEFT JOIN b ON t.card = b.card;

运行结果：

+--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+
|id|select_type|table|partitions|type |possible_keys|key|key_len|ref     |rows|filtered|Extra      |
+--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+
|1 |SIMPLE     |t    |NULL      |index|NULL         |X  |4      |NULL    |20  |100     |Using index|
|1 |SIMPLE     |b    |NULL      |ref  |Y            |Y  |4      |j.t.card|2   |100     |Using index|
+--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+

解释说明：这时候驱动表和被驱动表都会利用索引了。

最后，如果我们再把被驱动表的索引删了，示例代码：

1	DROP INDEX Y ON b;

EXPLAIN
SELECT *
FROM t
         LEFT JOIN b ON t.card = b.card;

运行结果：

+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------------------------+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra                                     |
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------------------------+
|1 |SIMPLE     |t    |NULL      |index|NULL         |X   |4      |NULL|20  |100     |Using index                               |
|1 |SIMPLE     |b    |NULL      |ALL  |NULL         |NULL|NULL   |NULL|40  |100     |Using where; Using join buffer (hash join)|
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------------------------+

内连接

继续上文，现在只有t表上有索引，我们采取内连接的方式，示例代码：

EXPLAIN
SELECT *
FROM t
         INNER JOIN b ON t.card = b.card;

运行结果：

+--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref     |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+
|1 |SIMPLE     |b    |NULL      |ALL |NULL         |NULL|NULL   |NULL    |40  |100     |NULL       |
|1 |SIMPLE     |t    |NULL      |ref |X            |X   |4      |j.b.card|1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+

这时候是b表驱动t表了，这是因为对于内连接，查询优化器会自主决定，驱动表和被驱动表。
那么，现在问题就来了，为什么查询优化器要选择有索引的表作为被驱动表呢？

如果我们再把b表的索引加回来呢？示例代码：

1 2	ALTER TABLE b ADD INDEX Y (card);

EXPLAIN
SELECT *
FROM t
         INNER JOIN b ON t.card = b.card;

运行结果：

+--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+
|id|select_type|table|partitions|type |possible_keys|key|key_len|ref     |rows|filtered|Extra      |
+--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+
|1 |SIMPLE     |t    |NULL      |index|X            |X  |4      |NULL    |20  |100     |Using index|
|1 |SIMPLE     |b    |NULL      |ref  |Y            |Y  |4      |j.t.card|2   |100     |Using index|
+--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+

为什么现在又是t表驱动b表？

小表驱动大表

为什么

举个例子，假设现在存在两张表，t1是驱动表，上面没有索引，t2是被驱动表，上面有索引。

对于驱动表来说，是全表扫描，假设驱动表的行数是 $N$ ，那么时间复杂度就是 $O(N)$ 。每扫描到一行，就会再去被驱动表中扫描一次。

对于被驱动表，我们有索引，假设被驱动表的行数是 $M$ ，那么时间复杂度就是 $2 \times O(\log_2 M)$ 。
(乘以 $2$ 的原因，是因为还有根据主键的回表操作；但根据复杂度的计算规则，也可以忽略乘以 $2$ )

所以，整体的时间复杂度是

$N + N \times 2 \times \log_2 M$

那么，现在问，为了整体的时间复杂度更小，且 $N$ 和 $M$ 中，必须有一个更大一些，是 $N$ 更大一些好，还是 $M$ 更大一些好？
显然， $M$ 更大一些好。
这就是为什么 小表驱动大表。

什么是小表

但，为什么当两张表都有card索引时，要选择t表驱动b表呢？

我们可以看看t表的card索引数，示例代码：

1 2	SELECT COUNT(DISTINCT (card)) FROM t;

运行结果：

再看看b表，示例代码：

1 2	SELECT COUNT(DISTINCT (card)) FROM b;

运行结果：

所以，t表驱动b表的话， $N = 11$ ， $M = 19$ ，即

$11 + 11 \times 2 \times \log_2 19$

但，如果b表驱动t表的话， $N = 19$ ， $M = 11$ ，即

$19 + 19 \times 2 \times \log_2 11$

这就是为什么要用t表驱动b表。

也就是说： 小表，并不是简单意义上的数据量小的表，而是遍历次数少的表。

子查询

子查询的执行效率不高，因为：

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

所以，尽可能使用关联查询来替代子查询。

ORDER BY

在MySQL中，支持两种排序方式，FileSort和IndexSort，尽量避免使用FileSort。

GROUP BY

在GROUP BY的字段上建立索引。
对于联合索引，GROUP BY的列放在ORDER BY的列的前面，因为GROUP BY会比ORDER BY先执行。
包含了ORDER BY、GROUP BY、DISTINCT这些查询的语句，WHERE条件过滤出来的结果集请保持在1000行以内，否则会很慢。

WHERE HAVING

WHERE效率高于HAVING，能写在WHERE限定的条件就不要写在HAVING中了

分页查询

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容，示例代码：

SELECT *
FROM student t,
     (SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二

该方案适用于主键严格自增的表，示例代码：

SELECT *
FROM student
WHERE id > 2000000
LIMIT 10;

特别注意！如果主键不是严格自增，这种方法不适合。

覆盖索引

关于覆盖索引，我们在上文讨论EXPLAIN的Extra的Using index中，已经讨论过了。
不再赘述。
这里再强调一下优缺点：
优点：

避免二次查询(回表)
可以把随机IO变成顺序IO加快查询效率

缺点：索引字段的维护有代价，会影响更新数据的效率。

IN和EXISTS

什么时候选择IN，什么时候选择EXISTS？
标准依旧是"小表驱动大表"

对于IN，形如：

1
2
3

SELECT *
FROM A
WHERE col in (SELECT col FROM B)

其规则是，子查询驱动外部，所以这时候B小一些会更好。

对于EXISTS，形如：

1
2
3

SELECT *
FROM A
WHERE EXISTS(SELECT col FROM B WHERE B.col = A.col)

这其实是一个相关子查询，在《2.DQL(SELECT)》，我们讨论过，首先要把外部表中的相关数据送进子查询。
所以，这时候是"外部"驱动"内部"，即A表驱动B表，所以A小一些更好。

LIMIT 1

针对会扫描全表的SQL语句，如果可以确定结果集只有一条，那么可以加上LIMIT 1的时候，这样会加快查询速度。

其它优化

软件

选择适合的数据库。除了关系型数据库外，还有很多非关系型数据库。
(关于非关系型数据库，我们在《1.概述和工具准备》有简单的讨论。)
优化表设计
1. 表结构要尽量遵循三范式的原则。这样可以让数据结构更加清晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。
2. 如果查询应用比较多，尤其是需要进行多表关联查询的时候，可以采用反范式进行优化，即采用空间换时间的方式，通过增加冗余字段提高查询的效率。
3. 表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。
  1. 一般来说，如果字段可以采用数值类型就不要采用字符类型；
  2. 针对字符类型来说，当确定字符长度固定时，就可以采用CHAR类型：当长度不固定时，通堂采用VARCHAR类型。
优化查询，即我们上文讨论的查询优化。
优化逻辑，在确定了查询优化之后，可以对索引进行优化，增加、修改或删除部分索引。
库级优化
1. 读写分离。
2. 数据分片。

硬件

服务器的硬件性能直接决定着MySQL数据库的性能，针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。

配置较大的内存，以避免磁盘过多的IO。
配置高速磁盘，以提高磁盘IO速度。
合理分布磁盘IO，把磁密IO分散在多个设备上。
配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。

参数

我们还可以通过修改MySQL的参数，进行优化。

innodb_buffer_pool_size：InnoDB类型的表和索引的最大缓存，这个值越大，查询的速度就会越快，但太大会影响操作系统的性能。
key_buffer_size：索引缓冲区的大小，是所有的线程共享。增加索引缓冲区可以更好的处理索引，但太多会导致操作系统频繁换页，也会降低系统性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。
table_cache：同时打开的表的个数。
sort_buffer_size：每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。对于内存在4GB左右的服务器推荐设置为6M到8M。
join_buffer_size：关联查询操作所能使用的缓冲区大小。
max_connections：允许连接到MySQL数据库的最大数量。

参考资料：

https://www.bilibili.com/video/BV1iq4y1u7vj

https://www.yisu.com/zixun/29947.html

https://www.mmzsblog.cn/articles/2022/05/07/1651914715938.html

文章作者: Kaka Wan Yifan

文章链接: https://kakawanyifan.com/11105

留言板

索引

分类

物理实现方式

作用字段个数

索引的功能

创建索引

在建表时

隐式的方式

显式的方式

创建普通索引

创建唯一索引

创建联合索引

创建全文索引

创建主键索引

在建表后

ALTER TABLE

CREATE INDEX

删除索引

ALTER TABLE

DROP INDEX

8版本中的新特性

降序索引

隐藏索引

简介

创建表同时，创建

创建表之后，创建

切换索引可见状态

优化案例

创建索引的案例

WHERE条件的字段

ORDER BY和GROUP BY的字段

DISTINCT字段

JOIN连接的字段

区分度较高的字段

占用字节较小的字段

对于较长的字段建立前缀索引

联合索引的创建

区分度高的列放前面

使用频繁的列放前面

联合索引优于单值索引

GROUP BY的列放在ORDER BY的列的前面

不创建索引的案例

为什么不创建索引

起不到定位作用的字段

有大量重复数据的列

避免对频繁更新的字段创建索引

不建议用无序的值作为索引

不再使用或者很少使用的索引

不要创建冗余索引

不要创建重复索引

数据量小的表

索引失效案例

最左前缀法则

函数和计算

类型转换

范围条件的右边的列

不等于

IS NOT NULL

OR条件中存在非索引的列

like以通配符%开头

分析工具

查看系统性能

开启慢查询日志

作用

参数

slow_query_log

long_query_time

min_examined_row_limit

持久化

查看慢查询数目

删除慢查询日志

分析慢查询日志

查看帮助

常用参考

sys schema

sys schema视图摘要

索引相关

表相关

语句相关

IO相关

`GROUP BY`的列放在`ORDER BY`的列的前面