MYSQL數據庫學習系列四

                  MYSQL數據庫學習系列四

四．MYSQL的應用優化
4.1-MySQL索引優化與設計
什么是索引
?索引的意義 —— 快速定位要查找的數據
數據庫索引查找
?全表掃描 VS 索引查找
如何根據首字母找到所在行
?二分查找
?B+tree
InnoDB表聚簇索引
索引中只放著排序字段和ID
創建索引
?單列索引
create index idx_test1 on tb_student (name);
?聯合索引
create index idx_test2 on tb_student (name, age);
?索引中先根據name排序，name相同的情況下，根據age排序
索引維護
?索引維護由數據庫自動完成
?插入/修改/刪除每一個索引行都會變成一個內部封裝的事務
?索引越多，事務越長，代價越高
?索引越多對表的插入和索引字段修改就越慢
?控制表上索引的數量，切忌胡亂添加無用索引
如何使用索引
?依據WHERE查詢條件建立索引
select a, b from tab_a where c=? ;
idx_c (c)select a, b from tab_a where c=? and d=?;
idx_cd (c, d)
?排序order by, group by, distinct字段添加索引
select from tb_a order by a;select a, count() from tb_a group by a;
idx_a (a)
select from tb_a order by a, b;
idx_a_b (a, b)
select from tb_a order where c=? by a;
idx_c_a (c, a)
索引與字段選擇性
?
某個字段其值的重復程度
?
?
選擇性很差的字段通常不適合創建單列索引
?
o男女比例相仿的列表中性別不適合創建單列索引
o如果男女比例極不平衡，要查詢的又是少數方(理工院校查女生)可以考慮使用索引
?
聯合索引中選擇性好的字段應該排在前面
?
select from tab_a where gender=? and name=?;
idx_a1 (name, gender)
聯合索引與前綴查詢
?聯合索引能為前綴單列，復列查詢提供幫助
idx_smp (a, b, c)where a=? ;where a=? and b=? ;where a=? and c=? ;(部分ok)
?合理創建聯合索引，避免冗余 (a) , (a, b) , (a, b, c) X (a, b, c) ok
長字段上的索引
?在非常長的字段上建立索引影響性能
?InnoDB索引單字段(utf8)只能取前767 bytes
?對長字段處理的方法
oEmail類，建立前綴索引
Mail_addr varchar(2048)
idx_mailadd (Mail_addr(30)) ok
o住址類，拆分字段
Home_address varchar(2048)
idx_mailadd (Mail_addr(30)) ? -- 很可能前半段都是相同的省市區街道名稱
Province varchar(1024), City varchar(1024), District varchar(1024), Local_address varchar(1024) ... -- 建立聯合索引或單列索引
索引覆蓋掃
?最核心SQL考慮索引覆蓋
select Name from tb_user where UserID=?
Key idx_uid_name(UserID, Name)
?
?不需要回表獲取name字段，IO最小，效率最高
無法使用索引的情況
?索引列進行數學運算或函數運算
where id+1=10; Xwhere id = (10-1); ok
year(col) < 2007; X
col < '2007-01-01'; ok
?
?未含符合索引的前綴字段
Idxabc (a, b, c):where b=? and c=?; X
(b, c) ok
?
?前綴通配,''和'%'通配符
Like '%xxx%'; XLike 'xxx%'; ok
?
?where 條件使用NOT, <>, !=
?字段類型匹配
o并不絕對，但是無法預測地會造成問題，不要使用
a int(11), idx_a (a)where a = '123'; Xwhere a = 123 ; ok
利用索引排序
idx_a_b (a, b)
?能夠使用索引幫助排序的查詢：
order by a
a = 3 order by border by a, border by a desc, b desc
a > 5 order by a
?不能使用索引幫助排序的查詢：
order by b
a > 5 order by b
a in (1, 3) order by border by a asc, b desc
如何確定一個查詢走沒走索引，走了哪個索引
?explain是確定一個查詢如何走索引最簡便有效的方法?explain select from tb_test ;
?關注的項目
otype:查詢access的方式
okey:本次查詢最終選擇使用哪個索引，NULL為未使用索引
okey_len:選擇的索引使用的前綴長度或者整個長度
orows:可以理解為查詢邏輯讀，需要掃描過的記錄行數
oextra:額外信息，主要指的fetch data的具體方式
4.2-MySQL數據庫設計
什么是Schema設計
?設計數據庫的表，索引，以及表和表的關系
o在數據模型的基礎上將關系模型轉化為數據庫表
o滿足業務模型需要基礎上根據數據庫和應用特點優化表結構
為什么Schema需要設計
?Schema關系到應用程序功能與性能
o滿足業務功能需要
o同性能密切相關
o數據庫擴展性
o滿足周邊需求(統計，遷移等)
?關系型數據庫修改Schema經常是高危操作
oSchema設計要體現一定的前瞻性
完全由開發者主導的Schema設計
?著眼于實現當前功能
?完全基于功能的設計可能存在一些隱患
o不合理的表結構或索引設計造成性能問題
o沒有合理評估到數據量的增長造成空間緊張而且難以維護
o需求頻繁修改造成表結構經常變更
o業務重大調整導致數據經常需要重構訂正
基于性能的表設計
?根據查詢需要設計好索引
?根據核心查詢需求，適當調整表結構
?基于一些特殊業務需求，調整實現方式
索引
?正確使用索引
?更新盡可能使用主鍵或唯一索引
?主鍵盡可能使用自增ID字段
?核心查詢覆蓋掃描
o用戶登錄需要根據用戶名返回密碼用于驗證create index idx_uname_passwd on tb_user (username, password);
o建立聯合索引避免回表取數據
反范式，冗余必要字段
?針對核心SQL保留查詢結果所必須的冗余字段，避免頻繁join
o例：消息表中冗余了每次讀消息必須返回的nickname字段，避免每次讀消息都變成join操作。代價是用戶修改nickname成本變高。
拆分大字段
?拆分大字段到單獨表中，避免范圍掃描代價大
o例：博文表拆分兩份，標題表只保留標題和內容縮略部分，用于快速批量返回標題列表，正文表保存大段博文內容，用于點開文章單個讀取
避免過多字段或過長行
?根據SQL必要返回設計字段，有必要就拆表，避免過多字段
?一次沒有必要獲取那么多列數據
?行過長導致表數據頁記錄變少，范圍掃描性能降低
?更新數據也代價增加
?16K也最少放2行，可能出現行遷移
分頁查詢
?避免limit + offset過大
?應該使用自增主鍵ID模擬分頁
o第一頁，直接查
o得到第一頁的max(id)=123(一般是最后一條記錄)
o第二頁，帶上id>123查詢：where id>123 limit 100
o這樣每次只需要掃描100條數據
?要求業務上禁止查詢XX頁之后的數據
熱點讀數據特殊處理
?根據數據獲取的頻率或數量不同對熱點數據做特殊處理
o例1：論壇系統中置頂帖、公告貼，可以單獨拆分存儲，由于每次訪問都要全部讀出來，單獨放在一起，避免每次都到普通表中隨機找出來
熱點寫數據特殊處理
?根據數據獲取的頻率或數量不同對熱點數據做特殊處理
o例2：微博系統中對于大量關注的熱點賬號消息從"推"改為"拉"，避免過量insert操作。
準實時統計
?對不需要精確結果的計數等統計要求，建立定期更新結果表
o例：首頁要求展示動態成交總金額，維護一個計數表，每分鐘根據原表注冊時間獲取增量sum值更新計數表，避免每次用戶刷新都要掃描交易全記錄表
實時統計改進1 - 觸發器實時統計
?對需要精確統計的計數利用數據庫觸發器維護計數表
o例：用戶量沖億活動要求實時統計，用戶表上加觸發器，每次有新用戶插入就同時在計數表+1
實時統計改進2 - 緩存實時統計
?對需要精確統計的計數利用前端緩存實時維護計數
o例：用戶量沖億活動要求實時統計，注冊數量在緩存中實時維護，每注冊一個就+1，完全避免數據庫讀寫操作。緩存萬一故障失效，可從數據庫整體count重新獲取。
實時統計改進2 - 最大自增ID獲取總數
?很多邏輯可以利用自增ID主鍵最大值直接作為總數
o例：用戶量沖億活動要求實時統計，用戶表加上自增ID作為主鍵，只要取當時max(ID)就可以得到用戶總數
課拓展性設計
?可拓展性
o硬件資源增長有極限的情況下處理盡可能久的線上業務
?數據分級，冷數據歸檔與淘汰
o可以不斷釋放空間供新數據使用
?為數據分布式做準備
o分庫分表
o水平拆分
o犧牲一定的關系模型支持
分區表與數據淘汰
?range分區
?適合數據需要定期過期的大表
?單個分區掃描遷移數據到歷史庫避免全表掃描IO開銷
?刪除單個分區非常高效
分區表與垂直分區
?list分區
?適合將來可能要基于地區，類目等方式垂直拆分數據的方式
?清理節點上不要的數據非常高效
分區表與水平分區
?hash分區
?適合將來需要做水平拆分的表
?清理節點上不要的數據非常高效
MySQL分區表的局限
?主鍵或唯一鍵必須包含在分區字段內
?分區字段必須是整數類型，或者加上返回整數的函數
滿足周邊需求
?為周邊需求額外增加表設計
o為后臺統計任務增加特殊索引
o為數據遷移或統計需求增加時間戳
統計和后臺需求
?統計運行SQL往往和線上有很大不同
o利用MySQL——主多從，主從可以建不同索引的特性將統計分流到特定從庫
o包括一些特殊用戶批量查詢等，所有對線上有IO壓力的查詢都要讀寫分離
自動更新時間戳
?統計需求經常要求從線上讀走增量數據
?表的第一個timestamp類型字段再寫入時如果不填值，會自動寫入系統時間戳
?表的第一個timestamp類型字段每次記錄發生更新后都會自動更新
?在update_time字段上建索引用于定時導出增量數據
Schema設計與前瞻性
?基于歷史經驗教訓，預防和解決同類問題
?把折騰DBA夠嗆的所有Schema改造的原因記錄并分析總結 例：
?業務為例用戶信息加密做了大改造
o數據庫結果大量改動，增加了加密字段，驗證策略表，所有表重新訂正數據等等
o是否所有用到用戶信息管理的應用都要去上線就用密文？
?程序bug誤刪數據，線上風險大
o改造業務流程，不再刪除數據，加入is_deleted標記位，經常給各種表加
o今后的類似表是否一上線就都用標記位的方式，并加上修改原因字段？
?支付類應用后期做了風控改造
o對線上訂單大表改造，加了限額，終端類型等字段
o遇到支付類應用，是否一上線就提示業務是否需要考慮風控并留好相關字段？
4.3-MySQL容量評估
性能容量評估
?分析線上業務場景
?評估數據庫服務器所需性能指標
?預估可能成為瓶頸的服務器資源
?幫助數據庫性能調優
數據庫服務器硬件性能指標
?磁盤IO性能
?內存容量
?CPU
?網絡吞吐量
?磁盤容量
數據庫業務特點關鍵詞
?OLTP/OLAP類型
?并發請求
?讀寫比例
?數據量
?冷熱數據比
?數據分級存儲
OLTP/OLAP
?T = Transaction
?面向廣大用戶，高并發，較短事務操作
?互聯網應用絕大部分屬于OLTP
?OLTP看重服務器CPU，內存，寫事務較多或內存不夠則依賴磁盤IO
?A = Analytical
?通常面向內部人員，大規模復查詢
?OLAP看重磁盤掃描的IO能力，部分依賴內存排序
并發請求 - 衡量線上業務繁忙程度
?業務高峰時數據庫的每秒并發訪問量是多少
?通過應用服務器數量，連接池配置判斷
?通過產品估算初上線用戶規模和用戶增長速度判斷
?通過實際業務業務類型判斷
?并發量相關資源：CPU
讀寫比例 - 描述應用程序如何使用數據庫
?線上業務select只讀與update/delete/insert寫操作比例
?delete/update通常都是先讀再寫
?insert需要區分數據寫入時持續insert還是大量導入數據
?根據業務實際場景分析
?多讀場景相關資源：內存
?多寫場景相關資源：磁盤IO
數據量 - 總量
?數據庫服務器存儲設備可擴容能力的上限
?根據估算的業務量，寫入模式，分析數據增長量
?預估一個硬件升級周期內數據庫可存放數據的總量，上線時要留好余量
?數據總量相關資源：磁盤容量
冷數據與熱數據 - 有用數據的實時集合
?熱數據，線上最新一定周期內將被反復訪問的數據
?冷數據，線上保存著的，最近不會被在線用戶用到的數據
?估算活躍用戶量，數據增長量等預估熱數據量
?內存大小盡可能足夠存放線上實時熱數據
?熱數據相關資源：內存
線上數據分層存儲 - 緩解線上磁盤空間壓力
?最新熱數據確保放在內存中
?還可能訪問到的較早數據存放在線上庫磁盤中
?更早的不會常規訪問的數據定期遷移至歷史庫中
?區分哪些數據時效性強可以遷移
服務器資源選型 - 將可選方案列出來
資源指標 可選方案
磁盤IO性能 單盤 -> 盤陣; SATA -> SAS; HDD -> SSD
內存容量 較小內存 -> 較大內存
CPU 普通 -> 多核，超線程
網絡吞吐量 千兆 -> 萬兆; 單網卡 -> 多路;
磁盤容量 單盤 -> 盤陣; 單盤 -> LVM
案例一，網易云音樂曲庫數據庫服務器評估
?用于存放線上數千萬歌曲信息
?確定屬于OLTP線上類型數據庫
?并發請求量
o50臺應用服務器，每臺最大連接數100
o可能峰值5000qps，并發請求量較大
?CPU需求高
?讀寫比例
o訪問模式以用戶列出歌單和播放歌曲時查詢歌曲信息為主，用戶只有只讀查詢
o寫數據發生在錄入新歌或修改歌曲信息時后臺操作，寫比例小，且為批量導入
o讀寫比100:1
?數據總量
o估算每首歌信息8K，總計5000萬，總量400G
o數據總量增長相對緩慢
?冷熱數據
o5000萬歌曲中大約40%可能被訪問，10%屬于熱點歌曲
o熱數據大約<=40G
?數據分級存儲需求
o由于沒有用戶產生的數據，歌曲信息無法分級存儲
?內存需求一般，>=40G
?磁盤IO能力需求一般
?網絡流量要求，8k*2500/1024 ≈ 20MB/S，一般
資源指標 可選方案
磁盤IO性能 兩塊SAS做RAID1
內存容量 96G內存
CPU 2c8core超線程 相當于32核
網絡吞吐量 千兆雙網卡bunding
磁盤容量 900G
案例二，網易理財銷售數據庫服務器評估
?用于存放理財用戶線上訂單
?確定屬于OLTP線上類型數據庫
?業務場景有明顯特征
o特定高息產品秒殺銷售時間窗有大量并發訂單寫入
o平時只有少量訂單查詢和請求，和較低的常規產品購買請求
?評估應以滿足最關鍵的業務高峰為基準
?并發請求量
o秒殺期間持續時間短，但是并發量預估30臺應用服務器約2000tps
?讀寫比例
o高峰時寫訂單是主要開銷操作
?CPU要求高
?磁盤IO要求很高
?數據總量
o根據業務分析，訂單屬于寫入瞬時量大，總量較小，單筆金額較高
o總量預估一年成交百萬級別，增長較穩定
o判斷數據存儲需求小于200G
?冷熱數據
o峰值寫入為主，內存要求存放熱點期間產生的臟數據即可
?數據分級存儲需求
o用戶訂單業務約定頁面展示最近半年訂單，半年前的需要到歷史查詢頁面專門查詢
o因此可以做分級存儲，遷移所有半年前的訂單至歷史庫
?內存需求一般， >= 30G
?磁盤空間需求一般， >=200G
?磁盤IO能力需求很高
?網絡要求較高
o并發流量較高
o響應速度要求高
資源指標 可選方案
磁盤IO性能 兩塊SSD做RAID1
內存容量 64G內存
CPU 2c8core超線程 相當于32核
網絡吞吐量 萬兆雙網卡bunding
磁盤容量 600G
4.4-MySQL性能測試
為什么需要性能測試
?對線上產品缺乏心理預估
?重現線上異常
?規劃未來的業務增長
?測試不同硬件軟件配置
性能測試的分類
?設備層的測試
?業務層的測試
?數據庫層的測試
設備層的測試
?關注的指標
o服務器、磁盤性能
o磁盤壞塊率
o服務器壽命
業務層測試
?針對業務進行測試
數據庫層測試
?什么情況下要做MySQL的測試
o測試不同的MySQL分支版本
o測試不同的MySQL版本
o測試不同的MySQL參數搭配
MySQL測試分類
?CPU Bound
?IO Bound
寫入測試 更新測試 純讀測試 混合模式
常用的測試工具
?開源的MySQL性能測試工具
osysbench
otpcc-mysql
omysqlslap
?針對業務編寫性能測試工具
oblogbench
性能測試衡量指標
?服務吞吐量(TPS, QPS)
?服務響應時間
?服務并發性
Sysbench
?業界較為出名的性能測試工具
?可以測試磁盤、CPU、數據庫
?支持多種數據庫：Oracle, DB2, MySQL
?需要自己下載編譯安裝
?建議版本：sysbench0.5
編譯安裝Sysbench
?下載sysbench
ogit clone https://github.com/akopytov/sysbench.git
?編譯&安裝
o./autogen.sh
o./configure
omake && make install
Sysbench流程
?常見的做法
初始化數據 -> 運行測試 -> 清理數據
Prepare語法
sysbench --test=parallel_prepare.lua --oltp_tables_count=1 --rand-init=on --oltp-table-size=500000000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sys --mysql-password=netease --mysql-db=sbtest --max-requests=0 prepare
參數 含義
--test=parallel_prepare.lua 運行導數據的腳本
--oltp_tables_count 測試需要幾張表
--oltp-table-size 每張表的大小
--mysql-host MySQL Host
--mysql-port MySQL Port
--mysql-db MySQL DB
--mysql-user MySQL User
--mysql-password MySQL Password
--rand-init 是否隨機初始化數據
--max-requests 執行多少個請求之后停止
prepare 執行導數據
Sysbench表結構
create table 'sbtest1'(
'id' int(10) unsigned not null AUTO_INCREMENT,
'k' int(10) unsigned not null DEFAULT '0',
'c' char(120) not null DEFAULT '',
'pad' char(60) not null DEFAULT '',
PRIMARY KEY ('id'),
KEY 'k_1' ('k')
) ENGINE=InnoDB AUTO_INCREMENT=3000000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
Run語法
sysbench --test=oltp.lua --oltp_tables_count=1 --num-threads=100 --oltp-table-size=500000000 --oltp-read-only=off --report-interval=10 --rand-type=uniform --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sys --mysql-password=netease --mysql-db=sbtest --max-time=1000 --max-requests=0 run
參數 含義
--test=oltp.lua 需要運行的lua腳本
--oltp_tables_count 測試需要幾張表
--oltp-table-size 每張表的大小
--num-threads 測試并發線程數
--oltp-read-only 是否為只讀測試
--report-interval 結果輸出間隔
--rand-type 數據分布模式，熱點數據或者隨機數據
--max-time 最大運行時間
--max-requests 執行多少個請求之后停止
prepare 開始測試
特殊情況
?寫入測試
寫入數據進行測試 -> 清理數據
cleanup
?手動drop掉表和database
?使用sysbench提供的cleanup命令
sysbench --test=parallel_prepare.lua --oltp_tables_count=1 --rand-init=on --oltp-table-size=500000000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sys --mysql-password=netease --mysql-db=sbtest --max-requests=0 cleanup
Tpcc-mysql
?
TPC-C是專門針對聯機交易處理系統(OLTP系統)的規范
?
?
Tpcc-mysql由percona根據規范實現
?
?
下載Tpcc-mysql
?
obzr branch lp:~percona-dev/perconatools/tpcc-mysql
?
編譯安裝
?
使用Tpcc-mysql的步驟
創建表結構和索引 -> 導數據 -> 運行測試 -> 數據清理
創建表結構
?create_table.sql
?add_fkey_idx.sql
Tpcc-load
tpcc_load [server] [DB] [user] [pass] [warehouse]
函數 含義
server 數據庫IP
DB DB名稱
user 用戶名
pass 密碼
warehouse 倉庫數量
Tpcc-start
tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouse -c connections -r warmup_time -I running_time -i report-interval -f report-file
函數 含義
warehouse 倉庫數量
connections 并發線程數
warmup_time 預熱時間
running_time 運行時間
report_interval 輸出時間間隔
report_file 輸出文件
總結
?IO Bound測試數據量要遠大于內存、CPU Bound測試數據量要小于內存
?測試時間建議大于60分鐘，減小誤差
?Sysbench更傾向于測試MySQL性能、TPCC更接近于業務
?運行測試程序需要同時監控機器負載，MySQL各項監控指標