Learn►Coding►MySQL

MySQL 学习笔记 InnoDB存储引擎篇

  1. 1. 连接
  2. 2. 线程
  3. 3. 内存
  4. 4. 文件
  5. 5. 日志文件
  6. 6. 分区
  7. 7. 索引
  8. 8. 锁
  9. 9. 事务
  10. 10. 主从数据库​

数据库的应用分为两类：
OLTP（在线事物处理）：博客，电子商务，网络游戏等。分区要小心
OLAP（在线分析处理）：数据仓库，数据集市。分区可提高查询性能

对于一张大表，一般的B+树需要2~3次的磁盘IO。

连接

TCP/IP，命名管道和共享内存，Unix域套接字。

线程

默认情况下InnoDB有13个后台线程：

  • 10 个 IO thread（1 insert buffer, 1 log, 4 read, 4 write）（innodb_read_io_threads & innodb_write_io_threads 参数）
  • 1 个 master thread
  • 1 个锁监控线程
  • 1 个错误监控线程。

master thread：主循环(loop)，后台循环(background loop)，刷新循环(frush loop)，暂停循环(suspend loop)。

内存

缓冲池，重做日志缓冲池，额外的内存池。

将数据库文件按页读取到缓冲池，然后按 LRU 算法来保留在缓冲池中的缓存数据。
如果数据库文件需要修改，总是首先修改在缓存池中的页（脏页），然后按照一定的频率将脏页刷新到文件。
缓冲池中的数据页类型：索引页／数据页／undo页/插入缓存/自适应哈希索引/锁信息/数据字典信息 等。

文件

MySQL通用：参数文件，日志文件，套接字文件，pid文件，表结构定义文件（.frm）。
InnoDB文件：重做日志文件，表空间文件。

日志文件

错误日志
慢查询日志：记录运行实践超过long_query_time阀值的SQL语句。使用mysqldumpslow分析。
查询日志
二进制文件：恢复/复制（主从数据库**）**，通过mysqlbinlog工具查看。
binlog_format参数：

  • STATEMENT：日志的逻辑SQL语句；
  • ROW：记录表的行更新情况。此时可以将InnoDB的事务隔离基本设定微READ COMMITTED，以获得更好的并发性；
  • MIXED：默认为STATEMENT，当有（存储引擎微NDB / 使用了不确定函数 / 使用了INSERT DELAY语句 / 使用了用户定义函数 / 使用了临时表）时为ROW。

表 B+树索引组织
默认主键：

  • 非空唯一索引；
  • 自动创建一个6个字节大小的指针。
    表空间（tabespace） —> 段（segment）—> 区（extent）—> 页（page）

MySQL官方手册中定义的65535（字节）长度是指所有VARCHAR列的长度总和。
对于多字节字符编码的CHAR数据类型的存储，InnoDB在内部将其视为是变长的字符。（和VARCHAR基本没区别）
B+树索引只能找到记录所在的页，数据库吧页载入内存，然后通过Page Directory再进行二叉查找。

数据完整性：

  • 实体完整性
  • 域完整性
  • 参照完整性
    InnoDB提供了5种约束：
  • Primary Key
  • Unique Key
  • Foreign Key(参照完整性)
  • Default
  • NOT NULL

MySQL的外键是即时检查的，可以再导入数据时忽视外键的检查：SET foreign_key_checks = 0;

分区

MySQL支持水平分区，分区是局部分区索引，一个分区中既存放了数据又存放了索引。

  • RANGE ： PARTITION BY RANGE + VALUES LESS THAN 主要用于日期列的分区（YEAR() / TO_DAYS() / TO_SECONDS() / UNIX_TIMESTAMP()）
  • LIST ： PARTITION BY LIST + VALUES IN 分区列的值是离散的，只能是定义的值（1,3,5,7,9）（2,4,6,8,10）
  • [LINEAR] HASH ： PARTITION BY HASH (expr) 目的是将数据均匀地分布到预先定义的各个分区中，只需指定一个列值或表达式，以及指定分区数量，MySQL将自动完成(MOD)
  • [LINEAR] KEY ： PARTITION BY KEY 和HASH相似，但使用MySQL提供的函数进行分区
  • [RANGE | LIST] COLUMNS ： 可以直接使用非整形的数据进行分区，更好的RANGE | LIST
  • 子分区（SUBPARTITION） ： 在 RANGE 和 LIST 上再进行 HASH 或 KEY 分区
    RANGE 的 NULL值会放入最左边的分区；LIST必须显示指出放在哪个分区

