MySql筆記量没，筆記分為四個(gè)部分：
1.MySQL架構(gòu)與SQL執(zhí)行流程
2.MySQL索引原理與使用原則
3.MySQL事務(wù)與鎖詳解
4.MySQL性能優(yōu)化總結(jié)
本節(jié)目標(biāo)：

掌握 MySQL 數(shù)據(jù)庫優(yōu)化的層次和思路
掌握 MySQL 數(shù)據(jù)庫優(yōu)化的工具缘眶。

一，優(yōu)化思路

作為架構(gòu)師或者開發(fā)人員绢彤，說到數(shù)據(jù)庫性能優(yōu)化，你的思路是什么樣的职辨？
或者具體一點(diǎn)铁孵，如果在面試的時(shí)候遇到這個(gè)問題：你會從哪些維度來優(yōu)化數(shù)據(jù)庫汽畴，你會怎么回答旧巾？
我們在第一章開始的時(shí)候講了，這章的目標(biāo)是為了讓大家建立數(shù)據(jù)庫的知識體系忍些，和正確的調(diào)優(yōu)的思路鲁猩。
我們說到性能調(diào)優(yōu)，大部分時(shí)候想要實(shí)現(xiàn)的目標(biāo)是讓我們的查詢更快罢坝。一個(gè)查詢的動作又是由很多個(gè)環(huán)節(jié)組成的廓握，每個(gè)環(huán)節(jié)都會消耗時(shí)間，我們在第一章講 SQL 語句的執(zhí)行流程的時(shí)候已經(jīng)分析過了炸客。
我們要減少查詢所消耗的時(shí)間疾棵，就要從每一個(gè)環(huán)節(jié)入手戈钢。

sql執(zhí)行流程.png

在項(xiàng)目里面痹仙，方會開啟事務(wù)，或者配置了事務(wù)殉了？無論是在方法上加注解开仰，還是配置切面。

一薪铜，連接——配置優(yōu)化

第一個(gè)環(huán)節(jié)是客戶端連接到服務(wù)端众弓，連接這一塊有可能會出現(xiàn)什么樣的性能問題？有可能是服務(wù)端連接數(shù)不夠?qū)е聭?yīng)用程序獲取不到連接隔箍。比如報(bào)了一個(gè) Mysql: error 1040: Too many connections 的錯(cuò)誤谓娃。
我們可以從兩個(gè)方面來解決連接數(shù)不夠的問題：
1、從服務(wù)端來說蜒滩，我們可以增加服務(wù)端的可用連接數(shù)滨达。
如果有多個(gè)應(yīng)用或者很多請求同時(shí)訪問數(shù)據(jù)庫奶稠，連接數(shù)不夠的時(shí)候，我們可以：
（1）修改配置參數(shù)增加可用連接數(shù)捡遍，修改 max_connections 的大行慷：

show variables like 'max_connections'; -- 修改最大連接數(shù)，當(dāng)有多個(gè)應(yīng)用連接的時(shí)候

（2）或者画株，或者及時(shí)釋放不活動的連接辆飘。交互式和非交互式的客戶端的默認(rèn)超時(shí)時(shí)間都是 28800 秒，8 小時(shí)谓传，我們可以把這個(gè)值調(diào)小蜈项。

show global variables like 'wait_timeout'; --及時(shí)釋放不活動的連接，注意不要釋放連接池還在使用的連接

2续挟、從客戶端來說战得，可以減少從服務(wù)端獲取的連接數(shù)，如果我們想要不是每一次執(zhí)行SQL 都創(chuàng)建一個(gè)新的連接庸推，應(yīng)該怎么做常侦？
這個(gè)時(shí)候我們可以引入連接池，實(shí)現(xiàn)連接的重用贬媒。
我們可以在哪些層面使用連接池聋亡？ORM 層面（MyBatis 自帶了一個(gè)連接池）；或者使用專用的連接池工具（阿里的 Druid际乘、Spring Boot 2.x 版本默認(rèn)的連接池 Hikari坡倔、老牌的 DBCP 和 C3P0）。
當(dāng)客戶端改成從連接池獲取連接之后脖含，連接池的大小應(yīng)該怎么設(shè)置呢罪塔？大家可能會有一個(gè)誤解，覺得連接池的最大連接數(shù)越大越好养葵，這樣在高并發(fā)的情況下客戶端可以獲取的連接數(shù)更多征堪，不需要排隊(duì)。
實(shí)際情況并不是這樣关拒。連接池并不是越大越好佃蚜，只要維護(hù)一定數(shù)量大小的連接池，其他的客戶端排隊(duì)等待獲取連接就可以了着绊。有的時(shí)候連接池越大谐算，效率反而越低。
Druid 的默認(rèn)最大連接池大小是 8归露。Hikari 的默認(rèn)最大連接池大小是 10洲脂。為什么默認(rèn)值都是這么小呢？
在 Hikari 的 github 文檔中剧包，給出了一個(gè) PostgreSQL 數(shù)據(jù)庫建議的設(shè)置連接池大小的公式：
https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
它的建議是機(jī)器核數(shù)乘以 2 加 1恐锦。也就是說雇毫，4 核的機(jī)器，連接池維護(hù) 9 個(gè)連接就夠了踩蔚。這個(gè)公式從一定程度上來說對其他數(shù)據(jù)庫也是適用的棚放。這里面還有一個(gè)減少連接池大小實(shí)現(xiàn)提升并發(fā)度和吞吐量的案例。
為什么有的情況下馅闽，減少連接數(shù)反而會提升吞吐量呢飘蚯？為什么建議設(shè)置的連接池大小要跟 CPU 的核數(shù)相關(guān)呢？
每一個(gè)連接福也，服務(wù)端都需要創(chuàng)建一個(gè)線程去處理它局骤。連接數(shù)越多，服務(wù)端創(chuàng)建的線程數(shù)就會越多暴凑。
問題：CPU 是怎么同時(shí)執(zhí)行遠(yuǎn)遠(yuǎn)超過它的核數(shù)大小的任務(wù)的峦甩？時(shí)間片。上下文切換现喳。
而 CPU 的核數(shù)是有限的凯傲，頻繁的上下文切換會造成比較大的性能開銷。
我們這里說到了從數(shù)據(jù)庫配置的層面去優(yōu)化數(shù)據(jù)庫嗦篱。不管是數(shù)據(jù)庫本身的配置冰单，還是安裝這個(gè)數(shù)據(jù)庫服務(wù)的操作系統(tǒng)的配置，對于配置進(jìn)行優(yōu)化灸促，最終的目標(biāo)都是為了更好地發(fā)揮硬件本身的性能诫欠，包括 CPU、內(nèi)存浴栽、磁盤荒叼、網(wǎng)絡(luò)。
在不同的硬件環(huán)境下典鸡，操作系統(tǒng)和 MySQL 的參數(shù)的配置是不同的被廓，沒有標(biāo)準(zhǔn)的配置。
在我們前面也接觸了很多的 MySQL 和 InnoDB 的配置參數(shù)椿每，包括各種開關(guān)和數(shù)值的配置伊者，大多數(shù)參數(shù)都提供了一個(gè)默認(rèn)值，比如默認(rèn)的 buffer_pool_size间护，默認(rèn)的頁大小，InnoDB 并發(fā)線程數(shù)等等挖诸。
這些默認(rèn)配置可以滿足大部分情況的需求汁尺，除非有特殊的需求，在清楚參數(shù)的含義的情況下再去修改它多律。修改配置的工作一般由專業(yè)的 DBA 完成痴突。
至于硬件本身的選擇搂蜓，比如使用固態(tài)硬盤，搭建磁盤陣列辽装，選擇特定的 CPU 型號這些帮碰，更不是我們開發(fā)人員關(guān)注的重點(diǎn)，這個(gè)我們就不做過多的介紹了拾积。
如果想要了解一些特定的參數(shù)的含義殉挽，官網(wǎng)有一份系統(tǒng)的參數(shù)列表可以參考：
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
除了合理設(shè)置服務(wù)端的連接數(shù)和客戶端的連接池大小之外，我們還有哪些減少客戶端跟數(shù)據(jù)庫服務(wù)端的連接數(shù)的方案呢拓巧？
我們可以引入緩存斯碌。

