相關學習推薦：

前言

MySQL索引底層資料結構與演演算法
MySQL效能優化原理-前篇

前兩篇說完了索引底層資料結構、效能優化原理的基本概念。本篇將講講具體實踐。分兩篇來講，這是實踐的第一篇。對於一個以資料為中心的應用，資料庫的好壞直接影響到程式的效能，因此資料庫效能至關重要。一般來說，要保證資料庫的效率，要做好以下四個方面的工作：

資料庫表設計
SQL語句優化
資料庫引數設定
恰當的硬體資源和作業系統

此外，使用適當的儲存過程，也能提升效能。這個順序也表現了四個方面對效能影響的大小。

資料庫表設計

通俗地理解三個正規化，對於資料庫設計大有好處。在資料庫設計當中，為了更好地應用三個正規化，就必須通俗地理解三個正規化。

第一規格化：1NF - 確保原子性

是對屬性的原子性約束，要求屬性（列）具有原子性，不可再分解；（只要是關係型資料庫都滿足 1NF）

第二正規化：2NF - 確保表中每列都和主鍵相關

是對記錄的唯一性約束，要求記錄有唯一標識，即實體的唯一性；

先滿足1NF，然後每張表要有主鍵，並且確保每一列都和主鍵相關，而不是主鍵的一部分（主要針對聯合主鍵）。換言之，一個表中只儲存一種資料而不是多種資料。

錯誤示範：商品訂單資訊錯誤設計

正確示範：商品訂單資訊正確設計

第三正規化：3NF - 確保每列都和主鍵直接相關，而不是間接相關

3NF 是對欄位冗餘性的約束，它要求欄位沒有冗餘。

第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。不能傳遞依賴，如非主鍵列A依賴非主鍵列B，非主鍵列B依賴主鍵。

關鍵欄位 -> 非關鍵欄位x -> 非關鍵欄位y複製程式碼

案例1:

比如在設計一個訂單資料表的時候，可以將客戶編號作為一個外來鍵和訂單表建立相應的關係。而不可以在訂單表中新增關於客戶其它資訊（比如姓名、所屬公司等）欄位。如下這兩個表所示的設計就是一個滿足第三正規化的資料庫表。

案例2:

假定學生關係表為 Student (學號、姓名、年齡、所在學院、學院地點、學院電話)，關鍵字為單一關鍵字「學號」，因為存在如下決定關係：

（學號）-> (姓名、年齡、所在學院、學院地點、學院電話)複製程式碼

即存在非關鍵欄位「學院地點」、「學院電話」對關鍵欄位「學號」的傳遞函數依賴。它也會存在資料冗餘，更新異常、插入異常和刪除異常的情況。正確應把學生關係表分為如下兩個表：

學生：（學號、姓名、年齡、所在學院）
學院：（學院、地點、電話）

正規化化優缺點

正規化化的優點：

重複資料少，不冗餘；
維護更新快；
正規化化的表更小，可在記憶體中執行。

正規化化的缺點：

查詢的時候經常需要很多關聯，增加查詢的代價。也可能使一些索引策略失效，因為正規化化將列放在不同的表中，而這些列在一個表中本可以屬於同一個索引。

反正規化化的優缺點

反正規化化的優點：

避免關聯，幾乎所有資料可以在一張表中顯示。
可以設計有效的索引。

反正規化化的缺點：

冗餘資料多，更小維護麻煩，刪除資料時也容易丟失重要資訊。

資料表設計的建議

沒有冗餘的資料庫設計可以做到，但是，沒有冗餘的資料庫未必是最好的資料庫，有時為列提高執行效率，就必須降低正規化標準，適當保留冗餘資料。具體做法：在概念資料模型設計時遵守第三正規化，降低正規化標準的工作放到物理資料模型設計時考慮。降低正規化就是增加欄位，允許冗餘。

另外，《阿里巴巴Java開發手冊》，資料庫的表設計允許適當冗餘，以提升SQL查詢的效能，避免表的關聯查詢。

適度冗餘，減少join的關聯

冗餘更新頻率不高，但是查詢頻率極高的欄位。如訂單中的商品名稱、微博發帖中的使用者暱稱。

大欄位垂直拆分

如把部落格列表中的內容拆分出去，存取列表的時候不讀取部落格內容，為縱深的邏輯關係。

