Skip to content

Files

Latest commit

chaseSpacechaseSpace
++
Jul 7, 2024
1fe6f87 · Jul 7, 2024

History

History
727 lines (491 loc) · 34.5 KB

db_mysql.md

File metadata and controls

727 lines (491 loc) · 34.5 KB

MySQL 相关

本文内容部分摘自网络。

什么是关系型数据库

一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。 大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

常见的关系型数据库

MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite）。

数据库三范式

数据库设计中的三范式（3NF，Third Normal Form）是关系数据库中的一种规范化方法，用于消除数据冗余和提高数据一致性。

  • 第一范式 (1NF)：确保表中每列的原子性，也就是不可拆分。
  • 第二范式 (2NF)：确保数据库表中的每一列都和主键相关。对于联合主键，也必须与联合主键的全部字段相关。
  • 第三范式 (3NF)：确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
    • 换句话说，非主键字段之间不能直接相关，而都是通过主键关联。

字段类型相关

MySQL 字段类型可以简单分为三大类：

  • 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
  • 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么

DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DATETIME 和 TIMESTAMP 的区别是什么

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样造成了一个问题，Timestamp 表示的时间范围更小。

  • DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

为什么不建议使用 NULL 作为列默认值

  • NULL 与空字符串不同，NULL 需要更多的存储空间。
  • 查询 NULL 值需要使用专门的 SQL 语句，比如 IS NULL和IS NOT NULL，而查询空字符串只需要使用 = 或者 <> 即可。
  • NULL 会影响聚合函数的查询结果，例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。
    • COUNT(*) 会包含 NULL 值所在的行，但 COUNT(col) 不会。
  • 查询不便：在查询中使用NOT IN或!=等反向条件时，查询结果不会包含 NULL 值所在的行，需要加上ISNULL(col)。

注意：DISTINCT 会将多个 NULL 值算作一个 NULL。

对于不需要做聚合的字段，可以允许 NULL 值。

存储引擎

有哪些存储引擎

Innodb 和 MyISAM。使用SHOW ENGINES查看支持的引擎列表。

InnoDB 和 MyISAM 对比

  • 锁支持：MyISAM 是表级锁，InnoDB 支持表级锁和行级锁。
  • 事务支持：只有 InnoDB 支持事务。
  • 外键支持：只有 InnoDB 支持外键，但一般不用。
  • 崩溃恢复：只有 InnoDB 支持。数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log。
  • MVCC 支持：只有 InnoDB 支持。这是个废话，因为支持事务才支持 MVCC。多版本并发控制，是一种用于实现数据库事务并发控制的技术，可以减少加锁操作，提高性能。
  • 索引实现：都使用 B+Tree 作为索引结构，但实现方式不同。Innodb 实现为聚簇索引，即主键索引和数据存储在一起；MyISAM 实现为非聚簇索引，即主键索引和数据分开存储。
    • 聚簇索引的叶子节点存储了数据，非聚簇索引的叶子节点存储了数据指针。二级索引的实现没有差别。
  • 设置主键：Innodb 一定拥有一个主键，MyISAM 可以没有主键。
  • 性能差别：InnoDB 的性能比 MyISAM 更强大。

MySQL 架构分层

MySQL 的架构设计分为多个层次，每个层次负责不同的功能和任务。

  • 客户端层：负责与 MySQL 服务器进行通信，主要功能是处理客户端连接。
  • 服务层：由多个组件组成。
    • 连接器：管理客户端的连接和用户身份认证和鉴权。
    • 缓存层：尝试查询缓存，如果命中缓存，则直接返回结果，否则再查询数据库。当发生写操作时，缓存会失效。
      • 由于频繁变更的数据表，命中率很低，反而导致读写速度，MySQL 8.0 移除缓存层（需验证！）。
    • 分析器：负责解析 SQL 语句，检查语法错误，并将其分解成解析树。
    • 优化器：对解析树进行优化，生成一个或多个执行计划，选择最有效的查询执行计划。
    • 执行器：调用存储引擎接口，真正的执行 SQL 查询。
  • 存储引擎层：可插拔式实现，负责数据的存取，提供统一抽象接口与服务层进行交互。
  • 文件系统层：严格来说不属于 MySQL 内部层，但为了方便理解，将它放在这里。
    • 负责管理数据的物理存储，包括表数据、索引、各种日志文件等。
      • 表文件：使用单文件或每表一个文件的形式保存表数据。包含用户表、系统表、临时表等。
      • 索引文件：包含索引数据。
      • 日志文件：分别有 redo-log、undo-log、binlog，以及慢查询、通用查询和错误日志文件。
      • 配置文件：my.cnf文件。

