Dev

Database Mysql

数据库相关总结

总结了遇到了数据库相关的知识。

索引

MySQL 索引是提高数据库查询性能的重要工具。索引类似于书的目录，可以帮助数据库快速找到所需的数据，而不必遍历整个表。以下是有关 MySQL 索引的一些关键点和常见类型：

索引的作用

  1. 加速查询：通过索引，MySQL 可以快速定位到所需的数据行，从而减少查询时间
  2. 确保唯一性：唯一索引（Unique Index）可以确保表中的某一列的所有值都是唯一的
  3. 优化排序和分组：索引可以优化 ORDER BY 和 GROUP BY 操作。
  4. 提高连接性能：在多表连接时，索引用于加速表之间的匹配。

索引的类型

  1. 普通索引（Index）：最基本的索引类型，没有任何限制

    1

    CREATE INDEX idx_name ON table_name(column_name);

  2. 唯一索引（Unique Index）：确保索引列中的值是唯一的。

    1

    CREATE UNIQUE INDEX idx_name ON table_name(column_name);

  3. 主键索引（Primary Key）：一种特殊的唯一索引，不允许有空值。

    1
2
3
4
5

    CREATE TABLE table_name (
 id INT NOT NULL,
 column_name datatype,
 PRIMARY KEY (id)
);

  4. 全文索引（Full-Text Index）：用于对文本进行全文搜索。

    1

    CREATE FULLTEXT INDEX idx_name ON table_name(column_name);

  5. 复合索引（Composite Index）：在多个列上创建的索引。

    1

    CREATE INDEX idx_name ON table_name(column1, column2);

索引的创建和管理

  1. 创建索引：使用 CREATE INDEX 或在 CREATE TABLE 语句中定义。

    1

    CREATE INDEX idx_name ON table_name(column_name);

  2. 删除索引：使用 DROP INDEX 语句。

    1

    DROP INDEX idx_name ON table_name;

  3. 查看索引：使用 SHOW INDEX 语句。

    1

    SHOW INDEX FROM table_name;

索引的优缺点

优点：

  • 显著提高查询速度。
  • 帮助维护数据的唯一性。
  • 加快数据检索的效率。

缺点：

  • 占用磁盘空间。
  • 影响插入、更新和删除操作的速度（因为需要维护索引）。

索引的最佳实践

  1. 选择性高的列上创建索引：选择性越高，索引的效率越高。
  2. 尽量避免在频繁更新的列上创建索引：减少索引维护开销。
  3. 使用覆盖索引：创建包含所有查询列的复合索引，避免回表查询。
  4. 定期维护索引：使用 ANALYZE TABLE 和 OPTIMIZE TABLE 进行索引维护。

创建索引的原则

  1. 最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a, b, c, d)顺序的索引，d 是用不到索引的，如果建立(a, b, d, c)的索引则都可以用到，a, b, d 的顺序可以任意调整。
  2. =和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a, b, c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。
  3. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。
  4. 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = '2014-05-29' 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp('2014-05-29')。
  5. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a, b)的索引，那么只需要修改原来的索引即可。

索引失效

在某些情况下，MySQL 的索引可能会失效，导致查询性能下降。以下是一些常见的索引失效的情况：

条件不符合索引使用规则

  1. 范围查询影响后续索引：

    • 当一个查询中使用了范围条件（如 <, >, BETWEEN, LIKE 'abc%' 等）时，索引在范围条件之后的列将不会被使用。
    1
2
3

     -- 复合索引 idx_name_department(last_name, department_id)
 -- 只会使用 last_name 的索引，department_id 不会被使用
 SELECT * FROM employees WHERE last_name > 'Smith' AND department_id = 5;

  2. 函数操作导致索引失效：

    • 如果在查询条件中对索引列使用了函数或运算，索引会失效。
    1
2

     -- 索引不会被使用
 SELECT * FROM employees WHERE LEFT(last_name, 3) = 'Smi';

  3. 数据类型不一致：

    • 查询条件中的数据类型与索引列的数据类型不一致时，索引可能失效。
    1
2

     -- 索引可能失效，因为 '123' 是字符串，而 id 是整数
 SELECT * FROM employees WHERE id = '123';

