MySQL · Q & A with Arwen
1. 请求发出的背后
客户端发出sql —> 连接器校验身份 ---> 服务层 --->
-
语法分析
- 词法分析和语法分析是两步。词法分析把字符串拆成token (识别出SELECT、FROM、WHERE这些关键字),语法分析检查这些token的组合是否符合SQL语法规则, 做这件事的组件叫解析器(Parser)。它最终产出一棵语法树(AST)。
-
查询缓存
- 表一次更新缓存全部作废, 写多命中率低, 维护缓存成本大
-
优化器
- 优化器评估的是每种执行方案的IO成本和CPU成本。读多少页数据、 比较多少行,综合算出一个数值,选最小的那个方案。
- 小表接大表
- 假设A表10行,B表1000行。 A驱动B:外层扫10行,每行去B表查一次,查10次。 B驱动A:外层扫1000行,每行去A表查一次,查1000次。 如果被驱动表的连接字段上有索引,每次查的代价差不多。那外层循环次数越 少,总代价越小。 所以小表驱动大表本质上是在减少外层循环次数。
- Explain
代码块收起展开
EXPLAIN SELECT * FROM user WHERE name = '张三';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | user | ref | idx_name | idx_name | 102 | const | 3 | NULL |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
┌───────────────┬────────────────────────────────────────────────────────────────────────────────────┐
│ 字段 │ 含义 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ id │ 查询编号，子查询或UNION时会有多行，id越大越先执行 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ select_type │ 查询类型，SIMPLE=简单查询，SUBQUERY=子查询，DERIVED=派生表 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ table │ 当前行在访问哪张表 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ type │ 访问类型，性能从好到差：const > eq_ref > ref > range > index > ALL │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ possible_keys │ 这条查询可能用到的索引有哪些 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ key │ 优化器最终选了哪个索引，NULL就是没用索引 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ key_len │ 用到了索引的多少字节，联合索引时可以判断用了几个字段 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ ref │ 索引列跟什么比较，const=常量，字段名=关联查询的连接字段 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ rows │ 预估扫描行数，越小越好 │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────┤
│ Extra │ 额外信息，重点关注：Using index（好）、Using filesort（差）、Using temporary（差） │
└───────────────┴────────────────────────────────────────────────────────────────────────────────────┘2. 锁
间隙锁
假设有一张表 t,其中 id 是唯一索引,表里现在有 id 为 5, 10, 15 的三条记录。
当事务A在RR隔离级别下执行 SELECT * FROM t WHERE id = 7 FOR UPDATE; 时,
会加上一个间隙锁 (5, 10)
间隙锁必须要在索引上才生效, 索引是有序的节点列表,
而间隙锁的范围依靠节点来界定, 只有索引才能提供可计算的顺序区间。
记录锁
同样是基于上题的表 t (id 为 5, 10, 15 的唯一索引)。
如果在RR隔离级别下执行 SELECT * FROM t WHERE id = 10 FOR UPDATE;,
实际生效的锁是记录锁,仅锁住id=10这一条记录
意向锁
它是一个表级标记,用于让后续请求表锁的操作能快速判断是否冲突,避免逐行扫描。
- 意向共享锁(IS):事务打算对某些行加共享锁前,先在表上加 IS
- 意向排他锁(IX):事务打算对某些行加排他锁前,先在表上加 IX
事务在给行记录加X锁之前,必须先在表级别加上IX锁 这就是意向锁的定义。
3. 幻读
- 行锁 = Record Lock = 锁具体某条索引记录
- 间隙锁 = Gap Lock = 锁两条记录之间的区间
比如索引里有:
10 20 30
如果只加行锁:
锁住 20
锁住 30
那只是把这两条“点”锁住了。
但:
(20,30)
这个区间还是开的。
别人还能插入 25。
所以范围查询:
代码块收起展开
SELECT * FROM t WHERE id BETWEEN 20 AND 30 FOR UPDATE;如果没有 Gap Lock,第二个事务插入 25,你再查一次就多了一条新记录——幻读出现。/