一、 mysql的逻辑架构
![](https://img-blog.csdnimg.cn/7cb0ff4794454c3094ecdf6d3ec10e3b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5aSp6YGT6YWs5YukLeaYjuWkqeS8muabtOWlvQ==,size_16,color_FFFFFF,t_70,g_se,x_16)
① 问题解决
如果表 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)状态,更新完成
![](https://img-blog.csdnimg.cn/fd365e8a72bf454b9e9843a387ca022f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5aSp6YGT6YWs5YukLeaYjuWkqeS8muabtOWlvQ==,size_10,color_FFFFFF,t_70,g_se,x_16)
③ 参数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;
2. 选定一个从库执行查询语句;
3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
4. 如果返回值是0,则在这个从库执行查询语句;
5. 否则,到主库执行查询语句
select wait_for_executed_gtid_set方法很好用,其实就是判断传入的gtid和当前的gtid对比,返回0则表示,从库已经执行了这个gtid对应的事务。