MySQL面试题八股文

爱吃巧克力的程序媛
315 阅读43分钟

mysql中in 和exists的区别

  1. in内查询的sql结果查出来，放到一个临时表中，然后遍历临时表（条件表），将临时表中的每一行带入到外查询中查找。 当外查询表大，内查询表小时，推荐使用in。用in可以减少对外查询表的循环遍历次数。
SELECT *
FROM outer_table
WHERE id IN (
    SELECT id
    FROM inner_table
);
  1. exists用于对外表记录筛选。 exists会遍历外查询表，将外查询表的每一行，带入到内查询进行判断。当exists里的条件语句能够返回记录行时，条件为真，返回外表当前的记录；否则，条件为假，则当前记录丢弃。 当外查询表小，内查询表大时，推荐使用exists。因为当子查询表比较大时，使用exists可以有效减少总的循环次数，来提升速度

image.png

SELECT *
FROM table_name t
WHERE EXISTS (
    SELECT 1
    FROM another_table a
    WHERE a.column_name = t.column_name
);

mysql 的内连接、左连接、右连接有什么区别?

MySQL 中的内连接、左连接和右连接是用于在多个表之间进行关联查询的不同方式，它们之间的区别如下：

  1. 内连接（INNER JOIN）：
    • 内连接通常用于获取两个表中能够匹配的共同的数据。
    • 如果两个表中的某行没有与另一个表中的行匹配，则该行将不会包含在结果集中。
SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
  1. 左连接（LEFT JOIN）：
    • 左连接返回左表（左侧表）中的所有行，以及右表（右侧表）中与左表匹配的行。
    • 如果右表中没有与左表匹配的行，则返回的结果集中右表的字段值将为 NULL。
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
  1. 右连接（RIGHT JOIN）：
    • 右连接返回右表（右侧表）中的所有行，以及左表（左侧表）中与右表匹配的行。
    • 如果左表中没有与右表匹配的行，则返回的结果集中左表的字段值将为 NULL。
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

总的来说，内连接会返回两个表中共同的数据，左连接会返回左表中的所有数据和与之匹配的右表数据，右连接会返回右表中的所有数据和与之匹配的左表数据。

什么是笛卡尔积？

在关系型数据库中，笛卡尔积（Cartesian Product）指的是将两个表中的所有行进行组合，生成一个新的表。具体来说，如果有两个表 A 和 B，每个表包含 m 和 n 行，则它们的笛卡尔积将包含 m × n 行。

在进行笛卡尔积操作时，每个表中的每一行都将与另一个表中的所有行进行组合，生成一个新的结果集。这种操作通常发生在没有指定任何连接条件的情况下，或者使用了交叉连接（CROSS JOIN）关键字。

笛卡尔积操作可能会导致结果集非常庞大，尤其是在对包含大量行的表进行操作时。因此，在实际应用中，应该尽量避免使用笛卡尔积，或者在进行笛卡尔积操作前确保有合适的条件来限制结果集的大小。

InnoDB与MyISAM的区别

InnoDB 和 MyISAM 是 MySQL 数据库中两种常见的存储引擎，它们在功能和特性上有一些区别：

  1. 事务支持：

    • InnoDB 支持事务（ACID 属性），可以使用 COMMIT 和 ROLLBACK 进行事务管理，确保数据的一致性和完整性。
    • MyISAM 不支持事务，不具备事务的特性，不能进行回滚操作。

  2. 行级锁定：

    • InnoDB 使用行级锁定（row-level locking），可以更好地支持并发操作，减少锁定的竞争，提高系统的并发性能。
    • MyISAM 使用表级锁定（table-level locking），在对表进行写操作时会锁定整个表，影响了并发性能。

  3. 外键约束：

    • InnoDB 支持外键约束（FOREIGN KEY），可以在表之间建立关联关系，确保数据的完整性和一致性。
    • MyISAM 不支持外键约束，无法定义外键关系。

  4. 崩溃恢复：

    • InnoDB 支持崩溃恢复和自动恢复功能，具备更好的可靠性和容错性，能够在数据库崩溃后自动恢复到一致状态。
    • MyISAM 对于崩溃恢复支持较弱，可能会出现数据损坏或丢失的情况，需要手动进行修复。

  5. 表空间：

    • InnoDB 存储引擎使用表空间（tablespace）来管理存储，支持行级别的压缩和数据加密。
    • MyISAM 使用文件系统来管理存储，表的数据和索引存储在不同的文件中。

  6. 全文索引：

    • InnoDB 在 MySQL 5.6 版本之后开始支持全文索引（FULLTEXT INDEX），可以用于进行全文搜索。
    • MyISAM 早期就支持全文索引，可以用于进行全文搜索。

