實(shí)踐（1）–MySQL性能優(yōu)化

相關(guān)學(xué)習(xí)推薦：mysql教程

前言

MySQL索引底層數(shù)據(jù)結(jié)構(gòu)與算法
MySQL性能優(yōu)化原理-前篇

前兩篇說(shuō)完了索引底層數(shù)據(jù)結(jié)構(gòu)、性能優(yōu)化原理的基本概念。本篇將講講具體實(shí)踐。分兩篇來(lái)講，這是實(shí)踐的第一篇。對(duì)于一個(gè)以數(shù)據(jù)為中心的應(yīng)用，數(shù)據(jù)庫(kù)的好壞直接影響到程序的性能，因此數(shù)據(jù)庫(kù)性能至關(guān)重要。一般來(lái)說(shuō)，要保證數(shù)據(jù)庫(kù)的效率，要做好以下四個(gè)方面的工作：

數(shù)據(jù)庫(kù)表設(shè)計(jì)
SQL語(yǔ)句優(yōu)化
數(shù)據(jù)庫(kù)參數(shù)配置
恰當(dāng)?shù)挠布Y源和操作系統(tǒng)

此外，使用適當(dāng)?shù)拇鎯?chǔ)過(guò)程，也能提升性能。這個(gè)順序也表現(xiàn)了四個(gè)方面對(duì)性能影響的大小。

數(shù)據(jù)庫(kù)表設(shè)計(jì)

通俗地理解三個(gè)范式，對(duì)于數(shù)據(jù)庫(kù)設(shè)計(jì)大有好處。在數(shù)據(jù)庫(kù)設(shè)計(jì)當(dāng)中，為了更好地應(yīng)用三個(gè)范式，就必須通俗地理解三個(gè)范式。

第一范式：1NF – 確保原子性

是對(duì)屬性的原子性約束，要求屬性（列）具有原子性，不可再分解；（只要是關(guān)系型數(shù)據(jù)庫(kù)都滿足 1NF）

第二范式：2NF – 確保表中每列都和主鍵相關(guān)

是對(duì)記錄的唯一性約束，要求記錄有唯一標(biāo)識(shí)，即實(shí)體的唯一性；

先滿足1NF，然后每張表要有主鍵，并且確保每一列都和主鍵相關(guān)，而不是主鍵的一部分（主要針對(duì)聯(lián)合主鍵）。換言之，一個(gè)表中只保存一種數(shù)據(jù)而不是多種數(shù)據(jù)。

錯(cuò)誤示范：商品訂單信息錯(cuò)誤設(shè)計(jì)

正確示范：商品訂單信息正確設(shè)計(jì)

第三范式：3NF – 確保每列都和主鍵直接相關(guān)，而不是間接相關(guān)

3NF 是對(duì)字段冗余性的約束，它要求字段沒有冗余。

第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。不能傳遞依賴，如非主鍵列A依賴非主鍵列B，非主鍵列B依賴主鍵。

關(guān)鍵字段?-&gt;?非關(guān)鍵字段x?-&gt;?非關(guān)鍵字段y復(fù)制代碼

案例1:

比如在設(shè)計(jì)一個(gè)訂單數(shù)據(jù)表的時(shí)候，可以將客戶編號(hào)作為一個(gè)外鍵和訂單表建立相應(yīng)的關(guān)系。而不可以在訂單表中添加關(guān)于客戶其它信息（比如姓名、所屬公司等）字段。如下這兩個(gè)表所示的設(shè)計(jì)就是一個(gè)滿足第三范式的數(shù)據(jù)庫(kù)表。

案例2:

假定學(xué)生關(guān)系表為 Student?(學(xué)號(hào)、姓名、年齡、所在學(xué)院、學(xué)院地點(diǎn)、學(xué)院電話)，關(guān)鍵字為單一關(guān)鍵字“學(xué)號(hào)”，因?yàn)榇嬖谌缦聸Q定關(guān)系：

（學(xué)號(hào)）-&gt;?(姓名、年齡、所在學(xué)院、學(xué)院地點(diǎn)、學(xué)院電話)復(fù)制代碼

