步步遞進(jìn)SQL調(diào)優(yōu)的基本操作

前言

本文平鋪直敘梧喷，步步深入义起，先講解數(shù)據(jù)庫相關(guān)理論知識嘲玫，再講到索引，詳細(xì)介紹explain關(guān)鍵字并扇，最后通過實(shí)戰(zhàn)教程，講解sql調(diào)優(yōu)的基本邏輯以及常見的索引失效場景抡诞。本文提到的數(shù)據(jù)庫均為Mysql穷蛹。

1、什么是數(shù)據(jù)庫

作為軟件系統(tǒng)開發(fā)者昼汗，幾乎天天都會(huì)跟數(shù)據(jù)庫打交道肴熏，不知道大家是否有認(rèn)真思考過，到底什么是數(shù)據(jù)庫顷窒。在生活中蛙吏，保存糧食的倉庫我們叫糧庫，保存汽油的倉庫叫糧庫鞋吉，那么以此類推鸦做，保存數(shù)據(jù)的倉庫就叫做倉庫。電腦上我們把照片放到同一個(gè)文件夾下谓着，那么這個(gè)文件夾就是一個(gè)照片數(shù)據(jù)庫泼诱；把文檔資料放到一個(gè)文件夾，那么這個(gè)文件夾也是一個(gè)數(shù)據(jù)庫赊锚。

在計(jì)算機(jī)編程中治筒，數(shù)據(jù)庫的定義和生活中有一定的區(qū)別。同樣是數(shù)據(jù)的集合這沒有變舷蒲，但是多了一些條件限定耸袜，每一種類型數(shù)據(jù)集合里面的數(shù)據(jù)都有固定的內(nèi)容結(jié)構(gòu)。

  • 數(shù)據(jù)庫中的數(shù)據(jù)都有一定規(guī)律結(jié)構(gòu)牲平，相同類型的數(shù)據(jù)放在一起堤框，不同類型的數(shù)據(jù)之間相互隔離
  • 數(shù)據(jù)庫由統(tǒng)一的規(guī)則來讀寫，有SQL語言專門用來讀寫數(shù)據(jù)庫，一般都是用程序來讀寫數(shù)據(jù)庫的內(nèi)容胰锌。

數(shù)據(jù)庫是“按照數(shù)據(jù)結(jié)構(gòu)來組織骗绕、存儲(chǔ)和管理數(shù)據(jù)的倉庫”。是一個(gè)長期存儲(chǔ)在計(jì)算機(jī)內(nèi)的资昧、有組織的酬土、可共享的、統(tǒng)一管理的大量數(shù)據(jù)的集合格带。（摘抄自百度百科）

面試題：如果讓你開發(fā)一個(gè)數(shù)據(jù)庫撤缴，該如何進(jìn)行設(shè)計(jì)？

2叽唱、數(shù)據(jù)庫設(shè)計(jì)三大范式

2.1屈呕、第一范式

數(shù)據(jù)表中的每一列(字段)，必須是不可拆分的最小單元棺亭，也就是確保每一列的原子性虎眨。
比如用戶地址信息，如果僅僅作為屬性展示镶摘，無相關(guān)業(yè)務(wù)可以僅用一個(gè)地址字段進(jìn)行保存嗽桩，但如果涉及用戶歸屬省市區(qū)查詢統(tǒng)計(jì)等，則需要再拆分凄敢。

2.2碌冶、第二范式

確保數(shù)據(jù)庫表中的每一列都和主鍵相關(guān)，而不能只與主鍵的某一部分相關(guān)（主要針對聯(lián)合主鍵而言）涝缝。也就是說在一個(gè)數(shù)據(jù)庫表中扑庞，一個(gè)表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中拒逮。
比如訂單表（訂單號罐氨、數(shù)量、商品名稱滩援、商品介紹岂昭、購買人id）把訂單和商品詳細(xì)信息都放在了同一張表中，應(yīng)該拆分為兩個(gè)表狠怨，商品信息保存在商品表中约啊，訂單表只存放訂單信息。

2.3佣赖、第三范式

