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 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高级 之 索引失效与优化详解

索引性能优化

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

在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中数据是如何存储的

参考：

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

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

MySQL主从同步详解与配置

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

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

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

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

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

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

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