前言
本文主要針對的是關(guān)系型數(shù)據(jù)數(shù)據(jù)庫MySql。鍵值類數(shù)據(jù)庫可以參考最簡大數(shù)據(jù)Redis藤违。先簡單梳理下Mysql的基本概念浪腐，然后分創(chuàng)建時和查詢時這兩個階段的優(yōu)化展開。
1.0 基本概念簡述
1.1 邏輯架構(gòu)
第一層：客戶端通過連接服務(wù)顿乒，將要執(zhí)行的sql指令傳輸過來
第二層：服務(wù)器解析并優(yōu)化sql议街，生成最終的執(zhí)行計劃并執(zhí)行
第三層：存儲引擎，負責(zé)數(shù)據(jù)的儲存和提取
1.2 鎖
數(shù)據(jù)庫通過鎖機制來解決并發(fā)場景-共享鎖（讀鎖）和排他鎖（寫鎖）璧榄。讀鎖是不阻塞的特漩，多個客戶端可以在同一時刻讀取同一個資源。寫鎖是排他的骨杂，并且會阻塞其他的讀鎖和寫鎖涂身。簡單提下樂觀鎖和悲觀鎖。
樂觀鎖搓蚪，通常用于數(shù)據(jù)競爭不激烈的場景蛤售，多讀少寫，通過版本號和時間戳實現(xiàn)妒潭。
悲觀鎖悴能，通常用于數(shù)據(jù)競爭激烈的場景，每次操作都會鎖定數(shù)據(jù)雳灾。
要鎖定數(shù)據(jù)需要一定的鎖策略來配合漠酿。
表鎖，鎖定整張表谎亩，開銷最小记靡，但是會加劇鎖競爭。
行鎖团驱，鎖定行級別摸吠，開銷最大，但是可以最大程度的支持并發(fā)嚎花。
但是MySql的存儲引擎的真實實現(xiàn)不是簡單的行級鎖寸痢，一般都是實現(xiàn)了多版本并發(fā)控制（MVCC）。MVCC是行級鎖的變種紊选，多數(shù)情況下避免了加鎖操作啼止，開銷更低道逗。MVCC是通過保存數(shù)據(jù)的某個時間點快照實現(xiàn)的。
（Java高級程序員）學(xué)習(xí)交流QQ群：478052716 你在學(xué)習(xí)Java的過程中或者在工作中遇到什么問題都可以來群里提問献烦，阿里Java高級大牛直播講解知識點滓窍，分享知識，多年工作經(jīng)驗的梳理和總結(jié)巩那，帶著大家全面吏夯、科學(xué)地建立自己的技術(shù)體系和技術(shù)認知！非喜勿進即横！
1.3 事務(wù)
事務(wù)保證一組原子性的操作噪生，要么全部成功，要么全部失敗东囚。一旦失敗跺嗽，回滾之前的所有操作。MySql采用自動提交页藻，如果不是顯式的開啟一個事務(wù)桨嫁，則每個查詢都作為一個事務(wù)。
隔離級別控制了一個事務(wù)中的修改份帐，哪些在事務(wù)內(nèi)和事務(wù)間是可見的瞧甩。四種常見的隔離級別：
未提交讀（Read UnCommitted），事務(wù)中的修改弥鹦，即使沒提交對其他事務(wù)也是可見的肚逸。事務(wù)可能讀取未提交的數(shù)據(jù)彬坏，造成臟讀务冕。
提交讀（Read Committed）落恼，一個事務(wù)開始時戴涝，只能看見已提交的事務(wù)所做的修改啥刻。事務(wù)未提交之前可帽，所做的修改對其他事務(wù)是不可見的。也叫不可重復(fù)讀弯蚜，同一個事務(wù)多次讀取同樣記錄可能不同贷洲。
可重復(fù)讀（RepeatTable Read），同一個事務(wù)中多次讀取同樣的記錄結(jié)果時結(jié)果相同。
可串行化（Serializable），最高隔離級別德挣，強制事務(wù)串行執(zhí)行唠帝。
1.4 存儲引擎
InnoDB引擎懂扼，最重要赶熟，使用最廣泛的存儲引擎。被用來設(shè)計處理大量短期事務(wù)劳澄，具有高性能和自動奔潰恢復(fù)的特性莫矗。
MyISAM引擎庵芭，不支持事務(wù)和行級鎖眨唬，奔潰后無法安全恢復(fù)曹宴。
2.0 創(chuàng)建時優(yōu)化
2.1 Schema和數(shù)據(jù)類型優(yōu)化
整數(shù)
TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存儲8,16,24,32,64位存儲空間。使用Unsigned表示不允許負數(shù)礁芦，可以使正數(shù)的上線提高一倍闺魏。
實數(shù)
Float,Double , 支持近似的浮點運算析桥。
Decimal，用于存儲精確的小數(shù)艰垂。
字符串
VarChar泡仗，存儲變長的字符串。需要1或2個額外的字節(jié)記錄字符串的長度猜憎。
Char娩怎，定長，適合存儲固定長度的字符串胰柑，如MD5值截亦。
Blob，Text 為了存儲很大的數(shù)據(jù)而設(shè)計的旦事。分別采用二進制和字符的方式魁巩。
時間類型
DateTime急灭，保存大范圍的值姐浮，占8個字節(jié)。
TimeStamp葬馋，推薦卖鲤，與UNIX時間戳相同，占4個字節(jié)畴嘶。
優(yōu)化建議點
盡量使用對應(yīng)的數(shù)據(jù)類型蛋逾。比如，不要用字符串類型保存時間窗悯，用整型保存IP区匣。
選擇更小的數(shù)據(jù)類型。能用TinyInt不用Int蒋院。
標識列（identifier column）亏钩，建議使用整型，不推薦字符串類型欺旧，占用更多空間姑丑，而且計算速度比整型慢。
不推薦ORM系統(tǒng)自動生成的Schema辞友，通常具有不注重數(shù)據(jù)類型栅哀，使用很大的VarChar類型震肮，索引利用不合理等問題。
真實場景混用范式和反范式留拾。冗余高查詢效率高戳晌，插入更新效率低；冗余低插入更新效率高痴柔，查詢效率低躬厌。
創(chuàng)建完全的獨立的匯總表\緩存表，定時生成數(shù)據(jù)竞帽，用于用戶耗時時間長的操作扛施。對于精確度要求高的匯總操作，可以采用 歷史結(jié)果+最新記錄的結(jié)果 來達到快速查詢的目的屹篓。
數(shù)據(jù)遷移疙渣，表升級的過程中可以使用影子表的方式，通過修改原表的表名堆巧，達到保存歷史數(shù)據(jù)妄荔，同時不影響新表使用的目的。
2.2 索引
索引包含一個或多個列的值谍肤。MySql只能高效的利用索引的最左前綴列啦租。索引的優(yōu)勢：
減少查詢掃描的數(shù)據(jù)量
避免排序和零時表
將隨機IO變?yōu)轫樞騃O （順序IO的效率高于隨機IO）
B-Tree
使用最多的索引類型。采用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲數(shù)據(jù)（每個葉子節(jié)點都包含指向下一個葉子節(jié)點的指針荒揣，從而方便葉子節(jié)點的遍歷）篷角。B-Tree索引適用于全鍵值，鍵值范圍系任，鍵前綴查找恳蹲，支持排序。
B-Tree索引限制：
如果不是按照索引的最左列開始查詢俩滥，則無法使用索引嘉蕾。
不能跳過索引中的列。如果使用第一列和第三列索引霜旧，則只能使用第一列索引错忱。
如果查詢中有個范圍查詢，則其右邊的所有列都無法使用索引優(yōu)化查詢挂据。
哈希索引
只有精確匹配索引的所有列以清，查詢才有效。存儲引擎會對所有的索引列計算一個哈希碼棱貌，哈希索引將所有的哈希碼存儲在索引中玖媚，并保存指向每個數(shù)據(jù)行的指針。
哈希索引限制：
無法用于排序
不支持部分匹配
只支持等值查詢?nèi)?婚脱，IN（）今魔，不支持 < >
優(yōu)化建議點
注意每種索引的適用范圍和適用限制勺像。
索引的列如果是表達式的一部分或者是函數(shù)的參數(shù)，則失效错森。
針對特別長的字符串吟宦，可以使用前綴索引，根據(jù)索引的選擇性選擇合適的前綴長度涩维。
使用多列索引的時候殃姓，可以通過 AND 和 OR 語法連接。
重復(fù)索引沒必要瓦阐，如（A蜗侈，B）和（A）重復(fù)。
索引在where條件查詢和group by語法查詢的時候特別有效睡蟋。
將范圍查詢放在條件查詢的最后踏幻，防止范圍查詢導(dǎo)致的右邊索引失效的問題。
索引最好不要選擇過長的字符串戳杀，而且索引列也不宜為null该面。
3.0 查詢時優(yōu)化
3.1 查詢質(zhì)量的三個重要指標
響應(yīng)時間 （服務(wù)時間，排隊時間）
掃描的行
返回的行
3.2 查詢優(yōu)化點
避免查詢無關(guān)的列信卡，如使用Select * 返回所有的列隔缀。
避免查詢無關(guān)的行
切分查詢。將一個對服務(wù)器壓力較大的任務(wù)傍菇，分解到一個較長的時間中猾瘸，并分多次執(zhí)行。如要刪除一萬條數(shù)據(jù)桥嗤，可以分10次執(zhí)行须妻，每次執(zhí)行完成后暫停一段時間仔蝌，再繼續(xù)執(zhí)行泛领。過程中可以釋放服務(wù)器資源給其他任務(wù)。
分解關(guān)聯(lián)查詢敛惊。將多表關(guān)聯(lián)查詢的一次查詢渊鞋，分解成對單表的多次查詢∏萍罚可以減少鎖競爭锡宋，查詢本身的查詢效率也比較高。因為MySql的連接和斷開都是輕量級的操作特恬，不會由于查詢拆分為多次执俩，造成效率問題。
注意count的操作只能統(tǒng)計不為null的列癌刽，所以統(tǒng)計總的行數(shù)使用count（*）役首。
group by 按照標識列分組效率高尝丐，分組結(jié)果不宜出行分組列之外的列。
關(guān)聯(lián)查詢延遲關(guān)聯(lián)衡奥，可以根據(jù)查詢條件先縮小各自要查詢的范圍爹袁，再關(guān)聯(lián)。
Limit分頁優(yōu)化矮固∈ⅲ可以根據(jù)索引覆蓋掃描，再根據(jù)索引列關(guān)聯(lián)自身查詢其他列档址。如
SELECT
id,
NAME,
age
WHERE
student s1
INNER JOIN (
SELECT
id
FROM
student
ORDER BY
age
LIMIT 50,5
) AS s2 ON s1.id = s2.id
Union查詢默認去重盹兢，如果不是業(yè)務(wù)必須，建議使用效率更高的Union All
后記
歡迎大家在評論中補充守伸，我會把大家補充的內(nèi)容持續(xù)更新出來蛤迎。如果有幫助，請幫忙點喜歡含友。
補充更新日志
2017.09.08
—->來自大神-小寶
1.條件中的字段類型和表結(jié)構(gòu)類型不一致替裆，mysql會自動加轉(zhuǎn)換函數(shù)，導(dǎo)致索引作為函數(shù)中的參數(shù)失效窘问。
2.like查詢前面部分未輸入辆童，以%開頭無法命中索引。
3.補充2個5.7版本的新特性：
generated column惠赫，就是數(shù)據(jù)庫中這一列由其他列計算而得
CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLE AS (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’);
—->來自JVM專家-達
關(guān)注explain在性能分析中的使用
EXPLAIN SELECT settleId FROM Settle WHERE settleId = “3679”
select_type，有幾種值：simple（表示簡單的select儿咱，沒有union和子查詢）庭砍，primary（有子查詢，最外面的select查詢就是primary）混埠，union（union中的第二個或隨后的select查詢怠缸，不依賴外部查詢結(jié)果），dependent union（union中的第二個或隨后的select查詢钳宪，依賴外部查詢結(jié)果）
type揭北，有幾種值：system（表僅有一行（=系統(tǒng)表），這是const連接類型的一個特例）吏颖，const（常量查詢）, ref(非唯一索引訪問搔体，只有普通索引)，eq_ref（使用唯一索引或組件查詢）半醉，all（全表查詢）疚俱，index（根據(jù)索引查詢?nèi)恚瑀ange（范圍查詢）
possible_keys: 表中可能幫助查詢的索引
key缩多，選擇使用的索引
key_len呆奕，使用的索引長度
rows夯尽，掃描的行數(shù)，越大越不好
extra登馒，有幾種值：Only index（信息從索引中檢索出匙握，比掃描表快），where used（使用where限制）陈轿，Using filesort （可能在內(nèi)存或磁盤排序）圈纺，Using temporary（對查詢結(jié)果排序時使用臨時表）