索引

索引是一种用于快速查询和检索数据的数据结构，本质上是一种经过排序的数据结构。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

优缺点

优点：

  • 显著提高查询速度。
  • 加速排序和分组操作。
  • 强制数据唯一性，确保数据完整性。

缺点：

  • 占用额外的存储空间。
  • 插入、更新和删除操作可能变慢，因为索引也需要维护。
  • 过多的索引可能导致查询优化器选择不当，反而降低查询性能。

索引类型

MySQL 中包含以下索引类型：

  • 主键索引：唯一且不能为 NULL 的索引。
  • 唯一索引：唯一索引，不能有重复的值。
  • 普通索引：普通索引，可以有重复的值。
  • 全文索引：全文索引，用于全文检索。
  • 组合索引：组合索引，可以同时使用多个字段进行索引。
    • 我们在创建组合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

原理

MySQL 底层使用 B+树实现主键索引、二级索引。B+树中，所有数据都存放在叶子节点中，而非叶子节点中存放的是索引值。所有叶节点通过链表相连，便于范围查询。

主键索引

一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。Innodb 的主键索引又叫聚簇索引，含义是数据行也存储在索引结构中。而 MyISAM 的主键索引则是非聚簇索引，索引结构中仅包含索引值。

二级索引

又叫辅助索引，包含唯一/普通/组合/全文索引，一般将非主键索引都叫做二级索引。索引结构中不含数据行，实际查询时需要通过二级索引查询到的数据主键值，再次回到主键索引中查询数据。这个过程叫做 回表。

为什么选 B+树而不是 B 树

B 树也称 B- 树,全称为多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

二者最大区别是 B 树的非叶节点也存储了数据行，且叶子节点之间独立；而 B+树的非叶节点只放索引键，只有叶子节点存放数据行，且叶子节点之间是通过链表连接的。

B+树的优点

  • 更高的空间利用率：非叶节点不存数据，可以保存更多的索引键，这使得整棵树的高度降低，减少查询过程中的 IO 次数。
  • 查询路径固定：数据都在叶子节点，因此所有数据访问路径固定为从根节点到叶子节点，查找过程更统一，易于优化和实现。
  • 方便范围查询和全表查询：B+树的叶子节点是有序的，并且通过链表链接，而 B 树则要对树做中序遍历。

什么是最左匹配原则

最左匹配原则指的是数据库在使用复合索引时，会优先考虑索引的最左前缀。只有查询条件中包含了索引的最左列（或最左几列）时，索引才能被利用。

一次查询最多使用几个索引

一张表最多使用一个索引，优化器会选择一个最合适的索引。对于多表连接（JOIN）查询时，最多使用的索引数量等于表数量。

什么是索引下推

索引下推（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的一项二级索引查询优化技术，旨在通过减少访问表数据的次数 来提高查询性能。它的基本思想是将部分 WHERE 子句的条件尽量下推到存储引擎层，在存储引擎扫描索引时尽可能多地过滤掉不符合条件的记录， 从而减少传递到 Server 层的数据量。

示例：

-- 联合索引（name,id）
select *
from t
where name = 'a'
  and xid > 2;

没有索引下推时

