MySQL探险-2、索引

一、简介：

  MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构，所以说索引的本质是：数据结构。

  除数据本身之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。索引的目的在于提高查询效率。

  索引本身会占用不少空间，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上。

  通常说的索引，没有特别指明的话，就是 B+ 树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引、次要索引、覆盖索引、符合索引、前缀索引、唯一索引默认都是使用 B+ 树索引，统称索引。此外还有哈希索引等。

  ①索引的优劣

    优势：
      ●提高数据检索效率，降低数据库 I/O 成本。
      ●降低数据排序的成本，降低CPU的消耗。

    劣势：
      ●索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用存储空间。
      ●虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息。

  ②索引的分类

    从数据结构角度：
      ●B+ 树索引
      ●Hash 索引
      ●Full-Text 全文索引
      ●R-Tree 索引

    从物理存储角度：
      ●聚集索引（clustered index）
      ●非聚集索引（non-clustered index），也叫辅助索引（secondary index）
    注意：聚集索引和非聚集索引都是 B+ 树结构。

    从逻辑角度：
      ●主键索引：主键索引是一种特殊的唯一索引，不允许有空值。
      ●普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引。
      ●多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合。
      ●唯一索引或者非唯一索引
      ●空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有4种，分别是 GEOMETRY、POINT、LINESTRING、POLYGON。MySQL 使用 SPATIAL 关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MYISAM 的表中创建。

---

二、索引结构：

  索引（index）是在存储引擎（storage engine）层面实现的，而不是 server 层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

  ①B+ 树索引

    MyISAM 和 InnoDB 存储引擎，都使用 B+ 树的数据结构，它相对于 B 树结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

    B 树

      B 树是为磁盘等外存储设备设计的一种平衡查找树。

      系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么精确读取什么。

      InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：

