1. 如何定位慢查询

我们当时在做压力测试时发现有些接口响应时间非常慢，超过了2秒。因为我们的系统部署了运维监控系统Skywalking，在它的报表展示中可以看到哪个接口慢，并且能分析出接口中哪部分耗时较多，包括具体的SQL执行时间，这样就能定位到出现问题的SQL。

如果没有这种监控系统，MySQL本身也提供了慢查询日志功能。可以在MySQL的系统配置文件中开启慢查询日志，并设置SQL执行时间超过多少就记录到日志文件，比如我们之前项目设置的是2秒，超过这个时间的SQL就会记录在日志文件中，我们就可以在那里找到执行慢的SQL。不过不建议在生产环境使用，因为开启慢日志功能会损耗MySQL的性能

2. 那这个SQL语句执行很慢，如何分析呢？

聚合查询——新增一个临时表去解决

多表查询——优化sql语句结构

表数据量过大——添加索引

深度分页查询——覆盖索引+子查询

如果一条SQL执行很慢，我们通常会使用MySQL的EXPLAIN命令来分析这条SQL的执行情况。通过key和key_len可以检查是否命中了索引，如果已经添加了索引，也可以判断索引是否有效。通过type字段可以查看SQL是否有优化空间，比如是否存在全索引扫描或全表扫描。通过extra建议可以判断是否出现回表情况，如果出现，可以尝试添加索引或修改返回字段来优化。

3. 了解过索引吗？（什么是索引）

索引在项目中非常常见，它是一种帮助MySQL高效获取数据的数据结构，主要用来提高数据检索效率，降低数据库的I/O成本。同时，索引列可以对数据进行排序，降低数据排序的成本，也能减少CPU的消耗。

4. 索引的底层数据结构了解过吗**？**

MySQL的默认存储引擎InnoDB使用的是B+树作为索引的存储结构。选择B+树的原因包括：

• 节点可以有更多子节点，路径更短；
• 磁盘读写代价更低，非叶子节点只存储键值和指针，叶子节点存储数据；
• B+树适合范围查询和扫描，因为叶子节点形成了一个双向链表。

5. B树和B+树的区别是什么呢？

B树和B+树的主要区别在于：

1. B树的非叶子节点和叶子节点都存放数据，而B+树的所有数据只出现在叶子节点，这使得B+树在查询时效率更稳定。
2. B+树在进行范围查询时效率更高，因为所有数据都在叶子节点，并且叶子节点之间形成了双向链表。

6. 什么是聚簇索引什么是非聚簇索引？

聚簇索引是指数据与索引放在一起，B+树的叶子节点保存了整行数据，通常只有一个聚簇索引，一般是由主键构成。

非聚簇索引则是数据与索引分开存储，B+树的叶子节点保存的是主键值，可以有多个非聚簇索引，通常我们自定义的索引都是非聚簇索引。

7. 知道什么是回表查询吗？

回表查询是指通过二级索引找到对应的主键值，然后再通过主键值查询聚簇索引中对应的整行数据的过程。

8. 知道什么叫覆盖索引吗？

覆盖索引是指在SELECT查询中，返回的列全部能在索引中找到，避免了回表查询，提高了性能。使用覆盖索引可以减少对主键索引的查询次数，提高查询效率。

9. MySQL超大分页怎么处理？

超大分页通常发生在数据量大的情况下，使用LIMIT分页查询且需要排序时效率较低。可以通过覆盖索引和子查询来解决。首先查询数据的ID字段进行分页，然后根据ID列表用子查询来过滤只查询这些ID的数据，因为查询ID时使用的是覆盖索引，所以效率可以提升。

10. 索引创建原则有哪些？

这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。 还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。 如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

11. 什么情况下索引会失效？

这个情况比较多，我说一些自己的经验，以前遇到过的 比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。 我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效所以，

通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

12. SQL的优化经验有哪些？

SQL优化可以从以下几个方面考虑：

• 建表时选择合适的字段类型。
• 使用索引，遵循创建索引的原则，避免索引失效。
• 编写高效的SQL语句，比如避免使用SELECT *，尽量使用UNION ALL代替UNION，以及在表关联时使用INNER JOIN，内连接会对两个表优化，优先把小表放在外边。
• 读多写少时，采用主从复制和读写分离提高性能。
• 在数据量大时考虑分库分表。500万

13. 创建表的时候，你们是如何优化的呢？

创建表时，我们主要参考阿里《嵩山版》开发手册，选择字段类型时结合字段内容，比如数值类型选择TINYINT、INT、BIGINT等，字符串类型选择CHAR、VARCHAR或TEXT。

14. 在使用索引的时候，是如何优化呢？

在使用索引时，我们遵循索引创建原则，确保索引字段是查询频繁的，使用复合索引覆盖SQL返回值，避免在索引字段上进行运算或类型转换，以及控制索引数量。

