mysql優化之limit優化

今天來探討的問題是mysql的limit優化問題。

準備環境：

1.centos7一台

2.mysql資料庫一個

3.actor表一個

我們首先來看一下表結構：

下面我們來看一下表的記錄條數：

從圖中可以看出來，表有四個欄位，其中actor_id是主鍵,並且表中記錄是200條。

那麼我們來測試第一條sql。

explain select * from actor limit 0,1 G;n

從圖中我們可以看出當我們查詢這一條數據的時候資料庫掃描的行數為200行。

那麼我們來測試第二條sql

explain select * from actor limit 1,1 G;n

從圖中我們可以看出當我們執行這條sql的時候資料庫掃描的行數為200行。

從上面的情況下似乎我們這一個簡單的查詢語句需要表掃描，為了證明這一點我們向表中插入一條數據看一下。

好的：我們插入一條數據成功。這個時候我們再來執行一下剛開始的sql吧。

我們從圖中發現事實就如我們猜想的那樣，儘管我們只查詢了一條數據，但是表掃描行數確實整張表，如果這張表的行數是幾千萬，那豈不是要掃描幾千萬行才返回給我們一條數據嗎？遇到這種問題，我們該怎麼優化呢？

我們用主鍵或者索引的時候可以減少查詢的行數，那麼讓我們來改一下我們的sql。

explain select * from actor order by actor_id limit 0,1 G;n

從圖中我們驚喜的發現它這次只查詢了一條就直接返回了我們的數據，那到底是不是這樣呢？

帶著疑問我們再把sql改一下：

explain select * from actor order by actor_id limit 10,1 G;n

從圖中我們看到我們同樣是查詢了一條數據，但是資料庫查詢的行數是11行，這個既讓我們驚喜的同時也讓我們覺得有些傷心。

驚喜的是我們查詢一條數據不在是掃描整張表了，傷心的是我們需要掃描從查詢的起始開始的之前數據都要被掃描。

我們再測試一條sql

explain select * from actor order by actor_id limit 200,1 G;n

從圖中我們看出掃描了201行，那麼我們來假設這種情況，如果我們做分頁的時候，行數達到了1千萬行，那麼我分頁查詢倒數第一條數據的時候，資料庫就要掃描1千萬行，我們只需要查詢一條啊，加入這個數再大了呢？這是我們覺得不允許的，那麼我們該如何來優化呢？

我們來改一下之前sql

expalin select * from actor where actor_id > 0 and actor_id < 2 G;n

從圖中我們發現執行這條語句的時候只查詢了1條。那麼我們把where的值改大一下看一下吧。

explain select * from actor where actor_id > 199 and actor_id < 201 G;n

從圖中我們發現我們達到了要求，當我們只想要一條數據時候，我們只希望表掃描一行的要求。

那麼到現在我們做的這些有什麼意義呢？

比如我們是否這樣可以解讀？只需要一條數據，完全可以用主鍵代替查詢，比如像下面這樣

explain select * from actor where actor_id = 200 G;n

很顯然，上面是查詢了一條數據就返回了結果。但是對於資料庫分頁來說真實來說每次返回的數據顯然不是一條而是多條。

但是我們又從上面看出來用limit呢數據量小的時候還好，即使沒有按照主鍵排序來說資料庫的I/o操作都是很小的，可以忽略不計的，但是我們的sql優化針對的不是簡單的200行數據，而是針對的幾百萬行，上千萬行，這個時候對於limit的使用來說我們查詢幾條數據帶來的資料庫I/O操作開銷是巨大的。

那麼我們就可以用上面的測試結果來進行分頁的優化，我們可以這樣想每次查詢完之前就得到上次查詢的最後的ID，然後再放入where中，當然了前提是where中的查詢條件是主鍵。

但是在上面的操作是有一個缺點的，比如我們的資料庫主鍵如果中間斷掉了一部分怎麼辦，我們就無法保證每頁都存在數據了。

比如我們分頁的時候每次要求查詢三條數據，這個時候資料庫從101到104這些數據都被刪除了，那麼當用戶查詢這一頁的時候就得不到數據，資料庫返回為空。

所以為了解決上面的問題我們想著如果有這麼一行數據和主鍵相似，但是刪除之後就可以自己從新開始排序多好啊，這樣我們賦予這一類數據為索引，當使用上面的where的時候豈不是完美嗎？

但是真有這樣的數據類型嗎？