深入浅出数据库索引原理

这句SQL语句的执行过程如下

首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置

最后， 从得到的真实数据中取得user_name字段的值返回， 也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引，

create index index_birthday_and_user_name on user_info(birthday, user_name);

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，如下图

再详细说下这个覆盖索引，也叫组合索引

组合索引，即一个索包含多个列。(当一个表中查询大的情况下，where条件中有多个，那么可以使用组合查询,不会扫描表，直接从索引中获取，查询效率高)

它遵循最左前缀匹配原则，也是就是说一个查询可以只使用复合索引最左侧的一部份。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

因为组合索引建立的时候先对第一维排序，再对第二维，再对第三维...（非常不严谨的说法，但便于初学者理解）

对于如下表结构：

create table test(
    a int,
    b int,
    c int,
    KEY a(a,b,c)
);

那么我们看一下下列语句：

优: select * from test where a=10 and b>50
差: select * from test where b = 50

优: select * from test order by a
差: select * from test order by b
差: select * from test order by c

优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c

优: select * from test where a>10 order by a
差: select * from test where a>10 order by b   // 其实会索引失效
差: select * from test where a>10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c

重点：如果where条件第一个参数取范围值，会导致索引失效（>或者<等相关范围查询），后面的索引也会失效。
比如：select * from test where a>10 order by b 组合索引失效。

 注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

参考链接：

1. 知乎专栏-深入浅出数据库索引原理

2. CSDN_xlxxcc-MySl单列索引和组合索引

3. CSDN_工作QQ感冒的石头-mysql多个单列索引和联合索引的区别详解

深入浅出数据库索引原理

标签：存储空间   创建索引   多列   联合   参考   直接   建表   sele   ima