最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL 查詢優(yōu)化器 (Query Optimizer) 的使用小結

 更新時間：2026年03月19日 09:38:45   作者：學亮編程手記  
本文主要介紹了MySQL 查詢優(yōu)化器的使用小結,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

一、MySQL優(yōu)化器概述

1.1 什么是查詢優(yōu)化器

查詢優(yōu)化器(Query Optimizer)是MySQL的核心組件,負責將SQL語句轉換為最優(yōu)的執(zhí)行計劃。

工作流程:

SQL語句 → 解析器(Parser) → 優(yōu)化器(Optimizer) → 執(zhí)行器(Executor) → 存儲引擎 

優(yōu)化器的主要職責:

  • 選擇最優(yōu)的索引
  • 確定表的連接順序
  • 選擇合適的連接算法
  • 優(yōu)化子查詢
  • 簡化和重寫查詢語句

1.2 優(yōu)化器類型

MySQL主要有兩種優(yōu)化器:

  1. 基于規(guī)則的優(yōu)化器(RBO - Rule-Based Optimizer)

    • 基于預定義的規(guī)則進行優(yōu)化
    • 較為簡單,但不夠靈活

  2. 基于成本的優(yōu)化器(CBO - Cost-Based Optimizer) ?

    • MySQL主要使用這種
    • 通過計算各種執(zhí)行計劃的成本,選擇成本最低的
    • 依賴統(tǒng)計信息

二、優(yōu)化器的工作原理

2.1 成本模型

MySQL優(yōu)化器通過成本模型評估不同執(zhí)行計劃的代價。

成本計算因素:

總成本 = I/O成本 + CPU成本

-- I/O成本: 從磁盤讀取數據的成本
-- CPU成本: 處理數據(比較、排序)的成本

成本常量(MySQL 5.7+):

-- 查看成本常量
SELECT * FROM mysql.server_cost;
SELECT * FROM mysql.engine_cost;

-- 主要成本參數:
-- disk_temptable_create_cost: 創(chuàng)建臨時表成本(默認20.0)
-- disk_temptable_row_cost: 臨時表行讀取成本(默認0.5)
-- key_compare_cost: 鍵比較成本(默認0.05)
-- memory_temptable_create_cost: 內存臨時表創(chuàng)建成本(默認1.0)
-- memory_temptable_row_cost: 內存臨時表行成本(默認0.1)
-- row_evaluate_cost: 行評估成本(默認0.1)

2.2 統(tǒng)計信息

優(yōu)化器依賴表和索引的統(tǒng)計信息做決策。

-- 查看表統(tǒng)計信息
SHOW TABLE STATUS LIKE 'table_name'\G

-- 查看索引統(tǒng)計信息
SHOW INDEX FROM table_name;

-- 關鍵統(tǒng)計指標:
-- Cardinality: 索引中唯一值的數量(區(qū)分度)
-- Rows: 表中的行數
-- Data_length: 數據文件大小
-- Index_length: 索引文件大小

-- 更新統(tǒng)計信息
ANALYZE TABLE table_name;

統(tǒng)計信息采樣:

-- InnoDB統(tǒng)計信息采樣設置
SHOW VARIABLES LIKE 'innodb_stats%';

-- innodb_stats_persistent: 持久化統(tǒng)計信息(ON/OFF)
-- innodb_stats_auto_recalc: 自動重新計算統(tǒng)計信息
-- innodb_stats_sample_pages: 采樣頁數(默認8)

三、優(yōu)化器的優(yōu)化策略

3.1 條件簡化和優(yōu)化

常量傳播:

-- 原始SQL
SELECT * FROM t WHERE a = 5 AND b = a;

-- 優(yōu)化后
SELECT * FROM t WHERE a = 5 AND b = 5;

恒等式消除:

-- 原始SQL
SELECT * FROM t WHERE a > 3 AND a > 5;

-- 優(yōu)化后
SELECT * FROM t WHERE a > 5;

范圍合并:

-- 原始SQL
SELECT * FROM t WHERE (a > 1 AND a < 5) OR (a > 3 AND a < 7);

-- 優(yōu)化后
SELECT * FROM t WHERE a > 1 AND a < 7;

3.2 索引選擇

優(yōu)化器通過以下步驟選擇索引:

1. 找出所有可能的索引:

EXPLAIN SELECT * FROM user WHERE age = 25 AND name = '張三';

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

2. 計算每個索引的成本:

