MySQL

典型回答 前缀索引是一种对字符串类型字段进行索引优化的方式。它通过只索引字符串字段的前若干字符，而不是整个字段，从而减少索引大小并提高性能。前缀索引常用于处理长文本字段或变长字符串字段，如 VARCHAR 和 TEXT。 假如说我们有一个字段，内容非常的长，比如URL、邮箱地址、家庭住址等，他们就会比较长，那么如果全部都放到索引中，不仅会占用更多的空间，同时也会导致性能下降。 而前缀索引就是说我不要把整个字段都放到索引中，而是保留前N个字符来生成索引。 比如说在一个 VARCHAR(255) 字段上，索引的默认存储会包含整个字符串内容，而通过前缀索引INDEX (address(10)) ，就能为name字的前10个字符创建索引，从而减少索引存储空间。 所以他的用法就是在常见索引时指定前缀长度： 1 2 3 4 5 CREATE TABLE example ( id INT NOT NULL, name VARCHAR(255), INDEX (name(10)) -- 为 `name` 字段的前 10 个字符创建索引 ); 这么做的好处非常明显，就是可以减少索引占用的磁盘空间，一个索引占用的空间小了，B+树中能存放的索引的数量就更多了，那么检索起来性能也就会更好了。而且后期的维护成本也会低很多。 但是，这么做会带来一些问题的，一定要注意： 精确匹配能力下降：由于只索引部分字段内容，前缀索引在字段值高度相似时可能导致索引失效。比如身份证号的前几位、 无法用于覆盖索引：覆盖索引要求索引中存储完整字段，而前缀索引仅存储部分内容，无法满足需求。 选择性依赖于 N 的选择：如果前缀长度选得过短，可能导致索引重复率较高，降低查询性能。而太长的话有没有太大意义。 比如我之间遇到过的那个死锁的问题，就是因为前缀设置的不合理导致的。 ✅数据库死锁问题排查过程 所以，这个前缀索引的前缀的长度，需要选择一个合理的值，重复度不能太高，但是又不能太长。具体的设置方式就要根据具体的字段的数据的分布情况来设定了。但是有个基本原则就是一定不能用选择性不高的前缀长度。 啥叫选择性，可以看下下面这篇文章： ✅为什么MySQL会选错索引，如何解决？ 简单的选择性公式：**选择性=不重复前缀数量/总记录数，**选择性越高，说明索引效果越好 举个例子， 假设有一个包含邮箱的表 1 2 3 4 CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) ); 邮箱的特点是，前几位通常就足以区分大多数记录。例如： ...

典型回答 在 MySQL 中，LIMIT 关键字用于限制查询结果返回的行数。它的原理主要是通过控制查询结果集的返回范围来提高查询效率。LIMIT 可以与 OFFSET 一起使用，指定从哪一行开始返回数据，并限制返回的最大行数。 1 2 SELECT * FROM users LIMIT 10; -- 返回 users 表的前 10 行数据 SELECT * FROM users LIMIT 10, 20; -- 第 11 行开始，返回 20 行数据。 ✅一个查询语句的执行顺序是怎么样的？ limit执行顺序 在上面的文档中，我们介绍了一个SQL查询语句的执行顺序，可以看到，limit其实是在最后执行的，也就是说，在做完筛选、分组、排序等操作之后，最后进行的limit。因为它是对最终结果集的限制。所以在执行完其他所有操作后，才应用 LIMIT，从而确保查询返回的结果集已经是经过完整处理的。 还有就是，limit的查询中，如果是像 LIMIT 10000, 100 这种形式 ，他会先查询出全部数据（10000+100），然后丢弃前面的结果，再返回需要的部分。 这也是为什么深分页很慢的原因： ✅limit 0,100和limit 10000000,100一样吗？ limit执行过程 对于 SQL 查询中 LIMIT 的使用，像 LIMIT 10000, 100 这种形式，MySQL 的执行顺序大致如下： 从数据表中读取所有符合条件的数据（包括排序和过滤）。 将数据按照 **ORDER BY** 排序。 根据 **LIMIT** 参数选择返回的记录： 跳过前 10000 行数据（这个过程是通过丢弃数据来实现的）。 然后返回接下来的 100 行数据。 所以，LIMIT 是先检索所有符合条件的数据，然后丢弃掉前面的行，再返回指定的行数。这解释了为什么如果数据集很大，LIMIT 会带来性能上的一些问题，尤其是在有很大的偏移量（比如 LIMIT 10000, 100）时。 ...

