日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問(wèn)題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷(xiāo)解決方案
MySQL數(shù)據(jù)庫(kù)的SQL調(diào)優(yōu)，你會(huì)了嗎？

目錄

  • 前言
  • 初步了解索引
  • 要調(diào)優(yōu) SQL，怎么能不認(rèn)識(shí) explain
  • 重點(diǎn)!SQL 優(yōu)化

一、前言

因?yàn)楣P者現(xiàn)在工作中用的存儲(chǔ)引擎大多是 InnoDB，所以本文基于 InnoDB，數(shù)據(jù)庫(kù)版本MySQL 5.7為前提寫(xiě)的。我們平常說(shuō)的 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è)概念需要先知道：

  • MySQL 索引的數(shù)據(jù)結(jié)構(gòu)

B+Tree，是 M 階搜索樹(shù)?，F(xiàn)在以主鍵索引為例，非葉子節(jié)點(diǎn)會(huì)冗余我們的主鍵排序并構(gòu)成樹(shù)結(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)化器

優(yōu)化器是MySQL 眾多組件中的一個(gè)，它會(huì)對(duì)我們的 SQL 進(jìn)行分析，看預(yù)計(jì)使用哪些索引，SQL 的執(zhí)行順序如何，實(shí)際會(huì)使用哪些索引(沒(méi)有真的執(zhí)行 SQL，執(zhí)行 SQL 是存儲(chǔ)引擎去進(jìn)行讀寫(xiě)的)，使用索引的情況等等。

二、初步了解索引

需要知道使用 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è)字段)

為什么大廠的DBA都建議InnoDB表建自增整型主鍵?

  • 主鍵(不會(huì)重復(fù))

如果我們沒(méi)有主鍵，MySQL會(huì)使用我們表從第一列開(kāi)始選擇一列所有元素都不相等的列構(gòu)建B+Tree，假設(shè)我們不存在符合這個(gè)要求的列，MySQL會(huì)自己為我們創(chuàng)建一個(gè)符合這個(gè)條件的隱藏列構(gòu)建索引。像這種開(kāi)銷(xiāo)沒(méi)必要花費(fèi)，我們自己建表時(shí)，直接處理可以。

  • 自增

維護(hù)B+Tree時(shí)，更容易，性能更好。

  • 整型

查詢范圍時(shí)，整型比較大小更簡(jiǎn)單;整型占用空間更小，節(jié)約空間，事實(shí)上公司一般都會(huì)要求明確字段大小，過(guò)大字段，DBA一般都會(huì)要求開(kāi)發(fā)解釋為什么要這么大，當(dāng)然從存儲(chǔ)數(shù)據(jù)量角度來(lái)看，索引也是越小越好。

二級(jí)索引

二級(jí)索引是非聚集的，主要是為了節(jié)約空間。二級(jí)索引是先找到主鍵，通過(guò)主鍵回表找到真正的數(shù)據(jù)行。

聯(lián)合索引(復(fù)合索引)

假如現(xiàn)在我有個(gè)用戶表有4個(gè)字段：username、telephone、age、sex。

我們可以建兩種類(lèi)型的聯(lián)合索引：聯(lián)合主鍵，普通的聯(lián)合索引。

聯(lián)合主鍵

現(xiàn)在我用 username、sex 構(gòu)建成聯(lián)合主鍵，維護(hù)索引如下：

普通的聯(lián)合索引

這個(gè)和上面的差不多，只是 data 存的是主鍵，需要回表查找。

最左匹配原則：

以上圖為例子，先根據(jù)名字轉(zhuǎn)成的ascii碼進(jìn)行排序，如果 ascii 碼一樣，那么再根據(jù)性別的 ascii 碼大小比較排序。只有 username 的索引生效了，sex 的索引才有可能生效。要證明也很容易：如果沒(méi)有匹配 username，直接匹配 sex，單看 sex 的話，我們索引的排序是無(wú)序的，就沒(méi)法使用二分法了，所以不走索引。

講了索引的數(shù)據(jù)結(jié)構(gòu)，以及生效的情況，那么接下來(lái)就要看看如何 SQL 優(yōu)化了。但是在此之前，我們要先了解下 explain 。

三、要調(diào)優(yōu) SQL，怎么能不認(rèn)識(shí) 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

3.1 explain 中各列的含義

了解每一列的意義，掌握最常用那幾列。

