《叶问》第16期

关 于 叶 问

《叶问》是知数堂新设计的互动栏目，不定期给大家提供技术知识小贴士，形式不限，或提问、或讨论均可，并在当天发布答案，让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

2019年06月04日，周二

MySQL错误日志出现[Note] InnoDB: Discarding tablespace of table db.table: Data structure corruption产生可能的原因及解决办法？

一、产生原因

从错误日志即可判断出表空间ibd文件发生了物理损坏，那么其中的可能包括如下：

1、数据库服务器异常down机/断电等

2、kill -9强制关闭mysqld

3、表空间导出导入过程中发生的ibd文件损坏

4、人为损坏ibd文件

5、底层物理I/O设备损坏，导致文件损坏

二、解决办法

1、从其他实例（如slave）上进行表空间传输进行导入或进行SQL导出导入

2、从历史备份中进行全量+增量恢复完整的ibd文件后再进行表空间传输或进行SQL导出导入

3、尝试innodb force recovery从1-6逐一尝试启动数据库，启动后通过mysqldump重新将表导出导入

4、考虑使用工具undrop-for-innodb从index page尝试数据恢复

2019年06月11日，周二

MGR集群中节点间数据一致性如何检查？

按原理上来说，MGR由paxos协议保证数据最终一致性，但是不排除人为因素导致数据不一致，可以采用如下方法来校验数据一致性

大体思路(主体思想还是学习pt-table-checksum 和 mydumper的多线程备份)：

1、由一个协调线程负责协调多个备份线程

2、MGR写入节点创建checksums表

3、协调线程线程下发FTWRL动作，每个备份线程 ,确认每个节点gtid一致，Relay_Master_Log_File && Exec_Master_Log_Pos 一致

4. 每个节点开始事务事离级别为rr，创建一致性快照

5. 每个表按chunk进行分批校验，把结果记到本地

6. 最终通过dsn对比每个节点产生的结果

2019年06月13日，周四

MySQL的子查询有何问题，MySQL各个版本优化器针对子查询做了哪些改进？

以下只关于括号内子查询的情况（不涉及in/exists等情况）：

一、MySQL5.5

1、子查询无法合并（针对括号内的子查询），优化器处理的逻辑是将数据加载到内存中形成视图，如select * from (select * from t1),因此MySQL5.5中子查询不能随便加括号

2、子查询结果集无法使用索引

3、可通过打开子查询改成join的方式优化

二、MySQL5.6

1、同MySQL5.5子查询同样不能合并

2、新增了auto_key特性，即会对被驱动的子查询结果集自动创建索引（适用于子查询结果集比较小且连接条件无索引的情况，因为需要在内存中创建索引，需要消耗cpu,tmp_table还有可能用到磁盘临时表，造成IO消耗）

三、MySQL5.7

1、支持简单视图合并，optimizer_switch新增derived_merge(如果被驱动表的结果集较少且没有索引就不利，如果被驱动表结果集较大且连接条件有索引就有利)

2、由于该特性，从MySQL5.6迁移到MySQL5.7子查询可能会出现性能下降，因此在数据库版本升级时需要特别注意

四、MySQL8.0

1、新增lateral特性

2、如SQL：select * from t1 left join (select * from t2 group by c1) on t1.id=t2.id可以改写为：select * from t1 left join lateral(select * from t2 on t1.id=t2.id) t2 on t1.id=t2.id

2019年06月18日，周二

MySQL已启用了slow query log且long_query_time=0.01，为什么有些慢SQL还是没被记录呢？

1、在线动态设置long_query_time=0.01，但该设置对当前已建立的连接不会生效

2、log_slow_admin_statements=0，因此ALTER、CREATE INDEX、ANALYZE TABLE等操作即使超过long_query_time不会记录

3、min_examined_row_limit设置非0值，SQL检查行数未超过该值不会记录

4、slow log文件句柄发生了变化，如运行期间用vim打开log，最后又保存退出，此时文件句柄发生变化，需要执行flush slow logs
5、误将slow_query_log_file当做slow log的开关，设置为1（此时slow log文件名为1）

2019年06月20日，周四

为什么long_query_time设置了1秒，slow log中还会记录小于1秒的慢查询？

可能原因如下：

1、设置了全局的long_query_time未对当前连接生效

2、打开了log_queries_not_using_indexes选项，记录了未走索引的SQL

2019年06月25日，周二

在主从服务器上，同一个表的表空间文件大小相差特别大，可能原因是什么，怎么解决？

可能的原因：

1、MySQL表默认是InnoDB引擎且目前索引只支持B+树索引，在数据的增删改过程中，会导致表产生碎片，主从服务器上同张表的碎片率不同也会导致表空间相差很大

2、主库整理过碎片，从库是从原先的未整理的物理备份中恢复出来的

3、主从表结构不一致，如从库可能比主库多索引

4、主从表的行格式不一致，如主库为dynamic，从库为compressed

5、个别云数据库在从库上可能采用特殊的并行复制技术，导致在从库上有更高的碎片率（有个极端的案例，同一个表在主库只有6G，从库上则有将近150G）

解决方式：

1、保证主从表结构一致（包括page大小、索引、行格式等）

2、在业务低峰期使用pt-osc或gh-ost通过alter table xxx engine=innodb;重整表空间，消除碎片（切记：执行前要先检查有无未结束事务或其他未释放锁）

2019年06月27日，周四

MySQL有哪些行锁，是如何解决幻读的？

行锁根据互斥的纬度可以分为：

1、共享锁：当读取当一行记录时为了防止别人修改则需要添加S锁

2、排他锁：当修改一行记录时为了防止别人同时进行修改则需要添加X锁

根据锁定的范围可以分为：

1、间隙锁：间隙锁锁定范围是索引记录之间的间隙或者第一个或最后一个索引记录之前的间隙(指虚拟最大记录)

2、记录锁：MySQL中记录锁都是添加在索引上，即使表上没有索引也会在隐藏的聚集索引上添加记录锁

3、next-key lock：Next-Key Locks是Record Locks与Gap Locks间隙锁的组合，也就是索引记录本身加上 之前的间隙。间隙锁防止了保证RR级别下不出现幻读现象会，防止同一个事务内得 到的结果不一致

4、插入意向锁：插入意向锁定是在行插入之前由INSERT操作设置的一种间隙锁。这个锁表示插入的意图，即插入相同索引间隙的多个事务如果不插入间隙内的相同位置则不需要等待彼此，插入意向锁是一种特殊的GAP LOCK

如何解决幻读的:

1、MySQL在RR隔离级别引入gap lock，把2条记录中间的gap锁住，避免其他事务写入(例如在二级索引上锁定记录1-3之间的gap，那么其他会话无法在这个gap间插入数据)

2、MySQL出现幻读的条件是隔离级别<=RC，或者innodb_locks_unsafe_for_binlog=1(8.0已取消该选项)

PS:董大爷的《解析MySQL中的各种锁》已支持视频回放，并且资料也已经可以下载

 

扫码加入QQ技术交流群

（群号：793818397）