MySQL數據庫優化方法

這篇文章給大家分享的是有關MySQL數據庫優化方法的內容。小編覺得挺實用的，因此分享給大家學習。如下資料是關于MySQL數據庫優化的內容。

一、概述

1. 為什么要優化

• 一個應用吞吐量瓶頸往往出現在數據庫的處理速度上
• 隨著應用程序的使用，數據庫數據逐漸增多,數據庫處理壓力逐漸增大
• 關系型數據庫的數據是存放在磁盤上的，讀寫速度較慢(與內存中的數據相比)

2. 如何優化

• 表、字段的設計階段,考量更優的存儲和計算
• 數據庫自身提供的優化功能，如索引
• 橫向擴展，主從復制、讀寫分離、負載均衡和高可用
• 典型SQL語句優化(收效甚微)

二、字段設計

1. 典型方案

①. 對精度有要求

• decimal
• 小數轉整數

②. 盡量使用整數表示字符串（IP）

• inet_ aton("ip' )
• inet_ ntoa(num)

③. 盡可能使用not null

• nuI數值的計算邏輯比較復雜

④. 定長和非定長的選擇

• 較長的數字數據可以使用decimal
• char為定長(超過長度的內容將被截掉)， varchar為非定長，text對內容 長度的保存額外保存而varchar對長度的保存占用數據空間

⑤. 字段數不要過多字段注釋是必要的、字段命名見名思意、可以預留字段以備擴展

2. 范式

①. 第一范式：段原子性(關系型數據庫有列的念，默認就符合了)

②. 第二范式：消除對主鍵的部分依賴(因為主鍵可能不止一個)；使用一 個與業務無關的字段作為主鍵

③. 第三范式：消除對主鍵的傳遞依賴；高內聚, 如商品表可分為商品簡略信息表和商品詳情表兩張表

三、存儲引擎的選擇（MyISAM和Innodb）

1. 功能差異

Innodb支持事務、 行級鎖定、外健

2. 存儲差異

①. 存儲方式：MyISAM的數據和索弓 |是分開存儲的(.MYI.MYD) ， 而Innodb是存在一起的(.frm)

②. 表可移動性：可以通過移動表對應的MYI和MYD能夠實現表的移動，而Innodb還有 額外的關聯文件

③. 碎片空間：MyISAM刪除數據時會產生碎片空間(占用表文件空間)，需要定期通過optimizetable table-name手動優化。而Innodb不會。

④. 有序存儲：Innodb插入數據時按照主鍵有序來插入。因此表中數據默認按主鍵有序(耗費寫入時間，因為需要在b+ tree中查找插入點，但查找效率高)

3. 選擇差異

①. 讀多寫少用MyISAM：新聞、博客網站

②. 讀多寫也多用Innodb：

• 支持事務/外鍵，保證數據-致性、完整性
• 并發能力強(行鎖)

四、索引

1. 什么是索引

從數據中提取的具有標識性的關鍵字，并且有到對應數據的映射關系

2. 類型

①. 主鍵索引primary key：要求關鍵字唯一且不為null

②. 普通索引key：符合索引僅按照第一字段有序

③. 唯一索引unique key：要求關鍵字唯一

④. 全文索引fulltext key (不支持中文)

3. 索引管理語法

①. 查看索引

• show create table student
• desc student

②. 建立索引

• 創建時指定，如first. name varchar(1 6),last name(1 6) , key name(first name,last name)
• 更改表結構：alter table student add key/unique key/primary key/ultext key key. name(first name,last name)

③. 刪除索引

• alter table student drop key key_ name
• 如果刪除的是主鍵索引，并且主鍵自增長，則需要alter modify先取消自增長再刪除

4. 執行計劃explain

分析SQL執行是否用到了索引，用到了什么索引

5. 索引使用的場景

• where：如果查找字段都建立了索引，則會索引覆蓋
• order by：如果排序字段建立了索引，而索引又是有序排列的，直接根據索引拿對應數據即可，與讀取查詢出來的所有數據再排序相比效率很高
• join：如果join on的條件字段建立了索引，查找會變得高效
• 索引覆蓋：直接對索引做查找，而不去讀取數據

6. 語法細節

即使建立了索引，有些場景也不一定使用

• where id+1 = ?建議寫成where id = ?-1，即保證索弓|字段的獨立出現
• like語句不要在關鍵字前模糊匹配，即"%keyword不會使用索引，而"keyword% 會使用索引
• or關鍵兩邊條件字段都建立索引時才會使用索引，只要有一邊不是就會做全表掃描
• 狀態值。像性別這樣的狀態值，-個關鍵字對應很多條數據，會認為使用索引比全表掃描效率還低

7. 索引的存儲結構

• btree：搜索多叉樹：結點內關鍵字有序排列，關鍵字之間有一個指針,查找效率log(nodeSize,N)，其中nodeSize指一 個結點內關鍵字數量 (這取決于關鍵字長度和結點大小)

• b+ tree：由btree升級而來，數據和關鍵字存在一塊空間，省去了由關鍵字到數據的映射找數據存放地的時間

五、查詢緩存

1. 將select查詢結果緩存起來，key為SQL語句,value為查詢結果

如果SQL功能一樣，但只是多個空格或略微改動都會導致key的不匹配

2. 客戶端開啟

query. cache. _type

• 0-不開啟
• 1-開啟，默認緩存每條select,針對某個sq不緩存: select sql-no-cache
• 2-開啟，默認都不緩存,通過select sql-cache制定緩存哪-個條

3. 客戶端設置緩存大小

query_ cache .size

4. 重蛋緩存

reset query cache

5. 緩存失效

日對數據表的改動會導致基 于該數據表的所有緩存失效(表層面的管理)

六、分區

1. 默認情況下一張表對應一組存儲文件，但當數據量較大時(通常千萬條級別)需要將數據分到多組存儲文件，保證單個文件的處理效率

2. partition by分區函數(分區字段)(分區邏輯)

• hash-分區字段為整型
• key-分區字段為字符串
• range-基于比較，只支持less than
• list-基于狀態值

3. 分區管理

• 創建時分區：create table article0 partition by key(title) partitions 10
• 修改表結構：alter table article add partition(分區邏輯)

4. 分區字段應選擇常用的檢素字段，否則分區意義不大

七、水平分割和垂直分割

1. 水平

多張結構相同的表存儲同一類型數據

單獨一張表保證id唯一性

2. 垂直

分割字段到多張表，這些表記錄是一對應關系

八、集群

1. 主從復制

①. 首先手動將slave和master同步一下

• stop slave
• master導出數據到slave執行一遍
• show master status with read lock記錄File和Position
• 到slave.上change master to

②. start slave查看Slave IO Running和Slave SQL _Running,必須都為YES

③. master可讀可寫，但slave只能讀，否則主從復制會失效需要重新手動同步

④. mysqlreplicate快速配置主從復制

2. 讀寫分離(基于主從復制)

①. 使用原stcConecton

WriteDatabase提供寫連接

ReadDatabase提供讀連接

②. 借助Sping AOP和Aspec實現數據源動態切換

• RoutingDataSourcelmpl extends AbstractRoutingDataSource,重寫determineDatasource,注入到SqISessionFactory, 配置defaultTargetDatasource和targetDatasource (根據determineDatasource的返回值選擇 具體數據源value-ref)

• DatasourceAspect切面組件，配置切入點@Pointcut aspect0 (所有DAO類的所有方法)，配置前置增強@Before(" aspect0") before(Joinpoint point)， 通過point.getSignature.getName獲取方法名，與METHOD TYPE MAP的前綴集合比對，將write/read設置到當前線程上(也是接下來要執行DAO方法的線程，前置增強將其攔截下來了)

• DatasourceHandler,使用ThreadLocal在前置通知中將方法要使用的數據源綁定到執行該方法的線程上，執行方法要獲取數據源時再根據當前線程獲取

3. 負載均衡

算法

• 輪詢
• 加權輪詢
• 依據負載情況

4. 高可用

為單機服務提供一個冗余機

• 心跳檢測
• 虛IP
• 主從復制

九、典型SQL

1. 線上DDL

為了避免長時間表級鎖定

• copy策略,逐行復制，記錄復制期間舊表SQL日志重新執行
• mysq|5.6 online ddl,大大縮短鎖定時間

2. 批量導入

①. 先禁用索引和約束,導入之后統一建立

②. 避免逐條事務

innodb為了保證一致性，默認為每條SQL加事務(也是要耗費時間的)，批量導入前應手動建立事務，導入完畢后手動提交事務。

3. limit offset,rows

避兔較大的offset (較大頁碼數)

offset用來跳過數據，完全可以用過濾篩選數據，而不是查出來之后再通過offset跳過

4. select *

盡量查詢所需字段,減少網絡傳輸延時(影響不大)

5. order by rand（）

會為每條數據生成一個隨機數最后根據隨機數排序，可以使用應用程序生成隨機主鍵代替

6. limit 1

如果確定了僅僅檢索一條數據，建議都加上limit 1

十、慢查詢日志

1. 定位查詢效率較低的SQL,針對性地做優化

2. 配置項

• 開啟slow_ query. log
• 臨界時間long_ query. time

3. 慢查詢日志會自己記錄超過臨界時間的SQL，并保存在datadir下的xxx-slow.log中

十一、Profile

1. 自動記錄每條SQL的執行時間和具體某個SQL的詳細步驟花費的時間

2. 配置項日

開啟profiling

3. 查看日志信息show profiles

4. 查看具體SQL的詳細步驟花費的時間日

show profiles for query Query_ ID

十二、典型的服務器配置

1. max_ connections, 最大客戶端連接數

2. table open cache, 表文件緩存句柄數，加快表文件的讀寫

3. key_ buffer. _size, 索引緩存大小

4. innodb_ buffer. pool size, innodb的緩沖池大小，實現innodb各種功能的前提

5. innodb file per_ table,每個表一個ibd文件， 否則innodb共享 表空間

十三、壓測工具MySQLSlap

1. 自動生成sq|并執行來測試性能

myqslap -a-to-generate sql -root -root

2. 并發測試

mysqlslap --auto-generate-sql --concurrency= 100 -uroot -proot,模擬100個客戶端執行sql

3. 多輪測試，反應平均情況

mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -uroot -proot,模擬100個客戶端執行sql.執行3輪

4. 存儲引擎測試

• --engine=innodb：mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -- engine-innodb -uroot -proot,模擬100個客戶端執行sql.執行3輪，innodb的處理性能

• -- engine= myisam：mysqlslap -- auto-generate-sql --concurrency= 100 --interations=3 --engine-innodb -uroot -proot,模擬100個客戶端執行sql.執行3輪，myisam的處理性能
• 看完這篇文章，你們學會MySQL數據庫優化方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀。