Mysql数据库慢查询优化

1. Mysql出现慢查询原因

1.1 核心原因只有两点：

没有走主键索引或者其他索引或者索引建立的不合理（索引不合理创建导致BTree索引树太深了，查询与全表扫描差不多）
数据表太大，一次性扫描数据量过多

2.优化方法

① 针对没有创建主键索引的或者索引创建不合理的，请删除索引，并重建索引，如果表没有主键的，也需要添加上主键索引

(1) 普通索引

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

ALTER TABLE users ADD INDEX idx_age (age);

CREATE INDEX idx_name ON users (name);

(2) 唯一索引

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

ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

(3) 主键索引

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

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 TABLE articles ADD FULLTEXT INDEX idx_content (content);

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 TABLE locations ADD SPATIAL INDEX idx_point (point);

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 TABLE orders ADD INDEX idx_user_date (user_id, order_date);

CREATE INDEX idx_user_date ON orders (user_id, order_date);

其他特殊场景：

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

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

(2) 函数索引（MySQL 8.0+）: 基于表达式或函数创建索引

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

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

ALTER TABLE users ADD INDEX idx_age (age) INVISIBLE;

删除索引：

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

② 针对数据量特别巨大的情况下，Mysql查询都会走局部扫描或者全局扫描，极大地增加扫描时间，因此将大表拆分成小的分区表（基于时间或者id增加参数作为分区键等），可以极大的提升查询速度，减少不必要的扫描

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
);

添加新分区（RANGE 示例）

-- 为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';

使用存储过程定期自动根据实际创建分区表：

-- 创建存储过程，自动插入分区表
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();