MySQL丁奇45讲(上)
基础篇
基础架构:一条SQL查询语句是如何执行的
- Mysql可以分为Server层和存储引擎层两部分
- Server层包括连接器、查询缓存、分析器、优化器、执行器，以及所有的内置函数。所有垮存储引擎的功能，比如存储过程、触发器、视图等
- 存储引擎层负责数据存储和提取，架构模式是插件式，默认引擎是InnoDB
- 客户端空闲太久，连接器会自动将它断开，这个是由参数wait_timeout控制的，默认8小时
- 长连接需要定期断开重连或者使用MySQL5.7版本之后的mysql_reset_connection重新初始化连接资源，避免MySQL内存占用太大
- 查询缓存的失效非常频繁，不建议使用，MySQL8之后查询缓存功能被删掉
- 分析器首先做词法分析，然后做语法分析
- 优化器在表中有多个索引的时候决定使用哪个索引；或者一个语句有夺标关联的时候决定各个表的连接顺序
- 执行器先判断权限
日志系统:一条SQL更新语句是如何执行的
- 更新流程设计redo log(重做日志)和binlog(归档日志)两个重要的日志模块
- WAL全称Write-Ahead Loggin，更新时先写日志，更新内存，再写磁盘。在适当的时候将日志中的操作更新到磁盘
- InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB
- crash-safe能力，有了redo log可以保证即使数据库发生异常重启，不会丢失之前提交的记录
- redo log是InnoDB存储引擎特有的日志，binlog是Server层的日志
- redo log是物理日志，binlog是逻辑日志，记录语句的原始逻辑
- redo log空间固定会用完，binlog是追加写，不会覆盖以前的日志
- 更新时，先写入redolog处于prepare阶段，然后写binlog，最后提交事务，redolog处于commit状态
- 如果没有两阶段提交，redolog或binlog将无法强一致
- innodb_flush_log_at_trx_commit参数建议设置成1，表示每次事务的redolog都直接持久化到磁盘，保证数据不丢失
- sync_binlog参数建议设置成1，表示每次事务的binlog都持久化到磁盘，保证MySQL异常重启binlog不丢失
事务隔离:为什么你该了我还看不见
- 事务四大特性：A(原子性) C(一致性) I(隔离性) D(持久性)
- 多个事务同时执行，可能出现脏读、不可重复读、幻读的问题
- 为了解决以上问题提出了隔离级别，读未提交、读已提交、可重复读、串行化
- 读未提交：一个事物还没提交，做的变更能被别的事务看到
- 读已提交：一个事务提交之后，做的变更才能被别的事务看到
- 可重复读：一个事务在执行过程中看到的数据始终一致，做的变更对其他事务也是不可见的
- 串行化：读写加锁，执行串行
- 通过参数transaction-isolation控制
- 读已提交和可重复读通过创建视图实现
- MySQL默认隔离级别是可重复读
- MySQL中每条记录在更新的时候同事记录一条回滚操作，通过回滚操作可以得到前一个状态的值。这样同一条记录在系统中存在多个版本，就是数据库的多版本并发控制(MVCC)
- 回滚操作在系统判断没有事务再需要用到时会删除
深入浅出索引(上)
- 索引的出现是为了提高查询效率
- 索引的实现由哈希表、有序数组和搜索树
- 哈希表适合等值查询
- 有序数组在等值查询和范围查询场景中的性能非常优秀，但是更新数据成本高
- 搜索树考虑到磁盘读取使用N叉
- 数据库底层存储的核心就是基于这些数据模型
- 索引是在存储引擎层实现的，所以没有统一的索引标准
- InnoDB引擎使用B+数索引模型，每一个索引在InnoDB中对应一棵B+树
- 根据叶子节点内容分为主键索引和非主键索引
- 主键索引的叶子节点存储整行数据，也被称为聚簇索引
- 非主键索引的叶子结点存储主键的值，也被称为二级索引
- 索引维护：页分裂和页合并
- 自增主键比字符串更适合。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，数据页容纳的量越多，B+树就越浅
- 当只有一个索引且索引是唯一索引时可以使用业务字段直接做主键，避免两次查询索引树
深入浅出索引(下)
- 查询过程中回到主键索引搜索的过程，称为回表
- 查询结果已经在索引树上提供，称为覆盖索引
- 有时候可以通过建立联合索引用到覆盖索引，去掉回表操作，提高效率。比如建立身份证、名字联合索引来应对通过身份证查询姓名的高频请求
- B+树可以利用索引的最左前缀来定位记录
- 最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
- 当已经有了(a,b)这个联合索引时，一般就不需要单独在a上建立索引
- MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
全局锁和表锁:给表加个字段怎么有这么多阻碍
- 根据加锁的范围，锁可以分成全局锁、表级锁和行锁
- 全局锁 Flush tables with read lock，可以让整个数据库处于只读状态
- 备份可以使用mysqldump，加上参数-single-transaction，在导数据之前启动一个事务，确保拿到一致性视图。只适用于所有表使用事务引擎的库
- FTWRL和set global readonly=true的比较：
- 在有些系统中readonly被用来做其他逻辑，比如判断是主库还是备库
- 在异常处理机制上，FTWRL由于客户端发生异常断开，MySQL会自动释放这个全局锁。
- 表级别锁分为表锁和元数据锁
- 表锁语法：lock read/write
- 元数据锁(MDL)在访问一个表的时候会被自动加上，MySQL5.5引入MDL，在对一个表做增删改查操作的时候加MDL读锁，在对标做结构变更操作的时候加MDL写锁
- 修改表时可以在语句中设定等待时间，如果在指定的等待时间里面拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃
- 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
- 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源，就会导致这几个线程进入无限等待的状态，称为死锁
- 应对死锁的策略：
- 设置锁超时时间innodb_lock_wait_timeout
- 开启死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。参数innodb_deadlock_detect
- 正常情况下采用第二种策略，也是默认开启的，需要额外负担死锁检测消耗的CPU资源
- begin/start tracnsaction 命令不是事务的起点，在执行第一个语句的时候事务才真正启动。
- start transaction with consistent snapshot 可以马上启动事务不用等到语句执行
- M有SQL中的两个视图
- view，查询语句定义的虚拟表。create view ...
- InnoDB实现MVCC中用到的一致性读视图(consistent read view)，用于支持读提交和可重复读隔离级别
- 一致性读视图的实现：事务在更新数据的时候，都会生成一个新的数据版本，并且把tracsaction id赋值给这个数据版本的事务ID。记为row trx_id。旧数据版本保留，并且在新的数据版本中，能够直接拿到它
- 数据表的每一行数据可能有多个版本，每个版本都有自己的row trx_id
- 每个数据版本之间都有一条undo log，如果需要获取较早版本的记录，需要根据当前版本和redo log计算。
- 实现上，InnoDB为每个事务构造了一个数组，存储这个事务启动瞬间，当前活跃的所有事务ID(启动未提交的事务)
- 事务ID数组中的最小值记为低水位，当前系统创建过的事务ID的最大值+1记为高水位。数组和高水位组成了当前事务的一致性视图
- 更新数据都是先读后写，这个读只能读取当前值，称为“当前读”
- 事务隔离级别决定一致性读的数据版本
- 对于可重复读，查询只承认在事务启动前就已经提交完成的数据
- 对于读提交，查询只承认在语句启动钱就已经完成的数据
- 对于当前读，总是读取已经提交完成的最新版本
- 普通索引和唯一索引的查询区别：
- 普通索引查到满足的记录需要继续查找对比下一条记录，直到碰到不满足条件的记录
- 唯一索引因为定义了唯一性，查到满足记录就会停止检索
- 这个不同性能差距微乎其微，因为MySQL读取是按页读取，除非碰到第一条查找的记录在数据页的最后一条记录，一个数据页可存储数据近千个整型字段，概率极低
- change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，否则，在不影响数据一致性的情况下，InnoDB会将这些更新操作缓存在change buffer中。
- 在下次查询需要访问数据页的时候，将数据页读入内存后执行change buffer与这个页相关的操作。或者后台线程会定期执行change buffer，这个过程称为merge
- 对于唯一索引，更新数据需要判断是否违反唯一性约束，这需要将数据页读入内存，不能使用change buffer
- 综上，在更新操作中，唯一索引效率低于普通索引。可能多了随机磁盘访问
- 优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。扫描行数是影响执行代价的因素之一，扫描函数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少
- 扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断
- 一个索引上不同的值越多，区分度就越好，不同值的个数被称之为基数，show index from <table> 可以查看表索引的基数
- show index from t查询的基数是mysql取样统计得到的，并不准确
- analyze table t可以修正表统计的索引基数
- 优化器选错索引的解决方案：
- analyze table修正索引统计信息
- 使用force index强行指定索引
- 增加或删除索引
- 建立索引时关注的是区分度，区分度越高越好。使用前缀索引可能损失区分度
- 前缀索引用不上覆盖索引对查询性能的优化
- 使用较小的空间处理前缀相似度高的字段索引：
- 使用倒叙存储
- 增加hash字段
- 都不支持范围查询
