前言

本文主要針對的是關系型數據數據庫MySql.鍵值類數據庫可以參考最簡大數據Redis.先簡單梳理下Mysql的基本概念,然后分創立時和查詢時這兩個階段的優化展開.

1.0 根本概念簡述

1.1 邏輯架構

• 第一層：客戶端通過銜接服務,將要執行的sql指令傳輸過來

• 第二層：服務器解析并優化sql,生成最終的執行方案并執行

• 第三層：存儲引擎,賣力數據的儲存和提取

1.2 鎖

數據庫通過鎖機制來辦理并發場景-共享鎖(讀鎖)和排他鎖(寫鎖).讀鎖是不阻塞的,多個客戶端可以在同一時刻讀取同一個資源.寫鎖是排他的,并且會阻塞其他的讀鎖和寫鎖.簡單提下樂觀鎖和悲觀鎖.

• 樂觀鎖,通常用于數據競爭不激烈的場景,多讀少寫,通過版本號和光陰戳實現.

• 消極鎖,通常用于數據競爭激烈的場景,每次操作都會鎖定數據.

要鎖定數據需要必定的鎖策略來配合.

• 表鎖,鎖定整張表,開銷最小,然則會加劇鎖競爭.

• 行鎖,鎖定行級別,開銷最大,但是可以最大水平的支持并發.

但是MySql的存儲引擎的真實實現不是簡單的行級鎖,一般都是實現了多版本并發控制(MVCC).MVCC是行級鎖的變種,多數情況下避免了加鎖操作,開銷更低.MVCC是通過保留數據的某個時間點快照實現的.

1.3 事務

事務保證一組原子性的操作,要么全部成功,要么全部失敗.一旦失敗,回滾之前的所有操作.MySql采納自動提交,如果不是顯式的開啟一個事務,則每個查詢都作為一個事務.

隔離級別控制了一個事務中的改動,哪些在事務內和事務間是可見的.四種常見的隔離級別：

• 未提交讀(Read UnCommitted),事務中的改動,即使沒提交對其他事務也是可見的.事務可能讀取未提交的數據,造成臟讀.

• 提交讀(Read Committed),一個事務開始時,只能看見已提交的事務所做的修改.事務未提交之前,所做的修改對其他事務是弗成見的.也叫弗成重復讀,同一個事務多次讀取同樣記錄可能不同.

• 可重復讀(RepeatTable Read),同一個事務中多次讀取同樣的記錄成果時成果相同.

• 可串行化(Serializable),最高隔離級別,強制事務串行執行.

1.4 存儲引擎

• InnoDB引擎,最重要,使用最廣泛的存儲引擎.被用來設計處置大量短期事務,具有高性能和自動奔潰恢復的特性.

• MyISAM引擎,不支持事務和行級鎖,奔潰后無法平安恢復.

2.0 創立時優化

2.1 Schema和數據類型優化

整數

• TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存儲8,16,24,32,64位存儲空間.使用Unsigned表現不允許負數,可以使正數的上線提高一倍.

實數

• Float,Double , 支撐近似的浮點運算.

• Decimal,用于存儲準確的小數.

字符串

• VarChar,存儲變長的字符串.必要1或2個額外的字節記錄字符串的長度.

• Char,定長,得當存儲固定長度的字符串,如MD5值.

• Blob,Text 為了存儲很大的數據而設計的.分別采納二進制和字符的方式.

光陰類型

• DateTime,保留大范圍的值,占8個字節.

• TimeStamp,保舉,與UNIX時間戳相同,占4個字節.

優化建議點

1. 盡量使用對應的數據類型.比如,不要用字符串類型保留時間,用整型保留IP.

2. 選擇更小的數據類型.能用TinyInt不消Int.

3. 標識列(identifier column),建議使用整型,不保舉字符串類型,占用更多空間,而且計算速度比整型慢.

4. 不保舉ORM系統自動生成的Schema,通常具有不注重數據類型,使用很大的VarChar類型,索引利用不合理等問題.

5. 真實場景混用范式和反范式.冗余高查詢效力高,插入更新效力低；冗余低插入更新效力高,查詢效力低.

6. 創建完全的獨立的匯總表\緩存表,定時生成數據,用于用戶耗時時間長的操作.對于精確度要求高的匯總操作,可以采納 歷史結果+最新記錄的結果 來達到快速查詢的目的.

7. 數據遷移,表升級的過程中可以使用影子表的方式,通過修改原表的表名,達到保留歷史數據,同時不影響新表使用的目的.

2.2 索引

索引包括一個或多個列的值.MySql只能高效的利用索引的最左前綴列.索引的優勢：

• 削減查詢掃描的數據量

• 避免排序和零時表

