MySQL 进阶（三）【SQL 优化】

2024-07-15 06:59:27

1、SQL 优化

1.1、插入数据优化

1.1.1、Insert 优化

1、批量插入

插入多条数据时，不建议使用单条的插入语句，而是下面的批量插入：

INSERT INTO tb_name VALUES (),(),(),...;

批量插入建议一次批量 500~100 条，如果数据量比较大，建议通过多条批量插入语句来插入；

2、手动提交事务

MySQL 默认会开启事务，但是默认每执行一次插入语句就开启和关闭一次事务，所以会有大量的事务启动关闭开销；建议使用手动提交事务

START TRANSACTIONS;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
...
COMMIT;

3、主键顺序插入

在插入数据时，建议尽量顺序插入主键，而不是乱序插入：

主键顺序插入(高效)：1 2 3 4 5 ...

主键乱序插入(低效)：5 3 4 2 1 ...

1.1.2、load 大批量插入数据

使用 load 指令将本地磁盘文件中的数据插入到数据库表当中：

# 客户端连接MySQL服务器时，加上参数 --local-infile
mysql --local-infile -uroot -p
# 设置全局参数 local_infile = 1
set global local_infile = 1;
# 执行 load 指令将准备好的数据加载到表中
load data local infile '/path_to_data' into table table_name fields terminated by ',' lines terminated by '\n';

这里的 load 指令有点像 HQL 中的 load：

LOAD DATA [LOCAL] INPATH '/opt/module/data/xxx.txt' TO TABLE table_name;

HQL 的 load 命令并不需要指定分隔符，因为在建表的时候我们已经在 row format 中设置的文件的分隔符了；此外，这里想到今天使用 HQL load 命令的一些需要注意的问题：

在向分桶表 load 数据的时候，不能从 local 直接 load，而是得先上传到 hdfs 上，再从 hdfs load 到分桶表才行

1.2、主键优化

1.2.1、数据组织方式

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

之前我们在学习索引的时候知道，InnoDB 存储引擎中的索引可以分为聚集索引和二级索引，而聚集索引正是由主键构成的一颗B+Tree，它的叶子节点存储的是行数据；

接下来，我们看一下当我们往数据库表中插入数据的时候，它的流程是什么样的？

1.2.2、页分裂

page 可以填满，也可以为空，但在 InnoDB 存储引擎规定，在一个 page 中至少包含 2 行数据；

主键顺序插入

当主键顺序插入时，一切都非常平静：

主键乱序插入

下面，我们看一下主键乱序插入时的情况：

上面，我们的主键都是乱序插入的，可以看到，现在来了一个主键为 50 的 row，按道理它是应该放到主键为 47 的前面，主键为 23 的后面（也就是 page 1）的，但是显然 page 1 现在已经存满了，那么接下来就会发生页分裂：

首先，page 1 （因为主键为 23 的数据当前理应插入到 page 1）会把自己 50% 之后的数据移动到一个新的 page 当中，然后将要主键为 23 的新数据页添加进去，最后将原本页之间的连接断开，重新建立页的连接；

所以，不难想到如果是大量数据的场景下，主键乱序插入时会出现频繁的页分裂现象，性能很低；与新增数据相反的是删除数据，删除数据又会引起页合并：

1.2.3、页合并

在 MySQL 中，当删除一行记录时，实际上数据并不会被物理删除，只是记录被标记为删除并且它的空间变得允许被其它记录回收使用；

当页中被删除的记录达到了 50%，InnoDB 会开始寻找最靠近的页（前或后），看看能否将两个页合并以优化空间使用；

1.2.4、主键设置原则

满足业务需求的情况下，尽量降低主键的长度。（因为在一张表中，聚集索引只有一个，而二级索引可以有多个，如果主键很长，二级索引也有很多，那么就会在存储时会消耗大量的磁盘空间，查询时也会消耗大量的磁盘IO）
插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键；（不会出现页分裂现象）
尽量不要使用 uuid 做主键或者是其它自然主键，如身份证号；（主键长度太长，而且无序）
尽量避免主键的修改；

