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

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

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

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

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

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

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

-- innodb_stats_persistent: 持久化統(tǒng)計(jì)信息(ON/OFF)
-- innodb_stats_auto_recalc: 自動(dòng)重新計(jì)算統(tǒng)計(jì)信息
-- innodb_stats_sample_pages: 采樣頁(yè)數(shù)(默認(rèn)8)

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

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

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

-- 表結(jié)構(gòu)
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)化器會(huì)選擇 idx_age_name(覆蓋索引)
EXPLAIN SELECT age, name FROM user WHERE age = 25;

-- 查詢2: 如果需要所有字段,可能選擇 idx_age(需要回表)
EXPLAIN SELECT * FROM user WHERE age = 25;

-- 使用 optimizer_trace 查看詳細(xì)過(guò)程
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;

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

-- 簡(jiǎn)單嵌套循環(huán)(Simple Nested-Loop)
for each row in t1:
    for each row in t2:
        if row matches join condition:
            output row

-- 時(shí)間復(fù)雜度: O(n * m)

-- 使用索引加速內(nèi)表查詢
for each row in t1:
    use index to find matching rows in t2
    output matched rows

-- 時(shí)間復(fù)雜度: O(n * log m)

-- 使用join buffer緩存外表數(shù)據(jù)
-- MySQL 8.0+ 使用Hash Join替代

-- 查看join buffer大小
SHOW VARIABLES LIKE 'join_buffer_size';

-- 構(gòu)建哈希表,性能更好
-- 適用于等值連接

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

-- ? 小表驅(qū)動(dòng)大表
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強(qiáng)制連接順序(謹(jǐn)慎使用)
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);

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

-- EXPLAIN 中看到 "MATERIALIZED"

-- 原始SQL(相關(guān)子查詢)
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;

-- 原始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)化策略
SHOW VARIABLES LIKE 'optimizer_switch';

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

-- ? 使用索引排序(Index Scan)
-- 假設(shè)有索引 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過(guò)程:
-- 1. 根據(jù)WHERE條件讀取數(shù)據(jù)
-- 2. 將需要排序的字段放入sort buffer
-- 3. 如果數(shù)據(jù)量大于sort_buffer_size,使用磁盤臨時(shí)文件
-- 4. 進(jìn)行排序(快速排序)

-- ? 松散索引掃描(Loose Index Scan)
-- 假設(shè)索引 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

-- ? 臨時(shí)表分組
EXPLAIN SELECT city, COUNT(*) FROM user GROUP BY city;
-- Extra: Using temporary; Using filesort

-- 查看排序緩沖區(qū)大小
SHOW VARIABLES LIKE 'sort_buffer_size';     -- 默認(rèn)256KB
SHOW VARIABLES LIKE 'max_length_for_sort_data'; -- 默認(rèn)1024

-- 臨時(shí)表相關(guān)
SHOW VARIABLES LIKE 'tmp_table_size';       -- 內(nèi)存臨時(shí)表大小
SHOW VARIABLES LIKE 'max_heap_table_size';  -- 堆表最大值

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

-- 建議使用某個(gè)索引(優(yōu)化器可能忽略)
SELECT * FROM user USE INDEX(idx_age) WHERE age = 25;

-- 忽略某個(gè)索引
SELECT * FROM user IGNORE INDEX(idx_age) WHERE age = 25;

-- MySQL 8.0+ 新語(yǔ)法
SELECT /*+ INDEX(user idx_age) */ * FROM user WHERE age = 25;

-- 強(qiáng)制連接順序
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);

-- 指定臨時(shí)表使用內(nèi)存
SELECT /*+ SET_VAR(internal_tmp_mem_storage_engine=TempTable) */ 
    age, COUNT(*) 
FROM user GROUP BY age;

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

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

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

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

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

-- 關(guān)閉跟蹤
SET optimizer_trace='enabled=off';

{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "/* 展開后的查詢 */"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              /* 條件優(yōu)化過(guò)程 */
            }
          },
          {
            "table_dependencies": [
              /* 表依賴關(guān)系 */
            ]
          },
          {
            "rows_estimation": [
              /* 行數(shù)估算 */
              {
                "table": "user",
                "range_analysis": {
                  "potential_range_indexes": [
                    /* 可能使用的索引 */
                  ],
                  "analyzing_range_alternatives": {
                    /* 分析每個(gè)索引的成本 */
                    "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í)行計(jì)劃 */
              {
                "plan_prefix": [],
                "table": "user",
                "best_access_path": {
                  /* 最佳訪問(wèn)路徑 */
                }
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              /* 附加條件到表 */
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        /* 執(zhí)行階段 */
      }
    }
  ]
}

-- 傳統(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;

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

-- system: 表只有一行(系統(tǒng)表)
-- const: 通過(guò)主鍵或唯一索引查詢,最多返回一行
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無(wú)索引

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

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

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

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

-- 查看JOIN順序
EXPLAIN FORMAT=TREE 
SELECT * FROM large_table t1
JOIN small_table t2 ON t1.id = t2.large_id;

-- 如果順序不對(duì),使用STRAIGHT_JOIN
SELECT * FROM small_table t2
STRAIGHT_JOIN large_table t1 ON t1.id = t2.large_id;

-- ? 相關(guān)子查詢(每行都執(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
);

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

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

-- 3. 檢查表
CHECK TABLE user;

-- 4. 修復(fù)表
REPAIR TABLE user;

-- 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 1秒
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查詢?nèi)罩疚恢?
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 分析慢查詢?nèi)罩?使用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();

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