mysql中使用order by 和limit查詢變慢解決辦法

先來看一下測試效能

代碼如下

複製代碼

1.顯示行 0 - 9 (10 總計, 查詢花費 32.4894 秒)   2.SQL 查詢: SELECT *   3.FROM tables   4.WHERE m_id   5.IN ( 50, 49 )   6.ORDER BY id DESC   7.LIMIT 10   顯示行 0 - 9 (10 總計, 查詢花費 32.4894 秒) SQL 查詢: SELECT * FROM tables WHERE m_id IN ( 50, 49 ) ORDER BY id DESC LIMIT 10 1.顯示行 0 - 9 (10 總計, 查詢花費 0.0497 秒)   2.SQL 查詢: SELECT *   3.FROM tables   4.WHERE m_id   5.IN ( 50, 49 )   6.LIMIT 10    顯示行 0 - 9 (10 總計, 查詢花費 0.0497 秒) SQL 查詢: SELECT * FROM tables WHERE m_id IN ( 50, 49 ) LIMIT 10 1.顯示行 0 - 29 (1,333 總計, 查詢花費 0.0068 秒)   2.SQL 查詢: SELECT *   3.FROM tables   4.WHERE m_id   5.IN ( 50, 49 )   6.ORDER BY id DESC   7.  顯示行 0 - 29 (1,333 總計, 查詢花費 0.0068 秒) SQL 查詢: SELECT * FROM tables WHERE m_id IN ( 50, 49 ) ORDER BY id DESC 1.顯示行 0 - 29 (1,333 總計, 查詢花費 0.12秒)   2.SQL 查詢: SELECT *   3.FROM tables   4.WHERE m_id   5.IN ( 50, 49 )   6.ORDER BY m_id, id DESC   7.  顯示行 0 - 29 (1,333 總計, 查詢花費 0.12秒) SQL 查詢: SELECT * FROM tables WHERE m_id IN ( 50, 49 ) ORDER BY m_id, id DESC   1.顯示行 0 - 29 (1,333 總計, 查詢花費 0.0068 秒)   2.SQL 查詢: SELECT *   3.FROM tables   4.FORCE INDEX ( m_id )     //強制索引   5.WHERE m_id   6.IN ( 50, 49 )   7.ORDER BY id DESC   8.

上面的辦法如果資料量上千萬級也是會很慢的，有可能查詢一次到10秒或更長，

最佳化limit和offset

MySQL的limit工作原理就是先讀取n條記錄，然後拋棄前n條，讀m條想要的，所以n越大，效能會越差。
 

代碼如下	複製代碼
最佳化前SQL: SELECT * FROM member ORDER BY last_active LIMIT 50,5 最佳化後SQL: SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

分別在於，最佳化前的SQL需要更多I/O浪費，因為先讀索引，再讀資料，然後拋棄無需的行。而最佳化後的SQL(子查詢

那條)唯讀索引(Cover index)就可以了，然後通過member_id讀取需要的列。

對mysql伺服器最佳化也可以提升效能了

1、只返回需要的資料

返回資料到用戶端至少需要資料庫提取資料、網路傳輸資料、用戶端接收資料以及用戶端處理資料等環節，如果返

回不需要的資料，就會增加伺服器、網路和用戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：

A、橫向來看，不要寫SELECT *的語句，而是選擇你需要的欄位。

B、縱向來看，合理寫WHERE子句，不要寫沒有WHERE的SQL語句。

C、注意SELECT INTO後的WHERE子句，因為SELECT INTO把資料插入到暫存資料表，這個過程會鎖定一些系統資料表，如果這

個WHERE子句返回的資料過多或者速度太慢，會造成系統資料表長期鎖定，諸塞其他進程。

D、對於彙總查詢，可以用HAVING子句進一步限定返回的行。

2、盡量少做重複的工作

這一點和上一點的目的是一樣的，就是盡量減少無效工作，但是這一點的側重點在用戶端程式，需要注意的如下：

A、控制同一語句的多次執行，特別是一些基礎資料的多次執行是很多程式員很少注意的。

B、減少多次的資料轉換，也許需要資料轉換是設計的問題，但是減少次數是程式員可以做到的。

C、杜絕不必要的子查詢和串連表，子查詢在執行計畫一般解釋成外串連，多餘的串連錶帶來額外的開銷。

D、合并對同一表同一條件的多次UPDATE，比如

代碼如下	複製代碼
UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’ UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’

這兩個語句應該合并成以下一個語句

代碼如下	複製代碼
UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’  WHERE EMP_ID=’ VPA30890F’

E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是效能差別是很大的。

F、不要寫一些沒有意義的查詢，比如: SELECT * FROM EMPLOYEE WHERE 1=2

3、注意事務和鎖

事務是資料庫應用中和重要的工具，它有原子性、一致性、隔離性、持久性這四個屬性，很多操作我們都需要利用

事務來保證資料的正確性。在使用事務中我們需要做到盡量避免死結、盡量減少阻塞。具體以下方面需要特別注意

：

A、事務操作過程要盡量小，能拆分的事務要拆分開來。

B、事務操作過程不應該有互動，因為互動等待的時候，事務並未結束，可能鎖定了很多資源。

C、事務操作過程要按同一順序訪問對象。

D、提高事務中每個語句的效率，利用索引和其他方法提高每個語句的效率可以有效地減少整個事務的執行時間。

E、盡量不要指定鎖類型和索引，SQL SERVER允許我們自己指定語句使用的鎖類型和索引，但是一般情況下，SQL

SERVER最佳化器選擇的鎖類型和索引是在當前資料量和查詢條件下是最優的，我們指定的可能只是在目前情況下更有

，但是資料量和資料分布在將來是會變化的。

F、查詢時可以用較低的隔離等級，特別是報表查詢的時候，可以選擇最低的隔離等級