Mysql高级 索引优化

Mysql逻辑架构

Mysql与其他数据库相比有点与众不同，他的架构可以在多种不同的场景中应用并发挥作用，主要体现在存储引擎的架构上，插件式的存储引擎结构将查询处理和其他的系统任务以及数据的存储提取分离。
数据库是C/S架构的，client发送命令请求，客户端负责响应，这种架构可以根据业务额的需求和实际需要选择合适的存储引擎。
client：提供连接MySQL服务器功能的常用工具集
Server :MySQL实例，真正提供数据存储和数据处理功能的MySQL服务器进程。

分层设计

自顶向上依次为：连接层、服务层、引擎层、存储层
连接层：负责客户端与服务端的连接，并拿到数据库的操作语句，但并不处理。
服务层：
（1）提供各种用户使用的接口（增删改查）
（2）提供sql优化器（Mysql Query Optimizer），如果sql语句过于复杂，优化器会自动转化为一个较为简单而又等价的写法，对性能产生一定好处，但有弊端，有可能造成执行结果与本意不同
引擎层：（1）提供各种存储数据的格式：常见的有InnoDB默认，MyISAM等
（2） 有一个面试题 InnoDB和MyISAM（麦爱色目）的主要区别？
InnoDB: 事务优先 防止一些并发操作造成的混乱 适合高并发操作
行锁：一次锁一行 适合高并发
表锁：一次锁一张表数据 不适合高并发
存储层：存储数据，要是将数据存储在运行于裸设备的文件系统上，并完成存储引擎的交互

MyISAM和InnoDB的区别

InnoDB的四个特性：插入缓冲，二次写，自适应哈希索引、预读

如何选择

• 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
• 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
• 系统奔溃后，MyISAM恢复起来更困难，能否接受；

阿里巴巴和淘宝用哪一个存储引擎

把存储引擎换了，阿里巴巴大部分使用mysql数据其实用的是perconn的原型加以修改

流程

client发出数据库请求 第一步与数据库连接，连接完毕后，将查询请求给服务层，服务层对查询进行优化，将优化结果给引擎层，选择当前数据库的引擎，选择完引擎后，将数据交给存储层来存储数据。

索引优化分析

性能下降sql慢原因

性能下降sql慢（执行时间长，等待时间长）
（1）查询语句写的烂
（2）索引失效
索引分为单值索引和复合索引
单值索引：每个索引只包含单个列一个表中可以有多个单列索引
复合索引：一个索引包含多个列
（3）关联查询太多join
（4）服务器调优及各个参数适（缓冲、线程数等）

sql执行顺序

语法：
select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引，条目数;  ⑨

7种join

内连接 inner join
外连接包括：左连接 left join 右连接 right join外联
左连接–B
右连接-A
全连接
全连接-中间

索引

含义：

索引是排好序的快速查找的数据结构，是为了解决SQL数据过于庞大引起效率下降的优化方法，可以帮助我们快速的进行数据的查找。

索引的选择性是指索引列中不同值的数目与表中记录数的比
主键当做索引
详解图：

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往都是以索引文件的形式存储在磁盘上
• 我们平常所说的索引是B树索引（多路搜索树，并不一定是二叉树），其中聚集索引，次要索引复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称为索引。除了B+树索引还有哈希索引

索引的优势：

• 类似大学图书馆建书目索引，提高数据的检索效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗

索引的劣势：

• 实际上索引是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引占空间的
• 索引虽然提高了查询速度，同时会降低更新表的速度，比如对标的insert,update,delete。因为更新表时mysql不仅要保存数据，还要保存索引文件每次更新索引列的字段，都会调整因为更新到来的键值变化后的索引信息。
  如果mysql有大量表 ，就需要花费时间建立最优秀的索引

索引分类：

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列
  基本语法：
  

create index IDX_testNoPK_Name on testNoPK (name);

mysql索引结构：（B+树详解）

B+树索引

由磁盘加载到内存 发生一次IO

BTRee索引
Hash索引
full-text全文索引
R-Tree索引

参考博文链接https://blog.csdn.net/qq_21579045/article/details/99702766

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
where条件里用不到的字段不创建索引
单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不需要创建索引

