您现在的位置是：首页 >技术杂谈 >SQL优化的策略和示例网站首页技术杂谈

1、索引优化

1.1 避免全表扫描

• 场景：WHERE 条件未命中索引导致查询慢。

        -- 优化前（无索引）
        SELECT * FROM users WHERE name = 'Alice';

        -- 优化后：为 name 字段添加索引
        CREATE INDEX idx_name ON users(name);

1.2 联合索引的最左匹配原则

• 场景：多个字段联合查询时，索引字段顺序需匹配查询条件。

• -- 创建联合索引 (age, city)
  CREATE INDEX idx_age_city ON users(age, city);

  -- 有效使用索引的查询：
  SELECT * FROM users WHERE age = 25; -- ✅ 命中索引
  SELECT * FROM users WHERE age = 25 AND city = 'Beijing'; -- ✅ 命中索引
  SELECT * FROM users WHERE city = 'Beijing'; -- ❌ 未命中索引（未从最左字段开始）覆盖

1.3 索引减少回表

• 场景：查询仅需索引字段时，避免回表查询。

-- 优化前：需回表查所有字段
SELECT * FROM orders WHERE user_id = 100;

-- 优化后：仅查询索引字段
SELECT user_id, order_date FROM orders WHERE user_id = 100;
-- 创建覆盖索引 (user_id, order_date)
CREATE INDEX idx_user_order ON orders(user_id, order_date);

2、查询语句优化

2.1 避免 SELECT* 

• 场景：查询不必要的字段增加 I/O 开销。

-- 优化前
SELECT * FROM products WHERE category = 'electronics';

-- 优化后：仅查询所需字段
SELECT product_id, product_name FROM products WHERE category = 'electronics';

2.2 分页优化（避免 OFFSET 过大）

• 场景：分页查询时，LIMIT 100000, 20 会导致扫描大量数据。

  -- 优化前
  SELECT * FROM logs ORDER BY id LIMIT 100000, 20;

  -- 优化后：通过记录上一次的 ID 跳过偏移
  SELECT * FROM logs WHERE id > 100000 ORDER BY id LIMIT 20;

2.3 用JOIN 替代子查询

• 场景：子查询可能导致临时表或重复执行。

-- 优化前：子查询
SELECT * FROM users 
WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 优化后：使用 JOIN
SELECT u.* FROM users u
JOIN orders o ON u.user_id = o.user_id 
WHERE o.amount > 1000;

3、表结构优化

3.1 合理选择字段类型

• 场景：避免使用大字段（如 TEXT）存储小数据。

  -- 优化前：使用 TEXT 存储状态
  CREATE TABLE orders (
      status TEXT  -- ❌ 低效
  );

  -- 优化后：使用 ENUM 或 TINYINT
  CREATE TABLE orders (
      status ENUM('pending', 'completed', 'cancelled')  -- ✅ 高效
  );

3.2 垂直拆分大表

• 场景：将频繁查询的字段与不常用的大字段分离。

  -- 原始表
  CREATE TABLE articles (
      id INT PRIMARY KEY,
      title VARCHAR(100),
      content TEXT,  -- 大字段
      author VARCHAR(50)
  );

  -- 拆分后
  CREATE TABLE articles_base (
      id INT PRIMARY KEY,
      title VARCHAR(100),
      author VARCHAR(50)
  );
  CREATE TABLE articles_content (
      id INT PRIMARY KEY,
      content TEXT
  );

4、执行计划分析

   使用 EXPLAIN 分析查询执行计划，重点关注以下字段

     type:查询类型（ALL 表示全表扫描，需要优化）

     key: 实际使用的索引

     rows:预估扫描行数

     Extra:额外信息（如 Using filesort 表示需要排序，Using temporary 表示使用临时表）

例： EXPLAIN SELECT * FROM users WHERE age > 25;

5、数据库参数调优

5.1 调整缓冲池大小（InnoDB）

-- 配置 InnoDB 缓冲池大小为物理内存的 70%
SET GLOBAL innodb_buffer_pool_size = 8G;

5.2 优化日志写入策略

-- 提交事务时异步刷盘（牺牲部分安全性，提升性能）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

6、高级优化工具

6.1 慢查询日志分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 记录超过 2 秒的查询

6.2 使用Profiler 分析查询耗时

-- 启用性能分析
SET profiling = 1;
SELECT * FROM users WHERE age > 25;
SHOW PROFILES;  -- 查看各阶段耗时

通过索引优化、优化查询语句、表结构调整  执行计划分析等策略，可以提升SQL性能，优化后可以使用 EXPLAIN 进行验证