查询条件的组合问题

  1. OR 条件影响索引使用：

    • 当查询中使用 OR 条件时，如果 OR 条件中的每个部分都没有单独使用索引，则整个查询不会使用索引。
    1
2

     -- 索引不会被使用，除非 last_name 和 department_id 上都有单独的索引
 SELECT * FROM employees WHERE last_name = 'Smith' OR department_id = 5;

  2. 不符合最左前缀原则：

    • 对于复合索引，查询条件必须包含索引的最左前缀，否则索引失效。
    1
2
3

     -- 复合索引 idx_name_department(last_name, department_id)
 -- department_id 列不会单独使用索引
 SELECT * FROM employees WHERE department_id = 5;

索引选择策略和优化器影响

  1. 小表全表扫描：

    • 对于数据量很小的表，MySQL 优化器可能选择全表扫描，而不是使用索引。

  2. 高选择性列上的索引：

    • 如果索引列的选择性不高（即列中的重复值很多），优化器可能会选择不使用索引。

  3. 统计信息不准确：

    • 当表的数据量变化较大时，索引的统计信息可能变得不准确，需要手动更新统计信息。
    1

    ANALYZE TABLE employees;

其他影响索引使用的情况

  1. 覆盖索引失效：

    • 如果查询的列无法被索引完全覆盖（即索引无法提供所有需要的列），则覆盖索引可能失效。
    1
2
3

     -- 如果 idx_name_department 只是 (last_name, department_id)
 -- 而查询需要其他列
 SELECT first_name FROM employees WHERE last_name = 'Smith' AND department_id = 5;

  2. 前导模糊查询：

    • 使用前导通配符的模糊查询会导致索引失效。
    1
2

    -- 索引不会被使用
SELECT * FROM employees WHERE last_name LIKE '%Smith';

索引例子

下面是一些示例，展示了不同情况下索引的使用与失效：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 假设有一个复合索引 idx_name_department(last_name, department_id)

-- 索引有效
SELECT * FROM employees WHERE last_name = 'Smith' AND department_id = 5;

-- 索引部分失效，仅 last_name 被使用
SELECT * FROM employees WHERE last_name > 'Smith' AND department_id = 5;

-- 索引失效，因为使用了函数
SELECT * FROM employees WHERE LEFT(last_name, 3) = 'Smi';

-- 索引失效，因为使用了前导通配符
SELECT * FROM employees WHERE last_name LIKE '%Smith';

-- 索引失效，除非 last_name 和 department_id 上都有单独的索引
SELECT * FROM employees WHERE last_name = 'Smith' OR department_id = 5;

-- 索引失效，因为不符合最左前缀原则
SELECT * FROM employees WHERE department_id = 5;

锁

MySQL 锁是数据库管理系统（DBMS）中用于协调多个用户对数据库资源（如表、行）访问的一种机制。锁的主要作用是确保数据一致性和完整性，同时允许并发访问。以下是 MySQL 锁的一些关键概念和常见类型：

锁的类型

  1. 表级锁（Table Lock）：

    • 读锁（共享锁，S 锁）：允许多个事务同时读取表中的数据，但任何事务在持有读锁时不能对表进行写操作。
    • 写锁（排他锁，X 锁）：独占锁，只有获得写锁的事务可以读取和修改表中的数据，其他事务不能读或写该表。
    1
2
3
4
5
6

     -- 加读锁
 LOCK TABLES table_name READ;
 -- 加写锁
 LOCK TABLES table_name WRITE;
 -- 释放锁
 UNLOCK TABLES;

  2. 行级锁（Row Lock）：

    • 共享锁（S 锁）：允许多个事务读取一行，但不能修改该行。
    • 排他锁（X 锁）：独占锁，获得排他锁的事务可以读取和修改该行，其他事务不能访问该行。

行级锁是通过 InnoDB 存储引擎实现的，用于提高并发性能。

锁的粒度

  1. 表锁（Table Lock）：锁住整张表，适用于读多写少的场景，开销较小，但并发性较差。
  2. 行锁（Row Lock）：锁住表中的某一行，适用于写操作频繁的场景，开销较大，但并发性较好。

