內(nèi)容從底層到應(yīng)用層的結(jié)構(gòu)來描述椒功，最后簡單介紹了下監(jiān)控和架構(gòu)
ps: mysql系統(tǒng)5.6
硬件/OS系統(tǒng)
1.優(yōu)化硬件:更好的cpu更好的內(nèi)存以及SSD等
2.調(diào)整內(nèi)核最大文件數(shù)
查看最大文件數(shù): sysctl fs.file-max
臨時修改: sysctl -w fs.file-max=6553500
永久生效則: echo "fs.file-max=6553500" >>/etc/sysctl.conf
3.優(yōu)化網(wǎng)絡(luò):
- DNS配置 盡量使用skip-name-resolve來減少因解析帶來的不必要麻煩士修、
- 檢查網(wǎng)絡(luò)的ping 丟包率
- 通過優(yōu)化/etc/sysctl.cnf 中的網(wǎng)絡(luò)參數(shù)耳鸯，提升性能，以下便是參考值
net.ipv4.ip_local_port_range = 1024 65535 改變本地的端口范圍
net.ipv4.tcp_max_syn_backlog = 4096 允許更多的連接進(jìn)入隊列
net.ipv4.tcp_fin_timeout = 30 對于只在本地使用的數(shù)據(jù)庫服務(wù)器慧域，可以縮短tcp保持狀態(tài)的超時時間鲤竹、默認(rèn)1分鐘
數(shù)據(jù)庫系統(tǒng)配置
并發(fā)性能
max_connections:
MySQL的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請求量比較大昔榴，建議調(diào)高此值，以增加并行連接數(shù)量碘橘，當(dāng)然這建立在機(jī)器能支撐的情況下互订，因為如果連接數(shù)越多，介于MySQL會為每個連接提供連接緩沖區(qū)痘拆，就會開銷越多的內(nèi)存仰禽，所以要適當(dāng)調(diào)整該值
thread_concurrency: 并發(fā)數(shù)
線程狀態(tài):
SHOW STATUS like '%thread%';
Threads_cached 緩存的 通過thread_cache_size設(shè)置
Threads_connected 當(dāng)前連接數(shù)
Threads_created 已經(jīng)創(chuàng)建的數(shù)量
Connections:歷史連接數(shù)
未命中 = Treads_created/Connections
如果未命中率比較高的話，可以提高thread_cache_size,增加并發(fā)性能back_log
MySQL能暫存的連接數(shù)量纺蛆。當(dāng)主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求吐葵，這就起作用。如果MySQL的連接數(shù)據(jù)達(dá)到 max_connections時桥氏，新來的請求將會被存在堆棧中温峭，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log字支，如果等待連接的數(shù)量超過 back_log凤藏，將不被授予連接資源。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在堆棧中堕伪。只有如果期望在一個短時間內(nèi)有很多連接揖庄，你需要增加它，換句話說欠雌，這值對到來的TCP/IP連接的偵聽隊列的大小蹄梢。open_files_limit:
MySQL打開的文件描述符限制，默認(rèn)最小1024;當(dāng)open_files_limit沒有被配置的時候富俄，比較max_connections*5和ulimit -n的值禁炒，哪個大用哪個,同時還受到系統(tǒng)內(nèi)核設(shè)置的影響qcache,維護(hù)復(fù)雜，在多cpu的場合下性能沒有優(yōu)勢蛙酪，同時8.0之后官方貌似不再支持齐苛，可以通過memcached或者redis等來做緩存
sync_binlog
控制binlog同步到硬盤的頻率。
- 設(shè)置為0時桂塞，由文件系統(tǒng)控制同步或者緩存滿了后才同步凹蜂，性能最好，但可能會丟失數(shù)據(jù)；
- 設(shè)置為1時玛痊，同步每一條事務(wù)汰瘫，性能最差，但不會丟失數(shù)據(jù)擂煞；
- 設(shè)置為N時混弥，表示每N條同步一次，同樣对省，可能會丟失N條數(shù)據(jù)
- innod引擎
- innodb-open-files:
innodb_open_files的大小對InnoDB效率的影響比較小蝗拿。但是在InnoDBcrash的情況下，innodb_open_files設(shè)置過小會影響recovery的效率蒿涎。所以用InnoDB的時候還是把innodb_open_files放大一些比較合適哀托。 - innodb_buffer_pool_size
專用的數(shù)據(jù)庫服務(wù)器下需要設(shè)置成物理內(nèi)存的80%大小。不要設(shè)置太大劳秋，以免因此與操作系統(tǒng)進(jìn)行分頁競爭仓手。注意，在32位系統(tǒng)中玻淑，每個進(jìn)程被限制在2-3.5G嗽冒，因此不要設(shè)置太高。 -
innodb_buffer_pool_instances
當(dāng)innodb_buffer_pool_size設(shè)置超過1G的時候补履，可以通過這個變量來切分innodb_buffer,每個分片都有自己的lru算法添坊，通過hash
算法分片，從而提高并發(fā)性能干像。為了達(dá)到最好的性能帅腌，innodb_buffer_pool_size最好設(shè)置為GB((2^30)的整數(shù)倍 - innodb_log_file_size
在一個日志組中，每個log的大小麻汰。結(jié)合innodb_buffer_pool_size設(shè)置其大小速客，25%-100%。避免不需要的刷新五鲫。注意：這個值分配的大小和數(shù)據(jù)庫的寫入速度溺职，事務(wù)大小，異常重啟后的恢復(fù)有很大的關(guān)系位喂。一般取256M可以兼顧性能和recovery的速度浪耘。
上限為每個日志上限大小為4G.一般控制在幾個Log文件相加大小在2G以內(nèi)為佳。具體情況還需要看你的事務(wù)大小塑崖，數(shù)據(jù)大小為依據(jù)七冲。 - innodb_log_files_in_group
有多少個innodb_log,這個是用來寫redo log的，這幾個文件相當(dāng)于ring隊列.一般設(shè)置為2-3规婆，寫操作頻繁的話澜躺，可以稍微大些蝉稳，減少IO操作 - innodb_flush_log_at_trx_commit
innodb引擎ib_logfile的刷新方式- 設(shè)置為0:表示每隔一秒把log buffer刷到文件系統(tǒng)中(log file)去，并且調(diào)用文件系統(tǒng)的“flush”操作將緩存刷新到磁盤上去, mysql崩潰會丟掉最后一秒內(nèi)的事務(wù)掘鄙；
- 設(shè)置為1(默認(rèn)為1),在每次事務(wù)提交的時候?qū)og buffer 中的數(shù)據(jù)都會寫入到log file耘戚，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步,不會丟失數(shù)據(jù)；
- 設(shè)置為2,表示在每次事務(wù)提交的時候會把log buffer刷到文件系統(tǒng)中去操漠，但并不會立即刷寫到磁盤收津。如果只是MySQL數(shù)據(jù)庫掛掉了，由于文件系統(tǒng)沒有問題浊伙，那么對應(yīng)的事務(wù)數(shù)據(jù)并沒有丟失撞秋。只有在數(shù)據(jù)庫所在的主機(jī)操作系統(tǒng)損壞或者突然掉電的情況下，數(shù)據(jù)庫的事務(wù)數(shù)據(jù)可能丟失1秒之類的事務(wù)數(shù)據(jù)嚣鄙。這樣的好處部服，減少了事務(wù)數(shù)據(jù)丟失的概率，而對底層硬件的IO要求也沒有那么高(log buffer寫到文件系統(tǒng)中拗慨，一般只是從log buffer的內(nèi)存轉(zhuǎn)移的文件系統(tǒng)的內(nèi)存緩存中，對底層IO沒有壓力)奉芦。
注: 大多數(shù)情況下赵抢，對數(shù)據(jù)的一致性并沒有很嚴(yán)格的要求，所以并不會把 sync_binlog 配置成 1. 為了追求高并發(fā)声功，提升性能烦却，可以設(shè)置為 100 或直接用 0.
而和 innodb_flush_log_at_trx_commit 一樣，對于支付服務(wù)這樣的應(yīng)用先巴，還是比較推薦 sync_binlog = 1
sql語句調(diào)優(yōu)
語句調(diào)優(yōu)的種類太多太多了其爵，需要具體情況具體分析，
大致思路
通過slow_query_log找到耗時的語句伸蚯，再通過explain調(diào)優(yōu), 還可以通過 profiling 查看詳細(xì)的時間耗費,另外GUI工具通常也會給出執(zhí)行時間
profile 功能默認(rèn)是關(guān)閉的摩渺，需要開啟，用完后記得關(guān)閉
SET profiling=1;
SHOW profiles; // 所有的
SHOW profile FOR QUERY 175; // 某個特定的query的性能
下面詳細(xì)解釋 explain
explain 解釋
explain select * from ads_task_play_log;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+
執(zhí)行結(jié)果重要字段解釋:
select_type: select 語句類型
- SIMPLE:簡單SELECT(不使用UNION或子查詢)
- PRIMARY:最外面的SELECT
- UNION:UNION中的第二個或后面的SELECT語句
- DEPENDENT UNION:UNION中的第二個或后面的SELECT語句,取決于外面的查詢
- UNION RESULT:UNION 的結(jié)果
- SUBQUERY:子查詢中的第一個SELECT
- DEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決于外面的查詢
- DERIVED:導(dǎo)出表的SELECT(FROM子句的子查詢)
possible_keys: 用的那個索引
- key:顯示MySQL實際決定使用的鍵(索引)剂邮。如果沒有選擇索引,鍵是NULL
- key_len:使用索引的長度
type: The join type(聯(lián)接類型),幾種常見的,效率從高到低
- const:常數(shù)查找摇幻，如:主鍵，唯一索引挥萌，會很快绰姻，因為它們只讀取一次!
- eq_ref:對于每個來自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯(lián)接類型,除了const類型引瀑。
- ref:基于連接的查找
- range:基于索引的范圍查找
- index:基于索引的掃描狂芋。該聯(lián)接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數(shù)據(jù)文件小憨栽。
- ALL:對于每個來自于先前的表的行組合,進(jìn)行完整的表掃描
extra: 而外信息帜矾，非常重要
當(dāng)extra中如果出現(xiàn)Using temporary或者Using filesort時翼虫，這說明我們的sql語句就需要進(jìn)行優(yōu)化了。
對于Using temporary黍特，當(dāng)查詢涉及多張表時蛙讥，需要將查詢結(jié)果放入第三張臨時表中來存放。這樣勢必會降低我們的查詢效率;
對于Using filesort,當(dāng)查詢需要排序同時又沒有用到索引的時候灭衷，會用filesort來排序(全表掃)次慢，當(dāng)然會比索引要慢，但不一定是決定性因素翔曲，可以通過order by null來關(guān)閉排序迫像，做對比測試.
filesort的優(yōu)化:
1.走索引(給需要order by 或者group by 的添加組合索引)
2.優(yōu)化配置,走更高效的索引算法.主要的配置有max_length_for_sort_data和sort_buffer_size
3.去掉不必要的返回字
簡單的調(diào)優(yōu)
查看表的詳細(xì)信息:
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'
FROM information_schema.TABLES WHERE table_schema LIKE 'table_name';
- 索引優(yōu)化
- 盡量用索引數(shù)據(jù)⊥椋可以通過explain查看優(yōu)化.
- 一個表的索引最好不要超過7個闻妓，太多的話，會影響寫入性能掠械，如果更新不頻繁的表由缆，可以適當(dāng)?shù)脑黾铀饕?/li>
- 對于組合(多個字段)索引，越是離散的字段越是要靠前, 查詢順序要和聯(lián)合索引順序保持一致
- 無論是組合索引還是單個列的索引猾蒂，盡量不要選擇那些唯一性很低的字段,比如說均唉，在只有兩個值0和1的字段上建立索引沒有多大意義
- 數(shù)據(jù)量優(yōu)化
- 盡可能只選取需要的數(shù)據(jù)
- 限制結(jié)果集，通過分頁處理肚菠，獲取數(shù)據(jù)
- 盡量不要超過兩個表的聯(lián)表操作
- 不要用存儲過程舔箭，不好維護(hù)和移植
監(jiān)控
硬件: cpu和內(nèi)存(通過telgraf或者zabbix等監(jiān)控系統(tǒng)處理)
-
數(shù)據(jù)庫:
日志系統(tǒng)
log_output 控制log文件輸出位置:table,還是file,還是不輸出.- 慢日志
通過long_query_time設(shè)置時間閾值，超過這個閾值同時slow_query_log又是開啟的話蚊逢，會寫入到log中.
相關(guān)配置變量:
slow_query_log 用來控制是否開啟
slow_query_log_file 慢日志保存位置
log_queries_not_using_indexes 是否記錄沒有使用索引的查詢
log_throttle_queries_not_using_indexes 每分鐘記錄沒有使用索引查詢的最大條數(shù)
min_examined_row_limit: 查詢結(jié)果小于這個行數(shù)的层扶，則不記入日志
格式化慢日志:
mysqldumpslow -s at,al /var/lib/mysql/suse11b-slow.log - general log
全量log,記錄所有的log而不僅僅是查詢log, 不用的時候要關(guān)閉或者定時刪除，會消耗很多資源
general_log 是否開啟general_log
general_log_file 日志輸出位置 - 錯誤日志
log_error 變量查看文件路徑
5.7后烙荷，可以通過 log_error_verbosity控制錯誤級別 - QPS和TPS(這兩個目前沒什么用)
QPS:
每隔一段時間獲取一次queries值镜会，通過差值求均值,當(dāng)qps超過1W，需要小心了(為什么呢?)
TSP:
事務(wù)總數(shù) = Com_commit + Com_rollback ,同樣也是每隔一段時間獲取一個值奢讨，再求均值稚叹。超過4k，就要小心了(同樣也不知道為什么這定) - 死鎖:
查詢是否鎖表:
show OPEN TABLES where In_use > 0;
查詢進(jìn)程:
show processlist / show full processlist
查看正在鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
查看 innodb 死鎖日志:
SHOW ENGINE INNODB STATUS;
死鎖日志上半部分說明了事務(wù)1在等待什么鎖 - 追蹤長時間事務(wù)
- 慢日志
SELECT
trx.trx_id
,trx.trx_started
,trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX AS trx
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl
ON trx.trx_mysql_thread_id = pl.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 59 SECOND
AND pl.user <> 'system_user';
架構(gòu)優(yōu)化
- 通過redis或memcached添加緩存拿诸，減少數(shù)據(jù)庫的調(diào)用
- 分庫分表
- 讀寫分離
- 熱備份和雙活等