表中的每一列都要與主鍵直接相關(guān)恰矩，而不是間接相關(guān)（表中的每一列只能依賴于主鍵）。
示例同范式2憎蛤，如果已經(jīng)有了訂單表和商品表外傅，那訂單表設(shè)計(jì)字段就只需要包含商品id纪吮，不需要額外的商品名稱、介紹等字段萎胰。
范式2與范式3的本質(zhì)區(qū)別：在于有沒有分出兩張表碾盟。范式2是說一張表中包含了多種不同實(shí)體的屬性，那么必須要分成多張表技竟，比如分成了訂單和商品表冰肴。范式3是要求已經(jīng)分好了多張表的話，一張表中只能有另一張表的ID榔组，而不能有其他任何信息（其他任何信息熙尉，一律用主鍵在另一張表中查詢）。

必須先滿足范式1才能滿足范式2搓扯，必須同時(shí)滿足范式1和2才能滿足范式3检痰。

3、什么是索引

索引本質(zhì)是一種可以高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)锨推。
數(shù)據(jù)庫維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)铅歼，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級查找算法换可，這種數(shù)據(jù)結(jié)構(gòu)就是索引谭贪。

4、索引分類

主鍵索引：也簡稱主鍵锦担，它可以提高查詢效率，并提供唯一性約束慨削。一張表中只能有一個(gè)主鍵洞渔。被標(biāo)志為自動(dòng)增長的字段一定是主鍵，但主鍵不一定是自動(dòng)增長缚态，不允許有空值磁椒。
普通（單值）索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)普通索引玫芦。
唯一索引：索引列的值必須唯一浆熔，但允許有空值。
組合（復(fù)合）索引：即一個(gè)索引包含多個(gè)列桥帆。
全文索引：主要用來查找文本中的關(guān)鍵字医增。

聚簇索引：也叫聚集索引，它實(shí)際上并不是一種單獨(dú)的索引類型老虫，而是一種數(shù)據(jù)存儲(chǔ)方式叶骨，將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，找到索引也就找到了數(shù)據(jù)祈匙。一個(gè)表僅有一個(gè)聚簇索引忽刽。聚簇索引默認(rèn)是主鍵天揖，如果表中沒有定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一的非空索引代替跪帝。如果沒有這樣的索引今膊，InnoDB會(huì)隱式定義一個(gè)主鍵來作為聚簇索引。
非聚簇索引：也叫輔助索引伞剑、二級索引斑唬，它的葉子節(jié)點(diǎn)只包含一個(gè)主鍵值，通過非聚簇索引查找記錄要先找到主鍵纸泄，然后通過主鍵再到聚簇索引中找到對應(yīng)的記錄行赖钞，這個(gè)過程被稱為回表。

Innodb的主鍵是聚簇索引聘裁、MyISAM都是非聚簇索引雪营。

5、索引結(jié)構(gòu)

二叉樹：每個(gè)節(jié)點(diǎn)最多有兩個(gè)子節(jié)點(diǎn)衡便，如果是插入有順序的數(shù)據(jù)献起，可能蛻化成鏈表。
平衡二叉樹：基于二叉樹容易蛻化成鏈表的缺點(diǎn)镣陕，進(jìn)行優(yōu)化谴餐，任意節(jié)點(diǎn)的左右子樹高度差絕對值不超過1 。
紅黑樹：屬于平衡二叉樹的一種呆抑，引入了紅黑色節(jié)點(diǎn)岂嗓。

B樹：與二叉樹的區(qū)別是多叉，又叫多路自平衡查找樹鹊碍。

B+樹：B樹的升級版厌殉，B+樹非葉子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，所有數(shù)據(jù)均保存在葉子節(jié)點(diǎn)中侈咕，每個(gè)葉子節(jié)點(diǎn)指向相鄰的葉子節(jié)點(diǎn)公罕，便于區(qū)間查找。

面試題：用InnoDB引擎使用B+樹作為Mysql的索引結(jié)構(gòu)有什么好處耀销。
目的是減少磁盤IO次數(shù)楼眷，加快查詢效率，具體體現(xiàn)在下面兩點(diǎn)：

  • B+樹熊尉，非葉子節(jié)點(diǎn)不存數(shù)據(jù)罐柳，節(jié)點(diǎn)數(shù)據(jù)小，每次磁盤IO的時(shí)候狰住，數(shù)據(jù)就多硝清，相同區(qū)域，B+樹有更多的key 转晰。
  • B+數(shù)芦拿，子節(jié)點(diǎn)鏈表士飒，磁盤讀取預(yù)讀原理，多讀數(shù)據(jù)蔗崎，可以減少磁盤IO酵幕，同時(shí)可以快速的進(jìn)行范圍查詢。

