Mysql 问题收集

Mysql问题收集

主从同步流程

异步模式

主节点

1、当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中；
2、当从节点连接到主节点时，主节点会创建一个叫做 binlog dump 的线程；

3、一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；

4、当主节点的 binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的 binlog 内容发送给从节点；

从节点

当开启主从同步的时候，从节 点会创建两个线程用来完成数据同步的工作。

I/O线程： 此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay log,并将读取到的主库bin log文件名和位置position记录到master-info文件中，以便在下一次读取用;

SQL线程： 该线程读取 I/O 线程写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作。

主从配置一般都是和读写分离相结合，主服务器负责写数据，从服务器负责读数据，并保证主服务器的数据及时同步到从服务器。

全同步模式

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步模式

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

binlog记录格式

MySQL 主从复制有三种方式：基于SQL语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。对应的binlog文件的格式也有三种：STATEMENT,ROW,MIXED。

  • Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4 及之前的版本都是使用的这种复制格式。优点是只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。
  • Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。
  • Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

选型与配置

mysql主从模式默认是异步复制的，而MySQL Cluster是同步复制的，只要设置为相应的模式即是在使用相应的同步策略。

从MySQL5.5开始，MySQL以插件的形式支持半同步复制。其实说明半同步复制是更好的方式，兼顾了同步和性能的问题。

集群方案

  1. 首先反对大家做读写分离，关于这方面的原因解释太多次数（增加技术复杂度、可能导致读到落后的数据等），只说一点：99.8%的业务场景没有必要做读写分离，只要做好数据库设计优化 和配置合适正确的主机即可。

2.Keepalived+MySQL --确实有脑裂的问题，还无法做到准确判断mysqld是否HANG的情况；

3.DRBD+Heartbeat+MySQL --同样有脑裂的问题，还无法做到准确判断mysqld是否HANG的情况，且DRDB是不需要的，增加反而会出问题；

3.MySQL Proxy -- 不错的项目，可惜官方半途夭折了，不建议用，无法高可用，是一个写分离；

4.MySQL Cluster -- 社区版本不支持NDB是错误的言论，商用案例确实不多，主要是跟其业务场景要求有关系、这几年发展有点乱不过现在已经上正规了、对网络要求高；

5.MySQL + MHA -- 可以解决脑裂的问题，需要的IP多，小集群是可以的，但是管理大的就麻烦，其次MySQL + MMM 的话且坑很多，有MHA就没必要采用MMM

事务

redo

它记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页，且只能恢复到最后一次提交的位置。

有了redo以后，innodb引擎会把更新记录先写入redo log中,再修改Buffer Bool中的数据，这个时候状态为prepare状态，还未真正提交成功，要等bin log写入磁盘以后，才会变成为commit状态，事务才算是提交完成。

至于缓存中更新的数据文件何时刷入磁盘，则由后台线程异步处理，这样即使缓存刷入磁盘发生宕机，也可以在重启时解析redo log重新刷盘。

redo log用到了WAL（Write-Ahead Logging）技术，这个技术的核心就在于修改记录前，一定要先写日志，并保证日志先落盘，才能算事务提交完成。

redo log日志满了，在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求的，此刻MySQL的性能会下降。所以在并发量大的情况下，合理调整redo log的文件大小非常重要。

undo

如其名主要起到回滚的作用，它是保证事务原子性的关键。记录的是数据修改前的状态，在数据修改的流程中，同时会记录一条与当前操作相反的逻辑日志到undo log中。

我们举个栗子：假如更新ID=1记录的name字段，name原始数据为小富，现改name为程序员内点事

事务执行update X set name = 程序员内点事 where id =1语句时，先会在undo log中记录一条相反逻辑的update X set name = 小富 where id =1记录，这样当某些原因导致服务异常事务失败，就可以借助undo log将数据回滚到事务执行前的状态，保证事务的完整性。