存储引擎只负责使用索引列检索符合等值条件的数据，而 WHERE 子句中的非等值条件由 Server 层处理。示例中，引擎层不会对xid 字段进行过滤。

有索引下推时

存储引擎在检索数据时会考虑非等值条件，这样可以尽量减少传递到 Server 层的数据量，提高查询性能。

使用索引下推的条件

  • 必须是利用二级索引。
  • Where 条件是对索引列进行范围查询/NULL 查询。

如何验证使用了索引下推

在 Explain 语句查询结果的 Extra 列中，如果包含Using index condition，则表示使用了索引下推。

什么是覆盖索引

如果 SQL 选择的索引覆盖了所有涉及的字段，我们就称之为这个 SQL 使用了覆盖索引。比如 Where 条件列都被索引覆盖，但 SELECT 部分未被索引覆盖， 则需要回表查询，增加了 I/O 次数。当然，还包括排序、分组、去重等关键字。

我们可以利用覆盖索引原理，为需要频繁查询/排序/分组等操作的列建立联合索引，以此来减少回表查询次数，提高查询性能。

如何验证

通过 Explain 查询结果中的 Extra 这一列的 Using index ，可以验证成功使用了覆盖索引。

创建索引的建议

  • 选择合适的字段创建索引：
    • 不为 NULL 的字段：对于数据为 NULL 的字段，数据库较难优化。
    • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
    • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
    • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
    • 被经常频繁用于连接的字段。
    • 被频繁更新的字段应该慎重建立索引。
  • 使用复合索引：对于经常一起使用的多个列，考虑创建复合索引。复合索引可以减少索引的数量，并且在多列条件下更高效。
  • 限制每张表上的索引数量：较多的索引会影响写入性能。
  • 注意避免冗余索引：避免建立可能被同时命中的索引。

什么是索引合并

索引合并（Index Merge）是 MySQL 引擎的一项优化技术，它允许查询在某些条件下同时利用多个单列索引或一个多列索引（符合最左匹配原则），从而提高查询性能。 索引合并技术主要在 MySQL 5.0 及以后版本中得到支持，并且可以在某些复杂查询中显著提高查询效率。

索引合并技术有三种算法：

  • INTERSECT：对多个二级索引里符合条件的主键值取交集合并，应用于 OR 两边为等值条件。
  • UNION：对多个二级索引里符合条件的主键值去重后取并集合并，应用于 AND 两边为等值条件。
  • SORT_UNION：对多个二级索引里符合条件的主键值去重并排序后，再取并集合并，应用于 OR 一边是等值条件，另一边是范围条件。
    • 示例：where a=1 OR b>='Z'，查询过程：
      • 先从idx_b索引中取出所有符合条件记录，提取 id 集合后排序，记作id_setB；
      • 此时id_setB已经是有序的了，从idx_a中依次取出记录的 id 值，再走取并集过程。

不管哪种算法，都要求二级索引取出的记录是主键排序的，这样才能最快地实现合并和交集操作。 前两种算法由于是等值查询，所以取出的索引记录都是已经排序的，不需要额外排序。而 SORT_UNION 算法由于需要对二级索引进行 范围查询，所以取出的索引记录不一定是主键排序的，需要额外排序，所以叫做 SORT_UNION。

Note

没有 SORT_INTERSECT，是因为对where a > 1 and b > 'Z'的查询，两个子集都可能较大， 由于取交集前需要先对记录按主键排序，所以对两个子集的排序操作可能本身就非常耗时，不如执行全表扫描。

事务使用

介绍

事务是并发控制的基本单位。事务主要保证一组连续 SQL 操作的数据完整性和一致性。

事务一般满足四个条件：原子性、一致性、隔离性、持久性。

  • 原子性：事务中的所有操作要么全部执行，要么全部不执行。
  • 一致性：事务执行之前和执行之后，数据库的完整性没有改变。
  • 隔离性：事务之间不能互相干扰（受到隔离级别影响！！！）。
  • 持久性：事务执行之后，对数据库的修改是永久的。

