声明：以下内容自行整理，但也参考了众多文章与资料，在此感谢！

案例分析

可见性分析

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t(id, k) values(1,1),(2,2);

A：1
B：3

数据修改的诡异现象

begin;

select * from t;
+--------+----+
| id | c |
+--------+----+
| 1  | 1 |
| 2  | 2 |
| 3  | 3 |
| 4  | 4 |
+--------+----+

update t set c=0 where id=c;

select * from t;
+--------+----+
| id | c |
+--------+----+
| 1  | 1 |
| 2  | 2 |
| 3  | 3 |
| 4  | 4 |
+--------+----+

上文中update无法修改的问题，为什么会产生这种情况？

场景1：update之前，另一个事务B中执行update t set c=c+1
- update是当前读，可以读取最新的数据，id不等于c，更新失败
- select是快照读，事务B是处于高水位之后红色部分，对于select的事务不可见
场景2：第一次select前启动事务B，update前事务B执行update t set c=c+1，且提交
- update是当前读，可以读取最新的数据，id不等于c，更新失败
- select是快照读，事务B对于当前事务是活跃的，处于黄色部分，不可见

索引场景分析

CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

非主键索引的叶子节点上会挂着主键，因此：

索引c+主键索引，可以看做是c、a、b
索引ca+主键索引，可以看做是c、a、b，重叠部分合并

由上可以得出，索引c可以等价于ca，保留较小的索引，去除索引ca

重建索引

-- 非主键索引重建
alter table T drop index k;
alter table T add index(k);

-- 主键索引重建方式1
alter table T drop primary key;
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
alter table T add primary key(id);

-- 主键索引重建方式2
alter table T engine=InnoDB;

索引重建：碎片整理可通过索引重建进行
主键索引：
- InnoDB必须有一个主键索引，未主动声明时，Mysql会默认给创建一列6字节的整数列
- 自增只能定义在索引列上，因此直接删除自增列上索引异常：1075
- 主键索引重建方式1中删除并重建的方式，其实相当于创建了两次索引，建议采用方式2

大批量删除数据

-- 第一种，直接执行 
delete from T limit 10000;

-- 第二种，在一个连接中循环执行 20 次 
delete from T limit 500;

-- 第三种，在 20 个连接中同时执行 
delete from T limit 500

第一种：长事务，索引时间较长，且可能导致主从延迟
第三种：人为造成锁冲突

IS NULL、IS NOT NULL是否走索引

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |       93536 | NULL     | NULL   |      | BTREE      |         |               |
| t     |          1 | a        |            1 | a           | A         |       93536 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> explain select * from t where a is null;
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t     | ref  | a             | a   | 5       | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
1 row in set

mysql> explain select * from t where a is not null;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | t     | ALL  | a             | NULL | NULL    | NULL | 93536 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set

is null使用了索引，is not null未使用索引。那么，是否可以得出结论：is null走索引，is not null不走索引呢？

对于二级索引来说，索引列的值可能为NULL，对于索引列值为NULL的二级索引记录来说，它们被放在B+树的最左边。由此，可以看出SQL中的NULL值认为是列中最小的值。因此，is null使用了索引，is not null由于需要查询所有值，最终还需要回表到主键索引，因此，直接使用全部扫描。

上述现象的本质还是优化器对索引成本的估算，如果上述案例中a is NULL的数量达到一定的程度，回表成本增加，可能就会被优化器放弃，改走全部扫描。

同理，!=、not in是否走索引，都是同样的原理。

select count()

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

MyISAM 引擎：表的总行数存在磁盘上，没有where条件的情况下，会直接返回这个数，效率很高；
InnoDB 引擎：由于MVCC，不同事务中返回多少行是不确定的，需要把数据一行一行地从引擎里面读出来，然后累积计数。因此，优化器会找到最小的索引树来遍历

不同count的用法对比：

