索引选择成本计算
查询未使用过的索引
计算索引区分度
- SHOW INDEXES FROM table;
- information_schema.STATISTICS表查看Cardinality
optimizer_trace【可查看引擎选择索引依据】
函数索引
索引扩展(Index Extensions)

索引选择成本计算

更新mysql数据统计占比：

查询未使用过的索引

MySQL查询冗余索引和未使用过的索引操作Mysql脚本之家
MySQL5.7及以上
SELECT * FROM schema_unused_indexes WHERE object_schema != ‘performance_schema’;

计算索引区分度

select
count(DISTINCT audit_status)/COUNT() AS audit_rate
COUNT(DISTINCT product_id)/COUNT() AS product_rate
FROM product_comment
索引相关 - 图3

SHOW INDEXES FROM table;

查看索引的cardinality
Cardinality 是基数的意思，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性
索引相关 - 图4

information_schema.STATISTICS表查看Cardinality

预估的

SELECT 
    CONCAT(t.TABLE_SCHEMA,'.',t.TABLE_NAME) table_name,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
      SELECT table_schema,table_name,index_name,seq_in_index
      FROM information_schema.STATISTICS
      WHERE seq_in_index=1 )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema','sys') 
ORDER BY SELECTIVITY;