事务允许用户设置隔离级别，来根据场景控制事务的并发性能，不同的隔离级别适用于不同场景：

  • READ UNCOMMITTED：最低的隔离级别，允许脏读，不可重复读和幻读。
  • READ COMMITTED：禁止脏读，允许不可重复读和幻读。
  • REPEATABLE READ（默认）：禁止脏读和不可重复读，允许幻读。
  • SERIALIZABLE：最高隔离级别，禁止脏读、不可重复读和幻读。

事务控制语句

BEGIN 或 START TRANSACTION -- 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK -- 提交事务，二者是等价；
ROLLBACK 也可以使用 ROLLBACK WORK -- 回滚事务，二者是等价；
SAVEPOINT identifier -- 创建一个保存点，用于回滚事务；
RELEASE SAVEPOINT identifier -- 删除一个事务的保存点；
ROLLBACK TO identifier -- 把事务回滚到标记点；

查询自动提交开关

开启自动提交后，除非使用BEGIN或START TRANSACTION显式开启事务，否则每个 SQL 都自动在一个事务中完成。 带来的直观影响就是，加锁的 SQL 执行后会立即释放锁。

SHOW
VARIABLES LIKE '%autocommit%' -- 默认开;
SET AUTOCOMMIT=0 -- 关闭自动提交，1开启；

查询隔离级别

-- 查看当前、全局事务的隔离级别，仅适用于8.0以下版本；
SELECT @@tx_isolation;
SELECT @@global.tx_isolation;

-- 5.7及以上版本
SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;

修改隔离级别

-- isolation_level 替换为具体隔离级别，包括 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
SET
{SESSION | GLOBAL} TRANSACTION ISOLATION LEVEL isolation_level;

-- example
SET
GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

锁机制

锁机制可以在事务并发时保证数据的一致性和完整性。关于锁机制的详细介绍，请参考：MySQL 锁机制详解。

锁类型

  • 共享锁（S 锁，Shared Lock）：读锁，允许多个事务同时读，但不允许并发读写（包括获取共享锁的事务）。
  • 排他锁（X 锁，Exclusive Lock）：粒度最大，与其他任何锁互斥，完全占有行，当前事务可以读写，其他事务同一时刻不能读写该行。
    • 例如，INSERT、UPDATE、DELETE 语句会自动使用排他锁，所以说修改数据的单条 SQL 具有原子性。
  • 意向锁（Intent Locks）：表示事务将要对表中的某些行加锁，主要作用是为了让行级锁和表级锁之间能够协同工作。
    • 仅 Innodb 支持。
    • 意向共享锁（IS 锁）：当一个事务打算在某些行上加共享锁时，它会先在表上加一个意向共享锁。
    • 意向排他锁（IX 锁）：当一个事务打算在某些行上加排他锁时，它会先在表上加一个意向排他锁。
    • 自动管理：意向锁由存储引擎自动管理，不能手动获取。
    • 在为数据行加共享锁 / 排他锁之前，InnoDB 会先获取该表的意向共享/排他锁。

锁粒度

  • 表级锁：锁定整张表，适用于需要对整张表进行操作的场景。
    • 比如在执行某些 DDL 操作（如 ALTER TABLE）时会使用表级锁。
    • Innodb 和 MyISAM 支持。
    • IS 锁、IX 锁和 AUTO-INC 锁都是表级锁。
    • 显式使用：LOCK TABLE [tbl_name {READ|WRITE} ...]
  • 行级锁：锁定单行数据，可以允许其他事务访问不同的行，适用于高并发的应用场景。
    • InnoDB 支持，是默认锁级别。
    • 记录锁、间隙锁、临键锁和插入意向锁都是行级锁。
  • 页级锁：锁定整页数据，适用于对大量数据进行操作的场景。

锁思想

  • 悲观锁：先获取锁，再执行操作。
    • 可通过 X 锁或 S 锁实现。
  • 乐观锁：先执行操作，提交时检查数据是否被其他事务更改，否就提交成功，是就重试整个事务。
    • 通过版本号或时间戳来实现，无需锁。

锁等待和超时

  • 锁等待：当一个事务需要获取一个已经被其他事务占用的资源时，就会发生锁等待。
  • 锁超时：当一个事务在等待获取锁时，如果超过了一定的时间，就会发生锁超时。
    • 查看会话锁超时：show variables like 'innodb_lock_wait_timeout';
    • 查看全局锁超时：show global variables like 'innodb_lock_wait_timeout';
    • 设置：set [global|session] innodb_lock_wait_timeout=30，默认 50，单位秒。
    • 超时错误：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

死锁

MySQL 死锁（deadlock）是指在数据库中两个或多个事务因为资源竞争而互相等待对方释放锁，导致这些事务无法继续执行的现象，直到事务超时。

必要条件

要形成死锁，必要满足下面几个必要条件。

  • 多个事务并发，并且针对相同数据表。
  • 存在 X 锁：事务需要获取 X 锁和其他锁，因为 X 锁和其他锁都不兼容。
  • 等待环路：这是重点。事务之间互相等待对方释放对应的锁，形成了一个锁等待环路，直到超时（默认 50s）。

事务中的读取问题

在事务并发时，可能出现读取脏读、不可重复读和幻读的问题。

脏读

脏读发生在一个事务读取了另一个未提交事务所修改的数据。如果该修改事务回滚，那么读取到的数据就是无效的或“脏”的。

  • 🔨：脏读在 读未提交（READ UNCOMMITTED） 隔离级别下会发生，在更高的隔离级别下不会发生。

不可重复读

不可重复读是指在一个事务内，两次读取同一行数据却得到了不同的结果，通常是因为在两次读取之间，另一个事务修改了这行数据并提交了。

  • 🔨：不可重复读在 读已提交（READ COMMITTED） 及以下隔离级别可能会发生，在更高的隔离级别下不会发生。

幻读

幻读指在一个事务内，两次执行相同的查询却得到了不同的结果集，通常是因为在两次查询之间，另一个事务插入了符合查询条件的新记录。

  • 🔨：幻读在 可重复读（REPEATABLE READ） 及以下隔离级别可能会发生，但在串行化（SERIALIZABLE）隔离级别下不会发生。 另一种常用方式是给记录加上排他锁（实际使用间隙锁）。

快照读

快照读（Snapshot Read）是 MySQL InnoDB 存储引擎中用来实现一致性非锁定读的一种技术。默认的SELECT语句都是快照读，所读取的数据可能是过期的。 由于快照读不加锁，所以不会阻塞其他写操作，提高系统并发读写性能。如果对SELECT语句显式加锁，就成为当前读。

大致原理

快照读基于 MVCC 技术实现。事务开始时，Innodb 会创建一个一致性视图（Consistent Read View），这个视图记录了当前活跃事务的状态和事务 ID 列表。 事务在读取数据时，基于这个视图来决定允许读取哪些版本的数据。

不同隔离级别的快照读情况

  • SR 级别：不需要创建一致性视图，因为事务本身都是串行执行；
  • RR 级别：在事务中第一个读操作执行时创建一致性视图，事务内的后续操作都读取这个视图下的数据；
  • RC 级别：在事务中每次读操作执行时创建一致性视图，确保每次都能读到最新的已提交数据；
  • RU 级别：不需要创建一致性视图，每次都读取最新数据；

当前读

对比快照读，当前读是指对 SQL 加锁后的读取，这个读取包含增删改查，其中查是显式加锁的。当前读读取的是最新已提交版本的数据，并且对数据加锁， 阻塞其他事务需要同时修改的操作。

事务原理

MySQL 事务用到的技术包含日志文件（redo-log 和 undo-log）、锁和 MVCC，通过这些技术来实现 ACID 特性。

MVCC

todo

锁