典型回答 不一定。有可能走a的索引，有可能走b的索引。 ✅为什么MySQL会选错索引，如何解决？ 在上面的文章介绍过，mysql选择索引的时候，有很多考量，包括了基数性、选择性、order by等。优化器会根据成本预估选择一个他认为合适的索引。 如果 WHERE a = xx 的过滤条件的选择性较高（即能过滤掉大量行），MySQL 可能优先使用 a 的索引来快速定位满足条件的记录。 如果 WHERE a = xx 的过滤条件的选择性较低（即 a = xx 会匹配大量的记录），而 ORDER BY b 对结果的顺序要求较高，MySQL 可能使用 b 的索引来避免排序操作。

典型回答 首先，在我们常用的Innodb引擎上，默认的加锁是行级锁（当然还有gap lock 和 next key lock），虽然也有可能会进行表锁，但是Innodb会尽可能的避免锁表。只有在极特殊情况下，比如没有用到索引，需要全表扫描时，才有可能会用到表级锁，但是也是有可能，并不代表着一定会加表锁。 另外，在MyISAM 存储引擎上，update语句会默认使用表锁的。 还有，如果我们显示的要进行表锁了，如LOCK TABLES 那么也会进行表锁，包括Innodb。 扩展知识 Innodb的行级锁机制 InnoDB 使用行级锁（Row-level Locking）作为默认的锁定机制。执行 UPDATE 语句时，InnoDB 会根据所更新的记录的索引来锁定特定的行。 如果 UPDATE 语句中使用了有效的索引，并且查询条件能够定位到具体的行，InnoDB 会仅锁定匹配条件的行，而不是整个表。这是最常见的情况。 如果查询条件没有明确指定索引，虽然InnoDB 可能会使用全表扫描，但也不意味着Innodb就会锁全表，它仍然会倾向于使用行锁，对查找到的每一行进行锁定。 行级锁&表级锁 ✅InnoDB中的表级锁、页级锁、行级锁？ 锁的到底是什么？ ✅MySQL的行级锁锁的到底是什么？

典型回答 当使用 LIKE '%xx' 进行查询时，通配符 % 放在了字符串的前面。因为% 表示任意字符，因此查询必须从每一行的开头开始，逐个字符进行比较，以检查是否符合 xx 结尾的条件。所以这种不符合最左前缀匹配的查询模式无法利用常规的索引。 而 LIKE 'xx%'时，% 通配符放在了字符串的后面。那么数据库可以利用索引来优化查询，来查询所有以xx开头的索引，这是因为索引是基于排序的，能够根据前缀值快速定位到符合条件的数据范围。 使用 LIKE 'xx%xxx'时，和LIKE 'xx%'是一样的，他都可以用到左边的xx来进行索引查询。 这就像你要去图书馆查一本书，图书馆的书都是按照名称排序的，你知道你要找的书的第一个字，那么你就可以到和第一个字一样的书架上去直接查找。但是如果你只知道最后两个字，那你就只能从头到尾全都看一遍。

