MySQL

典型回答 MySQL的索引失效是一个比较常见的问题，这种情况一般会在慢SQL发生时需要考虑，考虑是否存在索引失效的问题。 在排查索引失效的时候，第一步一定是找到要分析的SQL语句，然后通过explain查看他的执行计划。主要关注type、key和extra这几个字段。 ✅SQL执行计划分析的时候，要关注哪些信息？ 我们需要通过key+type+extra来判断一条SQL语句是否用到了索引。如果有用到索引，那么是走了覆盖索引呢？还是索引下推呢？还是扫描了整颗索引树呢？或者是用到了索引跳跃扫描等等。 一般来说，比较理想的走索引的话，应该是以下几种情况： 首先，key一定要有值，不能是NULL 其次，type应该是ref、eq_ref、range、const等这几个 还有，extra的话，如果是NULL，或者using index，using index condition都是可以的 如果通过执行计划之后，发现一条SQL没有走索引，比如 type = ALL， key = NULL , extra = Using where 那么就要进一步分析没有走索引的原因了。我们需要知道的是，到底要不要走索引，走哪个索引，是MySQL的优化器决定的，他会根据预估的成本来做一个决定。 那么，有以下这么几种情况可能会导致没走索引： 1、没有正确创建索引：当查询语句中的where条件中的字段，没有创建索引，或者不符合最左前缀匹配的话，就是没有正确的创建索引。 2、索引区分度不高：如果索引的区分度不够高，那么可能会不走索引，因为这种情况下走索引的效率并不高。 3、表太小：当表中的数据很小，优化器认为扫全表的成本也不高的时候，也可能不走索引 4、查询语句中，索引字段因为用到了函数、类型不一致等导致了索引失效 这时候我们就需要从头开始逐一分析： 1、如果没有正确创建索引，那么就根据SQL语句，创建合适的索引。如果没有遵守最左前缀那么就调整一下索引或者修改SQL语句 2、索引区分度不高的话，那么就考虑换一个索引字段。 3、表太小这种情况确实也没啥优化的必要了，用不用索引可能影响不大的 4、排查具体的失效原因，然后针对性的调整SQL语句就行了。 扩展知识 可能导致索引失效的情况 假设我们有一张表（以下SQL实验基于Mysql 5.7）： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `age` int(11) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `age` (`age`), KEY `create_time` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into mytable(id,name,age,create_time) values (1,"hollis",20,now()); insert into mytable(id,name,age,create_time) values (2,"hollis1",21,now()); insert into mytable(id,name,age,create_time) values (3,"hollis2",22,now()); insert into mytable(id,name,age,create_time) values (4,"hollis3",20,now()); insert into mytable(id,name,age,create_time) values (5,"hollis4",14,now()); insert into mytable(id,name,age,create_time) values (6,"hollis5",43,now()); insert into mytable(id,name,age,create_time) values (7,"hollis6",32,now()); insert into mytable(id,name,age,create_time) values (8,"hollis7",12,now()); insert into mytable(id,name,age,create_time) values (9,"hollis8",1,now()); insert into mytable(id,name,age,create_time) values (10,"hollis9",43,now()); 索引列参与计算 1 2 3 4 5 6 7 select * from mytable where age = 12; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ 以上SQL是可以走索引的，但是如果我们在字段中增加计算的话，就会索引失效： ...

典型回答 不一定的 假如我的表中有一个性别字段，他的区分度肯定是不高的，只有男和女两种。一般情况下，如果表中数据量很大的话，用这个字段查询会导致没办法过滤掉很多数据，就可能没办法发挥索引的效果。 但是，如果有一种特殊情况，如男女比例是95:5，那么，这时候，如果我用"女’作为性别的查询条件的话，还是可以走索引，并且有很大的性能提升的，原因就是因为他可以过滤掉大部分数据。走索引可以大大提升效率。 这种一般在任务表中比较多，比如任务表中有状态，两种情况：INIT和SUCCESS，大多数情况下，任务都是SUCCESS的，只有一少部分是INIT，这时候就可以给这个字段加索引。这样当我们扫描任务表执行任务的时候，还是可以大大提升查询效率的。