请阅读笔者的专篇文章 锁原理。

redo-log

undo-log

Explain 优化

Extra 列

Extra 列显示了 MySQL 在执行查询时采取的一些额外操作或优化策略。

Using index

表示 SQL 语句中的查询、排序和分组等关键字涉及到的列都能被所选择的索引覆盖。

Using where

表示存储引擎返回结果后，Server 层需要再次筛选。这通常是因为 WHERE 条件中包含了无法使用索引的列。

Using filesort

表示 SQL 语句中包含了排序需求，并且排序字段没有被索引覆盖。这种情况下只能由引擎层返回未排序的索引记录给 Server 层， 后者再调用引擎层接口查询完整记录，再排序，增加了与引擎交互次数。

它与Using index 是互斥的，不会同时出现。

Using temporary

意味着 MySQL 在执行查询时需要创建一个临时表来存储中间结果。

这通常发生以下几种情况：

  • 首先在查询包含 GROUP BY 子句，其次 SQL 包含了ORDER BY子句，但排序字段没有出现在分组字段中；
    • 另一种情况是 SELECT 字段也不在分组字段中。

示例 1：

-- SQL中的排序字段不在分组字段中，需要临时表来存储分组后的结果，再对其进行排序。
SELECT product_id as total_amount
FROM sales
GROUP BY product_id
ORDER BY sale_date;

注意，mysql v5.7 以上默认sql_mode=ONLY_FULL_GROUP_BY，即不允许 SQL 出现上面这种情况，所以需要手动修改sql_mode，修改方式如下：

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';


