第一讲:一条SQL查询语句的执行过程

MySQL架构

0d2070e8f84c4801adbfa03bda1f98d9

Server层
  • 连接器: 管理连接,权限验证
  • 分析器:词法分析,语法分析
  • 优化器:索引选择,join顺序选择,执行计划制定
  • 执行器:操作引擎返回结果
存储引擎层
  • 负责数据的存储和提取

第二讲:一条SQL更新语句的执行过程

两种日志

  • redo log: InnoDB引擎特有的的日志系统;大小固定,循环写;物理日志,记录的是数据页做的改动
  • binlog: Server层的日志, 逻辑日志,sql语句的原始逻辑

更新流程(浅绿色框表示在InnoDB内部执行)

2e5bff4910ec189fe1ee6e2ecc7b4bbe

  • redo log 两阶段提交
  • 相关参数设定:
    • innodb_flush_log_at_trx_commit=1表示每次失误的redo log 都持久化到磁盘 - sync_binlog=1表示每次的binlog都持久化到磁盘

第三讲:事务隔离

四种隔离级别

  • 读未提交(read uncommitted):
    • 事务还未提交时能被其他事务看到
  • 读提交(read committed):
    • 事务提交后才能被其他事务看到
  • 可重复读(repeatable read)(默认选项):
    • 事务执行过程中看到的数据和该事务启动时一致
  • 串行化(serializable):
    • 读写冲突的时候,后一个事务必须等前一个事务执行完成才能继续执行

示例

初始化
1
2
3
CREATE DATABASE TEST;
CREATE TABLE T(c int) engine InnoDB;
INSERT INTO T(c) values(1);
对于如下示例,在不同的隔离级别c1/c2/c3的结果分别是什么?
事务A 事务B
start transaction;
select * from t;
start transaction;
update t set c = c + 1 where c = 1;
select * from t;(c1)
commit;
select * from t;(c2)
commit;
select * from t;(c3)
  • read uncommitted : 均为2
  • read committed : c1为1,c2/c3为2
  • repeatable read : c1/c2为1, c3为2
  • serializable : c1/c2为1, c3为2 (事务B update时阻塞)

底层实现逻辑

  • “读未提交”隔离级别下直接返回记录上的最新值
  • “串行化”隔离级别下直接用加锁的方式来避免并行访问
  • “读已提交”事务在每次Read操作时,都会建立Read View
  • “可重复读”在事务第一次Read时建立一个视图,整个事务存在期间都用这个视图,但是update操作时会读当前并更新视图

第四、五讲:索引

索引的常见模型

  • 哈希表: 适合等值查询的场景
  • 有序数组:适用于静态存储,等值查询和范围查询都适用
  • 搜索树

InnoDB的B+索引模型

  • 主键索引(clustered index):叶子节点存有整行数据
  • 二级索引(secondary index): 叶子节点存有主键的值

索引维护

  • 自增主键是追加操作,不会触发叶子节点的分裂
  • 主键长度越小,二级索引的叶子节点就越小,更节省空间
  • 数据页太小会加深层数,太大会增加加载时间和查询时间

重建表

alter table T engine=InnoDB 减少数据空洞

覆盖索引

所查询的数据在索引树上,无需再回表

最左前缀原则

索引(a, b)可用于查询条件有a或a和b的情况

索引下推

对索引中包含的字段先做判断, 减少回表次数,如查询 select * from table where name like "王%" and age = 10, 索引为(name, age),有索引下推时会对索引中的age做判断而非在回表后再判断

其他

  • 主键为(a, b)的表索引c和索引(c, a)等价

第六讲:全局锁和表锁

