😨MySQL 问题集合

Mysql专题

2020-06-18

数据库读写分离架构情况下，如何保证主从数据一致性？

1.可以将强一致性的操作指向主库
2.主从双写
总结：强一致性的操作做不到读写分离，可以适当用缓存进行缓冲

基于业务场景，如果是当前业务场景像朋友圈，或者微博，发送之后，自己先读到，那么可以本地缓存一份，别人异步查询到

3（mysql proxy）.mater节点增加一张表，slave节点也是，只要进行insert和update操作都去自增一条记录，master同步到从的时候也去写到从表中。当有一个查询条件过来时，比较两个节点的表中最新一条字段如果是一致的，那么说明当前的数据状态是一致的，就可以查找从库，否则从主库去取。
缺点：进行了double请求，造成了额外的压力，但是不过由于Proxy与真实的Mysql Server采用连接池的方式连接，因此额外的压力还是可以接受的。

2020-06-19

数据库联合索引 失效的场景和注意点?

1、最左匹配
2、mysql优化器，判断你走索引成本大于全表
3、字段类型不匹配
4、like
5、函数运算
6、or运算（存疑）
7、隐式转换

2020-07-10

mysql对于千万级的大表如何优化？

很多人第一反应是各种切分；我给的顺序是:
第一优化你的sql和索引；
第二加缓存，memcached,redis；
第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；
第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；
第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
mysql数据库一般都是按照这个步骤去演化的，成本也是由低到高；
有人也许要说第一步优化sql和索引这还用说吗？的确，大家都知道，但是很多情况下，这一步做的并不到位，甚至有的只做了根据sql去建索引，根本没对sql优化（中枪了没？），除了最简单的增删改查外，想实现一个查询，可以写出很多种查询语句，不同的语句，根据你选择的引擎、表中数据的分布情况、索引情况、数据库优化策略、查询中的锁策略等因素，最终查询的效率相差很大；优化要从整体去考虑，有时你优化一条语句后，其它查询反而效率被降低了，所以要取一个平衡点；即使精通mysql的话，除了纯技术面优化，还要根据业务面去优化sql语句，这样才能达到最优效果；你敢说你的sql和索引已经是最优了吗?
再说一下不同引擎的优化，myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）；myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,
innodb一般都是行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的，普通的select是不需要锁的，当查询的记录遇到锁时，用的是一致性的非锁定快照读，也就是根据数据库隔离级别策略，会去读被锁定行的快照，其它更新或加锁读语句用的是当前读，读取原始行；因为普通读与写不冲突，所以innodb不会出现读写饿死的情况，又因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低；
ps:很奇怪，为什innodb的索引叶子节点存的是主键而不是像mysism一样存数据的物理地址指针吗？如果存的是物理地址指针不就不需要二次查找了吗，这也是我开始的疑惑，根据mysism和innodb数据存储方式的差异去想，你就会明白了，我就不费口舌了！
所以innodb为了避免二次查找可以使用索引覆盖技术，无法使用索引覆盖的，再延伸一下就是基于索引覆盖实现延迟关联；不知道什么是索引覆盖的，建议你无论如何都要弄清楚它是怎么回事！
尽你所能去优化你的sql吧！说它成本低，却又是一项费时费力的活，需要在技术与业务都熟悉的情况下，用心去优化才能做到最优，优化后的效果也是立竿见影的！

2020-07-17

1.为什么mysql页的大小是4kb的整数倍？

2020-09-07

1.redo log的概念是什么？为什么会存在？

在思考这个问题之前，我们可以先看看MySQL INNODB执行一条update语句的流程
1. 将数据所在的数据页加载到BufferPool对应的缓存页(事务开始)
2.将数据的旧值写入到undolog中
3.更新数据在BufferPool中的值
4.写redolog日志
5.写binlog归档日志
6.事务提交，在redolog中写commit标记。
7.数据会在之后的某个时刻刷入硬盘中的数据页。
redolog其实就是上面第四步的操作，他记录的是数据修改之后的值，他的意义在于，当我们事务提交之后但修改的数据还没有刷入磁盘的时候机器宕机了，那么我们就可以根据redolog中的日志来恢复内存中的数据，那为什么不直接在提交事务的将修改的数据刷入硬盘？原因有以下几点：
1.数据在内存中修改的数据很快，但磁盘随机写很慢。
2.数据在磁盘和内存中是以页为单位的，默认是16kb，假如你就改了一天数据的某个属性就需要将这一整个数据页刷入硬盘，效率很低。而redolog就快很多，首先他不是随机在磁盘写，而是通过追加的方式写入redolog文件，速度快很多，然后他也不是直接往硬盘中的redolog文件写，而是先写入redologbuffer（默认大小16mb） 中的redolog block（默认大小512字节）这个内存结构中，首先往redologbuffer中写的很快，然后会在极短时间内以redolog block为单位刷入到磁盘，这样的话性能就会很高。

2020-09-08

1.什么是WAL(write_ahead log )机制？好处是什么？

