【MySQL】SQL优化-摩杜云开发者社区

文章目录

一、大批量插入数据
二、优化insert
三、优化order by
四、优化group by
五、优化子查询
六、优化OR
七、优化分页查询

一、大批量插入数据

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率

【MySQL】SQL优化_性能优化

1）主键顺序插入

load data local infile '/root/data1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

插入ID顺序排列数据：

【MySQL】SQL优化_性能优化_02

插入ID无序排列数据：

【MySQL】SQL优化_MySQL_03

2）关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率

3）手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率

二、优化insert

逐条插入改为批量插入

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

优化后的方案为：

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

在事务中进行数据插入

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

数据有序插入

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

优化后

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

三、优化order by

MySQL中有两种排序方式：using filesort 和 using index

filesort ：对返回数据进行排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
index：有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

【MySQL】SQL优化_MySQL_04

我们现在有联合索引A、B，当A相等时，B升序排列，比如：

A：1 1 1 1 2 2 3 3 3
B：2 3 4 5 3 4 5 1 2

对于Filesort ， MySQL 有两种排序算法：

1）两次扫描算法：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小，来判定使用哪种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

【MySQL】SQL优化_mysql_05

四、优化group by

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。既然会排序，就可以用到索引

于是我们有两种方式优化group by：

order by null，手动禁止排序

【MySQL】SQL优化_sql_06

给group by字段创建索引，优化排序

【MySQL】SQL优化_字段_07

五、优化子查询

用更高效的连接（JOIN）替代子查询

示例，查找有角色的所有的用户信息 :

explain select * from t_user where id in (select user_id from user_role);

【MySQL】SQL优化_字段_08

explain select * from t_user u , user_role ur where u.id = ur.user_id;

【MySQL】SQL优化_字段_09

六、优化OR

建议使用 union 替换 or

【MySQL】SQL优化_mysql_10

【MySQL】SQL优化_sql_11

这就把type字段从range优化为了const

七、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时MySQL需要排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

-- 耗时2.12s
select * from tb_item limit 2000000, 10;

优化思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

-- 耗时1.51s
select * from tb_item t, (select id from tb_item order by id limit 2000000,10) a where t.id = a.id;

【MySQL】SQL优化_sql_12

优化思路二：该方案适用于主键自增且连续的表，可以把limit查询转换成某个位置的查询

-- 耗时0.00s
select * from tb_item where id > 2000000 limit 10;

【MySQL】SQL优化_MySQL_13