重复、冗余和不可见索引

2023-01-13 08:46 abce 阅读(83) 评论(0) 编辑收藏举报

如何找出重复的索引？

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)