2. 首页 > MariaDB教程 > 正文

MariaDB教程FG029-MariaDB执行计划分析与SQL深度优化实战

教程整理：风哥教程  |  更新时间：2026-01-01  |  教程分类：MariaDB教程  |  文档学习：24
内容简介：本文风哥教程参考MariaDB官方文档MariaDB Server、EXPLAIN、Query Optimization等章节，详细讲解MariaDB执行计划分析与SQL深度优化，包括执行计划解读、SQL优化技术和性能调优。

Part01-基础概念与理论知识

1.1 执行计划基本概念

执行计划是MariaDB查询优化器生成的查询执行方案，它描述了如何执行SQL语句，包括使用哪些索引、如何连接表、如何排序等。执行计划是SQL优化的基础，通过分析执行计划可以找出查询性能瓶颈。

1.2 执行计划解读

  • id：查询标识符
  • select_type：查询类型（SIMPLE、PRIMARY、SUBQUERY等）
  • table：表名
  • partitions：分区信息
  • type：访问类型（ALL、index、range、ref、eq_ref、const、system）
  • possible_keys：可能使用的索引
  • key：实际使用的索引
  • key_len：索引长度
  • ref：参考的列或常量
  • rows：估计的行数
  • filtered：过滤后的行数百分比
  • Extra：额外信息（Using where、Using index、Using temporary等）

1.3 SQL优化基本原理

  • 减少数据扫描量：只查询必要的列和行
  • 使用索引：提高查询速度
  • 优化连接方式：选择合适的连接算法
  • 减少排序和临时表：避免不必要的排序和临时表
  • 优化子查询：合理使用子查询
  • 避免全表扫描：使用索引覆盖查询
更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 SQL优化策略

风哥提示：合理的SQL优化策略可以显著提高系统性能。
  • 查询优化：
    • 使用EXPLAIN分析执行计划
    • 避免SELECT *
    • 使用LIMIT限制返回行数
    • 合理使用WHERE条件
    • 避免使用函数在WHERE子句中
  • 索引优化：
    • 为经常查询的列创建索引
    • 使用复合索引
    • 避免过度索引
    • 定期维护索引
  • 表结构优化：
    • 合理设计表结构
    • 使用合适的数据类型
    • 避免使用NULL值
    • 合理分表

2.2 索引优化建议

  • 选择合适的索引列：
    • 经常用于WHERE条件的列
    • 经常用于JOIN条件的列
    • 经常用于ORDER BY和GROUP BY的列
  • 复合索引设计：
    • 遵循最左前缀原则
    • 将选择性高的列放在前面
    • 避免包含过多列
  • 索引维护：
    • 定期分析表
    • 重建碎片化索引
    • 删除不必要的索引

2.3 生产环境最佳实践

  • 在开发环境充分测试SQL语句
  • 监控SQL执行性能
  • 使用慢查询日志
  • 定期优化数据库
  • 使用连接池
学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 执行计划分析

更多学习教程公众号风哥教程itpux_com

# 基本EXPLAIN命令
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users WHERE age > 30;
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 33.33 | Using where |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+
# 详细EXPLAIN命令
MariaDB [fgedudb]> EXPLAIN EXTENDED SELECT * FROM fgedu_users WHERE age > 30;
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+———-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | Warning |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+———-+
| 1 | SIMPLE | fgedu_users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 33.33 | Using where | Using where |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+———-+
# JSON格式执行计划
MariaDB [fgedudb]> EXPLAIN FORMAT=JSON SELECT * FROM fgedu_users WHERE age > 30;
{
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “101.00”
},
“table”: {
“table_name”: “fgedu_users”,
“access_type”: “ALL”,
“rows_examined_per_scan”: 1000,
“rows_produced_per_join”: 333,
“filtered”: “33.33”,
“cost_info”: {
“read_cost”: “70.00”,
“eval_cost”: “31.00”,
“prefix_cost”: “101.00”,
“data_read_per_join”: “33K”
},
“used_columns”: [
“id”,
“name”,
“email”,
“age”,
“created_at”
],
“attached_condition”: “(`fgedudb`.`fgedu_users`.`age` > 30)”
}
}
}
# 分析JOIN查询
MariaDB [fgedudb]> EXPLAIN SELECT u.name, o.total_amount
-> FROM fgedu_users u
-> JOIN fgedu_orders o ON u.id = o.user_id
-> WHERE u.age > 30;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 33.33 | Using where |
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 2000 | 100.00 | Using where;
Using join buffer (flat, BNL join) |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

