【Mysql调优】4、索引的优化策略

最新推荐文章于 2024-08-07 23:56:32 发布

一飘微草

最新推荐文章于 2024-08-07 23:56:32 发布

阅读量172

点赞数

分类专栏： Mysql调优文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_45473962/article/details/126810665

版权

Mysql调优专栏收录该内容

9 篇文章 1 订阅

订阅专栏

本文探讨了如何避免索引使用不当导致的性能问题，包括索引选择性差、联合索引查询优化、函数操作与冗余索引的识别，以及如何利用索引来优化排序。还介绍了统计索引使用情况和维护索引的方法，如检查冗余并删除，以提高数据库性能。

摘要由CSDN通过智能技术生成

1、什么时候不会使用索引？

索引选择性太差（查询条件的数据重复性过高、数据查询范围过大等情况，MySQL的查询优化器会认为全表扫描比使用索引快则不会使用索引）
<> / not in 不会使用索引
is null 会使用索引，但是is not null 不会使用索引（使用了not关键字）
联合索引中，where子句的查询条件跳过了最左侧索引列，使用右侧索引列时不会使用索引
对索引列进行了计算或者使用函数，索引失效

# (1)索引选择性差
# 注意：MySQL并没有明确指出，什么情况下索引选择性较好，但是一般扫描的数据超过全表数据数量的25%就属于选择性差
create index idx_forum on testdb.t_content(forum_id);
# 索引选择性好，使用索引
explain select * from t_content where forum_id = 407;
# 索引选择性差，全标扫描
explain select * from t_content where forum_id > 407;


# (2)联合索引中，where子句的查询条件跳过了最左侧索引列，使用右侧索引列时不会使用索引
create index idx_uid_sid on testdb.t_content(uid,source_id);
# 联合索引查询条件必须包含左侧列
# 查询条件包含uid，走了索引idx_uid_sid
explain select * from t_content where uid = 12601190;
# 查询条件包含uid，走了索引idx_uid_sid
explain select * from t_content where uid = 12601190 and source_id = 13054;
# 查询条件不包含uid，没走索引idx_uid_sid，全表扫描
explain select * from t_content where source_id <> 13054;


# (3)对索引列进行了计算或者使用函数，索引失效
explain select * from t_content where uid + 1 = 14206987;
# 解决方法：将运算放在右边，不针对索引列去执行，索引即可生效，select * from t_content where uid = 1 + 14206987;
explain select * from t_content where cast(uid as char) = '14206987';
# 解决方法：如果字符类型业务必要，可在表中单独创建一列字符列的uid，并加上索引

2、使用索引优化排序

当Order by字段与索引字段顺序/排序顺序相同时，索引可优化排序顺序

create index idx_uid_sid on testdb.t_content (uid, source_id);

# 排序使用最左侧字段时，允许升降序排序
# 左侧但字段排序时，索引支持升降序
explain select * from t_content where uid < 14206987 order by uid asc;
explain select * from t_content where uid < 14206987 order by uid desc;

# 没有使用左侧字段，优化排序索引失效
# 解析计划使Extra出现了，Using filesort，表示所有没有优化排序
explain select * from t_content where uid < 14206987 order by source_id;

# 多字段情况下，左侧字段必须是asc升序，并且排序顺序要和索引字段顺序保持一致，否则索引失效
# 如果上述要求不符合，则会出现Using filesort ，索引优化排序失败
explain select * from t_content where uid < 14206987 order by uid asc,source_id;

3、删除冗余索引

使用 运维工具percona-toolkit 工具包下的 pt-duplicate-key-checker命令 检测表中重复的索引或主键
站友的linux环境的安装教程

命令执行过程中可能出现Cannot connect to MySQL because the Perl DBI module is not installed or not found
-bash: Cannot: command not found的错误，只需要安装DBI模块即可。

//安装DBI模块用来连接数据库
yum install perl-DBD-mysql

//使用命令进行分析
/opt/percona-toolkit-3.0.13/bin/pt-duplicate-key-checker --host='127.0.0.1' --user='用户名' --password='密码' --databases=数据库名称 --tables=要分析的表名

之后会出现一个报告，表中有哪些冗余索引，然后可以参考进行取舍删除：

在这里插入图片描述

4、数据库当中索引使用情况统计

# 索引使用情况统计
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;

执行结果：
在这里插入图片描述

object_type：访问对象的类型
object_schema：访问的数据库
object_name：访问的表
index_name：访问使用的索引
count_read：查询结果的过程中扫描的行数
count_fetch：查询出的结果的行数
count_insert：通过索引的新增操作行数
count_update：通过索引的更新操作行数
count_delete：通过索引的删除操作行数

注意：在线上程序运行一段时间后
1、如果出现index_name为空的结果很多的话，说明有sql进行了全表扫描，需要进行排查
2、如果索引使用情况后几行结果均出现0的情况，说明该索引没有被用到，需要排查是否需要删除
3、sum_timer_wait desc 代表索引执行时间的倒序排列，排列顺序靠前也可以作为分析索引执行情况的参考

5、减少表与索引碎片

analyze table tableName;（重算统计索引信息，该命令对于查询信息有很大帮助）
optimize table tableName;（InnoDB是按照id进行数据的聚簇排列的，该命令将数据重新整理，优化释放空间，注意：改命令会锁表）
在数据库表中出现频繁的增删改操作时，索引和表存储空间也会相应发生变化，但是可能存在空间分配不合理的情况，需要对空间进行回收