3.1.1 id

id 列的編號(hào)是 select 的序列號(hào)，查幾個(gè)表就有幾個(gè) id，并且 id 值越大執(zhí)行優(yōu)先級(jí)越高。如果 id 值相同，就從上往下執(zhí)行，最后執(zhí)行 id 為 null 的。

3.1.2 select_type

查詢類(lèi)型。

  • primary

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

  • subquery

復(fù)雜查詢中最外層的 select。

  • derived

包含在 from 子句中的子查詢。MySQL會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中，也稱(chēng)為派生表(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)鍵字后面的查詢，如下：

3.1.3 table

表示這一列使用的是哪一張表。

當(dāng) from 子句中有子查詢時(shí)，table列是格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。如下圖：

當(dāng)有 union 時(shí)，UNION RESULT 的 table 列的值為，1和2表示參與 union 的 select 行id。

3.1.4 partitions

使用的哪個(gè)分區(qū)，需要結(jié)合表分區(qū)才可以看到。因?yàn)槲业睦佣际菦](méi)有分區(qū)的，所以是 null。

3.1.5 type

關(guān)聯(lián)類(lèi)型或者訪問(wèn)類(lèi)型。一般要保證查詢達(dá)到 range 級(jí)別，最好達(dá)到 ref。

從最優(yōu)到最差：system > const > eq_ref > ref > range > index > ALL。

  • system, const

const 是 MySQL 能對(duì)查詢的某部分轉(zhuǎn)成一個(gè)常量，如下:

而 system 是 conts 的一個(gè)特例，當(dāng)表里只有一條記錄時(shí)，匹配時(shí)為 system。

  • eq_ref

使用了主鍵字段或者唯一索引字段進(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

  • ref

相較于 eq_ref，它使用的是普通索引或者唯一索引的部分前綴，可能會(huì)找到多條符合條件的記錄。

  • range

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

explain select * from actor where id > 1;

  • index

這種一般是通過(guò)掃描某個(gè)二級(jí)索引的所有葉子節(jié)點(diǎn)(其實(shí)就是應(yīng)該做全表掃描，但是這里利用了B+Tree的葉子節(jié)點(diǎn)是鏈表的特性遍歷)。這種方式，雖然比較慢，但是用覆蓋索引優(yōu)化，性能上還是要比全表掃描(ALL)要好的，因?yàn)樗加每臻g小，一次IO可以讀更多數(shù)據(jù)。

  • ALL

這個(gè)級(jí)別沒(méi)啥好說(shuō)的，就是我們常說(shuō)的全表掃描。

3.1.6 possible_keys

顯示可能會(huì)使用的索引。

3.1.7 key

實(shí)際會(huì)使用的索引。

3.1.8 key_len

通過(guò)這個(gè)值，可以推算出使用到索引的哪些列(一般針對(duì)聯(lián)合索引使用多些)，舉個(gè)例子：

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;

key_len計(jì)算規(guī)則如下：

  • 字符串：char(n) 和 varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個(gè)數(shù)字或字母占1個(gè)字節(jié)，一個(gè)漢字占3個(gè)字節(jié)

– char(n)：如果存漢字長(zhǎng)度就是 3n 字節(jié)

– varchar(n)：如果存漢字則長(zhǎng)度是 3n + 2 字節(jié)，加的2字節(jié)用來(lái)存儲(chǔ)字符串長(zhǎng)度，因?yàn)関archar是變長(zhǎng)字符串

  • 數(shù)值類(lèi)型

– tinyint：1字節(jié)

– smallint：2字節(jié)

– int：4字節(jié)

– bigint：8字節(jié)

  • 時(shí)間類(lèi)型

– 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)似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。

3.1.9 ref

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

3.1.10 rows

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

3.1.11 filtered

通過(guò)過(guò)濾條件之后對(duì)比總數(shù)的百分比。

3.1.12 Extra

這一列展示的是額外信息。常見(jiàn)的重要值如下：

  • Using index

使用覆蓋索引。覆蓋索引其實(shí)就是查詢列是索引字段，這樣就能避免回表，提高性能。因此，我們覆蓋索引針對(duì)的是輔助索引。

  • Using where

使用 where 語(yǔ)句處理結(jié)果，并且查詢列未被索引覆蓋。如下：

explain select * from actor where name = 'a';

  • Using index condition

