定位低效SQL语句
：通过慢查日志等工具定位执行效率低的SQL语句，这是优化的第一步。
执行计划分析
：使用EXPLAIN分析SQL的执行计划，重点关注type、rows、filtered、extra等参数。
- type 连接类型 system > const > eq_reg > ref > range > index > ALL
- rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。不是结果集里的行数。
- filtered 显示了通过条件过滤出的行数的百分比估计值
- 表示存储引擎返回数据后，经过服务器层进一步筛选后 剩余有效数据的预估百分比
- filtered = 100 ，表示所有从存储引擎读取的行均满足查询条件，无需额外过滤（理想状态）
- filtered=18，表明仅 18% 的数据通过查询条件筛选，剩余 82% 被丢弃，存在优化空间
- extra 解析查询的额外信息
性能分析
：通过SHOW PROFILE对SQL进行性能分析，找出瓶颈所在。
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from user;
+----+----------+------+--------------------+-------------+---------+
| id | name | age | id_no | phone | address |
+----+----------+------+--------------------+-------------+---------+
| 1 | zhangsan | 19 | 341223199008212345 | 18365265123 | nanjing |
| 2 | lisi | 17 | 341223199608212346 | 18365265124 | nanjing |
| 3 | wangwu | 22 | 341223199008212347 | 18365265125 | nanjing |
+----+----------+------+--------------------+-------------+---------+
3 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00030400 | select * from user |
+----------+------------+--------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile for query 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000065 |
| Executing hook on transaction | 0.000004 |
| starting | 0.000006 |
| checking permissions | 0.000003 |
| Opening tables | 0.000025 |
| init | 0.000003 |
| System lock | 0.000006 |
| optimizing | 0.000003 |
| statistics | 0.000017 |
| preparing | 0.000019 |
| executing | 0.000064 |
| end | 0.000003 |
| query end | 0.000002 |
| waiting for handler commit | 0.000026 |
| closing tables | 0.000008 |
| freeing items | 0.000046 |
| cleaning up | 0.000007 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
SHOW PROFILE 的输出中，常见的阶段包括：
- starting：SQL 语句开始执行。
- checking permissions：检查权限。
- Opening tables：打开表。
- init：初始化。
- System lock：系统锁。
- optimizing：优化查询。
- statistics：统计信息。
- preparing：准备执行。
- executing：执行查询。
- Sending data：发送数据。
- end：结束。
- query end：查询结束。
- closing tables：关闭表。
- freeing items：释放资源。
- cleaning up：清理。
在 MySQL 8.0 中，推荐使用 Performance Schema 进行更全面的性能分析。
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 984 rows affected (0.01 sec)
Rows matched: 1253 Changed: 984 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
Query OK, 9 rows affected (0.00 sec)
Rows matched: 16 Changed: 9 Warnings: 0
mysql> select * from user;
+----+----------+------+--------------------+-------------+---------+
| id | name | age | id_no | phone | address |
+----+----------+------+--------------------+-------------+---------+
| 1 | zhangsan | 19 | 341223199008212345 | 18365265123 | nanjing |
| 2 | lisi | 17 | 341223199608212346 | 18365265124 | nanjing |
| 3 | wangwu | 22 | 341223199008212347 | 18365265125 | nanjing |
+----+----------+------+--------------------+-------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM performance_schema.events_statements_history_long order by TIMER_START desc limit 1\G;
*************************** 1. row ***************************
THREAD_ID: 50
EVENT_ID: 337
END_EVENT_ID: 399
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:105
TIMER_START: 887238175800000
TIMER_END: 887238426600000
TIMER_WAIT: 250800000
LOCK_TIME: 2000000
SQL_TEXT: select * from user
DIGEST: 4681397545a73eb8e3b6bb1e918323c06d906d4114619ef72013aa442fced2ba
DIGEST_TEXT: SELECT * FROM SYSTEM_USER
CURRENT_SCHEMA: mcc
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 3
ROWS_EXAMINED: 3
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 26
CPU_TIME: 0
MAX_CONTROLLED_MEMORY: 1094784
MAX_TOTAL_MEMORY: 1692963
EXECUTION_ENGINE: PRIMARY
1 row in set (0.00 sec)
ERROR:
No query specified
- 启用 profiling：在分析 SQL 性能时启用 profiling。
- 分析耗时阶段：重点关注耗时较长的阶段，优化相关操作。
- 在 MySQL 8.0 中，使用 Performance Schema 替代 SHOW PROFILE。
Trace分析
：使用TRACE工具进一步分析SQL执行的详细过程。
SET optimizer_trace="enabled=on";
select * from user;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+----------+------+--------------------+-------------+---------+
| id | name | age | id_no | phone | address |
+----+----------+------+--------------------+-------------+---------+
| 1 | zhangsan | 19 | 341223199008212345 | 18365265123 | nanjing |
| 2 | lisi | 17 | 341223199608212346 | 18365265124 | nanjing |
| 3 | wangwu | 22 | 341223199008212347 | 18365265125 | nanjing |
+----+----------+------+--------------------+-------------+---------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
QUERY: select * from user
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`id_no` AS `id_no`,`user`.`phone` AS `phone`,`user`.`address` AS `address` from `user`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"table": "`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"table": "`user`",
"table_scan": {
"rows": 2,
"cost": 0.25
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 2,
"access_type": "scan",
"resulting_rows": 2,
"cost": 0.45,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 2,
"cost_for_plan": 0.45,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`user`",
"attached": null
}
]
}
},
{
"finalizing_table_conditions": [
]
},
{
"refine_plan": [
{
"table": "`user`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
ERROR:
No query specified
总结
确定问题并采取措施：根据以上分析确定问题所在，并采取相应的优化措施。
### SQL优化十大案例
- 最左匹配原则：在复合索引中，MySQL会从左到右匹配条件，未携带最左字段的条件会导致索引失效。
- 隐式转换问题：字符与数字的隐式转换可能导致索引失效，应确保类型一致。
- 大分页问题：对于大分页查询，可以通过延迟关联或携带上次查询的最后一条记录的值来优化。
- IN + ORDER BY问题：当IN条件与ORDER BY结合时，需要确保索引能够覆盖查询条件。
- 范围查询阻断：范围查询会阻断后续字段使用索引，应避免在复合索引中使用范围查询。
- 不等于和不包含：这些条件可能导致无法使用索引，可以考虑使用ICP（Index Condition Pushdown）。
- 优化器不使用索引：有时候优化器可能不选择使用索引，可以通过调整查询或强制索引来优化。
- 复杂查询优化：复杂查询往往涉及多表关联和子查询，优化这类查询需要综合考虑索引和查询逻辑。
- ASC和DESC混用：在同一个查询中混用ASC和DESC可能会导致索引失效，应避免这种情况。
- 大数据量处理：对于大数据量的表，优化索引和查询逻辑尤为重要，可以考虑分区表和物化视图等技术。
