数据库的性能取决于许多因素，主要是查询，schema，配置项，硬件。
- 检查查询计划：EXPLAIN FORMAT = TREE
- 分析查询执行：EXPLAIN ANALYZE
- 了解计划选择：OPTIMIZER TRACE
执行计划
EXPLAIN
MySQL 执行查询的方式是影响数据库性能的主要因素之一。可以使用 Explain命令来验证MySQL的执行计划。
从 MySQL 5.7.2 开始，可以使用 EXPLAIN 命令来检查当前在其他会话中执行的查询。
可以使用 EXPLAIN FORMAT = JSON 命令来输出详细信息。
EXPLAIN 执行计划提供了关于 查询优化器 如何执行查询的信息，只需将 EXPLAIN 关键字作为 SQL 语句的前缀执行即可。详细可参考官方文档 8.8.2 EXPLAIN Output Format。
mysql> EXPLAIN SELECT
*
FROM
film f
JOIN film_actor fa ON fa.film_id = f.film_id
JOIN actor a ON fa.actor_id = a.actor_id
WHERE
a.first_name = 'BETTE';
+----+-------------+-------+------------+--------+------------------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+------------------------+---------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 200 | 10.00 | Using where |
| 1 | SIMPLE | fa | NULL | ref | PRIMARY,idx_fk_film_id | PRIMARY | 2 | sakila.a.actor_id | 27 | 100.00 | NULL |
| 1 | SIMPLE | f | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.fa.film_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+------------------------+---------+---------+-------------------+------+----------+-------------+
3 rows in set (0.08 sec)
EXPLAIN FORMAT
EXPLAIN FORMAT=JSON
以 JSON 格式输出 EXPLAIN 计划，能提供有关查询执行情况的完整信息：
mysql> EXPLAIN FORMAT=JSON SELECT
*
FROM
film f
JOIN film_actor fa ON fa.film_id = f.film_id
JOIN actor a ON fa.actor_id = a.actor_id
WHERE
a.first_name = 'BETTE';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "696.91"
},
"nested_loop": [
{
"table": {
"table_name": "a",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 200,
"rows_produced_per_join": 20,
"filtered": "10.00",
"cost_info": {
"read_cost": "19.00",
"eval_cost": "2.00",
"prefix_cost": "21.00",
"data_read_per_join": "5K"
},
"used_columns": [
"actor_id",
"first_name",
"last_name",
"last_update",
"status"
],
"attached_condition": "(`sakila`.`a`.`first_name` = 'BETTE')"
}
},
{
"table": {
"table_name": "fa",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"idx_fk_film_id"
],
"key": "PRIMARY",
"used_key_parts": [
"actor_id"
],
"key_length": "2",
"ref": [
"sakila.a.actor_id"
],
"rows_examined_per_scan": 27,
"rows_produced_per_join": 546,
"filtered": "100.00",
"cost_info": {
"read_cost": "20.47",
"eval_cost": "54.62",
"prefix_cost": "96.09",
"data_read_per_join": "8K"
},
"used_columns": [
"actor_id",
"film_id",
"last_update"
]
}
},
{
"table": {
"table_name": "f",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"film_id"
],
"key_length": "2",
"ref": [
"sakila.fa.film_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 546,
"filtered": "100.00",
"cost_info": {
"read_cost": "546.20",
"eval_cost": "54.62",
"prefix_cost": "696.91",
"data_read_per_join": "426K"
},
"used_columns": [
"film_id",
"title",
"description",
"release_year",
"language_id",
"original_language_id",
"rental_duration",
"rental_rate",
"length",
"replacement_cost",
"rating",
"special_features",
"last_update"
]
}
}
]
}
}
1 row in set (0.11 sec)
EXPLAIN FORMAT=TREE
mysql> EXPLAIN FORMAT=TREE SELECT
*
FROM
film f
JOIN film_actor fa ON fa.film_id = f.film_id
JOIN actor a ON fa.actor_id = a.actor_id
WHERE
a.first_name = 'BETTE';
| -> Nested loop inner join (cost=285.11 rows=546)
-> Nested loop inner join (cost=93.94 rows=546)
-> Filter: (a.first_name = 'BETTE') (cost=20.25 rows=20)
-> Table scan on a (cost=20.25 rows=200)
-> Index lookup on fa using PRIMARY (actor_id=a.actor_id) (cost=1.09 rows=27)
-> Single-row index lookup on f using PRIMARY (film_id=fa.film_id) (cost=0.25 rows=1)
|
1 row in set (0.06 sec)
EXPLAIN ANALYZE
mysql> EXPLAIN ANALYZE SELECT
*
FROM
film f
JOIN film_actor fa ON fa.film_id = f.film_id
JOIN actor a ON fa.actor_id = a.actor_id
WHERE
a.first_name = 'BETTE';
| -> Nested loop inner join (cost=285.11 rows=546) (actual time=0.101..0.244 rows=20 loops=1)
-> Nested loop inner join (cost=93.94 rows=546) (actual time=0.087..0.164 rows=20 loops=1)
-> Filter: (a.first_name = 'BETTE') (cost=20.25 rows=20) (actual time=0.067..0.136 rows=1 loops=1)
-> Table scan on a (cost=20.25 rows=200) (actual time=0.061..0.119 rows=200 loops=1)
-> Index lookup on fa using PRIMARY (actor_id=a.actor_id) (cost=1.09 rows=27) (actual time=0.019..0.025 rows=20 loops=1)
-> Single-row index lookup on f using PRIMARY (film_id=fa.film_id) (cost=0.25 rows=1) (actual time=0.004..0.004 rows=1 loops=20)
|
1 row in set (0.06 sec)
EXPLAIN 会话
可以为正在运行的会话执行 Explain 计划，但要指定会话ID（connection ID）。
-- 获取当前连接的会话ID
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 6965 |
+-----------------+
1 row in set (0.00 sec)
-- 会话执行计划只支持 SELECT/UPDATE/INSERT/DELETE/REPLACE
mysql> explain for connection 6965;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE
mysql> explain for connection 6968;
Query OK, 0 rows affected (0.00 sec)
-- JSON格式输出
mysql> explain format=json for connection 6968;
Query OK, 0 rows affected (0.00 sec)
基准查询
EXPLAIN 给出的是 SQL 的执行计划分析结果，并没有明确给出 SQL 的执行耗时。
如果要评估某一条SQL的执行耗时，使用 pager grep rows命令，只返回结果行数和耗时，精确到 100 ms。
mysql> pager grep rows;
PAGER set to 'grep rows'
mysql> SELECT * FROM film f JOIN film_actor fa ON fa.film_id = f.film_id JOIN actor a ON fa.actor_id = a.actor_id WHERE a.first_name = 'BETTE';
20 rows in set (0.00 sec)
模拟负载
可以使用 mysqlslap 模拟客户端负载，并在多个迭代中并行运行某条SQL，可以得出平均耗时。
查看优化后的SQL
查看经过优化器优化后真正执行的 SQL。
https://blog.csdn.net/wangjun5159/article/details/51227831
https://blog.csdn.net/weixin_38004638/article/details/106427205
更多内容请访问：IT源点
注意：本文归作者所有，未经作者允许，不得转载
