前言

本文主要針對(duì)的是關(guān)系型數(shù)據(jù)數(shù)據(jù)庫(kù)MySql.鍵值類(lèi)數(shù)據(jù)庫(kù)可以參考最簡(jiǎn)大數(shù)據(jù)Redis.先簡(jiǎn)單梳理下Mysql的基本概念,然后分創(chuàng)立時(shí)和查詢(xún)時(shí)這兩個(gè)階段的優(yōu)化展開(kāi).

1.0 根本概念簡(jiǎn)述

1.1 邏輯架構(gòu)

• 第一層：客戶(hù)端通過(guò)銜接服務(wù),將要執(zhí)行的sql指令傳輸過(guò)來(lái)

• 第二層：服務(wù)器解析并優(yōu)化sql,生成最終的執(zhí)行方案并執(zhí)行

• 第三層：存儲(chǔ)引擎,賣(mài)力數(shù)據(jù)的儲(chǔ)存和提取

1.2 鎖

數(shù)據(jù)庫(kù)通過(guò)鎖機(jī)制來(lái)辦理并發(fā)場(chǎng)景-共享鎖(讀鎖)和排他鎖(寫(xiě)鎖).讀鎖是不阻塞的,多個(gè)客戶(hù)端可以在同一時(shí)刻讀取同一個(gè)資源.寫(xiě)鎖是排他的,并且會(huì)阻塞其他的讀鎖和寫(xiě)鎖.簡(jiǎn)單提下樂(lè)觀鎖和悲觀鎖.

• 樂(lè)觀鎖,通常用于數(shù)據(jù)競(jìng)爭(zhēng)不激烈的場(chǎng)景,多讀少寫(xiě),通過(guò)版本號(hào)和光陰戳實(shí)現(xiàn).

• 消極鎖,通常用于數(shù)據(jù)競(jìng)爭(zhēng)激烈的場(chǎng)景,每次操作都會(huì)鎖定數(shù)據(jù).

要鎖定數(shù)據(jù)需要必定的鎖策略來(lái)配合.

• 表鎖,鎖定整張表,開(kāi)銷(xiāo)最小,然則會(huì)加劇鎖競(jìng)爭(zhēng).

• 行鎖,鎖定行級(jí)別,開(kāi)銷(xiāo)最大,但是可以最大水平的支持并發(fā).

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

1.3 事務(wù)

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

隔離級(jí)別控制了一個(gè)事務(wù)中的改動(dòng),哪些在事務(wù)內(nèi)和事務(wù)間是可見(jiàn)的.四種常見(jiàn)的隔離級(jí)別：

• 未提交讀(Read UnCommitted),事務(wù)中的改動(dòng),即使沒(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ù)中多次讀取同樣的記錄成果時(shí)成果相同.

• 可串行化(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ù)類(lèi)型優(yōu)化

整數(shù)

• TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存儲(chǔ)8,16,24,32,64位存儲(chǔ)空間.使用Unsigned表現(xiàn)不允許負(fù)數(shù),可以使正數(shù)的上線提高一倍.

實(shí)數(shù)

• Float,Double , 支撐近似的浮點(diǎn)運(yùn)算.

• Decimal,用于存儲(chǔ)準(zhǔn)確的小數(shù).

字符串

• VarChar,存儲(chǔ)變長(zhǎng)的字符串.必要1或2個(gè)額外的字節(jié)記錄字符串的長(zhǎng)度.

• Char,定長(zhǎng),得當(dāng)存儲(chǔ)固定長(zhǎng)度的字符串,如MD5值.

• Blob,Text 為了存儲(chǔ)很大的數(shù)據(jù)而設(shè)計(jì)的.分別采納二進(jìn)制和字符的方式.

光陰類(lèi)型

• DateTime,保留大范圍的值,占8個(gè)字節(jié).

• TimeStamp,保舉,與UNIX時(shí)間戳相同,占4個(gè)字節(jié).

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

1. 盡量使用對(duì)應(yīng)的數(shù)據(jù)類(lèi)型.比如,不要用字符串類(lèi)型保留時(shí)間,用整型保留IP.

2. 選擇更小的數(shù)據(jù)類(lèi)型.能用TinyInt不消Int.

3. 標(biāo)識(shí)列(identifier column),建議使用整型,不保舉字符串類(lèi)型,占用更多空間,而且計(jì)算速度比整型慢.

4. 不保舉ORM系統(tǒng)自動(dòng)生成的Schema,通常具有不注重?cái)?shù)據(jù)類(lèi)型,使用很大的VarChar類(lèi)型,索引利用不合理等問(wèn)題.

5. 真實(shí)場(chǎng)景混用范式和反范式.冗余高查詢(xún)效力高,插入更新效力低；冗余低插入更新效力高,查詢(xún)效力低.

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

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

2.2 索引

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

• 削減查詢(xún)掃描的數(shù)據(jù)量

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

