MYSQL执行计划优化与进阶查询

MYSQL统计信息与执行计划优化

optimize_trace

可通过比较mysql在不同的执行计划间是如何选择出最佳的执行计划的，包括多表查询时的扫描顺序（各种排序间的cost比较）、单表的最佳access_path等。

#功能开启
SET SESSION optimizer_trace='enabled=on';

explain sql_query;

#查看具体的优化路径
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

#关键信息
"plan_prefix": [] 
#多表查询时 如果该后缀为空， 表示是执行计划中的第一个表
#如果该后缀中存在表名 表示这是在当前已经确定的顺序下 找寻下一个被驱动表
"best_access_path": {
  "considered_access_paths": [
#对该表 可以考虑使用的访问方法 会标明access_type、rows_scan、cost

Innodb统计信息查询

#Innodb表统计信息
select * from mysql.innodb_table_stats where database_name = '' and table_name = ''; 
#Innodb索引统计信息
select * from mysql.innodb_index_stats where database_name = '' and table_name = '';

mysql> select * from mysql.innodb_table_stats where database_name = 'tpch' and table_name = 'orders';
+---------------+------------+---------------------+-----------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows    | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+-----------+----------------------+--------------------------+
| tpch          | orders     | 2025-01-08 17:16:05 | 134916185 |              1107264 |                   163583 |
+---------------+------------+---------------------+-----------+----------------------+--------------------------+

mysql> select * from mysql.innodb_index_stats where database_name = 'tpch' and table_name = 'orders';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| tpch          | orders     | ORDERS_FK1 | 2025-01-08 17:16:05 | n_diff_pfx01 |   10460612 |          20 | O_CUSTKEY                         |
| tpch          | orders     | ORDERS_FK1 | 2025-01-08 17:16:05 | n_diff_pfx02 |  130404212 |          20 | O_CUSTKEY,O_ORDERKEY              |
| tpch          | orders     | ORDERS_FK1 | 2025-01-08 17:16:05 | n_leaf_pages |     142807 |        NULL | Number of leaf pages in the index |
| tpch          | orders     | ORDERS_FK1 | 2025-01-08 17:16:05 | size         |     163583 |        NULL | Number of pages in the index      |
| tpch          | orders     | PRIMARY    | 2025-01-08 17:16:05 | n_diff_pfx01 |  134916089 |          20 | O_ORDERKEY                        |
| tpch          | orders     | PRIMARY    | 2025-01-08 17:16:05 | n_leaf_pages |    1106323 |        NULL | Number of leaf pages in the index |
| tpch          | orders     | PRIMARY    | 2025-01-08 17:16:05 | size         |    1107264 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

mysql> show keys from orders;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY    |            1 | O_ORDERKEY  | A         |   134916096 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | ORDERS_FK1 |            1 | O_CUSTKEY   | A         |    10460612 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

通过相关统计信息，可以判断mysql在进行执行计划抉择时，使用的信息是否准确，是否是错误的统计信息导致的执行计划错误。多表查询时，尤其关注扇出值faount,通常是在ref或者eq_ref的访问方法下，预估的行数等于records/Cardinality。

相关优化是否开启

#查看相关优化是否开启
select @@optimizer_switch\G

@@optimizer_switch: 
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on, #条件下推
index_condition_pushdown=on, #索引下推


#Innodb索引下推 状态转换
set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

条件下推、索引合并等都是默认开启的。