• 表记录太少

• 经常增删改的表
  Why：提高了查询速度，同时却会降低更新表的速度，如对表进行
  INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  

性能分析

MySQL Query Optimizer查询优化器

MySQL常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain（查看执行计划）

简介

使用Explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句。分析你的查询语句或是表结构的性能瓶颈。

• 通过EXPLAIN，我们可以分析出以下结果：
  （1）表的读取顺序
  （2）数据读取的操作类型
  （3）哪些索引可以使用
  （4）哪些索引被实际使用
  （5）表之间的引用
  （6）每张表有多少行被优化器查询

使用方式如下：EXPLAIN +SQL语句

EXPLAIN SELECT * FROM t1

执行计划包含的信息

执行计划各字段含义

• id :select查询的序列号，表示查询汇总select语句或操作表的顺序
  id 的结果包含3种情况
  （1）id相同，执行顺序由上至下
  
  总结：加载表的顺序如上图的table列所示： t1 t3 t2
  （2） id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  
  执行顺序 t3 t2 t1
  (3)id相同不同，同时存在
  
  id相同：认为是同一组，从上往下顺序执行
  在所有组中，id值越大，优先级越高 越优先执行
  执行顺序 t3 dervied2 t2
  derived 衍生

分析：如上图所示，在id为1时，table显示的是 derived2 ,这里指的是指向id为2的衍生表，即t3表的衍生表。

• select_type：表示查询的类型，主要是区别普通查询，联合查询，子查询等的复杂查询
  
  

• table 表示当前的表

• type 显示的是查询使用了哪种类型
  type包含的类型
  

  从最好到最差的情况：
  
  system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  const :表示通过索引一次就找到了，const用于比较primary key 或者
  unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
  eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
  
  range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  
  index： Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
  
  all ：Full Table Scan 将遍历全表以找到匹配的
  
  一般来说保证查询至少达到range级别，最好能达到ref级别

• possible_keys 和 key
possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及的字段上若存在索引，则该索引被列出，但不一定被查询实际应用
  key 实际上使用的索引
  实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
  
  查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中
  

• key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
  
  key_len越大，查询精度高

• ref 显示索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
  

• rows
  根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数**，也就是说，用的越少越好**
  

• extra 包含不适合在其他列中显式但十分重要的额外信息

（1）Using filesort（九死一生）：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。

（2）Using temporary (十死无生)：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

（3）Using index（发财了）：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where：表明使用了where过滤
Using join buffer：使用了连接缓存。
impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）

覆盖索引：select后要查询的字段刚好和创建的索引字段完全相同
如果使用覆盖索引，一定要注意select列表中只取出需要的列，不可以select*
如果所有字段一起做索引会导致索引文件过大，查询性能下降

案例分析

索引优化

单表索引优化

创建表：
某网站的文章数据表

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

表中的测试数据

SELECT * FROM article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)

查询案例
查询category_id为1且comments 大于1的情况下，views最多的article_id

SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.00 sec)

此时 article 表中只有一个主键索引

SHOW INDEX FROM article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

使用 explain 分析 SQL 语句的执行效率：EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

结论：
type是all 即是最坏的情况
extra里出现了using filesort也是最坏的情况
所以需要优化
开始优化 创建索引
在 category_id 列、comments 列和 views 列上建立联合索引

create index idx_article_ccv on article(category_id, comments, views);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

再次执行查询：type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                                 |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | article | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

分析：
但是我们已经建立了索引，为啥没用呢？
这是因为按照B+Tree索引的工作原理，先排序 category_id，如果遇到相同的 category_id 则再排序comments，如果遇到相同的 comments 则再排序 views。
当comments字段在联合索引里处于中间位置时，因为comments>1条件是一个范围值（所谓 range），MySQL 无法利用索引再对后面的views部分进行检索，即 range 类型查询字段后面的索引无效。

将查询条件中的 comments > 1 改为 comments = 1 ，发现 Use filesort 神奇地消失了，从这点可以验证：范围后的索引会导致索引失效

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| id | select_type | table   | type | possible_keys   | key             | key_len | ref         | rows | Extra       |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

