問(wèn)題描述

table 表中30萬(wàn)記錄 id，自增主鍵， node，create_at 都有索引 但是沒(méi)有聯(lián)合索引

下面的語(yǔ)句查一次要8s左右， 可以預(yù)估隨著數(shù)據(jù)的繼續(xù)增加，速度會(huì)越來(lái)越慢。 最近在學(xué)習(xí) mysql 查詢優(yōu)化

也看了很多文章，教程（但是沒(méi)有系統(tǒng)的看mysql手冊(cè)，不好意思）

請(qǐng)各位朋友指導(dǎo)下，如何優(yōu)化，如果可以 請(qǐng)大概講述下，怎么分析的，為什么使用xxxx方式優(yōu)化就會(huì)有效。

謝謝各位。

EXPLAINSELECT `id` FROM `table` WHERE `node` = 2 ORDER BY `create_at` DESC LIMIT 12 OFFSET 69996------------------------------------------------------------------------ id: 1 select_type: SIMPLEtable: table type: refpossible_keys: node key: node key_len: 5 ref: const rows: 72278Extra: Using where; Using filesort

問(wèn)題解答

不要使用OFFSET方式分頁(yè)以你的例子來(lái)說(shuō)

MySQL會(huì)先查詢所有符合條件的數(shù)據(jù)，通過(guò)EXPLAIN可以發(fā)現(xiàn)（72278），查詢了這么多

因?yàn)镺FFSET的關(guān)系，MySQL丟棄前面（69996）的記錄。查詢優(yōu)化就是指在第1步的時(shí)候就讓MySQL查詢最少的數(shù)據(jù)。

我目前在用的分頁(yè)方式（數(shù)據(jù)量千萬(wàn)級(jí)），依舊拿你的例子來(lái)說(shuō)，假設(shè)分頁(yè)大小為10第一頁(yè)

#查詢1SELECT `id` FROM `table` WHERE `node` = 2 ORDER BY `id` ASC LIMIT 10

假設(shè)查詢1的第10條數(shù)據(jù)的id是10，第1條數(shù)據(jù)的id是1那么查詢第二頁(yè)的SQL如下

SELECT `id` FROM `table` WHERE `node` = 2 AND `id`>10 ORDER BY `id` ASCLIMIT 10

這樣你可以發(fā)現(xiàn)響應(yīng)速度超快。不過(guò)有個(gè)問(wèn)題是無(wú)法前往指定頁(yè)數(shù)，不過(guò)從我目前的實(shí)際情況來(lái)看，這個(gè)沒(méi)有影響，有個(gè)搜索功能就可以彌補(bǔ)

優(yōu)化分頁(yè)的問(wèn)題其實(shí)就是offset的問(wèn)題，尤其是偏移量大的時(shí)候。mysql會(huì)掃描大量不需要的行然后拋棄，只取limit的數(shù)量。所以一般最好不要用offset。解決方法有1.可以先使用索引覆蓋掃描，而不是查詢所有的列，然后做關(guān)聯(lián)操作返回相關(guān)的列。這個(gè)方法可以叫做“延遲關(guān)聯(lián)”2.可以把limit查詢轉(zhuǎn)換成已知位置的查詢，變成between XXX and XXX 。3.可以記錄上次查詢的數(shù)據(jù)的位置，下一次查詢直接從該位置開(kāi)始掃描，樓上就是用的這種辦法。

鑒于問(wèn)題好像只查詢id一個(gè)字段。1方法用不到，2，3可以考慮。