典型回答 所谓ACID，其实是原子性、一致性、隔离性、持久性，那么，MySQL的事务是如何实现原子性、一致性、隔离性和持久性的呢？ 原子性 原子性意味着事务的所有操作要么全部提交成功，要么全部失败回滚。MySQL的原子性是通过undolog实现的。 **Undo Log则用于在事务回滚或系统崩溃时撤销（回滚）事务所做的修改。**当一个事务执行过程中，MySQL会将事务修改前的数据记录到Undo Log中。如果事务需要回滚，则会从Undo Log中找到相应的记录来撤销事务所做的修改。 ✅binlog、redolog和undolog区别？ 隔离性 隔离性指的是多个并发事务执行时，彼此互不干扰。MySQL的隔离性主要靠MVCC和锁机制来保证的。 我们都知道，MySQL中是有不同的隔离级别的，比如RC、RR等，这些的背后其实都是MVCC和锁在起作用。 在下面这篇中，我们介绍过，不同的隔离级别是分别如何实现的。 ✅InnoDB如何解决脏读、不可重复读和幻读的？ 隔离级别 实现方式 读未提交 直接读取最新数据，无锁和 MVCC 控制。 读已提交 每次查询生成新 ReadView，读取已提交的最新版本。 可重复读 事务开始时生成 ReadView，后续所有读操作基于此视图。 串行化 强制加锁，所有操作串行执行。 持久性 持久性指的是事务提交后，修改永久保存，即使系统崩溃也不丢失。 这个我认为主要是依赖MySQL的持久化机制，它是基于磁盘存储的，并且还有Redo Log可以用来崩溃恢复。 ✅binlog、redolog和undolog区别？ **Redo Log是MySQL用于实现崩溃恢复和数据持久性的一种机制。**在事务进行过程中，MySQL会将事务做了什么改动到Redo Log中。当系统崩溃或者发生异常情况时，MySQL会利用Redo Log中的记录信息来进行恢复操作，将事务所做的修改持久化到磁盘中。 一致性 一致性，要求事务执行后，数据库从一个一致状态转换到另一个一致状态。 MySQL中还有很多主键、外键、唯一性约束、非空约束等等都能帮我们保证数据的一致性的逻辑。还有就是其实一致性最重视靠底层 ACID 机制共同保障的，如果原子性、隔离性、持久性做到了，那一致性也就满足了。 ✅什么是事务的2阶段提交？ 总结 特性 实现机制 原子性 Undo Log 隔离性 锁机制（共享锁、排他锁、间隙锁） + MVCC（版本链、ReadView） 持久性 Redo Log（崩溃恢复） 一致性 原子性、隔离性、持久性共同保障 + 数据库约束

典型回答 SELECT * 表示查询一张表中的所有的字段，但是一般线上都不允许直接用select *查询的，都是要求大家指明要查询的字段名，如select a,b,c 。 之所以这么做，一方面是因为select * 会返回所有列的数据，而很多情况下，我们只需要其中一部分数据，全部查询会导致不必要的I/O开销。而且也会增加数据库和应用程序之间的数据传输的开销，而且对象查询过来之后放到内存中，也会占用更多的空间。 而且，有些数据如果直接返回给前端，直接select *，可能也会把一些本来无关的数据给返回回去了，但是有些数据其实是不该返回的。就可能导致数据泄漏、 其次，数据库通常会使用覆盖索引 来优化查询，这样可以减少回表，提升性能，但是SELECT * 可能导致查询无法使用覆盖索引，从而增加 I/O 和查询时间。 ✅什么是索引覆盖、索引下推？ 在以前，数据库还支持缓存的时候，用了SELECT *，只要表结构发生变化（新增/删除字段），缓存可能会失效，导致频繁的查询执行。但是后来缓存被废弃了，这个也就无所谓了。 ✅为什么MySQL 8.0要取消查询缓存？ 还有一个就是可能会导致隐患，比如我们之前就出现过，线上增加了一个新的字段，但是这个字段只在数据库中加了，代码中还不能用，这时候select *就会查到这个字段，就会导致处理异常。当然，这个原因并没那么重要，主要还是上面两个问题。

