MySQL

典型回答 在InnoDB存储引擎中，当执行计划中出现Using filesort时，意味着MySQL需要对结果集进行外部排序，以满足查询的ORDER BY条件。 如以下执行计划中的Extra中，出现的Using filesort表明了本次需要进行文件排序。 1 2 3 4 5 +----+-------+----------------------+---------------------+----------------------------------------------------+ | id | type | possible_keys | key | Extra | +----+-------+----------------------+---------------------+----------------------------------------------------+ | 1 | range | idx_subject_product | idx_subject_product | Using index condition; Using where; Using filesort | +----+-------+----------------------+---------------------+----------------------------------------------------+ 在下面这篇文章中，我们介绍过了order by的实现原理。**Using filesort**通常发生在无法直接利用索引完成排序的情况下，而是需要额外的排序步骤，可能会导致查询性能下降，尤其是在处理大量数据时。优化Using filesort的目的是减少排序所需的资源和时间，提高查询效率。 ✅order by 是怎么实现的？ 所以，当执行计划中出现**Using filesort**的是时候，就是一个我们可以优化的方向。（但是，并不是说一定要优化！要看是否有必要以及收益是否够大） 针对<font style="background-color:#FBDE28;">Using filesort</font>的优化，可以有以下几个方向： 1、尽量使用索引排序： 索引是天然有序的，所以当我们在使用order by的时候，如果能借助索引，那么效率一定是最高的。 那么我们就可以确保ORDER BY子句中的字段是索引的一部分。 并且如果可能，使ORDER BY中的列顺序与索引中的列顺序一致（order by a,b,c ， idx_a_b_c(a,b,c）)。 并且考虑使用复合索引。如果ORDER BY子句涉及多个列，创建一个包含这些列的复合索引可能会有助于消除Using filesort。 2、优化MySQL配置： 我们还可以调整sort_buffer_size参数。这个参数决定了排序操作可以使用的内存量。增加其值可以提高处理大型排序操作的能力（但设置过大可能会消耗过多内存资源，影响系统性能） 根据sort_buffer_size的大小不同，会在不同的地方进行排序操作： 如果要排序的数据量小于 sort_buffer_size，那么排序就在内存中完成。 如果排序数据量大于sort_buffer_size，则需要利用磁盘临时文件辅助排序。 在内存中排序肯定会更快一点的。 扩展知识 filesort优化实战 ✅Sort aborted问题排查过程

典型回答 一般来说，MySQL的自增主键，都是可以保证连续自增的，因为底层采用了AUTO-INC锁进行并发控制，但是还是有些情况会导致他出现不自增的情况。 主要有以下这几种情况： 事务回滚：如果一个事务包含了对自增主键表的插入操作，但事务最终失败并回滚，已经分配的自增值不会被重用。这会导致自增主键序列中出现跳跃。 删除操作：这个好理解，从表中删除行会导致自增主键序列出现间隔。因为当行被删除后，其对应的自增主键值不会被重用。 手动指定自增值：如果在插入数据时手动指定了自增列的值，MySQL将使用该值而非自动生成的下一个自增值。如果指定的值大于当前的自增值，MySQL将更新自增计数器为该值，导致后续自动生成的自增值跳跃。 服务器重启：对于某些数据库引擎（尤其是早期版本的MySQL和InnoDB引擎），自增计数器可能不会持久化到磁盘。如果数据库服务器重启，自增计数器可能会重置为当前最大自增值加一，但未提交的事务占用的自增值会丢失，导致不连续。 更改自增值设置：通过修改表的自增起始值或者直接修改自增计数器的值，也可以导致自增主键不连续。 数据导入：在导入数据时，如果导入的数据中包含自增主键列，并且导入数据的主键值与现有数据不连续，也会导致自增主键不连续。 用了**INSERT INTO ON DUPLICATE KEY UPDATE**，MySQL 在尝试插入新记录时，会先分配一个新的自增主键值，无论后续是插入成功还是执行更新操作，这个主键值都已经被分配并且会增加。当插入失败时会导致主键加1，但实际没记录。 ✅SQL语句如何实现insertOrUpdate的功能？

