数据库性能提升的逆向思维：
不是加速查询，而是先让数据库“少干活”
1、优化SQL提升性能
（1）SQL优化快速（实时）生成报表
快速生成报表的关键是梳理需求和对应索引。尤其是日期，已经定义了是当天的数据，那么就和历史数据无关。但是SQL写的是全表查询，实际查询了所有历史数据，或者SQL查询没有用到索引，最后导致报表生成越来越慢。
示例：
问题描述：
假设某电商平台需要生成「当日订单统计报表」，初始 SQL 可能写成全表扫描或未合理利用索引的形式，导致查询缓慢。以 MySQL 为例，典型低效 SQL 如下：
-- 低效SQL示例：全表扫描且未用索引
SELECT
order_id, customer_id, order_amount, order_time, product_category
FROM
orders_table
WHERE
DATE(order_time) = CURDATE()
ORDER BY
order_amount DESC;
问题分析：
1、未建立合适索引：orders_table
表可能未在order_time
字段创建索引，导致每次查询需扫描全表（假设表中有 1000 万条历史数据）。
2、全表数据过滤：DATE(order_time) = CURDATE()
条件虽过滤当日数据，但 SQL 引擎无法利用索引快速定位，仍需遍历所有行。
3、排序开销大：对全量结果集按order_amount
排序，内存占用高，处理时间长。
SQL优化：
-- 优化后SQL：利用索引+覆盖查询
SELECT
order_id, order_amount, order_time
FROM
orders_table
WHERE
order_time >= CURDATE() AND order_time < DATE_ADD(CURDATE(), INTERVAL1DAY)
ORDERBY
order_amount DESCLIMIT1000; -- 若只需前N条数据，添加LIMIT减少返回量
时间条件优化：用
order_time >= CURDATE() AND order_time < CURDATE()+1
替代DATE(order_time)=CURDATE()
，避免函数计算导致索引失效；覆盖查询：查询字段
order_id
、order_amount
、order_time
均包含在索引中，无需回表查询，提升效率；限制返回量：添加
LIMIT
条件，若报表只需部分数据，可减少数据传输与处理开销。
总结：实时报表 SQL 优化核心原则
1、索引优先：为过滤条件（如日期）和排序字段创建复合索引，避免全表扫描；
2、查询精简：使用覆盖查询减少回表，用LIMIT
限制返回量；
3、条件优化：避免在索引字段上使用函数计算（如DATE(order_time)
），保持条件原生性；
4、架构适配：对海量数据使用分区表，结合缓存降低数据库负载。
（2）慎用分页有效提升性能
传统分页：
1000 万条数据的用户表users_table
，查询第 1000 页（每页 10 条）
SELECT * FROM users_table ORDER BY id LIMIT 10000, 10;
问题：
数据库需扫描 10010 行，丢弃前 10000 行，仅返回 10 行。偏移量越大，扫描行数呈线性增长（如第 10 万页需扫描 1000010 行），耗时从 50ms 飙升至 5000ms 以上。
SQL优化：
1）利用主键索引的有序性，通过上一页最后一条记录的 ID 作为条件，直接定位下一页数据，扫描行数从 10010 行降至 10 行，耗时优化至 < 10ms。
-- 主键接力法：深分页
第1页：SELECT * FROM users_table ORDER BY id LIMIT 10;（假设最后ID为10）
第2页：SELECT * FROM users_table WHERE id > 10 ORDER BY id LIMIT 10;
2）子查询仅查询主键 ID（利用覆盖索引），避免回表，扫描行数从 10010 行降至 10 行，耗时从 50ms 优化至 15ms，同时支持任意页码跳转。
-- 覆盖索引+子查询：平衡灵活性与效率
SELECT * FROM users_table
WHERE id IN (SELECT id FROM users_table ORDER BY id LIMIT 10000, 10)
ORDER BY id;
总结：
1、浅分页（offset<1000）：可使用LIMIT
，但需确保排序字段有索引；
2、深分页：必须用「主键接力法」或「覆盖索引 + 子查询」，避免扫描无效数据；
3、超大数据量：结合分区表（按时间 类型分区）缩小查询范围，减少扫描行数。
（3）杜绝低效SQL
1.避免SELECT *:
问题： SELECT *
查询所有列，即使只需要其中几列。这会增加网络传输量和磁盘I/O，尤其在宽表中效率低下。案例： SELECT * FROM users WHERE user_id = 123;
假设users
表有几十个字段，但只需要用户名和邮箱。优化： 明确选择需要的列： SELECT user_name, email FROM users WHERE user_id = 123;
2. 索引失效：
问题： 在索引列上使用函数、进行类型转换或使用 !=
、NOT IN
、LIKE '%...
等操作，会导致索引失效，从而进行全表扫描。案例： SELECT * FROM orders WHERE DATE(order_time) = '2024-03-15';order_time
列上有索引，但DATE()
函数导致索引失效。优化： 使用范围查询： SELECT * FROM orders WHERE order_time >= '2024-03-15' AND order_time < '2024-03-16';
3. 大量数据排序：
问题： ORDER BY
子句对大量数据进行排序会消耗大量CPU和内存资源。案例： SELECT * FROM products ORDER BY price;products
表有数百万条记录。优化： 如果只需要前几条排序结果，使用 LIMIT
限制返回的行数：SELECT * FROM products ORDER BY price LIMIT 10;
或者，如果排序字段有索引，可以利用索引排序，避免全表排序。
4. 频繁的子查询：
问题： 在 WHERE
或SELECT
子句中使用子查询，可能会导致多次查询数据库，效率低下。案例： SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE city = 'New York');优化： 使用连接查询： SELECT o.* FROM orders o JOIN users u ON o.user_id = u.user_id WHERE u.city = 'New York';
5. 未使用绑定变量：
问题： 每次执行SQL语句时，数据库都需要重新解析和优化SQL，增加数据库负担。 案例： 在循环中执行 SELECT * FROM products WHERE product_id =
+ productId; 每次循环productId
的值都不同。优化： 使用预编译语句和绑定变量，数据库只需解析一次SQL，提高效率。
6. 过度使用OR
条件:
问题： OR
条件可能会导致索引失效，从而进行全表扫描。案例： SELECT * FROM products WHERE category = 'Electronics' OR category = 'Clothing';优化： 使用 IN
或UNION
：SELECT * FROM products WHERE category IN ('Electronics', 'Clothing');
或者SELECT * FROM products WHERE category = 'Electronics' UNION SELECT * FROM products WHERE category = 'Clothing';
2、避免数据库对象设计失误
（1）避免不必要的多表关联
示例：
假设有订单表（orders）、用户表（users）、商品表（products）、地区表（regions）。只想查某天订单数量，却写了如下查询：
SELECT COUNT(DISTINCT o.id)
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
LEFT JOIN regions r ON o.region_id = r.id
WHERE o.order_date = '2024-06-20';
问题：
只用 orders
表数据，却无谓地JOIN
了users
、products
、regions
，result重复且性能大降（大表×大表，笛卡尔积风险）。
优化做法：
只查主表，不做多余关联：
SELECT COUNT(*) FROM orders WHERE order_date = '2024-06-20';
（2）避免动态计算结果没有单独存储导致的低效查询
示例：
需要统计每位用户的历史累计销售额，每次报表都如下查询：
SELECT u.id, SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
问题：
每次全表遍历 orders
，数据大则查询巨慢。动态聚合一遍遍重复、重复消耗计算资源。
优化做法：
设计历史累计表（冗余存储或物化视图）：
user_id | total_amount
----------------------
1001 | 22800.50
1002 | 15400.00查询时直接查单表，响应毫秒级。 实时性强要求可结合触发器、Binlog、流式统计等。 每天定时汇总到 user_total_amount
表：
3、从识别需求的合理性提升性能
必要性说明：
避免无效查询：频繁执行不必要或重复的SQL语句，导致计算资源浪费，增加响应时间，降低系统性能。 确保资源集中在关键业务：合理理解和定义业务需求，避免“过度查询”或“无关数据的处理”，提升系统效率。 引导业务优化：通过与业务部门沟通，优化需求，将复杂、多余或重复的需求转化为高效的存储结构或预计算结果。
举例：
仅查询当天数据而非全表，减少扫描量； 避免过度利用高成本操作（如复杂嵌套子查询、无索引的全表扫描）； 引导业务定义合理的查询范围和过滤条件。
4、减小IO操作
必要性说明：
IO操作是数据库性能的瓶颈之一，尤其是在存储和检索大量数据时，过多的读写操作严重影响吞吐量和响应时间。 批量写入：可以减少磁盘碎片、减少事务次数、提升写入效率，特别是在导入大量数据或批量更新时极其有效。 减小IO操作的实际做法： 将多条操作合并为批量操作； 利用事务机制确保批量写入的原子性； 设计合理的索引，避免频繁的随机IO； 使用缓存或内存数据结构，减少对磁盘的直接访问。
举例：
每次批量插入多个记录，而非逐条插入； 使用批处理（batch processing）同步或异步写操作； 在数据加载或导入过程中，关闭不必要的索引，总体批量重建。