典型回答 SQL调优是面试中经常爱问的问题，这个问题可以考察一个候选人对于SQL的整体性能优化的理解和掌握程度，一般来说，SQL调优需要从以下几个方面和步骤入手。 首先，需要先发现问题，尤其是在面试中，最好是结合业务说明，比如是某一次线下报警出现了慢SQL，或者是接口RT比较长，做了性能分析发现瓶颈是在SQL查询上面都可以。但是不管怎么样，一定要有背景。 有了问题之后，那就是问题的分析了。 首先需要定位到具体的SQL语句，这个可以通过各类监控平台或者工具来实现，通过定位到SQL语句之后，我们就知道具体是哪张表、哪个SQL慢了。 那接下来就是进行分析了，一般一个SQL慢，可能有以下几种原因： 1、索引失效 2、多表join 3、查询字段太多 4、表中数据量太大 5、索引区分度不高 6、数据库连接数不够 7、数据库的表结构不合理 8、数据库IO或者CPU比较高 9、数据库参数不合理 10、事务比较长 11、锁竞争导致的等待 12、深分页问题 所以，一次完整的SQL调优，一般需要考虑以上几个因素，一般会涉及到其中的一个或者多个问题。那么就逐个优化。 首先，索引失效的问题一般是先通过执行计划分析是否走了索引，以及所走的索引是否符合预期，如果因为索引设计的不合理、或者索引失效导致的，那么就可以修改索引，或者修改SQL语句。或者强制执行使用某个索引。具体可以参考： ✅索引失效的问题如何排查？ 其次，多表join也是SQL执行的比较慢的一个常见原因，关于这个问题，我们在以下文章中有详细的阐述背景和解决方案： ✅为什么大厂不建议使用多表join？ 接下来，如果是索引区分度不高的话，这个其实也和索引不合理有关，但是其实到底快不快，用不用索引，并不是因为区分度高不高导致，其实还是索引扫描的行数的成本导致。所以，有的时候不能认为区分度不高就一定会效率低，或者一定就不适合创建索引。 区分度不高的字段建索引一定没用吗？ 查询字段太多，这个有的时候是因为我们错误的用到了select * 导致的，一般来说，查询字段小于100个，都不是特别大的问题，除非真的是字段数特别多，这时候可以采用两种办法解决。第一个就是不要查询那些你不关心的字段，只查询少部分字段。第二个就是做分表，垂直分表，把数据拆分到多张表中。但是这么做可能也会带来需要多表join的问题，所以拆分的时候也需要考虑冗余。 表中数据量太大，一般来说，单表超过1000万，会导致查询效率变低，即使使用索引可能也会比较慢，所以如果表中数据量太大的话，这时候可能通过建索引并不一定能完全解决了。那么具体的解决方案有几种： 1、数据归档，把历史数据移出去，比如只保留最近半年的数据，半年前的数据做归档。 2、分库分表、分区。把数据拆分开，分散到多个地方去，这里不详细介绍了，我们的文档中有分库分表和分区的详细介绍，不展开了。 3、使用第三方的数据库，比如把数据同步到支持大数量查询的分布式数据库中，如oceanbase、tidb，或者搜索引擎中，如ES等。 数据库连接数不够，这个也需要具体分析，到底是什么原因，可能的原因有几个，第一个就是业务量太大了，单库确实扛不住了，那就选择分库吧。 第二个可能就是存在一些慢SQL、或者长事务导致的，慢SQL占用数据库链接，数据库连接数不够，其他的查询就会阻塞，就更慢。 ✅数据库连接池满排查过程 数据库的表结构不合理，这个也是一个关键原因，有的时候比如某个字段中存了很长的内容，或者没有做合理的冗余需要多表关联查询等等。解决思路就是重构，或者分表。 数据库IO或者CPU比较高，这种问题也常见的，当数据库整体IO或者CPU飙高的时候，查询速度就有可能下降，所以需要分析背后的原因及解决思路，可以参考： ✅数据库CPU被打满排查过程 存在长事务，这个和慢SQL同理，都是占用了数据库链接，导致其他请求要等待。 **锁竞争导致的等待，**当有大并发争抢共享资源的时候，就会导致锁等待，这个过程就会拉长耗时，导致SQL变慢。这个也可以参考上面的CPU被打满的问题。 **数据库参数不合理，**这个也是经常会遇到的，针对我们具体的业务场景，做一些适当的参数调整，有时候也能大大的提升SQL的效率。比如调整内存大小、缓存大小、线程池大小等。 深度分页问题是指在数据库查询中，当你尝试访问通过分页查询返回的结果集的后面部分（即深层页码）时遇到的性能问题。可以考虑使用子查询以及记录上一页ID的方案解决。 ✅MySQL的深度分页如何优化 扩展知识 参数优化 假设我们有一个名为 mydb 的数据库，其中包含一个名为 mytable 的 InnoDB 表。该表有一个自增主键 id，一个整数类型字段 age 和一个字符串类型字段 name，我们希望对该表进行优化。 首先，我们可以使用 SHOW VARIABLES LIKE ‘innodb%’; 命令查看当前的 InnoDB 参数设置。这些参数包括缓冲池大小、刷新间隔、日志大小等等。 接下来，我们可以尝试调整以下几个参数来优化数据库性能： innodb_buffer_pool_size: 缓冲池大小是 InnoDB 存储引擎的核心参数之一，它控制着 InnoDB 存储引擎使用的内存大小。通常，我们可以将该参数设置为系统可用内存的 70%-80%。例如，如果系统有 8GB 内存可用，我们可以将 innodb_buffer_pool_size 设置为 6GB。在 MySQL 中，可以使用以下命令进行设置： ...