典型回答 MySQL 通过 多版本并发控制（MVCC） 和 锁机制 实现不同的事务隔离级别。以下是各隔离级别的实现原理及对应的并发控制手段： 隔离级别 脏读 不可重复读 幻读 实现机制 READ UNCOMMITTED 可能 可能 可能 直接读取最新数据（无版本控制） READ COMMITTED 不可能 可能 可能 每次查询生成新 Read View REPEATABLE READ 不可能 不可能 可能 事务开始时生成 Read View + Next-Key 锁 SERIALIZABLE 不可能 不可能 不可能 所有读操作加共享锁，写操作加排他锁 ✅InnoDB如何解决脏读、不可重复读和幻读的？ 扩展知识 MVCC InnoDB 通过 Undo Log 和 Read View 实现 MVCC： ✅如何理解MVCC？ Undo Log： 记录数据修改前的旧版本（版本链），用于构建历史快照。 1 2 3 -- 示例：更新操作生成 Undo Log UPDATE users SET name = 'Bob' WHERE id = 1; -- Undo Log 中会记录旧值（name = 'Alice'）和事务 ID（trx_id=200） Read View： 事务启动时生成，包含以下信息： trx_ids，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。 low_limit_id，应该分配给下一个事务的id 值。 up_limit_id，未提交的事务中最小的事务 ID。 creator_trx_id，创建这个 Read View 的事务 ID。 ✅什么是ReadView，什么样的ReadView可见？ ...

典型回答 不会，索引只有一个，也就是你指定的 (a, b, c) 这个复合索引。 并不会单独创建a，或者ab的索引。也就是最终的索引树上只有一个。会把三个字段都放到树的节点中。 之所以会有这么个问题，是因为很多人知道，(a, b, c)这个联合索引创建之后，按照a、a和b、a和c、a和b和c查询，都能用到索引。 ✅A,B,C的联合索引，按照 AB,AC,BC查询，能走索引吗？ 但是这是因为索引的查询遵循最左前缀匹配原则，存储的时候是按照a,b,c的顺序来的，也就是只要查询时候包含最左字段就能用到索引，但是索引树还是只有一棵的。

典型回答 我们知道多表join效率很低，也介绍过通过一些方式避免join的出现，那么一定要用join的话，该如何优化，减少查询耗时呢？ ✅为什么大厂不建议使用多表join？ 优化一，使用索引做join MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，具体到算法上面主要有simple nested loop join，block nested loop join和index nested loop join这三种。 其中index nested loop，当Inner Loop的表用到字段有索引的话，可以用到索引进行查询数据，因为索引是B+树的，复杂度可以近似认为是N*logM。 比如：ON a.user_id = b.user_id，那么 a.user_id 和 b.user_id 都应该有索引会更好。 优化二，小表驱动大表 ✅MySQL 为什么是小表驱动大表，为什么能提高查询性能？ 小表放在前面，大表放在后面的话，会使得整体的查询性能有很大提升。（当然也使用了索引的情况下） 优化三，数据过滤后再join 参考小表驱动大表的思路，我们可以先把表中的数据做一下where筛选过滤，把无关的数据过滤掉，这样就能尽量避免把大表和大表直接 JOIN，所以，一般是先使用子查询或临时表缩小数据量。 如： 1 2 3 4 5 6 7 SELECT o.id, o.order_date, c.name FROM ( SELECT id, order_date, customer_id FROM orders WHERE order_date >= '2024-01-01' ) AS o JOIN customers c ON o.customer_id = c.id; 1 2 3 4 5 6 7 8 9 10 -- 第一步：创建临时表（或中间结果表） CREATE TEMPORARY TABLE recent_orders AS SELECT id, order_date, customer_id FROM orders WHERE order_date >= '2024-01-01'; -- 第二步：再做 JOIN SELECT o.id, o.order_date, c.name FROM recent_orders o JOIN customers c ON o.customer_id = c.id; 优化四，优先使用内连接 如果不需要保留所有左表或右表数据，用 INNER JOIN 更高效。 因为INNER JOIN 的执行逻辑更加简单，他只需要保留 两个表中都存在匹配记录的行。而且INNER JOIN 在两侧字段上都容易使用到索引。LEFT JOIN 如果右表字段为 NULL，某些场景下索引会失效。 ...