MYSQL读书笔记整理

# MySQL ## MySQL优化 暂时先放个脑图，后期会不断完善。。。 由于图片太大(5M)，未能显示，建议下载看看，贼屌。 [图片地址](https://github.com/asdbex1078/MySQL/blob/master/MYSQL(5.7%E5%90%AB%E9%83%A8%E5%88%868.0%E6%96%B0%E7%89%B9%E6%80%A7).png) ## MySQL存储引擎 (下图概览，脑图已上传，[地址](https://github.com/asdbex1078/MySQL/tree/master/mysql-others/%E8%84%91%E5%9B%BE))  ### InnoDB 1. #### 从MySQL架构到InnoDB架构 1. [MySQL架构](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#mysql%E4%BD%93%E7%B3%BB%E6%9E%B6%E6%9E%84) 2. [InnoDB架构](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#innodb) 3. [InnoDB的多线程模型](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#innodb%E7%9A%84%E4%B8%80%E4%B8%AA%E5%A4%9A%E7%BA%BF%E7%A8%8B%E6%A8%A1%E5%9E%8B) - [Master Thread](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#master-thread---%E6%A0%B8%E5%BF%83%E7%BA%BF%E7%A8%8B) - [IO Thread](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#io-thread) - [Purge Thread](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#purge-thread) - [Page Cleaner Thread](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.0.MySQL%E6%9E%B6%E6%9E%84%E5%88%B0innoDB%E6%9E%B6%E6%9E%84.md#page-cleaner-thread) 2. [InnoDB简介](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.1.0.InnoDB%E2%80%94%E2%80%94%E7%AE%80%E4%BB%8B.md#innodb%E7%AE%80%E4%BB%8B) 3. InnoDB关键特性 - [Insert Buffer——插入缓冲区](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.1.1.InnoDB%E2%80%94%E2%80%94%E5%85%B3%E9%94%AE%E7%89%B9%E6%80%A7.md#insert-buffer---%E6%8F%92%E5%85%A5%E7%BC%93%E5%86%B2) - [Double Write Buffer——双写缓冲区](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.1.1.InnoDB%E2%80%94%E2%80%94%E5%85%B3%E9%94%AE%E7%89%B9%E6%80%A7.md#double-write---%E4%B8%A4%E6%AC%A1%E5%86%99) - [Adaptive Hash Index——自适应哈希索引](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.1.1.InnoDB%E2%80%94%E2%80%94%E5%85%B3%E9%94%AE%E7%89%B9%E6%80%A7.md#adaptive-hash-index---%E8%87%AA%E9%80%82%E5%BA%94%E5%93%88%E5%B8%8C%E7%B4%A2%E5%BC%95) - [AIO——异步IO]() - [刷新临近页](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.1.1.InnoDB%E2%80%94%E2%80%94%E5%85%B3%E9%94%AE%E7%89%B9%E6%80%A7.md#%E5%88%B7%E6%96%B0%E4%B8%B4%E8%BF%91%E9%A1%B5) - [预读](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.1.1.InnoDB%E2%80%94%E2%80%94%E5%85%B3%E9%94%AE%E7%89%B9%E6%80%A7.md#%E9%A2%84%E8%AF%BB) 4. #### InnoDB内存结构 1. [缓冲池 - Buffer Pool](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.2.0.InnoDB%E5%86%85%E5%AD%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94%E7%BC%93%E5%86%B2%E6%B1%A0.md#%E7%BC%93%E5%86%B2%E6%B1%A0) - 为什么会出现 - 是什么 - 架构 - 关键概念 - 数据页 - 怎么做的 - 服务器的内存大小很重要 - 缓冲池中缓存的数据页的类型 - 怎么识别数据在哪个缓冲页中 - 缓冲池实例 - Buffer Pool如何应对高并发场景 - Buffer Pool的初始化 - LRU List - Free List - Flush List - 总结 2. [日志缓冲区 - Log Buffer](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.2.1.InnoDB%E5%86%85%E5%AD%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94log%20buffer.md#log-buffer) - redo log buffer - undo log buffer 3. [变更缓冲区 - Change Buffer](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.2.2.InnoDB%E5%86%85%E5%AD%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94change%20buffer.md) - 简介 - 什么时候合并到数据页 - 在架构中的位置 - 作用 - 组成部分 - 参数配置 - 监控变更缓冲区 - 其他信息 4. [额外内存池](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.2.3.InnoDB%E5%86%85%E5%AD%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94%E9%A2%9D%E5%A4%96%E5%86%85%E5%AD%98%E6%B1%A0.md) 5. #### InnoDB磁盘结构 1. [页逻辑存储结构](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.3.0.InnoDB%E7%A3%81%E7%9B%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94%E9%80%BB%E8%BE%91%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84.md#innodb%E9%A1%B5%E9%80%BB%E8%BE%91%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84) - 页的结构 - 页中插入数据的过程 - COMPACT行格式 - Page Directory - Page Header - File Header - File Trailer - 总结 2. [表 - table](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.3.1.InnoDB%E7%A3%81%E7%9B%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94%E8%A1%A8.md#%E8%A1%A8) 3. [索引 - index](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.3.2.InnoDB%E7%A3%81%E7%9B%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95.md#%E7%B4%A2%E5%BC%95) - 聚簇索引和二级索引 - InnoDB索引的物理结构 - 排序索引的创建 4. [表空间 - TableSpaces](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.3.3.InnoDB%E7%A3%81%E7%9B%98%E7%BB%93%E6%9E%84%E2%80%94%E2%80%94%E8%A1%A8%E7%A9%BA%E9%97%B4.md#tablespaces) - 系统表空间 - 每个表的表空间 - InnoDB内存中对 .ibd 文件的管理 - 数据字典和 .idb 文件的关系 - 通用表空间 - 创建通用表空间 - 撤销表空间 - 临时表空间 5. [数据字典 - Data Dictionary]() 6. #### MySQL文件 1. [参数文件](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.4.0.Mysql%E6%96%87%E4%BB%B6%E2%80%94%E2%80%94%E5%8F%82%E6%95%B0%E6%96%87%E4%BB%B6.md#%E5%8F%82%E6%95%B0%E6%96%87%E4%BB%B6) - 作用 - 参数文件类型 - 参数类型 - 参数文件加载顺序 2. 日志文件 - [错误日志](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.4.1.0.Mysql%E6%96%87%E4%BB%B6%E2%80%94%E2%80%94%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%E4%B9%8B%E9%94%99%E8%AF%AF%E6%97%A5%E5%BF%97.md) - [慢查询日志](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.4.1.1.Mysql%E6%96%87%E4%BB%B6%E2%80%94%E2%80%94%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%E4%B9%8B%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97.md#%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97---slow-query-log) - 作用 - 查看慢日志是否开启 / 慢日志位置 - 满足什么条件会记录到慢日志中 - 测试慢日志记录 - mysqldumpslow的高级用法 - 慢查询日志的输出方式 - [二进制日志](https://github.com/asdbex1078/MySQL/blob/master/mysql-storage-engines/innodb/1.4.1.2.Mysql%E6%96%87%E4%BB%B6%E2%80%94%E2%80%94%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%E4%B9%8B%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97.md#%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97) - 简介 - 参数控制 - 二进制日志的切换 - 删除二进制日志文件 - 相关参数 - 查看二进制日志