MySQL通关面试题

MySQL主从复制什么原因会造成不一致，如何预防及解决？
一、导致主从不一致的原因主要有： 
人为原因导致从库与主库数据不一致（从库写入）
主从复制过程中，主库异常宕机
设置了ignore/do/rewrite等replication等规则
binlog非row格式
异步复制本身不保证，半同步存在提交读的问题，增强半同步起来比较完美。 但对于异常重启（Replication Crash Safe），从库写数据（GTID）的防范，还需要策略来保证。
从库中断很久，binlog应用不连续，监控并及时修复主从
从库启用了诸如存储过程，从库禁用存储过程等
数据库大小版本/分支版本导致数据不一致？，主从版本统一
备份的时候没有指定参数 例如mysqldump --master-data=2 等
主从sql_mode 不一致
一主二从环境，二从的server id一致
MySQL自增列 主从不一致
主从信息保存在文件里面，文件本身的刷新是非事务的，导致从库重启后开始执行点大于实际执行点
采用5.6的after_commit方式半同步，主库当机可能会引起主从不一致，要看binlog是否传到了从库
启用增强半同步了（5.7的after_sync方式），但是从库延迟超时自动切换成异步复制

二、预防和解决的方案有：
master:innodb_flush_log_at_trx_commit=1&sync_binlog=1
slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1
设置从库库为只读模式
可以使用5.7增强半同步避免数据丢失等
binlog row格式
必须引定期的数据校验机制
当使用延迟复制的时候，此时主从数据也是不一致的（计划内），但在切换中，不要把延迟从提升为主库哦~
mha在主从切换的过程中，因主库系统宕机，可能造成主从不一致（mha本身机制导致这个问题）

你为什么会决定进行分库分表，分库分表过程中遇到什么难题，如何解决的？
一、为什么决定进行分库分表？
根据业务类型，和业务容量的评估，来选择和判断是否使用分库分表
当前数据库本事具有的能力，压力的评估
数据库的物理隔离，例如减少锁的争用、资源的消耗和隔离等
热点表较多,并且数据量大，可能会导致锁争抢，性能下降
数据库的高并发，数据库的读写压力过大，可能会导致数据库或系统宕机
数据库（MySQL5.7以下）连接数过高，会增加系统压力
单表数据量大，如SQL使用不当，会导致io随机读写比例高。查询慢（大表上的B+树太大，扫描太慢，甚至可能需要4层B+树）
备份和恢复时间比较长
二、都遇到什么问题?
全局pk（主键和唯一索引）的冲突检测不准确，全局的自增主键支持不够好
分片键的选择。如没有选择好，可能会影响SQL执行效率
分布式事务，中间价产品对分布式事务的支持力度
对于开发来说，需要进行业务的拆分
对于开发来说，部分SQL不兼容则需要代码重构，工作量的评估
对于开发来说，跨库join，跨库查询
三、如何解决?
使用全局分号器。或者使用全局唯一id，（应用生成顺序唯一int类型做为全局主键）
应用层来判断唯一索引
配合应用选择合适的分片键，并加上索引
配合应用，配合开发，对不兼容SQL的进行整改

MySQL高可用架构应该考虑什么? 你认为应该如何设计？
一、MySQL高可用架构应该考虑什么？
对业务的了解，需要考虑业务对数据库一致性要求的敏感程度，切换过程中是否有事务会丢失
对于基础设施的了解，需要了解基础设施的高可用的架构。例如 单网线，单电源等情况 
对于数据库故障时间掌握，业务方最多能容忍时间范围，因为高可用切换导致的应用不可用时间
需要了解主流的高可用的优缺点：例如 MHA/PXC/MGR 等。
考虑多IDC多副本分布，支持IDC级别节点全部掉线后，业务可以切到另一个机房
二、你认为应该如何设计? 
基础层 和基础运维部门配合，了解和避免网络/ 硬盘/ 电源等是否会出现单点故障 
应用层 和应用开发同学配合，在关键业务中记录SQL日志，可以做到即使切换，出现丢事务的情况，也可以通过手工补的方式保证数据一致性，例如：交易型的业务引入状态机，事务状态，应对数据库切换后事务重做 
业务层 了解自己的应用，根据不同的应用制定合理的高可用策略。 
单机多实例 环境及基于虚拟机或容器的设计不能分布在同一台物理机上。 
最终大招 在数据库不可用 ，可以把已提及的事务先存储到队列或者其他位置，等数据库恢复，重新应用

MySQL备份，使用xtrabackup备份全实例数据时，会造成锁等待吗？那么如果使用mysqldump进行备份呢？
一、xtrabackup和mysqldump会造成锁等待吗？ 
xtrabackup会，它在备份时会产生短暂的全局读锁FTWL(flush table with read lock)，用于拷贝frm/MYD/MYI等文件，以及记录binlog信息。如果MyISAM表的数据量非常大，则拷贝时间就越长，加锁的时间也越长
mysqldump有可能会。如果只是添加 --single-transacton 选项用于保证备份数据一致性，这时就不会产生FTWL锁了。但通常我们为了让备份文件和binlog保持一致，通常也会设置 --master-data 选项用于获得当前binlog信息，这种情况也会短暂加锁
数据量特别大的话，建议优先用 xtrabackup，提高备份/恢复速度。而如果数据量不是太大或者想备份单表，则建议用mysqldump了，方便逻辑恢复。各有利弊，注意其适用场景
二、xtrabackup冷知识
基于MySQL 5.6版本开发的xtrabackup，会在备份过程中生成内部通信文件 suspend file，用于 xtrabackup 和 innobackupex 的通信，备份结束后文件删除，默认文件位置 /tmp/xtrabackup_suspended 
如果在备份过程中，修改了 /tmp 的访问权限或该文件的权限，则两个程序间直接不能通信，会造成 xtrabackup hang 住，正在备份的表不能正常释放锁，会造成锁等待，此时需要强制 kill 掉 xtrabackup 进程

MySQL 5.7开始支持JSON，那还有必要使用MongoDB存JSON吗？请列出你的观点/理由。
一、观点A：支持MySQL存储JSON
1.MongoDB不支持事务，而MySQL支持事务
2.MySQL相对MongoDB而言，MySQL的稳定性要优于MongoDB
3.MySQL支持多种存储引擎
二、观点B:支持MongoDB存储JSON 
1.从性能的角度考虑，对于JSON读写效率MongoDB要优于MySQL
2.MongoDB相对MySQL而言，MongoDB的扩展性要优于MySQL
3.MongoDB支持更多的JSON函数
三、总结
1.如果应用程序无事务要求，存储数据表结构复杂并且经常被修改， 例如游戏中装备等场景用MongoDB比较适合
2.如果应用程序有事务要求，存储数据的"表"之间相互有关联，例如有订单系统等场景用MySQL比较适合
3.整体来看相对看好MySQL的JSON功能，在未来官方的努力下MySQL的JSON功能有机会反超MongoDB

当数据被误删除/误操作后造成数据丢失。你尝试过用什么手段来挽救数据/损失？
一、前提 
1.当数据被误删除/误操作后，第一时间要关闭数据库。业务方需要紧急挂停机公告，避免数据二次污染，用于保护数据的一致性
2.BINLOG格式为ROW格式，不讨论其他格式的BINLOG
二、数据被误操作(update/delete/drop)造成数据丢失，可以用哪些手段来恢复？ 
1.BINLOG恢复：可以使用逆向解析BINLOG工具来恢复。例如：binlog2SQL等
2.延迟从库: 可以通过解除延迟从库，并指定BINLOG结束位置点，可以实现数据恢复
三、数据被误删除(rm/物理文件损坏)造成数据丢失，可以用哪些手段来恢复？ 
1.如果有备份，可以通过备份恢复 mysqldump/xtrabackup + binlog 来实现全量+增量恢复
2.如果无备份但是有从库，可以通过主从切换，提升从库为主库，从而实现数据恢复
3.如果无备份并且无从库，但MySQL没有重启，可以通过拷贝/proc/$pid/fd中的文件，来进行尝试恢复
4.如果无备份并且无从库，但MySQL有重启，可以通过extundelete或undrop-for-innodb来恢复

MySQL 5.7的复制架构,在有异步复制、半同步、增强半同步、MGR等的生产中，该如何选择？
一、生产环境中： 
几种复制场景都有存在的价值。下面分别描述一下： 
从成熟度上来选择，推荐：异步复制(GTID+ROW)
从数据安全及更高性能上选择：增强半同步 （在这个结构下也可以把innodb_flush_log_trx_commit调整到非1， 从而获得更好的性能)
对于主从切换控制觉的不好管理，又对数据一致性要求特别高的场景，可以使用MGR
二、理由：
异步复制，相对来讲非常成熟，对于环境运维也比较容易上手 
增强半同步复制，可以安全的保证数据传输到从库上，对于单节点的配置上不用要求太严格，特别从库上也可以更宽松一点，而且在一致性和性能有较高的提升，但对运维上有一定的要求
MGR组复制。相对增强半同步复制，MGR更能确保数据的一致性，事务的提交，必须经过组内大多数节点（n/2+1）决议并通过，才能得以提交。MGR架构对运维难度要更高，不过它也更完美
总的来讲，从技术实现上来看：MGR> 增强半同步>异步复制。
未来可能见到更多的MGR在生产中使用，对于MySQL的运维的要求也会更上一层楼。

为什么说pt-osc可能会引起主从延迟，有什么好办法解决或规避吗？
若复制中binlog使用row格式，对大表使用pt-osc把数据从旧表拷贝到临时表，期间会产生大量的binlog，从而导致延时
pt-osc在搬数据过程中insert...select是有行锁的，会降低事务并行度；且pt-osc搬数据过程中生成的binlog不是并行的，所以在slave不能并行回放
可以通过设定参数 --chunk-size、--chunk-time控制每次拷贝数据大小，也可以设定--max-log、check-interval、check-slave-lag等参数控制主从复制延迟程度（但这样可能会造成pt-osc工作耗时太久，需要自行权衡）

你遇到过哪些原因造成MySQL异步复制延迟？
master上多为并发事务，salve上则多为单线程回放（MySQL 5.7起，支持真正的并行回放，有所缓解）
异步复制，本来就是有一定延迟的（否则也不叫做异步了，介意的话可以改成半同步复制）
slave机器一般性能比master更弱（这是很常见的误区，其实slave对机 器性能要求并不低）
有时为了节省机器资源，会在slave上运行多个实例
表结构设计不合理，尤其是在MySQL 5.6之前没主键，几乎会造成所有更新都全表扫描一遍，效率非常低
slave上运行大量只读低效率的SQL
大量大事务，也会造成slave无法并行回放 
业务设计缺陷，或网络延迟等导致延迟

MySQL每天产生了多大容量的binlog，用SQL语句能查到吗？
首先，这是个假设性命题（又一个钓鱼题）。 
这个需求完全可以通过系统层命令，配合MySQL中的“FLUSH BINARY LOGS”快速完成。 
运行SHOW MASTER/BINARY LOGS命令能查看全部binlog列表，但没办法区别哪些是当天内生成的。

用什么方法可以防止误删数据？
以下几个措施可以防止误删数据，如下： 
生产环境中，业务代码尽量不明文保存数据库连接账号密码信息
重要的DML、DDL通过平台型工具自动实施，减少人工操作
部署延迟复制从库，万一误删除时用于数据回档，且从库设置为read-only
确认备份制度及时有效
启用SQL审计功能，养成良好SQL习惯
启用 sql_safe_updates 选项，不允许没 WHERE 条件的更新/删除
将系统层的rm改为mv
线上不进行物理删除，改为逻辑删除（将row data标记为不可用）
启用堡垒机，屏蔽高危SQL
降低数据库中普通账号的权限级别
务必开启binlog

MySQL 8.0相对于5.7的复制改进，都有哪些呢？
一、普通复制功能改进 
新增WRITESET并行复制模式，提高并行度，降低延迟 
在多源复制中，可在线动态修改每个channel的filter rule，并且能在P_S中查看/监控 
Binary Log中存储更多元数据，并支持毫秒级别的延迟监控 
对JSON Documents的复制效率更高了 
支持DDL Crashsafe 
增加caching_sha2_password安全策略，提高复制安全性
二、MGR功能改进:
支持设置节点权重，且权重最大的在线节点将被选举为主 
每个节点中存储更多的状态信息，如版本、角色等 
可根据从节点的事务状态，自动化流控 
离开集群的服务器自动被设置为read only，避免被误操作更新数据 
可监控MGR的内存使用情况