综上所述，InnoDB 更适合于需要事务支持、并发操作、数据完整性和可靠性的应用场景，而 MyISAM 则适用于对性能要求较高、不需要事务支持和数据完整性检查的简单应用场景。

事务

事务是一组操作的集合，它是一个业务，是不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或销毁操作。假设存在两条正在执行的sql，这两条sql要么都成功，要么都失败

MySQL事务的四大特性（或ACID原则）以及实现原理

MySQL 事务的四大特性，也称为 ACID 原则，是指原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）：

  1. 原子性（Atomicity）：

    • 原子性要求事务中的所有操作要么全部执行成功，要么全部执行失败，不存在部分成功的情况。
    • 实现原理：MySQL 使用日志（Redo Log 和 Undo Log）来实现原子性。当事务执行时，将所有操作记录在日志中，如果事务执行失败，可以使用 Undo Log 进行回滚，保证事务的原子性。

  2. 一致性（Consistency）：

    • 一致性要求事务执行前后，数据库的状态保持一致性，即满足所有的约束和规则。
    • 实现原理：在数据库设计和业务逻辑中设置合适的约束和规则，保证事务执行过程中数据的一致性。

  3. 隔离性（Isolation）：

    • 隔离性要求多个事务之间的操作相互隔离，每个事务看到的数据应该是一致的，不受其他事务的影响。
    • 实现原理：MySQL 通过事务隔离级别来实现隔离性，常见的隔离级别包括读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和串行化（SERIALIZABLE），通过锁机制和数据版本控制来保证事务的隔离性。

  4. 持久性（Durability）：

    • 持久性要求事务一旦提交成功，对数据库中的数据修改就是永久性的，即使系统发生故障或崩溃，修改的数据也不会丢失。
    • 实现原理：MySQL 使用日志来实现持久性。事务提交成功后，将事务的提交记录（Commit Record）写入到日志中，然后将数据修改写入到磁盘中，确保数据持久化。

综上所述，MySQL 事务的四大特性是通过日志、锁机制和数据版本控制来实现的。通过这些机制，MySQL 能够保证事务的原子性、一致性、隔离性和持久性，从而确保数据库操作的安全性和可靠性。

INSERT INTO `account` (`name`,`money`) VALUES ('A',2000),('B',1000)
-- mysql 默认开启事务自动提交

-- 手动处理事务
-- 操作：开启事务：START TRANSACTION;/BEGIN    提交事务：COMMIT    回滚事务：ROLLBACK

-- 1.关闭自动提交
SET autocommit = 0  -- 关闭

-- 2. 事务开启
START TRANSACTION  -- 标记一个事务的开始，这个之后的sql都在一个事务内(sQL都成功，或都失败)

-- 事务控制的SQL
UPDATE `account` SET `money` = `money` - 500 WHERE `name` = 'A'
UPDATE `account` SET `money` = `money` + 500 WHERE `name` = 'B'
 
-- 3.
-- ①执行成功，COMMIT 提交：持久化到数据库 
COMMIT
-- 或②执行失败，ROLLBACK 回滚：回到原来的状态
ROLLBACK
-- 事务结束
SET autocommit = 1  -- 开启自动提交

-- 补充
SAVEPOINT 保存点  -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点 -- 回滚到保存点

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

juejin.cn/post/684490…

事务的隔离级别是指多个事务之间的操作相互隔离程度，MySQL 支持以下四种隔离级别：

  1. 读未提交（Read Uncommitted）：允许事务读取其他事务尚未提交的数据，可能会导致脏读（Dirty Read）和不可重复读（Non-repeatable Read）问题。

  2. 读已提交（Read Committed）：确保事务只能读取其他事务已经提交的数据，避免了脏读问题，但仍可能出现不可重复读和幻读（Phantom Read）问题。

  3. 可重复读（Repeatable Read）：确保事务在执行期间多次读取同一数据时，数据保持一致性，即使其他事务对数据进行了修改或删除，也不会被看到，避免了不可重复读问题，但仍可能出现幻读问题。

  4. 串行化（Serializable）：最高的隔离级别，确保事务之间完全隔离，每个事务都像是在独立运行的系统中一样，避免了脏读、不可重复读和幻读问题，但会导致并发性能下降。

MySQL 的默认隔离级别是 可重复读（Repeatable Read）。

什么是幻读，脏读，不可重复读呢？（或隔离性可能出现的问题）