• 將隨機IO變為次序IO (次序IO的效率高于隨機IO)

B-Tree

使用最多的索引類型.采用B-Tree數據結構來存儲數據(每個葉子節點都包括指向下一個葉子節點的指針,從而方便葉子節點的遍歷).B-Tree索引適用于全鍵值,鍵值范圍,鍵前綴查找,支持排序.

B-Tree索引限定：

• 如果不是依照索引的最左列開始查詢,則無法使用索引.

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

• 如果查詢中有個規模查詢,則其右邊的所有列都無法使用索引優化查詢.

哈希索引

只有精確匹配索引的所有列,查詢才有效.存儲引擎會對所有的索引列計算一個哈希碼,哈希索引將所有的哈希碼存儲在索引中,并保留指向每個數據行的指針.

哈希索引限定：

• 無奈用于排序

• 不支持部門匹配

• 只支撐等值查詢如=,IN(),不支撐 < >

優化建議點

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

2. 索引的列如果是表達式的一部門或者是函數的參數,則失效.

3. 針對分外長的字符串,可以使用前綴索引,根據索引的選擇性選擇合適的前綴長度.

4. 使用多列索引的時候,可以通過 AND 和 OR 語法銜接.

5. 重復索引沒需要,如(A,B)和(A)重復.

6. 索引在where條件查詢和group by語法查詢的時候分外有效.

7. 將規模查詢放在條件查詢的最后,防止規模查詢導致的右邊索引失效的問題.

8. 索引最好不要選擇過長的字符串,并且索引列也不宜為null.

3.0 查詢時優化

3.1 查詢質量的三個緊張指標

• 響應光陰 (服務光陰,排隊光陰)

• 掃描的行

• 返回的行

3.2 查詢優化點

1. 避免查詢無關的列,如使用Select * 返回所有的列.

2. 避免查詢無關的行

3. 切分查詢.將一個對服務器壓力較年夜的任務,分解到一個較長的時間中,并分多次執行.如要刪除一萬條數據,可以分10次執行,每次執行完成后暫停一段時間,再繼續執行.過程中可以釋放服務器資源給其他任務.

4. 分解關聯查詢.將多表關聯查詢的一次查詢,分解成對單表的多次查詢.可以減少鎖競爭,查詢自己的查詢效率也比較高.因為MySql的連接和斷開都是輕量級的操作,不會由于查詢拆分為多次,造成效率問題.

5. 注意count的操作只能統計不為null的列,以是統計總的行數使用count(*).

6. group by 依照標識列分組效率高,分組結果不宜出行分組列之外的列.

7. 關聯查詢延遲關聯,可以根據查詢條件先縮小各自要查詢的規模,再關聯.

8. Limit分頁優化.可以根據索引籠罩掃描,再根據索引列關聯自身查詢其他列.如

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

9. Union查詢默認去重,如果不是業務必需,建議使用效率更高的Union All

跋文

歡迎大家在評論中補充,我會把大家補充的內容持續更新出來.如果有幫助,請幫忙點喜歡.

彌補更新日志

2017.09.08

---->來自年夜神-小寶

1.條件中的字段類型和表結構類型紛歧致,mysql會自動加轉換函數,導致索引作為函數中的參數失效.

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

3.彌補2個5.7版本的新特性：

• generated column,便是數據庫中這一列由其他列計算而得

  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格局數據,并提供相關內置函數

  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專家-達

1. 關注explain在性能闡發中的使用

   EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"

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

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

• possible_keys: 表中可能贊助查詢的索引

• key,選擇使用的索引

• key_len,使用的索引長度

• rows,掃描的行數,越大越欠好

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

Java工程化、高性能及分布式、高性能、深入淺出.高架構.性能調優、Spring,MyBatis,Netty源碼分析和大數據等多個知識點.如果你想拿高薪的,想學習的,想就業前景好的,想跟別人競爭能取得優勢的,想進阿里面試但擔心面試不外的,你都可以來,群號為：647631030

注：加群要求

1、具有1-5工作經驗的,面對目前流行的技術不知從何下手,必要突破技術瓶頸的可以加.

2、在公司待久了,過得很安適,但跳槽時面試碰壁.需要在短時間內進修、跳槽拿高薪的可以加.

3、如果沒有工作經驗,但基礎非常扎實,對java工作機制,常用設計思想,常用java開發框架掌握純熟的,可以加.

4、覺得本身很牛B,一般需求都能搞定.但是所學的知識點沒有系統化,很難在技術領域繼續突破的可以加.

5.阿里Java高級大牛直播講解知識點,分享知識,多年工作經驗的梳理和總結,帶著大家全面、科學地建立本身的技術體系和技術認知!

6.小號或者小白之類加群一概不給過,謝謝.