count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加
count(*)：MySQL专门进行了优化，不取值，等价于count(1)，建议优先使用
count(主键id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加
count(字段)：
- 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
- 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加

order by工作方式

CREATE 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;

select city,name,age from t where city='杭州' order by name limit 1000;

--  MySQL中用于控制排序行数据长度的一个参数，如果单行的长度超过这个值，改用rowid排序
SET max_length_for_sort_data = 16;

全字段排序	rowid 排序

sort_buffer_size：如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。反之，利用磁盘临时文件辅助排序
rowid 排序多访问了一次表 t 的主键索引，因此，MySQL会优先选择全字段排序，可以通过修改参数max_length_for_sort_data让优化器选择rowid排序算法，默认16，当要查询的单条数据全文本长度大于16采用rowid排序
对于需要使用临时表进行排序时，需要看临时表是内存临时表，还是磁盘临时表，由tmp_table_size决定，默认16M。若是内存临时表，回表在内存中完成，不会访问磁盘，优先选用rowid排序

优化方案：使数据本身有序

alter table t add index city_user(city, name);

-- 利用索引中相同city下name有序性
select city,name,age from t where city='杭州' order by name limit 1000;

-- 进一步优化，使用覆盖索引，减少回表
alter table t add index city_user_age(city, name, age);

-- city多值情况下，又该如何处理？ sql拆分
select * from t where city in ('杭州'," 苏州 ") order by name limit 100;

group by优化

CREATE TABLE t1 (
	id INT PRIMARY KEY,
	a INT,
	b INT,
	INDEX (a)
);

select id%10 as m,count(*) as c from t2 group by m;

首先分析下group by语句的执行计划，如下：

-- 此处使用MySQL 8.0+，已取消group by隐式排序，否则Exta中还会多一个Using filesort
mysql> explain select id%10 as m,count(*) from t group by m;
+----+-------------+-------+------------+-------+---------------+-----+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows   | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+--------+----------+------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | PRIMARY,a     | a   | 5       | NULL | 998529 |      100 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+--------+----------+------------------------------+

只用到了主键id字段，可以使用覆盖索引，因此选择了索引a，不用回表
获取主键id，id%10后放入临时表，如果存在，计数列加1
MySQL 8.0前group by支持隐式排序，无排序需求时，建议加上order by null

如何优化？

适合创建索引，直接加索引

-- 此处举例中分组字段是不存在，新增一个，并创建索引
-- 实际场景中可能会有已有分组字段，但未加索引，加上索引即可
mysql> alter table t1 add column z int generated always as(id % 100), add index(z);

-- 使用索引字段进行分组排序
mysql> explain select z as m,count(*) from t1 group by z ;
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | z             | z   | 5       | NULL | 1000 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+

索引是有序的，顺序扫描，依次累加，统计完一个再统计下一个，不需要暂存中间结果，也不需要额外排序。如果需要倒序排列，Backward index scan，从后扫描索引即可
多个分组字段，建议使用联合索引

不适合创建索引，数据量不大，走内存临时表即可。如果数据量较大，使用SQL_BIG_RESULT告诉优化器，放弃内存临时表，直接磁盘临时表

mysql> explain select SQL_BIG_RESULT id%10 as m,count(*) from t1 group by m ;
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a,z   | a   | 5       | NULL | 1000 |      100 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------------+

通过执行计划可以看出实际并未使用临时表，为什么呢？

因此，磁盘临时表是B+树存储，存储效率不高，从磁盘空间考虑，直接使用数组存储，流程如下：

直接把分组值m放在sort_buffer中，空间不足使用磁盘临时文件辅助排序，这样就得到一个有序数组。在有序数组上计算相同值出现的次数就比较简单了，和在索引上统计计数一样，逐个累加计数即可。

慢查询分析

示例1：

session A	session B
start transaction with consistent snapshot；
	update t set c=c+1 where id=1;//执行100万次
select * from t where id=1;
select * from t where id=1 lock in share mode;

示例2：

-- 创建表t
CREATE TABLE `t` (
	`id` INT (11) NOT NULL,
	`b` VARCHAR (10) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `b` (`b`)
) ENGINE = INNODB;

-- 值超出字段长度，字符串截断后传递给执行引擎，可能匹配上大量数据，最终导致大量回表二次验证b='1234567890abcd'
explain select * from t where b='1234567890abcd';

-- 类型隐式转换，扫描全部索引树
explain select * from t where b=1235

互关问题设计

业务上有这样的需求，A、B 两个用户，如果互相关注，则成为好友。

-- 创建关注表
CREATE TABLE `like` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` INT (11) NOT NULL,
	`liker_id` INT (11) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_user_id_liker_id` (`user_id`, `liker_id`)
) ENGINE = INNODB;