15. 你平时对SQL语句做了哪些优化呢？

我对SQL语句的优化包括指明字段名称而不是使用SELECT *，避免造成索引失效的写法，聚合查询时使用UNION ALL代替UNION，表关联时优先使用INNER JOIN，以及在必须使用LEFT JOIN或RIGHT JOIN时，确保小表作为驱动表。

16. 事务的特性是什么？可以详细说一下吗？

事务的特性是ACID，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。例如，A向B转账500元，这个操作要么都成功，要么都失败，体现了原子性。转账过程中数据要保持一致，A扣除了500元，B必须增加500元。隔离性体现在A向B转账时，不受其他事务干扰。持久性体现在事务提交后，数据要被持久化存储。

17. 并发事务带来哪些问题？

并发事务可能导致脏读、不可重复读和幻读。

• 脏读是指一个事务读到了另一个事务未提交的“脏数据”。
• 不可重复读是指在一个事务内多次读取同一数据，由于其他事务的修改导致数据不一致。
• 幻读是一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

18. 怎么解决这些问题呢？MySQL的默认隔离级别是？

解决这些问题的方法是使用事务隔离。MySQL支持四种隔离级别：

1. 未提交读（READ UNCOMMITTED）：解决不了所有问题。
2. 读已提交（READ COMMITTED）：能解决脏读，但不能解决不可重复读和幻读。
3. 可重复读（REPEATABLE READ）：能解决脏读和不可重复读，但不能解决幻读，这也是MySQL的默认隔离级别。
4. 串行化（SERIALIZABLE）：可以解决所有问题，但性能较低。

InnoDB如何解决幻读

MySQL支持四种事务隔离级别：

• READ UNCOMMITTED（读未提交）：可能发生脏读、不可重复读、幻读。
• READ COMMITTED（读已提交）：解决脏读，但仍可能发生不可重复读和幻读。
• REPEATABLE READ（可重复读，默认级别）：解决脏读和不可重复读，InnoDB通过间隙锁（Gap Lock）和Next-Key Lock进一步解决幻读。
• SERIALIZABLE（串行化）：最高隔离级别，完全避免并发问题，但性能最低。

第一个MYSQL的事务隔离级别，MYSQL有四种事务隔离级别，这四种事物隔离级别分别代表，当存在多个事物并发冲突的时候，可能会出现的脏读不可重复读，换读的一些问题，而一诺dB在R2的隔离级别模式下，解决了换读这样一个问题，第二个什么是幻读呢，换读是指在同一个事物中，前后两次查询相同范围的时候，得到的结果不一致，我们来看这个图，第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条，而在第二个事务里面，它插入了一行数据，并且进行了提交，接着第一个事物再去查询的时候，得到的结果比第一次查询的结果，多出来了一条数据，注意第一个事物的第一次和第二次查询，都在同一个事物里面，所以啊换读会带来数据一致性的问题，第3ino dB是如何去解决幻读问题的呢，换毒问题，为了更清晰地说明这两种锁，以及它是怎么样去解决幻读的，我来举个例子，假设现在存在这样一个B加速的索引结构，这个结构里面有四个索引元素，分别是一七和十，当我们通过组件索引查询一条记录，并且对这条记录通过for update加锁的时候，像这样，这个时候呢会产生一个叫记录锁，也就是行锁，锁定id等于一这个索引，像这样一个图被锁定的记录，在所释放之前，其他事物是无法对这一条记录做任何操作的，前面我们说过对换图的定义啊，换读是指在同一个事物中，前后两次查询相同范围的时候，得到结果不一致，注意这里强调的是范围查询，也就是说啊，一诺dB引擎要解决幻读的问题，必须要保证一个点，就是如果一个事物通过这样一条查询语句，进行锁定的时候，另外一个事物再执行这样一条insert语句，需要被阻塞，直到前面获得所的事物被释放，所以在INDODB中设计了一种间隙锁，它的主要功能是锁定一段范围内的索引记录，像这样一个图，当对查询范围id大于四，and id小于七这个范围加锁的时候，会针对B加速中的四和七这个开区间的范围，加间隙锁意味着在这种情况下，其他事物对这个区间的数据进行插入更新，删除都会被锁住，但是还有另外一种情况，比如像这样，这条查询语句是针对id大于四这个条件加锁，那么它需要锁定多个索引区间，所以这个情况下，inno dB引入一个叫next key lock机制，next key lock呢相当于间隙锁和记录所的合集，记录所锁定存在记录的行间隙锁，锁住的是记录行之间的间隙，而next key呢锁住的是两者的和像这样一个图，每个数据行上的非唯一索引列，都会存在一把next lock，当某个事物持有这一行数据的next lock的时候，会锁住一段在左开右闭区间的数据，因此当通过id大于四，这样一个范围查询加锁的时候，一诺dB呢会去加一个next key lock锁，锁定的区间范围是四七和十十到正无穷大，记住是左开右闭区间，间隙锁和next he look的区别是在于加锁的范围，间隙锁锁定的是两个索引之间的间隙，而next klock呢会锁定多个索引区间，它包含记录锁和间隙锁，当我们使用范围查询不仅仅命中record记录，还包含了gap间隙的时候，在这种情况下使用的就是零件锁，也就是next key lock，它是MEXICO里面默认的行锁算法，第四简单总结一下，解决了换毒的问题，但是枷锁之后一定会影响到并发的性能，因此啊对于性能较高的一些业务场景，我们可以把隔离级别设置成RC，那么这个级别中就不存在借机所，也就不存在这样的一个性能的影响

