MySQL數(shù)據(jù)庫優(yōu)化二三事
05
2020-10
今天距2021年56天
這是ITester軟件測試小棧第172次推文
點(diǎn)擊上方藍(lán)字“ITester軟件測試小棧“關(guān)注我，每周一、三、五早上?08:30準(zhǔn)時(shí)推送，每月不定期贈(zèng)送技術(shù)書籍。
微信公眾號(hào)后臺(tái)回復(fù)“資源”、“測試工具包”領(lǐng)取測試資源，回復(fù)“微信群”一起進(jìn)群打怪。
本文4317字，閱讀約需11分鐘
系統(tǒng)現(xiàn)存問題
“系統(tǒng)慢不是問題，只要不崩潰就行”，這可能這是大多數(shù)懶癌技術(shù)派的想法。但是，如果系統(tǒng)經(jīng)常拋出一些故障（硬件問題除外，不過如果磁盤經(jīng)常壞，可能也和性能有關(guān)）。很多時(shí)候就是因?yàn)椋簺]有使用綁定變量、錯(cuò)誤的設(shè)置了一些優(yōu)化器參數(shù)、并發(fā)過大、缺少索引（最普遍）、統(tǒng)計(jì)信息不準(zhǔn)確、SQL寫法不佳、RAC系統(tǒng)按照單節(jié)點(diǎn)設(shè)計(jì)等等一系列性能問題，導(dǎo)致系統(tǒng)壓力過大而出現(xiàn)的狀況。但是懶癌晚期們往往寧愿出故障時(shí)救火，卻不愿意花時(shí)間去優(yōu)化數(shù)據(jù)庫。試想如果系統(tǒng)經(jīng)過全面優(yōu)化，負(fù)載很小，還會(huì)經(jīng)常出各種問題嗎？100%的數(shù)據(jù)庫都是可以優(yōu)化的，CPU降低，資源爭用小，系統(tǒng)就會(huì)更加穩(wěn)定；IO壓力降低，SQL執(zhí)行速度加快，磁盤壽命也會(huì)更長。
設(shè)計(jì)上的問題：單列索引太多，總索引量太多，容易出現(xiàn)索引合并，優(yōu)化器無法選中最優(yōu)索引，間接導(dǎo)致大家使用force index ，又導(dǎo)致了優(yōu)化器無法自動(dòng)智能選則執(zhí)行計(jì)劃。
?
使用上的問題：萬能查詢，多個(gè)接口并用，查詢所有列，force index 濫用 ，單表數(shù)據(jù)量過大，SQL寫法不規(guī)范。
數(shù)據(jù)庫查詢慢的探索
SQL語句執(zhí)行得很慢的原因有哪些？這個(gè)問題可以涉及到 MySQL 的很多核心知識(shí)，就像要考查計(jì)算機(jī)網(wǎng)絡(luò)總問“輸入U(xiǎn)RL回車之后，究竟發(fā)生了什么”一樣。
SQL 語句執(zhí)行的很慢，那是每次執(zhí)行都很慢呢？還是大多數(shù)情況下是正常的，偶爾出現(xiàn)很慢呢？我們得分以下2種情況來討論：
在數(shù)據(jù)量不變的情況下，這條SQL語句一直以來都執(zhí)行的很慢。
大多數(shù)情況是正常的，只是偶爾會(huì)出現(xiàn)很慢的情況。
針對(duì)這兩種情況，我們來分析下可能是哪些原因?qū)е碌摹?/span>
掃描行數(shù)太多；
返回行數(shù)太多；
額外的操作（排序，分組，計(jì)算）；
根據(jù)SQL 執(zhí)行慢的程度，我們再分2種情況具體討論：
這條 SQL 語句一直執(zhí)行的很慢，有如下原因：
沒有用上索引：由于對(duì)字段進(jìn)行運(yùn)算、函數(shù)操作導(dǎo)致無法用索引。
數(shù)據(jù)庫選錯(cuò)了索引。
大多數(shù)情況下很正常，偶爾很慢，有如下原因：
數(shù)據(jù)庫在刷新臟頁，例如 redo log 寫滿了需要同步到磁盤。
執(zhí)行的時(shí)候，遇到鎖，如表鎖、行鎖。
我們可以通過開啟慢查詢?nèi)罩镜姆绞饺ザㄎ挥袉栴}的SQL ，找到問題的根源。
（1）查看MySQL是否開啟慢查詢?nèi)罩荆?br>
show?variables?like?'slow_query_log';
（2）設(shè)置沒有索引的記錄到慢查詢?nèi)罩荆?br>
set?global?log_queries_not_using_indexes=on;
（3）查看超過多長時(shí)間的SQL進(jìn)行記錄到慢查詢?nèi)罩荆?br>
show?variables?like?'long_query_time';
（4）開啟慢查詢?nèi)罩荆?br>
set?global?slow_query_log=on;
（5）設(shè)置超時(shí)時(shí)間：
set?global?long_query_time=5;
--超過5s的語句才記錄日志
（6）查看慢查詢?nèi)罩镜奈恢?：
show?variables?like?'slow%';
數(shù)據(jù)庫優(yōu)化原則
1. 新建表必須設(shè)置主鍵，推薦自增id，無符號(hào)bigint類型。
2. 所有字段必須要有注釋，表必須要有注釋。
3. 所有字段盡量設(shè)置為not null 限制，默認(rèn)值有則給，無則不寫，禁止使用默認(rèn) NULL，字符類型推薦默認(rèn)，以最小滿足使用為宜。
4. 表必須包含邏輯刪除，創(chuàng)建人，創(chuàng)建時(shí)間，修改時(shí)間，備注 ；
5.順序固定一致，且一直保持在表的最后。
例如，如下復(fù)制：
is_delete?TINYINT(1)?UNSIGNED?NOT?NULL?DEFAULT?0?COMMENT?'是否刪除，默認(rèn)0：有效，1：失效。',
createdby?MEDIUMINT(8)?UNSIGNED?NOT?NULL?DEFAULT?0?COMMENT?'創(chuàng)建人',
created?INT(10)?UNSIGNED?NOT?NULL?DEFAULT?0?COMMENT?'創(chuàng)建時(shí)間',
changed_time?DATETIME?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
remarks?VARCHAR(100)?NOT?NULL?DEFAULT?''?COMMENT?'備注，保留字段'
1.最左前綴匹配原則，where條件的順序應(yīng)該盡量與索引列的順序保持一致。
2.盡量選擇區(qū)分度高的列作為索引。
3. 新建索引時(shí)，常用的范圍查詢，最好放在索引的最后面。
4.查看索引離散度 show index from his.tb_api_log。?
5.索引盡量不在字符類型上使用，不頻繁更新的字段上。
6.索引以 idx_字段1_字段2_字段n 命名 字段可簡寫，順序不能亂。
?
連接數(shù)(connection)配置：當(dāng)發(fā)現(xiàn)MySQL有能力處理更多的并發(fā)的時(shí)候， 建議調(diào)大max_connections這個(gè)值，相應(yīng)給服務(wù)器帶來更高的負(fù)載(CPU/IO/內(nèi)存)。
查詢緩存(query_cache)配置：MySQL的查詢緩存用于緩存select查詢結(jié)果，并在下次接收到同樣的查詢請(qǐng)求時(shí)，不再執(zhí)行實(shí)際查詢處理而直接 返回結(jié)果，有這樣的查詢緩存能提高查詢的速度，使查詢性能得到優(yōu)化。
臨時(shí)表緩存(tmp_table_size)配置：MySQL進(jìn)行復(fù)雜查詢或者 做高級(jí)GROUP BY操作的時(shí)候，系統(tǒng)為了優(yōu)化查詢，生成一些臨時(shí)表。一般情況下，MySQL會(huì)先創(chuàng)建內(nèi)存臨時(shí)表，但內(nèi)存臨時(shí)表超過配置指定的值后，MySQL會(huì)將內(nèi)存臨時(shí)表導(dǎo)出到磁盤臨時(shí)表。
索引緩沖區(qū)（key_buffer_size）配置：是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)，key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。
?
硬件層面的優(yōu)化是最后的手段，主要需考慮CPU、存儲(chǔ)、網(wǎng)絡(luò)等幾個(gè)方面。CPU：CPU并不是越多越好，增加CPU數(shù)量并不能提高性能。存儲(chǔ)：機(jī)械磁盤 or SSD（當(dāng)然是SSD更快），單個(gè)大磁盤 or 多個(gè)小磁盤組合使用（單個(gè)1T的磁盤應(yīng)該沒有2個(gè)500G磁盤的組合快，因?yàn)榇疟P的轉(zhuǎn)速都是固定的，兩個(gè)磁盤相當(dāng)于可以并行的讀?。?。
網(wǎng)絡(luò)：一般不是問題，但是在分布式的集群環(huán)境中，各個(gè)數(shù)據(jù)庫節(jié)點(diǎn)之間的網(wǎng)絡(luò)環(huán)境經(jīng)常會(huì)稱為系統(tǒng)的瓶頸。另外，如果服務(wù)端和數(shù)據(jù)庫分布在不同的城市，一條簡單SQL傳輸?shù)臅r(shí)間可能就要幾十毫秒。
數(shù)據(jù)庫優(yōu)化總結(jié)
其實(shí)在需求分析階段就要預(yù)估可能的業(yè)務(wù)量和數(shù)據(jù)量，這樣才能在建表時(shí)有針對(duì)的進(jìn)行設(shè)計(jì)。否則拋開需求談優(yōu)化都是耍流氓，就像這個(gè)世界上沒有包治百病的神藥一樣，也不會(huì)有解決一切問題的完美技術(shù)。所以數(shù)據(jù)庫的設(shè)計(jì)必須和需求有關(guān)系，因?yàn)楸斫Y(jié)構(gòu)也得符合需求，一套數(shù)據(jù)庫的設(shè)計(jì)也是和需求有密切關(guān)系。一個(gè)需求里面會(huì)體現(xiàn)對(duì)某個(gè)表是側(cè)重讀取還是側(cè)重寫入，數(shù)據(jù)設(shè)計(jì)應(yīng)盡可能遵循如下原則：
SQL語句盡可能簡單，大的SQL想辦法拆成小的SQL語句;
事務(wù)要簡單，整個(gè)事務(wù)的時(shí)間長度不要太長，不同事務(wù)更新表的順序需要一致;
注意非按照索引的update造成大面積鎖（應(yīng)當(dāng)先查，再按主鍵更新）;
避免使用觸發(fā)器、函數(shù)、存儲(chǔ)過程、事件;
降低業(yè)務(wù)耦合度（避免萬能查詢，比較嚴(yán)重）;
慎用范圍查詢;
避免在數(shù)據(jù)庫中進(jìn)數(shù)學(xué)運(yùn)算(MySQL不擅長數(shù)學(xué)運(yùn)算和邏輯判斷);
不要用select *，查詢哪幾個(gè)字段就select 這幾個(gè)字段;
禁止不同類型字段做比較，避免隱式轉(zhuǎn)換;
like的參數(shù)以通配符開頭時(shí);
like 盡量使用全文索引(分區(qū)表不支持全文索引);
in里面數(shù)字的個(gè)數(shù)建議控制在1000以內(nèi);
limit分頁注意效率。limit越大，效率越低,可改成延遲關(guān)聯(lián),這是大數(shù)據(jù)量單表查詢中最有效最常用的優(yōu)化方法;
避免使大表的join;
對(duì)大數(shù)據(jù)的更新要分批次更新，不要一次更新太多數(shù)據(jù)（否則可能造成阻塞，鎖爭搶）;
減少與數(shù)據(jù)庫的交互次數(shù)（連接池）;
注意使用性能分析工具;
注意程序捕獲異常,打印日志;
請(qǐng)格式化SQL語句;
多使用explain 查看執(zhí)行計(jì)劃;
?回復(fù)"資源"取干貨個(gè)人微信：Cc2015123
添加請(qǐng)注明來意 :)
