建站
咨詢
售后
建站咨詢
新聞中心
目錄
- 前言
- 初步了解索引
- 要調(diào)優(yōu) SQL，怎么能不認(rèn)識 explain
- 重點!SQL 優(yōu)化
一、前言
因為筆者現(xiàn)在工作中用的存儲引擎大多是 InnoDB，所以本文基于 InnoDB，數(shù)據(jù)庫版本MySQL 5.7為前提寫的。我們平常說的 SQL 優(yōu)化，基本上就是對索引的優(yōu)化。這里既然重點是 SQL 優(yōu)化，所以我們得先了解索引，然后了解下我們分析 SQL 的工具 explain，最后才能到優(yōu)化。這也是本文的大綱順序。
創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的遷安網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
了解 SQL 優(yōu)化之前，有幾個概念需要先知道：
- MySQL 索引的數(shù)據(jù)結(jié)構(gòu)
B+Tree，是 M 階搜索樹。現(xiàn)在以主鍵索引為例，非葉子節(jié)點會冗余我們的主鍵排序并構(gòu)成樹結(jié)構(gòu)(非葉子節(jié)點不會存儲數(shù)據(jù));葉子節(jié)點會存儲數(shù)據(jù)，并且葉子節(jié)點會形成一個雙向鏈表，值得注意的是首尾節(jié)點也有指針互相指向。(具體可以看：https://segmentfault.com/a/1190000008545713?utm_source=sf-related)
- 聚簇索引
葉子節(jié)點存儲索引對應(yīng)的 record信息。
- 非聚簇索引
葉子節(jié)點只存儲主鍵數(shù)據(jù)，所以要查詢索引以外的數(shù)據(jù)需要回表。
- 回表
走非聚簇索引得到主鍵數(shù)據(jù)后，根據(jù)主鍵再走一次聚簇索引那里查詢列需要的數(shù)據(jù)。
- 優(yōu)化器
優(yōu)化器是MySQL 眾多組件中的一個，它會對我們的 SQL 進(jìn)行分析，看預(yù)計使用哪些索引，SQL 的執(zhí)行順序如何，實際會使用哪些索引(沒有真的執(zhí)行 SQL，執(zhí)行 SQL 是存儲引擎去進(jìn)行讀寫的)，使用索引的情況等等。
二、初步了解索引
需要知道使用 InnoDB 的表肯定有一個聚簇索引(有且僅有一個)，使用的數(shù)據(jù)結(jié)構(gòu)是 B+Tree。
*.frm：數(shù)據(jù)表結(jié)構(gòu)相關(guān)信息存儲的文件
*.idb：索引和數(shù)據(jù)存儲的文件
注意：*.idb 這個文件本身就是 B+Tree 的文件，葉子節(jié)點包含完整的數(shù)據(jù)記錄。
下面以主鍵索引為例(我的user表就只有三個字段)
為什么大廠的DBA都建議InnoDB表建自增整型主鍵?
- 主鍵(不會重復(fù))
如果我們沒有主鍵，MySQL會使用我們表從第一列開始選擇一列所有元素都不相等的列構(gòu)建B+Tree，假設(shè)我們不存在符合這個要求的列，MySQL會自己為我們創(chuàng)建一個符合這個條件的隱藏列構(gòu)建索引。像這種開銷沒必要花費，我們自己建表時，直接處理可以。
- 自增
維護(hù)B+Tree時，更容易，性能更好。
- 整型
查詢范圍時，整型比較大小更簡單;整型占用空間更小，節(jié)約空間，事實上公司一般都會要求明確字段大小，過大字段，DBA一般都會要求開發(fā)解釋為什么要這么大，當(dāng)然從存儲數(shù)據(jù)量角度來看，索引也是越小越好。
二級索引
二級索引是非聚集的，主要是為了節(jié)約空間。二級索引是先找到主鍵，通過主鍵回表找到真正的數(shù)據(jù)行。
聯(lián)合索引(復(fù)合索引)
假如現(xiàn)在我有個用戶表有4個字段：username、telephone、age、sex。
我們可以建兩種類型的聯(lián)合索引：聯(lián)合主鍵，普通的聯(lián)合索引。
聯(lián)合主鍵
現(xiàn)在我用 username、sex 構(gòu)建成聯(lián)合主鍵，維護(hù)索引如下：
普通的聯(lián)合索引
這個和上面的差不多，只是 data 存的是主鍵，需要回表查找。
最左匹配原則：
以上圖為例子，先根據(jù)名字轉(zhuǎn)成的ascii碼進(jìn)行排序，如果 ascii 碼一樣，那么再根據(jù)性別的 ascii 碼大小比較排序。只有 username 的索引生效了，sex 的索引才有可能生效。要證明也很容易：如果沒有匹配 username，直接匹配 sex，單看 sex 的話，我們索引的排序是無序的，就沒法使用二分法了，所以不走索引。
講了索引的數(shù)據(jù)結(jié)構(gòu)，以及生效的情況，那么接下來就要看看如何 SQL 優(yōu)化了。但是在此之前，我們要先了解下 explain 。
三、要調(diào)優(yōu) SQL，怎么能不認(rèn)識 explain
使用 explain 可以模擬優(yōu)化器執(zhí)行 SQL，分析 SQL，看看能否優(yōu)化。
explain 標(biāo)識的 SQL 不會真的執(zhí)行，只是返回執(zhí)行計劃。如果 from 中包含子查詢，仍會執(zhí)行該子查詢，子查詢的結(jié)果將會放在臨時表中。
explain 分析的 SQL 中，每查詢一個表就會有一行記錄。
更多內(nèi)容請參考官方文檔：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
3.1 explain 中各列的含義
了解每一列的意義，掌握最常用那幾列。
3.1.1 id
id 列的編號是 select 的序列號，查幾個表就有幾個 id，并且 id 值越大執(zhí)行優(yōu)先級越高。如果 id 值相同，就從上往下執(zhí)行，最后執(zhí)行 id 為 null 的。
3.1.2 select_type
查詢類型。
- primary
簡單查詢。查詢不包含子查詢和union。
- subquery
復(fù)雜查詢中最外層的 select。
- derived
包含在 from 子句中的子查詢。MySQL會將結(jié)果存放在一個臨時表中，也稱為派生表(derived的英文含義)。如下：
#關(guān)閉mysql5.7新特性對衍生表的合并優(yōu)化
set session optimizer_switch='derived_merge=off';
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
? union
其實就是使用了 union 關(guān)鍵字后面的查詢，如下：
3.1.3 table
表示這一列使用的是哪一張表。
當(dāng) from 子句中有子查詢時，table列是格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。如下圖：
當(dāng)有 union 時，UNION RESULT 的 table 列的值為，1和2表示參與 union 的 select 行id。
3.1.4 partitions
使用的哪個分區(qū)，需要結(jié)合表分區(qū)才可以看到。因為我的例子都是沒有分區(qū)的，所以是 null。
3.1.5 type
關(guān)聯(lián)類型或者訪問類型。一般要保證查詢達(dá)到 range 級別，最好達(dá)到 ref。
從最優(yōu)到最差：system > const > eq_ref > ref > range > index > ALL。
- system, const
const 是 MySQL 能對查詢的某部分轉(zhuǎn)成一個常量，如下:
而 system 是 conts 的一個特例，當(dāng)表里只有一條記錄時，匹配時為 system。
- eq_ref
使用了主鍵字段或者唯一索引字段進(jìn)行關(guān)聯(lián)，最多只會返回一條符合條件的記錄時，等級為 eq_ref。
explain select * from film_actor left join film on film_actor.film_id = film.id
- ref
相較于 eq_ref，它使用的是普通索引或者唯一索引的部分前綴，可能會找到多條符合條件的記錄。
- range
范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個索引來檢索給定范圍的行。
explain select * from actor where id > 1;
- index
這種一般是通過掃描某個二級索引的所有葉子節(jié)點(其實就是應(yīng)該做全表掃描，但是這里利用了B+Tree的葉子節(jié)點是鏈表的特性遍歷)。這種方式，雖然比較慢，但是用覆蓋索引優(yōu)化，性能上還是要比全表掃描(ALL)要好的，因為它占用空間小，一次IO可以讀更多數(shù)據(jù)。
- ALL
這個級別沒啥好說的，就是我們常說的全表掃描。
3.1.6 possible_keys
顯示可能會使用的索引。
3.1.7 key
實際會使用的索引。
3.1.8 key_len
通過這個值，可以推算出使用到索引的哪些列(一般針對聯(lián)合索引使用多些)，舉個例子：
film_actor 的聯(lián)合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個 int 列組成，并且每個 int 是4字節(jié)。通過結(jié)果中的 key_len=4 可推斷出查詢使用了第一個列：film_id列來執(zhí)行索引查找。
explain select * from film_actor where film_id = 2;
key_len計算規(guī)則如下：
- 字符串：char(n) 和 varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個數(shù)字或字母占1個字節(jié)，一個漢字占3個字節(jié)
– char(n)：如果存漢字長度就是 3n 字節(jié)
– varchar(n)：如果存漢字則長度是 3n + 2 字節(jié)，加的2字節(jié)用來存儲字符串長度，因為varchar是變長字符串
- 數(shù)值類型
– tinyint：1字節(jié)
– smallint：2字節(jié)
– int：4字節(jié)
– bigint：8字節(jié)
- 時間類型
– date：3字節(jié)
– timestamp：4字節(jié)
– datetime：8字節(jié)
- 如果字段允許為 NULL，需要1字節(jié)記錄是否為 NULL。索引最大長度是768字節(jié)，當(dāng)字符串過長時，MySQL會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引。
3.1.9 ref
這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const(常量)，字段名(例：film.id)。
3.1.10 rows
這一列是MySQL估計要讀取并檢測的行數(shù)，注意這個不是結(jié)果集里的行數(shù)。
3.1.11 filtered
通過過濾條件之后對比總數(shù)的百分比。
3.1.12 Extra
這一列展示的是額外信息。常見的重要值如下：
- Using index
使用覆蓋索引。覆蓋索引其實就是查詢列是索引字段，這樣就能避免回表，提高性能。因此，我們覆蓋索引針對的是輔助索引。
- Using where
使用 where 語句處理結(jié)果，并且查詢列未被索引覆蓋。如下：
explain select * from actor where name = 'a';
- Using index condition
查詢的列沒被索引完全覆蓋， where 條件中是一個前導(dǎo)列的范圍。
explain select * from film_actor where film_id > 1;
- Using temporary
創(chuàng)建臨時表來處理查詢
(1)actor.name沒有索引，此時創(chuàng)建了張臨時表來distinct。
explain select distinct name from actor;
(2)film.name建立了idx_name索引，此時查詢時extra是using index,沒有用臨時表。
explain select distinct name from film;
- Using filesort
使用外部排序而不是索引排序，數(shù)據(jù)量較小時使用內(nèi)存，否則會使用磁盤。
(1)actor.name未創(chuàng)建索引，會瀏覽actor整個表，保存排序關(guān)鍵字name和對應(yīng)的id，然后排序name并檢索行記錄。
explain select * from actor order by name;
(2)film.name建立了idx_name索引，此時查詢時extra是using index。
explain select * from film order by name;
Using filesort 原理詳解：
– 單路排序
一次性取出滿足條件的所有字段，然后在 sort buffer 中排序。用 trace 工具可以看到 sort_mode 信息里顯示 或者 < sort_key, packed_additional_fields>
– 雙路排序(回表排序)
先根據(jù)條件獲取相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行ID，然后在 sort buffer 中排序，最后回表獲取完整記錄。用 trace 工具可以看到 sort_mode 信息里顯示 。
– MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) 的大小和需要查詢的字段總大小來判斷使用哪種排序模式。
- 如果字段的總長度小于 max_length_for_sort_data，那么使用單路排序。
- 如果字段的總長度大于 max_length_for_sort_data，那么使用雙路排序。
- Select tables optimized away
使用某些聚合函數(shù)(比如 max、min)來訪問存在索引的某個字段時為 Select tables optimized away。
四、重點!SQL 優(yōu)化
極端點說，SQL 優(yōu)化就是對索引的優(yōu)化。因此，我們要看下各種情況下，如何優(yōu)化索引。
在我看來，SQL優(yōu)化分以下幾種情況：
1.可以走索引
- 應(yīng)該走索引，但是沒走
- 走索引了，但是沒到最優(yōu)(explain 分析，type 一般我們要求至少到達(dá) range 這個級別)
- order by 和 group by 優(yōu)化
2.沒法走索引(客觀現(xiàn)實上的)或者 type 是 index，而且數(shù)據(jù)量大
- 了解適用索引的情況，請不要只有面試時會說，工作就不知道了(數(shù)據(jù)量不大，直接查沒事;大的話，考慮引進(jìn)其他技術(shù)解決，如 :Redis, MongoDB, elasticsearch等)
3.小表驅(qū)動大表
4.count 查詢優(yōu)化
5.如何建索引
- 該在哪個字段建索引
- 哪些字段要使用聯(lián)合索引
- 表字段的設(shè)計(數(shù)據(jù)類型，大小)
Note : 單個索引生不生效，怎么處理還是比較簡單的，所以下面只針對聯(lián)合索引做分析。
下面先建表和造數(shù)據(jù)：
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=100004 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
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 COMMENT='演員表';
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='電影表';
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 COMMENT='電影演員中間表';
其中員工表插入了10W+數(shù)據(jù)。
4.1 常見的應(yīng)該走索引，但是沒走
- 聯(lián)合索引第一個字段不能過濾大部分?jǐn)?shù)據(jù)，導(dǎo)致回表效率低，走全表掃描的 cost 更小。
explain SELECT * FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
當(dāng)然我們也可以選擇強(qiáng)制走索引，如下：
explain SELECT * FROM `employees` force index(idx_name_age_position) where `name` like "sai%" and age = 22 and position = 'dev';
不過，走索引一定性能就更好嗎?我們試驗下。
-- 關(guān)閉查詢緩存
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = 0;
-- 耗時 0.064s
SELECT * FROM `employees` WHERE `name` LIKE "sai%" AND age = 22 AND position = 'dev';
-- 耗時 0.079s
SELECT * FROM `employees` force index(idx_name_age_position) where `name` like "sai%" and age = 22 and position = 'dev';
別看我這差距不大，我這只是表列不多，字段不大，數(shù)據(jù)量也不算太多，所以差距不大，如果表更大的話，差距就會比較明顯了。實際工作中，我們很難確定走索引的 cost 就一定小于全表掃描的。因此，我們一般不強(qiáng)制走索引。
優(yōu)化方案：
我想讓 MySQL自己去走索引，而不是我強(qiáng)制走索引。怎么辦呢?其實上面已經(jīng)提到了，這里是因為第一個字段過濾不多，導(dǎo)致回表效率低。既然如此，我們讓它不回表不就好了嗎?使用覆蓋索引優(yōu)化，就是我們查詢列的字段都是使用的這個索引樹上建了索引的字段，這樣就不需要回表了。如下：
explain SELECT id,`name`,age,position FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
-- 耗時 0.051s
SELECT id,`name`,age,position FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
擴(kuò)展：
- 我們使用 in 和 or 時，有時走索引，有時不走，其實是因為 MySQL 判斷走索引的cost不如全表掃描的。
- 我們這里用了 like 'keyword%'，這里涉及到一個概念叫索引下推。其實就是，MySQL 5.6 之前，對于以下的SQL，如果是走索引的話，它會先根據(jù) name 過濾得到主鍵，進(jìn)行回表拿到數(shù)據(jù)后，再去對比 age 和 position。MySQL 5.6 對此進(jìn)行了優(yōu)化——索引下推，根據(jù) name 過濾后，不先回表，而是直接去對比 age 和 position，最后得到的主鍵才回表查數(shù)據(jù)。注意：1、索引下推只用于二級索引;2、不是 like 'keyword%' 就一定使用索引下推。
SELECT * FROM `employees` WHERE `name` LIKE "sai%" AND age = 22 AND position = 'dev';
- 分頁不走索引
分頁查詢，系統(tǒng)十分常見的查詢，建議大家學(xué)習(xí)完后，趕緊看下自己負(fù)責(zé)的分頁功能是否走索引了，或者是否走了索引但是還能優(yōu)化。以下，看例子來說一些優(yōu)化手段。
select * from employees limit 10000, 10;
這 SQL 其實是去了10010條記錄出來，然后再舍棄前面的一萬條。因此數(shù)據(jù)量大的話，其實效率是十分低的。
一些優(yōu)化方案：
1.和產(chǎn)品同事商量，給一些一定有的查詢條件或者隱藏的查詢條件，給這些條件使用上索引。
這個方案是最簡單并且直接的。
2.像我這里記錄的id是連續(xù)且自增的情況下：
explain select * from employees where id > 10000 limit 10;
屬于取巧，通過主鍵索引使用 where 直接篩選掉前面10000條記錄。
缺點：
(1) 如果 id 不是連續(xù)且自增，那么這種方式就不行。
(2)不是使用主鍵排序，這種情況也不行。
3.非主鍵排序，不用ID連續(xù)自增也能生效。
-- 0.085s
select * from employees order by `name` desc limit 10000, 10;
- 首先想到覆蓋索引優(yōu)化，看看能否這樣干
explain select `name`, age, position from employees order by `name` desc limit 10000, 10;
-- 0.077s
select `name`, age, position from employees order by `name` desc limit 10000, 10;
擴(kuò)展：
-- 我們常認(rèn)為 like 以通配符開頭，索引會失效，但其實也可以通過覆蓋索引，讓索引生效。
explain select `name`, age, position from employees where `name` like '%sai%';
- 不能使用覆蓋索引，用了非主鍵排序，全表掃描的原因：MySQL 5.6~5.7 版本的優(yōu)化器認(rèn)為走二級索引再回表的效率不如全表掃描，這時是不會走索引的(但是也有例外，select * from employees order by name desc limit 10 就會走索引，因為只需要拿10條記錄，這數(shù)量足夠小，具體可以看這個博客，寫得很好：https://www.cnblogs.com/25lH/p/11010095.html)。
解決方案如下：
（1）
explain select e.* from employees e inner join (select id from employees order by `name` desc limit 10000, 10) t on t.id = e.id;
-- 0.045s
select e.* from employees e inner join (select id from employees order by `name` desc limit 10000, 10) t on t.id = e.id;
這里其實就是利用了二級索引，拿到了10010條數(shù)據(jù)，并且按照 name 排好序，由于這里的子查詢只要 id，所以不需要回表，然后再通過 join 就能利用主鍵索引快速拿到記錄。
（2）當(dāng)然除了這種方式，我們也可以強(qiáng)制走索引，因為我們知道這里二級索引只有一個，并且 name 是前導(dǎo)列，所以我這個案例走索引性能肯定比全表掃描好。因此，我們也可以選擇強(qiáng)制走索引。
-- 0.011s
select * from employees force index(idx_name_age_position) order by `name` desc limit 10000, 10;
- 不符合最左原則
我們索引之所以可以幫我們快速找到目標(biāo)數(shù)據(jù)，是因為它的數(shù)據(jù)結(jié)構(gòu)的特點。其中有序這一特征十分重要，如果不滿足，那么肯定是不會走索引的(具體原因要回到平衡二叉查找樹，再到二分法。因為不是這里的重點，所以不展開講)。
- 在索引列上做了以下操作：
– 對索引列是用了函數(shù)
– 對索引列做了類型轉(zhuǎn)換
-- 類型轉(zhuǎn)換會有特例，當(dāng)我們轉(zhuǎn)成日期范圍查詢時，有可能走索引。
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <
='2018‐09‐30 23:59:59';
- 根據(jù)查詢條件過濾的數(shù)據(jù)不多，導(dǎo)致優(yōu)化器認(rèn)為走索引不如全表掃描。
其實第一個案例已經(jīng)涉及到了，但是這里針對的是不等于， not in, not exists, <, >, is null, is not null 等等，這些能匹配到多條記錄的寫法。
4.2 order by 和 group by 優(yōu)化
排序和分組的優(yōu)化其實是十分像的，本質(zhì)是先排序后分組，遵循索引創(chuàng)建順序的最左匹配原則。因此，這里以排序為例。
??https://www.cnblogs.com/25-lH/p/11010095.html??這個博客有講到無查詢條件的排序的案例，我這里就直接上圖了，如下：
接下來寫的都是有查詢條件的情況。
explain select * from employees where `name` = 'sai999' and position = 'dev' order by age;
-- 這里沒有走索引，是因為不符合最左原則，跳過了 age
explain select * from employees where `name` = 'sai999' order by position;
-- 這樣就會走索引了，排序了
explain select * from employees where `name` = 'sai999' order by age, 1position;
-- 又不走索引了，因為 age 和 position 順序顛倒了，不符合我們索引的順序
explain select * from employees where `name` = 'sai999' order by position, age;
-- 修改成這樣，就又可以走索引了，因為 age 是個常量了，所以在排序中被優(yōu)化，沒有和索引順序沖突
explain select * from employees where `name` = 'sai999' and age = 999 order by position, age;
-- 這里雖然符合索引順序，但是 age 是升序，而 position 是降序，所以不走索引。聽說 MySQL 8 支持這種查詢方式，我沒安裝8就不測試了
explain select * from employees where `name` = 'sai999' order by age asc, position desc;
-- 想想我們聯(lián)合索引的 B+Tree 數(shù)據(jù)結(jié)構(gòu)，當(dāng) name 有兩個值時，得出的結(jié)果集對于 age, position 而言是無序的，所以沒法走索引
explain select * from employees where `name` in ('sai100', 'sai101') order by age, position;
-- 可以使用覆蓋索引優(yōu)化
explain select `name`, age, position from employees where `name` > 'a' order by `name`;
MySQL 支持兩種排序方式 filesort 和 index， Using index 是掃描索引完成的排序，而 Using filesort 是利用內(nèi)存甚至磁盤完成排序的。因此，index 效率高，filesort 效率低。
4.3 小表驅(qū)動大表
當(dāng)我們做多表關(guān)聯(lián)查詢時，常常會聽到小表驅(qū)動大表。這里要了解什么是小表，什么是大表，為什么是小表驅(qū)動大表，MySQL 用了什么算法。
下面以兩張表關(guān)聯(lián)為例，介紹概念
什么是小表，什么是大表?不是表數(shù)據(jù)量較多那張表就是大表!!!而是經(jīng)過我們的條件篩選后，匹配數(shù)據(jù)相對較小的那張表就是小表，另外一張就是大表。
所謂的小表驅(qū)動大表就是：先查小表，然后通過關(guān)聯(lián)字段去匹配大表數(shù)據(jù)。
MySQL 的表關(guān)聯(lián)常見有兩種算法：
- Nested-Loop Join 算法(NLJ)
- Block Nested-Loop Join 算法(BNL)
4.3.1 NLJ，嵌套循環(huán)連接算法
這個算法就是一次一行地從驅(qū)動表中讀取，通過關(guān)聯(lián)字段在被驅(qū)動表中取出滿足條件的行，然后取出兩張表的結(jié)果合集。
explain select * from uuc_user u inner join uuc_user_role ur on ur.user_id = u.id;
從執(zhí)行計劃可以看出：
- uuc_user_role 是驅(qū)動表，并且掃描了9條記錄(表里只有9條記錄)，然后通過 user_id 去關(guān)聯(lián)了 uuc_user(被驅(qū)動表)。
注意：優(yōu)化器一般會優(yōu)先選擇小表驅(qū)動大表，我們 SQL 寫的表的先后順序有可能會被優(yōu)化。
上面 SQL 的大致流程如下：
- 先從 uuc_user_role 中讀取一行記錄(如果有查詢條件，會根據(jù)查詢條件過濾結(jié)果中取一條)
- 獲取關(guān)聯(lián)字段，通過關(guān)聯(lián)字段到 uuc_user 找到匹配記錄
- 對第二步得到的記錄，根據(jù)查詢條件得到的記錄跟第一步得到的記錄進(jìn)行合并，返回客戶端
- 重復(fù)上面三步
查詢結(jié)果如下(由于數(shù)據(jù)太多，手工拼接圖了)
NLJ這個過程會讀取 ur 所有數(shù)據(jù)(9行記錄)，每次讀一行并拿到 user_id 的值，然后得到對應(yīng)的 uuc_user 里的記錄(這就是又掃了一次索引得到一行數(shù)據(jù))。也就是說，整個過程掃描了18行記錄。注意：如果被驅(qū)動表的關(guān)聯(lián)字段沒有索引，使用NLJ算法性能較低，MySQL會選擇使用 BNL 算法。
擴(kuò)展：如果我這里使用的是 left join，這時，左邊的是驅(qū)動表，右邊的是被驅(qū)動表;right join 則剛好相反。
explain select * from uuc_user u left join uuc_user_role ur on ur.user_id = u.id;
4.3.2 BNL，基于塊的嵌套循環(huán)連接算法
把驅(qū)動表的數(shù)據(jù)讀入 join_buffer 中，然后掃描被驅(qū)動表，把被驅(qū)動表每一行取出來和 join_buffer 中的數(shù)據(jù)做匹配。
上面擴(kuò)展已經(jīng)出現(xiàn)了 BNL 算法的例子了，我就直接使用了。
select * from uuc_user u left join uuc_user_role ur on ur.user_id = u.id;
這條 SQL 的流程大致如下：
- 把 uuc_user 所有記錄放入 join_buffer
- 查 uuc_user_role 的記錄和 join_buffer 中的數(shù)據(jù)匹配
- 返回滿足條件的數(shù)據(jù)
整個過程掃描了 uuc_user 表225條記錄和 uuc_user_role 表9條記錄，總掃描行數(shù)為234行。內(nèi)存比較最大次數(shù) = 225 * 9 = 2025(次)，想想 for 循環(huán)的代碼就知道了。
兩個問題：
- 如果內(nèi)存不夠大，即 join_buffer 放不下 uuc_user 的數(shù)據(jù)怎么辦?
- 為什么被驅(qū)動表的關(guān)聯(lián)字段沒有索引會選擇 BNL 算法呢?
答案：
- 內(nèi)存不夠，那就分段放。打個比方我內(nèi)存只能放下200條記錄，我這里225，那么我一次放200，分兩次放完就好了。join_buffer 默認(rèn)值是256k。
- 如果關(guān)聯(lián)字段沒有索引，使用 NLJ 算法的話，那么我們的比較都需要走磁盤掃描(等于是查詢沒有用到索引)。這時，都沒用到索引的話，我內(nèi)存比較的性能要比磁盤的好。因此，使用 BNL。但是有索引的話，我們可以通過索引大大提升查詢性能(其實就是減少IO)，所以會使用 NLJ。
4.3.3 多表關(guān)聯(lián)的優(yōu)化
互聯(lián)網(wǎng)公司其實一般不允許做多表關(guān)聯(lián)，如果做了關(guān)聯(lián)，最多不超過3張表。多表關(guān)聯(lián)時，關(guān)聯(lián)字段一定要有索引，并且數(shù)據(jù)類型保持一致。為什么這么要求?直接原因，阿里規(guī)范(老大都這樣規(guī)范，小弟跟著做，沒毛病)。根本原因?看《高性能MySQL》，這本書推薦閱讀。
- 關(guān)聯(lián)字段加索引，讓 MySQL 做 join 時盡量選擇 NLJ 算法。
- 小表驅(qū)動大表，如果自己能知道哪張表肯定是小表，我們可以使用 straight_join，省去優(yōu)化器的判斷時間。
-- 像 select * from uuc_user u inner join uuc_user_role ur on ur.user_id = u.id; 這 SQL
-- 我們可以優(yōu)化成下面的 SQL,用左邊的表驅(qū)動右邊的表
explain select * from uuc_user_role ur straight_join uuc_user u on ur.user_id = u.id;
4.3.4 in 和 exsits
原則還是小表驅(qū)動大表
假設(shè) A 表是左表，B 表是子查詢的表。當(dāng) A 表是大表， B 表是小表時，使用 in。
select * from A where id in (select id from B)
當(dāng) A 表是小表， B 表是大表時，使用 exsits。
-- exists(subquery)只返回 true 或 false，官方也有說過實際執(zhí)行時會忽略查詢列。因此，select * 和 select 1 沒區(qū)別。
-- exists子查詢實際執(zhí)行過程是被優(yōu)化了的，不是我們之前理解的逐條匹配。
select * from A where exists (select 1 from B where B.id = A.id)
4.4 count 查詢優(yōu)化
網(wǎng)上挺多資料說，要count(id)或者count(1)，不要count(*)，到底是不是這樣呢?我們今天就來實踐一下。
-- 臨時關(guān)閉查詢緩存，看實驗的真實時間
set global query_cache_size=0;
set global query_cache_type=0;
-- 首先下面四條語句得到的執(zhí)行計劃都是一樣的，說明理論上這四個SQL的執(zhí)行效率應(yīng)該是差不多的
explain select count(1) from employees; -- 有時0.03左右，有時0.015s左右
explain select count(id) from employees;-- 穩(wěn)定在0.015s左右
explain select count(*) from employees;-- 穩(wěn)定在0.015s左右
explain select count(`name`) from employees;-- 穩(wěn)定在0.015s左右
具體耗時如下(其實，隨著電腦的狀態(tài)不同，會有出入，但是多次測試會發(fā)現(xiàn)，這截圖的排序結(jié)果是多數(shù))。
因此，我們可以看出 count(*) 少用，性能較差是謠言，可以放心使用。這是因為 MySQL 5.6+ 會對 count(*) 進(jìn)行優(yōu)化，所以執(zhí)行效率還是很高的。
hire_time 慢的原因是因為沒有索引。
4.5 如何建索引
老生常談的東西了，面試也經(jīng)常問，這里就做個總結(jié)。
對于如何建索引這個問題，我個人覺得應(yīng)該從以下幾個角度思考：
- 什么場景要建索引
- 應(yīng)該挑選哪些字段建索引，字段的大小，字段的類型
- 索引的數(shù)量
4.5.1 什么場景要建索引
- 高頻查詢，且數(shù)據(jù)較多，能夠通過索引篩選較多數(shù)據(jù)
- 表關(guān)聯(lián)
- 統(tǒng)計，排序，分組聚合
4.5.2 應(yīng)該挑選哪些字段建索引，字段的大小，字段的類型
- 高頻查詢，更新低頻，并且可以過濾較多數(shù)據(jù)的字段
- 用于表關(guān)聯(lián)的關(guān)聯(lián)字段
- 用于排序，分組，統(tǒng)計等等的字段
- 作為建索引的字段盡量小，可以降低樹的高度，具體規(guī)則看下面的阿里規(guī)范
4.5.3 索引的數(shù)量
索引的數(shù)量要盡量的少。
- 因為索引是會占空間的;
- 記錄更新數(shù)據(jù)庫記錄時，是有維護(hù)索引的成本的，數(shù)量越多，維護(hù)成本越高;
- 一張表索引過多，當(dāng)一個條件發(fā)現(xiàn)多個索引都生效時，優(yōu)化器一般會挑選性能最好的那個索引來用，數(shù)量多，優(yōu)化器的挑選的成本也會上升。
4.6 索引設(shè)計原則
1.代碼先行，索引后上
只有對系統(tǒng)有了一定全局觀，才知道哪些地方需要用索引，大多 SQL 是怎樣的，我應(yīng)該如何建索引。這樣，我們就能有效減少不必要的索引，做到聯(lián)合索引盡量覆蓋條件。
2.盡量不要在過濾數(shù)據(jù)不多的字段建立索引，如：性別。
3.where 與 order by 沖突時，優(yōu)先處理 where。
作者介紹
蔡柱梁，社區(qū)編輯，從事Java后端開發(fā)8年，做過傳統(tǒng)項目廣電BOSS系統(tǒng)，后投身互聯(lián)網(wǎng)電商，負(fù)責(zé)過訂單，TMS，中間件等。
分享題目：MySQL數(shù)據(jù)庫的SQL調(diào)優(yōu)，你會了嗎？
網(wǎng)頁鏈接：http://www.dlmjj.cn/article/cdicjsh.html
