16效能優化

16效能優化

===============

索引

合理使用索引可以提高資料訪問速度
索引是否被合理使用可以使用show status或者mysqladmin擴充套件命來檢視

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 456   |
+-----------------------+-------+
6 rows in set (0.00 sec)

其中：
 Handler_read_key這個值表示了一個行被索引讀的次數，值高表示索引使用率較高
 Handler_read_rnd_next這個值表示按照順序讀下一行的申請次數，值高表示查詢效率偏低，需要建立合力的索引

表中索引被建立後，分析表可以減少碎片，提高訪問效率
mysql> analyze table a,b;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| an.a  | analyze | status   | OK       |
| an.b  | analyze | status   | OK       |
+-------+---------+----------+----------+
2 rows in set (0.03 sec)

--------------

查詢快取記憶體

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 9437184 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

have_query_cache  是否設定了查詢快取記憶體
query_cache_size  表示分配的查詢快取記憶體的大小，為0則表示關閉了查詢告訴快取
query_cache_type  值範圍為0到2，0或者off表示查詢告訴快取關閉，1表示查詢告訴快取已經開啟，但使用sql_no_cache選項的select語句除外，2或者demand根據需要按照執行帶sql_cache選項的select語句提供查詢告訴快取

查詢中使用或者不是用查詢告訴快取可以在查詢中是用關鍵字sql_cache,sql_no_cache來控制

mysql> select sql_cache * from a;
+----+------+
| id | name |
+----+------+
|  1 | ab   |
|  2 | abc  |
|  3 | abcd |
|  4 | ann  |
+----+------+
4 rows in set (0.00 sec)

mysql> select sql_no_cache * from a;
+----+------+
| id | name |
+----+------+
|  1 | ab   |
|  2 | abc  |
|  3 | abcd |
|  4 | ann  |
+----+------+
4 rows in set (0.00 sec)

----------------

分析查詢

類似於oracle的檢視執行計劃

mysql> explain select * from a;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from a where id=1 union select * from a;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY      | a          | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
|  2 | UNION        | a          | ALL   | NULL          | NULL    | NULL    | NULL  |    4 |       |
|NULL | UNION RESULT | | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |       |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.02 sec)

各個列的含義：
id   查詢中色了傳統的位置
table  查詢的表名
select_type 查詢型別，包括是否有子查詢，union，外部查詢，外部查詢中的子查詢等
type  連線的執行情況，const：連線的最佳種類，all：掃描所有資料後才得到結果
possible_keys  提高查詢速度可以使用的索引
key   實際使用的鍵，包括在key_len列中顯示的鍵長度
rows  查詢到資料的長度
extra  其他資訊，如mysql如何處理查詢結果等資訊

---------------

優化多表查詢

mysql中連線效果比子查詢好
避免使用巢狀
使用中間變數減少查詢層次

----------------

使用臨時表

使用臨時表存放中間資訊可以提高效能

-----------------

優化表設計

合理的欄位選擇，欄位長度選擇
optimize table

------------------

調整伺服器設定

mysql優化調整首先想到的是調整key_buffer_size和table_cache

key_buffer_size  mysql索引緩衝可以使用的記憶體量，一般建議使用武力記憶體的25%到30%
table_cache    表快取記憶體使用的記憶體量，與其有關的引數還有max_connections
         mysql建議table_cache=max_connections*n,n為標準連線中表的數量

mysql> select @@table_cache;
+---------------+
| @@table_cache |
+---------------+
|           700 |
+---------------+
1 row in set (0.00 sec)

mysql> show variables like '%table_cache%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 700   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global table_cache=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@table_cache;
+---------------+
| @@table_cache |
+---------------+
|          1000 |
+---------------+
1 row in set (0.00 sec)

這樣設定重啟資料庫後設定將會恢復

其他的優化方法：
 增加sort_buffer可以提高order by和group by語句的查詢速度
 增加read_rnd_buffer_size變數可以提高分類行的速度
 增加read_buffer_size可以提高讀快取進而提高select效率
 增加binlog_cache_size增加二進位制日誌快取區，可以提高日誌的處理速度
 增加bulk_insert_buffer_size可以提高批量插入速度，但只能在myisam表中生效
 增加thread_cache_size可以控制每個程式分配的記憶體量，如果連線很多的話可以做到更好的控制

--------------

基準技術
 mysql benchmark suite
 要求能夠執行perl，包括perl dbi套件和mysql資料庫驅動程式（dbd）
 perl -e "use DBI"
  

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13177610/viewspace-684360/，如需轉載，請註明出處，否則將追究法律責任。