一、優(yōu)化思路

作為架構(gòu)師或者開發(fā)人員，說到數(shù)據(jù)庫性能優(yōu)化，你的思路是什么樣的？

或者具體一點(diǎn)，如果在面試的時(shí)候遇到這個(gè)問題：你會(huì)從哪些維度來優(yōu)化數(shù)據(jù)庫，你會(huì)怎么回答？

我們?cè)诘谝还?jié)課開始的時(shí)候講了，這四節(jié)課的目標(biāo)是為了讓大家建立數(shù)據(jù)庫的知識(shí)體系，和正確的學(xué)習(xí)調(diào)優(yōu)的思路。

我們說到性能調(diào)優(yōu)，大部分時(shí)候想要實(shí)現(xiàn)的目標(biāo)是讓我們的查詢速度更快。一個(gè)查詢的動(dòng)作又是有很多個(gè)環(huán)節(jié)組成的，每個(gè)環(huán)節(jié)都會(huì)消耗時(shí)間，我們?cè)诘谝还?jié)課講 SQL 語句的執(zhí)行流程的時(shí)候已經(jīng)分析過了。

我們要減少查詢所消耗的時(shí)間，就要從每一個(gè)環(huán)節(jié)入手。

二、 連接——配置優(yōu)化

第一個(gè)環(huán)節(jié)是客戶端連接到服務(wù)端，連接這一塊有可能會(huì)出現(xiàn)什么樣的性能問題？有可能是服務(wù)端連接數(shù)不夠?qū)е聭?yīng)用程序獲取不到連接。比如報(bào)了一個(gè) Mysql: error1040: Too many connections 的錯(cuò)誤。

我們可以從兩個(gè)方面來解決連接數(shù)不夠的問題：

1、從服務(wù)端來說，我們可以增加服務(wù)端的可用連接數(shù)。

如果有多個(gè)應(yīng)用或者很多請(qǐng)求同時(shí)訪問數(shù)據(jù)庫，連接數(shù)不夠的時(shí)候，我們可以：

（1）修改配置參數(shù)增加可用連接數(shù)，修改 max_connections 的大?。?/p>

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

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

show global variables like ‘wait_timeout’; –及時(shí)釋放不活動(dòng)的連接，注意不要釋放連接池還

在使用的連接

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）。

我們這里說到了從數(shù)據(jù)庫配置的層面去優(yōu)化數(shù)據(jù)庫。不管是數(shù)據(jù)庫本身的配置，還是安裝這個(gè)數(shù)據(jù)庫服務(wù)的操作系統(tǒng)的配置，對(duì)于配置進(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ù)端的連接數(shù)和客戶端的連接池大小之外，我們還有哪些減少客戶端跟數(shù)據(jù)庫服務(wù)端的連接數(shù)的方案呢？

我們可以引入緩存。

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

緩存

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

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

運(yùn)行獨(dú)立的緩存服務(wù)，屬于架構(gòu)層面的優(yōu)化。

為了減少單臺(tái)數(shù)據(jù)庫服務(wù)器的讀寫壓力，在架構(gòu)層面我們還可以做其他哪些優(yōu)化措施？

主從復(fù)制

如果單臺(tái)數(shù)據(jù)庫服務(wù)滿足不了訪問需求，那我們可以做數(shù)據(jù)庫的集群方案。

集群的話必然會(huì)面臨一個(gè)問題，就是不同的節(jié)點(diǎn)之間數(shù)據(jù)一致性的問題。如果同時(shí)讀寫多臺(tái)數(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。

主從復(fù)制是怎么實(shí)現(xiàn)的呢？在第一節(jié)課我們說過，更新的語句會(huì)記錄下來 binlog，它是一種邏輯日志。

有了這個(gè) binlog，從服務(wù)器會(huì)獲取主服務(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ù)庫里的。

這個(gè)是主從復(fù)制涉及到的三個(gè)線程。

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

讀寫分離可以一定程度地減輕數(shù)據(jù)庫服務(wù)器的訪問壓力，但是需要特別注意數(shù)據(jù)一致性的問題。

我們?cè)谧隽酥鲝膹?fù)制之后，如果單個(gè) master 節(jié)點(diǎn)或者單張表存儲(chǔ)的數(shù)據(jù)過大的時(shí)候，比如一張表有上億的數(shù)據(jù)，單張表的查詢性能還是會(huì)下降，我們要進(jìn)一步對(duì)單臺(tái)數(shù)據(jù)庫節(jié)點(diǎn)的數(shù)據(jù)進(jìn)行拆分，這個(gè)就是是分庫分表。

分庫分表

垂直分庫，減少并發(fā)壓力。水平分表，解決存儲(chǔ)瓶頸。

