一、概述
1. 為什么要優(yōu)化
- 一個(gè)應(yīng)用吞吐量瓶頸往往出現(xiàn)在數(shù)據(jù)庫(kù)的處理速度上
- 隨著應(yīng)用程序的使用，數(shù)據(jù)庫(kù)數(shù)據(jù)逐漸增多,數(shù)據(jù)庫(kù)處理壓力逐漸增大
- 關(guān)系型數(shù)據(jù)庫(kù)的數(shù)據(jù)是存放在磁盤(pán)上的，讀寫(xiě)速度較慢(與內(nèi)存中的數(shù)據(jù)相比)
2. 如何優(yōu)化
- 表、字段的設(shè)計(jì)階段,考量更優(yōu)的存儲(chǔ)和計(jì)算
- 數(shù)據(jù)庫(kù)自身提供的優(yōu)化功能，如索引
- 橫向擴(kuò)展，主從復(fù)制、讀寫(xiě)分離、負(fù)載均衡和高可用
- 典型SQL語(yǔ)句優(yōu)化(收效甚微)
二、字段設(shè)計(jì)
1. 典型方案
①. 對(duì)精度有要求
- decimal
- 小數(shù)轉(zhuǎn)整數(shù)
②. 盡量使用整數(shù)表示字符串（IP）
- inet_ aton("ip' )
- inet_ ntoa(num)
③. 盡可能使用not null
- nuI數(shù)值的計(jì)算邏輯比較復(fù)雜
④. 定長(zhǎng)和非定長(zhǎng)的選擇
- 較長(zhǎng)的數(shù)字?jǐn)?shù)據(jù)可以使用decimal
- char為定長(zhǎng)(超過(guò)長(zhǎng)度的內(nèi)容將被截掉)， varchar為非定長(zhǎng)，text對(duì)內(nèi)容 長(zhǎng)度的保存額外保存而varchar對(duì)長(zhǎng)度的保存占用數(shù)據(jù)空間
⑤. 字段數(shù)不要過(guò)多字段注釋是必要的、字段命名見(jiàn)名思意、可以預(yù)留字段以備擴(kuò)展
2. 范式
①. 第一范式：段原子性(關(guān)系型數(shù)據(jù)庫(kù)有列的念，默認(rèn)就符合了)
②. 第二范式：消除對(duì)主鍵的部分依賴(因?yàn)橹麈I可能不止一個(gè))；使用一 個(gè)與業(yè)務(wù)無(wú)關(guān)的字段作為主鍵
③. 第三范式：消除對(duì)主鍵的傳遞依賴；高內(nèi)聚, 如商品表可分為商品簡(jiǎn)略信息表和商品詳情表兩張表
三、存儲(chǔ)引擎的選擇（MyISAM和Innodb）
1. 功能差異
Innodb支持事務(wù)、 行級(jí)鎖定、外健
2. 存儲(chǔ)差異
①. 存儲(chǔ)方式：MyISAM的數(shù)據(jù)和索弓 |是分開(kāi)存儲(chǔ)的(.MYI.MYD) ， 而Innodb是存在一起的(.frm)
②. 表可移動(dòng)性：可以通過(guò)移動(dòng)表對(duì)應(yīng)的MYI和MYD能夠?qū)崿F(xiàn)表的移動(dòng)，而Innodb還有 額外的關(guān)聯(lián)文件
③. 碎片空間：MyISAM刪除數(shù)據(jù)時(shí)會(huì)產(chǎn)生碎片空間(占用表文件空間)，需要定期通過(guò)optimizetable table-name手動(dòng)優(yōu)化。而Innodb不會(huì)。
④. 有序存儲(chǔ)：Innodb插入數(shù)據(jù)時(shí)按照主鍵有序來(lái)插入。因此表中數(shù)據(jù)默認(rèn)按主鍵有序(耗費(fèi)寫(xiě)入時(shí)間，因?yàn)樾枰赽+ tree中查找插入點(diǎn)，但查找效率高)
3. 選擇差異
①. 讀多寫(xiě)少用MyISAM：新聞、博客網(wǎng)站
②. 讀多寫(xiě)也多用Innodb：
- 支持事務(wù)/外鍵，保證數(shù)據(jù)-致性、完整性
- 并發(fā)能力強(qiáng)(行鎖)
四、索引
1. 什么是索引
從數(shù)據(jù)中提取的具有標(biāo)識(shí)性的關(guān)鍵字，并且有到對(duì)應(yīng)數(shù)據(jù)的映射關(guān)系
2. 類型
①. 主鍵索引primary key：要求關(guān)鍵字唯一且不為null
②. 普通索引key：符合索引僅按照第一字段有序
③. 唯一索引unique key：要求關(guān)鍵字唯一
④. 全文索引fulltext key (不支持中文)
3. 索引管理語(yǔ)法
①. 查看索引
- show create table student
- desc student
②. 建立索引
- 創(chuàng)建時(shí)指定，如first. name varchar(1 6),last name(1 6) , key name(first_ name,last_ name)
- 更改表結(jié)構(gòu)：alter table student add key/unique key/primary key/ultext key key. name(first_ name,last_ name)
③. 刪除索引
- alter table student drop key key_ name
- 如果刪除的是主鍵索引，并且主鍵自增長(zhǎng)，則需要alter modify先取消自增長(zhǎng)再刪除
4. 執(zhí)行計(jì)劃explain
分析SQL執(zhí)行是否用到了索引，用到了什么索引
5. 索引使用的場(chǎng)景
- where：如果查找字段都建立了索引，則會(huì)索引覆蓋
- order by：如果排序字段建立了索引，而索引又是有序排列的，直接根據(jù)索引拿對(duì)應(yīng)數(shù)據(jù)即可，與讀取查詢出來(lái)的所有數(shù)據(jù)再排序相比效率很高
- join：如果join on的條件字段建立了索引，查找會(huì)變得高效
- 索引覆蓋：直接對(duì)索引做查找，而不去讀取數(shù)據(jù)
6. 語(yǔ)法細(xì)節(jié)
即使建立了索引，有些場(chǎng)景也不一定使用
- where id+1 = ?建議寫(xiě)成where id = ?-1，即保證索弓|字段的獨(dú)立出現(xiàn)
- like語(yǔ)句不要在關(guān)鍵字前模糊匹配，即"%keyword不會(huì)使用索引，而"keyword% 會(huì)使用索引
- or關(guān)鍵兩邊條件字段都建立索引時(shí)才會(huì)使用索引，只要有一邊不是就會(huì)做全表掃描
- 狀態(tài)值。像性別這樣的狀態(tài)值，-個(gè)關(guān)鍵字對(duì)應(yīng)很多條數(shù)據(jù)，會(huì)認(rèn)為使用索引比全表掃描效率還低
7. 索引的存儲(chǔ)結(jié)構(gòu)
- btree：搜索多叉樹(shù)：結(jié)點(diǎn)內(nèi)關(guān)鍵字有序排列，關(guān)鍵字之間有一個(gè)指針,查找效率log(nodeSize,N)，其中nodeSize指一 個(gè)結(jié)點(diǎn)內(nèi)關(guān)鍵字?jǐn)?shù)量 (這取決于關(guān)鍵字長(zhǎng)度和結(jié)點(diǎn)大小)
- b+ tree：由btree升級(jí)而來(lái)，數(shù)據(jù)和關(guān)鍵字存在一塊空間，省去了由關(guān)鍵字到數(shù)據(jù)的映射找數(shù)據(jù)存放地的時(shí)間
五、查詢緩存
1. 將select查詢結(jié)果緩存起來(lái)，key為SQL語(yǔ)句,value為查詢結(jié)果
如果SQL功能一樣，但只是多個(gè)空格或略微改動(dòng)都會(huì)導(dǎo)致key的不匹配
2. 客戶端開(kāi)啟
query. cache. _type
- 0-不開(kāi)啟
- 1-開(kāi)啟，默認(rèn)緩存每條select,針對(duì)某個(gè)sq不緩存: select sql-no-cache
- 2-開(kāi)啟，默認(rèn)都不緩存,通過(guò)select sql-cache制定緩存哪-個(gè)條
3. 客戶端設(shè)置緩存大小
query_ cache .size
4. 重蛋緩存
reset query cache
5. 緩存失效
日對(duì)數(shù)據(jù)表的改動(dòng)會(huì)導(dǎo)致基 于該數(shù)據(jù)表的所有緩存失效(表層面的管理)
六、分區(qū)
1. 默認(rèn)情況下一張表對(duì)應(yīng)一組存儲(chǔ)文件，但當(dāng)數(shù)據(jù)量較大時(shí)(通常千萬(wàn)條級(jí)別)需要將數(shù)據(jù)分到多組存儲(chǔ)文件，保證單個(gè)文件的處理效率
2. partition by分區(qū)函數(shù)(分區(qū)字段)(分區(qū)邏輯)
- hash-分區(qū)字段為整型
- key-分區(qū)字段為字符串
- range-基于比較，只支持less than
- list-基于狀態(tài)值
3. 分區(qū)管理
- 創(chuàng)建時(shí)分區(qū)：create table article0 partition by key(title) partitions 10
- 修改表結(jié)構(gòu)：alter table article add partition(分區(qū)邏輯)
4. 分區(qū)字段應(yīng)選擇常用的檢素字段，否則分區(qū)意義不大
七、水平分割和垂直分割
1. 水平
多張結(jié)構(gòu)相同的表存儲(chǔ)同一類型數(shù)據(jù)
單獨(dú)一張表保證id唯一性
2. 垂直
分割字段到多張表，這些表記錄是一對(duì)應(yīng)關(guān)系
八、集群
1. 主從復(fù)制
①. 首先手動(dòng)將slave和master同步一下
- stop slave
- master導(dǎo)出數(shù)據(jù)到slave執(zhí)行一遍
- show master status with read lock記錄File和Position
- 到slave.上change master to
②. start slave查看Slave_ IO_ Running和Slave_ SQL_ _Running,必須都為YES
③. master可讀可寫(xiě)，但slave只能讀，否則主從復(fù)制會(huì)失效需要重新手動(dòng)同步
④. MySQLreplicate快速配置主從復(fù)制
2. 讀寫(xiě)分離(基于主從復(fù)制)
①. 使用原stcConecton
WriteDatabase提供寫(xiě)連接
ReadDatabase提供讀連接
②. 借助Sping AOP和Aspec實(shí)現(xiàn)數(shù)據(jù)源動(dòng)態(tài)切換
- RoutingDataSourcelmpl extends AbstractRoutingDataSource,重寫(xiě)determineDatasource,注入到SqISessionFactory, 配置defaultTargetDatasource和targetDatasource (根據(jù)determineDatasource的返回值選擇 具體數(shù)據(jù)源value-ref)
- DatasourceAspect切面組件，配置切入點(diǎn)@Pointcut aspect0 (所有DAO類的所有方法)，配置前置增強(qiáng)@Before(" aspect0") before(Joinpoint point)， 通過(guò)point.getSignature.getName獲取方法名，與METHOD TYPE MAP的前綴集合比對(duì)，將write/read設(shè)置到當(dāng)前線程上(也是接下來(lái)要執(zhí)行DAO方法的線程，前置增強(qiáng)將其攔截下來(lái)了)
- DatasourceHandler,使用ThreadLocal在前置通知中將方法要使用的數(shù)據(jù)源綁定到執(zhí)行該方法的線程上，執(zhí)行方法要獲取數(shù)據(jù)源時(shí)再根據(jù)當(dāng)前線程獲取
3. 負(fù)載均衡
算法
- 輪詢
- 加權(quán)輪詢
- 依據(jù)負(fù)載情況
4. 高可用
為單機(jī)服務(wù)提供一個(gè)冗余機(jī)
- 心跳檢測(cè)
- 虛IP
- 主從復(fù)制
九、典型SQL
1. 線上DDL
為了避免長(zhǎng)時(shí)間表級(jí)鎖定
- copy策略,逐行復(fù)制，記錄復(fù)制期間舊表SQL日志重新執(zhí)行
- mysq|5.6 online ddl,大大縮短鎖定時(shí)間
2. 批量導(dǎo)入
①. 先禁用索引和約束,導(dǎo)入之后統(tǒng)一建立
②. 避免逐條事務(wù)
innodb為了保證一致性，默認(rèn)為每條SQL加事務(wù)(也是要耗費(fèi)時(shí)間的)，批量導(dǎo)入前應(yīng)手動(dòng)建立事務(wù)，導(dǎo)入完畢后手動(dòng)提交事務(wù)。
3. limit offset,rows
避兔較大的offset (較大頁(yè)碼數(shù))
offset用來(lái)跳過(guò)數(shù)據(jù)，完全可以用過(guò)濾篩選數(shù)據(jù)，而不是查出來(lái)之后再通過(guò)offset跳過(guò)
4. select *
盡量查詢所需字段,減少網(wǎng)絡(luò)傳輸延時(shí)(影響不大)
5. order by rand（）
會(huì)為每條數(shù)據(jù)生成一個(gè)隨機(jī)數(shù)最后根據(jù)隨機(jī)數(shù)排序，可以使用應(yīng)用程序生成隨機(jī)主鍵代替
6. limit 1
如果確定了僅僅檢索一條數(shù)據(jù)，建議都加上limit 1
十、慢查詢?nèi)罩?/h1>
1. 定位查詢效率較低的SQL,針對(duì)性地做優(yōu)化
2. 配置項(xiàng)
- 開(kāi)啟slow_ query. log
- 臨界時(shí)間long_ query. time
3. 慢查詢?nèi)罩緯?huì)自己記錄超過(guò)臨界時(shí)間的SQL，并保存在datadir下的xxx-slow.log中
十一、Profile
1. 自動(dòng)記錄每條SQL的執(zhí)行時(shí)間和具體某個(gè)SQL的詳細(xì)步驟花費(fèi)的時(shí)間
2. 配置項(xiàng)日
開(kāi)啟profiling
3. 查看日志信息show profiles
4. 查看具體SQL的詳細(xì)步驟花費(fèi)的時(shí)間日
show profiles for query Query_ ID
十二、典型的服務(wù)器配置
1. max_ connections, 最大客戶端連接數(shù)
2. table_ open_ cache, 表文件緩存句柄數(shù)，加快表文件的讀寫(xiě)
3. key_ buffer. _size, 索引緩存大小
4. innodb_ buffer. pool size, innodb的緩沖池大小，實(shí)現(xiàn)innodb各種功能的前提
5. innodb_ file_ per_ table,每個(gè)表一個(gè)ibd文件， 否則innodb共享 表空間
十三、壓測(cè)工具M(jìn)ySQLSlap
1. 自動(dòng)生成sq|并執(zhí)行來(lái)測(cè)試性能
myqslap -a-to-generate sql -root -root
2. 并發(fā)測(cè)試
mysqlslap --auto-generate-sql --concurrency= 100 -uroot -proot,模擬100個(gè)客戶端執(zhí)行sql
3. 多輪測(cè)試，反應(yīng)平均情況
mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -uroot -proot,模擬100個(gè)客戶端執(zhí)行sql.執(zhí)行3輪
4. 存儲(chǔ)引擎測(cè)試
- --engine=innodb：mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -- engine-innodb -uroot -proot,模擬100個(gè)客戶端執(zhí)行sql.執(zhí)行3輪，innodb的處理性能
- -- engine= myisam：mysqlslap -- auto-generate-sql --concurrency= 100 --interations=3 --engine-innodb -uroot -proot,模擬100個(gè)客戶端執(zhí)行sql.執(zhí)行3輪，myisam的處理性能