查詢的列沒(méi)被索引完全覆蓋， where 條件中是一個(gè)前導(dǎo)列的范圍。

explain select * from film_actor where film_id > 1;

  • Using temporary

創(chuàng)建臨時(shí)表來(lái)處理查詢

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

explain select distinct name from actor;

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

explain select distinct name from film;

  • Using filesort

使用外部排序而不是索引排序，數(shù)據(jù)量較小時(shí)使用內(nèi)存，否則會(huì)使用磁盤(pán)。

(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 通過(guò)比較系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) 的大小和需要查詢的字段總大小來(lái)判斷使用哪種排序模式。

  1. 如果字段的總長(zhǎng)度小于 max_length_for_sort_data，那么使用單路排序。
  2. 如果字段的總長(zhǎng)度大于 max_length_for_sort_data，那么使用雙路排序。
  • Select tables optimized away

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

四、重點(diǎn)!SQL 優(yōu)化

極端點(diǎn)說(shuō)，SQL 優(yōu)化就是對(duì)索引的優(yōu)化。因此，我們要看下各種情況下，如何優(yōu)化索引。

在我看來(lái)，SQL優(yōu)化分以下幾種情況：

1.可以走索引

  • 應(yīng)該走索引，但是沒(méi)走
  • 走索引了，但是沒(méi)到最優(yōu)(explain 分析，type 一般我們要求至少到達(dá) range 這個(gè)級(jí)別)
  • order by 和 group by 優(yōu)化

2.沒(méi)法走索引(客觀現(xiàn)實(shí)上的)或者 type 是 index，而且數(shù)據(jù)量大

  • 了解適用索引的情況，請(qǐng)不要只有面試時(shí)會(huì)說(shuō)，工作就不知道了(數(shù)據(jù)量不大，直接查沒(méi)事;大的話，考慮引進(jìn)其他技術(shù)解決，如 :Redis, MongoDB, elasticsearch等)

3.小表驅(qū)動(dòng)大表

4.count 查詢優(yōu)化

5.如何建索引

  • 該在哪個(gè)字段建索引
  • 哪些字段要使用聯(lián)合索引
  • 表字段的設(shè)計(jì)(數(shù)據(jù)類(lèi)型，大小)

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

4.1 常見(jiàn)的應(yīng)該走索引，但是沒(méi)走

  • 聯(lián)合索引第一個(gè)字段不能過(guò)濾大部分?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ò)，走索引一定性能就更好嗎?我們?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è)字段過(guò)濾不多，導(dǎo)致回表效率低。既然如此，我們讓它不回表不就好了嗎?使用覆蓋索引優(yōu)化，就是我們查詢列的字段都是使用的這個(gè)索引樹(shù)上建了索引的字段，這樣就不需要回表了。如下：

 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ò)展：

  1. 我們使用 in 和 or 時(shí)，有時(shí)走索引，有時(shí)不走，其實(shí)是因?yàn)?MySQL 判斷走索引的cost不如全表掃描的。
  2. 我們這里用了 like 'keyword%'，這里涉及到一個(gè)概念叫索引下推。其實(shí)就是，MySQL 5.6 之前，對(duì)于以下的SQL，如果是走索引的話，它會(huì)先根據(jù) name 過(guò)濾得到主鍵，進(jìn)行回表拿到數(shù)據(jù)后，再去對(duì)比 age 和 position。MySQL 5.6 對(duì)此進(jìn)行了優(yōu)化——索引下推，根據(jù) name 過(guò)濾后，不先回表，而是直接去對(duì)比 age 和 position，最后得到的主鍵才回表查數(shù)據(jù)。注意：1、索引下推只用于二級(jí)索引;2、不是 like 'keyword%' 就一定使用索引下推。
SELECT * FROM `employees` WHERE `name` LIKE "sai%" AND age = 22 AND position = 'dev';
  • 分頁(yè)不走索引

分頁(yè)查詢，系統(tǒng)十分常見(jiàn)的查詢，建議大家學(xué)習(xí)完后，趕緊看下自己負(fù)責(zé)的分頁(yè)功能是否走索引了，或者是否走了索引但是還能優(yōu)化。以下，看例子來(lái)說(shuō)一些優(yōu)化手段。

select * from employees limit 10000, 10;

這 SQL 其實(shí)是去了10010條記錄出來(lái)，然后再舍棄前面的一萬(wàn)條。因此數(shù)據(jù)量大的話，其實(shí)效率是十分低的。