• 將隨機(jī)IO變?yōu)榇涡騃O (次序IO的效率高于隨機(jī)IO)

B-Tree

使用最多的索引類(lèi)型.采用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)始查詢(xún),則無(wú)法使用索引.

• 不克不及跳過(guò)索引中的列.如果使用第一列和第三列索引,則只能使用第一列索引.

• 如果查詢(xún)中有個(gè)規(guī)模查詢(xún),則其右邊的所有列都無(wú)法使用索引優(yōu)化查詢(xún).

哈希索引

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

哈希索引限定：

• 無(wú)奈用于排序

• 不支持部門(mén)匹配

• 只支撐等值查詢(xún)?nèi)?,IN(),不支撐 < >

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

1. 注意每種索引的適用規(guī)模和適用限制.

2. 索引的列如果是表達(dá)式的一部門(mén)或者是函數(shù)的參數(shù),則失效.

3. 針對(duì)分外長(zhǎng)的字符串,可以使用前綴索引,根據(jù)索引的選擇性選擇合適的前綴長(zhǎng)度.

4. 使用多列索引的時(shí)候,可以通過(guò) AND 和 OR 語(yǔ)法銜接.

5. 重復(fù)索引沒(méi)需要,如(A,B)和(A)重復(fù).

6. 索引在where條件查詢(xún)和group by語(yǔ)法查詢(xún)的時(shí)候分外有效.

7. 將規(guī)模查詢(xún)放在條件查詢(xún)的最后,防止規(guī)模查詢(xún)導(dǎo)致的右邊索引失效的問(wèn)題.

8. 索引最好不要選擇過(guò)長(zhǎng)的字符串,并且索引列也不宜為null.

3.0 查詢(xún)時(shí)優(yōu)化

3.1 查詢(xún)質(zhì)量的三個(gè)緊張指標(biāo)

• 響應(yīng)光陰 (服務(wù)光陰,排隊(duì)光陰)

• 掃描的行

• 返回的行

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

1. 避免查詢(xún)無(wú)關(guān)的列,如使用Select * 返回所有的列.

2. 避免查詢(xún)無(wú)關(guān)的行

3. 切分查詢(xú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ù).

4. 分解關(guān)聯(lián)查詢(xún).將多表關(guān)聯(lián)查詢(xún)的一次查詢(xún),分解成對(duì)單表的多次查詢(xún).可以減少鎖競(jìng)爭(zhēng),查詢(xún)自己的查詢(xún)效率也比較高.因?yàn)镸ySql的連接和斷開(kāi)都是輕量級(jí)的操作,不會(huì)由于查詢(xún)拆分為多次,造成效率問(wèn)題.

5. 注意count的操作只能統(tǒng)計(jì)不為null的列,以是統(tǒng)計(jì)總的行數(shù)使用count(*).

6. group by 依照標(biāo)識(shí)列分組效率高,分組結(jié)果不宜出行分組列之外的列.

7. 關(guān)聯(lián)查詢(xún)延遲關(guān)聯(lián),可以根據(jù)查詢(xún)條件先縮小各自要查詢(xún)的規(guī)模,再關(guān)聯(lián).

8. Limit分頁(yè)優(yōu)化.可以根據(jù)索引籠罩掃描,再根據(jù)索引列關(guān)聯(lián)自身查詢(xún)其他列.如

   SELECTid,NAME,ageWHEREstudent s1INNER JOIN (SELECT idFROM studentORDER BY ageLIMIT 50,5) AS s2 ON s1.id = s2.id

9. Union查詢(xún)默認(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.條件中的字段類(lèi)型和表結(jié)構(gòu)類(lèi)型紛歧致,mysql會(huì)自動(dòng)加轉(zhuǎn)換函數(shù),導(dǎo)致索引作為函數(shù)中的參數(shù)失效.

2.like查詢(xún)前面部門(mén)未輸入,以%開(kāi)頭無(wú)法命中索引.

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

• generated column,便是數(shù)據(jù)庫(kù)中這一列由其他列計(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格局?jǐn)?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專(zhuān)家-達(dá)

1. 關(guān)注explain在性能闡發(fā)中的使用

   EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"

• select_type,有幾種值：simple(表示簡(jiǎn)單的select,沒(méi)有union和子查詢(xún)),primary(有子查詢(xún),最外面的select查詢(xún)便是primary),union(union中的第二個(gè)或隨后的select查詢(xún),不依賴(lài)外部查詢(xún)結(jié)果),dependent union(union中的第二個(gè)或隨后的select查詢(xún),依賴(lài)外部查詢(xún)結(jié)果)

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

• possible_keys: 表中可能贊助查詢(xún)的索引

• key,選擇使用的索引

• key_len,使用的索引長(zhǎng)度

• rows,掃描的行數(shù),越大越欠好

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

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)心面試不外的,你都可以來(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)或者小白之類(lèi)加群一概不給過(guò),謝謝.