MySQL索引的维护
2022-03-07 21:58 abce 阅读(578) 评论(0) 编辑 收藏 举报1.找出未使用的索引
结合sys schema查询
mysql> select * from sys.schema_unused_indexes;
+---------------+-----------------+-------------+
| object_schema | object_name | index_name |
+---------------+-----------------+-------------+
| world | City | CountryCode |
| world | CountryLanguage | CountryCode |
+---------------+-----------------+-------------+
2 rows in set (0.01 sec)
视图schema_unused_indexes是基于表performance_schema.table_io_waits_summary_by_index_usage。需要开启performance_schema、events_waits_current consumer和wait/io/table/sql/handler instrument。
逐渐会被忽略。
update performance_schema.setup_consumers set enabled = 'yes' where name = 'events_waits_current';
update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/io/table/sql/handler';
发现有未使用的索引之后，删除即可。
开启对应consumer和instrument之后，一定要运行一段时间，生成的数据才可靠。
比如，如果有个月度任务，那至少要运行一个月，数据才会可靠。
2.找出重复的索引
(1)使用pt-duplicate-key-chekcer
[root@e51d333b1fbe mysql-sys]# pt-duplicate-key-checker
# ########################################################################
# world.CountryLanguage
# ########################################################################
# CountryCode is a left-prefix of PRIMARY
# Key definitions:
# KEY `CountryCode` (`CountryCode`),
# PRIMARY KEY (`CountryCode`,`Language`),
# Column types:
# `countrycode` char(3) not null default ''
# `language` char(30) not null default ''
# To remove this duplicate index, execute:
ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 2952
# Total Duplicate Indexes 1
# Total Indexes 37
(2)使用视图sys.schema_redundant_indexes
sys.schema_redundant_indexes是基于表information_schema.statistics
mysql> select * from schema_redundant_indexesG
*************************** 1. row ***************************
table_schema: world
table_name: CountryLanguage
redundant_index_name: CountryCode
redundant_index_columns: CountryCode
redundant_index_non_unique: 1
dominant_index_name: PRIMARY
dominant_index_columns: CountryCode,Language
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`
1 row in set (0.00 sec)
3.可能缺失的索引
视图sys.statements_with_full_table_scans
mysql> select * from world.CountryLanguage where isOfficial = 'F';
55a208785be7a5beca68b147c58fe634 -
746 rows in set (0.00 sec)
mysql> select * from statements_with_full_table_scansG
*************************** 1. row ***************************
query: SELECT * FROM `world` . `Count ... guage` WHERE `isOfficial` = ?
db: world
exec_count: 1
total_latency: 739.87 us
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 746
rows_examined: 984
rows_sent_avg: 746
rows_examined_avg: 984
first_seen: 2016-09-05 19:51:31
last_seen: 2016-09-05 19:51:31
digest: aa637cf0867616c591251fac39e23261
1 row in set (0.01 sec)
找出全表扫描的语句之后，可以使用explain看下具体的执行计划：
mysql> explain select * from world.CountryLanguage where isOfficial = 'F'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using where
可以分析一下，看看是否需要增加合适的索引。
4.多列索引的顺序
多列索引的列的顺序很重要。
假设有一张表
mysql> show create table CountryLanguageG
*************************** 1. row ***************************
Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
下面的查询就无法使用到索引：
mysql> explain select * from CountryLanguage where Language = 'English'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using where
如果在查询条件中增加一个条件：CountryCode
就可以使用到索引了。
mysql> explain select * from CountryLanguage where Language = 'English' and CountryCode = 'CAN'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: const
possible_keys: PRIMARY,CountryCode
key: PRIMARY
key_len: 33
ref: const,const
rows: 1
Extra: NULL
这时就需要分外考虑列的选择性了，哪个放在前面更好。
在这个例子中，"language"的选择性比"countrycode"好：
mysql> select count(distinct CountryCode)/count(*), count(distinct Language)/count(*) from CountryLanguage;
+--------------------------------------+-----------------------------------+
| count(distinct CountryCode)/count(*) | count(distinct Language)/count(*) |
+--------------------------------------+-----------------------------------+
| 0.2368 | 0.4644 |
+--------------------------------------+-----------------------------------+
在这个例子中，如果我们创建多列索引，推荐的顺序是(language,countrycode)
在没有排序或分组的时候，将选择性最好的放在前面。
表没有均匀分布的特殊情况怎么办？当单个值出现的次数比所有其他值多时？在这种情况下，任何索引都不够好。注意不要假设平均情况下的性能代表特殊情况下的性能。特殊情况可能会破坏整个应用程序的性能。
以上测试用例的软件版本是：
mysql> select * from sys.version;
+-------------+-----------------+
| sys_version | mysql_version |
+-------------+-----------------+
| 1.5.1 | 5.6.31-77.0-log |
+-------------+-----------------+
