MySQL高级深入解析：架构、存储引擎与索引优化-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_46596508/article/details/117931496

本文详细介绍了MySQL的架构，包括连接层、服务层、引擎层和存储层，强调了InnoDB存储引擎的特性，如事务支持和外键约束。此外，还详细探讨了索引优化的重要性，包括SQL性能下降的原因、不同类型的JOIN查询、索引的创建和查看等。最后，文章提到了索引的选择与优化策略，强调了正确使用索引以提高查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql高级

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NezEC3d1-1623756460261)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210529145704209.png)]

1mysql的架构介绍

1.1Mysql简介

概述：目前归oracle，开源的。是一种关联型数据库管理系统，将数据保存在不同的表中。

1.2MysqlLinux安装需要安装虚拟机，在镜像环境下安装linvx。这一步暂且作废。第3-7集

下载，一个server一个client。
检查当前系统是否安装过mysql。

linvx系统里面第三方软件包，安装规范放在opt目录下面，这里的后缀是rpm，

安装命令：
的身份

1.3Mysql配置文件

1.4Mysql逻辑架构介绍【分层，可拔插】

和其他数据库相比，MYSQL的架构在不同场景有着良好的作用，具体体现在存储引擎的架构上。插件式的存储引擎架构将查询处理和其他的任务系统任务以及数据的存储提取相分离。这种架构可以在不同的场景选择不同的存储引擎。

这里写图片描述

整个MySQL Server由以下组成

   Connection Pool : 连接池组件
   Management Services & Utilities : 管理服务和工具组件
   SQL Interface : SQL接口组件
   Parser : 查询分析器组件
   Optimizer : 优化器组件
   Caches & Buffers : 缓冲池组件
   Pluggable Storage Engines : 存储引擎
   File System : 文件系统

1）连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2）服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3）引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4）存储层
数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

注：储存引擎是基于表的，而不是数据库的；

哪层出问题找哪层

1.5Mysql存储引擎

1.5.1概述

   和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
   存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。**存储引擎是基于表的，而不是基于库的。所**以存储引擎也可被称为表类型。
   Oracle，SqlServer等数据库只有一种存储引擎。**InnoDB和BDB提供事务安全表**，其他存储引擎是非事务安全表。

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

mysql> show engines;查询当前数据库支持的存储引擎；
mysql> show variables like ‘%storage_engine%’;查询mysql数据库默认的存储引擎。----InnoDB

1.5.2各种存储引擎特性

1.5.2.1常见的几种

行锁：操作时只锁定某一行，不对其他行有影响，适合高并发。

表锁；即使操作某一行也会锁住整个表，不适合高并发。

1.5.2.2InnoDB

重点关注innodb和myisam

对比项	MyIsam	Innodb
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁	行锁，适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	是	是

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点：

1事务控制

2外键约束：

MySQL支持外键的存储引擎只有InnoDB ，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

show create table city_innodb;查看外键信息

3存储方式

InnoDB 存储表和索引有以下两种方式：
①. 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。
②. 使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。

注意：Linux系统的Mysql，默认的数据存储在/var/lib/mysql目录下面；

1.5.2.3 MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。有以下两个比较重要的特点：

不支持事务：我们可以通过之前测试InnoDB的时候一样进行测试，发现在MyISAM中是没有事务控制的。

文件存储方式：每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：.frm (存储表定义)；.MYD(MYData , 存储数据)；.MYI(MYIndex , 存储索引)；
阿里大部分mysql数据库使用的是percona的原型加以修改。该公司有一款存储引擎叫xtradb，完全可以替代innodb，在性能和并发上做的更好。Alisql+Aliredis

1.6存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

   InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。


   MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

2索引优化分析【面试会问，笔试会考】

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题

2.1sql性能下降原因

性能下降SQL慢，执行时间长，等待时间长【主要是以下四个，尤其是1和2】

查询语句写的烂
索引失效，单值索引，复合索引。
关联查询太多join（设计缺陷或不得已的需求）
服务器调优及各个参数设置（缓冲、线程数等）

2.2常见通用的join查询【原因3，复习，基本功】

2.2.1sql执行加载顺序

手写

SELECT DISTINCT 字段
FROM 表连接方式 JOIN 表 ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 筛选
ORDER BY 排序
LIMIT

机读【from开始】

FROM 表连接方式 JOIN 表 ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 筛选
SELECT DISTINCT 字段
ORDER BY 排序
LIMIT

总结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ffbgRgMO-1623756460269)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210529170249071.png)]

from表1，表2，这里的表一表2连接用的笛卡尔积。结果就是1的条数乘以2的条数。

2.2.2join图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JDMBJVNK-1623756460272)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210529171325941.png)]

