MySQL索引原理及优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL索引原理及优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13830字，纯文字阅读大概需要20分钟。

内容图文

　　这一小节结合哈希表、完全平衡二叉树、B树以及B+树的优缺点来介绍为什么选择B+树。

　　假如有这么一张表(表名：sanguo)：

（1）Hash索引

　　对name字段建立哈希索引：

　　根据name字段值进行hash计算，定位到数组的下标，因为字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。其中每一个节点存储的是name字段值及对应的行数据地址，那么对于这样一个索引结构，现在来执行下面的sql语句： 　

　　select * from sanguo where name = ‘周瑜‘;

　　可以直接对‘周瑜’按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据。 那么如果现在执行下面的sql语句：

　　select * from sanguo where name > ‘周瑜‘

　　则需要进行全表扫描，因为哈希表的特点就是可以快速的精确查询，但是不支持范围查询。

（2）完全平衡二叉树

　　针对上面的表数据用完全平衡二叉树表示如下图：

　　图中的每一个节点实际上应该有四部分：（1）左指针，指向左子树；（2）键值；（3）键值所对应的行数据的存储地址；（4）右指针，指向右子树

　　二叉树是有顺序的，即节点的左子树中的值要严格小于该节点的值，节点右子树中的值要严格大于该节点的值，如果查找‘周瑜’，需要找2次（第一次曹操，第二次周瑜），比哈希表要多一次。而且由于完全平衡二叉树是有序的，所以也是支持范围查找的。

（3）B树

　　还是上面的表数据用B树表示如下图：

　　可以发现同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。同时，B树种每一个节点均存储了索引值及对应的行数据的指针。

（4）B+树

　　还是上面的表数据用B+树表示如下图（为了简单，数据对应的地址就不画在图中了。）：

　　我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点只存储索引键值而不存储行数据地址等相关数据，非叶子节点会冗余一份在叶子节点中，所有的行数据或者行数据地址只存储在叶子节点中，并且叶子节点之间用指针相连。

二、为什么选择B+树？

　　索引也是很“大”的，因为索引也是存储元素的，我们的一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上索引也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的IO效率。　　

　　假如用完全平衡二叉树作为索引的数据结构，当我们需要查询“张飞”时，需要以下步骤

1. 从磁盘中取出“曹操”到内存，CPU从内存取出数据进行比较，“张飞”<“曹操”，取左子树（产生了一次磁盘IO）

2. 从磁盘中取出“周瑜”到内存，CPU从内存取出数据进行比较，“张飞”>“周瑜”，取右子树（产生了一次磁盘IO）

3. 从磁盘中取出“孙权”到内存，CPU从内存取出数据进行比较，“张飞”>“孙权”，取右子树（产生了一次磁盘IO）

4. 从磁盘中取出“张飞”到内存，CPU从内存取出数据进行比较，“张飞”=“张飞”，找到结果（产生了一次磁盘IO）

　　假如使用B树，只发送三次磁盘IO就可以找到“张飞”了，这就是B树的优点：一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了。

　　而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

三、一个B+树的节点中到底存多少个元素合适呢？

　　其实也可以换个角度来思考B+树中一个节点到底多大合适？

　　答案是：B+树中一个节点为一页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费；如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费；所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

　　那么，Mysql中B+树的一个节点大小为多大呢？

　　这个问题的答案是“1页”，这里说的“页”是Mysql自定义的单位（其实和操作系统类似），Mysql的Innodb引擎中一页的默认大小是16k（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页），可以使用命令SHOW GLOBAL STATUS like ‘Innodbpagesize‘; 查看。

　　并且还可以告诉你的是，一个节点为1页就够了。

　　为什么一个节点为1页（16k）就够了？解决这个问题，我们先来看一下Mysql中利用B+树的具体实现。

四、Mysql中MyISAM和innodb使用B+树

