程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL延遲聯系關系機能優化辦法

MySQL延遲聯系關系機能優化辦法

編輯:MySQL綜合教程

MySQL延遲聯系關系機能優化辦法。本站提示廣大學習愛好者:(MySQL延遲聯系關系機能優化辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL延遲聯系關系機能優化辦法正文


【配景】

  某營業數據庫load 報警異常,cpu usr 到達30-40 ,居高不下。應用對象檢查數據庫正在履行的sql ,排在後面的年夜部門是:

SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;

表的數據量年夜致有36w閣下,該sql是一個異常典范的排序+分頁查詢:order by col limit N,OFFSET M , MySQL 履行此類sql時須要先掃描到N行,然後再去取 M行。關於此類年夜數據量的排序操作,取後面多數幾行數據會很快,然則越靠後,sql的機能就會越差,由於N越年夜,MySQL 須要掃描不須要的數據然後在丟失落,如許消耗年夜量的時光。

【剖析】

針對limit 優化有許多種方法,
1 前端加緩存,削減落到庫的查詢操作
2 優化SQL
3 應用書簽方法 ,記載前次查詢最新/年夜的id值,向後追溯 M行記載。
4 應用Sphinx 搜刮優化。
關於第二種方法 我們推舉應用"延遲聯系關系"的辦法來優化排序操作,何謂"延遲聯系關系" :經由過程應用籠罩索引查詢前往須要的主鍵,再依據主鍵聯系關系原表取得須要的數據。

【處理】

依據延遲聯系關系的思緒,修正SQL 以下:

優化前

root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows   | Extra                       |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
| 1  | SIMPLE      | relation    | range | ind_endtime   | ind_endtime | 9       | NULL | 349622 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

其履行時光:

優化後:

SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id


root@xxx 12:33:43>explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
3 rows in set (0.36 sec)

履行時光:

優化後 履行時光 為本來的1/3 。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved