MySql數(shù)據(jù)庫(kù)保姆級(jí)優(yōu)化方案

序號(hào)

時(shí)間

版本

1

2021/11/25

1.0

劉志虎

優(yōu)化角度數(shù)據(jù)庫(kù)設(shè)計(jì)操作語(yǔ)句基礎(chǔ)設(shè)施數(shù)據(jù)庫(kù)架構(gòu)

一、數(shù)據(jù)庫(kù)設(shè)計(jì)優(yōu)化

根據(jù)業(yè)務(wù)需求選擇正確的存儲(chǔ)引擎，如果對(duì)事務(wù)性要求高就用InnoDB（默認(rèn)），讀多寫少盡量使用MyISAM。

設(shè)置合理的字段類型及字段長(zhǎng)度，比如說(shuō)你這個(gè)字段就20多個(gè)字段你設(shè)置成VARCHAR(255)就是對(duì)磁盤空間的浪費(fèi)。

默認(rèn)值盡可能的使用 NOT NULL，可以通過(guò)設(shè)置默認(rèn)值避免NULL值。

盡量少的使用VARCHAR，TEXT，BLOB這三個(gè)字段。

添加適當(dāng)索引(index) [普通索引、主鍵索引、唯一索引unique、全文索引]。

不要濫用索引，大表索引，小表不索引。

表的設(shè)計(jì)合理化（符合3NF）。

二、SQL語(yǔ)句優(yōu)化

優(yōu)化利器 -- EXPLAIN

EXPLAIN SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'iacl_cmp' UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'iacl_cmp' UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'iacl_cmp';

運(yùn)行結(jié)果

主要關(guān)注4個(gè)指標(biāo)：

type：優(yōu)化目標(biāo)至少達(dá)到range級(jí)別，要求是ref級(jí)別，如果可以const最好。

type 性能從高到低

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

key：是查詢使用到的索引，如果此列為空，要么未建立索引，要么索引失效。

rows：是這條SQL語(yǔ)句掃描的行數(shù)，越少越好。

extra：此列為擴(kuò)展列，如果出現(xiàn)臨時(shí)表、文件排序則需要優(yōu)化。

where、= 條件避免類型轉(zhuǎn)換入?yún)㈩愋驮趥魅肭稗D(zhuǎn)換成跟字段類型一致內(nèi)關(guān)聯(lián)避免也要避免類型不一致做過(guò)濾

避免使用 select *

like引起索引失效，盡量滿足最左匹配原則，建議這樣寫：where display_name like '開(kāi)始%';

不要在where條件中帶有函數(shù)計(jì)算、類型轉(zhuǎn)換

關(guān)于in和exist，如果查詢的倆個(gè)表大小一致則性能差別可忽略，如果子查詢表大用exist，否則使用in

預(yù)知查詢一條數(shù)據(jù)要加上limit 1，以便查到數(shù)據(jù)終止掃描

關(guān)聯(lián)查詢盡量不要超過(guò) 3 個(gè)表關(guān)聯(lián)

數(shù)據(jù)字段更新頻繁、重復(fù)度高不適合建索引

explain中的type至少要達(dá)到range，建議為ref

盡量用join代替子查詢

盡量用union all代替union

數(shù)據(jù)優(yōu)化常用命令

查看Mysql服務(wù)器的鏈接

show?full?processlist;

查詢慢查詢次數(shù)

show status like 'slow_queries';

查看慢查詢?cè)O(shè)置的時(shí)間 ，默認(rèn)10s

show variables like 'long_query_time';

設(shè)置慢查詢時(shí)間、set long_query_time = 1;

查看建表語(yǔ)句

show create table wf_node;

查看表的字段

desc wf_node;

查看表索引

show indexes from wf_node;

創(chuàng)建索引主鍵索引

alter?table?wf_node?add?primary?key(id);

唯一索引

alter?table?wf_node?add?UNIQUE?(gmt_create);

普通索引

alter?table?wf_node?add?index?idx_display_name?(display_name);

復(fù)合索引

ALTER?TABLE?wf_node?ADD?INDEX?idx_display_name_gmt_create?(display_name, gmt_create);

刪除索引

drop?index?idx_display_name?on?wf_node;

重建索引

OPTIMIZE?TABLE?wf_node;

三、基礎(chǔ)設(shè)施優(yōu)化

硬件優(yōu)化

CPU：配置多核心和頻率高的cpu,多核心可以執(zhí)行多個(gè)線程

內(nèi)存：配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤I/O時(shí)間,從而提高響應(yīng)速度.

磁盤：配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.

數(shù)據(jù)庫(kù)本身參數(shù)配置

key_buffer_size：索引緩沖區(qū)大小

table_open_cache：指定表高速緩存的大小

query_cache_size和query_cache_type：前者是查詢緩沖區(qū)大小,后者是前面參數(shù)的開(kāi)關(guān),0表示不使用緩沖區(qū),1表示使用緩沖區(qū),但可以在查詢中使用SQL_NO_CACHE表示不要使用緩沖區(qū),2表示在查詢中明確指出使用緩沖區(qū)才用緩沖區(qū),即SQL_CACHE

sort_buffer_size：排序緩沖區(qū)

四、數(shù)據(jù)庫(kù)架構(gòu)優(yōu)化

增加緩存

給數(shù)據(jù)庫(kù)增加緩存系統(tǒng)，把熱數(shù)據(jù)緩存到內(nèi)存中，如果緩存中有請(qǐng)求的數(shù)據(jù)就不再去請(qǐng)求MySQL，減少數(shù)據(jù)庫(kù)負(fù)載。

主從復(fù)制與讀寫分離

在生產(chǎn)環(huán)境中，業(yè)務(wù)系統(tǒng)通常讀多寫少，可部署一主多從架構(gòu)，主數(shù)據(jù)庫(kù)負(fù)責(zé)寫操作，并做雙機(jī)熱備，多臺(tái)從數(shù)據(jù)庫(kù)做負(fù)載均衡，負(fù)責(zé)讀操作

分表

分表分為垂直拆分和水平拆分：垂直拆分：把原來(lái)的一個(gè)很多字段的表拆分多個(gè)表，解決表的寬度問(wèn)題。你可以把不常用的字段單獨(dú)放到一個(gè)表中，也可以把大字段獨(dú)立放一個(gè)表中，或者把關(guān)聯(lián)密切的字段放一個(gè)表中。

水平拆分：把原來(lái)一個(gè)表拆分成多個(gè)表，每個(gè)表的結(jié)構(gòu)都一樣，解決單表數(shù)據(jù)量大的問(wèn)題。

分區(qū)

分區(qū)就是把一張表的數(shù)據(jù)根據(jù)表結(jié)構(gòu)中的字段（如range、list、hash等）分成多個(gè)區(qū)塊，這些區(qū)塊可以在一個(gè)磁盤上，也可以在不同的磁盤上，分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列在多個(gè)位置，這樣一來(lái)，多塊硬盤同時(shí)處理不同的請(qǐng)求，從而提高磁盤I/O讀寫性能。

五、PDF文檔下載

有條友的同行在留言或者在私信中說(shuō)要pdf文檔，但頭條沒(méi)有發(fā)現(xiàn)哪里可以發(fā)附件，就發(fā)到百度網(wǎng)盤了，下面是聯(lián)系地址，有需要的下載就行了，大家記得關(guān)注我哦

鏈接：

https://pan.baidu.com/s/1gY9D2yX0Vyojnh2qOcNgWA

提取碼：a8jb

MySQL 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。