即存在非關(guān)鍵字段“學(xué)院地點(diǎn)”、“學(xué)院電話”對(duì)關(guān)鍵字段“學(xué)號(hào)”的傳遞函數(shù)依賴。它也會(huì)存在數(shù)據(jù)冗余，更新異常、插入異常和刪除異常的情況。正確應(yīng)把學(xué)生關(guān)系表分為如下兩個(gè)表：

學(xué)生：（學(xué)號(hào)、姓名、年齡、所在學(xué)院）
學(xué)院：（學(xué)院、地點(diǎn)、電話）

范式化優(yōu)缺點(diǎn)

范式化的優(yōu)點(diǎn)：

重復(fù)數(shù)據(jù)少，不冗余；
維護(hù)更新快；
范式化的表更小，可在內(nèi)存中運(yùn)行。

范式化的缺點(diǎn)：

查詢的時(shí)候經(jīng)常需要很多關(guān)聯(lián)，增加查詢的代價(jià)。也可能使一些索引策略失效，因?yàn)榉妒交瘜⒘蟹旁诓煌谋碇?，而這些列在一個(gè)表中本可以屬于同一個(gè)索引。

反范式化的優(yōu)缺點(diǎn)

反范式化的優(yōu)點(diǎn)：

避免關(guān)聯(lián)，幾乎所有數(shù)據(jù)可以在一張表中顯示。
可以設(shè)計(jì)有效的索引。

反范式化的缺點(diǎn)：

冗余數(shù)據(jù)多，更小維護(hù)麻煩，刪除數(shù)據(jù)時(shí)也容易丟失重要信息。

數(shù)據(jù)表設(shè)計(jì)的建議

沒有冗余的數(shù)據(jù)庫(kù)設(shè)計(jì)可以做到，但是，沒有冗余的數(shù)據(jù)庫(kù)未必是最好的數(shù)據(jù)庫(kù)，有時(shí)為列提高運(yùn)行效率，就必須降低范式標(biāo)準(zhǔn)，適當(dāng)保留冗余數(shù)據(jù)。具體做法：在概念數(shù)據(jù)模型設(shè)計(jì)時(shí)遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計(jì)時(shí)考慮。降低范式就是增加字段，允許冗余。

另外，《阿里巴巴Java開發(fā)手冊(cè)》，數(shù)據(jù)庫(kù)的表設(shè)計(jì)允許適當(dāng)冗余，以提升SQL查詢的性能，避免表的關(guān)聯(lián)查詢。

適度冗余，減少join的關(guān)聯(lián)

冗余更新頻率不高，但是查詢頻率極高的字段。如訂單中的商品名稱、微博發(fā)帖中的用戶昵稱。

大字段垂直拆分

如把博客列表中的內(nèi)容拆分出去，訪問(wèn)列表的時(shí)候不讀取博客內(nèi)容，為縱深的邏輯關(guān)系。

大表水平拆分

舉例說(shuō)明：在一個(gè)論壇系統(tǒng)里，管理員經(jīng)常會(huì)發(fā)一些帖子，這些帖子要求在每個(gè)分類列表里都要置頂。

設(shè)計(jì)方案一：在發(fā)帖表里增加一列用來(lái)標(biāo)示是否是管理員發(fā)帖，這樣在每個(gè)分類列表展示時(shí)就需要對(duì)發(fā)帖表查詢兩次，一次是置頂帖，一次是普通帖，然后將兩次結(jié)果合并。如果發(fā)帖表內(nèi)容較大時(shí)，查詢置頂帖的性能開銷會(huì)比較大。
設(shè)計(jì)方案二：將置頂帖存放在一個(gè)單獨(dú)的置頂表里。因?yàn)橹庙斕麛?shù)量相比會(huì)很少，但訪問(wèn)頻率很高，這樣從發(fā)帖表里分拆開來(lái)，訪問(wèn)的性能開銷會(huì)少很多。

合適的數(shù)據(jù)類型

如果數(shù)據(jù)量一樣，但數(shù)據(jù)類型更小的話，數(shù)據(jù)存放同樣的數(shù)據(jù)就會(huì)占用更少的空間，這樣檢索同樣的數(shù)據(jù)所帶來(lái)的IO 消耗自然會(huì)降低，性能也就很自然的得到提升。此外，MySQL 對(duì)不同類型的數(shù)據(jù)，處理方式也不一樣，比如在運(yùn)算或者排序操作中，越簡(jiǎn)單的數(shù)據(jù)類型操作性能越高，所以對(duì)于要頻繁進(jìn)行運(yùn)算或者排序的字段盡量選擇簡(jiǎn)單的數(shù)據(jù)類型。

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

