MySQL：InnoDB

发表于 更新于 分类于
本文字数： 15k 阅读时长 ≈ 28 分钟

MySQL体系结构和存储引擎

尽管各平台在底层实现方面都各有不同，但是MySQL基本上能保证在各平台上的物理体系结构的一致性。

定义数据库和实例

  • 数据库：

    • 物理操作系统文件或其他形式文件类型的集合。
    • 在MySQL数据库中，文件可以是frm、MYD、MYI等结尾的文件。
    • NDB引擎时，可能是存放于内存当中的文件。
    • 是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。

  • 实例：

    • MySQL数据库由后台线程以及一个共享内存区组成。可以被运行的后台线程所共享。
    • 是真正用于操作数据库文件的。
    • 是程序，是位于用户与OS间的一层数据管理软件，程序只有通过实例才能和数据库打交道。

MySQL是一个单进程多线程架构的数据库。

MySQL体系结构

1554105660683

MySQL组成

  • 连接池组件

  • 管理服务和工具组件

  • SQL接口组件

  • 查询分析器组件

  • 优化器组件

  • 缓冲组件

  • 插件式存储引擎。基于表而不是数据库

  • 物理文件

MySQL存储引擎

用户应该根据具体的应用选择合适的存储引擎

InnoDB

  • 支持事务，设计目标主要面向在线事务处理的应用
  • 特点
    • 行锁设计
    • 支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁
  • 通过使用多版本并发控制（MVCC）来获得高并发性，并实现了SQL标准的4种隔离级别
    • 默认为Repeatable read
    • 使用一种被称为next-keylocking的策略避免幻读
  • 提供插入缓冲、二次写、自适应哈希索引、预读等高性能功能。
  • 对于表中数据的存储，采用了聚集的方式，因此每张表存储都是按照主键的顺序进行存放，如果没有显式定义逐渐，InnoDB将为每一行生成一个6字节的ROWID
  • InnoDB存储引擎将数据放在一个逻辑的表空间，这个表空间像一个黑盒一样由InnoDB管理，可以将表单独存放到一个独立的idb文件中

MyISAM

  • 不支持事务、表锁设计
  • 支持全文索引
  • 主要面向一些OLAP数据库应用。适合报表查询系统，因为这些系统并不需要事务。
  • 只缓存索引文件，不缓冲数据文件
  • 锁的粒度为表级
  • 相对简单，在效率上更优，小型应用更为适合
  • 表是保存成文件形式，在跨平台的数据转移中会省去不少麻烦

NDB

  • 一个集群存储引擎。结构为share nothing的集群架构，能够提供更高的可用性
  • 特点
    • 数据全部放在内存中，因此主键查找速度极快
    • 可通过添加NDB数据存储结点，线性提高数据库性能
    • 从MySQL5.1开始，可以将非索引数据放在磁盘上。
  • 缺陷
    • NDB存储引擎的连接操作是在MySQL数据库层完成的，而不是存储引擎层。因此复杂的连接操作需要巨大的网络开销，查询速度很慢

Memory

  • 适用于存储临时数据的临时表，以及数据仓库中的维度表
  • 特点
    • 将表中数据放在内存中，如果数据库重启或崩溃，表中数据都将消失。
    • 默认使用哈希索引
    • 速度非常快。
    • 只支持表锁，并发性能较差，不支持TEXT和BLOB列类型。存储变长字段时是按照定常字段的方式进行，会浪费内存。
  • 当作为临时表存放中间结果集，如果中间结果集大于Memory存储引擎表的容量设置，或含有TEXT列类型，则会转换为MyISAM存储引擎

Archive

  • 只支持Insert和select操作
  • 从MySQL5.1支持索引
  • 适合存储归档数据，如日志。使用zlib算法将数据行压缩存储，压缩比一般可达到1：10。
  • 使用行锁实现高并发的插入操作，但本身不是事务安全的，设计目标是提供高速的插入和压缩功能

Federated

  • 不存放数据，只是指向一台远程MySQL数据库服务器上的表