删除刚才创建的 idx_article_ccv 索引

DROP INDEX idx_article_ccv ON article;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

再次创建索引
由于 range 后（comments > 1）的索引会失效，这次我们建立索引时，直接抛弃 comments 列，先利用 category_id 和 views 的联合索引查询所需要的数据，再从其中取出 comments > 1 的数据（我觉着应该是这样的）

create index idx_article_ccv on article(category_id, views);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

再次执行查询：可以看到，type变为了ref，Extra中的Using filesort也消失了，结果非常理想

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 4       | const |    2 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

两表索引优化

两表索引优化分析：主外键
建表

CREATE TABLE IF NOT EXISTS class(
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS book(
	bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(bookid)
);

INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));

INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));

class 表中的测试数据

select * from class;
+----+------+
| id | card |
+----+------+
|  1 |   12 |
|  2 |   13 |
|  3 |   12 |
|  4 |   17 |
|  5 |   11 |
|  6 |    3 |
|  7 |    1 |
|  8 |   16 |
|  9 |   17 |
| 10 |   16 |
| 11 |    9 |
| 12 |   17 |
| 13 |   18 |
| 14 |   16 |
| 15 |    7 |
| 16 |    8 |
| 17 |   19 |
| 18 |    9 |
| 19 |    6 |
| 20 |    5 |
| 21 |    6 |
+----+------+
21 rows in set (0.00 sec)

book 表中的测试数据

select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      1 |   16 |
|      2 |    1 |
|      3 |   17 |
|      4 |    3 |
|      5 |   20 |
|      6 |   12 |
|      7 |   18 |
|      8 |   13 |
|      9 |   13 |
|     10 |    4 |
|     11 |    1 |
|     12 |   13 |
|     13 |   20 |
|     14 |   20 |
|     15 |    1 |
|     16 |    2 |
|     17 |    9 |
|     18 |   16 |
|     19 |   14 |
|     20 |    2 |
+--------+------+
20 rows in set (0.00 sec)

查询
实现两表的连接，连接条件是 class.card = book.card

SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+------+--------+------+
| id | card | bookid | card |
+----+------+--------+------+
|  1 |   12 |      6 |   12 |
|  2 |   13 |      8 |   13 |
|  2 |   13 |      9 |   13 |
|  2 |   13 |     12 |   13 |
|  3 |   12 |      6 |   12 |
|  4 |   17 |      3 |   17 |
|  5 |   11 |   NULL | NULL |
|  6 |    3 |      4 |    3 |
|  7 |    1 |      2 |    1 |
|  7 |    1 |     11 |    1 |
|  7 |    1 |     15 |    1 |
|  8 |   16 |      1 |   16 |
|  8 |   16 |     18 |   16 |
|  9 |   17 |      3 |   17 |
| 10 |   16 |      1 |   16 |
| 10 |   16 |     18 |   16 |
| 11 |    9 |     17 |    9 |
| 12 |   17 |      3 |   17 |
| 13 |   18 |      7 |   18 |
| 14 |   16 |      1 |   16 |
| 14 |   16 |     18 |   16 |
| 15 |    7 |   NULL | NULL |
| 16 |    8 |   NULL | NULL |
| 17 |   19 |   NULL | NULL |
| 18 |    9 |     17 |    9 |
| 19 |    6 |   NULL | NULL |
| 20 |    5 |   NULL | NULL |
| 21 |    6 |   NULL | NULL |
+----+------+--------+------+
28 rows in set (0.00 sec)

使用 explain 分析 SQL 语句的性能，可以看到：驱动表是左表 class 表

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   21 | NULL                                               |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

结论：
type 有 All ，rows 为表中数据总行数，说明 class 和 book 进行了全表检索
即每次 class 表对 book 表进行左外连接时，都需要在 book 表中进行一次全表检索
添加索引：在右表添加索引
在 book 的 card 字段上添加索引

ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY  |            1 | bookid      | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | Y        |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

测试结果：可以看到第二行的type变为了ref，rows也变成了优化比较明显。

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL            |   21 | NULL        |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db01.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)

分析：
这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引

三表索引优化

