【MySQL】SQL优化

一、大批量插入数据

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

1） 主键顺序插入

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

插入ID顺序排列数据：

插入ID无序排列数据：

2） 关闭唯一性校验

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

3） 手动提交事务

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

二、优化insert

1. 逐条插入改为批量插入

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

优化后的方案为 ：

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

2. 在事务中进行数据插入

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

3. 数据有序插入

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

优化后

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');
1
2
3
4
5

三、优化order by

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

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

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

A：1 1 1 1 2 2 3 3 3
B：2 3 4 5 3 4 5 1 2
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 系统变量，来增大排序区的大小，提高排序的效率。

四、优化group by

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

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

• order by null，手动禁止排序

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

五、优化子查询

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

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

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

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

六、优化OR

建议使用 union 替换 or

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

七、优化分页查询

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

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

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

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

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

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

学习资料：黑马程序员MySQL全套教程