ByteCoding

短连接 短连接模式：连接到数据库后，执行很少的SQL后就断开，下次需要的时候再重连 在业务高峰期，会出现连接数突然暴涨的情况 MySQL建立连接的成本非常昂贵 成本：TCP/IP三次握手 + 登录权限判断 + 获取连接的数据读写权限 max_connections max_connections：MySQL实例同时存在的连接数上限 当连接数超过max_connections，系统会拒绝接下来的连接请求，返回：Too many connections 当连接被拒绝，从业务角度来看是数据库不可用 如果机器负载较高，处理现有请求的时间会变长，每个连接保持的时间也会变长 如果再有新建连接的话，很容易触发max_connections的限制 max_connections的目的是保护MySQL的 如果把max_connections设置得过大，更多的连接就会进来，导致系统负载会进一步加大 大量的资源会耗费在权限验证等逻辑上，而已经拿到连接的线程会抢不到CPU资源去执行业务SQL 123456mysql> SHOW VARIABLES LIKE '%max_connect ...

本文环境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 向右遍 ...

表初始化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 ...

表初始化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; ...

结论先行如果对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器会决定放弃走树搜索功能 条件字段函数操作交易日志表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 ...

单词表目的：随机选择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 ...

市民信息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&#x ...

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是索引组织表 聚簇索引树：叶 ...

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， ...

场景建表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层行为），将该行记录加入结果集 ...