Innodb（二）

• 数据结构与算法

  • 二分查找法

    • 二叉查找树和平衡二叉树（AVL树）

      • AVL树

        • 首先符合二叉查找树的定义
        • 足任何节点的两个子树的高度最大差为1
        • 平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能
        • 是维护一棵平衡二叉树的代价是非常大
          • 通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性

      • B+树

        • B+树由B树和索引顺序访问方法演化而来

          这也是MyISAM引擎最初参考的数据结构）

        • B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树

        • LeafPage IndexPage 旋转做旋转来保持树的高度和平衡

      • B+树索引

        • 高扇出性

        • B+树的高度一般都在2～4层，

        • 分类

          • 聚集索引（clustered inex）和辅助索引（secondary index）
          • 。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分
          • 在多数情况下，查询优化器倾向于采用聚集索引

        • B+树索引的分裂

        • MySQL数据库对于索引的添加或者删除的这类DDL操作

          • 首先创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结购

          • 然后把原表中数据导入到临时表。

          • 接着删除原表。

          • 最后把临时表重名为原来的表名。

          • 改进

            • 快速索引创建
              • 对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁

          • Cardinality值

            • 怎样查看索引是否是高选择性的呢？可以通过SHOW INDEX结果中的列Cardinality来观察。Cardinality值非常关键，表示索引中不重复记录数量的预估值
            • 。在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1

          • 联合索引

            • 联合索引是指对表上的多个列进行索引
              • 联合索引的键值的数量不是1，而是大于等于2。
              • 联合索引的第二个好处是已经对第二个键值进行了排序处理。

          • 覆盖索引

            从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记
            录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

          • 索引提示

            MySQL数据库支持索引提示（INDEXHINT），显式地告诉优化器使用哪个索引。个人总结以下两种情况可能需要用到INDEX HINT

            如果用户确定指定某个索引来完成查询，那么最可靠的是使用FORCEINDEX，而不是USE INDEX

          • Multi-Range Read优化

            Multi-Range Read优化的目的就是为了减少磁盘的随机访问;

            并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升

            Multi-Range Read优化可适用于range，ref，eq_ref类型的查询。

          • MRR优化有以下几个好处：

            • ​ MRR使数据访问变得较为顺序。

            • 在查询辅
              助索引时，首先根据得到的查询结果，按照主键
              进行排序，并按照主键排序的顺序进行书签查
              找。

            • 减少缓冲池中页被替换的次数。

            • 批量处理对键值的查询操作。

            • 对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下

            • ❑ 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
              ❑将缓存中的键值根据RowID进行排序。
              ❑根据RowID的排序顺序来访问实际的数据文件。

            • Multi-Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询

              • 好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据

            • 启用了Multi-Range Read优化，所以会对查询条件进行拆分，这样在列Extra中可以看到Using MRR选项

              是否启用Multi-Range Read优化可以通过参数optimizer_switch中的标记（flag）来控制。当mrr为on时，表示启用Multi-Range Read优化。
              mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。若将mrr设为on，mrr_cost_based设为off，则总是启用Multi-Range
              Read优化。

              mysql＞
              SET@@optimizer_switch=‘mrr=on,mrr_cost_based=off’;
              Query OK,0 rows affected(0.00 sec)

              参数read_rnd_buffer_size用来控制键值的缓冲区大小，当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256K：

              show variables like ‘%optimizer_switch%’;
              index_merge=on,index_merge_union=on,
              index_merge_sort_union=on,
              index_merge_intersection=on,
              engine_condition_pushdown=on,
              index_condition_pushdown=on,
              mrr=on,//

              mrr_cost_based=on,//
              block_nested_loop=on,
              batched_key_access=off,
              materialization=on,
              semijoin=on,
              loosescan=on,
              firstmatch=on,
              duplicateweedout=on,
              subquery_materialization_cost_based=on,
              use_index_extensions=on,
              condition_fanout_filter=on,
              derived_merge=on

          • Index ConditionPushdown（ICP）优化

            • 索引下推

              • 则在索引取出时，就会进行WHERE条件的过滤，然后再去获取记录

              • ·当然，WHERE可以过滤的条件是要该索引可以覆盖到的范围。

                • |主键是(emp_no，from_date)的联合索引，所以idx_s索引
                  中包含了from_date的数据，故可使用此优化方
                  式。

            • 和Multi-Range Read一样，Index ConditionPushdown同样是MySQL5.6开始支持的一种根据索引进行查询的优化方式。之前的MySQL数据库版本不支持Index Condition Pushdown，当进行索引查询时，

              1、首先根据索引来查找记录，

              2、然后再根据WHERE条件来过滤记录。在支持IndexCondition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层

              在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能

              **** Index Condition Pushdown优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。可在执行计划的列Extra看到Using index condition提示。

  • 哈希算法

    • 哈希表

    • 哈希表（Hash Table）也称散列表，由直接寻址表改进而来。

    • 哈希表技术很好地解决了直接寻址遇到的问题

      • 碰撞（collision）
        • 采用最简单的碰撞解决技术，这种技术被称为链接法（chaining）。

    • 哈希函数h必须可以很好地进行散列

      • 数据库中一般采用除法散列的方法。

    • InnoDB存储引擎中的哈希算法

    • InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用
      除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。

    • 自适应哈希索引

    • 5.8 全文检索

    • |全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查
      找出来的技术。

      它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统
      计和分析。

      • 倒排索引

      • 倒排索引同B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中
        存储了单词与单词自身在一个或多个文档中所在位置之间的映射。

      • 两种表现形式

        • inverted file index，其表现形式为{单词，单词所在文档的ID}

        • full invertedindex，其表现形式为{单词，(单词所在文档的ID，在具体文档中的位置)}

        • 。相比之下，full invertedindex占用更多的空间，但是能更好地定位数据，并扩充一些其他的搜索特性

        • df

      • InnoDB全文检索

      • InnoDB存储引擎从1.2.x版本开始支持全文检索的技术，其采用full inverted index的方式

        为了提高全文检索的并行性能，共有6张Auxiliary Table，目前每张表根据word的Latin编码进行分区

        Auxiliary Table是持久的表，存放于磁盘上。
        然而在InnoDB存储引擎的全文索引中，还有另外一个重要的概念FTS Index Cache（全文检索索引缓存），其用来提高全文检索的性能。

        FTS Index Cache是一个红黑树结构，其根据（word，ilist）进行排序。

        InnoDB存储引擎会批量对Auxiliary Table进行更新，而不是每次插入后更新一次Auxiliary Table

        参数innodb_ft_cache_size用来控制FTS IndexCache的大小，默认值为32M。当该缓存满时，会将其中的(word，ilist)分词信息同步到磁盘的Auxiliary Table中。增大该参数可以提高全文检索的性能，但是在宕机时，未同步到磁盘中的索引信息可能需要更长的时间进行恢复

      • 全文检索

      • mysql＞SELECT*FROM fts_a WHERE body LIKE'%Pease%'; // 普通
        mysql＞SELECT*FROM fts_a
        -＞WHERE MATCH(body)
        -＞AGAINST('Porridge'IN NATURAL LANGUAGE
        MODE);
        +------------+------------------------------
        -----------+
        |FTS_DOC_ID|body|
        +------------+------------------------------
        -----------+
        |2|Pease porridge hot,pease porridge cold|
        |1|Pease porridge in the pot|
        +------------+------------------------------
        -----------+
        2 rows in set(0.00 sec)
        
        

      • Boolean

• 事务

• 事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undolog来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的
  一致性。

  redo和undo的作用都可以视为是一种恢复操作，

  redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。

  undo是逻辑日志，根据每行记录进行记录。

  ​

  ​

  ​

  ​