投稿
  1. 极客之音首页
  2. 技术随笔

SQL语句优化（落实到代码，不绕弯子）

飞熊 • • 技术随笔 • 阅读 204

追求适度，才能走向成功；人在顶峰，迈步就是下坡；身在低谷，抬足既是登高；弦，绷得太紧会断；人，思虑过度会疯；水至清无鱼，人至真无友，山至高无树；适度，不是中庸，而是一种明智的生活态度。

导读：本篇文章讲解 SQL语句优化（落实到代码，不绕弯子），希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

文章目录

看完本篇文章你能学到什么？

1、排序优化

2、分组优化

3、分页优化

4、表优化

SQL语句优化

本篇内容全部围绕SQL语句如何优化，告诉大家实际的优化手段（落实到代码），不绕弯子。

注： 阅读本篇文章需要具备执行计划、索引失效等相关知识。

  • 创建一张测试表，用于后期测试使用：
CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int(3) NULL DEFAULT NULL COMMENT '年龄',
  `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男   ‘1’-女',
  `desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍',
  `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
  `login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间',
  `pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址',
  `look` int(10) NULL DEFAULT NULL COMMENT '查看数',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

1.1 排序优化

1.1.1 索引优化

在排序时，尽量使用索引字段进行排序，否则会采用文件排序（filesort），效率低。

  • 根据普通字段排序：
show index from userinfo;								-- 查询当前表有多少索引

explain select * from userinfo order by username;		-- 根据普通字段排序

在这里插入图片描述

  • 给排序字段创建索引：
create index idx_name on userinfo(username);							-- 创建索引

explain select * from userinfo order by username;						-- filesort

explain select username from userinfo order by username;				-- Using index

explain select username,age from userinfo order by username;			-- filesort

在这里插入图片描述

根据索引字段排序，之后查询的数据必须是索引数上的数据，不可查询额外字段，更不能查询全表字段

其实这一段算是废话来着，谁不知道使用索引能够加快速度啊！还用你说？？？

好吧，其实这里只想跟你说一个东西：文件排序。

就是上面看到的Using filesort，其实filesort并不代表MySQL使用到了文件进行排序，文件排序只是一种算法，我们习惯把它翻译成”文件排序”而已。一旦看到了filesort，那么就意味着效率很低了。

1.1.2 算法优化

MySQL在排序时，如果不能够借助索引直接完成排序，那么将会使用文件排序（filesort）。如果使用了filesort，那么MySQL会将数据在内存中进行排序，排序内存由系统变量sort_buffer_size控制。默认为256KB。
在这里插入图片描述

注意，排序缓冲区是每个线程是独享的。因此设置太大在并发量高的情况下会消耗MySQL服务器大量内存。

MySQL的排序算法分为两种：

  • 1）多扫描排序：首先根据排序条件取出排序字段的行指针信息，然后在排序缓冲区（sort_buffer_size）中进行排序。排序完毕之后会根据排序缓冲区中的行指针回表查询。操作磁盘次数多（两次），效率较低。
  • 2）单扫描排序：根据条件取出所有字段的信息（不仅仅是排序字段），然后在排序缓冲区中进行排序，排序完毕之后直接将结果集返回。这一步对排序缓冲区要求比较大，但排序效率高。

1、如果排序缓冲区大小不足，那么则会采用临时表（temporary table）存储排序结果。之后临时表的行指针信息重新回表查询记录。效率低

2、每个线程都有自己独自的排序缓冲区，如果排序缓冲区设置过大，会浪费内存。

因此sort_buffer_size的大小根据你们需要排序的数据大小来决定。太大浪费内存，太小造成临时表的使用，降低效率。

那MySQL到底采用哪种排序算法？

MySQL4.1版本之前只有多扫描排序算法，单扫描排序是MySQL4.1版本推出的新排序算法，用于优化多扫描排序。MySQL主要根据系统变量max_length_for_sort_data的大小和此次Query语句所取出的所有字段大小之后对比，如果max_length_for_sort_data大，则使用单扫描排序，反之使用多扫描排序。

  • 查询max_length_for_sort_data的默认值：
select @@max_length_for_sort_data;

在这里插入图片描述

max_length_for_sort_data默认1KB。

1.1.3 排序优化建议

如果是排序场景很多，且每次排序的数据量大。建议使用单排序，即max_length_for_sort_data调的尽可能大，并且保证每次排序的数据都在sort_buffer_size大小之内。

  • 创建举例：

在这里插入图片描述

有些网站业务不怎么复杂，专门做统计用的，经常需要升序/降序，并且数据量大。
当然了，上面的场景那些前端UI框架都能帮我们做好，根本不需要发送请求到后台，注意，我说的是类似场景。

1.2 分组优化

在MySQL中group by语句会触发一次默认的order by排序操作，造成不必要的性能浪费。我们可以手动的禁止分组操作带来的排序操作。

  • 准备测试表：
CREATE TABLE `student`  (				-- 学生表
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `c_id` int(11) NULL DEFAULT NULL,		-- 班级id
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `class`  (					-- 班级表
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `class` VALUES (1, 'Java01');
INSERT INTO `class` VALUES (2, 'Java02');

INSERT INTO `student` VALUES (1, '小红', 20, 2);
INSERT INTO `student` VALUES (2, '小军', 25, 2);
INSERT INTO `student` VALUES (3, '小明', 24, 1);
INSERT INTO `student` VALUES (4, '小龙', 19, 2);
INSERT INTO `student` VALUES (5, '小蓝', 22, 2);
INSERT INTO `student` VALUES (6, '小刚', 25, 1);
  • 根据班级id（c_id）计算每个班级平均年龄：
mysql> select avg(age),c_id from student group by c_id;
+----------+------+
| avg(age) | c_id |
+----------+------+
|  24.5000 |    1 |
|  21.5000 |    2 |
+----------+------+
2 rows in set (0.00 sec)

mysql>

发现默认根据c_id排序了。

  • 如果我们自己手动去除排序结果应该是这样的：
mysql> select avg(age),c_id from student group by c_id order by null;
+----------+------+
| avg(age) | c_id |
+----------+------+
|  21.5000 |    2 |
|  24.5000 |    1 |
+----------+------+
2 rows in set (0.00 sec)

mysql>
  • 查询如下两个SQL的执行计划：
explain select avg(age),c_id from student group by c_id;

explain select avg(age),c_id from student group by c_id order by null;

在这里插入图片描述

发现每次的group by 分组操作都会触发一次默认的排序操作，如果我们没有这样的需求，无疑是增加了SQL语句的响应时间

1.3 分页优化

通常使用分页查询是来提高我们的查询效率的，因为通常用户不希望一下子查询到那么多的数据，一般是查询前几条数据，此时的分页效率会比较高。但是有时候在分页查询会遇到一个比较尴尬的问题，那就是limit N,10，即前面跳过N多条记录，只查询N多条记录的后面几条记录。如果N的值非常大，那么效率必然就会很低。

  • 此时数据库有300W记录：
select count(*) from userinfo;

在这里插入图片描述

  • 假设我们需要查询2900000-2900010记录：
explain select * from userinfo limit 2900000,10;			-- 获取sql的执行计划

select * from userinfo limit 2900000,10;					-- 执行sql,查看消耗的时间

在这里插入图片描述

从执行计划可以看出，进行了全表扫描

花费的时间为：
在这里插入图片描述

显然，效率非常低。

1.3.1 分页优化一

我们可以借助索引，在索引上面排序，然后通过索引关联表查询。

explain select * from userinfo u1,(select id from userinfo order by id limit 2900000,10) t where u1.id=t.id;

在这里插入图片描述
查看执行消耗时间：
在这里插入图片描述

1.3.2 分页优化二

如果id是顺序排列的话，我们可以先根据id进行排序，然后取后面10条。

explain select * from userinfo where id>2900000 limit 10;

在这里插入图片描述

  • 查看执行消耗时间：
    在这里插入图片描述

1.4 表优化

1.4.1 数据空洞

当我们对数据库表进行删除（delete）时，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。而这些被标记的行就是数据空洞。

我们进入/var/lib/mysql/${db_name}目录下查看当前数据库的一些磁盘存储信息。

在这里插入图片描述

300W记录大概花了536M的空间。

  • 我们删除200W数据再次查看磁盘占用：
start transaction;							-- 控制在一个事务中(效率高)

delete from userinfo where id > 1000000;

commit;

再次查看磁盘占用空间，发现还是536M。并没有释放空间。

  • 查看表的状态信息：
mysql> show table status like 'userinfo'\G;
*************************** 1. row ***************************
           Name: userinfo
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1110488
 Avg_row_length: 492
    Data_length: 547356672
Max_data_length: 0
   Index_length: 0
      Data_free: 99614720
 Auto_increment: NULL
    Create_time: 2020-06-04 20:28:45
    Update_time: 2020-06-04 20:32:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment: 
1 row in set (0.00 sec)

其中Data_free就是碎片（空洞）的数量

1.4.2 optimize优化表

当表中存在有大量的数据空洞时，我们可以使用optimize命令来优化表。即删除数据空洞。

1.4.2.1 InnoDB表优化

InnoDB表优化后会做一个重新构建索引+分析的一个过程。因为我们知道InnoDB引擎将数据与索引是放在一个文件中的，名为.idb，对数据进行整理后，必定会对索引造成影响。而MyISAM是将数据与索引文件分开存储的（.MYD、.MYI），因此MyISAM整理空洞不会对索引造成影响。

  • 执行表优化操作：
optimize table userinfo;

在这里插入图片描述

InnoDB表优化后，还会对表中的索引进行重构分析。因此在同等数量的碎片整理，InnoDB花费时间比Myisam表要多一点。

  • 再次查看表，发现占用磁盘空间变小。

在这里插入图片描述

1.4.2.2 MyISAM表优化

  • 创建一张MyISAM表：
CREATE TABLE `userinfo_myisam` (
  `id` int(10) NOT NULL COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int(3) NULL DEFAULT NULL COMMENT '年龄',
  `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男   ‘1’-女',
  `desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍',
  `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
  `login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间',
  `pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址',
  `look` int(10) NULL DEFAULT NULL COMMENT '查看数',
  PRIMARY KEY (`id`)
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  • 编写存储过程，批量插入300W数据：
CREATE PROCEDURE `test_myisam`(count int)
begin
		declare i int default 1;
		while i<=count do 
			INSERT INTO userinfo_myisam values(
			i,
			uuid(),
			CEILING(RAND()*90+10),
			FLOOR(RAND()*100000000000),
			round(FORMAT(rand(),1)),
			uuid(),
			now(),
			now(),
			uuid(),
			CEILING(RAND()*90+10)
			);
			set i=i+1;
		end while;
end

call test_myisam(3000000);			-- 插入300W数据
  • 删除200W数据：
delete from userinfo_myisam where id>1000000;
  • 执行表优化：
optimize table userinfo_myisam;

在这里插入图片描述

花费2.65s，比InnoDB效率高，因为InnoDB需要重新构建索引。

再次查看表磁盘占用情况，发现占用空间减少。

1.5 总结

总结本篇文章的重要几点：

  • 1、牢记排序优化的两个参数：sort_buffer_size、max_length_for_sort_data。
  • 2、分组操作默认会做一次排序操作，如果没有这个需求建议关闭（order by null）。
  • 3、分页优化参考具体SQL语句。
  • 4、表空洞优化

SQL优化很多方面是索引、锁、参数调优方面的优化，其中锁和索引的优化可以明显的提高SQL语句的查询速度，参数方面的优化则可以帮助我们搭建一个稳定的、高性能、高可用的MySQL集群架构，由于索引、锁、参数调优方面牵扯知识太广，本篇不做讲解。

好了，本篇就说到这里了，看完觉得有帮助的童鞋记得点赞！点赞！点赞！（重要的事情说三遍！）

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/131823.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！