SQL優(yōu)化的一般步驟

通過(guò)show status命令了解各種SQL的執(zhí)行頻率；
定位執(zhí)行效率較低的SQL語(yǔ)句-（重點(diǎn)select）；
通過(guò)explain分析低效率的SQL；
確定問(wèn)題并采取相應(yīng)的優(yōu)化措施。

--?select語(yǔ)句分類SelectDml數(shù)據(jù)操作語(yǔ)言(insert?update?delete) dtl?數(shù)據(jù)事物語(yǔ)言(commit?rollback?savepoint)Ddl數(shù)據(jù)定義語(yǔ)言(create?alter?drop..) Dcl(數(shù)據(jù)控制語(yǔ)言)?grant?revoke--?Show?status?常用命令--查詢本次會(huì)話Show?Session?status?like?'com_%';?????//show?session?status?like?'Com_select'--查詢?nèi)諷how?global?status?like?'com_%';--?給某個(gè)用戶授權(quán)grant?all?privileges?on?*.*?to?'abc'@'%';--為什么這樣授權(quán)?'abc'?表示用戶名??'@'?表示host,?查看一下mysql-&gt;user表就知道了--回收權(quán)限r(nóng)evoke?all?on?*.*?from?'abc'@'%';--刷新權(quán)限[也可以不寫]flush?privileges;復(fù)制代碼

SQL語(yǔ)句優(yōu)化-show參數(shù)

MySQL客戶端連接成功后，通過(guò)使用 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。其中的session來(lái)表示當(dāng)前的連接的統(tǒng)計(jì)結(jié)果，global來(lái)表示自數(shù)據(jù)庫(kù)上次啟動(dòng)至今的統(tǒng)計(jì)結(jié)果。默認(rèn)是session級(jí)別的。

show?status?like?'Com_%';復(fù)制代碼

其中， Com_XXX?表示 XXX?語(yǔ)句所執(zhí)行的次數(shù)。重點(diǎn)注意：Com_select,Com_insert,Com_update,Com_delete 通過(guò)這幾個(gè)參數(shù)，可以了解到當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執(zhí)行比例是多少。

還有幾個(gè)常用的參數(shù)便于用戶了解數(shù)據(jù)庫(kù)的基本情況。Connections：試圖連接MySQL服務(wù)器的次數(shù)Uptime：服務(wù)器工作的時(shí)間（單位秒）Slow_queries：慢查詢的次數(shù) (默認(rèn)是慢查詢時(shí)間10s)

show?status?like?'Connections';show?status?like?'Uptime';show?status?like?'Slow_queries';復(fù)制代碼

查詢MySQL的慢查詢時(shí)間

show?variables?like?'long_query_time';復(fù)制代碼

修改MySQL慢查詢時(shí)間

set?long_query_time=2;復(fù)制代碼

SQL語(yǔ)句優(yōu)化-定位慢查詢

上面我們介紹了獲取MySQL數(shù)據(jù)庫(kù)的一些運(yùn)行狀態(tài)是如何查詢

比如當(dāng)前MySQL運(yùn)行的時(shí)間： show status like ‘Uptime’;?
一共執(zhí)行了多少次select/update/delete.. / ： show status like ‘Com_%’;
當(dāng)前連接數(shù)

定位慢查詢

如何從一個(gè)項(xiàng)目中快速定位執(zhí)行速度慢的語(yǔ)句（定位慢查詢）

show?variables?like?'%query%';復(fù)制代碼

slow_query_log 默認(rèn)是off關(guān)閉的，使用時(shí)，需要改為on 打開　　　　　　
slow_query_log_file 記錄的是慢日志的記錄文件
long_query_time 默認(rèn)是10S，每次執(zhí)行的sql達(dá)到這個(gè)時(shí)長(zhǎng)，就會(huì)被記錄

查看慢查詢狀態(tài)

