需要的准备知识
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分析