zlog

MySQL复习

/笔记#学习
0%
分享
复制链接

索引#

为什么 InnoDB 选择 B+Tree 作为索引的数据结构#

B+Tree vs B Tree

  • B+Tree 只在叶子节点存储数据，而 B Tree 非叶子节点也存储数据，存储相同数量级的情况下，B+Tree 树高更低，磁盘 I/O 次数更少
  • B+Tree 叶子节点用双向链表，适合范围查询，B Tree 无法做到这点
    B+Tree vs 二叉树
  • 数据量增加二叉树树高会越来越高，磁盘 I/O 次数也会更多
    B+Tree vs Hash
  • Hash 等值效率高，但是做不到范围查询

联合索引#

使用联合索引需要遵循 最左匹配原则
比如创建了一个 (a,b,c) 的联合索引，查询条件是以下几种就能匹配上联合索引

  • where a=1
  • where a=1 and b=2 and c=3;
  • where b=2 and a=1;(a 字段在 where 子句里的顺序不重要)
    但如果是
  • where b=2;
  • where c=3;
    就会失效，因为联合索引是先按 a 排序，在 a 相同的情况下按 b 排序，再是 c，如果没有遵循最左匹配原则的情况下就无法使用（b 和 c 是全局无序局部有序的）

利用索引的前提是索引里的 key 是有序的

❓ select * from table where a > 1 and b = 2，联合索引 (a, b) 哪一个字段用到了联合索引的 B+Tree？
☑️ 在 a>1 的记录范围内，b 字段是无序的，所以 b 并没有使用到联合索引
也就是说联合索引的最左匹配原则到 a>1 的时候就停止匹配了

❓ select * from table where a >= 1 and b = 2，联合索引 (a, b) 哪一个字段用到了联合索引的 B+Tree？
☑️ 在 a>=1 的范围内，b 字段是无序的，但对于 a=1 范围内，b 字段是有序的
所以 a>1 不走联合索引，但 a=1 和 a=2 是走联合索引的
那为什么 a>1 and b=2 不走联合索引，而 a>=1 and b=2 走联合索引？
因为 a>=1 and b=2 会被拆成两句 sql：

  • a>1 and b=2，这条 b 不走联合索引
  • a=1 and b=2，这条 ab 都走
    主要是 a>1 的情况下，b 不是有序的，就无法通过二分查找的特性快速找到 b

❓ select * from table where a BETWEEN 2 AND 8 and b=2，联合索引 (a, b) 哪一个字段用到了联合索引的 B+Tree？
☑️ 都用到了，在不同数据库中对于 between...and 的处理有差异，对于 MySQL 中，是包含边界值的，既然包含边界值就跟上一个问题类似，会有 a=2 and b=2 和 a=8 和 b=2 有用到联合索引

❓ select * from table where name like 'j%' and age = 22，联合索引 (name, age) 哪一个字段用到了联合索引的 B+Tree？
☑️ name 字段在联合索引查询是形成的扫描区间是 ['j','k')，j 是闭区间，也就是 name=j and age=22，自然都用到了

综上，联合索引的最左匹配原则在遇到范围查询如>、<的时候就停止匹配，范围查询字段可以用到联合索引，但后续字段无法用到。而对于>=、<=、BETWEEN、like 等前缀匹配的范围查询，不会停止匹配，也就是说等值条件可以一直从左往右推进; 遇到第一个范围条件时，这个范围字段本身可用于索引扫描，但后面的字段就不行了
所以在实际工作当中，建立联合索引需要把区分度大的字段排在前面，区分度大的字段更有可能被 sql 使用到

联合索引排序

sql
select * from order where status = 1 order by create_time asc*

更好的方式是给 status 和 create_time 建立一个联合索引，防止 MySQL 发生文件排序
因为在查询时如果只用到 status 索引后续还需要对 create_time 排序，这时候如果没有联合索引就要用到文件排序
如果使用了联合索引，那么根据 status 筛选出的数据就是按照 create_time 排序的，避免文件排序提高查询效率

索引失效#

索引失效的情况：

  • 当使用左或者左右模糊匹配时，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效
  • 当我们在查询条件中对索引列进行了计算、函数、类型转换操作，会造成索引失效
  • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式向右进行等值匹配，否则会导致索引失效
  • 在使用 where 子句时，or 的部分条件不是索引列，会导致索引失效

❓ select * from user where phone = 13000000001 索引失效
select * from user where id = '1' 索引不失效
☑️ 原因在于 MySQL 数据类型转换规则
字段 phone 是字符串类型，输入是数字，在这种比较的场合下，它会将 phone 字段转换成数字在和右边的数字比较，而不是简单地把右边数字转成字符串
这句 sql 也就等价于 select * from user where CAST(phone AS signed int) = 1300000001，CAST 函数作用在了 phone 字段，而 phone 字段是索引，也就是对索引字段使用了函数，就导致了索引失效
而第二句 sql 是输入是字符串，也就是需要把字符串转成数字，也就等价于 select * from user where id = CAST("1" AS signed int)，函数只是作用在了输入函数上，就可以走索引扫描

索引优化#

  • 前缀索引优化
  • 覆盖索引优化：避免回表，一次索引即包含了本次查询所需要的所有字段，无需查询整行记录
  • 主键索引最好是自增的
  • 防止索引失效

事务#