-- 创建好友表
CREATE TABLE `friend` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`friend_1_id` INT (11) NOT NULL,
	`friend_2_id` INT (11) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_friend` (
		`friend_1_id`,
		`friend_2_id`
	)
) ENGINE = INNODB;

session1（A关注B，A=1，B=2）	session2（B关注A，A=1，B=2）
begin;
select * from user_like where user_id=2 and liker_id=1;(Empty set)	begin;
insert into user_like(user_id,liker_id) values(1,2);
	select * from user_like where user_id=1 and liker_id=2;(Empty set)
	insert into user_like(user_id,liker_id) values(2,1);
commit;
	commit;

A、B两个用户同时关注对方，即使session2中select先于session1中insert操作，session2也无法感知其未提交的数据。从而两个session执行完后建立了双向关注，但未建立好友关系。如何解决？

方案1：按照规则，使AB互关映射到同一条数据上，通过行锁冲突+on duplicate key实现好友关系的建立

-- 增加互关关系字段
ALTER TABLE `user_like`
ADD COLUMN `relation_ship`  int NOT NULL AFTER `liker_id`;

-- 按照用户编号正序排列，不关A关注B，还是B关注A，都会命中同一条数据，用relation_ship标识两者之间的关系

-- A关注B，若A=1、B=2
insert into user_like(user_id,liker_id,relation_ship) values(1,2,1) on duplicate key update relation_ship = relation_ship|1;

-- A关注B，若A=2、B=1
insert into user_like(user_id,liker_id,relation_ship) values(1,2,2) on duplicate key update relation_ship = relation_ship|2;

-- 查询AB之前的关系
select relation_ship from user_like where user_id=1 and liker_id=2;

-- 以上两条insert执行后，上一步查询的relation_ship=1|2=3，可执行好友插入
insert ignore into user_friend(friend_1_id, friend_2_id) values(1,2);

on duplicate key需要建立在主键或者唯一键的基础上
insert ignore可保证好友插入的幂等性
好处在于两条数据记录了关注和好友关系
坏处在于不便于查询场景实现（可在异构数据源上进行查询）
- 查询场景复杂化，例如：查询用户A关注的用户，(user_id=A and relation_ship<>2) or (liker_id=A and relation_ship=3)
- 现有索引无法满足查询场景
- 分表的情况下，无法映射指定用户到单一的表上，例如：查询用户A关注的用户

方案2：新的事务中或者异步调用好友关系建立服务

begin;

-- 验证双向关系是否存在，即存在两条数据
select couny(*) from user_like where user_id in (1,2) and liker_id in (1,2);

-- 双向关系存在，插入两条双向好友关系
insert ignore into user_friend (friend_1_id,friend_2_id)  select 
user_id,liker_id from user_like where user_id in (1,2) and liker_id in (1,2);

好处在于关注和好友关系明确，查询实现简单
坏处在于数据存储量翻倍，且关注和友好的建立不在同一个事务中，好友的建立有可能失败，需要提供补偿机制

更新中当前读问题

CREATE TABLE `t` (
	`id` INT (11) NOT NULL,
	`a` INT (11) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB;

insert into t values(1,2);

session A	session B
begin;
select * from t where id=1;
	update t set a=3 where id=1;
update t set a=3 where id=1;
select * from t where id=1;
update t set a=4 where id=1;
select * from t where id=1;

session A中后两次select返回结果是什么？

有疑问的在于第二次，由于session B中已经把a修改为了3，session A中update是当前读，就看是否可以感知a已变更为3。MySQL 8.0.11中已感知不会执行修改操作，第二次读取的快照读还是(1,2)。有说法是update中当前读读取的只是where条件中的列，无法感知a是否变更，执行了修改操作，第二次读取结果为(1,3)

随机显示N条数据

方案1：随机函数排序

-- 不建议采用：排序耗费资源
select * from t order by rand() limit n;

方案2：随机主键

-- 查询主键取值区间
select max(id),min(id) into @M,@N from t ;

-- 随机一个主键区间的值
set @X=floor((@M-@N+1)*rand() + @N);

-- 随机的主键值可能不存在，使用范围查找
select * from t where id >= @X limit 1;

缺点：

只适用取一条数据，多条数据返回查找可能不够
主键分布不均衡的情况下，不同行概率不一样，例如：1、2、3、40000、400001

方案3：随机行数

-- 获取总行数
select count(*) into @C from t;

-- 设置随机显示数量
set @N = 1;

-- 计算起始行数
set @Y = floor(@C * rand())-@N+1;

-- 拼接sql
set @sql = concat("select * from t limit ", @Y, ",", @N);

-- 预处理语句
prepare stmt from @sql;

-- 执行语句
execute stmt;

-- prepare、execute、deallocate统称为prepare statement，称为预处理语句，deallocate用于释放资源
deallocate prepare stmt;

间隙锁加锁分析

以下案例均基于以下表及数据

CREATE 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);

案例一：等值查询间隙锁

session A	session B	session C
begin;
update t set d=d+1 where id=7;
	insert into t values(8,8,8);
		update t set d=d+1 where id=10;

加锁范围(5,10)，B被堵塞，C正常执行

案例二：非唯一索引等值锁

session A	session B	session C
begin;
select id from t where c=5 lock in share mode;
	update t set d=d+1 where id=5;
		insert into t values(7,7,7);

加锁范围：(0,10)，C被堵塞
查找c=5仅需要返回id，id作为主键索引，在二级索引上有，走覆盖索引即可。因此，主键索引没有被访问，不用加锁，B正常执行

案例三：主键索引范围锁

session A	session B	session C
begin;
select * from t where id>=10 and id<11 for update;
	insert into t values(8,8,8);
	insert into t values(13,13,13);
		update t set d=d+1 where id=15;

加锁范围[10,15]：

B中第一次插入成功，第二次插入堵塞
C中更新堵塞

把 A中id>=10 and id<11改为id>=10 and id<=11，C不再堵塞，为什么？

锁是一个一个申请的，要分开来看。id>=10 and id<=11可分解为

id=10：加锁范围行锁(10)，主键且数据存在，退化为行锁，去除间隙锁(5,10)
id>10 and id<11：第一个扫描到的数是15，默认加锁范围(10,15]
id=11：第一个扫描到的数也是15，由于是主键退化为间隙锁(10,15)，(15)上不再加锁

案例四：非唯一索引范围锁

session A	session B	session C
begin;
select * from t where c>=10 and c<11 for update;
	insert into t values(8,8,8);
		update t set d=d+1 where c=15;

加锁范围(5,15]，B、C均堵塞

把 A中c>=10 and c<11改为c>=10 and c<=11`，C依然堵塞，对比案例三