Maria

  • 新开发的引擎，设计目标是取代原有的MyISAM，从而成为MySQL的默认存储引擎
  • 支持缓存数据和索引文件
  • 应用行锁设计，提供MVCC功能，支持事务和非事务安全的选项，以及更好地blob字符类型的处理性能

连接MySQL

管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字

InnoDB存储引擎

是事务安全的存储引擎。在InnoDB 1.2x增加了全文索引支持。

概述

  • 第一个完整支持ACID事务的存储引擎
    • 原子性（atomicity，或称不可分割性）。一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
    • 一致性（consistency）。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
    • 隔离性（isolation，又称独立性）。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
    • 持久性（durability）。事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
  • 特点
    • 行锁设计、支持MVCC、支持外键、提供一致性非锁定读
  • 被设计用来最有效地以及使用内存和CPU

InnoDB体系架构

1554108833485

InnoDB存储引擎有多个内存块，可以认为他们组成一个大内存池，负责：

  • 维护所有进程/线程需要访问的多个内部数据结构。
  • 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改前在这里缓存。
  • 重做日志(redo log)缓冲。
  • 。。。

数据存储

Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。

独立表空间：

每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

优点：

  • 每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。
  • 空间可以回收（除drop table操作处，表空不能自已回收）
  • Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
  • 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
  • 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法；

共享表空间：

即idbdata*，又称为系统表空间。某一个数据库的所有的表数据，索引文件全部放在一个单独的表空间中，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。

优点：

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制。数据和文件放在一起方便管理。

缺点：

所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。

共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（可以理解为oracle的表空间10G，但是才使用10M，但是操作系统显示mysql的表空间为10G），进行数据库的冷备很慢；

后台线程

负责

  • 刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。
  • 将已经修改的数据文件刷新到磁盘文件。
  • 保证数据库在发生异常的情况下InnoDB能恢复到正常运行状态。

  1. MasterThread

    负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收

  2. IO Thread

    • InnoDB大量使用AIO处理写请求，可以极大提高数据库的性能
    • 负责将IO请求的回调处理。
    • Linux下有4个，为write、read、insert buffer、log IO thread
    • Windows可以自行设定，write与read增大到了4个，通过参数innodb_read(write)_io_threads设定

  3. Purge Thread

    事务提交后，其使用的undo log可能不再需要，使用该线程回收已经使用并分配的undo页

  4. Page Cleaner Thread

    将之前版本中脏页的刷新操作放入到单独的线程。

内存

缓冲池

InnoDB存储引擎基于磁盘存储的，并将其中的记录按照页的方式进行管理。即可认为是基于磁盘的数据库系统。

考虑CPU与磁盘速度，则使用缓冲池技术提高数据库整体性能。缓冲池是一块内存区域，通过内存的速度来弥补磁盘的速度较慢。

  • 数据库读取页的操作：

    • 首先将从磁盘读到的页存放到缓冲池中。
    • 下一次读取相同的页，判断该页是否在缓冲池中。若在，则该页被命中，直接读取。否则读取磁盘。

  • 数据库页修改操作：

    • 首先修改在缓冲池中的页，再以一定频率刷新到磁盘（Checkpoint机制）。

缓冲池大小配置：innodb_buffer_pool_size。

数据页类型

缓冲池当中的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。

1565692774481

多缓冲池

InnoDB 1.0.x后允许有多个缓冲池实例。每个页根据哈希值平均分配到不同的缓冲池实例中。

减少了数据库内部的资源竞争，增加数据库的并发处理能力

配置：innodb_buffer_pool_instances

LRU List、Free List和Flush List

对缓冲池这么大的内存区域进行管理。

InnoDB使用LRU管理页，缓冲池中页的默认大小是16KB。使用innodb_old_blocks_time表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

在数据库当启动时，LRU列表是空的，此时页都存放在Free列表中，当需要从缓冲区中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中，否则根据LRU算法淘汰LRU列表末尾的列，将该内存空间分配给新的页。