建表 SQL

CREATE TABLE IF NOT EXISTS phone(
	phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(phoneid)
)ENGINE=INNODB;

INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));

phone 表中的测试数据

select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
|       1 |    7 |
|       2 |    7 |
|       3 |   13 |
|       4 |    6 |
|       5 |    8 |
|       6 |    4 |
|       7 |   16 |
|       8 |    4 |
|       9 |   15 |
|      10 |    1 |
|      11 |   20 |
|      12 |   18 |
|      13 |    9 |
|      14 |    9 |
|      15 |   20 |
|      16 |   11 |
|      17 |   15 |
|      18 |    3 |
|      19 |    8 |
|      20 |   10 |
+---------+------+
20 rows in set (0.00 sec)

查询案例
实现三表的连接查询：

SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+------+--------+------+---------+------+
| id | card | bookid | card | phoneid | card |
+----+------+--------+------+---------+------+
|  2 |   13 |      8 |   13 |       3 |   13 |
|  2 |   13 |      9 |   13 |       3 |   13 |
|  2 |   13 |     12 |   13 |       3 |   13 |
|  8 |   16 |      1 |   16 |       7 |   16 |
| 10 |   16 |      1 |   16 |       7 |   16 |
| 14 |   16 |      1 |   16 |       7 |   16 |
|  8 |   16 |     18 |   16 |       7 |   16 |
| 10 |   16 |     18 |   16 |       7 |   16 |
| 14 |   16 |     18 |   16 |       7 |   16 |
|  7 |    1 |      2 |    1 |      10 |    1 |
|  7 |    1 |     11 |    1 |      10 |    1 |
|  7 |    1 |     15 |    1 |      10 |    1 |
| 13 |   18 |      7 |   18 |      12 |   18 |
| 11 |    9 |     17 |    9 |      13 |    9 |
| 18 |    9 |     17 |    9 |      13 |    9 |
| 11 |    9 |     17 |    9 |      14 |    9 |
| 18 |    9 |     17 |    9 |      14 |    9 |
|  6 |    3 |      4 |    3 |      18 |    3 |
|  4 |   17 |      3 |   17 |    NULL | NULL |
|  9 |   17 |      3 |   17 |    NULL | NULL |
| 12 |   17 |      3 |   17 |    NULL | NULL |
|  1 |   12 |      6 |   12 |    NULL | NULL |
|  3 |   12 |      6 |   12 |    NULL | NULL |
|  5 |   11 |   NULL | NULL |    NULL | NULL |
| 15 |    7 |   NULL | NULL |    NULL | NULL |
| 16 |    8 |   NULL | NULL |    NULL | NULL |
| 17 |   19 |   NULL | NULL |    NULL | NULL |
| 19 |    6 |   NULL | NULL |    NULL | NULL |
| 20 |    5 |   NULL | NULL |    NULL | NULL |
| 21 |    6 |   NULL | NULL |    NULL | NULL |
+----+------+--------+------+---------+------+
30 rows in set (0.00 sec)

使用 explain 分析 SQL 指令：

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   21 | NULL                                               |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

分析：
type 有All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索
Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区

创建索引：
进行 LEFT JOIN ，永远都在右表的字段上建立索引
所以在book表和phone表建立索引

ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY  |            1 | bookid      | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | Y        |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

ALTER TABLE phone ADD INDEX Z (card);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM phone;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| phone |          0 | PRIMARY  |            1 | phoneid     | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| phone |          1 | Z        |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

执行查询

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL            |   21 | NULL        |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db01.class.card |    1 | Using index |
|  1 | SIMPLE      | phone | ref  | Z             | Z    | 4       | db01.book.card  |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
3 rows in set (0.00 sec)

后2行的type都是ref，且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

结论：
用小表驱动大表，在大表上建立索引，在小结果集中遍历全表。

索引失效（应该避免）

建表：

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

staffs 表中的测试数据

select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos     | add_time            |
+----+------+-----+---------+---------------------+
|  1 | z3   |  22 | manager | 2020-08-04 14:42:33 |
|  2 | July |  23 | dev     | 2020-08-04 14:42:33 |
|  3 | 2000 |  23 | dev     | 2020-08-04 14:42:33 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)