2.2.3建表语句

建立部门表

CREATE TABLE tbl_dept(
id int(11) not null auto_increment,
deptName varchar(30) DEFAULT NULL,
locAdd varchar(40) DEFAULT null,
PRIMARY key(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

奖励员工表

CREATE TABLE tbl_emp(
id int(11) not null auto_increment,
name varchar(20) DEFAULT NULL,
deptId int(11) DEFAULT null,
PRIMARY key(id),
KEY fk_dept_id(deptId)
#CONSTRAINT fk_dept_id FOREIGN KEY(deptId) REFERENCES tbl_dept(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

插入一些数据，

员工表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aGWIJmKv-1623756460277)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531101610493.png)]

部门表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n36hIn0s-1623756460283)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531101555017.png)]

2.2.4 7种join的sql编写

测试；复习7种join’

内联 inner join

SELECT * FROM tbl_emp a INNER JOIN tbl_dept b on a.deptId=b.id ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-flV0tcvx-1623756460284)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531102130957.png)]
左联 left join，结果是左表，除了共有部分（内联结果），左表独有的那一行，右表为 null

SELECT * FROM tbl_emp a left JOIN tbl_dept b on a.deptId=b.id ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4JwimRzY-1623756460285)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531102418129.png)]
右联，同理左联

SELECT * FROM tbl_emp a right JOIN tbl_dept b on a.deptId=b.id ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MwZDEefC-1623756460286)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531102558125.png)]
只要左边独有，在2的条件上加个筛选条件。

SELECT * FROM tbl_emp a left JOIN tbl_dept b on a.deptId=b.id WHERE b.deptName is NULL;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lkaO6QIz-1623756460287)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531102846213.png)]
只要右边独有，在3的条件上加个筛选条件。

SELECT * FROM tbl_emp a right JOIN tbl_dept b on a.deptId=b.id WHERE a.name is NULL;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WTDWYclW-1623756460288)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531102929811.png)]
全外联，公有加上各自独有

SELECT * FROM tbl_emp a full outer JOIN tbl_dept b on a.deptId=b.id ;这句话在mysql不支持，oracle支持。

变通的表示全部：union合并加去重

SELECT * FROM tbl_emp a left JOIN tbl_dept b on a.deptId=b.id
union
SELECT * FROM tbl_emp a right JOIN tbl_dept b on a.deptId=b.id ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kCY3sI9k-1623756460289)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531103436454.png)]
各自独有

SELECT * FROM tbl_emp a left JOIN tbl_dept b on a.deptId=b.id WHERE b.id is null
union
SELECT * FROM tbl_emp a right JOIN tbl_dept b on a.deptId=b.id where a.name is null;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SJ54m0ic-1623756460290)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210531103630171.png)]

2.3索引简介【面试必问】

2.3.1索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的目的:提高效率，类比字典。例如查找mysql，需要先定位m，再从下往上找y，再找sql。没有索引，就需要从a-z。
索引为什么可以查的快，是因为排过序。索引可以简单理解为“排好序的快速查找数据结构”

所以，索引有两大功能，1排好序，影响orderby2快速查找影响where
在数据之外，数据库系统还维护着满足待定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。
平常所说的索引，如果没有特别指明，都是B树（多路搜索树，不一定是二叉树）结构组织的索引。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

2.3.2索引优点缺点

优势
1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2）通过索引列对数据进行**排序，**降低数据排序的成本，降低CPU的消耗。
劣势

1）实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2）虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果mysql有大量的表，就需要花时间研究建立最优秀的索引，或优化查询。【是需要人为多次修改的】

2.3.3mysql索引分类

1）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
2）唯一索引：索引列的值必须唯一，但允许有空值
3）复合索引：即一个索引包含多个列

2.3.4索引基本语法

2.3.4.1创建索引

方式1：create，在某个表的某个字段【单值索引】或多个字段【复合索引】建立索引，索引名为xxx，如果是唯一所以，用unique修饰

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,…)

index_col_name : column_name[(length)][ASC | DESC]

示例：create index idx_city_name on city(city_name);

创建复合索引:**
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
就相当于
对name 创建索引 ;
对name , email 创建了索引 ;
对name , email, status 创建了索引 ;**

方式2：alter

alter table 表名 add [unique] index[索引名] (字段。。。)

例如alter table tb_name add unique/index/fulltext index_name(column_list);

唯一，null可以出现多次/普通/全文索引

2.3.4.2查看索引

show index from table_name;加上\G会以另一种形式显示

示例：查看city表的索引信息，

show index from table_name\G;

2.3.4.3删除索引

DROP INDEX index_name ON tbl_name;

示例：想要删除city表上的索引idx_city_name

drop index idx_city_name on city;

