MySQL整理笔记一】—— 索引篇

Solipo
132 阅读10分钟

前言

本文章通过整理索引的分类、结构以及常见的知识，帮助自己对MySQL进一步了解，如果有不对的地方，还请指出，十分感谢。

索引概述

索引是一种为了加快数据库系统查询速度的数据结构，MySQL中主要采用的是聚簇索引。

当查询语句中没有索引时，将会进行全表扫描。

使用索引的优缺点

从空间角度（数据存储）、时间角度（数据检索）、数据库复杂角度出发。

优点

  • 加快数据检索的速度，对分组和排序子句适用
  • 加速表和表之间的连接

缺点

  • 创建索引耗费时间和空间
  • 维护索引耗费时间
  • 动态维护索引增加难度。

索引分类

逻辑角度

  • 主键索引：具有强唯一性，不允许有空值。在 InnoDB 中默认使用聚簇索引结构。
  • 普通索引（辅助索引）：每个索引只支持单个列，在使用的时候支持 change buffer 机制。
  • 联合索引（覆盖索引）：多个列字段具有唯一性，遵循最左前缀原则。

最左前缀匹配原则是什么？

  • 依次比较，向右匹配，直至遇到范围查询(一个一个查？)
  • 如果有字段缺失，只能依次全部查找完，再进行匹配

物理存储角度

  • 聚簇索引（clustered index）
  • 非聚簇索引（non-clustered index）

聚簇索引怎么实现的？

在B+树中的叶子结点中，存储主键和信息，在页分裂和页合并的时候进行合并。
在InnoDB中，聚簇索引又叫一级索引，索引和数据存储在一起，存储在B+树的叶子结点中；
非聚簇索引又叫二级索引，叶子结点中只保存主键索引，这种模式的好处是在页分裂不用维护二级索引，减少索引维护工作。

索引结构

索引的实质是一种数据结构，在数据库系统中，通常有以下三种：hash、有序数组、B+树（N叉树）。


优点缺点
hash采用k-v结构，适用于等值查询大量等值键值对场景下，存在哈希碰撞问题；不支持范围查询，不支持最左前缀原则
有序数组适用于范围查询，采用二分法，加快查询速度动态插入数据较为麻烦，只适用于静态存储引擎
B+树查询、存储都比较快存储结构比较复杂，需要耗费成本

B+树

B-Tree：为磁盘等外存储设备设计的一种平衡查找树，B-Tree 采用二元组[key, data] 的结构数据，可以让系统高效的找到数据所在的磁盘块。
B+Tree：在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。本质上维护一个多叉平衡树，通过将叶子结点连接成链表支持索引下推。
!

为什么使用B+树结构，而不是B-Tree、二叉树等数据结构？ 索引的底层结构是什么？为什么采用B+树？ 为什么不用二叉树？红黑树？ 索引下推怎么实现的？
回答角度：磁盘预读 索引存储 读取方式


磁盘IO与预读
因为磁盘读的时间一般为9ms，远远超过在内存中读取数据的时间。所以计算机进行了预读的操作，每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。

因为局部预读原理说明：当访问一个地址数据的时候，与其相邻的数据很快也会被访问到。每次磁盘IO读取的数据我们称之为一页（page）。一页的大小与操作系统有关，一般为4k或者8k。这也就意味着读取一页内数据的时候，实际上发生了一次磁盘IO。

B-Tree和二叉查找树
两者的差距主要体现在磁盘IO的次数，磁盘页在树中以节点的形式体现。而在树的查找过程中，只能依次进行遍历，二叉树相较于N叉树，多了磁盘IO读的次数。所以不适用二叉查找树。

B+树和B-Tree

索引的存储
一般而言，索引本身大，不可能全部储存在内存中，索引往往以索引文件的形式存储的磁盘上。B+树的内部结点并没有存储数据，仅作为索引使用，所以在同个数据页Page所能容纳的关键字数量更多，一次性读入内存中可以查找的关键字也就越多，所以，对于B+树来说，IO读写也会相应降低。

是否支持顺序遍历和查询效率更加稳定
B-Tree 虽然提高了磁盘IO性能，但是并没有解决元素遍历的效率低下，对于顺序遍历仍需要一个一个进行查找。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。

由于在B树中，查询可能会在非叶子结点终止，但在B+树中，非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

所以，综上，还是使用B+树效率更快，IO次数更少。

Hash索引