3.2 SQL优化技术

# 1. 为age列创建索引
MariaDB [fgedudb]> CREATE INDEX idx_age ON fgedu_users (age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次分析执行计划
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users WHERE age > 30;
+—-+————-+————-+————+——-+—————+———+———+——+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——-+—————+———+———+——+——+———-+———————–+
| 1 | SIMPLE | fgedu_users | NULL | range | idx_age | idx_age | 5 | NULL | 333 | 100.00 | Using index condition |
+—-+————-+————-+————+——-+—————+———+———+——+——+———-+———————–+
# 2. 优化JOIN查询
MariaDB [fgedudb]> CREATE INDEX idx_user_id ON fgedu_orders (user_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次分析JOIN查询
MariaDB [fgedudb]> EXPLAIN SELECT u.name, o.total_amount
-> FROM fgedu_users u
-> JOIN fgedu_orders o ON u.id = o.user_id
-> WHERE u.age > 30;
+—-+————-+——-+————+——-+—————+———–+———+————-+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——-+—————+———–+———+————-+——+———-+———————–+
| 1 | SIMPLE | u | NULL | range | PRIMARY,idx_age | idx_age | 5 | NULL | 333 | 100.00 | Using index condition |
| 1 | SIMPLE | o | NULL | ref | idx_user_id | idx_user_id | 5 | fgedudb.u.id | 2 | 100.00 | NULL |
+—-+————-+——-+————+——-+—————+———–+———+————-+——+———-+———————–+
# 3. 优化子查询
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users
-> WHERE id IN (SELECT user_id FROM fgedu_orders WHERE total_amount > 1000);
+—-+————-+————-+————+——+—————+————-+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————+————-+———+——+——+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 100.00 | Using where |
| 1 | SIMPLE | fgedu_orders | NULL | index | idx_user_id | idx_user_id | 5 | NULL | 2000 | 5.00 | Using where;
Using index;
Semi-join |
+—-+————-+————-+————+——+—————+————-+———+——+——+———-+————-+
# 优化为JOIN查询
MariaDB [fgedudb]> EXPLAIN SELECT DISTINCT u.* FROM fgedu_users u
-> JOIN fgedu_orders o ON u.id = o.user_id
-> WHERE o.total_amount > 1000;
+—-+————-+——-+————+——-+—————+———–+———+————-+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——-+—————+———–+———+————-+——+———-+———————–+
| 1 | SIMPLE | o | NULL | ALL | idx_user_id | NULL | NULL | NULL | 2000 | 5.00 | Using where;
Using temporary |
| 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | fgedudb.o.user_id | 1 | 100.00 | Using index |
+—-+————-+——-+————+——-+—————+———–+———+————-+——+———-+———————–+
# 4. 优化排序
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users ORDER BY created_at DESC LIMIT 10;
+—-+————-+————-+————+——+—————+——+———+——+——+———-+—————-+-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+—————-+-
| 1 | SIMPLE | fgedu_users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using filesort |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+—————-+-
# 为created_at列创建索引
MariaDB [fgedudb]> CREATE INDEX idx_created_at ON fgedu_users (created_at);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次分析排序查询
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users ORDER BY created_at DESC LIMIT 10;
+—-+————-+————-+————+——-+—————+—————-+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——-+—————+—————-+———+——+——+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | index | NULL | idx_created_at | 5 | NULL | 10 | 100.00 | Using index |
+—-+————-+————-+————+——-+—————+—————-+———+——+——+———-+————-+

3.3 性能监控与调优

# 启用慢查询日志
MariaDB [(none)]> SET GLOBAL slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET GLOBAL slow_query_log_file = ‘/mariadb/fgdata/slow-query.log’;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
# 查看慢查询日志
$ tail -n 10 /mariadb/fgdata/slow-query.log
# 使用pt-query-digest分析慢查询
$ pt-query-digest /mariadb/fgdata/slow-query.log
# 查看数据库状态
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Queries’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Queries | 10000 |
+—————+——-+
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Slow_queries’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Slow_queries | 10 |
+—————+——-+
# 查看连接状态
MariaDB [(none)]> SHOW PROCESSLIST;
+—-+——+———–+——+———+——+————————+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+————————+——————+
| 1 | root | fgedu.localhost | NULL | Query | 0 | executing | SHOW PROCESSLIST |
+—-+——+———–+——+———+——+————————+——————+
# 查看表状态
MariaDB [fgedudb]> SHOW TABLE STATUS WHERE Name = ‘fgedu_users’\G
*************************** 1. row ***************************
Name: fgedu_users
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1000
Avg_row_length: 164
Data_length: 163840
Max_data_length: 0
Index_length: 163840
Data_free: 0
Auto_increment: 1001
Create_time: 2023-01-01 12:00:00
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
# 分析表
MariaDB [fgedudb]> ANALYZE TABLE fgedu_users;
+———————+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———————+———+———-+———-+
| fgedudb.fgedu_users | analyze | status | OK |
+———————+———+———-+———-+
# 优化表
MariaDB [fgedudb]> OPTIMIZE TABLE fgedu_users;
+———————+———-+———-+——————————————————————-+
| Table | Op | Msg_type | Msg_text |
+———————+———-+———-+——————————————————————-+
| fgedudb.fgedu_users | optimize | status | Table is already up to date |
+———————+———-+———-+——————————————————————-+
学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 执行计划分析实战

# 场景：电商系统商品查询
MariaDB [fgedudb]> EXPLAIN SELECT p.id, p.name, p.price, c.name AS category_name
-> FROM fgedu_products p
-> JOIN fgedu_categories c ON p.category_id = c.id
-> WHERE p.price > 1000 AND p.stock > 0
-> ORDER BY p.created_at DESC
-> LIMIT 10;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where;
Using temporary;
Using filesort |
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | Using where;
Using join buffer (flat, BNL join) |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
# 优化步骤1：为price和stock创建索引
MariaDB [fgedudb]> CREATE INDEX idx_price_stock ON fgedu_products (price, stock);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 优化步骤2：为category_id创建索引
MariaDB [fgedudb]> CREATE INDEX idx_category_id ON fgedu_products (category_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 优化步骤3：为created_at创建索引
MariaDB [fgedudb]> CREATE INDEX idx_created_at ON fgedu_products (created_at);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次分析执行计划
MariaDB [fgedudb]> EXPLAIN SELECT p.id, p.name, p.price, c.name AS category_name
-> FROM fgedu_products p
-> JOIN fgedu_categories c ON p.category_id = c.id
-> WHERE p.price > 1000 AND p.stock > 0
-> ORDER BY p.created_at DESC
-> LIMIT 10;
+—-+————-+——-+————+——-+————————+—————-+———+————-+——+———-+———————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——-+————————+—————-+———+————-+——+———-+———————————-+
| 1 | SIMPLE | p | NULL | range | idx_price_stock,idx_category_id | idx_price_stock | 10 | NULL | 100 | 100.00 | Using index condition;
Using temporary;
Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | fgedudb.p.category_id | 1 | 100.00 | NULL |
+—-+————-+——-+————+——-+————————+—————-+———+————-+——+———-+———————————-+
# 优化步骤4：创建复合索引包含排序字段
MariaDB [fgedudb]> CREATE INDEX idx_price_stock_created_at ON fgedu_products (price, stock, created_at);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次分析执行计划
MariaDB [fgedudb]> EXPLAIN SELECT p.id, p.name, p.price, c.name AS category_name
-> FROM fgedu_products p
-> JOIN fgedu_categories c ON p.category_id = c.id
-> WHERE p.price > 1000 AND p.stock > 0
-> ORDER BY p.created_at DESC
-> LIMIT 10;
+—-+————-+——-+————+——-+—————————————–+————————–+———+————-+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——-+—————————————–+————————–+———+————-+——+———-+———————–+
| 1 | SIMPLE | p | NULL | range | idx_price_stock,idx_category_id,idx_price_stock_created_at | idx_price_stock_created_at | 15 | NULL | 100 | 100.00 | Using index condition |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | fgedudb.p.category_id | 1 | 100.00 | NULL |
+—-+————-+——-+————+——-+—————————————–+————————–+———+————-+——+———-+———————–+

4.2 SQL优化实战

# 场景：用户查询优化
# 原SQL
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users
-> WHERE name LIKE ‘%John%’ AND age > 25;
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | ALL | idx_age | NULL | NULL | NULL | 1000 | 1.67 | Using where |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+————-+
# 优化1：使用覆盖索引
MariaDB [fgedudb]> CREATE INDEX idx_name_age ON fgedu_users (name, age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 分析优化后的SQL
MariaDB [fgedudb]> EXPLAIN SELECT name, age FROM fgedu_users
-> WHERE name LIKE ‘%John%’ AND age > 25;
+—-+————-+————-+————+——+—————+————+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————+————+———+——+——+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | ALL | idx_name_age | NULL | NULL | NULL | 1000 | 1.67 | Using where |
+—-+————-+————-+————+——+—————+————+———+——+——+———-+————-+
# 注意：LIKE ‘%John%’无法使用索引
# 优化2：使用全文索引
MariaDB [fgedudb]> ALTER TABLE fgedu_users ADD FULLTEXT INDEX ft_name (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 使用全文索引查询
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_users
-> WHERE MATCH(name) AGAINST(‘John’ IN BOOLEAN MODE) AND age > 25;
+—-+————-+————-+————+———-+—————+———+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+———-+—————+———+———+——+——+———-+————-+
| 1 | SIMPLE | fgedu_users | NULL | fulltext | ft_name,idx_age | ft_name | 0 | NULL | 1 | 100.00 | Using where |
+—-+————-+————-+————+———-+—————+———+———+——+——+———-+————-+
# 场景：聚合查询优化
# 原SQL
MariaDB [fgedudb]> EXPLAIN SELECT category_id, COUNT(*) AS product_count
-> FROM fgedu_products
-> GROUP BY category_id;
+—-+————-+————-+————+——+—————+——+———+——+——+———-+—————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+—————–+
| 1 | SIMPLE | fgedu_products | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using temporary |
+—-+————-+————-+————+——+—————+——+———+——+——+———-+—————–+
# 优化：为category_id创建索引
MariaDB [fgedudb]> CREATE INDEX idx_category_id ON fgedu_products (category_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 分析优化后的SQL
MariaDB [fgedudb]> EXPLAIN SELECT category_id, COUNT(*) AS product_count
-> FROM fgedu_products
-> GROUP BY category_id;
+—-+————-+————-+————+——-+—————+—————+———+——+——+———-+————————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——-+—————+—————+———+——+——+———-+————————–+
| 1 | SIMPLE | fgedu_products | NULL | index | idx_category_id | idx_category_id | 5 | NULL | 1000 | 100.00 | Using index;
Using temporary |
+—-+————-+————-+————+——-+—————+—————+———+——+——+———-+————————–+

4.3 复杂查询优化实战

# 场景：复杂报表查询
# 原SQL
MariaDB [fgedudb]> EXPLAIN SELECT
-> c.name AS category_name,
-> COUNT(p.id) AS product_count,
-> SUM(p.price) AS total_price,
-> AVG(p.price) AS avg_price,
-> MAX(p.price) AS max_price,
-> MIN(p.price) AS min_price
-> FROM fgedu_categories c
-> LEFT JOIN fgedu_products p ON c.id = p.category_id
-> WHERE p.created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
-> GROUP BY c.id
-> ORDER BY product_count DESC;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | Using temporary;
Using filesort |
| 1 | SIMPLE | p | NULL | ALL | idx_category_id | NULL | NULL | NULL | 1000 | 10.00 | Using where;
Using join buffer (flat, BNL join) |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
# 优化步骤1：为created_at创建索引
MariaDB [fgedudb]> CREATE INDEX idx_created_at ON fgedu_products (created_at);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 优化步骤2：创建复合索引
MariaDB [fgedudb]> CREATE INDEX idx_category_created_at ON fgedu_products (category_id, created_at);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次分析执行计划
MariaDB [fgedudb]> EXPLAIN SELECT
-> c.name AS category_name,
-> COUNT(p.id) AS product_count,
-> SUM(p.price) AS total_price,
-> AVG(p.price) AS avg_price,
-> MAX(p.price) AS max_price,
-> MIN(p.price) AS min_price
-> FROM fgedu_categories c
-> LEFT JOIN fgedu_products p ON c.id = p.category_id
-> WHERE p.created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
-> GROUP BY c.id
-> ORDER BY product_count DESC;
+—-+————-+——-+————+——-+———————————-+————————–+———+————-+——+———-+———————————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——-+———————————-+————————–+———+————-+——+———-+———————————————-+
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | Using temporary;
Using filesort |
| 1 | SIMPLE | p | NULL | range | idx_category_id,idx_created_at,idx_category_created_at | idx_category_created_at | 10 | NULL | 100 | 100.00 | Using index condition;
Using where;
Using join buffer (incremental, BNL join) |
+—-+————-+——-+————+——-+———————————-+————————–+———+————-+——+———-+———————————————-+
# 优化步骤3：使用子查询减少数据量
MariaDB [fgedudb]> EXPLAIN SELECT
-> c.name AS category_name,
-> p.product_count,
-> p.total_price,
-> p.avg_price,
-> p.max_price,
-> p.min_price
-> FROM fgedu_categories c
-> LEFT JOIN (
-> SELECT
-> category_id,
-> COUNT(id) AS product_count,
-> SUM(price) AS total_price,
-> AVG(price) AS avg_price,
-> MAX(price) AS max_price,
-> MIN(price) AS min_price
-> FROM fgedu_products
-> WHERE created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
-> GROUP BY category_id
-> ) p ON c.id = p.category_id
-> ORDER BY p.product_count DESC;
+—-+————-+————-+————+——-+———————————-+————————–+———+——+——+———-+————————————————————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+————+——-+———————————-+————————–+———+——+——+———-+————————————————————–+
| 1 | PRIMARY | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | Using temporary;
Using filesort |
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 2 | DERIVED | fgedu_products | NULL | range | idx_category_id,idx_created_at,idx_category_created_at | idx_category_created_at | 10 | NULL | 100 | 100.00 | Using index condition;
Using where;
Using temporary;
Using filesort |
+—-+————-+————-+————+——-+———————————-+————————–+———+——+——+———-+————————————————————–+
风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 SQL优化经验

在实际生产环境中，SQL优化需要注意以下几点：

  • 理解业务需求：根据业务需求优化SQL语句
  • 分析执行计划：使用EXPLAIN分析执行计划
  • 合理使用索引：为频繁查询的列创建索引
  • 优化查询结构：避免复杂的子查询和连接
  • 定期维护数据库：分析表、优化表、重建索引

5.2 常见问题与解决方案

  • 全表扫描：
    • 问题：查询性能慢
    • 解决方案：为查询条件创建索引
  • 索引失效：
    • 问题：索引未被使用
    • 解决方案：检查索引是否正确创建，避免使用函数在WHERE子句中
  • 临时表和文件排序：
    • 问题：查询速度慢
    • 解决方案：创建合适的索引，优化排序操作
  • 连接查询性能差：
    • 问题：JOIN查询速度慢
    • 解决方案：为连接列创建索引，优化连接顺序

5.3 生产故障案例分析

某电商系统在大促销期间出现SQL性能问题，经过分析发现：

  • 问题原因：
    • 大量慢查询导致数据库负载过高
    • 缺少必要的索引
    • SQL语句编写不合理
    • 数据库配置不当
  • 解决方案：
    • 为频繁查询的列创建索引
    • 优化SQL语句，减少复杂查询
    • 调整数据库配置，增加缓冲区大小
    • 使用缓存减少数据库访问
    • 实施读写分离
  • 效果：系统响应时间从5秒减少到0.5秒，能够处理10倍于原来的并发请求
from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息