如何对 SQL 语句进行优化

当我们在设计一个项目时，开发阶段的业务数据量相对较少，一些 SQL 语句的执行效率对程序运行效率的影响不太明显，所以对于开发或运维人员来说不会注重对 SQL 语句的优化。但是，随着时间的积累，业务数据量会越来越多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

本篇章主要从以下几个方面对 SQL 语句的优化进行介绍：

插入数据优化
主键优化
order by 优化
group by 优化
limit 优化
count 优化
update 优化

在探索 SQL 优化前，我们必须对 MySQL 索引有一个认识，因为 SQL 语句的优化基本上是基于索引进行的，所以，有不了解索引的小伙伴们可以先去看这篇文章： MySQL 索引及使用规则（优化sql的关键）

一、插入数据优化

insert 语句

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

insert into tb_test values(1,'tom'); 
insert into tb_test values(2,'cat'); 
insert into tb_test values(3,'jerry'); 
.....

（1）批量插入数据（建议一条 sql 语句插入的数据量在 500-1000 之间）

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

（2）手动控制事务：MySQL 默认自动提交事务，每执行一次 sql 语句就提交一次事务，较为频繁，我们可以手动地控制事务，在批量执行完 sql 语句后，手动提交事务。

start transaction; 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); 
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); 
commit;

（3）主键顺序插入，性能要高于乱序插入

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

load 指令大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。操作如下：

-- 客户端连接服务端时，加上参数 -–local-infile ，表示要加载本地文件
mysql –-local-infile -u root -p -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1; -- 执行load指令将准备好的数据，加载到表结构中 
load data local infile '[.sql文件的路径]' into table [表名] fields terminated by '[字段的分隔符]' lines terminated by '[行末结尾符]' ;

这里的字段分隔符是 ',' ，行末结尾符是 '\n'

二、主键优化

在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的（因为乱序插入会产生页分裂）。这一小节，就来介绍一下具体的原因，然后再分析一下主键又该如何设计。

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的（如下图），这种存储方式的表称为索引组织表( index organized table IOT )。

在 InnoDB 引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认 16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充 100%。每个页包含了 2-N 行数据(如果一行数据过大，会行溢出)，根据主键排列。

① 当主键顺序插入时：首先从磁盘中申请一个页，数据按照主键顺序插入页中，当第一个页写满之后，再申请并写入第二个页，页与页之间会通过指针连接，当第二页写满了，再往第三页写入......

② 当主键乱序插入时：如下图，当加入 1#, 2# 页都已经写满了，此时再插入 id 为 50 的记录，按照顺序，应该存储在 47 之后。但是 47 所在的 1# 页已经写满了。那么此时会开辟一个新的页 3#，但是并不会直接将 50 存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在 3# 页，插入 50。此时 1# 2# 3# 页的顺序是不对的，所以，需要重新设置链表指针。这种现象，称之为 "页分裂"，是比较耗费性能的操作。

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

补充：MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

由于每个二级索引的返回列中都会存在主键，所以当我们设计主键时应遵循以下原则：

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

三、order by 优化

MySQL的排序，有两种方式：

① Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sortbuffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

② Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序
操作时，尽量要优化为 Using index。

下面我们通过一个例子看一下如何进行 order by 优化：

① 如果进行排序的字段没有索引，此时进行 order by 排序时出现的是 Using filesort，排序性能较低。

② 如果为 age, phone 字段创建一个联合索引，

create index idx_user_age_phone_aa on tb_user(age, phone);

a. 如果对 age 和 phone 进行升序查询（默认），就由原来的 Using filesort，变为了 Using index，性能就是比较高的了。

b. 而如果对 age 和 phone 进行降序查询，也出现 Using index，但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。（MySQL 8 之后支持）

c. 如果先查 phone，后查 age，会出现 Using filesort，因为要遵循最左前缀原则，而 order by 查询条件与 where 查询条件不一样，order by 的查询顺序和 SQL 语句中字段的顺序保持一致，索引与 SQL 语句中的顺序有关，而 where 的各个字段不存在顺序问题，与位置无关，只看是否存在

d. 如果对 age 升序查询，phone 降序查询，就会出现 Using filesort。因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现 Using filesort。

③ 为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。（MySQL 8 之后支持）

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

创建好索引后，再次对 age 升序查询，phone 降序查询是，就显示 Using index 了。

order by 设计原则：

由上述的测试，我们得出 order by 优化原则：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规（ASC/DESC）
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

四、group by 优化

首先在没有索引的情况下，执行如下 SQL，查询执行计划：

然后，我们再针对于 profession ， age， status 创建一个联合索引。紧接着，再执行前面相同的SQL查看执行计划。

create index idx_user_pro_age_sta on tb_user(profession, age, status);

继续更改查询条件我们可以得到：使用：

explain select age, count(*) from tb_user where profession = '软件工程' group by age;
explain select age, count(*) from tb_user where age = 17 group by profession;

等语句，就不会出现 Using temporary，而使用：

explain select age, count(*) from tb_user group by age;
explain select profession, count(*) from tb_user group by age, profession;

等语句，就会出现 Using temporary，所以，group by 也是符合最左前缀法则的。

group by 设计原则：

由上述的测试，我们需要通过以下两点对 group by 进行优化，以提升性能：

在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。

五、limit 优化

在数据量比较大时，如果进行 limit 分页查询，在查询时，越往后，分页查询效率越低。例如：当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。即，不进行回表查询，把在二级索引查询的结果作为子查询。

explain select s.* from tb_user t , (select id from tb_user order by id limit 2000000,10) a where t.id = a.id;

六、count 优化

在使用 count(*) 聚合函数对数据量进行统计时，如果数据量很大，在执行 count 操作时，是非常耗时的。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的 count，MyISAM 也慢。
InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升 InnoDB 表的 count 效率，主要的优化思路：自己计数(可以借助于 redis 这样的数据库进行,但是如果是带条件的 count 又比较麻烦了)。

补充：当使用 count(字段) 时，返回值不一定等于 count(*)，因为若某字段的值为 null 时，则不进行计数。

count( ) 的几种用法：

count用法	含义
count( 主键)	InnoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加( 主键不可能为 null )
count( 字段)	没有 not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null ，不为 null ，计数累加。有 not null 约束： InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count( 数字)	InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 “1” 进去，直接按行进行累加。
count(*)	InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(*) ≈ count(1) > count(主键) > count(字段)，所以尽量使用 count(*)。

七、update 优化

当我们执行下面一条更新语句时（ id 为主键）：

update user set name = '李四' where id = 1;

在执行删除的 SQL 语句时，会锁定 id 为 1 这一行的数据，然后事务提交之后，行锁释放。

但是如果我们执行如下 SQL 语句（name 字段既不是主键，也没有索引）：

update user set name = '李四' where name = '张三' ;

当我们开启多个事务，在执行上述的 SQL 时，我们发现行锁升级为了表锁。导致该 update 语句的性能大大降低。

所以可以得出结论：InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁。