來源：juejin.im/post/59b11ba151882538cb1ecbd0

「前言」

本文主要針對(duì)的是關(guān)系型數(shù)據(jù)數(shù)據(jù)庫MySql。鍵值類數(shù)據(jù)庫可以參考：

https://www.jianshu.com/p/098a870d83e4

先簡單梳理下Mysql的基本概念，然后分創(chuàng)建時(shí)和查詢時(shí)這兩個(gè)階段的優(yōu)化展開。

「1 基本概念簡述」

1.1 邏輯架構(gòu)

• 第一層：客戶端通過連接服務(wù)，將要執(zhí)行的sql指令傳輸過來

• 第二層：服務(wù)器解析并優(yōu)化sql，生成最終的執(zhí)行計(jì)劃并執(zhí)行

• 第三層：存儲(chǔ)引擎，負(fù)責(zé)數(shù)據(jù)的儲(chǔ)存和提取

1.2 鎖

數(shù)據(jù)庫通過鎖機(jī)制來解決并發(fā)場景-共享鎖(讀鎖)和排他鎖(寫鎖)。讀鎖是不阻塞的，多個(gè)客戶端可以在同一時(shí)刻讀取同一個(gè)資源。寫鎖是排他的，并且會(huì)阻塞其他的讀鎖和寫鎖。簡單提下樂觀鎖和悲觀鎖。

• 「樂觀鎖」，通常用于數(shù)據(jù)競爭不激烈的場景，多讀少寫，通過版本號(hào)和時(shí)間戳實(shí)現(xiàn)。

• 「悲觀鎖」，通常用于數(shù)據(jù)競爭激烈的場景，每次操作都會(huì)鎖定數(shù)據(jù)。

要鎖定數(shù)據(jù)需要一定的鎖策略來配合。

• 「表鎖」，鎖定整張表，開銷最小，但是會(huì)加劇鎖競爭。

• 「行鎖」，鎖定行級(jí)別，開銷最大，但是可以最大程度的支持并發(fā)。

但是MySql的存儲(chǔ)引擎的真實(shí)實(shí)現(xiàn)不是簡單的行級(jí)鎖，一般都是實(shí)現(xiàn)了多版本并發(fā)控制(MVCC)。MVCC是行級(jí)鎖的變種，多數(shù)情況下避免了加鎖操作，開銷更低。MVCC是通過保存數(shù)據(jù)的某個(gè)時(shí)間點(diǎn)快照實(shí)現(xiàn)的。

1.3 事務(wù)

事務(wù)保證一組原子性的操作，要么全部成功，要么全部失敗。一旦失敗，回滾之前的所有操作。MySql采用自動(dòng)提交，如果不是顯式的開啟一個(gè)事務(wù)，則每個(gè)查詢都作為一個(gè)事務(wù)。

隔離級(jí)別控制了一個(gè)事務(wù)中的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見的。四種常見的隔離級(jí)別：

• 「未提交讀」(Read UnCommitted)，事務(wù)中的修改，即使沒提交對(duì)其他事務(wù)也是可見的。事務(wù)可能讀取未提交的數(shù)據(jù)，造成臟讀。

• 「提交讀」(Read Committed)，一個(gè)事務(wù)開始時(shí)，只能看見已提交的事務(wù)所做的修改。事務(wù)未提交之前，所做的修改對(duì)其他事務(wù)是不可見的。也叫不可重復(fù)讀，同一個(gè)事務(wù)多次讀取同樣記錄可能不同。

• 「可重復(fù)讀」(RepeatTable Read)，同一個(gè)事務(wù)中多次讀取同樣的記錄結(jié)果時(shí)結(jié)果相同。

• 「可串行化」(Serializable)，最高隔離級(jí)別，強(qiáng)制事務(wù)串行執(zhí)行。

1.4 存儲(chǔ)引擎

InnoDB引擎，最重要，使用最廣泛的存儲(chǔ)引擎。被用來設(shè)計(jì)處理大量短期事務(wù)，具有高性能和自動(dòng)崩潰恢復(fù)的特性。

MyISAM引擎，不支持事務(wù)和行級(jí)鎖，崩潰后無法安全恢復(fù)。

「2 創(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ǔ)變長的字符串。需要1或2個(gè)額外的字節(jié)記錄字符串的長度。

• Char，定長，適合存儲(chǔ)固定長度的字符串，如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類型，索引利用不合理等問題。

• 真實(shí)場景混用范式和反范式。冗余高查詢效率高，插入更新效率低；冗余低插入更新效率高，查詢效率低。

• 創(chuàng)建完全的獨(dú)立的匯總表\緩存表，定時(shí)生成數(shù)據(jù)，用于用戶耗時(shí)時(shí)間長的操作。對(duì)于精確度要求高的匯總操作，可以采用 歷史結(jié)果+最新記錄的結(jié)果 來達(dá)到快速查詢的目的。

• 數(shù)據(jù)遷移，表升級(jí)的過程中可以使用影子表的方式，通過修改原表的表名，達(dá)到保存歷史數(shù)據(jù)，同時(shí)不影響新表使用的目的。

2.2 索引

索引包含一個(gè)或多個(gè)列的值。MySql只能高效的利用索引的最左前綴列。索引的優(yōu)勢：

• 減少查詢掃描的數(shù)據(jù)量

• 避免排序和零時(shí)表

• 將隨機(jī)IO變?yōu)轫樞騃O (順序IO的效率高于隨機(jī)IO)

「B-Tree」

使用最多的索引類型。采用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)數(shù)據(jù)(每個(gè)葉子節(jié)點(diǎn)都包含指向下一個(gè)葉子節(jié)點(diǎn)的指針，從而方便葉子節(jié)點(diǎn)的遍歷)。B-Tree索引適用于全鍵值，鍵值范圍，鍵前綴查找，支持排序。

B-Tree索引限制：

• 如果不是按照索引的最左列開始查詢，則無法使用索引。

• 不能跳過索引中的列。如果使用第一列和第三列索引，則只能使用第一列索引。

• 如果查詢中有個(gè)范圍查詢，則其右邊的所有列都無法使用索引優(yōu)化查詢。

「哈希索引」

只有精確匹配索引的所有列，查詢才有效。存儲(chǔ)引擎會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼，哈希索引將所有的哈希碼存儲(chǔ)在索引中，并保存指向每個(gè)數(shù)據(jù)行的指針。

哈希索引限制：

• 無法用于排序

• 不支持部分匹配

• 只支持等值查詢?nèi)?#61;，IN()，不支持 < >

「優(yōu)化建議點(diǎn)」

• 注意每種索引的適用范圍和適用限制。

• 索引的列如果是表達(dá)式的一部分或者是函數(shù)的參數(shù)，則失效。

• 針對(duì)特別長的字符串，可以使用前綴索引，根據(jù)索引的選擇性選擇合適的前綴長度。

• 使用多列索引的時(shí)候，可以通過 AND 和 OR 語法連接。

• 重復(fù)索引沒必要，如(A，B)和(A)重復(fù)。

• 索引在where條件查詢和group by語法查詢的時(shí)候特別有效。

• 將范圍查詢放在條件查詢的最后，防止范圍查詢導(dǎo)致的右邊索引失效的問題。

• 索引最好不要選擇過長的字符串，而且索引列也不宜為null。

「3 查詢時(shí)優(yōu)化」

3.1 查詢質(zhì)量的三個(gè)重要指標(biāo)

• 響應(yīng)時(shí)間 (服務(wù)時(shí)間，排隊(duì)時(shí)間)

• 掃描的行

• 返回的行

3.2 查詢優(yōu)化點(diǎn)

• 避免查詢無關(guān)的列，如使用Select * 返回所有的列。

• 避免查詢無關(guān)的行

• 切分查詢。將一個(gè)對(duì)服務(wù)器壓力較大的任務(wù)，分解到一個(gè)較長的時(shí)間中，并分多次執(zhí)行。如要?jiǎng)h除一萬條數(shù)據(jù)，可以分10次執(zhí)行，每次執(zhí)行完成后暫停一段時(shí)間，再繼續(xù)執(zhí)行。過程中可以釋放服務(wù)器資源給其他任務(wù)。

• 分解關(guān)聯(lián)查詢。將多表關(guān)聯(lián)查詢的一次查詢，分解成對(duì)單表的多次查詢。可以減少鎖競爭，查詢本身的查詢效率也比較高。因?yàn)镸ySql的連接和斷開都是輕量級(jí)的操作，不會(huì)由于查詢拆分為多次，造成效率問題。

• 注意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ō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查詢默認(rèn)去重，如果不是業(yè)務(wù)必須，建議使用效率更高的Union All

「補(bǔ)充內(nèi)容」

來自大神-小寶

1.條件中的字段類型和表結(jié)構(gòu)類型不一致，mysql會(huì)自動(dòng)加轉(zhuǎn)換函數(shù)，導(dǎo)致索引作為函數(shù)中的參數(shù)失效。

2.like查詢前面部分未輸入，以%開頭無法命中索引。

3.補(bǔ)充2個(gè)5.7版本的新特性：

generated column，就是數(shù)據(jù)庫中這一列由其他列計(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專家-達(dá)

關(guān)注explain在性能分析中的使用

EXPLAIN?SELECT?settleId?FROM?Settle?WHERE?settleId?=?"3679"??
?

• 「select_type」，有幾種值：simple(表示簡單的select，沒有union和子查詢)，primary(有子查詢，最外面的select查詢就是primary)，union(union中的第二個(gè)或隨后的select查詢，不依賴外部查詢結(jié)果)，dependent union(union中的第二個(gè)或隨后的select查詢，依賴外部查詢結(jié)果)

• 「type」，有幾種值：system(表僅有一行(=系統(tǒng)表)，這是const連接類型的一個(gè)特例)，const(常量查詢), ref(非唯一索引訪問，只有普通索引)，eq_ref(使用唯一索引或組件查詢)，all(全表查詢)，index(根據(jù)索引查詢?nèi)?，range(范圍查詢)

• 「possible_keys」: 表中可能幫助查詢的索引

• 「key」，選擇使用的索引

• 「key_len」，使用的索引長度

• 「rows」，掃描的行數(shù)，越大越不好

• 「extra」，有幾種值：Only index(信息從索引中檢索出，比掃描表快)，where used(使用where限制)，Using filesort (可能在內(nèi)存或磁盤排序)，Using temporary(對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表)

最后給大家送下福利，大家可以關(guān)注Java核心技術(shù)公眾號(hào)，在后臺(tái)回復(fù)?“福利”可以獲取一份我整理的最新Java面試題資料。

最近好文分享

寫這么騷的代碼，不怕被揍么？

面試官再問 System.out.println()，跪求你一定要把這個(gè)發(fā)給他！

拼多多，一面，i++ 是線程安全的嗎？

厲害了，用 Java 也能實(shí)現(xiàn)圖片識(shí)別！

啥？HashMap 1.8 還有死循環(huán)？

牛逼！一個(gè)故事就把 NIO 講清楚了！

更多請(qǐng)掃碼關(guān)注???Java核心技術(shù)

一個(gè)分享Java核心技術(shù)干貨的公眾號(hào)歡迎大家在看、轉(zhuǎn)發(fā) 與50位技術(shù)專家面對(duì)面20年技術(shù)見證，附贈(zèng)技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的sql长整型_SQL性能优化，太太太太太太太有用了！的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。