-- 成本計算公式(簡化版):
成本 = (掃描的數據頁數 × I/O成本) + (處理的記錄數 × CPU成本)

3. 選擇成本最低的索引

示例分析:

-- 表結構
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: 優(yōu)化器會選擇 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';

3.3 JOIN優(yōu)化

連接順序優(yōu)化:

-- 三表連接
SELECT * FROM t1 
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id
WHERE t1.status = 1;

-- 優(yōu)化器會評估6種連接順序(3! = 6):
-- t1 → t2 → t3
-- t1 → t3 → t2
-- t2 → t1 → t3
-- t2 → t3 → t1
-- t3 → t1 → t2
-- t3 → t2 → t1

連接算法選擇:

  1. 嵌套循環(huán)連接(Nested-Loop Join)
-- 簡單嵌套循環(huán)(Simple Nested-Loop)
for each row in t1:
    for each row in t2:
        if row matches join condition:
            output row

-- 時間復雜度: O(n * m)
  1. 索引嵌套循環(huán)(Index Nested-Loop Join)
-- 使用索引加速內表查詢
for each row in t1:
    use index to find matching rows in t2
    output matched rows

-- 時間復雜度: O(n * log m)
  1. 塊嵌套循環(huán)(Block Nested-Loop Join)
-- 使用join buffer緩存外表數據
-- MySQL 8.0+ 使用Hash Join替代

-- 查看join buffer大小
SHOW VARIABLES LIKE 'join_buffer_size';
  1. Hash Join(MySQL 8.0.18+)
-- 構建哈希表,性能更好
-- 適用于等值連接

EXPLAIN FORMAT=TREE 
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
-- 可以看到 "Hash Join" 字樣

JOIN優(yōu)化建議:

-- ? 小表驅動大表
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;

3.4 子查詢優(yōu)化

子查詢轉換策略:

1. 子查詢物化(Subquery Materialization):

-- 原始SQL
SELECT * FROM t1 
WHERE id IN (SELECT t1_id FROM t2 WHERE status = 1);

-- 優(yōu)化過程:
-- 1. 先執(zhí)行子查詢,結果存入臨時表
-- 2. 臨時表加索引
-- 3. 用臨時表進行JOIN

-- EXPLAIN 中看到 "MATERIALIZED"

2. 子查詢轉JOIN:

-- 原始SQL(相關子查詢)
SELECT * FROM t1 
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id);

-- 優(yōu)化后(Semi-Join)
SELECT t1.* FROM t1 
SEMI JOIN t2 ON t2.t1_id = t1.id;

3. 子查詢展開:

-- 原始SQL
SELECT * FROM t1 
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.t1_id = t1.id) > 5;

-- 優(yōu)化后
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;

控制子查詢優(yōu)化:

-- 查看子查詢優(yōu)化策略
SHOW VARIABLES LIKE 'optimizer_switch';

-- 關鍵參數:
-- materialization: 子查詢物化
-- semijoin: 半連接優(yōu)化
-- subquery_materialization_cost_based: 基于成本選擇

3.5 ORDER BY 和 GROUP BY 優(yōu)化

索引排序 vs 文件排序:

-- ? 使用索引排序(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. 進行排序(快速排序)

GROUP BY優(yōu)化:

-- ? 松散索引掃描(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

優(yōu)化配置:

-- 查看排序緩沖區(qū)大小
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';  -- 堆表最大值

四、優(yōu)化器提示(Hint)

4.1 索引提示

-- 強制使用某個索引
SELECT * FROM user FORCE INDEX(idx_age) WHERE age = 25;

-- 建議使用某個索引(優(yōu)化器可能忽略)
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;

4.2 JOIN提示

-- 強制連接順序
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;

4.3 其他提示

-- 子查詢物化
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;

-- 限制執(zhí)行時間(8.0+)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM user;  -- 1秒超時

-- 查看所有可用提示
SELECT /*+ QB_NAME(qb1) */ * FROM t1;

五、優(yōu)化器跟蹤

5.1 使用optimizer_trace

-- 開啟優(yōu)化器跟蹤
SET optimizer_trace='enabled=on';

-- 執(zhí)行查詢
SELECT * FROM user WHERE age = 25 AND name = '張三';

-- 查看優(yōu)化過程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 關閉跟蹤
SET optimizer_trace='enabled=off';

trace信息解讀:

{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "/* 展開后的查詢 */"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              /* 條件優(yōu)化過程 */
            }
          },
          {
            "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": [
              /* 考慮的執(zhí)行計劃 */
              {
                "plan_prefix": [],
                "table": "user",
                "best_access_path": {
                  /* 最佳訪問路徑 */
                }
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              /* 附加條件到表 */
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        /* 執(zhí)行階段 */
      }
    }
  ]
}

