| 導購 | 订阅 | 在线投稿
分享
 
 
 

MySQL數據庫中對前端和後台進行系統優化

來源:互聯網  2008-06-01 03:05:23  評論

本文中介紹的系統優化,主要針對前端和後台這兩方面(後台方面主要對SQL語句和數據存儲進行了優化),下文中我們將介紹一些優化技巧和經驗。

技巧:

1. 如何查出效率低的語句?

在MySQL下,在啓動參數中設置 --log-slow-queries=[文件名],就可以在指定的日志文件中記錄執行時間超過long_query_time(缺省爲10秒)的SQL語句。你也可以在啓動配置文件中修改long query的時間,如:

# Set long query time to 8 seconds

long_query_time=8

2. 如何查詢某表的索引?

可使用SHOW INDEX語句,如:

SHOW INDEX FROM [表名]

3. 如何查詢某條語句的索引使用情況?

可用EXPLAIN語句來看一下某條SELECT語句的索引使用情況。如果是UPDATE或DELETE語句,需要先轉換爲SELECT語句。

4. 如何把導出INNODB引擎的內容到錯誤日志文件中?

我們可以使用SHOW INNODB STATUS命令來查看INNODB引擎的很多有用的信息,如當前進程、事務、外鍵錯誤、死鎖問題和其它一些統計數據。如何讓該信息能記錄在日志文件中 呢?只要使用如下語句創建innodb_monitor表,MySQL就會每15秒鍾把該系統寫入到錯誤日志文件中:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

如果你不再需要導出到錯誤日志文件,只要刪除該表即可:

DROP TABLE innodb_monitor;

5. 如何定期刪除龐大的日志文件?

只要在啓動配置文件中設置日志過期時間即可:

expire_logs_days=10

注意事項:

1. 重點關注索引

下面以表TSK_TASK表爲例說明SQL語句優化過程。TSK_TASK表用于保存系統監測任務,相關字段及索引如下:

ID:主鍵;

MON_TIME:監測時間;建了索引;

STATUS_ID:任務狀態;與SYS_HIER_INFO.ID建立了外鍵關系。

注MySQL自動會爲外鍵建立索引,在本次優化過程中,發現這些自動建立的外鍵索引會對SQL語句的效率産生不必要的幹擾,需要特別注意!

首先,我們在日志文件中查到下面語句的執行比較慢,超過10秒了:

# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143

select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';

原來在88143條記錄中要查出符合條件的295條記錄,那當然慢了。趕緊用EXPLAIN語句看一下索引使用情況吧:

+----+-------------+----------+------+----------

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+------+-----------

| 1 | SIMPLE | TSK_TASK | ref | FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME | FK_task_status_id_TO_SYS_HIER_INFO | 9 | const | 276168 | Using where |

+----+-------------+----------+------+-----------

可以看出,有兩個索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME,而最終執行語句時采用了STATUS_ID上的外鍵索引。

再看一下TSK_TASK表的索引情況吧:

+----------+------------------------------------

| Table | Key_name | Column_name | Cardinality |

+----------+------------+-----------------------

| TSK_TASK | PRIMARY | ID | 999149 |

| TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16 |

| TSK_TASK | TSK_TASK_KEY_MON_TIME | MON_TIME | 13502 |

+----------+------------------------------------

在Oracle或其他關系數據庫下,WHERE條件中的字段順序對索引的選擇起著很重要的作用。我們調整一下字段順序,把STATUS_ID放在後面,再EXPLAIN一下:

EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;

但是沒什麽效果,MySQL還是選用系統建立的STATUS_ID外鍵索引。

仔細分析一下,看來Cardinality屬性(即索引中的唯一值的個數)對索引的選擇起了極其重要的作用,MySQL選擇了索引值唯一值個數小的那個索引作爲整條語句的索引。

針對這條語句,如果使用FK_task_status_id_TO_SYS_HIER_INFO做索引,而TSK_TASK表中存放很多天數據的話,那掃描的記錄數會很多,速度較慢。可以有以下幾個優化方案:

如果一天的任務數不多的話,我們刪除索引FK_task_status_id_TO_SYS_HIER_INFO,那MySQL會使用索引TSK_TASK_KEY_MON_TIME,然後在該天的數據中在掃描STATUS_ID爲1064的記錄,那速度也不慢;

如果一天的任務數多的話,我們需刪除索引FK_task_status_id_TO_SYS_HIER_INFO和TSK_TASK_KEY_MON_TIME,然後再建立STATUS_ID,MON_TIME的聯合索引,這樣效率肯定會很高。

因此建議,對那些記錄數多的表,建議不要使用外鍵,以避免造成性能效率的嚴重降低。

2. 盡量控制每張表的記錄數

當一張表的記錄數很大時,管理和維護就會很麻煩,如索引維護就會占用很長時間,從而會給系統的正常運行造成很大的幹擾。

對隨時間推移數據量不斷增長的表,我們可以根據時間來區分實時數據和曆史數據,可以使用後台服務程序定期移動實時表中的數據到曆史表中,從而控制實時表的 記錄數,提高查詢和操作效率。但注意每次移動的時間要足夠短,不要影響正常程序的數據寫入。如果占用時間太長,可能會造成死鎖問題。

3. 數據散列(partition)策略

