——mysql架构
- 连接层
- 服务层
- sql接口
- 解析器
- 优化器
- 查询缓存
- 引擎层
- InnoDb 存储引擎三大特性
- 自适应 Hash 索引（Adatptive Hash Index，AHI）：InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据
- Buffer Pool（BP）：为了提高访问速度，MySQL预先就分配/准备了许多这样的空间，为的就是与MySQL数据文件中的页做交换，来把数据文件中的页放到事先准备好的内存中。数据的访问是按照页（默认为16KB）的方式从数据文件中读取到 buffer pool中。Buffer Pool按照最少使用算法（LRU），来管理内存中的页
- 双写缓冲区（DoubleWrite Buffer）：是一个位于系统表空间的存储区域，在写入时，InnoDB先把从缓冲池中的得到的page写入系统表空间的双写缓冲区。之后，再把page写到.ibd数据文件中相应的位置。如果在page写入数据文件的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复；它的主要作用是为了避免partial page write（部分页写入）的问题。因为InnoDB的page size一般是16KB，校验和写入到磁盘是以page为单位进行的。而操作系统写文件是以4KB作为单位的，每写一个page，操作系统需要写4个块，中间发生了系统断电或系统崩溃，只有一部分页面是写入成功的。这时page数据出现不一样的情形，从而形成一个"断裂"的page，使数据产生混乱
- InnoDb 存储引擎三大特性
- 存储层
——基本概念
标识列（自增长列）
- 可以不用手动插入值，系统提供默认的序列值
- 标识列不是必须和主键搭配的，但要求是个key（主键、唯一索引、外键）
- 一个表至多有一个标识列
- 标识列的数据类型只能是数值型
主键和唯一索引的区别
| 保证唯一性 | 是否允许为空 | 一个表可以有多少个 | 是否可组合 | |
|---|---|---|---|---|
| 主键 | √ | × | 至多有一个 | √ |
| 唯一 | √ | √（但只允许一个为null） | 可以有多个 | √ |
存储过程和函数
- 类似 Java 中的方法，一组预先编译好的SQL语句，批处理语句
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少和数据库服务器的连接次数，提高效率
- 一张表一般不超过5个索引
聚簇索引
- 表示数据行和相邻的键值聚簇的存储在一起
- 好处：按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，节省大量的IO操作
- 只有InnoDB存储引擎支持聚簇索引
- 一个表只能有一个聚簇索引，一般情况都是主键
- 为了充分利用聚簇的特性（顺序排列），通常都选择有序id，不建议用无序id（uuid）
——索引
特性
- 是一种数据结构（排好序的快速查找数据结构）
- 目的：提高查找效率
- 索引往往以索引文件的形式存储在磁盘上
- 平常索引都是指B数（多路搜索树）结构组织的索引
- 复合索引优先于单值索引，高并发下倾向创建组合索引
优点：
- 提高数据检索的效率，降低了数据库的IO成本
- 降低数据排序的成本，降低了CPU的消耗
缺点：
- 需要占空间
- 降低更新表的速度（增删改）
- 索引只是提高效率的一个因素，需要花时间研究建立最优秀的索引
结构：
- BTree / B+ Tree 索引
- Hash索引
- 全文索引
- R-Tree索引
检索原理：
- 磁盘块，数据项，指针
- 真实数据值存在于叶子节点，非叶子节点不存储真实的数据，只存储指引搜索方向的数据项
- 数据项的广度增多，深度减少，避免多次IO，通常为3层
——sql 优化
Explain执行计划表头字段
使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句，分析你的查询语句或者表结构的性能瓶颈
- id：操作表的顺序，表的读取顺序
- id不同，id值越大优先级越高，越先被执行
- id相同，顺序执行
- select_type：查询类型
- simple：不包含子查询或者union
- primary：查询包含任何复杂的子部分，最外层则标记为主查询
- subquery：子查询
- derive：衍生表，临时表（derive1，1则为id）
- union：第二个select出现在union后，则标记union
- union result：从union表中获取结果的select
- table：表名
- type：访问类型
- system：表中只有一行
- const：直接通过索引一次就找到，用于比较主键或者唯一索引，匹配一行数据，能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，表中只有一条记录匹配，常见于主键或者唯一索引扫描，常用于联表查询
- ref：非唯一性索引扫描，返回匹配单独值的所有行
- range：只检索给定范围的行，where条件中出现了between、>、<、in
- index：全索引扫描
- all：全表扫描
- 至少达到range级别
- possible_keys：可能应用到这张表的索引
- key：实际使用到的索引
- key_len：索引使用的字节数，显示的值为索引字段的最大可能长度，并非实际使用长度
- ref：具体索引的哪一列，可能的话，会是一个常量
- rows：找到所需要的记录所需读取的行数
- Extra：包含不适合在其他列中显示但十分重要的额外信息
- Using filesort：无法利用索引完成的排序操作，称为“文件排序”，需要优化
- Using temporary：使用临时表保存结果，常见于排序和分组，耗性能
- Using index：表示查询操作中用到 覆盖索引 ，若同时出现Using where，则索引被用来执行索引键值查找，否则，索引只用来读取数据
- 覆盖索引：查询的列被所建的索引完全覆盖（匹配），常见于组合索引
索引优化
多表索引
- 左连接加右表字段索引，右连接加左表字段索引
索引失效
- 最左匹配原则： 建立组合索引（ABC），查询从索引的最左前列开始并且不跳过索引中的列
- 不在索引列上做任何操作（计算、函数、自动/手动类型转换）
- 不能使用索引中范围条件右边的列（B> <），导致C失效
- 尽量使用覆盖索引（只访问索引的查询），减少select *
- 在使用不等于（!= 或者 <>）的时候索引失效
- is null，is not nul
- like 模糊查询
- %只写在右边不会导致索引失效
- Q：like ‘%字符串%’，保证索引还不失效的方法？
- A：利用覆盖索引可解决该问题
- 字符串不加单引号，发生数据类型隐式转换
- 少用or
group by 基本上都需要排序，会有临时表产生
——查询截取分析
流程
- 观察：查看生产的慢SQL情况
- 慢查询的开启并捕获
- explain + 慢SQL分析
- show profile 查询SQL在Mysql服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优
慢查询日志
- 记录在Mysql中响应时间超过阈值时间的语句，具体指运行时间超过long_query_time值的SQL，会被记录到慢查询日志中
- 默认不开启，开启会带来一定的性能影响
- 日志分析工具：mysqldumpslow，通过命令能够获取慢查询日志中条件的sql（访问次数、查询时间等）
小表驱动大表： 当子查询B表数据集小于主查询A表时，用in，否则，用exist
group by：先排序后分组，遵照索引建的最佳左前缀原则
——show profile
- 是mysql提供可以用来分析当前的会话中语句执行的资源消耗情况（CPU、IO），查看sql执行的整个生命周期及分别耗时情况
- 注意Status字段以下值
- converting HEAP to MyISAM：查询结果的太大，内存不够用了往磁盘上搬
- Creating tmp table：创建临时表（拷完数据到临时表，用完再删除，一般发生在group by）
- Copy to tmp table on disk：把内存中临时表复制到磁盘
- locked：锁表
——数据库锁
表锁
- 读锁（共享锁）
- 写锁（排它锁、独占锁）
- 读锁会阻塞写，但不会阻塞读。写锁会把读和写都阻塞
行锁变表锁情况：索引失效的情况下的操作数据未提交，其他会话再进入查询时会被阻塞
间隙锁： 当用 范围条件 而不是相等条件检索数据，请求共享或排他时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”；InnoDb也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）
——主从复制
原理
- master 将改变记录到二进制日志（binary log），这记录过程叫做二进制日志事件（binary log events）
- slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）
- slave 重做中继日志的事件，将改变应用到自己的数据库中，MySQL复制是异步、串行化的
2827
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