典型回答 在数据库查询过程中，通常认为索引是提高数据查询速度的有效方式。然而，在某些情况下，索引可能不仅不会提高效率，反而会降低查询性能。 比较常见的是，**当表中的数据量非常小的时候，使用索引可能不会带来性能提升。**数据库可能会选择全表扫描而非使用索引，因为在这种情况下，索引的维护成本（如更新、插入或删除数据时维护索引结构）可能超过了它的查询效率优势。 有人疑惑了，为啥优化器这种情况下还会选择索引呢？直接扫全表不就行了么？ 确实，这种时候优化器基本就直接扫全表了。但是万一你用了force index，也可能用索引。那么去除force index，让索引失效，也能提升效率。 类似，如果一个索引，他的过滤性不好，数据库优化器可能也会决定全表扫描比使用索引更高效。这是因为如果查询结果包含了表中大部分行，那么使用索引可能需要更多的I/O操作来遍历索引再获取数据，而全表扫描可以更连续地读取磁盘数据。 还有一个就是如果区分度不高也是一样的，就是数据存在严重的不均匀导致倾斜的时候，在这种情况下，优化器可能认为全表扫描比通过索引过滤这些行更有效。 其实以上几条，就是我们在下面这篇文章中讲过的： ✅为什么MySQL会选错索引，如何解决？ 还有一种情况也可以通过让索引失效来提升效率。比如说一条查询语句，走A索引和B索引都行，但是优化器选错了，选择了A索引，那么如果我能让他失效，那么他就会选择效率更高的B索引。 很多人会奇怪了，优化器为啥会选错？ 这个问题的答案也在上面的那个链接中，你比如说我的这个问题排查过程： ✅如何做SQL调优：用了主键索引反而查询很慢？ 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT * FROM `table_name` WHERE `DELETED` = 0 AND `STATE` = "INIT" AND `ID` >= 474968311 AND event_type = "" ORDER BY id LIMIT 100 因为有order by，优化器会倾向于选择ID索引，但是其实STATE的索引过滤性更高好。这个就是一个典型的因为优化器选错了索引导致效率低。那么如果我们能让ID的这个索引失效，那么也能提升效率。 ...

典型回答 在高并发情况下，MySQL的自增主键也不会重复。当然，前提是单库的情况下，如果你说的是分库分表的情况下，那就另当别论了。如果在同一张表中插入数据，再高的并发也不会导致自增主键出现重复。 这是因为MySQL通过AUTO-INC锁机制确保了自增主键的唯一性。 AUTO-INC锁是InnoDB存储引擎专门用于管理自增长列（AUTO_INCREMENT列）值分配的一种内部锁机制。这种锁的主要目的是确保在向表中插入新记录时，每个新记录的自增列都能获得唯一的、连续的值，即使在高并发的插入操作中也能保持这一点。 AUTO-INC锁的工作原理是当一个事务尝试向一个包含自增列的表中插入一条或多条新记录时，InnoDB会为该表上的自增列申请一个AUTO-INC锁。这个锁确保了在该事务插入新记录期间，没有其他事务可以插入新记录到同一个表中，从而保证了自增值的唯一性和连续性。 在早期的MySQL版本中（5.1之前），AUTO-INC锁是一个表级锁，它在第一个插入操作开始时被获取，并持续到整个事务结束。这意味着在同一时刻只有一个事务能对该表进行插入操作，这虽然保证了自增值的唯一性和连续性，但限制了并发性能。 从MySQL 5.1开始，InnoDB引入了一种新的AUTO-INC锁策略，使得AUTO-INC锁在插入操作完成后立即释放，而不是在事务结束时释放。这种锁被称为“轻量级AUTO-INC锁”，它大大提高了并发插入的性能，因为不同的事务可以更快地连续插入新记录到同一个表中。

典型回答 索引合并（Index Merge）是数据库查询优化的一种技术，它允许数据库管理系统（DBMS）在处理查询时，利用多个索引来改进数据检索的效率。这种技术主要应用在那些涉及多个条件的查询中，通过合并多个索引的结果集，来找出最终满足所有条件的数据行。 索引合并在MySQL 5.1中引入。如以下 SQL 就可能会用到索引合并（分别有两个单独的索引，key1和key2，非联合索引）： 1 2 3 4 5 6 7 8 9 10 11 12 SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2); 在MySQL中，如果进行了索引合并，执行计划中会明确显示type 为index_merge，key 中会列出用到的索引列表，并且在 extra 中会提示具体用了哪种索引合并策略： ...

