一、連線與執行緒模型
- 每個客戶端連線對應一個專屬執行緒，查詢在該執行緒處理。
- 執行緒快取機制：服務端快取空閒執行緒，避免頻繁建立/銷毀開銷。
- 連線驗證：
user、host、password 三元組，連線後載入權限快取。
二、查詢快取
- 僅快取
SELECT 語句及結果集。
- 解析前先查快取，命中直接返回，跳過解析/優化/執行。
- MySQL 8.0 移除查詢快取，建議應用層用 Redis 替代。
三、資料目錄檔案結構
datadir/
├── db1/
│ ├── tbl1.frm -- 表定義
│ ├── tbl1.MYD -- MyISAM 資料
│ ├── tbl1.MYI -- MyISAM 索引
│ └── tbl2.ibd -- InnoDB 獨立表空間 (file_per_table=ON)
├── ibdata1 -- InnoDB 共用表空間
├── ib_logfile0/1 -- Redo Log
└── mysql/ -- 系統庫
四、IP 儲存最佳實踐
- 勿用 VARCHAR(15)，改用
INT UNSIGNED（IPv4）或 VARBINARY(16)（IPv6）。
- PHP：
ip2long() / long2ip()；MySQL：INET_ATON() / INET_NTOA()。
- 優勢：節省空間、範圍查詢高效（
WHERE ip BETWEEN start AND end）、索引友善。
五、B+Tree 索引與自適應雜湊索引
- 索引列順序 = 建表列順序，最左前綴原則。
- InnoDB 自適應雜湊索引：熱點索引頁自動在記憶體建立雜湊映射，加速點查詢。
六、導致索引失效的常見寫法
-- 1. 索引列參與運算
SELECT name FROM user WHERE id + 1 = 4;
-- 2. 索引列使用函數
SELECT name FROM user WHERE TO_DAYS(birth) > 20;
-- 3. 隱式轉型（字串比數字）
SELECT * FROM user WHERE phone = 13800138000; -- phone 是 VARCHAR
-- 4. LIKE 以 % 開頭
SELECT * FROM user WHERE name LIKE '%abc';
-- 5. OR 條件未全覆蓋索引
SELECT * FROM user WHERE name='Tom' OR age=20; -- 無複合索引覆蓋
-- 6. 不等於 (!= / <>)
SELECT * FROM user WHERE status != 1;
七、實用 DDL/DML 技巧
| 操作 | SQL |
|---|
| 複製表結構 | CREATE TABLE b LIKE a; |
| 修改引擎 | ALTER TABLE a ENGINE=InnoDB; |
| 複製資料 | INSERT INTO b SELECT * FROM a; |
| 查看表資訊 | SHOW TABLE STATUS LIKE '%xxx%'; |
| 清空 Binlog | RESET MASTER; |
| 取前 N 字元 | SELECT LEFT(name, 3) FROM user; |
| 建立前綴索引 | ALTER TABLE xxx ADD KEY idx_name_prefix (name(3)); |
| 索引覆蓋查詢避免回表 | SELECT * FROM t JOIN (SELECT id FROM t WHERE actor='X' AND title LIKE '%Y%') tmp USING(id); |
八、效能優化口訣
- EXPLAIN 先行，關注
type、key、rows、Extra。
- 慢查詢日誌必開，
long_query_time=1。
- 索引建在高選擇性列，組合索引遵循最左前綴。
- 避免
SELECT *，只取需要列（覆蓋索引）。
- 大事務拆小，減少鎖持有時間。
- 讀寫分離、分庫分表、緩存分層，架構層面解決擴展性。
