学习索引主要是为了写更快的sql。我们写sql的时候，需要清楚的知道sql为什么要带索引。为什么有些sql不带索引？Sql将遍历这些索引。为什么？我们需要了解它的原理和内部具体流程，这样才能更方便地使用它，写出更高效的sql。在本文中，我们只是了解这些问题。

在阅读这篇文章之前，你需要知道一些事情:

什么是索引？mysql索引原理详解mysql索引管理详解

如果你还没有看过以上三篇文章，那你最好读一读，否则下面的内容就很难理解了。

我们先来复习一些知识。

在本文中，我们以innodb存储引擎为例来说明。

mysql使用B树存储索引信息。

B树结构如下:

说说B树的一些特点:

叶子节点(最底层)存储关键字(索引字段的值)信息和对应的数据，叶子节点存储所有记录的关键字信息。

其他非叶节点只存储子节点的关键字信息和指针。

每个叶子节点相当于mysql中的一个页面，同一级别的叶子节点以链表的形式连接。

每个节点(页面)中存储多条记录，记录以单链表的形式连接起来形成有序链表，按照索引字段排序。

在B树中检索数据时:每次检索都是从根节点开始，总是需要树叶。

InnoDB的数据是以数据页为单位读写的。也就是说，当需要读取一条记录时，并不是从磁盘中读取记录本身，而是以页为单位将整条记录加载到内存中。一页中可能有多条记录，然后在内存中搜索该页。在innodb中，默认情况下每页的大小是16kb。

MySQL中的索引分为

聚集索引(主键索引)

每个表都必须有一个聚集索引，整个表的数据存储以B树的形式存储在一个文件中，以B叶的子节点中的键作为主键值，数据作为完整的记录信息；非叶节点存储主键的值。

通过聚簇索引检索数据，只需要按照B树的搜索过程，即可以检索到对应的记录。

非聚集索引

每个表可以有多个非聚集索引，采用B树结构，其中叶节点的键是索引字段的值，数据是主键的值；非叶节点只存储索引字段的值。

通过非聚集索引检索记录时，需要两次操作，首先从非聚集索引中检索主键，然后从聚集索引中检索主键对应的记录，这比聚集索引多了一次操作。

怎么索引？为什么有些查询没有索引？为什么不用函数来索引数据呢？

这些问题可以先放一放。我们来看B树检索数据的过程，属于原理部分。了解了B树的各种数据检索流程后，就可以理解上述问题了。

这个查询被索引通常是什么意思？

当我们检索一个字段的值时，如果能够快速定位到目标数据所在的页面，有效减少页面的io操作，而不需要扫描所有的数据页面，我们认为这种情况可以有效地使用索引，也就是所谓的索引。如果在此过程中无法确定这些页面中的数据，我们认为该索引对于此查询是无效的。

B树中的数据检索过程

唯一记录检索

如上图，所有数据都是唯一的。查询105记录的过程如下:

将P1页加载到内存在内存中采用二分法查找，可以确定105位于[100,150)中间，所以我们需要去加载100关联P4页将P4加载到内存中，采用二分法找到105的记录后退出

查询一个值的所有记录。

如上图，查询105所有记录的过程如下:

将P1页加载到内存在内存中采用二分法查找，可以确定105位于[100,150)中间，100关联P4页将P4加载到内存中，采用二分法找到最有一个小于105的记录，即100，然后通过链表从100开始向后访问，找到所有的105记录，直到遇到第一个大于100的值为止

范围搜索

数据如上图所示，所有记录[55，150]均可查询。由于页面是双向链表升序排列，页面内部的数据是单项升序排列，我们只需要找到范围初始值所在的位置，然后依靠链表访问两个位置之间的所有数据。流程如下:

将P1页加载到内存内存中采用二分法找到55位于50关联的P3页中，150位于P5页中将P3加载到内存中，采用二分法找到第一个55的记录，然后通过链表结构继续向后访问P3中的60、67，当P3访问完毕之后，通过P3的nextpage指针访问下一页P4中所有记录，继续遍历P4中的所有记录，直到访问到P5中所有的150为止。