在数据库事务中，隔离性问题可能会导致以下几种问题：

  1. 脏读（Dirty Read）：

    • 脏读指的是一个事务读取到了另一个事务未提交的数据。假设事务 A 修改了某条数据，但尚未提交，此时事务 B 可以读取到事务 A 修改的数据。如果事务 A 最终回滚，那么事务 B 读取到的数据实际上是无效的，这就是脏读问题。

  2. 不可重复读（Non-repeatable Read）：

    • 不可重复读指的是一个事务内的两次查询结果不一致。假设事务 A 在两次查询之间，事务 B 修改了某条数据并提交，那么事务 A 第二次查询的结果就会与第一次查询的结果不一致。这是因为事务 A 两次查询之间，数据发生了变化，导致了不一致的结果。

  3. 幻读（Phantom Read）：

    • 幻读指的是一个事务在两次查询之间，另一个事务插入了新的数据，导致第一个事务在第二次查询时发现了新增的数据。这种情况下，第一个事务会产生幻行，就好像出现了幻觉一样。幻读与不可重复读的区别在于，不可重复读是现有的数据被修改，而幻读是现有数据之外的新数据被插入。

这些问题的出现是由于事务的并发操作导致的，为了解决这些问题，需要在事务中使用适当的隔离级别来保证数据的一致性和完整性。

undo log和redo log的区别

redo log：记录数据页的物理变化，当服务宕机可以用来同步数据。保证了事物的持久性。

undo log：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据。保证了事务的原子性和一致性。

image.png image.png

image.png

事务的隔离性如何保证？---MVCC

  1. 锁：排他锁：如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁。
  2. mvcc：多版本并发控制。维护一个数据的多个版本，使得读写没有冲突。

mvcc具体实现：隐藏字段、undo log日志，readView blog.csdn.net/Waves___/ar…

索引

索引数据结构

索引常见的有三种数据结构：哈希表、有序数组和二叉树。MySQL使用了B+树。

  • 二叉树

    • 结构：右侧元素大于父元素数据，左侧数据小于父元素数据。
    • 缺点：顺序插入时，会形成一个单链表，查询性能大大降低。

  • 红黑树（平衡二叉树）

    • 与二叉树结构一样，但是在生成的过程中红黑树会自动平衡节点
    • 缺点：虽然会自动平衡，但是在大数据量下，层级较深，检索速度会慢。

image.png

  • b-Tree
    • 叶节点具有相同的深度，叶节点的指针为空
    • 所有索引元素不重复
    • 节点中的数据索引从左到右递增排列
    • 节点中存储data数据

image-20240130220921733.png

  • b+Tree

    • 非叶子节点不存储data，只存储索引，目的是可以放更多的索引。
    • 叶子节点包含所有索引字段
    • 叶子节点使用指针连接，提高区间访问的性能

image.png

数据库索引的原理，为什么要用 B+树，不使用B树？

因为.B+比B书层级小，效率高。 B树每个结点存放以索引列为条件的该用户所有信息。而B+树非叶子结点只存放主键的信息、叶子结点存放所有信息，非叶子结点只起到索引作用 在相同磁盘空间存放时，B+树存放的元素会更多。

索引分类

  1. 主键索引：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据，表中只能有一个主键索引
  2. 唯一索引：唯一索引要求索引列的值是唯一的，不允许重复值，用于确保数据的唯一性。
  3. 常规索引：快速定位特定数据。
  4. 全文索引：全文索引是针对文本数据的索引，查找的是文本中的关键词，用于加速全文搜索的查询操作。
  5. 聚集索引：将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据。聚集索引必须有且只有一个。如果存在主键，主键索引就是聚集索引；如果不存在主键，将使用第一个唯一索引作为聚集索引。

image-20240131112536233.png 7. 二级索引（非聚集索引）：将数据和索引分开存储，索引结构的叶子节点关联对应的主键。可以存在多个二级索引。

image-20240131112745292.png

聚集索引与非聚集索引的区别？

blog.csdn.net/m0_45406092…

  • 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
  • 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
  • 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚集索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
  • 聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

回表

在MySQL数据库中回表，通常与索引相关。回表发生在使用索引查找数据行后，MySQL需要进一步检索其他数据列 例如：先根据'Arm'在二级索引找到对应的id主键，然会再去聚集索引找id对应的行数据。

select * from user where name = 'Arm';

覆盖索引

覆盖索引（Covering Index）：查询使用了索引，返回的列，必须在索引中能够全部被找到。

  1. 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
  2. 如果返回的列中没有创建索引，有可能触发回表查询，因此开发中尽量避免使用select*。

例子：下图中，第一条sql走聚集索引（主键索引），能够查到所有信息 第二条sql，走二级索引，能够查到name和id 第三条，先走二级索引，拿到id值，再去聚集索引找到这一行数据，查找gender。（回表查询）

image.png

MySQL超大分页如何处理？

在数据量比较大时，使用limit进行分页查询，需要对数据进行排序，效率低。 解决方案：覆盖索引+子查询。 先分页查询数据的id字段，确定了id之后，再用子查询过滤。因为查询id的时候，走的是覆盖索引，所以效率可以提升很多。

image.png

image.png

什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则和最左匹配原则是数据库索引中常用的两个概念，它们指导了数据库在利用复合索引进行查询时的匹配方式。

  1. 最左前缀原则（Leftmost Prefix Rule）：
    • 最左前缀原则要求在使用复合索引进行查询时，查询条件必须从索引的最左边开始，并且连续使用索引中的列，不能跳过索引中的列。
    • 也就是说，如果一个复合索引包含了多个列 (a, b, c)，那么查询条件必须以列a开始，并且连续使用 a、b、c 中的一部分或全部列，不能跳过任何列。否则，数据库无法有效地利用这个索引进行查询。

例如：复合索引：idx_user_add_em（address,email），其中address比email先执行。因此最左边的列（address）必须存在。最左边的列不存在，则全表扫描。

image-20240201153536971.png

  1. 最左匹配原则（Leftmost Match Rule）：
    • 最左匹配原则是最左前缀原则的延伸，它描述了在满足最左前缀的条件下，可以在查询中使用索引的前缀部分进行匹配。
    • 也就是说，当查询条件中包含了索引的前缀列时，数据库可以利用这个索引进行匹配。例如，如果一个复合索引包含了多个列 (a, b, c)，那么查询条件中只包含列 a 或者 a、b 时，数据库仍然可以使用这个索引。

最左前缀原则和最左匹配原则的应用可以使数据库尽可能地利用复合索引，提高查询效率。因此，在设计复合索引时，需要根据实际查询的需要来考虑索引的顺序，尽量让最频繁使用的列放在索引的最左边。

索引下推

索引下推（Index Condition Pushdown，简称ICP）是一种数据库优化技术，用于在查询执行过程中将部分条件推送至存储引擎层级，以减少数据库引擎在上层进行过滤的数据量，从而提高查询性能。

具体来说，索引下推的过程如下：

  1. 当数据库接收到一个带有查询条件的 SQL 查询请求时，它会首先检查是否有合适的索引可以用于查询。

  2. 如果存在合适的索引，数据库引擎会尝试将查询条件中与索引相关的部分推送至存储引擎层级。这些条件通常是可以利用索引进行快速定位的条件，例如等值查询、范围查询等。

  3. 存储引擎接收到推送的查询条件后，会利用这些条件在索引中进行快速定位，找到满足条件的索引条目。

  4. 存储引擎根据索引中定位到的条目，获取对应的数据行，并返回给数据库引擎层级。

  5. 数据库引擎在接收到数据行后，根据未推送至存储引擎层级的查询条件进行进一步过滤，以满足查询的完整条件。

通过索引下推技术，数据库可以将部分查询条件下推至存储引擎层级，减少了上层数据库引擎需要处理的数据量，从而提高了查询性能。这种优化技术特别适用于复杂的查询语句，可以显著减少数据库引擎的工作量，提升查询效率。

InnoDB 的索引策略

www.cnblogs.com/jamaler/p/1…

索引创建原则

image.png

索引失效

juejin.cn/post/684490…

MySQL 索引使用有哪些注意事项呢？

  1. 选择合适的字段：选择最常用于查询条件的字段创建索引，尤其是经常用于 WHERE 子句、JOIN 子句和 ORDER BY 子句的字段。

  2. 避免在过大的字段上创建索引：过大的字段会占用更多的存储空间，并且对索引效率没有帮助，应该尽量避免在这些字段上创建索引。

  3. 避免在频繁修改的字段上创建索引：频繁修改的字段会导致索引频繁更新，增加数据库的负担，应尽量避免在这些字段上创建索引。

  4. 使用短索引：如果字段值长度很长，可以使用字段值的前缀来创建索引，以减小索引的大小，提高索引效率。

  5. 避免过多的索引：过多的索引会增加数据库的存储空间和维护成本，应根据实际需求和查询模式，合理地选择和创建索引。

  6. 定期维护索引：定期对数据库进行索引优化和维护，包括删除不再使用的索引、重建破碎的索引等，以保持索引的高效性。

  7. 使用覆盖索引：如果查询中只涉及到了索引列，可以使用覆盖索引，避免访问表的数据行，从而提高查询效率。

  8. 分析查询执行计划：定期分析查询执行计划，查看索引的使用情况和效率，根据需要进行调整和优化。

