Mysql技术内幕InnoDB存储引擎

发表于 | 分类于

以下是《MySQL技术内幕(InnoDB存储引擎)第2版》一书的读书笔记。

  • mysql体系结构

  • mysql存储引擎是基于表而不是数据库。
    InnoDB特点：行锁，支持外键，读操作不加锁

  • 缓冲池：数据库读操作首先从磁盘读取页数据导内存缓冲池中，下次读相同数据时判断该页是否在缓冲池中，如果命中直接读取该页，否则读取磁盘上的页。数据库中页的修改首先修改缓冲池中的页然后再以一定频率刷新到磁盘上。可以通过设置innodb_buffer_pool_size来设置缓冲池大小。缓冲池数量可以通过innodb_buffer_pool_instance设置，实现使用多个缓冲池。
    缓冲池结构如下：

    缓冲池中数据通过LRU算法控制，确保最频繁使用的数据在LRU列表最前端。缓冲池中大小默认为16kb。

  • 索引：

    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但是InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，这也就是说数据行和相邻的键值紧凑地存储在一起。

    辅助索引（非聚集索引）叶子节点中并不包含行记录全部数据，而是包含键值与一个指向对应聚集索引的bookmark。当通过辅助索引查找数据是，InnoDB引擎首先遍历辅助索引查找目标目标叶子节点，再根据找到的叶子节点的bookmark（也可能是主键）在聚集索引中查找相应数据。

    InnoDB支持B+索引与哈希索引，其中InnoDB的哈希索引是自适应的，引擎会根据表使用情况为表生成哈希索引，而不能人为干预是否生成哈希索引。需要注意的是B+树缩影并不能找到一个具体的行值，而是查找到数据所在的页，然后把页读入内存，再在内存中查找行得到数据。
    索引使用原则：索引所在的列数据应该区分度较高，同时取出的数据较少。

    索引管理：使用命令 show index from tablename 可以查看某个表的索引情况。例如

    其中优化器会根据Cardinality这个值来判断是否使用这个索引。Cardinality表示索引中唯一值的估计数目，这个值同该表总行数对比，越接近总行数越适合使用索引。

    当sql没有显式指定主键的时候，InnoDB为每行生成一个6字节的ROWID，并以此作为主键。InnoDB后台线程包括以下4种：insert buffer thread、log thread、read thread、write thread；使用 show variables like ‘innodb_%io_threads’; 命令可以查看IO thread 参数。

  • InnoDB存储引擎内存组成如下:

    当使用ssd时，可以将属性 innodb_io_capacity 的值调高至符合磁盘io吞吐量。

    show variables like ‘log_error’ 可以查看默认错误日志的存储位置。

    InnoDB文件构成：数据目录下 ibdata1文件是默认的表空间文件，对于所有基于InnoDB引擎的表数据都会记录到该文件内，通过设置参数innodb_file_per_table可以将每个基于innodb的表都单独产生一个表空间，文件名为表名.ibd。

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_file_per_table';
    +-----------------------+-------+
    | Variable_name | Value |
    +-----------------------+-------+
    | innodb_file_per_table | ON |
    +-----------------------+-------+
    1 row in set (0.02 sec)

    重做日志文件：重做日志文件的主要目的是保证数据的完整性，当数据库崩溃或服务器断电等情况发生时，InnoDB引擎会使用重做日志恢复到崩溃前时刻的数据。
    表结构定义文件：mysql对于数据的存储是以表为基础的，所以每个表都会有与之对应的文件，无论采用哪种引擎，都有一个frm后缀的文件用于记录表结构定义。frm还用来存放视图定义。
    表的数据文件以ibd结尾存储。从物理上看，InnoDB表由共享表空间（mysql数据文件目录下 ib_logfile0、ib_logfile1）、日志文件组（redo文件组）、表结构定义文件组成。

    分区操作：针对于大量数据能够提供查询效率，如果是以主键id分区可以减少查询的范围。此外也可以实现对数据的管理（清除），比如保留三个月有效数据，可以根据时间字段分区，将三个月前的数据清除（直接清除分区）。分区的实现是物理隔离，即每个分区有单独的idb，frm文件。
    === 水平分区的几种模式：===

    • Range（范围） – 这种模式允许DBA将数据划分不同范围。
    • Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。
    • Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是用户函数产生的。
    • Composite（复合模式）
      对于存储过程最适合使用的是分析业务，因为当数据量很大的时候，可以通过区分分区条件来从符合条件的分区获取数据进而缩小查找的数据范围从而提升查找速度。但是对于crud比较频繁的业务，当查询条件不仅限于分区条件的时候，并不能提升查找速度，反而可能因为查找所有分区增加io进而拖慢查找速度。

  • InnoDB支持行级锁并实现了两种标准的行级锁：共享锁与排他锁。对于读使用的是共享锁，对于写使用的是排他锁。但是当事务需要获得排他锁时首先需要等待行上的共享锁全部释放。

  • mysql实例在发生数据变化时不是直接写入到磁盘，而是先写入到内存，当满足一定条件时将内存中缓冲的数据写入磁盘，完成数据的持久化。当数据在内存中已经变化但还没有刷新到磁盘时，数据库实例内存中的页与磁盘中的页数据不一致，（当然，日志已经被写入重做日志文件），这时从内存中读取数据就有可能发生脏读。