模糊匹配

数据如上图。

查询所有以' f '开头的记录

流程如下:

将P1数据加载到内存中在P1页的记录中采用二分法找到最后一个小于等于f的值，这个值是f，以及第一个大于f的，这个值是z，f指向叶节点P3，z指向叶节点P6，此时可以断定以f开头的记录可能存在于[P3,P6)这个范围的页内，即P3、P4、P5这三个页中加载P3这个页，在内部以二分法找到第一条f开头的记录，然后以链表方式继续向后访问P4、P5中的记录，即可以找到所有已f开头的数据

查询包含“f”的记录

包含一个在sql中写成%f%的查询。我们能通过索引快速定位页面吗？

你可以看看上面的数据。f存在于每一页。我们无法通过P1页面中的记录来判断哪些页面包含F。我们只能通过io加载所有叶节点，并过滤所有记录，以找到包含F的记录..

因此，如果使用% value%,则索引对于查询无效。

最左匹配原则

当B树中的数据项是复杂的数据结构，比如(姓名，年龄，性别)时，B树从左到右建立搜索树。比如搜索(张三，20，F)这样的数据时，B树会先比较名字，确定下一步的搜索方向。如果姓名相同，则依次比较年龄和性别，最终得到搜索。但是当(20，F)这样的数据没有名字就来了，B-tree就不知道下一步要查哪个节点了，因为建立搜索树的时候名字是第一个比较因素，你必须根据名字搜索，才能知道下一步要查哪里。比如在搜索(张三，F)这样的数据时，B树可以用name指定搜索方向，但是下一个字段age是缺失的，所以我们只能找到所有名字是张三的数据，然后匹配性别是F的数据，这是一个很重要的性质，也就是索引最左边的匹配特征。

我们来试几个例子。

下图是三个字段(a，b，c)的联合索引。索引中数据的顺序以A ASC、B ASC、C ASC的排序方式存储在节点中。索引先按A域升序排列，A相同则按B域升序排列，B相同则按C域升序排列。仔细查看节点中的每个数据。

查询a=1的记录

由于页面中的记录是以A ASC、B ASC、C ASC的排序方式存储的，所以A字段是有序的，可以通过二分法快速检索。流程如下:

将P1加载到内存中在内存中对P1中的记录采用二分法找，可以确定a=1的记录位于{1,1,1}和{1,5,1}关联的范围内，这两个值子节点分别是P2、P4加载叶子节点P2，在P2中采用二分法快速找到第一条a=1的记录，然后通过链表向下一条及下一页开始检索，直到在P4中找到第一个不满足a=1的记录为止

查询a=1，b=5的记录。

方法同上，可以确定a=1，b=5的记录在{1，1，1}和{1，5，1}的关联范围内，搜索过程与a=1的相似。

查询b=1的记录。

在这种情况下，通过P1页中的记录，无法判断b=1的记录在那些页中。我们只能锁定索引树的所有叶节点，遍历所有记录，然后进行筛选。此时，索引无效。

根据c的值查询。

这种情况和查询b=1一样，只能扫描所有叶子节点，此时索引无效。

按B和c一起查。

这种索引也不能用，只能扫描所有数据，此时索引无效。

按两个字段查询[a，c]

这只能在索引的A字段中使用。通过A确定索引范围，然后加载与A关联的所有记录，然后过滤C的值..

查询a=1，b>=0，c=1的记录。

这种情况下，只能先确定a=1且b>=0的页面所在的范围，然后才能遍历这个范围内的所有页面。在这个查询的过程中，无法确定C的数据在哪些页面。此时我们称C不索引，只有A和B能有效确定索引页面的范围。

类似的还有>，，alter table test 1 modify id int not null主键； 查询正常，0行受影响(10.93秒) 记录:0重复:0警告:0 mysql >显示test1的索引； ---------------------------------------------------------------------------------------------------------------------------------------------------------// h/]| 1/]| test1 | 0 | PRIMARY | 1

id设置为主键后，会在ID上建立一个聚集索引，任意一个都会被检索到。我们来看看效果:

mysql> select * from test1其中id = 1000000 -- | id | name | sex | email | -- | 1000000 | javacode 1000000 | 2 | javacode1000000@163.com | - 集合中的一行(0.00秒)

这个非常快，这个采用了上面描述的‘唯一记录检索’。

在和范围之间搜索

mysql >从test1中选择count(*)，其中id介于100和110之间； - | count(*)| - | 11 | - 集合中的1行(0.00秒)

也很快，id上有主键索引。上面介绍的范围搜索可以快速定位目标数据。

但是，如果范围太大，跨页太多，速度会比较慢，如下:

MySQL > select count(*)from test1，其中id介于1和2000000之间； - | count(*)| - | 2000000 | - 集合中的一行(1.17秒)

上述id的值跨度太大。1所在的页面和200万所在的页面之间要读的页面很多，所以比较慢。

所以在使用between和的时候，区间跨度不能太大。

在中检索

我们仍然经常使用 in来检索数据。

通常我们做项目的时候，建议少用表连接。比如在电子商务中，如果需要查询订单信息和订单中商品的名称，可以先查询订单表，然后从订单表中取出商品的id列表，通过in的方式从商品表中检索商品信息。因为商品id是商品表的主键，所以检索速度还是比较快的。

按id从400万条数据中检索100条数据，看效果:

mysql> select * from test1 a其中a.id in (100000，100001，100002，100003，100004，100005，100006，100007，100008，100009，100010，100011，100012，100013，100014 100071, 100072, 100073, 100074, 100075, 100076, 100077, 100078, 100079, 100080, 100081, 100082, 100083, 100084, 100085, 100086, 100087, 100088, 100089, 100090, 100091, 100092, 100093, 100094, 100095, 100096, 100097, 100098, 100099); --- | id | name | sex | email | --100000 | javacode 100000 | 2 | javacode100000@163.com | | 100001 | javacode 100001 | 1 | javacode100001@163.com | | 100002 | javacode 100002 | 2 | javacode100002@163.com | ....... | 100099 | javacode 100099 | 1 | javacode100099@163.com | - 集合中的100行(0.00秒)

用时不到1毫秒，还是挺快的。

这相当于搜索多个唯一的记录，然后合并这些记录。

有多个索引时如何查询？

让我们分别对姓名和性别字段建立索引。

mysql >在test1(name)上创建索引idx1 查询正常，0行受影响(13.50秒) 记录:0重复:0警告:0 mysql >在test1(sex)上创建索引idx2 查询正常，0行受影响(6.77秒) 记录:0重复项:0警告:0

看看这个查询:

mysql> select * from test1其中name='javacode3500000 '和sex = 2； -- | id | name | sex | email | -- | 3500000 | javacode 3500000 | 2 | javacode3500000@163.com | - 集合中的一行(0.00秒)

上面的查询速度很快，名字和性别上分别有索引。你认为应该取哪个指标？

有人说名字位于第一位，所以取名字字段所在的索引。该过程可以解释如下:

转到name所在的索引，找到对应于javacode3500000的所有记录。

遍历记录，筛选出性别=2的值。

我们来看看name='javacode3500000 '的检索速度，真的很快，如下:

MySQL > select * from test1 where name = ' javacode 3500000 '； -- | id | name | sex | email | -- | 3500000 | javacode 3500000 | 2 | javacode3500000@163.com | - 集合中的一行(0.00秒)

真的可以取名字索引然后过滤，速度也很快。真的和where之后的字段顺序有关吗？让我们交换姓名和性别的顺序，如下所示:

mysql> select * from test1其中sex=2，name = ' javacode3500000 -- | id | name | sex | email | -- | 3500000 | javacode 3500000 | 2 | javacode3500000@163.com | - 集合中的一行(0.00秒)

速度还是很快的。是不是应该先通过性别索引检索数据，再筛选名字？我们先来看看sex=2的查询速度:

MySQL > select count(id)from test1其中sex = 2； - | count(id)| - | 2000000 | - 集合中的一行(0.36秒)