压缩页

从1.0.x开始支持压缩页的概念，可将原本16KB的页压缩为1KB、2KB、4KB、8KB，因此LRU列表页存在变化，对于压缩过的页是通过unzip_LRU列表进行管理的。

Flush List

该列表当中的页为脏页列表，脏页既存在于LRU列表，也存在FLUSH列表中，LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘。

重做日志缓冲

InnoDB存储引擎的内存区域除了有缓冲池，还有重做日志缓冲(redo log buffer)。

InnoDB首先将重做日志信息放入到该缓冲区，然后按一定频率将其刷新到重做日志文件。一般不需要很大，一般每s会将日志缓冲刷新到日志文件，用户只需要保证每s产生的事务量在这个缓冲范围内即可。

  • 在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存。
  • 记录的是物理数据页面的修改的信息。
  • 可通过innodb_log_buffer_size控制，默认为8MB。

刷新日志缓冲到外部磁盘的redo log buffer

  • Master Thread每秒将重做日志缓冲刷新到重做日志文件。
  • 每个事务提交时会将重做日志缓冲刷新到重做日志文件中
  • 当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

额外的内存池

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

分配了缓冲池，但是每个缓冲池的帧缓冲和对应的缓冲控制对象（这些对象记录LRU、锁、等待等信息）的内存需要从缓冲控制块申请。因此申请了很大的缓冲池，也要考虑相应增加额外内存池。

Checkpoint技术

页的操作首先是在缓冲池中完成的。如果一条DML语句，则此时页是脏的，缓冲池中的页版本比磁盘当中新，数据库需要将新版本的页刷新到磁盘。

  • 倘若每次一个页发生变化，就刷新到磁盘，则开销极大。
  • 倘若在刷新到磁盘的过程中，服务器宕机，则数据无法恢复。

为了避免数据丢失问题，普遍采用Write Ahead Log策略

  • 当事务提交时，先写redo log，再修改页。
  • 当宕机导致数据丢失时，通过重做日志来完成数据的恢复，实现持久性的要求。
  • 是否不需要将缓冲池中页刷新到磁盘。
    • 缓冲池无法缓存数据库所有的数据。
    • 重做日志无法无限增大，即使可以，运维难度大、成本高。
    • 日志文件太大，恢复数据库需要时间太久。

Checkpoint技术

解决的问题：

  • 缩短数据库的恢复时间。只需对Checkpoint后的重做日志进行恢复。
  • 缓冲池不够用时，将脏页刷新到磁盘。采用LRU算法溢出页面，如果为脏页则强制执行Checkpoint，刷新页面。
  • 重做日志不可用时，刷新脏页。数据库对重做日志进行循环使用，覆盖重用。
    • 重做日志不可用是指当repo log已经满了，而repo log不可增大，此时必须将脏页刷新回磁盘。

Checkpoint发生的时间、条件以及脏页的选择等都非常复杂。Innodb中有两种Checkpoint：

  • Sharp Checkpoint。
    • 默认的工作方式，发生在数据库关闭时，将所有的脏页都刷新回磁盘。即参数innodb_fast_shutdown=1
    • 若数据库运行时页使用该技术，则数据库可用性会受到很大限制。
  • Fuzzy Checkpoint。
    • 只刷新一部分脏页，而不是刷新所有脏页回磁盘。

Fuzzy Checkpoint

InnoDB存储引擎中可能发生的几种请求的Fuzzy Checkpoint。

  • Master Thread Checkpoint。每s或每10s刷新，是异步操作。
  • FLUSH_LRU_LIST Checkpoint。为保证LRU列表有100个空闲页可用。若没有，则移除尾端页，若这些页有脏页，则需要进行Checkpoint。
  • Async/Sync Flush Checkpoint。为了保证重做日志的循环使用的可用性。重做日志文件不可用的情况下（写满了），需要强制将一些页刷新会磁盘。
  • Dirty Page too much Checkpoint。脏页数量太多，目的是为了保证缓冲池当中有足够可用的页。

