MySQL 索引原理

1.0 MySQL 索引

1.1 索引的优点

优点：极大的提高了查询效率

没有索引的数据表就是一个无序的数据行的集合，如果想在无索引的数据表中查找数据，就需要一条一条的检查每一个数据行，直到找到匹配的结果。如果此时数据表中有 10000 条数据，那么平均也得查找 5000 次，才能找到要找的数据，这样一来，效率就太低了。

而使用索引的目的，就是为了提高查询效率。

索引值是经过分类的，索引中包含了数据表里的每一个数据行的项。那么索引为什么能提高查询效率呢？ —– （1）可以得知匹配数据行在什么位置结束，从而跳过其余部分；（2）因为使用了定位算法，它们可以不用从索引开始位置经过线性扫描就能直接找到第一个匹配项（例如，二进制搜索就比扫描快许多）

不同数据库引擎的索引实现细节：
InnoDB —– 数据表的数据和索引存在同一个表空间文件
InnoDB 存储引擎使用的是一个表空间，在这个表空间里，它管理着所有的 InnoDB 类型数据表的数据和索引的存储。即使通过配置让 InnoDB 为每个数据表分别创建一个它自己的表空间，一个给定的数据表的数据和索引也是保存在同一个表空间文件里。
MyISAM —– 数据表的数据行是在数据文件里，而索引值是在索引文件里
一个数据表可以有多个索引，所有的索引都存储在同一个索引文件里，索引文件里的每一个索引都是由分类的关键记录数组组成的，这些数组用于快速访问数据文件。

1.2 索引的缺点

1）索引加快了检索速度，但是降低了在带索引的数据列里插入、删除以及修改数值的速度。（即索引降低了涉及写入的操作的速度）

原因：写入一条数据行，不仅要求写入到数据行，还要求所有的索引都要随之做出改变，一个表有越多的索引，需要做出的改变就越多，平均性能就会下降越多。

2）索引要占据磁盘空间。

1.3 如何确定要索引的数据列

1、为用来搜索（WHERE）、分类或者分组（GROUP BY）的数据列编制索引，不要为输出显示（SELECT）的数据列编制索引
最适合索引的数据列是那些在 WHERE 子句中出现的数据列、在联结子句中给出的数据列，或者是在 ORDER BY 或 GROUP BY 子句中出现的数据列。根据 SELECT 关键字仅出现在输出数据列清单里的数据列最好不要有索引。

2、考虑各数据列的维度
什么是维度？ —– 数据列的维度等于它所容纳的非重复值得个数。比如，某个数据列里的值是（1，2 ， 2， 3 ，3 ，4），它的维度就是 4 。维度越高，重复值越少，索引的效果也越好。

3、尽量选用比较不占内存的数据类型
短小的值可以加快索引查找速度、减少磁盘 I/O 、让MySQL 在内存里同时容纳更多的键，从而加大在不需要从磁盘读取更多索引块的前提下在内存里找到键值的概率。

4、为字符串值得前缀编索引
假设有一个 CHAR（300）的数据列，而且大多数值的前 10 或 20 个字符都是唯一的，那么就不用为整个数据列编制索引，而只需要为前面的 20 或者 30 个字符编制索引就行了，这样可以降低索引占据的内存空间，而且会使得查询更快。

5、充分利用最左边的前缀
创建一个 n 个数据列的复合索引，实际上就创建了 n 个索引。一个复合索引在工作时，就相当于 n 个索引，因为索引中最左边的数据列集合能够用于匹配数据行，这样的一个集合就被称为“最左边的前缀”。

MySQL 不能使用没有包含最左边前缀的搜索的索引。
假设有个数据表，数据表有复合索引，数据列名称是 state（州）、city（城市）、code（邮编）。索引中的数据行是以 州/城市/邮编 的顺序存储的，因此它们自动以 州/城市 的顺序，也以 州 的顺序分类。所以，索引可以用来搜索一下的数据列的组合：

state, city, code
state, city
state

如果使用 state, code ，这时，尽管 MySQL 可以使用索引来找到那些与这个州匹配的数据行从而缩小搜索范围，但是这个索引不能用于值的组合。

6、利用“慢查询”日志找出性能低劣的查询
“慢查询”日志是一个文本文件，如果在这个日志里经常出现某个查询命令，就意味着它的代码可能不够优化。所以这个日志可以帮助我们找出可能受益于使用索引的查询命令。

7、索引的类型要与打算进行比较操作的类型保持匹配

索引有哪些类型（从数据结构角度分）：
1）B+树索引
2）hash 索引
3）FULLTEXT 索引
4）R-Tree 索引

InnoDB 总是使用 B树索引；MyISAM 也是用 B树索引，但在遇到空间数据类型时会改用 R树索引。

1.4 MySQL 使用索引的方式

1）在查询操作中把与 WHERE 子句所给出的条件相匹配的数据行尽快找出来；在关联操作中把与其他数据表里的数据行相匹配的数据行尽快找出来
2）对于使用 MAX() 和 MIN() 函数的查询，如果数据列带有索引，那么最大值和最小值可以快速找到，而不用逐行检查来查找。
3）MySQL 使用索引来迅速完成 ORDER BY 和 GROUP BY 子句的分类和分组操作

1.5 索引原理

数据库的数据是存储在磁盘上的，为了提高效率，会把部分数据缓存在内存中。而访问磁盘的代价约是访问内存代价的 10000 倍，所以，降低访问磁盘的次数，是索引所采用的数据结构的目的。那么就需要一个高度可控的多路搜索树，就这样 B+ 树应运而生。

B+ 树

B-树索引：
B-Tree索引是最常用的一种索引，如果没有指定特定的类型，那么多半就是B-Tree索引，事实上，很多搜索引擎使用的是它的变种 B+树，这是对B-树的一个优化。
B+树索引：
B+ 树在节点访问时间远远超过节点内部访问时间的时候，通常在多数节点在次级存储比如硬盘中的时候出现。通过最大化在每个内部节点内的子节点的数目来减少树的高度，从而减少磁盘 I/O 次数

B+树性质：
1）IO次数取决于B+数的高度 h。假设 N(数据表的数据），m(每个磁盘块的数据项的数量)。

m = 磁盘块的大小(大小是固定的) / 数据项(即索引字段)的大小
结论1： m 越大，h 越小，则 I/O 次数越少
>结论2：数据项（即索引字段）越小（则 m 越大），I/O次数越少。这就是为什么每个索引字段越小，可以减少 磁盘 I/O 的根本原因。
结论3：数据项越少，那么数据项的数目就越多，所以 B+ 树要求把数据项都放到叶子节点。

2）当 B+ 树的数据项是复合的数据结构，比如(name,age,sex)的时候，B+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，B+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，B+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，B+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性

参考资料：

MySQL技术内幕（第4版）
美团点评技术团队： https://tech.meituan.com/mysql-index.html

欢迎访问我的网站：https://paynewoo.github.io/