MySQL性能优化-Mysql索引篇（2）

索引底层数据结构

关于索引使用到的底层数据结构，我们这里不会具体去说，因为
这是一块非常固定的内容，大家可以下去后具体了解下，主要包括这几种，平衡二叉搜索树、B树、B+树、Hash结构。我们这里主要是对这几种数据结构，进行一波对比分析。（后续会对B+树的内部结构详细分析，小伙伴们可以期待一波）

一、 平衡二叉搜索树和B树：
我们知道，我们使用索引是为了在大量数据时，提升我们搜索的效率。所以我们可以说，使用了索引，说明了数据的量级非常大的，一般都是到达了百万级别。虽然平衡二叉搜索树在二叉树的基础上，平衡了节点的分布，避免了一般二叉树在极端情况下的链表化现象。但是还是有非常致命的一点，那就是一个父点最多有两个，这会导致平衡二叉搜索树在大数据量的情况下树高非常高。

举例说明：
如果是一百万数据，那么它的树高就会达到差不多20层。我们讨论最坏情况，如果节点对应的数据页面不在内存中的话，那么就要进行20次磁盘IO，一次磁盘IO的时间将近10ms，也就是说一次查询就要将近200ms的时间。也就是说，使用了索引一条普通的查询仍会被判定为慢查询。这还仅仅是一百万的数据，如果数据量达到一千万呢？那查询时间就会达到秒级别了，这对于用户体验的影响可以说是毁灭性的。

二、 B树和B+树
B树和B+树最大的区别就是数据存在的位置，B树是每一个节点，无论是叶子还是非叶子节点，都会存储数据。而B+树的非叶子节点只会存储指向下一层节点的指针，叶子节点才会存储数据。那么B+树这么做，相比于B树有什么好处呢？

B+ 树查询效率更稳定：因为 B+ 树每次只有访问到叶子节点才能找到对应的数据，而在 B 树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
B+ 树的查询效率更高：这是因为通常 B+ 树比 B 树更矮胖（阶数更大，深度更低），查询所需要的磁盘 I/O 也会更少。同样的磁盘页大小，B+ 树可以存储更多的节点关键字。

不仅是对单个关键字的查询上，在查询范围上，B+ 树的效率也比 B 树高。这是因为所有关键字都出现在 B+ 树的叶子节点中，并通过有序链表进行了链接。而在 B 树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

三、Hash与B+树的区别

Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。同理，我们也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用。

对于等值查询来说，通常 Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

如何通过索引将SQL查询效率最大化？

什么时候应该创建索引？

建索引有一定的规律。当这些规律出现的时候，我们就可以通过创建索引提升查询效率，下面我们来看看什么情况下可以创建索引：

字段的数值有唯一性的限制，比如用户名

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。

频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下

在数据量大的情况下，某个字段在 SQL 查询的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。

需要经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。
举例说明：
执行SQL如下：

SELECT user_id, count(*) as num FROM product_comment group by user_id order by comment_time desc limit 100

实际上多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。在这个例子中，我们创建联合索引 (user_id, comment_time)，再来看下查询的时间，查询时间为 0.775s，效率提升了很多。如果我们创建联合索引的顺序为 (comment_time, user_id) 呢？运行时间为 1.990s，同样比两个单列索引要快，但是会比顺序为 (user_id, comment_time) 的索引要慢一些。这是因为在进行 SELECT 查询的时候，先进行 GROUP BY，再对数据进行 ORDER BY 的操作，所以按照这个联合索引的顺序效率是最高的。

UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

DISTINCT 字段需要创建索引

这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

做多表 JOIN 连接操作时，创建索引需要注意以下的原则

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 user_id 在 product_comment 表和 user 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

什么时候不需要用索引？

如果索引进行了表达式计算，则会失效

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001

如果对索引使用函数，也会造成失效

SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text = '462eed7ac6e791292a79'

SQL 语句，comment_id 是主键，而 comment_text 没有进行索引，因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效：

当我们使用 LIKE 进行模糊查询的时候，前面不能是 %

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc'

索引列尽量设置为 NOT NULL 约束。

MySQL 官方文档建议我们尽量将数据表的字段设置为 NOT NULL 约束，这样做的好处是可以更好地使用索引，节省空间，甚至加速 SQL 的运行。
判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为 NOT NULL 约束比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 (‘’)。

我们在使用联合索引的时候要注意最左原则

最左原则也就是需要从左到右的使用索引中的字段，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。我在讲联合索引的时候举过索引失效的例子。

总结