Master Thread工作方式

InnoDB的主要工作都是在一个单独的后台线程Master Thread中完成的，其具有最高的线程优先级别。内部由多个循环组成：主循环、后台循环、刷新循环、暂停循环。Master Thread会根据数据库运行的状态在四个循环间切换。

  • 主循环通过thread sleep实现，因此其计时并不精确，可能延迟。其包含每s或每10s的操作
    • 每s的操作：
      • 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）。
      • 合并插入缓冲（可能，会根据当前1s内IO次数是否小于5次，如果是则可以执行该操作）。
      • 至多刷新100个InnoDB的缓冲池中脏页到磁盘（可能，会去判断脏页比例是否超标）。
      • 如果当前没有用户活动，则切换大background loop（可能）。
    • 每10s的操作
      • 刷新100个脏页到磁盘（可能，判断10s内IO是否小于200次，如果是则执行）。
      • 合并至多5个插入缓冲（总是）。
      • 将日志缓冲刷新到磁盘（总是）。
      • 删除无用的undo页（总是）。
      • 数显100个或10个脏页到磁盘（总是）。
  • 后台循环，若当前没有用户活动，或数据库关闭就会切换到该循环
    • 删除无用的undo页（总是）。
    • 合并20个插入缓冲（总是）。
    • 跳回主循环（总是）。
    • 不断刷新100个页直到符合条件（可能或跳转到flush loop中完成）。
  • 刷新循环。
  • 暂停循环。若flush loop无事可做，则切换到该循环，将Master Thread挂起。

在InnoDB1.1.x时

  • 合并插入缓冲时，数量未innodb_io_capacity的5%
  • 从缓冲区刷新脏页时，数量为innodb_io_capacity
  • 脏页阈值调整到75%
  • 可以动态设置full purge回收undo页的数量

InnoDB1.2.x

在InnoDB1.2.x时，将Master Thread中的刷新脏页操作分离到了一个单独的Page Cleaner Thread当中，进一步提高了系统的并发现。

InnoDB关键特性

插入缓冲

Insert Buffer并不是缓冲区，而是与数据页一样，都是物理页的一个组成部分。

在InnoDB存储引擎中，主键是行唯一标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此插入聚集索引一般是顺序的，不需要磁盘的随机读取。

对于主键自增长的列，在页当中，行记录是按照聚集索引的值进行顺序存放的，一般不需要随机读取另一个页中的记录，因此对于此类插入操作，速度非常快。

并不是所有的主键插入都是顺序的，若主键类为UUID这样，那么插入和辅助索引一样，同样随机

但是一张表上同样有多个非聚集的辅助索引，则此时会产生非聚集且不是唯一的索引

  • 进行插入操作时，数据页的存放还是按主键进行顺序存放
  • 对于非聚集索引叶子结点的插入不再是顺序的了，需要离散地访问非聚集索引的页，由于随机读取的存在而导致了插入操作性能下降
  • B+树的特性决定了非聚集索引插入的离散性

Insert Buffer

对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页当中

  • 判断插入的非聚集索引页是否在缓冲池内，若在，直接插入
  • 若不在，则先放入一个Insert Buffer对象中，假装已经插入到叶子结点
  • 以一定频率和情况进行Insert Buffer和辅助索引页子节点的合并操作。将多次插入合并到一次操作（因为在一个索引页当中），这样就大大提高了对于非聚集索引插入的性能。

需要满足的条件

  • 索引是辅助索引。
  • 索引不唯一。插入缓冲时，数据库就不必去查找索引页判断记录的唯一性。否则为了判断唯一性一定要去磁盘当中查找该页面，此时Insert Buffer就没有了意义。

缺陷

  • 宕机时，可能有很多Insert Buffer没有合并，导致恢复需要较长时间。
  • 在写密集情况下，Insert Buffer会占用过多的缓冲池内存，默认最大可用占用1/2的缓冲池内存。

内部实现