一些優(yōu)化方案：

1.和產(chǎn)品同事商量，給一些一定有的查詢條件或者隱藏的查詢條件，給這些條件使用上索引。

這個(gè)方案是最簡(jiǎn)單并且直接的。

2.像我這里記錄的id是連續(xù)且自增的情況下：

explain select * from employees where id > 10000 limit 10;

屬于取巧，通過(guò)主鍵索引使用 where 直接篩選掉前面10000條記錄。

缺點(diǎn)：

(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 以通配符開(kāi)頭，索引會(huì)失效，但其實(shí)也可以通過(guò)覆蓋索引，讓索引生效。
explain select `name`, age, position from employees where `name` like '%sai%';

  • 不能使用覆蓋索引，用了非主鍵排序，全表掃描的原因：MySQL 5.6~5.7 版本的優(yōu)化器認(rèn)為走二級(jí)索引再回表的效率不如全表掃描，這時(shí)是不會(huì)走索引的(但是也有例外，select * from employees order by name desc limit 10 就會(huì)走索引，因?yàn)橹恍枰?0條記錄，這數(shù)量足夠小，具體可以看這個(gè)博客，寫(xiě)得很好：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;

這里其實(shí)就是利用了二級(jí)索引，拿到了10010條數(shù)據(jù)，并且按照 name 排好序，由于這里的子查詢只要 id，所以不需要回表，然后再通過(guò) 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ì)走索引的(具體原因要回到平衡二叉查找樹(shù)，再到二分法。因?yàn)椴皇沁@里的重點(diǎn)，所以不展開(kāi)講)。

  • 在索引列上做了以下操作：

– 對(duì)索引列是用了函數(shù)

– 對(duì)索引列做了類(lèi)型轉(zhuǎn)換

 -- 類(lèi)型轉(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';

  • 根據(jù)查詢條件過(guò)濾的數(shù)據(jù)不多，導(dǎo)致優(yōu)化器認(rèn)為走索引不如全表掃描。

其實(shí)第一個(gè)案例已經(jīng)涉及到了，但是這里針對(duì)的是不等于， not in, not exists, <, >, is null, is not null 等等，這些能匹配到多條記錄的寫(xiě)法。

4.2 order by 和 group by 優(yōu)化

排序和分組的優(yōu)化其實(shí)是十分像的，本質(zhì)是先排序后分組，遵循索引創(chuàng)建順序的最左匹配原則。因此，這里以排序?yàn)槔?/p>

??https://www.cnblogs.com/25-lH/p/11010095.html??這個(gè)博客有講到無(wú)查詢條件的排序的案例，我這里就直接上圖了，如下：

接下來(lái)寫(xiě)的都是有查詢條件的情況。

explain select * from employees where `name` = 'sai999' and position = 'dev' order by age;

 -- 這里沒(méi)有走索引，是因?yàn)椴环献钭笤瓌t，跳過(guò)了 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)化，沒(méi)有和索引順序沖突
explain select * from employees where `name` = 'sai999' and age = 999 order by position, age;

-- 這里雖然符合索引順序，但是 age 是升序，而 position 是降序，所以不走索引。聽(tīng)說(shuō) MySQL 8 支持這種查詢方式，我沒(méi)安裝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 而言是無(wú)序的，所以沒(méi)法走索引
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)存甚至磁盤(pán)完成排序的。因此，index 效率高，filesort 效率低。

4.3 小表驅(qū)動(dòng)大表

當(dāng)我們做多表關(guān)聯(lián)查詢時(shí)，常常會(huì)聽(tīng)到小表驅(qū)動(dòng)大表。這里要了解什么是小表，什么是大表，為什么是小表驅(qū)動(dòng)大表，MySQL 用了什么算法。

下面以兩張表關(guān)聯(lián)為例，介紹概念

什么是小表，什么是大表?不是表數(shù)據(jù)量較多那張表就是大表!!!而是經(jīng)過(guò)我們的條件篩選后，匹配數(shù)據(jù)相對(duì)較小的那張表就是小表，另外一張就是大表。

所謂的小表驅(qū)動(dòng)大表就是：先查小表，然后通過(guò)關(guān)聯(lián)字段去匹配大表數(shù)據(jù)。

