Mysql中如何进行sql调优

一.Mysql中如何进行sql调优

平时使用进行sql调优，主要通过观察慢sql，然后利用explain分析查询语句的执行计划，识别性能瓶颈，然后进行优化。

1.合理设计索引，利用联合索引进行覆盖索引的优化，避免回表产生，减少一次查询和随机I/O

2.避免select*，只查询必要字段

3.避免在sql中进行函数计算等操作，使得无法命中索引而进行全表扫描降低效率

4.避免使用%LIKE，导致全表扫描

5.注意联合索引需要满足最左匹配原则

6.不要对无索引字段进行排序操作（）

7.连接查询需要注意不同字段的字符集是否一致，否则也会导致全表查询

比如下面代码

CREATE TABLE users (  
  id INT PRIMARY KEY,  
  name VARCHAR(50) CHARSET utf8mb4  -- 字符集为 utf8mb4  
);  
CREATE TABLE orders (  
  id INT PRIMARY KEY,  
  customer_name VARCHAR(50) CHARSET latin1  -- 字符集为 latin1  
);  
SELECT * FROM users  
JOIN orders ON users.name  = orders.customer_name;   

··问题原因

users.name （utf8mb4）与 orders.customer_name （latin1）字符集不一致，数据库需隐式转换字符集才能比较。

字符集转换会导致索引失效，触发全表扫描（如 users 表的 name 字段有索引也无法使用）。

··优化方案

统一字符集：

ALTER TABLE orders MODIFY customer_name VARCHAR(50) CHARSET utf8mb4;

显式指定字符集（临时方案）：

SELECT * FROM users JOIN orders ON CONVERT(users.name USING latin1) = orders.customer_name;

此外还可以利用缓存进行优化，一些变化少或者访问频繁的数据设置到缓存中，减小数据库压力提高查询效率。

还可以通过业务来进行优化sql，例如少展示一些不需要的字段，减少多表查询的情况，将列表查询替换成分页分批次查询等。

效果：避免隐式转换，索引可正常命中，查询速度提升。

（1. 减少查询字段：业务需求驱动数据精简

业务场景：用户列表页仅需展示用户的头像和昵称，无需其他详细信息（如地址、年龄、注册时间等）。
问题：若使用 SELECT * FROM users，会查询所有字段，浪费数据库资源和网络带宽。
优化方案：仅查询所需字段，如 SELECT id, avatar, nickname FROM users。
效果：减少数据传输量，提升查询速度，降低内存占用。

---

2. 分页查询替代全量查询：应对大数据量场景

业务场景：电商后台需展示 10 万条订单记录，但前端每次仅展示 20 条。
问题：SELECT * FROM orders 会导致单次查询数据量过大，甚至内存溢出。
优化方案：

• 分页查询：SELECT * FROM orders LIMIT 20 OFFSET 0（逐步翻页）；
• 游标分页：SELECT * FROM orders WHERE id > 1000 ORDER BY id LIMIT 20（避免深分页性能问题）。
  效果：单次查询数据量可控，减少数据库负载和响应时间。

---

3. 冗余字段减少联表查询：以空间换时间

业务场景：订单列表需展示用户昵称，传统方案需联表查询 orders 和 users。
问题：联表查询（JOIN）可能触发全表扫描，性能随数据量增长而下降。
优化方案：在 orders 表中冗余存储用户昵称字段 user_nickname，下单时同步写入。
效果：单表查询 SELECT order_id, user_nickname FROM orders，避免 JOIN 操作，但需维护冗余数据的一致性（如用户修改昵称时触发更新）。）