看完了工具,死磕算法,那么怎么从算法和工具去解决我们忽略的用法呢,下面我们来个小试牛刀。
表table employees (id,name,age,position )几个字段 name, age, work 三个字段作为联合索引
1、全值匹配
1)EXPLAIN SELECT * FROM employees WHERE name= '张三';
2)EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 22;
3)EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 22 AND position ='manager';
上面三个sql 可以看到用了联合索引,同时索引的长度在变化,走了联合索引,根据联合索引树找到对应的主键,从主键索引树种找到全部的匹配数据。因为mysql优化实现的联合索引叶子节点除了索引本身,就是主键索引树。
2,最左前缀原则(非主键联合索引)
如果索引了多列,查询从索引的最左前列开始并且不跳过索引 中的列。这就是最左前缀法则。减去我们不要的列效率会更高。只从联合索引中去数据就能满足就行,不必再去一次主键索引树查找数据。
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';
上面连个sql没有走索引,这是不符合联合索引最左前缀原则,因为联合索引树是按照创建索引字段添加数据去创建的,用age ,age + position ,position 这三种都是全表扫描,走联合索引树还没全部扫描效率高。
EXPLAIN SELECT * FROM employees WHERE name = '张三';
EXPLAIN SELECT * FROM employees WHERE name = '张三' ;
EXPLAIN SELECT * FROM employees WHERE name ='张三' and position = 'manager';
上面三个sql 只走联合索引树的第一个字段,是可以实现的eg:
所以使用联合索引的时候要注意这几个顺序组合,合理的设置联合索引。
3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转 向全表扫描
EXPLAIN SELECT * FROM employees WHERE left(name,3) = '张三';
给hire_time增加一个普通索引:
EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
转化为日期范围查询,会走索引:
EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';
这个时候又会走索引,索引在范围查询是可以走索引的。
EXPLAIN SELECT * FROM employees WHERE name= '张三' and age = 22 AND position ='manager'; 这个sql 调整顺序是可以正常走索引的 。
4.存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age > 22 AND position ='manager';
第三个字段没有走索引。
5、尽量使用覆盖索引,减少select *语句
就是尽可能的只是使用索引字段中存在的列。
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
虽然根据这个条件看这两个sql最终只走一个联合索引,可是select * 他还走了一次主键索引树。
限于篇幅,后面再介绍其他的常用的方式。