　　通常我们认为B+树的非叶子节点不存储数据，只有叶子节点才存储数据；而B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低，所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间会有指针相连，也方便进行范围查找。上图的data区域两个存储引擎会有不同。

1、MyISAM中的B+树

　　MYISAM中叶子节点的数据区域存储的是行数据记录的地址

　　MyISAM主键索引：

　　MyISAM辅助索引：

　　MyISAM存储引擎在使用索引查询数据时，会先根据索引查找到行数据地址，再根据地址查询到具体的数据。MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

　　因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

　　MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2、InnoDB中的B+树

　　InnoDB中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值。

　　主键索引

　　辅助索引：

　　Innodb中的主键索引和实际数据时绑定在一起的，叶节点包含了完整的数据记录，这种索引叫做聚集索引。也就是说Innodb的一个表一定要有主键索引，如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见）。另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高（少一次磁盘IO），并且比辅助索引也要高很多。所以，我们在使用Innodb作为存储引擎时，我们最好：

1. 手动建立主键索引

2. 尽量利用主键索引查询

　　在使用辅助索引查询时，会先定位到主键值，然后在根据主键索引查询：

　　知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调（非自增）的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

五、回到我们的问题：为什么一个节点为1页（16k）就够了？

　　对着上面Mysql中Innodb中对B+树的实际应用（主要看主键索引），可以发现B+树中的一个节点存储的内容是：

• • 非叶子节点：主键+指针

  • 叶子节点：数据

　　那么，假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级条）。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。所以也就回答了我们的问题，1页=16k这么设置是比较合适的，是适用大多数的企业的，当然这个值是可以修改的，所以也能根据业务的时间情况进行调整。

六、索引使用策略及优化

（1）最左前缀原理与相关优化

　　高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

　　这里先说一下联合索引（组合索引）的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列。另外，单列索引可以看成联合索引元素数为1的特例。

　　以employees.titles表为例，下面先查看其上都有哪些索引：

　　从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉：

　　ALTER TABLE employees.titles DROP INDEX emp_no;

　　这样就可以专心分析索引PRIMARY的行为了。

　　情况一：全列匹配。

　　很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：

　　效果是一样的。

　　情况二：最左前缀匹配。

　　当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

　　情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。

　　此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

首先我们看下title一共有几种不同的值：

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

这次key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key。看下两种查询的性能比较：

　　“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引。

　　情况四：查询条件没有指定索引第一列。

　　由于不是最左前缀，索引这样的查询显然用不到索引。

　　情况五：匹配某列的前缀字符串。

　　此时可以用到索引，但是如果通配符不是只出现在末尾，则无法使用索引。（原文表述有误，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀）

　　情况六：范围查询。

　　范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

　　可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

　　看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

　　情况七：查询条件中含有函数或表达式。

　　很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如：

　　虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，因为需要计算每一行数据的title列，而情况五中用LIKE则可以。再如：

　　显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工进行代数运算，转换为无表达式的查询语句。

七、索引选择性与前缀索引

　　既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

　　（1）第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

　　（2）另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

　　　　Index Selectivity = Cardinality / #T

　　显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

　　title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

　　有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

　　从图12可以看到employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

　　如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

　　<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

　　选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

　　这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引 建上：

　　此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

　　性能的提升是显著的，查询速度提高了120多倍。

　　前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

八、InnoDB的主键选择与插入优化

　　在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

　　上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

　　这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

　　如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

　　此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

　　因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

　参考：

　　　　MySQL常见的面试题＋索引原理分析！　　https://mp.weixin.qq.com/s/eG5KIp-mOMQCis_pDvCN_w

　　　　MySQL索引背后的数据结构及算法原理　　http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL索引原理及优化

标签：nod   因子   冗余   顺序   type   导致   sel   glob   位置   

本文系统来源：https://www.cnblogs.com/aiqiqi/p/10516697.html

内容总结

以上是互联网集市为您收集整理的MySQL索引原理及优化全部内容，希望文章能够帮你解决MySQL索引原理及优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端