MYSQL
引擎对比
innodb
存储结构
- 表保存在同一数据文件，单表大小受限于操作系统
事务处理
- 支持
锁
- 能提高并发
表锁
行锁
全文索引
- 不支持FULLTEXT，可使用插件，5.6.4后支持FULLTEXT，5.7.6后支持中文
MyISAM
存储结构
- .frm文件存储表定义
- .MYD文件存储表数据
- .MYI文件存储表索引
每张表存在3个文件
可被压缩，存储空间小，索引和数据分开且可以被压缩，内存利用率高
事务处理
- 不支持
锁
- 所有操作都会给表加锁，包括select
表级锁
全文索引
- FULLTEXT
memchar
- 文件存储在内存，容易丢失
InnoDB概述
Server
连接器
- 管理连接的Auth
分发器
查询优化器
表变更模块
表维护模块
复制模块
状态模块
- 选择索引
- 选择基准表
生成执行计划
- DML语句
- DDL语句
- 命中缓存可直接返回查询结构
- 8.0版本后被删除
查询缓存
分析器
日志记录
执行器
- 操作索引返回结果
索引
数据结构
- 多路平衡搜索树
- 只有达到叶子结点才命中
- 叶子节点不存储指针
- 非叶子节点不存储data,只存储key,可以增大度
- 范围查询和等值查询，更新成本较大
特点
数据结构
- 适合于等值查找
- 利用链地址法处理键值冲突
- hashTab加链表
Hash
有序数列
B+树
索引类型
- 索引可以覆盖多个数据列
- 最左前缀原则
- 唯一索引可以保证数据记录的唯一性
- 唯一任务是加快对数据的访问速度
- 是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录
主键索引
普通索引
唯一索引
联合索引
存储方式
- 节点存储的不再是行的物理位置，而是主键值
- 按照数据存放的物理位置为顺序的，叶子节点就是数据节点，对应主键索引，若没有设置主键索引，innodb会添加一个隐藏列成为聚簇索引
聚簇索引
非聚簇索引
回表
- 通过二级索引过滤了一些条件，再通过主键索引回表查询符合条件的全部数据。
- 查询的列的值已经在查询索引树上，不需要回表
在使用二级索引查询时，在索引树上搜索到主键值，在通过主键索引定位行记录
覆盖索引
索引下推
前缀索引
- 前缀索引能有效减小索引文件的大小，提高索引的速度
- MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引
事务
事务特性ACID
- 事务完成后，对数据库数据的所有更新被保存，不能被回滚。
- 同一时间只允许一个事务请求同一数据，不同的事务彼此之间没有任何干扰
- 事务开始前后，数据库的完整性没有被破坏。
- 事务是一个不可分割的整体，事务开始后，要么全部做完，要么全部不做。若执行过程中出现错误，回滚到事务开始状态。
原子性
一致性
隔离性
持久性
隔离级别
- 表锁
也就是一个事务读取范围行数时读取到另外一个事务的新增或者删除的数据。
一般是next-key锁，若检索条件非索引，表锁
- 快照读情况下，mysql通过mvcc来避免幻读。
- 当前读情况下，mysql通过间隙锁加行锁来避免其他事务的新增。
不会出现脏读，但会产生不可重复读，也就是一个事务多次读取同一条数据可能产生不同的数据。因另一个事务提交的修改。主要针对update
不会产生间隙锁与next-key锁，会产生幻读
允许脏读，也就是一个事务可以读到另一个事务修改而未提交的数据。
一般写数据是行锁
读未提交/read-uncommitted
不可重复读/read-committed
重复读/repeatable-read
串行化/serializable
锁
- 在多个事务中，互相持有部分锁且不释放并等待形成。
行锁
间隙锁
next-key锁
- 对索引项加锁，即锁定一条记录
- 对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身
- 锁定一个范围的记录并包含记录本身
允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁。
- 可以通过SELECT * FROM table LOCK IN SHARE MODE 来设置
共享锁
排他锁
死锁
优化相关
指令
- 最后次查询价值，MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）
- 重新利用未使用的空间，并整理数据文件的碎片
字段解析
distinct
firstMatch
Full scan on NULL key
Impossible HAVING
Impossible where
No matching min/max row
Not exists
range checked each record
using filesort
using index
Using index condition
using temporary
using where
- MySQL正在寻找不同的值，因此在找到第一个匹配行后，它将停止搜索当前行组合的更多行
- 半连接去重执行优化策略，当匹配到第一个值后放弃之后的记录搜索。(半连接既当左表找到右表中匹配记录后，即使右表找到多条记录，左表也只返回一条记录)
- 当优化器无法使用索引去访问，作为子查询的一个回退策略优化时发生。
- Having子句总是为false,例如 having 1 < 0
- WHERE子句始终为false，不能选择任何行
- 没有满足条件的行
- 对查询执行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的一行后，不会在上一行组合中检查此表中的更多行
- MySQL发现没有使用好的索引，但是发现在前面的表的列值已知之后，可能会使用一些索引。
- 得到的结果集需要对所有记录进行文件排序
- 仅使用索引树中的信息从表中检索列信息，而不需要进行附加搜索来读取实际行
- 索引下推，使用2级索引检索数据时，需要回表，利用索引下推减少访问表次数
- 需要创建一个临时表来保存结果
- 使用了WHERE条件进行过滤
- 按条件过滤表估计的百分比，与rows相乘得到被过滤出去的行数
- 估算出结果集行数
- 引用到上个表的列
- 使用的索引
- 可能被使用的index
表扫描方式
- 全表扫描，需要优化
- 类似全表扫描。但扫描的是索引树
- 给定范围内的检索，使用一个索引来检查行
- value IN (SELECT key_column FROM single_table WHERE some_expr)
- value IN (SELECT primary_key FROM single_table WHERE some_expr)
- 使用了索引合并优化方法
- 解决子查询中经常使用该联接类型的优化
- 非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找；
- 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- 比较的是PrimaryKey或UniqueIndex,只有一行数据
- Const的特例，一般不出现
system
const
eq_ref
ref
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
all
- 查询被扫描的表
Simple
Primary
Union
dependent union
union result
subQuery
- 没有子查询的情况
- 在有子查询时,最外层的Select
- 联合查询的SELECT
- 第二个或者后面的select语句
- 最终联合后的结果
- 在子查询中最开始的查询
- 执行语句的顺序，ID越大越先执行
id
select_type
table
type
possible_keys
key
ref
rows
filtered
extra
explain
Optimize
Analyze
last_query_cost
常见优化
- HIGH_PRIORITY
- LOW_PRIORITY
- FORCE INDEX强制制定使用某个index
- IGNORE INDEX 忽略某个index
- STRAIGHT_JOIN，一般联表时，mysql按照数据量少的为优先执行
- 使用limit在偏移量很大时,最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。
- 例如:WHERE login_time>? AND age BETWEEN 10 AND 30
- 例如索引Index1（A,B） Index2(A)
- 使用覆盖索引
- 对字段做了函数计算，就用不上索引
- 隐式的类型转换会导致索引失效
- 索引列参与了计算不会走索引
- 小表驱动大表。
- 被驱动表走索引的情况下的时候才考虑用join
- 确保ON和USING字句中的列上有索引,除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引
- rowid 排序相对于全字段排序，不会把所有字段都放入 sort_buffer，所以在 sort buffer 中进行排序之后还得回表查询。在少数情况下，可以使用联合索引+索引覆盖的方式来优化 order by
Count(*)
Count(id)
Count(1)
- 需要把数据从磁盘中读取出来然后累计计数
- 遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加
- InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1” 进去，判断是不可能为空的，按行累加
Conut(*)
order by
join
索引失效
避免重复索引
尽量避免多个范围条件
分页
表执行顺序
制定索引策略
语句执行优先级
控制缓冲
日志
错误日志(error log)
- 记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息
查询日志
- 记录所有执行时间超过long_query_time的所有查询或不使用索引的查询
- 慢查询日志的工具mysqldumpslow
- 在超时时间内完成的查询是一般查询
- 默认是关闭的，建议关闭
一般日志查询（general log）
慢查询日志(slow log)
二进制日志(bin log)
- 记录所有更改数据的语句，可用于数据复制
- 对于事务表来说，一个事务中可能包含多条二进制日志事件，它们会在提交时一次性写入。而对于非事务表的操作，每次执行完语句就直接写入
- 二进制文件查询工具 mysqlbinlog
中继日志(relay log)
- 主从复制时使用的日志
事务日志
- 用来回滚行记录到某个版本。undo log是逻辑日志，根据每行记录进行记录,可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)
与bin log的区别
innodb_flush_log_at_trx_commit刷入log file on disk参数
- binlog 记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。
- redolog记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化
- bin log记录操作的方法是逻辑性的语句.在每次事务提交的时候一次性写入缓存中的日志"文件"(对于非事务表的操作，则是每次执行语句成功后就直接写入)
- redo log记录是数据库中每个页的修改记录，是物理格式上的日志,在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作
- binlog先于redolog被记录
- binlog是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产binlog。
- redo log是innodb层产生的，只记录该存储引擎中表的修改。
- 存储
- 记录时间
- 记录方式
- 记录内容
- 0（延迟刷），redo log buffer不写入os buffer,每秒调用fsync()刷到log file on disk
- 1（实时写，实时刷），log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中
- 2（实时写，延迟刷）， log buffer中的日志写入os buffer，每秒调用fsync()刷到log file on disk
redolog
undo log
缓冲池
缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。
读缓冲
- 当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。
- 页被访问，且在老子表停留时间超过配置阈值的，才进入新子表，防止大量数据访问，淘汰热数据
- 使用LRU算法，但与OS的LRU有一定区别，采用新，老2个链表在存储。新子表的tail与老子表的tail，预读的页只会加入老子表的head并淘汰老子表的tail,若此页被读，则加入新子表的tail，新子表的tail进入老子表并不会被真正淘汰。在老子表待上一定时间才会替换新子表，预防内存污染。
磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。
局部性原理，数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据
预读失效
- 由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效
预读
内存淘汰
污染
写缓冲
- 在写缓冲中记录这个操作，一次内存操作
- 写入redo log，一次磁盘顺序写操作
- 数据读取时，有另外的流程，将数据合并到缓冲池
在缓冲池中
不在缓冲池
- 直接修改缓冲池中的页，一次内存操作
- 写入redo log，一次磁盘顺序写操作
- 从磁盘加载到缓冲池，一次磁盘随机读操作
- 修改缓冲池中的页，一次内存操作
- 写入redo log，一次磁盘顺序写操作
应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能
未有写缓冲
利用写缓冲
参数
- 什么指令会利用写缓冲，all/none/insert/deletes
- 写缓冲占缓冲池比例
- 老生代停留时间窗口
- 老生代占整个LRU链长度的比例
- 缓冲池的大小
innodb_buffer_pool_size
innodb_old_blocks_pct
innodb_old_blocks_time
innodb_change_buffer_max_size
innodb_change_buffering
分布式事务支持
XA
资源管理器
事务管理器
- 用来管理系统资源，是通向事务资源的途径
- 事务管理器是分布式事务的核心管理者。事务管理器与每个资源管理器（resource manager）进行通信，协调并完成事务的处理。事务的各个分支由唯一命名进行标识
二阶段提交协议（两阶段提交主要保证了分布式事务的原子性：即所有结点要么全做要么全不做）
角色
