這篇文章給大家分享的是有關(guān)MySQL數(shù)據(jù)庫(kù)優(yōu)化方法的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)。如下資料是關(guān)于MySQL數(shù)據(jù)庫(kù)優(yōu)化的內(nèi)容。

沂南網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、APP開(kāi)發(fā)、響應(yīng)式網(wǎng)站開(kāi)發(fā)等網(wǎng)站項(xiàng)目制作，到程序開(kāi)發(fā)，運(yùn)營(yíng)維護(hù)。成都創(chuàng)新互聯(lián)自2013年創(chuàng)立以來(lái)到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。

一、概述

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. 類(lèi)型

①. 主鍵索引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ǔ)同一類(lèi)型數(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類(lèi)的所有方法)，配置前置增強(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)罩?/h2>

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的處理性能
• 看完這篇文章，你們學(xué)會(huì)MySQL數(shù)據(jù)庫(kù)優(yōu)化方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀。

本文題目：MySQL數(shù)據(jù)庫(kù)優(yōu)化方法
網(wǎng)站鏈接：http://jinyejixie.com/article20/ggegjo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、全網(wǎng)營(yíng)銷(xiāo)推廣、企業(yè)網(wǎng)站制作、外貿(mào)建站、網(wǎng)站改版、網(wǎng)站設(shè)計(jì)公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)