MYSQL 技术内幕

查看 Mysql 版本：

命令行下: mysql -V

sql 查询: select version();

MYSQL 体系结构和存储引擎

1.定义数据库和数据库实例

数据库：磁盘上的文件或者内存中记录的文件。

实例：数据库后台线程和内存组成。可以操作数据库文件。

集群情况下，一个数据库可以被多个实例使用，多个实例操作数据库文件。

配置加载顺序

都出现的配置参数以最后的为准

mysql --help|grep my.cnf

1582180000844.png

2.MYSQL 存储引擎

​ InnoDB 存储引擎支持事务，其特点是行锁设计、支持外键，并支持类似于 Oracle 的非锁定读，即默认读取操作不会产生锁。InnoDB 通过使用多版本并发控制 (MVCC) 来获得高并发性，并且实现了 SQL 标准的 4 种隔离级别，默认为 REPEATABLE 级别。针对 OLTP （主要是基本的、日常的事务处 理，记录即时的增、删、改、查，比如在银行存取一笔款，就是一个事务交易）

​ MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些 OLAP（复杂的分析操作）

2.1 InnoDB 存储引擎

1583743587944.png

后台线程负责刷新内存池中的数据。此外将修改的数据刷新到磁盘文件，同时保证数据库发生异常 InnoDB 能恢复到正常的运行状态。

2.1.1 后台线程

  • Master Thread

    核心后台线程，刷新数据到磁盘，包括脏页的刷新、合并插人缓冲、UNDO 页的回收

  • IO Thread

    write、read、insert buffer、log thread

  • Purge Thread

    事务被提交后，其所使用的 undolog 可能不再需要，因此需要 PurgeThread 来回收已经使用并分配的 undo 页。

  • Page Cleaner Thread

    将脏页的刷新操作都放人到单独的线程中来完成，其目的是为了减轻原 Master Thread 的工作

2.1.2 内存

  1. 缓冲池

    查询：将页FIX在缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

    修改：首先修改在缓冲池中的页，然后再以一定的频率刷 新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为 Checkpoint 的机制刷新回磁盘。同样，这也是为了提高数 据库的整体性能。

    具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲 (insert buffer)、自适应哈希索引( adaptive hash index)、InnoDB 存储的锁信息(lock info)、数据字典信息(data dictionary) 等。不能简单地认为，缓冲池只是缓存索引页和 数据页，它们只是占缓冲池很大的一部分而已。

1583745883087.png

  1. LRU list、Free List、Flush List

    通常来说，数据库中的缓冲池是通过 LRU (Latest Recent Used，最近最少使用) 算法来进行管理的。即最频繁使用的页(16k)在 LRU 列表的前端，而最少使用的页在 LRU 列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放 LRU 列表中尾端的页。

    Innodb 优化后：LRU 中位插入策略，插入到末尾 37% 约 正向 5/8 左右，可配置。

    数据库刚启动时，LRU list 为空，所有的页存放在 Free list 中，申请空闲页。

    压缩页：减少空间，降低 IO，针对读操作多，SSD 昂贵，很有用。压缩后页大小小于 16k，称为脏页（和磁盘页大小不一致）

  2. 重做日志缓存

    Master Thread 每秒、每个事务提交、日志缓存剩余空间小于一半都会刷新到外部磁盘的重做日志文件中。

  3. CheckPoint (检查点) 技术

    将缓冲池中脏页的数据刷回到磁盘，每次刷新多少页到磁盘，每次从哪里取脏页，LSN (Log Sequence Number) 标记版本。

2.1.3 Insert Buffer

指的不是内存中的 insert buffer，这个是和数据页一样的物理页。数据结构是一棵 B+ 树，提升性能。

InnoDB 存储引擎开创性地设计了 Insert Buffer，对于非聚集索引的插人或更新操作, 不是每一次直接插人到索引页中，而是先判断插人的非聚集索引页是否在缓冲池中，若在，则直接插人；若不在，则先放人到一个 Insert Buffer 对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge (合并)操作，这时通 常能将多个插人合并到一个操作中(因为在一个索引页中),这就大大提高了对于非聚 集索引插人的性能。

简单理解，插入数据时先插入得到 Inser Buffer 里面，再易一定频率刷到磁盘。针对的是非聚集索引（非唯一，唯一的需要在跑一次查询无法减少 IO）的插入和更新。缺点：极端情况下，宕机后恢复大量的 Insert Buffer到实际的非聚集索引中要很久。

2.1.4 两次写 （Double Write）

如果脏页写入磁盘过程中宕机，可能数据 16k 写了 4k，也可能数据文件损坏（redolog 也无法恢复），这些情况下数据就丢失了。

写磁盘前先备份页的副本，失败由副本恢复。保证了数据的可靠性。

3 文件

参数文件、日志文件、socket 文件、pid 文件、Mysql 表结构文件、存储引擎文件

3.1 参数文件 my.cnf
3.2 日志文件

逻辑日志：可以简单理解为记录的就是sql语句。

物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

  • binlog 归档日志

    使用场景：主从复制、通过工具 mysqlbinlog 数据恢复

    mysql 的逻辑日志，通过配置 max_binlog_size 设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。sync_binlog 参数控制biglog的刷盘时机，(0:自动，1:每次事务，N:每 n 个事务)

    日志格式： STATMENT(仅纪录修改)、ROW(每行纪录)和MIXED(混合，有限纪录行，不行就row)

  • redo log

    由于 Innodb 是以页为单位进行磁盘交互，一个事务可能跨很多页，不可能每次都 flush 缓冲池的内容到磁盘页中。redo log 大小是固定的，采用循环写的方式纪录，适用于崩溃恢复（crash-safe）

  • undo log

    undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

    原子性底层就是通过undo log实现的。

5.索引与算法

  • 二叉查找树

    左子树的键值总是小于根的键值，右子树的键值总是大于根的 键值。因此可以通过中序遍历得到键值的排序输出 2、3、5、6、7、8。

    平均复杂度为以2为底log2n 的对数

1592044713668.png

B+ 树：
  • B+树的非叶子节点是不存储数据的，只存储索引，数据全部存储在叶子节点上。
  • 叶子节点之间使用指针连接，提高区间访问效率。如果我们要进行范围查询，可以轻松通过B+树叶子节点之间的指针进行遍历，减少了不必要的磁盘 IO。
  • 数据页都通过一个双向链表来进行链接。

下面是一棵高度为 2 的 B+ 树，每页存放 4 条记录。上面一层叫 leaf page叶子页，下面的是index page索引页，插入删除操作都有可能对 leaf page 和 index page 进行拆分。 B+树的插入与删除

1598076649896.png

B+树索引
  1. 聚集索引

    聚集索引 (clustered index) 就是按照每张表的主键构造一棵 B+ 树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。需要注意的是，这里根据主键构造的也就是有顺序的意思，但是并不是物理上的有序，而是逻辑上的有序。

  2. 辅助索引（非聚集索引）

    说白了就是聚集索引的索引，对表的其他字段构建索引，数据页指向主键索引，查找时根据索引找到需要数据的主键，再通过主键索引来找到完整的行记录。每个表可以有多个辅助索引。

    举例来说，如果在一棵高度为 3 的辅助索引树中查找数据，那需要对这棵辅助索引树遍历 3 次找到指定主 键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次査找，最终找到一 个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页。

  3. 联合索引

    联合索引是指对表上的多个列进行索引，联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

    最左缀匹配原则

    建立索引 （a，b，c，d），根据最左匹配原则，查询条件中 where 可以出现 a、ab、abc、abcd的可以使用上该索引，和查询条件中的顺序无关，即dcab、abcd都一样。

​

6.Cardinality 值

并不是在所有的查询条件中出现的列都需要添加索引。一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、 地区字段、类型字段，它们可取值的范围很小，称为低选择性。如 SELECT FROM student WHERE sex='M' 按性别进行查询时,可取值的范围一般只有M、F。如果某个字段的取值范围很广，几乎没有重复，即属于高选择性。

7.强制使用某索引

from 后面加上 FORCE INDEX(indexName) 指定使用某个索引

SELECT * FROM picc_old_ops FORCE INDEX(PRIMARY) WHERE class_id = '402880ea4313da4c014313da4cf70000'
上次更新时间: 2024/5/7 05:59:02

← Oracle试题→