MySQL 查询优化器 (Query Optimizer) 的使用小结

-- 查看成本常量
select * from mysql.server_cost;
select * from mysql.engine_cost;

-- 主要成本参数:
-- disk_temptable_create_cost: 创建临时表成本(默认20.0)
-- disk_temptable_row_cost: 临时表行读取成本(默认0.5)
-- key_compare_cost: 键比较成本(默认0.05)
-- memory_temptable_create_cost: 内存临时表创建成本(默认1.0)
-- memory_temptable_row_cost: 内存临时表行成本(默认0.1)
-- row_evaluate_cost: 行评估成本(默认0.1)

-- 查看表统计信息
show table status like 'table_name'\g

-- 查看索引统计信息
show index from table_name;

-- 关键统计指标:
-- cardinality: 索引中唯一值的数量(区分度)
-- rows: 表中的行数
-- data_length: 数据文件大小
-- index_length: 索引文件大小

-- 更新统计信息
analyze table table_name;

-- innodb统计信息采样设置
show variables like 'innodb_stats%';

-- innodb_stats_persistent: 持久化统计信息(on/off)
-- innodb_stats_auto_recalc: 自动重新计算统计信息
-- innodb_stats_sample_pages: 采样页数(默认8)

-- 原始sql
select * from t where a = 5 and b = a;

-- 优化后
select * from t where a = 5 and b = 5;

-- 原始sql
select * from t where a > 3 and a > 5;

-- 优化后
select * from t where a > 5;

-- 原始sql
select * from t where (a > 1 and a < 5) or (a > 3 and a < 7);

-- 优化后
select * from t where a > 1 and a < 7;

explain select * from user where age = 25 and name = '张三';

-- possible_keys 显示所有可能使用的索引

-- 表结构
create table user (
    id int primary key,
    age int,
    name varchar(50),
    city varchar(50),
    index idx_age(age),
    index idx_name(name),
    index idx_age_name(age, name)
);

-- 查询1: 优化器会选择 idx_age_name(覆盖索引)
explain select age, name from user where age = 25;

-- 查询2: 如果需要所有字段,可能选择 idx_age(需要回表)
explain select * from user where age = 25;

-- 使用 optimizer_trace 查看详细过程
set optimizer_trace='enabled=on';
select * from user where age = 25;
select * from information_schema.optimizer_trace\g
set optimizer_trace='enabled=off';

-- 三表连接
select * from t1 
join t2 on t1.id = t2.t1_id
join t3 on t2.id = t3.t2_id
where t1.status = 1;

-- 优化器会评估6种连接顺序(3! = 6):
-- t1 → t2 → t3
-- t1 → t3 → t2
-- t2 → t1 → t3
-- t2 → t3 → t1
-- t3 → t1 → t2
-- t3 → t2 → t1

-- 简单嵌套循环(simple nested-loop)
for each row in t1:
    for each row in t2:
        if row matches join condition:
            output row

-- 时间复杂度: o(n * m)

-- 使用索引加速内表查询
for each row in t1:
    use index to find matching rows in t2
    output matched rows

-- 时间复杂度: o(n * log m)

-- 使用join buffer缓存外表数据
-- mysql 8.0+ 使用hash join替代

-- 查看join buffer大小
show variables like 'join_buffer_size';

-- 构建哈希表,性能更好
-- 适用于等值连接

explain format=tree 
select * from t1 join t2 on t1.id = t2.id;
-- 可以看到 "hash join" 字样

-- ✅ 小表驱动大表
select * from small_table t1
join large_table t2 on t1.id = t2.small_id;

-- ✅ 确保join字段有索引
alter table t2 add index idx_small_id(small_id);

-- ✅ 使用straight_join强制连接顺序(谨慎使用)
select * from t1 
straight_join t2 on t1.id = t2.t1_id;

-- 原始sql
select * from t1 
where id in (select t1_id from t2 where status = 1);

