Mysql資料庫最佳化

標籤：style   blog   http   io   ar   os   使用   sp   for   

 

to be add...

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

前輩文章原址

http://blog.chinaunix.net/uid-20639775-id-3154234.html

說明：本文的環境為CENTOS 5.5 64 Bit /Mysql 5.1.50

簡介：使用Mysql有一段時間了，期間做了不少關於Mysql最佳化、設計、維護的工作，這兩天有時間做一下簡單的總結，方便自己回憶，同時也希望能對大家有點協助.

I            硬體設定最佳化

• CPU選擇：多核的CPU，主頻高的CPU
• 記憶體：更大的記憶體
• 磁碟選擇：更快的轉速、RAID、陣列卡，
• 網路環境選擇：盡量部署在區域網路、SCI、光纜、千兆網、雙網線提供冗餘、0.0.0.0多連接埠綁定監聽

 

 

II         作業系統級最佳化

• 使用64位的作業系統，更好的使用大記憶體。
• 設定noatime,nodiratime

[[email protected]_server1 ~]$ cat /etc/fstab

LABEL=/         /          ext3    defaults,noatime,nodiratime        1 1

/dev/sda5      /data       xfs     defaults,noatime,nodiratime        1 2

• 最佳化核心參數

net.ipv4.tcp_keepalive_time=7200

net.ipv4.tcp_max_syn_backlog=1024

net.ipv4.tcp_syncookies=1

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

net.ipv4.neigh.default.gc_thresh3 = 2048

net.ipv4.neigh.default.gc_thresh2 = 1024

net.ipv4.neigh.default.gc_thresh1 = 256

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.forwarding = 1

net.ipv4.conf.default.proxy_arp = 0

net.ipv4.tcp_syncookies = 1

net.core.netdev_max_backlog = 2048

net.core.dev_weight = 64

net.ipv4.tcp_rmem = 4096 87380 16777216

net.ipv4.tcp_wmem = 4096 65536 16777216

net.ipv4.tcp_rfc1337 = 1

net.ipv4.tcp_sack = 0

net.ipv4.tcp_fin_timeout = 20

net.ipv4.tcp_keepalive_probes = 5

net.ipv4.tcp_max_orphans = 32768

net.core.optmem_max = 20480

net.core.rmem_default = 16777216

net.core.rmem_max = 16777216

net.core.wmem_default = 16777216

net.core.wmem_max = 16777216

net.core.somaxconn = 500

net.ipv4.tcp_orphan_retries = 1

net.ipv4.tcp_max_tw_buckets = 18000

net.ipv4.ip_forward = 0

net.ipv4.conf.default.proxy_arp = 0

net.ipv4.conf.all.rp_filter = 1

kernel.sysrq = 1

net.ipv4.conf.default.send_redirects = 1

net.ipv4.conf.all.send_redirects = 0

net.ipv4.ip_local_port_range = 5000    65000

kernel.shmmax = 167108864

vm.swappiness=0

• 加大檔案描述符限制

Vim /etc/security/limits.conf

加上

*        soft    nofile  65535

*        hard    nofile  65535

• 檔案系統選擇 xfs

/dev/sda5      /data       xfs     defaults,noatime,nodiratime        1 2

 

 

III      Mysql設計最佳化
III.1儲存引擎的選擇

• Myisam：資料庫並發不大，讀多寫少,而且都能很好的用到索引，sql語句比較簡單的應用，TB資料倉儲
• Innodb：並發訪問大，寫操作比較多，有外鍵、事務等需求的應用，系統記憶體較大。

 

III.2命名規則

• 多數開發語言命名規則：比如MyAdress
• 多數開源思想命名規則：my_address
• 避免隨便命名

 

III.3欄位類型選擇

欄位類型的選擇的一般原則：

• 根據需求選擇合適的欄位類型，在滿足需求的情況下欄位類型儘可能小。
• 只分配滿足需求的最小字元數，不要太慷慨。

原因：更小的欄位類型更小的字元數佔用更少的記憶體，佔用更少的磁碟空間，佔用更少的磁碟IO，以及佔用更少的頻寬。

 

III.3.1  整型：

見如:

類型	位元組	最小值	最大值
		(帶符號的/無符號的)	(帶符號的/無符號的)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

根據滿足需求的最小整數為選擇原則，能用INT的就不要用BIGINT。

用無符號INT儲存IP，而非CHAR(15)。

 

III.3.2  浮點型：