典型回答 慢查询是指数据库中查询时间超过指定阈值的SQL，这个阈值根据不同的业务来说一般是不一样的，在我们内部，这个阈值是1s，也就是说，如果一条SQL执行超过1秒钟，就认为是一个慢SQL。 慢SQL的问题排查一般分为几个步骤。 发现问题 一般来说，慢SQL的问题，是比较容易能够发现的。首先如果有很成熟的监控体系的话，会把慢SQL进行统计，然后以报警的形式推送出来。 如果用了一些数据库的中间件，他们也会有慢SQL的日志，如TDDL： 1 Cause: ERR-CODE: [TDDL-4202][ERR_SQL_QUERY_TIMEOUT] Slow query leads to a timeout exception, please contact DBA to check slow sql. SocketTimout:12000 ms, 如果中间件也没用，那么数据库自己也是可以配置慢SQL日志的，配置方式如下： 1、找到MySQL的配置文件 my.cnf（或 my.ini，取决于操作系统），通常位于MySQL安装目录下的 etc 或 conf 文件夹。 2、在配置文件中启用慢查询日志：找到或添加以下配置项，并取消注释（如果有注释），确保以下行存在或添加到配置文件中： 1 2 3 slow_query_log = 1 slow_query_log_file = /path/to/slow-query.log long_query_time = 1 3、重启MySQL服务：保存配置文件并重新启动MySQL服务，使配置生效。 配置生效后，如果有SQL的执行时长超过long_query_time 配置的时间阈值，那么就会打印慢SQL日志 4、查看慢查询日志：使用文本编辑器打开慢查询日志文件，路径为在配置文件中指定的路径。例如，在Linux上可以使用以下命令： 1 vim /path/to/slow-query.log 如果有慢SQL，内容如下： 1 2 3 4 5 # Time: 2023-06-04T12:00:00.123456Z # User@Host: hollis[192.168.0.1]:3306 # Query_time: 2.345678 Lock_time: 0.012345 Rows_sent: 10 Rows_examined: 100 SET timestamp=1650000000; SELECT * FROM orders WHERE status = 'pending' ORDER BY gmt_created DESC; 定位问题 在如上的各种监控、报警以及日志中，我们就可以找到对应的慢SQL的具体SQL了，然后就可以进一步分析为什么这个SQL是慢SQL了，主要就是排查一下他慢在哪里。 ...