2.3.5索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：
BTREE 索引：最常见的索引类型，大部分索引都支持 B 树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text （全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE 索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	Mysql5.6版本开始支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

2.3.5.1检索原理

【初始化介绍】

一颗b+树，每一个模块都是一个磁盘块，包含几个数据项（紫色）和指针（蓝色）。例如，磁盘块1包含数据26，150；指针p1,p2,p3；p1表示小于26的磁盘块，P2表示介于26-150的磁盘块，p3表示大于150的磁盘块。
真实的数据只存在叶子节点，即磁盘块4，5，6…；非叶子节点不存储真实的数据，只存储只因搜索方向的数据项，如26，150.只是参考的比较值。

【查找过程】

例如查找数据29，首先，会把磁盘块1由磁盘加载到内存，发生一次IO。在内存中用二分查找确定29是在26-150之间。锁定磁盘块1的p2指针，内存时间因为非常短（相比IO）可以忽略不计，通过磁盘块1的p2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO,29小于36，锁定磁盘块3的p1指针，通过指针加载磁盘块7到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
如果没有索引，每个数据项都要发生一次IO，成本很高。

2.3.5.2索引创建原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

哪些情况需要创建索引：

主键自动建立唯一索引‘【使用唯一索引，区分度越高，使用索引的效率越高。】
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引【因为每次更新不仅仅更新记录还会更新索引】
where条件里用不到的字段不创建索引【用到的字段建立索引不仅有助于业务还有助于查询】
在高并发下倾向创建组合索引
查询中排序的字段，排序字段若通过索引去访问将会大大提高排序速度
查询中统计或者分组字段【因为分组的前提是必排序，所以说分组也和索引有关】

索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

创建复合索引:
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
就相当于
对name 创建索引 ;
对name , email 创建了索引 ;
对name , email, status 创建了索引 ;

哪些情况不需要创建索引：

表记录太少
经常增删改的表
数据重复且分布平均的表字段，应该只为最经常查询和最经常拍下的数据列建立索引

注意：如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。

索引的选择性：索引列中不同值的数目与表中记录数目的比。一个索引的选择性越接近1，这个索引的效率越高。

2.4性能分析【重点】

2.4.1Mysql Query Optimizer

服务层中自带的Mysql查询优化器

MySQL中有专门负责优化Select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是Select并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算Fenix，然后再得出最后的执行计划。

2.4.2Mysql常见瓶颈

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

2.4.3Explain

是什么（查看执行计划）

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

表的读取顺序—explain之id【并不是按自己写的顺序读取的】

数据读取操作的操作类型----explain之select type

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询
怎么玩

explain+sql语句

例如 explain select * from tbl_emp;

执行计划包含的信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AHmStybC-1623756460291)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210602210051151.png)]

名字段解释

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-posNqfri-1623756460292)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210602210639531.png)]

id

**id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。**id是几，就是第几个select。

三种情况：【读表的时候并不是按自己写的顺序读取的】
1. id 相同表示加载表的顺序是从上到下，【id相同就顺序执行】
  
  案例explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
  
  按照加载顺序，看where子句，结果重点看加粗部分。先加载r表，然后是ur和u
  
  mysql> explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
  ±—±------------±------±-------±----------------------------±--------------±--------±-------------------±-----±------------+
  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  ±—±------------±------±-------±----------------------------±--------------±--------±-------------------±-----±------------+
  | 1 | SIMPLE | r | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL |
  | 1 | SIMPLE | ur | ref | fk_ur_user_id,fk_ur_role_id | fk_ur_role_id | 99 | demo_02.r.id | 1 | Using where |
  | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 98 | demo_02.ur.user_id | 1 | NULL |
  ±—±------------±------±-------±----------------------------±--------------±--------±-------------------±-----±------------+
2. id 不同id值越大，优先级越高，越先被执行。【（）优先执行】
  
  案例：
  
  EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id= (SELECT id FROM t_user WHERE username = ‘stu1’));
  
  分析：这里面包含了子查询，越是后面的子查询越先执行，所以对应的id也大。所以，t_user先执行对应的id为3，然后是user_role对应id为2然后是t_role对应id为1。
  ±—±------------±----------±------±---------------------±---------------------±--------±------±-----±------------+
  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  ±—±------------±----------±------±---------------------±---------------------±--------±------±-----±------------+
  | 1 | PRIMARY | t_role | const | PRIMARY | PRIMARY | 98 | const | 1 | NULL |
  | 2 | SUBQUERY | user_role | ref | fk_ur_user_id | fk_ur_user_id | 99 | const | 1 | Using where |
  | 3 | SUBQUERY | t_user | const | unique_user_username | unique_user_username | 137 | const | 1 | Using index |
  ±—±------------±----------±------±---------------------±---------------------±--------±------±-----±------------+