類型	位元組	精度類型	使用情境
FLOAT(M,D)	4	單精確度	精度要求不高，數值比較小
DOUBLE(M,D)（REAL）	8	雙精確度	精度要求不高，數值比較大
DECIMAL(M,D)(NUMERIC）	M+2	自訂精度	精度要求很高的情境

 

 

III.3.3  時間類型

類型	取值範圍	儲存空間	零值標記法
DATE	1000-01-01~9999-12-31	3位元組	0000-00-00
TIME	-838:59:59~838:59:59	3位元組	00:00:00
DATETIME	1000-01-01  00:00:00~9999-12-31 23:59:59	8位元組	0000-00-00 00:00:00
TIMESTAMP	19700101000000~2037年的某個時刻	4位元組	00000000000000
YEAR	YEAR(4):1901~2155 YEAR(2)：1970~2069	1位元組	0000

 

III.3.4  字元類型

類型	最大長度	佔用儲存空間
CHAR[(M)]	M位元組	M位元組
VARCHAR[(M)]	M位元組	M+1位元組
TINYBLOD，TINYTEXT	2^8-1位元組	L+1位元組
BLOB，TEXT	2^16-1位元組	L+2
MEDIUMBLOB，MEDIUMTEXT	2^24-1位元組	L+3
LONGBLOB，LONGTEXT	2^32-1位元組	L+4
ENUM(‘value1‘,‘value2‘,...)	65535個成員	1或2位元組
SET(‘value1‘,‘value2‘,...)	64個成員	1,2,3,4或8位元組

註：L表示可變長度的意思

對於varchar和char的選擇要根據引擎和具體情況的不同來選擇，主要依據如下原則:

1. 如果列資料項目的大小一致或者相差不大，則使用char。
2. 如果列資料項目的大小差異相當大，則使用varchar。
3. 對於MyISAM表，盡量使用Char，對於那些經常需要修改而容易形成片段的myisam和isam資料表就更是如此，它的缺點就是佔用磁碟空間。
4. 對於InnoDB表，因為它的資料行內部儲存格式對固定長度的資料行和可變長度的資料行不加區分（所有資料行共用一個表頭部分，這個標題部分存放著指向各有關資料列的指標），所以使用char類型不見得會比使用varchar類型好。事實上，因為char類型通常要比varchar類型佔用更多的空 間，所以從減少空間佔用量和減少磁碟i/o的角度，使用varchar類型反而更有利。
5. 表中只要存在一個varchar類型的欄位，那麼所有的char欄位都會自動變成varchar類型，因此建議定長和變長的資料分開。

 

 

III.4編碼選擇

單位元組 latin1

多位元組 utf8(漢字佔3個位元組，英文字母佔用一個位元組)

如果含有中文字元的話最好都統一採用utf8類型，避免亂碼的情況發生。

 

 

III.5主鍵選擇原則

註：這裡說的主鍵設計主要是針對INNODB引擎

1. 能唯一的表示行。
2. 顯式的定義一個數實值型別自增欄位的主鍵，這個欄位可以僅用於做主鍵，不做其他用途。
3. MySQL主鍵應該是單列的，以便提高串連和篩選操作的效率。
4. 主鍵欄位類型儘可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。
5. 盡量保證不對主鍵欄位進行更新修改，防止主鍵欄位發生變化，引發資料存放區片段，降低IO效能。
6. MySQL主鍵不應包含動態變化的資料，如時間戳記、建立時間列、修改時間列等。
7. MySQL主鍵應當有電腦自動產生。
8. 主鍵欄位放在資料表的第一順序。

推薦採用數實值型別做主鍵並採用auto_increment屬性讓其自動成長。

 

 

III.6其他需要注意的地方

• NULL OR NOT NULL

儘可能設定每個欄位為NOT NULL，除非有特殊的需求，原因如下：

1. 使用含有NULL列做索引的話會佔用更多的磁碟空間，因為索引NULL列需要而外的空間來儲存。
2. 進行比較的時候，程式會更複雜。
3. 含有NULL的列比較特殊，SQL難最佳化，如果是一個複合式索引，那麼這個NULL 類型的欄位會極大影響整個索引的效率。

 

• 索引

索引的缺點：極大地加速了查詢，減少掃描和鎖定的資料行數。

索引的缺點：佔用磁碟空間，減慢了資料更新速度，增加了磁碟IO。

添加索引有如下原則：

1. 選擇唯一性索引。
2. 為經常需要排序、分組和聯合操作的欄位建立索引。
3. 為常作為查詢條件的欄位建立索引。
4. 限制索引的資料，索引不是越多越好。
5. 盡量使用資料量少的索引，對於大欄位可以考慮首碼索引。
6. 刪除不再使用或者很少使用的索引。
7. 結合核心SQL優先考慮覆蓋索引。
8. 忌用字串做主鍵。

 

• 反範式設計

適當的使用冗餘的反範式設計，以空間換時間有的時候會很高效。

 

IV     Mysql軟體最佳化

• 開啟mysql複製，實現讀寫分離、負載平衡，將讀的負載分攤到多個從伺服器上，提高伺服器的處理能力。
• 使用推薦的GA版本，提升效能
• 利用分區新功能進行大資料的資料拆分

 

 

V        Mysql配置最佳化

注意：全域參數一經設定，隨伺服器啟動預佔用資源。

• key_buffer_size參數

mysql索引緩衝，如果是採用myisam的話要重點設定這個參數，根據（key_reads/key_read_requests）判斷

• innodb_buffer_pool_size參數

INNODB 資料、索引、日誌緩衝最重要的引擎參數，根據（hit riatos和FILE I/O）判斷

• wait_time_out參數

線程串連的逾時時間，盡量不要設定很大，推薦10s

• max_connections參數

伺服器允許的最大串連數，盡量不要設定太大，因為設定太大的話容易導致記憶體溢出，需要通過如下公式來確定：

SET @k_bytes = 1024;

SET @m_bytes = @k_bytes * 1024;

SET @g_bytes = @m_bytes * 1024;

SELECT

      (

 @@key_buffer_size + @@query_cache_size + @@tmp_table_size+

      @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size+

      @@innodb_log_buffer_size+

      @@max_connections *

 

 ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+

         @@join_buffer_size + @@binlog_cache_size + @@thread_stack

        ) )