跑truncate table，4亿条数据会不会造成长时间锁表呢?有什么更好的方法吗?
最好是create新表，然后交叉rename对调，再drop/truncate table或其他方式清除数据。 
一、可操作步骤： 
创建新的 tmp 表，正式表与tmp表表名交换（注意在一个SQL里完成，并锁表） 
对 tmp 表创建硬链接 ln tmp.ibd tmp.ibd.hdlk 
mysql中删除表tmp（truncate / drop 都行）
然后找个业务不繁忙的时间删除数据文件或者用coreutils 的truncate慢慢搞 
二、关于truncate table，官档解释：
Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements 
When a table is truncated, it is dropped and re-created in a new .ibd file, and the freed space is returned to the operating system

明明有个索引“感觉”应该被选中，EXPLAIN时在possible_keys也有它，但最后没被选中，可能的原因有哪些？ 
一、执行计划如下：
desc select * from t1 where c2 >= 2; 
key: NULL 
key_len: NULL 
rows: 14 
filtered: 92.86 
Extra: Using where
二、可能的原因如下： 
隐式转换
表碎片，因为表的碎片率过高
根据索引读取到的数据在整个表中的数据占比超过30%
统计信息没有及时更新
三、上述执行计划的结果是：
预计扫描的行数为14行，filtered（是指返回结果的行占需要读到的行的百分比）的值为92%。
当前执行计划中filtered值92% 说明根据索引查询获取的结果占整张表的92%，在MySQL中根据索引查询的结果占整张表的数据30%则不会走索，所以不会走索引。 
另外，也有可能是表的碎片率过高或隐式转换导致的。

主从复制线程均正常（为Yes，也没报错），Master的binlog已到binlog.000100，但slave上看到Master_Log_File却只到binlog.000090，可能的原因有哪些?
首先要注意，这是Master_Log_File IO线程延迟，并不是Relay_Master_Log_File SQL线程延迟。
一、可能的原因如下： 
由于sync_relay_log值过低，导致Slave频繁刷新relay_log文件，使 Slave的硬盘资源消耗过高，所以导致SlaveIO Thread很慢。 
Master/Slave压力过大导致Slave IO Thread不能及时响应， 无法及时获得Master的event。 
网络丢包严重。小包可以连接并且保持连接不断，但是大包就无法发送。可能是Master和Slave关于TCP MTU值设置不一致导致。 
Master和Slave网络链接已经断开。但slave_net_timeout值等于0（表示完全禁用心跳）或者slave_net_timeout和Slave_heartbeat_period非常大（表示检测主从心跳的时间）。 
Master的binlog非常大，io线程的file很长时间都在读同一个。 
二、总结 
本次案例是在主库进行压力测试，在压力测试的过程中，因为Master本身的压力就很大Master来不及把binlog发送给Slave。所以表面上看起来没有延迟，但实际上已经产生了延迟。

如何优化Linux操作系统用于MySQL环境？
一、初级玩法 
1. 在BIOS及内核层面关闭NUMA 
2. 在BIOS层面将CPU、内存均设置最大性能模式 
3. 在BIOS层面关闭CPU节能模式 
4. 修改IO Scheduler为deadline 或 noop 
5. 使用xfs文件系统，挂载选项noatime、nodiratime、nobarrier 
6. 在内核层面设置vm.swappiness<=5，vm.dirty_ratio<=10， vm.dirty_background_rati<=5 
7. 在内核层面修改用户可最大打开文件数和线程数为65535 
8. 禁用SWAP分区
二、高端玩法
1. 使用最新稳定Linux发行版 
2. 升级各个硬件设备到最新稳定firmware版本 
3. 使用SSD时，开启TRIM功能，并且可以的话文件系统block size和SSD对齐 
4. 当磁盘I/O存在瓶颈时，除了常规因素外，还需要关注中断不均衡的可能性

MySQL 8.0 InnoDB哪些新特性你最期待，为什么？
1. 数据字典全部采用InnoDB引擎存储，支持DDL原子性、crash safe，metadata管理更完善 
2. 快速在线加新列（腾讯互娱DBA团队贡献） 
3. 并行redo log，并提升redo log的I/O性能 
4. 新增倒序索引 
5. 增强CBO特性 
6. 消除了buffer pool mutex（Percona的贡献） 
7. 自增ID持久化 
8. 行锁增加SKIP LOCKED和NOWAIT特性选项 
9. 新增事务CATS特性，大大提升事务性能（Michigan大学贡献） 
10. memcached plugin增强 
11. 增强JSON性能、功能 
12. 新增智能选项 innodb_dedicated_server

 MySQL hang的原因有哪些？ 
1. MySQL使用资源过高导致服务器太累扛不住。例如CPU、内存、 I/O等开销。 
2. 磁盘无可用空间。 
3. MySQL频繁的创建和销毁连接。 
4. MySQL使用的最大文件打开数和连接数，超过了操作系统的限制。 
5. MySQL的锁不能有效的释放。例如持有行锁或者表锁，造成了MDL等待。 
6. MySQL的bug导致的。 
导致MySQL hang住的原因有很多，不局限于上述因素，还需要机智的你来挖掘。

MySQL数据导入数据仓库（Hadoop）有哪几种方式？ 
1. 传统方式，采用mysqldump等工具将数据文件上传至HDFS 
2. 使用Sqoop Kettle等ETL工具，将数据表对应导入Hive的数据表 
3. 使用kafka+flume方案，将mysql binlog通过流式采集的方式导入Hadoop 
4. 设计实现Hive的快照表、增量表、全量表，实现MySQL到Hive数据的增量导入，并支持分库分表等特性。 

MongoDB高并发写场景开启读写分离读从库为何阻塞? 我们该如何处理? 
按业务拆分逻辑降低读写并发度 
添加分片均衡读写 
升级至即将到来的4.0通过读snapshot解决从库读阻塞 

MongoDB 4.0有哪些新特性，你最期待的有哪些，为什么？
多文档事务的支持，解决了多文档操作的原子性问题
snapshot读相关支持，使得可在某个timestamp点上读到一个一致性的快照
Change Streams 支持实例及库级别粒度为业务提供了更多实时捕获变更的选择
聚合框架支持类型转换及字符前后空格截断操作
加入对SCRAM-SHA-256认证策略以支持更强的认证加密验证
提供通过简单的命令开启免费监控功能
更多的操作支持w:majority 比如对集合进行分片，创建删除集合等
listCollections 可以指定nameOnly:true 而不加锁
增加 rollbackTimeLimitSecs参数控制节点回滚的最大时间限制
支持直接在mongos路由节点直接kill具体操作无需按分片进行
使用WiredTiger引擎不允许关闭journal日志

Redis如何获取所有的key，不阻塞？ 
在slave上执行Save命令，拷贝rdb文件到其他redis实例上用于统计key。 
可以利用scan命令，来遍历当前数据库中的数据库键。

MySQL中间件的性能测试和常规业务性能测试相比有什么不同？ 
性能测试的方法论基本都一样，以观察-分析-改进-再观察这个循环进行。
常规业务由于业务交互复杂、技术栈庞杂、性能瓶颈通常集中于业务, 性能测试使用的分析方法比较简单, 通过诊断业务通常可以低成本地找到性能瓶颈。
MySQL中间件的应用场景比较简单、技术栈稳定、性能瓶颈通常集中于架构和环境, 性能测试使用的分析方法比较多, 对性能瓶颈的分析通常成本比较高。另外在这一方面的现有知识积累并不很成体系, 也是成本较高的原因之一。
专访黄炎:MySQL中间件的性能测试和常规业务性能测试相比有什么不同？
性能测试的方法论基本都一样, 以“观察-分析-改进-再观察”这个循环进行。
常规业务由于业务交互复杂、技术栈庞杂、性能瓶颈通常集中于业务, 性能测试使用的分析方法比较简单, 通过诊断业务通常可以低成本地找到性能瓶颈。
MySQL中间件的应用场景比较简单、技术栈稳定、性能瓶颈通常集中于架构和环境, 性能测试使用的分析方法比较多, 对性能瓶颈的分析通常成本比较高. 另外在这一方面的现有知识积累并不很成体系, 也是成本较高的原因之一。
 

《全方位认识SYS系统库》公开课精彩互动问答：
1、为什么我用root用户调用call ps_setup_enable_instrument('wait');报错说存储过程不存在？
答：sys schema是从MySQL 5.7之后才默认支持，请确保你的数据库版本正确，且先使用use语句切换默认数据库，否则请带上 sys.库名称限定前缀。
2、myisam锁如何查询？
答：MyISAM 不支持事务，所以不存在事务锁，但可以查询表级锁（例如：MDL锁），通常表级锁是Server层添加的锁，与具体的存储引擎无关，所以与InnoDB存储引擎查询方法一致，建议多多尝试即可得出答案。
3、为什么我查询session系统表，当前正在执行SQL的会话的progress为 NULL 呢？
答：对于progress信息，仅支持stages事件（performance_schema.setup_instruments表的name字段以stages开头的采集项），其他事件类型不支持，且就算是stages类型事件，也不是所有的采集项都支持，可以通过观察performance_schema.events_stages_current表的WORK_COMPLETED和WORK_ESTIMATED字段，需要不为NULL值，progress信息就是根据这些不为NULL的值进行计算的
注意：要成功采集stages性能数据，必须打开stages事件相关的instruments和consumers
如果不满足以上条件，session视图查询到的progress字段就会显示NULL。
4、线上数据库，开启ps和关闭ps功能，mysqld使用的内存会相差20G，可以判断ps会用到很多主机内存。怎么判断ps功能回来多少内存？怎么进行优化ps对内存的使用？
答：ps的整体功能无法动态开关，必须在数据库启动之前就设置好，能够动态开关的只是ps的具体的instruments采集项和consumers存储表，对于查询ps使用的内存总量，可以使用语句 select sys.format_bytes(sum(current_alloc)) from sys.x$memory_global_by_current_bytes where event_name like 'memory/performance_schema%'; 查询，对于ps内存使用的优化，MySQL 提供了一系列performance_schema打头的系统变量来进行灵活配置，请根据需要自行调整，默认情况下不建议调整，除非你真的需求，否则就会浪费内存空间。
 

在MySQL中如果发现乱码的情况该如何判断原因及应对？
1、直接修改法. alter或者pt-osc等其他工具直接对数据进行修改。
2、备份修改法. 利用mysqldump或者其他逻辑备份进行备份，备份的结果集再利用iconv进行转换
3、跳过字符集备份.利用mysqldump备份的时候跳过字符集-t --skip-set-charset。在恢复的时候指定表的字符集。
那么应该如何避免乱码呢？
1、首先要从应用端到MySQL，采用统一编码格式。
2、在MySQL的配置中,指定编码格式。
3、在上线或者导入SQL的时候，要注意本地的编码集。

MySQL的表中有唯一索引，设置unique_checks为0时，还能否写入重复值？
首先，即便设置unique_checks=0，也无法往唯一索引中写入重复值。
其次，设置unique_checks=0的作用在于，批量导入数据（例如load data）时，在确保导入数据中无重复值时，无需再次检查其唯一性，加快导入速度。
所以，unique_checks=0并不是允许唯一约束失效，而是再批量导数据时不再逐行检查唯一性。

某人曰，在数据检索的条件中使用!=操作符时，存储引擎会放弃使用索引。 理由：因为检索的范围不能确定,所以使用索引效率不高，会被引擎自动改为全表扫描。
你认可他的说法吗？
答：通常情况下，这个说法是正确的。当然，也有特殊情况，话不能说绝对了。 
有一个测试表共80万条数据，其中type列只有1、2两个值，分别占比97%和3%。 
这种情况下，查询条件 WHERE type != 1，是有可能也可以走索引的。 
下面是两个SQL的执行计划： 
mysql> desc select * from t1 where type = 1\G 
************ 1. row ************ 
id: 1 
select_type: SIMPLE 
table: t1 
partitions: NULL 
type: ref 
possible_keys: type 
key: type 
key_len: 4 
ref: const 
rows: 399731 
filtered: 100.00 
Extra: NULL
mysql> desc select * from t1 where type != 1\G 
************ 1. row ************ 
id: 1 
select_type: SIMPLE 
table: t1 
partitions: NULL 
type: ref 
possible_keys: type 
key: type 
key_len: 4 
ref: const 
rows: 10182 
filtered: 100.00 
Extra: NULL
type数据分布 
mysql> select type, count(*) as cnt from t1 group by type order by cnt; 
+------+--------+ 
| type | cnt | 
+------+--------+ 
| 2 | 38304 | 
| 1 | 761690 | 
+------+--------+

Redis集群的slot迁移是如何实现的? 
答：迁移源slot设置为migrating 状态，迁移目标slot设置为importing状态。
在内部用dump & restore命令，把数据迁移到目标节点，迁移结束之后，移除migrating和importing状态。
在迁移过程中如果有数据访问，如果数据没迁移到目标节点，那么直接返回结果，如果迁移到目标节点，那么给客户端返回ASK重定向。
 