垂直分庫的做法，把一個(gè)數(shù)據(jù)庫按照業(yè)務(wù)拆分成不同的數(shù)據(jù)庫：

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

以上是架構(gòu)層面的優(yōu)化，可以用緩存，主從，分庫分表

第三個(gè)環(huán)節(jié)：

解析器，詞法和語法分析，主要是保證語句的正確性，語句不出錯(cuò)就沒問題。由 Sever 自己處理，跳過。

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

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

優(yōu)化器就是對(duì)我們的 SQL 語句進(jìn)行分析，生成執(zhí)行計(jì)劃。

問題是：在我們做項(xiàng)目的時(shí)候，有時(shí)會(huì)收到 DBA 的郵件，里面列出了我們項(xiàng)目上幾個(gè)耗時(shí)比較長的項(xiàng)目詢問語句，讓我們?nèi)?yōu)化，這些語句是從哪里來的呢？

我們的服務(wù)層每天執(zhí)行了這么多 SQL 語句，它怎么知道哪些 SQL 語句比較慢呢？

第一步，我們要把 SQL 執(zhí)行情況記錄下來。

慢查詢?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%’;

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

show variables like ‘%long_query%’;

可以直接動(dòng)態(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è)窗口后才會(huì)查

到最新值

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’;

慢日志分析

日志內(nèi)容

show global status like ‘slow_queries’; — 查看有多少慢查詢show variables like ‘%slow_query%’; — 獲取慢日志目錄cat /var/lib/mysql/ localhost-slow.log

有了慢查詢?nèi)罩?，怎么去分析統(tǒng)計(jì)呢？比如 SQL 語句的出現(xiàn)的慢查詢次數(shù)最多，平均每次執(zhí)行了多久？人工肉眼分析顯然不可能。

mysqldumpslow

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

MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目錄下。

mysqldumpslow –help

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

mysqldumpslow -s t -t 10 -g ‘select’ /var/lib/mysql/localhost-slow.log

Count 代表這個(gè) SQL 執(zhí)行了多少次；

Time 代表執(zhí)行的時(shí)間，括號(hào)里面是累計(jì)時(shí)間；

Lock 表示鎖定的時(shí)間，括號(hào)是累計(jì)；

Rows 表示返回的記錄數(shù)，括號(hào)是累計(jì)。

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

SHOW PROFILE

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

SHOW PROFILE 谷歌高級(jí)架構(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ì)

show profiles;

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

show profile;

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)行信息、存儲(chǔ)引擎信息來分析。

其他系統(tǒng)命令

show processlist 運(yùn)行線程

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

這是很重要的一個(gè)命令，用于顯示用戶運(yùn)行線程?？梢愿鶕?jù) id 號(hào) kill 線程。

也可以查表，效果一樣：（可以 group order by 了）

select * from information_schema.processlist;

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

說明：https://dev.mysql.com/doc/refman/5.7/en/show-status.html

詳細(xì)參數(shù)：https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

SHOW STATUS 用于查看 MySQL 服務(wù)器運(yùn)行狀態(tài)（重啟后會(huì)清空），有 session和 global 兩種作

用域，格式：參數(shù)-值。

可以用 like 帶通配符過濾。

SHOW GLOBAL STATUS LIKE ‘com_select’; — 查看 select 次數(shù)

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

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

https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html

show engine 用來顯示存儲(chǔ)引擎的當(dāng)前運(yùn)行信息，包括事務(wù)持有的表鎖、行鎖信息；事務(wù)地鎖等待

情況；線程信號(hào)量等待；文件 IO 請(qǐng)求；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)、存儲(chǔ)引擎狀態(tài)、線程運(yùn)行信息的命令，如果讓你去寫一個(gè)數(shù)據(jù)庫監(jiān)控系統(tǒng)，你會(huì)怎么做？

其實(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í)行任務(wù) SQL 查詢語句的過程，才知道 MySQL 是怎么處理的SQL 語句的。通過這種方式我們可以分析語句或者表的性能瓶頸。

SHOW GLOBAL STATUS LIKE ‘com_select’; — 查看 select 次數(shù)show engine innodb status;

show variables like ‘innodb_status_output%’; — 開啟輸出：

SET GLOBAL innodb_status_output=ON;

SET GLOBAL innodb_status_output_locks=ON;

MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update、delete、insert 了。

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

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

我們先創(chuàng)建三張表。一張課程表，一張老師表，一張老師聯(lián)系方式表（沒有任何索引）。