主要通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数随机法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置。 在存储中如果发生哈希冲撞的话，则在对应Hash键下以链表形式存在。

支持的存储引擎不多，MySQL中主要有Memory。

全文索引

全文索引是MYISAM的一种特殊索引类型，主要用于全文的模糊查询。MYISAM中原本支持，在InnoDB从5.6之后提供对全文索引的支持。索引程序从数据库中读取数据，索引程序对需要索引的内容进行“分词”，索引程序对分好词的一个个词条加入索引文件。

本质上是维护一个以词条为索引的数据库，通过分词系统维护一个词库。相较于用 like + 通配符或者正则表达式就可以实现模糊匹配，效率更高。

分类

  • 自然语言的全文搜索（默认）
  • 布尔全文搜索

基本使用

  • 主键
  • 全文索引
CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`), # 主键
    fulltext key b_index(b) # 全文索引
) ENGINE=InnoDB  default charset=utf8;


# InnoDB的全文索引（设置最小搜索长度和最大搜索长度）
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;1234567

常见问题

是选择普通索引还是唯一索引？ MySQL为什么有时候选错索引？ 什么时候不适合用索引？ 对于字符串应该怎么设置索引？

是选择普通索引还是唯一索引？

思考方向：change buffer机制对于索引存储过程的影响

change buffer机制

本质上是内存空间，属于MySQL中buffer pool的一部分，适用写多读少的业务。主要是执行一个merge的过程，具体是将change buffer中的操作应用到原数据页，得到最新结果。由于数据库的一致性，在每一次的查询中都会询问最新数据，即都会执行一次merge操作。

  • 在查询多的业务中，IO次数不会增加，反而增加维护压力。
  • 由于在唯一索引中需要判断主键是否冲突，所以只能用于普通索引，不适用于唯一索引。也是回答本问题的依据。
  • 崩溃恢复的时候，先读入老版本的数据页，再将redo log的日志重新写入change buffer数据页，依次运用，得到新版数据页。

从性能方面考虑，选择普通索引。

  • 对于唯一索引，需要将数据页读入内存，然后判断有没有冲突，然后进行插入。
  • 对于普通索引，只需要将条目更新操作记录在change buffer就执行结束了。

MySQL为什么有时候选错索引

优化器的执行过程

优化器在选择执行流程，由于索引统计信息不准确导致的问题。可以使用analyze table 重新收集信息，帮助优化器重新选择信息。

可以从以下方面进行优化：

  1. 强行使用索引 force index
  2. 改变DQL语句，使索引查询使用相应的索引，和order byb,a limit 1
  3. 可以新建一个更好的索引，提供给优化器做选择。

什么时候不适合用索引？

应当优先 从数据出发，首先要能够唯一确认数据， 索引的性质：唯一性 索引的功能：建立数据结构，辅助查询

  1. 字段唯一性差的时候，没有太大区分度的时候
  2. 频繁更新的字段不加索引
  3. 数据量少的不适合用索引
  4. 字段不是用于频繁查询不要使用

对于字符串应该怎么设置索引？

递进优化、倒序存储

常见思路是对整个字符串进行索引，但是效率会慢，且有可能占用大部分存储空间。

针对上述问题是对字符串有选择地使用前缀索引，注意索引的唯一确认性质，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

当前缀索引的前面字符串区分度不高的时候，无法唯一确认数据的时候，可以考虑倒序存储或者使用Hash算法来增加数据的唯一性。

索引失效的情况

对索引进行函数操作，有可能破坏索引值的有序性。

常见的几种情况：

  1. 对字段做了函数计算，就用不上索引
  2. 类型转换
  3. 字符集转换（字符串编码） --- 实质上还是在进行字符串的转化中调用了函数
# 01
select * from tradelog where id + 1 = 10000
# 02
select * from tradelog where tradeid = 110717; --> 
select * from tradelog where CAST(tradid AS signed int) = 110717;
# 03
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

为什么不使用UUID作主键，而是使用自增ID呢？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的。在数据增加和删除的过程中，如果是使用UUID的话，可能在页分裂和页合并过程中产生大量的内存碎片，进而造成插入性能的下降。如果是自增ID，索引结构不断向后移动，只需要不断向后排列。

总之，在数据量大一些的情况下，用自增主键性能会好一些。


参考文献

MySQL实战45讲
MySQL 三万字精华总结 + 面试100 问

avatar
文章
阅读
粉丝