SELECT * 
FROM orders 
WHERE user_id = 12345 
  AND status = 1 
ORDER BY create_time DESC 
LIMIT 0, 20;

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 0, 20;

+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | filtered | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | 5000000 | 0.10  | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+

SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `status` tinyint NOT NULL DEFAULT '0',
  `amount` decimal(10,2) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 創(chuàng)建聯(lián)合索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+
| id | select_type | table  | type | possible_keys       | key                 | key_len | ref         | rows | Extra |
+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user_status_time| idx_user_status_time| 9       | const,const | 156  | NULL  |
+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+

聯(lián)合索引順序：(user_id, status, create_time)

查詢條件：WHERE user_id = ? AND status = ?
排序條件：ORDER BY create_time

索引匹配過程：
1. user_id = 12345  → 定位到用戶的訂單
2. status = 1       → 進(jìn)一步過濾狀態(tài)
3. create_time      → 索引本身有序，無需filesort

優(yōu)化前：28.35秒，掃描500萬行
優(yōu)化后：0.003秒，掃描156行

提升：9000倍+

-- 差：查詢所有字段
SELECT * FROM orders WHERE ...

-- 好：只查需要的字段
SELECT id, user_id, amount, create_time FROM orders WHERE ...

-- 索引：idx_user_status_time (user_id, status, create_time)

-- 這個(gè)查詢可以用覆蓋索引
SELECT user_id, status, create_time FROM orders WHERE user_id = 12345;

-- EXPLAIN顯示 Using index

-- ? 對(duì)索引列使用函數(shù)
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';

-- ? 改寫
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

-- ? 隱式類型轉(zhuǎn)換
SELECT * FROM orders WHERE user_id = '12345';  -- user_id是bigint

-- ? 類型一致
SELECT * FROM orders WHERE user_id = 12345;

-- ? LIKE前置通配符
SELECT * FROM orders WHERE order_no LIKE '%ABC';

-- ? LIKE后置通配符（可以用索引）
SELECT * FROM orders WHERE order_no LIKE 'ABC%';

-- ? 深分頁(yè)很慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 需要掃描100萬行

-- ? 用游標(biāo)分頁(yè)
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 直接定位，很快

原則：
1. 區(qū)分度高的列放前面
2. 等值查詢的列放前面
3. 排序列放最后

# mysqldumpslow
mysqldumpslow -s t -t 10 slow.log  # 按時(shí)間排序，前10條

# pt-query-digest（推薦）
pt-query-digest slow.log > report.txt

type（重要，從好到差）：
- system/const：常量查詢
- eq_ref：主鍵/唯一索引
- ref：普通索引
- range：范圍掃描
- index：索引全掃描
- ALL：全表掃描 ?

Extra（重要）：
- Using index：覆蓋索引 ?
- Using where：需要回表過濾
- Using filesort：額外排序 ??
- Using temporary：臨時(shí)表 ??

# 組網(wǎng)后直接用虛擬IP連接
mysql -h 192.168.188.10 -u root -p

1. 開啟慢查詢?nèi)罩?→ 發(fā)現(xiàn)問題SQL
2. EXPLAIN分析 → 定位問題原因
3. 添加/優(yōu)化索引 → 解決問題
4. 再次EXPLAIN → 驗(yàn)證效果
5. 線上執(zhí)行 → 監(jiān)控觀察