bin log与redo log区别

  • 层次不同：redo log 是InnoDB存储引擎实现的，bin log 是MySQL的服务器层实现的，但MySQL数据库中的任何存储引擎对于数据库的更改都会产生bin log。
  • 作用不同：redo log 用于碰撞恢复（crash recovery），保证MySQL宕机也不会影响持久性；bin log 用于时间点恢复（point-in-time recovery），保证服务器可以基于时间点恢复数据和主从复制。
  • 内容不同：redo log 是物理日志，内容基于磁盘的页Page；bin log的内容是二进制，可以根据binlog_format参数自行设置。
  • 写入方式不同：redo log 采用循环写的方式记录；binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上。
  • 刷盘时机不同：bin log在事务提交时写入；redo log 在事务开始时即开始写入。

bin log 与 redo log 功能并不冲突而是起到相辅相成的作用，需要二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

MySql更新流程

MySQL更新数据的基础流程，其中包括redo log、bin log、undo log三种日志间的大致关系

三种日志总结

undo log让mysql有回滚事物的能力，redo log让mysql有崩溃恢复的能力，以及我们现在说的bin log让MySQL有搭建集群、数据备份、恢复数据的能力

问题

手动用begin开启事务，然后执行update语句，再然后执行commit语句，那上面的update更新流程哪些是update语句执行之后做的，哪些是commit语句执行之后做的?

实际上，redo log在内存中有一个redo log buffer,binlog 也有一个binlog cache.所以在手动开启的事务中，你执行sql语句，其实是写到redo log buffer和binlog cache中去的(肯定不可能是直接写磁盘日志，一个是性能差一个是回滚的时候不可能去回滚磁盘日志吧)，然后当你执行commit的时候，首先要将redo log的提交状态游prepare改为commit状态，然后就要把binlog cache刷新到binlog日志(可能也只是flush到操作系统的page cache，这个就看你的mysql配置)，redo log buffer刷新到redo log 日志(刷新时机也是可以配置的)。 如果你回滚的话，就只用把binlog cache和redo log buffer中的数据清除就行了。

在update过程中，mysql突然宕机，会发生什么情况?

1.如果redolog写入了，处于prepare状态，binlog还没写入，那么宕机重启后，redolog中的这个事务就直接回滚了。

2.如果redolog写入了，binlog也写入了，但redolog还没有更新为commit状态，那么宕机重启以后，mysql会去检查对应事务在binlog中是否完整。如果是，就提交事务；如果不是，就回滚事务。 (redolog处于prepare状态，binlog完整启动时就提交事务，为啥要这么设计? 主要是因为binlog写入了，那么就会被从库或者用这个binlog恢复出来的库使用，为了数据一致性就采用了这个策略) redo log和binlog是通过xid这个字段关联起来的。

MVCC 多版本并发控制

通过多版本并发控制MVCC解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此Innodb的RR隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。

MVCC解决读写互不阻塞和不重复读的问题。

MVCC只在READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容。

因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

Undo日志版本链

undolog隐藏两个字段trx_id(事务id)roll_pointer(指向上一次修改记录指针)

ReadView

Read View 主要来帮我们解决可见性的问题的, 即他会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。

在 Read View 中有几个重要的属性：

  • trx_ids，系统当前未提交的事务 ID 的列表。
  • low_limit_id，未提交的事务中最大的事务 ID。
  • up_limit_id，未提交的事务中最小的事务 ID。
  • creator_trx_id，创建这个 Read View 的事务 ID。

每开启一个事务，我们都会从数据库中获得一个事务 ID，这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

那么，一个事务应该看到哪些快照，不应该看到哪些快照该如何判断呢？

其实原则比较简单，那就是事务ID大的事务应该能看到事务ID小的事务的变更结果，反之则不能！举个例子：

假如当前有一个事务3想要对某条记录进行一次快照读的时候，他会先创建一个Read View，并且把当前所有还未提交的事务的信息记录下来。比如up_limit_id = 2，low_limit_id = 5，trx_ids= [2,4,5]，creator_trx_id= 6

我们前面说过，每一条记录上都有一个隐式字段db_trx_id记录对这条记录做了最新一次修改的事务的ID，如db_trx_id = 3；

那么接下来，数据库会拿这条记录db_trx_id和Read View进行可见性比较。

