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
• 综上，在更新操作中，唯一索引效率低于普通索引。可能多了随机磁盘访问

MySQL为什么有时候会选错索引

• 优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。扫描行数是影响执行代价的因素之一，扫描函数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少
• 扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断
• 一个索引上不同的值越多，区分度就越好，不同值的个数被称之为基数，show index from <table> 可以查看表索引的基数
• show index from t查询的基数是mysql取样统计得到的，并不准确
• analyze table t可以修正表统计的索引基数
• 优化器选错索引的解决方案：
  • analyze table修正索引统计信息
  • 使用force index强行指定索引
  • 增加或删除索引

怎么给字符串字段加索引

• 建立索引时关注的是区分度，区分度越高越好。使用前缀索引可能损失区分度
• 前缀索引用不上覆盖索引对查询性能的优化
• 使用较小的空间处理前缀相似度高的字段索引：
  • 使用倒叙存储
  • 增加hash字段
  • 都不支持范围查询