6缓苛、explain關(guān)鍵字

explain信息有10列芳撒，分別是id、select_type未桥、table笔刹、type、possible_keys冬耿、key舌菜、key_len、ref亦镶、rows日月、Extra。

6.1缤骨、id

SELECT的查詢序列號爱咬，表示語句的執(zhí)行順序。

  • id相同時(shí)绊起，執(zhí)行順序由上至下精拟。
  • 如果是子查詢，id的序號會(huì)遞增虱歪，id值越大優(yōu)先級越高蜂绎，越先被執(zhí)行。
  • id如果相同实蔽，可以認(rèn)為是一組，從上往下順序執(zhí)行谨读；在所有組中局装，id值越大，優(yōu)先級越高劳殖，越先執(zhí)行铐尚。

6.2、select_type

查詢類型哆姻，主要用于區(qū)別普通查詢宣增、聯(lián)合查詢、子查詢等復(fù)雜的查詢

  • SIMPLE矛缨。簡單SELECT爹脾，不使用UNION或子查詢等帖旨。
  • PRIMARY。當(dāng)存在子查詢時(shí)灵妨，最外層的select被標(biāo)記為PRIMARY解阅。
  • UNION。UNION中的第二個(gè)或后面的SELECT語句泌霍。
  • UNION RESULT货抄。UNION的結(jié)果，union語句中第二個(gè)select開始后面所有select朱转。
  • SUBQUERY蟹地。在SELECT或者WHERE列表中包含了子查詢。
  • DERIVED藤为。派生表的SELECT怪与，F(xiàn)ROM子句的子查詢。

6.3凉蜂、table

標(biāo)識SQL操作屬于哪張表琼梆。顯示這一步所訪問數(shù)據(jù)庫中表名稱，有時(shí)不是真實(shí)的表名字窿吩，可能是別名茎杂，也可能是第幾步執(zhí)行的結(jié)果簡稱。

6.4纫雁、type

標(biāo)識對表的訪問方式煌往，常用的類型有（從上到下，性能從差到好）：

  • ALL：Full Table Scan轧邪， 全表掃描刽脖，MySQL將遍歷全表以找到匹配的行。
  • index：Full Index Scan忌愚，遍歷索引曲管，index與ALL區(qū)別為index類型只遍歷索引樹。
  • range：只檢索給定范圍的行硕糊，使用一個(gè)索引來選擇行院水。
  • ref：非主鍵非唯一索引等值掃描。
  • eq_ref：類似ref简十，區(qū)別就在使用的索引是唯一索引檬某，對于每個(gè)索引鍵值，表中只有一條記錄匹配螟蝙，簡單來說恢恼，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件。
  • const：當(dāng)MySQL對查詢某部分進(jìn)行優(yōu)化胰默，并轉(zhuǎn)換為一個(gè)常量時(shí)场斑，使用這些類型訪問漓踢。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量和簸。
  • system：system是const類型的特例彭雾，當(dāng)查詢的表只有一行的情況下，使用system
  • NULL：MySQL在優(yōu)化過程中分解語句锁保，執(zhí)行時(shí)甚至不用訪問表或索引薯酝，例如從一個(gè)索引列里選取最小值可以通過單獨(dú)索引查找完成。

6.5爽柒、possible_keys

表示可能應(yīng)用在這張表中的索引吴菠，一個(gè)或者多個(gè)，但不一定被查詢實(shí)際使用浩村。

6.6做葵、key

表示實(shí)際使用到的索引，必然包含在possible_keys中心墅。
如果為NULL酿矢，則沒有建立索引或者索引失效。查詢中若使用了覆蓋索引（查詢的數(shù)據(jù)列只用從索引中就能夠取得怎燥，不必再讀取數(shù)據(jù)化瘫筐，換句話說：查詢列要被所建的索引覆蓋），則該索引僅出現(xiàn)在key列表铐姚。

6.7策肝、key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度隐绵。
key_len顯示的值為索引字段的最大可能長度之众，并非實(shí)際使用長度，即key_len是根據(jù)表定義計(jì)算而得依许，不是通過表內(nèi)檢索出的棺禾。不損失精確性的情況下，長度越短越好

