下午事真的多的一匹，正好測(cè)試送來(lái)一批mysql慢查詢(xún)的日志。于是便看了起來(lái)
第一次慢查詢(xún)
SELECT d.id, d.name
FROM rec_kb_drugs d
LEFT JOIN dict_kb_classify c ON d.dept_code = c.CODE
LEFT JOIN dict_kb_classify cc ON c.parent_id = cc.id
WHERE 1 = 1
-- AND (d.name LIKE CONCAT(CONCAT(?,?),?)
-- OR cc.value LIKE CONCAT(CONCAT(?, ?), ?))
AND d.status = 1
ORDER BY d.sort_no ASC
執(zhí)行計(jì)劃如下:
索引使用情況在possible_keys、key和key_len三列,表的掃描情況在type
type：MySQL在表中找到所需行的方式，或者叫訪問(wèn)類(lèi)型
性能從最差到最好：ALL < index < range < ref < eq_ref < const/system < NULL
eq_ref的意思是使用唯一索引或者主鍵掃描，對(duì)于某個(gè)索引鍵值，表中只有一條索引記錄匹配。通常出現(xiàn)在多表的 join 查詢(xún), 表示對(duì)于前表的每一個(gè)結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢(xún)的比較慢
rows就是mysql認(rèn)為必須要逐行去檢查和判斷的記錄的條數(shù)，即加索引后受影響的行數(shù)
possible_keys指出MySQL能使用哪個(gè)索引在該表中找到行
key 顯示MySQL實(shí)際決定使用的鍵(索引)。如果沒(méi)有選擇索引,鍵是NULL。
key_len 顯示MySQL決定使用的鍵長(zhǎng)度。如果鍵是NULL,則長(zhǎng)度為NULL。
我們發(fā)現(xiàn)cc表即dict_kb_classify表。這里就不太清楚了，為什么不直接把同一張表的條件放到where后面呢？
因?yàn)閙ysql在通過(guò)連接兩張或多張表來(lái)返回記錄時(shí)，都會(huì)生成一張中間的臨時(shí)表，然后再將這張臨時(shí)表返回。
在使用left jion時(shí)，on和where條件的區(qū)別如下：
on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會(huì)返回左邊表中的記錄。
where條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過(guò)濾的條件。
從執(zhí)行計(jì)劃可以看出加cc表走了主鍵索引，且受索引影響的行數(shù)為1，證明了表數(shù)據(jù)量也不多，典型的小表驅(qū)動(dòng)大表的案例。所以考慮在小表上加索引。
既然parent_id和id都是同一張表的，根據(jù)mysql的索引類(lèi)型eq_ref正好支持UNIQUE和PRIMARY，所以對(duì)兩個(gè)字段使用聯(lián)合索引
優(yōu)化結(jié)果:
row按需掃描的行減少了
第二次慢查詢(xún)
早上過(guò)來(lái)一看又一波慢查，頓時(shí)感覺(jué)整個(gè)人都炸了
執(zhí)行計(jì)劃:
咦！我加了索引呀，怎么沒(méi)走過(guò)去，看了看順序完全符合最左前綴原則的我，滿(mǎn)臉問(wèn)號(hào)
走的是const類(lèi)型的查詢(xún)方式，但仍然造成1s左右的耗時(shí)，懷疑是順序錯(cuò)了..好吧channe_id和user_id換個(gè)位置就好了
第三波慢查詢(xún)
SELECT *
FROM rec_channel
WHERE id IN (
SELECT channel_id
FROM rec_attention
WHERE user_id = '2c90a2f26b669c87016b68a9cfc20420'
AND product_code = 'PLATFORM'
)
OR type = 1
AND subscribe_flag = 1
AND product_code = 'PLATFORM'
ORDER BY subscribe_flag DESC
LIMIT 100, 56546
雖然這里選擇用IN做內(nèi)聯(lián)查詢(xún)是很耗費(fèi)性能，建議盡量用子查詢(xún)，會(huì)根據(jù)掃描的行數(shù)而記錄內(nèi)循環(huán)，且建索引后會(huì)先走子查詢(xún)
SELECT *
FROM rec_channel c,
(
SELECT channel_id
FROM rec_attention
WHERE user_id = '2c90a2f26b669c87016b68a9cfc20420'
AND product_code = 'PLATFORM'
) b,
WHERE c.id = b.id
OR type = 1
AND subscribe_flag = 1
AND product_code = 'PLATFORM'
ORDER BY subscribe_flag DESC
LIMIT 100, 56546
那么為什么要這樣做呢？
根據(jù)大表分頁(yè)查詢(xún)優(yōu)化的方案,之所以單獨(dú)查詢(xún)id是為了通過(guò)使用覆蓋索引查詢(xún)返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。
舉個(gè)例子
select * from t where kid =3 and type=1 order by id desc 8,2;
對(duì)于Innodb表，系統(tǒng)是根據(jù) idxkidtype 二級(jí)索引里面包含的主鍵去查找對(duì)應(yīng)的行。下圖是mysql中兩種數(shù)據(jù)庫(kù)引擎的結(jié)構(gòu)
InnoDB的數(shù)據(jù)文件本身就是主鍵聚合索引文件，其他列的索引葉節(jié)點(diǎn)上的data存儲(chǔ)的主鍵(不存放數(shù)據(jù)的地址的原因是地址可能會(huì)變)
MyISAM葉節(jié)點(diǎn)上的data不是數(shù)據(jù)本身，而是數(shù)據(jù)存放的地址
上圖我們可以看出二級(jí)索引和數(shù)據(jù)葉子節(jié)點(diǎn)不在同一個(gè)物理塊兒上存儲(chǔ)，二級(jí)索引與主鍵的相對(duì)無(wú)序映射關(guān)系，那么也就是說(shuō)我們只需要先現(xiàn)找出主鍵索引中數(shù)據(jù)行（row）的位置，就必須要進(jìn)行全表掃描每一個(gè)索引頁(yè)和數(shù)據(jù)行，才能找到對(duì)應(yīng)的值。
因此，我們考慮將id單獨(dú)拿出來(lái)作為索引，進(jìn)行對(duì)應(yīng)數(shù)據(jù)行的檢索。
這樣從而避免了全表掃描，跳過(guò)前面無(wú)關(guān)的數(shù)據(jù)頁(yè)遍歷，可以直接通過(guò)索引定位到具體的數(shù)據(jù)行。
