MySQL

典型回答 考虑查询的频率和效率：在决定创建索引之前，需要分析查询频率和效率。对于频繁查询的列，可以创建索引来加速查询，但对于不经常查询或者数据量较少的列，可以不创建索引。 选择适合的索引类型：MySQL提供了多种索引类型，如B+Tree索引、哈希索引和全文索引等。不同类型的索引适用于不同的查询操作，需要根据实际情况选择适合的索引类型。 **考虑区分度：**尽量不要选择区分度不高的字段作为索引，比如性别。但是也并不绝对，对于一些数据倾斜比较严重的字段，虽然区分度不高，但是如果有索引，查询占比少的数据时效率也会提升。 ✅区分度不高的字段建索引一定没用吗？ 考虑联合索引：联合索引是将多个列组合在一起创建的索引。当多个列一起被频繁查询时，可以考虑创建联合索引。 **考虑索引覆盖：**联合索引可以通过索引覆盖而避免回表查询，可以大大提升效率，对于频繁的查询，可以考虑将select后面的字段和where后面的条件放在一起创建联合索引。 ✅什么是回表，怎么减少回表的次数？ 避免创建过多的索引：创建过多的索引会占用大量的磁盘空间，影响写入性能。并且在数据新增和删除时也需要对索引进行维护。所以在创建索引时，需要仔细考虑需要索引的列，避免创建过多的索引。 ✅联合索引是越多越好吗？ 避免使用过长的索引：索引列的长度越长，索引效率越低。在创建索引时，需要选择长度合适的列作为索引列。对于文本列，可以使用前缀索引来减少索引大小。 合适的索引长度：虽然索引不建议太长，但是也要合理设置，如果设置的太短，比如身份证号，但是只把前面6位作为索引，那么可能会导致大量锁冲突。 执行计划分析：多用执行计划分析，因为随着数据库的数据量变化、索引数量变化，最终使用的索引可能也不太一样，所以需要经常查看索引是否有使用对。 ✅为什么MySQL会选错索引，如何解决？

典型回答 不是的，主键是可以自己选择的，我们可以选择任意一种数据类型作为主键。 但是一般都是单独创建一个自增字段作为主键，主要能带来以下几个好处： 索引大小更小：使用自增主键可以确保主键的递增性，使得新插入的数据都会在索引的末尾处，减少了数据页的分裂和页分裂导致的IO操作，使得索引大小更小，查询速度更快。 索引顺序和插入顺序相同：使用自增主键可以保证索引顺序和插入顺序相同，减少了插入新数据时索引的重新排序，提高了插入速度。 安全性：使用自增主键可以避免主键重复的情况，确保数据完整性和唯一性。 减少页分裂（合并）及内存碎片。 ✅什么是InnoDB的叶分裂和叶合并 另外，即使我们用了自增字段作为主键，最后体现在数据库上，字段也可能不是连续自增的，比如做了某一次插入失败但是主键ID被占用了，比如数据做了删除，比如做了回滚、比如做了数据订正等等。 扩展知识 如果没有定义主键怎么办？ 如果我们在一张表中没有定义主键，那么，MySQL会默认选择一个非空的唯一索引作为聚簇索引。 如果没有适合的非空唯一索引，则会创建一个隐藏的主键（row_id）作为聚簇索引。 这个隐藏的主键包含一个自增列和一个6字节的定长记录指针，对用户透明，即用户无法看到或修改这个列，但可以通过使用该列来进行排序或连接操作。

典型回答 UUID和自增主键ID是作为数据库主键比较常用的方式。使用这两个做主键各自都有优缺点。 UUID UUID 是一个 128 位长的唯一标识符，通常以字符串形式表示。它可以使用不同的算法生成，比如基于时间戳的 UUID（version 1）和随机数生成的 UUID（version 4）等。 ✅什么是UUID，能保证唯一吗？ 他的优点是： 全局唯一：使用不同的算法生成，几乎可以保证在全球范围内唯一，避免了多台机器之间主键冲突的问题。 不可预测性：随机生成的 UUID 很难被猜测出来，对于需要保密性的应用场景较为适用。 分布式：由于可以在不同的机器上生成 UUID，因此可以用于分布式系统中。 UUID当做主键ID的话的缺点同样也有，如： 存储空间比较大：UUID 通常以字符串形式存储，占用的存储空间比较大。 不适合范围查询：因为不是自增的，所以在做范围查询的时候是不支持的。（后插入的UUID在排序时可能比前面的要小，在做范围查询的时候可能会出现数据重复或者漏数据的情况） 不方便展示：主键ID有的时候会需要在系统间、或者前台页面展示，如果是UUID的话，就因为比较长、并且没有任何业务含义，所以不方便展示。 查询效率低： 在UUID列上创建索引，因为他很长，所以索引的大小会变得非常大。大的索引会占用更多的磁盘空间，导致缓存命中率下降，进而增加了磁盘I/O的需求。此外，大的索引还会导致查询时的内存开销增加。 当使用UUID进行排序时，新的UUID值通常会插入到叶子节点的中间位置。这可能导致B+树的分裂和平衡操作频繁进行，从而增加了写入的开销。每次分裂或平衡都涉及到数据的重新排序和移动，这会影响查询的性能。 UUID v7这个版本中，已经可以实现自增的UUID了（https://www.yuque.com/hollis666/ec96i7/pi2zfc9ykug141im ），但是目前JDK官方并未支持，想要使用的话需要用第三方的包才行。 自增ID 在 MySQL 中，可以通过设置 AUTO_INCREMENT 属性实现ID的自增长，通常可以用来作为主键ID。 使用自增ID做主键的好处是： 存储空间：ID是数字，所以占用的位数要比UUID小多了，所以在存储空间上也节省很多。 查询效率：ID 是递增的，因此在使用 B+Tree 索引时，查询效率较高。 方便展示：因为ID比较短，方便展示。 分页方便：因为ID是连续自增的，所以在分页的时候，可以通过ID解决深度分页的问题。 但是，使用自增主键做主键ID也存在一定的问题： 分库分表：当我们做分库分表的时候，就没办法依赖一张表的自增主键来做主键ID了，这样就会发生重复导致冲突的问题 可预测：因为ID是顺序自增的，所以是可以预测的，这就给系统带来了一定的安全风险。 可能用尽：自增id的话可能是int、bigint等，但是他们都是有范围的，有可能会用尽

典型回答 不一样，这是MySQL中典型的深度分页的问题。 MySQL的limit m n工作原理就是先读取前面m+n条记录，然后抛弃前m条，然后返回后面n条数据，所以m越大，偏移量越大，性能就越差。 ✅limit的原理是什么？ 所以，limit 10000000,100要比limit 0,100的性能差的多，因为他要先读取10000100条数据，然后再抛弃前面的10000000条。 ✅MySQL的深度分页如何优化 扩展知识 limit优化 通常，我们在查询数据的时候，如果明确的知道我们想要多少行的话，那么建议大家在查询语句中使用limit，而不是把整个结果集查出来再自己丢弃那些不用的数据。 虽然我们前面提过，深度分页的时候，MySQL自己也是先全都查出来，再丢弃的，但是，MySQL针对limit也是有一些优化的。但是下面我们要介绍的优化前提都是使用limit的同时没有用having语句。（参考https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html、https://dev.mysql.com/doc/refman/8.4/en/limit-optimization.html） 1、MySQL通常更愿意执行全表扫描，但是如果你用LIMIT只查询几行记录的话，MySQL在某些情况下可能会使用索引。 2、如果你将LIMIT row_count子句与ORDER BY子句组合在一起使用的话，MySQL会在找到排序结果的row_count行数据后后立即停止排序，而不是对整个结果进行排序。如果使用索引来完成排序，这将非常快。 当ORDER BY子句使用了索引时，MySQL就能够基于已经排好序的索引树快速找到所需的前N行数据，而不需要对整个表进行全表扫描和排序了。 3、如果LIMIT row_count与DISTINCT一起使用，一旦找到row_count惟一的行，MySQL就会停止。 4、LIMIT 0 可以快速返回一个空的结果集，这是用来检测一个查询是否有效的一种很有用的方法。 5、如果ORDER BY不走索引，而且后面还带了LIMIT的话，那么优化器可能可以避免用一个合并文件，并使用内存中的filesort操作对内存中的行进行排序。 limit 和 order by 我们知道，当我们在查询的时候，对某个字段order by时，如果这个字段有一些重复值，那么MySQL会是可以自由的以任意顺序返回这些行记录的，并且根据总体的执行计划而有有不同的表现。也就是说，排序结果可能是不固定的。 所以，当order by语句中，有Limit的时候，可能每次的查询结果都不一样。比如有下面两次查询的结果可能就不一样： 1 2 3 4 5 6 7 8 9 10 11 12 mysql> SELECT * FROM ratings ORDER BY category; +----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | | 2 | 3 | 5.0 | | 7 | 3 | 2.7 | +----+----------+--------+ 1 2 3 4 5 6 7 8 9 10 mysql> SELECT * FROM ratings ORDER BY category LIMIT 5; +----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 4 | 2 | 3.5 | | 3 | 2 | 3.7 | | 6 | 2 | 3.5 | +----+----------+--------+ 那么该怎么解决呢？一个好的办法就是排序的时候不要只用一个字段，可以再加一个字段，比如id字段这种一定不会重复的。 ...

典型回答 在MySQL数据库中，binlog、redolog和undolog都是日志类型文件，但它们各自的作用和实现方式有所不同。 binlog主要用来对数据库进行数据备份、崩溃恢复和数据复制等操作，redo log和undo log主要用于事务管理，记录的是数据修改操作和回滚操作。redolog用来做恢复，undolog用来做回滚。 崩溃恢复是指在数据库系统因为某种原因而发生崩溃或非正常关闭后，通过一系列机制和步骤来恢复数据库到一个一致性和可用的状态。崩溃恢复是数据库管理系统的重要功能，它确保数据库在出现异常情况后能够自动恢复，避免数据丢失或数据库状态不一致的问题。 在MySQL中，redo log和undo log只适用于InnoDB存储引擎，因为要支持事务。而不适用于MyISAM等其他存储引擎。而binlog则适用于所有存储引擎。 binlog是MySQL用于记录数据库中的所有DDL语句和DML语句的一种二进制日志。它记录了所有对数据库结构和数据的修改操作，如INSERT、UPDATE和DELETE等。binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。binlog的格式分为基于语句的格式和基于行的格式。 **Redo Log是MySQL用于实现崩溃恢复和数据持久性的一种机制。**在事务进行过程中，MySQL会将事务做了什么改动到Redo Log中。当系统崩溃或者发生异常情况时，MySQL会利用Redo Log中的记录信息来进行恢复操作，将事务所做的修改持久化到磁盘中。 **Undo Log则用于在事务回滚或系统崩溃时撤销（回滚）事务所做的修改。**当一个事务执行过程中，MySQL会将事务修改前的数据记录到Undo Log中。如果事务需要回滚，则会从Undo Log中找到相应的记录来撤销事务所做的修改。另外，Undo Log还支持MVCC（多版本并发控制）机制，用于在并发事务执行时提供一定的隔离性。 支持引擎 主要作用 binlog innodb、myiasm、… 数据备份、崩溃恢复、主从复制 redolog innodb 崩溃恢复 undolog innodb 事务回滚、MVCC 很多人不知道该怎么记忆，参考咱们的读者@彭超 的建议，可以这么记忆： undo就是回退的意思，就跟在文本编辑器里面有一个undo按钮一样，你编辑的东西，按一下这个undo按钮就回退到上一个版本了。 redo是“re”+“do”，“re"就是重来一次的意思，“do”就是做的意思。所以连在一起，就是重新再做一遍，也就是重新再执行一次sql。那么什么时候需要重新再执行一次sql呢？执行的数据丢了嘛，自然就需要重新执行一次。 bin就是“binary”的缩写，“binary"就是二进制的意思，可以引申为“原始”的意思，所以bin log就是最全最原始的东西，里面包含了一切，所以可以用来做备份，有了它，就有了一切。 扩展知识 undolog 和 redolog区别 目的：Redo log的目的是为了保证事务的持久性，主要用于崩溃恢复，而Undo log的目的是为了保证事务的原子性和一致性，主要用于事务回滚。 记录内容：Redo Log 记录了事务的所有数据更改（这些日志不仅仅记录了数据更改的最终结果，而且还记录了实现这些更改的具体操作）。而Undo log记录的是事务执行前的内容。

