MySQL的索引使用

聚集索引和非聚集索引

聚集索引指的是在mysql 的innodb 引擎中的主键索引中，这张表的所有字段数据都存放在叶子结点中。通过索引就可以在不回表的情况下查询出所有的字段数据，这就叫做聚集索引。

在innodb中，创建完成的表，在实际的磁盘中会生成两个文件，一个是.frm文件，一个是.ibd文件，在.frm文件中存储的是表的字段结构等配置，而在ibd文件中，存储的是该表的索引及数据。innodb对应的主键索引及对应数据都是存放在idb文件中。

所有的二级索引都是非聚集索引。

非聚集索引比较典型案例是在MyIsAm引擎中，它的主键索引就是非聚集索引。非聚集索引的意思是，在B+树结构索引树中，与聚集索引相反，索引对应的每一行数据是存在另一个文件中，所有叶子结点对应的存放的是索引及索引对应数据的物理地址。在MyIsAM引擎中，它生成的表文件有3种，一个是.frm，表字段结构文件，一个是.MYD表数据文件，还有一个是.MYI表索引文件。相比较Innodbu引擎来说，使用MyIsAm引擎的主键索引查询数据时，通过.MYI文件查询到数据的物理地址，再根据物理地址到MYD获取实际的数据，这个操作很像回表查询。

注意：MySQL8.0版本中，Innondb的表结构文件已经被全新的字段缓存设计取代。而在MyIsAm中的.frm文件也变成了.sdi（Serialized Dictionary Information）文件。

 

所以二级索引存的是什么呢？

我们通过下图中的信息可以看到，二级索引存储的是二级索引的键，也就是以name作为key来进行一个排序，构建一棵B+树，而叶子节点中存储的对应的值则是每条记录的主键值。我们在使用二级索引的时候，流程是通过二级索引找到其对应的叶子节点，拿到记录的主键值，然后再通过主键获取该条记录的信息。这个现象叫做回表查询。

毫无疑问，如果通过普通的二级索引进行查询时，都有可能进行回表查询。如果进行回表查询的话就可能耗费性能。这里引申出一个概念叫做覆盖索引。就是尽可能不要让查询进行回表查询，争取一次就查询完毕。

 

Innondb的主键索引树，叫做聚集索引，利用聚集索引树可以直接在叶子节点就可以得到这一行的所有数据。

除Innodb引擎外的其他索引，不管是MyIsAm的主键索引，还是二级索引都叫做非聚集索引。

覆盖索引

覆盖索引是表示使用索引的一种场景。通过两个示例来解释一下。

select * from test where name = 'lilei';

当使用*来查询一条语句的时候，如果我们创建了关于name的索引index_name，那么这条查询语句就会先去通过索引index_name来查询，查询到name='lilei'对应的数据的主键，然后再去主键索引中获取所有的数据，因为这里'*'代表查询所有的字段值，在二级索引树中没有这些完整的数据所以只能通过回表来获取。

 

再看下面这条语句，通过索引index_name来查询相应的单个字段时，也是需要回表查询。

select balance from test where name = 'lilei';

 

但是如果建立一个联合索引(name,balance) index_name_balance的话，再来执行select balance from test where name = 'lilei';这条语句看看

像这样通过索引一次性查找到所需要的数据就叫做覆盖索引。

通过上面的例子可以看出，覆盖索引并不是索引的种类，而是索引使用的一种现象。同样是建立在index_name字段上的索引，当我们使用该索引查询除name和id外的其他字段时，那index_name就是一个普通的需要回表的索引，当使用索引index_name查询name和id时，那我们可以称这条查询语句使用了覆盖索引。是不是覆盖索引，其实是取决于我们要查询的字段与索引的匹配度的。

总而言之，查询时通过索引一次性查询出所有数据的现象就叫做覆盖索引。

最左前缀原则

最左前缀原则是在联合索引中才会体现的一个原则，就是在使用联合索引的字段进行搜索时，需要按照索引字段从左到右的顺序来编写查询条件，这样才会用到索引，否则就会走全表扫描。联合索引就是多个字段组合的索引，联合索引树是按照创建索引时字段的顺序来创建的。例如索引index_name_balance是由name字段和balance字段组成的联合索引，他们所创建的索引树，先按照name排序，如果有相同name的值则按照第二个字段balance进行排序，如下图中的hanmeimei、lilei、wangliu和wangwu，仔细看，他们名字相同时他们的balance是递增的，有序的。

而最左前缀原则，用几个示例表示一下：

select name from test1 where name='hanmeimei' and balance=300；---使用索引

select name from test1 where balance=300 and name='hanmeimei'；---使用索引

按照联合索引的所有字段作为查询条件，使用联合索引字段的顺序在查询语句中是不受影响的，也就是说在查询语句中，只要有所有的联合索引字段时，mysql的优化器就会自动优化语句执行。在mysql真正执行时，还需要经过一系列的处理，这个到底层原理再详细介绍。

select name from test1 where name='hanmeimei'；---使用索引

因为name是左边第一个，所以在没有name的单独索引的情况下，也会使用联合索引

select name from test1 where balance=300；---未使用索引

跳过联合索引中的第一个字段name，则不使用索引，因为无法跳过索引树的树头进行使用索引。

从上面的简单例子中可以看出，要使联合索引生效，必须从索引字段的左边字段开始作为条件查询。这就是最左前缀原则的一个简单解释。

 

联合索引的创建方法和创建单字段索引一样，不过是在表中多加几个字段，创建示例如下

语法：CREATE INDEX 索引名 ON 表名(字段名...) 注：多个字段以逗号分隔 
单字段索引创建
CREATE INDEX index_name ON test (name);
联合索引创建
CREATE INDEX index_name_balance ON test (name,balance);

索引失效

索引失效的情况有很多种：

1、破坏最左前缀原则，如上例

2、在查询语句中，使用索引字段进行函数运算，MySQL8.0 可以建立函数索引

3、MySQL优化器预估使用全表扫描比使用索引要快，数据量太大，使用索引的性能不如全表扫描（mysql内部优化器决定的，可以通过explain分析）

4、like查询以%开头， 相当于破坏了最左前缀原则

5、查询条件中的隐形转换，比如使用字符串字段的索引查询，语句中并没有写双引号，导致查询语句在执行前的处理中进行了隐性转换，自动转换为了int类型，此时并未使用索引

6、where子句索引列有运算

7、or关键字关联的列其中有一个没有索引，则索引不会生效