Insert Buffer的数据结构是B+树，在当前版本全局一颗Insert Buffer B+树。

  • 负责对所有的表的辅助索引进行Insert Buffer。
  • 存放在共享表空间，默认是idbata1中。
    • 试图通过独立表空间idb文件恢复表数据，往往导致Check table失败，因为辅助索引还在Insert Buffer中。
    • 通过idb恢复后，还需要进行repair table进行重建辅助索引。

B+树非叶子结点存放的是查询的search key键值：

1554113635740

当一个辅助索引要插入到页，且页不在缓冲池，则需要先构造一个search key，并插入到叶子结点当中。

1554113750574

1554113839689

第5列开始即实际插入记录的各个字段。

Change Buffer

Insert Buffer的升级，1.0.x。对DML操作都进行缓冲，即Insert Buffer、Delete Buffer、Purge buffer。

对一条记录进行update操作：

  • 将记录标记为已删除，Delete Buffer
  • 真正将记录删除，Purge buffer

Insert Buffer Bitmap

启用Insert Buffer后，辅助索引页中的记录可能被插入到Insert Buffer B+树中，为了保证每次的Merge Insert Buffer成功，hi需要有一个特殊的页来标记每个辅助索引页的可用空间，即Insert Buffer Bitmap。

每个Insert Buffer Bitmap用来追踪16384个辅助索引页，即256个区。

1565757877630

Merge Insert Buffer

将Insert Buffer合并到真正的辅助索引中，合并辅助索引可能发生在以下几种情况：

  • 辅助索引页被读取到缓冲池时
  • Insert Buffer Bitmap页追踪到该辅助索引页已经没有可用空间时
    • 至少需要有1/32页的空间
  • Master Thread
    • 每s或每10s进行一次操作，不同之处在于每次进行merge的页数量不同

两次写

提供数据页的可靠性：

部分写失效：当服务器宕机，可能InnoDB存储引擎正在写入某个页到表中，但只写了一部分就宕机。面对部分写失效，重做日志可能没有效果

  • 重做日志中记录的是对页的物理操作，如偏移量800，写’aaaa’记录，如果页本身发生损坏，则进行重做无意义。
  • 在应用重做日志前，用户需要一个页的副本，当写入失效发生时，写通过页的副本来还原该页，再进行重做，即doublewrite。

doublewrite

1554120134521

在对缓冲池的脏页进行刷新时：

  • 并不直接写磁盘，而是通过memcpy函数将脏页先赋值到内存的doublewrite buffer。
  • 通过doublewrite buffer分两次，每次1MB顺序写入共享表空间的物理磁盘。double write页是连续的，顺序写，开销小。
  • 调用fsync函数，同步磁盘，避免缓冲写带来的问题。表空间是不连续的，写入是离散的。

解决部分写失效：

  • InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件。
  • 应用redo log

自适应哈希索引

在生产环境下B+树的高度一般为3-4层。

自适应哈希索引（AHI）：InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。

  • AHI通过缓冲池的B+树页构造，速度很快，而且不需要对整张表构建哈希索引。
  • 会自动根据访问的频率和模式来自动为某些热点页建立哈希索引
  • 要求对这个页的连续访问模式必须是一样的，如where a= ？与where a=? and b=?是两种访问模式
  • 以该模式访问了100次，或页通过该模式访问了N次，N=页中记录/16

异步IO（AIO）

提高磁盘操作性能。在InnoDB1.1.X提供了内核级别的AIO支持。

Sync IO：与AIO对应，每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。

AIO可以进行IO Merge操作，将对多个连续页的IO合并为一个操作，提高IOPS的性能。

刷新临近页

当刷新一个脏页时，InnoDB存储引擎会检测该页所在区的所有页，如果是脏页，那么一起进行刷新。通过AIO可以将多个IO写入操作合并为1个操作。

存在的问题：

  • 是不是可能将不怎么脏的页进行了写入，而该页又很快变成了脏页。
  • 固态硬盘有较高的IOPS，是否还需要这个特性。
  • 因此提供参数innodb_flush_neighbos来控制是否启用该特性,对于固态硬盘具有较高的IOPS建议关闭。

