fgedu.net.cn
目录
一、基础概念
1.1 SQL优化定义
SQL优化是指通过分析和改进SQL语句的执行计划，提高数据库查询和操作的性能。TiDB作为分布式数据库，SQL优化需要考虑分布式执行的特点。
1.2 执行计划
执行计划是数据库执行SQL语句的详细步骤，包括：
- 表扫描方式（全表扫描或索引扫描）
- 连接方式（嵌套循环、哈希连接等）
- 排序方式
- 分布式执行策略
1.3 性能瓶颈
- 全表扫描：未使用索引，导致扫描大量数据
- 索引失效：查询条件导致索引无法使用
- 连接效率低：连接方式选择不当或连接顺序不合理
- 排序开销大：大结果集排序
- 分布式执行开销：数据传输和协调开销
二、规划建议
2.1 SQL优化原则
- 减少数据扫描：使用索引，限制查询范围
- 减少数据传输：只查询需要的列，使用LIMIT
- 合理使用索引：选择合适的索引类型和索引列
- 优化连接操作：选择合适的连接方式和顺序
- 避免复杂操作：减少子查询、复杂函数和表达式
- 考虑分布式特性：避免全局排序、大结果集操作
2.2 索引使用建议
- 为频繁查询的列创建索引
- 遵循最左前缀原则
- 避免在索引列上使用函数
- 合理使用复合索引
- 定期维护索引统计信息
2.3 查询设计建议
- 使用参数化查询
- 避免SELECT *
- 合理使用LIMIT
- 避免在WHERE子句中使用函数
- 使用EXPLAIN分析执行计划
三、实施方案
3.1 执行计划分析
使用EXPLAIN分析执行计划
EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;
+-------------------------+----------+-----------+------------------------+---------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+----------+-----------+------------------------+---------------------------------------+ | Sort_6 | 10000.00 | root | | sort by:created_at DESC | | └─TableReader_5 | 10000.00 | root | | data:Selection_4 | | └─Selection_4 | 10000.00 | cop[tikv] | | where:age > 30 | | └─TableScan_3 | 100000.00| cop[tikv] | table:fgedu_users | range:[-inf,+inf], keep order:false | +-------------------------+----------+-----------+------------------------+---------------------------------------+
使用EXPLAIN ANALYZE分析实际执行情况
EXPLAIN ANALYZE SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;
+-------------------------+----------+-----------+------------------------+---------------------------------------+-----------------+-----------+----------------+------------------+----------------+---------------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | estimate cost | actual latency | concurrency | +-------------------------+----------+-----------+------------------------+---------------------------------------+-----------------+-----------+----------------+------------------+----------------+---------------+ | Sort_6 | 10000.00 | 9500 | root | | time:80ms, loops:10 | sort by:created_at DESC | 1.2 MB | 0 Bytes | 20000.00 | 80.5ms | 1 | | └─TableReader_5 | 10000.00 | 9500 | root | | time:60ms, loops:10 | data:Selection_4 | N/A | N/A | 15000.00 | 60.2ms | 1 | | └─Selection_4 | 10000.00 | 9500 | cop[tikv] | | time:50ms, loops:10 | where:age > 30 | N/A | N/A | 10000.00 | 50.1ms | 8 | | └─TableScan_3 | 100000.00| 100000 | cop[tikv] | table:fgedu_users | time:40ms, loops:100 | range:[-inf,+inf], keep order:false | N/A | N/A | 5000.00 | 40.3ms | 8 |风哥提示： +-------------------------+----------+-----------+------------------------+---------------------------------------+-----------------+-----------+----------------+------------------+----------------+---------------+
3.2 索引优化
创建合适的索引
-- 为age和created_at列创建索引
CREATE INDEX idx_age_created_at ON fgedudb.fgedu_users (age, created_at);
验证索引使用情况
EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;
+-------------------------+----------+-----------+------------------------+---------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+----------+-----------+------------------------+---------------------------------------+ | IndexReader_6 | 10000.00 | root | | index:IndexScan_5 | | └─IndexScan_5 | 10000.00 | cop[tikv] | table:fgedu_users, index:idx_age_created_at(age, created_at) | range:(30,+inf), keep order:true | +-------------------------+----------+-----------+------------------------+---------------------------------------+
3.3 查询优化技巧
避免全表扫描
-- 优化前：全表扫描
SELECT * FROM fgedudb.fgedu_users WHERE age > 30;
-- 优化后：使用索引
CREATE INDEX idx_age ON fgedudb.fgedu_users (age);
SELECT * FROM fgedudb.fgedu_users WHERE age > 30;
使用覆盖索引
-- 优化前：需要回表
SELECT id, username, email FROM fgedudb.fgedu_users WHERE age > 30;
-- 优化后：使用覆盖索引
CREATE INDEX idx_age_username_email ON fgedudb.fgedu_users (age, username, email);
SELECT id, username, email FROM fgedudb.fgedu_users WHERE age > 30;
优化排序操作
-- 优化前：额外排序
SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;
-- 优化后：使用索引排序
CREATE INDEX idx_age_created_at ON fgedudb.fgedu_users (age, created_at);
SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;
优化连接操作
-- 优化前：可能使用嵌套循环连接
SELECT u.*, o.* FROM fgedudb.fgedu_users u JOIN fgedudb.fgedu_orders o ON u.id = o.user_id WHERE u.age > 30;
-- 优化后：确保连接列有索引
CREATE INDEX idx_user_id ON fgedudb.fgedu_orders (user_id);
SELECT u.*, o.* FROM fgedudb.fgedu_users u JOIN fgedudb.fgedu_orders o ON u.id = o.user_id WHERE u.age > 30;
四、实战案例
4.1 电商平台订单查询优化
场景：电商平台需要优化订单查询性能，包括按用户、时间、状态等维度的查询。
步骤1：创建订单表
CREATE TABLE fgedudb.fgedu_orders (
id BIGINT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
status TINYINT NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
);
步骤2：添加索引
-- 添加常用索引
CREATE UNIQUE INDEX idx_order_no ON fgedudb.fgedu_orders (order_no);
CREATE INDEX idx_user_id ON fgedudb.fgedu_orders (user_id);
CREATE INDEX idx_created_at ON fgedudb.fgedu_orders (created_at);
CREATE INDEX idx_status ON fgedudb.fgedu_orders (status);
CREATE INDEX idx_user_status ON fgedudb.fgedu_orders (user_id, status);
CREATE INDEX idx_status_created ON fgedudb.fgedu_orders (status, created_at);
步骤3：优化查询语句
-- 优化前：全表扫描
SELECT * FROM fgedudb.fgedu_orders WHERE user_id = 1001;
-- 优化后：使用索引学习交流加群风哥QQ113257174
EXPLAIN SELECT * FROM fgedudb.fgedu_orders WHERE user_id = 1001;
+-------------------------+----------+-----------+------------------------+---------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+----------+-----------+------------------------+---------------------------------------+ | IndexReader_6 | 10.00 | root | | index:IndexScan_5 | | └─IndexScan_5 | 10.00 | cop[tikv] | table:fgedu_orders, index:idx_user_id(user_id) | range:[1001,1001], keep order:false | +-------------------------+----------+-----------+------------------------+---------------------------------------+
步骤4：优化复杂查询
-- 优化前：可能使用临时表
SELECT status, COUNT(*) FROM fgedudb.fgedu_orders WHERE created_at >= '2024-01-01' GROUP BY status;
-- 优化后：使用索引
EXPLAIN SELECT status, COUNT(*) FROM fgedudb.fgedu_orders WHERE created_at >= '2024-01-01' GROUP BY status;
+-------------------------------+----------+-----------+------------------------+---------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+----------+-----------+------------------------+---------------------------------------+ | HashAgg_11 | 5.00 | root | | group by:status, funcs:count(1) | | └─TableReader_12 | 10000.00 | root | | data:Selection_10 | | └─Selection_10 | 10000.00 | cop[tikv] | | where:created_at >= '2024-01-01' | | └─IndexScan_9 | 10000.00 | cop[tikv] | table:fgedu_orders, index:idx_created_at(created_at) | range:['2024-01-01 00:00:00',+inf), keep order:false | +-------------------------------+----------+-----------+------------------------+---------------------------------------+
4.2 数据分析查询优化
场景：需要对销售数据进行分析，生成报表。
步骤1：创建销售表
CREATE TABLE fgedudb.fgedu_sales (
id BIGINT PRIMARY KEY,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
sale_date DATE NOT NULL,
store_id BIGINT NOT NULL
);
步骤2：添加索引
-- 添加分析常用索引
CREATE INDEX idx_sale_date ON fgedudb.fgedu_sales (sale_date);
CREATE INDEX idx_store_id ON fgedudb.fgedu_sales (store_id);
CREATE INDEX idx_product_id ON fgedudb.fgedu_sales (product_id);
CREATE INDEX idx_sale_date_store ON fgedudb.fgedu_sales (sale_date, store_id);
步骤3：优化分析查询
-- 优化前：可能全表扫描
SELECT store_id, SUM(quantity * price) AS total_sales FROM fgedudb.fgedu_sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY store_id ORDER BY total_sales DESC;
-- 优化后：使用索引
EXPLAIN SELECT store_id, SUM(quantity * price) AS total_sales FROM fgedudb.fgedu_sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY store_id ORDER BY total_sales DESC;
+--------------------------------+----------+-----------+------------------------+---------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+------------------------+---------------------------------------+ | Sort_8 | 100.00 | root | | sort by:total_sales DESC | | └─HashAgg_12 | 100.00 | root | | group by:store_id, funcs:sum(mul(quantity, price)) | | └─TableReader_13 | 10000.00 | root | | data:Selection_11 | | └─Selection_11 | 10000.00 | cop[tikv] | | where:sale_date between ... | | └─IndexScan_10 | 10000.00 | cop[tikv] | table:fgedu_sales, index:idx_sale_date(sale_date) | range:['2024-01-01','2024-01-31'], keep order:false | +--------------------------------+----------+-----------+------------------------+---------------------------------------+
步骤4：使用物化视图优化
-- 创建物化视图
CREATE MATERIALIZED VIEW fgedudb.mv_daily_sales AS
SELECT
sale_date,
store_id,
SUM(quantity * price) AS total_sales,
COUNT(*) AS order_count
FROM fgedudb.fgedu_sales
GROUP BY sale_date, store_id;
-- 查询物化视图
SELECT * FROM fgedudb.mv_daily_sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY total_sales DESC;
五、经验总结
5.1 SQL优化最佳实践
- 使用EXPLAIN分析执行计划：了解SQL语句的执行方式
- 创建合适的索引：根据查询模式创建索引
- 避免全表扫描：确保查询条件能够使用索引
- 优化连接操作：确保连接列有索引
- 减少数据传输：只查询需要的列
- 合理使用LIMIT：限制结果集大小
- 避免复杂子查询：考虑使用JOIN替代子查询
- 优化排序和分组：使用索引排序，避免临时表
5.2 性能优化技巧
- 批量操作：使用批量插入和更新
- 使用PreparedStatement：减少SQL解析开销
- 避免频繁提交：合理使用事务
- 缓存查询结果：对于频繁查询的数据
- 定期分析表：更新统计信息
- 分区表：对于大表使用分区
- 使用物化视图：对于复杂分析查询
5.3 常见问题与解决方案
| 问题 | 原因 | 解决方案 |
|---|---|---|
| 全表扫描 | 查询条件未使用索引 | 创建合适的索引，修改查询条件 |
| 索引失效 | 使用了函数或表达式 | 避免在索引列上使用函数，修改查询方式 |
| 连接效率低 | 连接列无索引 | 在连接列上创建索引 |
| 排序开销大 | 大结果集排序 | 使用索引排序，限制结果集大小 |
| 子查询性能差 | 复杂子查询 | 使用JOIN替代子查询 |
5.4 监控与调优
- 慢查询日志：启用慢查询日志，分析慢SQL
- 执行计划分析：定期分析执行计划
- 索引使用情况：监控索引使用率
- 系统资源监控：监控CPU、内存、磁盘使用情况
- TiDB Dashboard：使用TiDB Dashboard进行性能分析
更多视频教程www.fgedu.net.cn
© 2024 TiDB数据库培训文档
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息