三，緩存——架構(gòu)優(yōu)化

1. 緩存

   在應(yīng)用系統(tǒng)的并發(fā)數(shù)非常大的情況下肛度，如果沒有緩存傻唾，會造成兩個(gè)問題：一方面是會給數(shù)據(jù)庫帶來很大的壓力。另一方面承耿，從應(yīng)用的層面來說冠骄，操作數(shù)據(jù)的速度也會受到影響。

   我們可以用第三方的緩存服務(wù)來解決這個(gè)問題加袋，例如 Redis猴抹。

緩存.png

運(yùn)行獨(dú)立的緩存服務(wù)，屬于架構(gòu)層面的優(yōu)化锁荔。
為了減少單臺數(shù)據(jù)庫服務(wù)器的讀寫壓力蟀给，在架構(gòu)層面我們還可以做其他哪些優(yōu)化措施？

2. 主從復(fù)制

   如果單臺數(shù)據(jù)庫服務(wù)滿足不了訪問需求阳堕，那我們可以做數(shù)據(jù)庫的集群方案跋理。
   集群的話必然會面臨一個(gè)問題，就是不同的節(jié)點(diǎn)之間數(shù)據(jù)一致性的問題恬总。如果同時(shí)讀寫多臺數(shù)據(jù)庫節(jié)點(diǎn)前普，怎么讓所有的節(jié)點(diǎn)數(shù)據(jù)保持一致？
   這個(gè)時(shí)候我們需要用到復(fù)制技術(shù)（replication）壹堰，被復(fù)制的節(jié)點(diǎn)稱為 master拭卿，復(fù)制的節(jié)點(diǎn)稱為 slave。slave 本身也可以作為其他節(jié)點(diǎn)的數(shù)據(jù)來源贱纠，這個(gè)叫做級聯(lián)復(fù)制峻厚。

主從復(fù)制.png

主從復(fù)制是怎么實(shí)現(xiàn)的呢？更新語句會記錄 binlog谆焊，它是一種邏輯日志惠桃。

有了這個(gè) binlog，從服務(wù)器會獲取主服務(wù)器的 binlog 文件，然后解析里面的 SQL語句辜王，在從服務(wù)器上面執(zhí)行一遍劈狐，保持主從的數(shù)據(jù)一致。

這里面涉及到三個(gè)線程呐馆，連接到 master 獲取 binlog肥缔，并且解析 binlog 寫入中繼日志，這個(gè)線程叫做 I/O 線程汹来。

Master 節(jié)點(diǎn)上有一個(gè) log dump 線程续膳，是用來發(fā)送 binlog 給 slave 的。

從庫的 SQL 線程俗慈，是用來讀取 relay log姑宽，把數(shù)據(jù)寫入到數(shù)據(jù)庫的。

master-slave.png

做了主從復(fù)制的方案之后闺阱，我們只把數(shù)據(jù)寫入 master 節(jié)點(diǎn)炮车，而讀的請求可以分擔(dān)到slave 節(jié)點(diǎn)。我們把這種方案叫做讀寫分離酣溃。

讀寫分離.png

讀寫分離可以一定程度低減輕數(shù)據(jù)庫服務(wù)器的訪問壓力瘦穆，但是需要特別注意主從數(shù)據(jù)一致性的問題。如果我們在 master 寫入了赊豌，馬上到 slave 查詢扛或，而這個(gè)時(shí)候 slave 的數(shù)據(jù)還沒有同步過來，怎么辦碘饼？

所以熙兔，基于主從復(fù)制的原理，我們需要弄明白艾恼，主從復(fù)制到底慢在哪里住涉？

• 單線程

  在早期的 MySQL 中，slave 的 SQL 線程是單線程钠绍。master 可以支持 SQL 語句的并行執(zhí)行舆声，配置了多少的最大連接數(shù)就是最多同時(shí)多少個(gè) SQL 并行執(zhí)行。

  而 slave 的 SQL 卻只能單線程排隊(duì)執(zhí)行柳爽，在主庫并發(fā)量很大的情況下媳握，同步數(shù)據(jù)肯定會出現(xiàn)延遲。

  為什么從庫上的 SQL Thread 不能并行執(zhí)行呢磷脯？舉個(gè)例子蛾找，主庫執(zhí)行了多條 SQL 語句，首先用戶發(fā)表了一條評論争拐，然后修改了內(nèi)容腋粥，最后把這條評論刪除了晦雨。這三條語句在從庫上的執(zhí)行順序肯定是不能顛倒的架曹。

  insert into user_comments (10000009,'nice');
  update user_comments set content ='very good' where id =10000009;
  delete from user_comments where id =10000009;
  

  怎么解決這個(gè)問題呢隘冲？怎么減少主從復(fù)制的延遲？

