在項(xiàng)目中遇到的真實(shí)問題，以及我的解決方案，部分?jǐn)?shù)據(jù)做了脫敏處理。

問題

最近在做項(xiàng)目時(shí)需要寫sql做單表查詢，每次查出來的數(shù)據(jù)有幾百萬甚至上千萬條，公司用的數(shù)據(jù)庫是MySQL5.7，做了分庫分表，部分?jǐn)?shù)據(jù)庫設(shè)置了查詢超時(shí)時(shí)間，比如查詢超過15s直接報(bào)超時(shí)錯(cuò)誤，如下圖：

可以通過show variables like 'max_statement_time';命令查看數(shù)據(jù)庫超時(shí)時(shí)間（單位：毫秒）：

方案1

嘗試使用索引加速sql，從下圖可以看到該sql已經(jīng)走了主鍵索引，但還是需要掃描150萬行，無法從這方面進(jìn)行優(yōu)化。

方案2

嘗試使用limit語句進(jìn)行分頁查詢，語句為：

sql復(fù)制代碼SELECT * FROM table WHERE user_id = 123456789 limit 0, 300000;

像這樣每次查30萬條肯定就不會(huì)超時(shí)了，但這會(huì)引出另一個(gè)問題--查詢耗時(shí)與起始位置成正比，如下圖：

第二條語句實(shí)際上查了60w條記錄，不過把前30w條丟棄了，只返回后30w條，所以耗時(shí)會(huì)遞增，最終仍會(huì)超時(shí)。

方案3

使用指定主鍵范圍的分頁查詢，主要思想是將條件語句改為如下形式（其中id為自增主鍵）：

ini復(fù)制代碼WHERE user_id = 123456789 AND id > 0 LIMIT 300000; WHERE user_id = 123456789 AND id > (上次查詢結(jié)果中最后一條記錄的id值) LIMIT 300000;

也可以將上述語句簡化成如下形式（注意：帶了子查詢會(huì)變慢）：

sql復(fù)制代碼WHERE user_id = 123456789 AND id >= (SELECT id FROM table LIMIT 300000, 1) limit 300000;

每次查詢只需要修改子查詢limit語句的起始位置即可，但我發(fā)現(xiàn)表中并沒有自增主鍵id這個(gè)字段，表內(nèi)主鍵是fs_id，而且是無序的。

這個(gè)方案還是不行，組內(nèi)高工都感覺無解了。

方案4

既然fs_id是無序的，那么就給它排序吧，加了個(gè)ORDER BY fs_id，最終解決方案如下：

ini復(fù)制代碼WHERE user_id = 123456789 AND fs_id > 0 ORDER BY fs_id LIMIT 300000; WHERE user_id = 123456789 AND fs_id > (上次查詢結(jié)果中最后一條記錄的id值) ORDER BY fs_id LIMIT 300000;

效果如下圖：

查詢時(shí)間非常穩(wěn)定，每條查詢的fs_id都大于上次查詢結(jié)果中最后一條記錄的fs_id值。正常查30w條需要3.88s，排序后查30w條需要6.48s，確實(shí)慢了許多，但總算能把問題解決了。目前代碼還在線上跑著哈哈，如果有更好的解決方案可以在評論區(qū)討論喲。