MySQL速记小册（2）

11

【Q】：Mysql建立索引时需要注意哪些事项？

【A】：

1. 不能盲目建立索引，索引并不是越多越好， 索引会占用空间，而且每次修改的时候都需要维护索引数据，消耗资源。
2. 对于字段的值大量重复的不要建立索引。比如性别。但是，定时任务场景中，少部分失败任务建立索引是可以的。
3. 长字段不应该建立索引。
4. 修改频繁＞查询频繁，应该考虑是否要建立索引。
5. 频繁查询的字段应该建立索引，可以考虑联合索引，减少索引数量。
6. order by ，group by ， distinct后的字段建立索引。

12

【Q】：Mysql中索引一定会有效吗？如何排查索引效果？

【A】：

1. 索引不一定生效。
2. 对于一些小表来说使用全表扫描比使用索引来说可能开销更小。
3. 最终是否使用索引还是要根据Mysql的成本进行计算，评估CPU和I/O成本最终选择是使用辅助索引还是全表扫描。
4. 排查方法使用EXPLAIN命令，在查询前面添加上EXPLAIN，可以查看Mysql最终执行的查询计划。
5. 主要观察

1. 1. type（访问的类型）：这个属性显示了查询的访问方法，列如ALL,index，range等，通常情况下是index或range，如果是全表扫描那么就是ALL
   2. key（使用的索引）：这个属性显示了查询使用的索引，如果没有使用索引就会显示为空。
   3. rows（扫描的行数）：这个属性显示了查询扫描的行数，需要评估下扫描量。

13

【Q】：Mysql中的索引是否越多越好？

【A】：

1. 并不是越多越好，因为索引在时间还是空间上都有一定的成本。
2. 从时间上：每次对表中的数据进行增删改查的时候，索引也必须被更新，这会增加写人操作的开销。索引越多需要修改的地方就越多，时间开销就大了，并且B+树可能会有页的分裂，合并等操作，时间开销就会更大。
3. 从空间上：每建立一个二级索引，都会新建一个B+数，默认每个数据页都是16kb，如果数据量大，索引又多，占用空间会比较大。

14

【Q】：请详细描述MySQL的B+树中查询数据的全过程

【A】：

1. 数据从根节点找起，根据比较数据键值与节点中存储的索引键值（用了二分法），确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点。
2. 叶子节点存储实际的数据行记录，但是一页有16KB大小，存储的数据行不止一条。
3. 叶子节点中数据行以组的形式划分，利用页目录结构，通过二分查找可以定位到对应的组。
4. 定位到组后，利用链表遍历就可以找到对应的数据行。

15

【Q】：为什么MySQL选择使用B+树作为索引结构？

【A】：

1. 高效的查找性能：B+树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+树在删除和插入节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。时间复杂度为logn。
2. 树的高度增长不会过快，使得查询磁盘的IO次数减少：B+树不像红黑树，数据越多树的高度增长的越快。它是多叉树，非叶子结点仅保存主键或索引值和页面指针。使得每一页能容纳更多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的IO次数减少。
3. 范围查询能力强：B+树特别适合访问查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描连表即可遍历后续的数据，非常高效。

16

【Q】：MySQL如何实现事务？

【A】：

1. 主要通过锁，Redo Log，Undo Log，MVCC来实现事务。
2. Mysql利用锁（行锁，间隙锁等等）机制，使用数据并发修改的控制，满足事务的隔离性。
3. Redo Log （重做日志），它会记录事务对数据库的所有修改，当Mysql发送宕机或崩溃时，通过重放redolog就可以恢复数据，来满足事务的持久性。
4. Undo Log（回滚日志），它会记录事务的方向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性。
5. MVCC（多版本并发控制），满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。
6. 其实事务主要是为了实现一致性，具体是通过AID，即原子性、隔离性和持久性来达到一致性目的。

17

【Q】：MySQL中的长事务可能导致什么问题？

【A】：

1. 长时间的锁竞争，阻塞资源：长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时候发生阻塞，从而增加系统的等待时间和降低并发性能。业务线程也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全面崩盘，导致非常严重的上线事故。
2. 死锁风险：长事务更容易产生死锁，因为多个事务可能在相互等待对方释放锁，导致系统无法继续执行。
3. 主从延迟：主库需要长时间执行，然后传输给从库，从库又要重放好久，期间可能有很长一段时间数据时不同步的。
4. 回滚导致时间浪费：如果长事务执行很长一段时间，中间突发状况导致报错，使得事务回滚了，之前做的执行都浪费了。

18

【Q】：Mysql中的MVCC是什么？

【A】：

1. MVCC是一种并发控制机制，运行多个事务同时读取和写入数据库，而无需相互等待，从而提高数据库的并发性能。
2. 在MVCC中，数据库为每个事务创建一个数据快照。每当数据被修改时，Mysql不会立即覆盖原有的数据，而是生成新版本的记录。每个记录都保留了对应的版本号和时间撮。
3. 多版本之间串联起来就形成了一条版本链，这样不同时刻启动的事务可以无锁地获得不同版本的数据（普通读）。此时读（普通读）操作不会阻塞。
4. 写操作可以继续写，无非就是会创建新的数据版本（但只有在事务提交后，新版本才会对其他事务可见。未提交的事务修改不会影响其他事务的读取），历史版本记录可供已经启动的事务读取。

19

【Q】：MySQL二级索引有MVCC快照吗？

【A】：

1. 二级索引本身没有MVCC快照。
2. 因为二级索引条目只存索引列值+主键值，不包含InnoDB隐藏列（TRX_ID、ROLL_PTR）。因此，二级索引自身没有版本链，无法提供判断事务快照的可见性。
3. 当二级索引项被修改或删除被标记是，InnoDB必须回表到聚簇索引，提供TRX_ID+Undo Log 判断当前事务能看到哪个版本。
4. 此时覆盖索引也会失效，因为InnoDB仍要回表到聚簇索引确认其可见性。

20

【Q】：如果MySQL没有MVCC，会有什么影响？

【A】：

1. 如果没有MVCC，系统必须频繁地对读写操作进行加锁来保证数据的正确性，因为增加了锁的获取和释放的开销，会导致整体系统相应速度变慢，这种实现叫LBCC