3. id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
  
  案例EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.user_id =‘2’) a WHERE r.id = a.role_id ;
  
  分析：先执行括号里面的。所以先加载表user_role 对应的id为2，经历了括号形成了一张衍生表a，然后这张表和t_role是同级，就会顺序加载。里面的2意思是说由id为2的衍生出来的表。
  
  结果
  ±—±------------±-----------±-------±--------------±--------------±--------±----------±-----±----------------------+
  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  ±—±------------±-----------±-------±--------------±--------------±--------±----------±-----±----------------------+
  | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
  | 1 | PRIMARY | r | eq_ref | PRIMARY | PRIMARY | 98 | a.role_id | 1 | NULL |
  | 2 | DERIVED | ur | ref | fk_ur_user_id | fk_ur_user_id | 99 | const | 1 | Using index condition |
  ±—±------------±-----------±-------±--------------±--------------±--------±----------±-----±----------------------+
select_type

用于表示查询的类型，主要是用于区别。普通查询、联合查询、子查询等复杂查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jn8aWBMy-1623756460293)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210602213437931.png)]

union result是使用了union的两个select的结果的合并
table

展示这一行的数据是关于哪一张表的

type

访问类型排序，最好到最差依次是[常见的几种]

system>const>eq_ref>ref>range>index>all

一般来说，得保证查询至少达到range级别，最好达到ref。

Type的值	含义
NULL	MySQL不访问任何表，索引，直接返回结果：explain select ‘Hello’;
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现【业务中不会出现】
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const将"主键" 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个），属于查找和扫描的结合体。
range	只检索给定返回的行，使用一个索引来选择行。Key列显示使用了哪些索引。一般就是在where 之后出现 between ， < , > , in 等操作。这种比全表扫描好，因为有范围不用全表扫描索引。
index	index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。 index是从索引中读取，all是从硬盘读取。【索引覆盖】
all	将遍历全表以找到匹配的行

possible keys

显示可能应用在这张表上的索引，一个或多个。

查询涉及到的字段若存在索引，则该索引被列出，但不一定被查询实际使用。
key

实际使用的索引，如果为null，则没有使用索引或建了失效。

覆盖索引的理解方式1：select的数据列只用从索引中就可以获取，不必读取数据列，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说就是查询列要被所建索引所覆盖或所包含【例如建的是col1_col2查的是col1col2或者部分满足】读取索引的叶子节点就是数据。

所以，要使用覆盖索引，不能用select*和select col1,col2,其他字段，这样就超过了所建的索引字段数目。而且要注意顺序是一致的，个数可以小于索引个数。

查询中若使用了覆盖索引，则该索引仅出现在key列表中。

【覆盖索引（索引覆盖）查询的字段和建的索引的字段刚好吻合，个数和顺序一致，这样就可以从索引上取，而不用全表扫描，所以type是index，理论上mysql觉得没有用到索引，所以possible keys为null，但实际上用到了索引【我们关心的】，所以key为idx_col1_col2】

select col1,col2 from t1;查询

create index idx_col1_col2 on t2(col1,col2);建索引
key_len

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

注意：key_len和精确是矛盾的，查的越精确付出的代价越大

【用得越少越好，就像给马儿吃草，吃得少跑得远是最好】

例如：select * from t wherecol1="ab"和select * from t wherecol1="ab"and col2=“ac”,后者的key_len为26，前者为13，因为后者查的更精确。
ref

和type并列的一个属性，显示索引的哪一列被使用了，如果可能的话是一个常数，哪些列或者常量被用于查找索引列上的值。

例如explain select * from t1,t2 where t1.col1=t2.col1 and t1.col2=“ac”;

| id |… | table | type | possible_keys | key | key_len | ref | 1 | … | t2 | all | null |null | null | null | 1 | NULL |
| 1| … | t1 | ref |idx_col1_col2 | idx_col1_col2 | 26 | shared.t2.col1,const|
分析，id相等，先加载t2，t2要全表扫描，理论上没有用到索引实际也没有，所以后面都为null；在加载t1，非唯一索引，理论和实际都用到了索引，根据查询语句的where子句，还可以看出t1表的col匹配shared库的t2表的col1，col2匹配了一个常量ac。这些常量或者列被用于查找。
rows

根据表统计信息及索引引用情况，大致估算出找到所需要的记录所需要读取的行数。【每张表有多少行被优化器查询】

例如explain select * from t1,t2 where t1.id=t2.id and t2.col1=“ac”;

原来t2表没有创建索引时，实际利用了t1表的id【有索引】和t2表的col1【没有索引】。

| id |… | table | type | possible_keys | key | key_len | ref | rows

|1 | … | t2 | all |primary |null | null | null |648
| 1| … | t1 | eq_ref |primary |paimary |4 | shared.t2.id| 1

给t2表建立复合索引。create index idx_col1_col2 on t2(col1,col2);建索引之后的结果。

| id |… | table | type | possible_keys | key | key_len | ref | rows

|1 | … | t2 | ref |primary ，idx_col1_col2 |idx_col1_col2 | 195 |const| 142
| 1| … | t1 | eq_ref |primary |paimary |4 | shared.t2.id| 1

extra

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

id,type,key,rows,extra是最重要的五个。

索引的作用有二**，排序和查询**

关于using filesort

例如explain select col1 from t1 where col1=“ac” order by col3;这个表有一个索引idx_col1_col2_col3

结果

| id |… | table | type | possible_keys | key | key_len | ref | rows|extra

|1 | … | t1 | ref |idx_col1_col2 _col3 |idx_col1_col2_col3 | 13 |const| 142|using where;using index;using filesort
分析：这里建的索引是给col123建的，这里查询的时候只用到了col1,字段是个常数，排序只用到了col3，而没有用到col2，所以不是按照建的索引进行读取的，效率变低。【原则：查询中排序的字段要建索引，顺序和个数】

如果变成explain select col1 from t1 where col1=“ac” order by col2， col3

结果

| id |… | table | type | possible_keys | key | key_len | ref | rows|extra

|1 | … | t1 | ref |idx_col1_col2 _col3 |idx_col1_col2_col3 | 13 |const| 142|using where;using index

这样的效率更快，因为查询是按建好的索引走的，无需使用外部的索引排序。排序用到的字段要和索引的字段顺序一致，
关于using temporary

例如explain select col1 from t1 where col1 in(ac,ab,aa) group by col2;这个表有一个索引idx_col1_col2,【建好的索引是先1后2】

结果

| id |… | table | type | possible_keys | key | key_len | ref | rows|extra

|1 | … | t1 | range |idx_col1_col2 |idx_col1_col2 | 13 |null| 569|using where;using index;using filesort；using temporary

分析：建好的索引是先1后2，那么分组就要先1后2比较好，但是查询语句只按2分，就会产生临时表，效率就会下降

改为explain select col1 from t1 where col1 in(ac,ab,aa) group by col1，col2;

结果

| id |… | table | type | possible_keys | key | key_len | ref | rows|extra

|1 | … | t1 | range |idx_col1_col2 |idx_col1_col2 | 26 |null| 569|using where;using index for group-by

分析：分组用到的字段要和索引的字段顺序一致，

关于using index【使用覆盖索引】

例如explain select col2 from t1 where col1 =ab;这个表有一个索引idx_col1_col2,【建好的索引是先1后2】

结果

| id |… | possible_keys | key | key_len | ref | rows|extra

|1 | …|idx_col1_col2 |idx_col1_col2 | 13 |const| 143|using where;using index

分析：建好的索引是先1后2，查询用到的字段是col2，部分用到了索引。同时出现using where和using index，表明索引被用来执行索引键值的查找，不需要回表查找。

改为explain select col1, col2 from t1 ;这个表有一个索引idx_col1_col2,【建好的索引是先1后2】

结果

| id |… |TYPE| possible_keys | key | key_len | ref | rows|extra

|1 | …|INDEX|NULL |idx_col1_col2 | 390 |NULL| 682**|using index**

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

extra的值	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作 *称为文件排序”, 效率低。【这种情况比较危险】
Usingtemporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低【更加凶险】
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。**
Using where	在查找使用索引的情况下，使用了where过滤，需要回表查询所需数据
Using join buffer	使用了连接缓存
Impossible where	Where子句的值总是false，不能用来获取任何元组
Select tables optimized away	在没有group by子句的情况下，基于索引优化min max操作或者对于myisam存储引擎优化count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
Distinct	优化distinct操作，找到第一个匹配的就停止找到同样值的操作

案例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fzNqMaHw-1623756460294)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210614214310650.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uxcyy3jD-1623756460295)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210614214321263.png)]

2.5索引优化

2.5.1索引分析

2.5.1.1单表

案例；

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

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；

分析:功能可以实现，需要完善性能。因为type是all，还出现了using filesort。原来的索引只有id，因为是主键。

开始优化：

方法：新建索引+删除索引

新增：create index idx_article_ccv on article (category_id,comments,views)；

删除：drop index idx_article_ccv on article

新增索引后，再次执行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 where;using filesort；

分析：解决了全表扫描问题，但是没有解决filesort的问题