索引与算法

若索引太多，程序的性能可能受到影响；索引太少，对查询性能又会产生影响。

如果知道数据的使用，则一开始就应该在需要处添加索引

InnoDB存储引擎索引概述

  • B+树索引（传统意义上的索引）。
    • 不能找到一个给定键值的具体行，能找到的只是被查找数据行所在的页。然后数据库将页读入到内存，在内存中进行查找，最后得到所要查找的数据。
    • 高度一般在2-4层，即查找某一键值的行记录最多需要2-4次IO，当前一般机械硬盘100IO/s，即需要0.02-0.04s
  • 全文索引。
  • 哈希索引（自适应的，不能人为干预）。

索引

特点：

  • 可以加快数据库的检索速度。
  • 降低数据库插入、修改、删除等维护的速度。
  • 只能创建在表上，不能创建到视图上。
  • 既可以直接创建又可以间接创建。
  • 可以优化隐藏中使用索引。
  • 使用查询处理器执行SQL语句，在一个在表上，一次只能使用一个索引。

优点：

  • 创建唯一性索引，保证数据库表中每一行数据的唯一性。
  • 大大加快数据的检索速度，这是创建索引的最主要原因。
  • 加速数据库表间连接，特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  • 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能。

缺点：

  • 创建索引和维护索引需要时间，随着数据量的增加而增加。
  • 索引需要占用物理空间，除了数据表占用数据空间外，每一个索引需要占用一定的物理空间，如果建立聚簇索引，需要的空间更大。
  • 当队表中数据进行增加、删除和修改时，索引也需要维护，降低数据维护的速度。

分类：

  • 直接创建索引和间接创建索引。
  • 普通索引与唯一性索引。
  • 单个索引和复合索引。
  • 聚簇索引和非聚簇索引。

索引失效：

  • 如果条件中有Or，即使其中有条件带索引也不会使用。
  • 对于多列索引，不是使用的第一部分，则不会使用索引。
  • like查询时以%开头。
  • 如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引。
  • 如果MySQL估计全表扫描比索引块，则不使用索引。

什么情况下适合建立索引：

  • 在经常出现关键字order by、group by、distinct后面的字段，建立索引。
  • 在union等集合操作的结果集字段上，建立索引。
  • 为经常用作查询选择的字段，建立索引。
  • 在经常用作表连接的属性上，建立索引。
  • 考虑使用索引覆盖，对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

密集索引与稀疏索引

密集索引：文件中每个索引码值都对应一个索引值。叶子节点保存的不仅仅是键值，而且保存位于同一行的其他列信息。

稀疏索引：文件只为索引码的某些值建立索引项。仅保存键信息以及地址。

索引调优

索引调优

如何定位并优化慢查询SQL

最左匹配原则

索引建立

索引不是建立越多越好。

  • 数据量小的表不需要建立索引，建立会增加额外的开销。
  • 数据变更需要维护索引，意味更多的维护成本与更大的空间

B+树索引

B+树索引可以分为聚集索引和辅助索引。

聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页，每个数据页都通过一个双向链表来进行连接。

数据页上存放的是完整的每行的记录，在非数据页的索引页中，存放的仅仅是键值以及指向数据页的偏移量，不是一个完整的行记录。

  • 对于主键的排序查找和范围查找非常快
    • 叶子节点的数据就是用户查询的数据，若需要表最后的10位用户，由于是索引是双向链表，则用户可以快速地找到最后一个数据页。
    • 对于范围查询，通过叶子节点的上层中间节点就可以得到页的范围，然后直接读取数据页即可。
  • 对于排序，由于聚集索引默认有序，则不需要进行filesort

辅助索引

叶子节点不包含行记录的全部数据，除了包含键值外，还包含一个书签（告诉InnoDB可以在哪里找到与索引对应的行数据）。

  • 若辅助索引与聚集索引的高度均为3，则一共需要6次IO以得到最终的数据页。