Slow_queries 記錄的是慢查詢數(shù)量? 當(dāng)有一條sql執(zhí)行一次比較慢時(shí)，這個(gè)vlue就是1 （記錄的是本次會(huì)話的慢sql條數(shù)）

show?status?like?'%slow_queries%';復(fù)制代碼

注意：

如何打開慢查詢： SET GLOBAL slow_query_log = ON;

將默認(rèn)時(shí)間改為1S： SET GLOBAL long_query_time = 1;

（設(shè)置完需要重新連接數(shù)據(jù)庫(kù)，PS：僅在這里改的話，當(dāng)再次重啟數(shù)據(jù)庫(kù)服務(wù)時(shí)，所有設(shè)置又會(huì)自動(dòng)恢復(fù)成默認(rèn)值，永久改變需去my.ini中改）

SQL語(yǔ)句優(yōu)化-Explain工具

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語(yǔ)句，分析你的查詢語(yǔ)句或是結(jié)構(gòu)的性能瓶頸在 select 語(yǔ)句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息，而不是執(zhí)行這條SQL。

注意:如果 from 中包含子查詢，仍會(huì)執(zhí)行該子查詢，將結(jié)果放入臨時(shí)表中

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);復(fù)制代碼

explain?select?*?from?actor;復(fù)制代碼

查詢中的每個(gè)表會(huì)輸出一行，如果有兩個(gè)表通過(guò)join連接查詢，那么會(huì)輸出兩行。每一列具體的說(shuō)明在后面進(jìn)行說(shuō)明。

Explain 兩個(gè)變種

explain extended

會(huì)在 explain 的基礎(chǔ)上額外提供一些查詢優(yōu)化的信息。緊隨其后通過(guò) show warnings 命令可以得到優(yōu)化后的查詢語(yǔ)句，從而看出優(yōu)化器優(yōu)化了什么。額外還有 filtered 列，是一個(gè)半分比的值，rows * filtered/100 可以估算出將要和 explain 中前一個(gè)表進(jìn)行連接的行數(shù)(前一個(gè)表指 explain 中的id值比當(dāng)前表id值小的表)。

explain?extended?select?*?from?film?where?id?=?1;復(fù)制代碼

show?warnings;復(fù)制代碼

explain partitions

相比 explain 多了個(gè) partitions 字段，如果查詢是基于分區(qū)表的話，會(huì)顯示查詢將訪問(wèn)的分區(qū)。

Explain中的列

接下來(lái)我們將展示 explain 中每個(gè)列的信息。

id 列

id 列的編號(hào)是 select 的序列號(hào)，有幾個(gè) select 就有幾個(gè) id，并且id的順序是按 select 出現(xiàn)的順序遞增的。id列越大執(zhí)行優(yōu)先級(jí)越高，id相同則從上往下執(zhí)行，id為 NULL 最后執(zhí)行。

select_type 列

select_type 表示對(duì)應(yīng)行是簡(jiǎn)單還是復(fù)雜的查詢。

simple ：簡(jiǎn)單查詢。查詢不包含子查詢和union

explain?select?*?from?film?where?id?=?2;復(fù)制代碼

primary ：復(fù)雜查詢中最外層的 select
subquery ：包含在 select 中的子查詢（不在 from 子句中）
derived ：包含在 from 子句中的子查詢。MySQL 會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中，也稱為派生表（derived的英文含義）

用下面這個(gè)例子來(lái)了解 primary、subquery、derived類型。

explain?select?(select?1?from?actor?where?id?=?1)?from?(select?*?from?film?where?id?=?1)?der;復(fù)制代碼

未關(guān)閉MySQL5.7新特性對(duì)衍生表的合并優(yōu)化，如下：

#關(guān)閉mysql5.7新特性對(duì)衍?生表的合并優(yōu)化set?session?optimizer_switch='derived_merge=off';? 復(fù)制代碼

#還原默認(rèn)配置set?session?optimizer_switch='derived_merge=on';? 復(fù)制代碼

union ：在 union 中的第二個(gè)和隨后的 select

explain?select?1?union?all?select?1;復(fù)制代碼

table 列

