[.com原稿]腾讯云数据库负责人林晓斌说：“我们面试MySQL同事的时候，只考察两点，索引和锁。“图片来自Pexels简洁明了，MySQL索引的重要性不言而喻。MySQL索引经历了多个版本的迭代，从语法到底层数据结构都发生了很多变化。MySQL索引，我们真的了解吗？好了，今天我们就来了解一下MySQL索引的前世今生，说说关于索引的那些事。什么是索引？在关系数据库中，索引是一种单独的物理存储结构，用于对数据库表中一个或多个列的值进行排序。它是表中一个或多个列中的值的集合，以及指向表中物理标识这些值的数据页的逻辑指针的对应列表。索引的作用相当于书的目录，根据目录中的页码可以快速找到需要的内容。当表中有大量记录时，如果要查询表：第一种查找信息的方式是全表查找，即将所有记录一条一条取出来，与查询条件进行比较一条一条，然后返回满足条件的记录，这样会消耗大量的数据库系统时间，造成大量的磁盘I/O操作。第二种是在表中创建索引，然后在索引中找到满足查询条件的索引值，最后通过索引中存储的ROWID（相当于页码）快速找到表中对应的记录。MySQL5.5之后InnoDB存储引擎使用的索引数据结构主要使用：B+Tree；本文将带大家聊一聊B+Tree的前世今生。Mark：B+Tree可以使用不以通配符开头的<、<=、=、>、>=、BETWEEN、IN、LIKE索引。（MySQL5.5之后）这些事实可能会颠覆你的一些认知，比如在你读过的其他文章或书籍中。以上都是“范围查询”，不使用索引！是的，在5.5之前，优化器不会选择通过索引进行搜索。因为要回表再查，可能涉及到I/O的行比较多，会被优化器抛弃。算法（B+Tree）优化后，支持部分范围类型的扫描（deli和B+Tree数据结构的有序性）。这种做法也违反了最左前缀原则，导致范围查询后的情况不能使用联合索引，这个我们后面会详细说明。索引的优缺点索引的优点如下：索引大大减少了服务器需要扫描的数据量。索引帮助服务器避免排序和临时表。索引可以将随机I/O变成顺序I/O。索引的缺点如下：虽然索引大大提高了查询速度，但是会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE等操作。因为在更新表的时候，MySQL不仅保存了数据，还保存了索引文件。索引占用磁盘空间的索引文件。一般来说，这个问题并不严重，但是如果在一个大表上创建多个复合索引，插入大量数据，索引文件的大??小也会迅速膨胀。如果一个数据列包含很多重复项，对其建立索引就不太实用。对于非常小的表，在大多数情况下，简单的全表扫描效率更高。因此，应该只对查询最频繁和排序最频繁的数据列建立索引。（MySQL中同一个数据表的索引总数限制为16个）数据库存在的意义之一就是解决数据存储和快速查找。那么数据库中的数据存在于何处呢？是的，它是磁盘。磁盘的优点是什么？很便宜！缺点是什么？与内存访问速度相比，它更慢。那么你知道MySQL索引使用的主要数据结构吗？B+树！你脱口而出。B+树是一种什么样的数据结构？MySQL索引为什么选择B+树？其实B+树的最终选择经历了漫长的演变：二叉排序树→二叉平衡树→B-Tree（B树）→B+Tree（B+树）有朋友问我“B和B有什么区别-树和B树”？我在这里普及一下。MySQL的数据结构只有B-Tree（B树）和B+Tree（B+树）。只是发音不同罢了。“B-Tree”一般简称为B-tree，也可以叫B-tree！还有一个朋友提到的红黑树，是一种编程语言中的存储结构，不是MySQL；Java的HashMap是链表加红黑树。好吧，今天就带大家看一下进化成B+树的过程。B+Tree索引的前世今生①二叉排序树在了解B+树之前，先简单说一下二叉排序树。对于一个结点来说，它的左子树的子结点值要小于它自己，而它的右子树的子结点的值一定要大于它自己。如果所有节点都满足这个条件，那么它就是一棵二叉排序树。（这里可以把二分查找的知识点串起来）上图是二叉排序树。大家可以尝试利用它的特性来体验一下找9的过程：9小于10，去它的左子树（结点3）查找。9大于3，去节点3的右子树（节点4）找。9大于4，去节点4的右子树（节点9）找。节点9等于9，查找成功。总共进行了4次比较。大家有没有想过上面结构的优化方法呢？②AVL树（自平衡二叉搜索树）上图是一棵AVL树，节点的个数和取值与二叉排序树完全一样。再来看找9的过程：9大于4，去它的右子树找。9小于10，去它的左子树找。节点9等于9，查找成功。一共比较了3次，同样的数据量比二叉排序树少了一个，为什么呢？因为AVL树的高度小于二叉排序树的高度，所以高度越高代表比较的次数越多；别小看optimized这一次，如果是200w条数据，对比次数会有明显的不同。你可以想象一棵有100万个节点、高度为20的平衡二叉树。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘中随机读取一块数据需要大约10ms的寻址时间。也就是说，对于一个100万行的表，如果用二叉树来存储，访问单行可能需要20次10ms。这个查询真的很慢！③B-tree（BalancedTree）多路平衡搜索树，多叉B-tree是一种多路自平衡搜索树，类似于普通的二叉树，但是B-tree允许每个节点有更多的child节点。B-tree的示意图如下：B-tree的特点是：所有的key值都分布在整棵树中。任何关键字出现在一个且仅一个节点中。搜索有可能在非叶节点处结束。在完整的关键字集中进行搜索，性能接近于二分查找算法。要提高效率，请尽量减少磁盘I/O的数量。在实际过程中，并不是每次都严格按需读取磁盘，而是每次都提前读取。磁盘读取完需要的数据后，会按顺序再读一些数据到内存中。其理论依据是计算机科学中提到的局部性原理：由于磁盘顺序读取的效率非常高（不需要寻址时间，需要的自旋时间非常少），所以对于具有局部性的程序，预读可以提高I/O效率。预读的长度一般是一页的整数倍。MySQL（默认使用InnoDB引擎）以页为单位管理记录，默认每页大小为16K（可以修改）。B-Tree采用计算机磁盘预读机制：每创建一个新节点，就申请一个页面空间，因此每次查找节点只需要一次I/O；因为在实际应用中，节点深度会很小，所以搜索效率很高。那么最终版本的B+树是如何制作出来的呢？④B+树（B+树是B树的变种，也是一种多路搜索树）。从图中也可以看出，B+树和B树的区别在于：所有的关键字都存储在叶子节点中，非叶子节点不存储真实的数据，因此可以快速定位到叶子节点。所有的叶子节点都加了一个链指针，也就是说所有的值都是按顺序存储的，而且每个叶子页到根的距离都是一样的，非常适合找范围数据。因此，B+Tree可以使用不以通配符开头的<、<=、=、>、>=、BETWEEN、IN、LIKE等索引。B+树的优点是比较次数均衡，I/O次数减少，搜索速度提高，搜索更稳定：B+树的磁盘读写开销更低。B+树的查询效率更稳定。你需要知道的是，你每创建一张表，系统都会自动为你创建一个基于ID的聚簇索引（上面提到的B+树）来存储所有的数据。你每增加一个索引，数据库都会为你创建一个额外的索引（上面提到的B+树）。索引选择的字段个数就是每个节点存储的数据索引个数。请注意，索引并不存储所有数据。为什么MySQL索引选择B+树而不是B树？原因有二：B+树更适合外存（一般指磁盘存储），因为内部节点（非叶子节点）不存储数据，所以一个节点可以存储更多的内节点，每个节点可以索引更大的和更精确的范围。也就是说，使用B+树的单次磁盘I/O的信息量比B树大，I/O效率更高。MySQL是一个关系数据库。它经常根据范围访问索引列。B+树的叶子节点建立链式指针，以增强范围的可访问性。因此，B+树对于索引列上的range范围查询是非常友好的。但是B-tree的每个节点的key和data都在一起，无法进行范围搜索。程序员，你应该知道的索引知识点①回表查询比如你创建了name和age索引name_age_index，你在查询数据的时候使用了：select*fromtablewherename='ChenHaha'andage=26;因为附加索引中只有name和age，所以命中索引后，数据库必须回到聚簇索引中去寻找其他数据。②索引覆盖率和表返回的结合会更容易理解。比如上面的name_age_index索引有一个query：selectname,agefromtablewherename='ChenHaha'andage=26;此时可以在索引name_age_index中获取select字段name和age，所以不需要回表，满足索引覆盖，直接返回索引中的数据，效率高。是DBA同学优化的首选优化方法。③最左前缀原则B+树的节点存储索引顺序是从左到右存储的，匹配的时候自然是从左到右匹配。通常我们在建联合索引的时候，也就是在多个字段上建索引，相信建过索引的同学会发现，Oracle和MySQL都会让我们选择索引的顺序。比如我们要对a,b,c三个字段建立联合索引，我们可以选择我们想要的优先级，a,b,c，或者b,a,c或者c,a,b，ETC。。为什么数据库让我们选择字段的顺序呢？不是三个字段的联合索引吗？这就引出了数据库索引最左前缀的原则。在我们的开发中，经常会遇到这样的问题，已经为这个字段建立了联合索引，但是SQL查询这个字段的时候不会使用索引。比如索引abc_index:(a,b,c)就是a,b,c三个字段的联合索引。以下SQL在执行时无法命中索引abc_index。选择*fromtablewherec='1';选择*fromtablewhereb='1'andc='2';以下三种情况都会去索引：select*fromtablewherea='1';select*fromtablewherea='1'andb='2';select*fromtablewherea='1'andb='2'andc='3';从以上两个例子，你是不是有所了解呢？是的，索引abc_index：(a,b,c)只会在(a),(a,b),(a,b,c)三种类型的查询中使用。其实这里说的有点模棱两可。其实(a,c)也会去，只是会用到a字段索引，不会用到c字段。另外有一个特例说明一下，下面的类型只会有a和b去索引，c不会去。select*fromtablewherea='1'andb>'2'andc='3';像上面这种sql语句，a和b走完索引后，c已经乱序了，所以c不能走索引，优化器会认为它没有全表扫描的速度快c场。最左前缀：顾名思义，就是最左在前。在上面的例子中，我们创建了a_b_c多列索引，相当于创建了(a)单列索引、(a,b)复合索引和(a,b,c)复合索引。因此，在创建多列索引时，根据业务需要，将where子句中使用频率最高的列放在最左边。④索引下推优化仍然是索引name_age_index，有如下sql：select*fromtablewherenamelike'Chen%'andage>26；这条语句有两种执行可能：命中name_age_index联合索引，查询所有name以“Chen”开头的数据，然后回表查询所有满足的行。打name_age_index联合索引，查询所有name以“Chen”开头的数据，然后顺便筛选出age>20的索引，然后回表查询整行数据。很明显，第二种方式返回表查询的行数更少，I/O次数也会减少，这就是索引下推。所以并不是所有的喜欢都会达到索引。使用索引的注意事项①索引不会包含空值的列。只要列包含空值，它们就不会包含在索引中。只要复合索引中有一列包含空值，那么这一列就不起作用了。所以我们建议在设计数据库的时候不要让字段的默认值为null。②使用短索引对串行列进行索引，如果可能的话，指定一个前缀长度。例如，如果您有一个char(255)列，如果大多数值在前10或20个字符内是唯一的，则不要索引整个列。短索引不仅可以提高查询速度，还可以节省磁盘空间和I/O操作。③索引列排序查询只使用一个索引，所以如果where子句中已经使用了该索引，那么orderby中的列将不会使用该索引。因此，如果数据库默认的排序可以满足要求，就不要使用排序操作；尽量不要包含多列的排序，如果有必要，最好为这些列创建复合索引。④like语句操作一般不建议使用like操作。如果一定要用，怎么用也是个问题。like"%陈%"不会使用索引，like"陈%"可以使用索引。⑤不要对列进行操作，会导致索引失效，进行全表扫描，例如：SELECT*FROMtable_nameWHEREYEAR(column_name)<2017;⑥不要在和<>操作中使用not。这不是一个受支持的范围查询条件，不会被用作索引。我的经历曾经，我以为我很了解MySQL。刚进公司的时候，我还是个孩子。记得第一个需求是做一个统计接口，查询近两个小时的网站访问量，每隔5分钟查询一次。JSONArray总共返回了24个值。我写了一个接口循环二十四次，发了24条SQL去查（捂脸）。因为那个接口，技术经理嘲笑他写的SQL比我吃的还多。虽然我们山东人基本不吃米饭，但我还是很惭愧。然后manager调用一个dateTime函数分组查询处理，就OK了。效率是我的几十倍。从那时起，我就定下了一个目标，要深入学习MySQL，万一哪天有机会回去呢？小伙伴们，MySQL任重道远，任重而道远。心胸不要太高，一起努力吧，希望这篇文章能帮到你。作者：陈哈哈简介：MySQL社区非知名贡献者，擅长嫖知识；陪伴MySQL五年，致力于高性能SQL和事务锁优化的研究；路漫漫其修远兮，希望通过我的分享，让大家少踩一些坑。我是陈哈哈，一个爱笑的程序员。编辑：陶佳龙征稿：如有意向投稿或寻求报道，请联系editor@51cto.com【原创稿件请注明原作者和出处为.com，合作网站转载】