如果db_trx_id<up_limit_id，则说明，在Read View中所有未提交的事务创建之前，db_trx_id = 3的这个事务就已经提交了，并且在这期间，并没有新的事务提交。所有，这条记录对当前事务就应该是可见的。

如果，db_trx_id>low_limit_id，则说明，db_trx_id = 3的这个事务是在Read View中所有未提交的事务创建之后才提交的，也就是说，在当前事务开启之后，有别的事务修改了数据并作了提交。所以，这个记录对于当前事务来说应该就是不可见的。（不可见怎么办呢？后面讲）

那么，还有另外一种情况，那就是up_limit_id > db_trx_id > low_limit_id，这种情况下，会再拿db_trx_id和Read View中的trx_ids进行逐一比较。

如果，db_trx_id在trx_ids列表中，那么表示在当前事务开启时，并未提交的某个事务在修改数据之后提交了，那么这个记录对于当前事务来说应该是不可见的。

如果，db_trx_id不在trx_ids列表中，那么表示的是在当前事务开启之前，其他事务对数据进行修改并提交了，所有，这条记录对当前事务就应该是可见的。

所以，当读取一条记录的时候，经过以上判断，发现记录对当前事务可见，那么就直接返回就行了。那么如果不可见怎么办？没错，那就需要用到undo log了。

当数据的事务ID不符合Read View规则时候，那就需要从undo log里面获取数据的历史快照，然后数据快照的事务ID再来和Read View进行可见性比较，如果找到一条快照，则返回，找不到则返回空。

所以，总结一下，在InnoDB中，MVCC就是通过Read View + Undo Log来实现的，undo log中保存了历史快照，而Read View 用来判断具体哪一个快照是可见的。

MVCC和可重复读

其实，根据不同的事务隔离级别，Read View的获取时机是不同的，在RC下，一个事务中的每一次SELECT都会重新获取一次Read View，而在RR下，一个事务中只在第一次SELECT的时候会获取一次Read View。

所以，可重复读这种事务隔离级别之下，因为有MVCC机制，就可以解决不可重复读的问题，因为他只有在第一次SELECT的时候才会获取一次Read View，天然不存在重复读的问题了。

再有人问你什么是MVCC，就把这篇文章发给他！

Mysql事务隔离与锁

  • 未提交读：事务读不加锁,写入操作对修改数据加行级共享锁
  • 已提交读：事务对读取的数据加行级共享锁(读的时候才加锁),一旦读取结束,立刻释放,事务对数据更新的瞬间,加行级排他锁,直到事务结束才释放.
  • 可重复读：事务对读取的数据加行级共享锁(读的时候才加锁),直到事务结束才释放,事务对数据更新的瞬间,加行级排他锁,直到事务结束才释放.
  • 串行化：读取数据时,必须先加表级共享锁,直到事务结束才释放.更新数据时,必须先加表级排他锁,直到事务结束才释放.

深入分析事务的隔离级别

Mysql 当前读 快照读

MySQL-当前读、快照读、MVCC

当前读

  • select...lock in share mode (共享读锁)
  • select...for update
  • update , delete , insert

当前读的实现方式：next-key锁(行记录锁+Gap间隙锁)
间隙锁：只有在Read Repeatable、Serializable隔离级别才有，就是锁定那些范围空间内的数据，假设锁定id>3的数据，id有3,4,5，那么4，5和后面的数字都会被锁定，像6,7...，为什么要这样？因为如果我们不锁定没有的数据，当加入了新的数据id=6，就会出现幻读，间隙锁避免了幻读。

快照读

单纯的select操作，不包括上述 select ... lock in share mode, select ... for update。　　　　
Read Committed隔离级别：每次select都生成一个快照读
Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读

快照读的实现方式：undolog和多版本并发控制MVCC

Mysql explain 优化

  • system: 表中只有一条数据. 这个类型是特殊的 const 类型.

  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
    例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.

    explain select * from user_info where id = 2

  • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.

    EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id

  • ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.

    EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5
  • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
    当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
  • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
    index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
  • ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
    下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

type 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

MySQL 性能优化神器 Explain 使用分析

