MySQL-索引优化篇(4)_索引的维护

小小工匠

发布于 2021-08-17 11:14:17

6850

文章被收录于专栏：小工匠聊架构小工匠聊架构

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

删除重复索引

举个例子，对id 建立了多个索引，重复索引

primary key(id) 【主键索引】, unique key(id) 【唯一索引】, index(id)【普通索引】

主键上MySQL会自动创建索引的，所以就么有必要再对主键建立唯一索引，更没有必要建立普通索引了。

删除冗余索引

举个例子

index(a) , index(a,b) 在a上建立普通索引，在a 、b 建立了联合索引。

这种情况是冗余的，只需要保存那个联合索引中就行了，删除对a 单独建立的普通索引即可。

因为 a 都在左侧，所以查询a，也是可以走那个联合索引的。

primary key(id), index(a, id)

这种情况是冗余的，因为Innodb中对于二级索引会自动增加主键索引，所以也是没必要对a 和主键id 建立联合索引的。

检查重复和删除的索引 pt-duplicate-key-checker

利用第三方插件 pt-duplicate-key-checker 。

pt-duplicate-key-checker是percona-toolkit中一个工具，它可以检测表中重复的索引或者主键，这款工具可以帮助我们找到重复的索引并给出建议。

下载地址： https://www.percona.com/downloads/percona-toolkit/LATEST/ ，选择合适的操作系统下载。

安装

[root@artisan ~]# yum -y install percona-toolkit-3.1.0-2.el7.x86_64.rpm

位于： /usr/bin/pt-duplicate-key-checker

用法

[root@artisan ~]# pt-duplicate-key-checker  --host=192.168.18.131 --user='root' --password='artisan'  --databases=artisan

更多用法 pt-duplicate-key-checker --help

查找未被使用的索引

SELECT
	OBJECT_SCHEMA,
	OBJECT_NAME,
	INDEX_NAME,
	b.`TABLE_ROWS`
FROM
	`performance_schema`.table_io_waits_summary_by_index_usage a
JOIN information_schema.`TABLES` b ON a.OBJECT_SCHEMA = b.TABLE_SCHEMA
AND a.OBJECT_NAME = b.TABLE_NAME
WHERE
	INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
ORDER BY
	OBJECT_SCHEMA,
	OBJECT_NAME;

更新索引统计信息

analyze table table_name

索引碎片整理

索引在使用的过程中会产生碎片，表也在使用的过程中会产生碎片

查看某个表的碎片程度

SELECT
	ROW_FORMAT,
	TABLE_ROWS,
	DATA_LENGTH,
	INDEX_LENGTH,
	MAX_DATA_LENGTH,
	DATA_FREE,
	ENGINE
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'SAKILA'
AND TABLE_NAME = 'ACTOR'
LIMIT 1;

整理

optimize table table_name

这个地方需要注意，闲时操作。使用不当会导致锁表

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/02/02 ，如有侵权请联系 cloudcommunity@tencent.com 删除

https

网络安全