-- 优化过程:
-- 1. 先执行子查询,结果存入临时表
-- 2. 临时表加索引
-- 3. 用临时表进行join

-- explain 中看到 "materialized"

-- 原始sql(相关子查询)
select * from t1 
where exists (select 1 from t2 where t2.t1_id = t1.id);

-- 优化后(semi-join)
select t1.* from t1 
semi join t2 on t2.t1_id = t1.id;

-- 原始sql
select * from t1 
where (select count(*) from t2 where t2.t1_id = t1.id) > 5;

-- 优化后
select t1.* from t1
join (
    select t1_id, count(*) as cnt 
    from t2 
    group by t1_id 
    having cnt > 5
) t2 on t1.id = t2.t1_id;

-- 查看子查询优化策略
show variables like 'optimizer_switch';

-- 关键参数:
-- materialization: 子查询物化
-- semijoin: 半连接优化
-- subquery_materialization_cost_based: 基于成本选择

-- ✅ 使用索引排序(index scan)
-- 假设有索引 idx_age(age)
explain select * from user order by age;
-- extra: using index

-- ❌ 使用文件排序(filesort)
explain select * from user order by name;
-- extra: using filesort

-- filesort过程:
-- 1. 根据where条件读取数据
-- 2. 将需要排序的字段放入sort buffer
-- 3. 如果数据量大于sort_buffer_size,使用磁盘临时文件
-- 4. 进行排序(快速排序)

-- ✅ 松散索引扫描(loose index scan)
-- 假设索引 idx_age_city(age, city)
explain select age, count(*) from user group by age;
-- extra: using index for group-by

-- ✅ 紧凑索引扫描(tight index scan)
explain select age, city, count(*) from user 
where age > 20 group by age, city;
-- extra: using where; using index

-- ❌ 临时表分组
explain select city, count(*) from user group by city;
-- extra: using temporary; using filesort

-- 查看排序缓冲区大小
show variables like 'sort_buffer_size';     -- 默认256kb
show variables like 'max_length_for_sort_data'; -- 默认1024

-- 临时表相关
show variables like 'tmp_table_size';       -- 内存临时表大小
show variables like 'max_heap_table_size';  -- 堆表最大值

-- 强制使用某个索引
select * from user force index(idx_age) where age = 25;

-- 建议使用某个索引(优化器可能忽略)
select * from user use index(idx_age) where age = 25;

-- 忽略某个索引
select * from user ignore index(idx_age) where age = 25;

-- mysql 8.0+ 新语法
select /*+ index(user idx_age) */ * from user where age = 25;

-- 强制连接顺序
select * from t1 straight_join t2 on t1.id = t2.t1_id;

-- mysql 8.0+ join提示
select /*+ join_order(t1, t2, t3) */ * 
from t1, t2, t3 
where t1.id = t2.t1_id and t2.id = t3.t2_id;

-- 指定join算法
select /*+ bnl(t1, t2) */ *     -- block nested-loop
from t1 join t2 on t1.id = t2.id;

select /*+ hash_join(t1, t2) */ *  -- hash join(8.0.18+)
from t1 join t2 on t1.id = t2.id;

-- 子查询物化
select /*+ subquery(materialization) */ * 
from t1 where id in (select t1_id from t2);

-- 指定临时表使用内存
select /*+ set_var(internal_tmp_mem_storage_engine=temptable) */ 
    age, count(*) 
from user group by age;

-- 限制执行时间(8.0+)
select /*+ max_execution_time(1000) */ * from user;  -- 1秒超时

-- 查看所有可用提示
select /*+ qb_name(qb1) */ * from t1;

-- 开启优化器跟踪
set optimizer_trace='enabled=on';

-- 执行查询
select * from user where age = 25 and name = '张三';

-- 查看优化过程
select * from information_schema.optimizer_trace\g

-- 关闭跟踪
set optimizer_trace='enabled=off';

