Mysql-03.工作中对SQL优化的心得体会

1.使用explain分析sql

explain+sql就可以开始分析sql啦，下面介绍一些比较重要的字段

1.1 type

• const 通过索引一次就找到了（最好）
• eq_ref 是用来唯一性索引（primary key或者unique index）
• ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• range 单表索引中的范围查询
• index 比all好点
• all 全表扫描（最差）

SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。

1.2 possible_keys

可能使用的索引，注意不一定使用。当该列为NULL时，就要考虑当前的SQL是否要优化了

1.3 extra

• Using index 使用覆盖索引
• Using where 使用了where子句来过滤结果集
• Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化

2.超大分页怎么处理？

在阿里巴巴《Java开发手册》中，对超大分页的解决办法

• 先快速定位需要获取的id段，然后再关联
• SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

场景：

• 类似于select * from table where age > 20 limit 1000000,10，这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢。

• 我们也可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快.

• 还可以select * from table where id > 1000000 limit 10,效率也是不错

3.数据量大的情况下，主键使用自增ID，不使用UUID。

4.存储用户的密码散列用char不是用varchar，减少空间提高检索效率。

5.优化where子句

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引，以下是导致引擎放弃使用索引而进行全表扫描的例子。

• 尽量避免在 where 子句中对字段进行 null 值判断

select id from t where num is null
-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=

• 尽量避免在 where 子句中使用!=或<>操作符
• 尽量避免在 where 子句中使用or 来连接条件

select id from t where num=10 or num=20
-- 可以这样查询：
select id from t where num=10 union all select id from t where num=20

• in 和 not in 也要慎用，否则会导致全表扫描

select id from t where num in(1,2,3) 
-- 对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

• 尽量避免在 where 子句中对字段进行表达式操作

select id from t where num/2=100
-- 应改为:
select id from t where num=100*2

• 应尽量避免在where子句中对字段进行函数操作

select id from t where substring(name,1,3)=’abc’
-- name以abc开头的id应改为:
select id from t where name like ‘abc%’

这里推荐一篇博客里面有大量避免索引失效的例子

• https://www.cnblogs.com/cyhbyw/p/8898977.html