前言
本文主要針對的是關係型數據數據庫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個字節。
優化建議點
儘量使用對應的數據類型。比如,不要用字符串類型保存時間,用整型保存IP。
選擇更小的數據類型。能用TinyInt不用Int。
標識列(identifier column),建議使用整型,不推薦字符串類型,佔用更多空間,而且計算速度比整型慢。
不推薦ORM系統自動生成的Schema,通常具有不注重數據類型,使用很大的VarChar類型,索引利用不合理等問題。
真實場景混用範式和反範式。冗餘高查詢效率高,插入更新效率低;冗餘低插入更新效率高,查詢效率低。
創建完全的獨立的彙總表\緩存表,定時生成數據,用於用戶耗時時間長的操作。對於精確度要求高的彙總操作,可以採用 歷史結果+最新記錄的結果 來達到快速查詢的目的。
數據遷移,表升級的過程中可以使用影子表的方式,通過修改原表的表名,達到保存歷史數據,同時不影響新表使用的目的。
2.2 索引
索引包含一個或多個列的值。MySql只能高效的利用索引的最左前綴列。索引的優勢:
減少查詢掃描的數據量
避免排序和零時表
將隨機IO變為順序IO (順序IO的效率高於隨機IO)
B-Tree
使用最多的索引類型。採用B-Tree數據結構來存儲數據(每個葉子節點都包含指向下一個葉子節點的指針,從而方便葉子節點的遍歷)。B-Tree索引適用於全鍵值,鍵值範圍,鍵前綴查找,支持排序。
B-Tree索引限制:
如果不是按照索引的最左列開始查詢,則無法使用索引。
不能跳過索引中的列。如果使用第一列和第三列索引,則只能使用第一列索引。
如果查詢中有個範圍查詢,則其右邊的所有列都無法使用索引優化查詢。
哈希索引
只有精確匹配索引的所有列,查詢才有效。存儲引擎會對所有的索引列計算一個哈希碼,哈希索引將所有的哈希碼存儲在索引中,並保存指向每個數據行的指針。
哈希索引限制:
無法用於排序
不支持部分匹配
只支持等值查詢如=,IN(),不支持 < >
優化建議點
注意每種索引的適用範圍和適用限制。
索引的列如果是表達式的一部分或者是函數的參數,則失效。
針對特別長的字符串,可以使用前綴索引,根據索引的選擇性選擇合適的前綴長度。
使用多列索引的時候,可以通過 AND 和 OR 語法連接。
重複索引沒必要,如(A,B)和(A)重複。
索引在where條件查詢和group by語法查詢的時候特別有效。
將範圍查詢放在條件查詢的最後,防止範圍查詢導致的右邊索引失效的問題。
索引最好不要選擇過長的字符串,而且索引列也不宜為null。
3.0 查詢時優化
3.1 查詢質量的三個重要指標
響應時間 (服務時間,排隊時間)
掃描的行
返回的行
3.2 查詢優化點
避免查詢無關的列,如使用Select * 返回所有的列。
避免查詢無關的行
切分查詢。將一個對服務器壓力較大的任務,分解到一個較長的時間中,並分多次執行。如要刪除一萬條數據,可以分10次執行,每次執行完成後暫停一段時間,再繼續執行。過程中可以釋放服務器資源給其他任務。
分解關聯查詢。將多表關聯查詢的一次查詢,分解成對單表的多次查詢。可以減少鎖競爭,查詢本身的查詢效率也比較高。因為MySql的連接和斷開都是輕量級的操作,不會由於查詢拆分為多次,造成效率問題。
注意count的操作只能統計不為null的列,所以統計總的行數使用count(*)。
group by 按照標識列分組效率高,分組結果不宜出行分組列之外的列。
關聯查詢延遲關聯,可以根據查詢條件先縮小各自要查詢的範圍,再關聯。
Limit分頁優化。可以根據索引覆蓋掃描,再根據索引列關聯自身查詢其他列。如
SELECTid,NAME,ageWHEREstudent s1INNER JOIN (SELECT idFROM studentORDER BY ageLIMIT 50,5) AS s2 ON s1.id = s2.id
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專家-達
關注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.小號或者小白之類加群一律不給過,謝謝。