MySQL 优化学习记录

1、索引

索引只是一种数据结构而已，具体看 Mysql 的数据库引擎。

比如 MyISAM 使用 B 树，InnoDB 使用的 B+ 树。

（聚簇索引与非聚簇索引是 b 与 b+ 树的两个别名）

缺点：

• 1、少量数据不需要

• 2、频繁更新的数据不适合作为索引

• 3、很少使用的字段

• 4、索引提升查询效率、降低增删改的效率

• 5、索引占用空间很大（但是机械硬盘也不值钱了）

优点：

• 1、查询效率高

• 2、CPU 占用少（order by XXX desc）b 树、b+树不用排序，所以 CPU 计算少

b+ 树也是 b 树的一个种类，b 树查询一个值时间复杂度就是 O(n)

1、索引细节

分类：

索引一般有三种： 单值索引、唯一索引、复合索引。

• 单值索引：单列的索引，一个表可以有很多单值索引

• 唯一索引：所有值不能重复，即 distinct

• 复合索引：多个列组成的二级索引

创建索引：

方式一：create

单值： create index 索引名称 on 表(字段名称)

唯一：create unique index 索引名称 on 表(字段名称)

复合：create index 索引名称 索引名称 on 表(字段名称) `

方式二：alter

单值： alter table 表名 add index 索引名称(字段名称)

唯一：alter table 表名 add unique index 索引名称(字段名称)

复合：alter table 表名 add index 索引名称(字段名称, 字段名称)

注：primary key 设置自动是主键索引。主键与唯一索引均不能为 null。

查询/删除索引

show index from 表名 / drop index 索引名 on 表名

2、SQL 优化之执行计划

SQL 优化的主要的原因即是：性能低、SQL 语句执行时间长、等待时间长、索引失效、服务器参数不合理等

注意： 有索引才能涉及到 SQL 优化，没有索引一般都是 ALL 级别

先说 SQL 语句的执行，在 Mysql 数据库引擎进行数据解析的过程中，识别SQL语句后，实际的执行过程与编写的语句不一致，一般执行过程如下：

from...on...join...where...group by...

having...select (distinct)...order by... limit

优化时候，必须解决的问题就是 SQL 语句的执行计划。SQL 执行计划的关键字：explain，可以模拟 SQL 优化器执行 SQL 语句，从而让开发人员具体知道自己写的 SQL 语句的执行。

使用方法 ：explain + SQL 语句

如 explain select * from tables

字段值如下图所示，图片点击可以放大查看，更清晰：

1、id 值相同的执行计划

id 值相同，即看表后的 table 从上到下的顺序进行执行

而此种情况下的表查询的顺序，会因为表内数量的个数改变而改变。

原因是计算权重的算法就是笛卡尔积笛卡尔积是连乘，产生的中间过程的数据量，理应越小越好。

所以在执行计划中，在表最终的结果积一致的情况下，表遵循表内数据从小到大的顺序进行执行。

2、id 值不同的情况下的执行计划

id 值不同时，id 值越大越优先进行执行。本质即是多表联结查询转变为子查询，子查询是先执行最内层查询，在执行外层查询，所以即是 id 值越大，越优先进行执行。

3、select_type 查询类型

此字段有以下几种显示：

1、primary：此为主查询方法，为最外层的查询

2、subquery：此为主查询的对立方法，即子查询方法，为内层查询

3、simple： 此为简单查询，即不涉及子查询与 union 查询

4、derived：此为衍生查询，会创建一个临时表。

(1)：如果在 from 子查询中只涉及一个表，则该表为临时表。

(2)：如果在 from 子查询中涉及两个表，则左表是临时表。

4、type 索引类型

一般企业中常用的索引类型如下：

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

以上类型的索引效率越往左越高。

其中，system 与 const 只是理想情况下的效果，一般 SQL 优化，在 ref ~ range 之间

• system (忽略)：只有一条数据的系统表，或衍生表只有一条数据的主查询

• const (忽略)：仅仅能查到一条数据，只能用于 primary key 或 unique index

• eq_ref (尽量满足，但是可遇不可求)：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据 ( 只能为 1，不能多、也不能是 0 )，常见于主键索引与唯一索引。即查询的主数据表与临时的表内的数据数量必须一致，才能达到 eq_ref 的效果。

• ref ：非唯一性索引，对于每一个索引键的拆线，返回匹配的所有行 ( 0，多 )

• range： 检索指定范围的行，where 后面为范围查询的情况（between、in，>，< 等），但是 in 有时候会索引失效，转变为 ALL 级别

• index：查询全部索引的数据，索引查询肯定要小于等于全表查询

• all：查询全部表中数据

总结：system、const 结果只能有一条数据，eq_ref 结果是多条，但是每条数据具有唯一性，ref 结果多条，但是每条数据是 0 或 多条。

5、possible keys 、key 、key_len 、ref 、rows 、Extra

possible keys 为预测的索引值，不准确

key 为实际使用的索引。注意：如果possible keys / key 均为 null， 则没使用索引

判断复合索引是否完全被使用。如果复合索引有一个索引允许为空，那么key_len 长度会默认+1B，作为空标识符。2 B 标识 varchar 的可变长度。

ref 为：指明当前表所参照的字段。注意：与 type 中的 ref 进行区分

row 为：被索引优化查询的个数，即量级

Extra 常见有以下字段：

• 1、using filesort : 性能消耗大，需要进行额外一次排序(查询)

  • 对于单索引，如果排序查找的是同一字段，则不会出现 filesort ，如果排序与查找的不是同一字段，则需要使用 using filesort
  • 在复合索引下，避免使用 using filesort，就使用 where 与 order by 按照复合索引的顺序使用，不要跨列就不用使用（最佳左前缀）

• 2、using temporary：出现性能损耗，用到了临时表，一般出现 group by 语句中

• 3、using index：性能提升，覆盖索引。原因：不读取原文件，只从索引文件读取数据，不需要回表查询，但是使用 using index 时，会对 possible_keys 和 key 造成影响：

  • 1、没有 where 索引出现在 key 中；
  • 2、有 where 则索引出现在 key 和 possible_keys 中。

• 4、using where ：既查索引，又查原表。（即回表查询）。

• 5、impossible where：where 字句永远 false

3、单表优化

在进行单表优化时，主要注意以下几点：

• 1、where 与 order by 联合使用索引查询，尽量不要跨列进行搜索。

• 2、如果复合索引和使用索引顺序全部一致(且不跨列使用)，则复合索引全部使用，如果部分使用(且不跨列使用)，则使用部分索引。

举个例子，假设我们有一张 book 表，建表语句如下所示，我们也先添加四个数据：

create table book 
(
	bid int(4) primary key,
	name varchar(20) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null
);

insert into book values(1, 'tjava', 1, 1, 2);
insert into book values(2, 'tc', 2, 1, 2);
insert into book values(3, 'wx', 3, 2, 2);
insert into book values(4, 'math', 4, 3, 2); 

然后我们想查询一 authorid = 1 且 typeid 为 2 或 3 的 bid， 其查询语句如下：

select bid from book where typeid in (2,3) and authorid = 1;

explain select bid from book where typeid in (2,3) and authorid = 1;

发现没有索引的情况，默认发生的是全表查询，即 explain 执行计划 type 字段值为all ，extra 字段为 using where，而后我们为了让优化的效果更加明显一点，再配合order by desc 来使用，其相应的 sql 语句如下所示：

select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc ;

此时我们会发现，extra 字段会出现 using filesort 字段值，即查询全表后还对返回的数据重新排序，这样很浪费性能，所以我们对其添加索引优化，如下：

alter table book add index idx_bta (bid, typeid, authorid);

然后再执行上述的sql 语句，结果如下图：

可见，增加索引后可以将全表查询变为索引查询，type 等字段发生改变，但是 using where 与 using filesort 还是未能优化掉，所以说此条 SQL 语句还能继续进行相应的优化。

而sql 语句的优化，即是我们之前说的，理解sql语句在mysql 中的执行顺序，那么按照执行顺序进行索引的优化即是最好。

比如我们这里是先查 bid ，再查 typeid 与 authorid。但是mysql 中，select 语句执行时在where 之后，所以优化时，索引应为 (typeid, authorid, bid)。

而 索引 bid 是否能去除呢？

这里我选择不去除，因为虽然删除 bid 索引后，根据索引也能回表进行查询，但是如果只查一次索引即可得到数据，还是只建议只查索引，虽然这样会导致索引很大，但是牺牲空间换时间也是一种常用的做法。

继续回到原 SQL 的语句：

select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc ;

这里注意： 如果是范围查询的 in 语句，有时候会实现，有时候会失效，所以我们需要再开始使用索引时走一个必定实现的索引，所以更改语句如下：

select bid from book where authorid = 1 and typeid in (2,3) order by typeid desc;

explain select bid from book where authorid = 1 and typeid in (2,3) order by typeid desc ;

同时，我们需要先删除之前添加的索引，防止之前的索引干扰，使用如下：

drop index idx_bta on book;

alter table book add index idx_atb (authorid, typeid, bid);

通过 explain 查看相应的执行计划，如下图，可见 type 级别从 index 变为了 range ，提升了一个级别。

其中有字段 Backward index scan 这是 MySQL8 的新特性，叫做降序索引，在颜群老师的课程中，执行计划显示 type 优化为 ref 级别，而在 MySQL8 中则是 range 级别，可见在 MySQL8 中，这样优化达不到 Mysql5.x 的优化效果。

using where 与 using index 最大的区别即是需不需要回原表进行查询。而两者同时出现，即是 in 范围查询有时候失效，有时候不失效的情况。

所以为了消除这种情况，一般情况用其他有效条件替换 in 即可，如以下 SQL 语句

select bid from book where authorid = 1 and typeid = 3 order by typeid desc;

explain select bid from book where authorid = 1 and typeid = 3 order by typeid desc;

其 explain 结果如下图，可见 type 字段变为 ref 值，优化上升两个级别，而一般达到ref 或者 eq_ref 级别，已经是比较好的一种情况了。

4、多表优化

最好的学习方式，还是举例进行学习！所以跟单表优化一样，还是进行举例：

创建两个表 teacher 与 course，其建表语句如下：

create table teacher2 
(
	id int(4) primary key,
	cid int(4) not null
);

create table course2
(
	cid int(4),
	cname varchar(20)
);

insert into teacher2 values (1,2);
insert into teacher2 values (2,1);
insert into teacher2 values (3,3);

insert into course2 values (1,'java');
insert into course2 values (1,'python');
insert into course2 values (1,'kotlin');

然后我们将两表通过左连接进行两表连接，查询 cname = ‘java’ 的值：

select 
	* 
from teacher2 as t 
left outer join course2 as c on t.cid = c.cid 
where c.cname = 'java';

其相应的执行计划如下：

可见 type 为 all，即全表查询，而且 extra 出现一个新的字段值： using join buffer，出现此字段就是说明 sql 写的很差，MySQL 底层使用优化器对 sql 进行了优化，即使用连结缓存。但是由于不涉及索引查询，所以必然还是很慢。

所以两表的索引应该如何去加呢？直接上结论：小表驱动大表，写法如 小表.X = 大表.X ，原理就是根据 CPU 与内存的空间局部性原理，不用频繁进入磁盘访存拿出数据。

所以，关于两表索引的增加，一般是先看连接，比如左外连结在左表上加索引、右外连结在右表上加索引。小表中使用频繁的字段加索引。

故加索引如下：

alter table teacher2 add index index_teacher2_cid(cid);

alter table course2 add index index_course2_cname(cname);

此后执行效果直接提升为 ref 级别，而且不再出现 using join buffer，证明 SQL 语句优化的还行。

而关于三表优化只需要记住两个个原则即可

• 1、小表驱动大表
• 2、索引建立在经常查询的字段上

注意 ：SQL 优化是一种概率事件！是否实际使用了我们的优化，需要通过 explain 进行查看

5、避免索引失效的一些原则

• a、复合索引；
  • 复合索引不跨列或者无序使用（最佳左前缀匹配）
  • 复合索引，尽量使用全索引匹配。
• b、不要再索引上进行任何操作（函数计算、类型转换等），否则都会使索引失效；
  • 而且复合索引中，如果对左侧的索引进行操作，那么包括此索引的右侧索引全部失效。、
  • 复合索引不能使用不等于（!=><）或 is null （is not null），否则自身以及右侧的索引全部失效。
  • 复合索使用等于（=><）有部分概率使自身以及右侧的索引全部失效。
  • SQL优化由于SQL 优化器等原因，并非100%成立。一般而言，范围查询的（> < in）之后的索引失效
  • 补救方式：尽量使用覆盖索引。
• c、like 后尽量以“常量”开头，不要以%开头，否则索引失效；
• d、尽量不要使用类型转换（显示、隐式）否则索引失效；
• e、尽量不要使用 or ，否则索引失效，左右的索引都会失效

6、索引优化方法

• 1、exist 与 in 使用情况
  • 主查询数据集大：in
  • 子查询数据集大：exist
• 2、order by 优化
  • using filesort ，有两种算法：双路排序与单路排序（根据 I/O 的次数）
  • 选择使用 单路、双路，调整 buffer 的容量大小。
  • 避免使用 select *
  • 复合索引不跨列
  • 保证全部的排序字段，排序的一致性（都是升序与降序）

7、SQL排查-慢查询

慢查询日志是 MySQL 提供的一种日志记录，用于记录 MySQL 中响应时间超过阈值的 SQL 语句。

配置文件中显示为 : long_query_time，默认为 10s 。

慢查询日志默认是关闭的：开发时建议打开，部署时候关闭。

一般是临时开启，如果需要永久开启，直接在配置文件中追加配置即可。

linux 上也能通过使用 mysqldumpslow 工具对慢 SQL 语句进行排查，可以通过设置，对sql语句进行快速筛选。
使用命令 mysqldumpslow --help

• s : 排序方式
• r : 逆序
• l : 锁定时间
• g : 正则匹配

参考 bash 写法如下

# 获取返回记录最多的 3 个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

#获取访问次数最多的 c3 个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

具体细节，在实践中学习即可。