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对于该语句的执行计划

如下图所示

sql语句性能优化

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分析

上一篇:HTTP协议中TCP的三次握手,四次挥手总结


下一篇:译:Spring框架参考文档之IoC容器(未完成)