典型回答 InnoDB的索引通常使用的是B+树的数据结构。B+树的结构特性和磁盘I/O操作的特性就决定了索引的key如果太长会影响性能。 ✅介绍一下InnoDB的数据页，和B+树的关系是什么？ 在上面的文章汇中，介绍过，InnoDB的数据页是InnoDB存储引擎中用于存储数据的基本单位。B+树的每个节点都对应着一个数据页，包括根节点、非叶子节点和叶子节点。一个数据页的大小是固定的，通常为16KB。 数据页是磁盘上的一个连续区域，通常大小为16KB当然，也可以通过配置进行调整。16KB就意味着Innodb的每次读写都是以 16KB 为单位的，一次从磁盘到内存的读取的最小是16KB，一次从内存到磁盘的持久化也是最小16KB。 首先，B+树是一种平衡多路查找树，其性能部分依赖于树的深度。key长度增加会导致每个数据页能存储的键值对数量减少（因为页大小固定，key长度更大，能存的数量就更少），这可能导致B+树的深度增加。 树的深度增加意味着查询、插入或删除操作需要更多的磁盘I/O操作来遍历这些额外的层级，从而降低性能。 其次，磁盘I/O操作是数据库操作中成本最高的部分之一。因为每个B+树节点通常对应于磁盘上的一个页，其大小在InnoDB中默认为16KB。如果索引的key长度很长，每个页面能存放的节点数就会减少，这意味着处理查询时需要读取更多的页面，从而增加了磁盘I/O操作的次数，降低了查询效率。 还有就是，在B+树中进行键值查找时，如果key长度过长，比较操作的成本会增加，尤其是对于字符串这类可变长度的数据类型。这会导致每一次查找操作都消耗更多的CPU资源，进一步影响到查询性能。 所以，索引的 key 长度不建议太长。但是也不要太短，太短可能会导致区分度不够高，比如身份证号，如果你只用前6位当做索引的话，因为重复度很高，那么索引效果就会很差。所以需要在区分度和长度时间做一个平衡。

典型回答 首先，正常情况下，如果没有 a,b 的联合索引的话，这条 SQL 应该会在 a 和 b 这两个索引之间选择其中一个，选择的时候由优化器根据区分度、选择性这样进行选择一个效率更高的。 具体选择索引会看那些信息参考： ✅为什么MySQL会选错索引，如何解决？ 另外，还需要考虑索引失效的情况，如果发生索引失效的，那么比如 a 失效了，那么就只会走 b 的索引，索引失效的可能的情况有很多，比如函数、比如类型转换等等。 ✅索引失效的问题是如何排查的，有那些种情况？ 另外，除了上面说的情况外，还有一种特殊情况是，可能会同时走a,b 两个索引。 因为在 MySQL 5.1中推出了索引合并，这个功能可以通过使用同时使用两个单独索引来提升查询效率： ✅什么是索引合并，原理是什么？

