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

有關mysql優化的一些東東整理

編輯: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的緩存中來的話,內存的帶寬就成了瓶頸。

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