有關mysql最佳化的一些東東整理

1.查詢協助 ? contents
2.使用合成的散列值,分離BLOB或者TEXT
3.貨幣使用定點數(decimal或者numberic)
4.sql_mode?
5.order by rand() limit 1000;
6.最佳化show status like 'Com' Com_select Com_insert等查看插入多還是查詢多
7.Handler_read_key 的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的效能改善不高，因為索引並不經常使用.Handler_read_rnd_next 的值高則意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明表索引不正確或寫入的查詢沒有利用索引.
8.定期分析表 ANALYZE TABLE CHECK TABLE CHECKSUM TABLE
9.最佳化表 OPTIMIZE TABLE
10.匯入大資料:Myisam ALTER TABLE tblname DISABLE KEYS loading the data ALTER TABLE tblname ENABLE KEYS;
Innodb SET UNIQUE_CHECKS=0 SET AUTOCOMMIT=0
11.最佳化insert:LOAD DATA INFILE replace ignore
12.最佳化group by ORDER BY NULL
13.show status like 'Table%'; show status like 'innodb_row_lock%';
14.CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
15.影響Mysql 效能的重要參數:
key_buffer_size : 說明：金鑰快取(變數key_buffer_size) 被所有線程共用；伺服器使用的其它緩衝則根據需要分配。此參數只適用於myisam 儲存引擎。
table_cache:資料庫中開啟表的緩衝數量。table_cache 與max_connections 有關。例如，對於200 個並行啟動並執行串連，應該讓表的緩至少有200 * N，這裡N 是可以執行的查詢的一個聯結中表的最大數量。還需要為暫存資料表和檔案保留一些額外的檔案描述符。
innodb_buffer_pool_size:緩衝InnoDB 資料和索引的記憶體緩衝區的大小。你把這個值設得越高，訪問表中資料需要得磁碟I/O 越少。

innodb_flush_log_at_trx_commit:0|1|2
innodb_additional_mem_pool_size:1M
innodb_table_locks:0|1
innodb_lock_wait_timeout:
innodb_support_xa:通過該參數設定是否支援分散式交易，預設值是ON 或者1，表示支援分散式交易。如果確認應用中不需要使用分散式交易，則可以關閉這個參數，減少磁碟排清的次數並獲得更好的InnoDB 效能。
innodb_doublewrite:
innodb_log_buffer_size:
innodb_log_file_size:

1.資料庫的設計
盡量把資料庫設計的更小的占磁碟空間.
1).儘可能使用更小的整數類型.(mediumint就比int更合適).
2).儘可能的定義欄位為not null,除非這個欄位需要null.（這個規則只適合欄位為KEY的情形）
3).如果沒有用到變長欄位的話比如varchar,那就採用固定大小的紀錄格式比如char.（CHAR 總是比VARCHR快）
4).表的主索引應該儘可能的短.這樣的話每條紀錄都有名字標誌且更高效.
5).只建立確實需要的索引。索引有利於檢索記錄，但是不利於快速儲存記錄。如果總是要在表的組合欄位上做搜尋，那麼就在這些欄位上建立索引。索引的第一部分必須是最常使用的欄位.如果總是需要用到很多欄位，首先就應該多複製這些欄位，使索引更好的壓縮。
（這條只適合MYISAM引擎的表，對於INNODB則在儲存記錄的時候關係不大，因為INNODB是以事務為基礎的，如果想快速儲存記錄的話，特別是大批量的匯入記錄的時候）
6).所有資料都得在儲存到資料庫前進行處理。
7).所有欄位都得有預設值。
8).在某些情況下,把一個頻繁掃描的表分成兩個速度會快好多。在對動態格式表掃描以取得相關記錄時，它可能使用更小的靜態格式表的情況下更是如此。
(具體的表現為：MYISAM表的MERGE類型，以及MYISAM和INNODB通用的分區，詳情見手冊)
9).不會用到外鍵約束的地方盡量不要使用外鍵。

2.系統的用途
1).及時的關閉對MYSQL的串連。
2).explain 複雜的SQL語句。（這樣能確定你的SELECT 語句怎麼最佳化最佳）
3).如果兩個關聯表要做比較話，做比較的欄位必須類型和長度都一致.（在資料龐大的時候建立INDEX）
4).LIMIT語句盡量要跟order by或者 distinct.這樣可以避免做一次full table scan.
5).如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename.
不過有一個問題，truncate 不會在交易處理中復原。因為她要調用create table 語句。
（Truncate Table 語句先刪除表然後再重建，這個是屬於檔案層級的，所以自然快N多）
實測例子：
song2為INNODB表。

複製代碼 代碼如下:mysql> select count(1) from song2;
+----------+
| count(1) |
+----------+
| 500000 |
+----------+
1 row in set (0.91 sec)
mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)

6).能使用STORE PROCEDURE 或者 USER FUNCTION的時候.（ROUTINE總是減少了伺服器端的開銷）
7).在一條insert語句中採用多重紀錄插入格式.而且使用load data infile來匯入大量資料，這比單純的indert快好多.（在MYSQL中具體表現為：INSERT INTO TABLEQ VALUES (),(),...();）
（還有就是在MYISAM表中插入大量記錄的時候先禁用到KEYS後面再建立KEYS,具體表現語句：
ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
而對於INNNODB 表在插入前先 set autocommit=0;完了後：set autocommit=1;這樣效率比較高。）
8).經常OPTIMIZE TABLE 來整理片段.
9).還有就是date 類型的資料如果頻繁要做比較的話盡量儲存在unsigned int 類型比較快。

3.系統的瓶頸
1).磁碟搜尋.
並行搜尋,把資料分開存放到多個磁碟中，這樣能加快搜尋時間。
2).磁碟讀寫(IO)
可以從多個媒介中並行的讀取資料。
3).CPU周期
資料存放在主記憶體中.這樣就得增加CPU的個數來處理這些資料。
4).記憶體頻寬
當CPU要將更多的資料存放到CPU的緩衝中來的話,記憶體的頻寬就成了瓶頸。