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

本文章來給大家介紹一篇關於mysql中使用order by 和limit查詢變慢解決辦法，因為在mysql分頁利用limit來操作，同時排序也需要使用了order by ，所以經常會兩者同是使用到，但是如果數據量大的話就會碰到查詢很慢很慢，下面我來給大家介紹解決辦法。

先來看一下測試性能

 代碼如下 復制代碼

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、查詢時可以用較低的隔離級別，特別是報表查詢的時候，可以選擇最低的隔離級別