常用計(jì)算說明（utf8編碼格式）
varchar(10)且字段允許NULL峭跳，key_len = 10*3+2+1
varchar(10) 且字段不允許NULL膘婶，key_len = 10*3+2
int字段允許NULL，key_len = 4+1
int字段不允許NULL坦康，key_len = 4
+2表示可變長字段竣付，+1表示可以為空诡延。

6.8滞欠、ref

表示哪些列被使用了，如果可能的話肆良，最好是一個(gè)常數(shù)（const）筛璧，哪些列或常量被用于查找索引列上的值。


6.9、rows

根據(jù)表的統(tǒng)計(jì)信息和索引的選用情況恃锉，大概估算所需要查詢的行數(shù)隔盛。

6.10、Extra

表示不適合在其他列顯示朗儒，但是也非常重要的額外信息颊乘，挑選幾個(gè)常見結(jié)果說明如下。

  • Using filesort醉锄。表示當(dāng)SQL中有一個(gè)地方需要對一些數(shù)據(jù)進(jìn)行排序的時(shí)候乏悄，優(yōu)化器找不到能夠使用的索引，所以只能使用外部的索引排序恳不。
    Mysql無法利用索引完成的排序操作稱為文件排序
  • Using tempporary檩小。表示在對MySQL查詢結(jié)果進(jìn)行排序時(shí)，使用了臨時(shí)表烟勋，這樣的查詢效率是比外部排序更低的规求，常見于order by和group by。
  • Using index 表示使用了索引卵惦，很好阻肿。
    相應(yīng)的select操作中使用了覆蓋索引，避免訪問表的數(shù)據(jù)行鸵荠，效率不錯(cuò)冕茅。
    如果同時(shí)出現(xiàn)Useing where，表明索引被用來執(zhí)行索引鍵值的查找蛹找。
    如果沒有同時(shí)出現(xiàn)Useing where姨伤，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作。
  • Useing where庸疾。表明使用了where過濾條件乍楚。
  • Using join buffer。表明使用了連接緩存届慈，比如說在查詢的時(shí)候徒溪，多表join的次數(shù)非常多，那么將配置文件中的緩沖區(qū)的join buffer調(diào)大一些金顿。
  • impossible where臊泌。where篩選條件沒能篩選出任何東西，比如恒為false揍拆。

需特別注意以下幾點(diǎn)：

  • explain不會(huì)告訴你關(guān)于觸發(fā)器渠概、存儲(chǔ)過程的信息或用戶自定義函數(shù)對查詢的影響情況。
  • explain不考慮各種Cache
  • explain不能顯示MySQL在執(zhí)行查詢時(shí)所作的優(yōu)化工作
  • 部分統(tǒng)計(jì)信息是估算的，并非精確值
  • explain只能解釋SELECT操作播揪，其他操作要重寫為SELECT后查看執(zhí)行計(jì)劃贮喧。

7、最左前綴原則（最左匹配原則）

可以對某一列建立索引猪狈，還可以對多列建立一個(gè)復(fù)合索引箱沦，對復(fù)合索引存在一個(gè)最左前綴匹配原則的概念。

  • 對于復(fù)合索引雇庙，MySQL 會(huì)一直向右匹配直到遇到范圍查詢（> 谓形， < ，between疆前，like）就停止匹配套耕。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）這種順序的索引峡继，那么 d 是用不到索引冯袍。的，但是如果建立的是 （a,b,d,c）這種順序的索引的話碾牌，那么就沒問題康愤，而且 a，b舶吗，d 的順序可以隨意調(diào)換征冷。
  • = 和 in 可以亂序，比如 a = 3 and b = 4 and c = 5 建立 （a誓琼，b检激，c）索引可以任意順序。
  • 如果建立的索引順序是 （a腹侣，b）那么直接采用 where b = 5 這種查詢條件是無法利用到索引的叔收，這一條最能體現(xiàn)最左匹配的特性。

8傲隶、SQL優(yōu)化實(shí)戰(zhàn)

