MySQL索引

一、两大类索引定义

1.1 聚集索引

聚集索引（Clustered index）定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引也称为聚簇索引、主键索引等、聚类索引、簇集索引。

1.2 非聚集索引定义

非聚集索引（Non-Clustered index）定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚集索引也称普通索引、二级索引等，除聚集索引外的索引，即非聚集索引。

二、索引存储结构

2.1 聚集索引

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引

聚集索引的顺序就是数据的物理存储顺序，它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

聚集索引

比如字典中，用【拼音】查汉字，就是聚集索引。因为正文中字都是按照拼音排序的。而用【偏旁部首】查汉字，就是非聚集索引，因为正文中的字并不是按照偏旁部首排序的，我们通过检字表得到正文中的字在索引中的映射，然后通过映射找到所需要的字。

2.2 非聚集索引

非聚集索引： 索引顺序与物理存储顺序不同

非聚集索引

三、何时使用聚集索引或非聚集索引

何时使用聚集索引或非聚集索引

• 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。

• 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。

• 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如某个表有一个时间列，恰好把聚合索引建立在了该列，这时查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为这本字典正文是按日期进行排序的，聚集索引只需要找到要检索的所有数据中的开头和结尾数据即可。而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

四、explain分析索引

4.1 数据准备

4.1.1 建表