索引不适合哪些场景

虽然索引可以显著提高数据库查询性能，但在某些情况下并不适合使用索引，具体情况包括：

  1. 低基数的列：如果某个列的取值范围很小，即基数（Cardinality）很低，那么在这个列上创建索引可能会失去意义，因为大部分查询都会返回大量的数据行，索引无法有效地过滤数据。

  2. 大数据量的表：对于大数据量的表，索引的维护成本会变得很高，因为索引需要占用额外的存储空间，并且在数据修改时需要维护索引，会增加数据库的负担。

  3. 频繁更新的列：如果某个列经常被更新，那么在这个列上创建索引会增加更新操作的开销，因为每次更新都需要更新索引。

  4. 短查询范围的列：对于某些查询范围很小的列，例如性别列（只有两种取值），在这些列上创建索引并不会提高查询效率，因为大多数查询都只返回少量的数据行。

  5. 高并发的写入操作：对于高并发的写入操作，索引的维护成本会变得很高，可能会导致数据库性能下降。

MySQL 主从复制/主从同步原理

juejin.cn/post/684490…

image.png

如何进行同步的？

image.png

MySQL 锁

在MySQL中，锁是用于控制对数据库中数据的并发访问的机制。MySQL中的锁可以分为多种类型，包括表级锁和行级锁，具体如下：

  1. 表级锁：

    • 表级锁是对整个表进行锁定，当一个事务获取了表级锁后，其他事务无法对表进行修改操作。MySQL中常见的表级锁有：
      • 表锁（Table Lock）：锁定整个表，常见的表锁有读锁（LOCK TABLES ... READ）和写锁（LOCK TABLES ... WRITE）。
      • 元数据锁（Metadata Lock）：用于锁定数据库的元数据，包括表结构、表状态等。

  2. 行级锁：blog.csdn.net/m0_37695902…

    • 行级锁是对表中的单行数据进行锁定，可以实现更细粒度的并发控制。MySQL中常见的行级锁有：
      • 共享锁（Shared Lock，也称读锁）：允许多个事务同时对同一行进行读操作，但不允许对该行进行写操作。
      • 排他锁（Exclusive Lock，也称写锁）：只允许一个事务对某一行进行修改操作，其他事务无法同时对该行进行读或写操作。
      • 行锁（Row Lock）：MySQL中实际使用的行级锁，通过在索引上加锁实现，不同的存储引擎对行锁的实现方式略有不同。

MySQL的锁机制在保证并发性的同时，也会带来一定的性能开销和复杂性，因此在设计数据库应用时需要谨慎考虑锁的使用。合理的锁策略可以提高系统的并发性能，避免数据不一致和死锁等问题。

什么是死锁？怎么解决？

在数据库中，死锁指的是多个事务相互等待对方释放锁资源，从而导致所有事务都无法继续执行的情况。

解决死锁问题的方法主要有以下几种：

  1. 优化数据库设计：

    • 合理设计数据库表结构，避免事务过长时间持有锁资源。
    • 尽量减少事务中涉及的表数量，缩短事务持有锁的时间。

  2. 尽量缩短事务执行时间：

    • 在事务中，尽量减少对数据库的锁定时间，以减少发生死锁的可能性。
    • 合理拆分大事务，减小事务的粒度。

  3. 加锁顺序：

    • 确定良好的加锁顺序，尽量按照相同的顺序访问表和行，以减少死锁的发生。
    • 对于需要更新多个表的事务，应该按照固定的顺序对表进行加锁，避免不同的事务加锁顺序不一致导致的死锁。

  4. 设置超时时间：

    • 对于等待锁资源的事务，可以设置超时时间，当等待时间超过一定阈值时，自动回滚事务，释放锁资源。

  5. 监控和检测：

    • 设置监控机制，定期检测系统中是否存在死锁，并进行相应的处理。
    • 可以通过数据库的系统视图或者日志来监控死锁的发生情况，并及时采取措施进行处理。

  6. 死锁检测和解除：

    • 对于已经发生的死锁，可以通过数据库系统自带的死锁检测机制来检测和解除死锁。
    • 当数据库检测到死锁时，会自动选择一个事务进行回滚，释放资源，从而解除死锁。

