18-20

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大

案例一：条件字段函数操作

不要对字段进行计算。

select * from tradelog where id + 1 = 1000;
select count(*) from tradelog where month(create_time) = 10;

案例二：隐式类型转换

数据类型与字段类型不同的，将导致全表扫描。

select * from tradelog where id = '1000';
select * from tradelog where create_time = '2021-10-01';

select "10" > 9;

案例三：隐式字符编码转换

两张表编码格式不一致也会导致全表查询。

select * from tradelog where name = '张三';
select * from tradelog where name = '张三' collate utf8_general_ci;

19 | 为什么我只查一行的语句，也执行这么慢？

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

第一类：查询长时间不返回

等MDL 锁，元数据锁
等flush
等行锁

等MDL 锁，元数据锁

show processlist;
//或下面sql，可以找出pid（设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失）
select blocking_pid from sys.schema_table_lock_waits;

等flush

//该sql可以查询到当前状态
select * from information_schema.processlist where id= 'pid';

MySQL 里面对表做 flush 操作的用法，一般有以下两个：

flush tables t with read lock;
flush tables with read lock;

等行锁

select * from t sys.innodb_lock_waits where locked_table=table_name \G

KILL pid 断开连接，隐含逻辑自动回滚这个连接里面正在执行的线程，释放行锁。

第二类：查询慢

select * from t where c=50000 limit 1;

如果字段c上没有索引，这个语句只能走id主键顺序扫描，需要扫描5万行。

扫描一行却很慢的语句

全表扫描：select * from t where c=50000;

slow

session B执行100万次后，生成了100万个undo log，所以第一个select 快照读要将undo log执行100万次回到快照的版本。而第二个select当前读。

最后，老师提了for update加锁，关于各隔离级别加锁情况参考链接，这个大哥写的可以。

20 | 幻读是什么，幻读有什么问题

幻读是什么

事务A读取某一行，事务B插入了一行数据，事务A再次读取时，发现多了一条记录。

间隙锁和next-key lock

innodb为了解决幻读加入了间隙锁，锁住一个索引区间(开区间)。锁住索引记录的区间，或第一条索引记录之前的范围，或者最后一条索引记录之后的范围。

间隙锁和行锁合成next-key lock，前开后闭区间。

如下，间隙锁的引入容易导致死锁。因为select for update会加入间隙锁。

begin;
select * from t where id=N for update;
/* 如果行不存在 */
insert into t values(N,N,N);
/* 如果行存在 */
update t set d=N set id=N;
commit;