• 異步與全同步

  首先我們需要知道绑雄，在主從復(fù)制的過程中展辞，MySQL 默認(rèn)是異步復(fù)制的。也就是說万牺，對于主節(jié)點(diǎn)來說罗珍，寫入 binlog，事務(wù)結(jié)束脚粟，就返回給客戶端了覆旱。對于 slave 來說，接收到 binlog核无，就完事兒了扣唱，master 不關(guān)心 slave 的數(shù)據(jù)有沒有寫入成功。

全同步復(fù)制.png

如果要減少延遲团南，是不是可以等待全部從庫的事務(wù)執(zhí)行完畢噪沙，才返回給客戶端呢？這樣的方式叫做全同步復(fù)制吐根。從庫寫完數(shù)據(jù)正歼，主庫才返會給客戶端。

這種方式雖然可以保證在讀之前拷橘，數(shù)據(jù)已經(jīng)同步成功了局义，但是帶來的副作用大家應(yīng)該能想到，事務(wù)執(zhí)行的時(shí)間會變長冗疮，它會導(dǎo)致 master 節(jié)點(diǎn)性能下降萄唇。

有沒有更好的辦法呢？既減少 slave 寫入的延遲赌厅，又不會明顯增加 master 返回給客戶端的時(shí)間穷绵？

• 半同步復(fù)制

  介于異步復(fù)制和全同步復(fù)制之間，還有一種半同步復(fù)制的方式特愿。

  半同步復(fù)制是什么樣的呢仲墨？

  主庫在執(zhí)行完客戶端提交的事務(wù)后不是立刻返回給客戶端，而是等待至少一個(gè)從庫接收到 binlog 并寫到 relay log 中才返回給客戶端揍障。master 不會等待很長的時(shí)間目养，但是返回給客戶端的時(shí)候，數(shù)據(jù)就即將寫入成功了毒嫡，因?yàn)樗皇Ｗ詈笠徊搅耍壕褪亲x取 relay log癌蚁，寫入從庫。

半同步復(fù)制.png

如果我們要在數(shù)據(jù)庫里面用半同步復(fù)制，必須安裝一個(gè)插件努释，這個(gè)是谷歌的一位工程師貢獻(xiàn)的碘梢。這個(gè)插件在 mysql 的插件目錄下已經(jīng)有提供：

  cd /usr/lib64/mysql/plugin/

主庫和從庫是不同的插件，安裝之后需要啟用：

   -- 主庫執(zhí)行
   INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
   set global rpl_semi_sync_master_enabled=1;
   show variables like '%semi_sync%'; 
   -- 從庫執(zhí)行
   INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
   set global rpl_semi_sync_slave_enabled=1;
   show global variables like '%semi%';

相對于異步復(fù)制伐蒂，半同步復(fù)制提高了數(shù)據(jù)的安全性煞躬，同時(shí)它也造成了一定程度的延遲，它需要等待一個(gè) slave 寫入中繼日志逸邦，這里多了一個(gè)網(wǎng)絡(luò)交互的過程恩沛，所以，半同步復(fù)制最好在低延時(shí)的網(wǎng)絡(luò)中使用缕减。

這個(gè)是從主庫和從庫連接的角度雷客，來保證 slave 數(shù)據(jù)的寫入。

另一個(gè)思路桥狡，如果要減少主從同步的延遲搅裙，減少 SQL 執(zhí)行造成的等待的時(shí)間，那有沒有辦法在從庫上总放，讓多個(gè) SQL 語句可以并行執(zhí)行滤港，而不是排隊(duì)執(zhí)行呢品山？

• 多庫并行復(fù)制

怎么實(shí)現(xiàn)并行復(fù)制呢味抖？設(shè)想一下一疯，如果 3 條語句是在三個(gè)數(shù)據(jù)庫執(zhí)行，操作各自的數(shù)據(jù)庫炬搭，是不是肯定不會產(chǎn)生并發(fā)的問題呢蜈漓？執(zhí)行的順序也沒有要求。當(dāng)然是宫盔，所以如果是操作三個(gè)數(shù)據(jù)庫融虽，這三個(gè)數(shù)據(jù)庫的從庫的 SQL 線程可以并發(fā)執(zhí)行。這是 MySQL 5.6版本里面支持的多庫并行復(fù)制灼芭。

多庫并行復(fù)制.png

但是在大部分的情況下有额，我們都是單庫多表的情況，在一個(gè)數(shù)據(jù)庫里面怎么實(shí)現(xiàn)并行復(fù)制呢彼绷？或者說巍佑，我們知道，數(shù)據(jù)庫本身就是支持多個(gè)事務(wù)同時(shí)操作的寄悯；為什么這些事務(wù)在主庫上面可以并行執(zhí)行萤衰，卻不會出現(xiàn)問題呢？

因?yàn)樗麄儽旧砭褪腔ハ嗖桓蓴_的猜旬，比如這些事務(wù)是操作不同的表脆栋，或者操作不同的行倦卖，不存在資源的競爭和數(shù)據(jù)的干擾。那在主庫上并行執(zhí)行的事務(wù)椿争，在從庫上肯定也是可以并行執(zhí)行怕膛，是不是？比如在 master 上有三個(gè)事務(wù)同時(shí)分別操作三張表丘薛，這三個(gè)事務(wù)是不是在 slave 上面也可以并行執(zhí)行呢嘉竟？

• 異步復(fù)制之 GTID 復(fù)制
  https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html
  所以邦危，我們可以把那些在主庫上并行執(zhí)行的事務(wù)洋侨，分為一個(gè)組，并且給他們編號倦蚪，這一個(gè)組的事務(wù)在從庫上面也可以并行執(zhí)行希坚。這個(gè)編號，我們把它叫做 GTID（Global Transaction Identifiers）陵且，這種主從復(fù)制的方式裁僧，我們把它叫做基于 GTID 的復(fù)制。

GTID復(fù)制.png

如果我們要使用 GTID 復(fù)制慕购，我們可以通過修改配置參數(shù)打開它聊疲，默認(rèn)是關(guān)閉的：

    show global variables like 'gtid_mode';

無論是優(yōu)化 master 和 slave 的連接方式，還是讓從庫可以并行執(zhí)行 SQL沪悲，都是從數(shù)據(jù)庫的層面去解決主從復(fù)制延遲的問題获洲。