5.2 使用EXPLAIN詳細分析

-- 傳統(tǒng)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;

-- 查看實際執(zhí)行統(tǒng)計(MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM user WHERE age = 25;

EXPLAIN關鍵字段詳解:

字段說明重要值
id查詢序列號數字越大越先執(zhí)行
select_type查詢類型SIMPLE, PRIMARY, SUBQUERY, DERIVED
table表名實際表名或別名
partitions分區(qū)匹配的分區(qū)
type訪問類型system > const > eq_ref > ref > range > index > ALL
possible_keys可能的索引候選索引列表
key實際索引實際使用的索引
key_len索引長度使用的索引字節(jié)數
ref引用與索引比較的列
rows掃描行數預估掃描的行數
filtered過濾百分比滿足條件的行百分比
Extra額外信息Using index, Using where, Using filesort等

type類型詳解:

-- system: 表只有一行(系統(tǒng)表)
-- 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無索引

六、優(yōu)化器常見問題

6.1 優(yōu)化器選錯索引

原因:

  • 統(tǒng)計信息不準確
  • 成本估算偏差
  • 數據分布不均勻

解決方案:

-- 1. 更新統(tǒng)計信息
ANALYZE TABLE user;

-- 2. 使用索引提示
SELECT * FROM user FORCE INDEX(idx_age) WHERE age = 25;

-- 3. 調整優(yōu)化器參數
SET optimizer_search_depth = 5;  -- 控制JOIN搜索深度
SET optimizer_prune_level = 1;   -- 啟用優(yōu)化器剪枝

-- 4. 修改索引或查詢
-- 例如:添加更合適的組合索引

6.2 JOIN順序不優(yōu)

-- 查看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;

6.3 子查詢性能差

-- ? 相關子查詢(每行都執(zhí)行一次)
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
);

七、優(yōu)化器最佳實踐

7.1 定期維護

-- 1. 定期更新統(tǒng)計信息
ANALYZE TABLE user;

-- 2. 優(yōu)化表(重建索引,回收空間)
OPTIMIZE TABLE user;

-- 3. 檢查表
CHECK TABLE user;

-- 4. 修復表
REPAIR TABLE user;

7.2 監(jiān)控慢查詢

-- 開啟慢查詢日志
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

7.3 使用性能監(jiān)控

-- 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;

7.4 版本升級建議

  • MySQL 5.7: 引入成本模型,優(yōu)化器改進
  • MySQL 8.0: Hash Join, CTE, Window Function, Invisible Index
  • MySQL 8.0.18+: EXPLAIN ANALYZE(實際執(zhí)行統(tǒng)計)
  • MySQL 8.0.20+: Hash Join默認啟用
-- 查看MySQL版本
SELECT VERSION();

-- 查看優(yōu)化器特性
SHOW VARIABLES LIKE 'optimizer_switch';

八、總結

MySQL優(yōu)化器是一個復雜的系統(tǒng),理解其工作原理有助于:

  1. 編寫更高效的SQL
  2. 設計合理的索引
  3. 排查性能問題
  4. 合理使用優(yōu)化器提示

核心要點:

  • 優(yōu)化器基于成本模型選擇執(zhí)行計劃
  • 依賴準確的統(tǒng)計信息
  • 需要定期維護(ANALYZE TABLE)
  • 使用EXPLAIN分析執(zhí)行計劃
  • 謹慎使用優(yōu)化器提示
  • 關注MySQL版本新特性

到此這篇關于MySQL 查詢優(yōu)化器 (Query Optimizer) 的使用小結的文章就介紹到這了,更多相關MySQL 查詢優(yōu)化器內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

最新評論

阳东县| 昭通市| 雷州市| 敦化市| 台东市| 湖口县| 东乡族自治县| 宝山区| 宜黄县| 湖南省| 昆明市| 云南省| 高碑店市| 萝北县| 沂南县| 缙云县| 扬州市| 岳西县| 饶阳县| 和田县| 阆中市| 永城市| 长沙市| 东乡县| 平湖市| 温泉县| 连南| 阳城县| 岳池县| 禹州市| 黑河市| 基隆市| 民丰县| 英德市| 泗洪县| 峨边| 三门峡市| 肥西县| 昂仁县| 炎陵县| 广西|