SHOW VARIABLES LIKE 'innodb_page_size';

      系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小。InnoDB 读取数据时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

      B 树结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B 树，首先定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值；data 为一行记录中除主键外的数据。对于不同的记录，key 值互不相同。

      一棵 m 阶的 B 树有如下特性：
        ●每个节点最多有 m 个孩子。
        ●除了根节点和叶子节点外，其它每个节点至少有 Ceil(m/2) 个孩子。
        ●若根节点不是叶子节点，则至少有 2 个孩子。
        ●所有叶子节点都在同一层，且不包含其它关键字信息。
        ●每个非终端节点包含 n 个关键字信息（P0,P1,…Pn, k1,…kn）。
        ●关键字的个数 n 满足：ceil(m/2)-1 <= n <= m-1。
        ●ki(i=1,…n) 为关键字，且关键字升序排序。
        ●Pi(i=1,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki，但都大于 k(i-1)。

      B 树中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B 树示意图：

      每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 17 和 35，17 左边的指针指向的子树的数据范围为小于 17，17 和 35 之间的指针指向的子树的数据范围为 17~35，35 右边的指针指向的子树的数据范围为大于 35。

      由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B 树查找效率的决定因素。B 树相对于 AVL 树缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

    B+ 树

      B+ 树是在 B 树基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+ 树实现其索引结构。

      从上一节中的 B 树结构图中可以看到每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B 树的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+ 树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+ 树的高度。

      B+ 树相对于 B 树有几点不同：
        ●非叶子节点只存储键值信息。
        ●所有叶子节点之间都有一个链指针。
        ●数据记录都存放在叶子节点中。

      将上一节中的 B 树优化，由于 B+ 树的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+ 树后其结构如下图所示：

      通常在 B+ 树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式结构。因此可以对 B+ 树进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

      B+ 树做这样的改进，有什么好处呢？下面做一个大致推算：
        InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用 4 个字节）或 BIGINT（占用 8 个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+ 树中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的 K 取值为1000）。也就是说一个深度为 3 的 B+ 树索引可以维护 1000 * 1000 * 1000 = 10 亿条记录。

      实际情况中每个节点可能不会填充满，因此在数据库中，B+ 树的高度一般都在 2-4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

      B+ 树总结：
        ●磁盘 I/O 次数取决于 B+ 树的高度 h，假设当前数据表的数据为 N，每个磁盘块的数据量是 m，则有 h=㏒(m+1)N。当数据量 N 一定的情况下，m 越大，h 越小。而 m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项（即索引字段）要尽量的小的原因。这也是为什么 B+ 树要把真实的数据放到叶子节点而不是内层节点的原因（一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高），极限场景下将会退化成线性表。
        ●当 B+ 树的数据项是复合的数据结构，比如（name,age,sex）的时候，B+ 树是按照从左到右的顺序来建立搜索树的。比如当（张三,20,F）这样的数据来检索的时候， B+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当（20,F）这样的数据（没有 name）来查询的时候，B+ 树就不知道该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当（张三,F）这样的数据（没有 age）来检索时，B+ 树可以用 name 来指定搜索方向，但因为字段 age 的缺失，所以只能把名字等于“张三”的数据都找到，然后再匹配性别是 F 的数据了。 这个是非常重要的性质，即索引的最左匹配特性。

    MyISAM 主键索引与辅助索引的结构

      MyISAM 引擎的索引文件和数据文件是分离的。MyISAM 引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为“非聚簇索引”。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

      在 MyISAM 中，索引（含叶子节点）存放在单独的 .myi 文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

      主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

      通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

    InnoDB 主键索引与辅助索引的结构

      InnoDB 引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行）。或者说，InnoDB 的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引。

      主键索引：

        InnoDB 的主键索引是聚簇索引，它的索引和数据是存入同一个 .idb 文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据。

        在 InnoDB 中，索引分叶子节点和非叶子节点。非叶子节点就像新华字典的目录，单独存放在索引段中；叶子节点则是顺序排列的，在数据段中。InnoDB 的数据文件可以按照表来切分（只需要开启 innodb_file_per_table)，切分后存放在 xxx.ibd 中；默认不切分，存放在 xxx.ibdata 中。

      辅助（非主键）索引：

        辅助索引的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照 ASCII 码进行排序，第二行的整数是主键的值。

        这就意味着，对辅助索引的列进行条件搜索，需要两个步骤：
          ●在辅助索引上检索，到达其叶子节点，获取对应的主键。
          ●使用主键在主键索引上再进行对应的检索操作。
        这也就是所谓的“回表查询”。

      InnoDB 索引结构需要注意的点：

        ●数据文件本身就是索引文件。
        ●表数据文件本身就是按 B+ 树组织的一个索引结构文件。
        ●聚簇索引中叶子节点包含了完整的数据记录。
        ●InnoDB 表必须要有主键，并且推荐使用整型自增主键。

      正如上面介绍的 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据。如果在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引；如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为 6 个字节，类型为整型。

    引申思考

      为什么 Mysql 索引要用 B+ 树不是 B 树？
        ●用 B+ 树不用 B 树考虑的是磁盘 I/O 对性能的影响。B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，磁盘 I/O 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在 MySQL 底层对 B+ 树进行进一步优化：在叶子节点中是双向链表。

      为何一般不采用 Hash 方式？
        ●因为 Hash 索引底层是哈希表，哈希表是一种以 key-value 存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的。这导致区间查询无法直接通过索引查询，需要全表扫描。所以，Hash 索引只适用于等值查询的场景。而 B+ 树是一种多路平衡查询树，它的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。
        ●Hash 索引也不支持多列联合索引的最左匹配规则，如果有大量重复键值的情况下，Hash 索引的效率会很低，因为存在哈希碰撞问题。

      为什么主键推荐使用整型自增主键而不是选择 UUID？
        ●UUID 是字符串，比整型消耗更多的存储空间。
        ●在 B+ 树中进行查找时，需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速。
        ●自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续的；UUID 是随机产生的，读取的上下两行数据存储是分散的，不适合执行范围条件查询语句。
        ●在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID 主键很容易出现这样的情况，B+ 树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

      为什么非主键索引结构叶子节点存储的是主键值？
        ●为了保证数据一致性和节省存储空间。当数据结构变更时，不需要再次维护所有数据，同时也节省了存储空间。

  ②Hash 索引

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

    检索算法：在检索查询时，就再次对待查关键字再次执行相同的 Hash 算法，得到 Hash 值，到对应 Hash 表对应位置取出数据即可。如果发生 Hash 碰撞，则需要在取值时进行筛选。目前使用 Hash 索引的数据库并不多，主要有 Memory 等。

    MySQL 目前有 Memory 引擎和 NDB 引擎支持 Hash 索引。

  ③Full-Text 全文索引

    全文索引也是 MyISAM 的一种特殊索引类型，主要用于全文索引。InnoDB 从 MYSQL5.6 版本提供对全文索引的支持。

    它用于替代效率较低的 LIKE 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

    同样使用 B 树存放索引数据，但使用的是特定的算法：将字段数据分割后再进行索引（一般每 4 个字节一次分割）。索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应 B 树结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

  ④R-Tree 索引

    空间索引是 MyISAM 的一种特殊索引类型，主要用于地理空间数据类型。