除了數(shù)據(jù)庫本身的層面之外，在應(yīng)用層面殿如，我們也有一些減少主從同步延遲的方法贡珊。

我們在做了主從復(fù)制之后，如果單個(gè) master 節(jié)點(diǎn)或者單張表存儲的數(shù)據(jù)過大的時(shí)候涉馁，比如一張表有上億的數(shù)據(jù)门岔，單表的查詢性能還是會下降，我們要進(jìn)一步對單臺數(shù)據(jù)庫節(jié)點(diǎn)的數(shù)據(jù)分型拆分烤送，這個(gè)就是分庫分表寒随。

3. 分庫分表

   垂直分庫，減少并發(fā)壓力帮坚。水平分表妻往，解決存儲瓶頸。
   垂直分庫的做法叶沛，把一個(gè)數(shù)據(jù)庫按照業(yè)務(wù)拆分成不同的數(shù)據(jù)庫：

垂直分庫.png

水平分庫分表的做法蒲讯，把單張表的數(shù)據(jù)按照一定的規(guī)則分布到多個(gè)數(shù)據(jù)庫。

水平分庫分表.png

通過主從或者分庫分表可以減少單個(gè)數(shù)據(jù)庫節(jié)點(diǎn)的訪問壓力和存儲壓力灰署，達(dá)到提升數(shù)據(jù)庫性能的目的判帮，但是如果 master 節(jié)點(diǎn)掛了局嘁，怎么辦？
所以晦墙，高可用（High Available）也是高性能的基礎(chǔ)悦昵。

4. 高可用方案
   https://dev.mysql.com/doc/mysql-ha-scalability/en/ha-overview.html

   • 主從復(fù)制

     傳統(tǒng)的 HAProxy + keepalived 的方案，基于主從復(fù)制晌畅。

   • NDB Cluster

     https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-overview.html
     基于 NDB 集群存儲引擎的 MySQL Cluster但指。

   cluster-components-1.png

• Galera

  https://galeracluster.com/

  一種多主同步復(fù)制的集群方案。

Galera.png

• MHA/MMM

  https://tech.meituan.com/2017/06/29/database-availability-architecture.html
  MMM（Master-Master replication manager for MySQL）抗楔，一種多主的高可用架構(gòu)棋凳，是一個(gè)日本人開發(fā)的，像美團(tuán)這樣的公司早期也有大量使用 MMM连躏。
  MHA（MySQL Master High Available）剩岳。
  MMM 和 MHA 都是對外提供一個(gè)虛擬 IP，并且監(jiān)控主節(jié)點(diǎn)和從節(jié)點(diǎn)入热，當(dāng)主節(jié)點(diǎn)發(fā)生故障的時(shí)候拍棕，需要把一個(gè)從節(jié)點(diǎn)提升為主節(jié)點(diǎn)，并且把從節(jié)點(diǎn)里面比主節(jié)點(diǎn)缺少的數(shù)據(jù)補(bǔ)上勺良，把 VIP 指向新的主節(jié)點(diǎn)绰播。

• MGR

  https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
  https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html
  MySQL 5.7.17 版本推出的 InnoDB Cluster，也叫 MySQL Group Replicatioin（MGR）尚困，這個(gè)套件里面包括了 mysql shell 和 mysql-route蠢箩。

  MGR.png

總結(jié)一下：

高可用 HA 方案需要解決的問題都是當(dāng)一個(gè) master 節(jié)點(diǎn)宕機(jī)的時(shí)候，如何提升一個(gè)數(shù)據(jù)最新的 slave 成為 master尾组。如果同時(shí)運(yùn)行多個(gè) master忙芒，又必須要解決 master 之間數(shù)據(jù)復(fù)制，以及對于客戶端來說連接路由的問題讳侨。
不同的方案呵萨，實(shí)施難度不一樣，運(yùn)維管理的成本也不一樣跨跨。
以上是架構(gòu)層面的優(yōu)化潮峦，可以用緩存，主從勇婴，分庫分表忱嘹。

第三個(gè)環(huán)節(jié)：
解析器，詞法和語法分析耕渴，主要保證語句的正確性拘悦，語句不出錯(cuò)就沒問題。由 Sever自己處理橱脸，跳過础米。

第四步：優(yōu)化器

四分苇，優(yōu)化器——SQL 語句分析與優(yōu)化

優(yōu)化器就是對我們的 SQL 語句進(jìn)行分析，生成執(zhí)行計(jì)劃屁桑。
問題：在我們做項(xiàng)目的時(shí)候医寿，有時(shí)會收到 DBA 的郵件，里面列出了我們項(xiàng)目上幾個(gè)耗時(shí)比較長的查詢語句蘑斧，讓我們?nèi)?yōu)化靖秩，這些語句是從哪里來的呢？
我們的服務(wù)層每天執(zhí)行了這么多 SQL 語句竖瘾，它怎么知道哪些 SQL 語句比較慢呢沟突？
第一步，我們要把 SQL 執(zhí)行情況記錄下來准浴。

1. 慢查詢?nèi)罩?slow query log

   https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

• 打開慢日志開關(guān)

因?yàn)殚_啟慢查詢?nèi)罩臼怯写鷥r(jià)的（跟 bin log事扭、optimizer-trace 一樣），所以它默認(rèn)是關(guān)閉的：

    show variables like 'slow_query%;

v1.png

除了這個(gè)開關(guān)乐横，還有一個(gè)參數(shù)，控制執(zhí)行超過多長時(shí)間的 SQL 才記錄到慢日志今野，默認(rèn)是 10 秒葡公。

    show variables like '%slow_query%';

可以直接動態(tài)修改參數(shù)（重啟后失效）。

     set @@global.slow_query_log=1; -- 1 開啟条霜，0 關(guān)閉催什，重啟后失效
     set @@global.long_query_time=3; -- mysql 默認(rèn)的慢查詢時(shí)間是 10 秒，另開一個(gè)窗口后才會查到最新值
     show variables like '%long_query%';
     show variables like '%slow_query%;

或者修改配置文件 my.cnf宰睡。
以下配置定義了慢查詢?nèi)罩镜拈_關(guān)蒲凶、慢查詢的時(shí)間、日志文件的存放路徑拆内。

     slow_query_log = ON
     long_query_time=2
     slow_query_log_file =/var/lib/mysql/localhost-slow.log

模擬慢查詢：

     select sleep(10);

