數(shù)據(jù)庫(kù)優(yōu)化那些事

我們出去面試經(jīng)常會(huì)被問(wèn)到數(shù)據(jù)庫(kù)這一塊，而涉及數(shù)據(jù)庫(kù)這一塊問(wèn)的最多的就是數(shù)據(jù)庫(kù)優(yōu)化。那么我們?cè)趺醋霾拍茏龊脙?yōu)化問(wèn)題呢？今天我們就來(lái)聊聊數(shù)據(jù)庫(kù)優(yōu)化那些事。

數(shù)據(jù)庫(kù)優(yōu)化我們一般可以從以下幾個(gè)方面考慮：

• 數(shù)據(jù)庫(kù)
• 表設(shè)計(jì)
• sql語(yǔ)句優(yōu)化

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

大型項(xiàng)目拆分為小項(xiàng)目，每個(gè)項(xiàng)目有自己獨(dú)立的數(shù)據(jù)庫(kù)

原來(lái)所有數(shù)據(jù)表都放在一個(gè)數(shù)據(jù)庫(kù)節(jié)點(diǎn)上，所有的讀寫請(qǐng)求也都發(fā)到這個(gè)MySQL上面，所以數(shù)據(jù)庫(kù)的負(fù)載太高。如果把一個(gè)節(jié)點(diǎn)的數(shù)據(jù)庫(kù)拆分成多個(gè)MySQL數(shù)據(jù)庫(kù)，這樣就可以有效的降低每個(gè)MySQL數(shù)據(jù)庫(kù)的負(fù)載。

分表分庫(kù)（取模分表、水平分表、垂直分表)

通過(guò)取模算法進(jìn)行水平分表，例如總共有3張表， 1%3=1 放入第一張表，2%3=2,放入低二張表，3%3=0 放入第0張表，相當(dāng)于用幾張表來(lái)平分一張表，最好的做法是一張主表，再來(lái)幾張字表。因?yàn)榉直聿缓米龇猪?yè)，數(shù)據(jù)存放在多張表中，所以需要一張主表用來(lái)存放所有的數(shù)據(jù)，當(dāng)子表無(wú)法滿足時(shí)采用主表。

讀寫分離

把對(duì)數(shù)據(jù)庫(kù)的讀寫操作分到不同的數(shù)據(jù)庫(kù)服務(wù)上，以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的高性能和高并發(fā)能力；讀寫分離最大的弊端就是復(fù)制延遲，對(duì)于以查詢?yōu)橹鞯捻?xiàng)目比較合適，看項(xiàng)目進(jìn)行取舍。

表設(shè)計(jì)

數(shù)據(jù)庫(kù)表設(shè)計(jì)遵循三范式

1. 第一范式：原子約束，每列不能再分

2. 第二范式：每張表只描述一件事情，就是主鍵就對(duì)應(yīng)著所有信息。
3. 第三范式：要保證表中的數(shù)據(jù)和主鍵直接相關(guān)，而不是間接相關(guān)。
   比如訂單表中出現(xiàn)了快遞的屬性（快遞單號(hào)，收件人姓名、收貨地址），可以把收件人姓名和收貨地址提出來(lái)單獨(dú)放到一張表，只留快遞單號(hào)在訂單表中作為關(guān)聯(lián)。并不是所有的表設(shè)計(jì)都必須按照三大范式設(shè)計(jì)，具體需要根據(jù)需求來(lái)定

表字段設(shè)計(jì)

盡量設(shè)計(jì)成not null，盡量使用數(shù)字型字段（如性別，男：1 女：2），若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷,字段長(zhǎng)度設(shè)計(jì)合理，比如郵編，只需要位長(zhǎng)度，沒(méi)必要過(guò)長(zhǎng),

用varchar/nvarchar 代替 char/nchar

因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時(shí)，空間就固定了， 不管是否插入值（NULL也包含在內(nèi)），都是占用 100個(gè)字符的空間的，如果是varchar這樣的變長(zhǎng)字段， null 不占用空間。

sql語(yǔ)句優(yōu)化

• 最大化使用索引，合理的創(chuàng)建索引情況下，最大化使用索引
• 減少數(shù)據(jù)訪問(wèn)：設(shè)置合理的字段類型
• 返回更少的數(shù)據(jù)：只返回需要的字段和數(shù)據(jù)分頁(yè)處理
• 減少交互次數(shù)：批量DML操作，函數(shù)存儲(chǔ)等減少數(shù)據(jù)連接次數(shù)
• 盡量減少數(shù)據(jù)庫(kù)排序操作以及全表查詢
  sql語(yǔ)句優(yōu)化的時(shí)候首先，我們需要定位有沒(méi)有慢查詢 慢查詢 ，顧名思義，執(zhí)行很慢的查詢。有多慢？超過(guò)long_query_time參數(shù)設(shè)定的時(shí)間閾值（默認(rèn)10s）

  慢查詢基本配置

• slow_query_log 啟動(dòng)停止技術(shù)慢查詢?nèi)罩?/li>
• slow_query_log_file 指定慢查詢?nèi)罩镜么鎯?chǔ)路徑及文件（默認(rèn)和數(shù)據(jù)文件放一起）
• long_query_time 指定記錄慢查詢?nèi)罩維QL執(zhí)行時(shí)間得伐值（單位：秒，默認(rèn)10秒）
• log_queries_not_using_indexes 是否記錄未使用索引的SQL
• log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

mysql5.7以上的版本，在my.ini中配置慢查詢配置

