史上最全存儲引擎滤淳、索引使用及SQL優(yōu)化的實(shí)踐
3.5 trace分析優(yōu)化器執(zhí)行計(jì)劃
整個(gè)MySQL Server由以下組成 ：
Connection Pool ：連接池組件
Management Services & Utilities ：管理服務(wù)和工具組件
SQL Interface ：SQL接口組件
Parser ：查詢分析器組件
Optimizer ：優(yōu)化器組件
Caches & Buffers ：緩沖池組件
Pluggable Storage Engines ：存儲引擎
File System ：文件系統(tǒng)
1）連接層
最上層是一些客戶端和鏈接服務(wù)梧喷，包含本地sock通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于TCP/IP的通信。主要完成一些類似于連接處理脖咐、授權(quán)認(rèn)證铺敌、及相關(guān)的安全方案。在該層上引入了線程池的概念屁擅，為通過認(rèn)證安全接入的客戶端提供線程偿凭。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限派歌。
2）服務(wù)層
第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能弯囊，如SQL接口痰哨，并完成緩存的查詢，SQL的分析和優(yōu)化匾嘱，部分內(nèi)置函數(shù)的執(zhí)行斤斧。所有跨存儲引擎的功能也在這一層實(shí)現(xiàn)，如過程霎烙、函數(shù)等撬讽。在該層，服務(wù)器會解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹吼过，并對其完成相應(yīng)的優(yōu)化如確定表的查詢的順序锐秦，是否利用索引等，最后生成相應(yīng)的執(zhí)行操作盗忱。如果是select語句酱床，服務(wù)器還會查詢內(nèi)部的緩存，如果緩存空間足夠大趟佃，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能扇谣。
3）引擎層
存儲引擎層，存儲引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲和提取闲昭，服務(wù)器通過API和存儲引擎進(jìn)行通信罐寨。不同的存儲引擎具有不同的功能，這樣我們可以根據(jù)自己的需要序矩，來選取合適的存儲引擎鸯绿。
4）存儲層
數(shù)據(jù)存儲層，主要是將數(shù)據(jù)存儲在文件系統(tǒng)之上簸淀，并完成與存儲引擎的交互瓶蝴。
和其他數(shù)據(jù)庫相比，MySQL有點(diǎn)與眾不同租幕，它的架構(gòu)可以在多種不同場景中應(yīng)用并發(fā)揮良好作用舷手。主要體現(xiàn)在存儲引擎上，插件式的存儲引擎架構(gòu)劲绪，將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲提取分離男窟。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲引擎。
和大多數(shù)的數(shù)據(jù)庫不同贾富，MySQL中有一個(gè)存儲引擎的概念歉眷，針對不同的存儲需求可以選擇最優(yōu)的存儲引擎。
存儲引擎就是存儲數(shù)據(jù)颤枪，建立索引汗捡，更新查詢數(shù)據(jù)等等技術(shù)的實(shí)現(xiàn)方式。存儲引擎是基于表汇鞭，而不是基于庫的凉唐。所以存儲引擎也可被稱為表類型庸追。
Oracle、SqlServer等數(shù)據(jù)庫只有一種存儲引擎台囱。MySQL提供插件式的存儲引擎架構(gòu)淡溯。所以MySQL存在多種存儲引擎，
可以根據(jù)需要使用相應(yīng)的引擎簿训，或者編寫存儲引擎咱娶。
MySQL5.0支持的存儲引擎包含 ：InooDB、MyISAM强品、BDB膘侮、MEMORY、MERGE的榛、EXAMPLE琼了、NDB Cluster、ARCHIVE夫晌、
CSV雕薪、BLACKHOLE、FEDERATED等晓淀，其中InnoDB和BDB提供事物安全表所袁，其他存儲引擎是非事物安全表。
可以通過指定show engines凶掰，來查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎 ：
創(chuàng)建新表時(shí)如果不指定存儲引擎燥爷，那么系統(tǒng)就會使用默認(rèn)的存儲引擎，MySQL5.5之前的默認(rèn)存儲引擎是MyISAM懦窘，5.5之后就改為了InnoDB前翎。
查看MySQL數(shù)據(jù)庫默認(rèn)的存儲引擎 ，指令 ：
show variables like ‘%storage_engine%’;
下面重點(diǎn)介紹幾種常用的存儲引擎奶赠，并對比各個(gè)存儲引擎之間的區(qū)別鱼填，如下表所示 ：
InnoDB存儲引擎是MySQL的默認(rèn)存儲引擎药有。InnoDB存儲引擎提供了具有提交毅戈、回滾、崩潰恢復(fù)能力的事務(wù)安全愤惰。但是對比MyISAM的存儲引擎苇经，
InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引宦言。
InnoDB存儲引擎不同于其他存儲引擎的特點(diǎn) ：
事務(wù)控制
測試發(fā)現(xiàn)InnoDB中是存在事務(wù)的扇单。
外鍵約束
MySQL支持外鍵的存儲引擎只有InnoDB，在創(chuàng)建外鍵的時(shí)候奠旺，要求父表必須有對應(yīng)的索引蜘澜，子表在創(chuàng)建外鍵的時(shí)候施流，也會自動(dòng)的創(chuàng)建
對應(yīng)的索引。
下面是兩張表中鄙信，country_innodb是父表瞪醋，country_id為主鍵索引，city_innodb表是子表装诡，country_id字段為外鍵银受，對應(yīng)于
country_innodb表的主鍵country_id
在創(chuàng)建索引時(shí)，可以指定在刪除鸦采、更新父表時(shí)宾巍，對子表進(jìn)行的相應(yīng)操作，包括RESTRICT渔伯、CASCADE顶霞、SET NULL和NO ACTION。
RESTRICT和NO ACTION相同锣吼，是指限制在子表有關(guān)聯(lián)記錄的情況下确丢，父表不能更新；
CASCADE表示父表在更新或者刪除時(shí)吐限，更新或者刪除子表對應(yīng)的記錄鲜侥；
SET NULL則表示父表在更新或者刪除的時(shí)候，子表的對應(yīng)字段被SET NULL.
針對上面創(chuàng)建的兩個(gè)表诸典，子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE方式的描函，那么在主表刪除記錄的時(shí)候，如果子表有對應(yīng)記錄狐粱，則不允許刪除舀寓，主表在更新記錄的時(shí)候，如果子表有對應(yīng)的記錄肌蜻，則子表對應(yīng)更新互墓。
ON DELETE RESTRICT ----> 刪除主表數(shù)據(jù)時(shí)，如果有關(guān)聯(lián)記錄蒋搜，不刪除篡撵；
ON UPDATE CASCADE ----> 更新主表時(shí)，如果子表有關(guān)聯(lián)記錄豆挽，更新子表記錄育谬。
表中數(shù)據(jù)如下圖所示 ：
存放方式
InnoDB存儲表和索引有以下兩種方式 ：
（1）使用共享表空間存儲，這種方式創(chuàng)建的表的表結(jié)構(gòu)保存在.frm文件中帮哈，數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中膛檀，可以是多個(gè)文件。
（2）使用躲表空間存儲，這種方式創(chuàng)建的表的表結(jié)構(gòu)任然存在.frm文件中咖刃，但是每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在.ibd中泳炉。
MyISAM不支持事務(wù)、也不支持外鍵嚎杨，其優(yōu)勢是訪問速度快胡桃，對事物的完整性沒有要求或者以SELECT、INSERT為主的應(yīng)用基本上都可以使用這個(gè)
引擎來創(chuàng)建表磕潮。有以下兩個(gè)比較重要的特點(diǎn) ：
不支持事務(wù)
通過測試翠胰，我們發(fā)現(xiàn)，在MyISAM存儲引擎中自脯，是沒有事務(wù)控制的之景。
MySQL客戶端連接成功后，通過show [session | global] status命令可以提高服務(wù)器狀態(tài)信息膏潮。show [session | global] status 可以根據(jù)需要加上參數(shù) “session”或者“global”來顯示session級（當(dāng)前連接）的計(jì)結(jié)果和global級（自數(shù)據(jù)庫上次啟動(dòng)至今）的統(tǒng)計(jì)結(jié)果锻狗。如果不寫，默認(rèn)使用參數(shù)是“session”焕参。
下面的命令顯示了當(dāng)前session中所有統(tǒng)計(jì)參數(shù)的值 ：
show status like “Com_”;
show status like ‘Innodb_rows_%’;
Com_xxx表示每個(gè)xxx語句執(zhí)行的次數(shù)轻纪，我能通常比較關(guān)心的是以下幾個(gè)統(tǒng)計(jì)參數(shù)
Com_*** : 這些參數(shù)對于所有存儲引擎的表操作都會進(jìn)行累計(jì)。
Innodb_*** ：這幾個(gè)參數(shù)只是針對InooDB存儲引擎叠纷，累加的算法也略有不同刻帚。
可以通過以下兩種方式定位執(zhí)行效率較低的SQL語句。
1）慢查詢?nèi)罩?：通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的SQL語句涩嚣，用–log-slow-queries[=file_name]選型啟動(dòng)時(shí)崇众，mysqld寫一個(gè)包含索引執(zhí)行時(shí)間超過long_query_time秒的SQL語句的日志文件。
2）show processlist : 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才記錄航厚，所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題的時(shí)候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}顷歌，可以使用 show processlist 查看當(dāng)前MySQL在進(jìn)行的線程，包括線程的狀態(tài)幔睬、是否鎖表等眯漩，可以實(shí)時(shí)地查看SQL的執(zhí)行情況，同時(shí)對一些鎖表操作進(jìn)行優(yōu)化麻顶。
1）id列赦抖，用戶登錄mysql時(shí)，系統(tǒng)分配的"connection_id"，可以使用函數(shù)connection_id()查看
2）user列共苛，顯示當(dāng)前用戶。如果不是root，這個(gè)命令就只顯示用戶權(quán)限范圍的sql語句
3）host列萧吠，顯示這個(gè)語句是從哪個(gè)ip的哪個(gè)端口上發(fā)的，可以用來跟蹤出現(xiàn)問題語句的用戶
4）db列，顯示這個(gè)進(jìn)程目前連接的哪個(gè)數(shù)據(jù)庫
5）command列，顯示當(dāng)前連接的執(zhí)行的命令盈电，一般取值為休眠（sleep），查詢（query）杯活，連接（connect）等
6）time列匆帚，顯示這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒
7）state列旁钧，顯示使用當(dāng)前連接的sql語句的狀態(tài)吸重，很重要的列。state描述的是語句執(zhí)行中的某一個(gè)狀態(tài)歪今。一個(gè)sql語句嚎幸，查詢?yōu)槔赡苄枰?jīng)過copying to tmp table寄猩、sorting result嫉晶、sending data等狀態(tài)才可以完成。
8）info列田篇，顯示這個(gè)sql語句替废，是判斷問題語句的一個(gè)重要依據(jù)
通過以上步驟查詢到效率低的SQL語句后，可以通過EXPLAIN或者DESC命令獲取MySQL如何執(zhí)行SELECT語句的信息泊柬，包括在SELECT語句執(zhí)行過程中表如何連接和連接的順序椎镣。
查詢SQL語句的執(zhí)行計(jì)劃 ：
explain select * from tb_item where id = 1;
explain select * from tb_item where title = ‘阿爾卡特（ot-979）冰川白 聯(lián)通3G手機(jī)3’;
id字段是select查詢的序列號，是一組數(shù)字兽赁，表示的是查詢中執(zhí)行select子句或者是操作表的順序衣陶。id情況有三種 ：
1）id相同表示加載表的順序是從上到下。
explain select * from t_role r,t_user u,user_role ur where?r.id?= ur.role_id and?u.id?= ur.user_id;
2）id不同id值越大闸氮，優(yōu)先級越高剪况，越先被執(zhí)行。
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = ‘stul’))
3）id有相同蒲跨，也有不同译断，同時(shí)存在。id相同的可以認(rèn)為是一組或悲，從上往下順序執(zhí)行孙咪；在所有的組中，id的值越大巡语，優(yōu)先級越高翎蹈，優(yōu)先執(zhí)行。
EXPLAIN SELECT * FROM t_role r,(SELECT * FROM user_role ur WHERE ur.‘user_id’ = ‘2’) a WHERE?r.id?= a.role_id;
表示SELECT的類型男公，常見的取值荤堪，如下表所示 ：
SIMPLE ：簡單的select查詢，查詢中不包含子查詢或者UNION
PRIMARY ：查詢中若包含任何復(fù)雜的子查詢，最外層查詢標(biāo)記為該標(biāo)識
SUBQUERY ：在SELECT或WHERE列表中包含了子查詢
DERIVED ：在FROM列表中包含的子查詢澄阳，被標(biāo)記為DERIVED（衍生）MySQL會遞歸執(zhí)行這些子查詢拥知，把結(jié)果放在臨時(shí)表中
UNION ：若第二個(gè)SELECT出現(xiàn)在UNION之后，則標(biāo)記為UNION碎赢；若UNION包含在FROM子句的子查詢中低剔，外層SELECT將被標(biāo)記為 ：DERIVED
UNION RESULT ：從UNION表獲取結(jié)果的SELECT
展示這一行的數(shù)據(jù)是關(guān)于哪一張表的
type顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)肮塞，可取值為 ：
NULL ：MySQL不訪問任何表襟齿，索引，直接返回結(jié)果
system ：表只有一行記錄（等于系統(tǒng)表）枕赵，這是const類型的特例蕊唐，一般不會出現(xiàn)
const ：表示通過索引一次就找到了，const用于比較primary key（主鍵）或者unique（唯一）索引烁设。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)替梨，所以很快。如將主鍵置于where列表中装黑，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常亮副瀑。const于將“主鍵”或“唯一”索引的所有部分與常量值進(jìn)行比較。
eq_ref ：類似ref恋谭，區(qū)域在于使用的是唯一索引糠睡，使用主鍵的關(guān)聯(lián)查詢，關(guān)聯(lián)查詢出的記錄只有一條疚颊。常見于主鍵或唯一索引掃描
ref ：非唯一性索引掃描狈孔，返回匹配某個(gè)單獨(dú)值的所有行。本質(zhì)上也是一種索引訪問材义，返回所有匹配某個(gè)單獨(dú)值的所有行（多個(gè)）
range ：只檢索給定返回的行均抽，使用一個(gè)索引來選擇行。where之后出現(xiàn)between其掂，<油挥，>，in等操作款熬。
index ：index與ALL的區(qū)別為 index類型只是遍歷了索引樹深寥，通常比ALL快，ALL是遍歷數(shù)據(jù)文件贤牛。
all ：將遍歷全表以找到匹配的行
結(jié)果值從最好到最壞依次是 ：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > range > index > ALL
一般來說惋鹅，我們需要保證查詢至少達(dá)到range基本，最好達(dá)到ref殉簸。
possible_keys : 顯示可能應(yīng)用在這張表的索引闰集，一個(gè)或多個(gè)沽讹。
key ：實(shí)際使用的索引，如果為null返十，則沒有使用索引妥泉。
key_len : 表示索引中使用的字節(jié)數(shù)椭微，該值為索引字段最大可能長度洞坑，并非實(shí)際使用長度，在不損失精確性的前提下蝇率，長度越短越好迟杂。
掃描行的數(shù)量
其他的額外的執(zhí)行計(jì)劃信息，在該列展示本慕。
using filesort : 說明mysql會對數(shù)據(jù)使用一個(gè)外部的索引排序排拷，而不是按照表內(nèi)的索引順序進(jìn)行讀取，稱為“文件排序”锅尘。效率低
using temporary ：使用了臨時(shí)表保存中間結(jié)果监氢，MySQL在對查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于order by 和group by藤违。效率低
using index ：表示相應(yīng)的select操作使用了覆蓋索引浪腐，避免訪問表的數(shù)據(jù)行，效率不錯(cuò)顿乒。
當(dāng)extra列出現(xiàn)using filesort和using temporary時(shí)就需要進(jìn)行sql優(yōu)化了议街。
還有一個(gè)問題，很多人認(rèn)為當(dāng)出現(xiàn)效率低的情況璧榄，加索引特漩，一味的加索引就認(rèn)為能解決問題？實(shí)際上這種想法是錯(cuò)誤的骨杂，索引不是想加就加的涂身，每個(gè)索引都需要深思熟慮過的，不是因?yàn)闃I(yè)務(wù)需要而去加索引搓蚪，這是一種錯(cuò)誤的做法访得。索引是為了提升獲取數(shù)據(jù)庫數(shù)據(jù)的獲取效率而加的。而業(yè)務(wù)的需要可以用其他方式去實(shí)現(xiàn)陕凹。比如排序悍抑，很多人第一時(shí)間想到數(shù)據(jù)庫order by去排序，而需要排序的字段又是一些特殊的字段杜耙。我不認(rèn)為這個(gè)時(shí)候去加索引是一種很好解決方案搜骡，可以嘗試使用ES。
MySQL從5.0.37版本開始增加了對 show profiles和show profile語句的支持佑女。show profiles能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪里去了记靡。
通過have_profiling參數(shù)谈竿，能夠看到當(dāng)前MySQL是否支持profile；
默認(rèn)profiling是關(guān)閉的摸吠，可以通過set語句在Session級別開啟profiling空凸；
set profiling=1; // 開啟profiling開關(guān)；
通過profile寸痢，我們能夠更清楚地了解SQL執(zhí)行的過程呀洲。
首先，我們可以執(zhí)行一系列的操作啼止，如下圖所示 ：
show databasesl
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
執(zhí)行完上述命令之后道逗，再執(zhí)行show profiles指令，來查看SQL語句執(zhí)行的耗時(shí) ：
通過show profile for query query_id 語句可以查看到該SQL執(zhí)行過程中每個(gè)線程的狀態(tài)和消耗的時(shí)間 ：
TIP :
Sending data 狀態(tài)表示MySQL線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端献烦，而不僅僅是返回個(gè)客戶端滓窍。由于在Sending data狀態(tài)下，MySQL線程往往需要做大量的磁盤讀取操作巩那，所以經(jīng)常是整個(gè)查詢中耗時(shí)最長的狀態(tài)吏夯。
在獲取到最消耗時(shí)間的線程狀態(tài)后，MySQL支持進(jìn)一步選擇all即横、cpu噪生、block io、context switch令境、page faults等明細(xì)類型查看MySQL在使用什么資源上耗費(fèi)了過高的時(shí)間杠园。例如，選擇查看CPU的耗費(fèi)時(shí)間 ：
show PROFILE ALL for QUERY 58; 可以查看到所有的狀態(tài)耗時(shí)舔庶。
3.5 trace分析優(yōu)化器執(zhí)行計(jì)劃
MySQL5.6提供了對SQL的跟蹤trace抛蚁，通過trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A計(jì)劃，而不是選擇B計(jì)劃惕橙。
打開trace瞧甩，設(shè)置格式為JSON，并設(shè)置trace最大能夠使用的內(nèi)存大小弥鹦，避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整展示肚逸。
set optimizer_trace=“enabled=on”,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
執(zhí)行SQL語句 ：
select * from tb_item where id < 4;
最后，檢查information_schema.optimizer_trace就可以知道MySQL是如何執(zhí)行SQL的 ：
select * from information_schema.optimizer_trace\G;
開源的小米SQL優(yōu)化工具SOAR可以了解一下彬坏。
索引是數(shù)據(jù)庫優(yōu)化最常用也是最重要的手段之一朦促，通過索引通常可以幫助用戶解決大多數(shù)的MySQL的性能優(yōu)化問題栓始。
在表中存儲了300萬條記錄务冕；
A. 根據(jù)ID查詢
select * from tb_item where id = 1999;
查詢速度很快，接近0秒幻赚，主要的原因是因?yàn)閕d 為主鍵禀忆，有索引臊旭；
如果查詢條件沒有索引那么查詢效率會很低。
1）.全值匹配箩退，對索引中所有列都指定具體值离熏。
改情況，索引生效戴涝，執(zhí)行效率高滋戳。
explain select * from tb_seller where name = ‘小米科技’ and status = ‘1’ and address = ‘北京市’;
2）.最左前綴法則
如果索引多列，要遵守最左前綴法則喊括。指的是查詢從索引的最左前列開始胧瓜，并且不能跳過創(chuàng)建復(fù)合索引中的中間列矢棚。
匹配最左前綴法則郑什，走索引 ：
如下創(chuàng)建復(fù)合索引的列是name、status蒲肋、address蘑拯，那么查詢的時(shí)候就必需要有name列，不能跳過中間的status直接去查詢address兜粘，因?yàn)檫@樣索引會失效申窘。（where條件后面的and順序可以和復(fù)合索引的添加順序不一致）
explain select * from tb_seller where name=‘小米科技’ and address=‘北京市’;
這個(gè)時(shí)候雖然走了索引，但是只是name字段走列索引孔轴，address沒有走索引（看key_len只有403剃法，而name索引長度就是403）。所以可以無序但是不能跳過status字段進(jìn)行查詢路鹰，否則索引無效贷洲。
3）.范圍查詢右邊的列，不能使用索引晋柱。
根據(jù)前面的兩個(gè)字段name优构，status查詢是走索引的，但最后一個(gè)條件address沒有用到索引雁竞。
4）.不要在索引列上進(jìn)行運(yùn)算操作钦椭，索引將失效。
5）.字符串不加單引號碑诉，造成索引失效彪腔。
由于，在查詢中进栽，沒有對字符串加單引號德挣，MySQL的查詢優(yōu)化器，會自動(dòng)的進(jìn)行類型轉(zhuǎn)換泪幌，造成索引失效盲厌。
6）.盡量使用覆蓋索引署照，避免select *
盡量使用覆蓋索引（只訪問索引的查詢（索引列完全包含查詢列）），減少select *吗浩。
如果查詢列建芙，超出索引列，也會降低性能懂扼。
TIP ：
using index : 使用覆蓋索引的時(shí)候就會出現(xiàn)
using where ：在查找使用索引的情況下禁荸，需要回表去查詢所需的數(shù)據(jù)
using index condition ：查找使用列索引，但是需要回表查詢數(shù)據(jù)
using index 阀湿；using where ：查找使用了索引赶熟，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表查詢數(shù)據(jù)陷嘴。
7）用or分割開的條件映砖，如果or前的條件中的列有索引，而后面的列中沒有索引灾挨，那么涉及的索引都不會被用到邑退。
示例 ：name字段是索引列，而createtime不是索引列劳澄，中間是or進(jìn)行連接是不走索引的 ：
explain select * from tb_seller where name=‘姓名’ or createtime = ‘2018-01-01’地技；
8）以%開頭的Like模糊查詢，索引失效秒拔。
如果僅僅是尾部模糊匹配莫矗，索引不會失效。如果是頭部模糊匹配砂缩，索引失效作谚。
解決方案 ：通過覆蓋索引來解決；如下圖所示 ：
9）如果MySQL評估使用索引比全表更慢梯轻，則不使用索引食磕。
例如所查詢字段的address有11行是北京市，只有一行是西安市喳挑。那么MySQL的底層在進(jìn)行了優(yōu)化彬伦，執(zhí)行器會進(jìn)行判斷走索引不如全表查詢速度更快，所以就算給address添加索引伊诵，MySQL的評估也會使用全表查詢单绑。
10）is NULL，is NOT NULL 有時(shí)索引失效曹宴。
主要是因?yàn)樗樵兞械臄?shù)據(jù)是以null為主搂橙，還是以not null為主。如果以null為主那么where后面查詢列是is not null時(shí)笛坦，MySQL底層會判斷走索引更快一些区转，如果以not null為主那么where后面查詢列是is not null時(shí)苔巨，MySQL底層會判斷走全表會更快一些。反之亦然废离。
11）in走索引侄泽，not in索引失效。
12）單列索引和復(fù)合索引
盡量使用復(fù)合索引蜻韭，而少使用單列索引悼尾。
創(chuàng)建復(fù)合索引 ：
create index idx_name_sta_address on tb_seller(name,status,address);
就相當(dāng)于創(chuàng)建三個(gè)索引 ：
name
name + status
name + status + address
創(chuàng)建單列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
數(shù)據(jù)庫會選擇一個(gè)最油的索引來使用，并不會使用全部索引肖方。
show status like ‘Handler_read%’;
show global status like ‘Handler_read%’;
Handler_read_first : 索引中第一條被讀的次數(shù)闺魏。如果較高，表示服務(wù)器正執(zhí)行大量全索引掃描（這個(gè)值越低越好）俯画。
Handler_read_key : 如果索引正在工作析桥，這個(gè)值代表一個(gè)行被索引值讀的次數(shù)，如果值越低活翩，表示索引得到的性能改善不高烹骨，因?yàn)樗饕唤?jīng)常使用（這個(gè)值越高越好）翻伺。
Handler_read_next : 按照鍵順序讀下一行的請求數(shù)材泄。如果你用范圍約束或如果執(zhí)行索引掃描來查詢索引列，該值增加吨岭。
Handler_read_prev : 按照鍵順序讀取前一行的請求數(shù)拉宗。該讀方法主要用于優(yōu)化ORDER BY … DESC。
Handler_read_rnd : 根據(jù)固定位置讀一行的請求數(shù)辣辫。如果你正執(zhí)行大量查詢并需要對結(jié)果進(jìn)行排序該值較高旦事。你可能使用了大量需要MySQL掃描正整個(gè)表的查詢或你的連接沒有正確使用鍵。這個(gè)值較高急灭，意味著運(yùn)行效率低姐浮，應(yīng)該建立索引來補(bǔ)救。
Handler_read_rnd_next : 在數(shù)據(jù)文件中讀下一行的請求數(shù)葬馋。如果你正進(jìn)行大量的表掃描卖鲤，該值較高。通常說明你的表索引不正確或?qū)懭氲牟樵儧]有利用索引畴嘶。
當(dāng)使用load命令導(dǎo)入數(shù)據(jù)的時(shí)候蛋逾，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的效率。
對于InnoDB類型的表窗悯，有以下幾種方式可以提高導(dǎo)入的效率 ：
1）主鍵順序插入
因?yàn)镮nnoDB類型的表是按照主鍵的順序保存的区匣，所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排列，可以有效的提高導(dǎo)入數(shù)據(jù)的效率蒋院。如果InnoDB表沒有主鍵亏钩，那么系統(tǒng)會自動(dòng)默認(rèn)創(chuàng)建一個(gè)內(nèi)部列做為主鍵莲绰，所以如果可以給表創(chuàng)建一個(gè)主鍵，將可以利用這點(diǎn)姑丑，來提高導(dǎo)入數(shù)據(jù)的效率钉蒲。
插入ID順序排列的數(shù)據(jù) ：
下圖是有序數(shù)據(jù)導(dǎo)入的時(shí)間
下圖是無序數(shù)據(jù)導(dǎo)入的時(shí)間
2） 關(guān)閉唯一性校驗(yàn)
在導(dǎo)入數(shù)據(jù)前執(zhí)行SET_UNIQUE_CHECKS=0，關(guān)閉唯一性校驗(yàn)彻坛，在導(dǎo)入結(jié)束后執(zhí)行SET_UNIQUE_CHECKS=1顷啼，恢復(fù)唯一性校驗(yàn)，可以提高導(dǎo)入的效率昌屉。
3）手動(dòng)提交事務(wù)
如果應(yīng)用使用自動(dòng)提交的方式钙蒙，建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0，關(guān)閉自動(dòng)提交间驮，導(dǎo)入結(jié)束后再執(zhí)行SET AUTOCOMMIT=1躬厌，打開自動(dòng)提交，也可以提高導(dǎo)入的效率竞帽。
當(dāng)進(jìn)行數(shù)據(jù)的insert操作的時(shí)候扛施，可以考慮采用以下幾種優(yōu)化方案。
如果需要同時(shí)對一張表插入很多行數(shù)據(jù)時(shí)屹篓，應(yīng)該盡量使用多個(gè)值表的insert語句疙渣；這種方式將大大的縮減客戶端與數(shù)據(jù)庫之間的連接、關(guān)閉等消耗堆巧。使得效率比分開執(zhí)行的單個(gè)insert語句快妄荔。
示例，原始方式為 ：
優(yōu)化后的方案為 ：
在事務(wù)中進(jìn)行數(shù)據(jù)插入谍肤。
1）第一種是通過對返回?cái)?shù)據(jù)進(jìn)行排序啦租，也就是通常說的filesort排序，所有不是通過索引直接返回排序結(jié)果的排序都叫FileSort排序荒揣。
2）第二種通過有序索引順序掃描直接返回有序數(shù)據(jù)篷角，這種情況既為using index，不需要額外排序系任，操心效率高恳蹲。
多字段排序
1）order by多字段時(shí)，要么全部升序赋除，要么全部降序阱缓。并且排序字段使用復(fù)合索引字段。
2）當(dāng)為多字段排序時(shí)举农，排序字段順序要和創(chuàng)建復(fù)合索引的字段順序保持一致荆针。
了解MySQL的排序方式，優(yōu)化目標(biāo)就清晰了 ：
盡量減少額外的排序，通過索引直接返回有序數(shù)據(jù)航背。where條件和order by使用相同的索引喉悴，并且order by的順序和索引順序相同，并且order by的字段都是升序玖媚，或者都是降序箕肃。否則肯定需要額外的操作，這樣就會出現(xiàn)FileSort今魔。
通過創(chuàng)建合適的索引勺像，能夠減少FileSort的出現(xiàn)，但是在某些情況下错森，條件限制不能讓FileSort消失吟宦，那就需要加快FileSort的排序操作。對于FileSort涩维，MySQL有兩種排序算法 ：
1）兩次掃描算法 ：MySQL4.1之前殃姓，使用該方式排序。首先根據(jù)條件取出排序字段和行指針信息瓦阐，然后在排序區(qū)sort buffer中排序蜗侈，如果sort buffer不夠，則在臨時(shí)表temporary table中存儲排序結(jié)果睡蟋。完成排序之后踏幻，再根據(jù)行指針回表讀取記錄，該操作可能會導(dǎo)致大量隨機(jī)I/O操作薄湿。
2）一次掃描算法 ：一次性取出滿足條件的所有字段叫倍，然后在排序區(qū)sort buffer中排序后直接輸出結(jié)果集。排序時(shí)內(nèi)存開銷較大豺瘤，但是排序效率比兩次掃描算法要高。
MySQL通過比較系統(tǒng)變量max_length_for_sort_data的大小和Query語句取出的字段總大小听诸，來判定是否那種排序算法坐求，如果max_length_sort_data更大，那么使用第二種優(yōu)化之后的算法 ：否則使用第一種晌梨。
可以適當(dāng)提高sort_buffer_size和max_length_for_sort_data系統(tǒng)變量桥嗤，來增大排序區(qū)的大小，提高排序的效率仔蝌。
由于GROUP BY實(shí)際上也同樣會進(jìn)行排序操作，而且與ORDER BY相比，GROUP BY主要只是多了排序之后的分組操作涛碑。當(dāng)然胀莹，如果在分組的時(shí)候還使用來其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計(jì)算。所以锡宋，在GROUP BY的實(shí)現(xiàn)過程中儡湾，與ORDER BY一樣也可以利用索引。
如果查詢包含group by但是用戶想要避免排序結(jié)果的消耗执俩，則可以執(zhí)行oerder by null禁止排序徐钠。如下 ：
drop index inx_emp_age_salary on emp;
explain select age,count() from emp group by age;
優(yōu)化后
explain select age,count(
) from emp group by age order by null;
從上面的例子可以看出，第一個(gè)SQL語句需要進(jìn)行“filesort”役首，而第二SQL由于order by null不需要進(jìn)行“filesort”尝丐，而上文提過FileSort往往非常耗費(fèi)時(shí)間。
也可以通過創(chuàng)建索引提高分組列的效率
create index idx_emp_age_salary on emp(age,salary);
MySQL4.1版本之后衡奥，開始支持SQL的子查詢摊崭。這個(gè)技術(shù)可以使用SELECT語句來創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中杰赛。使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作呢簸，同時(shí)也可以避免事務(wù)或者表死鎖，并且寫起來也很容易乏屯。但是根时，有些情況下，子查詢是可以被更搞笑的連接（JOIN）替代辰晕。
示例 ：查找有角色的所有的用戶信息 ：
explain select * from t_user where id in (select user_id user_role);
執(zhí)行計(jì)劃為 ：
優(yōu)化后 ：
explain select * from t_user u, user_role ur where?u.id?= ur.user_id;
對于包含OR的查詢子句蛤迎，如果要利用索引，則OR之間的每個(gè)條件列都必須使用到索引含友，而且不能使用到復(fù)合索引替裆；如果沒有索引，則應(yīng)該考慮增加索引窘问。
獲取emp表的所有索引 ：
示例 ：
explain select * from emp where id = 1 or age = 30辆童；
建議使用union 替換or ：
這里的type中const要性能遠(yuǎn)高于range
我們來比較下重要指標(biāo)，發(fā)現(xiàn)主要差別是type和ref這兩項(xiàng)
type顯示的是訪問類型惠赫，是較為重要的一個(gè)指標(biāo)把鉴，結(jié)果值從好到壞依次是 ：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL
UNION語句的type值為const，OR語句的type值為range儿咱，可以看到這是一個(gè)很明顯的差距庭砍。
UNION語句的ref值為const，OR語句的type值為null混埠，const表示是常量值引用怠缸，非常快這兩項(xiàng)的差距就說明來
UNION要優(yōu)于OR钳宪。
一般分頁查詢時(shí)揭北，通過創(chuàng)建覆蓋索引能夠比較好的提高性能扳炬。一個(gè)常見又非常頭痛的問題就是 limit 20000000，10罐呼，此時(shí)需要MySQL排序前2000010記錄鞠柄，僅僅返回2000000 - 2000010的記錄，其他記錄丟棄嫉柴，查詢排序的代價(jià)非常大厌杜。
在索引上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容计螺。
兩個(gè)SQL的執(zhí)行計(jì)劃如下
該方案適用于主鍵自增的表（不能出現(xiàn)主鍵斷層）夯尽，可以把limit查詢轉(zhuǎn)換成某個(gè)位置的查詢。
SQL提示登馒，是優(yōu)化數(shù)據(jù)庫的一個(gè)重要手段匙握，簡單來說，就是在SQL語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的陈轿。
在查詢語句中表名的后面圈纺，添加use index來提供希望MySQL去參考的索引列表，就可以讓MySQL不再考慮其他可用的索引麦射。
create index idex_seller_name on tb_seller(name);
如果用戶只是單純的想讓MySQL忽略一個(gè)或者多個(gè)索引蛾娶，則可以使用ignore index作為hint
explain select * from tb_seller ignore index(idx_seller_name) where name = ‘小米科技’;
為強(qiáng)制MySQL使用一個(gè)特定的索引，可在查詢中使用force index作為hint潜秋。
create index inx_seller_address on tb_seller(address);
