Mysql-索引创建,索引失效案例

索引创建建议

1 什么情况下需要创建索引？

1. 频繁出现在where 条件字段，order排序，group by分组字段
2. select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
3. 多表join关联查询，on字段两边的字段都要创建索引

索引优化建议

1. 表记录很少不需创建索引 ：索引是要有存储的开销
2. 一个表的索引个数不能过多：
   （1）空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。
   （2）时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。
3. 频繁更新的字段不建议作为索引：频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。
4. 区分度低的字段，不建议建索引：
   比如性别，男，女；比如状态。区分度太低时，会导致扫描行数过多，再加上回表查询的消耗。
   如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。姓名，手机号就非常适合建索引。
5. 在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段：
6. 主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查询
   时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越
   长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。
7. 不建议用无序的值作为索引：例如身份证、UUID。更新数据时会发生频繁的页分裂，页内数据不
   紧凑，浪费磁盘空间。
8. 尽量创建组合索引，而不是单列索引：
   优点：
   （1）1个组合索引等同于多个索引效果，节省空间。
   （2）可以使用覆盖索引
   创建原则：组合索引应该把频繁用到的列、区分度高的值放在前面。频繁使用代表索引的利用率
   高，区分度高代表筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围

索引失效分析

CREATE TABLE `t_user_index_analyse` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`pos` varchar(10) DEFAULT NULL,
`pay_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB;
insert into t_user_index_analyse (id,name,age,pos,pay_time) values(1 ,'z3'
,22,'manager','2022-03-13 09:31:34');
insert into t_user_index_analyse (id,name,age,pos,pay_time) values(2 ,'July'
,23,'dev','2022-03-13 09:31:34');
insert into t_user_index_analyse (id,name,age,pos,pay_time) values(3 ,'2000'
,23,'dev','2022-03-13 09:31:34');

1 全局匹配
explain 相关知识
下面这两条SQL语句都是使用了组合索引

explain select * from t_user_index_analyse where name='July' and age=25 and pos='dev';
#这个SQL优化器会优化顺序以使用上索引,但是我们最好还是按照顺序来写
explain select * from t_user_index_analyse where  name = 'July';
explain select * from t_user_index_analyse where name='July' and age=25;

第一条SQL语句：
我们可以看到使用了组合索引，key_len是806（最左匹配），ref有三个引用，过滤掉的数据占数据的百分比，比如值为100%，就是没有查到数据。

第二条SQL语句
可以使用了组合索引的一部分，key_len是768（最左匹配），ref中也只是引用了一个字段。

第三条语句：
引用了组合索引的两个字段key_len是773

2 最左法则

#可以使用到索引
explain select * from t_user_index_analyse where name='July';
explain select * from t_user_index_analyse where name='July' and age=25;
#无法使用索引
explain select * from t_user_index_analyse where age=23 and pos='dev'; #没有最左字段
explain select * from t_user_index_analyse where pos='dev'; #没有最左字段
#根据最左匹配使用到了组合索引的一部分
explain select * from t_user_index_analyse where name='July' and pos='dev'; #age字段间断只能使用到一部分

前两条SQL语句：

无法使用索引SQL语句：

使用到了组合索引的一部分：

总结：我们使用组合索引的时候要遵循最左匹配原则，组合索引最左的索引如果没有则使用不到索引，如果有最左字段，但是中间字段没有，那么这个时候只会应用最左的字段。
比如（a,b,c） 如果要使用这个索引 a必然不能少， 如果你条件里面是ac,b被跳过了，那么这个时候也只能使用到a,这个与组合索引的存贮有关。因为组合索引是先看a的顺序，在b,c如果都一样看主键的顺序。如果没有最左字段或中间跳字段了这个比较无法进行。

3 不能再索引上做计算

#这个肯定是走不了索引的，因为你的值是动态计算的不知道要从何查起
explain select * from t_user_index_analyse where left(name,4)='July';

4 范围条件右边的列不会走索引

explain select * from t_user_index_analyse where name='July' and age>25 and pos='manager';

可以看到走了最左索引name和age这两个字段是可以走组合索引的，age是查询的范围不是等值比较后面的pos就走不了索引了，但是这里可以索引下推，就是后面这个条件可以直接在查询的时候利用起来做一进一步筛选，可以减少服务端筛选的工作量。

5 使用覆盖索引

explain select * from t_user_index_analyse where name='July' and age=25 and pos='manager';
explain select name,age,pos from t_user_index_analyse where name='July' and age=25 and pos='manager';

第一条SQL语句：

第二条语句：

对比发现explain的结果有所不同，是因为第二条SQL语句组合索引已经存放了所有字段的信息，不用进行回表查询，这就是覆盖索引。

6 索引字段上不要使用不等

# 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
explain select * from t_user_index_analyse where name != 'July';
explain select * from t_user_index_analyse where name <> 'July';

很容易理解，不等判断根本无法知道，我下一步应该怎么走，比如我来到树的根节点对比不等这个时候我应该向那条指针走呢？大于这个字符串，小于这个字符串都可能和这个字符串不等，所以没办法在树上进行寻找。
7 索引字段上不使用null

# 索引字段上使用 is not null 判断时，会导致索引失效而转向全表扫描
explain select * from t_user_index_analyse where name is not null;

8 索引字段使用like不以通配符开头

# 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描
explain select * from t_user_index_analyse where name like '%July%';
explain select * from t_user_index_analyse where name like '%July';
#这条语句是可以走索引的
explain select * from t_user_index_analyse where name like 'July%';

前两条语句：

最后一条语句，因为最左匹配，所以左边的信息可以利用

9 索引字段字符串要加单引号

# 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
explain select * from t_user_index_analyse where name = '2000';
explain select * from t_user_index_analyse where name = 2000;

10 索引字段不要使用or

# 索引字段使用 or 时，会导致索引失效而转向全表扫描
explain select * from t_user_index_analyse where name = 'July' or name='z3';