全局锁

  • 效果: 加上全局锁是的整个服务处于只读状态
  • 加锁命令:

    1
    2
    
    # FTWRL
    Flush tables with read lock
    • 典型使用场景:做全库逻辑备份

    注: - 对于使用InnoDB的库备份是可用 msyqldump --single-transaction - 全库只读也可用set global readonly=true的方式,但这个对超级用户无效

    表级锁

    分类有2种: 表锁和元数据锁(MDL:meta data lock)

    表锁
    • 语法:

      1
      2
      3
      4
      
      # 加锁
      lock tables table_name read/write;
      # 解锁
      unlock tables;
  • 效果:

    • 读锁:其他session写会阻塞,当前session不可写,不可访问其他表
    • 写锁: 其他session读写都会阻塞,当前session不可以访问其他表

元数据锁

  • 获取: 自动获取
  • 类型

    • 读锁:增删改查操作
    • 写锁:变更表结构
  • 互斥性

    • 读锁之间不互斥
    • 读写锁之间、写写锁之间互斥,会发生阻塞

第七讲 行锁

行锁是由引擎层实现的,如MyISAM就不支持

两阶段

  • 执行相应语句时加上,事务结束时释放
  • 尽量将最可能锁冲突的的语句放在事务最后

死锁

  • 参数innodb_lock_wait_timeout 锁超时, 默认50s
  • 参数innodb_deadlock_detect,检测死锁,默认打开,复杂度O(n2),所以尽量控制并发事务量
  • 死锁检测只会在阻塞时才会执行,只会检测依赖的事务

第八讲 视图

68d08d277a6f7926a41cc5541d3dfced

一致性读视图(consistent read view)

  • 用于RC和RR隔离级别的实现
  • begin/start transaction并不会马上启动事务,需要等到第一个执行操作才启动,显式启动可用start transaction with consistent snapshot
  • 每行数据可能有多个版本,每个版本有自己的trx_id(即对应的事务id),旧的版本需要根据当前版本和undo log计算出了
  • 更新数据都是先读后写,读指读当前的值(current read), 如果当前的记录的行锁被其他事务占用,就需要等待
  • 读语句加上lock in share mode或for update 也是当前读
  • 读提交隔离级别下,每个语句执行前都会重新算出一个视图
  • 重复读隔离级别下, 事务启动是会建立一个视图

第九讲 唯一索引和普通索引的抉择

查询过程

  • 数据按页读入内存,InnoDB默认页大小为16KB

更新过程

  • 当数据在内存中时直接更新
  • 当数据不在内存中, 将更新操作缓存在change buffer中,之后再merge, change buffer减少了随机磁盘访问
  • merge的流程

    • 从磁盘读入数据页到内存
    • 依次更新change buffer中相关记录
    • 将内存数据的变更和change buffer的变更写入redo log
  • 唯一索引需要将数据页读入内存中才能判断唯一性,故不能使用change buffer

  • 对于普通索引,如果是写多读少的场景如日志系统,适合change buffer

  • change buffer用的是buffer pool里面的内存,innodb_change_buffer_max_size设置其百分比

  • redo log 会记录在内存数据页更新数据的操作和change buffer的操作;redo log 主要节省的是随机写磁盘的IO消耗,改为顺序写,而change buffer主要节省的是堆积读存盘的IO消耗

  • change buffer的数据会持久化到ibdata系统表里,内存不足或checkpoint的时候会触发落盘

第十讲 Mysql为什么会选错索引

优化器选择索引的逻辑

采样统计预估扫描行情 - 方式:选择N个数据页,统计这些页面的不同值,得到一个平均值,然后乘以这个索引的页数,得到这个索引的基数cardinality,基数越大,表示区分度也好 - 更新: 当变更的数据行数超过1/M,会自动触发重新做索引统计

回表也会被优化器列入考虑因素

analyze table 来解决统计信息不准确的问题

选择异常和处理

  • 使用force index
  • 修改语句,引导MySQL使用我们期望的索引
  • 新建更适合的索引或者删除多余的索引

评论区拾遗

  • where a in (1,3) and b in (2, 4)会转换成 (a=1 and b=2) or (a=1 and b=4) or …