典型回答 驱动表是表连接中的基础表，也就是通过驱动表的数据结果集作为循环基础数据，然后一条一条的通过这个结果集的数据作为过滤条件到被驱动表中查询数据，然后再做合并。那么，也就意味着：驱动表在SQL语句执行的过程中先读取。而被驱动表在SQL语句执行的过程中后读取。 当我们知道MySQL的join的原理之后，其实就可以很容易的知道，驱动表的选择会决定着一条SQL的执行效率。所以，一条SQL中，该使用哪张表作为驱动表，其实是优化器决定的。 MySQL的优化器选择驱动表的原则是：更好的访问性能和筛选性能，所以，通常情况下， 会做以下几个方面的考量： 表大小：这个很容易理解，小表作为驱动表可以更快地被扫描和匹配。所以优化器倾向于选择较小的表作为驱动表。 当然，如果两张表都没有索引，那么都需要全表扫描，那么在nested loop join下就是笛卡尔积， 那么小表驱动大表的提升就微乎其微。 但是如果我们考虑到有索引的情况、hash join的情况等的话，小表驱动大表还是优选的，所以总体来说，数据库还是会倾向于小表驱动大表。 索引：在MySQL中，索引能大大的影响SQL的查询效率，所以选择可以利用索引进行加速访问的表作为驱动表可以提升效率。 where条件：如果查询中包含过滤条件，优化器会选择能够使用过滤条件进行筛选的表作为驱动表，以减少后续的匹配操作。 连接类型：根据连接类型，INNER JOIN、LEFT JOIN、RIGHT JOIN等，优化器可能会做一些选择。比如left join会选择左表作为驱动表，主要是因为LEFT JOIN要返回左表中的所有记录，而右表中的匹配记录是可选的。通过以左表作为驱动表，可以确保返回左表中的所有记录。 left join： 左表是驱动表，右表是被驱动表 right join： 右表是驱动表，左表是被驱动表 inner join： 表数据量较小的表会由mysql自动选择作为驱动表 扩展知识 如何判断哪张表是驱动表 可以使用explain查看一下SQL的执行计划。在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。 1 2 3 4 5 6 +----+-------------+---------------+--------+------------------+---------+---------+--------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+------------------+---------+---------+--------------------------------+------+-------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 100 | Using where | | 1 | SIMPLE | order_details | ref | order_id | order_id| 4 | db_name.orders.order_id | 2 | NULL | +----+-------------+---------------+--------+------------------+---------+---------+--------------------------------+------+-------------+ 如上图，orders表为驱动表，order_details表为非驱动表。 ...

典型回答 所谓大事务（长事务），一般是指事务中要执行的SQL很多，事务的时间比较长。 这样的事务，会带来很多问题。 1、占用数据库连接：这个很容易理解，SQL多了，执行的就会很慢，那么大的事务就会很长时间占用数据库链接，但是因为数据库连接是有限的，被长事务占用后，就会导致其他事务可能无法获取连接，导致应用的吞吐量下降， 影响系统可用性。 2、难以回滚：由于大事务涉及的数据量较大，执行回滚操作可能会变得非常耗时。如果事务需要回滚或失败，可能需要花费很长时间才能完全回滚所有修改，这会对数据库的可用性和性能造成负面影响。 3、导致主从延迟：大事务往往伴随着很复杂的操作，那么就可能会有很多数据的更新操作，在主库执行的时间很长的话，在同步过程中，就可能在备库上也要执行很长时间的数据同步，那么这段同步的时间就会出现主从延迟。 4、锁竞争：大事务的话，写操作多了就可能要锁定许多数据。这可能导致其他并发事务在访问相同资源时遇到锁竞争，从而导致性能下降和延迟增加。长时间的锁定还可能导致其他事务的等待和阻塞。 5、日志空间占用：大事务会生成大量的日志，尤其是binlog，当单个事务最大允许使用的Binlog文件的大小超过了max_binlog_cache_size时，会导致报错：Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again 6、对MVCC影响：大事务会导致MVCC中旧版本的数据持续存在，影响数据库的Purging，从而影响整体性能。 ✅undolog会一直存在吗？什么时候删除？ 7、影响索引覆盖：当一个长事务修改一个表时，可能会导致其他事务对该表的查询不使用覆盖索引技术。 ✅二级索引在索引覆盖时如何使用MVCC？ 解决方案： 拆分，把一个大事务，拆成多个事务。把不需要在事务中的操作，如读操作，内存计算操作、IO操作，远程调用等，放到事务外处理。

典型回答 K是Key的意思，就是代表约束的，所以PK、UK这些都是代表不同类型的约束： PK：Primary Key ，主键约束 UK：Unique Key， 唯一约束 CK： check()， 检查约束 FK：Foreign Key， 外键约束 DF：default ，默认约束