---

三、索引的应用：

  ①需要创建索引的场景

    ●主键自动建立唯一索引。
    ●频繁作为查询条件的字段。
    ●查询中与其他表关联的字段，外键关系建立索引。
    ●单键/组合索引的选择问题（高并发下倾向创建组合索引）。
    ●查询中排序的字段，排序字段通过索引访问大幅提高排序速度。
    ●查询中统计或分组字段。

  ②不建议创建索引的场景

    ●表记录太少。
    ●经常增、删、改的表（会加重 I/O 负担）。
    ●数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）。
    ●where 条件里用不到的字段不创建索引

  ③覆盖索引

    覆盖索引（Covering Index），也叫索引覆盖， 也就是回表查询的优化措施。

    使用索引可以高效找到行，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了索引的数据，当能通过读取索引就可以得到想要的数据时，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。

    覆盖索引的目标是 select 的数据列只用从索引中就能够取得，不必读取数据行。MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件。换句话说，查询列要被所建的索引覆盖。

    判断语句是否使用覆盖索引，可以使用 explain，通过输出的extra列来判断。对于一个索引覆盖查询，显示为“using index”。MySQL 查询优化器在执行查询前会决定是否有索引覆盖查询。

  ④索引下推

    Mysql5.6 版本提出了索引下推的原则：用于查询优化，主要是用于 LIKE 关键字的查询的优化。

    举个例子：
      假设有表 employee，其中 employee_name 字段建有索引，查询语句为：SELECT * FROM employee WHERE employee_name LIKE ‘张%’ AND age = 60;（此时假设有很多“张”姓员工，但只有一名年龄为 60）
      此时按照正常情况，会忽略 age 字段，将 employee_name 查询到的“张”开头的结果返回给 Mysql 服务器，再执行回表查询。
      如果应用了索引下推，则 Mysql 会将查询条件 age=60 的查询条件传递给存储引擎，再次过滤掉不满足条件的数据行，这样回表的次数就变为了一次，提高了查询效率。

    索引下推就是在执行查询的时候，会将一部分的索引列的判断条件传递给存储引擎，由存储引擎通过判断是否符合条件，只有符合条件的数据才会返回给 Mysql 服务器。

  ⑤索引原则和优化

    要正确的使用索引，就要正确的创建索引，用索引正确的查询，不要使索引失效，因此索引的设计和优化的原则应该遵循下面的一些原则：

      设计原则：
        ●搜索条件、排序、分组和联合操作字段。
          出现在 where 关键词后面的字段适合设置为索引列，或者连接子句中指定的列也是可以设置为索引列。
        ●唯一性字段（字段值基数越小）。
          对于唯一性的列，设置索引效果是最佳的；而对于具有多个重复值的列，其索引效果是最差的。因此设置索引时，需要考虑该列中值的分布情况。（注意：此处说的字段值的基数越小越适合做索引列，但这里不是指唯一索引）
          扩展：区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，唯一键的区分度是1。
        ●短索引原则。
          对于长字符串字段列设置索引，最好遵循短索引原则（即指定前缀长度），节省大量索引空间。
          比如：有一个备注列字段 VARCHAR(200)。如果该列设置为索引列，查询效率不很高，因为索引字段长度过大，索引节点树高增加，I/O 次数也会增加。因此，对于长子字符考虑使用前缀索引。将备注字段值的前 n（比如 10） 个字符设置为索引，就会节省索引空间，提高效率。
        ●适度设置索引列。
          设置索引时要考虑设置合适的列，不要造成“过多的索引列”。因为每个索引需要额外的磁盘空间，并降低写操作的性能。并且在修改表内容的时候，索引会进行更新，更有甚至需要重构，索引列越多，所花费的时间就会越长。所以只保持需要的索引有利于查询即可。如果想要给已存在索引列的表再添加索引，则需要考虑一下要增加的索引是否能够使用现有多列索引的最左索引，如果是，则无须增加该索引。对于长时间不再使用或者很少使用的索引要进行删除操作。

      优化原则：
        ●联合索引，遵循最左前缀匹配原则。
          注意：最左匹配原则并不是指查询条件的顺序，而是指查询条件中是否包含索引最左列字段。
        ●隐式转换。
          数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。（同理，数字类型查询时注意不要加引号）
        ●索引列不能参与计算，保持列“干净”。
          索引列不要在表达式中出现，这样会导致索引失效。比如：SELECT … WHERE id + 1 =5;（同时，索引列不要作为函数的参数使用）
        ●范围条件存在多个索引时，查询可以命中索引。
          范围条件有：<、<=、>、>=、between等。（尽量不要在条件 NOT IN、<>、!= 中使用索引）
          范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，并且索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
          如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引。
        ●利用覆盖索引进行查询，避免回表。
          被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”。
        ●在索引列的字段中不要出现 NULL 值，NULL 值会使索引失效，可以用特殊的字符（比如：空字符串’ '或者0）来代替 NULL 值。