我们首先以redo log为例站在WAL机制的角度上来看redo log的执行步骤:

(1).将redo log写入到redo log buffer中的redo log block中

(2).调用写磁盘操作( write ) ,这-步将redo log buffer中的redolog日志写到操作系统中的page cache里面。

(3).调用os的fsync操作,将page cache中的redolog日志落盘,落盘之后数据便在磁盘上了。

上面就是WAL(write_ ahead log )机制在redo log刷盘过程中的具体实现,其实我的理解就是将日志先写到内存中,然后在后的某个时间在刷入磁盘,日志异步落盘。其实把数据从内存写入到磁盘看似就一个操作,为啥要拆分成多个步骤?当然是为了性能啦

好处:

(1).从磁盘随机写变成顺序写,性能提升巨大。

(2).组提交,这个是啥意思呢,在上面redolog的执行步骤第三步,调用fsync可以同时将多次第二步write操作写入到page cache的数据- -批落盘 ,这样的话就大量的减少了iops,减少磁盘压力。这个思想在redis持久化中也有提现,大家可以看看aof日志落盘的操作。

2020-09-09

1.redo log为什么能保证crash safe机制？

2020-09-10

1.redo log和bin log 不同点有哪些？

1. Redo Log是属于InnoDB引擎功能;Binlog是属于MySQL Server自带功能，所有引擎都可
   以使用，并且是以二进制文件记录。
   2. Redo Log属于物理日志，记录该数据页更新状态内容;Binlog是逻辑日志，记录更新操作语
   句的原始逻辑。
   3. Redo Log日志是循环写，日志空间大小是固定;Binlog是追加写入，写完一个写下一个，不
   会覆盖使用。
   4. Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据
   恢复使用。Binlog没有自动crash-safe能力。

2020-09-11

1.物理一致性和逻辑一致性应该怎么理解？

2020-09-14

1.什么是两阶段提交？为什么需要两阶段提交？两阶段提交如何保证数据库中两份日志间的逻辑一致性？

2020-09-15

1.执行器在执行update语句时候的流程是什么样子的？

2020-09-16

1.如果不是两阶段提交，先写binlog和先写redolog两种场景，各会遇到什么问题？

2020-09-17

1.如何使用缓冲实现惰性B树？

2020-09-18

1.mvcc的实现原理？

首先每行数据会按照row trx_id记录下每个版本的undolog，根据undolog就可以向前回溯获得某一个状态。
这就是快照（多视图）的原理。

其次，在特定的事务隔离级别下，innodb会为事务都构建一个事务id数组：
（低水位）进行中的事务（高水位）

当该事务需要read的时候，可以判断到读取的该行的trx_id，如果trx_id比低水位低，那表示该数据已提交，可见；如果trx_id比高水位高，证明该事务未开始，不可见，则需要根据多个视图回溯到当前事务可见的数据版本；如果trx_id位于之间，则分两种情况，如果trx_id在数组中，则表示数据尚未提交，不可见，如果trx_id不在数组中，则表示数据已经提交，可见。

按照这个原理就可以实现mvcc，控制特定隔离级别下，不同事务对彼此的可见性。

2021-12-03

之前聊的火热的with … as，它和临时表在原理上到底有啥区别，哪个更好用？
https://www.cnblogs.com/zhaowei303/articles/4204805.html

2021-12-09

Mysql的分区表是什么？为何不建议使用它？

2021-12-02

昨天我们一起了解了一下mysql的临时表，你觉得你会在平常的工作中使用它吗？可以列举一些你觉得适合使用它的场景。
你有时候in的条件特别多的时候可以建立个临时表 将查询条件写到临时表 然后在关联查询就好了

2021-12-01

MySQL可以创建与正常表同名的临时表，因为两表是同名的，那我增删改查的时候操作的是临时表还是正常表？

对于这个问题，优先搞清楚mysql在什么情况会使用临时表，以及为什么使用临时表(临时表的作用)

当我们需要保存一些临时结果时或者一些复杂的操作需要使用额外的空间进行操作时会使用临时表。不过临时表仅限于当前会话，并且对Show tables不可见。
当使用了临时表，操作指定了临时表名，那么操作的是临时表，如果临时表和实际表名相同，我理解应该是可能会操作临时表，可能是实际表，只是个人理解

临时表分两种：1. 外部临时表通过语句create temporary table…创建的临时表 2.内部临时表: 主要有两类，一类是information_schema中临时表，另一类是会话执行查询时，如果执行计划中包含有“Using temporary”时，会产生临时表
临时表的存储也有两类 1：内存 2：磁盘

对于临时表和实际表名相同，个人理解是会操作实际表的。当时如果指定了临时表名，那么是操作临时表

第一点，补充一下其他情况临时表没在内存的。
1. 存在BLOB 、TEXT 的表（TempTable 引擎除外）
2. 存在 string 字段长度大于 512 的

2021-11-15

双8G内存和单16G内存速度谁比较快？
双通道快

