如何正确的建立索引

作者: C_ROCK | 来源:发表于2020-03-05 12:17 被阅读0次

    在日常开发中,遇到 Mysql 查询慢,"索引"是我们最经常使用的一个技术,索引可以加快读取数据的速度,但是我们要知道索引并不是乱加的.如果使用不好还有可能适得其反.拖垮整个数据库.
    我们需要了解一下几个常用的知识点

    回表

    假如有这么一个表:

    mysql> create table student (
    ID int primary key,
    id_card int NOT NULL DEFAULT 0, 
    name varchar(16) NOT NULL DEFAULT '',
    age int NOT NULL 0,
    index id_card(id_card)
    )
    engine=InnoDB;
    #插入以下数据
    insert into student values(10,1, 'a'),(20,2,'b'),(40,3,'c'),(50,5,'d'),(60,6,'e'),(70,7,'f');
    

    执行 select * from student where id_card between 3 and 5, 需要执行几次树的搜索操作,会扫描多少行?

    我们先来看一下这个语句的执行流程

    1. 在 id_card 索引树上找到 id_card=3 的记录,索引上存着主键 取得ID = 40;
    2. 再到主键索引树查到 ID=40 对应的记录;
    3. 在 id_card 索引树取下一个值 k=5,取得 ID=50;
    4. 再回到 ID 索引树查到 ID=50 对应的记录;
    5. 在 id_card 索引树取下一个值 k=6,不满足条件,循环结束。

    在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了 id_card 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。

    回表次数越多,效率越低

    联合索引

    两个或更多个列上的索引被称作联合索引,联合索引又叫复合索引。对于复合索引,Mysql 从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持 a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。

    联合索引 配合 覆盖索引最左前缀 是最常用的优化手段. 可以满足很多场景下的索引需求.

    覆盖索引

    上面的例子中,因为我们查找的字段是 * 所以会造成回表, 如果我们是 select id from student where id_card between 3 and 5 则不需要回表, 当索引满足了我们的查询请求而不需要回表时,我们称为 覆盖索引

    覆盖索引可以避免回表查询,所以可以有效的的提高查询效率, 使用覆盖索引来提高查询效率是我们常用的一种优化手段.

    最左前缀原则

    在 Mysql 建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配

    示例:
    对列col1、列col2和列col3建一个联合索引

    KEY test_col1_col2_col3 on test(col1,col2,col3);

    联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
    所以一下3个语句都可以走索引

    #上面这个查询语句执行时会依照最左前缀匹配原则,检索时会使用索引(col1,col2)进行数据匹配。
    SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”
    
    SELECT * FROM test WHERE col1=“1” AND clo2=“2”
    # 与查询的顺序无关,优化器会执行最优 执行路径.
    SELECT * FROM test WHERE col2=“2” AND clo1=“1”
    

    注意

    在建立联合索引的时候,如何安排索引内的字段顺序?

    这里我们的评估标准是,索引的复用能力

    因为可以支持最左前缀,所以当已经有了 (col1,col2) 这个联合索引后,一般就不需要单独在 col1 上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的

    那么,如果既有联合查询,又有基于 col1、 col2各自的查询呢?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护 (col1,col2)、(col2) 这两个索引。这时候,我们要考虑的原则就是空间了。比如上面这个学生表的情况,从存储的角度来说 name 字段是比 age 字段大的 ,那我就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。

    索引下推

    索引下推优化(index condition pushdown)是在 MySQL 5.6之后引入的,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    例如

    #有一个 联合索引(name, age)
    select * from student where name like '张%' and age=10 and ismale=1;
    

    在 Mysql5.6 之前,搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录。然后只能从第一个记录开始一个个回表。到主键索引上找出数据行,再对比字段值。

    在 Mysql5.6 之后,搜索索引树的时候,用 “张”,找到第一个满足条件的记录。然后在索引遍历过程中,对索引中包含的字段先做判断,以sql 为例 age != 10 的数据不会再回表,直接过滤掉不满足条件的记录,从而大大减少了回表次数。

    更多文章

    相关文章

      网友评论

        本文标题:如何正确的建立索引

        本文链接:https://www.haomeiwen.com/subject/ioztrhtx.html