Mysql前面已經(jīng)把基礎(chǔ)和原理部分鋪墊的差不多了，現(xiàn)在要來講講的是Sql優(yōu)化和調(diào)優(yōu)部分了，這個(gè)基本是Mysql拷問系列最直接靈魂的環(huán)節(jié)了，優(yōu)化這幾乎是每場(chǎng)面試對(duì)話的最高潮部分，但這里又是一個(gè)開放的話題，能不能綻放出亮點(diǎn)，就看平時(shí)在調(diào)優(yōu)方面下的功夫夠不夠了，數(shù)據(jù)庫優(yōu)化維度有四個(gè)：硬件、系統(tǒng)配置、數(shù)據(jù)庫表結(jié)構(gòu)、SQL及索引。這一篇的重點(diǎn)是放在SQL語句的優(yōu)化上，表設(shè)計(jì)與大表優(yōu)化方案則放在下一個(gè)篇文章中。

一、總優(yōu)化思路

對(duì)于程序開發(fā)者來說，MySQL層優(yōu)化一般可以遵從五個(gè)原則：

• 減少數(shù)據(jù)訪問：設(shè)置合理的字段類型，啟用壓縮，通過索引訪問等減少磁盤 IO。
• 返回更少的數(shù)據(jù)：只返回需要的字段和數(shù)據(jù)分頁處理，減少磁盤 IO 及網(wǎng)絡(luò) IO。
• 減少交互次數(shù)：批量 DML 操作，函數(shù)存儲(chǔ)等減少數(shù)據(jù)連接次數(shù)。
• 減少服務(wù)器 CPU 開銷：盡量減少數(shù)據(jù)庫排序操作以及全表查詢，減少 CPU 內(nèi)存占用
• 利用更多資源：使用表分區(qū)，可以增加并行操作，更大限度利用 CPU 資源。

總結(jié)到 SQL 優(yōu)化中，就如下三點(diǎn)：

• 最大化利用索引。
• 盡可能避免全表掃描。
• 減少無效數(shù)據(jù)的查詢。

二、索引優(yōu)化策略

眾所周知，索引類似于字典的目錄，可以提高查詢的效率。索引從物理上可以分為：聚集索引，非聚集索引。從邏輯上可以分為：普通索引，唯一索引，主鍵索引，聯(lián)合索引，全文索引。這一部分主要總結(jié)Sql優(yōu)化的一些技巧和規(guī)范，尤其對(duì)于一些還有歧義和誤導(dǎo)性的說法做一個(gè)糾正。

1、合理建立覆蓋索引

合理的覆蓋索引，可以減少回表次數(shù)。表數(shù)據(jù)是放在一個(gè)聚集索引上的，而建立的索引為非聚集索引，非聚集索引的葉子節(jié)點(diǎn)存放索引鍵值，以及該索引鍵指向的主鍵。一般查找的過程是從非聚集索引上找到數(shù)據(jù)的主鍵，然后根據(jù)該主鍵到聚集索引上查找記錄，這個(gè)過程稱為回表。

如有下面這個(gè)sql

select uid, login_time from user where username = ? and passwd = ?

可以建立(username, passwd, login_time)的聯(lián)合索引，由于 login_time的值可以直接從索引中拿到，不用再回表查詢，提高了查詢效率。

建立聯(lián)合索引的時(shí)候注意將區(qū)分度最高的字段放在最左邊！

2、union，or，in都能命中索引，建議使用in

新版Mysql中union，or，in都是會(huì)走索引的，所以如下三條sql語句都是等價(jià)的：

select * from article where id = 1
union all
select * from article where id = 2

select * from article where id in (1 , 2)

select * from article where id = 1 or id = 2

效率從高到低為union，in，or。in和union的效率差別可以忽略不計(jì)，所以直接建議使用in。union 比 union all 多一個(gè)去重邏輯，業(yè)務(wù)上知道已經(jīng)有字段有唯一屬性的話，直接用union all 會(huì)比 union 效率更高點(diǎn)兒。

exist和in的差別，并不是exist替換都比in要好：

• 子查詢使用 exists，會(huì)先進(jìn)行主查詢，將查詢到的每行數(shù)據(jù)循環(huán)帶入子查詢校驗(yàn)是否存在，過濾出整體的返回?cái)?shù)據(jù)；子查詢使用 in，會(huì)先進(jìn)行子查詢獲取結(jié)果集，然后主查詢匹配子查詢的結(jié)果集，返回?cái)?shù)據(jù)
• 外表內(nèi)表相對(duì)大小情況不一樣時(shí)，查詢效率不一樣：兩表大小相當(dāng)，in 和 exists 差別不大；內(nèi)表大，用 exists 效率較高；內(nèi)表小，用 in 效率較高。
• 不管外表與內(nèi)表的大小，not exists 的效率一般要高于 not in，跟子查詢的索引訪問類型有關(guān)。

負(fù)向條件索引不會(huì)使用索引，建議用in

負(fù)向條件有：!=、<>、not in、not exists、not like 等

-- 全表掃描
select * from article where id != 1 and id != 2

