目錄導航
- 前言
- Undo-log與Redo-log
- 案例
- 當前讀、快照讀
- Redo Log的落盤配置
- MySQL配置優化
- MySQL服務器引數型別
- 快速定位MySql組態檔
- MySQL記憶體引數配置
- MySQL資料庫表設計
- 三大范式
- 資料庫表設計
- 附錄 58同城軍規
- 一、基礎規范
- 二、命名規范
- 三、表設計規范
- 四、欄位設計規范
- 五、索引設計規范
- 六、SQL使用規范
- 寫在最后
前言
性能優化專題共計四個部分，分別是：
- Tomcat 性能優化
- MySql 性能優化
- JVM 性能優化
- 性能測驗
本節是性能優化專題第二部分 —— MySql 性能優化篇，共計四個小節，分別是：
- MySql索引機制
- MySql運行機理
- 深入理解InnoDB
- MySql調優
本節重點：
? MVCC + Undo Log，解決幻讀
? Redo Log的落盤配置
? ?如何尋找MySQL組態檔
? ? MySQL記憶體引數如何配置
? ? MySQL資料庫設計三大范式
Undo-log與Redo-log
回顧上一小節末尾的SQL案例，似乎是MVCC沒有解決幻讀問題，實際上MySQL還有Undo-log機制用來處理幻讀，默認的Innodb在REPEATABLE READ隔離級別下,是如何通過MVCC + Undo Log，解決幻讀的呢？
Undo Log ：Undo log指事務開始之前，在操作任何資料之前,首先將需操作的資料備份到一個地方 (Undo Log)
undo意為取消，以撤銷操作為目的，回傳指定某個狀態的操作
- UndoLog是為了實作事務的原子性而出現的產物
? 事務處理程序中如果出現了錯誤或者用戶執行了 ROLLBACK陳述句,MySQL可以利用Undo Log中的備份將資料恢復到事務開始之前的狀態
- Undo Log在MySQL Innodb存盤引擎中用來實作多版本并發控制
? 事務未提交之前，Undo保存了未提交之前的版本資料，Undo 中的資料可作為資料舊版本快照供其他并發事務進行快照讀
案例
如下圖
- 事務A 執行update之前，備份數舊資料 --> Undo buffer -->落地 Undo Log
- 事務B 此時查詢的是Undo buffer中的內容（相當于讀取的是快照，實作多版本并發控制）
- 事務A 若因意外rollback，會從Undo buffer中資料恢復（實作事務原子性）
當前讀、快照讀
快照讀：
? SQL讀取的資料是快照版本，也就是歷史版本，普通的SELECT就是快照讀Innodb快照讀，資料的讀取將由 cache(原本資料) + undo(事務修改過的資料) 兩部分組成
當前讀：
? SQL讀取的資料是最新版本，通過鎖機制來保證讀取的資料無法通過其他事務進行修改
UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是當前讀
Redo Log的落盤配置
? 指定Redo log 記錄在{datadir}/ib_logfile1&ib_logfile2 可通過innodb_log_group_home_dir 配置指定目錄存盤
mysql> show variables like "innodb_log_group_home_dir";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_group_home_dir | ./ |
+---------------------------+-------+
1 row in set (0.01 sec)
一旦事務成功提交且資料持久化落盤之后，此時Redo log中的對應事務資料記錄就失去了意義，所以Redo log的寫入是日志檔案回圈寫入的
指定Redo log日志檔案組中的數量 innodb_log_files_in_group 默認為2
mysql> show variables like "innodb_log_files_in_group";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_group_home_dir | 2 |
+---------------------------+-------+
1 row in set (0.01 sec)
指定Redo log每一個日志檔案最大存盤量innodb_log_file_size 默認48M
mysql> show variables like "innodb_log_files_in_group";
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| innodb_log_group_home_dir |10485760|
+---------------------------+--------+
1 row in set (0.01 sec)
指定Redo log在cache/buffer中的buffer池大小innodb_log_buffer_size 默認16M
mysql> show variables like "innodb_log_buffer_size";
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| innodb_log_group_home_dir |10485760|
+---------------------------+--------+
1 row in set (0.01 sec)
Redo buffer 持久化Redo log的策略Innodb_flush_log_at_trx_commit：
mysql> show variables like "Innodb_flush_log_at_trx_commit";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0 |
+--------------------------------+-------+
1 row in set (0.01 sec)
- 取值 0 每秒提交 Redo buffer --> Redo log OS cache -->flush cache to disk[可能丟失一秒內的事務資料]
- 取值 1 默認值，每次事務提交執行Redo buffer --> Redo log OS cache --> flush cache to disk[最安全，性能最差的方式]
- 取值 2 每次事務提交執行Redo buffer --> Redo log OS cache 再每一秒執行 --> flush cache to disk操作
MySQL配置優化
MySQL服務器引數型別
基于引數的作用域：
set global autocommit = ON/OFF;
-- 全域引數
set session autocommit = ON/OFF;
-- 會話引數(會話引數不單獨設定則會采用全域引數)
注意：
- 全域引數的設定對于已經存在的會話無法生效
- 會話引數的設定隨著會話的銷毀而失效
- 全域類的統一配置建議配置在默認組態檔中，否則重啟服務會導致配置失效
快速定位MySql組態檔
假如剛進公司，boss丟了一個服務給你，讓你去優化一下MySQL的配置，只需要執行命令
mysql --help
# 尋找組態檔的位置和加載順序
這里給出一個通過管道過濾，干凈地過濾掉其他資訊，從而只保留MySql組態檔資訊的命令：
mysql --help | grep -A 1 'Default options are read from the following files in the given order'
演示效果：
[root@localhost mysql]# mysql --help | grep -A 1 'Default options are read from the following files in the given order'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@localhost mysql]#
當然，如果記不住怎么多，只需要記住mysql --help 即可，
常見的全域組態檔配置
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
max_connections=2000
lower_case_table_names = 0 #表名區分大小寫
server-id = 1
tmp_table_size=16M
transaction_isolation = REPEATABLE-READ
ready_only=1
MySQL記憶體引數配置
每一個connection記憶體引數配置：
-
sort_buffer_size connection排序緩沖區大小
? 建議256K(默認值)-> 2M之內（若配置為2M）
? 當查詢陳述句中有需要檔案排序功能時，馬上為connection分配配置的記憶體大小(2M) -
join_buffer_size connection關聯查詢緩沖區大小
? 建議256K(默認值)-> 1M之內
? 當查詢陳述句中有關聯查詢時，馬上分配配置大小的記憶體用這個關聯查詢，所以有可能在一個查詢陳述句中會分配很多個關聯查詢緩沖區
上述配置4000連接占用記憶體：
4000((256k/1024)M + (256K/1024)M) ~= 2G*
Innodb_buffer_pool_size
Innodb buffer/cache的大小（默認128M）
Innodb_buffer_pool
- 資料快取
- 索引快取
- 緩沖資料
- 內部結構
大的緩沖池可以減小多次磁盤I/O訪問相同的表資料以提高性能
參考計算公式：
Innodb_buffer_pool_size = （總物理記憶體 - 系統運行所用 - connection 所用） 90%*
wait_timeout
服務器關閉非互動連接之前等待活動的秒數
innodb_open_files
限制Innodb能打開的表的個數
innodb_write_io_threads、innodb_read_io_threads
?Innodb使用后臺執行緒處理innodb緩沖區資料頁上的讀寫 I/O(輸入輸出)請求
innodb_lock_wait_timeout
?InnoDB事務在被回滾之前可以等待一個鎖定的超時秒數
更多配置詳見此貼：
https://www.cnblogs.com/wyy123/p/6092976.html
MySQL資料庫表設計
? 資料庫設計(Database Design)是指對于一個給定的應用環境，構造最優的資料庫模式，建立資料庫及其應用系統，使之能夠有效地存盤資料，滿足各種用戶的應用需求（資訊要求和處理要求），在資料庫領域內，常常把使用資料庫的各類系統統稱為資料庫應用系統，
三大范式
第一范式（ 1NF）：
? 欄位具有原子性,不可再分， 所有關系型資料庫系統都滿足第一范式）資料庫表中的欄位都是單一屬性的， 不可再分；
第二范式（ 2NF）：
? 要求物體的屬性完全依賴于主鍵， 所謂完全依賴是指不能存在僅依賴主鍵一部分的屬性，如果存在， 那么這個屬性和主關鍵字的這一部分應該分離出來形成一個新的物體， 新物體與原物體之間是一對多的關系，為實作區分通常需要為表加上一個列，以存盤各個實體的惟一標識，簡而言之， 第二范式就是屬性完全依賴主鍵，
第三范式（ 3NF）：
? 滿足第三范式（ 3NF） 必須先滿足第二范式（ 2NF）， 簡而言之， 第三范式（ 3NF）要求一個資料庫表中不包含已在其它表中已包含的非主鍵資訊，
簡單一點：
- 每一列只有一個單一的值，不可再拆分
- 每一行都有主鍵能進行區分
- 每一個表都不包含其他表已經包含的非主鍵資訊，
資料庫表設計
充分的滿足第一范式設計將為表建立太量的列
? 資料從磁盤到緩沖區，緩沖區臟頁到磁盤進行持久的程序中，列的數量過多會導致性能下降，過多的列影響轉換和持久的性能
過分的滿足第三范式化造成了太多的表關聯
? 表的關聯操作將帶來額外的記憶體和性能開銷
使用innodb引擎的外鍵關系進行資料的完整性保證
? 外鍵表中資料的修改會導致Innodb引擎對外鍵約束進行檢查，就帶來了額外的開銷，一般資料庫不用外鍵，外鍵邏輯在業務層控制
舉個案例，想一想下面的SQL如何優化
SELECT
*
FROM
order
WHERE
(convert((price_full * 100 - price * 100) , SIGNED) - convert(coupon_price*100,SIGNED)
AND
is_del = 0)
ORDER BY
id
desc
limit 100
問題：在where條件，對索引列計算，會讓索引失效，從而掃描全表，性能比較差
優化：（僅供參考）
? 假如(convert((price_full * 100 - price * 100) , SIGNED) - convert(coupon_price*100,SIGNED)沒有優化的余地，那這條SQL無法拯救了嘛？
? 可以在該表添加一個列（成本列），把這個條件的運算結果在insert之前計算好結果（假設新增之后，price_full、price、coupon_price列不會頻繁更新），放入表中，然后對添加的這個成本列和is_del 建一個聯合索引，把查詢消耗的時間成本轉移到了新增，這也只是一個方案，實際還要看具體的業務場景，技術服務于業務，
附錄 58同城軍規
軍規適用場景：并發量大、資料量大的互聯網業務
軍規：介紹內容
解讀：講解原因，解讀比軍規更重要
一、基礎規范
-
必須使用InnoDB存盤引擎
解讀：支持事務、行級鎖、并發性能更好、CPU及記憶體快取頁優化使得資源利用率更高 -
必須使用UTF8字符集
UTF-8MB4
解讀：萬國碼，無需轉碼，無亂碼風險，節省空間 -
資料表、資料欄位必須加入中文注釋
解讀：N年后誰tm知道這個r1,r2,r3欄位是干嘛的 -
禁止使用存盤程序、視圖、觸發器、Event
解讀：高并發大資料的互聯網業務，架構設計思路是“解放資料庫CPU，將計算轉移到服務
層”，并發量大的情況下，這些功能很可能將資料庫拖死，業務邏輯放到服務層具備更好的
擴展性，能夠輕易實作“增機器就加性能”，資料庫擅長存盤與索引，CPU計算還是上移吧 -
禁止存盤大檔案或者大照片
解讀：為何要讓資料庫做它不擅長的事情？大檔案和照片存盤在檔案系統，資料庫里存URI
多好
二、命名規范
-
只允許使用內網域名，而不是ip連接資料庫
-
線上環境、開發環境、測驗環境資料庫內網域名遵循命名規范
業務名稱：xxx
線上環境：dj.xxx.db
開發環境：dj.xxx.rdb
測驗環境：dj.xxx.tdb
從庫在名稱后加-s標識，備庫在名稱后加-ss標識
線上從庫：dj.xxx-s.db
線上備庫：dj.xxx-sss.db -
庫名、表名、欄位名：小寫，下劃線風格，不超過32個字符，必須見名知意，禁止
拼音英文混用 -
表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx
三、表設計規范
-
單實體表數目必須小于500
-
單表列數目必須小于30
-
表必須有主鍵，例如自增主鍵
解讀：
- 主鍵遞增，資料行寫入可以提高插入性能，可以避免page分裂，減少表碎片提升空間和
記憶體的使用 - 主鍵要選擇較短的資料型別， Innodb引擎普通索引都會保存主鍵的值，較短的資料類
型可以有效的減少索引的磁盤空間，提高索引的快取效率 - 無主鍵的表洗掉，在row模式的主從架構，會導致備庫夯住
- 禁止使用外鍵，如果有外鍵完整性約束，需要應用程式控制
解讀：外鍵會導致表與表之間耦合，update與delete操作都會涉及相關聯的表，十分影響
sql 的性能，甚至會造成死鎖，高并發情況下容易造成資料庫性能，大資料高并發業務場景
資料庫使用以性能優先
四、欄位設計規范
- 必須把欄位定義為NOT NULL并且提供默認值
解讀：
- null的列使索引/索引統計/值比較都更加復雜，對MySQL來說更難優化
- null 這種型別MySQL內部需要進行特殊處理，增加資料庫處理記錄的復雜性；同等條
件下，表中有較多空欄位的時候，資料庫的處理性能會降低很多 - null值需要更多的存盤空，無論是表還是索引中每行中的null的列都需要額外的空間來標
識 - 對null 的處理時候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、
not in這些運算子號，如：where name!=’zhangsan’，如果存在name為null值的記
錄，查詢結果就不會包含name為null值的記錄
-
禁止使用TEXT、BLOB型別
解讀：會浪費更多的磁盤和記憶體空間，非必要的大量的大欄位查詢會淘汰掉熱資料，導致內
存命中率急劇降低，影響資料庫性能 -
禁止使用小數存盤貨幣
解讀：使用整數吧，小數容易導致錢對不上 -
必須使用varchar(20)存盤手機號
解讀：
- 涉及到區號或者國家代號，可能出現±()
- 手機號會去做數學運算么？
- varchar可以支持模糊查詢，例如：
like“138%”
- 禁止使用ENUM，可使用TINYINT代替
解讀：
- 增加新的ENUM值要做DDL操作
- ENUM的內部實際存盤就是整數，你以為自己定義的是字串？
五、索引設計規范
-
單表索引建議控制在5個以內
-
單索引欄位數不允許超過5個
解讀：欄位超過5個時，實際已經起不到有效過濾資料的作用了 -
禁止在更新十分頻繁、區分度不高的屬性上建立索引
解讀：
- 更新會變更B+樹，更新頻繁的欄位建立索引會大大降低資料庫性能
- “性別”這種區分度不大的屬性，建立索引是沒有什么意義的，不能有效過濾資料，性
能與全表掃描類似
- 建立組合索引，必須把區分度高的欄位放在前面
解讀：能夠更加有效的過濾資料
六、SQL使用規范
- 禁止使用
SELECT *，只獲取必要的欄位，需要顯示說明列屬性
解讀：
- 讀取不需要的列會增加CPU、IO、NET消耗
- 不能有效的利用覆寫索引
-
解讀：容易在增加或者洗掉欄位后出現程式BUG
-
禁止使用屬性隱式轉換
解讀：SELECT uid FROM t_user WHERE phone=110會導致全表掃描，而不
能命中phone索引 -
禁止在WHERE條件的屬性上使用函式或者運算式
解讀：SELECT uid FROM t_user WHERE from_unixtime(day)>=‘2021-01-31’會導致全
表掃描
正確的寫法是：
SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2021-01-31
00:00:00’)
- 禁止負向查詢，以及%開頭的模糊查詢
解讀：
- 負向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃描
- %開頭的模糊查詢，會導致全表掃描
-
禁止大表使用JOIN查詢，禁止大表使用子查詢
解讀：會產生臨時表，消耗較多記憶體與CPU，極大影響資料庫性能 -
禁止使用OR條件，必須改為IN查詢
解讀：舊版本Mysql的OR查詢是不能命中索引的，即使能命中索引，為何要讓資料庫耗費
更多的CPU幫助實施查詢優化呢？ -
應用程式必須捕獲SQL例外，并有相應處理
總結：大資料量高并發的互聯網業務，極大影響資料庫性能的都不讓用，不讓用喲，
還可以多看看阿里巴巴開發手冊終極版中的關于MySQL部分的
點擊下載《阿里巴巴開發手冊終極版》
寫在最后
更多架構知識，歡迎關注本套系列文章：Java架構師成長之路
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/255187.html
標籤：其他
下一篇：Echarts原始碼閱讀指南