1.3、order by 优化

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

也就是说，我们在优化 order by 的时候，尽量优化成 using index；下面我们看一条普通的 order by 语句：

可以看到，在未建立索引时，order by 语句默认走的是 filesort；下面我们为 age 字段建立一个索引：

CREATE INDEX idx_student_no ON student(no);

注意：这里搜索时，我设置投影的字段是 id 和 no，其中 id 是主键，no 我们刚创建了索引，不能使用 select * ，因为只有使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中全部能够找到对应的值）才能命中索引，不然索引不生效；

可以看到，即使是降序排序也是索引也是可以命中优化的；

现在我们创建一个联合索引：idx_student_no_name，做一个测试：

可以看到，当 order by 的字段顺序和联合索引相反时，只有字段 no 能被索引命中，name 不可以，这是因为违背了最左前缀法则；继续：

可以看到，当 order by 的两个字段分别升序和降序排列时，降序的字段无法被索引命中，这是因为：

这是因为默认创建索引时，索引字段都是按照升序进行排列的，所以我们可以根据之后的需求在创建索引时，根据排序规则进行创建：

CREATE INDEX idx_student_no_name_ad ON student(no asc,name desc);

此时，再次查看查询计划：

可以看到，这样就解决了字段排序规则不同的问题；

注意：

只有联合索引需要注意创建时的排序规则，单列索引不需要，因为单列索引默认升序，反向扫描只需要反向扫描即可。
如果不可避免 filesort ，大数据排序时，可以适当增加排序缓冲区的大小 sort_buffer_size （默认 256 K）

1.4、group by 优化

这里我们同样讨论的是索引对于 group by 的影响，因为分组也是通过索引来提高查询效率的：

可以看到，在未建立索引前，使用 group by 语句时效率很低（临时表），但是创建索引后就可以走索引了；

可以看到，这次当我们用 age 字段 group by 时，又出现了 temporary ，效率依然不够好，这是因为违背了联合索引的最左前缀法则；可当我们同时使用 profession 和 age 进行 group by 时，索引再次命中；

但是有时候业务逻辑不需要我们去 group by 那么多的字段怎么办？

其实，就像上面这样，我们也可以通过前面给联合索引左边的字段加个 where 条件，让它满足最左前缀法则即可；

1.5、limit 优化

在大数据场景下，比如 limit 10000000,10 ，此时 MySQL 需要排序前 100000010 条记录（存储引擎会把这 100000010 条记录返回给服务层的缓存），并返回后 10 条记录，其它丢弃，查询排序的代价非常大；这一类问题也叫做深度分页：

MySQL 深度分页是指在分页查询数据量比较大的表时，需要访问表中的某一段数据，而这段数据的位置非常靠后，需要通过较大的 offset 来获取目标数据。

阿里巴巴《Java 开发手册》：

1.5.1、覆盖索引 + 子查询

1.5.2、inner join 延迟关联

上面这两种方式没什么区别，下面是 inner join，上面是笛卡尔积，但是因为设置的过滤条件，所以等价于一个 inner join；对于 limit 的优化原理，简单来讲就是：控制返回的总页数。

对于上面的 limit 10000000,10 来说，它会返回给服务端 10000010 条记录，然后再根据 offset 挨个抛弃前 10000000 条记录，返回给客户端剩余的 10 条记录。

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，这就让原本就耗时的操作变得更加离谱。

因为前面的offset条数据最后都是不要的，就算将完整字段都拷贝来了又有什么用呢，所以我们可以将sql语句修改成下面这样：

select * from tb_user where id >=(select id from tb_user order by id limit 10000000, 1) order by id limit 10;

上面这条sql语句，里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到6000000+1条数据，然后server层会抛弃前6000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的id之后，假设这个id正好等于10000000，那sql就变成了

select * from tb_user where id >=(10000000) order by id limit 10;

这样 innodb 再走一次主键索引，通过B+树快速定位到id=6000000的行数据，时间复杂度是lg(n)，然后向后取10条数据。

关于深度分页，知乎这篇文章讲的很不错，我也是受启发于这篇文章；

1.6、count 优化

count 是一个聚合函数，对于返回的结果集，一行一行进行判断，只有不是 NULL 才会计数（count(字段)的时候，count(*)或者count(id)依然计算null）；

SELECT COUNT(*) FROM table_name;

上面的 SQL 是查询当前表的总行数，不同的存储引擎的效率是不一样的：

对于 MyISAM 而言，它会把表的总行数存到磁盘上，所以不加条件直接查询 count(*) 会直接返回结果，O（1）的时间复杂度；
而对 InnoDB 而言，它就只能遍历整张表了，性能很低；

可以看到，如果 count(*) 的查询语句中包含 where 过滤条件，不管是 MYISAM 还是 InnoDB ，性能都很差，所以我们需要对它进行优化：

1.6.1、count 的几种用法

对于 count，我们使用的无非就是那几种：

count(主键)
- InnoDB 会遍历整张表，把每一行主键取出来返回给服务层，服务层拿到主键后直接进行累加（主键不可能为空）；
count(字段)
- InnoDB 会遍历整张表，把每一行字段值取出来返回给服务层，如果字段有 not null 约束，那么就直接按行累加；如果没有，那么就对非 null 的值进行计数
count(1)
- InnoDB 遍历整张表，但是不取值。服务层会对返回的每一行放一个数字 1 进去，直接按行进行累加
count(*)
- InnoDB 同样遍历整张表，但是不会把字段取出来，而是专门做了优化。服务层直接按行进行累加

按照排序效率：count(字段) < count(主键) < count(1) < count(*) ，所以尽量使用 count(*)，因为数据库专门对它做了优化；

1.7、update 优化

关于 update 语句需要注意的就是，update 的条件一定要是索引字段（比如主键），因为只有更新条件的字段是索引列才会是行锁，否则将是表锁

可以看到，当我们的更新条件是 no 字段时（不是索引列），当另一个客户端去更新数据时直接被阻塞，最后甚至超时更新失败；

所以，当我们在使用 update 语句的时候，一定要注意尽量使用索引字段做为更新条件去更新，否则就会出现行锁升级为表锁，并发性能就会降低；因为 InnoDB 的行锁是针对索引加的锁，而不是针对记录加的锁！

总结

插入数据
- inset 语句，大数据量（分成500~1000的记录批量插入）建议使用批量插入，而且建议手动事务（避免频繁创建销毁事务开销）、主键顺序插入（避免页分裂，顺序插入性能高）
- 大批量数据：load data local infile
主键优化
- 主键设计应尽量短、顺序插入（建议 auto_increment 而不是 uuid，比如身份证号，不仅长度长，而且无序）
order by 优化
- using index：直接返回数据（不需要再去服务层缓冲区排序），性能高
- using filesort：需要将查询返回的结果去服务层缓冲区去排序
- 所以在对 order by 进行优化时，其实是使用索引来进行优化的；涉及导排序的字段尽量建立索引，同时注意创建索引时的升序降序问题
- 尽量使用覆盖索引而不是 select *
group by 优化
- 索引，多字段分组时遵循最左前缀法则
limit 优化
- 深度分页性能低，使用覆盖索引 + 子查询
count 优化
- count(字段) < count(主键) < count(1) < count(*)
update 优化
- update 的条件字段尽量使用索引字段（尽量主键），InnoDB 的行锁是针对索引加的锁，而不是针对记录加的锁！

所以，总之我们在做 SQL 优化的时候，其实基本都是在针对索引进行优化；

码农公寓