這一列表示 explain 的一行正在訪問(wèn)哪個(gè)表。當(dāng) from 子句中有子查詢時(shí)，table列是 ?格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。當(dāng)有 union 時(shí)，UNION RESULT 的 table 列的值為，1和2表示參與 union 的 select 行id。

type 列

這一列表示關(guān)聯(lián)類型或訪問(wèn)類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。

依次從最優(yōu)到最差分別為：

system?&gt;?const?&gt;?eq_ref?&gt;?ref?&gt;?range?&gt;?index?&gt;?ALL復(fù)制代碼

一般來(lái)說(shuō)，得保證查詢達(dá)到 range 級(jí)別，最好達(dá)到 ref。

NULL：MySQL 能夠在優(yōu)化階段分解查詢語(yǔ)句，在執(zhí)行階段用不著再訪問(wèn)表或索引。例如：在索引列中選取最小值，可以單獨(dú)查找索引來(lái)完成，不需要在執(zhí)行時(shí)訪問(wèn)表。

explain?select?min(id)?from?film;復(fù)制代碼

system、const ：MySQL 能對(duì)查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量（可以看 show warnings 的）。用于 primary key 或 unique key 的所有列與常數(shù)比較時(shí)，所以表最多有一個(gè)匹配行，讀取1次，速度比較快。system 是 const 的特例，表里只有一條元組匹配時(shí)為 system。

explain?extended?select?*?from?(select?*?from?film?where?id?=?1)?tmp;復(fù)制代碼

show?warnings;復(fù)制代碼

eq_ref ：primary key 或 unique key 索引的所有部分被連接使用，最多只會(huì)返回一條符合條件的記錄。這可能實(shí)在 const 之外最好的連接類型了，簡(jiǎn)單的 select 查詢不會(huì)出現(xiàn)這種 type。

explain?select?*?from?film_actor?left?join?film?on?film_actor.film_id?=?film.id;復(fù)制代碼

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

（1）簡(jiǎn)單 select 查詢，name 是普通索引（非唯一索引）

explain?select?*?from?film?where?name?=?'film1';復(fù)制代碼

（2）關(guān)聯(lián)表查詢， idx_film_actor_id?是 film_id 和 actor_id 的聯(lián)合索引，這里使用到了 film_actor 的左邊前綴 film_id 部分。

?explain?select?film_id?from?film?left?join?film_actor?on?film.id?=?film_actor.film_id;復(fù)制代碼

range ：范圍掃描通常出現(xiàn)在 in()、betwwen、>、= 等操作中。使用一個(gè)索引來(lái)檢索給定范圍的行。

explain?select?*?from?actor?where?id?&gt;?1;復(fù)制代碼

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

explain?select?*?from?film;復(fù)制代碼

ALL ：即全表掃描，意味著MySQL需要從頭到尾去查找所需要的行。通常情況下這需要增加索引來(lái)進(jìn)行優(yōu)化了。

explain?select?*?from?actor復(fù)制代碼

possible_keys 列

這一列顯示查詢可能使用哪些索引來(lái)查找。 explain 時(shí)可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引對(duì)此查詢幫助不大，選擇了全表查詢。如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過(guò)檢查 where 子句看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺?lái)提高查詢性能，然后用 explain 查看效果。

key 列

這一列顯示mysql實(shí)際采用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的訪問(wèn)。如果沒有使用索引，則該列是 NULL。如果想強(qiáng)制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。

key_len 列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過(guò)這個(gè)值可以算出具體使用了索引中的哪些列。舉例來(lái)說(shuō)，film_actor的聯(lián)合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個(gè)int列組成，并且每個(gè)int是4字節(jié)。通過(guò)結(jié)果中的key_len=4可推斷出查詢使用了第一個(gè)列:film_id列來(lái)執(zhí) 行索引查找。

explain?select?*?from?film_actor?where?film_id?=?2;復(fù)制代碼

key_len計(jì)算規(guī)則如下: 字符串

char(n):n字節(jié)長(zhǎng)度
varchar(n):2字節(jié)存儲(chǔ)字符串長(zhǎng)度，如果是utf-8，則長(zhǎng)度 3n +2

數(shù)值類型

tinyint:1字節(jié)
smallint:2字節(jié)
int:4字節(jié)
bigint:8字節(jié)

時(shí)間類型