什么是Mysql的next-key、插入缓冲、二次写、自适应哈希索引和预读？

blog.csdn.net/m0_46761060…

数据库的三范式是什么

数据库的三范式（Normal Forms）是一种数据设计原则，用于规范化数据库表结构，减少数据冗余，提高数据存储的效率和一致性。三范式包括以下三个级别：

  1. 第一范式（1NF） ：确保每个数据库表中的每一列都是原子性的，即不可再分。换句话说，每个字段中不包含多个值或重复的值。这样可以避免数据的重复和冗余。例如，如果一个学生有多个电话号码，应该将电话号码单独存储在一个表中，并通过外键与学生表关联，而不是将电话号码存储在学生表的多个字段中。
  2. 第二范式（2NF） ：在满足第一范式的基础上，确保每个非主键列完全依赖于表中的每个候选键（组合键）。换句话说，每个非主键列都应该与表中的全部键有关，而不是部分键。这样可以避免部分依赖和传递依赖。例如，如果一个订单表中包含订单号和产品号，应该将产品的其他信息（如名称、价格等）存储在单独的产品表中，而不是在订单表中重复存储产品信息。
  3. 第三范式（3NF） ：在满足第二范式的基础上，确保每个非主键列之间不存在传递依赖。换句话说，如果一个非主键列依赖于另一个非主键列，那么它们之间应该直接关联，而不是通过第三个非主键列传递依赖。这样可以进一步减少数据冗余。例如，如果一个员工表中包含部门号和部门名称，而部门名称仅依赖于部门号，而不是员工号，则应将部门信息存储在单独的部门表中，并通过外键关联。

表设计经验（三大范式要遵守，必要的时候进行适当的冗余，表字段多的时候进行合理的拆分）

  • 什么时候进行冗余：username（频繁查询）。当业务分库、分表或者运行在不同服务器上，那么进行组合查询时代价比较大，并且这个字段更新不频繁，那么可以进行冗余。
  • 合理拆分：比如用户表，用户的工作经历也可以拆分出来，减少一次查询返回太多的数据，减轻压力。也可以在业务中利用懒加载的思想，先进行主表查询，点击查看工作经历，再进行工作经历表的查询。

SQL优化经验

juejin.cn/post/712857…

  • 加索引
  • 避免返回不必要的数据
  • 适当分批量进行
  • 优化sql结构
  • 分库分表
  • 读写分离

说说分库与分表的设计

注意：主从复制让读写分开访问，解决了访问的压力。但是解决不了海量数据存储的问题。

image.png

如何定位慢查询？

慢查询表象：页面加载过慢、接口压测响应时间过长（超过1s）

  1. 利用慢查询日志 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒）的所有SQL语句的日志。
  2. 使用运维工具Skywalking，可以监测出哪个接口

如何优化慢接口的

  • 采用explain命令获取MySQL如何执行Select语句的信息
    • 通过key和key_len检查走了索引（索引本身存在是否有失效的情况）
    • 通过type字段查看sql是否有进一步优化空间，是否存在全表扫描
    • 通过extra建议判断，是否出现了回表情况，如果出现，可以尝试添加索引或者修改返回字段来修复。

image.png

image.png

  • 判断这些查询是否可以压缩（比如for循环里查数据库），减少查询次数
  • 分析这些查询所查出的字段是否必要，精简要查询的字段
  • 判断是否有索引、索引字段的顺序是否满足最左匹配等原则
  • 判断这个接口是否有加缓存的必要（很多人来调用，拿到的结果都一样）
  • 业务上可不可以接受懒加载，如果能懒加载，一般会能得到更好的提升
  • 最后再检查一下硬件资源，判断带宽使用情况，因为有时候并不是接口的处理慢，而是网络传输慢、前端渲染慢，这时候要扩充硬件资源，比如将服务器拆分，后端服务器、前端服务器、数据库服务器、文件服务器，同时做集群部署。

如何配置多数据源

  1. 配置每个数据源的连接信息。
