索引创建建议
1 什么情况下需要创建索引?
- 频繁出现在where 条件字段,order排序,group by分组字段
- select 频繁查询的列,考虑是否需要创建联合索引(覆盖索引,不回表)
- 多表join关联查询,on字段两边的字段都要创建索引
索引优化建议
- 表记录很少不需创建索引 :索引是要有存储的开销
- 一个表的索引个数不能过多:
(1)空间:浪费空间。每个索引都是一个索引树,占据大量的磁盘空间。
(2)时间:更新(插入/Delete/Update)变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。所以索引虽然能够提高查询效率,索引并不是越多越好,应该只为需要的列创建索引。 - 频繁更新的字段不建议作为索引:频繁更新的字段引发频繁的页分裂和页合并,性能消耗比较高。
- 区分度低的字段,不建议建索引:
比如性别,男,女;比如状态。区分度太低时,会导致扫描行数过多,再加上回表查询的消耗。
如果使用索引,比全表扫描的性能还要差。这些字段一般会用在组合索引中。姓名,手机号就非常适合建索引。 - 在InnoDB存储引擎中,主键索引建议使用自增的长整型,避免使用很长的字段:
- 主键索引树一个页节点是16K,主键字段越长,一个页可存储的数据量就会越少,比较臃肿,查询
时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值,主键值越
长,一个页可存储的数据量就会越少,查询时磁盘IO次数会增多,查询效率会降低。 - 不建议用无序的值作为索引:例如身份证、UUID。更新数据时会发生频繁的页分裂,页内数据不
紧凑,浪费磁盘空间。 - 尽量创建组合索引,而不是单列索引:
优点:
(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';
不使用索引的其它情况:
- 索引选择性差,这个的意思是通过索引筛选也并不会筛选出很多数据。
- 不等 和 not in 无法使用索引
- is null 会使用索引,is not null 不会使用索引
- where 子句逃过左侧索引列,直接查询右侧索引字段
- 对索引列使用函数或者进行计算
使用索引优化排序:
当order by 字段与索引字段排序、排序方向相同时索引可优化排序速度。
如果排序字段是单字段则支持升序和降序。
多字段的情况下,左侧字段必须是升序(主键中的最左字段),且顺序不允许打乱(也就是遵循最左原则,字段和建索引的字段保持一致)。
11 字符集不一致可能会导致索引失效。
索引优化-删除冗余索引
工具:pt-duplicate-key-checker是percona-toolkit工具包,可以分析表是否存在冗余索引。
查看索引使用情况,这样就可以看到那些索引使用的频繁,如果count全是0则可以删除这个索引表示使用的很少:
#索引使用状况统计
SELECT object_type,object_schema,object_name,index_name, count_read,count_fetch, count_insert, count_update,count_delete FROM performance_schema.table_io_waits_summary_by_index_usage ORDER BY sum_timer_wait desc;
减少表与索引碎片:
analyze table 表名 索引重新统计
optimize table 表名 优化表空间释放表空间,会锁表