在大表执行ddl的过程中，若临时中断，会发生什么状况，需要特别处理吗 ？ 
前提说明：MySQL5.7.23、innodb表、“双1” 
1、添加/删除列，采用copy的方式 
1.1、ctrl+c。在当前session中，手动执行ctrl+c。无影响，并且会自动删除产生的临时文件。 
1.2、kill -9。在执行ddl的时候，服务器发生意外宕机或者手动执行kill -9。待MySQL启动后，则会自动执行InnoDB Recovered流程。并且不会删除产生的临时文件，需要手工处理。 
2、添加/删除索引，采用INPLACE方式 
2.1、ctrl+c，同1.1 
2.2、kill -9。不会删除临时文件，也不会执行InnoDB Recovered流程并且报错 Operating system error number 2 in a file operation ....OS error: 71 
在开始执行alter table的过程中，在没有结束的时候，并不会写入到binglog文件中。
innodb已经使用了独立undo表空间，那么ibdata1还会增长吗，为什么？ 
答：使用独立undo表空间后，ibdata1里主要存储Data dictionary、Rollback segments、Double write buffer、change buffer、Foreign key constraint system tables等数据。 
下面两种情况可能还会导致ibdata1文件增长： 
1、当使用共享表空间模式时（设置innodb_file_per_table=0），ibdata1还会存储用户数据，导致ibdata1文件增长。（当然了，现在应该几乎没人再这么用了） 
2、在高I/O负载时，可能会来不及刷新和回收change buffer page，也会导致ibdata1文件增长。（此种情况再高并发压测情形下更容易出现）
怎么安心过好国庆节？ 
答：
1、检查备份。不管是逻辑备份、物理备份，还有binlog也要备份。备份文件可恢复，才是好备份。 
2、做好安全措施。授权合理不要过大，在外不要连陌生的网络办公。危险操作，尽量等节后执行。 
3、健康巡检。节前巡检包括：数据库桩体、硬盘空间、日志信息、物理硬件、系统负载是否预警等。
MySQL线上实例insert慢常见原因有哪些？
答：
1、锁等待：SQL产生的间隙锁、自增锁、死锁、MDL锁、外键检查锁，锁等待时间
2、iops达到瓶颈：例如备份任务、高频binlog redolog等文件写入
3、semi-sync：因为网络抖动，MySQL半同步、增强半同步导致语句卡住
4、高并发：高并发场景下，导致系统资源达到瓶颈，从而SQL执行慢
5、大字段：当前表索引过多，或者写入大量的text类型数据
6、硬件故障：因为磁盘、raid卡、内存等物理硬件故障导致写入慢
7、磁盘资源耗尽：操作系统的磁盘、inode资源耗尽
8、文件系统故障：MySQL data目录的所在挂在的不可写、或者被设置为只读
9、binlog group commit等待 
10、参数配置：innodb_buffer、redo_buffer过小 
11、autocommit：事物非自动提交，等待程序提交。
虽然命中索引，但SQL效率仍然慢，可能有哪些原因？
答：
1、索引字段重复值或者空值太多。 
2、查询条件范围太广返回结果数太多，全索引扫描 
3、没有利用到覆盖索引，造成大量回表 
4、查询字段过多，并且包含大字段 
5、索引字段数据分布太随机，回表不多也会引起大量随机io 
6、统计信息不准 
7、表的单行数据值很大，需要较多io 
8、表中包含多个索引， 命中的索引不是最优的索引。
MongoDB服务器CPU一直很高，最高达到900%，可能是哪些原因？ 
答：可能原因如下：
1、高并发场景下，服务器开启numa
2、mongo查询无索引，消耗大量内存和io
3、服务器硬件故障，例如CPU 内存 raid卡等
3、高并发写入下开启读写分离+oplog应用加锁 
4、高并发短链接+最新SCRAM-SHA-1认证的情况
MySQL运行环境中，当发现系统已经用到了swap，该怎么处理？
答：一般来说，发生swap的原因是系统认为内存不够了。
那么，当物理内存真的不够了，或者著名的NUMA都是引起swap的可能原因。通常的应对方法有几种：
1、通过BIOS、系统内核参数关闭NUMA，或者在mysqld启动时，利用numactl关闭NUMA的使用
2、调低系统使用swap的权重，设置内核参数 参数 vm.swappiness 不高s 不高于10
3、Linux下使用free命令查看内存使用情况，确认是否发生了内存泄露
4、修改MySQL参数innodb_flush_method = O_DIRECT，这样InnoDB在读写物理数据的时候会绕过cache来访问磁盘
5、优化SQL效率，避免产生额外的分组、排序、临时表情况发生，参考文章：文章：http://t.cn/EwLIuFv
6、在【夜间或业务不繁忙】时适合执行 swapoff -a，并执行sync刷新操作系统内存脏页到硬盘
大量SQL语句文本，如何快速导入到MySQL中？
答：
1、可在SQL文本前，添加set session sql_log_bin=0（需要在从库也导入一次）。
2、导入期间临时修改参数sync_binlog=10000、innodb_flush_log_at_trx_commit=0、innodb_autoinc_lock_mode=2。
3、导入前，根据业务情况看能否删除除了自增列主键外的其他索引。
4、将SQL文件切割成多份，再并发多线程导入。
5、若该SQL文件是每个INSERT一行，需要先行将多行合并成一行，即启用extended-insert模式。
6、以上建议，在线上环境请谨慎评估该骚操作的风险性。
7、以上建议，仅考虑尽快导入，涉及到和具体业务需求相冲突时（例如太快导入反倒会影响在线数据库性能），以实际情况为主。
Show proceslist时发现大量的sleep，有什么风险吗，该如何处理？
答：
（一）可能的风险有：
1、大量sleep线程会占用连接数，当超过max_connections后，新连接无法再建立，业务不可用；
2、这些sleep线程中，有些可能有未提交事务，可能还伴随着行锁未释放，有可能会造成严重锁等待；
3、这些sleep线程中，可能仍有一些内存未释放，数量太多的话，是会消耗大量无谓的内存的，影响性能。
（二）建议应对措施：
1、升级到5.7及以上版本，连接性能有所提升；
2、采用MariaDB/Percona版本，根据情况决定是否启用thread pool功能；
3、适当调低wait_timeout/interactive_timeout值，例如只比java连接池的timeout时间略高些即可；
4、利用pt-kill或辅助脚本/工具巡查并杀掉无用sleep进程；
5、利用5.7的新特性，适当设置max_execution_time阈值，消除长时间执行的SQL；
6、定期检查show processlist的结果，找到长时间sleep的线程，根据host&port反推找到相关应用负责人，协商优化方案。
MySQL如何对只对个别列授权？
答：其实挺简单的，用GRANT授权即可，例如：
GRANT SELECT (c1), INSERT (c1, c2) ON zhishutang.yewen TO 'yewen'@'zhishutang.com';
如何正确监控MySQL主从复制延迟(请考虑5.6版本前后区别，即并行复制及GTID等因素)?
答：基于Binlog和Postion复制
1、通过观察io线程减去sql线程对比的方式对比： Master_Log_File == Relay_Master_Log_File && Read_Master_Log_Pos == Exec_Master_Log_Pos。
基于GTID复制
2、通过接受事务数减去已经执行事务数对比：Retrieved_Gtid_Set == Executed_Gtid_Set。
基于并行复制
3、先通过P_S库replication_applier_status_by_coordinator和replication_applier_status_by_worker表来观察每个复制线程的状态，后配合postion复制或GTID复制方法来监控复制延迟。
4、可以采用pt-heartbeat工具。
大家不要再相信Seconds_Behind_Master。
从库Seconds_Behind_Master是通过拿Slave服务器当前的时间戳与SQL线程读取relay log中的事件的时间戳相比得到的，所以只有当SQL线程执行事件时才会报告延迟，不然Seconds_Behind_Master=0。
如果备库复制线程没有运行，则Seconds_Behind_Master = NULL。
即使备库线程正在运行，备库有时候可能无法计算延时，如果发生这种情况，Seconds_Behind_Master会报0或者NULL。
如果网络延迟，导致从库拉取日志速度跟不上主库；但此时从库IO线程跟SQL线程在同一位点，那么Seconds_Behind_Master值为0，误以为没有延迟。
一个大事务可能会导致延迟波动，例如一个事务更新数据长达1个小时，最后提交；这条更新语句将比它实际发生时间要晚一个小时才记录到二进制日志中（Binlog记录开始执行时间以及执行时间），当备库执行这条语句时，会临时报告备库延迟1小时，然后又很快变为0。
长期未提交的事物延迟（如begin…dml…wait…commit），会造成延迟的瞬时增加。
当表上无主键或唯一键时会造成主从延迟，那么对于在该表上做的DML，如果是以ROW模式复制，在从库回放时则每一个行记录前镜像都需要执行一遍，这是与主库不同的地方。在从库回放行记录时，遵循先找主键定位数据，其次是唯一键或二级索引；但唯一键或二级索引都需要回表，比主键要慢。在无主键或索引情况下，在备库每一个行记录都会产生一次全表扫描（也就是说一条delete，如果删了10条，从库会做10次全表扫），大多数情况下，这种开销都是非常不可接受的，并且产生大量的延迟。但有二级索引的情况下会比无主键无索引情况要好一些。
大表，某列无索引，先需要查询该列，删除符合条件的记录，大约占40%数据量，请问有何更好的方案吗？
一、存在其他索引（主键、唯一索引、普通索引）的情况
1、可利用现有索引分段扫描全表，例如每次只读取1000条记录，然后再根据条件进行判断并删除数据（最好是进行归档，而不是真正删除）。
2、由于要删除掉的数据量比较大，会造成InnoDB表较多碎片，可以考虑用反向操作，也就是创建新表，把要保留的数据复制过去，最后再将两个表对调。
3、利用 pt-archiver 工具进行归档。
二、无任何索引的情况
1、真发生这种情况的话，负责的DBA或者相关同学麻烦引咎辞职吧。这句话是开玩笑的，嘿，不过说真的也太挫了。
2、观察条件列，如果区分度较高（不同值较多），就创建一个新索引，再根据索引删除数据。
3、对该表创建一个自增主键列，然后参考上述方案，根据主键索引分段扫描、判断、删除（归档）。
MySQL所有的压力都在一个CPU核心上，为什么会产生这种现象，改如何解决？
一、为什么会产生这种现象？
事实上，并不是所有压力都在一个逻辑CPU上，其实是因为MySQL还不支持并行计算，因此一个会话中的SQL只会被分配到一个逻辑CPU上。
之所以出现这种现象，大概率是因为下面几种情况：
1、某个会话中正在执行慢SQL，尚未结束；
2、某个会话中的SQL开启了大事务，并且持有很多行锁或等待很多行锁，该事务尚未结束
二、如何解决?
1、检查是否有慢SQL，并进行优化
2、尽量不使用大事务，拆分成小事务
3、记住老司机的名言，当mysqld进程消耗CPU很高时，极大概率是有些SQL没索引导致
4、也可以尝试用perf top工具来排查具体什么原因导致
监控MySQL的性能，应该主要观察那几个监控项？
一、liunx操作系统层面
1、整体cpu负载的%user最好不长期超过20%（若%user太高，有极大可能性是索引使用不当）
2、整体cpu负载的%iowat最好不长期超过10%（确认I/O子系统是否有明显瓶颈）
3、整体cpu负载的%idle最好保持在70%以上（让CPU保持低负载）
4、关注各个逻辑CPU之间的负载是否均衡（可能是中断不均衡导致性能问题）
5、关注是否有swap产生（注意关闭NUMA），可使用命令或工具：vmstat、sar、dstat等
二、MySQL状态层面
1、主要关注tps、qps、并发连接数（Threads_connected）、并发活跃线程数（Threads_running）、临时表（*tmp_disk_tables*）、锁（*locks_waited*, Innodb_row_lock*）等指标
2、关注当前是否有不良线程状态，例如：copy*to*tmp table、Creating sort index、Sorting result、Creating tmp table、长时间的Sending data等
3、关注InnoDB buffer pool page的使用情况，主要是Innodb*pages_free、Innodb*wait_free两个
4、关注InnoDB的redo log刷新延迟，尤其是checkpoint延迟情况，并关注unpurge list大小
5、关注innodb status中是否有long semaphore wait的情况出现
6、关注slow query sql的增长情况
RDS上，MySQL实例中某张表数据小于tmp_table_size，但有查询时会报错临时空间满 The table '/data/mysql/zst/tmp/#sql_13975_23' is full。原因可能是什么？
一、可能有下面几种情况：
1、在SQL中执行group by、order by、distinct、union、多表update、子查询、多表JOIN等情况下，可能需要生成内部临时表，当内部临时表超过tmp-table-size时，就会产生磁盘临时表。
2、接上，若查询包含BLOB、TEXT类型字段时，MySQL会直接使用磁盘临时表。
3、云数据库购买的磁盘空间，是包括数据库文件、日志文件（binlog、relay log、error log等）、临时文件&临时表（关注 Created_tmp_disk_tables、Created_tmp_tables、Binlog_cache_disk_use、Binlog_stmt_cache_disk_use等指标）所消耗占用的磁盘空间。
4、发生table...is full报错，说明可能生成磁盘临时表太多，超过云数据库购买的空间限制。
二、解决办法可以有：
1、关注slow query log，或者查看processlist，及时发现需要用到临时文件、临时表的SQL，尽快优化。
2、调高 tmp_table_size 参数值来调高内存临时表的上限。
3、调高参数loose_rds_max_tmp_disk_space值，可设置为当前空闲空间的80%（阿里云RDS专属参数）。
4、优化表DDL设计，尽量避免使用BLOB、TEXT类型字段，并且在SQL中减少对这些大字段的访问。
5、优化查询逻辑，避免使用UNION或者需要中间数据集的子查询等SQL。
云环境上自建MySQL，有哪些高可用实现方案？
1、基于VPC环境， 支持独立分配IP相关IP段的，还是可以考虑VIP方案，云环境把协议阉割，使用TCP方式，如：青云开源的Xenon， MHA 。 在VPC中，是可以自主绑定私有IP，还是比较方便。
2、基于MGR、PXC构建MySQL高可用。因为MGR、PXC无法告知应用端切换后的IP地址，所以建议配合使用类似consul来使用。如果使用多主模式的MGR/PXC，可以使用LVS/haproxy或者SLB等。
3、基于中间件层MySQL高可用。使用consul配合MGR/PXC，或者consul配合MHA使用。
4、基于ProxySQL+Replication-manager+Consul进行构建，用Replication-manager提供主从切换，动态通知proxysql,利用consul感知ProxySQL可用性。
MySQL DBA运维中那些动作属于危险性操作?
1、MySQL无备份、备份无校对
2、执行rm -rf / tmp 等类似操作，执行rm 前要三思。
3、执行kill -9等操作
4、binlog 非row格式，执行dml操作(update、delete)
5、在生产环境执行测试命令。或在生产环境直接调索引
6、避免使用一些骚操作"slave_skip_errors" 或故意导致主从不一致操作
7、drop database 
8、DML操作条件写错， 线上DDL导致业务报错
9、恢复数据，实例不对（基于IP连接管理环境）
10、线上高并发环境运行 flush table ; flush table with read lock; lock table;
11、数据库重启空间不够文件损坏，初始化数据库把机器IO资源占满
12、从库延迟并对外提供服务
13、开多窗口操作重要数据库
14、敏感字段不加密，备份不加密存放，线上数据同步到线下
15、犯困时操作线上环境
MySQL binlog_format=mixed，可行吗，为什么
不可行，因为会导致主从数据不一致
Mixed格式相当于 Row 和 Statement 模式的融合。遇到表结构变更的时候就会以statement模式来记录。像update或者delete等修改数据的语句，还是会记录所有行的变更。
但某些情况就会产生主从数据不一致例如：
1、当带有自增主键的更新多个列的表，并调用触发器或存储函数时
2、当SQL使用LOAD_FILE()功能时。（Bug＃39701）
3、当SQL语句引用一个或多个系统变量时。（Bug＃331168）
更多请参考，https://dev.mysql.com/doc/refman/8.0/en/binary-log-mixed.html
MySQL误删除frm文件该怎么办？
情况一：误删后还未重启MySQL
1、从proc中恢复.frm文件
情况二：误删后也重启MySQL了
2、从备份中获取表结构
2.1 物理备份
从物理备份中直接把.frm文件拷贝回来。
2.2 逻辑备份
找到该表的DDL，在备用实例创建该表，再把.frm文件拷贝回来。
注意事项：
1、无论是情况一还是情况二，都需要重新设置属主和属组。
2、若恢复期间对该表执行了新的DDL，则上述方法可能都无效。
3、本案例在MySQL 5.7.18版本（开启表独立空间模式）下亲测通过。
你遇到过PHP连接MySQL的性能问题吗，如何解决的？
PHP连接MySQL用得比较多的有原生mysql扩展、mysqli和pdo_mysql等，其与MySQL的连接可以分为三类
1、短连接，每次都需要建立新连接，数据库开销较大。尤其是高并发环境下，有可能会把数据库连接数直接打满并造成CPU很高。
2、长连接，但无法像连接池那样做到连接复用，控制不好的话，更容易导致数据库连接数爆表。
3、利用第三方应用提供的连接池功能，如swoole、ProxySQL等，基本上就可以解决连接性能瓶颈了。尤其像ProxySQL这样的还能顺便解决读写分离、高可用切换等问题。
另建议设置net.ipv4.tcp_tw_reuse=1打开tcp重用，提高tcp连接性能。
MySQL常用的SQL调优手段或工具有哪些
1、根据执行计划优化
2、利用profiling优化
3、利用optimizer_trace优化
4、利用session status优化
5、其他优化工具
在一个2c4g的服务器上如何用python操作8GB的超大文件
1、使用with open的方式，for line in f文件对象f视为一个迭代器，会自动的采用缓冲IO和内存管理，并且能够自动关闭文件，推荐该方式
举例：
with open('filename') as f:
2、open file的方式，可以通过read(size)指定每次读取的大小，将大文件切割成小文件来读取，每次处理完小块即释放内存
举例：
f = open(filePath)
while True:
 content = f.read(chunk_size)
 do_things(content)