查詢 user_innodb 表的 500 萬數(shù)據(jù)（檢查是不是沒有索引）旋圆。

     SELECT * FROM `user_innodb` where phone = '136';

• 慢日志分析

  1. 日志內(nèi)容

        show global status like 'slow_queries'; -- 查看有多少慢查詢
        show variables like '%slow_query%'; -- 獲取慢日志目錄

        cat /var/lib/mysql/ localhost-slow.log

log1.png

有了慢查詢?nèi)罩荆趺慈シ治鼋y(tǒng)計(jì)呢麸恍？比如 SQL 語句的出現(xiàn)的慢查詢次數(shù)最多灵巧，平均每次執(zhí)行了多久？

2. mysqldumpslow
   https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
   MySQL 提供了 mysqldumpslow 的工具抹沪，在 MySQL 的 bin 目錄下刻肄。

        mysqldumpslow --help

例如：查詢用時(shí)最多的 20 條慢 SQL：

      mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-

log2.png

Count 代表這個(gè) SQL 執(zhí)行了多少次；
Time 代表執(zhí)行的時(shí)間融欧，括號里面是累計(jì)時(shí)間敏弃；
Lock 表示鎖定的時(shí)間，括號是累計(jì)噪馏；
Rows 表示返回的記錄數(shù)麦到，括號是累計(jì)虹茶。

除了慢查詢?nèi)罩局猓€有一個(gè) SHOW PROFILE 工具可以使用.

2. SHOW PROFILE

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
SHOW PROFILE 是谷歌高級架構(gòu)師 Jeremy Cole 貢獻(xiàn)給 MySQL 社區(qū)的隅要，可以查看SQL 語句執(zhí)行的時(shí)候使用的資源蝴罪，比如 CPU、IO 的消耗情況步清。
在 SQL 中輸入 help profile 可以得到詳細(xì)的幫助信息.

• 查看是否開啟

     select @@profiling;
     set @@profiling=1;

• 查看 profile 統(tǒng)計(jì)

  （命令最后帶一個(gè) s）

     show profiles;

v2.png

查看最后一個(gè) SQL 的執(zhí)行詳細(xì)信息要门，從中找出耗時(shí)較多的環(huán)節(jié)（沒有 s）

     show profile;

v3.png

6.2E-5，小數(shù)點(diǎn)左移 5 位廓啊，代表 0.000062 秒欢搜。

也可以根據(jù) ID 查看執(zhí)行詳細(xì)信息，在后面帶上 for query + ID.

     show profile for query 1;

除了慢日志和 show profile谴轮，如果要分析出當(dāng)前數(shù)據(jù)庫中執(zhí)行的慢的 SQL炒瘟，還可以通過查看運(yùn)行線程狀態(tài)和服務(wù)器運(yùn)行信息、存儲引擎信息來分析第步。

• 其他系統(tǒng)命令

show processlist 運(yùn)行線程

https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

     show processlist;

這是很重要的一個(gè)命令疮装，用于顯示用戶運(yùn)行線程≌扯迹可以根據(jù) id 號 kill 線程廓推。
也可以查表，效果一樣：

     select * from information_schema.processlist;

v4.png

show status 服務(wù)器運(yùn)行狀態(tài)

https://dev.mysql.com/doc/refman/5.7/en/show-status.html
SHOW STATUS 用于查看 MySQL 服務(wù)器運(yùn)行狀態(tài)（重啟后會清空），有 session和 global 兩種作用域糯景，格式：參數(shù)-值嘁圈。
可以用 like 帶通配符過濾。

     SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次數(shù)

show engine 存儲引擎運(yùn)行信息

https://dev.mysql.com/doc/refman/5.7/en/show-engine.html
show engine 用來顯示存儲引擎的當(dāng)前運(yùn)行信息蟀淮，包括事務(wù)持有的表鎖最住、行鎖信息；事務(wù)的鎖等待情況怠惶；線程信號量等待涨缚；文件 IO 請求；buffer pool 統(tǒng)計(jì)信息。
例如：

     show engine innodb status;

如果需要將監(jiān)控信息輸出到錯(cuò)誤信息 error log 中（15 秒鐘一次）脓魏，可以開啟輸出兰吟。

     show variables like 'innodb_status_output%'; -- 開啟輸出：
     SET GLOBAL innodb_status_output=ON;
     SET GLOBAL innodb_status_output_locks=ON;

我們現(xiàn)在已經(jīng)知道了這么多分析服務(wù)器狀態(tài)、存儲引擎狀態(tài)茂翔、線程運(yùn)行信息的命令混蔼，如果讓你去寫一個(gè)數(shù)據(jù)庫監(jiān)控系統(tǒng)，你會怎么做珊燎？
其實(shí)很多開源的慢查詢?nèi)罩颈O(jiān)控工具惭嚣，他們的原理其實(shí)也都是讀取的系統(tǒng)的變量和狀態(tài)。

現(xiàn)在我們已經(jīng)知道哪些 SQL 慢了悔政，為什么慢呢晚吞？慢在哪里?

MySQL 提供了一個(gè)執(zhí)行計(jì)劃的工具（在架構(gòu)中我們有講到，優(yōu)化器最終生成的就是一個(gè)執(zhí)行計(jì)劃）谋国，其他數(shù)據(jù)庫槽地，例如 Oracle 也有類似的功能。
通過 EXPLAIN 我們可以模擬優(yōu)化器執(zhí)行 SQL 查詢語句的過程芦瘾，來知道 MySQL 是怎么處理一條 SQL 語句的捌蚊。通過這種方式我們可以分析語句或者表的性能瓶頸。
explain 可以分析 update旅急、delete逢勾、insert 么？
MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update藐吮、delete、insert 了.

3. EXPLAIN 執(zhí)行計(jì)劃

官方鏈接：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
我們先創(chuàng)建三張表逃贝。一張課程表谣辞，一張老師表，一張老師聯(lián)系方式表（沒有任何索引）沐扳。

   DROP TABLE IF EXISTS course;
   CREATE TABLE `course` (
   `cid` int(3) DEFAULT NULL, `cname` varchar(20) DEFAULT NULL, `tid` int(3) DEFAULT NUL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   DROP TABLE IF EXISTS teacher;
   CREATE TABLE `teacher` (
   `tid` int(3) DEFAULT NULL, `tname` varchar(20) DEFAULT NULL, `tcid` int(3) DEFAULT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   DROP TABLE IF EXISTS teacher_contact;
   CREATE TABLE `teacher_contact` (
   `tcid` int(3) DEFAULT NULL, `phone` varchar(200) DEFAULT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   INSERT INTO `course` VALUES ('1', 'mysql', '1');
   INSERT INTO `course` VALUES ('2', 'jvm', '1');
   INSERT INTO `course` VALUES ('3', 'juc', '2');
   INSERT INTO `course` VALUES ('4', 'spring', '3');
   INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');
   INSERT INTO `teacher` VALUES ('2', 'jack', '2');
   INSERT INTO `teacher` VALUES ('3', 'mic', '3');
   INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
   INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
   INSERT INTO `teacher_contact` VALUES ('3', '17722225555')

explain 的結(jié)果有很多的字段泥从，我們詳細(xì)地分析一下。
先確認(rèn)一下環(huán)境：

   select version();
   show variables like '%engine%';

• id

id 是查詢序列編號

id 值不同

id 值不同的時(shí)候沪摄，先查詢 id 值大的（先大后星怠）

     -- 查詢 mysql 課程的老師手機(jī)號
     EXPLAIN SELECT tc.phone
     FROM teacher_contact tc
     WHERE tcid = (
     SELECT tcid
     FROM teacher t
     WHERE t.tid = (
     SELECT c.tid
     FROM course c
     WHERE c.cname = 'mysql' )
     );

查詢順序：course c——teacher t——teacher_contact t.

v5.png

先查課程表，再查老師表杨拐，最后查老師聯(lián)系方式表祈餐。子查詢只能以這種方式進(jìn)行，只有拿到內(nèi)層的結(jié)果之后才能進(jìn)行外層的查詢哄陶。

id 值相同

     -- 查詢課程 ID 為 2帆阳，或者聯(lián)系表 ID 為 3 的老師
     EXPLAIN
     SELECT t.tname,c.cname,tc.phone
     FROM teacher t, course c, teacher_contact tc
     WHERE t.tid = c.tid
     AND t.tcid = tc.tcid
     AND (c.cid = 2
     OR tc.tcid = 3);
     

v6.png

id 值相同時(shí)，表的查詢順序是從上往下順序執(zhí)行屋吨。例如這次查詢的 id 都是 1蜒谤，查詢的順序是 teacher t（3 條）——course c（4 條）——teacher_contact tc（3 條）山宾。
teacher 表插入 3 條數(shù)據(jù)后：

     INSERT INTO `teacher` VALUES (4, 'james', 4);
     INSERT INTO `teacher` VALUES (5, 'tom', 5);
     INSERT INTO `teacher` VALUES (6, 'seven', 6);
     COMMIT; -- （備份）恢復(fù)語句
     DELETE FROM teacher where tid in (4,5,6);
     COMMIT;

id 也都是 1，但是從上往下查詢順序變成了：teacher_contact tc（3 條）——teacher t（6 條）——course c（4 條）鳍徽。

v7.png

為什么數(shù)據(jù)量不同的時(shí)候順序會發(fā)生變化呢资锰？這個(gè)是由笛卡爾積決定的。

舉例：假如有 a阶祭、b绷杜、c 三張表，分別有 2胖翰、3接剩、4 條數(shù)據(jù)，如果做三張表的聯(lián)合查詢萨咳，當(dāng)查詢順序是 a→b→c 的時(shí)候懊缺，它的笛卡爾積是：234=64=24。如果查詢順序是 c→b→a培他，它的笛卡爾積是 432=122=24鹃两。

因?yàn)?MySQL 要把查詢的結(jié)果，包括中間結(jié)果和最終結(jié)果都保存到內(nèi)存舀凛，所以 MySQL會優(yōu)先選擇中間結(jié)果數(shù)據(jù)量比較小的順序進(jìn)行查詢俊扳。所以最終聯(lián)表查詢的順序是 a→b→c。這個(gè)就是為什么 teacher 表插入數(shù)據(jù)以后查詢順序會發(fā)生變化猛遍。
（小標(biāo)驅(qū)動大表的思想）

既有相同也有不同

如果 ID 有相同也有不同馋记，就是 ID 不同的先大后小，ID 相同的從上往下懊烤。

• select type 查詢類型

  這里并沒有列舉全部（其它：DEPENDENT UNION梯醒、DEPENDENT SUBQUERY、MATERIALIZED腌紧、UNCACHEABLE SUBQUERY茸习、UNCACHEABLE UNION）。
  下面列舉了一些常見的查詢類型：

  SIMPLE

簡單查詢壁肋，不包含子查詢号胚，不包含關(guān)聯(lián)查詢 union。

     EXPLAIN SELECT * FROM teacher;

v8.png

再看一個(gè)包含子查詢的案例：

     -- 查詢 mysql 課程的老師手機(jī)號
     EXPLAIN SELECT tc.phone
     FROM teacher_contact tc
     WHERE tcid = (
     SELECT tcid
     FROM teacher t
     WHERE t.tid = (
     SELECT c.tid
     FROM course c
     WHERE c.cname = 'mysql' )
     );

v9.png

PRIMARY

子查詢 SQL 語句中的主查詢浸遗，也就是最外面的那層查詢猫胁。

SUBQUERY

子查詢中所有的內(nèi)層查詢都是 SUBQUERY 類型的。

DERIVED

衍生查詢乙帮，表示在得到最終查詢結(jié)果之前會用到臨時(shí)表杜漠。例如：

     -- 查詢 ID 為 1 或 2 的老師教授的課程
     EXPLAIN SELECT cr.cname
     FROM (
     SELECT * FROM course WHERE tid = 1
     UNION
     SELECT * FROM course WHERE tid = 2
     ) cr;

v10.png

對于關(guān)聯(lián)查詢，先執(zhí)行右邊的 table（UNION），再執(zhí)行左邊的 table驾茴，類型是DERIVED盼樟。

UNION

用到了 UNION 查詢。同上例锈至。

UNION RESULT

主要是顯示哪些表之間存在 UNION 查詢晨缴。代表 id=2 和 id=3 的查詢存在 UNION。同上例峡捡。

• type 連接類型

  https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
  所有的連接類型中击碗，上面的最好，越往下越差们拙。
  在常用的鏈接類型中：system > const > eq_ref > ref > range > index > all
  這 里 并 沒 有 列 舉 全 部 （ 其 他 ： fulltext 稍途、 ref_or_null 、 index_merger 砚婆、unique_subquery械拍、index_subquery）。
  以上訪問類型除了 all装盯，都能用到索引坷虑。
  const

  主鍵索引或者唯一索引，只能查到一條數(shù)據(jù)的 SQL埂奈。

     DROP TABLE IF EXISTS single_data;
     CREATE TABLE single_data(
     id int(3) PRIMARY KEY, content varchar(20)
     );
     insert into single_data values(1,'a');
     EXPLAIN SELECT * FROM single_data a where id = 1;
     

v11.png

system
system 是 const 的一種特例迄损，只有一行滿足條件。例如：只有一條數(shù)據(jù)的系統(tǒng)表账磺。

     EXPLAIN SELECT * FROM mysql.proxies_priv;

v12.png

eq_ref

通常出現(xiàn)在多表的 join 查詢芹敌，表示對于前表的每一個(gè)結(jié)果,都只能匹配到后表的一行結(jié)果。一般是唯一性索引的查詢（UNIQUE 或 PRIMARY KEY）垮抗。
eq_ref 是除 const 之外最好的訪問類型党窜。
先刪除 teacher 表中多余的數(shù)據(jù)，teacher_contact 有 3 條數(shù)據(jù)借宵，teacher 表有 3條數(shù)據(jù)。

     DELETE FROM teacher where tid in (4,5,6);
     commit; -- 備份
     INSERT INTO `teacher` VALUES (4, 'james', 4);
     INSERT INTO `teacher` VALUES (5, 'tom', 5);
     INSERT INTO `teacher` VALUES (6, 'seven', 6);
     commit;

為 teacher_contact 表的 tcid（第一個(gè)字段）創(chuàng)建主鍵索.

     -- ALTER TABLE teacher_contact DROP PRIMARY KEY;
     ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

為 teacher 表的 tcid（第三個(gè)字段）創(chuàng)建普通索引矾削。

     -- ALTER TABLE teacher DROP INDEX idx_tcid;
     ALTER TABLE teacher ADD INDEX idx_tcid (tcid

執(zhí)行以下 SQL 語句:

     select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

v13.png

此時(shí)的執(zhí)行計(jì)劃（teacher_contact 表是 eq_ref）：

v14.png

小結(jié)：

以上三種 system壤玫，const，eq_ref哼凯，都是可遇而不可求的欲间，基本上很難優(yōu)化到這個(gè)狀態(tài)。

ref

查詢用到了非唯一性索引断部，或者關(guān)聯(lián)操作只使用了索引的最左前綴猎贴。
例如：使用 tcid 上的普通索引查詢：

     explain SELECT * FROM teacher where tcid = 3;

v15.png

range

索引范圍掃描。
如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 這些，type 類型就為 range她渴。
不走索引一定是全表掃描（ALL）达址，所以先加上普通索引。

     -- ALTER TABLE teacher DROP INDEX idx_tid;
     ALTER TABLE teacher ADD INDEX idx_tid (tid);

執(zhí)行范圍查詢（字段上有普通索引）：

     EXPLAIN SELECT * FROM teacher t WHERE t.tid <3; -- 或
     EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;

v16.png

IN 查詢也是 range（字段有主鍵索引)

     EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);

v17.png

index

Full Index Scan趁耗，查詢?nèi)克饕械臄?shù)據(jù)（比不走索引要快）沉唠。

     EXPLAIN SELECT tid FROM teacher;

