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...
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 ...
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 ORD...
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 |+-----------------------+-------+ 文件空洞空洞：可以被复用但没有被使用的空间，经过大量增删改的表，都会存在空洞 删除 如果删掉...
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种状...