/ @g_bytes AS MAX_MEMORY_USED_GB;

 

• thread_concurrency參數

線程並發利用數量，(cpu+disk)*2,根據(os中顯示的請求隊列和tickets)判斷

• sort_buffer_size參數

獲得更快的--ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT

• read_rnd_buffer_size參數

當根據鍵進行分類操作時獲得更快的--ORDER BY

• join_buffer_size參數

join串連使用全表掃描串連的緩衝大小，根據select_full_join判斷

• read_buffer_size參數

全表掃描時為查詢預留的緩衝大小，根據select_scan判斷

• tmp_table_size參數

臨時記憶體表的設定，如果超過設定就會轉化成磁碟表，根據參數(created_tmp_disk_tables)判斷

• innodb_log_file_size參數(預設5M)

記錄INNODB引擎的redo log檔案，設定較大的值意味著較長的恢復。

• innodb_flush_method參數(預設fdatasync)

Linux系統可以使用O_DIRECT處理資料檔案，避免OS層級的cache，O_DIRECT模式提高資料檔案和記錄檔的IO提交效能

• innodb_flush_log_at_trx_commit(預設1)

1. 0表示每秒進行一次log寫入cache，並flush log到磁碟。
2. 1表示在每次事務提交後執行log寫入cache，並flush log到磁碟。
3. 2表示在每次事務提交後，執行log資料寫入到cache，每秒執行一次flush log到磁碟。

 

 

VI     Mysql語句級最佳化

1. 效能查的讀語句，在innodb中統計行數,建議另外弄一張統計表，採用myisam，定期做統計.一般的對統計的資料不會要求太精準的情況下適用。
2. 盡量不要在資料庫中做運算。
3. 避免負向查詢和%首碼模糊查詢。
4. 不在索引列做運算或者使用函數。
5. 不要在生產環境程式中使用select * from 的形式查詢資料。只查詢需要使用的列。
6. 查詢儘可能使用limit減少返回的行數，減少資料轉送時間和頻寬浪費。
7. where子句儘可能對查詢列使用函數，因為對查詢列使用函數用不到索引。
8. 避免隱式類型轉換，例如字元型一定要用’’，數字型一定不要使用’’。
9. 所有的SQL關鍵詞用大寫，養成良好的習慣，避免SQL語句重複編譯造成系統資源的浪費。
10. 聯表查詢的時候，記得把小結果集放在前面，遵循小結果集驅動大結果集的原則。
11. 開啟慢查詢，定期用explain最佳化慢查詢中的SQL語句。

Mysql資料庫最佳化