mysql的Late row lookups(延遲row查找) Sql代碼 CREATE TABLE `20130122handler` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `content` varchar(50) NOT NULL, PRIMARY KEY (`id`), KEY `20130122handler_idx_uid` (`uid`) ) ENGINE=InnoDB www.2cto.com 裡面有60w數據,現在模擬按uid排序分頁的情況 要看第七頁的內容,用 Sql代碼 select SQL_NO_CACHE * from 20130122handler order by uid LIMIT 120,20 查找20條數據,基本就是瞬間的事情 假設用戶比較變態,直接點到了102頁,用 Sql代碼 select SQL_NO_CACHE * from 20130122handler order by uid LIMIT 2020,20 www.2cto.com 查找20條數據,發現各種性能相當差 似乎mysql在這種情況下,要從20130122handler_idx_uid索引中讀取2040條secondary記錄, 然後執行2040次的主鍵查詢,然後返回20條,所以浪費了2020次主鍵查詢 可以考慮用這種手段,減少無用的row lookup Sql代碼 select SQL_NO_CACHE m.* from( select uid from 20130122handler ORDER BY uid LIMIT 2020,20) t,20130122handler m where t.uid=m.uid 這是因為20130122handler_idx_uid是secondary索引,所以要row lookup 用了 Sql代碼 select SQL_NO_CACHE * from 20130122handler order by id LIMIT 120,20 select SQL_NO_CACHE * from 20130122handler ORDER BY id LIMIT 2040,20 差別就不明顯了