v18.png

all

Full Table Scan，如果沒有索引或者沒有用到索引苛败，type 就是 ALL满葛。代表全表掃描。

NULL

不用訪問表或者索引就能得到結(jié)果罢屈，例如：

     EXPLAIN select 1 from dual where 1=1;

小結(jié)：
一般來說嘀韧，需要保證查詢至少達(dá)到 range 級別，最好能達(dá)到 ref缠捌。
ALL（全表掃描）和 index（查詢?nèi)克饕┒际切枰獌?yōu)化的锄贷。

• possible_key、key

可能用到的索引和實(shí)際用到的索引鄙币。如果是 NULL 就代表沒有用到索引肃叶。
possible_key 可以有一個(gè)或者多個(gè)，可能用到索引不代表一定用到索引十嘿。
反過來因惭，possible_key 為空，key 可能有值嗎绩衷？
表上創(chuàng)建聯(lián)合索引：

     ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
     ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

執(zhí)行計(jì)劃（改成 select name 也能用到索引）:

     explain select phone from user_innodb where phone='126';

v9.png

結(jié)論：是有可能的（這里是覆蓋索引的情況）蹦魔。
如果通過分析發(fā)現(xiàn)沒有用到索引，就要檢查 SQL 或者創(chuàng)建索引.

• key_len

  索引的長度（使用的字節(jié)數(shù)）咳燕。跟索引字段的類型勿决、長度有關(guān)。

• rows

  MySQL 認(rèn)為掃描多少行才能返回請求的數(shù)據(jù)招盲，是一個(gè)預(yù)估值低缩。一般來說行數(shù)越少越好。

• filtered

  這個(gè)字段表示存儲引擎返回的數(shù)據(jù)在 server 層過濾后曹货，剩下多少滿足查詢的記錄數(shù)量的比例咆繁，它是一個(gè)百分比。

• ref

  使用哪個(gè)列或者常數(shù)和索引一起從表中篩選數(shù)據(jù)顶籽。

• Extra

  執(zhí)行計(jì)劃給出的額外的信息說明玩般。

using index

用到了覆蓋索引，不需要回表礼饱。

EXPLAIN SELECT tid FROM teacher ;

using where

使用了 where 過濾坏为，表示存儲引擎返回的記錄并不是所有的都滿足查詢條件究驴，需要在 server 層進(jìn)行過濾（跟是否使用索引沒有關(guān)系）。

EXPLAIN select * from user_innodb where phone ='13866667777';

v20.png

Using index condition（索引條件下推）

索引下推匀伏，在第二章中已經(jīng)講解過了洒忧。
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.htm

using filesort

不能使用索引來排序，用到了額外的排序（跟磁盤或文件沒有關(guān)系）帘撰。需要優(yōu)化跑慕。
（復(fù)合索引的前提）

   ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
   ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
   EXPLAIN select * from user_innodb where name='青山' order by id;

（order by id 引起）

v21.png

using temporary

用到了臨時(shí)表。例如（以下不是全部的情況）：
1摧找、distinct 非索引列

   EXPLAIN select DISTINCT(tid) from teacher t;

2核行、group by 非索引列

   EXPLAIN select tname from teacher group by tname;

3、使用 join 的時(shí)候蹬耘，group 任意列

   EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;