看上面，查询用时360毫秒，有200万条数据。如果拿性来说，肯定不行。

我们用解释来看看:

mysql >解释select * from test1，其中sex=2，name = ' javacode3500000 --------------------- id | select _ type | table | partitions | type | possible _ keys | key | key _ len | ref | rows | filtered | Extra | ------------- 1 | SIMPLE | test1 | NULL | ref | idx 1，id x2 | idx 1 | 62 | const | 1 | 50.00 | Using where | ------------------------//h/]1行，1警告(0

possible_keys:列出该查询可能采用的两个索引(idx1，idx2)。

其实是idx1(关键列:索引实际取)。

当多个条件中存在指标，且关系为and时，将取区分度高的指标。很明显，姓名字段重复度低，取姓名查询会更快。

模糊查询

看两个查询

MySQL > select count(*)from test1a where a . name like ' javacode 1000% '； - | count(*)| - | 1111 | - 集合中的1行(0.00秒) MySQL > select count(*)from test1a其中a.name类似于“% javacode 1000%”； - | count(*)| - | 1111 | - 集合中的1行(1.78秒)

上面的第一个查询可以使用name字段上面的索引，后面的查询无法确定要搜索的值的范围，所以只能扫描整个表而不使用索引，所以速度比较慢，如上所述。

返回到表格

当要查询的数据不在索引树中时，需要再次从聚集索引中检索。这个过程叫做表返回，比如查询:

MySQL > select * from test1 where name = ' javacode 3500000 '； -- | id | name | sex | email | -- | 3500000 | javacode 3500000 | 2 | javacode3500000@163.com | - 集合中的一行(0.00秒)

上面的查询是*，因为姓名列所在的索引只包含姓名和id两列的值，不包含性别和邮箱，所以上面的过程如下:

取名称索引检索javacode3500000对应的记录，取出3500000的id。

从主键索引中检索id=3500000的记录，并获取所有字段的值。

索引覆盖

查询中使用的索引树包含了查询需要的所有字段的值，不需要聚合索引来检索数据。这就是所谓的指数覆盖率。

让我们来看一个查询:

select id，name from test1 where name = ' javacode 3500000 '；

name对应idx1索引，id是主键，所以idx1索引叶子的子节点包含name和id的值。这个查询只需要将idx1作为索引。如果在select之后使用了*,则需要返回到表中一次，以获取sex和email的值。

所以，写sql的时候，尽量避免使用*。*可能会多一个回表操作，看能不能通过索引叠加实现效率更高。

索引下推

缩写为ICP，索引条件下推(ICP)是MySQL 5.6中的新特性，是在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表和MySQL服务器访问存储引擎的次数。

例如:

我们需要查询姓名以javacode35开头，性别为1的记录的数量。sql如下所示:

MySQL > select count(id)from test1a，其中name like 'javacode35% '，sex = 1； - | count(id)| - | 55556 | - 集合中的1行(0.19秒)

流程:

用javacode35按名称索引检索第一条记录，并获取记录id。

此记录R1是通过使用id从主键索引中找到的。

判断R1的性别是否为1，然后重复上述操作，直到找到所有记录。

在上面的过程中，需要取名字索引，返回表。

如果采用ICP，我们可以这样做，创建一个(姓名，性别)的组合索引。查询过程如下:

取(name，sex)索引用javacode35检索第一条记录，可以得到(name，sex，id)，记录为R1。

判断R1.sex是否为1，然后重复上述操作，直到找到所有记录。

这个过程不需要返回表，整个条件可以通过索引的数据进行筛选，比上面的更快。

数字使字符串类索引无效。

mysql> insert into test1 (id，name，sex，email)值(4000001，' 1 '，1，' javacode 2018 @ 163 . com ')； 查询正常，1行受影响(0.00秒) MySQL > select * from test1 where name = ' 1 '； --- | id | name | sex | email | --4000001 | 1 | 1 | javacode2018@163.com | --集合中的1行(0.00秒) MySQL > select * from test1 where name = 1； -- | id | name | sex | email | -- | 4000001 | 1 | 1 | javacode2018@163.com | - 集合中的1行，65535个警告(3.30秒)