# 数据源1配置
spring.datasource.datasource1.url=jdbc:mysql://localhost:3306/database1
spring.datasource.datasource1.username=root
spring.datasource.datasource1.password=password
​
# 数据源2配置
spring.datasource.datasource2.url=jdbc:mysql://localhost:3307/database2
spring.datasource.datasource2.username=root
spring.datasource.datasource2.password=password
  1. 在 Spring Boot 应用程序中，使用 @Configuration 注解配置多个数据源以及事务管理
  • AbstractRoutingDataSource 实现了动态数据源选择的功能。在需要使用特定数据源的地方，通过 DataSourceContextHolder 设置当前线程的数据源选择策略，然后框架会根据当前线程选择对应的数据源进行操作。
  • 配置事务管理器，确保事务可以正确地跨数据源管理。
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
​
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
​
@Configuration
public class DataSourceConfig {
​
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.datasource1")
    public DataSource dataSource1() {
        return new DriverManagerDataSource();
    }
​
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.datasource2")
    public DataSource dataSource2() {
        return new DriverManagerDataSource();
    }
​
    @Bean
    public DataSource routingDataSource() {
        AbstractRoutingDataSource routingDataSource = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                // 根据当前线程的数据源选择策略选择数据源
                return DataSourceContextHolder.getDataSource();
            }
        };
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("datasource1", dataSource1());
        targetDataSources.put("datasource2", dataSource2());
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(dataSource1()); // 默认数据源
        return routingDataSource;
    }
    
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(routingDataSource());
    }
}

  1. 创建数据源上下文类：DataSourceContextHolder.java

    创建一个数据源上下文类，用于存储当前线程所使用的数据源标识。这个类通常会使用 ThreadLocal 来存储数据源标识，确保在每个线程中都可以独立设置和获取数据源。

public class DataSourceContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
​
    public static void setDataSource(String dataSourceKey) {
        contextHolder.set(dataSourceKey);
    }
​
    public static String getDataSource() {
        return contextHolder.get();
    }
​
    public static void clearDataSource() {
        contextHolder.remove();
    }
​
  1. 在需要切换数据源的地方进行切换：通过调用数据源上下文类的方法，设置当前线程所使用的数据源标识，然后在执行数据库操作前切换到对应的数据源，在操作完成后清除数据源设置
@Service
public class UserService {
​
    @Autowired
    private JdbcTemplate jdbcTemplate;
​
    public void getUser() {
        DataSourceContextHolder.setDataSource("datasource1");
        // 使用 dataSource1 进行数据库操作
        DataSourceContextHolder.clearDataSource();
​
        DataSourceContextHolder.setDataSource("datasource2");
        // 使用 dataSource2 进行数据库操作
        DataSourceContextHolder.clearDataSource();
    }
}

书写高质量SQL的30条建议

juejin.cn/post/684490…

1、查询SQL尽量不要使用select *，而是select具体字段。

反例子：

select * from employee;

正例子：

select id，name from employee;

理由：

  • 只取需要的字段，节省资源、减少网络开销。
  • select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

2、如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1

假设现在有employee员工表，要找出一个名字叫jay的人.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

反例：

select id，name from employee where name='jay'

正例

select id，name from employee where name='jay' limit 1;

理由：

  • 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。
  • 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

3、应尽量避免在where子句中使用or来连接条件

新建一个user表，它有一个普通索引userId，表结构如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下sql

反例:

select * from user where userid=1 or age =18

正例：


//使用union all 
select * from user where userid=1 
union all 
select * from user where age = 18

//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 18

理由：

  • 使用or可能会使索引失效，从而全表扫描。

对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。 mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

4、优化limit分页

我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。

反例：


select id，name，age from employee limit 10000，10

正例：


//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.

//方案二：order by + 索引
select id，name from employee order by id  limit 10000，10

//方案三：在业务允许的情况下限制页数：

理由：

  • 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
  • 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
  • 方案二使用order by+索引，也是可以提高查询效率的。
  • 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

5、优化你的like语句

日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。

反例：


select userId，name from user where userId like '%123';

正例：


select userId，name from user where userId like '123%';

理由：

  • 把%放前面，并不走索引，如下：

  • 把% 放关键字后面，还是会走索引的。如下：

6、使用where条件限定要查询的数据，避免返回多余的行

假设业务场景是这样：查询某个用户是否是会员。曾经看过老的实现代码是这样。。。

反例：


List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);

正例：


Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId！=null;

理由：

  • 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。

7、尽量避免在索引列上使用mysql的内置函数

业务需求：查询最近七天内登陆过的用户(假设loginTime加了索引)

反例：


select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

正例：


explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

  • 索引列上使用mysql的内置函数，索引失效

  • 如果索引列不加内置函数，索引还是会走的。

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

反例：


select * from user where age-1 =10；

正例：


select * from user where age =11；

理由：

  • 虽然age加了索引，但是因为对它进行运算，索引直接迷路了。。。

9、Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

  • Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
  • left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
  • right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

反例:


select * from tab1 t1 left join tab2 t2  on t1.size = t2.size where t1.id>2;

正例：


select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

理由：

  • 如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。
  • 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

