2020-10-09

SQL优化

字数统计: 1.8k字 | 阅读时长: 6分

SQL优化

一、为什么SQL需要优化?

查询性能低

执行时间过长

等待时间过长

SQL写的太差(尤其体现在多表查询上)

索引失效

服务器参数(缓存,线程数)设置不合理

项目需求不合理

二、优化的一般步骤

1）通过 show status 命令了解各种 sql 的执行频率

2）定位执行效率较低的 sql 语句

3）通过 explain 分析低效 sql 的执行计划

4）通过 show profile 分析 sql

5）通过 trace 分析优化器的执行计划

6）确定问题并采取相应的优化措施

四、常见的优化操作

（一）索引

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

针对索引的优化，除了正确、合理的创建索引之外，防止索引失效也是至关重要的一环，常见的避免索引失效的方法如下：

1）全值匹配，对索引中所有列都指定具体

2）遵守最左前缀法则

3）范围查询右边的列，不能使用索引（范围查询右边的列使用的索引会失效）

4）不要在索引列上进行运算操作，索引将失效

5）字符串不加单引号，造成索引失效（本质上就是类型转换，从而导致索引出现运算操作）

6）尽量使用覆盖索引，避免select * （会导致回表查询，效率降低）

7）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么索引会失效

8）以%开头的Like模糊查询，索引失效（通过覆盖索引来解决该问题）

9）如果MySQL评估使用索引比全表更慢，则不使用索引

10）is NULL, is NOT NULL 有时索引失效

11）in 走索引， not in 索引失效

12）尽量使用复合索引，而少使用单列索引

补充：查看索引的使用情况

1 2	show status like 'Handler_read%'; show global status like 'Handler_read%';

（二）大批量插入数据

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

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

1）主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。

2）关闭唯一性校验

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

3）手动提交事务

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

（三）优化 insert 语句

1）如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

2）在事务中进行数据插入。

3）数据根据主键有序插入

（四）优化 order by 语句

首先了解一下MySQL的两种排序方式：

1）第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

2）第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。

（五）优化 group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是想要避免排序结果的消耗，则可以执行order by null 禁止排序, 如下：
1
2
3
4
select age,count(*) from emp group by age;

-- 优化后 
select age,count(*) from emp group by age order by null;
小结：执行order by null 禁止排序，并且还建议使用索引。

（六）优化嵌套查询

有些情况下，子查询是可以被更高效的连接（JOIN）替代的。

示例，查找有角色的所有的用户信息:
1
2
3
4
select * from t_user where id in (select user_id from user_role );

-- 优化后
select * from t_user u , user_role ur where u.id = ur.user_id;
连接(Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

（七）优化 or 条件

思路一：OR之间的每个条件列都必须用到索引，而且每个条件中使用的索引不能来自同一个复合索引；

思路二：使用 union 替换 or，如：
1
2
3
4
select * from emp where id = 1 or id = 10;

-- 优化后
select * from emp where id = 1 union select * from emp where id = 10;

（八）优化分页查询

思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容，如：
1
2
3
4
select * from emp limit 200000,10;

-- 优化后
select * from emp e, (select id from emp order by id limit 200000,10) t where e.id = t.id;
思路二：该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询，如：
1
2
3
4
select * from emp limit 200000,10;

-- 优化后
select * from emp where id > 200000 limit 10;

（九）使用 SQL 提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1、USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引，如：
1
select * from tb_seller use index(idx_seller_name) where name = '小米科技';

2、IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint，如：
1
select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

3、FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint，如：
1
select * from tb_seller force index(idx_seller_name) where name = '小米科技';

三、总结

参考文章：

优化 sql 语句的一般步骤

SQL 优化——一般步骤、索引问题、优化方法(ANALYZE、CHECK、OPTIMIZE)、常用 SQL 的优化

Java新手，若有错误，欢迎指正！

| 阅读量次