联合索引在B+树上的结构

首先，表T1有字段a,b,c,d,e，其中a是主键，除e为varchar其余为int类型，并创建了一个联合索引idx_t1_bcd(b,c,d)，然后b、c、d三列作为联合索引，在B+树上的结构正如上图所示。联合索引的所有索引列都出现在索引数上，并依次比较三列的大小。

联合索引在B+树上的存储结构及数据查找方式

Mysql中的innodb与myisam区别？

  1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

  2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

  3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

  5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

慢查询优化基本步骤

  1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE

  2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

  3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

  4. order by limit 形式的sql语句让排序的表优先查

  5. 了解业务方使用场景

  6. 加索引时参照建索引的几大原则

  7. 观察结果，不符合预期继续从0分析

mysql索引失效的场景

  • 使用!= 或者 <> 导致索引失效
  • 字类型不一致导致的索引失效（字段类型与查询条件类型不一致）
  • 函数导致的索引失效
  • 运算符导致的索引失效
    SELECT * FROM user WHERE age - 1 = 20;
  • OR引起的索引失效

    OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

  • 模糊搜索导致的索引失效

MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！

导致MySQL索引失效的几种常见写法

MySQL - 负向查询完全不能走索引么？

「MySQL系列」索引设计原则、索引失效场景、limit 、order by、group by 等常见场景优化

我面试几乎必问：你设计索引的原则是什么？怎么避免索引失效？

mysql对千万级数据的优化

mysql数据表规模90000000(九千万)左右，怎么优化查询？

MySQL 对于千万级的大表要怎么优化？

mysql对执行语句分析

mysql索引命中分析

MySQL索引原理及慢查询优化

为什么不建议将字段设置为可以为null

  • Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位（如果为not null，那么null标志位中就不用存储该行数据的该字段是否为null，即节省了一位）
  • NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错

MySQL 一千个不用 Null 的理由

or 、in | not in 、is null | is not null、!=、<>,使用，是否走索引

  1. 全表扫描是否比索引更快，以至于优化器选择全表扫描
  2. mysql-server 的版本。。。
  3. 可以通过优化语法或者配置优化器，走索引

not in或者!=会导致索引失效并不是绝对的 对于数据较为均匀的场景是会失效的 但是如果业务数据严重不均的字段加了索引的话是不一定失效的 mysql自己会做判断 并不是绝对判定不使用索引 比如表A性别列有男10000条女20条，当sex!=’男‘是可以使用索引的 同样的如果你sex='男'反而不会使用索引 mysql自己会选择最优的检索方式

那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引，那到底什么时候索引，什么时候采用全表扫描呢？

答案很简单：成本。当然，关于如何定量的计算使用某个索引执行查询的成本比较复杂，我们在小册中花了很大的篇幅来唠叨了。不过因为篇幅有限，我们在这里只准备定性的分析一下。对于使用二级索引进行查询来说，成本组成主要有两个方面：

  • 读取二级索引记录的成本
  • 将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

很显然，要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）。

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦，我们就不展开说了，小册里有说），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1，而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。

反正不论采用index dive还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。

理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。

MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！

MySQL索引原理及慢查询优化

无效索引

  • 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
  • 对于多列索引，不是使用的第一部分(第一个)，则不会使用索引，就是未匹配到最左匹配原则
  • like查询是以%开头
  • 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  • 对where查询条件中，对列使用了函数的（day(column)=....）
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

Mysql索引失效的几种情况

Mysql有效索引和无效索引的介绍

MySQL高级 之 索引失效与优化详解

使用select * 效率低的原因

  1. 不需要的列会增加数据传输时间和网络开销
  2. 对于无用的大字段，如 varchar、blob、text，会增加 io 操作
  3. 失去MySQL优化器“覆盖索引”策略优化的可能性

索引性能优化

  • 最左匹配原则
  • 覆盖索引
  • 索引下推

在MySQL 5.6中 引入的索引下推优化（index condition push down)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，比如 like '张%'

Mysql存储方式

InnoDB 储存引擎支持有四种行储存格式：COMPACT、Redundant、Dynamic 和 COMPRESSED。

