丁奇mysql学习笔记-基础篇

一、 mysql的逻辑架构

① 问题解决

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢

答案：分析器，分析器会判断表是否存在，字段是否存在

② 当我们在一张表更新的时候，跟这张表有关的查询缓存都会被清空，所以不建议使用查询缓存。MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了，如果不需要这个功能可以将参数 query_cache_type 设置成 DEMAND。

③ 一条select语句执行就是上面的从上往下的流程

二、一条SQL更新语句是如何执行的

更新语句除了上面的逻辑架构层的处理，还需要两个走两个重要的日志模块。

① redo log 重做日志

重做日志是innoDB存储引擎存产生的。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB

redo log是记录的是页上面的修改，binlog则是类似于sql语句一样的逻辑日志

② 更新流程

当执行一条update语句，数据库会先把更新记录写入到redo log里并更新内存数据，这时候更新就算完成了，同时innoDB会在适当的时候，将redo log批量刷新到磁盘中。这样做的好处就是：操作都在内存，减少磁盘读写的IO。

 update T set c=c+1 where ID=2;

执行上面的sql语句流程如下

1) 执行器先到内存中找有没有id = 2的数据，如果没有则到引擎层取出

2) 执行器拿到数据并计算 c = c+1，得到新的一行数据，再调用新的引擎接口写入这行数据

3) InnoDB存储引擎将数据更新到内存中，同时将这个更新操作写入redo log中，此时redo log处于prepare 状态，然后告知执行器执行完成，随时可以提交事务

4) 执行器生成这个操作的binlog,并把binlog写入磁盘

5) 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log的状态改成提交(commit)状态，更新完成

③ 参数innodb_flush_log_at_trx_commit

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

④ sync_binlog

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失

三、索引

① 覆盖索引

② 最左前缀原则

③ 索引下推

索引下推也是建立在复合索引下面的，索引index（a, b） select * from t where a=10 AND b = 2; mysql不会根据index索引先找到a = 10的数据回表判断b = 2的数据，而是先找到a = 10和b = 2的再去回表

④ mysql选错索引

对于由于索引统计信息不准确导致的问题，你可以用analyze table来解决。而对于其他优化器误判的情况，你可以在应用端用force index来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题

四、redo log重做日志

① 脏页刷新到磁盘的情况

1）redo log日志满了，必须要刷新脏页，给redo log腾出空间

2）内存满了，因为脏页的数据都是放到内存的，需要给innoDB pool腾出空间

3) Master Thread Checkpint 线程会定时去刷新脏页

4）mysql关闭，脏页会刷新到磁盘

②刷新脏页的控制参数

innodb_io_capacity 读写磁盘的能力这个值表示innodb读写磁盘的能力(IOPS)，可以用工具测试出来

innodb_max_dirty_pages_pct 脏页的比例上限可以看Mysql5.7 checkpoint和LSN_天道酬勤-明天会更好的博客-CSDN博客

这个最下面的统计到，数据库实际的比例

五、删除数据，mysql的储存大小没有变小

① delete 删除数据后，表的存储为什么没有变小，

mysql的存储都是以页为单位的。删除一条记录，就会产生复用的记录，而删除多条记录，假如删除了一页的数据，则会产生复用的数据页，这些页并没有被mysql释放，而是当做备用的复用页，等待下次的数据使用。

② 怎么让数据表数据得到实际大小了

执行 alter table y engine=InnoDB;

原理是：建立一张新的表，然后把数据移动到新表，然后改名；

MySQL 5.6版本前不能在线操作，如果online DDL 会造成数据流失，因为在移动数据时，可能有新的数据添加到旧表。

MySQL 5.6版本后就优化了，可以online DDL，因为在移动的数据时，如果有数据进行了更新，会记录日志，等数据移动完了，在用日志更新。

六、sql没有走索引分析

① 隐式类型转换

select * from tradelog where tradeid=110717;

如果tradeid是字符串且有索引，当执行上面的sql，数据库不会走索引。是因为mysql会默认把上面的sql转换成

 select * from tradelog where CAST(tradid AS signed int) = 110717;

这种左边有函数的where查询时不走索引的。

② 隐式字符编码转换

做表连接查询的时候用不上关联字段的索引。

如果两个表的字符集不同，一个是utf8，一个是 utf8mb4，做表连接查询的时候会关联不上字段的，内部mysql会进行字符集转码。

七、查看MDL锁

lock table x write;会加一个MDL的写锁；

select查询会产生一个MDL的读锁，所以session B会有阻塞。

可以通过

select blocking_pid from sys.schema_table_lock_waits\G

找到processlist的id,然后kill 杀掉。

mysql7.22版本后才有MDL的锁记录到schema_table_lock表中。

如果大于mysql7.22版本没有记录，需要配置 vim /etc/my.cnf

performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

在线开启，但这种方式是临时生效，实例重启后，又会恢复为默认值。

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

八、解决幻读带来的锁(隔离级别是RR)

幻读是：在同一个事务中，同一个SQL查询范围，后查询的看到前查询没有的行。

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。（这个是比较经典的）

为了解决幻读mysql引用了Gap锁和Next-key Lock，怎么理解了。



*************************** 1. row ***************************
       Table: t
Create Table: 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 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

(root@localhost) [test]> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
6 rows in set (0.00 sec

id是主键，c是普通索引，d没有索引

① begin; select * from t where id = 5 for update;

不会有Gap 锁和 next-key锁，因为id是唯一的，上面的sql会加入一个主键行锁，插入id = 5是不可以的。

② begin; select * from t where c = 5 for update;

会有一个Gap锁和next-key锁， next-key锁是 ( 0, 5 ], Gap锁是 ( 5, 10 )

我们可以反向思考：我们执行下面的sql,假如成功了，就会产生幻读，这是不允许的。

insert into t value (100, 5, 5);

那么为什么还有范围了( 0, 5 ] 和 ( 5, 10 )了，这是根据二级索引来的，

当我们插入c = 5时，需要添加到二级索引，二级索引的存储是(二级索引,主键索引)

(0,0) (5,5) (10,10) (15,15) (20,20) (25,25)这样的，所以数据有可能是插入到 (5,5) 的左边和右边，所以这样是不允许的，所以要加上( 0, 5 ] 和 ( 5, 10 )范围了，这是我的理解哈，

③ begin; select * from t where d = 5 for update;

我们假设执行下面的sql

insert into t value (N, N, 5);

是不是都会产生幻读了，所以上面的sql会有所有主键的Gap锁，就是什么数据都插入不了

九、锁的规则

总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

特例：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

上面的原则和优化是需要理解的，可以看一下丁奇的21讲案例说明。

十、读写分离数据不一致怎么处理

mysql的一主多从的架构，一般是master写，多个从机读，一般采用半复制的模式；

在多个从机读的时候就有可能会产生过期读，即写的数据，从机还没有同步，读出来的数据不一致的问题。

会有下面的这几个方案

①强制走主库方案；

需要查询同步的sql就放到master主机查询，不着急的放到slave备机查询

② sleep方案；

要同步查询的sql可以，休眠1s再查询

③ 判断主备无延迟方案；

就是下面的主库点位的方案

④配合semi-sync方案；

半同步的方案，多个从机读的话，可能只有一台从机是无损同步了

⑤等主库位点方案；

1. trx1事务更新完成后，马上执行showmaster status得到当前主库执行到的File和Position；

2. 选定一个从库执行查询语句；

3. 在从库上执行select master_pos_wait(File, Position, 1)；

4. 如果返回值是>=0的正整数，则在这个从库执行查询语句；

5. 否则，到主库执行查询语句。

⑥等GTID方案。

1. trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；