DROP TABLEIFEXISTS course;CREATE TABLE `course` ( `cid` INT ( 3 ) DEFAULT NULL, `cname` VARCHAR ( 20 )DEFAULT NULL, `tid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET =utf8mb4;DROP TABLEIFEXISTS 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 TABLEIFEXISTS 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’, ‘bobo’, ‘1’ );INSERT INTO `teacher`VALUES( ‘2’, ‘jim’, ‘2’ );INSERT INTO `teacher` explain 的結(jié)果有很多的字段，我們?cè)敿?xì)地分析一下。先確認(rèn)一下環(huán)境：4.3.1 idid 是查詢序列編號(hào)。id 值不同id 值不同的時(shí)候，先查詢 id 值大的（先大后?。?。查詢順序：course c——teacher t——teacher_contact tc。先查課程表，再查老師表，最后查老師聯(lián)系方式表。子查詢只能以這種方式進(jìn)行，只有拿到內(nèi)層的結(jié)果之后才能進(jìn)行外層的查詢。id 值相同（從上往下）VALUES( ‘3’, ‘dahai’, ‘3’ );INSERT INTO `teacher_contact`VALUES( ‘1’, ‘13688888888’ );INSERT INTO `teacher_contact`VALUES( ‘2’, ‘18166669999’ );INSERT INTO `teacher_contact`VALUES( ‘3’, ‘17722225555’ );

explain 的結(jié)果有很多的字段，我們?cè)敿?xì)地分析一下。

先確認(rèn)一下環(huán)境：

select version();show variables like ‘%engine%’;

id

id 查詢序列編號(hào)。

id 值得不同的時(shí)候，先查詢 id 值大的（先大后小）。

— 查詢 mysql 課程的老師手機(jī)號(hào)EXPLAIN SELECTtc.phoneFROMteacher_contact tcWHEREtcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM coursec WHERE c.cname = ‘mysql’ ) );

查詢順序：course c——teacher t——teacher_contact tc。

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

id 值相同（從上往下）

— 查詢課程 ID 為 2，或者聯(lián)系表 ID 為 3 的老師EXPLAIN SELECTt.tname,c.cname,tc.phoneFROMteacher t,course c,teacher_contact tcWHEREt.tid = c.tidAND t.tcid = tc.tcidAND ( c.cid = 2 OR tc.tcid = 3 );

id 值相同時(shí)，表的查詢順序是從上往下順序執(zhí)行。例如這次查詢的 id 都是 1，查詢的順序是teacher t（3 條）——course c（4 ）——teacher_contact tc（3 條）。

既有相同也有不同

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

4.3.2 select type 查詢類型

這里并沒有列舉全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。

下面列舉了一些常見的查詢類型：

SIMPLE

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

EXPLAIN SELECT * FROM teacher;

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

— 查詢 mysql 課程的老師手機(jī)號(hào)EXPLAIN SELECTtc.phoneFROMteacher_contact tcWHEREtcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM coursec WHERE c.cname = ‘mysql’ ) );

PRIMARY

的查詢 SQL 語句中的主查詢，也就是最外面的那層查詢。

SUBQUERY

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

DERIVED

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

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

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

UNION

用到了 UNION 查詢。同上例。

UNION RESULT

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

4.3.3 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 TABLEIFEXISTS single_data;CREATE TABLE single_data ( id INT ( 3 ) PRIMARY KEY, content VARCHAR ( 20 ) );INSERT INTO single_dataVALUES( 1, ‘a’ );EXPLAIN SELECT*FROMsingle_data aWHEREid = 1;

system

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

EXPLAIN SELECT * FROM mysql.proxies_priv;

eq_ref

通常出現(xiàn)在多表的 join 查詢，表示對(duì)于前表的每一個(gè)結(jié)果,，都只能匹配到后表的一行結(jié)果。一般是唯一性索引的查詢（UNIQUE 或 PRIMARY KEY）。

eq_ref 是除 const 之外最好的訪問類型。

先刪除 teacher 表中多余的數(shù)據(jù)，teacher_contact 有 3 條數(shù)據(jù)，teacher 表有 3條數(shù)據(jù)。

DELETEFROMteacherWHEREtid IN ( 4, 5, 6 );COMMIT;– 備份INSERT INTO `teacher`VALUES( 4, ‘jim’, 4 );INSERT INTO `teacher`VALUES( 5, ‘bobo’, 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;

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

小結(jié)：

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

ref

查詢用到了非唯一性索引，或者關(guān)聯(lián)操作只使用了索引的最左前綴。

例如：使用 tcid 上面的普通索引查詢：

explain SELECT * FROM teacher where tcid = 3;

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;

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

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

index

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

EXPLAIN SELECT tid FROM teacher;

all

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

小結(jié)：

一般來說，需要保證查詢至少達(dá)到 range 級(jí)別，最好能達(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′;

結(jié)論：是有可能的（這里是覆蓋索引的情況）。

如果通過分析發(fā)現(xiàn)沒有用到索引，就要檢查 SQL 或者創(chuàng)建索引。

key_len

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

表上有聯(lián)合索引：KEY comidx_name_phone ( name , phone )

explain select * from user_innodb where name =’jim’;

rows

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

filtered

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

一個(gè)百分比。

ref

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

Extra

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

using index

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

EXPLAIN SELECT tid FROM teacher ;

using where

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

EXPLAIN select * from user_innodb where phone =’13866667777′;

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 =’jim’ order by id;

（order by id 引起）

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 任意列3.

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

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

總結(jié)一下：

模擬優(yōu)化器執(zhí)行 SQL 查詢語句的過程，才知道 MySQL 是怎么處理一條 SQL 語句的。通過這種方式我們可以分析語句或者表達(dá)的性能瓶頸。

分析出問題之后，就是對(duì) SQL 語句的具體優(yōu)化。

SQL 與索引優(yōu)化

SQL 語句的優(yōu)化的目標(biāo)，大部分時(shí)候都是用到索引。

我們?cè)诘诙?jié)課里面也講到了索引創(chuàng)建的原則，什么情況會(huì)用到索引，什么情況不會(huì)用到索引。

五、存儲(chǔ)引擎

存儲(chǔ)引擎的選擇

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

字段定義

原則：使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型。

為每一列選擇合適的字段類型。

整數(shù)類型

• TINYINT 1 個(gè)字節(jié)
• SMALLINT 2 個(gè)字節(jié)
• MEDIUMINT 3 個(gè)字節(jié)
• INT, INTEGER 4 個(gè)字節(jié)
• BIGINT 8 個(gè)字節(jié)

INT 有 8 種類型，不同的類型的最大存儲(chǔ)范圍是不一樣的。

性別？用 TINYINT，因?yàn)?ENUM 也是整數(shù)存儲(chǔ)。

字符類型

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

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

不要用外鍵、觸發(fā)器、視圖

降低了可讀性；

影響數(shù)據(jù)庫性能，應(yīng)該把把計(jì)算的事情交給程序，數(shù)據(jù)庫專心做存儲(chǔ)；

數(shù)據(jù)的完整性應(yīng)該在程序中檢查。

大文件存儲(chǔ)

不要用數(shù)據(jù)庫存儲(chǔ)圖片（比如 base64 編碼）或者大文件；

把文件放在 NAS 上，數(shù)據(jù)庫只需要存儲(chǔ) URI（相對(duì)路徑），在應(yīng)用中配置 NAS 服務(wù)器地址。

表拆分或字段冗余

將不常用的字段拆分出去，避免列數(shù)過多和數(shù)據(jù)量過大。

比如在業(yè)務(wù)系統(tǒng)中，要記錄所有接收和發(fā)送的消息，這個(gè)消息是 XML 格式的，用blob 或者 text 存

儲(chǔ)，用來追蹤和判斷重復(fù)，可以建立一張表專門用來存儲(chǔ)報(bào)文。

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

所以，如果在面試的時(shí)候再問到這個(gè)問題“你會(huì)從哪些維度來優(yōu)化數(shù)據(jù)庫”，你會(huì)怎么回答？

除了對(duì)于代碼、SQL 語句、定義、架構(gòu)、配置優(yōu)化之外，業(yè)務(wù)層面的優(yōu)化也不能忽視。舉兩個(gè)例子：

1）在某一年的雙十一，為什么會(huì)做一個(gè)充值到余額寶和余額寶獎(jiǎng)金的活動(dòng)，例如充300 送 50？因?yàn)槭褂糜囝~或者余額寶付款是記錄本地或者內(nèi)部數(shù)據(jù)庫，而使用銀行卡付款，需要調(diào)用接口，操內(nèi)部數(shù)據(jù)庫肯定更快。

2）在去年的雙十一，為什么在凌晨禁止查詢今天之外的賬單？

這是一種降級(jí)措施，用來保證當(dāng)前最核心的業(yè)務(wù)。

3）最近幾年的雙十一，為什么提前個(gè)把星期就已經(jīng)有雙十一當(dāng)天的價(jià)格了？

預(yù)售分流。

4）公安局的同名查詢，不是實(shí)時(shí)返回結(jié)果（不是實(shí)時(shí)查詢數(shù)據(jù)庫），而是通過公眾號(hào)推送。

在應(yīng)用層面同樣有很多其他的方案來優(yōu)化，達(dá)到盡量減輕數(shù)據(jù)庫的壓力的目的，比如限流，或者引入 MQ 削峰，等等等等。

為什么同樣用 MySQL，有的公司可以抗住百萬千萬級(jí)別的并發(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ù)庫存儲(chǔ)。