3、linecache模块，可以指定读取文件某一行
举例：
content = linecache.getline('filename', linenum)
do_things(content)
MySQL反应慢的排查思路
一、导致MySQL慢可能的因素有
1、计算资源不足
2、系统层面未进行基本的优化，或不同进程间资源抢占
3、MySQL配置不科学(附神器：http://imysql.com/my-cnf-wizard.html)
4、垃圾SQL满天飞
二、查看系统层面负载手段
1、top查看整体负载情况，快速确认哪个进程系负载高
2、free查看内存情况，是否有内存泄露和用了swap等风险
3、vmstat/sar查看当前系统瓶颈到底在哪，如CPU、IO、网络等
4、终极神器perf top查看cpu消耗在哪些系统调用函数
三、查看MySQL的整体情况
1、观察show processlist输出中是否有临时表、排序、大量逻辑读、锁等待等状态
2、观察show engine innodb status输出中是否有大事务、长事务、锁等待等状态
四、干掉垃圾SQL，常用手段
1、用explain、desc观察执行计划
2、用profiling定位sql执行的瓶颈
3、用pt-query-digest分析慢sql
五、几个窍门
1、mysqld进程消耗CPU长时间超过90%的话，99.9%是因为没用好索引
2、cpu的%sys高的话，大概率是swap或中断不均衡导致，也可能是有多个索引且超高并发写入(更新)，或者有很严重的锁等待事件
3、最⼤的瓶颈通常是在磁盘I/O上，因此尽量用高速磁盘设备
4、如果物理磁盘无法再升级，则通过增加内存提升性能容量
5、遇到无法诊断的问题时，试试⽤perf top来观测跟踪
6、SQL执行慢，有时未必是效率低，也可能是因为锁等待，甚⾄是磁盘满了
生产环境MySQL死锁如何监控及如何减少死锁发生的概率
首先，死锁并不是"锁死"，死锁是由于两个或两个以上会话锁等待产生回路造成
一、死锁监控及处理方法
对于死锁的监控，各个版本都提供了innodb_print_all_deadlocks选项，打开该选项即会将死锁的日志输出到MySQL的错误日志当中,因此可以通过监控错误日志来达到监控死锁的目的。而对于MariaDB就更加简单了，MariaDB提供了Innodb_deadlocks的计数器，可以通过监控该计数器的增长来监控是否存在发生死锁。
假如线上出现死锁并且频率较高的话，务必要引起重视。由于死锁日志仅记录了最后引起死锁的两条SQL，因此并不能通过死锁日志立即定位
出死锁的原因，应当及时协同开发模拟出死锁过程，分析死锁产生原因，修改程序逻辑。
二、如何降低死锁发生的概率
1、尽量使用短小事务，避免大事务
2、加FOR UPDATE/LOCK IN SHARE MODE锁时，最好降低事务隔离级别，例如用RC级别，降低死锁发生概率，也可以降低锁定粒度
3、事务中涉及多个表，或者涉及多行记录时，每个事务的操作顺序都要保持一致
4、通过索引优化SQL效率，降低死锁概率，避免全表扫描导致锁定所有数据
5、程序中应有事务失败检测及自动重复提交机制
6、高并发（秒杀）场景中，关闭innodb_deadlock_detect选项，降低死锁检测开销，提高并发效率
MongoDB有哪些优秀特性及适合的场景是什么
一、优秀特性
1、实用性：面向类json富文档数据模型，对开发人员天然的友好
2、可用性：基于raft协议的自动高可用，轻松提供99.999%的可用性
3、扩展性：对分片集群的支持，为业务提供了友好的水平扩展
4、高性能：嵌套模型设计支持，减少了离散写，充分的物理内存利用率，避免了磁盘读
5、强压缩：WiredTiger引擎提供多种数据压缩策略，2~7倍的压缩比，大大节省了磁盘资源 
二、适合的场景
1、无多文档事务及多表关联查询需求
2、业务快速迭代，需求频繁变动行业
3、单集群并发过大无法支撑业务增长
4、数据量增长预期TB及以上存储需求
5、期望要求99.999%数据库高可用场景
GO语言对比其他的编程语言有何优势？实际生产环境如何取舍？
1、天生支持高并发，强一致语言，开发效率高兼具线上运行稳定安全
2、垃圾回收，不用关心内存分配与回收
3、强大的GMP模型，异步处理，支持高并发，小白也能轻松写出高并发代码
在实际生产环境中建议从如下几个方面考虑：
1、看业务场景，电商，大数据处理有现成的解决方案，不适合用。另外数学运算，cpu 密集型的也不用。 
2、GO 擅长快速出业务原型，迭代开发效率高，初创公司强推
3、看公司开发的技术栈，如果差异较大，那么选用 GO的话上手更快，编程风格也能统一起来
一个大事务，有很多更新，现在被回滚了，但是又着急关机重启，怎么办才好？
1、首先，尽量避免在MySQL中执行大事务，因为大事务将会带来主从复制延迟等问题
2、大事务被kill，MySQL会自动进行回滚操作，通过show engine innodb status的TRANSACTIONS可以看到ROLLING BACK的事务，并且在回滚操作的时候仍然会持有相应的行锁
3、此时如果强行关闭MySQL，等到MySQL再次启动后，仍然会进行回滚动作
4、因此，为确保数据安全，建议还是耐心等待回滚完成以后再进行关机重启。关机重启前，可以调低innodb_max_dirty_pages_pct让脏页尽量刷新完毕，并且关闭innodb_fast_shutdown
5、假如实在没有办法需要关机的情况下，可以kill -9先关闭MySQL，前提是需要设置双一保证事务安全，否则可能丢更多事务数据。然后重启实例后innodb会自行crash recovery回滚之前的事务
PS， kill -9是高危操作，可能导致MySQL无法启动等不可预知的问题，请谨慎使用
如何降低UPDATE/DELETE时WHERE条件写错，或者压根没写WHERE条件带来的影响
0、尽量不要在线手工执行任何SQL命令，很容易出差错。线上直接执行SQL命令最好有第二检查人帮助确认
1、最好在测试环境执行SQL确认无误后，再到生产环境执行，或者提前在本地文本环境编辑好确认后再执行
2、建议打开sql_safe_updates选项，禁止没有WHERE条件或者不加LIMIT或者没有使用索引条件的UPDATE/DELETE命令被执行。也可以在用mysql客户端连接到服务器端时增加--safe-updates选项，例如：mysql --safe-updates -h xx -u xx
3、线上手动执行DML操作时，先开启事务模式，万一误操作可以回滚。例如：mysql> begin; update xxx; rollback;
4、通过DB管理平台执行DML操作，且在平台上增加对此类危险SQL的判断，直接拒绝危险SQL的执行
5、配置延迟从库，发现误删除数据后，从延迟从库快速恢复数据
MySQL如何控制用户输错密码尝试次数？
一、插件辅助
从官方MySQL5.7.17开始，提供了CONNECTION_CONTROL和CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件，该插件又提供了connection_control_failed_connections_threshold、connection_control_min_connection_delay、connection_control_max_connection_delay三个参数
1、connection_control_failed_connections_threshold
该参数的含义是控制登陆失败多少次数后开启延迟登陆
2、connection_control_min_connection_delay
该参数分别表示超过失败次数后每次重新连接最小的延迟时间，延迟计算公式为(当前失败总次数-失败阈值)*connection_control_min_connection_delay，因此错误尝试次数越多那么延迟时间也是越大
3、connection_control_max_connection_delay
最大延迟时间，超过该值后客户端可重新连接
4、安装插件后，可通过监控Connection_control_delay_generated状态值和INFORMATION_SCHEMA下的表CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS来监控错误登录尝试次数
二、错误日志监控
通过定时扫描MySQL错误日志来捕获账号密码错误次数，达到某个阈值以后可在系统防火墙屏蔽对应的主机ip达到屏蔽账号的目的(具体操作视情况而定)
如：错误日志会显示2019-05-10T13:04:41.232259Z 5 [Note] Access denied for user 'xucl'@'127.0.0.1' (using password: YES)
三、其他说明
1、有些同学会误以为max_connection_errors能够控制错误密码的尝试次数，其实该参数只能防止如telnet类的端口探测，即记录协议握手错误的次数
2、最后，在生产环境一定要关注aborted_clients和aborted_connects的状态，发生异常必须及时关注
#1709 - Index column size too large. The maximum column size is 767 bytes.是什么原因导致的，如何处理？
一、原因
1、对于行格式为REDUNDANT和COMPACT的InnoDB表来说，索引的最大长度为767字节
2、行格式为DYNAMIC和COMPRESSED格式的InnoDB表最大索引长度允许达到3072字节（注：8.0版本之前，要求innodb_file_format=Barracuda，且innodb_large_prefix=1。 且8.0以后该参数已废弃）
3、注意，即便设置innodb_large_prefix=1，但若行格式是REDUNDANT和COMPACT时调整无效，且5.7.6版本以前该参数默认是关闭的，5.7.7版本后默认开启。8.0后该参数也已废弃
4、索引长度计算规则：
二、处理方法
1、降低索引长度，采用部分索引而不是整列索引，如：
create index idx_xx on t(code(30))
2、修改innodb_file_format为Barracuda，同时修改表的row format为DYNAMIC，使得最大索引长度增加到3072字节，如：alter table t row_format=dynamic
举几个你遇到的MySQL中的隐式转换案例
1.表中定义字符类型，where条件传入数字类型，如：
code varchar(10), SQL: select * from t where code=1
2.关联字符类型不一致(驱动表int传入被驱动表字符串中关联)，如：
t1.a int,t2.a varchar(10), SQL: select * from t1 left join t2 on t1.a=t2.a
3.关联两表字符集类型不一致(驱动表大字符集传入被驱动表小字符集关联)，如：
t1 utf8mb4, t2 utf8, SQL: select * from t1 left join t2 on t1.a=t2.a
4.关联两表字符校对规则不一致，如：
t1表coll为utf8_general_ci,t2表为ucs2_general_ci,SQL: select * from t1,t2 where t1.a=t2.a
5.字符串被截断(严格来说不算隐式转换，但容易被忽略)，如SQL: 
where a='1aaa'这个条件也能查到a=1的记录
判断出现类型转换方法：
1.在命令行查看执行计划，通过show warnings可以观察到
due to type or collation conversion on field
2.若发生类型转换，一般来说无法使用索引，执行计划的key那列通常是NULL
你觉得理想的MySQL备份策略应该是怎样的，实际上你真正的备份策略又是怎样的，为什么会有不同，出于什么原因呢？
一、备份目的
备份的主要目的是确保数据安全。在数据文件出现损坏或者误操作时用于数据恢复
二、全备策略
2.1、目前备份策略对于数据量小的库（数据量在50G以下），可以采用逻辑全备的方式(例如mysqldump或mydumper)
2.2、对于数据量大（数据量在50G以上）的库，一般采用xtrabackup进行物理备份
2.3、无论物理备份还是逻辑备份，尽可能在专属从库上执行，尽可能不要在主库上进行备份
2.4、执行备份前，最好先确认数据库中当前没有未结束的大事务，或者未结束的大select
2.5、除了上述备份外，同时也要备份binlog文件
2.6、建议在业务低峰期(例如凌晨)每天做全量备份
2.7、备份文件同时最好放在远程备份服务器上，不要只放在本地
三、增量备份
3.1、xtrabackup支持在全备基础上的增量备份
3.2、或者通过binlog备份做增量
四、其他策略
4.1、如有条件，可以配置延迟从库，在出现数据误删需要快速恢复的情况下，那么可以从延迟从库立刻恢复数据
4.2、对于超大的库（如日志库），可以直接用主从的方式代替备份，master出问题，立即将业务切换到slave上
五、备份文件有效性测试
5.1、除了备份策略以外，还需要验证备份的有效性，定期检验备份集
5.2、备份集检验包含备份有效性和备份完整性监测，通过是否能够恢复备份集并抽样访问表数据来检验备份的有效性，通过数据探针的方式来检验备份完整性（例如：备份前往某个表中插入特定标记数据，恢复后检查该数据是否存在）
MySQL执行sql时一个表只能用到一个索引吗？
答：并不是，以下几种情况一个表可能会使用多个索引
一、index merge
在执行计划的type列显示index_merge，key显示了使用的索引，key_len包含了这些索引列表的最长部分，根据Extra列的显示可以将index merge分为三类
1、Using intersect(...)，对应的算法为Index Merge Intersection，如：c1、c2是两个单列索引，SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2;
2、Using union(...)，Index Merge Union，如：c1、c2、c3是三个单列索引，SELECT * FROM t1 WHERE c1 = 1 OR c2 = 2 OR c3 = 3;
3、Using sort_union(...)，Index Merge Sort-Union，如：c1、c2是两个单列索引，SELECT * FROM t1 WHERE c1 < 10 OR c2 < 20;
二、表自关联
表自关联同样可以用到多个索引，如：
c1、c2是两个单列索引，SQL如下：select * from t as t1 join t as t2 on t1.c1=t2.c2 where t1.c2=1;
补充：
有很多同学答了ICP，ICP是指将单个索引内无法参与index key的索引条件下推到引擎层进行过滤（也就是index filter阶段下推到引擎层去执行），并不是将多个索引下推到引擎层过滤，需要注意区别
MySQL的前缀索引有什么特点？使用上有什么使用场景和限制么?
一、特点：
1、只能对列的部分长度建立索引，减少索引的大小即key_len，达到节省空间、提高查询效率的目的
2、可以通过length函数计算前n个字符长度的记录条数占比来决定前缀索引的长度，一般来说能够覆盖80%-90%即可
例如，一个表总数是100万，我们统计字符串前缀长度小于15个字符的数量是80万，那么前缀索引长度可能15个字符长度基本就够用了
select count(*) from t where length(c1)<=15;
(注意，这个SQL效率很低，不要在线执行)
二、不足、缺点：
1、前缀索引无法使用覆盖索引的特性，因此必须回表获取数据
2、无法利用前缀索引完成分组/排序
三、使用场景：
1、适合乱序数字或前n个字符可选性高的情况或者是text/blob这种无法整列创建索引的大字段
2、不适合于前n个字符重复率很高或者需要利用覆盖索引优化的场景
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尝试数据恢复
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对比每个节点产生的结果
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
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）
为什么long_query_time设置了1秒，slow log中还会记录小于1秒的慢查询？
可能原因如下：
1、设置了全局的long_query_time未对当前连接生效
2、打开了log_queries_not_using_indexes选项，记录了未走索引的SQL
在主从服务器上，同一个表的表空间文件大小相差特别大，可能原因是什么，怎么解决？
可能的原因：
1、MySQL表默认是InnoDB引擎且目前索引只支持B+树索引，在数据的增删改过程中，会导致表产生碎片，主从服务器上同张表的碎片率不同也会导致表空间相差很大
2、主库整理过碎片，从库是从原先的未整理的物理备份中恢复出来的
3、主从表结构不一致，如从库可能比主库多索引
4、主从表的行格式不一致，如主库为dynamic，从库为compressed
5、个别云数据库在从库上可能采用特殊的并行复制技术，导致在从库上有更高的碎片率（有个极端的案例，同一个表在主库只有6G，从库上则有将近150G）
	
解决方式：
1、保证主从表结构一致（包括page大小、索引、行格式等）
2、在业务低峰期使用pt-osc或gh-ost通过alter table xxx engine=innodb;重整表空间，消除碎片（切记：执行前要先检查有无未结束事务或其他未释放锁）
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已取消该选项)
想把MySQL有变更的数据分发到其他异构数据库，都有什么可选方案
一、触发器方式（不推荐）
1、触发器处理的方式，在表上建立触发器，将增删改动作捕捉到增量表
二、基于时间戳
1、表上如果有更新时间/插入时间的时间戳，可以根据时间戳捕捉增量数据
三、基于binlog方式（推荐）
MySQL目前比较好也是目前业务用的比较多的增量方案都是基于binlog实现
1、在MySQL提交的DML操作正常情况都会记录到binlog（也是主从复制的基础），首先上游通过解析binlog获取到增量日志
2、将解析到的更新事件存入消息队列，如kafka/MQ等
3、根据下游数据库种类，分别用不同的消费组件进行队列消费，拼接成SQL进行数据更新
四、业界典型的方案
4.1开源方案
1、阿里巴巴开源组件canal+kafka+自写消费组件
2、阿里巴巴开源组件otter(支持MySQL到MySQL的数据同步)
2、阿里巴巴开源迁移工具yugong（支持Oracle->Oracle/Oracle->MySQL的全量/增量迁移）
3、linkedin开源组件databus
4、美团点评开源组件puma
4.2商业方案
1、各云厂商提供的DTS方案
MySQL什么情况下DML操作不会记录binlog？
以下情况可能会造成DML不会记录binlog：
1、并未开启binlog
2、在执行DML之前set sql_log_bin=0;
3、DML操作提交后，未完成binlog写入，此时MySQL crash，MySQL重新启动后，会回滚该操作
4、DML操作提交后，由于sync_binlog配置为非0，MySQL服务器down机导致binlog并未持久化
5、主库提交事务，同步到从库，但从库并未开启log_slave_updates，也无法记录binlog
6、DML操作在temporary表（非memory引擎）上，由于是会话级的数据变更，并不记录binlog
7、update/delete等DML并没有达到数据更新
8、其他原因如：binlog文件权限、文件句柄、inode耗尽、磁盘空间满等原因导致binlog无法写入
MySQL中ANALYZE TABLE的作用是？生产上操作会有什么风险？
一、ANALYZE TABLE的作用
1、ANALYZE TABLE 会统计索引分布信息
2、对于 MyISAM 表，相当于执行了一次 myisamchk --analyze
3、支持 InnoDB、NDB、MyISAM 等存储引擎，但不支持视图（view）
4、执行 ANALYZE TABLE 时，会对表加上读锁（read lock）
5、该操作会记录binlog
二、生产上操作的风险
1、ANALYZE TABLE的需要扫描的page代价粗略估算公式：sample_pages * 索引数 * 表分区数
2、因此，索引数量较多，或者表分区数量较多时，执行ANALYZE TABLE可能会比较费时，要自己评估代价，并默认只在负载低谷时执行
3、特别提醒，如果某个表上当前有慢SQL，此时该表又执行ANALYZE TABLE，则该表后续的查询均会处于waiting for table flush的状态，严重的话会影响业务，因此执行前必须先检查有无慢查询
备注：上面多次提到MyISAM，仅是本次总结所需，并不推荐大家使用MyISAM引擎，使用InnoDB才是正道
MySQL生产中上亿数据大表怎么在线增加一个字段并设定默认值？
一、MySQL8.0版本
如果满足以下条件：
1、不是压缩表
2、不是data dictionary tablespace
3、不是全文索引表
4、不是临时表
5、新增列在最后位置
满足以上条件，可以通过instant方式完成快速加列，即修改metadata信息，代价非常小，秒级完成
二、MySQL5.6、MySQL5.7
1、可以这么说，虽然支持inplace（不阻塞同时在跑的DML），但还需要rebuild表，代价可能较大并且耗时可能会非常长，也会造成非常严重的主从延迟
2、因此生产环境还是推荐使用pt-osc/gh-ost等第三方工具进行在线加列，
3、需要注意的是pt-osc会建触发器，如果原先表上本身就有触发器就不能使用了
4、另外需要注意的是通过第三方工具加列时需要考虑到空间占用问题（影子表及加列过程中产生的binlog）
5、第三方工具加列同样可能造成主从延迟，因此注意控制速率
详细ONLINE DDL见：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
MySQL表中有自增列id，但是表中的id列数据不连续，产生的可能原因是什么？
可能的情况有如下几种：
1、虽然表定义了自增主键，但是插入时从全局ID生成器获取ID，导致每个表分配到的ID并不是连续的
2、表中数据进行过删除
3、session A插入了数据，获取了自增id为10，session B也插入了数据，但session A回滚了，导致id=10的空洞
4、设置了innodb_autoinc_lock_mode=2，高并发或批量插入的情况下导致自增ID不连续
5、手动调整过AUTO_INCREMENT，导致中间有空洞
6、设置步长非1，导致每次自增并不是加1
PS:8.0以前修改自增值重启后会丢失，务必注意一下
如何定位造成MySQL慢的罪魁祸首慢SQL及分析优化思路？
一、首先需要打开慢查询
1、设置slow_query_log = 1打开慢查询
2、通过设置long_query_time调整慢查询SQL的阈值
3、通过设置min_examined_row_limit记录慢查询的最小扫描行数
二、其次关注慢查询记录中的重要关注点
1、Query_time：慢查询的实际查询时间
2、Rows_sent：发送给客户端的行数
3、Rows_examined：扫描的行数
4、最后需要关注具体的sql及对应的执行计划
三、执行计划需要关注的部分可以参考
四、慢SQL分析需要的元素
1、表结构信息：show create table
2、数据量信息：select count(*) from table/show table status like 'xxx';/information_schema.tables
3、索引统计信息：show index from
4、执行计划：EXPALIN select * from t
五、主要的优化手段有如下：
1、SQL语句的改写，优化不良语句
2、符合业务场景需求的索引建立
3、表结构上对列属性、表结构的调整
4、MySQL参数的调整
5、和开发应用"撕"性能
六、最后有一些注意点：
1、实战优化很难一步到位，模拟测试环境很重要（不要随便在生产环境测试）
2、业务至上，数据至上，而后才是优化
3、衡量得失，索引也有维护成本，不是越多越好
4、没有对比就没有成就感，记得留存"胖胖"的SQL，形成优化报告
5、防范于未然，不要等到问题严重了才来做优化
MySQL中InnoDB表自增主键如何从0开始？
1、实际上，MySQL无法真正设置InnoDB表AUTO_INCREMENT=0
2、但是我们可以人为修改SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
3、指定自增列插入0值，如insert into t(id,name) values(0,'zhangsan');
另外有几点说明：
1、尽管MySQL允许设置SQL_MODE为NO_AUTO_VALUE_ON_ZERO并插入0值，但是还是不建议这样做，例如主从SQL_MODE不一致可能造成数据不一致
2、8.0以后AUTO_INCREMENT支持持久化，妈妈再也不用担心自增值丢啦
MySQL常见的主从复制错误有哪些？如何处理？
一、1032错误
1032错误即从库比主库少数据，分为UPDATE场景和DELETE场景
UPDATE场景处理思路是：
1、主要处理思路是将丢失的数据在从库补上
2、找到sql_thread停止位置(Relay_Master_Log_File + Exec_Master_Log_Pos)
3、去主库解析对应位置binlog，获取到UPDATE相关的数据
4、在slave上插入数据
5、重启sql_thread
DELETE场景处理思路：
1、主要的处理思路就是跳过该事务
2、非GTID环境下通过设置sql_slave_skip_counter=1跳过该事务
3、GTID环境下通过注入空事务方式跳过该事务，简要步骤如下：
4、重启sql_thread
二、1062错误
1062错误即从库比主库多数据（主键冲突情况）
处理思路比较简单，即删除对应的主键冲突数据即可：
1、删除从库上对应的数据（根据主键）
2、重启sql_thread
三、1236错误
1236错误即从库需要开始同步的binlog位置已经不存在主库上，该错误出现分几种情况
1、GTID从库需要开始复制的GTID要比主库上已经purged GTID位置要小
2、GTID从库GTID_SET比主库上缺失部分（如主库有多个GTID_SET，而从库只有一个）
3、非GTID环境下，从库去拉binlog的时间找不到对应的binlog（即从库当前的binlog比主库最旧的binlog之间存在缺失）
那么对应的处理方式是：
1、对于1、3两种情况，建议是直接通过备份的方式直接重新做主从
2、对于第2种情况，可以通过set global gtid_purged='xxx'的方式将差异的GTID_SET部分补全
Redis中如何发现并优化生产环境的big key？
Redis中的大key一直是重点需要优化的对象，big key既占用比较多的内存，也可能占用比较多的网卡资源，造成redis阻塞，因此我们需要找到这些big key进行优化
一、寻找big key
通常来说找到redis中的big key有如下几种方法
1、redis-cli自带--bigkeys，例如：redis-cli -h <hostip> -a <password> --bigkeys
2、获取生产Redis的rdb文件，通过rdbtools分析rdb生成csv文件，再导入MySQL或其他数据库中进行分析统计，根据size_in_bytes统计bigkey
3、通过python脚本，迭代scan key，每次scan 1000，对扫描出来的key进行类型判断，例如：string长度大于10K，list长度大于10240认为是big bigkeys
4、其他第三方工具，例如：redis-rdb-cli
地址：https://github.com/leonchen83/redis-rdb-cli
二、优化big key
1、优化big key的原则就是string减少字符串长度，list、hash、set、zset等减少成员数
2、以hash类型举例来说，对于field过多的场景，可以根据field进行hash取模，生成一个新的key，例如原来的
hash_key:{filed1:value, filed2:value, filed3:value ...}，可以hash取模后形成如下key:value形式
hash_key:mod1:{filed1:value}
hash_key:mod2:{filed2:value}
hash_key:mod3:{filed3:value}
...
取模后，将原先单个key分成多个key，每个key filed个数为原先的1/N
3、string类型的big key，如文章正文，建议不要存入redis，用文档型数据库MongoDB代替或者直接缓存到CDN上等方式优化
生产环境Redis中的热点key如何发现并优化？
Redis的热点key问题同样值得我们关注，Redis的热点key出现主要有以下几种情况
1、用户消费的数据远大于生产的数据（热卖商品、热点新闻、热点评论、明星直播）
2、请求分片集中，超过单Server的性能极限。
热点key可能造成如下问题：
1、流量集中，达到物理网卡上限
2、请求过多，缓存分片服务被打垮
3、DB击穿，引起业务雪崩
如何发现热点key：
1、通过tcpdump抓包，可以分析抓取到网络包分析key的频率
2、redis客户端抓取，例如请求key的时候记录日志，分析日志得到key的访问频率
3、redis的monitor可以记录redis的所有操作，记录并分析monitor日志得到key的访问频率（注意：monitor可能会造成性能问题，慎重使用）
4、Redis 4.0提供了—hot-keys配合maxmemory-policy可以统计热点key
5、第三方开源项目，如facebook开源项目redis-faina，原理同3
解决方案如下：
1、对于”get”类型的热点key，通常可以为redis添加slave，通过slave承担读压力来缓解
2、服务端本地缓存，服务端先请求本地缓存，缓解redis压力
3、多级缓存方案，通过多级请求，层层过滤解决热点key问题
4、proxy方案，有些方案会探测分片热点key，缓存在proxy上缓解redis压力
5、同解决big方案类似，将一个key通过hash分解为多个key，value值一样，将这些key分散到集群多个分片中，需要访问时先根据hash算出对应的key，然后访问的具体的分片
你们公司有哪些核心的MySQL应用开发规范?
这里重点介绍下Schema设计规范
一、schema设计原则：
1、尽量小的原则，不浪费
2、为了高并发，禁止使用外键
3、每个表必须有主键
4、字符集和库级保持一致，不单独定义字段字符集
二、字段规范：
1、每个表建议不超过30-50个字段
2、优先选择utf8mb4字符集，它的兼容性最好，而且还支持emoji字符。如果对存储容量比较敏感的，可以改成latin1字符集
3、严禁在数据库中明文存储用户密码、身份证、信用卡号（信用卡PIN码）等核心机密数据，务必先行加密
4、存储整型数据时，默认加上UNSIGNED，扩大存储范围
5、建议用INT UNSIGNED存储IPV4地址，查询时再利用INET_ATON()、INET_NTOA()函数转换
6、如果遇到BLOB、TEXT字段，则尽量拆出去，再用主键做关联
7、在够用的前提下，选择尽可能小的字段，用于节省磁盘和内存空间
8、涉及精确金额相关用途时，建议扩大N倍后，全部转成整型存储（例如把分扩大百倍），避免浮点数加减出现不准确问题
三、常用数据类型参考：
1、字符类型建议采用varchar数据类型（InnoDB建议用varchar替代char）
2、金额货币科学计数建议采用decimal数据类型，如果运算在数据库中完成可以考虑使用bigint存储，单位：分
3、自增长标识建议采用int或bigint数据类型，如果该表有大量的删除及再写入就使用bigint,反之int就够用
4、时间类型建议采用为datetime/timestamp数据类型
5、禁止使用text、longtext等的数据类型
6、字段值如果为非负数，就加上unsigned定语，提升可用范围
四、SQL规范
1、在MySQL中SQL语句一般不区分大小写，全部小写
2、sql语句在使用join, 子查询一定先要进行explain确定执行计划
3、为每个业务收集sql list.
高可用架构MHA有什么样的不足和风险点吗？
MHA作为传统复制下的高可用霸主，在今天的GTID环境下，开始慢慢走向没落，更多的人开始开始选择replication-manager或者orchestrator等高可用解决方案
不足及风险点：
1、failover依赖于外部脚本，比如VIP切换需要自己编写脚本实现
2、MHA启动后只检测主库是否正常，并不检查从库状态及主从延迟
3、需要基于SSH免认证配置，存在一定的安全隐患
4、没有提供从服务器的读负载均衡功能
5、从节点出现宕机等异常并没有能力处理，即没有从库故障转移能力
6、在高可用切换期间，某些场景下可能出现数据丢失的情况，并不保证数据0丢失
7、无法控制RTO恢复时间
为什么pt-osc操作表以后中文注释显示???，如何避免？
一般来说，生产环境使用的表都会使用中文注释表信息以及字段信息，但是如果使用pt-osc且未指定字符类型的情况下进行在线变更后，中文注释都会变成"???"，虽然不影响正常使用，但是对于认为阅读起来会造成困扰，某些平台会依据注释生成数据字典，因此正确的姿势是在使用pt-osc工具时通过--charset=utf8指定utf8字符集
示例：
pt-online-schema-change -h 127.0.0.1 -u xxx -p xxx --alter="add index idx_id(id)" --chunk-size=5000 \
--print --no-version-check --execute D=xucl,t=test --charset=utf8
MySQL 5.6升级5.7都有什么注意事项
一、升级方式
MySQL升级的方式一般来说有两种
1、通过inplace方式原地升级，升级系统表
2、通过新建实例，高版本作为低版本的从库进行滚动升级
MySQL5.7版本做了非常多的改变，升级5.6到5.7时需要考虑兼容性，避免升级到5.7之后因为种种参数设置不正确导致业务受影响，建议首先逐一查看release note
二、需要注意的参数及问题：
1、sql_mode：MySQL 5.7采用严格模式，例如ONLY_FULL_GROUP_BY等
2、innodb_status_output_locks：MySQL 5.7支持将死锁信息打印到error log（其实这个参数MySQL 5.6就已支持）
3、innodb_page_cleaners：MySQL 5.7将脏页刷新线程从master线程独立出来了，对应参数为innodb_page_cleaners
4、innodb_strict_mode：控制CREATE TABLE, ALTER TABLE, CREATE INDEX, 和 OPTIMIZE TABLE的语法问题
5、show_compatibility_56=ON：控制show变量及状态信息输出，如果未开启show status 命令无法获取Slave_xxx 的状态
6、log_timestamps：控制error log/slow_log/genera log日志的显示时间，该参数可以设置为:UTC 和 SYSTEM，但是默认使用 UTC
7、disable_partition_engine_check：在表多的情况下可能导致启动非常慢
8、range_optimizer_max_mem_size：范围查询优化参数，这个参数限制范围查询优化使用的内存，默认8M
9、MySQL 5.7新增优化器选项derived_merge=on，可能导致SQL全表扫描，而在MySQL 5.6下可能表现为auto key
10、innodb_undo_directory && innodb_undo_logs：MySQL 5.7支持将undo从ibdata1独立出来（只支持实例初始化，不支持在线变更）
11、主从复制问题：MySQL5.7到小于5.6.22的复制存在bug(bug 74683)
12、SQL兼容性问题：SQL在MySQL 5.7和MySQL 5.6环境下结果可能不一致，因此建议获取线上SQL，在同样数据的环境下，在两个实例运行获取到的结果计算hash，比较hash值做兼容性判断
三、友情提醒
1、升级前一定要做好备份！！！
2、升级正式环境前提前在测试环境进行仔细测试，确认无误以后再升级正式环境
3、做好相应的回退方案