。原因是建立的索引是ccv，也就是搭的楼梯是123，从一楼的101去201再去301可以找得到，但是现在在二楼出现了大于号，也就是书一楼到二楼可以，二楼出现了很多房间不知道怎么去三楼。就出现了后面的索引失效的问题。【按照b树索引原理，先排序 category_id， category_id相同则再排序comments，再遇到相同的comments则再排序views，当comments字段处于联合索引中间位置时，comments>1,是个范围值range，但是mysql无法利用索引在对后面的views部分进行索引，也就是说range类型查询字段后面的索引无效】

所以，目前的索引不太合适，先删除索引在建立新的索引。。

drop index idx_article_ccv on article；

create index idx_article_cv on article (category_id,views)；这样就盖了两层楼

| id |select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple | article | ref|idx_article_cv |idx_article_cv |4 |const|2|using where;

2.5.1.2两表

案例：book和class表，card是外键。

explain select * from class left join book on class .card=book.card;左外联，左表是全部，右表某些位置是null。两个表连接总有一个是驱动的。

结果

分析：type是all，需要建索引优化，那么是给左表建立还是右表建立，答案是不知道，得去尝试。

如果给book表的card建立索引。【左连接，索引建在右表上】

alter table book add index y(card);

结果

|1 | simple |book|ref|y|y |4 |db0629.class.card|1|using index

分析：第二行的type变成了ref，rows也变成了1优化比较明显。

如果是给class表的card建立索引【左连接，索引建在左表上】。

结果

这是由左连接的特性决定的，左连接确定如何从右表搜索行，左边一定都有。所以右边是关键点，要建立索引。

结论：左连接，索引建在右表。注意左右链接和左表右表

2.5.1.3三表

案例三表book，class，phone三表关联，索引建在哪些字段

explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

结果

建立索引

alter table book add index y(card);

alter table phone add index z(card);

结果

|1 | simple |book|ref|y|y |4 |db0629.class.card|1|using index

|1 | simple |phone|ref|z|z|4 |db0629.book.card|1|using index

结论：后两行的type都是ref且rows优化很好，所以索引最好设置在需要经常查询的字段中。

2.5.1.4join语句的优化

1尽可能减少join语句的循环总次数，永远用小表驱动大表。【class驱动book，书的类别要少于书】

2优先优化内层循环【内层查询快了总的才能快】

3保证join语句中被驱动表上join条件字段已经被索引。【例如上面的card】

4当无法保证被驱动表的join条件字段被索引，且内存资源充足的前提下，不要太吝惜joinbuffer的设置，可以调大些。

2.5.2索引失效【应该避免】

建立索引，就是排个顺序；失效就是建的顺序没用了。最完美的sql就是怎么建的索引怎么查询

案例：建立staffs表，字段为id,name,age,pos,add_time,建立复合索引idx_staffs_nameAgePos

常见的索引失效情况

全值匹配我最爱

全值匹配：对索引中所有列都指定具体值。【个数+顺序，如果是所有列都指定具体值的话可以不管顺序】

案例:

1 explain select * from staffs where name=‘july’【部分】

结果

| id |select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|74|const|1|using where

explain select * from staffs where name=‘july’ and age=15【部分】

结果

| id |select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|78|const，const|1|using where

explain select * from staffs where name=‘july’ and age=15 and pos=‘dev’【全部】

结果

| id |select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|140|const，const,const|1|using where

分析：随着利用索引的数目增大，精度和速度越来越高，但是付出的代价越来越大key_len增大。

注意出错的点：

案例：违背了最左前缀法则

1带头大哥没了，变成全表扫描

explain select * from staffs where age=15 and pos=‘dev’【建了索引却没用到】

explain select * from staffs where pos=‘dev’【建了索引却没用到】

explain select * from staffs where age=15 【建了索引却没用到】

结果

| id |select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | all|null|null|null|null|3|using where

2中间兄弟断了

explain select * from staffs where name=‘july’ and pos=‘dev’【虽然是索引的两个列但实际只用到了一个，由ket_len和ref可以看出】

结果

|id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|74|const|1|using where

分析：就像上楼梯，一楼三楼的楼梯是好的，二楼断了，所以只能用到一楼的梯子，也就是第一个索引。
最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。【带头大哥不能死，中间兄弟不能丢，丢了的话实际上只用到了丢之前的部分】
不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效导致全表扫描。

虽然：

explain select * from staffs where name=‘july’ 【用到了索引】

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
存储引擎不能使用索引中范围条件右边的列

案例

explain select * from staffs where name=‘july’ and age>15 and pos=‘dev’

结果

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | range|idx_staffs_nameAgePos|idx_staffs_nameAgePos|78|null|1|using where