典型回答 我们知道，在MySQL中，自增主键有两种，一种是显式的、一种是隐式的。如果我们在一张表中没有定义主键，那么，MySQL会创建一个隐藏的主键（row_id）作为主键。 那么，不管是我们自己定义的自增主键，还是row_id的这个主键，都是一个固定类型的，一般都是bigint unsigned，那么既然有固定类型，就有取值范围。那么随着数据量的增长，主键的值会不断增长，那么万一超过了这个范围限制，会怎么样呢？ 如果是我们自己显式定义的一个自增ID，如果已经达到了上限，那么下一次申请ID的时候，得到的值就是那个最大值，后续也不会再增加。这时候我们会拿到一个已经用过的主键，如果继续插入的话，会报主键冲突。 那如果我们没有自定义自增ID，那么就会默认使用row_id，如果已经达到了上限，那么下一次申请ID的时候，得到的值会从0开始，然后继续重新自增。但是，这种情况如果我们因为没有设置主键，所以他不会报主键冲突，他会直接把这个row_id = 0的数据插入到数据库中，并且会把之前的row_id=0的数据给直接覆盖了。 所以，结论是： 显示自定义的自增ID，用完以后下次插入会报主键冲突。 未定义自增ID主键，会用row_id，用完以后下一次插入会覆盖历史数据。 那么，从这个方面来看的话，我们为了避免数据被覆盖，还是需要自己设置一个自增的主键ID的，毕竟异常我们是可以感知到的，但是数据覆盖我们可能过了很久才能发现。 扩展知识 真用完了咋办 MySQL中的自增主键用完是一个相对罕见的情况，但确实可能发生，尤其是在大数据量的应用中。 一旦用完了，可以有以下几个解决方式： 重用未使用的主键值（不推荐）： 如果你的表中有删除操作，可能会有未使用的主键值。你可以通过编写脚本或程序来找到这些空缺，并在插入新行时显式地指定这些主键值。但这种方法可能会破坏数据的完整性和连续性。 归档旧数据（推荐）： 如果表中的一些数据是历史数据，不再经常访问，可以将其归档到另一个表中，然后从原表中删除这些数据。这可以为新数据释放主键空间。 使用UUID作为主键（不推荐）： 考虑使用 UUID（通用唯一标识符）作为主键。UUID 是128位长，几乎不可能用完。但这会增加存储需求，并可能影响性能。 ✅uuid和自增id做主键哪个好，为什么？

典型回答 ✅SQL执行计划分析的时候，要关注哪些信息？ 执行计划中，key有值，并且type=index，这时候很多人认为是走了索引的。 当我们执行执行计划查看一个SQL的执行过程的时候，通常会见到以下这样的执行计划： 1 2 3 4 5 +----+-------+---------------+----------+--------------------------+ | id | type | possible_keys | key | Extra | +----+-------+---------------+----------+--------------------------+ | 1 | index | NULL | idx_abcd | Using where; Using index | +----+-------+---------------+----------+--------------------------+ 这个执行计划中，type=index,key=idx_abcd很多人会认为这表示这条SQL走了索引，但是其实这么理解是不对的。 如果是走了索引Extra中的内容应该是Using index 而不是Using where; Using index 以上的这个执行计划表明，这个SQL确实用到了idx_abcd的这个索引树，但是他并没有直接通过索引进行匹配或者范围查询，而是扫描了整颗索引树。 所以，type=index 意味着进行了全索引扫描， 会遍历索引树来查找匹配的行，这个效率比扫表扫描快一些，但是很有限，和我们通常意义上理解的走了索引 是两回事儿。 遇到这种情况，大概率是因为没有遵守最左前缀匹配导致的索引失效了。所以需要调整查询语句，或者修改索引来解决。

典型回答 ✅乐观锁与悲观锁如何实现？ 使用乐观锁在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。 这是我们前面的文章中提到的一句话，但是，如果你认为乐观锁的这个过程中完全没有任何锁的参与的话那就大错特错了。 因为虽然在使用乐观锁的时候，我们没有显式的加锁，也没有用到对他的相关锁机制。但是乐观锁是使用update语句过程中实现的，update的过程是有锁的。数据库在更新时，会根据where条件对索引添加行级锁（可能还有gap 或者 next key） 所以，乐观锁的过程中，并不是完全无锁的。 那么，乐观锁既然也有锁，那么他相比悲观锁意义在哪里呢？ 乐观锁最大的好处就是通过CAS的方式做并发校验，这个过程不需要提前加锁，只需要在更新的那一刻加一个短暂的锁而已，而悲观锁的话，需要你先select for update，锁的时长要长得多。