如何为MySQL表创建高效索引?

  • 回答 (1)
  • 关注 (0)
  • 查看 (261)

已经给表上加了很多索引,为什么感觉查询还是没有那么理想呢?

壹捌柒肆壹捌柒肆修改于
请叫我sqlphp程序员修改于
推荐

我们现在都用innodb引擎,都用B+ tree索引,这里把几个基本的概念阐述一下,B+ tree的原理不再赘述。

  • 用B+ tree的目标: access less record ,换成白话就是从大量数据中找出少量数据
  • cardinality: the count of unique record ,count(distinct column)
  • high selectivity:高选择性,什么叫高选择性,姓名这个字段就是高选择性,而性别就不具备高选择性

那我们该怎么建索引呢?

正确的做法是, 业务里尽量走选择度高的字段查询,然后再这些列上加索引,这样才能最大发挥B+ tree的优势。

我们怎么来看一个索引列的选择度高不高呢?

SELECT 
    (SELECT 
            COUNT(DISTINCT 索引列)
        FROM
            tb_name / (SELECT 
            COUNT(索引列)
        FROM
            tb_name) selectivity;
结果集越接近1,说明选择度越高tips:这个也可以根据information_schema中的statistics和tables表来查,但是这里面是采样预估的数据,不是非常精确.如何将线上的低效索引一下子全部抓出来呢?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
        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')
HAVING SELECTIVITY < 0.1
ORDER BY SELECTIVITY DESC

tips:

1、针对复合索引这里只看最前面一个列,没必要把每个都统计出来,要用group by搞,比较麻烦。

2、通常来说可以将这些索引直接删掉,但有可能这个索引暂时没用,以后会用。MySQL 8.0版本推出了invisible index,可以将这些索引设置成invisible index,优化器就不会走这个索引,跑一段时间观察对业务有没有影响,如果没有影响再把这个索引删掉。

扫码关注云+社区

领取腾讯云代金券