下面我们将重点介绍 COMPACT 行格式：

COMPACT 行存储格式大概类似这样：

变长字段的长度列表，null值列表，数据头，column01的值，column02的值，column0n的值......

为了让磁盘空间得到最大的利用率，每个数据行都是紧紧地挨在一起的。

变长字段长度列表,存储长度使用的是16进制,如果有多个变长字段,那么是按照逆序存储的

Null值列表,长度必须为8bit的倍数,用二进制的bit位来表示字段是否为null,1就表示为null,反正为0,NULL字段列表也是逆序存储的

数据头的大小为 40 个bit位。信息如下:

名称 大小 (bit) 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树里每一层的非叶子节点里的最小值都有这个标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在记录堆的位置信息
record_type 3 标识当前记录的类型：0代表的是普通类型，1代表的是B+树非叶子节点，2代表的是最小值数据，3代表的是最大值数据。
next_record 16 表示当前记录的真实数据到下一条记录的真实数据的地址偏移量

加上数据头的实际存储：

0x06 0x08 00000101 0000000000000000000010000000000000011001 howinfun m foshan

数据头中的next_record,表示当前记录的真实数据到下一条记录的真实数据的地址偏移量,下一个字节就是真实数据

为什么next_record要指向数据头跟真实数据之间?

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

隐藏字段

除了变长字段长度列表、NULL值列表、40个bit位的数据头和真实数据，其实还包含了一些隐藏字段：

  1. DB_ROW_ID 字段：如果我们没有指定主键和unique key唯一索引的时候，他就内部自动加一个ROW_ID作为主键。
  2. DB_TRX_ID 字段：事务 ID，标识这是哪个事务更新的数据
  3. DB_ROLL_PTR 字段：回滚指针，用来进行事务回滚的

加上隐藏字段后，上面的例子的实际存储可能就是：

0x06 0x08 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262

括号里只是做说明用的，事实是不存在的。

行溢出问题

数据页的默认大小是 16kb，但是某些字段的值可以远远大于 16kb。

例如变长字段类型 varchar(N)：N 最大可为 65532（65kb），这就远远大于 16kb。

当然了，还有 text 和 blog 字段，这些都是大字段，都可以超过 16kb。

如果一行数据的大小超过了 16kb，就会出现行溢出的现象。

怎么解决？

当一行数据超了 16kb，会在超了大小的那个字段中，可能仅仅包含他的一部分数据，然后同时包含一个20个字节的指针，指向存储了这行数据超了的部分的其他数据页。

MySQL 学习总结 之 COMPACT 行格式的设计原理

下次面试我一定问：MySql数据是如何存储在磁盘上存储的？

InnoDB中数据是如何存储的

uuid作为主键问题

自增的主键的值是顺序的，所以 InnoDB 把每一条记录都存储在一条记录的后面。

当达到页面的最大填充因子时候(InnoDB 默认的最大填充因子是页大小的 15/16，会留出 1/16 的空间留作以后的修改)。

  • 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。
  • 新插入的行一定会在原有的最大数据行下一行，MySQL 定位和寻址很快，不会为计算新行的位置而做出额外的消耗。
  • 减少了页分裂和碎片的产生。

uuid无序，造成的问题：

  • 缓存失效问题，一般查询会将周边数据一起查出并放入缓存buffer中，写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb 在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 IO。
  • 因为写入是乱序的，innodb 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上。
  • 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。

使用自增 id 的缺点

自增 id 也会存在以下几点问题：

  • 别人一旦爬取你的数据库，就可以根据数据库的自增 id 获取到你的业务增长信息，很容易分析出你的经营情况。
  • 对于高并发的负载，InnoDB 在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。
  • Auto_Increment 锁机制会造成自增锁的抢夺，有一定的性能损失。

Auto_increment的锁争抢问题，如果要改善需要调优 innodb_autoinc_lock_mode 的配置。

自增id，被别人分析业务的问题，其实就是安全性中的水平越权问题，可以针对性的防范。

为啥不能用uuid做MySQL的主键？

