MySQL数据库优化小结

引言

在开始学习mysql优化之前，我们首先要搞清2点

为什么是mysql
为什么要优化

两个问题

1、为什么是mysql？

其实这主要是源于2007年左右阿里提出的去IOE理念，具体其中I O E分别表示（PS：这实际上也是国家之间的科技战）

去掉IBM的小型机
Oracle数据库
EMC存储设备

IT企业自己在开源软件基础上自行研发系统，因为原来的国外的服务价格昂贵，二来中国这些年来鼓励IT科技公司进行创新（尤其是中美贸易战后的芯片战），2009年阿里云成立后，随着阿里的飞天系统云操作系统诞生，阿里逐步在实现全面云化（2014年甲骨文数据库软件营收首次负增长，而在2019年去IOE潮流逐渐流行，甲骨文也慢慢将中国研发中心废除，大批裁员）

随后在2019年9月的华为全联接大会上，华为宣布企业级数据库Gauss开源，此数据库可覆盖70%企业的业务场景。openGauss将在2020年6月左右正式上线，并于2020年7月1日开放openGauss开源社区

但是早在Oracle支持大型企业收费的时候，能满足中小企业的需求就是mysql，并且同样是开源，当时在5.0之前是AB公司管理，随后被Sun公司收购，随着Sun公司被甲骨文收购，mysql自然也就归属oracle，而mysql在5.6之后的版本，也逐步向oracle靠拢，如：表空间、redo、undo分离、隐藏索引，至2019年，Mysql8.0.15的发布，也预示着mysql的发育的非常的好，我们之所以学习mysql，是因为目前绝大多数的中小型企业开发中，使用mysql的场景非常的多，对于大型的互联网公司一般都有自己的数据库系统或者基于mysql改造的数据库系统，如阿里云mysql

2、为什么要优化？

在这个数据为王的时代，互联网上的所有数据几乎都是存储在数据库中的，而如何快速高效地从数据库中获取到有效数据并呈现给用户，增加用户体验性，这就不得不提高数据库的稳定性以及性能，这就数据库优化的目的

数据库优化可以解决如下问题

1、避免网页错误产生

由于数据库连接timeout产生页面5xx错误
由于慢查询造成页面无法加载
由于阻塞造成数据无法提交

2、增加数据稳定性

3、增加数据库最高查询和加载性能

4、提高用户体验

流畅页面的访问速度
良好的网站功能体验

解决方案

编写出高效的sql语句
配置一些DB的调优参数

设计思路

mysql的存储引擎
sql优化准则
mvcc
事务
日志
主从复制
容灾备份
buffer pool
锁

配置文件查看

在mysql中，有很多常用的配置文件，在不同的环境下，对应的配置文件位置也有所差异

配置文件名

linux

配置文件名：my.cnf 指令： /etc/my.cnf

如果在linux中访问my.cnf访问拒绝，可以采取如下步骤：