典型回答 走了索引也并不一定快，因为有可能存在以下几种情况： 使用索引查询仍然很慢可能是由于以下原因： 选错索引：走了索引也可能走错，当一个SQL可能走多个索引的时候，MySQL的优化器会选择一个，但是这个选择是可能选错的，如果选错了，那就可能会比较慢。 数据分布不均匀：索引的效率与数据的分布密切相关。如果数据分布不均匀，就可能导致某些索引节点的数据量很大，而另外一些节点的数据量很少，从而使查询性能下降。 SQL语句存在问题：查询语句的优化非常重要，一些常用的优化技巧包括避免使用SELECT *、尽量避免多表join等。如果查询语句没有进行优化，就可能导致查询性能较差。 数据库设计不合理：数据库结构的设计也是影响查询性能的一个重要因素。如果表的结构设计不合理，就可能导致查询需要扫描大量的数据才能得到结果，从而影响查询性能。 系统硬件或者网络环境问题：最后，查询性能还可能受到系统硬件或者网络环境等因素的影响。如果系统硬件或者网络环境存在问题，就可能导致查询性能较差。

典型回答 会。 因为数据库的锁锁的是索引，并不是记录。 当我们在事务中，更新一条记录的时候，如果用到普通索引作为条件，那么会先获取普通索引的锁，然后再尝试获取主键索引的锁。 那么这个时候，如果刚好有一个线程，已经拿到了这条记录的主键索引的锁后，同时尝试在该事务中去拿该记录的普通索引的锁。 这时候就会发生死锁。 1 2 3 4 5 6 7 8 9 10 11 update my_table set name = 'hollis',age = 22 where name = "hollischuang"; 这个SQL会先对name加锁， 然后再回表对id加锁。 ----- select * from my_table where id = 15 for update; update my_table set age = 33 where name like "hollis%"; 以上SQL，会先获取主键的锁，然后再获取name的锁。 为了避免这种死锁情况的发生，可以在应用程序中设置一个规定的索引获取顺序，例如，只能按照主键索引->普通索引的顺序获取锁，这样就可以避免不同的线程出现获取不同顺序锁的情况，进而避免死锁的发生（靠SQL保证）。

典型回答 下面是一次explain返回的一条SQL语句的执行计划的内容： 1 2 3 4 5 +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | NULL | idx_abc | 198 | NULL | 5 | 20.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ 一个执行计划中，共有12个字段，每个字段都挺重要的，先来介绍下这12个字段 id：执行计划中每个操作的唯一标识符。对于一条查询语句，每个操作都有一个唯一的id。但是在多表join的时候，一次explain中的多条记录的id是相同的。 select_type：操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION等。不同类型的操作会影响查询的执行效率。 table：当前操作所涉及的表。 partitions：当前操作所涉及的分区。 type：表示查询时所使用的索引类型，包括ALL、index、range、ref、eq_ref、const、system等。 possible_keys：表示可能被查询优化器选择使用的索引。 key：表示查询优化器选择使用的索引。 key_len：表示索引的长度。索引的长度越短，查询时的效率越高。 ref：用来表示哪些列或常量被用来与key列中命名的索引进行比较。 rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。 filtered：表示此操作过滤中保留的的行数占扫描行数的百分比。 Extra：表示其他额外的信息，包括Using index、Using filesort、Using temporary等。 假如我们有如下一张表（MySQL Innodb 5.7）： ...

