深入理解MySQL
# SQL 优化
# 定位慢sql
- 一种方法是查看慢查询日志
- 另一种方法是 show process 查看正在执行的 SQL
# SQL分析
- explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
- profile：可以清楚了解到SQL到底慢在哪个环节；
- trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。
# 条件字段有索引，但是会破坏索引的场景
- 应该避免隐式转换
- like查询不能以%开头
- 范围查询时，包含的数据比例不能太大
- 不建议对条件字段做运算及函数操作
# 优化大量数据导入
- 一次插入多行的值；
- 关闭自动提交，多次插入数据的 SQL 一次提交；
- 调整参数，innodb_flush_log_at_trx_commit 和 sync_binlog 都设置为0（当然这种情况可能会丢数据）。
# order by 语句的优化
- 通过添加合适索引
- 去掉不必要的返回字段
- 调整参数：主要是 max_length_for_sort_data 和 sort_buffer_size
- 避免几种无法利用索引排序的情况
# 分页查询场景的优化
- 根据自增且连续主键排序的分页查询优化
select * from t1 where id >99000 limit 2;
- 查询根据非主键字段排序的分页查询优化
select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;
# Join语句优化
- 关联字段添加索引
- 小表做驱动表和
- 创建临时表
# MySQL 索引
对于 MySQL 而言，使用最频繁的就是 B+ 树索引，所以我们必须要知道 B+ 树的结构，而 B+ 树是借鉴了二分查找法、二叉查找树、平衡二叉树、B 树的一些思想构建的
# B+树和B数的区别
- 所有叶子节点中包含了全部关键字的信息
- 非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量。
- 各叶子节点用指针进行连接
# 聚集索引和非聚集索引
- 聚集索引
- 根据主键值创建了 B+ 树结构
- 每个叶子节点包含了整行数据
- 非聚集索引
- 根据 a 字段的值创建了 B+ 树结构
- 每个叶子节点保存的是 a 字段自己的键值和主键 ID
# 创建索引的场景
- 数据检索时在条件字段添加索引
- 聚合函数对聚合字段添加索引
- 对排序字段添加索引
- 为了防止回表添加索引
- 关联查询在关联字段添加索引
# 联合索引
联合索引 idx_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 三种索引，称为联合索引的最左原则。 当联合索引前面的字段使用了范围查询，对后面的字段排序使用不了索引排序
- where 条件中，经常同时出现的列放在联合索引中。
- 把选择性最大的列放在联合索引的最左边。
# 为什么MySQL会选错索引？
- 统计信息不准确导致选错索引
优化器控制着索引的选择。一般情况下，优化器会考虑扫描行数、是否使用临时表、是否排序等因素，然后选择一个最优方案去执行 SQL 语句。实际情况是通过统计信息来预估扫描行数。但它的值不一定准确的,可以考虑使用：analyze table xxx; 重新获取统计信息。
- 单次选取的数据量过大导致选错索引
单次选取的数据量过大也有可能导致优化器选错索引，这种时候，可以尝试使用 force index 让 sql 强制走某个索引。
# MySQL锁
# MySQL中锁可分为三类
- 全局锁
当执行 FTWRL 后，所有的表都变成只读状态，数据更新或者字段更新将会被阻塞。
- 表级锁
- 表锁
- 表读锁
对表执行 lock tables xxx read （表读锁）时，本线程和其它线程可以读，本线程写会报错，其它线程写会等待。
- 表写锁
对表执行 lock tables xxx write （表写锁）时，本线程可以读写，其它线程读写都会阻塞。
- 元数据锁
$MK@Z%C.png)
- 表锁
- 行锁
- InnoDB 行锁模式
- 共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁；
- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。
共享锁（S）：select * from table_name where … lock in share mode; 排他锁（X）：select * from table_name where … for update。
- InnoDB 行锁模式
# 死锁出现情况
- 不同线程并发访问同一张表的多行数据，未按顺序访问导致死锁；
- 不同线程并发访问多个表时，未按顺序访问导致死锁；
- RR 隔离级别下，由于间隙锁导致死锁。
# 事务
# 事务四大特性
- atomicity（原子性） ：要么全执行，要么全都不执行；
- consistency（一致性）：在事务开始和完成时，数据都必须保持一致状态；
- isolation（隔离性） ：事务处理过程中的中间状态对外部是不可见的；
- durability（持久性） ：事务完成之后，它对于数据的修改是永久性的。
# MySQL日志
- Redo log：称为重做日志，用于记录事务操作变化，记录的是数据被修改之后的值
- Binlog：记录了所有变更操作，其作用有：恢复、复制、审计等
如果想要数据库达到最安全的状态，可以将 innodb_flush_log_at_trx_commit 和 sync_binlog 都设置为 1。数据突然断电也能保证数据不丢失
# MVCC原理
MVCC， 即多版本并发控制, InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。
MVCC 最大的好处是读不加锁，读写不冲突，极大的增加了 MySQL 的并发性，通过 MVCC，也保证了事务 ACID 中的 I（隔离性）特性。
# 其他
# MySQL 异步复制
- 在主库开启 binlog 的情况下
- 如果主库有增删改的语句，会记录到 binlog 中
- 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中
- 主库给客户端返回 commit 成功（这里不会管从库是否已经收到了事务的 binlog）
- 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中
# MySQL 半同步复制
- 在主库开启 binlog 的情况下
- 如果主库有增删改的语句，会记录到 binlog 中
- 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中
- 从库收到 binlog 后，发送给主库一个 ACK，表示收到了
- 主库收到这个 ACK 以后，才能给客户端返回 commit 成功
- 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中