---

四、数据库内部存储结构探索：

  ①引出

    当谈起数据库内部存储结构时，通常会想到 B 树或者 B+ 树，这些数据结构为什么适合作为数据库存储的内部结构呢？（B 树和 B+ 树在大范畴上同属于 B 树，后续在更宏观的角度探索，不再详细区分，统称为 B 树）

      传统的关系型数据库将数据以 B 树的形式存储在磁盘上，同时会在 RAM 上使用 B 树维护这些数据的索引，来保证更快的访问速度。插入的行存储在 B 树的叶子节点上，所有的中间节点用来存储用于导航查询语句的原数据。

      当有数以百万计的数据被插入到数据库中时，索引和数据存储会变得十分庞大。因此，为了快速的访问，需要从磁盘中加载所有数据到 RAM，但是 RAM 一般没有这么大的空间来存储所有的数据。因此，数据库必须从磁盘中读取部分数据。这种加载数据的场景如下图所示：

      磁盘 I/O 花费的时间很长，是影响数据库性能的主要原因之一。B 树是支持随机读写、in-place 替换的十分紧凑并且自平衡的数据结构。随机读意味着当访问磁盘数据时，磁头必须移动到柱面上的指定位置，因此会消耗大量时间。

      B 树被设计为使用 block 的形式存储数据，因为操作系统读取读取一个 block 的数据要比读取单独字节数据要快的多。MySQL 的 InnoDB 存储引擎的 block 大小为 16KB。这意味着每次读取或者写入数据时，大小为 16KB 的 block 数据会被从磁盘加载到 RAM 中，它会被写入新的数据，并且再次写回到磁盘上。假设数据库表的每一行数据为 128 字节（实际大小会变化），一个 block（这里特指一个叶子节点）为 16KB，存储了 (16 * 1024) / 128 = 128 行数据。

      B 树的高度一般比较小，但是每一层的节点数量却很多，由此可以管理数以万计的数据。基于上述特性，B 树适合作为数据内部存储结构。

    在 B 树上进行读操作是相对来说比较快速的，因为该操作只需要遍历一些节点并且进行较少次数的磁盘 I/O 请求。而且，范围查询因为可以将数据以 block 的形式进行获取和操作而速度更快。可以进行下列操作来让基于 B 树的数据库性能更好：

      ●减少索引节点数量：这是提升关系型数据库性能的常用策略。索引越多，插入和更新操作需要管理的索引数量也越多。当数据库数据运行时间越来越久时，就需要删除一些老旧或者无用的索引，并且谨慎地添加新的索引。但是也要注意，索引越少意味着查询性能越差，需要在查询性能和插入更新性能之间进行取舍。

      ●顺序插入：如果能以主键大小为基础进行大量数据的顺序插入，那么插入数据的速度会十分的快。因为在插入过程中，插入行所属的 block 已经在内存中，所以数据库可以直接将行插入到内存的数据结构中，然后通过一次磁盘 I/O 提交到数磁盘中。当然，这些都取决于数据库的具体实现，但是现代的数据库一般都会进行类似的优化。

    但是 B 树并不是适合所有情景的最优存储结构。对 B 树结构的写操作性能很差，比随机读还要差。因为数据库必须从磁盘中加载数据对应的页，然后修改它并重新写入到磁盘中。随机写入时平均有 100 字节每秒写入速度，这个限制是由于磁盘的基本工作原理。

    事实上，简单依赖于缓存的使用，索引搜索和更多的内存可以处理更多的读操作，但是应付更多的写操作就比较麻烦。当需要写入或更新大量的数据时，B 树结构并不是最正确的选择。长久以来，传统数据库进行了大量的优化，比如说 InnoDB 尝试使用缓存来减少磁盘 I/O 操作。具体操作如下所示：

    数据库写操作可以通过提升磁盘的带宽来提升速度，但是目前关系型数据库都没有这样做。而且关系型数据库管理系统一般都是十分复杂的，因为他们使用锁、并发、ACID事务等操作，这使得写入操作更加复杂。

  ②LSM 树

    当今信息时代，在比如消息收发、聊天、实时通讯和物联网等客户为中心的服务和大量无结构化数据的分布式系统中，每小时都会进行数百万计的写入操作。这些系统是以写为主的系统，为了迎合这些系统的需要，数据库需要能够拥有快速插入数据的能力。典型的数据库并不能很好的满足类似的场景，因为它们无法应付高可用性、尽可能的最终一致性、无格式数据的灵活性和低延迟等要求。

    因此，LSM 树（Log Structured Merge Tree）应运而生。LSM 并不是一种类似于 B 树的数据结构，而是一个系统。在 LSM 系统中，并没有对数据的 in-place 替换；一旦数据被写入磁盘，它就再也不会被修改。显然，它是一种只能在末尾添加（append only）的写入系统。一些日志结构的文件系统也使用类似的原理。

    基本上，LSM 系统利用了顺序写的优势。传统的磁盘驱动的写操作最高可以达到 100MB/s，现代的固态硬盘在顺序写时的速度则更快。事实上，固态硬盘驱动有一些内置的并行机制来让它可以同时写入 16 到 32MB 的数据。LSM 树和固态硬盘的特性十分匹配。顺序写要比随机写快很多。

    为了正确地理解上述场景，简单的看下 Facebook 的 Cassandra 数据库是如何使用LSM原则的：

    Cassandra 或者任何 LSM 系统都会维护一个或者多个用来在写入磁盘前存储数据的内存数据结构（如上图中的 memtable），比如说子平衡树（AVL）、红黑树、B 树或者跳表。该内存数据结构维护一个有序的数据集。

    不同的 LSM 实现会使用不同的数据结构来适应不同的需求，并不存在标准的 LSM 实现。当内存中存储的数据超过配置的阈值时，内存中存储的数据就会被放置在将会被写入磁盘的队列中。此时执行 flush，将数据从队列中持久化到磁盘，因此 Cassandra 顺序地写入有序的数据到磁盘中。

    磁盘维护一个叫做“SSTable”（Sorted Strings Table）的数据结构，该数据就是写入文件数据的有序的快照，SSTable 是不可变的。LSM 系统可以管理磁盘上的多个文件。

    因此，如果数据在内存中没有被发现，Cassandra 需要扫描所有磁盘上的 SSTables 来搜索该数据。因此，Cassandra 的读操作相对来说要比写操作慢，但是这里有一些可以优化的方法。Cassandra 或者其他 LSM 系统会在后台运行压缩程序来减少 SSTable 的数量。压缩程序对 SSTable 进行归并排序，在新的 SSTable 找那个插入新的排序数据并且删除老的 SSTables。但是使用压缩程序有时候无法应付数据库中数以百万计的更新操作。

    因此，一些概率数据结构（probabilistic data structures）比如 Bloom filters 被应用来快速判断是否一些数据存在于 SSTable。Bloom filters 十分适合对内存中的数据进行判断，因为它需要进行大量的随机查询来进行数据是否存在的概率性判断。Bloom filters 算法可以极大地减少遍历查询 SSTables 的花费。这样，LSM 系统就解决了在大数据中写操作需要花费大量时间的问题。

    LSM 系统也有 Read amplification 的问题（会读取出比它实际需要更多的数据）。因此，还有介于 B Tree 和 LSM Tree 之间的解决方法来给出最优（不一定准确）的读写效率吗？

    Fractal Tree Index 是基于 B 树的数据结构。依据开发人员给出的 benchmark，该数据结构有比 B 树更优良的性能。Fractal tree 支持在非叶节点上的信息缓存。MySQL 的高性能存储引擎 Tokudb 就使用了 Fractal tree。

    如上图所示，在 Fractal Tree 中，用户进行的添加列、删除列、插入、更新等任何操作都会被当做操作消息存储在非叶节点上。由于操作只是被简单地存储在缓存或者任何次级索引缓存（secondary index buffer）中，所以，所有的操作都会被迅速执行结束。

    当某一个节点的缓存满了之后，这些操作消息会依次从根节点，经过非叶节点，向叶节点进行传递。叶节点仍然存储着真实数据。当进行读时，读操作会考虑查询路径节点上的所有操作消息来获取真实的数据状态。但是由于 Tokudb 会尽力将所有非叶节点缓存在内存中，所以这一过程也很快。

    Tokudb 中的 block 最大可以达到 4MB，而不是 InnoDB 中的 16KB。这样的大小可以允许一次 I/O 操作时加载或写回更多的数据，这也有助于一次压缩更多数据来减少磁盘上数据的存储大小。

    因此，Tokudb 强调借助更大的 block 大小能够实现更好的数据压缩和更少的磁盘 I/O。Tokudb 宣称它们的存储引擎比 InnoDB 更快，提供比 InnoDB 更快的读写吞吐，并且 Tokudb 也宣称自己有更少的碎片（fragmentation）问题，它也支持多集群索引等。下图是 benchmark 的相关统计图：

    只有自己系统中的 benchmark 可以帮助用户判断正确的数据点和需求解决方案。但是 MySQL 的存储引擎会持续地不断改进和支持新出现的需求。LSM 树是为了高写入场景的系统，而 B 树是为了传统的场景应用。Fractal 树的索引改进了 B 树索引存在的一些缺陷。因此，未来会不断地出现技术上的革新，包括数据库存储技术，硬件，磁盘驱动和操作系统，让我们拭目以待。

参考资料：
Some study on database storage internals