MySQL 的表關(guān)聯(lián)常見(jiàn)有兩種算法：

  • Nested-Loop Join 算法(NLJ)
  • Block Nested-Loop Join 算法(BNL)

4.3.1 NLJ，嵌套循環(huán)連接算法

這個(gè)算法就是一次一行地從驅(qū)動(dòng)表中讀取，通過(guò)關(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ì)劃可以看出：

  • uuc_user_role 是驅(qū)動(dòng)表，并且掃描了9條記錄(表里只有9條記錄)，然后通過(guò) user_id 去關(guān)聯(lián)了 uuc_user(被驅(qū)動(dòng)表)。

注意：優(yōu)化器一般會(huì)優(yōu)先選擇小表驅(qū)動(dòng)大表，我們 SQL 寫(xiě)的表的先后順序有可能會(huì)被優(yōu)化。

上面 SQL 的大致流程如下：

  1. 先從 uuc_user_role 中讀取一行記錄(如果有查詢條件，會(huì)根據(jù)查詢條件過(guò)濾結(jié)果中取一條)
  2. 獲取關(guān)聯(lián)字段，通過(guò)關(guān)聯(lián)字段到 uuc_user 找到匹配記錄
  3. 對(duì)第二步得到的記錄，根據(jù)查詢條件得到的記錄跟第一步得到的記錄進(jìn)行合并，返回客戶端
  4. 重復(fù)上面三步

查詢結(jié)果如下(由于數(shù)據(jù)太多，手工拼接圖了)

NLJ這個(gè)過(guò)程會(huì)讀取 ur 所有數(shù)據(jù)(9行記錄)，每次讀一行并拿到 user_id 的值，然后得到對(duì)應(yīng)的 uuc_user 里的記錄(這就是又掃了一次索引得到一行數(shù)據(jù))。也就是說(shuō)，整個(gè)過(guò)程掃描了18行記錄。注意：如果被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)有索引，使用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;

4.3.2 BNL，基于塊的嵌套循環(huán)連接算法

把驅(qū)動(dòng)表的數(shù)據(jù)讀入 join_buffer 中，然后掃描被驅(qū)動(dòng)表，把被驅(qū)動(dòng)表每一行取出來(lái)和 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 的流程大致如下：

  1. 把 uuc_user 所有記錄放入 join_buffer
  2. 查 uuc_user_role 的記錄和 join_buffer 中的數(shù)據(jù)匹配
  3. 返回滿足條件的數(shù)據(jù)

整個(gè)過(guò)程掃描了 uuc_user 表225條記錄和 uuc_user_role 表9條記錄，總掃描行數(shù)為234行。內(nèi)存比較最大次數(shù) = 225 * 9 = 2025(次)，想想 for 循環(huán)的代碼就知道了。

兩個(gè)問(wèn)題：

  • 如果內(nèi)存不夠大，即 join_buffer 放不下 uuc_user 的數(shù)據(jù)怎么辦?
  • 為什么被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)有索引會(huì)選擇 BNL 算法呢?

答案：

  • 內(nèi)存不夠，那就分段放。打個(gè)比方我內(nèi)存只能放下200條記錄，我這里225，那么我一次放200，分兩次放完就好了。join_buffer 默認(rèn)值是256k。
  • 如果關(guān)聯(lián)字段沒(méi)有索引，使用 NLJ 算法的話，那么我們的比較都需要走磁盤(pán)掃描(等于是查詢沒(méi)有用到索引)。這時(shí)，都沒(méi)用到索引的話，我內(nèi)存比較的性能要比磁盤(pán)的好。因此，使用 BNL。但是有索引的話，我們可以通過(guò)索引大大提升查詢性能(其實(shí)就是減少I(mǎi)O)，所以會(huì)使用 NLJ。

4.3.3 多表關(guān)聯(lián)的優(yōu)化

互聯(lián)網(wǎng)公司其實(shí)一般不允許做多表關(guān)聯(lián)，如果做了關(guān)聯(lián)，最多不超過(guò)3張表。多表關(guān)聯(lián)時(shí)，關(guān)聯(lián)字段一定要有索引，并且數(shù)據(jù)類(lèi)型保持一致。為什么這么要求?直接原因，阿里規(guī)范(老大都這樣規(guī)范，小弟跟著做，沒(méi)毛病)。根本原因?看《高性能MySQL》，這本書(shū)推薦閱讀。

  • 關(guān)聯(lián)字段加索引，讓 MySQL 做 join 時(shí)盡量選擇 NLJ 算法。
  • 小表驅(qū)動(dòng)大表，如果自己能知道哪張表肯定是小表，我們可以使用 straight_join，省去優(yōu)化器的判斷時(shí)間。
 -- 像 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;

