MySQL数据库（三）- 索引

目录

索引种类

三星索引

B树、B+树索引

      B树索引​

      B+树索引​

Hash索引

索引使用原则

      适合使用索引的情况

      创建索引需要注意以下的原则        

      不适用索引的情况

      索引失效的情况

---

索引种类

        1，功能逻辑分类：普通索引、唯一索引、主键索引、全文索引。

        普通索引：基础索引，没有任何约束，主要用于提高查询效率。

        唯一索引：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。

        主键索引：在唯一索引的基础上增加了不为空的约束，一张表里最多只有一个主键索引。

        全文索引：MySQL自带的全文索引只支持英文。一般采用专门的全文搜索引擎，例如：ES和

Solr。

        

        2，物理实现方式分类：聚集索引、非聚集索引（又称二级索引或者辅助索引）。

        聚集索引

        聚集索引可以按照主键来排序存储数据：数据表包含聚集索引时，表内的数据行会按照索引

列的值在磁盘上进行物理排序和存储。

        每一个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

        非聚集索引

        数据库系统会有单独的存储空间存放非聚集索引，这些索引项是按照顺序存储的，但索引项

指向的内容是随机存储的。

        系统会进行两次查找，第一次先找到索引，第二次找到索引对应的位置取出数据行。

        非聚集索引不会把索引指向的内容像聚集索引一样直接放到索引的后面，而是维护单独的索

引表（只维护索引，不维护索引指向的数据），为数据检索提供方便。

        

        聚集索引、非聚集索引的区别：

        聚集索引的叶子节点存储的是完整的数据记录，非聚集索引的叶子节点存储的是数据位置(主

键值或数据地址)。

        非聚集索引不会影响数据表的物理存储顺序。

        一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，

也就是多个索引目录提供数据检索。

        使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效

率会比非聚集索引低。

        例子：

        MyISAM索引 - 非聚簇索引

        主键索引，普通索引：B+树索引；

        主键索引，普通索引：非聚簇索引；

        叶子节点的data域存储数据记录的地址；

        查询数据时，首先找到data域中的地址，然后再根据地址去磁盘中读数据；

        

        InnoDB的索引

        主键索引，普通索引：B+树索引；

        主键索引：聚簇索引；

        普通索引：非聚簇索引；

        每个表只能有一个聚簇索引；

        主键索引查询数据，只需根据主键值拿到叶子节点中data域的数据即可。

        普通索引查询数据，首先找到叶子节点data域中的主键值，然后再去主键索引中根据主键值

去查数据。

        3，按照字段个数分类：单一索引、联合索引。

        索引列为一列时为单一索引；多个列组合在一起创建的索引叫做联合索引。

        创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (x, y, z) 和 (z, y, x) 在使用

的时候效率可能会存在差别。

        联合索引存在最左匹配原则，按照最左优先的方式进行索引的匹配。比如刚才举例的 (x, y,

z)，如果查询条件是 WHERE x=1 AND y=2 AND z=3，就可以匹配上联合索引；如果查询条件是

WHERE y=2，就无法匹配上联合索引。

        

        根据索引片中包含的匹配列的数量不同，将索引分成窄索引和宽索引。

        窄索引（比如包含索引列数为 1 或 2）和宽索引（包含的索引列数大于 2）。

        每个非聚集索引保存的数据都会存储主键值，然后通过主键值，来回表查找相应的数据；宽

索引可以避免回表；

三星索引

        在WHERE条件语句中，找到所有条件列，将它们作为索引片中的开始列；

        将 GROUP BY 和 ORDER BY 中的列加入到索引中；

        将 SELECT 字段中剩余的列加入到索引片中。

        

        优点：

        采用三星索引会让索引片变宽，把能添加的相关列都加入其中。对于一条 SQL 查询来说，这

样做的效率是最高的；三星索引会让索引变宽，好处就是不需要进行回表查询，减少了磁盘 I/O 的

次数。

        缺点：

        三星索引会让索引片变宽，这样每个页能够存储的索引数据就会变少，从而增加了页加载的数量。

        如果数据量很大，过多索引所需要的磁盘空间可能会成为一个问题，对缓冲池所需空间的压

力也会增加。

        增加了索引维护的成本。如果我们为所有的查询语句都设计理想的三星索引，就会让数据表

中的索引个数过多，这样索引维护的成本也会增加。

        

        三星索引是个理想的方式，但实际运行起来我们要考虑更多维护的成本，在索引效率和索引

维护之间进行权衡。      

     

B树、B+树索引

      B树索引

         平衡多路搜索树；

        使用二叉树作为索引的实现结构，会让树变得很高，增加硬盘的 I/O 次数；把二叉树改成M叉