Mysql8.0 索引跳跃扫描技术

MySQL遵循最左前缀匹配原则，面试官：回去等通知吧

Mysql中RR隔离级别是否解决幻读

RR隔离级别中，next-key锁可以解决当前读的幻读，多版本并发控制（Multiversion Concurrency Control，MVCC）可以解决快照读的幻读，但是一个事务里同时有快照读，当前读时，仍然可能会出现幻读

解决办法：

在事务一开始就立即加锁，这样就会有间隙锁，也能有效的避免幻读的发生。

比如一开始select.....for update

但是需要注意的是，间隙锁是导致死锁的一个重要根源~所以，用起来也需要慎重。

想要解决幻读，可以使用Serializable这种隔离级别，使用RR能解决大部分的幻读问题。

根据快照读的定义，在RR中，如果本事务中发生了数据的修改，那么就会更新快照，后续的查询的结果也会发送变化。

举例：

一个事务A，查询列表id小于30的数据，列表中不存在id为20的数据，然后第二个事务B，新增一条id为20的记录，事务A中再次进行查询id小于30的列表（此时发现数据跟第一次查询一样），然后更新id为20的数据（更新操作为当前读），再次查询id小于30的列表（此时会发现相对于前面查询，多一条id20的记录，因为前面有更新操作，所以更新了快照，此时会发现发生幻读）。

如下：

事务1 事务2
set session tx_isolation=’REPEATABLE-READ’;
BEGIN;
SELECT * FROM users WHERE AGE > 10 AND AGE < 30img
BEGIN;
INSERT INTO users(gmt_create,age,name) values(now(),20,’Hollis999′);img
COMMIT;
SELECT * FROM users WHERE AGE > 10 AND AGE < 30;img
UPDATE users set name =”Hollis888″ where AGE = 20;img
SELECT * FROM users WHERE AGE > 10 AND AGE < 30;img

Innodb的RR到底有没有解决幻读？

Mysql数据库锁，锁的是什么

Record Lock，翻译成记录锁，是加在索引记录上的锁。

Gap Lock，翻译成间隙锁，他指的是在索引记录之间的间隙上的锁，

Next-Key Lock和Gap Lock一样，只有在InnoDB的RR隔离级别中才会生效。

数据库的锁，到底锁的是什么？

阿里为什么禁用数据库做多表join

MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，简单点说就是要通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。

而具体到算法实现上主要有simple nested loop，block nested loop和index nested loop这三种。

而且这三种的效率都没有特别高。

  • 最差的算法就是simple nested loop，他的做法简单粗暴，就是全量扫描连接两张表进行数据的两两对比，所以他的复杂度可以认为是O（n^2)

  • 好一点的算法是index nested loop，当Inner Loop的表用到字段有索引的话，可以用到索引进行查询数据，因为索引是B+树的，复杂度可以近似认为是O（nlogn）

  • block nested loop这种算法，其实是引入了一个Buffer，会提前把外循环的一部分结果提前放到多个JOIN BUFFER中，然后内循环的每一行都和多个buffer中的所有数据作比较，从而减少内循环的次数。他的复杂度是O（M*N），这里的M是buffer的个数。

所以，虽然MySQL已经尽可能的在优化了，但是这几种算法复杂度都还是挺高的，这也是为什么不建议在数据库中多表JOIN的原因。随着表越多，表中的数据量越多，JOIN的效率会呈指数级下降。

如果不能通过数据库做关联查询，那么需要查询多表的数据的时候要怎么做呢？

主要有两种做法：

1、在内存中自己做关联，即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联。

2、数据冗余，那就是把一些重要的数据在表中做冗余，这样就可以避免关联查询了。(主流)

mysql的分库分表依据区别

数据库链接不够使用分库

数据库读写，qps过高，导致数据库连接不足，这时就要搞分库，通过增加数据库实例的方式，来提供更多的可用数据库连接，从而提升系统的并发度

MySQL数据库的默认最大连接数是：100，过期时间wait_timeout默认8小时，修改掉这两个配置

MySQL连接数太多应该怎么解决？

表数据量太大使用分表