10、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

反例：

复制代码select age,name  from user where age <>18;

正例：


//可以考虑分开两条sql写
select age,name  from user where age <18;
select age,name  from user where age >18;

理由：

  • 使用!=和<>很可能会让索引失效

11、使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

表结构：（有一个联合索引idx_userid_age，userId在前，age在后）


CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

反例：


select * from user where age = 10;

正例：


//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;

理由：

  • 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
  • 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

12、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描。

反例：


select * from user where address ='深圳' order by age ;

正例：


添加索引
alter table user add index idx_address_age (address,age)

13、如果插入数据过多，考虑批量插入。

反例：


for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：


//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：

  • 批量插入性能好，更加省时间

打个比喻:假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放500）,你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?

14、在适当的时候，使用覆盖索引。

覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

反例：

// like模糊查询，不走索引了
select * from user where userid like '%123%'

正例：

//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like '%123%';

15、慎用distinct关键字

distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

反例：

SELECT DISTINCT * from  user;

正例：

select DISTINCT name from user;

理由：

  • 带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。

16、删除冗余和重复索引

反例：

  KEY `idx_userId` (`userId`)  
  KEY `idx_userId_age` (`userId`,`age`)

正例:

  //删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
  KEY `idx_userId_age` (`userId`,`age`)

理由：

  • 重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的。

17、如果数据量较大，优化你的修改/删除语句。

避免同时修改或删除过多数据，因为会造成cpu利用率过高，从而影响别人对数据库的访问。

反例：

//一次删除10万或者100万+？
delete from user where id <100000;
//或者采用单一循环操作，效率低，时间漫长
for（User user：list）{
   delete from user； 
}

正例：

//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000；

理由：

  • 一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

18、where子句中考虑使用默认值代替null。

反例：

select * from user where age is not null;

正例：

//设置0为默认值
select * from user where age>0;

理由：

  • 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关。

如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，>is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的。

  • 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

19、不要有超过5个以上的表连接

  • 连表越多，编译的时间和开销也就越大。
  • 把连接表拆开成较小的几个执行，可读性更高。
  • 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

20、exist & in的合理利用

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

select * from A where deptId in (select deptId from B);

这样写等价于：

先查询部门表B

select deptId from B

再由部门deptId，查询A的员工

select * from A where A.deptId = B.deptId

可以抽象成这样的一个循环：

   List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：

select * from A where exists (select 1 from B where A.deptId = B.deptId);

因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。

那么，这样写就等价于：

select * from A,先从A表做循环

select * from B where A.deptId = B.deptId,再从B表做循环.

同理，可以抽象成这样一个循环：

   List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。即mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。

21、尽量用 union all 替换 union

如果检索结果中不会有重复的记录，推荐union all 替换 union。

反例：

select * from user where userid=1 
union  
select * from user where age = 10

正例：

select * from user where userid=1 
union all  
select * from user where age = 10

理由：

  • 如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

22、索引不宜太多，一般5个以内。

  • 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。
  • insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。
  • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

23、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

反例：

king_id` varchar（20） NOT NULL COMMENT '守护者Id'

正例：

`king_id` int(11) NOT NULL COMMENT '守护者Id'`

理由：

  • 相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。

24、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

25、尽量避免向客户端返回过多数据量。

假设业务需求是，用户请求查看自己最近一年观看过的直播数据。

反例：

//一次性查询所有数据回来
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)

正例：

//分页查询
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize

//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;

26、当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰。

反例：

select  * from A inner
join B on A.deptId = B.deptId;

正例：

select  memeber.name,deptment.deptName from A member inner
join B deptment on member.deptId = deptment.deptId;

27、尽可能使用varchar/nvarchar 代替 char/nchar。

反例：

  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'

正例：

  `deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：

  • 因为首先变长字段存储空间小，可以节省存储空间。
  • 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

28、为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

反例：

select job，avg（salary） from employee  group by job having job ='president' 
or job = 'managent'

正例：

复制代码
select job，avg（salary） from employee where job ='president' 
or job = 'managent' group by job；

29、如何字段类型是字符串，where时一定用引号括起来，否则索引失效

反例：

select * from user where userid =123;

正例：

select * from user where userid ='123';

理由：

  • 为什么第一条语句未加单引号就不走索引了呢？ 这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

30、使用explain 分析你SQL的计划

日常开发写SQL的时候，尽量养成一个习惯吧。用explain分析一下你写的SQL，尤其是走不走索引这一块。

explain select * from user where userid =10086 or age =18;

avatar
文章
阅读
粉丝