sql语句性能优化

需要的准备知识

1最左前缀匹配

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

对于where条件

a = 1 and b> 2 and c = 3

如果我们建立(a,b,c)顺序的索引，

那么c 是用不到索引的，如果建立(a,c,b)的索引则都可以用到，a,c的顺序可以任意调整。

当我们建立(a,b)的复合索引

对于where条件

a = 1 依然是可以用到索引的。

注意：

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql的查询优化器会帮你优化成索引可以识别的形式

2 sql索引原则

2.1索引列的区分度尽量要高，区分度的计算公式count(distinct col)/count(*)

唯一键的区分度是1，而比如类似 状态，性别等字段在大数据面前区分度是0

2.2要保持索引列的值的干净，不能参与计算

比如 假如用户年龄上有索引

      Select id from user where age>2 (会走索引)

  Select id from user where age-2>0 (不会走索引)

2.3尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引

修改即可。

3优化神器 explain / desc 的使用

在你原来的sql语句之前加上 explain 就可以查看mysql对于该语句的执行计划

如下图所示

 

type

const（常量连接），比如SELECT * FROM user WHERE id=1;  

eq_ref（等值引用），比如SELECT * FROM user,card WHERE user.id=card.userid;  

ref（引用），用于非唯一索引，比如SELECT * FROM user,card WHERE user.last_name='test';  

range（范围），比如SELECT * FROM tbl_name WHERE key_column > 10;  

unique_subquery  子查询 针对唯一索引或者主键

index_subquery  子查询 针对非唯一索引列

index（索引），根据索引来读取数据，如果索引已包含了查询数据，只需扫描索引树，否则执行全表扫描和All类似；  

ALL（所有），全表扫描  

Extra

Using index：表示使用索引，如果同时出现Using where，代表使用索引来查找读取记录，如果没有Using where，表示索引包含查询数据，无需额外的查找；

Using where：表示条件查询，如果type列是ALL或index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据；  

Using filesort：不是“使用文件索引”的含义！filesort是MySQL所实现的一种排序策略，通常在使用到排序语句ORDER BY的时候，会出现该信息；  [非索引字段排序]

Using temporary：表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。

我们需要重点关注rows。

 

优化方法（步骤）

1.先运行看看是否真的很慢，注意看是否设置了SQL_NO_CACHE。优化之前先关闭。

2.explain查看执行计划，看那些地方比较慢

3.order by limit 形式的sql语句让排序的表优先查（也就是遇到这类情况，我们可以先去掉order by limit 看是不是他导致的）

4.加索引时参照上面的sql索引原则

5.观察结果，不符合预期继续从1分析