单表数据量非常大，存储或查询的性能遇到瓶颈，这个时候就要考虑做（水平）分表，通过将数据拆分到多张表中，减少单表数据量，从而提高查询的速度。

mysql的分区与分表区别

分区，表面上还是一张表，只是存储在不同的位置上，操作数据还是用原表名，数据库会自己组织操作各个分区数据

innodb这种引擎中，分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件（表存储数据文件），但是还是只有一个.frm文件（表结构信息文件）

一般数据量大的时候先搞分区，分区搞不定再搞分表

不要在mysql中使用utf8

mysql中的utf8默认的是utf8mb3，utf8是utf8mb3的简称，官方文档说明在后续会删除，建议使用utf8mb4，这是一种可以支持补充字符的字符集，为了后续的兼容性，建议使用utf8mb4，utf8mb4支持1到4字节。

BMP是Basic Multilingual Plane的缩写，即码位在0到65535之间(或者U+0000和U+FFFF)的字符。

BMP中并不包含补充字符，即码位在U+10000和U+10FFFF之间的的字符。补充字符有哪些呢，如一些生僻的汉字，或者Emoji 表情等都是补充字符。

utf8mb3是一种只支持bmp字符，就是1到3字节，而一些生僻字，emoji表情等的补充字符都不在其支持范围内。

为什么Mysql的默认隔离级别为RR(可重复读)

主要是因为MySQL在主从复制的过程是通过bin log 进行数据同步的，而MySQL早期只有statement这种bin log格式，这种格式下，bin log记录的是SQL语句的原文。

当出现事务乱序的时候，就会导致备库在 SQL 回放之后，结果和主库内容不一致。

为了解决这个问题，MySQL采用了Repetable Read这种隔离级别，因为在 RR 中，会在更新数据的时候增加记录锁的同时增加间隙锁。可以避免这种情况的发生。

在 RC 中，只会对索引增加Record Lock，不会添加Gap Lock和Next-Key Lock。

在 RR 中，为了解决幻读的问题，在支持Record Lock的同时，还支持Gap Lock和Next-Key Lock；

MySQL的binlog主要支持三种格式，分别是statement、row以及mixed，但是，RC 隔离级别只支持row格式的binlog。如果指定了mixed作为 binlog 格式，那么如果使用RC，服务器会自动使用基于row 格式的日志记录。

而 RR 的隔离级别同时支持statement、row以及mixed三种。

MySQL 默认隔离级别是RR，为什么阿里这种大厂会改成RC？

Mysql主从同步延时解决方案

从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

针对不用的业务诉求，区别性对待。

场景一：

如果是对数据的 实时性 要求不是很高，比如：大V有千万粉丝，发布一条微博，粉丝晚几秒钟收到这条信息，并不会有特别大的影响。这时，可以走 从库。

场景二：

如果对数据的 实时性 要求非常高，比如金融类业务。我们可以在客户端代码标记下，让查询强制走主库。

解决方案：

  • 使用缓存中间件，写操作，在主库执行以后，同步数据到缓存，并且把数据同步到从库，应用读取缓存数据，缓存过期以后直接读从库

    上述方案，如果要问就缓存，数据一致性，那么可以回复，如果强要求的，可以在异常的时候，回滚db操作，提示异常

    如果非强一致的，就可以不用处理。

    如果追问redis挂了咋办，那么可以不用管，起码大部分场景是可用的，而且redis是集群的，有从节点。

    这里的缓存时间应该很短，不是经常用，如果经常用，说明及时性要求不高，就没缓存必要，只有每次查询都要实时数据的才会使用该方案。

Mysql跨机房同步方案

alibaba/otter

Canal的工作原理

MySQL主备复制原理

  • MySQL master 将数据变更写入二进制日志( binary log, 其中记录叫做二进制日志事件binary log events，可以通过 show binlog events 进行查看)
  • MySQL slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)
  • MySQL slave 重放 relay log 中事件，将数据变更反映它自己的数据