在用阿里云、腾讯云等公有云时，你是如何评估新建主机/数据库对象的配置级别？？
这里以云下业务迁移云上为例来探讨
1、首先熟悉现有业务的基本架构，比如一主多从、sharding架构等，并且知道相应的业务分布
2、获取现有业务的监控获取到的峰值QPS、TPS、IOPS、CPU使用率、磁盘使用量、内存使用量、最大连接数等关键指标
3、获取现有数据库的关键参数指标，如innodb_buffer_pool_size等
4、公有云每个规格都提供了相应的参数指标，如：核数、内存、IOPS、最大连接数等指标
5、根据第2、3、4步选择相应规格的RDS，原则为RDS规格参数大于现有环境状态指标，其中IOPS需要进行换算(云上的IOPS一般按4k算，而自建的一般按16k算)
6、上云前最好先购买实例进行测试，包括使用sysbench进行标准压测、业务兼容性测试、业务压测等来判断实例规格是否满足性能要求，，建议云上实例性能预留比如20-30%浮动空间
7、特别提醒，云上实例通常会把binlog以及SQL运行产生的临时表、临时文件也计入磁盘空间，此外云上数据表的碎片率可能会比自建实例大很多（曾经遇到本地5G的表云上占用120G），因此要特别注意磁盘空间要预留充足
8、最后说明一点，迁移云上最好选择数据库版本同自建版本。还有，尽量不要使用云上数据
ALTER TABLE 出现duplicate primary xxx报错的原因及处理？
好多同学都曾经问过这个问题，还有同学说这是bug，实际上这并不是bug
一、原因分析
1、Online DDL操作时MySQL会将DML操作缓存起来存入到变更日志
2、等到DDL执行完成后再应用变更日志中的DML操作
3、在Oline DDL执行期间，并行的DML可能会没先检查唯一性直接插入一条相同主键的数据，这时并不会导致DDL报错，而是在DDL执行完成再次应用变更日志时才报错，最终导致DDL报错执行失败
二、问题说明
其实这是Online DDL的正常情况，官方文档说明如下：
When running an in-place online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction
ref：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html
三、建议
1、推荐使用pt-osc、gh-ost等第三方工具进行DDL操作
2、建议在业务低谷期进行操作
InnoDB在什么情况下会触发检查点(checkpoint)?
一、MySQL的checkpoint分类
1、sharp checkpoint（激烈检查点，要求尽快将所有脏页都刷到磁盘上，对I/O资源的占有优先级高）
2、fuzzy checkpoint（模糊检查点，会根据系统负载及脏页数量适当平衡，不要求立即将所有脏页写入磁盘，这事默认的方式）
二、触发时机
1、数据库正常关闭时，即innodb_fast_shutdown=0时需要执行sharp checkpoint
2、redo log发生切换时或者redo log快满的时候进行fuzzy checkpoint
3、master thread每隔1秒或10秒定期进行fuzzy checkpoint
4、innodb保证有足够多的空闲page，如果发现不足，需要移除lru list末尾的page，如果这些page是脏页，那么也需要fuzzy checkpoint
5、innodb buffer pool中脏页比超过innodb_max_dirty_pages_pct时也会触发fuzzy checkpoint
三、checkpoint相关参数及状态
1、innodb_fast_shutdown
2、innodb_io_capacity/innodb_io_capacity_max
3、innodb_lru_scan_depth
4、innodb_max_dirty_pages_pct/innodb_max_dirty_pages_pct_lwm
5、Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
6、Innodb_buffer_pool_wait_free
update t set a=29 and b in (1,2,3,4);这样写有什么问题吗？
一、SQL分析
乍一看这个SQL貌似没有什么问题，本意是将t表中b的值属于1/2/3/4的数据的a列修改为29，但实际上该SQL是将t表数据的a列改成了条件29 & b in (1,2,3,4)的真假判断值
即：update t set a = (29 and b in (1,2,3,4));
修改后的SQL应该为update t set a = 29 where b in (1,2,3,4);
二、注意事项
1、生产环境中进行批量数据修改时应该开启事务，修改确认后再进行提交操作
2、进行DML操作时，建议还是通过SQL审核工具审核后执行
3、建议打开sql_safe_updates选项，避免没有WHERE条件的更新、删除操作
如何将excel数据导入MySQL表中？
将excel导入MySQL表的方式有很多，这里列举几种平时常用的方法：
1、将excel另存为csv文件，再使用LOAD DATA导入表，命令参考如下：
LOAD DATA INFILE 'c:/tmp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
2、利用Navicat、MySQL Workbench等第三方工具进行导入
3、excel利用函数拼接成insert SQL进行数据插入(数据量大时不推荐，效率极低)
4、例行批量导入，安利python的xlwt模块
注意：进行数据导入时注意先执行set names设置字符集，以免造成乱码
用xtrabackup跑mysql物理备份，建议授予哪些权限？
可能需要用到以下权限：
1、RELOAD and LOCK TABLES (用于FLUSH TABLES WITH READ LOCK 和 FLUSH ENGINE LOGS)
2、BACKUP_ADMIN （用于查询表performance_schema.log_status, 执行LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, 或 LOCK TABLES FOR BACKUP）
3、REPLICATION CLIENT（获取一致性位点）
4、PROCESS（用于执行SHOW ENGINE INNODB STATUS或者查看线程状态等）
5、SUPER（复制环境下用于start/stop the slave threads）
6、SELECT（使用选项--incremental-history-name or --incremental-history-uuid时获取innodb_to_lsn插入到PERCONA_SCHEMA.xtrabackup_history表）
select里用rand()，怎么优化效率？
案例：
select id from t1 where id = round(rand()*13241324);
其中id列是IINT类型的主键
一、问题点
该SQL的问题点主要在于当使用rand()匹配时，实际上是逐行提取数据，rand()每次生成一个随机数进行单行匹配，即如果t1表有100万数据就会匹配100万次，即便有索引也没用，也要全表扫描
二、优化方式
优化方式主要有2种思路，第一种是通过子查询关联，第二种是通过范围查询加limit 1，如下所示：
1、select id from t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2
2、select id from t1 where id > (select round(rand()*(select max(id) from t1)) as nid) limit 1
再次提醒，不要用rand()直接进行匹配或者排序，会引发性能灾难
参考：
http://imysql.com/2014/07/04/mysql-optimization-case-rand-optimize.shtml
现在业内常用的MySQL高可用方案有哪些？
目前来说，比较多的开源方案分内置高可用与外部实现，内置高可用有如下：
1、官方版本分支：MGR（首推）
2、percona分支：PXC
3、MariaDB：Galera Cluster
外部实现方案：
1、orchestrator(GTID)
2、replication-manager(GTID)
3、MHA(传统复制)
4、MOHA(支持多AZ部署)
5、其他...
什么时候MySQL的索引"失效"不可用？
1、通过索引扫描的记录超过20%~30%，可能会变成全表扫描
2、联合索引中，查询条件不符合左侧前导要求
3、查询条件列最左以通配符%开始
4、查询条件发生数据类型隐式转换，或者字符集不匹配
5、HEAP表使用HASH索引时，使用范围检索或者ORDER BY
6、多表关联时，排序字段不属于驱动表，无法利用索引完成排序
7、JOIN查询时，关联列数据类型（字符集）不一致也会导致索引不可用
8、不可见索引，即便force index也不可用
9、违反索引排序规则
MySQL从库show processlist出现system lock的原因以及解决方法有哪些？
由于大量的小事物如UPDATE/DELETE table where一行数据，这种只包含一行DML event的语句，table是一张大表。
1、这个表上没有主键或者唯一键，可以考虑尝试修改参数slave_rows_search_algorithms。
2、由于类似innodb lock堵塞，也就是slave从库修改了数据同时和sql_thread也在修改同样的数据。
3、确实I/O扛不住了，修改sync_binlog/innodb_flush_log_at_trx_commit或者提高IO子系统的IO能力
友情提示：
MySQL数据库表建议都设置int/bigint的自增主键，"业务主键"设置为not null + 唯一索引
周末了来一个轻松的python算法题：有64瓶药，其中63瓶是无毒的，只有一瓶是有毒的。如果小白鼠喝了有毒的药，3天后会死掉，喝了无毒的药，喝了多少瓶都没事。现在只剩下3天时间，请问最少需要多少只小白鼠才能试出哪瓶药有毒？
现在公布正确答案啦：
总共需要六只小白鼠。
6只小白鼠当做二进制的位数，那么小白鼠可以表示从000000 - 111111，也就是十进制的0-63个数，药按照1-64排好号，比如第一瓶药编号1，对应小白鼠000001，最右边的小白鼠喝，以此类推，比如第8瓶药，001000，第三只小白鼠喝。从1-63号药都喝一遍。假如最后死的小白鼠对应二进制是001111，那么是15号药有毒。
MySQL的slow log中Query_time包含了lock_wait_time吗？
首先给一个slow log的头部示例：
# Time: 2019-10-08T08:46:34.635823Z
# User@Host: root[root] @ localhost [] Id: 16
# Query_time: 0.064742 Lock_time: 0.000460 Rows_sent: 1 Rows_examined: 9997
其中：
1、Query_time为SQL的消耗时间，包括了Lock_time
2、Lock_time为锁等待的时间，包括行锁、MDL锁等
3、是否记录slow log的判定条件为SQL的实际执行时间（Query_time - Lock_time）是否超过long_query_time，或者是否开启log_queries_not_using_indexes
MySQL的data目录下有很多innodb_status.xxx文件，咋回事？
1、当MySQL启动时添加选项--innodb-status-file或my.cnf设置innodb_status_file = 1，会在data目录下生成innodb_status.xxx文件
2、当打开选项innodb_status_output选项后，每隔约15秒即会刷新innodb status信息到文件中(手动show engine innodb status数据也会写入文件)，并可能影响性能
3、当打开选项innodb_status_output选项后，innodb status信息及innodb row lock/deadlock信息(打开innodb_status_output_locks选项)也会以追加的方式写入到error log，可能导致error log文件过大，请务必注意这一点
4、innodb_status.xxx的xxx是当前mysqld的pid，即innodb_status.pid
5、正常关闭时MySQL会自动删除innodb_status.pid文件，当异常关闭mysqld时，会留下上次启动的innodb_status.pid文件，当多次异常关闭后data目录下就会产生很多innodb_status.pid文件
MySQL参数eqrange index dive limit的作用以及如何理解index dive?
首先解释一下什么是index dive:
在MySQL里只要存在范围查找方法，就可以通过索引下潜来估计范围内的行数，方法是找出范围的开始和结束，并计算出他们之间的行数。这项技术更精确，所以也是制定良好执行计划的一个基础。
而参数eq_range_index_dive_limit限定了进行索引下潜的等值条件的最大值+1，
1、当等值条件个数大于或等于eq_range_index_dive_limit，那么优化器将直接使用统计信息
2、当eq_range_index_dive_limit设置为0时，优化器将始终进行索引下潜，而不用索引统计信息
例如有如下SQL:
select * from t where col_name IN(val1, ..., valN)，当eq_range_index_dive_limit为N+1，优化器就会使用index dive来计算执行计划costindex dive适用条件有以下形式
(1) col_name IN(val1, ..., valN)
(2) col_name = val1 OR ... OR col_name = valN
col_name为非唯一索引8.0以后优化器在满足以下条件可能会跳过index dive，而8.0以前无法避免index dive：
查询时只访问一张表，而不是多表关联
force index(某个索引)
没有子查询
没有涉及全文索引
没有GROUP-BY or DISTINCT
没有ORDER-BY
请用python一条语句将:
[[1, 4, 7, 11], [2, 5, 8, 12], [3, 6, 9, 13]] 
这个题有不少同学解出来了，这里给出两种解法:
一、第三方库numpy
>>> a = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
>>> import numpy as np
>>> np.mat(a).T
matrix([[1, 4, 7],
二、列表推导式
>>> a = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
>>> [[row[i] for row in a] for i in range(len(a[0]))]
[[1, 4, 7], [2, 5, 8], [3, 6, 9]]

你平时在做SQL优化的时候通常会用到哪些简单有效的手段呢？
一、SELECT
1.单表SELECT
2.多表JOIN 查询
二、INSERT
三、UPDATE
详情：视频回放⎜《资深DBA带你学习SQL开发和优化》
六、MySQL主从复制结构下，如何判定是异步复制还是半同步复制？
对于半同步的监控可以采用如下方式：
mysql
>
 show 
global
 status like 
'%Rpl_semi%'
;
1、Rplsemisyncmasterstatus表示主库是否启用半同步 
2、Rplsemisyncslavestatus表示从库是否启用增强半同步
3、Rplsemisyncmastertxavgwaittime表示等待slave响应的事务平均等待时间，如果该值比较大的话可以检查一下网络情况了 
4、Rplsemisyncmastertxwaits表示slave响应的事务数，该值如果增长较快的话也需要检查准备之间的网络情况 
5、Rplsemisyncmasteryestx表示增强半同步复制下的事务数 
6、Rplsemisyncmasternotx表示异步复制的事务数，该值如果变化了，那么也需要检查半同步复制是否已经退化为异步复制，在退化时从error log也可以看到 
7、Rplsemisyncmasterstatus表示当前节点是否是半同步master
七、MySQL半同步退化成异步复制以后，网络恢复后还会自动切换为半同步复制吗？

1、即便会自动恢复，但是仍然需要做好监控，避免由于异步复制下的主从切换而导致数据丢失
2、金融支付环境建议将rpl_semi_sync_master_timeout设置为较大值，避免退化为异步复制


update执行过程：

一、Server层阶段：
1、连接器：负责跟客户端建立连接、获取权限、维持和管理连接
2、分析器：验证通过以后，分析器会对该语句分析, 判断是否语法有错误等。
3、优化器：选择索引，生成执行计划。
4、执行器：根据优化器生成的执行计划，调用存储引擎API执行SQL。

二、InnoDB引擎层阶段：
1、事务执行阶段：
进入InnoDB后引擎层，首先会判断该SQL涉及到的页是否存在于缓存中，如果不存在则从磁盘读取该行记录所在的数据页并加载到BP缓冲池。
假设不存在，然后通过 B+Tree 读取到磁盘的索引页加载到BP缓冲池中 ，如何加载到BP缓冲池中：
首先 通过 space id 和 page no 哈希计算之后把 索引页加载到指定的 buffer pool instance 中
判断 free list 是否有空闲页可用(Innodb_buffer_pool_pages_free、 Innodb_buffer_pool_wait_free)，没有则淘汰脏页或者lru list的Old页
把数据页 copy到 free list中，然后加载到 lru list的 old区的 midpoint（头部）；
通过二分法查找该页对应的记录，试图给这个SQL涉及到的行记录加上排他锁，过程如下：
如果事务当前记录的行锁被其他事务占用的话，就需要进入锁等待；
进入锁等待之后，同时判断会不会由于自己的加入导致了死锁；
检测到没有锁等待和不会造成死锁之后，行记录加上排他锁。
写逻辑的undo：
将修改前的记录写入undo中
修改当前行的值，填写事务编号，使用回滚指针指向undo log中的修改前的行
从而构建回滚段，用于回滚数据和实现MVCC的多版本
写redo log buffer：
先判断redo log buffer是否够用，redo log buffer不够用就等待，体现在状态值 Innodb_log_waits 上;
在 BP缓冲池 的 Lru list中old区的midpont中对该数据页的行记录的字段值做更新操作，并把修改之后的字段值写入到redo log buffer中
并给LSN加上当前redo log写入的长度(写入长度为 length 的redo log，LSN就会加上 length)
（因为redo group commit的原因，这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上）
字段值在BP缓冲池更新成功以后，对应的数据页就是脏页了
写binlog cache：
同时修改的信息，会按照event的格式,记录到binlog_cache中。
写change buffer:
之后把这条sql, 需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge
(随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)
事务commit or rollback:
此时update语句已经完成，需要commit或者rollback。这里讨论双1即sync_binlog=1 和 innodb_flush_log_at_trx_commit=1；

2、假设事务COMMIT
（1）事务的COMMIT 分为prepare阶段与commit阶段
事务的COMMIT操作，在存储引擎层与server层之间采用的是内部XA；
两阶段提交协议, 保证两个事务的一致性,这里主要保证redo log和binlog的原子性；
（2）redo log prepare:
写入 redo log处于prepare状态 并且写入事务的xid；
将 redo log buffer 刷新到 redo log磁盘文件中，用于崩溃恢复；  #刷盘的方式由 innodb_flush_log_at_trx_commit 决定
（3）binlog write&fsync: 执行器把 binlog cache 里的完整事务和 redo log prepare中的XID 写入到 binlog 中
dump线程会从binlog_cache里把event主动发送给slave的I/O线程，同时执行 fsync刷盘(大事务的话这步非常耗时)，并清空 binlog cache。
#事务中写 binlog 的部分日志：
190511 11:06:54 server id 123306  end_log_pos 439 CRC32 0x1c809de0     Xid = 614
COMMIT/*!*/;
binlog刷盘的方式由 sync_binlog 决定；binlog写入完成，事务就算是成功。
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog file 中。当sync_binlog为1时，当binlog落盘以后才会通知dump thread进行主从复制
（4）redo log commit: commit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit，说明事务提交成功。
（5）事务提交成功，释放行记录持有的排他锁；
（6）当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作:
先把该脏页复制到doublewrite buffer里，其次把doublewrite buffer里的刷新到共享表空间（ibdata），然后才是把脏页写入到磁盘中；
这时候内存页与磁盘的数据页一致。

3、假设事务ROLLBACK
如果事务因为异常或者被显式的回滚了，那么所有数据变更都要改回去。这里就要借助回滚日志中的数据来进行恢复了。
对于in-place(原地)更新，将数据回滚到最老版本；
对于delete+insert方式进行的，标记删除的记录清理删除标记，同时把插入的聚集索引和二级索引记录也会被直接删除。


MySQL中各参数的默认值有哪些是不合理的？

1、innodb_ buffer_ pool _size默认128M， 建议物理内存的50%-70%；
2、1og_ timestamps默认UTC, 建议SYSTEM；
3、time_ zone默认SYSTEM, 建议" +8: 00"；
4、join_ _buffer_ size默认2K，建议4M；
5、sort_ _buffer_ size默认2K,建议4M；
6、read_ rnd_ _buffer_ size默认2K，建议4M；
7、max_ connections默认151, 建议根据实际情况设定；
8、max_ connect_ errors默认100， 建议100000；
9、max_ allowed_ packet默认4M，建议32M；
10、interactive. timeout默认28800,建议300秒；
11、wait_ timeout默认28800， 建议300秒；
12、lock_ wait_timeout默认-一年，建议6秒；
13、long_ query_time默认10秒， 建议0.1~0.5；
14、binlog_cache_ size默认32K， 建议32M；
15、max_binlog_ cache_ size默认非常非常大，建议1G；
16、innodb_ log_ file_ size默认48M, 建议1~2G；
17、innodb_ log_ files_ in_ group默认2,建议3组以上；
18、innodb_ io_capacity默认200 ，根据实际磁盘io能力设定；
19、innodb_ open_ files默认-1,不限制 ，建议65535；
20、innodb_max_ dirty_ pages_ pct默认75，建议50；
21、innodb_ online_ alter_ log_ max_ _size默认128M，建议4G；
22、innodb_undo_ log truncate默认关闭， 建议打开；


MySQL8.0到目前为止有哪些真香特性？

一、优化器
1、anti join
2、hash join
3、CTE
4、window function
5、不可见索引
6、倒序索引
7、直方图
8、新增 Multi-valued indexes
9、新增Index skip scan

二、管理
1、新增备份锁BACKUP LOCK，避免FTWRL
2、快速加列，DBA福音
3、原子DDL
4、admin连接，额外
5、在线修改undo数量
6、支持参数修改持久化，SET PERSIST

三、复制
1、json增强，支持部分更新写binlog
2、InnoDB ReplicaSet

四、插件
1、clone plugin
2、MySQL Shell

五、安全
1、认证插件默认为caching_sha2_password
2、支持role
3、支持resource group
4、支持表空间加密

六、开发
默认utf8mb4字符集，支持表情

七、InnoDB
1、支持自增ID持久化
2、新增SKIP LOCK，NO WAIT
3、新增 TempTable 引擎




主从测试过程中，如何模拟网络抖动？

推荐工具：tc，常用的使用方式如下：

1、模拟延迟 
tc qdisc add dev eth0 root netem delay 1000ms 100ms 50%
# 这里表示有50%的包延迟会在1000+-100ms之间波动

2、模拟丢包
tc qdisc add dev eth0 root netem loss 1% 30%
# 这里表示eth0网卡丢失1%的包，成功率为30%

3、模拟包重复
tc qdisc add dev eth0 root netem duplicate 1%
# 设置eth0网卡随机产生1%的重复包

4、模拟包损坏
tc qdisc add dev eth0 root netem corrupt 1%
# 设置eth0网卡随机产生1%的损坏数据包

5、模拟包乱序
tc qdisc change dev eth0 root netem delay 10ms reorder 25% 50%
# 设置eth0网卡有25%的数据包(50%的关联度)会被立即发送,其他的延迟10ms发送。


使用 pt-osc原生 5.6 online ddl相比，如何选择
online ddl在必须copy table时成本较高，不宜采用
pt-osc工具在存在触发器时，不适用
修改索引、外键、列名时，优先采用online ddl，并指定 ALGORITHM=INPLACE
其它情况使用pt-osc，虽然存在copy data
pt-osc比online ddl要慢一倍左右，因为它是根据负载调整的
无论哪种方式都选择的业务低峰期执行
特殊情况需要利用主从特性，先alter从库，主备切换，再改原主库



当binlog_format=statement时 临时表的所有操作都会被记录下来
当binlog_format=row时 临时表的所有操作都不会被记录下来

有什么方案可以优雅地实现MySQL大表count操作？

1、首先MySQL innodb引擎并不是特别适合做count操作(MyISAM虽然count代价很小，但是不推荐再使用了)

2、对于count操作，MySQL会优先选择非空的二级索引，原因是一般来说二级索引所占的空间更小，读取的成本会更低

3、MySQL8.0对于没有WHERE条件的count支持并行读，效率更高

4、可考虑Redis或ES等nosql或newsql实现计数的方案，例如：业务写入MySQL同时对Redis对应的key做incr或decr等操作


1. 多表join 如何优化？
答：最多两张表join原则，主键或唯一键关联，通过执行计划。

2. 请问一下，mgr里的事务丢失是什么情况？
答：5.7.25以下低版本，在通过mgr确认和认证之后，底层回放的时候丢失。

3.mysql怎么评估支撑的连接数？
答：show engine innodb status 里有等在信息，信号量的一些统计，可以参考，建议并发不要超过1000。

4. 8.0现在生产用得多吗？
答：还是比较多的。5.7版本第一个阶段维护到10月份到期，后面支持会越来越少。

5. 8.0生产环境目前推荐哪个版本？
答：8.0.18版本开始。

6. 增强半同步参数有没有什么架构建议？
答：AFTER_SYNC和timeout设置。

7. 业务反应不想用VIP漂的形式了高可用形式了，有没有其他的解决方案。
答：域名方式 或则 中间件（proxysql推荐，myql router，Maxscale）。

8. MGR主要应用场景？
答：硬件方面：单机房 或则 跨机房 网络条件好 。数据量建议不要超过500G， 一致性要求高。

9. mysql innodb/ndb cluster 实际生产中用得多么？
答：mgr用的还是可以的，基本一些大厂都在用 / ndb使用场景少。

10. 生产环境一般用什么存储引擎？
答：铁定mysql innodb引擎。

11. 如何尽可能一次性故障信息收集要收集些什么？假如现场人员对现象把握不准，并避免多次交互？
答：收集信息可以 监控指标图，系统 message 日志，mysql参数，慢日志，  binglog，innodb status。

12. gtid是不是有些限制？5.6版本的做主备的时候，推荐启用gtid吗？
答：限制有的，一些语句不支持，可以自行网上查询一下。gtid 建议还是5.7版本使用。

13. MySQL什么场景下比pg有优势？
答：两个数据库角度不一样，底层实现也不一样；开发中的差异性 等等；MySQL最大的优势就是社区解决方案，周边资源多 和 稳定性 等等。

14. gtid是不是有些限制？5.6版本的做mha的时候，推荐启用gtid吗？
答：限制有的，一些语句不支持，可以自行网上查询一下；gtid 建议还是5.7版本使用。

15. mysql8.0.18 数据割接时 批量导入数据事务超过MGR 组复制限制时 primary节点就无法与其他通信了，会发生primary切换  xcom 底层是如何传输事务的？
答：xom通过Batching和Pipelining方式进行消息传输的。以下连个参数都可以有	效控制堵塞通信的。group_replication_transaction_size_limit，
group_replication_message_cache_size

16. 双主+VIP架构，数据不一致怎么修复？
答：pt-table-checksum可是使用，但注意有可能破坏主从。

17. MongoDB会不会部分替代MySQL？
答：这个可能性非常渺茫，只能说MySQL能替代mongodb可能性，8.0版本有很多类似的分析的功能出现，json支持也在完善中。

18. 高可用架构选择哪种比较好？自增字段。
答：高可用 按照业务情况选择，满足业务，选合适的，自己最熟悉的。心里没底 就主从+增强半同步既可以

19. 说MySQL不建议单库超过TB级别？
答：超过TB级别，要看怎样使用：比如分区表，高可用主从异步。或则定期迁移走数据。要做好归档部分。

20. MGR组内通信使用的是广播、组播还是点播啊？
答：mgr xcom协议算组播方式，小组成员之间必须始终保持双向通信。

23. 并发参数那个调配方法如何尝试？
答：4个参数innodb_thread_concurrency. innodb_adaptive_max_sleep_delay，innodb_thread_sleep_delay，innodb_concurrency_tickets，可以通过官场最高性能指标的情况进行设置。

24. 升级的话 MYSQL8 哪个版本稳定？
答：8.0.18版本开始比较稳定，升级可以看下墨天轮（www.modb.pro）上有专门8.0版本升级方式。