mysql index_Mysql的索引（index）

mysql索引(index)

一、什么是索引

索引(Index)是帮助MySQL高效获取数据的数据结构。

二、索引的分类(从应用层面来划分)

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引，仅加速查询

唯一索引：加速查询 列值唯一(可以有null)

主键索引：加速查询 列值唯一(不可以有null) 表中只有一个

组合索引：即一个索引包含多个列，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

在此引申几个概念：

1、索引合并：使用多个普通索引组合搜索。

2、覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。科普时间——覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

3、索引下推：mysql5.6之后引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。科普时间—— Index Condition Pushdown(索引下推) MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下： people表中(zipcode，lastname，firstname)构成一个索引。

SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

●如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

●如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

4、联合索引的最左前缀匹配原则：对于联合索引，MySQL 会从左开始一直向右匹配，直到遇到范围查询(> ， < ，between，like)就停止匹配。科普时间——MySQL 建立联合索引的规则是这样的，它会首先根据联合索引中最左边的，也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。第一个字段是绝对有序的，从第二个字段开始是无序的，这就解释了为什么直接使用第二字段进行条件判断用不到索引了(从第二个字段开始，无序，无法走 B Tree 索引)！这也是 MySQL 在联合索引中强调最左前缀匹配原则的原因。

详细举例参考材料：(https://blog.csdn.net/u013568373/article/details/93891531)

三、索引的存储结构(从存储结构上来划分)

在创建索引时，通常采用的数据结构有：Hash、二叉搜索树、红黑树、BTree以及B Tree。以下介绍常见的两种：

1、Hash索引：

2、B Tree索引：索引B Tree的叶子节点存储了被按照一定顺序一个个紧密地排列在一起存储的数据行，也被称之为聚簇索引。而索引B Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。科普时间——B Tree索引和Hash索引区别：哈希索引适合等值查询，但是不无法进行范围查询。哈希索引没办法利用索引完成排序，哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

四、聚簇索引和非聚簇索引(从数据的物理顺序与键值的逻辑顺序关系划分)

(一)定义

实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索引(nonclustered index，也称非聚类索引、非簇集索引)。

聚簇索引在同一个B-Tree中保存了索引列和具体的数据，在聚簇索引中，实际的数据保存在叶子页中，中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引，因为在一个表中数据的存放方式只有一种。

非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

通过以下例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果您不知道一个字的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

(二)何时使用聚集索引或非聚集索引动作描述使用聚集索引使用非聚集索引列经常被分组排序应应

返回某范围内的数据应不应

一个或极少不同值不应不应

小数目的不同值应不应

大数目的不同值不应应

频繁更新的列不应应

外键列应应

主键列应应

频繁修改索引列不应应

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