date:3字節(jié)
timestamp:4字節(jié)
datetime:8字節(jié)

如果字段允許為 NULL，需要1字節(jié)記錄是否為 NULL

索引最大長(zhǎng)度是768字節(jié)，當(dāng)字符串過(guò)長(zhǎng)時(shí)，mysql會(huì)做一個(gè)類似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。

ref 列

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

rows 列

這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)。

Extra 列

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

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

explain?select?film_id?from?film_actor?where?film_id?=?1;復(fù)制代碼

（2）Using where ：使用 where 語(yǔ)句來(lái)處理結(jié)果，查詢的列未被索引覆蓋

explain?select?*?from?actor?where?name?=?'a';復(fù)制代碼

（3）Using index condition ：查詢的列不完全被索引覆蓋，where 條件中是一個(gè)前導(dǎo)列的范圍

explain?select?*?from?film_actor?where?film_id?&gt;?1;復(fù)制代碼

（4）Using temporary ：MySQL 需要?jiǎng)?chuàng)建一張臨時(shí)表來(lái)處理查詢。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想到用索引來(lái)優(yōu)化。

actor.name沒有索引，此時(shí)創(chuàng)建了張臨時(shí)表來(lái)distinct

explain?select?distinct?name?from?actor;復(fù)制代碼

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

explain?select?distinct?name?from?film;復(fù)制代碼

（5）Using filesort ：將用外部排序而不是索引排序，數(shù)據(jù)較小時(shí)從內(nèi)存排序，否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來(lái)優(yōu)化的。

actor.name沒有索引，會(huì)瀏覽 actor 整個(gè)表，保存排序關(guān)鍵字 name 和對(duì)應(yīng)的 id，然后排序 name 并檢索行記錄。

explain?select?*?from?actor?order?by?name;復(fù)制代碼

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

explain?select?*?from?film?order?by?name;復(fù)制代碼

（6）Select tables optimized away ：使用某些聚合函數(shù)(比如 max、min)來(lái)訪問(wèn)存在索引的某個(gè)字段是

explain?select?min(id)?from?film;復(fù)制代碼

SQL語(yǔ)句優(yōu)化-索引最佳實(shí)踐

#?示例表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?'入職時(shí)間', 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());復(fù)制代碼

全值匹配

EXPLAIN?SELECT?*?FROM?employees?WHERE?name=?'ZhangSan';復(fù)制代碼

EXPLAIN?SELECT?*?FROM?employees?WHERE?name=?'ZhangSan'?AND?age?=?22;復(fù)制代碼

EXPLAIN?SELECT?*?FROM?employees?WHERE?name=?'ZhangSan'?AND?age?=?22?AND?position?='manager';復(fù)制代碼

最左前綴法則

如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過(guò)索引中的列。

EXPLAIN?SELECT?*?FROM?employees?WHERE?age?=?22?AND?position?='manager';復(fù)制代碼

EXPLAIN?SELECT?*?FROM?employees?WHERE?position?=?'manager';復(fù)制代碼

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'ZhangSan';復(fù)制代碼

不在索引列上做任何操作

不在索引列上做任何操作(計(jì)算、函數(shù)、(自動(dòng)or手動(dòng))類型轉(zhuǎn)換)，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'ZhangSan';EXPLAIN?SELECT?*?FROM?employees?WHERE?left(name,3)?=?'ZhangSan';復(fù)制代碼

給hire_time增加一個(gè)普通索引:

ALTER?TABLE?`employees`ADD?INDEX?`idx_hire_time`?(`hire_time`)?USING?BTREE;復(fù)制代碼

EXPLAIN?select?*?from?employees?where?date(hire_time)?='2020-09-30';復(fù)制代碼

轉(zhuǎn)化為日期范圍查詢，會(huì)走索引:

EXPLAIN?select?*?from?employees?where?hire_time?&gt;='2020-09-30?00:00:00'?and?hire_time?<figure><img alt="image.png" class="lazyload" src="https://img.php.cn/upload/article/000/000/052/4b3874ac0cd1cb8e2477d436392a5226-41.png" data-   style="max-width:90%" data- style="max-width:90%"><figcaption></figcaption></figure><p>還原最初索引狀態(tài)</p><pre class="brush:php;toolbar:false;">ALTER?TABLE?`employees`DROP?INDEX?`idx_hire_time`;復(fù)制代碼

