前言
本文主要針對(duì)的是關(guān)系型數(shù)據(jù)數(shù)據(jù)庫(kù)MySql。鍵值類數(shù)據(jù)庫(kù)可以參考最簡(jiǎn)大數(shù)據(jù)Redis献雅。先簡(jiǎn)單梳理下Mysql的基本概念管挟，然后分創(chuàng)建時(shí)和查詢時(shí)這兩個(gè)階段的優(yōu)化展開(kāi)。
1.0 基本概念簡(jiǎn)述
1.1 邏輯架構(gòu)
第一層：客戶端通過(guò)連接服務(wù)今艺，將要執(zhí)行的sql指令傳輸過(guò)來(lái)
第二層：服務(wù)器解析并優(yōu)化sql韵丑，生成最終的執(zhí)行計(jì)劃并執(zhí)行
第三層：存儲(chǔ)引擎，負(fù)責(zé)數(shù)據(jù)的儲(chǔ)存和提取
1.2 鎖
數(shù)據(jù)庫(kù)通過(guò)鎖機(jī)制來(lái)解決并發(fā)場(chǎng)景-共享鎖（讀鎖）和排他鎖（寫(xiě)鎖）虚缎。讀鎖是不阻塞的撵彻，多個(gè)客戶端可以在同一時(shí)刻讀取同一個(gè)資源钓株。寫(xiě)鎖是排他的，并且會(huì)阻塞其他的讀鎖和寫(xiě)鎖陌僵。簡(jiǎn)單提下樂(lè)觀鎖和悲觀鎖轴合。
樂(lè)觀鎖，通常用于數(shù)據(jù)競(jìng)爭(zhēng)不激烈的場(chǎng)景碗短，多讀少寫(xiě)受葛，通過(guò)版本號(hào)和時(shí)間戳實(shí)現(xiàn)。
悲觀鎖偎谁，通常用于數(shù)據(jù)競(jìng)爭(zhēng)激烈的場(chǎng)景总滩，每次操作都會(huì)鎖定數(shù)據(jù)。
要鎖定數(shù)據(jù)需要一定的鎖策略來(lái)配合巡雨。
表鎖闰渔，鎖定整張表，開(kāi)銷最小铐望，但是會(huì)加劇鎖競(jìng)爭(zhēng)冈涧。
行鎖，鎖定行級(jí)別蝌以，開(kāi)銷最大炕舵，但是可以最大程度的支持并發(fā)。
但是MySql的存儲(chǔ)引擎的真實(shí)實(shí)現(xiàn)不是簡(jiǎn)單的行級(jí)鎖跟畅，一般都是實(shí)現(xiàn)了多版本并發(fā)控制（MVCC）咽筋。MVCC是行級(jí)鎖的變種，多數(shù)情況下避免了加鎖操作徊件，開(kāi)銷更低奸攻。MVCC是通過(guò)保存數(shù)據(jù)的某個(gè)時(shí)間點(diǎn)快照實(shí)現(xiàn)的。
1.3 事務(wù)
事務(wù)保證一組原子性的操作虱痕，要么全部成功睹耐，要么全部失敗。一旦失敗部翘，回滾之前的所有操作硝训。MySql采用自動(dòng)提交，如果不是顯式的開(kāi)啟一個(gè)事務(wù)新思，則每個(gè)查詢都作為一個(gè)事務(wù)窖梁。
隔離級(jí)別控制了一個(gè)事務(wù)中的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見(jiàn)的夹囚。四種常見(jiàn)的隔離級(jí)別：
未提交讀（Read UnCommitted）纵刘，事務(wù)中的修改，即使沒(méi)提交對(duì)其他事務(wù)也是可見(jiàn)的荸哟。事務(wù)可能讀取未提交的數(shù)據(jù)假哎，造成臟讀瞬捕。
提交讀（Read Committed），一個(gè)事務(wù)開(kāi)始時(shí)舵抹，只能看見(jiàn)已提交的事務(wù)所做的修改肪虎。事務(wù)未提交之前，所做的修改對(duì)其他事務(wù)是不可見(jiàn)的掏父。也叫不可重復(fù)讀笋轨，同一個(gè)事務(wù)多次讀取同樣記錄可能不同。
可重復(fù)讀（RepeatTable Read）赊淑，同一個(gè)事務(wù)中多次讀取同樣的記錄結(jié)果時(shí)結(jié)果相同爵政。
可串行化（Serializable），最高隔離級(jí)別陶缺，強(qiáng)制事務(wù)串行執(zhí)行钾挟。
1.4 存儲(chǔ)引擎
InnoDB引擎，最重要饱岸，使用最廣泛的存儲(chǔ)引擎掺出。被用來(lái)設(shè)計(jì)處理大量短期事務(wù)，具有高性能和自動(dòng)奔潰恢復(fù)的特性苫费。
MyISAM引擎汤锨，不支持事務(wù)和行級(jí)鎖，奔潰后無(wú)法安全恢復(fù)百框。
2.0 創(chuàng)建時(shí)優(yōu)化
2.1 Schema和數(shù)據(jù)類型優(yōu)化
整數(shù)
TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存儲(chǔ)8,16,24,32,64位存儲(chǔ)空間闲礼。使用Unsigned表示不允許負(fù)數(shù)，可以使正數(shù)的上線提高一倍铐维。
實(shí)數(shù)
Float,Double , 支持近似的浮點(diǎn)運(yùn)算柬泽。
Decimal，用于存儲(chǔ)精確的小數(shù)嫁蛇。
字符串
VarChar锨并，存儲(chǔ)變長(zhǎng)的字符串。需要1或2個(gè)額外的字節(jié)記錄字符串的長(zhǎng)度睬棚。
Char第煮，定長(zhǎng)，適合存儲(chǔ)固定長(zhǎng)度的字符串抑党，如MD5值包警。
Blob，Text 為了存儲(chǔ)很大的數(shù)據(jù)而設(shè)計(jì)的新荤。分別采用二進(jìn)制和字符的方式。
時(shí)間類型
DateTime台汇，保存大范圍的值苛骨，占8個(gè)字節(jié)篱瞎。
TimeStamp，推薦痒芝，與UNIX時(shí)間戳相同俐筋，占4個(gè)字節(jié)。
優(yōu)化建議點(diǎn)
盡量使用對(duì)應(yīng)的數(shù)據(jù)類型严衬。比如澄者，不要用字符串類型保存時(shí)間，用整型保存IP请琳。
選擇更小的數(shù)據(jù)類型粱挡。能用TinyInt不用Int。
標(biāo)識(shí)列（identifier column）俄精，建議使用整型询筏，不推薦字符串類型，占用更多空間竖慧，而且計(jì)算速度比整型慢嫌套。
不推薦ORM系統(tǒng)自動(dòng)生成的Schema，通常具有不注重?cái)?shù)據(jù)類型圾旨，使用很大的VarChar類型踱讨，索引利用不合理等問(wèn)題。
真實(shí)場(chǎng)景混用范式和反范式砍的。冗余高查詢效率高痹筛，插入更新效率低；冗余低插入更新效率高挨约，查詢效率低味混。
創(chuàng)建完全的獨(dú)立的匯總表緩存表，定時(shí)生成數(shù)據(jù)诫惭，用于用戶耗時(shí)時(shí)間長(zhǎng)的操作翁锡。對(duì)于精確度要求高的匯總操作，可以采用 歷史結(jié)果+最新記錄的結(jié)果 來(lái)達(dá)到快速查詢的目的夕土。
數(shù)據(jù)遷移馆衔，表升級(jí)的過(guò)程中可以使用影子表的方式，通過(guò)修改原表的表名怨绣，達(dá)到保存歷史數(shù)據(jù)角溃，同時(shí)不影響新表使用的目的。
2.2 索引
索引包含一個(gè)或多個(gè)列的值篮撑。MySql只能高效的利用索引的最左前綴列减细。索引的優(yōu)勢(shì)：
減少查詢掃描的數(shù)據(jù)量
避免排序和零時(shí)表
將隨機(jī)IO變?yōu)轫樞騃O （順序IO的效率高于隨機(jī)IO）
B-Tree
使用最多的索引類型。采用B-Tree數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)數(shù)據(jù)（每個(gè)葉子節(jié)點(diǎn)都包含指向下一個(gè)葉子節(jié)點(diǎn)的指針赢笨，從而方便葉子節(jié)點(diǎn)的遍歷）未蝌。B-Tree索引適用于全鍵值驮吱，鍵值范圍，鍵前綴查找萧吠，支持排序左冬。
B-Tree索引限制：
如果不是按照索引的最左列開(kāi)始查詢，則無(wú)法使用索引纸型。
不能跳過(guò)索引中的列拇砰。如果使用第一列和第三列索引，則只能使用第一列索引狰腌。
如果查詢中有個(gè)范圍查詢除破，則其右邊的所有列都無(wú)法使用索引優(yōu)化查詢。
哈希索引
只有精確匹配索引的所有列癌别，查詢才有效皂岔。存儲(chǔ)引擎會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼，哈希索引將所有的哈希碼存儲(chǔ)在索引中展姐，并保存指向每個(gè)數(shù)據(jù)行的指針躁垛。
哈希索引限制：
無(wú)法用于排序
不支持部分匹配
只支持等值查詢?nèi)?，IN（）圾笨，不支持 < >
優(yōu)化建議點(diǎn)
注意每種索引的適用范圍和適用限制教馆。
索引的列如果是表達(dá)式的一部分或者是函數(shù)的參數(shù)，則失效擂达。
針對(duì)特別長(zhǎng)的字符串土铺，可以使用前綴索引，根據(jù)索引的選擇性選擇合適的前綴長(zhǎng)度板鬓。
使用多列索引的時(shí)候悲敷，可以通過(guò) AND 和 OR 語(yǔ)法連接。
重復(fù)索引沒(méi)必要俭令，如（A后德，B）和（A）重復(fù)。
索引在where條件查詢和group by語(yǔ)法查詢的時(shí)候特別有效抄腔。
將范圍查詢放在條件查詢的最后瓢湃，防止范圍查詢導(dǎo)致的右邊索引失效的問(wèn)題。
索引最好不要選擇過(guò)長(zhǎng)的字符串赫蛇，而且索引列也不宜為null绵患。
3.0 查詢時(shí)優(yōu)化
3.1 查詢質(zhì)量的三個(gè)重要指標(biāo)
響應(yīng)時(shí)間 （服務(wù)時(shí)間，排隊(duì)時(shí)間）
掃描的行
返回的行
3.2 查詢優(yōu)化點(diǎn)
避免查詢無(wú)關(guān)的列悟耘，如使用Select * 返回所有的列落蝙。
避免查詢無(wú)關(guān)的行
切分查詢。將一個(gè)對(duì)服務(wù)器壓力較大的任務(wù)，分解到一個(gè)較長(zhǎng)的時(shí)間中筏勒，并分多次執(zhí)行赚瘦。如要?jiǎng)h除一萬(wàn)條數(shù)據(jù)，可以分10次執(zhí)行奏寨，每次執(zhí)行完成后暫停一段時(shí)間，再繼續(xù)執(zhí)行鹰服。過(guò)程中可以釋放服務(wù)器資源給其他任務(wù)病瞳。
分解關(guān)聯(lián)查詢。將多表關(guān)聯(lián)查詢的一次查詢悲酷，分解成對(duì)單表的多次查詢套菜。可以減少鎖競(jìng)爭(zhēng)设易，查詢本身的查詢效率也比較高逗柴。因?yàn)镸ySql的連接和斷開(kāi)都是輕量級(jí)的操作，不會(huì)由于查詢拆分為多次顿肺，造成效率問(wèn)題戏溺。
注意count的操作只能統(tǒng)計(jì)不為null的列，所以統(tǒng)計(jì)總的行數(shù)使用count（*）屠尊。
group by 按照標(biāo)識(shí)列分組效率高旷祸，分組結(jié)果不宜出行分組列之外的列。
關(guān)聯(lián)查詢延遲關(guān)聯(lián)讼昆，可以根據(jù)查詢條件先縮小各自要查詢的范圍托享，再關(guān)聯(lián)。
Limit分頁(yè)優(yōu)化浸赫∪蛭В可以根據(jù)索引覆蓋掃描，再根據(jù)索引列關(guān)聯(lián)自身查詢其他列既峡。如
SELECTid,NAME,ageWHEREstudent s1INNER JOIN (SELECT idFROM studentORDER BY ageLIMIT 50,5) AS s2 ON s1.id = s2.id
Union查詢默認(rèn)去重羡榴，如果不是業(yè)務(wù)必須，建議使用效率更高的Union All
后記
歡迎大家在評(píng)論中補(bǔ)充涧狮，我會(huì)把大家補(bǔ)充的內(nèi)容持續(xù)更新出來(lái)炕矮。如果有幫助，請(qǐng)幫忙點(diǎn)喜歡者冤。
補(bǔ)充更新日志
2017.09.08
---->來(lái)自大神-小寶
1.條件中的字段類型和表結(jié)構(gòu)類型不一致肤视，mysql會(huì)自動(dòng)加轉(zhuǎn)換函數(shù)，導(dǎo)致索引作為函數(shù)中的參數(shù)失效涉枫。
2.like查詢前面部分未輸入邢滑，以%開(kāi)頭無(wú)法命中索引。
3.補(bǔ)充2個(gè)5.7版本的新特性：
generated column，就是數(shù)據(jù)庫(kù)中這一列由其他列計(jì)算而得
CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLEAS (sidea * sideb / 2));insert into triangle(sidea, sideb) values(3, 4);select * from triangle;
+-------+-------+------+
| sidea | sideb | area |
+-------+-------+------+
| 3 | 4 | 6 |
+-------+-------+------+
支持JSON格式數(shù)據(jù)困后，并提供相關(guān)內(nèi)置函數(shù)
CREATE TABLE json_test (name JSON);INSERT INTO json_test VALUES('{"name1": "value1", "name2": "value2"}');SELECT * FROM json_test WHERE JSON_CONTAINS(name, '$.name1');
---->來(lái)自JVM專家-達(dá)
關(guān)注explain在性能分析中的使用
EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"
select_type乐纸，有幾種值：simple（表示簡(jiǎn)單的select，沒(méi)有union和子查詢）摇予，primary（有子查詢汽绢，最外面的select查詢就是primary），union（union中的第二個(gè)或隨后的select查詢侧戴，不依賴外部查詢結(jié)果）宁昭，dependent union（union中的第二個(gè)或隨后的select查詢，依賴外部查詢結(jié)果）
type酗宋，有幾種值：system（表僅有一行（=系統(tǒng)表）积仗，這是const連接類型的一個(gè)特例），const（常量查詢）, ref(非唯一索引訪問(wèn)蜕猫，只有普通索引)寂曹，eq_ref（使用唯一索引或組件查詢），all（全表查詢）回右，index（根據(jù)索引查詢?nèi)恚┞≡玻瑀ange（范圍查詢）
possible_keys: 表中可能幫助查詢的索引
key，選擇使用的索引
key_len翔烁，使用的索引長(zhǎng)度
rows匾灶，掃描的行數(shù)，越大越不好
extra租漂，有幾種值：Only index（信息從索引中檢索出阶女，比掃描表快），where used（使用where限制）哩治，Using filesort （可能在內(nèi)存或磁盤(pán)排序）秃踩，Using temporary（對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表）
微信掃描二維碼，關(guān)注JAVA高級(jí)架構(gòu)公眾號(hào)业筏，精彩文章面試題憔杨，一起學(xué)習(xí)進(jìn)步
Java工程化、高性能及分布式蒜胖、高性能消别、深入淺出。高架構(gòu)台谢。性能調(diào)優(yōu)寻狂、Spring，MyBatis朋沮，Netty源碼分析和大數(shù)據(jù)等多個(gè)知識(shí)點(diǎn)蛇券。如果你想拿高薪的，想學(xué)習(xí)的，想就業(yè)前景好的纠亚，想跟別人競(jìng)爭(zhēng)能取得優(yōu)勢(shì)的塘慕，想進(jìn)阿里面試但擔(dān)心面試不過(guò)的，你都可以來(lái)蒂胞，群號(hào)為：647631030
注：加群要求
1图呢、具有1-5工作經(jīng)驗(yàn)的，面對(duì)目前流行的技術(shù)不知從何下手骗随，需要突破技術(shù)瓶頸的可以加岳瞭。
2、在公司待久了蚊锹，過(guò)得很安逸，但跳槽時(shí)面試碰壁稚瘾。需要在短時(shí)間內(nèi)進(jìn)修牡昆、跳槽拿高薪的可以加。
3摊欠、如果沒(méi)有工作經(jīng)驗(yàn)丢烘，但基礎(chǔ)非常扎實(shí)，對(duì)java工作機(jī)制些椒，常用設(shè)計(jì)思想播瞳，常用java開(kāi)發(fā)框架掌握熟練的，可以加免糕。
4赢乓、覺(jué)得自己很牛B，一般需求都能搞定石窑。但是所學(xué)的知識(shí)點(diǎn)沒(méi)有系統(tǒng)化牌芋，很難在技術(shù)領(lǐng)域繼續(xù)突破的可以加。
5.阿里Java高級(jí)大牛直播講解知識(shí)點(diǎn)松逊，分享知識(shí)躺屁，多年工作經(jīng)驗(yàn)的梳理和總結(jié)，帶著大家全面经宏、科學(xué)地建立自己的技術(shù)體系和技術(shù)認(rèn)知犀暑！
6.小號(hào)或者小白之類加群一律不給過(guò)，謝謝烁兰。