第十一讲 字符串索引

前缀索引

1
alter table SUser add index index2(email(6));

使用前缀索引,定义好长度,可以做到既省空间又不用额外增加太多的查询成本,使用前缀索引可能会损失区分度,需要预设一个可接受的比例

1
2
3
4
5
6
7
--- 查看不同长度前缀的数量
select 
    count(distinct left(filed,4)as L4, 
    count(distinct left(filed,,5)as L5, 
    count(distinct left(filed,,6)as L6, 
    count(distinct left(filed,,7)as L7
from SUser;

使用前缀索引用不上覆盖索引,对应字段只存了前缀部分

其他

  • 如果前缀区分度比后缀区分度低,可倒序存储再建前缀索引提高区分度
  • 可以增加一个int32字段存hash值

第十二讲 MySQL为什么会“抖动”

原因: flush刷脏页

触发场景:

  • redo log写满了
  • 系统内存不足
  • Mysql空闲时
  • Mysql正常关闭时

刷脏页策略控制

  • innodb_io_capacity: 磁盘IO能力,可设成磁盘的IOPS,IOPS可用如下命令测试
1
 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 
  • 刷盘速度参考因素: 脏页比例,redo log写盘速度
  • innodb_max_dirty_pages_pct 脏页比例上限,默认值75%, 计算方式Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
  • innodb_flush_neighbors控制是否把相邻的脏页也顺便一起flush, MySQL8.0默认为0
  • LSN(log sequence number)日志序号 可以用show engine innodb status 查看

第十三讲 表删除

  • 参数innodb_file_per_table控制每个表是否单独存一个文件, 默认为on, 表数据存储在一个以.ibd为后缀的文件中,如果使用drop table, 会直接删除这个文件

  • 删除数据时只是把对应数据标记为已删除,对应位置可以复用,符合范围条件的数据可以复用这个空间
  • 如果整个数据页都标记为删除,可以复用到任何位置
  • 如果相邻的两个数据页利用率都很小,系统会将数据合并到一个页,另外一个数据页被标记为可复用
  • 如果用delete删除整个表,所有的数据页被标记为可复用,但文件不会变小
  • 插入数据可能导致数据页分裂

重建表

  • 重建表可以减少数据空洞(每个页留了1/16给后续的更新用): alter table A engine=InnoDB

2d1cfbbeb013b851a56390d38b5321f0

  • Online DDL(data definition Language)(5.6起重建表的默认流程)

    • 建立一个临时文件,用原始表的记录生成B+树,存储到临时文件
    • 同时把期间原始表的操作记录到一个日志文件中
    • 临时文件生成后,将日志中的操作应用到到临时文件
    • 用临时文件提花原始表的数据文件
  • optimize talbe = recreate table + analyze table(对索引信息做重新统计)

评论区拾遗

  • truntace table t 等于 drop table + create table
  • 重建表时数据页会按90%满的比例来重新整理页数据(10%留给UPDATE使用)

第十四讲 count(*)

实现方式

  • MyISAM: 把表的总行数存在了磁盘,查询的时候直接返回这个数
  • InnoDB:一行一行读取,累计计数
  • show table status能取得估算的总行数

单独实现计数

  • 存redis,高效
  • 存mysql另一张表,持久化,可用事务保证一致性

不同count的区别

  • count(1):引擎遍历整张表,但不取值
  • count(id):引擎层遍历整张表逐行解析数据将id返回给server层,
  • count(字段):引擎层遍历整张表逐行解析数据将对应字段值返回给server层

结论:count(字段)<count(主键 id)<count(1)≈count(*)

第十五讲 日志和索引相关问题答疑

日志相关

两阶段提交过程中crash
1
2
3
graph TD
A(写入redolog 处于prepare状态) --> |时刻A| B(写入binlog)
B --> |时刻B| C[提交事务 处于commit状态]
  • 时刻A crash: binlog未写入,redo log未提交,事务会回滚
  • 时刻B
  • 如果binglog完整则提交事务
  • binlog不完整则回滚
binlog如何验证完整性
  • statement格式:最后会有comit
  • row格式:最后会XID event
  • 5.6.2版本之后还引入了binlog-checksum参数
redo log 和binlog如何关联

通过XID关联

奔溃恢复的时候,按顺序扫描redo log,如果既有prepare又有commit的redo log,就直接提交,如果没找commit,则拿XID找对应的binlog

redo log buffer

一个事务可能有多个操作,操作对应的red log先在redo log buffer中缓存,等执行commit时在写入文件

第十六讲 order by

using filesort表示需要排序,MySQL会给每个线程分配一块内存sort_buffer用于排序

全字段排序

把需要查询的字段一行一行查询出放入sort_buffer,再进行快速排序,如果要排序的数据量小于sort_buff_size,排序在内存中完成,否则会利用磁盘临时文件辅助排序,外部排序一般使用归并排序算法。

rowid排序

max_length_for_sort_data控制用于排序的行数据长度上限,如果超过这个值就只将排序用到的列和主键列放入sort_buffer,在排完序后再回表找到其他需要的字段. 所以会有两次访问主索引数

课后问题: sessionA 最后看到的A是多少

sessionA sessionB
start transaction;
select * from t where id =1; (query result: a=3)
update t set a = 5 where id = 1;
update t set a = 5 where id = 1;
select * from t where id =1;

答: 取决于binlog_row_image,binlog_row_image=minimal时a=3,binlog_row_image=full为5。因为mysql在full时会读出所有字段,更新前会判断是否需要更新

第十七讲 随机消息

order by rand

rand是建一个临时表,每行给一个随机值(0, 1),然后在sort buffer中排序,内存中会用到快排,文件中会用归并排序

1
select word from words order by rand() limit 3;

internal_tmp_disk_storage_engine控制临时表的类型,默认建内存临时表,如果超过了tmp_table_size就会转成磁盘临时表

随机排序法

  • 方法一
1
2
3
select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;
  • 方法二
1
2
3
4
5
6
 select count(*) into @C from t;
 set @Y = floor(@C * rand());
 set @sql = concat("select * from t limit ", @Y, ",1");
 prepare stmt from @sql;
 execute stmt;
 DEALLOCATE prepare stmt;

第十八讲 隐式转换

  • 函数:对字段做了函数操作,就用不上索引,因为可能会破坏索引值的有序性

  • 隐式类型转化,字符串和数字做比较的话,是将字符串转换成数字

1
2
3
4
5
select * from t where str_a = 100;
-- 等同于 select * from t where CAST(str_a as signed int) = 100;

-- 验证
select "12mw23" = 12;
  • 隐式字符编码转换
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- traddelog字符编码为utf8mb4, trade_detaul为utf8
-- tradeid字段有索引
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
-- 其中查询是用不上索引,因为在tradeid上做了隐式字符转换

select * from trade_detail where tradeid=$L2.tradeid.value;/*语句2*/

-- 等同于
select * from trade_detail where CONVERT(tradeid USING utf8mb4)=$L2.tradeid.value

-- 优化
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

-- 原因是tradeid没有了函数操作,即:
select * from trade_detail where tradeid = CONVERT($L2.tradeid.value USING utf8mb4)

第十九讲 常见的阻塞场景

查询长时间不返回

  • 等MDL锁
1
lock table t write;

可以通过查询 sys.schema_table_lock_waits 这张表,我们就可以直接找出造成阻塞的 process id

  • 等flush
1
flush tables t with read lock;
  • 等行锁

查询锁占用信息

1
mysql> select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G

查询慢

  • 没有命中索引
  • 一致性读时 undolog太多,回滚到对应版本耗时长,这种情况会比当前读慢(lock in share mode)