事务特性（ACID）#

  • 原子性 A：一个事务中的所有操作要么全部完成，要么全不完成
  • 一致性 C：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态
  • 隔离性 I：允许多个并发事务同时对数据库中数据进行读写和修改，每个事务对其他并发事务是隔离的
  • 持久性 D：事务结束后，对数据的修改就是永久的

InnoDB 通过什么来保证这四个特性？

  • 持久性是通过 redo log (重做日志) 来保证
  • 原子性是通过 undo log (回滚日志) 来保证
  • 隔离性是通过 MVCC (多版本并发控制) 或锁机制来保证
  • 一致性是通过上述三者来保证

事务隔离级别有哪些？#

  • 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到
  • 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到
  • 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，这是 MySQL InnoDB 引擎默认隔离级别
  • 串行化（serializable），会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

MVCC 原理（多版本并发控制）#

MVCC 允许多个事务同时读取同一行数据，而不会彼此阻塞，每个事务看到的数据版本是该事务开始时的数据版本。这意味着如果其他事务在此期间修改了数据，正在运行的事务仍然看到的是它开始时的数据状态，从而实现了非阻塞读操作
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，Read View 可以理解为一个数据快照，像相机，定格某一时刻的风景

  • 「读提交」隔离级别是在「每个 select 语句执行前」都会重新生成一个 Read View
  • 「可重复读」隔离级别是执行第一条 select 时，生成一个 Read View，然后整个事务期间都在用这个 Read View

幻读#

对于脏读现象，就要把隔离级别升级到读提交以上隔离级别；对于不可重复读现象，就要将隔离级别升级到可重复读以上隔离级别；而对于幻读，则不建议升级隔离级别为串行化，因为这会导致数据库并发性能差，对于幻读的解决方案有以下两种

  1. 针对 快照读（普通 select 语句），是通过 MVCC 方式解决的
  2. 针对 当前读（select ... for update 等语句），是通过 next-key lock(记录锁+间歇锁) 方式解决了幻读，因为当执行当前读时，会加上 next-key lock，如果有其他事物在 next-key lock 范围内插入一条记录，那么这个插入语句就会被阻塞，无法成功插入，就很好地避免了幻读

锁#

间歇锁#

间歇锁（Gap Lock）是 InnoDB 在索引记录之间的“空隙”上加的锁
锁住的是两个索引值之间的范围，主要作用是 防止其他事务往这个范围里插入新数据从而避免幻读
主要是防止在同一个事务中，两次按相同条件查询，第二次多出了之前不存在的数据

什么情况下使用？#

InnoDB + 可重复读 RR 隔离级别 + 当前读
当前读：

sql
select ... for update;
select ... lock in share mode;
update ... where ...;
delete ... where ...;

不是防修改，是防插入；不是锁行，是锁范围；主要解决幻读

1,10,15,20 有几个间歇锁？
索引上存在 5 个间歇，分别是 (-∞,1)、(1,10)、(10,15)、(15,20)、(20,+∞)，但这只是可被加锁的间歇

场景#

十亿条数据全表扫描时，另一个客户端随机修改一条，会不会阻塞扫描？#

考察#事务隔离级别有哪些？、MVCC、锁

如果是 MySQL InnoDB 里普通 select 全表扫描，一般不会被另一个事务的 update 阻塞，另一个 update 通常也不会被这个普通查询阻塞。原因是 InnoDB 支持 MVCC，普通一致性读是非锁定读，会基于 Read View 读取符合可见性的快照版本，而不是直接对读取到的每一行加锁

InnoDB 的一致性非锁定读会让普通 SELECT 读取某个时间点的快照；如果其他事务正在修改某条记录，查询可以通过 undo log 读取旧版本，所以通常不需要等待对方释放行锁。MySQL 官方文档也把普通 select 不带 FOR SHARE / FOR SHARE 的读取成为 consistent nonlocking read (一致性非锁定读)
但要补充边界条件：
如果语句是 SELECT * FROM table_name;，通常不阻塞更新
但如果语句是 SELECT * FROM table_name FOR UPDATE; 或者 SELECT * FROM table_name LOCK IN SHARE MODE；
那就是锁定读，可能会对扫描到的记录加锁，从而影响并发更新。MySQL 的 locking read，例如 SELECT ... FOR UPDATE，会对读取到的记录加锁，用于后续更新场景

还要区分隔离级别：

  • 在 Read Committed 下，每条语句生成一个 Read View，所以同一个事务里两次查询可能看到不同结果（第一次查询另一个事务还没提交修改，第二次查询的时候另一个事务提交修改了，所以另一个事务修改后的版本对该事务可见，两次结果不同）
  • 在 Repeatable Read 下，事务第一次一致性读会生成 Read View，后续一致性读复用，所以同一事务内多次读结果更稳定

普通快照读不会阻塞随机更新，因为 MVCC 让查询读快照，更新写新版本；但如果是 SELECT FOR UPDATE 这种当前读/锁定读，就可能和更新产生锁冲突。这个问题要看查询类型、隔离级别和存储引擎

范围查找只是双向链表的功劳吗？#

MySQL 使用 B+树索引支持范围查询，不只是因为叶子节点之间有双向链表。更准确地说，是因为 B+树的索引 key 是有序的，非叶子节点可以帮助快速定位到范围查询的起始位置，而叶子节点之间通过双向链表连接则使其可以从起点开始顺序扫描到范围结束

本质是“先定位，后顺序扫描”。树定位起点，链表扫范围，key 有序是前提，链表是加速

反向链接(1)