B+树索引的分裂

与一般B+树的分裂不同，需要涉及到并发

当自增的主键插入时，如

1，2，3，4，5（中间值），6，7，8，9

此时插入10需要分裂，则：

  • 1，2，3，4
  • 5（大于等于键值的在右），6，7，8，9，10

因为自增，则P1这个页不会再插入，从而导致浪费

InnoDB存储引擎的Page Header有以下几个部分用来保存插入的顺序信息

  • Page_Last_Insert
  • Page_Direction
  • Page_N_Direction

通过这些决定向左还是向右进行分裂，同时决定将分裂点记录为哪一个

  • 若插入是随机的，则取中间记录分裂
  • 若往同一方向进行插入的记录数量为5，并且目前已经定位到的记录之后还有3条记录。
    • 则分裂点的记录为定位到的记录后的第三条记录
    • 否则分裂点记录就是待插入的记录（自增插入中普遍存在）
    • 定位：在插入时，首先需要定位，定位到的记录为待插入记录的前一条记录

B+树索引的管理

Fast Index Creation

MySQL5.5前，对于索引的DDL操作操作为：

  • 首先创建一张新的临时表，表结构为通过Alter table定义的新结构。
  • 将原表中的数据导入临时表，并删除原表，将临时表重命名为原来的表名。

因此DDL操作需要很久的时间，则使用了FIC的索引创建方式

  • 对于辅助索引的创建，会为创建索引的表加上应该S锁。
  • 对于辅助索引的删除，只需要更新内部视图，并将辅助索引空间标记为可用，同时删除索引定义即可。

但是对于主键，依然需要重建一张表。

Cardinality值

什么是Cardinality

低选择性：对于性别字段、类型字段、地区字段，他们可选择的范围很小。

高选择性：某个字段的取值范围很广，几乎没有重复。这时使用B+树索引才是最合适的。

通过Show Index结果中的列Cardinality来观察索引是否高选择性。

Cardinality：表示索引中不重复记录数量的预估值，Cardinality/n_rows_in_table应尽可能接近1，如果非常小，则需要考虑是否还必要创建这个索引。

InnobDB存储引擎的Cardinality统计

数据库对于Cardinality的统计是通过采样的方法完成。

Cardinality统计信息的更新发生在两个操作中：insert与update。更新Cardinality信息的策略为：

  • 表中1/16的数据已经发生变化
  • stat_modified_counter（某一行数据频繁地变化，每变化一次+1）>2000000000

采样的过程，默认对8个叶子结点采用：

  • 取得B+树索引中叶子结点的数量，记为A
  • 随机取得B+树索引中的8个叶子结点。统计每个页不同记录的个数，记为P1，P2，P8
  • 根据采样信息给出Cardinality的预估值，即SUM（P）*A/8

B+树索引的使用

不同应用中B+树索引的使用

数据库存在两种类型的应用：OLTP、OLAP。

在OLTP当中，查询操作只是从数据库中取得一小部分数据，例如根据订单号获得订单的详情。这种情况下，B+树索引建立后，对该索引的使用也只是通过该索引取得表中少部分的数据，此时建立B+树索引才是有意义的。否则即使建立了，优化器也可能选择不使用索引。

对于OLAP应用，情况比较复杂。但是大部分都需要访问表中大量的数据，根据这些数据产生查询的结果，其多是面向分析的查询，例如月每个用户的消费情况。因此OLAP中索引的添加根据的是宏观的信息，而不是用户ID，但是如果存在复杂查询、多表联结，则索引依然有意义。

联合索引

联合索引是指对表上的多个列进行索引，例如key idx_a_b(a,b)。联合索引也是一颗B+树，不过其键值的数量>=2.

1565847371009

当执行select * from table where a = xx and b = xx则会利用到联合索引。如果只是单独查询一个字段，如果该字段存在单个键值的索引，则不会使用到，因为单个键值则理论上一个页能存放的记录更多。