存儲(chǔ)引擎不能使用索引中范圍條件右邊的列

EXPLAIN?SELECT?*?FROM?employees?WHERE?name=?'ZhangSan'?AND?age?=?22?AND?position?='manager';EXPLAIN?SELECT?*?FROM?employees?WHERE?name=?'ZhangSan'?AND?age?&gt;?22?AND?position?='manager';復(fù)制代碼

盡量使用覆蓋索引

盡量使用覆蓋索引(只訪問(wèn)索引的查詢(索引列包含查詢列))，減少select *語(yǔ)句。

EXPLAIN?SELECT?name,age?FROM?employees?WHERE?name=?'ZhangSan'?AND?age?=?23?AND?position?='manager';復(fù)制代碼

EXPLAIN?SELECT?*?FROM?employees?WHERE?name=?'ZhangSan'?AND?age?=?23?AND?position?='manager';復(fù)制代碼

mysql在使用不等于(!=或者)的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?!=?'ZhangSan';復(fù)制代碼

is null、is not null 也無(wú)法使用索引

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?is?null復(fù)制代碼

like以通配符開頭(‘%abc…’)mysql索引失效會(huì)變成全表掃描操作

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?like?'%Zhang'復(fù)制代碼

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?like?'Zhang%'復(fù)制代碼

問(wèn)題：解決like’%字符串%’索引不被使用的方法?

使用覆蓋索引，查詢字段必須是建立覆蓋索引字段

EXPLAIN?SELECT?name,age,position?FROM?employees?WHERE?name?like?'%Zhang%';復(fù)制代碼

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

字符串不加單引號(hào)索引失效

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'1000';? EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?1000;復(fù)制代碼

少用or或in

少用or或in，用它查詢時(shí)，mysql不一定使用索引，mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引，詳見范圍查詢優(yōu)化。

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'ZhangSan'?or?name?=?'HanMeimei';復(fù)制代碼

范圍查詢優(yōu)化

給年齡添加單值索引

ALTER?TABLE`employees`ADD?INDEX?`idx_age`?(`age`)USING?BTREE;復(fù)制代碼

explain?select?*?from?employees?where?age?&gt;=1?and?age?<figure><img alt="image.png" class="lazyload" src="https://img.php.cn/upload/article/000/000/052/7850e52c746bccf9509aa2e315e12575-52.png" data-   style="max-width:90%" data- style="max-width:90%"><figcaption></figcaption></figure><p><strong>沒走索引原因</strong>：mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引。比如這個(gè)例子，可能是由于單次數(shù)據(jù)量查詢過(guò)大導(dǎo)致優(yōu)化器最終選擇不走索引 優(yōu)化方法:可以講大的范圍拆分成多個(gè)小范圍。</p><pre class="brush:php;toolbar:false;">explain?select?*?from?employees?where?age?&gt;=1?and?age?=1001?and?age?<figure><img alt="image.png" class="lazyload" src="https://img.php.cn/upload/article/000/000/052/7850e52c746bccf9509aa2e315e12575-53.png" data-   style="max-width:90%" data- style="max-width:90%"><figcaption></figcaption></figure><p>還原最初索引狀態(tài)：</p><pre class="brush:php;toolbar:false;">ALTER?TABLE?`employees`DROP?INDEX?`idx_age`;復(fù)制代碼

索引使用總結(jié)

假設(shè) index(a,b,c)

like KK% 相當(dāng)于=常量，%KK 和 %KK% 相當(dāng)于范圍

想了解更多編程學(xué)習(xí)，敬請(qǐng)關(guān)注php培訓(xùn)欄目！

? 版權(quán)聲明

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。

THE END

數(shù)據(jù)庫(kù)
# 數(shù)據(jù)庫(kù)# php # mysql # sql # 性能優(yōu)化 # 字符串 # Java # 算法 # select # 數(shù)據(jù)結(jié)構(gòu)# 數(shù)據(jù)類型 # delete # table # NULL # 搜索引擎 # int # Session # date # 類型轉(zhuǎn)換 # 常量 # timestamp # char # union # using # 值類型 # const