典型回答 MySQL中的数据库表会存在物理存储碎片，这种情况通常发生在频繁执行插入、删除和更新操作的数据库中。这些操作会导致表中的数据页部分空间未被有效利用或数据在物理存储上的排列不连续，从而形成碎片。 碎片的主要来源是因为存在频繁的 DML 操作，如 insert、update以及 delete，除此之外，还有如果我们使用 varchar 或者 text 这种可变长度字段存储的时候，更新时如果长度发生变化，也会存在碎片。 insert 导致的碎片 我们都知道，InnoDB 使用 B+树索引结构存储数据，并且数据通常按主键顺序存储。当我们的主键并不是顺序自增时，比如用UUID，那么新插入的数据行可能会导致页分裂现象。 页分裂会导致数据分散在磁盘上多个不同的位置。新创建的页可能在物理存储上与原始页相距甚远，呢么这些数据在物理上就不是连续的，那么就会存在碎片。 页分裂发生在向 B+树索引中插入新数据时，如果目标页已满，系统需要为新数据腾出空间。在这种情况下，数据库将执行页分裂操作 ✅什么是InnoDB的页分裂和页合并 update 导致的碎片 前面说的 insert 导致碎片的情况，update 也同样会发生，除此之外，如果更新操作导致数据行大小增加，而原位置周围没有足够的空间容纳更新后的行，这行数据可能会被移动到数据文件的其他部分，这样就会留下空闲位置，导致碎片。 delete 导致的碎片 最容易导致碎片的，其实是 delete 操作，尤其是在 InnoDB中，delete执行后，只是给数据做了个标记，但空间并不会立即释放。这导致数据页中可能存在大量未使用的空间，增加了数据的分散程度，这就是碎片。 碎片的危害 当表的碎片增多时，数据在物理磁盘上的存储变得不连续，这将导致数据库在查询数据时需要更多的磁盘I/O操作，从而降低查询效率。 碎片会导致数据库实际占用的存储空间比数据实际需要的空间大，这会大大的浪费磁盘空间，还可能影响缓存效率。 碎片化的数据会增加备份文件的大小，同时也会使得备份和恢复的过程变得更慢，因为备份和恢复操作也会受到物理读写速度的影响。 所以，我们应该尽可能的减少碎片的出现。 扩展知识 如何避免碎片 1、使用连续自增的 ID，而不是用 UUID，这样可以使新创建的对象在B+树的后面插入，可以减少页分裂。 2、对于固定长度的字符串，用 char 代替 varchar 3、避免在高度易变的列上创建索引，因为这会频繁触发页分裂。 4、使用OPTIMIZE TABLE命令可以重新组织表和索引的物理存储。这个命令可以有效减少碎片，优化表的存储和访问速度。 如何清理碎片 ✅什么是MySQL的内存碎片？如何清理？

典型回答 在 MySQL 中，有的时候我们想要知道一个 SQL 的执行耗时有多少，有些数据库管理工具客户端是可以直接返回一个执行时长的，但是，如果没有这种工具怎么办呢？如何查看一个 SQL 的耗时情况呢？ 有两种办法，如果是 MySQL 8.0之前，可以用SHOW PROFILES、如果是 MySQL 8.0之后的版本，可以用EXPLAIN ANALYZE SHOW PROFILES SHOW PROFILES 是 MySQL 提供的一种用于查看查询执行概要信息的命令。它能够显示当前会话中最近执行的 SQL 语句的性能数据，包括每条语句的执行时间和详细的执行阶段耗时。 想要使用这个功能，需要先开启profiling： 1 2 -- 启用 Profiling SET profiling = 1; 启动之后，执行你自己的 SQL 即可： 1 2 -- 执行查询 select * from t1; 之后，通过SHOW PROFILES查询概要信息： 1 2 3 4 5 6 7 8 9 -- 查看 Profiles SHOW PROFILES; +----------+------------+------------------+ | Query_ID | Duration | Query | +----------+------------+------------------+ | 1 | 0.00050800 | select * from t1 | +----------+------------+------------------+ 1 row in set, 1 warning (0.00 sec) 这里面显示了刚刚我们执行的SQL的执行情况，耗时是0.00050800秒。 ...

典型回答 通常，我们的数据库 id 都是用自增 id 来实现的，但是这个获取自增 id 的过程会存在瓶颈吗，如果有的话，会出现在哪里呢？ 首先，获取一个自增主键是有锁竞争的，InnoDB存储引擎中有一个AUTO-INC锁，这种锁的主要目的是确保在向表中插入新记录时，每个新记录的自增列都能获得唯一的、连续的值，即使在高并发的插入操作中也能保持这一点。 ✅高并发情况下自增主键会不会重复，为什么？ 所以，如果并发量太大的去获取同一张表的主键的话，会存在锁竞争而影响性能。 其次，就是当我们要获取主键 id 的时候，是需要链接数据库的，而数据库的连接数是有限的，并且总数量有限，并且给每个连接他的服务器也是有数量限制的。所以，当并发量特别大的时候，可能会因为连接数不够而导致阻塞。 想要优化这个过程，有几个办法： 1、用其他方式生成主键：数据库存在性能瓶颈，那么我们就可以干脆不用数据库来生成id 就行了，比如说我们借助 redis来实现获取自增 id，然后直接保存到数据库中。redis 的性能要比数据库好得多。 2、借助缓存：我们可以参考有一些中间件的主键 id 的获取方式，比如 tddl，他就是基于分段的思想，一次从数据库中取出一段 id，然后缓存在服务器中，当使用的时候从本地缓存中读取即可。用完了再去数据库中申请新的一段即可。具体方案参考： ✅分表后全局ID如何生成？