staffs 表中的复合索引：name、age、pos

SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

索引实现准则

最佳左匹配法则：带头大哥不能死，中间兄弟不能断

（1）只有带头大哥 name 时

EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

分析：key = index_staffs_nameAgePos 表明索引生效ref = const ：这个常量就是查询时的 ‘July’ 字符串常量
(2)带头大哥 name 带上小弟 age

EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23;
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

分析：key = index_staffs_nameAgePos 表明索引生效，ref = const,const：两个常量分别为 ‘July’ 和 23
（3）带头大哥 name 带上小弟 age ，小弟 age 带上小小弟 pos

EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

分析：
key = index_staffs_nameAgePos 表明索引生效
ref = const,const,const ：三个常量分别为 ‘July’、23 和 ‘dev’
（4）带头大哥 name 挂了

EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

分析：key = NULL 说明索引失效ref = null 表示 ref 也失效
（5）带头大哥 name 没挂，小弟 age 跑了

EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

分析：key = index_staffs_nameAgePos 说明索引没有失效
ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效

在索引列上进行计算，会导致索引失效，进而转向全表扫描

操作前

EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

对带头大哥 name 进行操作：使用 LEFT 函数截取子串

EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

分析：
key = NULL 表明索引失效，type = ALL 表明进行了全表扫描

范围之后全失效

操作前

EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

分析：type = ref 表示非唯一索引扫描，SQL 语句将返回匹配某个单独值的所有行。key_len = 140 表明表示索引中使用的字节数
操作后：将age改为范围匹配

EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

分析：
type = range表示范围扫描 ，key = index_staffs_nameAgePos 表示索引并没有失效，key_len = 78 ，ref = NULL 均表明范围搜索使其后面的索引均失效

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），**减少 select ***

EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra                    |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)

覆盖索引的写法：Extra = Using where; Using index ，Using index 表示使用索引列进行查询，将大大提高查询的效率

EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

覆盖索引中包含 range 条件：type = ref 并且 Extra = Using where; Using index ，虽然在查询条件中使用了 范围搜索，但是由于我们只需要查找索引列，所以无需进行全表扫描

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM staffs WHERE name != 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM staffs WHERE name <> 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

分析：key = null 表示索引失效，rows = 3 表示进行了全表扫描

is null，is not null 也无法使用索引

EXPLAIN SELECT * FROM staffs WHERE name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM staffs WHERE name is not null;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

分析：is null，is not null 会导致索引失效：key = null 表示索引失效

like % 写最右不失效

操作前 staffs 表的索引关系

SHOW INDEX from staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

（1）like % 写在左边的情况

EXPLAIN SELECT * FROM staffs WHERE name like '%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM staffs WHERE name like '%July%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

分析：type = All ，rows = 3 表示进行了全表扫描 key = null 表示索引失效
（2）like % 写在右边的情况

EXPLAIN SELECT * FROM staffs WHERE name like 'July%';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

分析：key = index_staffs_nameAgePos 表示索引未失效

解决【like ‘%str%’ 】索引失效的问题：覆盖索引

建立表：

CREATE TABLE `tbl_user`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) DEFAULT NULL,
	`age`INT(11) DEFAULT NULL,
	`email` VARCHAR(20) DEFAULT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

查看表中数据：

select * from tbl_user;
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | 1aa1 |   21 | a@163.com |
|  2 | 2bb2 |   23 | b@163.com |
|  3 | 3cc3 |   24 | c@163.com |
|  4 | 4dd4 |   26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)

创建索引：在 tbl_user 表的 name 字段和 age 字段创建联合索引

CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

SHOW INDEX FROM tbl_user;
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_user |          0 | PRIMARY          |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_user |          1 | idx_user_nameAge |            1 | name        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| tbl_user |          1 | idx_user_nameAge |            2 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

测试覆盖索引
只要查询的字段能和覆盖索引扯得上关系，并且没有多余字段，覆盖索引就不会失效
下面的都不会失效

EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';

下面的都会失效，只要有多余字段，覆盖索引就会失效

EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_user | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_user | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