树，降低树的高度，减少磁盘的 I/O 次数；

        B树作为平衡的多路搜索树，它的每一个节点最多可以包括M个子节点，M 称为 B 树的阶。

      

      B+树索引

        B+树基于B树做出了改进，主流的DBMS都支持B+树的索引方式；

        B+ 树、 B树的差异：

        B+ 树和 B 树有个根本的差异在于，B+ 树的中间节点并不直接存储数据。

        B+树非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。

        B树非叶子节点既保存索引，也保存数据记录。所有关键字都在叶子节点出现，叶子节点构成

一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

        非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。

        B+树的优势：

        1，B+树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树

中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况。

        2，B+树的查询效率更高，这是因为通常B+树比B树更矮胖（阶数更大，深度更低），查询所

需要的磁盘 I/O 也会更少。同样的磁盘页大小，B+ 树可以存储更多的节点关键字。

        3，不仅是对单个关键字的查询上，在查询范围上，B+ 树的效率也比 B 树高。这是因为所有

关键字都出现在 B+ 树的叶子节点中，并通过有序链表进行了链接。而在 B 树中则需要通过中序遍

历才能完成查询范围的查找，效率要低很多。

Hash索引

        Hash散列函数只需要一步就可以找到对应的取值，算法复杂度为 O(1)，数组检索数据的算法

复杂度为 O(n)。从效率来说 Hash 比 B+ 树更快。

        键值 key 通过 Hash 映射找到桶 bucket。

        桶（bucket）指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存

指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到 Hash 冲突时，会在桶中进行

键值的查找。

        如果桶的空间小于输入的空间，不同的输入可能会映射到同一个桶中，这时就会产生 Hash 冲

突，如果 Hash 冲突的量很大，就会影响读取的性能。

    

        Hash索引和B+树索引的区别：

        1，Hash 索引不能进行范围查询，而 B+ 树可以。

        Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。

        2，Hash索引不支持联合索引的最左侧原则，即联合索引的部分索引无法使用，而 B+ 树可

以。

        对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算Hash值，

所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索

引无法被利用。

        3，Hash 索引不支持 ORDER BY 排序

         Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序

的，可以起到对该字段 ORDER BY 排序优化的作用。

        4，无法用 Hash 索引进行模糊查询

        而B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以

起到优化作用。

        Hash索引的适用场景

        当字段的重复度低，而且经常需要进行等值查询的时候，采用Hash索引是个不错的选择。

        遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所

以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

        MySQL 的 InnoDB 存储引擎还有个“自适应 Hash 索引”的功能，就是当某个索引值使用非常

频繁的时候，它会在 B+ 树索引的基础上再创建一个 Hash 索引，这样让 B+ 树也具备了 Hash 索

引的优点。

索引使用原则

      适合使用索引的情况

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

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

        3，需要经常 GROUP BY 和 ORDER BY 的列；

        多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为

索引），所以在多条件联合查询的时候最好创建联合索引。

        SELECT 查询的时候，先进行 GROUP BY，再对数据进行 ORDER BY 的操作，所以按照这

个联合索引的顺序效率是最高的。

         4，UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引需要注意平衡，如果索引

太多了，在更新数据的时候，如果涉及到索引更新，就会造成负担；

        5，DISTINCT 字段需要创建索引；

        6，做多表 JOIN 连接操作时；

      创建索引需要注意以下的原则        

        索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空

间。过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间，影响评估的效

率。

        多张数据表进行连接的时候，顺序很重要，因为不同的连接路径查询的效率不同，搜索空间

也会不同。小表驱动大表，如果不太有把握让谁做驱动表，就不要指定谁left/right join 谁了，让

MySQL优化器运行时决定。

        需要新建索引的时候，首先考虑在原有的索引片上增加索引，也就是采用复合索引的方式，

而不是新建一个新的索引。

        定期检查索引的使用情况，对于很少使用到的索引可以及时删除，从而减少索引数量。

        尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引；前缀索引：对文本的前

几个字符建立索引，字符数量在建立索引时指定。

        对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

        连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环。

      不适用索引的情况

        1，WHERE条件（包括 GROUP BY、ORDER BY）里用不到的字段；

        2，如果表记录太少，比如少于 1000 个；

        3，字段中如果有大量重复数据，比如性别字段。

      索引失效的情况

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

        2，如果对索引使用函数，会失效；

        3，在 WHERE 子句中，如果在OR前的条件列进行了索引，在OR后的条件列没有进行索引，

那么索引会失效。

        OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要

有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

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

        5，索引列尽量设置为 NOT NULL 约束；

        索引不会存储null；

        尽量将数据表的字段设置为 NOT NULL 约束，这样做的好处是可以更好地使用索引；

        判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就

将字段设置为 NOT NULL 约束，可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值

设置为空字符串。

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

        SQL语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。