重复、冗余和不可见索引

如何找出重复的索引？

pt-duplicate-key-checker可以找出数据库中的表是否有重复的索引或主键。

对于每个重复的索引，pt-duplicate-key-checker会给出drop index语句。

比如：

$ pt-duplicate-key-checker --host=localhost --user=percona --ask-pass
Enter password:
# ########################################################################
# mytestdb.authors_test
# ########################################################################

# idx_first_name is a left-prefix of idx_first_name_last_name
# Key definitions:
#   KEY `idx_first_name` (`first_name`),
#   KEY `idx_first_name_last_name` (`first_name`,`last_name`),
# Column types:
#	  `first_name` varchar(50) default null
#	  `last_name` varchar(50) default null
# To remove this duplicate index, execute:
ALTER TABLE `mytestdb`.`authors_test` DROP INDEX `idx_first_name`;

# Key idx_last_name_id ends with a prefix of the clustered index
# Key definitions:
#   KEY `idx_last_name_id` (`last_name`,`id`)
#   PRIMARY KEY (`id`),
# Column types:
#	  `last_name` varchar(50) default null
#	  `id` int not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `mytestdb`.`authors_test` DROP INDEX `idx_last_name_id`, ADD INDEX `idx_last_name_id` (`last_name`);

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes   4125830
# Total Duplicate Indexes  2
# Total Indexes            129

 

如何找出未被使用的索引？

借助sys.schema_unused_indexes找出自启动以来没有被使用的索引

mysql> select * from sys.schema_unused_indexes 
       where index_name not like 'fk_%' and object_schema='mytestdb' and object_name='testtable';
+---------------+-------------+----------------+
| object_schema | object_name | index_name     |
+---------------+-------------+----------------+
| mytestdb      | testtable   | idx_first_name |
| mytestdb      | testtable   | idx_last_name  |
+---------------+-------------+----------------+
2 rows in set (0.00 sec)

既然索引idx_first_name已经被使用了，自然就不会被列出来：

mysql> select last_name from testtable WHERE first_name=’Arun’;
+-----------+
| last_name |
+-----------+
| Jith      |
+-----------+
1 rows in set (0.00 sec)

mysql> select * from sys.schema_unused_indexes 
       where index_name not like 'fk_%' and object_schema='mytestdb' and object_name='testtable';
+---------------+-------------+---------------+
| object_schema | object_name | index_name    |
+---------------+-------------+---------------+
| mytestdb      | testtable   | idx_last_name |
+---------------+-------------+---------------+
1 row in set (0.01 sec)

 

在删除索引之前，将索引置为不可见的

在MySQL 8.0中，有一个特性允许你拥有一个不可见的索引。这意味着在表上创建索引，但优化器默认不使用它。通过使用该特性，可以测试删除索引的影响，而不实际删除索引。如果需要，可以使索引再次可见，从而避免将索引重新添加到更大的表的耗时过程。

set_var(optimizer_switch = 'use_invisible_indexes=on')允许在单个查询期间为特定的应用程序或模块使用不可见索引，同时防止它在整个应用程序中使用。

让我们看一个简单的例子，看看它是如何工作的。

CREATE TABLE `authors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50),
  `last_name` varchar(50),
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB; 

默认情况下，索引被设置为可见，我们可以使用alter table 表名 alter index 索引名 将其设置为invisible/visible。

要看索引的详细信息，可以使用"show indexes from table;"或查询information_schema.statistics。

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'mytestdb' AND TABLE_NAME = 'authors_test';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| email      | YES        |
| PRIMARY    | YES        |
+------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE authors_test ALTER INDEX email INVISIBLE;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'mytestdb' AND TABLE_NAME = 'authors_test';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| email      | NO         |
| PRIMARY    | YES        |
+------------+------------+
2 rows in set (0.00 sec)

将email上的索引置为不可见后，就不能使用到该索引了：

mysql> explain select email from authors_test WHERE email='amanda31@example.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: authors_test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10063
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

使用hint，SET_VAR(optimizer_switch = 'use_invisible_indexes=on') ，可以让查询临时使用不可见索引：

mysql> explain select /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ first_name 
               from authors_test WHERE email='amanda31@example.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: authors_test
   partitions: NULL
         type: const
possible_keys: email
          key: email
      key_len: 302
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

 

建议删除重复和不必要的索引，以防止在高并发工作负载和表上的数据分布不理想的情况下性能下降。不需要的索引会占用不必要的磁盘空间，这会导致DML和读取查询的开销。要测试在不完全删除索引的情况下删除索引的效果，可以先将其设置为不可见。