大表水平拆分

舉例說明：在一個論壇系統裡，管理員經常會發一些貼文，這些貼文要求在每個分類列表裡都要置頂。

設計方案一：在發帖表裡增加一列用來標示是否是管理員發帖，這樣在每個分類列表展示時就需要對發帖表查詢兩次，一次是置頂帖，一次是普通帖，然後將兩次結果合併。如果發帖表內容較大時，查詢置頂帖的效能開銷會比較大。
設計方案二：將置頂帖存放在一個單獨的置頂表裡。因為置頂帖數量相比會很少，但存取頻率很高，這樣從發帖表裡分拆開來，存取的效能開銷會少很多。

合適的資料型別

如果資料量一樣，但資料型別更小的話，資料存放同樣的資料就會佔用更少的空間，這樣檢索同樣的資料所帶來的IO 消耗自然會降低，效能也就很自然的得到提升。此外，MySQL 對不同型別的資料，處理方式也不一樣，比如在運算或者排序操作中，越簡單的資料型別操作效能越高，所以對於要頻繁進行運算或者排序的欄位儘量選擇簡單的資料型別。

SQL語句優化

SQL優化的一般步驟

通過show status命令瞭解各種SQL的執行頻率；
定位執行效率較低的SQL語句-（重點select）；
通過explain分析低效率的SQL；
確定問題並採取相應的優化措施。

-- select語句分類SelectDml資料操作語言(insert update delete)
dtl 資料事物語言(commit rollback savepoint)Ddl資料定義語言(create alter drop..)
Dcl(資料控制語言) grant revoke-- Show status 常用命令--查詢本次對談Show session status like 'com_%';     //show session status like 'Com_select'--查詢全域性Show global status like 'com_%';-- 給某個使用者授權grant all privileges on *.* to 'abc'@'%';--為什麼這樣授權 'abc' 表示使用者名稱  '@' 表示host, 檢視一下mysql->user表就知道了--回收許可權revoke all on *.* from 'abc'@'%';--重新整理許可權[也可以不寫]flush privileges;複製程式碼

SQL語句優化-show引數

MySQL使用者端連線成功後，通過使用 show [session|global] status 命令可以提供伺服器狀態資訊。其中的session來表示當前的連線的統計結果，global來表示自資料庫上次啟動至今的統計結果。預設是session級別的。

show status like 'Com_%';複製程式碼

其中， Com_XXX 表示 XXX 語句所執行的次數。重點注意：Com_select,Com_insert,Com_update,Com_delete 通過這幾個引數，可以瞭解到當前資料庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執行比例是多少。

還有幾個常用的引數便於使用者瞭解資料庫的基本情況。Connections：試圖連線MySQL伺服器的次數Uptime：伺服器工作的時間（單位秒）Slow_queries：慢查詢的次數 (預設是慢查詢時間10s)

show status like 'Connections';show status like 'Uptime';show status like 'Slow_queries';複製程式碼

查詢MySQL的慢查詢時間

show variables like 'long_query_time';複製程式碼

修改MySQL慢查詢時間

set long_query_time=2;複製程式碼

SQL語句優化-定位慢查詢

上面我們介紹了獲取MySQL資料庫的一些執行狀態是如何查詢

比如當前MySQL執行的時間： show status like 'Uptime';
一共執行了多少次select/update/delete.. / ： show status like 'Com_%';
當前連線數

定位慢查詢

如何從一個專案中快速定位執行速度慢的語句（定位慢查詢）

show variables like '%query%';複製程式碼

slow_query_log 預設是off關閉的，使用時，需要改為on 開啟　　　　　　
slow_query_log_file 記錄的是慢紀錄檔的記錄檔案
long_query_time 預設是10S，每次執行的sql達到這個時長，就會被記錄

檢視慢查詢狀態

Slow_queries 記錄的是慢查詢數量當有一條sql執行一次比較慢時，這個vlue就是1 （記錄的是本次對談的慢sql條數）

show status like '%slow_queries%';複製程式碼

注意：
如何開啟慢查詢： SET GLOBAL slow_query_log = ON;
將預設時間改為1S： SET GLOBAL long_query_time = 1;
（設定完需要重新連線資料庫，PS：僅在這裡改的話，當再次重新啟動資料庫服務時，所有設定又會自動恢復成預設值，永久改變需去my.ini中改）

