MySQL进阶：大厂高频面试——各类SQL语句性能调优经验

👨‍🎓作者简介：一位大四、研0学生，正在努力准备大四暑假的实习
🌌上期文章：MySQL进阶：强推，冲大厂必精通！MySQL索引底层（B+Tree）、性能分析、使用规则
📚订阅专栏：MySQL进阶
希望文章对你们有所帮助

上一篇文章介绍了MySQL底层的索引，以及如何巧用索引去优化MySQL的搜索性能，但是我们的业务除了查询，可能还涉及大量的增、改、计数的操作，他们的调优方式也是有不少区别的。

插入数据

insert

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

1、批量插入数据：

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

2、手动控制事务（MySQL自动控制事务的方式是每次在insert之前开启事务，插入后再关闭事务，频繁insert就会多次开关闭事务导致时间浪费）：

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、主键顺序插入，性能会高于乱序插入（思考二级索引的叶子节点，挂载了主键的id，且是顺序的、以双向链表的方式连接的）

大批量插入数据

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

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields 
terminated by ',' lines terminated by '\n' ; 

sql1.log中的数据，也尽量让主键是顺序排列的，将会比乱序排列的方式插入更高效。

主键优化

刚刚提到，主键顺序插入的性能是要高于乱序插入的，在这里会分析一下原因，再分析一下主键该如何设计：

1、数据的组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

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

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

（1）主键顺序插入的效果演示
①从磁盘中申请页， 主键顺序插入

②第一个页没有满，继续往第一页插入

③当第一个页写满之后，再写入第二个页，页与页之间会通过指针连接

④当第二页写满了，再往第三页写入

（2）主键乱序插入效果演示
①加入1#,2#页都已经写满了，存放了如图所示的数据

②此时再插入id为50的记录，并不会开启一个新的页，因为索引结构的叶子节点是有顺序的，按照顺序应该存储在47之后，由于47所在的1#页已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页3#，但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。
移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。
整体的流程如下所示，非常繁琐，显然是非常耗费性能的：






3、页合并
目前表中已有数据的索引结构(叶子节点)如下：

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。



删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

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

4、索引设计原则

• 满足业务需求的情况下，尽量降低主键的长度。（因为聚集索引只有一个，但是二级索引有很多个，每个二级索引树的叶子节点都挂载了主键，主键太长就会浪费空间）
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。（乱序不适合）
• 业务操作时，避免对主键的修改

order by优化

MySQL的排序，有两种方式：

（1）Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
（2）Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

因此，我们在优化排序操作的适合，尽量要优化为Using index。

测试：

1、数据准备
把之前测试时，为tb_user表所建立的部分索引直接删除掉

drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;

2、执行排序

explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age, phone;

由于 age, phone 都没有索引，所以此时再排序时，出现Using filesort， 排序性能较低

3、创建索引

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

4、创建索引后，根据age, phone进行升序排序

explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age ,phone;

查询方式变为了Using index，性能是比较高的。

5、创建索引后，根据age, phone进行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc;

排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现Using filesort。

排序查询需要和条件查询区分开，条件查询中的where子句之所以无所谓字段的位置，只要存在age就可以满足最左前缀法则，是因为where中的and是并列的意思，而排序查询中的顺序就代表了排序的权重，顺序不同，排序的结果就不同。

6、根据age, phone进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc ,phone desc;

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

为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone倒序排序。

7、创建联合索引(age 升序排序，phone 倒序排序)

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

8、再次执行如下SQL

explain select id,age,phone from tb_user order by age asc, phone desc;

升序/降序联合索引结构图示：


从测试中可以得出order by的优化原则：

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

group by优化

分组操作，我们主要来看看索引对于分组操作的影响。

首先我们先将 tb_user 表的索引全部删除掉：

drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone_aa on tb_user;
drop index idx_user_age_phone_ad on tb_user;

在没有索引的情况下执行下列SQL，查看执行计划：

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

然后，我们在针对于profession，age，status创建一个联合索引。

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

紧接着，再执行前面相同的SQL查看执行计划：

再执行如下的分组查询SQL，查看执行计划：


可以发现，如果仅仅根据age分组，会出现效率低下的临时表，即Using temporary，而如果根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作在联合索引中，也是符合最左前缀法则的。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。大家面试可能容易被问到limit的深分页查询效率低下时候的调优策略。

我们可以先通过下面几条测试语句的执行时间来进行分析：

这是因为，select *的操作在底层会回表排序，深分页查询的时候，排序的记录很多，但是返回的仅仅是后面的10条记录，丢弃了之前的所有记录，查询排序的代价就非常大。

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，所以可以通过覆盖索引+子查询的方式进行优化：

-- 覆盖索引：select id from tb_sku order by id limit 2000000,10
-- 上述查询出来的一系列id可以看作是一张表，再利用子查询，这样就不会发生回表查询后再排序
explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

注意子查询中不能有LIMIT，所以不能使用explain select * from tb_sku where id in (select id from tb_sku order by id limit 2000000,10)的方式来优化，只能用上述的方式来

count优化

概述

select count(*) from tb_user;

若数据量很大，上述指令是非常耗时的。

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

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

count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

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

因此，按照效率来排序：count(字段) < count(主键 id) < count(1) ≈ count(*)，因此尽量使用count(*)操作。

update优化（防止行锁升级成表锁）

我们需要注意一下下列update语句执行时候的注意事项：

update course set name = 'javaEE' where id = 1 ;

当我们执行上述语句的时候，会先锁定id=1的数据（锁住这一行，即行锁），然后事务提交后，行锁解放。

当我们执行下列语句：

update course set name = 'SpringBoot' where name = 'PHP' ;

我们可以开启多个事务验证并发性，可以发现无法再操作表了，这是因为此时行锁被升级成了表锁，导致update语句性能大大降低。

要解决这个问题，就需要我们的更新操作的条件判断要尽量针对主键，或者被建立索引的字段，防止行锁被升级成了表锁。