使用sudo chmod 777 mysqld.cnf修改访问的权限
再通过vim /etc/my.cnf` 指令对my.cnf进行权限编辑
最后再通过 sudo chmod 644 mysqld.cnf恢复权限

补充说明：

从左至右，1-3位数字代表文件所有者的权限，4-6位数字代表同组用户的权限，7-9数字代表其他用户的权限

具体的权限是由数字来表示的

读取的权限等于4，用r表示

写入的权限等于2，用w表示

执行的权限等于1，用x表示

通过4、2、1的组合，得到以下几种权限：0（没有权限）；4（读取权限）；5（4+1 | 读取+执行）；6（4+2 | 读取+写入）；7（4+2+1 | 读取+写入+执行）

window

配置文件名：my.ini

二进制日志

log-bin文件，主要是用于主从复制

错误日志

存放mysql的一些错误日志

查询日志

默认是关闭，主要用于记录查询的sql语句，开启会降低mysql性能

数据文件（重点）

根据使用的存储引擎的不同，对应的数据文件也有所差异，需要注意的是存储引擎针对是表而不是库

innodb下数据文件

db名.frm 表结构
db名.ibd 表数据（包含索引以及数据）

myisam下数据文件

db名.frm 表结构
db名.myi 表索引
db名.myd 表数据

mysql的架构组成

MySQL的优势在于它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在多样化的存储引擎的架构，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离（比如常用的innodb和myisam）。这种架构可以根据业务的需求和实际需要选择合适的存储引擎

连接层

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

服务层

第二层架构主要完成mysql核心服务功能，如SQL接口，缓存的查询，SQL的分析和优化器及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析语句并创建相应的内部解析树，对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

引擎层

存储引擎层，存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据实际需求进行选择，需要注意的是比较常用的是innodb和mysiam引擎

存储层

数据存储层，主要将数据存储在运行于裸设备的文件系统上，并完成与存储引擎的交互

流程分析

执行流程

从客户端开始，到连接器对应连接导数据，客户执行对应的sql语句，通过分析器分析，将对应的sql语句转换为一个AST抽象语法树，再通过优化器选择对应的优化方案，最后执行sql语句，再从对应存储引擎中拉取数据，最后返回给客户端

优化思路

所谓的优化，就是对上述的执行流程进行优化。

比如连接器的优化，尽量避免连接池的多次打开和关闭，IO流的频繁读取，一般的解决方案就是连接池的方案，目前常用的连接池有：DBCP、C3P0、Druid（德鲁伊）、HikariCP（日语:光，Spring集成，性能最高）

存储引擎

在之前学习mysql时，我们提及存储引擎，首先要明白以下几点：

存储引擎分类
存储引擎针对的对象是什么
存储引擎之间的区别

展示所有存储引擎

show engines;

support 表示当前版本是否是支持，yes表示支持，no表示不支持，default表示默认
Transactions 表示是否支持事务
XA指的是是否支持分布式事务
Savepoints表示时是否支持保存点（用于事务回档）

展示当前存储引擎

show variables like '%storage_engine%';

设置表的存储引擎

设置对应的mysql表的存储引擎（存储引擎针对的是表，而不是数据库）

语法

create table 表名(

)engine = 存储引擎类型

默认类型为innoDB

补充

如果希望多个一起使用，可以采用如下写法

CREATE TABLE jojo7(
id INT(3) COMMENT '666'
)ENGINE = INNODB,CHARSET = utf8,COMMENT = '狗'

查看指定表的存储引擎

想要查看某表的存储引擎，那么必须通过系统表来进行查询

select table_schema,table_name,table_type,engine 
from information_schema.tables 
where table_name = 'dog'

存储引擎的类型：MyISAM、InnoDB 、Memory

存储引擎区别：MyISAM VS InnoDB

名称	InnoDB	MyISAM
事务	支持	不支持
数据行锁定	支持（行锁）	不支持(表锁,不适用高并发)
外键	支持	不支持
全文索引	不支持	支持
表空间大小	较大，约2倍	较小
缓存	缓存索引和数据（使用内存来操作，对内存要求高）	仅缓存索引

应用场景

MyISAM: 不需事务，空间小，以查为主

InnoDB: 以增删改为主，安全性高，事务处理及并发控制，空间较大

PS：在后续讲到索引时，会再分析在MyISAM下的索引结构和InnoDB下的索引结构有什么本质上的区别

SQL语句的执行性能

所谓的mysql优化，就是希望通过各种方式，使执行sql语句所耗费的时间尽可能缩短，而引起sql语句性能下降的原因不外乎如下几点：

sql语句编写不合理
过多的连接查询导致笛卡尔积过大
设置了索引但缺没有使用到
mysql基础参数设置不合理
I/O瓶颈（装载数据远大于内存）
CPU瓶颈（CPU在读取装载数据时，使用率过高）
部署的服务器性能上的瓶颈

执行顺序

以以下sql为例子，对sql语句执行性能进行详细讲解

select [distinct] *
[from 表名]
[inner join on 另一张表] 
[where 表达式（条件）]
[group by 列名]
[having 聚合函数]
[order by 列名]
[limit [begin],row]

执行顺序如下

from 子句组装来自不同数据源的数据（笛卡尔积计算）
应用on筛选器
执行join
where 子句基于指定的条件对记录行进行筛选
group by 子句将数据划分为多个分组（如有cube rollup则在其后执行）
【聚集函数进行计算】
使用 having 子句筛选分组（二次筛选）
select 的字段
distinct 去除重复行
order by 对结果集进行排序
limit，查询指定范围

详细步骤理解

**第一步：**对from子句中的前两个表进行笛卡尔乘积，此时生成虚拟表 vt1（选择两张表中相对小的表做基础表）

第二步：应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2

**第三步：**如果是outer join 那么这一步就将添加外部行，生成虚拟表 vt3

left outer join 就是将左表在第二步中过滤的添加进来
right outer join 就是将右表在第二步中过滤掉的行添加进来

**第四步：**如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3

第五步：应用where筛选器，对之前步骤产生的虚拟表使用where筛选器，生成虚拟表vt4

**第六步：**group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等），原因在于最终的结果集中只为每个组包含一行（不可一个数据对应多行）

**第七步：**应用cube或者rollup选项，为vt5生成超组，生成vt6.（rollup和cube也是聚合函数，需要注意不可与order by一起使用）

**第八步：**应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器，即二次筛选，并在此处对聚合函数进行相应的计算

**第九步：**处理select子句。将vt7中的在select中出现的列筛选出来，生成vt8.

**第十步：**应用distinct子句，vt8中移除相同的行，生成vt9，如果编写了group by就不会使用distinct因为分组查询本身可以保证不重复

第十一步：应用order by子句。按照order_by_condition排序vt9，此时返回的是一个游标，而不是虚拟表。sql是基于集合理论，集合不会预先对行进行排序，本身只是成员的逻辑集合，成员的顺序无关紧要。如果对表进行排序的查询，则可返回一个对象，这个对象包含特定的物理顺序的逻辑组织，这个对象就叫游标。此外，order by可以使用别名排序

**第十二步：**对查询出来的虚拟表vt9 通过limit进行筛选生成最终虚拟表vt10

SQL JOIN

图例

案例

以用户表和身份表为案例，以下为sql脚本

use test11;
CREATE table `user`(
id int primary key auto_increment comment '用户id',
`name` varchar(20) not null comment '用户名',
`cid` int not null comment '身份id'
);
create table `character`(
`cid` int primary key  comment '身份id',
`cname` varchar(20) not null comment '身份名称'
);
insert into `user`
select null,'张三',1 union
select null,'李四',2 union
select null,'王五',3 union
select null,'赵四',4;

insert into `character`
select 1,'学生' union
select 2,'教师' union
select 3,'校长' union
select 5,'家长';

演示

#交叉连接（cross join）
select * FROM
`character`,`user`

#内连接
select id,`name`,`user`.`cid`,`cname` 
FROM `character`
inner join `user`
on `character`.cid = `user`.cid

#左连接
select id,`name`,`user`.`cid`,`cname` 
FROM `user`
left join `character`
on `character`.cid = `user`.cid

#左连接(左独有)
select id,`name`,`user`.`cid`,`cname` 
FROM `user`
left join `character`
on `character`.cid = `user`.cid
where `cname` is null

#右连接
select id,`name`,`user`.`cid`,`cname` 
FROM `user`
right join `character`
on `character`.cid = `user`.cid

#右连接(右独有)
select id,`name`,`user`.`cid`,`cname` 
FROM `user`
right join `character`
on `character`.cid = `user`.cid


#全连接（mysql没有full join,只能通过union组合,利用union的去重）
select id,`name`,`user`.`cid`,`cname` 
FROM `user`
left join `character`
on `character`.cid = `user`.cid
union 
select id,`name`,`user`.`cid`,`cname` 
FROM `user`
right join `character`
on `character`.cid = `user`.cid

索引

索引设计出来的目的，是为了提高查找的效率，是一种事先排好序的数据结构

索引的数据结构分类

二叉/分查找树（binary search tree）

又称为 ordered binary tree（有序二叉树）或 sorted binary tree（排序二叉树），拥有如下特点

若任意节点的左子树不空，则左子树上所有节点的值均小于它的根节点的值；
若任意节点的右子树不空，则右子树上所有节点的值均大于它的根节点的值；
任意节点的左、右子树也分别为二叉查找树；
没有键值相等的节点。

使用常规的二叉树作为索引的数据结构有一个缺点，对于本身无需的列值，可以采用二叉查找树来快速获取想要的索引位置；而如果对于对应列值为有序时，插入的层级会很高，与链表类似，查找的效率就会变得很低

红黑树(red/black Tree)

特点

节点是红色或黑色
根节点是黑色
所有叶子都是黑色
每个红色节点的两个子节点都是黑色。（从每个叶子到根的所有路径上不能有两个连续的红色节点）
从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点

概念

红黑树按广义上来讲，属于平衡二叉树的分支，说它广义上来讲是因为他不是严格按照平衡二叉树的要求，即严格控制左、右子树高度或节点数之差小于等于1

平衡二叉树（Balanced Binary Tree，又称AVL树）

它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。这个方案很好的解决了二叉查找树退化成链表的问题，把插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)。但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间，不过相对二叉查找树来说，时间上稳定了很多

右边的为平衡二叉树，左边的不满足层级小于1

虽然红黑树很大程度上解决了问题，但是对于数据行较多的情况，对应黑红树的高度过高，因而也会使对应的查找次数增加，降低效率

B树

首先，B树不要和二叉树混淆，在计算机中，B树是一种自平衡树数据结构，它维护有序数据并允许以对数时间进行搜索，顺序访问，插入和删除。B树是二叉搜索树的一般化，因为节点可以有两个以上的子节点。与其他自平衡二进制搜索树不同，B树非常适合读取和写入相对较大的数据块（如光盘）的存储系统。它通常用于数据库和文件系统

B树（B-树）

定义

B树是一种平衡的多分树，通常我们说m阶的B树，它必须满足如下条件：

每个节点最多只有m个子节点。
每个非叶子节点（除了根）具有至少⌈ m/2⌉子节点。
如果根不是叶节点，则根至少有两个子节点。
具有k个子节点的非叶节点包含k -1个键。
所有叶子都出现在同一水平，没有任何信息（高度一致）

特点

叶节点具有相同的深度
叶节点的指针为空所有索引元素不重复
节点中的数据索引从左到右递增排列

而在索引中，具体体现为，索引的下标和数据存储在一起

B+树

B+树的特点

有m个子树的中间节点包含有m个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引；
所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)；
所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)；

B+树比B树更适合做数据库索引的原因

1）B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了

原因

索引查询的数据主要受限于硬盘的I/O速度，查询I/O次数越少，速度越快，所以B树的结构才应需求而生；B树的每个节点的元素可以视为一次I/O读取，树的高度表示最多的I/O次数，在相同数量的总元素个数下，每个节点的元素个数越多，高度越低，查询所需的I/O次数越少；假设，一次硬盘一次I/O数据为8K，索引用int(4字节)类型数据建立，理论上一个节点最多可以为2000个元素，200020002000=8000000000，80亿条的数据只需3次I/O（理论值），可想而知，B树做为索引的查询效率有多高；

查询效率相关参数：总元素个数、树的高度

2）B+树查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

B树在提高了IO性能的同时并没有解决元素遍历的效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低

Hash表类型

hash表就是经典的键值对（K-V）模型，本质上是键对应值的操作，将对应存储的数据hash化得到一个数字，再将hash后的数字作为键，映射到对应数据存储的磁盘地址，这样的结构查询效率虽高，但不适合与范围查找，因为hash不适合范围查询

数据库存储引擎中索引实现分析

innodb存储引擎

innodb中的表数据文件本身就是按B+Tree组织的

innodb中的数据文件和索引是存放在一起的，因此称为聚集索引（聚簇索引），即B+树的数据结构，叶节点包含了完整的数据记录

innodb推荐使用整型的自增主键(如果不设置主键，DB还需要花费资源额外为我们设置隐藏列；如果不设置整数，相对比较麻烦，假设设置的主键为非整数类型，那么在比较时需转成指定code，再通过code进行比对；采用自增的话，本身B+树上的叶子节点之间类似双向链表，且叶子节点上的键本身是排序过后的，此时类似链表一样，在尾部添加新的元素，如果此时不是自增会破坏原有的B+树结构，使B+树结构分裂重新平衡)

注意，实际上，二级索引本质上也是一种非聚集索引，通过对应二级索引字段所绑定的主键id，通过该主键id反向来到主键索引处找到对应的索引，实现查询

非主键索引结构叶子节点存储的是主键值，为了一致性和节省存储空间

mysiam存储引擎

如果使用的mysiam的存储引擎，本身使用的是非聚簇索引（稀疏索引），数据文件和索引文件是分离的，叶节点不包含了完整的数据记录，保存的是对应索引文件对应的磁盘地址，通过磁盘地址找到对应的数据

索引设计注意事项

此处以此表结构为案例

1、选择唯一性索引

唯一性索引的数据是唯一的，可以更快的通过该索引查询某条数据。

2.为经常需要排序，分组和联合操作的字段建立索引

order by,group by的字段在排序操作时很是耗时，可以对其加索引。此处需要注意，order by，group by中的列也遵循最左前缀原则

3.为经常作为查询条件的字段建立索引

4.限制索引的数量

过多的索引很是消耗数据库的性能，多使用组合索引。

5.尽量使用数据量少的索引。

如果索引的值很长，查询的速度会受到影响

6.删除不再使用或很少使用的索引

减少对索引的维护

7.最左前缀原则

Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

匹配机制

mysql会一直从左向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

案例

如果按照顺序，则会应用到对应的索引内容，比如此处的3列做为组合索引，查询按name来查询，可以使用到索引

select * from A 
where `name` = 'Bill'

如果不按照顺序，则不会使用到索引

select * from A 
where age = 32 
and `name` = 'Bill'

8.选择区分度高的列作为索引

重复的值出现概率越高，索引性能越低，如索引中的数据为200，索引重复的数据有180，则180/200 这个重复数据占比越接近1，则越快

9.索引列不能参与运算，否则索引失效

10.尽量扩展索引，不要新增索引

例如，现在有个索引a,但是想新建一个组合索引(a,b),那么就对普通索引a,进行修改为组合索引，不要新建组合索引

11.尽量不要选择频繁更新的字段作为索引

频繁更新的字段也会同时更新索引(比如CRUD操作)

12.表数据过少，可以不建立索引

语法

创建索引

索引名一般起为：index_表名_字段名

index_name：索引名

table_name：表名

column_name：列名

length：索引的长度，一般字符串类型中才制定索引长度

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column_name[length]…);

案例

为product中的pname列设置索引

CREATE INDEX index_product_pname 
ON `product`(`pname`);

查看索引

SHOW INDEX 
FROM table_name;

案例

结果

Table：创建索引的表
Non_unique：索引是否非唯一 , 0 唯一 1 不唯一
Key_name：索引的名称
Column_name：定义索引的列字段
Seq_in_index：该列在索引中的位置
Null：该列是否能为空值
Index_type：索引类型（BTREE, FULLTEXT(全文索引), HASH, RTREE(仅支持geometry数据类型)）

删除索引

语法和创建相似

DROP  INDEX index_name 
ON table_name;

案例

DROP INDEX index_product_pname
ON `product`;

查看索引的使用情况

利用explain关键字，可以查看该SQL语句使用索引的情况

语法

explain
SQL语句

案例

EXPLAIN 
SELECT * 
FROM `product` 
WHERE `pname` = '牛奶糖'

列名解释

id

select查询的序列号，包含一组数字，表示查询中操作表的顺序或者执行select子句的顺序，在id数字不同的情况下，对应的场景也不同

场景：

id相同，执行顺序从上往下，顺序执行
id不同，id越大优先级越高，越先被执行

如果是子查询，id序号会递增（多表子查询案例）

如果是查询虚表，会存在id部分相同（续表查询案例）

select_type

选择类型，存在以下几种常用的类型：

1.SIMPLE

简单的select查询,查询中不包含子查询或者UNION

2.PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

select * from a标记为primary

in中的（select id from b）标记为subquery

select * from a
where id in (select id from b)

3.SUBQUERY

在SELECT或WHERE列表中包含的子查询

4.DERIVED

在FROM列表中包含的子查询被标记为DERIVED(衍生)，from中的为临时虚拟表，MySQL会递归执行这些子查询,把结果放在临时表里。

5.UNION

若第二个SELECT出现在UNION之后，则被标记为UNION;

若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERTVED

6.UNION RESULT

从UNION表获取结果的SELECT查询语句

table

表示当前这行数据属于哪一张表

type

type显示的是访问类型，主要用于判断查询用了哪种类型，是较为重要的一个指标，结果值从最好到最坏依次是

system > const > eq_ref > ref > fultext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

需要记住：system > const >eq_ref > ref >range > index >ALL（尽量达到range级别，最好能达到ref）

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const：通过索引只需要检索1次，const用于比较primary key或者unique索引等这种唯一类型的索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描（最好的理解就是主表的主键在从表中只匹配到一条数据）

ref：非唯一性索引扫描，返回匹配某个单独值的所有行（上面的eq_ref指的是通过主表的主键在从表匹配到的只有1条数据，而ref指的是通过主表的主键在从表匹配到的有多条数据）

range：给定检索范围,使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为只需要范围内区间进行查询，不用全表扫描

index：Full Index Scan，index与all区别为index类型是遍历索引树，一般情况下会比ALL快，因为索引文件通常比数据文件小，并且index是从内存加载，all是从硬盘读取

all： 全表扫描，效率最低的一种，这样的sql一般要考虑优化

possible_keys

显示可能应用在这张表中的索引，一个或多个，但实际上并不一定被使用

key

实际使用的索引。如果为NULL，则没有使用索引

一些特殊情况：

possible_keys为null，但实际上用到了索引
possible_keys有多个，但实际上用到了部分
possible_keys有使用到索引，但实际缺没有用到

如果直接通过索引查询到的数据（比如建立了复合索引，刚好查询的列也是包含在复合索引中，如index_col1_col2，而刚好查询的也为col1,col2或者单一的col1，此时不需要回表查询），不需要回表过程，查询结果受索引覆盖

key_len

表示索引中使用的字节数，可通过该列得到查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好(与时时间复杂度和空间复杂度同理：长度越长，查询的精度越高，速度越快，但需要的空间也越多了，反之亦然)

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

案例：给表中的某2列设置索引，并设置相同长度，观察key_len

CREATE TABLE `character2`(
`id` INT PRIMARY KEY  COMMENT 'id',
`c1` VARCHAR(5) NOT NULL COMMENT '身份1',
`c2` VARCHAR(5) NOT NULL COMMENT '身份2'
);

CREATE INDEX index_c1_c2
ON `character2`(`c1`,`c2`);

EXPLAIN 
SELECT * FROM `character2`
WHERE `c1` = '1' 
AND `c2` = '2'

可以通过观察得知，根据单条件和两个条件查询，获得的key_len的长度是不同的，刚好为2倍的原因是因为其定义时长度是相同的

ref

表示索引在使用时，哪一列被使用（如果是常量，比如确切的某个条件，则指代的该常量上的值）

SELECT * FROM `character`,`character2`
WHERE `c1` = '1' 
AND `c2` = '2'
AND `character2`.`id` =`character`.`cid`

通过ref可以得知，如果查询的条件为确切的某个值，则ref为const，如果查询的内容是通过键之间去关联，则使用的使用mysql_study.character2.id

如果possible_keys中为Null，则对应的ref也为空

rows

找到指定数据所需要查找的数据行数（根据表信息和索引的实际使用情况决定）, 数值越小越好（证明查找得越快）

extra

额外信息（重要）

using filesort：使用文件排序 ⭐

mysql使用外部索引排序而不是按照表中设置的索引顺序排序，此时对应的这种无法利用索引完成的排序操作称为文件排序（一般出现）

using temporay : 使用临时表⭐

常用于查询结果排序时使用临时表，比如在order by 和 group by 中常用

问题：这种级别的话容易出现性能上的问题，mysql需要创建临时表再进行销毁

解决方案：在使用group by的或者order by的时候，对应的列尽量为索引列且尽量要生效（关于索引的生效最主要是注意最左前缀原则）

using index ：使用索引 ⭐

select操作中使用了覆盖索引（covering index），避免访问表的数据行

索引覆盖查询效率更高（即复合索引和查询的列顺序数量一致或者对应的查询条件列顺序数量一致），最主要是优化了查询引擎，减少了回表操作和查询，整体提升了查询效率（PS:在使用查询语句如果对应查询的列相对总列较少，可以为对应的列添加索引来提升查询性能，但如果对于列较多的情况，为所有列做索引会大大提高索引文件大小进而降低mysql插叙性能）

特殊的：

如出现using where，表明索引用于执行索引键值的查找
```
select c1 
from t1
where c1 = 'kk'
(c1设置了索引)
```
如没出现using where，表明索引用于读取数据而非执行查找动作
```
select c1 
from t2
where c1 = 'kk'
(c1设置了索引)
```

using where : 使用了where 条件过滤

using join buffer : 使用了join缓冲，即join语句过多会启用，可通过配置修改join buffer大小

using impossible where : 使用了一直查不出任何结果的条件，不能用于获取元组

select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作，对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

distinct：优化distinct操作，在找到第一个匹配的元组后即停止找同样的值

案例解析

第一行（执行顺序4):id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name…】

第二行（执行顺序2): id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,namefrom t1 where other_column="】

第三行（执行顺序3): select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

第四行（执行顺序1): select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

第五行（执行顺序5)﹔代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行

索引优化案例

以一张表为例，查看流行音乐中，点击次数超过100次的歌曲类别

create table music(
id int primary key comment '歌曲id' auto_increment,
name nvarchar(20) comment '歌曲名称',
typeId int comment '歌曲类别',
`count` int comment '点歌次数'
)
--假设1为流行音乐
insert into music
values(null,'金曲1',1,200);
insert into music
values(null,'金曲2',2,200);
insert into music
values(null,'金曲3',1,99);
insert into music
values(null,'金曲4',3,101);

select id,name,typeId,`count`
from music
where typeId = 1 
and `count` > 100
order by typeId

如果觉得文章对你有用，请随意赞赏

MySQL 数据库优化