2021-11-16

如果只需要去重，不需要执行聚合函数，distinct 和 group by 哪种效率高一些呢？

2021-11-22

使用sql语句查询MySQL数据库，什么情况下会出现隐式转换，导致字段有索引的情况下不走索引。

隐式转换，最常见的就是 传入的值与表字段的类型不一致， 比如 数字跟字符串进行比对，那么字符串会被转换成数字，再进行比较。
（还有好几个规则，可以参照下官网所提及的）

而在索引中，如果字段的值需要被隐式转换，那么就失去了意义，因为每个索引 key 都需要先转换，才能进行匹配，所以不走索引。 （如果只是传入的值被隐式转换，那么还是会走索引的。）

还有隐式编码转换，比如两个表进行 join， 一个表是 utf8，一个表是 utf8mb4，那么会进行向上转化， utf8 -> utf8mb4， 这种情况也是会不走索引

2021-11-25

删库之后一定要跑路吗，除了跑路还能怎么办？（这里指的删库，可能是误删行数据，清空表/删除表，rm数据库实例）
1. 误删行

可以用 Flashback 工具通过闪回把数据恢复回来。

Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。最好在从库进行操作。

预防误删数据行的方法：

1. 把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
   2. 代码上线前，必须经过 SQL 审计。

1. 误删库 / 表

   这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。在这两个条件都具备的情况下，假如有人中午 12 点误删了一个库，恢复数据的流程如下：

   1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
      2. 用备份恢复出一个临时库；
      3. 从日志备份里面，取出凌晨 0 点之后的日志；
      4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

   预防误删库 / 表的方法

   第一条建议是，账号分离。这样做的目的是，避免写错命令。比如：

   • 只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
     - 即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。

   第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。比如：

   • 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
     - 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

2. rm 删除数据

   对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

   这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。

   当然了，现在不止是 DBA 有自动化系统，SA（系统管理员）也有自动化系统，所以也许一个批量下线机器的操作，会让你整个 MySQL 集群的所有节点都全军覆没。

   应对这种情况，我的建议只能是说尽量把你的备份跨机房，或者最好是跨城市保存。

2021-11-11

为什么innodb 的compact行格式记录头信息中的null值列表，变长字段列表中的数据是逆序存储的？请说说这样做有什么好处。
1. 如果不记录null值列表，就无法表示某个字段为null的情况了，因为null和空串还是有区别的(java中特别突出), 如果直接使用字段存储值null至少占四个字符，太浪费空间了
2. 变长为什么逆序存放：这个是因为作者设计页中数据二分查找时next_rec指针指向的位置是记录头信息和真实数据之间的位置，这样往左边就是记录头、null值列表、变长列表，右边就是真实数据，这样两个指针一个左一个右，方便读取数据

你会不会觉得next_record这个指针有点儿怪，为啥要指向记录头信息和真实数据之间的位置呢？为啥不干脆指向整条记录的开头位置，也就是记录的额外信息开头的位置呢？

因为这个位置刚刚好，向左读取就是记录头信息，向右读取就是真实数据。我们前边还说过变长字段长度列表、NULL值列表中的信息都是逆序存放，这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近，可能会提高高速缓存的命中率。

2021-11-09

Mysql innodb是如何保证被修饰auto-increment字段值的生成是连续的？
1. innodb_autoinc_lock_mode = 0(传统锁模式)
在这个模式下，所有的 INSERT 语句在插入有自增属性的列时都要获取一个特殊的 AUTO-INC 表级锁。该锁的持有时间到语句结束(而不是到事务结束，一个事务中可能包含多条语句)，它能够确保为有自增属性列在 INSERT 一行或者多行数据时分配连续递增的值。
2. innodb_autoinc_lock_mode = 1（连续锁模式）
这是默认的锁模式。在这个模式下，大量插入每条语句执行时都将获得特殊的表级 AUTO-INC 锁，语句执行完成后释放。每次只有一条语句可以执行并持有 AUTO-INC 锁。
Bulk inserts(大量插入)：
如果大量插入的源表和目标表是不同的，则在源表第一行数据获得共享锁之后，目标表就加上 AUTO-INC 表锁；
如果大量插入的源表和目标表是同一张表，当源表选取所有行获得共享锁之后，目标表才加上 AUTO-INC 表锁。
Simple inserts(简单插入)：
通过 MUTEX(轻量级的锁) 而不是 AUTO-INC特殊表锁控制插入分配自增属性列的值；
MUTEX 只在分配值的过程中持有，而无需等到语句结束，并且性能花销更少；
简单插入不持有 AUTO-INC 锁，但如果其他事务持有，需等待其他事务释放，就像大量插入那样
3. innodb_autoinc_lock_mode = 2（交叉锁模式）
在这种锁模式下，没有插入语句使用 AUTO-INC 表级锁，并且多条语句可以并发执行。这是最快并且扩展性最好的锁模式，但是如果binlog使用基于语句级复制的在从库重放SQL语句时是不安全的。