SQL語句優化-Explain工具

使用EXPLAIN關鍵字可以模擬優化器執行SQL語句，分析你的查詢語句或是結構的效能瓶頸在 select 語句之前增加 explain 關鍵字，MySQL 會在查詢上設定一個標記，執行查詢會返回執行計劃的資訊，而不是執行這條SQL。

注意:如果 from 中包含子查詢，仍會執行該子查詢，將結果放入臨時表中

Explain分析範例

DROP TABLE IF EXISTS `actor`; 
CREATE TABLE `actor` (`id` int(11) NOT NULL,`name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `actor` (`id`,`name`,`update_time`) VALUES (1,'a','2020-09-16 14:26:11'), (2,'b','2020-09-16 14:26:11'), (3,'c','2020-09-16 14:26:11');DROP TABLE IF EXISTS` film`; 
CREATE TABLE`film`(`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`),KEY `idx_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film`(`id`,`name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');DROP TABLE IF EXISTS `film_actor`;CREATE TABLE`film_actor`(`id` int(11) NOT NULL,`film_id` int(11) NOT NULL,`actor_id` int(11) NOT NULL,`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO`film_actor`(`id`,`film_id`,`actor_id`)VALUES(1,1,1), (2,1,2),(3,2,1);複製程式碼

explain select * from actor;複製程式碼

查詢中的每個表會輸出一行，如果有兩個表通過join連線查詢，那麼會輸出兩行。每一列具體的說明在後面進行說明。

Explain 兩個變種

explain extended

會在 explain 的基礎上額外提供一些查詢優化的資訊。緊隨其後通過 show warnings 命令可以得到優化後的查詢語句，從而看出優化器優化了什麼。額外還有 filtered 列，是一個半分比的值，rows * filtered/100 可以估算出將要和 explain 中前一個表進行連線的行數(前一個表指 explain 中的id值比當前表id值小的表)。

explain extended select * from film where id = 1;複製程式碼

show warnings;複製程式碼

explain partitions

相比 explain 多了個 partitions 欄位，如果查詢是基於分割區表的話，會顯示查詢將存取的分割區。

Explain中的列

接下來我們將展示 explain 中每個列的資訊。

id 列

id 列的編號是 select 的序列號，有幾個 select 就有幾個 id，並且id的順序是按 select 出現的順序遞增的。id列越大執行優先順序越高，id相同則從上往下執行，id為 NULL 最後執行。

select_type 列

select_type 表示對應行是簡單還是複雜的查詢。

simple ：簡單查詢。查詢不包含子查詢和union

explain select * from film where id = 2;複製程式碼

primary ：複雜查詢中最外層的 select
subquery ：包含在 select 中的子查詢（不在 from 子句中）
derived ：包含在 from 子句中的子查詢。MySQL 會將結果存放在一個臨時表中，也稱為派生表（derived的英文含義）

用下面這個例子來了解 primary、subquery、derived型別。

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;複製程式碼

未關閉MySQL5.7新特性對衍生表的合併優化，如下：

#關閉mysql5.7新特性對衍 生表的合併優化set session optimizer_switch='derived_merge=off'; 
複製程式碼

#還原預設設定set session optimizer_switch='derived_merge=on'; 
複製程式碼

union ：在 union 中的第二個和隨後的 select

explain select 1 union all select 1;複製程式碼

table 列

這一列表示 explain 的一行正在存取哪個表。當 from 子句中有子查詢時，table列是格式，表示當前查詢依賴 id=N 的查詢，於是先執行 id=N 的查詢。當有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。

type 列

這一列表示關聯型別或存取型別，即MySQL決定如何查詢表中的行，查詢資料行記錄的大概範圍。

依次從最優到最差分別為：

system > const > eq_ref > ref > range > index > ALL複製程式碼

一般來說，得保證查詢達到 range 級別，最好達到 ref。

NULL：MySQL 能夠在優化階段分解查詢語句，在執行階段用不著再存取表或索引。例如：在索引列中選取最小值，可以單獨查詢索引來完成，不需要在執行時存取表。

explain select min(id) from film;複製程式碼

system、const ：MySQL 能對查詢的某部分進行優化並將其轉化成一個常數（可以看 show warnings 的）。用於 primary key 或 unique key 的所有列與常數比較時，所以表最多有一個匹配行，讀取1次，速度比較快。system 是 const 的特例，表裡只有一條元組匹配時為 system。

explain extended select * from (select * from film where id = 1) tmp;複製程式碼

show warnings;複製程式碼

eq_ref ：primary key 或 unique key 索引的所有部分被連線使用，最多隻會返回一條符合條件的記錄。這可能實在 const 之外最好的連線型別了，簡單的 select 查詢不會出現這種 type。

explain select * from film_actor left join film on film_actor.film_id = film.id;複製程式碼

ref ：相比 eq_ref ，不使用唯一索引，而是使用普通索引或者唯一性索引的部分字首，索引要和某個值相比較，可能會找到多個符合條件的行。

（1）簡單 select 查詢，name 是普通索引（非唯一索引）

explain select * from film where name = 'film1';複製程式碼

（2）關聯表查詢， idx_film_actor_id 是 film_id 和 actor_id 的聯合索引，這裡使用到了 film_actor 的左邊字首 film_id 部分。

 explain select film_id from film left join film_actor on film.id = film_actor.film_id;複製程式碼

range ：範圍掃描通常出現在 in()、betwwen、>、<、>= 等操作中。使用一個索引來檢索給定範圍的行。

explain select * from actor where id > 1;複製程式碼

index ：掃描全表索引，通過比 ALL 快一些。

explain select * from film;複製程式碼

ALL ：即全表掃描，意味著MySQL需要從頭到尾去查詢所需要的行。通常情況下這需要增加索引來進行優化了。

explain select * from actor複製程式碼

possible_keys 列

這一列顯示查詢可能使用哪些索引來查詢。 explain 時可能出現 possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因為表中資料不多，mysql認為索引對此查詢幫助不大，選擇了全表查詢。如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查 where 子句看是否可以創造一個適當的索引來提高查詢效能，然後用 explain 檢視效果。

key 列

這一列顯示mysql實際採用哪個索引來優化對該表的存取。如果沒有使用索引，則該列是 NULL。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。

key_len 列

這一列顯示了mysql在索引裡使用的位元組數，通過這個值可以算出具體使用了索引中的哪些列。舉例來說，film_actor的聯合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個int列組成，並且每個int是4位元組。通過結果中的key_len=4可推斷出查詢使用了第一個列:film_id列來執行索引查詢。

explain select * from film_actor where film_id = 2;複製程式碼

key_len計算規則如下: 字串

char(n):n位元組長度
varchar(n):2位元組儲存字串長度，如果是utf-8，則長度 3n +2

數值型別

tinyint:1位元組
smallint:2位元組
int:4位元組
bigint:8位元組

時間型別

date:3位元組
timestamp:4位元組
datetime:8位元組

如果欄位允許為 NULL，需要1位元組記錄是否為 NULL

索引最大長度是768位元組，當字串過長時，mysql會做一個類似左字首索引的處理，將前半部分的字元提取出來做索引。

ref 列

這一列顯示了在key列記錄的索引中，表查詢值所用到的列或常數，常見的有:const(常量)，欄位名(例:film.id)

rows 列

這一列是mysql估計要讀取並檢測的行數，注意這個不是結果集裡的行數。

Extra 列

這一列展示的是額外資訊。常見的重要值如下:

（1）Using index ：使用覆蓋索引

explain select film_id from film_actor where film_id = 1;複製程式碼

（2）Using where ：使用 where 語句來處理結果，查詢的列未被索引覆蓋

explain select * from actor where name = 'a';複製程式碼

（3）Using index condition ：查詢的列不完全被索引覆蓋，where 條件中是一個前導列的範圍

explain select * from film_actor where film_id > 1;複製程式碼

（4）Using temporary ：MySQL 需要建立一張臨時表來處理查詢。出現這種情況一般是要進行優化的，首先是想到用索引來優化。

actor.name沒有索引，此時建立了張臨時表來distinct

explain select distinct name from actor;複製程式碼

film.name建立了idx_name索引，此時查詢時extra是using index,沒有用臨時表

explain select distinct name from film;複製程式碼

（5）Using filesort ：將用外部排序而不是索引排序，資料較小時從記憶體排序，否則需要在磁碟完成排序。這種情況下一般也是要考慮使用索引來優化的。

actor.name沒有索引，會瀏覽 actor 整個表，儲存排序關鍵字 name 和對應的 id，然後排序 name 並檢索行記錄。

explain select * from actor order by name;複製程式碼

film.name建立了idx_name索引,此時查詢時extra是using index

explain select * from film order by name;複製程式碼

（6）Select tables optimized away ：使用某些聚合函數(比如 max、min)來存取存在索引的某個欄位是

explain select min(id) from film;複製程式碼

SQL語句優化-索引最佳實踐

# 範例表CREATE TABLE`employees`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 )ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表'; 
INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());複製程式碼