CREATE TABLE IF NOT EXISTS `user` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` VARCHAR(60),
    `age` TINYINT(4),
    PRIMARY KEY (id),
    INDEX idx_age (age)
)  ENGINE=INNODB CHARSET=UTF8MB4;

id 字段是聚簇索引，age 字段是普通索引（二级索引）

4.1.2 插入数据

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

查询数据

4.2 索引存储结构

4.2.1 id 聚集索引

id 是主键且为聚集索引，其叶子节点存储的是对应行记录的数据。

id 聚集索引

4.2.2 age非聚集索引

age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值。

age非聚集索引

4.3 查询

如果查询条件为主键（聚集索引），则只需扫描一次B+树即可通过聚集索引定位到要查找的行记录数据。

select * from user where id = 1;

id查询

如果查询条件为普通索引（非聚集索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚集索引的值，然后第二次扫描通过聚集索引的值定位到要查找的行记录数据。

select * from user where age = 30;

1》先通过普通索引【age=30】定位到主键值 【id=1】

普通索引

2》在通过聚集索引【id=1】定位到行记录数据

普通索引

五、回表查询

先通过普通索引的值定位到聚集索引值，在通过聚集索引的值定位到行记录数据，要通过扫描两次索引B+树，它的性能较扫描一次较低。

六、索引覆盖

只需在一颗索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

select id,age from user where age = 10;

6.1 如何实现覆盖索引

常见的方法是：将被查询的字段，建立到联合索引里去（若查询有where条件，同时where条件字段也必须为索引字段）。

例一：覆盖索引

explain select id,age from user where age = 10;

explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引。此时的Extra列的【Using Index】表示进行了聚集索引。

覆盖索引

例二：索引未覆盖

explain select id,age,name from user where age = 10;

索引未覆盖

explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询。

为了实现索引覆盖，需要建组合索引idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

索引覆盖

6.2 查询条件中，一定不要使用select *

在SQL优化的方式中有一条是【查询条件中，一定不要使用select *】，其中有一个理由就是【覆盖索引】。因为使用select *失去mysql优化器“覆盖索引”策略优化的可能性。

例如，有一个表为t(a,b,c,d,e,f)，其中，a为主键，b列有索引。

那么，在磁盘上有两棵 B+ 树，即聚集索引和辅助索引（包括单列索引、联合索引），分别保存(a,b,c,d,e,f)和(a,b)，如果查询条件中where条件可以通过b列的索引过滤掉一部分记录，查询就会先走辅助索引，如果用户只需要a列和b列的数据，直接通过辅助索引就可以知道用户查询的数据。

如果用户使用select *，获取了不需要的数据，则首先通过辅助索引过滤数据，然后再通过聚集索引获取所有的列，这就多了一次b+树查询，速度必然会慢很多。

6.3 哪些场景适合使用索引覆盖来优化SQL

6.3.1 全表count查询优化

explain select count(age) from user;

count索引覆盖

6.3.2 分页查询

explain select id,age,name from user order by age limit 100,2;

分页索引覆盖

七、MySQL聚集索引&非聚集索引

MySQL的两种数据存储方式，一种是InnoDB，一种是MyISAM。这两种存储都是基于B+树的存储方式，但是也有点不同。

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。主索引和辅助索引没有区别都是非聚集索引。索引页正常大小为1024字节，索引页存放在.MYI 文件中。MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

InnoDB 也使用B+Tree作为索引结构，索引页大小16，和表数据页共同存放在表空间中。从InnoDB表数据存放方式可看出InnoDB表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

• 如果表设置了主键，则主键就是聚集索引
• 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚集索引。
• 以上都没有，则会默认创建一个隐藏的row_id作为聚集索引

一般来说，InnoDB 会以聚集索引的形式来存储实际的数据，它是其它二级索引的基础。

八、Innodb中建议设置主键自增

目前MySQL中就是将自增Id强制设定为主键索引，这是为了B+Tree和分页。MySQL中每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增！主键自增写入时新插入的数据不会影响到原有页，插入效率高！且页的利用率高！但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率，降低页的利用率，这也是为什么在Innodb中建议设置主键自增的原因。

九、联合索引最左前缀匹配原则

9.1 最左前缀匹配原则概念

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

要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

联合索引

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

由于整个过程是基于explain结果分析的，那接下来在了解下explain中的type字段和key_lef字段。

9.2 explain说明

9.2.1 type（联接类型）

下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:（重点看ref,rang,index）

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计。

• const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

注意：ALL全表扫描的表记录最少的表如t1表。

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。

• index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

• ALL：Full Table Scan，遍历全表以找到匹配的行。

9.2.2 key_len

key_len显示MySQL实际决定使用的索引的长度。如果索引是NULL，则长度为NULL。如果不是NULL，则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。

计算规则：

1.定长字段，int占用4个字节，date占用3个字节，char(n)占用n个字符。

2.变长字段varchar(n)，则占用n个字符+两个字节。

3.不同的字符集，一个字符占用的字节数是不同的。Latin1编码的，一个字符占用一个字节，gdk编码的，一个字符占用两个字节，utf-8编码的，一个字符占用三个字节。由于我数据库使用的是Latin1编码的格式，所以在后面的计算中，一个字符按一个字节算）

4.对于所有的索引字段，如果设置为NULL，则还需要1个字节。

9.3 explain分析

9.3.1 数据准备

创建数据表

CREATE TABLE staffs (
    id INT(10) DEFAULT NULL,
    name CHAR(10) DEFAULT NULL,
    age INT(10) DEFAULT NULL,
    KEY `id_name_age_index` (`id` , `name` , `age`)
)  ENGINE=INNODB DEFAULT CHARSET=LATIN1;

插入数据

insert into staffs(id,name,age) values(1,'hhh',20);
insert into staffs(id,name,age) values(2,'bbb',21);
insert into staffs(id,name,age) values(3,'ccc',22);

该表中对id列.name列.age列建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age）。

9.3.2 全值匹配查询时

SQL 1

explain select * from staffs where id=1 and age=20 and name='hhh';

1

SQL 2

explain select * from staffs where name='hhh' and id=1 and age=20;

2

SQL 3

explain select * from staffs where age=20 and name='hhh' and id=1;

3

通过观察上面的结果图可知，where后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引，可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引。另外通过观察上面三个图中的key_len字段，也可说明在搜索时使用的联合索引中的（id_name_age）索引，因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，又使用的是latin1编码，所以占11个字节，age为int型允许null，所以也占用5个字节，所以该索引长度为21（5+11+5），而上面key_len的值也正好为21，可证明使用的（id_name_age）索引。

9.3.3 匹配最左边的列时

SQL 1

explain select * from staffs where id=1;

1

该搜索是遵循最左匹配原则的，通过key字段也可知，在搜索过程中使用到了联合索引，且使用的是联合索引中的（id）索引，因为key_len字段值为5，而id索引的长度正好为5（因为id为int型，允许null，所以占5个字节）。

SQL 2

explain select * from staffs where id=1 and name='hhh';

2

由于id到name是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id_name）索引，因为key_len字段值为16，而(id_name)索引的长度正好为16（因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，又使用的是latin1编码，所以占11个字节）。

SQL 3

explain select * from staffs where id=1 and name='hhh' and age=20;

3

由于上面三个搜索都是从最左边id依次向右开始匹配的，所以都用到了id_name_age_index联合索引。那如果不是依次匹配呢？

SQL 4

explain select * from staffs where id=1 and age=20;

4

通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id）索引，从key_len字段也可知。因为联合索引树是按照id字段创建的，但age相对于id来说是无序的，只有id只有序的，所以他只能使用联合索引中的id索引。

SQL 5

explain select * from staffs where name='hhh';

5

通过观察发现上面key字段发现在搜索中也使用了id_name_age_index索引，可能许多同学就会疑惑它并没有遵守最左匹配原则，按道理会索引失效，为什么也使用到了联合索引？因为没有从id开始匹配，且name单独来说是无序的，所以它确实不遵循最左匹配原则，然而从type字段可知，它虽然使用了联合索引，但是它是对整个索引树进行了扫描，正好匹配到该索引，与最左匹配原则无关，一般只要是某联合索引的一部分，但又不遵循最左匹配原则时，都可能会采用index类型的方式扫描，但它的效率远不如最做匹配原则的查询效率高，index类型类型的扫描方式是从索引第一个字段一个一个的查找，直到找到符合的某个索引，与all不同的是，index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。

SQL 6

explain select * from staffs where age=20;

6

SQL 7

explain select * from staffs where name='hhh' and age=20;

7

这两个结果跟上面的是同样的道理，由于它们都没有从最左边开始匹配，所以没有用到联合索引，使用的都是index全索引扫描。

9.3.4 匹配列前缀

如果id是字符型，那么前缀匹配用的是索引，中坠和后缀用的是全表扫描。

select * from staffs where id like 'A%';//前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询

9.3.5 匹配范围值

SQL 1

explain select * from staffs where id>1 and id<3;

1

在匹配的过程中遇到<>=号，就会停止匹配，但id本身就是有序的，所以通过possible_keys字段和key_len 字段可知，在该搜索过程中使用了联合索引的id索引（因为id为int型，允许null，所以占5个字节），且进行的是rang范围查询。

explain select * from staffs where id<4 and age>20 and age<50;

SQL 2

2

由于不遵循最左匹配原则，且在id<4的范围中，age是无序的，所以使用的是index全索引扫描。

SQL 3

explain select * from staffs where id<2 and age>20 and age<50;

3

不遵循最左匹配原则，但在数据库中id<2的只有一条（id），所以在id<2的范围中，age是有序的，所以使用的是rang范围查询。

SQL 4

explain select * from staffs where age>20 and age<50;

4

不遵循最左匹配原则，而age又是无序的，所以进行的全索引扫描。

9.3.6 准确匹配第一列并范围匹配其他某一列

SQL 1

explain select * from staffs where id=1 and age<50;

1

由于搜索中有id=1，所以在id范围内age是无序的，所以只使用了联合索引中的id索引。