知道id的所有取值范圍，可以改為類似如下形式

-- 走索引
select * from article where id in (0, 3, 4)

在索引列上進(jìn)行運(yùn)算或使用函數(shù)會(huì)走全表

在列上進(jìn)行運(yùn)算或使用函數(shù)會(huì)使索引失效，從而進(jìn)行全表掃描。如下面例子在publish_time，id列上分別加上索引，publish_time為datetime類型，id為int類型:

-- 全表掃描
select * from article where year(publish_time) < 2019
-- 走索引
select * from article where publish_time < '2019-01-01'
-- 全表掃描
select * from article where id  + 1 = 5
-- 走索引
select * from article where id = 4

小心隱式類型轉(zhuǎn)換

假設(shè)id為varchar類型，用int類型查會(huì)走全表。

-- 全表掃描
select * from article where id = 100
-- 走索引
select * from article where id = '100'

隱式類型轉(zhuǎn)換在索引字段上做了函數(shù)操作，因此會(huì)全表掃描。

select * from article where id = 100
-- 等價(jià)于
select * from article where CAST(id AS signed int) = 100

那么如果id是int，執(zhí)行下面這個(gè)語句是否會(huì)導(dǎo)致全表掃描呢？select * from article where id = '100'，答案是會(huì)用到索引。

不建議使用%前綴模糊查詢

%李,%李%都會(huì)導(dǎo)致全表掃描，非前導(dǎo)模糊查詢可以使用索引

避免在where子句中進(jìn)行null值判斷

對(duì)于null的判斷會(huì)導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

經(jīng)常更改，區(qū)分度不高的列上不宜加索引

• 更新會(huì)變更 B+ 樹，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫性能。
• 一般區(qū)分度在80%以上的時(shí)候就可以建立索引。

區(qū)分度可以使用 count(distinct(列名))/count(*) 來計(jì)算
“性別”這種區(qū)分度不大的屬性，建立索引是沒有什么意義的，不能有效過濾數(shù)據(jù)，性能與全表掃描類似。

多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后

在 MySQL 中，執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的（Oracle 相反），第一張表會(huì)涉及到全表掃描。

調(diào)整 Where 字句中的連接順序

MySQL 采用從左往右，自上而下的順序解析 where 子句。根據(jù)這個(gè)原理，應(yīng)將過濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。

查詢是否存在可以加limit1

查詢是否存在或者當(dāng)查詢確定只有一條記錄時(shí)，可以加liimit1，讓MySQL停止游標(biāo)移動(dòng)，提高查詢效率

select uid from user where username = ? and passwd = ?

可改為:

select uid from user where username = ? and passwd = ? limit 1

Explain調(diào)優(yōu)

說了這么多的優(yōu)化總結(jié)，最重要的可能還是通過分析手段去，使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，號(hào)稱SQL中的debug。通過explain可以知道m(xù)ysql是如何處理sql語句，分析查詢語句或者表結(jié)構(gòu)的性能瓶頸。

explain + SQL

explain包含很多的Item，主要關(guān)注type，possible_key，key，row和Extra。
id：執(zhí)行權(quán)重，查詢多張表后對(duì)應(yīng)的id越大優(yōu)先級(jí)越高
type：查詢使用的類型，system > const > eq_ref > ref > range > index > all

        system 只有一行數(shù)據(jù)，一般不出現(xiàn)
        const 通過索引一次就找到
        eq_ref  唯一性索引掃描
        ref  非唯一性索引掃描
        range 檢索制定范圍
        index 只遍歷索引樹
        all 全表掃描

table：表名
possible_key：表中存在的索引
key：本次查詢使用的索引
row：找到目標(biāo)大致所需的行數(shù)
Extra：

        using filesort 使用外部的索引排序
        using temporary 使用臨時(shí)表保存數(shù)據(jù)
        using index 使用了覆蓋索引
        using where 使用where條件
        using join buffer 使用連接緩存
        impossible  where where條件無結(jié)果
        select tables optimized away 最佳優(yōu)化狀態(tài)，無需遍歷索引

優(yōu)化思路還是沿著我們上面總結(jié)的那些來進(jìn)行即可。

• 使用 explain 分析語句時(shí)若發(fā)現(xiàn) rows 非常大的查詢語句可以考慮在對(duì)應(yīng)的關(guān)鍵詞上加入索引
• 用聯(lián)合索引來減少回標(biāo)次數(shù)
• 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來，否則不會(huì)使用索引
• 應(yīng)盡量避免在 where 子句中使用!= 或 <> not in、not exisit等負(fù)向條件的操作符，改成in之類。
• 避免在where后面用函數(shù)或者計(jì)算，走全表了
• 避免在where后面做null值判斷
• 在搜索字符型字段時(shí)，我們有時(shí)會(huì)使用LIKE關(guān)鍵字和通配符，避免前綴也是模糊。

參考引用

1、這次被問懵了！搞定了這些SQL優(yōu)化技巧，下次橫著走
2、MySQL索引優(yōu)化實(shí)戰(zhàn)
3、巧用 explain 優(yōu)化 MySQL 語句