全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan';複製程式碼

EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 22;複製程式碼

EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 22 AND position ='manager';複製程式碼

最左字首法則

如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列。

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';複製程式碼

EXPLAIN SELECT * FROM employees WHERE position = 'manager';複製程式碼

EXPLAIN SELECT * FROM employees WHERE name = 'ZhangSan';複製程式碼

不在索引列上做任何操作

不在索引列上做任何操作(計算、函數、(自動or手動)型別轉換)，會導致索引失效而轉向全表掃描。

EXPLAIN SELECT * FROM employees WHERE name = 'ZhangSan';EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'ZhangSan';複製程式碼

給hire_time增加一個普通索引:

ALTER TABLE `employees`ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE;複製程式碼

EXPLAIN select * from employees where date(hire_time) ='2020-09-30';複製程式碼

轉化為日期範圍查詢，會走索引:

EXPLAIN select * from employees where hire_time >='2020-09-30 00:00:00' and hire_time <='2020-09-30 23:59:59';複製程式碼

還原最初索引狀態

ALTER TABLE `employees`DROP INDEX `idx_hire_time`;複製程式碼

儲存引擎不能使用索引中範圍條件右邊的列

EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 22 AND position ='manager';EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age > 22 AND position ='manager';複製程式碼

儘量使用覆蓋索引