分析：这里只用到了前两个索引，pos没用到。一楼是个等值，用于查找，一楼的梯子用过之后；二楼是个范围，用于排序；根据索引使用原则，三楼的梯子用不上，因为二楼是个范围没有指向三楼的具体梯子。所以范围条件右边的列就是第三列pos这个索引没有使用到。
尽量使用覆盖索引【查询的字段就是建立的索引或者包含在里面，就会出现using index】。（只访问索引的查询（索引列和查询列一致）），减少select *

explain select * from staffs where name=‘july’ and age=15 and pos=‘dev’

结果

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|140|const,const,const|1|using where；

explain select name,age,pos from staffs where name=‘july’ and age=15 and pos=‘dev’

结果

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|140|const,const,const|1|using where；using index

分析：使用了using index效果更好。

同理的，查询*变成查询索引字段，在带范围的这种下，也是不一样的，type由range变成了ref，是有索引的个数也变成了1个，多加了using index。

explain select * from staffs where name=‘july’ and age>15 and pos=‘dev’

结果

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | range|idx_staffs_nameAgePos|idx_staffs_nameAgePos|78|null|1|using where

explain select name,age,pos from staffs where name=‘july’ and age>15 and pos=‘dev’

结果

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | ref|idx_staffs_nameAgePos|idx_staffs_nameAgePos|74|const|1|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|idx_staffs_nameAgePos|null|null|null|5|using where；
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 |staffs | null|null|null|null|null|null|impossible where；

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|idx_staffs_nameAgePos|null|null|null|5|using where；

总结如下：当该列中的数据大部分都是null的时候，根据条件is null查询，索引失效，根据条件is not null查询，索引生效；当该列中的数据大部分都不是null的时候，根据条件is null查询，索引生效，根据条件is not null查询，索引失效。
like以通配符开头（’%abc…‘）mysql索引失效会变成全表扫描，最好是百分号写在右边。两边都有%也会索引失效，解决方案：覆盖索引。

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；

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|idx_staffs_nameAgePos|idx_staffs_nameAgePos|74|null|1|using where；
字符串varchar不加单引号索引失效

sql语句 select * from staffs where name =’2000‘和 select * from staffs where name =2000效果一样，都可以查询到，是因为底层实现了自动类型转换，但是根据第3条不在索引列上作任何操作，否则会索引失效。

explain select * from staffs where name =2000

结果理论上用到了，实际上没用到索引。

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | all|idx_staffs_nameAgePos|null|null|null|3|using where；
少用or，用它会索引失效【结果正确，过程曲折】

explain select * from staffs where name =’july‘ or age=23；

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |staffs | all|idx_staffs_nameAgePos|null|null|null|5|using where；

总结

假设建立复合索引abc

Where语句	索引是否被使用
a=3	使用a
a=3 and b=5	使用ab
a=3 and b=5 and c=4	使用abc
b=3或者b=3and c=4或者c=4	未使用【带头大哥丢了】
a=3 and c=5	使用a【中间丢了】，未用到c
a=3 and b>4 and c=5	使用a和b，b断了c不能用在范围后
a=3 and b like ‘kk%’ and c=5	使用abc，因为开头是kk是固定的，这个是验证过的，但是type还是range，踩着kk%可以用到c
a=3 and b like ‘%kk’ and c=5	使用a，前面是个%，是啥都不知道，不是范围查询，type是ref
a=3 and b like ‘%kk%’ and c=5	使用a，前面%是个模糊查询，type是ref
a=3 and b like ‘k%kk%’ and c=5	使用abc，左边是常量，右边不失效，可以用到c

2.5.3一般性建议

【排序和查找是索引的两个优点】

面试题：

解决like ’%字符串%‘时索引不被使用【索引失效，因为%只能在最右边才不失效】的方法？

答：某些情况下，确实不可避免要找例如1aa1,2aa2,3aa3，，，等这样的百分号就只能加在aa两边。假设表user字段id,name,age,email，其中name和age是常查询字段，给他两建立复合索引idx_user_nameage。【用了like ’%aa%‘，在不建立复合索引的时候无论查什么即使是查主键id都是全表扫描。】

解决方案;使用覆盖索引避免全表扫描。

explain select name,age fromuser where name like ’%aa%‘

结果

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |user| index|null|idx_user_nameage|68|null|4|using where；using index

explain select id fromuser where name like ’%aa%‘

结果因为id时主键也是索引。

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |user| index|null|idx_user_nameage|68|null|4|using where；using index

explain select name from user where name like ’%aa%‘

explain select age from user where name like ’%aa%‘

结果name是索引的一部分，age也是索引的一部分，在覆盖索引中可以不注意顺序，只看查询字段，但是select *就和索引顺序有关系了。

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |user| index|null|idx_user_nameage|68|null|4|using where；using index