{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "/* 展开后的查询 */"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              /* 条件优化过程 */
            }
          },
          {
            "table_dependencies": [
              /* 表依赖关系 */
            ]
          },
          {
            "rows_estimation": [
              /* 行数估算 */
              {
                "table": "user",
                "range_analysis": {
                  "potential_range_indexes": [
                    /* 可能使用的索引 */
                  ],
                  "analyzing_range_alternatives": {
                    /* 分析每个索引的成本 */
                    "range_scan_alternatives": [
                      {
                        "index": "idx_age",
                        "ranges": ["25 <= age <= 25"],
                        "rows": 100,
                        "cost": 121
                      }
                    ]
                  },
                  "chosen_range_access_summary": {
                    /* 选择的索引 */
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_age",
                      "rows": 100,
                      "cost": 121
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              /* 考虑的执行计划 */
              {
                "plan_prefix": [],
                "table": "user",
                "best_access_path": {
                  /* 最佳访问路径 */
                }
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              /* 附加条件到表 */
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        /* 执行阶段 */
      }
    }
  ]
}

-- 传统explain
explain select * from user where age = 25;

-- 格式化输出(mysql 8.0+)
explain format=tree select * from user where age = 25;
explain format=json select * from user where age = 25;

-- 查看实际执行统计(mysql 8.0.18+)
explain analyze select * from user where age = 25;

-- system: 表只有一行(系统表)
-- const: 通过主键或唯一索引查询,最多返回一行
explain select * from user where id = 1;

-- eq_ref: 唯一索引扫描,用于join
explain select * from t1 join t2 on t1.id = t2.id;

-- ref: 非唯一索引扫描
explain select * from user where age = 25;

-- range: 范围扫描
explain select * from user where age between 20 and 30;

-- index: 全索引扫描
explain select id from user;

-- all: 全表扫描(最差)
explain select * from user where name = '张三';  -- name无索引

-- 1. 更新统计信息
analyze table user;

-- 2. 使用索引提示
select * from user force index(idx_age) where age = 25;

-- 3. 调整优化器参数
set optimizer_search_depth = 5;  -- 控制join搜索深度
set optimizer_prune_level = 1;   -- 启用优化器剪枝

-- 4. 修改索引或查询
-- 例如:添加更合适的组合索引

-- 查看join顺序
explain format=tree 
select * from large_table t1
join small_table t2 on t1.id = t2.large_id;

-- 如果顺序不对,使用straight_join
select * from small_table t2
straight_join large_table t1 on t1.id = t2.large_id;

-- ❌ 相关子查询(每行都执行一次)
select * from t1 
where (select count(*) from t2 where t2.t1_id = t1.id) > 5;

-- ✅ 改写为join
select t1.* from t1
join (
    select t1_id from t2 group by t1_id having count(*) > 5
) t2 on t1.id = t2.t1_id;

-- ✅ 或使用exists
select * from t1 
where exists (
    select 1 from t2 
    where t2.t1_id = t1.id 
    group by t1_id 
    having count(*) > 5
);

-- 1. 定期更新统计信息
analyze table user;

-- 2. 优化表(重建索引,回收空间)
optimize table user;

-- 3. 检查表
check table user;

-- 4. 修复表
repair table user;

-- 开启慢查询日志
set global slow_query_log = on;
set global long_query_time = 1;  -- 1秒
set global log_queries_not_using_indexes = on;

-- 查看慢查询日志位置
show variables like 'slow_query_log_file';

-- 分析慢查询日志(使用mysqldumpslow工具)
-- mysqldumpslow -s t -t 10 /path/to/slow.log

-- performance schema
select * from performance_schema.events_statements_summary_by_digest
order by sum_timer_wait desc limit 10;

-- 查看索引使用情况
select * from sys.schema_unused_indexes;
select * from sys.schema_redundant_indexes;

-- 查看mysql版本
select version();

-- 查看优化器特性
show variables like 'optimizer_switch';