锁的机制

  1. 自动锁定：
    • InnoDB 存储引擎会在执行 DML 操作（如 SELECT, INSERT, UPDATE, DELETE）时自动加锁。
    • 事务开始时，InnoDB 会自动加上必要的锁，并在事务提交或回滚时释放锁。
  2. 显式锁定：
    • 开发者可以使用显式锁定语句来控制锁的行为，适用于需要精细控制锁的场景。

死锁

  1. 死锁的定义：
    • 当两个或多个事务相互持有对方所需要的资源并等待对方释放，导致无法继续执行的情况称为死锁。
  2. 死锁检测和处理：
    • InnoDB 存储引擎有内置的死锁检测机制，会自动检测并处理死锁，通过回滚其中一个事务来解除死锁。

常用锁相关语句

  1. 加锁和解锁：

    1
2
3
4
5
6

    -- 加读锁（共享锁）
LOCK TABLES table_name READ;
-- 加写锁（排他锁）
LOCK TABLES table_name WRITE;
-- 解锁
UNLOCK TABLES;

  2. 事务和锁：

    1
2
3
4
5
6

    -- 开始事务
START TRANSACTION;
-- 提交事务并释放锁
COMMIT;
-- 回滚事务并释放锁
ROLLBACK;

  3. 查看锁信息：

    1
2

    -- 查看当前锁信息
SHOW ENGINE INNODB STATUS;

锁的最佳实践

  1. 尽量使用 InnoDB 存储引擎：因为 InnoDB 支持行级锁，有更高的并发性能和更强的事务支持。
  2. 控制事务大小：尽量减少单个事务中的操作数量和时间，避免长事务导致锁定时间过长。
  3. 合适的索引：确保查询使用合适的索引，以减少锁定的行数，提高锁的效率。
  4. 显式锁定：在必要时使用显式锁定，但要注意控制锁的粒度和范围，以平衡并发性和一致性。
  5. 死锁重试机制：在应用程序中实现死锁重试机制，以应对偶发的死锁情况。

锁例子

下面是一些例子，展示了 MySQL 锁的使用：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 1. 显式表锁
LOCK TABLES employees READ; -- 加读锁
SELECT * FROM employees;
UNLOCK TABLES; -- 解锁

-- 2. 使用事务和行级锁
START TRANSACTION;
SELECT * FROM employees WHERE id = 1 FOR UPDATE; -- 加排他锁
UPDATE employees SET salary = salary + 1000 WHERE id = 1;
COMMIT;

-- 3. 死锁重试机制
DELIMITER //
CREATE PROCEDURE update_salary()
BEGIN
  DECLARE retry_count INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET retry_count = retry_count + 1;
    IF retry_count < 3 THEN
      ROLLBACK;
      RESIGNAL;
    ELSE
      ROLLBACK;
    END IF;
  END;

  START TRANSACTION;
  UPDATE employees SET salary = salary + 1000 WHERE id = 1;
  COMMIT;
END //
DELIMITER ;

事务

事务的四大特征

  1. 原子性（Atomicity）：事务里的内容要么全部成功要么都不成功。
  2. 一致性（Consistency）：事务前后数据的完整性保持一致，如：a 给 b 转一千块，事务执行以后，a 和 b 的钱总数是一样的。
  3. 隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 即要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
  4. 持久性（Durability）：事务结束，数据就持久化到数据库。

并发事务问题

  1. 脏读 (Dirty Read) 脏读发生在一个事务能够读取另一个事务尚未提交的数据时。如果第二个事务回滚（撤销）了这些更改，那么第一个事务读取到的数据就变得无效或“脏”了。脏读仅在最低的隔离级别“读未提交”（Read Uncommitted）下会发生。 示例：
    • 事务 A 更新了一行数据，但未提交。
    • 事务 B 读取了这个更新的数据。
    • 事务 A 回滚了更新。
    • 事务 B 读取到的数据现在无效，因为事务 A 的更改没有最终被提交。
  2. 不可重复读 (Non-Repeatable Read) 不可重复读问题出现在一个事务在两次读取同一数据时，数据发生了变化。这种情况通常发生在隔离级别“读已提交”（Read Committed）下。 示例：
    • 事务 A 读取了一行数据。
    • 事务 B 更新了该行数据并提交。
    • 事务 A 再次读取同一行数据，发现数据已经发生变化。
  3. 幻读 (Phantom Read) 幻读问题发生在一个事务执行两次相同的查询，但第二次查询返回的结果集包含了第一次查询时不存在的“幻影”行。这种情况通常发生在隔离级别“可重复读”（Repeatable Read）下，但可以在“串行化”（Serializable）级别避免。 示例：
    • 事务 A 读取符合某个条件的多行数据。
    • 事务 B 插入了几行满足事务 A 查询条件的新数据并提交。
    • 事务 A 再次执行相同的查询，发现结果集中包含了新插入的行。

