• 周日. 6 月 16th, 2024

    MySQL复合索引原理

    什么是复合索引

    复合索引也叫联合索引,是MySQL的InnoDB引擎中的一个索引方式,如果一个系统频繁的使用相同的几个字段查询,我们就可以考虑给这几个字段建立复合索引来提高查询效率。

    CREATE TABLE `table_name` (
    	`id` BIGINT ( 20 ) NOT NULL PRIMARY KEY,
    	`a` INT ( 11 ),
    	`b` INT ( 11 ),
    	`c` VARCHAR ( 22 ),
            KEY `key_a_b_c` ( `a`, `b`, `c` )  //建立a,b,c字段复合索引
    ) ENGINE = INNODB DEFAULT charset = utf8;

    复合索引的匹配原则为最左前缀原则。

    最左前缀原则就是要考虑查询字段的顺序,只有遵守这个原则才能最大地提高查询的效率,下面我们举个例子说明最左前缀原则

    以上边建立的表为例

    #完全按创建的顺序,能走到a,b,c 3个字段的索引, 评级:优化最高
    SELECT * FROM test.table_name WHERE a=1 AND b=2 AND c='3';
    
    #换了b和c的顺序,MYSQL会进行优化,效率和上面的一样,评级:优化最高
    SELECT * FROM test.table_name WHERE a=1 AND c="3" AND b=2;
    
    #能走到a和b的索引,评级:优化最高
    SELECT * FROM test.table_name WHERE a=1 AND b=2;
    
    #能走到a和b的索引,b的范围查询不影响优化,评级: 优化最高
    SELECT * FROM test.table_name WHERE a=1 AND b<2;
    
    #能走到a的索引,评级: 优化最高
    SELECT * FROM test.table_name WHERE a= 1;
    
    #能走到a的索引,评级:优化最高
    SELECT * FROM test.table_name ORDER BY a;
    
    #只能走到a的索引走不到c的索引,如果c的离散度高则查询效率很低,评级:优化差
    SELECT * FROM test.table_name WHERE a=1 AND c="3";
    
    #能走到a和b的索引走不到c的索引,b的范围查询使后面字段无法走索引,评级: 优化差
    SELECT * FROM test.table_name WHERE a=1 AND b < 2 AND c="3";
    
    #能走到a的索引,评级: 优化最高
    SELECT * FROM test.table_name WHERE a>1 order by a;
    
    #能走到a的索引,走不到b的索引 评级: 优化差
    SELECT * FROM test.table_name WHERE a>1 order by b;
    
    #同上,评级: 优化差
    SELECT * FROM test.table_name where a>1 order by c;
    
    #走不到b和c的索引,最左前缀原则必须以建立索引的第一个字段作为第一个条件,评级:最差
    SELECT * FROM test.table_name WHERE b=2 AND c="3"

    MySQL会为InnoDB的每个表建立聚簇索引,如果表有索引会建立二级索引。聚簇索引以主键建立索引,如果没有主键以表中的唯一键建立,唯一键也没会以隐式的创建一个自增的列来建立。聚簇索引和二级索引都是一个b+树,b+树的特点是数据按一定顺序存在叶子节点且每页数据相连。一般情况下使用索引查询时,先查询二级索引的b+树,查到数据并拿数据中保存的主键回查聚簇索引查到所有数据。下面我们举个例子来重现这个过程。

    以下面表举例,假设表中已经存了部分数据:

    create table `user_info`(
      `id` bigint(20) NOT NULL  PRIMARY KEY,
      `name` varchar(11),
      `age` int(11),
      `phone` varchar(20),
       KEY `key_name_age` (`name`,`age`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    InnoDB建立的聚簇索引和二级索引如下图

    假如我们想要查找名字为zhaoliu,年龄为30的人的信息。即name=’zhaoliu’,age=30

    • (1)先查二级索引,先用二分法查找发现在wangwu名字的右边
    • (2)读取右边的这页的数据到内存,二分法查到数据2个name为zhaoliu人。
    • (3)继续二分法比较age查到数据id=31
    • (4)id=31回查聚簇索引先用二分法查找发现在31右边
    • (5)读取31左边这页数据到内存,二分法查到数据并返回数据

    如果你仅仅查找id,name和age数据那么这样就用到了覆盖索引,这样就不用回查聚簇索引,在第(3)步直接返回数据即可。

    root