索引
分类
物理实现方式
以物理实现方式进行分类，有
聚簇索引非聚簇索引
关于聚簇索引和非聚簇索引，我们《4.结构》 有过讨论。
作用字段个数
以作用字段个数进行分类，有
单列索引联合索引
索引的功能
以索引的功能进行分类，有
普通索引唯一索引唯一索引。主键索引主键索引。
以及，全文索引和空间索引等。
创建索引
和创建约束一样，创建索引也有两种方式：
在建表时
在建表后，又有两种方法：ALTER TABLE和CREATE INDEX。
在建表时
隐式的方式
隐式的方式：在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。
1 2 3 4 5 CREATE TABLE dept( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR (20 ) );
显式的方式
语法格式：
1 2 3 4 5 6 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字段上建立普通索引。示例代码：
1 2 3 4 5 6 7 8 9 10 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 2 3 4 5 6 7 8 9 10 11 12 13 +-----+----------------------------------------------+ |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 2 3 4 5 +-----+----------+----------------+------------+----------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+ |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 | +-----+----------+----------------+------------+----------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
创建唯一索引
除了用添加唯一约束的方式，隐式的创建唯一索引，还有显式的创建唯一索引。
示例代码：
1 2 3 4 5 6 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;
运行结果：
1 2 3 4 5 +-----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+ |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，所以是唯一性的。
创建联合索引
在讨论创建联合索引之前，我们先看看创建单列索引。
1 2 3 4 5 6 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
运行结果：
1 2 3 4 5 +-----------+----------+---------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+ |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 | +-----------+----------+---------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
创建表test_multi，在表中的id、name和age字段上建立联合索引，示例代码：
1 2 3 4 5 6 7 8 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;
运行结果：
1 2 3 4 5 6 7 +----------+----------+---------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+ |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字段上建立全文索引，示例代码：
1 2 3 4 5 6 7 8 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字段，创建全文索引。
1 2 3 4 5 6 7 8 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 '%查询条件%';，要用MATCH + AGAINST的方式进行查询，示例代码：
1 2 3 SELECT *FROM papersWHERE MATCH (title, content ) AGAINST('查询字符串' );
创建主键索引
创建主键索引，需要通过主键约束的方式，添加主键索引。
示例代码：
1 2 3 4 5 6 7 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 TABLECREATE INDEX
ALTER TABLE
语法格式：
1 2 3 4 5 6 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
语法格式：
1 2 3 4 5 6 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 TABLEDROP INDEX
ALTER TABLE
语法格式：
1 ALTER TABLE table_name DROP INDEX index_name;
例如，有一张表如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 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降序。
1 2 3 4 5 6 CREATE TABLE ts1( a int , b int , index idx_a_b (a ASC , b DESC ) );
在8版本中，查看表ts1的表结构，示例代码：
运行结果：
1 2 3 4 5 6 7 8 9 +-----+------------------------------------------+ |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版本中，如下：
1 2 3 4 5 6 7 8 9 +-----+------------------------------------------+ |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 ;
运行结果：
1 2 3 4 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版本中，如下：
1 2 3 4 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，创建隐藏索引，
示例代码：
1 2 3 4 5 6 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' ;
运行结果：
1 2 3 4 5 +--+-----------+--------------+----------+----+-------------+----+-------+----+----+--------+-----------+ |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' ;
运行结果：
1 2 3 4 5 +--+-----------+--------------+----------+----+-------------+----+-------+-----+----+--------+-----+ |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;
运行结果：
1 2 3 4 5 6 7 8 9 10 +----------+ |student_id| +----------+ |48208 | |89247 | |183502 | |105262 | |95296 | 【部分运行结果略】
如果student_id字段上有索引，示例代码：
1 2 SELECT DISTINCT (student_id)FROM student;
运行结果：
1 2 3 4 5 6 7 8 9 10 +----------+ |student_id| +----------+ |1 | |2 | |3 | |4 | |5 | 【部分运行结果略】
居然还排序了，这是因为这时候我们利用了索引，而索引就是有序的。
1 2 3 EXPLAIN SELECT DISTINCT (student_id)FROM student;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+-----+-------------+----------+-------+----+------+--------+------------------------+ |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连接的字段，需要建立索引，而且该字段在多张表中的数据类型必须一致。
没创建索引的话，我们需要全表扫描，具体看执行计划，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM student JOIN course ON student.course_id = course.course_id WHERE student.name = 'EuiFEg' ;
运行结果：
1 2 3 4 5 6 +--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+------------------------------------------+ |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);
1 2 3 4 5 EXPLAIN SELECT *FROM student JOIN course ON student.course_id = course.course_id WHERE student.name = 'EuiFEg' ;
运行结果：
1 2 3 4 5 6 +--+-----------+-------+----------+----+-------------+-------------+-------+------------------+-----+--------+-----------+ |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字段创建索引的话，这时候两张表都有索引，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM student JOIN course ON student.course_id = course.course_id WHERE student.name = 'EuiFEg' ;
运行结果：
1 2 3 4 5 6 +--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----------+ |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条件去除的话，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM student JOIN course ON student.course_id = course.course_id
运行结果：
1 2 3 4 5 6 +--+-----------+-------+----------+----+-------------+-------------+-------+-------------------+-------+--------+-----+ |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.结构》 ，我们讨论了索引的结构，根据这个，我们就很容易理解为什么占用字节较小的字段更适合创建索引。
这也是为什么主键占用的空间小一些好，也是为什么TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不允许作为主键，而且主键，不仅仅存在于聚簇索引中，在二级索引中也会有。
对于较长的字段建立前缀索引
那么，如果一个字段确实较长，而且确实需要创建索引，怎么办呢？
我们可以通过截取宇段的前面一部分内容建立索引，这个就叫前缀索引。
那么，问题来了，截取多少呢？截取多了，达不到节省索引存储空间的目的；截取少了，重复内容太多，字段的散列度会降低，索引效果有限。
假设，存在一张表如下：
1 2 3 4 create table shop( address varchar (120 ) not null );
我们可以通过count(distinct left(列名, 索引长度))/count(*)，来统计其区分度，示例代码：
1 2 3 4 5 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 1 1
一般超过33 % 33\% 3 3 %
在根据区分度，确定索引的长度后，我们就可以建立前缀索引了，示例代码：
1 alter table shop add index (address(20 ));
有些资料会说，前缀索引，会影响排序的准确性。 这个是不对的，前缀索引，不会影响排序的准确性，但会影响排序的效率。
我们可以看个例子。
首先，创建表，示例代码：
1 2 3 4 5 6 7 8 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
然后插入数据，示例代码：
1 2 3 4 5 6 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_prefixORDER BY c2;
运行结果：
1 2 3 4 5 6 7 8 9 10 +--+-----+ |c1|c2 | +--+-----+ |6 |a0000| |1 |a0001| |2 |a0002| |3 |a0003| |4 |a0004| |5 |a0005| +--+-----+
我们看到，排序的准确性，并没有受到影响。
查看执行计划，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM test_prefixORDER BY c2;
运行结果：
1 2 3 4 5 +--+-----------+-----------+----------+----+-------------+----+-------+----+----+--------+--------------+ |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，排序的效率会受到影响。
再举一个例子，现在有一个市的公民信息系统，注意是一个市的，根据我国的身份证编码规则，这些身份证的前六位绝大部分是一样的。
1 2 3 select field_listfrom twhere id_card = reverse ('【输入的身份证号】' );
这样效率就能高很多了。
联合索引的创建
联合索引，是为多个字段建立索引，那么这时候先后顺序就有讲究了。
区分度高的列放前面
使用频繁的列放前面
联合索引优于单值索引
GROUP BY的列放在ORDER BY的列的前面
区分度高的列放前面
例如，假如在student表中，student_id比course_id的区分度更高的话，那么针对如下的SQL
1 2 3 4 SELECT *FROM studentWHERE student_id = 10013 AND course_id = 100 ;
建立联合索引的时候，应该student_id在course_id的前面。
那么，我们WHERE条件需要区分度高的写前面吗？
使用频繁的列放前面
但是，对于如下的SQL
1 2 3 SELECT *FROM studentWHERE course_id = 100 ;
不会利用student_id在course_id的前面的联合索引，我们可以看看执行计划，示例代码：
1 2 3 EXPLAIN SELECT *FROM studentWHERE course_id = 100 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+-------+--------+-----------+ |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进行查询，看执行计划，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE student_id = 10013 AND course_id = 100 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+----------------------------+-------------+-------+-----+----+--------+-----------+ |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。
但，如果我们建立联合索引的话，效率会更高，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE student_id = 10013 AND course_id = 100 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+------------------------+------------------------+-------+-----------+----+--------+-----+ |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 6 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)。
再举一个例子，假设存在一张表，如下：
1 2 3 4 5 6 7 8 9 10 11 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列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处，甚至会因为优化器的选择最佳方式，影响效率。
不要创建重复索引
假设存在一张表如下：
1 2 3 4 5 6 7 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进行查找，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key_part2 = 'Oefqm' and key_part3 = 'nFloOaMJjS'
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+ |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，且有索引。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE student.name LIKE 'abc%' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+ |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| +--+-----------+-------+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
这个没问题。
但，假如我们在字段上，添加函数呢？示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE LEFT (student.name, 3 ) = 'abc' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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，且有索引。
对列进行了计算，示例代码：
1 2 3 4 EXPLAIN SELECT id , stuno, NAME FROM studentWHERE stuno + 1 = 900001 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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，且有索引。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE name = 123 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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，说明进行了全表扫描。
但，如果我们对查询条件加上单引号呢？示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE name = '123' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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发生了类型转换，索引失效。
加上单引号去查询，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE stuno = '100009' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+-----+ |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)，进行查找，示例代码：
1 2 3 4 5 6 EXPLAIN SELECT *FROM studentWHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+ |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没用上。
如果我们将范围查询条件放置语句最后，示例代码：
1 2 3 4 5 6 EXPLAIN SELECT *FROM studentWHERE student.age = 30 AND student.name = 'abc' AND student.classId > 20 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+-----+--------------------+--------------------+-------+----+-----+--------+---------------------+ |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);
不等于
不等于，包括!=和<>，会导致索引失效。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE name != 'abc' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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，只能全表扫描了。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE age IS NOT NULL ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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| +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE age IS NULL ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+-------+-------+-----+----+--------+---------------------+ |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"非索引的列了，反正是要全表扫描，也不折腾用索引了。示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM studentWHERE age = 10 OR classid = 100 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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不会，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM studentWHERE age = 10 AND classid = 100 ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+-------+-------+-----+-----+--------+-----------+ |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的条件，两边都有索引呢？
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM studentWHERE age = 10 OR name = 'Abel' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+-----------+----------------+----------------+-------+----+-----+--------+------------------------------------------+ |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以通配符%开头
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM studentWHERE name like '%ab' ;
运行结果：
1 2 3 4 5 +--+-----------+-------+----------+----+-------------+----+-------+----+------+--------+-----------+ |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 studentWHERE id = 135940 ;
1 SHOW STATUS LIKE 'last_query_cost' ;
运行结果：
1 2 3 4 5 +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 1.000000 | +-----------------+----------+
解释说明：Value是所需要读取的页的数量。
示例代码：
1 2 3 SELECT *FROM studentWHERE id BETWEEN 0 AND 100000 ;
1 SHOW STATUS LIKE 'last_query_cost' ;
运行结果：
1 2 3 4 5 +-----------------+--------------+ | Variable_name | Value | +-----------------+--------------+ | Last_query_cost | 41344.253201 | +-----------------+--------------+
开启慢查询日志
作用
我们可以通过慢查询日志，定位慢SQL。
参数
slow_query_log
首先，我们我通过slow_query_log，查看慢查询日志是否已经开启，示例代码：
1 SHOW VARIABLES LIKE '%slow_query_log'
运行结果：
1 2 3 4 5 +--------------+-----+ |Variable_name |Value| +--------------+-----+ |slow_query_log|ON | +--------------+-----+
还可以查看慢查询日志的路径，示例代码：
1 SHOW VARIABLES LIKE '%slow_query_log%'
运行结果：
1 2 3 4 5 6 +-------------------+-------------------------+ |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%' ;
运行结果：
1 2 3 4 5 +---------------+---------+ |Variable_name |Value | +---------------+---------+ |long_query_time|10.000000| +---------------+---------+
需要注意的是，long_query_time，既是GLOBAL级别的，又是SESSION级别的。所以，如果需要修改的话，建议GLOBAL和SESSION都改。
修改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%'
运行结果：
1 2 3 4 5 +----------------------+-----+ |Variable_name |Value| +----------------------+-----+ |min_examined_row_limit|0 | +----------------------+-----+
持久化
需要注意的是，上述都是临时的，MySQL重启就失效了，如果想持久化的，可以修改配置文件。my.cnf，在[mysqld]下新增long-query_time、slow-query-log和slow_query_1og_file，然后重启MySQL服务器。
1 2 3 4 5 [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%' ;
运行结果：
1 2 3 4 5 +-------------+-----+ |Variable_name|Value| +-------------+-----+ |Slow_queries |0 | +-------------+-----+
删除慢查询日志
我们可以通过命令mysqladmin flush-logs来重新生成查询日志文件，以这种方式删除慢查询日志。
1 mysaladmin -uroot -p flush-logs slow
有些资料说通过rm命令即可，我担心rm之后，文件不会被恢复。
分析慢查询日志
慢查询日志分析工具是mysqldumpslow。
查看帮助
查看mysqldumpslow的帮助信息。
示例代码：
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 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
运行结果：
1 2 3 4 5 6 7 8 9 10 11 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_deletedFROM sys.schema_index_statisticsWHERE table_schema = 'e' ;
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 +------------+-------------+-------------+------------+------------+ |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 | +------------+-------------+-------------+------------+------------+
表相关
查询表的访问量
1 2 3 4 select table_schema, table_name, sum (io_read_requests + io_write_requests) as iofrom sys.schema_table_statisticsgroup by table_schema, table_nameorder by io desc ;
查询占用bufferpool较多的表
1 2 3 4 select object_schema, object_name, allocated, data from sys.innodb_buffer_stats_by_tableorder by allocatedlimit 10 ;
查看表的全表扫描情况
1 2 3 select *from sys.statements_with_full_table_scanswhere db = 'e' ;
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 +-----------------------------------------------------------------+--+----------+-------------+-------------------+------------------------+-----------------+---------+-------------+-------------+-----------------+--------------------------+--------------------------+----------------------------------------------------------------+ |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_analysisorder by exec_count desc ;
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 +---------+----------+-----------------------------------------------------------------+ |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_sortinglimit 1 ;
运行结果：
1 2 3 4 5 +--+----------+--------------------------+--------------------------+-----------------------------------------------------------------+ |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
示例代码：
1 2 3 4 5 select db, exec_count, tmp_tables, tmp_disk_tables, query from sys.statement_analysiswhere tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables + tmp_disk_tables) desc ;
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 +----+----------+----------+---------------+-----------------------------------------------------------------+ |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的文件
示例代码：
1 2 3 4 select file , avg_read, avg_write, avg_read + avg_write as avg_iofrom sys.io_global_by_file_by_bytesorder by avg_readlimit 10 ;
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 +---------------------------------+----------+----------+------+ |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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 +--------------------------------+----------+ | 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 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+ |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
不论我们的查询语句有多复杂，包含了多少个表，到具体的步骤，一定是单表访问的。table就是表名(有时不是真实的表名字，可能是简称)。
示例代码：
1 2 3 EXPLAIN SELECT *FROM s1;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+ |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 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+ |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如果相同，则认为是一组，从上往下顺序执行。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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 | +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+ |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，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a' ;
运行结果：
1 2 3 4 5 6 +--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+ |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，示例代码：
1 2 3 4 5 6 EXPLAIN SELECT *FROM s1UNION SELECT *FROM s2;
运行结果：
1 2 3 4 5 6 7 +----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+ |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，示例代码：
1 2 3 4 5 6 EXPLAIN SELECT *FROM s1UNION ALL SELECT *FROM s2;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+ |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？
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a' );
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+------+-------------+--------+-------+---------+-----+--------+----------------------------------+ |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看一下。
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 *************************** 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;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+ |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，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------------------------+ |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。
示例代码：
1 2 3 4 5 6 EXPLAIN SELECT *FROM s1UNION SELECT *FROM s2;
运行结果：
1 2 3 4 5 6 7 +----+------------+----------+----------+----+-------------+----+-------+----+-----+--------+---------------+ |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，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a' ;
运行结果：
1 2 3 4 5 6 +--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+ |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，派生表
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM (SELECT key1, count (*) as c FROM s1 GROUP BY key1) AS derived_s1where c > 1 ;
运行结果：
1 2 3 4 5 6 +--+-----------+----------+----------+-----+-------------+--------+-------+----+-----+--------+-----------+ |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。
我们可以创建一个分区表，来看看。
1 2 3 4 5 6 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，看看，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM user_partitionsWHERE id > 200 ;
运行结果：
1 2 3 4 5 +--+-----------+---------------+----------+-----+-------------+-------+-------+----+----+--------+-----------+ |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一下。
1 2 3 4 CREATE TABLE t( i int ) Engine = MyISAM;
1 2 INSERT INTO tVALUES (1 );
1 2 3 EXPLAIN SELECT *FROM t;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+------+-------------+----+-------+----+----+--------+-----+ |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;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+ |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，示例代码：
1 2 3 4 CREATE TABLE tt( i INT ) ENGINE = InnoDB
1 2 INSERT INTO ttVALUES (2 );
1 2 3 EXPLAIN SELECT *FROM tt;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+ |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。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE id = 100002 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+ |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，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE id = 1 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------+ |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 Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression s1 0 idx_key_part 1 key_part1 A 99640 NULL NULL YES BTREE YES NULL s1 0 idx_key_part 2 key_part2 A 99640 NULL NULL YES BTREE YES NULL s1 0 idx_key_part 3 key_part3 A 99640 NULL NULL YES BTREE YES NULL
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key_part1 = 'JgCshJKFXV' and key_part2 = 'Oefqm' and key_part3 = 'nFloOaMJjS'
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+------------+-------+-----------------+----+--------+-----+ |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。示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key_part1 = 'JgCshJKFXV' and key_part2 = 'Oefqm'
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+ |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。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2 ON s1.id = s2.id;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+ |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。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' OR key1 IS NULL ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----------+-------------+--------+-------+-----+----+--------+---------------------+ |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，使用索引合并的方式来执行，即可以对索引到的内容进行合并。
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' OR key3 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+ |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吗？
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' AND key3 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+ |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。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a' ;
运行结果：
1 2 3 4 5 6 +--+------------------+-----+----------+---------------+----------------+-------+-------+----+-----+--------+-----------+ |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)。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a' ;
运行结果：
1 2 3 4 5 6 +--+------------------+-----+----------+--------------+-----------------+--------+-------+----+-----+--------+-----------+ |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
只检索给定范围的行，使用一个索引来选择行。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 IN ('a' , 'b' , 'c' );
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+ |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| +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 > 'a' AND key1 < 'b' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+ |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：可以使用索引覆盖，但需要扫描全部索引记录，示例代码：
1 2 3 4 EXPLAIN SELECT key_part2FROM s1WHERE key_part3 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+------------+-------+----+-----+--------+------------------------+ |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;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----+ |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：最后选择的索引。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 > 'z' AND key3 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+ |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) 可以为NULLkey_len的长度计算：
10 × 单位字节数 + 1 + 2 10 \times \text{单位字节数} + 1 + 2 1 0 × 单位字节数 + 1 + 2
在UTF-8编码中，单位字节数为3 3 3 GBK编码中，单位字节数为2 2 2 《基于Java的后端开发入门：5.IO流》 也有讨论。 1 1 1 NULL(因为可以为NULL)。2 2 2
varchar(10) 不可以为NULLkey_len的长度计算：
10 × 单位字节数 + 2 10 \times \text{单位字节数} + 2 1 0 × 单位字节数 + 2
char(10) 可以为NULLkey_len的长度计算：
10 × 单位字节数 + 1 10 \times \text{单位字节数} + 1 1 0 × 单位字节数 + 1
因为char(10)是固定长度，所以不需要专门用两个字节来记录长度。
再来看几个具体的例子。
假设id为int类型，且是主键，示例代码：
1 EXPLAIN SELECT * FROM s1 WHERE id = 100005 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+ |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类型，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key2 = 100126 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+-----+----+--------+-----+ |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 5 5 key2可以为空，所以多占一个字节。
假设key1为VARCHAR(100)，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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 303 3 0 3 VARCHAR(100)，UTF8编码，就是占据的字节数：100 × 3 = 300 100 \times 3 = 300 1 0 0 × 3 = 3 0 0 303 303 3 0 3
在联合索引中的应用
在联合索引中，我们可以通过key_len检查是否充分利用了索引。
假设key_part1、key_part2和key_part2都是varchar(100)，且可以为NULL。idx_key_part(key_part1,key_part2,key_part3)。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key_part1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----+ |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，说明只用了联合索引的第一列。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key_part1 = 'a' AND key_part2 = 'b' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+ |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，说明只用了联合索引的前两列。
示例代码：
1 2 3 4 5 6 EXPLAIN SELECT *FROM s1WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+------------+-------+-----------------+----+--------+-----+ |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进行查询呢，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key_part1 = 'a' AND key_part3 = 'c' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+---------------------+ |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
当使用普通索引列进行等值匹配时，与索引列进行等值匹配的对象信息。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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，表示常量。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2 ON s1.id = s2.id;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+------+-------------+-------+-------+-------+-----+--------+-----+ |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。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2 ON s2.key1 = UPPER (s1.key1);
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+--------+-------+----+-----+--------+---------------------+ |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' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+ |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，某个表经过搜索条件过滤后剩余记录条数的百分比。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 > 'z' AND common_field = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+----------------------------------+ |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，额外信息。
但有时候会有和优化非常有关的信息。
No tables used
No tables used：没用到表。
示例代码：
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+ |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，不可能的过滤。
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE 1 != 1 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+ |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呢？
假设common_field字段上没有任何索引，那么这时候就会做全表扫描，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE common_field = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+-----------+ |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上有索引，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' AND common_field = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+ |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上有索引，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1WHERE key1 = 'a'
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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，示例代码：
1 2 3 4 EXPLAIN SELECT key1FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+ |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。
1 2 3 4 EXPLAIN SELECT key1, id FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+ |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| +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
示例代码：
1 2 3 4 EXPLAIN SELECT key1, id , key2FROM s1WHERE key1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+ |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。
1 2 3 4 EXPLAIN SELECT id ,key_part1,key_part2,key_part3FROM s1WHERE key_part1 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----------+ |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，如下：
1 2 3 4 SELECT *FROM s1WHERE key1 > 'z' AND key1 LIKE '%a' ;
其中key1 > 'z'可以使用索引，但是key1 LIKE '%a'，无法使用索引。key1 > z，获取其二级索引的记录，然后回表，再筛选出key1 LIKE '%a'的。key1 > z的二级索引记录，然后对二级索引记录进行筛查，找出其中key1 LIKE '%a'的，最后进行回表。MySQL把这个改进称之为索引条件下推(Index Condition Pushdown)
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 > 'z' AND key1 LIKE '%a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+---------------------+ |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的内存块来加快查询速度，也就是所谓的"基于块的嵌套循环算法"。
示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+------------------------------------------+ |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，表示不存在。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL ;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+--------+-------+---------+-----+--------+-----------------------+ |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(...)，说明计划使用索引合并的方式来执行，即可以对索引到的内容进行合并。
示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1WHERE key1 = 'a' OR key3 = 'a' ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----------+-----------------+-----------------+-------+----+----+--------+-------------------------------------------+ |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进行排序，就会利用文件排序，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1ORDER BY common_fieldLIMIT 10 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+--------------+ |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进行排序，就不会利用文件排序，示例代码：
1 2 3 4 5 EXPLAIN SELECT *FROM s1ORDER BY key1LIMIT 10 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----+ |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_fieldFROM s1;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+ |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 key1FROM s1;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+ |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，示例代码：
1 2 3 4 EXPLAIN SELECT common_field, COUNT (*) AS amountFROM s1GROUP BY common_field;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+-----+--------+---------------+ |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，示例代码：
1 2 3 4 EXPLAIN SELECT key1, COUNT (*) AS amountFROM s1GROUP BY key1;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+-----+-------------+--------+-------+----+-----+--------+-----------+ |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，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM s1LIMIT 0 ;
运行结果：
1 2 3 4 5 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------+ |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。
示例代码：
1 2 3 4 5 EXPLAIN FORMAT = JSON SELECT *FROM s1 INNER JOIN s2 s on s1.key2 = s.key2 WHERE s1.common_field = 'a'
运行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 { "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版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。
示例代码：
1 2 3 4 5 EXPLAIN FORMAT =TREESELECT *FROM s1 INNER JOIN s2 s on s1.key2 = s.key2 WHERE s1.common_field = 'a'
运行结果：
1 2 3 4 -> 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
查询优化
关联查询
现象
假设存在表如下：
1 2 3 4 5 CREATE TABLE t( id INT UNSIGNED NOT NULL AUTO_INCREMENT, card INT UNSIGNED NOT NULL , PRIMARY KEY (id ) );
1 2 3 4 5 CREATE TABLE b( bookid INT UNSIGNED NOT NULL AUTO_INCREMENT, card INT UNSIGNED NOT NULL , PRIMARY KEY (bookid) );
其中t表有记录20条，b表有记录40条。
外连接
我们以左外连接为例。
如果没有任何索引，EXPLAIN，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM t LEFT JOIN b on t.card = b.card;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+------------------------------------------+ |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);
1 2 3 4 EXPLAIN SELECT *FROM t LEFT JOIN b on t.card = b.card;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+ |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);
1 2 3 4 EXPLAIN SELECT *FROM t LEFT JOIN b ON t.card = b.card;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+ |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 2 3 4 EXPLAIN SELECT *FROM t LEFT JOIN b ON t.card = b.card;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------------------------+ |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表上有索引，我们采取内连接的方式，示例代码：
1 2 3 4 EXPLAIN SELECT *FROM t INNER JOIN b ON t.card = b.card;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+----+-------------+----+-------+--------+----+--------+-----------+ |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);
1 2 3 4 EXPLAIN SELECT *FROM t INNER JOIN b ON t.card = b.card;
运行结果：
1 2 3 4 5 6 +--+-----------+-----+----------+-----+-------------+---+-------+--------+----+--------+-----------+ |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 N N O ( N ) O(N) O ( N )
对于被驱动表，我们有索引，假设被驱动表的行数是M M M 2 × O ( log 2 M ) 2 \times O(\log_2 M) 2 × O ( log 2 M ) 2 2 2 2 2 2
所以，整体的时间复杂度是
N + N × 2 × log 2 M N + N \times 2 \times \log_2 M
N + N × 2 × log 2 M
那么，现在问，为了整体的时间复杂度更小，且N N N M M M N N N M M M M 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 N = 11 N = 1 1 M = 19 M = 19 M = 1 9
11 + 11 × 2 × log 2 19 11 + 11 \times 2 \times \log_2 19
1 1 + 1 1 × 2 × log 2 1 9
但，如果b表驱动t表的话，N = 19 N = 19 N = 1 9 M = 11 M = 11 M = 1 1
19 + 19 × 2 × log 2 11 19 + 19 \times 2 \times \log_2 11
1 9 + 1 9 × 2 × log 2 1 1
这就是为什么要用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中了
分页查询
优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容，示例代码：
1 2 3 4 SELECT *FROM student t, (SELECT id FROM student ORDER BY id LIMIT 2000000 ,10 ) a WHERE t.id = a.id;
优化思路二
该方案适用于主键严格自增的表，示例代码：
1 2 3 4 SELECT *FROM studentWHERE id > 2000000 LIMIT 10 ;
特别注意！如果主键不是严格自增，这种方法不适合。
覆盖索引
关于覆盖索引，我们在上文讨论EXPLAIN的Extra的Using index中，已经讨论过了。
避免二次查询(回表)
可以把随机IO变成顺序IO加快查询效率
缺点：索引字段的维护有代价，会影响更新数据的效率。
IN和EXISTS
什么时候选择IN，什么时候选择EXISTS？
对于IN，形如：
1 2 3 SELECT *FROM AWHERE col in (SELECT col FROM B)
其规则是，子查询驱动外部，所以这时候B小一些会更好。
对于EXISTS，形如：
1 2 3 SELECT *FROM AWHERE EXISTS (SELECT col FROM B WHERE B.col = A.col)
这其实是一个相关子查询，在《2.DQL(SELECT)》 ，我们讨论过，首先要把外部表中的相关数据送进子查询。A表驱动B表，所以A小一些更好。
LIMIT 1
针对会扫描全表的SQL语句，如果可以确定结果集只有一条，那么可以加上LIMIT 1的时候，这样会加快查询速度。
其它优化
软件
选择适合的数据库。除了关系型数据库外，还有很多非关系型数据库。《1.概述和工具准备》 有简单的讨论。)
优化表设计
表结构要尽量遵循三范式的原则。这样可以让数据结构更加清晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生。
如果查询应用比较多，尤其是需要进行多表关联查询的时候，可以采用反范式进行优化，即采用空间换时间的方式，通过增加冗余字段提高查询的效率。
表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。
一般来说，如果字段可以采用数值类型就不要采用字符类型；
针对字符类型来说，当确定字符长度固定时，就可以采用CHAR类型：当长度不固定时，通堂采用VARCHAR类型。
优化查询，即我们上文讨论的查询优化。
优化逻辑，在确定了查询优化之后，可以对索引进行优化，增加、修改或删除部分索引。
库级优化
读写分离。
数据分片。
硬件
服务器的硬件性能直接决定着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数据库的最大数量。