需要優(yōu)化芝雪，例如創(chuàng)建復(fù)合索引。

總結(jié)一下：
模擬優(yōu)化器執(zhí)行 SQL 查詢語句的過程综苔，來知道 MySQL 是怎么處理一條 SQL 語句的惩系。
通過這種方式我們可以分析語句或者表的性能瓶頸。
分析出問題之后如筛，就是對 SQL 語句的具體優(yōu)化堡牡。
比如怎么用到索引，怎么減少鎖的阻塞等待杨刨，在前面已經(jīng)講過晤柄。

4. SQL 與索引優(yōu)

   當(dāng)我們的 SQL 語句比較復(fù)雜，有多個(gè)關(guān)聯(lián)和子查詢的時(shí)候妖胀，就要分析 SQL 語句有沒有改寫的方法芥颈。
   舉個(gè)簡單的例子，一模一樣的數(shù)據(jù)：

   -- 大偏移量的 limit
   select * from user_innodb limit 900000,10; -- 改成先過濾 ID赚抡，再 limit
   SELECT * FROM user_innodb WHERE id >= 900000 LIMIT 10;

對于具體的 SQL 語句的優(yōu)化爬坑，MySQL 官網(wǎng)也提供了很多建議，這個(gè)是我們在分析具體的 SQL 語句的時(shí)候需要注意的涂臣，也是大家在以后的工作里面要去慢慢地積累的（這里我們就不一一地分析了）盾计。

https://dev.mysql.com/doc/refman/5.7/en/optimization.html

五，存儲引擎

1. 存儲引擎的選擇

   為不同的業(yè)務(wù)表選擇不同的存儲引擎赁遗，例如：查詢插入操作多的業(yè)務(wù)表闯估，用 MyISAM。
   臨時(shí)數(shù)據(jù)用 Memeroy吼和。常規(guī)的并發(fā)大更新多的表用 InnoDB。

2. 分區(qū)或者分表

   分區(qū)不推薦骑素。
   交易歷史表：在年底為下一年度建立 12 個(gè)分區(qū)炫乓，每個(gè)月一個(gè)分區(qū)刚夺。
   渠道交易表：分成當(dāng)日表；當(dāng)月表末捣；歷史表侠姑，歷史表再做分區(qū)。

3. 字段定義

   原則：使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型箩做。
   為每一列選擇合適的字段類型：

   • 整數(shù)類型
   p1.png

INT 有 8 種類型莽红，不同的類型的最大存儲范圍是不一樣的。
性別邦邦？用 TINYINT安吁，因?yàn)?ENUM 也是整型存儲。

• 字符類型

  變長情況下燃辖，varchar 更節(jié)省空間鬼店，但是對于 varchar 字段，需要一個(gè)字節(jié)來記錄長度黔龟。

  固定長度的用 char妇智，不要用 varchar。

• 非空

  非空字段盡量定義成 NOT NULL氏身，提供默認(rèn)值巍棱，或者使用特殊值、空串代替 null蛋欣。
  NULL 類型的存儲航徙、優(yōu)化、使用都會存在問題豁状。

• 不要用外鍵捉偏、觸發(fā)器稿械、視圖

  降低了可讀性叠萍；
  影響數(shù)據(jù)庫性能，應(yīng)該把把計(jì)算的事情交給程序砸紊，數(shù)據(jù)庫專心做存儲谊路；
  數(shù)據(jù)的完整性應(yīng)該在程序中檢查讹躯。

• 大文件存儲

  不要用數(shù)據(jù)庫存儲圖片（比如 base64 編碼）或者大文件；
  把文件放在 NAS 上缠劝，數(shù)據(jù)庫只需要存儲 URI（相對路徑）潮梯，在應(yīng)用中配置 NAS 服務(wù)器地址。

• 表拆分

  將不常用的字段拆分出去惨恭，避免列數(shù)過多和數(shù)據(jù)量過大秉馏。
  比如在業(yè)務(wù)系統(tǒng)中，要記錄所有接收和發(fā)送的消息脱羡，這個(gè)消息是 XML 格式的萝究，用blob 或者 text 存儲免都，用來追蹤和判斷重復(fù)，可以建立一張表專門用來存儲報(bào)文帆竹。

六绕娘，總結(jié)：優(yōu)化體系

p2.png

除了對于代碼、SQL 語句栽连、表定義险领、架構(gòu)、配置優(yōu)化之外秒紧，業(yè)務(wù)層面的優(yōu)化也不能忽視绢陌。舉幾個(gè)例子：
1）在某一年的雙十一，為什么會做一個(gè)充值到余額寶和余額有獎金的活動（充 300送 50）噩茄？
因?yàn)槭褂糜囝~或者余額寶付款是記錄本地或者內(nèi)部數(shù)據(jù)庫下面，而使用銀行卡付款，需要調(diào)用接口绩聘，操作內(nèi)部數(shù)據(jù)庫肯定更快沥割。
2）在去年的雙十一，為什么在凌晨禁止查詢今天之外的賬單凿菩？這是一種降級措施机杜，用來保證當(dāng)前最核心的業(yè)務(wù)。
3）最近幾年的雙十一衅谷，為什么提前一個(gè)多星期就已經(jīng)有雙十一當(dāng)天的價(jià)格了椒拗？
預(yù)售分流。
在應(yīng)用層面同樣有很多其他的方案來優(yōu)化获黔，達(dá)到盡量減輕數(shù)據(jù)庫的壓力的目的蚀苛，比如限流，或者引入 MQ 削峰玷氏，等等等等堵未。
為什么同樣用 MySQL，有的公司可以扛住百萬千萬級別的并發(fā)盏触，而有的公司幾百個(gè)并發(fā)都扛不住渗蟹，關(guān)鍵在于怎么用。所以赞辩，用數(shù)據(jù)庫慢雌芽，不代表數(shù)據(jù)庫本身慢，有的時(shí)候還要往上層去優(yōu)化辨嗽。

當(dāng)然世落，如果關(guān)系型數(shù)據(jù)庫解決不了的問題，我們可能需要用到搜索引擎或者大數(shù)據(jù)的方案了糟需，并不是所有的數(shù)據(jù)都要放到關(guān)系型數(shù)據(jù)庫存儲岛心。

一些信息

路漫漫其修遠(yuǎn)兮,吾將上下而求索
碼云：https://gitee.com/javacoo
QQ：164863067
作者/微信：javacoo
郵箱：xihuady@126.com