19. undo log和redo log的区别是什么？

redo log记录的是数据页的物理变化，用于服务宕机后的恢复，保证事务的持久性。而undo log记录的是逻辑日志，用于事务回滚时恢复原始数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作，保证事务的原子性和一致性。

20. 事务中的隔离性是如何保证的呢？（你解释一下MVCC）

事务的隔离性通过锁和多版本并发控制（MVCC）来保证。MVCC通过维护数据的多个版本来避免读写冲突。底层实现包括隐藏字段、undo log和read view。隐藏字段包括trx_id和roll_pointer。undo log记录了不同版本的数据，通过roll_pointer形成版本链。read view定义了不同隔离级别下的快照读，决定了事务访问哪个版本的数据。

21. MySQL主从同步原理是什么？

MySQL主从复制的核心是二进制日志（Binlog）。步骤如下：

1. 主库在事务提交时记录数据变更到Binlog。
2. 从库读取主库的Binlog并写入中继日志（Relay Log）。
3. 从库重做中继日志中的事件，反映到自己的数据中。

22. 你们项目用过MySQL的分库分表吗？

我们采用微服务架构，每个微服务对应一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

23. 那你之前使用过水平分库吗？

候选人：使用过。当时业务发展迅速，某个表数据量超过1000万，单库优化后性能仍然很慢，因此采用了水平分库。我们首先部署了3台服务器和3个数据库，使用mycat进行数据分片。旧数据也按照ID取模规则迁移到了各个数据库中，这样各个数据库可以分摊存储和读取压力，解决了性能问题。

为什么update一定要用索引

因为使用索引进行update时只会对这个索引项加锁。

使用没有索引的条件update时，不知道对哪一个索引加锁，导致对主键索引的每一结点进行加锁，行锁退化成了表锁

count(1)和count(*)和count(字段)的执行速度区别

索引的类型有什么

MySQL的锁有什么

全局锁：用于数据库备份

MySQL数据库的备份

数据库备份需要对数据库加全局锁

flush tables with read lock

1. mysqldump（MySQL自带的工具）

   适用于小数据库

   `mysqldump -u username -p database_name table1 table2 > tables_backup.sql#备份多个表``

   ``mysqldump -u username -p database_name < tables_backup.sql#恢复备份`

2. 二进制日志文件进行增量备份

   可以配合mysqldump使用

3. 对于大型数据库使用xtrabackup

MySQL导入大数据量

1. 如果数据使用的是文本文件(如，CSV、TSV)存储，可以使用load data [infile]
2. 如果使用的是sql文件，可以先使用一些脚本或工具将多个insert语句合并为一条，将数据分批次插入，一次不超过一千个数据（buffer pool限制），从而减少客户端与数据库的通信次数
3. 在插入时可以暂时关闭索引和外键检查，插入完毕后再开启并，如果必要，重建索引。
4. 为了保证执行过程对异常处理，可以创建一张临时的任务表，包含任务id，数据范围，任务状态等字段，每次插入数据前记录任务信息。如果插入出现异常就可以进行回滚或重试

深分页慢，怎么优化/limit 500000 10和limit 10的比较

深分页慢是因为需要先扫描并丢弃前 500,000 条记录，然后再返回接下来的 10 条数据，而limit10只需要直接返回前10条数据

优化方法

• 如果查询涉及到索引，

  • 如果查询覆盖索引，深分页仍然慢，这是因为它是先查询500010条全部数据后丢弃前 500000 条最后返回剩下的 10 条，影响性能的主要是物理读取每一行数据。所以可以使用延迟关联，就是先通过索引获取分页后的500010条数据的10条数据的主键后，再通过主键id查询这10条的完整数据，这样不用加载前500000条完整数据
  • 而如果索引不能覆盖查询的字段，可以先通过覆盖索引查到10条数据的主键id，再通过子查询查找完整的10条数据，这样可以避免查询语句先回表查询到全部500010条数据，再进行分页，提高性能

• 基于游标的分页：对于有序数据，可以记住上一次查询的最后一个记录的主键值，然后在下一次查询时直接从该位置开始查找。不过这种方法不能跨页查询，且主键必须是有序的

• 可以通过分库分表，将表水平拆分，这样查询只需要查询相关的表就行，但是需要解决分布式事务、全局唯一ID、跨库查询等问题

• 引入搜索引擎可以提供更强大的搜索和分页能力，特别是在处理复杂查询和大数据量时。不过这会增加系统架构的复杂度，

• 可以从业务层优化，一般不会跨过500000条数据来查询10条，所以可以通过设置查询的最大页数来限制深分页，比如淘宝、京东商品最多翻到100页

MySQL线上修改大表结构有哪些风险

在线修改大表的表结构执行时间

可以使用工具online-schema-change，可以在不影响表的正常读写的情况下执行DDL操作

MySQL不建议使用NULL作为默认值

SQL语言采用的是三值逻辑，除了真和假，还有第三个值UNKNOWN（不确定）。

• 查询复杂性增加：

  • 使用NULL会增加查询的复杂性，因为NULL不能通过常规的比较运算符（如=、<、>）进行比较，必须使用IS NULL或IS NOT NULL来判断。否则，NULL参与逻辑运算时会返回UNKNOWN，导致查询结果与预期不一致。

• 索引稀疏性问题：

  • 虽然MySQL的B+树索引可以包含NULL值，但在某些情况下（如唯一索引），NULL值可能会导致意外的行为。此外，查询优化器可能无法充分利用包含NULL值的索引，导致查询性能下降。

• 违反业务逻辑和约束：

  • NULL值可能违反业务逻辑和数据库约束。例如，如果某个列在业务逻辑上不能为空，但允许NULL值，可能会导致数据不一致。

• 聚合函数的影响：

  • NULL值会影响聚合函数的结果。例如，COUNT(column)会忽略NULL值，只计算非NULL值，这可能导致统计结果与预期不符。

• 存储和性能开销：

  • 虽然NULL值本身不占用存储空间，但MySQL需要额外的位来标记哪些值是NULL，这会增加一些存储和性能开销。

• 替代方案：

  • 为了避免使用NULL，可以为列设置一个有意义的默认值（如空字符串''、0或特定占位符），或者使用NOT NULL约束，以确保数据的完整性和一致性。

为什么mysql推荐使用自增id

• 自增 ID 是连续递增的整数，在插入新记录时，MySQL 可以直接将新记录追加到表的末尾（B+树的叶子节点），不会为计算新行的位置而做出额外的消耗，也不需要重新排序或调整索引结构。只有当达到页面的最大填充因子时(innodb默认的最大填充因子是页大小的15/16,会留出1/16的空间留作以后的 修改)：将下一条记录写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费，减少了页分裂和碎片的产生

• 因为uuid相对顺序的自增id来说是毫无规律可言的，每次插入都需要为新行寻找新的合适的位置，导致 B+ 树需要重新平衡，影响插入性能。

而插入数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

• 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO

• 因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上

• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片

结论：使用innodb应该尽可能的按主键的自增顺序插入，

• 尽管自增ID有诸多优点，但在某些场景下可能存在局限性：

1. 可预测性：
   • 自增ID是连续的，容易被猜测，可能存在安全风险（如数据爬取或恶意攻击）。
   • 可以通过一些技术手段（如ID加密、哈希等）来增强安全性。
2. 分布式系统的挑战：
   • 在分布式系统中，自增ID需要额外的逻辑来保证全局唯一性（如使用Snowflake算法或数据库分段自增）。
3. 对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

数据量达到多少的时候要开始分库分表？

何时进行分库分表的依据

• 数据存储方式
• 检索方式

InnoDB使用B+树索引结构，其性能与树高度直接相关，每次树高增加意味着多一次磁盘I/O

innoDB的索引树建议控制在三层，超过三层就要考虑分库分表，

而三层可以存储多少条数据呢

• innoDB的数据是存储在页中的，一个页是16KB
• 假设非叶子结点存储的目录项（索引键值（8字节的BIGINT）+ 子页指针（6字节））为14byte，行数据为1KB，
• 第一层的非叶子结点就可以存储16KB/14B = 1170个指向第二层子节点的目录项。
• 所以第二层有1170个非叶子结点，且每个节点也可以存储1170个目录项，
• 第三层就可以有1170*1170个结点，每个结点可以存储16KB/1KB = 16 条行数据。
• 所以第三层可以存储总共1170* 1170* 16 = 2000多万条行数据

实际容量应考虑行格式、碎片率等因素，建议按理论值的70%作为预警线

高频访问的表应适当降低阈值（如按50%计算）