# 啟動(dòng)慢查詢?nèi)罩?/span>
slow_query_log = ON
# 慢查詢?nèi)罩敬娣诺胤?/span>
slow_query_log_file = D:softwaremysqlmysql-5.7.24-winx64datalogsshow.log
# 設(shè)置慢查詢時(shí)間，默認(rèn)10秒，我們此處設(shè)置為1秒，也就是超過(guò)1秒就是慢查詢
long_query_time = 1

通過(guò)下面命令查看下上面的配置：

• show VARIABLES like '%slow_query_log%'
• show VARIABLES like '%slow_query_log_file%'
• show VARIABLES like '%long_query_time%'
• show VARIABLES like '%log_queries_not_using_indexes%'
• show VARIABLES like 'log_output'
• set global long_query_time=1; -- 默認(rèn)10秒，這里為了演示方便設(shè)置為1
• set GLOBAL slow_query_log = 1; -- 開(kāi)啟慢查詢?nèi)罩?/li>
• set global log_output='FILE' -- 項(xiàng)目開(kāi)發(fā)中日志只能記錄在日志文件中，不能記表中

例如現(xiàn)在我執(zhí)行 select sleep(2) 沉睡兩秒，超過(guò)我設(shè)置慢查詢時(shí)間1秒，就會(huì)會(huì)被記錄到慢查詢?nèi)罩局腥缦?/p>

有查詢時(shí)間，以及慢查詢語(yǔ)句，當(dāng)我們知道慢查詢語(yǔ)句了我們就可以針對(duì)性優(yōu)化sql了。而針對(duì)sql的優(yōu)化最多的就是添加索引了

索引

索引用來(lái)快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹(shù)的形式保存。如果沒(méi)有索引，執(zhí)行查詢時(shí)MySQL必須從第一個(gè)記錄開(kāi)始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無(wú)需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。

索引原理 ：折半查找，減少全表掃描，索引文件中是一個(gè)B_tree ,索引的最大查找數(shù)為2^n次方，查找數(shù)獨(dú)比較快

我們可以使用explain分析SQL的執(zhí)行計(jì)劃。該執(zhí)行計(jì)劃可以模擬SQL優(yōu)化器執(zhí)行SQL語(yǔ)句，可以幫助我們了解到自己編寫SQL是否有用到索引。1）查看執(zhí)行計(jì)劃 語(yǔ)法：explain + SQL語(yǔ)句 eg：explain select * from tb;

id ：編號(hào)
select_type ：查詢類型
table ：表
type ：類型
possible_keys ：預(yù)測(cè)用到的索引
key ：實(shí)際使用的索引
key_len ：實(shí)際使用索引的長(zhǎng)度
ref ：表之間的引用
rows ：通過(guò)索引查詢到的數(shù)據(jù)量
Extra ：額外的信息

創(chuàng)建索引 create index index_name on table_name(colum_name...);

索引使用注意事項(xiàng)

1. 索引加在常使用字段上，主鍵除外，主鍵自帶唯一索引，例如商品表查詢名字和價(jià)格比較多的情況，就在這兩個(gè)字段上加索引
2. 盡量避免使用子查詢,用關(guān)聯(lián)查詢替代

select t1.name,(select sex from user u1 where u1.user_id = t1.user_id) as sex from userInfo t1 
改成
select t1.name,u1.sex from userInfo left join user u1 on u1.user_id = t1.user_id;

3. 用in來(lái)替換or ，且含In的范圍查詢，放到where條件的最后，防止索引失效。select * from ep_product where channel_id = 1 or channel_id = 2 改成 select * from ep_product where channel_id in(1,2)
4. 盡量不要使用or，否則索引失效
5. 不要使用like "%%",會(huì)全表掃描，但是可以使用 like "_%",不以%開(kāi)頭，會(huì)使用到索引進(jìn)行查找 使用"%%"

使用"_%"

6. 判斷是否為null不能用=，要用is null ，=null 不會(huì)使用索引，is null會(huì)使用索引

7. 避免數(shù)據(jù)類型不一致,如果設(shè)置的是int類型，條件最好是傳入int類型，如果傳入String類型數(shù)據(jù)庫(kù)會(huì)先做轉(zhuǎn)換在執(zhí)行sql

select * from product where id = "1"
select * from product where id = 1

8. 最好不要給數(shù)據(jù)庫(kù)留 NULL，盡可能的使用 NOT NULL 填充數(shù)據(jù)庫(kù),可以在 name 上設(shè)置默認(rèn)值 0，確保表中 name 列沒(méi)有 null 值，然后這樣查詢：

select id from t where name = 0

9. 分組的時(shí)候需要效率比較高就禁止使用排序、在group by 后面加上order by null

select channel_id,count(*) from ep_product  GROUP BY channel_id 
改成
select channel_id,count(*) from ep_product  GROUP BY channel_id order by null

10. 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

select * from product where sell_price/2 = 100
改成 
select * from product where sell_price = 100*2

11. 在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

• 復(fù)合索引，不要跨列或無(wú)序使用(左匹配原則)
• 復(fù)合索引，盡量使用全索引匹配，也就是說(shuō)，你建立幾個(gè)索引，就使用幾個(gè)索引

12. Update 語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來(lái)大量日志
13. 左關(guān)聯(lián)查詢，左表加索引，有關(guān)聯(lián)查詢，右表加索引
14. 索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

其他注意事項(xiàng)

1. 避免出現(xiàn)select *
2. 關(guān)聯(lián)查詢的表最好不超過(guò)3張，數(shù)據(jù)庫(kù)的性能更加重要，適當(dāng)考慮規(guī)范性就好
3. 多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后。
4. 新增多條數(shù)據(jù)是避免使用

insert into user values(1,"張三");
  insert into user values(2,"李四");
  使用以下方式可以減少語(yǔ)句解析的操作
  insert into user values(1,"張三"),(2,"李四");