# 修改配置文件 my.ini
[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Using index condition

查询优化

Mysql 查询性能优化要从三个方面考虑，库表结构优化、索引优化和查询优化。

库表结构优化

  • 使用合适的数据类型：可以减少存储空间和提高查询效率。例如，尽量使用 TINYINT、SMALLINT 代替 INT，使用 VARCHAR 而不是 TEXT。
  • 避免使用 NULL 值： 因为它们会占用额外的空间，并且在索引和查询优化时可能会引起问题。
  • 分区表：对于大表，可以使用表分区（Partitioning）将表按某一列（如时间、ID 等）分成多个物理块，提高查询效率。
  • 垂直拆分：将表中的列拆分到多个表中，减少单个表的列数。
  • 水平拆分：将表中的行拆分到多个表中，减少单个表的数据量。

索引优化

  • 创建适当索引：对于经常用于查询、分组、排序和连接的列，创建索引。
  • 覆盖索引： 尽量使用覆盖索引，即查询所需的所有列都包含在索引中，避免回表查询。
  • 联合索引：对多列组合查询条件，创建联合索引。要注意列的顺序，遵循最左前缀原则。
  • 索引选择性： 索引列的选择性（唯一值的比例）越高，索引效果越好。重复数据过多会导致查询时全表扫描。
  • 避免冗余索引和重复索引：冗余索引和重复索引会增加维护成本和存储空间，应定期检查和删除。
  • 避免索引过多：经验说法是单表索引数量不超过 6 个。否则明显降低增删改的性能。
  • 不要更新聚集索引数据列：索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
    • 一般指的是主键列。

查询优化

  • 优化查询条件：where 条件过滤数据多的放在前面，减少产生的临时表的数据量。
  • 尽量避免select *，填写具体需要的字段。
  • 避免隐式转换：将不同类型进行比较或运算时会发现。不仅会导致查询不准确，还可能导致索引失效。
  • 合理使用比较符：
    • 尽量避免使用 !=, <> 和 null 值查询。
    • 对于 or/in/not，也要谨慎使用，也可能导致全表扫描。其中，or 确保两边字段使用索引；in 可以考虑 between。
    • like 查询确保通配符不在开头，比如%xx无法使用索引。
    • 避免使用正则查询。
  • 子句中使用变量时，也会全表扫描。
    • 因为 SQL 只有在运行时才会解析局部变量，可以改为强制查询使用索引： select id from t FORCE INDEX(索引名) where num=@num。
    • FORCE INDEX也可以用在 Explain 发现优化器选择了不合适的索引时手动选择效率最高的索引。
  • 避免在 where 子句中对字段进行表达式操作和函数操作，会导致全表扫描。
    • 表达式示例：Where price * 1.1 > 100
    • 函数示例：Where YEAR(time) > 2020
  • 尽量避免向客户端返回大数据量：先考虑分页查询，再考虑需求是否合理。

一条查询最多使用几个索引

答案是不确定。首先一张表中就可能通过 index merge（索引合并）技术使用多个索引，这发生在使用 AND、OR、自连接的情况。 索引合并指的是对表中的多个索引分别进行条件扫描，然后将它们各自的结果进行合并（INTERSECT/UNION），这是 MySQL 5.0 后引入的。

所以在一个查询语句中，单表查询使用索引的数量是不确定的，更不用说多表连接的情况。

SQL 语句的执行过程

连接器： 身份认证和权限相关(登录 MySQL 的时候)。 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，进行词法分析和语法分析，生成语法树；同时检查对应的表和字段是否存在。 优化器： 负责将语法树转化成执行计划。包括选择不同的索引、决定是否使用子查询或连接操作。 执行器： 由存储引擎执行语句，然后返回数据。

什么是 PrepareStatement

通常一条 SQL 发送到 DB 后，会大致经过以下步骤：

  • 词法和语义解析
  • 优化 sql 语句，制定执行计划
  • 执行并返回结果

其中前面两步是每次都要执行的，简称为编译阶段。所以对于执行大量重复的 SQL 很不友好，所以有了 PrepareStatement。 MySQL 支持提前设置一条预编译 SQL，后续可以直接执行预编译 SQL（仅传入参数），从而节省了编译时间。

示例

prepare ins from 'insert into t1 values (?, ?, ?)';

set @a = 1,@b = 1,@c = 1;
execute ins using @a,@b,@c;

# 释放预编译语句
deallocate prepare ins;

注意：MySQL 中的预编译语句作用域是 session 级，不能设置全局性的预编译语句。

Note

可以通过set @@global.max_prepared_stmt_count=1设置全局最大预编译语句数量，v5.7 默认值 16382。

更安全

由于 SQL 已经被预编译在 MySQL 中，后续传入的参数即使包含 SQL 关键字也只会被当做纯数据处理，因此避免了 SQL 注入攻击。 SQL 之所以能被注入，最主要的原因就是它在以常规方式执行时，数据和代码（指令）是混合的。

为什么不建议单表超过 2000w 数据

回答这个问题首先要弄清楚 MySQL 的查询过程，因为不建议的原因是超过 2000w 数据量会导致查询性能迅速降低。

简单的回答：单表超过 2000w 后，会引起索引和数据存储结构 B+tree 的高度增加，延长了索引的搜索路径，增加了磁盘 IO 次数，进而导致了性能下降。

索引结构

MySQL 采用 B+树结构来存储数据和索引，索引分为聚簇索引和二级索引。其中聚簇索引也叫主键索引，它的非叶节点存储主键值和页号映射关系，而叶节点存储的是完整的行数据。 二级索引的非叶节点存储的是索引值和页号映射关系，而叶节点存储的是主键 ID。若使用二级索引查找数据，最坏的情况下，在找到索引值后，还会根据主键 ID 在聚簇索引中查找数据（简称回表）。

查找过程

由于 MySQL 的数据或索引物理文件内部都是按页组织的，每个页里面才是存储的最终数据。所以索引树种存储的是索引值和页号映射关系，MySQL 在找到匹配条件的索引值后， 还需要根据页号继续查找数据，而每次查页都是一次磁盘 IO。当然，如果为 DB 分配的内存够多，刚好这些页都在内存中，那么就不会免去这部分磁盘 IO，但我们暂不考虑这个情况。

假设索引树高度为 3，那么几乎每次查找数据，都会需要 3 次磁盘 IO，因为有页缓存机制，所以我们感知不明显。

根据页属性计算 2000w 需要的索引树高度

MySQL 默认是 16K 的页面，抛开它的配置 header，大概剩下 15K。非叶节点存储的是主键和页号（主键 8byte+页号固定 4byte=12byte）， 因此，非叶子节点的单个索引页面可放 15*1024/12=1280 条数据；叶子节点存储行数据（算作 1K），因此，叶子节点的单个索引页面可放 15 条数据。

已知 B+树的总数据行数与树高度存在以下关系：

  • 非叶子节点内指向其他页的数量为 x
  • 叶子节点内能容纳的数据行数为 y
  • B+ 数的高度为 z

有公式：total=x^(z-1) *y，所以，三层的 B+树能容纳：(1280^2)*15=24576000 条数据。即超过这个数据量，索引树的高度会增加到 4。

当单行数据大小为 5k 时（大宽表），三层 B+树只能容纳不到 500w 条数据。所以，在保持相同的层级（相似查询性能）的情况下，在行数据大小不同的情况下， 其实这个最大建议值也是不同的。

评估查询性能

在使用以前的机械磁盘时，一次磁盘 IO 随机读取时间是 10ms 左右，若是 SSD，则会提高到 100 微秒的级别。所以在 3 层 B+树高度， 数据行不大（如 1k）时，一次单表索引查询的耗时最多也就 30ms 左右，在利用缓存时提升至 10ms 内。只是说，在进行多表或回表查询时，这个耗时会明显增加，可能到上百毫秒； 无索引时，性能会明显下降到秒级别。

其实这种性能在大部分的业务中都是可以接受的。

评估写入性能

我们不能重视查询而忽略了写入性能，因为写入性能也是许多业务数据库的关键。数据写入可能会导致 B+树的节点分裂或合并， 在这种时候，Innodb 采用乐观锁机制控制并发写入。当数据量较多时，可能会触发频繁的索引结构变更，从而导致写入性能下降。

常用 SQL

-- 查看版本
SELECT VERSION();

-- 查看当前连接ID
SELECT CONNECTION_ID();

-- 查看支持引擎列表
SHOW
ENGINES; -- v5.5.5之后innodb成为默认引擎，且只有它支持事务
    
-- 查看默认引擎
SHOW
VARIABLES  LIKE '%storage_engine%';

优化和检查表

-- 检查表的完整性
CHECK TABLE table_name;

-- 修复表
REPAIR
TABLE table_name;

-- 分析表，用于更新表的统计信息
ANALYZE
TABLE table_name;

-- 优化表
OPTIMIZE
TABLE table_name;

备份和恢复

-- 使用mysqldump工具备份数据库
mysqldump
-u username -p database_name > backup_file.sql

-- 从备份文件恢复数据库
mysql -u username -p database_name < backup_file.sql

或使用 GUI 工具。

查看表和索引信息

-- 查看表结构
DESCRIBE table_name;

-- 等同于 DESCRIBE ~
SHOW
COLUMNS FROM table_name;

-- 查看索引信息
SHOW
INDEX FROM table_name;

监控和性能调优

-- 查看当前正在执行的查询，不加full只显示前100条。非root用户只看到自己占用的连接
-- 命令详解：https://juejin.cn/post/6856958149027774477
SHOW
[FULL] PROCESSLIST;

-- 查看服务器概况信息，含当前连接信息、用户、服务器版本、client&server字符集、开启线程数、慢查询数、打开表数量、QPS等
STATUS;

-- 获取数据库状态变量信息（只读），GLOBAL关键字仅查看全局状态变量
SHOW
[SESSION|GLOBAL] STATUS;

-- 获取数据库变量信息
SHOW
[SESSION|GLOBAL] VARIABLES;
    
-- 修改变量
SET
[SESSION|GLOBAL] variable_name = value;

参考