explain select id，name from user where name like ’%aa%‘

explain select id，name ，age from user where name like ’%aa%‘

explain select name ，age from user where name like ’%aa%‘

| id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra

|1 | simple |user| index|null|idx_user_nameage|68|null|4|using where；using index

explain select id，name ，age，email from user where name like ’%aa%‘【超过了索引，复合索引失效，全表扫描】

explain select * from user where name like ’%aa%‘【超过了索引，复合索引失效，全表扫描】
索引使用情况总结

假设建立复合索引c1234
- where条件按照索引顺序逐渐增加个数
  
  explain select * from test where c1=‘a1’
  
  | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra
  
  |1 | simple |test| tref|idx_test_c1234|idx_test_c1234|31|const|1|using where；
  
  explain select * from test where c1='a1’and c2=‘a2’
  
  | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra
  
  |1 | simple |test| tref|idx_test_c1234|idx_test_c1234|62|const,const|1|using where；
  
  explain select * from test where c1='a1’and c2=‘a2’ and c3=‘a3’
  
  | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra
  
  |1 | simple |test| tref|idx_test_c1234|idx_test_c1234|93|const,const,const|1|using where；
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|124|const,const,const,const|1|using where；
- where条件指定索引的全部列的值。顺序不按照索引的顺序，是mysql底层翻译优化转化成了1234.实际用的时候，最好就是使用和建的顺序一致。【怎么建怎么用】
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|124|const,const,const,const|1|using where；
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|124|const,const,const,const|1|using where；
- where条件出现范围,顺序和建立索引顺序一致。
  
  explain select * from test 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 |test| range|idx_test_c1234|idx_test_c1234|93|null|1|using where；
  
  注意：c3是个范围，只能用于排序，不可以用作查找，所以c4更不可能用作查找。【范围之后全失效】
- where条件出现范围,顺序和建立索引顺序不完全一致。
  
  explain select * from test 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 |test| range|idx_test_c1234|idx_test_c1234|124|null|1|using where；
  
  注意:c123都是值，c4是个范围。所以4个都用到了。
  
  **结论：**mysql底层会给优化，先把楼梯顺序调好了。
- where条件出现排序，使用到索引的所有列
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|62|const,const|1|using where；
  
  注意：查找中用到了c1和c2.实际上排序中用到了c3，但没有统计在表里
- where条件出现排序，未使用到索引的所有列
  
  1.未使用的列出现在排序的列后，本身就与之后的列无关。
  
  explain select * from test where c1='a1’and c2=‘a2’ order by c3
  
  | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra
  
  |1 | simple |test| ref|idx_test_c1234|idx_test_c1234|62|const,const|1|using where；
  
  2未使用的列在排序的列之前【12在3断了4排序，mysql自己内部搞了一下效率降低】
  
  explain select * from test where c1='a1’and c2=‘a2’ order by c4
  
  | id|select_type| table | type | possible_keys | key | key_len | ref | rows|extra
  
  |1 | simple |test| ref|idx_test_c1234|idx_test_c1234|62|const,const|1|using where；using filesort
- where条件出现排序，条件中还出现了其他字段还有order by的顺序。
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|31|const|1|using where；
  
  注意：从查找意义上来说用到了c1，实际排序用到了c2和c3，无filesort
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|31|const|1|using where；using filesort
  
  注意：从查找意义上来说用到了c1，实际排序用到了c2和c3，出现了filesort，因为排序顺序和索引顺序反了。
- where条件出现排序，某字段既用于查找有用于排序
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|62|const,const|1|using where；
- where条件出现排序，某字段既用于查找有用于排序，且出现其他字段【迷惑作用】
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|62|const,const|1|using where；
- where条件某字段既用于查找有用于排序，且出现其他字段且排序顺序不是索引顺序
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|62|const，const|1|using where
  
  注意：这种情况不会出现filesort。首先多的字段是迷惑作用。那么重点就落在既用于查找有用于排序的字段c2，这就是order by顺序不一致但没有出现filesort的特例。因为用于排序的c2已经是个常量，实际上用于排序的只有c3。
- where条件出现分组，分组顺序按照索引顺序
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|31|const|1|using where；
- where条件出现分组，分组顺序和索引顺序不一致。
  
  explain select * from test 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 |test| ref|idx_test_c1234|idx_test_c1234|31|const|1|using where；using temporary;using filesort
  
  注意：分组之前必排序，二者索引分析是一样的。分组会导致临时表产生。
一般建议；
- 对于单值索引，尽量选择针对当前查询过滤性更好的索引。
- 在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前查询中的where字句中更多字段的索引。
- 尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的。