锁是一个一个申请的，要分开来看。id>=10 and id<=11可分解为

c=10：加锁范围行锁(5,10]
c>10 and c<11：第一个扫描到的数是15，默认加锁范围(10,15]
c=11：第一个扫描到的数也是15，加锁范围(10,15]，(15)上仍然有锁

把 A中c>=10 and c<11改为c>10 and c<11`，锁范围如何变化

首个扫描到的数据为c=15，加锁范围(10,15]

案例五：唯一索引范围锁 bug

session A	session B	session C
begin;
select * from t where id>10 and id<=15 for update;
	insert into t values(16,16,16);
		update t set d=d+1 where id=20;

加锁范围(10,20]，B、C均堵塞

案例六：limit 语句加锁

insert into t values(30,10,30);
insert into t values(40,10,40);

session A	session B	session C
begin;
delete from t where c=10 limit 2;
	insert into t values(12,12,12);
		update t set d=d+1 where id=40;
		update t set d=d+1 where id=30;

加上limit 2，加锁范围由(5,15)退化为(5,10]，B中插入不再堵塞
limit N决定了右区间的边界，C中第一次正常执行，第二次更新堵塞

案例七：死锁

session A	session B
begin;
select id from t where c=10 lock in share mode;
	update t set d=d+1 where c=10;
insert into t values(8,8,8);

A中lock in share mode加锁范围(5,15)，B先堵塞，在A中执行插入后检测到死锁异常。原因在于间隙锁和行锁是分开申请的，间隙锁之间不冲突。B先申请到(5,10)的间隙锁，再申请c=10的行锁，由于行锁已被A获取而堵塞。接下来，A执行插入，和B中间隙锁冲突，形成循环等待。

案例八：数据删除，锁范围扩大

session A	session B	session C
begin;
select id from t where c=5 lock in share mode;
	insert into t values(13,13,13);
	delete from t where c=10;
		insert into t values(12,12,12);

A中lock in share mode加锁范围(0,10)，B中插入数据c=13成功，C中插入数据c=12堵塞，均值间隙锁之外为什么后者会堵塞？

B中删除破坏了原有间隙锁结构，间隙锁扩大到(0,13)

案例九：排序对加锁的影响

session A	session B
begin;
select * from t where c>=15 and c<=20 order by c desc lock in share mode;
	insert into t values(6,6,6);

先不关心排序带来的影响，A中范围查询正常加锁范围为(10,25]，B中c=6插入是不会堵塞的。倒序排列后，如果还是从左边开始查找，最终结果是倒序的，还需要把结果集倒置。因此，从右边开始查找，即20开始找，加锁规则不变，还是左开右闭，一直查找到10为止，加锁是以next-key lock为单位，所以会加到(5,10]上，导致B中插入语句堵塞。

案例十：加锁顺序带来的死锁

session A	session B
begin;
select id from t where c in(5,20,10) lock in share mode;
	select id from t where c in(5,20,10) order by c desc for update;

A、B都需要在c=5、c=10、c=20上加锁，由于B中使用了倒序导致查找顺序相反，加锁顺序也刚好相反，一定并发下就会存在相互等待从而死锁。例如：

A先在c=5上成功加锁
B在c=20、c=10上依次成功加锁
A在c=10上加锁时，需要等待B中c=10上的行锁释放
B在c=5上加锁时，同样需要等待A中c=5上的行锁释放

insert加锁分析

session A	session B	session C
begin;
insert into t values(6,6,6);
	insert into t values(7,7,7);
		insert into t values(4,4,4);

-- A中插入语句执行后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

A中只有一个表级锁IX(排他意向锁)，对B、C的插入语句没有影响，B、C正常执行。A、B、C均申请各自的插入意向锁，分属不同的行，不存在冲突

insert唯一键冲突堵塞

-- 把字段c上索引改为唯一索引
ALTER TABLE `t`
DROP INDEX `c` ,
ADD UNIQUE INDEX `c` (`c`) USING BTREE ;

session A	session B
begin;
insert into t values(11,10,10);
1062 - Duplicate entry '10' for key 'c'	insert into t values(9,9,9);
	1205 - Lock wait timeout exceeded; try restarting transaction

-- A中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       109 | demo          | t3          | c          | RECORD    | S         | GRANTED     | 10        |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

-- B中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       109 | demo          | t3          | c          | RECORD    | S         | GRANTED     | 10        |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X,GAP     | WAITING     | 10        |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

根据上述加锁情况可发现，唯一键冲突的时候，在冲突的索引c=10上加了一个读锁。B中执行insert语句需要在c=10上加一个next-key lock，需要获取c=10的X锁，与A中c=10的S冲突，造成B堵塞。

根据官方唯一冲突加锁规则，非主键唯一冲突应该加的是间隙锁，B中插入时申请插入意向锁与间隙锁是同一间隙范围，被堵塞了，也解释的通，但和上述加锁对不上

insert唯一键冲突死锁

session A	session B	session C
begin;
insert into t values(6,6,6);
	insert into t values(7,6,6);
		insert into t values(8,6,6);
rollback;

rollback后，B、C死锁，如开启死锁检测，其中一个事务异常返回，为什么？

-- A中执行insert后加锁情况，只看了一个表级IX
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set

/**
B中执行insert后加锁情况，申请到了c=6的写锁，等待c=6的读锁，为什么？
此时c=6不存在，X锁加锁成功，但由于c是唯一索引，需要去验证唯一性，A中插入未提交，等待读锁
**/
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | S         | WAITING     | 6         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
4 rows in set

/** 
C中执行insert后加锁情况，等待c=6的读锁
**/
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | S         | WAITING     | 6         |
| INNODB |       114 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       114 | demo          | t3          | c          | RECORD    | S         | WAITING     | 6         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

A回滚后，B、C都成功获取S锁，执行插入需要相互等待对方的S锁，进行死锁，是否可优化？

session A	session B	session C
begin;
insert into t values(6,6,6);
	insert into t values(7,6,6) on duplicate key update d=7;
	Query OK, 1 rows affected	insert into t values(7,6,6) on duplicate key update d=8;
rollback;		Query OK, 2 rows affected

-- A中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

-- B中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | WAITING     | 6         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

-- C中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | WAITING     | 6         |
| INNODB |       114 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       114 | demo          | t3          | PRIMARY    | RECORD    | X         | WAITING     | 7         |
| INNODB |       114 | demo          | t3          | PRIMARY    | RECORD    | X         | WAITING     | 7         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

insert into … on duplicate key update会给索引 c 上 (5,10] 加一个排他的 next-key lock（写锁），所以A回滚前B、C不会持有锁。回滚后，先抢到写锁的执行插入，提交后另一个事务执行更新

join优化

CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

create table t2 like t1;
create table t3 like t2;
--  初始化三张表的数据
insert into ... 

-- 以下查询需要加哪些索引来优化？
SELECT
	*
FROM
	t1
JOIN t2 ON (t1.a = t2.a)
JOIN t3 ON (t2.b = t3.b)
WHERE
	t1.c >= X
AND t2.c >= Y
AND t3.c >= Z;

索引原则，尽量使用BKA算法，小表作为驱动表，假设第一个驱动表为：

t1：连接顺序为t1->t2->t3，要在被驱动表字段创建上索引，也就是 t2.a 和 t3.b 上创建索引
t2：连接顺序不确定，需要评估另外两个条件的过滤效果，都需要在t1.a、t3.b上创建索引
t3：连接顺序是 t3->t2->t1，需要在 t2.b 和 t1.a 上创建索引

同时，还需要在第一个驱动表的字段 c 上创建索引

自增主键是否连续

自增主键可能不连续，可能原因如下：

自增值保存策略
- 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值
- 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值
自增值修改机制
- 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
- 指定插入的主键值时，根据自增值生成算法计算新的自增值，影响参数： auto_increment_offset 、auto_increment_increment
自增值申请后未使用不允许回退
同一个语句多次申请自增id，每一次申请是前一次的两倍，可能造成浪费

MySQL 5.1.22 版本开始引入的参数 innodb_autoinc_lock_mode（默认1，语句结束后释放自增锁），控制了自增值申请时的锁范围。

默认值是 1。

0 ：表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁
1 ：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放。不包括普通的insert语句中包含多个value值的批量插入，因为可以计算需要多少个id，一次性申请后即可释放
2：申请后就释放锁（8.0默认值已改为2）

从并发性能的角度考虑，建议将其设置为 2，同时将 binlog_format 设置为 row

误删数据解决方案

误删数据分类：

使用 delete 语句误删数据行
- 确保 binlog_format=row 和 binlog_row_image=FULL的前提下，使用Flashback工具闪回恢复数据
- 建议在从库上执行，避免对数据的二次破坏(数据变更是有关联的，有可能因为误操作的数据触发其他业务逻辑，从而导致其他数据的变更。因此，数据恢复需要再从库上进行，验证后再恢复回主库)
- 事前预防， sql_safe_updates=on关闭批量修改或删除，增加SQL审计
误删库/表：drop table 、truncate table、drop database
- 恢复方案：全量备份+实时备份binlog，可通过延迟复制备库优化，相当于一个最近可用的全量备份
- 预防方案：权限控制、制定操作规范(例如：先备份后删除，只能删除指定后缀表)
rm删除数据：高可用集群即可，HA机制会重新选择一个主库

insert ...select加锁分析

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

-- 语句1：不走索引，加锁范围：所有行锁和间隙锁
mysql> explain insert into t2(c,d) select c,d from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL  |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

-- 语句2：强制走索引c，倒序取第一条，加锁范围：(3,4]、(4,supremum] 
mysql> explain insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
|  1 | INSERT      | t2    | NULL       | ALL   | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL                |
|  1 | SIMPLE      | t     | NULL       | index | NULL          | c    | 5       | NULL |    1 |      100 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+

-- 语句3：从表t查询数据，再插入到自身，需要暂存中间数据，使用了临时表，在临时表上limit，
-- 加锁范围：所有行锁和间隙锁(8.0.11上和语句2一样，锁范围未发生变化)
mysql> explain insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------+
|  1 | INSERT      | t     | NULL       | ALL   | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL                                 |
|  1 | SIMPLE      | t     | NULL       | index | NULL          | c    | 5       | NULL |    1 |      100 | Backward index scan; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------+

-- 假设语句3，先把数据放入临时表，再进行limit，会扫描所有行，如何优化？
create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

Mysql-案例分析