四种标准的事务隔离级别

在 MySQL 中，事务隔离级别定义了一个事务在读取数据时与其他并发事务的交互方式。

  1. 读未提交 (Read Uncommitted)：在这个级别，事务可以读取其他事务未提交的数据。允许脏读、不可重复读和幻读。
  2. 读已提交 (Read Committed)：在这个级别，事务只能读取已经提交的数据。防止脏读，但允许不可重复读和幻读。
  3. 可重复读 (Repeatable Read)：在这个级别，一个事务在开始时看到的数据一致，即使其他事务在该事务执行过程中提交了数据。MySQL 的默认隔离级别是可重复读。防止脏读和不可重复读，但可能允许幻读。
  4. 串行化 (Serializable)：在这个级别，所有事务按顺序执行，完全避免并发问题。这会使系统性能下降，但可以避免“脏读”、“不可重复读”和“幻读”。

各个隔离级别之间的关系如下：

读未提交 < 读已提交 < 可重复读 < 串行化

MySQL 的默认事务隔离级别是“可重复读”，可以通过以下 SQL 命令查看和设置当前的隔离级别：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查看当前会话的隔离级别
SELECT @@tx_isolation;

-- 设置当前会话的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查看全局隔离级别
SELECT @@global.tx_isolation;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Spring Boot 中可以通过使用 @Transactional 注解指定某个方法的隔离级别。

如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class YourService {

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void yourTransactionalMethod() {
        // your transactional code
    }
}

在这个例子中，yourTransactionalMethod 方法的事务隔离级别被设置为 READ_COMMITTED。

Spring 提供的隔离级别枚举如下，对应于标准的 SQL 隔离级别：

  • Isolation.DEFAULT：使用底层数据库的默认隔离级别。
  • Isolation.READ_UNCOMMITTED：读未提交。
  • Isolation.READ_COMMITTED：读已提交。
  • Isolation.REPEATABLE_READ：可重复读。
  • Isolation.SERIALIZABLE：串行化。

乐(悲)观锁

乐观锁 (Optimistic Lock)

概念：

乐观锁假设数据在并发环境下很少发生冲突，因此在操作数据时不加锁。乐观锁在提交更新时检查数据是否被其他事务修改，如果数据被修改，则回滚当前事务。

特点：

  • 无锁操作：在读取数据时不加锁，只在提交更新时检查冲突。
  • 冲突检测：通过比较版本号或时间戳来检测数据是否被修改。
  • 性能较高：由于没有频繁的加锁和解锁操作，系统性能较高。

适用场景：

  • 数据争用不严重的场景。
  • 读操作多于写操作的场景。
  • 对性能要求较高的场景。

实现方式：

乐观锁通常使用版本号或时间戳机制。例如，在数据库表中添加一个 version 字段，每次更新时检查并递增版本号。以下是一个示例：

假设有一个 accounts 表：

1
2
3
4
5

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(10, 2),
  version INT
);

更新操作：

1
2
3
4
5
6

BEGIN;
SELECT balance, version FROM accounts WHERE id = 1;
/* Perform some business logic, e.g., balance += 100 */
UPDATE accounts SET balance = balance + 100, version = version + 1
WHERE id = 1 AND version = old_version;
COMMIT;

在更新时，检查 version 是否与读取时一致，如果不一致则更新失败，可以通过应用逻辑重试或报错处理。

悲观锁 (Pessimistic Lock)

概念：

悲观锁假设数据在并发环境下会发生冲突，因此在操作数据之前会锁定资源，以防止其他事务对数据进行修改。悲观锁通常依赖于数据库的锁机制。