4.3.4 in 和 exsits

原則還是小表驅(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，官方也有說(shuō)過(guò)實(shí)際執(zhí)行時(shí)會(huì)忽略查詢列。因此，select * 和 select 1 沒(méi)區(qū)別。
-- exists子查詢實(shí)際執(zhí)行過(guò)程是被優(yōu)化了的，不是我們之前理解的逐條匹配。
select * from A where exists (select 1 from B where B.id = A.id)

4.4 count 查詢優(yōu)化

網(wǎng)上挺多資料說(shuō)，要count(id)或者count(1)，不要count(*)，到底是不是這樣呢?我們今天就來(lái)實(shí)踐一下。

-- 臨時(shí)關(guān)閉查詢緩存，看實(shí)驗(yàn)的真實(shí)時(shí)間
set global query_cache_size=0;
set global query_cache_type=0;
-- 首先下面四條語(yǔ)句得到的執(zhí)行計(jì)劃都是一樣的，說(shuō)明理論上這四個(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)闆](méi)有索引。

4.5 如何建索引

老生常談的東西了，面試也經(jīng)常問(wèn)，這里就做個(gè)總結(jié)。

對(duì)于如何建索引這個(gè)問(wèn)題，我個(gè)人覺(jué)得應(yīng)該從以下幾個(gè)角度思考：

  • 什么場(chǎng)景要建索引
  • 應(yīng)該挑選哪些字段建索引，字段的大小，字段的類(lèi)型
  • 索引的數(shù)量

4.5.1 什么場(chǎng)景要建索引

  • 高頻查詢，且數(shù)據(jù)較多，能夠通過(guò)索引篩選較多數(shù)據(jù)
  • 表關(guān)聯(lián)
  • 統(tǒng)計(jì)，排序，分組聚合

4.5.2 應(yīng)該挑選哪些字段建索引，字段的大小，字段的類(lèi)型

  • 高頻查詢，更新低頻，并且可以過(guò)濾較多數(shù)據(jù)的字段
  • 用于表關(guān)聯(lián)的關(guān)聯(lián)字段
  • 用于排序，分組，統(tǒng)計(jì)等等的字段
  • 作為建索引的字段盡量小，可以降低樹(shù)的高度，具體規(guī)則看下面的阿里規(guī)范

4.5.3 索引的數(shù)量

索引的數(shù)量要盡量的少。

  1. 因?yàn)樗饕菚?huì)占空間的;
  2. 記錄更新數(shù)據(jù)庫(kù)記錄時(shí)，是有維護(hù)索引的成本的，數(shù)量越多，維護(hù)成本越高;
  3. 一張表索引過(guò)多，當(dāng)一個(gè)條件發(fā)現(xiàn)多個(gè)索引都生效時(shí)，優(yōu)化器一般會(huì)挑選性能最好的那個(gè)索引來(lái)用，數(shù)量多，優(yōu)化器的挑選的成本也會(huì)上升。

4.6 索引設(shè)計(jì)原則

1.代碼先行，索引后上

只有對(duì)系統(tǒng)有了一定全局觀，才知道哪些地方需要用索引，大多 SQL 是怎樣的，我應(yīng)該如何建索引。這樣，我們就能有效減少不必要的索引，做到聯(lián)合索引盡量覆蓋條件。

2.盡量不要在過(guò)濾數(shù)據(jù)不多的字段建立索引，如：性別。

3.where 與 order by 沖突時(shí)，優(yōu)先處理 where。

作者介紹

蔡柱梁，社區(qū)編輯，從事Java后端開(kāi)發(fā)8年，做過(guò)傳統(tǒng)項(xiàng)目廣電BOSS系統(tǒng)，后投身互聯(lián)網(wǎng)電商，負(fù)責(zé)過(guò)訂單，TMS，中間件等。


網(wǎng)站名稱(chēng)：MySQL數(shù)據(jù)庫(kù)的SQL調(diào)優(yōu)，你會(huì)了嗎？
文章位置：http://m.5511xx.com/article/cdicjsh.html