字符串不加单引号索引失效

正常操作，索引不失效

SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

explain select * from staffs where name='2000';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

如果字符串忘记写‘’，那么mysql会为我们进行隐式的类型转换，但凡进行了类型准换，索引就会失效

explain select * from staffs where name=2000;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

少用or，用它连接时会索引失效

SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

explain select * from staffs where name='z3' or name = 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

索引优化面试题目

建立表

create table test03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

测试数据

select * from test03;
+----+------+------+------+------+------+
| id | c1   | c2   | c3   | c4   | c5   |
+----+------+------+------+------+------+
|  1 | a1   | a2   | a3   | a4   | a5   |
|  2 | b1   | b2   | b3   | b4   | b5   |
|  3 | c1   | c2   | c3   | c4   | c5   |
|  4 | d1   | d2   | d3   | d4   | d5   |
|  5 | e1   | e2   | e3   | e4   | e5   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)

test03 表中的索引

SHOW INDEX FROM test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 |          0 | PRIMARY          |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

问题：我们创建了复合索引idx_test03_c1234，根据以下SQL分析下索引使用情况？

即全值匹配，下面索引都生效
1234

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
1 row in set (0.00 sec)

4321生效

EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
1 row in set (0.00 sec)

范围之后全失效？c3 列使用了索引进行排序，并没有进行查找，导致 c4 无法用索引进行查找 c4失效

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'; 
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql 优化器进行了优化，所以我们的索引都生效了，在 c4 时进行了范围搜索

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3'; 
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

索引两个功能：查找和排序c3 列将索引用于排序，而不是查找，c4 列没有用到索引

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

c3直接用于排序 不用查找

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

因为索引建立的顺序和使用的顺序不一致，导致 mysql 动用了文件排序，直接从4楼开始排，Using filesort 就要知道：此句 SQL 必须优化

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
1 row in set (0.00 sec)

只用 c1 一个字段索引，但是c2、c3用于排序，无filesort，中间没有断，

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

出现了filesort，我们建的索引是1234，它没有按照顺序来，32颠倒了

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                                              |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

用c1、c2两个字段索引，但是c2、c3用于排序，无filesort

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

和 c5 没关系

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

注意查询条件 c2=‘a2’ ，我都把 c2 查出来了（c2 为常量），不需要将c2排序，所以没有产生 filesort

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

顺序为 1 2 3 ，没有产生文件排序

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                                                               |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+
1 row in set (0.01 sec)

结论：

group by 基本上都需要进行排序，但凡使用不当，会有临时表产生
定值为常量、范围之后失效，最终看排序的顺序

索引失效总结

like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量

举例:
= ‘kk’ ：key_len = 93 ，请记住此参数的值，后面有用

----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 93      | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

like ‘kk%’ key_len = 93 ，和上面一样，说明 c1 c2 c3 都用到了索引

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

like ‘%kk’ 和 like ‘%kk%’ ：key_len = 31 ，表示只有 c1 用到了索引

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk%' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

like ‘k%kk%’ ：key_len = 93 ，表示 c1 c2 c3 都用到了索引

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'k%kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

口诀

全值匹配我最爱， 最左前缀要遵守；

带头大哥不能死， 中间兄弟不能断；

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 *；

不等空值还有 OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

参考网址：
mysql面试题网址
https://www.cnblogs.com/mafeng/p/10041425.html
https://www.bilibili.com/read/cv7519001 bilibili
https://article.itxueyuan.com/eoJEMj
100常见面试题汇总
https://article.itxueyuan.com/eoJEMj 知乎
https://www.cnblogs.com/cxiaocai/p/11634257.html：
面试网址https://zhuanlan.zhihu.com/p/133275418
https://blog.csdn.net/qq_33745102/article/details/88392220

https://blog.csdn.net/qq_29726359/article/details/88562318
InnoDB和MylsAM区别：有几个面试题可以整理https://blog.csdn.net/qq_35642036/article/details/82820178
尚硅谷学习笔记
https://blog.csdn.net/qq_21579045/article/details/9970276
尚硅谷mysql学习高级笔记篇
https://blog.csdn.net/why15732625998/article/details/80388236