特点：

  • 加锁强度高：在读取或写入数据之前，先对数据进行加锁，确保在事务结束之前，其他事务不能对数据进行操作。
  • 阻塞性：如果一个事务对数据加了悲观锁，其他尝试访问该数据的事务会被阻塞，直到锁被释放。
  • 开销大：由于频繁的加锁和解锁操作，会增加系统开销，影响性能。

适用场景：

  • 数据争用严重的场景。
  • 写操作频繁的场景。
  • 对数据一致性要求极高的场景。

实现方式：

在 SQL 中可以使用 SELECT ... FOR UPDATE 来实现悲观锁。例如：

1
2
3
4

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
/* Perform updates */
COMMIT;

乐(悲)观锁总结

  • 悲观锁：通过加锁确保数据一致性，适用于高争用环境，但性能开销较大。
  • 乐观锁：通过版本控制或时间戳检测数据冲突，适用于低争用环境，性能较好，但需要处理冲突。

选择使用哪种锁机制，应根据具体应用的并发情况、数据访问模式和性能需求来决定。

MVCC（多版本并发控制）

多版本并发控制 (MVCC, Multi-Version Concurrency Control) 是一种用于数据库管理系统的并发控制机制，它允许多个事务并发地执行而不会互相阻塞，同时提供一致的读和写操作。MVCC 通过维护数据的多个版本来实现，避免了许多传统锁机制带来的性能开销。

MVCC 的工作原理

MVCC 通过为每个数据项保存多个版本，并为每个事务分配一个唯一的时间戳或事务 ID (Transaction ID, TID)，来管理并发事务。以下是 MVCC 的主要工作原理：

  1. 版本链： 每个数据项都有一个版本链，链中包含该数据项的所有历史版本。每个版本包含数据值和相关的元数据（如创建时间戳和删除时间戳）。
  2. 读取操作： 当事务读取数据时，它会查找与自己的时间戳匹配的最新版本。这意味着事务只会看到在其开始之前已经提交的版本，不会被其他并发事务未提交的修改所影响。
  3. 写入操作： 当事务修改数据时，它不会覆盖现有版本，而是创建一个新版本，并将其添加到版本链中。新版本会带有事务的时间戳，表明它是由该事务创建的。
  4. 提交和回滚： 当事务提交时，创建的新版本会成为可见版本。如果事务回滚，创建的新版本会被标记为无效，不会影响其他事务。

MVCC 的优点

  1. 减少锁竞争： 由于读操作不会阻塞写操作，写操作也不会阻塞读操作，MVCC 有效地减少了锁竞争，提高了系统的并发性能。
  2. 一致性读取： 事务在读取数据时，总是能够看到一致的视图，不会被其他并发事务的未提交修改所影响，避免了脏读。
  3. 提升性能： 由于读操作不需要加锁，系统的读性能得到显著提升。写操作创建新版本而不是覆盖旧版本，也提高了写操作的效率。

MVCC 处理的并发问题

  1. 避免脏读： 读操作只看到已经提交的数据版本，因此避免了读取未提交数据带来的脏读问题。
  2. 避免不可重复读： 事务在读取同一数据项时，总是能够看到一致的版本，避免了同一事务中多次读取到不同数据的问题。
  3. 幻读： MVCC 在某些数据库系统中可以避免幻读问题。通过使用 MVCC 和合适的隔离级别（如可重复读或串行化），可以确保查询结果集的一致性。

MVCC 的实现

不同数据库系统对 MVCC 的实现有所不同。以下是几种流行数据库的 MVCC 实现方式：

  1. PostgreSQL： PostgreSQL 使用事务 ID (XID) 和隐藏字段来实现 MVCC。每个数据行都有两个隐藏字段，分别表示创建该行的事务 ID 和删除该行的事务 ID。
  2. MySQL (InnoDB 存储引擎)： InnoDB 使用 undo log 和事务 ID 来实现 MVCC。每个数据行都有一个隐藏的事务 ID 和回滚指针，用于指向前一个版本的数据。
  3. Oracle： Oracle 使用回滚段来存储旧版本的数据，当需要读取旧版本时，通过回滚段获取数据的历史版本。

