I 硬體配置優化
CPU選擇：多核的CPU，主頻高的CPU
內存：更大的內存
磁碟選擇：更快的轉速、RAID、陣列卡，
網路環境選擇：盡量部署在區域網、SCI、光纜、千兆網、雙網線提供冗餘、0.0.0.0多埠綁定監聽
II 操作系統級優化
使用64位的操作系統，更好的使用大內存。
設置noatime,nodiratime
[zhangxy@dowload_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)
表示每秒進行一次log寫入cache，並flush log到磁碟。
表示在每次事務提交後執行log寫入cache，並flush log到磁碟。
表示在每次事務提交後，執行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 多表統計大量數據
從語句本身來看，應該沒有太多優化的餘地了
count(distinct a._url) CUrl 這里那個distinct完全可以不要。因為你的a._url在group by裡面，已經完成了distinct功能了。你也知道distinct得支出的。
建議：
1) 用view, 譬如t_gw_merge_log a, t_gw_netuser u on a._uid=u._id 建立view,其他依次類推。我曾經作過9個表的連接(DB2)，用了view之後，性能提升了30%。。 view只是把你的表裡面只需要的欄位拿出來從而減少表得掃描量。
語法：CREATE VIEW tab_view AS SELECT field1, file2 , filed3 FROM tab WHERE...
2) 因為sum(p._ul) SUl,sum(p._dl) SDl這兩個聚合函數引起的，因為相對應的t_gw_applog 這個表數據也很多。如果是因為這個，就要建立適當的索引了。下午有時間再研究下。