掃二維碼與項(xiàng)目經(jīng)理溝通
我們?cè)谖⑿派?4小時(shí)期待你的聲音
解答本文疑問/技術(shù)咨詢/運(yùn)營(yíng)咨詢/技術(shù)建議/互聯(lián)網(wǎng)交流
因?yàn)楣P者現(xiàn)在工作中用的存儲(chǔ)引擎大多是 InnoDB，所以本文基于 InnoDB，數(shù)據(jù)庫版本MySQL 5.7為前提寫的。我們平常說的 SQL 優(yōu)化，基本上就是對(duì)索引的優(yōu)化。這里既然重點(diǎn)是 SQL 優(yōu)化，所以我們得先了解索引，然后了解下我們分析 SQL 的工具 explain，最后才能到優(yōu)化。這也是本文的大綱順序。
創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的遷安網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
了解 SQL 優(yōu)化之前，有幾個(gè)概念需要先知道：
B+Tree，是 M 階搜索樹。現(xiàn)在以主鍵索引為例，非葉子節(jié)點(diǎn)會(huì)冗余我們的主鍵排序并構(gòu)成樹結(jié)構(gòu)(非葉子節(jié)點(diǎn)不會(huì)存儲(chǔ)數(shù)據(jù));葉子節(jié)點(diǎn)會(huì)存儲(chǔ)數(shù)據(jù)，并且葉子節(jié)點(diǎn)會(huì)形成一個(gè)雙向鏈表，值得注意的是首尾節(jié)點(diǎn)也有指針互相指向。(具體可以看：https://segmentfault.com/a/1190000008545713?utm_source=sf-related)
葉子節(jié)點(diǎn)存儲(chǔ)索引對(duì)應(yīng)的 record信息。
葉子節(jié)點(diǎn)只存儲(chǔ)主鍵數(shù)據(jù)，所以要查詢索引以外的數(shù)據(jù)需要回表。
走非聚簇索引得到主鍵數(shù)據(jù)后，根據(jù)主鍵再走一次聚簇索引那里查詢列需要的數(shù)據(jù)。
優(yōu)化器是MySQL 眾多組件中的一個(gè)，它會(huì)對(duì)我們的 SQL 進(jìn)行分析，看預(yù)計(jì)使用哪些索引，SQL 的執(zhí)行順序如何，實(shí)際會(huì)使用哪些索引(沒有真的執(zhí)行 SQL，執(zhí)行 SQL 是存儲(chǔ)引擎去進(jìn)行讀寫的)，使用索引的情況等等。
需要知道使用 InnoDB 的表肯定有一個(gè)聚簇索引(有且僅有一個(gè))，使用的數(shù)據(jù)結(jié)構(gòu)是 B+Tree。
*.frm：數(shù)據(jù)表結(jié)構(gòu)相關(guān)信息存儲(chǔ)的文件
*.idb：索引和數(shù)據(jù)存儲(chǔ)的文件
注意：*.idb 這個(gè)文件本身就是 B+Tree 的文件，葉子節(jié)點(diǎn)包含完整的數(shù)據(jù)記錄。
下面以主鍵索引為例(我的user表就只有三個(gè)字段)
如果我們沒有主鍵，MySQL會(huì)使用我們表從第一列開始選擇一列所有元素都不相等的列構(gòu)建B+Tree，假設(shè)我們不存在符合這個(gè)要求的列，MySQL會(huì)自己為我們創(chuàng)建一個(gè)符合這個(gè)條件的隱藏列構(gòu)建索引。像這種開銷沒必要花費(fèi)，我們自己建表時(shí)，直接處理可以。
維護(hù)B+Tree時(shí)，更容易，性能更好。
查詢范圍時(shí)，整型比較大小更簡(jiǎn)單;整型占用空間更小，節(jié)約空間，事實(shí)上公司一般都會(huì)要求明確字段大小，過大字段，DBA一般都會(huì)要求開發(fā)解釋為什么要這么大，當(dāng)然從存儲(chǔ)數(shù)據(jù)量角度來看，索引也是越小越好。
二級(jí)索引是非聚集的，主要是為了節(jié)約空間。二級(jí)索引是先找到主鍵，通過主鍵回表找到真正的數(shù)據(jù)行。
假如現(xiàn)在我有個(gè)用戶表有4個(gè)字段：username、telephone、age、sex。
我們可以建兩種類型的聯(lián)合索引：聯(lián)合主鍵，普通的聯(lián)合索引。
現(xiàn)在我用 username、sex 構(gòu)建成聯(lián)合主鍵，維護(hù)索引如下：
這個(gè)和上面的差不多，只是 data 存的是主鍵，需要回表查找。
以上圖為例子，先根據(jù)名字轉(zhuǎn)成的ascii碼進(jìn)行排序，如果 ascii 碼一樣，那么再根據(jù)性別的 ascii 碼大小比較排序。只有 username 的索引生效了，sex 的索引才有可能生效。要證明也很容易：如果沒有匹配 username，直接匹配 sex，單看 sex 的話，我們索引的排序是無序的，就沒法使用二分法了，所以不走索引。
講了索引的數(shù)據(jù)結(jié)構(gòu)，以及生效的情況，那么接下來就要看看如何 SQL 優(yōu)化了。但是在此之前，我們要先了解下 explain 。
使用 explain 可以模擬優(yōu)化器執(zhí)行 SQL，分析 SQL，看看能否優(yōu)化。
explain 標(biāo)識(shí)的 SQL 不會(huì)真的執(zhí)行，只是返回執(zhí)行計(jì)劃。如果 from 中包含子查詢，仍會(huì)執(zhí)行該子查詢，子查詢的結(jié)果將會(huì)放在臨時(shí)表中。
explain 分析的 SQL 中，每查詢一個(gè)表就會(huì)有一行記錄。
更多內(nèi)容請(qǐng)參考官方文檔：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
了解每一列的意義，掌握最常用那幾列。
id 列的編號(hào)是 select 的序列號(hào)，查幾個(gè)表就有幾個(gè) id，并且 id 值越大執(zhí)行優(yōu)先級(jí)越高。如果 id 值相同，就從上往下執(zhí)行，最后執(zhí)行 id 為 null 的。
查詢類型。
簡(jiǎn)單查詢。查詢不包含子查詢和union。
復(fù)雜查詢中最外層的 select。
包含在 from 子句中的子查詢。MySQL會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中，也稱為派生表(derived的英文含義)。如下：
#關(guān)閉mysql5.7新特性對(duì)衍生表的合并優(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
其實(shí)就是使用了 union 關(guān)鍵字后面的查詢，如下：
表示這一列使用的是哪一張表。
當(dāng) from 子句中有子查詢時(shí)，table列是格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。如下圖：
當(dāng)有 union 時(shí)，UNION RESULT 的 table 列的值為，1和2表示參與 union 的 select 行id。
使用的哪個(gè)分區(qū)，需要結(jié)合表分區(qū)才可以看到。因?yàn)槲业睦佣际菦]有分區(qū)的，所以是 null。
關(guān)聯(lián)類型或者訪問類型。一般要保證查詢達(dá)到 range 級(jí)別，最好達(dá)到 ref。
從最優(yōu)到最差：system > const > eq_ref > ref > range > index > ALL。
const 是 MySQL 能對(duì)查詢的某部分轉(zhuǎn)成一個(gè)常量，如下:
而 system 是 conts 的一個(gè)特例，當(dāng)表里只有一條記錄時(shí)，匹配時(shí)為 system。
使用了主鍵字段或者唯一索引字段進(jìn)行關(guān)聯(lián)，最多只會(huì)返回一條符合條件的記錄時(shí)，等級(jí)為 eq_ref。
explain select * from film_actor left join film on film_actor.film_id = film.id
相較于 eq_ref，它使用的是普通索引或者唯一索引的部分前綴，可能會(huì)找到多條符合條件的記錄。
范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個(gè)索引來檢索給定范圍的行。
explain select * from actor where id > 1;
這種一般是通過掃描某個(gè)二級(jí)索引的所有葉子節(jié)點(diǎn)(其實(shí)就是應(yīng)該做全表掃描，但是這里利用了B+Tree的葉子節(jié)點(diǎn)是鏈表的特性遍歷)。這種方式，雖然比較慢，但是用覆蓋索引優(yōu)化，性能上還是要比全表掃描(ALL)要好的，因?yàn)樗加每臻g小，一次IO可以讀更多數(shù)據(jù)。
這個(gè)級(jí)別沒啥好說的，就是我們常說的全表掃描。
顯示可能會(huì)使用的索引。
實(shí)際會(huì)使用的索引。
通過這個(gè)值，可以推算出使用到索引的哪些列(一般針對(duì)聯(lián)合索引使用多些)，舉個(gè)例子：
film_actor 的聯(lián)合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個(gè) int 列組成，并且每個(gè) int 是4字節(jié)。通過結(jié)果中的 key_len=4 可推斷出查詢使用了第一個(gè)列：film_id列來執(zhí)行索引查找。
explain select * from film_actor where film_id = 2;
key_len計(jì)算規(guī)則如下：
– char(n)：如果存漢字長(zhǎng)度就是 3n 字節(jié)
– varchar(n)：如果存漢字則長(zhǎng)度是 3n + 2 字節(jié)，加的2字節(jié)用來存儲(chǔ)字符串長(zhǎng)度，因?yàn)関archar是變長(zhǎng)字符串
– tinyint：1字節(jié)
– smallint：2字節(jié)
– int：4字節(jié)
– bigint：8字節(jié)
– date：3字節(jié)
– timestamp：4字節(jié)
– datetime：8字節(jié)
這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const(常量)，字段名(例：film.id)。
這一列是MySQL估計(jì)要讀取并檢測(cè)的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)。
通過過濾條件之后對(duì)比總數(shù)的百分比。
這一列展示的是額外信息。常見的重要值如下：
使用覆蓋索引。覆蓋索引其實(shí)就是查詢列是索引字段，這樣就能避免回表，提高性能。因此，我們覆蓋索引針對(duì)的是輔助索引。
使用 where 語句處理結(jié)果，并且查詢列未被索引覆蓋。如下：
explain select * from actor where name = 'a';
查詢的列沒被索引完全覆蓋， where 條件中是一個(gè)前導(dǎo)列的范圍。
explain select * from film_actor where film_id > 1;
創(chuàng)建臨時(shí)表來處理查詢
(1)actor.name沒有索引，此時(shí)創(chuàng)建了張臨時(shí)表來distinct。
explain select distinct name from actor;
(2)film.name建立了idx_name索引，此時(shí)查詢時(shí)extra是using index,沒有用臨時(shí)表。
explain select distinct name from film;
使用外部排序而不是索引排序，數(shù)據(jù)量較小時(shí)使用內(nèi)存，否則會(huì)使用磁盤。
(1)actor.name未創(chuàng)建索引，會(huì)瀏覽actor整個(gè)表，保存排序關(guān)鍵字name和對(duì)應(yīng)的id，然后排序name并檢索行記錄。
explain select * from actor order by name;
(2)film.name建立了idx_name索引，此時(shí)查詢時(shí)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é)) 的大小和需要查詢的字段總大小來判斷使用哪種排序模式。
使用某些聚合函數(shù)(比如 max、min)來訪問存在索引的某個(gè)字段時(shí)為 Select tables optimized away。
極端點(diǎn)說，SQL 優(yōu)化就是對(duì)索引的優(yōu)化。因此，我們要看下各種情況下，如何優(yōu)化索引。
在我看來，SQL優(yōu)化分以下幾種情況：
1.可以走索引
2.沒法走索引(客觀現(xiàn)實(shí)上的)或者 type 是 index，而且數(shù)據(jù)量大
3.小表驅(qū)動(dòng)大表
4.count 查詢優(yōu)化
5.如何建索引
Note : 單個(gè)索引生不生效，怎么處理還是比較簡(jiǎn)單的，所以下面只針對(duì)聯(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 '入職時(shí)間',
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ù)。
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';
不過，走索引一定性能就更好嗎?我們?cè)囼?yàn)下。
-- 關(guān)閉查詢緩存
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = 0;
-- 耗時(shí) 0.064s
SELECT * FROM `employees` WHERE `name` LIKE "sai%" AND age = 22 AND position = 'dev';
-- 耗時(shí) 0.079s
SELECT * FROM `employees` force index(idx_name_age_position) where `name` like "sai%" and age = 22 and position = 'dev';
別看我這差距不大，我這只是表列不多，字段不大，數(shù)據(jù)量也不算太多，所以差距不大，如果表更大的話，差距就會(huì)比較明顯了。實(shí)際工作中，我們很難確定走索引的 cost 就一定小于全表掃描的。因此，我們一般不強(qiáng)制走索引。
優(yōu)化方案：
我想讓 MySQL自己去走索引，而不是我強(qiáng)制走索引。怎么辦呢?其實(shí)上面已經(jīng)提到了，這里是因?yàn)榈谝粋€(gè)字段過濾不多，導(dǎo)致回表效率低。既然如此，我們讓它不回表不就好了嗎?使用覆蓋索引優(yōu)化，就是我們查詢列的字段都是使用的這個(gè)索引樹上建了索引的字段，這樣就不需要回表了。如下：
explain SELECT id,`name`,age,position FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
-- 耗時(shí) 0.051s
SELECT id,`name`,age,position FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
擴(kuò)展：
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 其實(shí)是去了10010條記錄出來，然后再舍棄前面的一萬條。因此數(shù)據(jù)量大的話，其實(shí)效率是十分低的。
一些優(yōu)化方案：
1.和產(chǎn)品同事商量，給一些一定有的查詢條件或者隱藏的查詢條件，給這些條件使用上索引。
這個(gè)方案是最簡(jiǎn)單并且直接的。
2.像我這里記錄的id是連續(xù)且自增的情況下：
explain select * from employees where id > 10000 limit 10;
屬于取巧，通過主鍵索引使用 where 直接篩選掉前面10000條記錄。
缺點(diǎn)：
(1) 如果 id 不是連續(xù)且自增，那么這種方式就不行。
(2)不是使用主鍵排序，這種情況也不行。
3.非主鍵排序，不用ID連續(xù)自增也能生效。
-- 0.085s
select * from employees order by `name` desc limit 10000, 10;
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 以通配符開頭，索引會(huì)失效，但其實(shí)也可以通過覆蓋索引，讓索引生效。
explain select `name`, age, position from employees where `name` like '%sai%';
解決方案如下：
（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;
這里其實(shí)就是利用了二級(jí)索引，拿到了10010條數(shù)據(jù)，并且按照 name 排好序，由于這里的子查詢只要 id，所以不需要回表，然后再通過 join 就能利用主鍵索引快速拿到記錄。
（2）當(dāng)然除了這種方式，我們也可以強(qiáng)制走索引，因?yàn)槲覀冎肋@里二級(jí)索引只有一個(gè)，并且 name 是前導(dǎo)列，所以我這個(gè)案例走索引性能肯定比全表掃描好。因此，我們也可以選擇強(qiáng)制走索引。
-- 0.011s
select * from employees force index(idx_name_age_position) order by `name` desc limit 10000, 10;
我們索引之所以可以幫我們快速找到目標(biāo)數(shù)據(jù)，是因?yàn)樗臄?shù)據(jù)結(jié)構(gòu)的特點(diǎn)。其中有序這一特征十分重要，如果不滿足，那么肯定是不會(huì)走索引的(具體原因要回到平衡二叉查找樹，再到二分法。因?yàn)椴皇沁@里的重點(diǎn)，所以不展開講)。
– 對(duì)索引列是用了函數(shù)
– 對(duì)索引列做了類型轉(zhuǎn)換
-- 類型轉(zhuǎn)換會(huì)有特例，當(dāng)我們轉(zhuǎn)成日期范圍查詢時(shí)，有可能走索引。
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';
其實(shí)第一個(gè)案例已經(jīng)涉及到了，但是這里針對(duì)的是不等于， not in, not exists, <, >, is null, is not null 等等，這些能匹配到多條記錄的寫法。
排序和分組的優(yōu)化其實(shí)是十分像的，本質(zhì)是先排序后分組，遵循索引創(chuàng)建順序的最左匹配原則。因此，這里以排序?yàn)槔?/p>
??https://www.cnblogs.com/25-lH/p/11010095.html??這個(gè)博客有講到無查詢條件的排序的案例，我這里就直接上圖了，如下：
接下來寫的都是有查詢條件的情況。
explain select * from employees where `name` = 'sai999' and position = 'dev' order by age;
-- 這里沒有走索引，是因?yàn)椴环献钭笤瓌t，跳過了 age
explain select * from employees where `name` = 'sai999' order by position;
-- 這樣就會(huì)走索引了，排序了
explain select * from employees where `name` = 'sai999' order by age, 1position;
-- 又不走索引了，因?yàn)?age 和 position 順序顛倒了，不符合我們索引的順序
explain select * from employees where `name` = 'sai999' order by position, age;
-- 修改成這樣，就又可以走索引了，因?yàn)?age 是個(gè)常量了，所以在排序中被優(yōu)化，沒有和索引順序沖突
explain select * from employees where `name` = 'sai999' and age = 999 order by position, age;
-- 這里雖然符合索引順序，但是 age 是升序，而 position 是降序，所以不走索引。聽說 MySQL 8 支持這種查詢方式，我沒安裝8就不測(cè)試了
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 有兩個(gè)值時(shí)，得出的結(jié)果集對(duì)于 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 效率低。
當(dāng)我們做多表關(guān)聯(lián)查詢時(shí)，常常會(huì)聽到小表驅(qū)動(dòng)大表。這里要了解什么是小表，什么是大表，為什么是小表驅(qū)動(dòng)大表，MySQL 用了什么算法。
下面以兩張表關(guān)聯(lián)為例，介紹概念
什么是小表，什么是大表?不是表數(shù)據(jù)量較多那張表就是大表!!!而是經(jīng)過我們的條件篩選后，匹配數(shù)據(jù)相對(duì)較小的那張表就是小表，另外一張就是大表。
所謂的小表驅(qū)動(dòng)大表就是：先查小表，然后通過關(guān)聯(lián)字段去匹配大表數(shù)據(jù)。
MySQL 的表關(guān)聯(lián)常見有兩種算法：
這個(gè)算法就是一次一行地從驅(qū)動(dòng)表中讀取，通過關(guān)聯(lián)字段在被驅(qū)動(dòng)表中取出滿足條件的行，然后取出兩張表的結(jié)果合集。
explain select * from uuc_user u inner join uuc_user_role ur on ur.user_id = u.id;
從執(zhí)行計(jì)劃可以看出：
注意：優(yōu)化器一般會(huì)優(yōu)先選擇小表驅(qū)動(dòng)大表，我們 SQL 寫的表的先后順序有可能會(huì)被優(yōu)化。
上面 SQL 的大致流程如下：
查詢結(jié)果如下(由于數(shù)據(jù)太多，手工拼接圖了)
NLJ這個(gè)過程會(huì)讀取 ur 所有數(shù)據(jù)(9行記錄)，每次讀一行并拿到 user_id 的值，然后得到對(duì)應(yīng)的 uuc_user 里的記錄(這就是又掃了一次索引得到一行數(shù)據(jù))。也就是說，整個(gè)過程掃描了18行記錄。注意：如果被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒有索引，使用NLJ算法性能較低，MySQL會(huì)選擇使用 BNL 算法。
擴(kuò)展：如果我這里使用的是 left join，這時(shí)，左邊的是驅(qū)動(dòng)表，右邊的是被驅(qū)動(dòng)表;right join 則剛好相反。
explain select * from uuc_user u left join uuc_user_role ur on ur.user_id = u.id;
把驅(qū)動(dòng)表的數(shù)據(jù)讀入 join_buffer 中，然后掃描被驅(qū)動(dòng)表，把被驅(qū)動(dòng)表每一行取出來和 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 的流程大致如下：
整個(gè)過程掃描了 uuc_user 表225條記錄和 uuc_user_role 表9條記錄，總掃描行數(shù)為234行。內(nèi)存比較最大次數(shù) = 225 * 9 = 2025(次)，想想 for 循環(huán)的代碼就知道了。
兩個(gè)問題：
答案：
互聯(lián)網(wǎng)公司其實(shí)一般不允許做多表關(guān)聯(lián)，如果做了關(guān)聯(lián)，最多不超過3張表。多表關(guān)聯(lián)時(shí)，關(guān)聯(lián)字段一定要有索引，并且數(shù)據(jù)類型保持一致。為什么這么要求?直接原因，阿里規(guī)范(老大都這樣規(guī)范，小弟跟著做，沒毛病)。根本原因?看《高性能MySQL》，這本書推薦閱讀。
-- 像 select * from uuc_user u inner join uuc_user_role ur on ur.user_id = u.id; 這 SQL
-- 我們可以優(yōu)化成下面的 SQL,用左邊的表驅(qū)動(dòng)右邊的表
explain select * from uuc_user_role ur straight_join uuc_user u on ur.user_id = u.id;
原則還是小表驅(qū)動(dòng)大表
假設(shè) A 表是左表，B 表是子查詢的表。當(dāng) A 表是大表， B 表是小表時(shí)，使用 in。
select * from A where id in (select id from B)
當(dāng) A 表是小表， B 表是大表時(shí)，使用 exsits。
-- exists(subquery)只返回 true 或 false，官方也有說過實(shí)際執(zhí)行時(shí)會(huì)忽略查詢列。因此，select * 和 select 1 沒區(qū)別。
-- exists子查詢實(shí)際執(zhí)行過程是被優(yōu)化了的，不是我們之前理解的逐條匹配。
select * from A where exists (select 1 from B where B.id = A.id)
網(wǎng)上挺多資料說，要count(id)或者count(1)，不要count(*)，到底是不是這樣呢?我們今天就來實(shí)踐一下。
-- 臨時(shí)關(guān)閉查詢緩存，看實(shí)驗(yàn)的真實(shí)時(shí)間
set global query_cache_size=0;
set global query_cache_type=0;
-- 首先下面四條語句得到的執(zhí)行計(jì)劃都是一樣的，說明理論上這四個(gè)SQL的執(zhí)行效率應(yīng)該是差不多的
explain select count(1) from employees; -- 有時(shí)0.03左右，有時(shí)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左右
具體耗時(shí)如下(其實(shí)，隨著電腦的狀態(tài)不同，會(huì)有出入，但是多次測(cè)試會(huì)發(fā)現(xiàn)，這截圖的排序結(jié)果是多數(shù))。
因此，我們可以看出 count(*) 少用，性能較差是謠言，可以放心使用。這是因?yàn)?MySQL 5.6+ 會(huì)對(duì) count(*) 進(jìn)行優(yōu)化，所以執(zhí)行效率還是很高的。
hire_time 慢的原因是因?yàn)闆]有索引。
老生常談的東西了，面試也經(jīng)常問，這里就做個(gè)總結(jié)。
對(duì)于如何建索引這個(gè)問題，我個(gè)人覺得應(yīng)該從以下幾個(gè)角度思考：
索引的數(shù)量要盡量的少。
1.代碼先行，索引后上
只有對(duì)系統(tǒng)有了一定全局觀，才知道哪些地方需要用索引，大多 SQL 是怎樣的，我應(yīng)該如何建索引。這樣，我們就能有效減少不必要的索引，做到聯(lián)合索引盡量覆蓋條件。
2.盡量不要在過濾數(shù)據(jù)不多的字段建立索引，如：性別。
3.where 與 order by 沖突時(shí)，優(yōu)先處理 where。
蔡柱梁，社區(qū)編輯，從事Java后端開發(fā)8年，做過傳統(tǒng)項(xiàng)目廣電BOSS系統(tǒng)，后投身互聯(lián)網(wǎng)電商，負(fù)責(zé)過訂單，TMS，中間件等。
我們?cè)谖⑿派?4小時(shí)期待你的聲音
解答本文疑問/技術(shù)咨詢/運(yùn)營(yíng)咨詢/技術(shù)建議/互聯(lián)網(wǎng)交流
微信二維碼
微信二維碼
移動(dòng)版官網(wǎng)