MVCC 总结

MVCC 是一种强大的并发控制机制，通过维护数据的多个版本，实现高效的并发事务处理。它有效地减少了锁竞争，提高了系统的性能和一致性，是现代数据库系统中广泛应用的技术。

EXPLAIN

EXPLAIN 是 MySQL 提供的一种分析工具，用于查看 SQL 查询的执行计划。它显示了查询优化器是如何执行 SQL 语句的，帮助开发人员优化查询性能。以下是 EXPLAIN 的用法和解释。

基本用法

EXPLAIN SELECT * FROM table_name WHERE condition;

EXPLAIN 会显示查询的执行计划，包括使用的索引、连接类型、扫描的行数等。

输出列的含义

执行 EXPLAIN 后，结果集包含多个列，每一列都有特定的含义：

  1. id：查询的标识符。每个 SELECT 子句或子查询都会分配一个唯一的 id。

  2. select_type：查询的类型。常见类型包括：

    • SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION。
    • PRIMARY：最外层的 SELECT 查询。
    • SUBQUERY：子查询中的 SELECT。
    • DERIVED：派生表（子查询中的 FROM 子句）。
    • UNION：UNION 中的第二个或后续的 SELECT 查询。
    • UNION RESULT：UNION 的结果集。

  3. table：查询的表。

  4. partitions：查询涉及的分区（如果有）。

  5. type：连接类型，表示查询中的表如何连接。连接类型的效率从高到低依次为：

    • system：表只有一行（系统表）。
    • const：表最多只有一行匹配（常量）。
    • eq_ref：对于每个来自前一个表的行，读取一行。
    • ref：对于每个来自前一个表的行，读取匹配几行。
    • range：只检索给定范围的行，使用索引选择行。
    • index：全索引扫描。
    • ALL：全表扫描。

  6. possible_keys：查询中可能使用的索引。

  7. key：查询实际使用的索引。

  8. key_len：使用的索引的长度。

  9. ref：使用的列或常量与索引比较。

  10. rows：MySQL 估计要读取的行数。

  11. filtered：查询条件过滤的行的百分比。

  12. Extra：其他信息，比如：

    • Using index：查询使用了覆盖索引。
    • Using where：查询使用了 WHERE 子句来过滤行。
    • Using temporary：查询使用了临时表。
    • Using filesort：查询使用了文件排序。

示例分析

简单查询

EXPLAIN SELECT * FROM employees WHERE id = 1;

结果可能显示：

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesconstPRIMARYPRIMARY4const1100.00Using where

解释：

  • id 为 1，表示这是一个简单查询。
  • select_type 为 SIMPLE，没有子查询。
  • table 为 employees，表示查询的表。
  • type 为 const，因为 id 是主键，这是一个常量查询。
  • possible_keys 和 key 都是 PRIMARY，表示使用了主键索引。
  • rows 为 1，表示预计扫描一行。
  • Extra 为 Using where，表示使用了 WHERE 子句。

连接查询

EXPLAIN SELECT e.*, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

结果可能显示：

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEdALLPRIMARYNULLNULLNULL10100.00
1SIMPLEerefdept_iddept_id4d.dept_id100100.00Using where

解释：

  • 第一个 id 为 1 的行表示对 departments 表的全表扫描（type 为 ALL）。
  • 第二个 id 为 1 的行表示对 employees 表的引用（type 为 ref），使用了 dept_id 索引。
  • rows 列表明 MySQL 预计从 departments 表中读取 10 行，并从 employees 表中读取 100 行。

优化建议

  1. 使用合适的索引：确保查询中使用了合适的索引，以减少扫描的行数。
  2. 避免全表扫描：尽量避免 type 为 ALL 的全表扫描，可以通过增加索引来优化。
  3. 优化连接类型：尽量使用效率较高的连接类型，如 ref、eq_ref 等。
  4. 减少临时表和文件排序：避免 Extra 列中出现 Using temporary 和 Using filesort，可以通过优化查询或增加索引来实现。

通过合理使用 EXPLAIN 分析查询执行计划，可以发现查询性能的瓶颈，并进行相应的优化。

© 2020 - 2025 Lucas's Article
Built with Hugo
Theme Stack designed by Jimmy