MySQL 性能優(yōu)化思路和工具

一、優(yōu)化思路

作為架構(gòu)師或者開發(fā)人員，說到數(shù)據(jù)庫性能優(yōu)化，你的思路是什么樣的？
或者具體一點(diǎn)，如果在面試的時(shí)候遇到這個(gè)問題：你會(huì)從哪些維度來優(yōu)化數(shù)據(jù)庫，你會(huì)怎么回答？

我們說到性能調(diào)優(yōu)，大部分時(shí)候想要實(shí)現(xiàn)的目標(biāo)是讓我們的查詢更快。一個(gè)查詢的動(dòng)作又是由很多個(gè)環(huán)節(jié)組成的，每個(gè)環(huán)節(jié)都會(huì)消耗時(shí)間。
我們要減少查詢所消耗的時(shí)間，就要從每一個(gè)環(huán)節(jié)入手。

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

第一個(gè)環(huán)節(jié)是客戶端連接到服務(wù)端，連接這一塊有可能是服務(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、從客戶端來說，可以減少?gòu)姆?wù)端獲取的連接數(shù)，如果我們想要不是每一次執(zhí)行SQL 都創(chuàng)建一
個(gè)新的連接，這個(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中有很多配置參數(shù)，包括各種開關(guān)和數(shù)值的配置，大多數(shù)參數(shù)都提供了一個(gè)默認(rèn)值，比如默認(rèn)的 buffer_pool_size，默認(rèn)的頁大小，InnoDB 并發(fā)線程數(shù)等等。

這些默認(rèn)配置可以滿足大部分情況的需求，除非有特殊的需求，在清楚參數(shù)的含義的情況下再去修
改它。修改配置的工作一般由專業(yè)的 DBA 完成。

除了合理設(shè)置服務(wù)端的連接數(shù)和客戶端的連接池大小之外，我們可以引入緩存。

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

3.1 緩存

在應(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)化措施。

3.2 主從復(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)的呢？在Mysql架構(gòu)與內(nèi)部模塊我們說過，更新語句會(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è)就是分庫分表。

3.3 分庫分表

垂直分庫，減少并發(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í)比較長(zhǎng)的查
詢語句，讓我們?nèi)?yōu)化，這些語句是從哪里來的呢？

我們的服務(wù)層每天執(zhí)行了這么多 SQL 語句，它怎么知道哪些 SQL 語句比較慢呢？
第一步，我們要把 SQL 執(zhí)行情況記錄下來。

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

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

4.1.1 打開慢日志開關(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í)行超過多長(zhǎng)時(shí)間的 SQL 才記錄到慢日志，默認(rèn)是 10 秒。

showvariableslike'%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';

4.1.2 慢日志分析

1、日志內(nèi)容

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

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

2、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 工具可以使用

4.2 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ì)的幫助信息

4.2.1 查看是否開啟

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

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

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

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ǔ)引擎信息來分析。

4.2.3 其他系統(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 號(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í)行 SQL 查詢語句的過程，來知道 MySQL 是怎么處理一條
SQL 語句的。通過這種方式我們可以分析語句或者表的性能瓶頸。

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

4.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 NULL ) 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', 'bobo', '1' );
INSERT INTO `teacher`
VALUES
( '2', 'jim', '2' );
INSERT INTO `teacher`
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%';

4.3.1 id

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

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

-- 查詢 mysql 課程的老師手機(jī)號(hào)
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 tc。

先查課程表，再查老師表，最后查老師聯(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 );

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
簡(jiǎn)單查詢，不包含子查詢，不包含關(guān)聯(lián)查詢 union。

EXPLAIN SELECT * FROM teacher;

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

-- 查詢 mysql 課程的老師手機(jī)號(hào)
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' ) );

PRIMARY
子查詢 SQL 語句中的主查詢，也就是最外面的那層查詢。
SUBQUERY
子查詢中所有的內(nèi)層查詢都是 SUBQUERY 類型的。
DERIVED
衍生查詢，表示在得到最終查詢結(jié)果之前會(huì)用到臨時(shí)表。例如：

-- 查詢 ID 為 1 或 2 的老師教授的課程
EXPLAIN SELECT
cr.cname
FROM
( 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 查詢。<union2,3>代表 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 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;

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ù)。

DELETE
FROM
teacher
WHERE
tid 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)化的。

4.3.4 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)建索引。

4.3.5 key_len

索引的長(zhǎng)度（使用的字節(jié)數(shù)）。跟索引字段的類型、長(zhǎng)度有關(guān)。
表上有聯(lián)合索引：KEY comidx_name_phone ( name , phone )

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

4.3.6 rows

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

4.3.7 filtered

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

4.3.8 ref

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

4.3.9 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 任意列

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 語句的。通過這種方式
我們可以分析語句或者表的性能瓶頸。
分析出問題之后，就是對(duì) SQL 語句的具體優(yōu)化。

4.4 SQL 與索引優(yōu)化

SQL 語句的優(yōu)化的目標(biāo)，大部分時(shí)候都是用到索引。
在mysql 索引原理和使用里面也講到了索引創(chuàng)建的原則，和什么情況會(huì)用到索引，什么情況不會(huì)用到索引。

五、存儲(chǔ)引擎

5.1 存儲(chǔ)引擎的選擇

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

5.2 字段定義

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

5.2.1 整數(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ǔ)。

5.2.2 字符類型

變長(zhǎng)情況下，varchar 更節(jié)省空間，但是對(duì)于 varchar 字段，需要一個(gè)字節(jié)來記錄長(zhǎng)度。
固定長(zhǎng)度的用 char，不要用 varchar。

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

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

5.2.4 大文件存儲(chǔ)

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

5.2.5 表拆分或字段冗余

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

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

除了對(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ǔ)。文章來源地址http://www.zghlxwxcb.cn/news/detail-407390.html

到了這里，關(guān)于MySQL 性能優(yōu)化思路和工具的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！