基礎(chǔ)知識(shí)
笛卡爾積：集合X與Y的所有可能的有序?qū)M成的集合，有序?qū)Φ谝粋€(gè)對(duì)象是X成員，第二個(gè)對(duì)象是Y成員：{Xi, Yj}
查詢執(zhí)行順序：
- 客戶端發(fā)送一條查詢給服務(wù)器
- 服務(wù)器檢查緩存，如果命中緩存，則立刻返回結(jié)果，否則進(jìn)入下一階段。
- 服務(wù)器進(jìn)行SQL解析，預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。
- 調(diào)用存儲(chǔ)引擎api執(zhí)行查詢。
- 返回結(jié)果給客戶端。
通信協(xié)議：MySQL客戶端與數(shù)據(jù)庫(kù)是半雙工，客戶發(fā)送完請(qǐng)求等待響應(yīng)。
連接查詢執(zhí)行原理：
MySQL的連接查詢執(zhí)行嵌套循環(huán)操作，循環(huán)迭代外表的數(shù)據(jù)，再循環(huán)內(nèi)表，對(duì)比符合的行輸出。
例如：
select tbl1.col1, tbl2.col2
from tbl1 inner join tbl2 using(col3)
where tbl1.col1 in(5, 6)
偽代碼：
outer_iter = iter over tbl1 where col in(5,6)
while out_row in outer_iter:
inner_iter = iter over tbl2 where col3 = outer_row.col3
while inner_row in inner_iter
output [out_row.col1, inner_row.col2]
end
end
MySQL優(yōu)化器會(huì)優(yōu)化掃描數(shù)據(jù)交少的表作為外表。
MySQL多表連接查詢是一顆左側(cè)深度優(yōu)先樹（嵌套循環(huán)），而不是平衡樹（并列循環(huán)）。
優(yōu)化層次
- 服務(wù)器性能優(yōu)化
- 表結(jié)構(gòu)設(shè)計(jì)優(yōu)化
- SQL優(yōu)化（主要學(xué)習(xí)）
SQL查詢優(yōu)化
sql查詢優(yōu)化、表結(jié)構(gòu)設(shè)計(jì)、索引優(yōu)化是統(tǒng)一的。
整體思想：SQL的優(yōu)化就是減少返回列、返回行、減少掃描的行。分而治之思想：大SQL分成多條小SQL，減少阻塞。SQL分批多次執(zhí)行或返回。
排查與分析SQL
慢SQL
開啟慢SQL日志，配置閾值，查看慢sql?？梢允褂?code>mysqldumpslow或者pt-query-digest分析慢sql日志。
查詢執(zhí)行狀況
show processlist 可以查看當(dāng)前查詢的進(jìn)程、執(zhí)行的sql語句。
死鎖排查
SHOW ENGINE INNODB STATUS; 可以查看最近一次的死鎖
SELECT * FROM information_schema.INNODB_TRX 可以查看進(jìn)行中事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 可以查看正在鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 可以查看等待鎖事務(wù)
SHOW OPEN TABLES where In_use > 0; 查看正在使用的表
分析SQL執(zhí)行
explain可以分析sql執(zhí)行計(jì)劃。
指標(biāo)：
- type：訪問類型，全表掃描還是索引，用了什么索引。
- rows：掃描的行數(shù)。
- keys：可能用到的索引。
- Extra：附加信息
| Extra 值 | 含義 | 建議 |
|---|---|---|
Using index |
覆蓋索引（不回表） | ? 優(yōu)秀 |
Using where |
需要在 server 層過濾（索引未完全覆蓋） | ? 一般 |
Using temporary |
使用臨時(shí)表（一般出現(xiàn)在 group by/order by） | ? 盡量?jī)?yōu)化 |
Using filesort |
非索引排序 | ? 重點(diǎn)優(yōu)化對(duì)象 |
Using join buffer |
連接使用了 join buffer（索引缺失） | ? 優(yōu)化 join 條件 |
Select tables optimized away |
聚合函數(shù)優(yōu)化，如 SELECT MIN(id)
|
? 很好 |
關(guān)聯(lián)子查詢優(yōu)化
最重要的建議：盡量使用關(guān)聯(lián)查詢代替子查詢
- IN子句
MySQL5.x版本的IN查詢性能非常差，可能會(huì)把外表壓到子查詢內(nèi)，導(dǎo)致全表查詢。通常建議用EXISTS代替IN。
MySQL8 IN做了半查詢優(yōu)化，IN和JOIN性能差不多了。
什么是半連接（SEMI JOIN）？
核心定義：半連接是一種優(yōu)化技術(shù)，用于處理使用 IN 或 EXISTS 的子查詢。它的核心思想是：“只要在右表中找到一個(gè)匹配項(xiàng)，就返回左表的當(dāng)前行，并停止在右表中繼續(xù)查找”。
例如下列語句，沒有半連接，會(huì)先執(zhí)行子查詢，得到臨時(shí)列表，可能很大，用了半連接匹配到第一條符合條件的就返回：
SELECT * FROM products p
WHERE p.id IN (
SELECT product_id FROM order_items
);
- UNION子句
MySQL5.7沒辦法把UNION ALL的條件推到內(nèi)層執(zhí)行，導(dǎo)致查出來一張大的臨時(shí)表再做條件過濾。
MySQL8可以條件下推，UNION ALL可以流式輸出不需要臨時(shí)表。
- COUNT查詢
count經(jīng)常用在分頁查找，InnoDB由于并發(fā)模型是MVCC，沒有全局的統(tǒng)計(jì)值，count性能有問題。
常見解決方案：
1.應(yīng)用層緩存計(jì)數(shù)值。
2.計(jì)算最小的索引數(shù)量SELECT COUNT(col) FROM t WHERE col IS NOT NULL;
3.估算而非精確值SHOW TABLE STATUS
- GROUP BY 和 DISTINCT
group by的工作原理是對(duì)select...where...獲取的數(shù)據(jù)按照分組列做分組。
group by如果用了非索引列，會(huì)用臨時(shí)表或文件排序。
用覆蓋索引優(yōu)化group by。
DISTINCT是在select獲取結(jié)果之后，返回最終結(jié)果前distinct去重，一般用排序去重或哈希去重，大數(shù)據(jù)量會(huì)生成臨時(shí)表。
建議：建立適合的覆蓋索引，單列的distinct，用索引掃描去重。用group by代替distinct，mysql對(duì)group by優(yōu)化的更好SELECT col1 FROM t GROUP BY col1;
- LIMIT分頁
LIMIT的執(zhí)行其實(shí)是根據(jù)之前步驟查詢返回的數(shù)據(jù)限制條數(shù)，所以select * from tbl limit 10000，10；是會(huì)拿10010條然后只要最后10條看著那么逆天。
建議1：記錄上次分頁最后一條的唯一索引列，從上次的OFFSET開始查。
建議2：覆蓋索引
建議3：先查ID再回表
建議4：從業(yè)務(wù)上避免分頁深度
建議5：預(yù)分片/預(yù)計(jì)算分頁 （大表分成小表、緩存分頁結(jié)果）
MySQL 處理 SQL 的標(biāo)準(zhǔn)邏輯執(zhí)行順序大致如下（簡(jiǎn)化版）：
- FROM / JOIN — 數(shù)據(jù)源讀取與連接
- WHERE — 行過濾
- GROUP BY — 數(shù)據(jù)分組（如果有）
- HAVING — 分組后過濾
- SELECT — 列計(jì)算
- DISTINCT — 去重
- ORDER BY — 排序
- LIMIT — 限制返回行數(shù)
