MySQL 慢查询优化

1. MySQL 出现慢查询的原因

1.1 核心原因只有两点

• 索引问题：没有走主键索引或其他索引，或者索引建立不合理（索引创建不合理导致 B+Tree 索引树过深，查询效率与全表扫描相近）。
• 数据量过大：数据表太大，单次查询扫描的数据量过多。

2. 优化方法

① 优化索引：针对没有创建主键索引或索引创建不合理的情况，请删除无效索引并重建索引；若表没有主键，则需要添加主键索引。

② 分区分表：当数据量特别巨大时，MySQL 查询会进行局部扫描或全表扫描，极大增加扫描时间。将大表拆分为小的分区表（基于时间或
ID 作为分区键等），可以显著提升查询速度，减少不必要的扫描。

3. 具体优化方法

3.1 优化索引：创建索引、删除不合理索引

针对查询慢的问题，通常需要增加索引，以空间换取查询时间的提升。

（1）普通索引

-- 建表时创建
CREATE TABLE users
(
    id    INT,
    name  VARCHAR(50),
    age   INT,
    INDEX idx_name (name) -- 普通索引
);

-- 使用 ALTER 添加
ALTER TABLE users
    ADD INDEX idx_age (age);

-- 使用 CREATE 单独创建
CREATE INDEX idx_name ON users (name);

（2）唯一索引

-- 建表时创建
CREATE TABLE users
(
    id    INT,
    email VARCHAR(100) UNIQUE,     -- 隐式唯一索引
    UNIQUE INDEX idx_email (email) -- 显式唯一索引
);

-- 使用 ALTER 添加
ALTER TABLE users
    ADD UNIQUE INDEX idx_email (email);

（3）主键索引

-- 建表时创建
CREATE TABLE users
(
    id   INT PRIMARY KEY, -- 主键索引（自动创建）
    name VARCHAR(50)
);

-- 使用 ALTER 添加主键
ALTER TABLE users
    ADD PRIMARY KEY (id);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

（4）全文索引

-- 建表时创建
CREATE TABLE articles
(
    id       INT,
    content  TEXT,
    FULLTEXT INDEX idx_content (content) -- 全文索引（仅适用于 MyISAM/InnoDB）
);

-- 使用 ALTER 添加
ALTER TABLE articles
    ADD FULLTEXT INDEX idx_content (content);

-- 使用 CREATE 单独创建
CREATE
FULLTEXT INDEX idx_content ON articles (content);

（5）空间索引

-- 建表时创建
CREATE TABLE locations
(
    id      INT,
    point   GEOMETRY NOT NULL,
    SPATIAL INDEX idx_point (point) -- 空间索引（需使用 MyISAM 或支持空间索引的引擎）
);

-- 使用 ALTER 添加
ALTER TABLE locations
    ADD SPATIAL INDEX idx_point (point);

-- 使用 CREATE 单独创建
CREATE
SPATIAL INDEX idx_point ON locations (point);

（6）组合索引

-- 建表时创建
CREATE TABLE orders
(
    user_id    INT,
    order_date DATE,
    INDEX      idx_user_date (user_id, order_date) -- 组合索引
);

-- 使用 ALTER 添加
ALTER TABLE orders
    ADD INDEX idx_user_date (user_id, order_date);

-- 使用 CREATE 单独创建
CREATE INDEX idx_user_date ON orders (user_id, order_date);

（7）其他特殊场景

前缀索引：对字段的前 N 个字符创建索引（适用于长文本字段）

ALTER TABLE users
    ADD INDEX idx_name_prefix (name(10)); -- 仅索引前10个字符

函数索引（MySQL 8.0+）：基于表达式或函数创建索引

ALTER TABLE users
    ADD INDEX idx_year (YEAR(create_time)); -- 按年份创建索引

隐藏索引（MySQL 8.0+）：创建不可见索引（用于测试索引是否有效）

ALTER TABLE users
    ADD INDEX idx_age (age) INVISIBLE;

（8）删除索引

ALTER TABLE users DROP INDEX idx_name;
-- 或
DROP INDEX idx_name ON users;

3.2 对于海量数据，必须分区分表

随着数据量不断提升，对于海量存储数据必须使用分区表以减少数据库压力，提升查询速度。

常用分区方法

（1）RANGE 分区（按日期范围）

-- 按年份分区存储订单数据
CREATE TABLE sales
(
    id         INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount     DECIMAL(10, 2),
    region     VARCHAR(20)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE   -- 处理未来数据
);

（2）RANGE COLUMNS 分区（多列范围）

-- 按日期和金额组合范围分区
CREATE TABLE sales
(
    id         INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount     DECIMAL(10, 2),
    region     VARCHAR(20)
) PARTITION BY RANGE COLUMNS(order_date, amount) (
    PARTITION p2023_low VALUES LESS THAN ('2024-01-01', 1000),   -- 2023年且金额<1000
    PARTITION p2024_high VALUES LESS THAN ('2025-01-01', 5000),
    PARTITION pmax VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

（3）LIST 分区（按预定义值）

-- 按预定义的地区编号分区
CREATE TABLE customers
(
    id        INT PRIMARY KEY,
    name      VARCHAR(50),
    region_id INT
) PARTITION BY LIST (region_id) (
    PARTITION p_east VALUES IN (1, 2, 3),    -- 东部地区
    PARTITION p_west VALUES IN (4, 5, 6),    -- 西部地区
    PARTITION p_other VALUES IN (DEFAULT)    -- 其他区域
);

（4）HASH 分区（均匀分布数据）

-- 按用户ID哈希分散到4个分区
CREATE TABLE user_logs
(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    user_id  INT,
    log_time DATETIME,
    content  TEXT
) PARTITION BY HASH(user_id)
PARTITIONS 4; -- 指定分区数量

（5）KEY 分区（简化哈希）

-- 按用户名的哈希值分区
CREATE TABLE messages
(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30),
    message  TEXT
) PARTITION BY KEY(username)
PARTITIONS 5; -- 分区数量

（6）复合分区（RANGE + HASH）

-- 先按年份范围分区，再按用户ID哈希子分区
CREATE TABLE orders
(
    id         INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    user_id    INT,
    amount     DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 (   -- 每个主分区下分4个子分区
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

添加分区示例

-- 为2023年新增分区（需确保分区连续）
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
    );

删除分区

-- 删除指定分区（数据会丢失！）
ALTER TABLE sales DROP PARTITION p2020;

查询分区元数据

-- 查看表的分区信息
SELECT PARTITION_NAME,
       PARTITION_METHOD,
       PARTITION_EXPRESSION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sales';

3.3 使用存储过程定期自动创建分区

由于 MySQL 开源版没有 Oracle 的自动创建分区功能，需要通过存储过程和定时事件来实现自动创建分区。以下为参考脚本：

-- 创建存储过程，自动插入分区表
DELIMITER
$$
CREATE PROCEDURE AddNextPartition()
BEGIN
    DECLARE
next_month VARCHAR(6);
    -- 计算下个月，例如当前如果数据需要新增 '202401' 分区
    SET
next_month = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m');

    SET
@sql = CONCAT(
        'ALTER TABLE revenue_detail_table REORGANIZE PARTITION pmax INTO (',
        'PARTITION p', next_month, ' VALUES LESS THAN (\'',
        DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%Y%m'), '
\'), ',
        'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
    );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 创建事件，每月检查并自动增加分区
CREATE
EVENT IF NOT EXISTS AutoAddPartition
ON SCHEDULE EVERY 1 MONTH
DO CALL AddNextPartition();