淺談數(shù)據(jù)庫優(yōu)化
在面試的時候經(jīng)常會被問到數(shù)據(jù)庫如何優(yōu)化的？面試的時候最擔心遇到這種寬泛的問題渔嚷。如果你直接說：“建索引” 類似這樣簡單的回答肢专，個人感覺這種回答很不專業(yè)妻枕，面試官也不會喜歡這樣的回答。雖然我們不需要像DBA一樣對數(shù)據(jù)庫有非常深入的研究葛账，但是至少應該對這種問題有個系統(tǒng)全面的回答會比較好柠衅。
當然，我們不單單是為了面試籍琳，在實際生產(chǎn)中我們也需要知道一些最基本的優(yōu)化方法菲宴。就在最近我們組依賴的服務線上就突然出現(xiàn)掛掉的情況贷祈，很久才恢復，后來發(fā)現(xiàn)是出現(xiàn)慢查詢導致數(shù)據(jù)庫連接池撐爆了喝峦，所以掌握數(shù)據(jù)庫優(yōu)化技巧是非常重要的势誊。在這里就總結幾條優(yōu)化方法。
PS：下面介紹的方法都是針對MySQL數(shù)據(jù)庫優(yōu)化谣蠢。
MySQL 架構解析
MySQL架構解析個人感覺這篇文章分析的非常透徹詳細键科。這里就不照搬過來了。
優(yōu)化策略
數(shù)據(jù)類型優(yōu)化
數(shù)據(jù)類型的優(yōu)化主要是指選取什么類型漩怎。需要遵循“小而簡單”的原則勋颖。因為這樣的數(shù)據(jù)類型占用的內(nèi)存、磁盤更低勋锤，CPU處理時間也更少饭玲。舉個常見的例子。
1叁执、日期類型選擇茄厘。MySQL中關于時間類型，MySQL中最小精度是秒谈宛。有Datetime次哈、Timestamp和int三種類型類型來存儲時間，個人推薦使用Datetime吆录。Datetime時間類型存儲的范圍比Timestamp更大窑滞，而且Timestamp因時區(qū)不同而不同，int時間顯示不夠直觀恢筝。
2哀卫、指定最大顯示寬度，不會改變存儲空間撬槽。顯示寬度與存儲大小或類型包含的值的范圍無關此改。也就是說對存儲和計算來講，指定了長度的int(1)和int(12)是相同的侄柔。
3共啃、?unsigned(無符號)屬性不允許有負值，這可以使正數(shù)存儲范圍擴大一倍暂题，比如UNSIGNED TINYINT存儲的范圍是0 - 255移剪，TINYINT的范圍是-128 ~ 127。所以在沒有用到負數(shù)的情況下敢靡，建議用unsigned(無符號)挂滓。
4、存儲IP地址時最好使用無符號整數(shù)，而不是字符串赶站，這樣可以節(jié)省存儲空間幔虏，Inet_ATON()將帶點兒的IP轉為數(shù)字，而Inet_NTOA可將數(shù)字轉為IP贝椿。
5想括、對一些精度要求比較高的數(shù)據(jù)，有人建議使用DECIMAL,decimal需要額外的空間和計算開銷烙博。建議使用BIGINT瑟蜈，在需要精確到千分之一的時候，可以先乘以1000渣窜，再用BIGINT存铺根。
6、通常情況下列最好為NOT NULL, NULL 會使得索引失效乔宿。
7位迂、當數(shù)據(jù)量比較大的時候，不推薦使用alter table详瑞。因為alter table 會創(chuàng)建一個新結構的表掂林，并把老表中的數(shù)據(jù)插入到新表中。
8坝橡、不推薦使用Enum泻帮。因為枚舉類型是固定的字符串列表，添加和刪除的時候需要使用alter table命令计寇。
索引優(yōu)化
MySQL優(yōu)化最重要建立索引锣杂，建立好的索引可以起到事半功倍的效果。
索引結構與算法
首先要介紹索引的結構饲常。索引數(shù)據(jù)結構與原理可以參考這篇文章：MySQL索引背后的數(shù)據(jù)結構及算法原理蹲堂。這里不做過多的解釋狼讨。
看過這篇文章之后拋出一個問題：為什么不用Hash索引贝淤，而是使用B-Tree索引結構，理論上Hash索引的查詢時間復雜度只有O(1)政供。這里主要有以下5點原因：
1播聪、hash函數(shù)計算后的結果，是隨機的,如果是在磁盤上放置數(shù)據(jù)布隔。比主鍵為id為例, 那么隨著id的增長, id對應的行,在磁盤上隨機放置.
2离陶、不法對范圍查詢進行優(yōu)化。
3衅檀、無法利用前綴索引. 比如 在btree中, field列的值“hellopworld”,并加索引招刨。查詢 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引，(左前綴索引)哀军。因為hash(‘helloword’),和hash(‘hello’),兩者的關系仍為隨機沉眶。
4打却、排序也無法優(yōu)化。
5谎倔、必須回行.就是說 通過索引拿到的只是數(shù)據(jù)位置,而不是數(shù)據(jù)值柳击，必須回到表中取數(shù)據(jù)。
索引注意點
理想的索引應該具有下面幾點：
1: 查詢頻繁 2: 區(qū)分度高 3: 長度小 4: 盡量能覆蓋常用查詢字段片习。哪些列不應該建立索引呢捌肴？
1、更新非常頻繁的字段不適合建立索引藕咏。2状知、唯一性太差的字段不適合單獨創(chuàng)建索引。3孽查、不會出現(xiàn)在where子句中的字段不應該創(chuàng)建索引试幽。索引覆蓋
索引覆蓋是指 如果查詢的列恰好是索引的一部分,那么查詢只需要在索引文件上進行,不需要回行到磁盤再找數(shù)據(jù).
這種查詢速度非常快,稱為”索引覆蓋”不要用UUID或者隨機字符串作為主鍵值卦碾，盡量用連續(xù)增長的值
對于innodb而言,因為節(jié)點下有數(shù)據(jù)文件,因此節(jié)點的分裂將會比較慢铺坞。對于innodb的主鍵,盡量用整型,而且是遞增的整型。如果是無規(guī)律的數(shù)據(jù),將會產(chǎn)生頁的分裂洲胖，影響速度济榨。關于UUID與自增主鍵的比較參看這篇文章索引不是創(chuàng)建的越多越好。過多的索引不可能一次性讀取到內(nèi)存绿映，索引是以索引文件的形式存儲在磁盤上擒滑。過多的索引是會產(chǎn)生磁盤I/O消耗，從而影響性能叉弦。
特定語句優(yōu)化
Count化
MyIsam的count()比較快丐一，原因是MyIsam對行數(shù)進行了存儲。一旦有條件的查詢, 速度就不再快了淹冰，尤其是where條件的列上沒有索引库车。
假如,id<100的商家都是我們內(nèi)部測試的,我們想查查真實的商家有多少?
select count(*) from lx_com where id>=100; (1000多萬行用了6.X秒)
小技巧:
select count(*) from lx_com; 快
select count(*) from lx_com where id<100; 快
Join優(yōu)化
Join語句經(jīng)常聽到“小結果集驅(qū)動大結果集”。為什么會有這句話樱拴？這和Mysql的關聯(lián)查詢原理有關柠衍。 Mysql的關聯(lián)查詢是取第一張表的一行數(shù)據(jù)去遍歷第二張表的所有數(shù)據(jù)找到匹配的行，依次遍歷第一張表的數(shù)據(jù)晶乔。有人會問MN和NM結果不是一樣的嗎珍坊？“小結果集驅(qū)動大結果集”，這句話的前提是連接字段建立了索引正罢。具體可以看下面的例子：
SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)
上述代碼的執(zhí)行可以參照下面的偽代碼：
outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
inner_row = inner_iterator.next;
while(inner_row) {
output[inner_row.yy,outer_row.xx];
inner_row = inner_iterator.next;
}
outer_row = outer_iterator.next;
}
假設我們在A和B表的c列都建立了索引阵漏，可以看到外層循環(huán)無法用到索引一定會遍歷M次，但是內(nèi)層循環(huán)可以利用索引減少內(nèi)存循環(huán)的次數(shù)，如果B的數(shù)據(jù)量大的話履怯，優(yōu)化效果還是非炒ㄎ蓿可觀的。
group by優(yōu)化
Group by的實質(zhì)是先排序然后分組虑乖。所以建議在group by中利用索引懦趋，這樣可以減少臨時表的創(chuàng)建以及文件排序。order by的列要和group by的一致疹味，否則也會引起臨時表
（原因是因為group by 和 order by 都需要排序仅叫，如果2者的列不一致，那必須經(jīng)過至少1次排序）糙捺。以A诫咱，B表連接為例 ,主要查詢A表的列, 那么 group by ,order by 的列盡量相同,而且列應該顯示聲明為A的列。
select A.id,A.cat_id from A inne join B group by A.cat_id order by A.cat_id
Limit優(yōu)化
當表的數(shù)據(jù)非常多的時候洪灯，limit的分頁優(yōu)化可以用延遲索引坎缭。比如我們要查詢5000000后的10條記錄，用下面的語句效率是非常低的签钩。
select id,name from lx_com limit 5000000,10;
這是因為limit offset,N, 當offset非常大時, 效率極低,
可以先在子查詢語句里利用覆蓋索引掃描掏呼，然后再做一個關聯(lián)查詢，這種技術就是延遲索引
铅檩。SQL語句如下：
select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);
Union優(yōu)化
建議使用union all 而不是union憎夷。union all 不過濾 效率提高,如非必須,請用union all。因為 union去重的代價非常高, Mysql會把各個查詢結果插入到臨時表中昧旨，然后做唯一性檢查拾给。所以請放在程序里去重。
性能分析工具
開啟慢查詢?nèi)罩?/h4>
![]()
image.png
MySQL 慢查詢的相關參數(shù)解釋：
slow_query_log ：是否開啟慢查詢?nèi)罩荆?表示開啟兔沃，0表示關閉蒋得。
log-slow-queries ：舊版（5.6以下版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健乒疏？梢圆辉O置該參數(shù)额衙，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健缰雇？梢圆辉O置該參數(shù)入偷，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log
long_query_time ：慢查詢閾值，當查詢時間多于設定的閾值時械哟，記錄日志。
使用profile語句
打開profile分析語句
查看分析列表
查看單條語句的執(zhí)行過程
使用explain語句
如果要定量分析查詢語句涉及到了多少行殿雪，可以使用explain暇咆。explain語句是非常重要的分析工具。提測之前使用explain分析一下SQL語句是一種美德。explain可以顯示如下字段：
關于每個字段的值的解釋可以參考這篇文章
這里會選擇我認為比較重要幾個字段的值通過列子進行講解爸业。
select_type 查詢類型
- simple語句中沒有子查詢或者union
select field from table;
- dependent subquery 子查詢中的第一個select語句其骄，依賴外部查詢結果集
select * from test.tabname where id in(select id from test.tabname2 where name='love');
以上語句有個錯誤的理解是認為按照下面兩個結果執(zhí)行
select group_concat(id) from test.tabname2 where name='love';
--內(nèi)層查詢結果：1,3,5,7,9,11,13,15,17,1
select * from test.tabname where id in(1,3,5,7,9,11,13,15,17,19);
通過explain發(fā)現(xiàn)其實他是先根據(jù)關聯(lián)外部tabname, 而不是先去執(zhí)行這個子查詢〕犊酰可以通過連接查詢來優(yōu)化上面的語句拯爽。
select tabname. * from test.tabname inner join test.tabname2 using(id) where tabname2.name='love';
可以看到查詢類型變成了simple簡單查詢。
- primary最外層的select, 例子參見dependent subquery
type：很重要,顯示了連接使用了哪種類別,有無使用索引钧忽。type代表查詢執(zhí)行計劃（QEP）中指定的表使用的連接方式
從最好到最差依次為：：system毯炮，const，eq_ref耸黑，ref桃煎，fulltext，ref_or_null大刊，unique_subquery为迈，index_subquery，range缺菌，index_merge葫辐，index，ALL
system: const的一個特例伴郁，表中只有一條記錄
-
const: where條件是以常量為單位另患，表中最多一條記錄匹配。
image.png -
eq_ref：最多只會有一條匹配結果蛾绎，一般是通過主鍵或是唯一索引來訪問昆箕。一般會出現(xiàn)在連接查詢的語句中。通過索引列,直接引用某1行數(shù)據(jù)
image.png -
ref: 它返回所有匹配某個單個值的行租冠。它用在關聯(lián)操作只使用了索引的最左前綴鹏倘，或者索引不是UNIQUE和PRIMARY KEY。連接查詢中被驅(qū)動的表索引引用查詢.顽爹。通過索引列,可以直接引用到某些數(shù)據(jù)行
image.png range: 引用范圍掃描纤泵，見上面的例子
all: 全表掃描效率最低
possible key 可能用到哪些索引進行查詢
key 實際用到的索引
key_len 實際用到的索引字節(jié)數(shù)
關于key_len大小的計算可以參照這篇文章
ref 列出是通過哪個字段來進行連接查詢，或者是否是通過常量（const）
extra 是EXPLAIN輸出中另外一個很重要的列镜粤，該列顯示MySQL在查詢過程中的一些詳細信息捏题。
重點關注下面幾個值：
Using filesort：文件排序(文件可能在磁盤,也可能在內(nèi)存)。
Using temporary：是指用上了臨時表, group by 與order by 不同列時,或group by ,order by 別的表的列.
Using index：表示使用索引肉渴，如果只有 Using index公荧，說明他沒有查詢到數(shù)據(jù)表，只用索引表就完成了這個查詢同规，這個叫覆蓋索引循狰，效率非常高窟社。
Using index condition：意味著查詢列的某一部分無法直接使用索引
Using where：過濾元素的時候出現(xiàn)，也會掃描表绪钥，但是如果在條件語句中存在索引列灿里，會優(yōu)先使用帶索引的條件。explain SELECT * from test where b = '4' （b不是索引程腹，全表掃描后匣吊，通過過濾獲取所需數(shù)據(jù)）
總結
本文先介紹了MySQL的架構，然后從數(shù)據(jù)類型寸潦、索引色鸳、性能分析三個角度描述如何進行數(shù)據(jù)庫優(yōu)化。相信當面試官再問你如何進行數(shù)據(jù)庫優(yōu)化的時候甸祭。你不會簡單的回答“建索引”缕碎。最后提醒大家：提測前請explain。
數(shù)據(jù)庫優(yōu)化是常見的面試題池户，更多資料請見：https://zfau4tzxp5.feishu.cn/docx/ByM0dxtWoo87QtxnWtTcG0kCnpg