canal 工作原理

  • canal 模拟 MySQL slave 的交互协议，伪装自己为 MySQL slave ，向 MySQL master 发送dump 协议
  • MySQL master 收到 dump 请求，开始推送 binary log 给 slave (即 canal )
  • canal 解析 binary log 对象(原始为 byte 流)

alibaba/canal

Mysql数据库扩容

检查是否有数据量超过1000万的表，若存在这样的表，就把现有的表数量double。
数据迁移只会在同库之间迁移，不会涉及跨数据库的情况。

使用一致性hash方式进行分库分表，那么迁移以后，只用改动重新分配虚拟节点的配置即可。

Sharding-Jdbc分库方式

  • range范围

    可能会有热点问题。因为订单id是一直在增大的，也就是说最近一段时间都是汇聚在一张表里面的

  • hash取模

    指定的路由key（一般是user_id、order_id、customer_no作为key）对分表总数进行取模，把数据分散到各个表中

    不存在明显热点问题，但是数据量又到瓶颈，扩容比较麻烦

  • 一致性hash

分库分表中间件区别

sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 sharding-jdbc 的依赖；

mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

MySQL的B+树的高度计算

叶子节点存储了键(主键key)和数据(data)。

非叶子节点不存储数据，只存储索引信息(主键key)和下一层节点的指针信息， 不保存实际数据记录。

InnoDB存储引擎最小储存单元是页，一页大小就是16k。B+树叶子节点存的是数据，非叶子节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据。

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数为16k/1k =16. 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是 8+6=14 字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，大概可以存放两千万左右的记录。B+树高度一般为1-3层，如果B+到了4层，查询的时候会多查磁盘的次数，SQL就会变慢。

IO次数取决于B+树的高度h

Mysql中，游标分页与offset，limit分页区别

传统分页的特点

  • 可以直接根据页码跳转到特定页
  • 可能会出现重复、丢失数据的情况
  • 页数较大时性能会降低
  • 排序条件与分页无关

游标分页的特点

  • 不可以直接跳转到特定页，只能加载下一页
  • 不会出现重复、丢失数据的情况
  • 查询效率与页数无关，并且优于传统分页
  • 不适合排序条件比较复杂的分页

Mysql中，查询数据，排序，过滤解析流程

  1. 首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）。

  2. 接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2 。

  3. 如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3 。

  4. 如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。

  5. 应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4。

    注意where与on的区别：先执行on，后执行where；on是建立关联关系在生成临时表时候执行，where是在临时表生成后对数据进行筛选的。

  6. group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

  7. 应用avg或者sum选项，为vt5生成超组，生成vt6.

  8. 应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

  9. 处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.

  10. 应用distinct子句，对vt8进行去重，生成vt9。

  11. 应用order by子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表。

  12. 应用limit选项。生成vt10返回结果给请求者即用户。

关于sql和MySQL的语句执行顺序(必看)

步步深入MySQL：架构->查询执行流程->SQL解析顺序！

SQL 查询语句的执行顺序解析

Mysql中，查看被锁的表及相关sql语句

# 查看正在执行的sql语句
show processlist;

# 查询哪些表锁了
show OPEN TABLES where In_use > 0;

# 查询innodb引擎的运行时信息 （查看造成死锁的sql语句）
show engine innodb status;

# 删除进程（解锁）
kill id;

# 查看正在执行的事务
select * from information_schema.INNODB_TRX;

# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

mysql死锁分析工具show engine innodb status

参考：

面试官你好，我已经掌握了MySQL主从配置和读写分离，你看我还有机会吗？

MySQL 半同步复制模式说明及配置示例 - 运维小结

MySQL主从同步详解与配置

MySQL集群之五大常见的MySQL高可用方案（转）

高性能、高可用、可扩展的MySQL集群如何组建？

MySQL不会丢失数据的秘密，就藏在它的 7种日志里

MySQL必知必会：简介undo log、truncate、以及undo log如何帮你回滚事务

理解 MySQL 一致性非锁定读原理

MySQL · 引擎特性 · 庖丁解InnoDB之REDO LOG

mysql_一条更新语句的执行流程

posted @ 2021-03-04 15:54  hongdada  阅读(65)  评论(0)  编辑  收藏  举报