对于上面的三条sql，我们插入了一条记录。

第二个查询很快，第三个查询用name和1比较。名字有索引，名字是字符串类型。用数字比较字符串时，字符串会被强制转换成数字再进行比较，所以第二次查询就变成了全表扫描，只能取出每一条数据，名字会被转换成数字再和1进行比较。

比较数值型字段和字符串有什么影响？如下所示:

MySQL > select * from test1 where id = ' 4000000 '； -- | id | name | sex | email | -- | 4000000 | javacode 4000000 | 2 | javacode4000000@163.com | - set中的1行(0.00秒) MySQL > select * from test1其中id = 4000000 -- | id | name | sex | email | -- | 4000000 | javacode 4000000 | 2 | javacode4000000@163.com | - 集合中的一行(0.00秒)

id上有一个主键索引，id的类型为int。如你所见，上面两个查询都非常快，可以正常使用索引快速检索，所以如果字段是数组类型，查询值是字符串还是数组都会被索引。

函数使索引无效。

mysql >从test1 a中选择a.name 1其中a.name = ' javacode1 - | a . name 1 | - | 1 | - 集合中的1行，1个警告(0.00秒) MySQL > select * from test1a where concat(a . name，' 1 ')= ' javacode 11 '； -- | id | name | sex | email | -- | 1 | javacode 1 | 1 | javacode1@163.com | - 集合中的一行(2.88秒)

名字上有索引，上面的查询，第一个取索引，第二个不取，第二个使用函数后，名字所属的索引树无法快速定位到需要查找数据的页面，只能将所有页面的记录加载到内存中，用函数计算每个数据后再进行条件判断。此时，索引是无效的，它变成了全表数据扫描。

结论:使用函数查询，索引字段无效。

运算符使索引无效。

mysql> select * from test1 a其中id = 2-1； --- | id | name | sex | email | --1 | javacode 1 | 1 | javacode1@163.com | --集合中的1行(0.00秒) MySQL > select * from test1a其中id 1 = 2； -- | id | name | sex | email | -- | 1 | javacode 1 | 1 | javacode1@163.com | - 集合中的一行(2.41秒)

id上有一个主键索引，上面的查询，第一个取索引，第二个不取索引，第二个用运算符。id所在的索引树无法快速定位要搜索的数据所在的页面，所以我们只能将所有页面的记录加载到内存中，然后计算每个数据的ID，再判断是否等于1。此时，索引是无效的，它变成了全表数据扫描。

结论:在索引字段中使用函数会使索引失效。

使用索引优化排序

我们有一个订单表T _ order (ID，User _ ID，addtime，Price)，经常会查询一个用户的订单，并按照AddTime升序排序。我们应该如何创建索引？我们来分析一下。

在user_id上创建索引。让我们分析一下这种情况和数据检索的过程:

走user_id索引，找到记录的的id通过id在主键索引中回表检索出整条数据重复上面的操作，获取所有目标记录在内存中对目标记录按照addtime进行排序

我们要知道，当数据量非常大的时候，排序还是很慢的，可能会用到磁盘上的文件。有没有一种方法可以很好的查询数据？

我们来复习一下mysql中B树数据的结构。记录是根据索引值排序的链表。如果把user_id和addtime放在一起组成一个联合索引(user_id，addtime)，通过user_id检索到的数据自然会按照addtime排序，效率更高。如果需要降序添加时间，只需翻转结果即可。

总结一些使用索引的建议。

在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引差不多联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整查询记录的时候，少使用*，尽量去利用索引覆盖，可以减少回表操作，提升效率有些查询可以采用联合索引，进而使用到索引下推（IPC），也可以减少回表操作，提升效率禁止对索引字段使用函数、运算符操作，会使索引失效字符串字段和数字比较的时候会使索引无效模糊查询'%值%'会使索引无效，变为全表扫描，但是'值%'这种可以有效利用索引排序中尽量使用到索引字段，这样可以减少排序，提升查询效率,