建表語句：
CREATE TABLE `user001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `idcard` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

插入測試數(shù)據(jù)：
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('張三', 15, '18200000000', 'idcard1');
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('李四', 27, '15900000000', 'idcard2');
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('王五', 29, '13712345678', 'idcard3');

創(chuàng)建復(fù)合索引：
alter table `user001` add index idx_nameAgeMobile(`name`, `age`, `mobile`);

1)饺律、未使用任何的where條件。

explain SELECT * from user001;

2)跺株、使用到where條件复濒，但未使用索引字段。

explain SELECT * from user001 where idcard= 'idcard1';

3)乒省、使用到索引字段巧颈。

explain select * from user001 where id = 1;
explain select * from user001 where name = '張三';
explain select * from user001 where id = 1 and name = '張三';
explain select * from user001 where name = '張三' and age = 15;
explain select * from user001 where name = '張三' and mobile = '18200000000';
explain select * from user001 where name = '張三' and idcard = 'idcard1';
explain select * from user001 where name = '張三' and age = 15 and mobile = '18200000000';
explain select * from user001 where mobile = '18200000000' and age = 15 and name = '張三';
explain select * from user001 where name = '張三' and age > 15;
explain select * from user001 where name = '張三' and age > 15 and mobile = '18200000000';
explain select * from user001 where name = '張三' and age > 15 and idcard = 'idcard1';
explain select * from user001 where name = '張三' and age = 15 and mobile like '18%';

3)、使用到索引字段袖扛，但未遵循最左前綴匹配原則砸泛。

explain select * from user001 where age = 15;
explain select * from user001 where mobile= '15900000000';
explain select * from user001 where age = 15 and mobile= '15900000000';

4)、使用到索引字段，但在該字段上進(jìn)行了操作（計(jì)算晾嘶、函數(shù)、自動(dòng)或者手動(dòng)類型轉(zhuǎn)換）娶吞。

explain select * from user001 where name = '張三';
explain select * from user001 where left(name,2) = '張三';
explain select * from user001 where left(name,2) = '張三';
explain select * from user001 where name = '張三' and (age + 1) > 15;
explain select * from user001 where name = 2000;
explain select * from user001 where name = '2000';

5)垒迂、盡量使用覆蓋索引。

explain select * from user001 where name = '張三' and age = 15 and mobile = '18200000000';
explain select name,age,mobile from user001 where name = '張三' and age = 15 and mobile = '18200000000';
explain select name,age,mobile from user001 where name = '張三' and age > 15 and mobile = '18200000000';
explain select name from user001 where name = '張三' and age = 15 and mobile = '18200000000';
explain select name,mobile from user001 where name = '張三' and age = 15 and mobile = '18200000000';
explain select age,mobile from user001 where name = '張三' and age = 15 and mobile = '18200000000';

6)妒蛇、is null机断、is not null無法使用索引。

explain select * from user001 where name is null;
explain select * from user001 where name is not null;

7)绣夺、like以通配符開頭（%abc..)會(huì)使索引失效吏奸。

explain select * from user001 where name like '%張三%';
explain select * from user001 where name like '%張三';
explain select * from user001 where name like '張三%';
explain select * from user001 where name like '張三%' and age = 15;
explain select * from user001 where name like '%張三' and age = 15;
explain select * from user001 where name like '張%三%' and age = 15;

面試題：如何解決like '%xx%'導(dǎo)致索引失效？

使用覆蓋索引
explain select name from user001 where name like '%張三%';

explain select id from user001 where name like '%張三%';
explain select id,name from user001 where name like '%張三%';

8)陶耍、order by奋蔚。

explain select * from user001 where name = '張三' and mobile = '18200000000' order by age;
explain select * from user001 where name = '張三' order by age;
explain select * from user001 where name = '張三' order by mobile;
explain select * from user001 where name = '張三' order by idcard;
explain select * from user001 where name = '張三' order by age,mobile;
explain select * from user001 where name = '張三' order by mobile,age;
explain select * from user001 where name = '張三' and age = 15 order by age,mobile;
explain select * from user001 where name = '張三' and age = 15 order by mobile,age;

order by關(guān)鍵字優(yōu)化（主要是避免文件排序）

  • 盡量使用Index排序，避免使用FileSort方式排序烈钞。Mysql支持兩種方式排序FileSort和Index泊碑，Index效率高，掃描索引本身完成排序毯欣。
    以下兩種情形會(huì)使用Index方式排序：order by語句使用索引最左前列馒过；使用where字子句與order by子句條件列組合滿足索引最左前綴原則。
  • 盡可能在索引列上完成排序操作酗钞，遵照索引最左前綴原則腹忽。

explain select * from user001 where name = '張三' and age > 10 order by age;
explain select * from user001 where name = '張三' and age > 10 order by age,mobile;
explain select * from user001 where name = '張三' and age > 10 order by mobile;
explain select * from user001 where name = '張三' and age > 10 order by mobile,age;

  • 如果不在索引列上，F(xiàn)ileSort有兩種算法砚作，雙路排序窘奏、單路排序。
    MySQL 4.1 之前使用的雙路排序葫录，通過兩次掃描磁盤得到數(shù)據(jù)蔼夜。先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和行指針，然后在sort buffer中進(jìn)行排序压昼，排序完后需要再次取回其它需要的字段求冷。
    單路排序是從磁盤中讀取查詢需要的所有列，按照order by列在sort buffer(排序緩存) 緩沖區(qū)對他們進(jìn)行排序窍霞，然后掃描排序后的列表輸出匠题。因?yàn)閱温放判蛐矢欤苊饬硕巫x取數(shù)據(jù)但金，把隨機(jī)IO變成了順序IO韭山，但是會(huì)使用更多的空間。因?yàn)榘衙恳恍械臄?shù)據(jù)都保存在內(nèi)存中，可能取出數(shù)據(jù)總大小超過sort buffer容量钱磅，導(dǎo)致每次只能取出sort buffer容量大小數(shù)據(jù)進(jìn)行排序（創(chuàng)建tmp文件梦裂，多路合并），從而多次進(jìn)行磁盤I/O盖淡。
    解決方案：
    避免select *年柠，只寫需要的字段。
    增大max_length_for_sort_data參數(shù)的設(shè)置+增大sort_buffer_size參數(shù)的設(shè)置褪迟。

假設(shè)復(fù)合索引為a_b_c(a , b , c)
1冗恨、order by能使用索引最左前綴
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
2、如果where使用索引的最左前綴定義為常量味赃，則order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c
3掀抹、不能使用索引進(jìn)行排序
order by a asc,b desc,c desc //排序不一樣
where d = const order by b,c //丟失a索引
where a = const order by c //丟失b索引
where a = const order by a,d //d不是索引的一部分
where a = in (...) order b,c //對于排序來說，多個(gè)相等條件也是范圍查詢

9)心俗、group by傲武。分組前基本上都需要先排序，可能會(huì)有臨時(shí)表產(chǎn)生

explain select * from user001 where name = '張三' group by age,mobile;
explain select * from user001 where name = '張三' group by mobile,age;

group by關(guān)鍵字優(yōu)化

  • group by實(shí)質(zhì)是先排序后進(jìn)行分組城榛，遵照索引的最左前綴原則谱轨，基本同order by。
  • 當(dāng)無法使用索引列時(shí)吠谢，增大max_length_for_sort_data參數(shù)的設(shè)置+增大sort_buffer_size參數(shù)的設(shè)置土童。
  • where優(yōu)先級高于having，能寫在where限定的條件就不要去aving限定了工坊。

10)献汗、查詢結(jié)果數(shù)量過大，占據(jù)了大表30%以上

explain select * from health_record where institutionId like '01%';
explain select * from health_record where institutionId like '0101%';

總結(jié)：

  • 對于單值索引王污，盡量選擇針對當(dāng)前query過濾性更好的索引罢吃。
  • 在選擇復(fù)合索引的時(shí)候，當(dāng)前查詢中過濾性最好的字段在索引字段順序中昭齐，位置越靠前越好尿招。
  • 在選擇復(fù)合索引的時(shí)候，盡量選擇可以包含當(dāng)前查詢中where條件更多字段的索引阱驾。
  • 盡可能通過分許統(tǒng)計(jì)信息和調(diào)整sql的寫法來達(dá)到選擇適合索引的目的就谜。

9、索引失效場景

  • 沒有使用查詢條件里覆，或者查詢條件并未建立在索引字段上丧荐。
  • 被索引字段，發(fā)生了類型轉(zhuǎn)換喧枷。
  • 被索引字段虹统，使用了運(yùn)算符或者函數(shù)（包含!=弓坞、<>）。
  • 使用了左模糊匹配车荔。like %xx
  • 復(fù)合索引渡冻，未遵守最左前綴原則。
  • 查詢結(jié)果數(shù)量過大忧便，占據(jù)了大表30%以上族吻。
  • 查詢條件中使用or會(huì)使索引失效。
  • is null或者is not null茬腿。

面試題：如何解決like '%xx%'導(dǎo)致索引失效？------覆蓋索引

10宜雀、SQL優(yōu)化步驟

  • 持續(xù)觀察切平，時(shí)間略微長一點(diǎn)，最好能把系統(tǒng)使用率高低峰谷都涵蓋進(jìn)去辐董，整體分析下系統(tǒng)的響應(yīng)情況悴品。
  • 在第1步的基礎(chǔ)上，結(jié)合數(shù)據(jù)庫服務(wù)器磁盤是否過載简烘，內(nèi)存是否用完進(jìn)行觀察苔严。
  • 開啟慢查詢?nèi)罩荆O(shè)置闕值孤澎，比如超過5秒的就是慢sql届氢，并將它抓出來。
  • explain進(jìn)行慢查詢sql分析覆旭，比如增加索引退子、優(yōu)化原有搜索邏輯等。
  • show profile 查詢sql在mysql服務(wù)器里面的執(zhí)行細(xì)節(jié)和生命周期情況型将。
  • 數(shù)據(jù)庫服務(wù)器的參數(shù)調(diào)優(yōu)寂祥。
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市七兜，隨后出現(xiàn)的幾起案子丸凭，更是在濱河造成了極大的恐慌，老刑警劉巖腕铸，帶你破解...
    沈念sama閱讀 206,968評論 6贊 482
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件惜犀，死亡現(xiàn)場離奇詭異，居然都是意外死亡狠裹，警方通過查閱死者的電腦和手機(jī)向拆，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,601評論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來酪耳，“玉大人浓恳，你說我怎么就攤上這事刹缝。” “怎么了颈将？”我有些...
    開封第一講書人閱讀 153,220評論 0贊 344
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵梢夯，是天一觀的道長。 經(jīng)常有香客問我晴圾，道長颂砸，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 55,416評論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任死姚，我火速辦了婚禮人乓，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘都毒。我一直安慰自己色罚，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 64,425評論 5贊 374
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布账劲。 她就那樣靜靜地躺著戳护，像睡著了一般。 火紅的嫁衣襯著肌膚如雪瀑焦。 梳的紋絲不亂的頭發(fā)上腌且，一...
    開封第一講書人閱讀 49,144評論 1贊 285
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音榛瓮，去河邊找鬼铺董。 笑死，一個(gè)胖子當(dāng)著我的面吹牛禀晓，可吹牛的內(nèi)容都是我干的柄粹。 我是一名探鬼主播，決...
    沈念sama閱讀 38,432評論 3贊 401
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼匆绣，長吁一口氣：“原來是場噩夢啊……” “哼驻右！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起崎淳，我...
    開封第一講書人閱讀 37,088評論 0贊 261
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤堪夭，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后拣凹，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體森爽，經(jīng)...
    沈念sama閱讀 43,586評論 1贊 300
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,028評論 2贊 325
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年嚣镜，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了爬迟。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,137評論 1贊 334
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡菊匿，死狀恐怖付呕，靈堂內(nèi)的尸體忽然破棺而出计福，到底是詐尸還是另有隱情，我是刑警寧澤徽职，帶...
    沈念sama閱讀 33,783評論 4贊 324
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布象颖，位于F島的核電站，受9級特大地震影響姆钉，放射性物質(zhì)發(fā)生泄漏说订。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,343評論 3贊 307
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一潮瓶、第九天 我趴在偏房一處隱蔽的房頂上張望陶冷。 院中可真熱鬧，春花似錦毯辅、人聲如沸埂伦。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,333評論 0贊 19
  • 一樁弒父案悉罕，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽赤屋。三九已至立镶，卻和暖如春壁袄，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背媚媒。 一陣腳步聲響...
    開封第一講書人閱讀 31,559評論 1贊 262
  • 情欲美人皮
    我被黑心中介騙來泰國打工嗜逻， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人缭召。 一個(gè)月前我還...
    沈念sama閱讀 45,595評論 2贊 355
  • 代替公主和親
    正文 我出身青樓栈顷，卻偏偏與公主長得像，于是被迫代替她去往敵國和親嵌巷。 傳聞我的和親對象是個(gè)殘疾皇子萄凤，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,901評論 2贊 345

推薦閱讀更多精彩內(nèi)容