如何优化 MySQL

为什么要对 SQL 进行优化

有时候数据库会出现性能低、执行时间太长、等待时间太长、SQL 语句欠佳（连接查询）、索引失效等问题，这些问题会严重拖慢一个系统的速度，因此需要对 SQL 进行优化。

SQL 的编写过程和解析过程并非是一致的，下面是两者执行的先后顺序：

编写过程：

select..from..join..on..where..group by...having..order by...limit.

解析过程：

from..on..join..where..group by....having...select..order by...limit..

SQL 如何优化
SQL 优化，主要就是在优化索引。索引相当于书的目录，是帮助 MySQL 高效获取数据的数据结构。就好比查字典，如果没有目录查一个字就需要遍历整本字典，而有了目录之后只需要按目录查询。索引的数据结构有（树：B+树（默认）、Hash 树等等）。

索引的优势

1. 提高查询效率（降低 IO 使用率）。
2. 降低 CPU 使用率。

索引的弊端

1. 索引本身很大，需要存放在内存/硬盘（通常为硬盘）。

2. 索引不是所有情况均适用，以下三种情况不适合用索引：少量数据、频繁更新的字段、很少使用的字段。

3. 索引提高了查询速度，但是会降低增删改的效率。

关于索引的分类

1. 单值索引：单列的索引，比如学生表中的 name。一个表可以有多个单值索引。

2. 唯一索引：与单值索引的区别是属性不能重复。比如主键 id。

3. 主键索引：与唯一索引的区别是内容不能为 null。

4. 复合索引：多个列构成的索引，（name，grade）构成索引后先查 name，再查 grade。

如何创建索引

方法一：create 索引类型 索引名 on 表（字段）：

单值索引：
create index name_index on student(name);
唯一索引：
create unique index id_index on student(id);
复合索引:
create index name_grade_index on student(name,grade);

方法二：alter table 表名 add 索引类型 索引名(字段)：

单值索引：
alter table student add index name_index(name);
唯一索引：
alter table student add  unique index id_index(id);
复合索引:
alter table student add index name_grade_index(name,grade);

删除索引

drop index 索引名 on 表名

explain 关键字

通过 explain 关键字可以看到 SQL 语句的执行过程中的一系列问题。explain 经常被用来查找慢 SQL 的原因。

explain 关键字的使用很简单，只需要在执行的 SQL 前加上 explain 即可。

输出的结果中的 type、key、key_len、Extra 需要尤其注意。

1、 id：标识符

如果有多个 id，id 值相同，顺序执行；id 值不同，id 值越大越优先查询。

2、 select_type：查询类型

• primary：包含子查询 SQL 中的主查询（最外层）。

• subquery：包含子查询 SQL 中的子查询（非最外层）。

• simple：简单查询（不包含子查询、union）。

• derived：衍生查询（使用到了临时表）。

• union：当查询时用到了 table1 union table2，table1 类型是 derived，table2 的类型是union。

• union result：从 union 表获取的 select。

3、 table

查询的是哪张表。

4、 type：索引类型

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

system 和 const 存在于理想状态，实际能达到 ref，索引的优化一般到 ref 为止。

const：仅仅能查到一条数据的 SQL，用于主键索引和唯一索引。

eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有 1 个，不能多、不能 0）。

ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行。

range：检索指定范围的行，where 后面是一个范围查询（between，>，<)等。

index：查询全部索引中的数据。

all：查询全部表中的数据。

5、 possible_keys

可能用到的索引。

6、 key

实际用到的索引。

7、 key_len

索引的长度，用于判断复合索引是否被完全使用。

8、 ref

指明当前表所参照的字段。

9、 rows

被索引优化查询的数据个数（实际通过索引查询到的数据个数）。

10、 Extra

1. using filesort：性能消耗大,需要“额外”的一次排序，常见于 orderby 语句。

2. using temporary：性能损耗大，用到了临时表，一般出现在 groupby 中。

3. using index：性能提升；索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据。

4. using where：回表查询。

索引优化实例

建一张 book 表，插入一些数据：

create table book
 (
     bid int(4) primary key,
     name varchar(20) not null,
     authorid int(4) not null,
     typeid int(4) not null
);
insert into book values(1,'java',1,1);
insert into book values(2,'c',2,2);
insert into book values(3,'math',3,3);
insert into book values(4,'english',4,3);

首先不建立索引查看结果：

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

虽然没有主动建立索引，但是 mysql 自动建立了主键索引，现在的 type 是 index，继续优化。

添加一个复合索引，将查询时所用到的属性均加入进去：

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

再次运行查询语句：
此时 type 从 index 升级到了 range，并且查询走了 a_t_b 索引。虽然看上去是一个小小的优化，实际在生产环境上能提升几倍或几十倍的查询速度。

加索引的技巧

1、 小表驱动大表：

select ... from .... where 小表.x=大表.x;

2、 索引建立在经常使用的字段上。

3、exist 和 in：

如果主查询的数据集大，则使用 in，如果子查询的数据集大，则使用 exist：

select .. from table where exist/in (子查询)

4、order by 优化：

1. 避免 select *的使用

2. 复合索引不要跨列使用

3. 保证所有排序字段排序顺序的一致性（都是升序或降序）。

5、最左前缀原则

建立组合索引 (a,b,c)，则可以利用到索引的查询条件是：

• where a = ?
• where a = ? and b = ? （注意与条件的先后次序无关，也可以是 where b = ? and a = ?，只要出现即可）
• where a = ? and b = ? and c = ? （注意事项同上）

不能利用的例子：

• where b = ?
• where b = ? and c = ?
• where c = ?

特殊情况：

• where a = ? and c = ?（a = ? 会利用索引，但 c = ? 不能利用索引加速，会触发索引条件下推）

避免索引失效的原则

1、复合索引不要跨列或无序使用（最佳左前缀）：索引的顺序和 SQL 语句查询时的顺序一致。

2、复合索引尽量使用全索引匹配。

3、不要在索引上进行任何操作（计算、函数、类型转换）。

4、like 尽量以“常量”开头，不要以%开头，否则索引失效。

5、尽量不要使用类型转换（显示、隐式），否则索引失效，如 name 的属性是 varchar，这里变成了 int：

select * from teacher where name=123

6、两个表或者字段编码格式不同导致索引失效，比如 table1 编码格式是 utf8，table2 编码格式是 utf8mb4，两个表 join 时 on 字段就无法走索引。