同时如果使用select * from table where a = xx order by b也会用到联合索引。

  • 使用联合索引后，键值是有序的，通过叶子结点但可用逻辑上顺序读出所有数据。其排序会首先根据第一个字段，然后根据第二个字段。
  • 使用联合索引避免了第二次的排序。因为索引本身在叶子结点已经排序了。

对于联合索引(a,b,c)而言，下面语句同样可用通过联合索引得到结果，因为对于优化器而言，联合索引已经排好序了，因此会用到：

select * from table where a = xxx order by b

select * from table where a = xxx and b=xxx order by c

但是对于下面的语句，依然需要filesort排序，因为索引(a,c)未排序：

select * from table where a = xxx order by c

覆盖索引

覆盖索引即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。由于辅助索引不包含整行记录的所有信息，其大小远小于聚集索引。

辅助索引中的信息包含主键信息，因此下面的语句可用使用一次辅助联合索引来完成查询：

select key2 from table where key1 = xxx

对于统计问题，例如select count(*) from buy_log也会使用辅助索引，因为辅助索引远小于聚集索引，可用减少IO操作。

优化器选择不使用索引的情况

  • 当查询需要整行信息时，索引不能覆盖到我们需要的信息，因此还需要一次书签访问，虽然索引查询到数据有序，但是书签访问是无序的，带来很多的IO操作。
    • 如果访问数据量很小，小于表20%时，会使用辅助索引。
    • 如果很大，则会优先通过聚集索引。如果对于硬盘很自信的话，可用强制使用force index来使用辅助索引

索引提示

Index hint显式地告诉优化器使用索引，其应用场景有：

  • MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢。
  • 某SQL语句可用选择的索引非常的多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。优化器分析Range查询本身就是耗时的操作。

Multi-Range-Read优化

MySQL5.6的MRR优化，其目的是为了减少磁盘的随机访问，并将随机访问转化未较为顺序的数据访问，对于IO-bound类型的SQL查询语句可用带来性能极大的提升。MRR适用于range、ref、eq_ref类型的查询。

优势：

  • MRR使数据访问变得较为顺序，在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
  • 减少缓冲池内页被替换的次数
  • 批量处理对键值的查询操作。

对于范围查询和Join查询：

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

ICP优化

MySQL5.6支持的根据索引进行查询的优化方式，支持range、ref、eq_ref、ref_or_null类型的查询。

之前的索引查询是首先根据索引来查找记录，然后再根据where来过滤记录。ICP之后，MySQL会在取出索引的同时判断是否可以进行where条件的过滤。

哈希算法

InnoDB哈希算法

冲突机制采用链表方式。对于缓冲池页的哈希表，都有一个chain指针指向相同哈希函数值的页。

哈希函数采用除法散列方式。m的取值为略大于2倍的缓冲池页数量的质数。

自适应哈希索引

只能用来搜索等值查找。

全文检索

概述

全文检索是将存储于数据库中的整本书或整篇文章中的任意内容信息查找处理的技术。可以根据需要获得全文中有关章、节、句、词等信息，也可以进行各种统计和分析。

InnoDB 1.2.x开始，支持全文检索，支持MyISAM的全部功能，以及一些其他特性。

倒排索引

全文检索通常使用倒排索引实现。在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。通常使用关联数组实现，有两种表现形式：

  • inverted file index，表现为｛单词，单词所在文档的ID｝
  • full inverted index，表现为｛单词，（单词所在文档的ID，在文档的具体位置）｝

InnoDB全文检索

使用full inverted index，将（单词所在文档的ID，在文档的具体位置）视为一个ilist。拥有两个列：word、ilist，并且在word上设有索引。

倒排索引需要将word存放在一张辅助表当中，在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张辅助表Auxiliary Table，每张表根据word的Latin编码进行分区。辅助表是持久的表，存放于磁盘上。

为了提高全文索引性能，使用了FTS Index Cache（全文检索索引缓存）。是一个红黑树结构，根据（word,ilist）排序，由于是缓存，其刷新类似于Insert Buffer。

全文检索

参考