正在查看旧版本。 查看 当前版本.

与当前比较 查看页面历史记录

« 前一个 版本 2 当前 »

Mysql数据库慢查询优化

1. Mysql出现慢查询原因

1.1 核心原因只有两点：

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


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增加参数作为分区键等），可以极大的提升查询速度，减少不必要的扫描



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



  • 无标签