典型回答 这是一个典型的考察知识储备以及架构能力的问题，一般可以考虑从以下几个方面回答： 硬件优化：升级硬件是提高数据库性能的一种有效方式，包括增加内存、磁盘、CPU和网络带宽等。 存储优化：使用高性能的SSD硬盘代替传统的HDD硬盘，减少I/O延迟。 内存扩展：增加服务器内存，以便更多的数据可以缓存在内存中，提高读取速度。4G不够就上8G，再不够就上16G。 网络优化：使用高速网络连接，减少网络延迟。或者把应用服务器和数据库服务器部署在同一个区域或者同一个机房中，减少网络延迟。 数据库设计：一个好的数据库设计可以提高数据库的性能和可扩展性。 表结构优化：简化表结构，避免过度的多表join。将频繁联合查询的数据进行合理冗余。 数据归档：定期归档旧数据，减少表的大小，提高查询性能。 查询优化：查询是数据库性能的瓶颈之一。使用合适的查询语句、索引、聚合、优化器统计信息等技术，可以有效地优化查询性能。 SQL优化：优化查询语句，避免复杂的子查询，避免多表JOIN，避免深度分页。 查询计划分析：利用数据库的查询分析器，分析并优化执行计划。 索引优化：索引是优化数据库性能的重要手段之一，可以使用合适的索引来提高查询效率。 创建合适的索引：对经常查询和排序的列创建索引。 避免过多索引：过多索引会降低写入性能。 使用覆盖索引：尽量使用索引覆盖所有查询字段，避免回表操作。 ✅设计索引的时候有哪些原则？ 缓存机制：将热点数据缓存在内存中，可以大大加快访问速度。可以使用Redis等缓存技术来实现。 查询缓存：使用Redis等内存数据库缓存频繁查询结果。 对象缓存：对经常访问的并且变化不频繁的对象（如用户信息）进行缓存。可以放到本地缓存中。 负载均衡：合理的负载均衡可以分摊数据库的负载，提高整个系统的性能。可以使用分布式数据库、读写分离等技术来实现。 读写分离：使用主从复制架构，主数据库处理写操作，从数据库处理读操作。 数据库集群：使用数据库集群分散负载。比如建立一主一备，把一些扫表任务和备库连接即可。 分区和分片：将数据分成多个区域或分片，可以降低单个节点的压力，提高整个系统的可扩展性和性能。 分库分表：通过分库来提升数据量连接数，进一步提升吞吐量。通过分表降低单表数据量，提升查询性能。 数据备份和恢复：备份和恢复是数据安全的基础，也是数据库可用性的重要保障。可以使用备份和恢复技术、数据同步技术等来保障数据的安全性和可用性。 性能监控和调优：定期对数据库系统进行性能监控和调优，可以及时发现并解决性能问题。可以使用性能监控工具、数据库性能分析工具等来实现。 综上所述，优化一个大规模的数据库系统需要从多个方面进行考虑和实践，需要不断地进行调整和改进，才能达到更好的性能和可用性。

典型回答 数据库死锁问题是指在多个并发事务中，彼此之间出现了相互等待的情况，导致所有事务都无法继续执行，称为死锁。 关于死锁的概念： ✅什么是死锁，如何解决？ 数据库的死锁的发生通常由以下原因导致： 资源竞争：多个事务试图同时访问相同的资源，如数据库表、行、页或锁。但是它们请求资源的顺序不同，导致互相等待。 未释放资源：事务在使用完资源后未及时释放资源，导致其他事务无法获得所需的资源。这可能是由于程序中的错误或异常情况引起的。 不同事务的执行速度不同：如果一个事务在获取资源后执行速度很慢，而其他事务需要等待该事务释放资源，那么可能会导致其他事务超时，从而发生死锁。 操作的数据量过大：在持有锁的同时，又请求获取更多的锁，导致互相等待。 如果发生了死锁，如何解决： 1、大部分现代数据库管理系统在检测到死锁时会**自动干预**。它们通常选择回滚一个或多个事务来打破死锁。 2、除了自动干预外，很多 DBMS 也支持手动强制回滚某些事务来解决死锁。比如 navicat 解决死锁的办法：https://www.cnblogs.com/xbdeng/p/16541111.html 3、还有就是如果你什么都不做，MySQL 自己也可以解决死锁，一种是立刻解决，一种是延迟解决。 如果MySQL开启了死锁检测（innodb_deadlock_detect = on ），那么他会定时的检测死锁，在检测到死锁后，MySQL将自动选择并终止事务中的一个或多个事务来解决死锁。 如果设置事务等待锁的超时时间（innodb_lock_wait_timeout）。当一个事务的等待获取锁的时长超过这个阈值的时候，会对这个事务进行回滚，这样也能解决死锁。 避免死锁的方法有： 1、减少锁的数量：比如使用RC来代替RR来避免因为gap锁和next-key锁而带来的死锁情况。 2、减少锁的时长：加快事务的执行速度，降低执行时间，也能减少死锁发生的概率。 3、固定顺序访问数据：事务在访问同一张表时，应该以相同的顺序获取锁，这样可以避免死锁的发生。 4、减少操作的数据量：尽量减少事务操作的数据量，尽量减少事务的持有时间，这样可以降低死锁的发生几率。 ✅数据库死锁问题排查过程