MySQL -- RR的行锁
本文环境12345678910111213mysql> SELECT VERSION();+-----------+| version() |+-----------+| 8.0.12 |+-----------+mysql> SHOW VARIABLES LIKE '%transaction_isolation%';+-----------------------+-----------------+| Variable_name | Value |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+
加锁规则
基本原则
加锁的基本单位是Next-Key Lock
遍历过程中被访问到的对象才有可能被加锁
等值查询的优化
如果遍历的是唯一索引(聚簇索引)且能等值命中,Next-Key Lock会降级为Row Lock
向右遍 ...
MySQL -- 幻读
表初始化123456789CREATE TABLE `t` ( `id` INT(11) NOT NULL, `c` INT(11) DEFAULT NULL, `d` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;INSERT INTO t VALUES (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
定义与问题定义
幻读:在同一个事务内,前后两次查询同一范围的时候,后一次查询看到了前一次查询没有看到的行
幻读专指_新插入的行_
在RR隔离级别下,普通查询是快照读,是看不到其他事务插入的数据的
幻读仅在当前读时才会出现
解决思路只有行锁假设SELECT * FROM t WHERE d=5 FOR UPDATE;只会在id=5这一行上加X Lock,执行时序如下:
时刻
session A
session B
session C
T1
BEGIN;SELECT * FROM t WH ...
MySQL -- 问题排查
表初始化12345678910111213141516171819CREATE TABLE `t` ( `id` INT(11) NOT NULL, `c` INT(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=1; WHILE (i<=100000) DO INSERT INTO t VALUES (i,i); SET i=i+1; END WHILE;END;;DELIMITER ;CALL idata();
查询长时间等待大概率是表t被锁住了,通过SHOW PROCESSLIST;查看语句处于什么状态
1SELECT * FROM t WHERE id=1;
等MDL执行时序
session A
session B
LOCK TABLE t WRITE;
SELECT * FROM t WHERE id=1;
...
MySQL -- 索引上的函数
结论先行如果对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器会决定放弃走树搜索功能
条件字段函数操作交易日志表123456789CREATE TABLE `tradelog` ( `id` INT(11) NOT NULL, `tradeid` VARCHAR(32) DEFAULT NULL, `operator` INT(11) DEFAULT NULL, `t_modified` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`), KEY `t_modified` (`t_modified`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
123456789101112131415-- 94608000 = 3 * 365 * 24 * 3600-- t_modified : 2016-01-01 00:00:00 ~ 2019-01-01 00:00:00DELIMITER ;;CREATE PRO ...
MySQL -- order by rand
单词表目的:随机选择3个单词
12345678910111213141516171819CREATE TABLE `words` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `word` VARCHAR(64) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;DELIMITER ;;CREATE PROCEDURE wdata()BEGIN DECLARE i INT; SET i=0; WHILE i<10000 DO INSERT INTO words(word) VALUES (CONCAT(CHAR(97+(i DIV 1000)), CHAR(97+(i % 1000 DIV 100)), CHAR(97+(i % 100 DIV 10)), CHAR(97+(i % 10)))); SET i=i+1; END WHILE;END;;DELIMITER ;CALL wdata();
查询语句1SELECT word FROM words ORDER ...
MySQL -- order by
市民信息123456789CREATE TABLE `t` ( `id` INT(11) NOT NULL, `city` VARCHAR(16) NOT NULL, `name` VARCHAR(16) NOT NULL, `age` INT(11) NOT NULL, `addr` VARCHAR(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`)) ENGINE=InnoDB;
查询语句1SELECT city,name,age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;
存储过程12345678910111213DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=0; WHILE i<4000 DO INSERT INTO t VALUES (i,'杭州',concat('zhongmingmao ...
MySQL -- 无过滤条件的count
count(*)实现
MyISAM:将表的总行数存放在磁盘上,针对无过滤条件的查询可以直接返回
如果有过滤条件的count(*),MyISAM也不能很快返回
InnoDB:从存储引擎一行行地读出数据,然后累加计数
由于MVCC,在同一时刻,InnoDB应该返回多少行是不确定
样例假设表t有10000条记录
session A
session B
session C
BEGIN;
SELECT COUNT(*) FROM t;(返回10000)
INSERT INTO t;(插入一行)
BEGIN;
INSERT INTO t(插入一行);
SELECT COUNT(*) FROM t;(返回10000)
SELECT COUNT(*) FROM t;(返回10002)
SELECT COUNT(*) FROM T;(返回10001)
最后时刻三个会话同时查询t的总行数,拿到的结果却是不同的
InnoDB默认事务隔离级别是RR,通过MVCC实现
每个事务都需要判断每一行记录是否对自己可见
优化
InnoDB是索引组织表
聚簇索引树:叶 ...
MySQL -- 空间回收
InnoDB的物理存储
InnoDB表的组成:表结构(frm)+数据(ibd)
MySQL 8.0开始,允许将表结构定义(占用空间很小)放在系统数据表中
控制参数innodb_file_per_table
ON:每个InnoDB表数据存储在一个以**.ibd为后缀的文件中,推荐**
更容易管理,DROP TABLE会直接删除这个文件
OFF:InnoDB表数据存储在共享表空间
DROP TABLE,空间也是不会回收的
123456mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+
文件空洞空洞:可以被复用但没有被使用的空间,经过大量增删改的表,都会存在空洞
删除
如果删掉R4, ...
MySQL -- 字符串索引
场景建表12345CREATE TABLE SUser( id BIGINT UNSIGNED PRIMARY KEY, name VARCHAR(64), email VARCHAR(64)) ENGINE=InnoDB;
查询1SELECT id,name,email FROM SUser WHERE email='[email protected]';
创建索引12ALTER TABLE SUser ADD INDEX index1(email);ALTER TABLE SUser ADD INDEX index2(email(6));
index1
索引长度:整个字符串
从index1索引树找到第一个满足索引值为zhangssxyz@xxx.com的记录,取得主键为ID2
到聚簇索引上查找值为ID2的行,判断email的值是否正确(Server层行为),将该行记录加入结果集
...
MySQL -- flush
脏页 + 干净页
脏页:内存数据页与磁盘数据页内容不一致
干净页:内存数据页与磁盘数据页内容一致
flush:将内存中的脏页写入磁盘
flush – 刷脏页;purge – 清undolog;merge – 应用change buffer
flush过程
触发flushredolog写满
当InnoDB的redolog写满,系统会停止所有的更新操作,推进checkpoint
把checkpoint从CP推进到CP’,需要将两点之间的日志(绿色),所对应的所有脏页都flush到磁盘上
然后write pos到CP’之间(红色+绿色)可以再写入redolog
性能影响InnoDB应该尽量避免,此时所有更新都会被堵住,更新数(写性能)跌为0
内存不足
当需要新的内存页,而内存不够用时,就需要淘汰一些内存数据页(LRU)
如果淘汰的是脏页,就需要先将脏页flush到磁盘
该过程不会动redolog,因为redolog在重放的时候
如果一个数据页已经flush过,会识别出来并跳过
性能影响
这种情况是常态,InnoDB使用buffer pool管理内存
buffer pool中内存页有3种状态
没 ...