當客戶數達到一定規模後,單個數據庫將無法支撐更高的並發訪問,此時可以考慮把客戶數據散列(partition)到多個數據庫中,以分擔負載,提高系統的整體性能與效率。

本文中介紹的系統優化,主要針對前端和後台這兩方面(後台方面主要對SQL語句和數據存儲進行了優化),下文中我們將介紹一些優化技巧和經驗。 技巧: 1. 如何查出效率低的語句? 在MySQL下,在啓動參數中設置 --log-slow-queries=[文件名],就可以在指定的日志文件中記錄執行時間超過long_query_time(缺省爲10秒)的SQL語句。你也可以在啓動配置文件中修改long query的時間,如: # Set long query time to 8 seconds long_query_time=8 2. 如何查詢某表的索引? 可使用SHOW INDEX語句,如: SHOW INDEX FROM [表名] 3. 如何查詢某條語句的索引使用情況? 可用EXPLAIN語句來看一下某條SELECT語句的索引使用情況。如果是UPDATE或DELETE語句,需要先轉換爲SELECT語句。 4. 如何把導出INNODB引擎的內容到錯誤日志文件中? 我們可以使用SHOW INNODB STATUS命令來查看INNODB引擎的很多有用的信息,如當前進程、事務、外鍵錯誤、死鎖問題和其它一些統計數據。如何讓該信息能記錄在日志文件中 呢?只要使用如下語句創建innodb_monitor表,MySQL就會每15秒鍾把該系統寫入到錯誤日志文件中: CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; 如果你不再需要導出到錯誤日志文件,只要刪除該表即可: DROP TABLE innodb_monitor; 5. 如何定期刪除龐大的日志文件? 只要在啓動配置文件中設置日志過期時間即可: expire_logs_days=10 注意事項: 1. 重點關注索引 下面以表TSK_TASK表爲例說明SQL語句優化過程。TSK_TASK表用于保存系統監測任務,相關字段及索引如下: ID:主鍵; MON_TIME:監測時間;建了索引; STATUS_ID:任務狀態;與SYS_HIER_INFO.ID建立了外鍵關系。 注MySQL自動會爲外鍵建立索引,在本次優化過程中,發現這些自動建立的外鍵索引會對SQL語句的效率産生不必要的幹擾,需要特別注意! 首先,我們在日志文件中查到下面語句的執行比較慢,超過10秒了: # Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143 select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23'; 原來在88143條記錄中要查出符合條件的295條記錄,那當然慢了。趕緊用EXPLAIN語句看一下索引使用情況吧: +----+-------------+----------+------+---------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+----------- | 1 | SIMPLE | TSK_TASK | ref | FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME | FK_task_status_id_TO_SYS_HIER_INFO | 9 | const | 276168 | Using where | +----+-------------+----------+------+----------- 可以看出,有兩個索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME,而最終執行語句時采用了STATUS_ID上的外鍵索引。 再看一下TSK_TASK表的索引情況吧: +----------+------------------------------------ | Table | Key_name | Column_name | Cardinality | +----------+------------+----------------------- | TSK_TASK | PRIMARY | ID | 999149 | | TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16 | | TSK_TASK | TSK_TASK_KEY_MON_TIME | MON_TIME | 13502 | +----------+------------------------------------ 在Oracle或其他關系數據庫下,WHERE條件中的字段順序對索引的選擇起著很重要的作用。我們調整一下字段順序,把STATUS_ID放在後面,再EXPLAIN一下: EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064; 但是沒什麽效果,MySQL還是選用系統建立的STATUS_ID外鍵索引。 仔細分析一下,看來Cardinality屬性(即索引中的唯一值的個數)對索引的選擇起了極其重要的作用,MySQL選擇了索引值唯一值個數小的那個索引作爲整條語句的索引。 針對這條語句,如果使用FK_task_status_id_TO_SYS_HIER_INFO做索引,而TSK_TASK表中存放很多天數據的話,那掃描的記錄數會很多,速度較慢。可以有以下幾個優化方案: 如果一天的任務數不多的話,我們刪除索引FK_task_status_id_TO_SYS_HIER_INFO,那MySQL會使用索引TSK_TASK_KEY_MON_TIME,然後在該天的數據中在掃描STATUS_ID爲1064的記錄,那速度也不慢; 如果一天的任務數多的話,我們需刪除索引FK_task_status_id_TO_SYS_HIER_INFO和TSK_TASK_KEY_MON_TIME,然後再建立STATUS_ID,MON_TIME的聯合索引,這樣效率肯定會很高。 因此建議,對那些記錄數多的表,建議不要使用外鍵,以避免造成性能效率的嚴重降低。 2. 盡量控制每張表的記錄數 當一張表的記錄數很大時,管理和維護就會很麻煩,如索引維護就會占用很長時間,從而會給系統的正常運行造成很大的幹擾。 對隨時間推移數據量不斷增長的表,我們可以根據時間來區分實時數據和曆史數據,可以使用後台服務程序定期移動實時表中的數據到曆史表中,從而控制實時表的 記錄數,提高查詢和操作效率。但注意每次移動的時間要足夠短,不要影響正常程序的數據寫入。如果占用時間太長,可能會造成死鎖問題。 3. 數據散列(partition)策略 當客戶數達到一定規模後,單個數據庫將無法支撐更高的並發訪問,此時可以考慮把客戶數據散列(partition)到多個數據庫中,以分擔負載,提高系統的整體性能與效率。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有