Mysql常用优化总结

发表于 更新于
你这辈子只能靠自己。

Like的参数以通配符开头时

尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。
以通配符开头的sql语句，例如：

1
select * from t_credit_detail where Flistid like '%0'\G;

使用！= 或 <> 操作符时

尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

1
select * from t_credit_detail where Flistid != '2000000608201108010831508721'\G;

索引列参与计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

1
select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'\G;

对字段进行null值判断

应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
低效：

1
select * from t_credit_detail where Flistid is null;

可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询：
高效：

1
select * from t_credit_detail where Flistid =0;

使用or来连接条件

应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
低效：

1
select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';

避免select *

在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
所以，应该养成一个需要什么就取什么的好习惯。

order by 语句优化

任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

  • 1.重写order by语句以使用索引；
  • 2.为所使用的列建立另外一个索引
  • 3.绝对避免在order by子句中使用表达式。

GROUP BY语句优化

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉

低效:

1
2
3
4
5
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER';

高效:

1
2
3
4
5
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP by JOB;

用 exists 代替 in

很多时候用 exists 代替 in 是一个好的选择：

1
select num from a where num in(select num from b);

用下面的语句替换：

1
select num from a where exists(select 1 from b where num=a.num);

使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

能用DISTINCT的就不用GROUP BY

1
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID;

可改为：

1
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10;

最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

1
2
3
4
5
6
7
有一个复合索引：INDEX(`a`, `b`, `c`);

使用方式	                                        能否用上索引
select * from users where a = 1 and b = 2	能用上a、b
select * from users where b = 2 and a = 1	能用上a、b（有MySQL查询优化器）
select * from users where a = 2 and c = 1	能用上a
select * from users where b = 2 and c = 1	不能

https://cloud.tencent.com/community/article/382852