儘量使用覆蓋索引(只存取索引的查詢(索引列包含查詢列))，減少select *語句。

EXPLAIN SELECT name,age FROM employees WHERE name= 'ZhangSan' AND age = 23 AND position ='manager';複製程式碼

EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 23 AND position ='manager';複製程式碼

mysql在使用不等於(!=或者<>)的時候無法使用索引會導致全表掃描

EXPLAIN SELECT * FROM employees WHERE name != 'ZhangSan';複製程式碼

`is null、is not null` 也無法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null複製程式碼

like以萬用字元開頭('%abc...')mysql索引失效會變成全表掃描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Zhang'複製程式碼

EXPLAIN SELECT * FROM employees WHERE name like 'Zhang%'複製程式碼

問題：解決like'%字串%'索引不被使用的方法?

使用覆蓋索引，查詢欄位必須是建立覆蓋索引欄位

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Zhang%';複製程式碼

如果不能使用覆蓋索引則可能需要藉助搜尋引擎

字串不加單引號索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000'; 
EXPLAIN SELECT * FROM employees WHERE name = 1000;複製程式碼

少用or或in

少用or或in，用它查詢時，mysql不一定使用索引，mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引，詳見範圍查詢優化。

EXPLAIN SELECT * FROM employees WHERE name = 'ZhangSan' or name = 'HanMeimei';複製程式碼

範圍查詢優化

給年齡新增單值索引

ALTER TABLE`employees`ADD INDEX `idx_age` (`age`)USING BTREE;複製程式碼

explain select * from employees where age >=1 and age <=2000;複製程式碼

沒走索引原因：mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子，可能是由於單次資料量查詢過大導致優化器最終選擇不走索引優化方法:可以講大的範圍拆分成多個小範圍。

explain select * from employees where age >=1 and age <=1000;explain select * from employees where age >=1001 and age <=2000;複製程式碼

還原最初索引狀態：

ALTER TABLE `employees`DROP INDEX `idx_age`;複製程式碼

索引使用總結

假設 index(a,b,c)

like KK% 相當於=常數，%KK 和 %KK% 相當於範圍

想了解更多程式設計學習，敬請關注欄目！

以上就是實踐（1）--MySQL效能優化的詳細內容，更多請關注TW511.COM其它相關文章！

實踐（1）--MySQL效能優化

前言