找出冗余索引

找出冗余索引

​ 最左原则 ：例如表 A 有 3 个索引, index1(a,b),index2(a),index3(b)。index1(a,b) 最左边第一个字段包含了 index2,所以 index2 是冗余的索引,建议删掉。

mysql> select * from sys.schema_redundant_indexes limit 1\G;
*************************** 1. row ***************************
              table_schema: sakila
                table_name: customer
      redundant_index_name: idx_fk_store_id
   redundant_index_columns: store_id
redundant_index_non_unique: 1
       dominant_index_name: idx_storeid_email
    dominant_index_columns: store_id,email
 dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `sakila`.`customer` DROP INDEX `idx_fk_store_id`
1 row in set (0.02 sec)

mysql> show index from  sakila.customer;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customer |          0 | PRIMARY           |            1 | customer_id | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | idx_fk_store_id   |            1 | store_id    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | idx_fk_address_id |            1 | address_id  | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | idx_last_name     |            1 | last_name   | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | idx_email         |            1 | email       | A         |         599 |     NULL | NULL   | YES  | BTREE      |         |               |
| customer |          1 | idx_storeid_email |            1 | store_id    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | idx_storeid_email |            2 | email       | A         |         599 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)