HASH 和 KEY 的任何分区函数都会将含有NULL值的记录返回为0

索引

B+树 ： **balance ，B+树由B树和**索引顺序访问方法（ISAM, Indexed Sequential Access Method）演化**而来。所有记录都在叶节点。 B**+树索引并不能找到一个给定键值的具体行，只能找到被参照数据行所在的页。然后数据库把页读入内存，再在内存中二分查找。

  • 聚集索引：按照每张表的主键构造一颗B+树，并且叶节点存放着整张表的行记录数据。每张表只能有有一个聚集索引
  • 辅助（非）聚集索引：叶节点除了包含键值意外，每个叶级别中的索引行中还包含了一个书签，指向相应行数据的聚集索引键。
  • 联合索引：键值的数量大于等于2，可以对第二个键值进行排序。
    对于索引的添加或者删除操作，MySQL数据库是先创建一张新的临时表，导入数据，删除原表，更名为原表。

某个字段的取值范围很广，几乎没有重复，既高选择性**，直冲表中取出很少一部分行时(20%)，使用B+树索引才有意义。**

自适应哈希索引：数据库中使用链接法解决碰撞，使用除法散列函数 h(k)=k mod m。对于字典类型的查找非常快速。

锁

  • 共享锁（S）：允许事物读一行数据。
  • 排他锁（X）：允许是ushanchu或者更新一行数据。

MVCC（多版本并发控制）：一致性的非锁定读操作：如果读取的行正在自行DELETE/UPDATE操作，此时读取操作不会等待行上锁的释放，而去读取行的一个快照数据（Undo段），快照可以有多个版本。
SELECT的两种加锁操作（必须在事务中）：

  • SELECT…FOR UPDATE：对读取的行记录加一个X锁。
  • SELECT… LOCK IN SHARE MODE：对读取的行记录加一个S锁。
    锁问题：
  • 丢失更新
  • 脏读：在缓冲池中被修改的数据，并且还没有提交。违反隔离性
  • 不可重复读：一个事务两次读之间被另一个事物修改了。违反一致性

发生死锁后，InnoDB会马上回滚一个事务。

事务

ACID：隔离性由锁实现。原子小/一致性/持久性通过redo和undo实现。

  • redo（重做日志文件）：记录了事务的行为，可以通过其进行“重做”。
  • undo：存放在共享表空间undo段：撤销事务。
    分布式事务：允许多个独立的事务资源参与一个全局的事务中。要么都提交，要么都回滚。
  • 资源管理器
  • 事务管理器
  • 应用程序

不好的事务习惯：

  • 在循环中提交：中途发生错误时，如何回滚前面提交的？
  • 使用自动提交
  • 使用自动回滚

主从数据库​

异步实时进行二进制日志重做。

  1. 主数据库把数据更新记录到二进制日志中。
  2. 从服务器把主服务器的二进制日志拷贝到自己的中级日志中。
  3. 从服务器重做中级日志中的实践，把更新应用到自己的数据库上。
    从服务器：两个线程：
  • I/O线程：负责读取主服务器的二进制日志，将其保存为中级日志。
  • SQL线程，复制执行中继日志。

功能：

  • 数据分布：在不同的数据中心之间实现数据的拷贝。
  • 读取的均衡负载：通过简历多个从服务器，可将读取平均地分布到这些从服务器中。一般通过DNS的Round-Robin和Linux的LVS功能实现负载平衡。
  • 数据库备份。
  • 高可用性和故障转移