執(zhí)行過(guò)程：
連接器：
建立與 MySQL 的連接，用于查詢SQL語(yǔ)句，判斷權(quán)限 。
查詢緩存：
如果語(yǔ)句不在查詢緩存中，就會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果會(huì)被存入查詢緩存中如果查詢命中緩存，MySQL不需要執(zhí)行后面的復(fù)雜操作，就可以直接返回結(jié)果，提升效率分析器：
對(duì) SQL 語(yǔ)句進(jìn)行硬解析，分析器先會(huì)做詞法分析。分析SQL 語(yǔ)句的組成成分。判斷輸入的 SQL 語(yǔ)句是否滿足語(yǔ)法規(guī)則。
優(yōu)化器：
優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。 不同的執(zhí)行方法的邏輯結(jié)果是一樣的，但是執(zhí)行的效率會(huì)有不同，而優(yōu)化器的作用就是決定選擇使用哪一個(gè)方案。
執(zhí)行器：
有索引：第一次調(diào)用的是取滿足條件的第一行這個(gè)接口，之后循環(huán)取滿足條件的下一行這個(gè)接口，最終把查詢結(jié)果返回客戶端無(wú)索引：調(diào)用 InnoDB 引擎接口取這個(gè)表的第一行，判斷sql查詢條件，如果不是則跳過(guò)，如果是則將這行存在結(jié)果集中； 調(diào)用引擎接口取下一行，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行。 執(zhí)行器將上述遍歷過(guò)程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端理解執(zhí)行計(jì)劃
EXPLAIN命令輸出MySQL將如何執(zhí)行你的SQL語(yǔ)句，但不會(huì)返回?cái)?shù)據(jù)
如何使用
[root@localhost][(none)]> explain select * from 表名 where project_id = 36;
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | 表名 | NULL | ref | project_id | project_id | 4 | const | 797964 | 100.00 | NULL |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+復(fù)制代碼
id
id相同執(zhí)行順序由上至下id不同，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行select_type
SIMPLE：簡(jiǎn)單的 select 查詢，查詢中不包含子查詢或者 union PRIMARY：查詢中包含子部分，最外層查詢則被標(biāo)記為 primary DERIVED：是子查詢from的一部分DEPENDENT SUBQUERY：子查詢中的第一個(gè)SELECT，子查詢依賴于外層查詢的結(jié)果SUBQUERY 表示在 select 或 where 列表中包含了子查詢，MATERIALIZED：表示 where 后面 in 條件的子查詢 UNION：表示 union 中的第二個(gè)或后面的 select 語(yǔ)句 UNION RESULT：union 的結(jié)果 table
表對(duì)象type
system > const > eq_ref > ref > range > index > ALL（查詢效率）
system：表中只有一條數(shù)據(jù)，這個(gè)類型是特殊的const類型const：針對(duì)于主鍵或唯一索引的等值查詢掃描，最多只返回一個(gè)行數(shù)據(jù)。速度非常快，因?yàn)橹蛔x取一次即可。eq_ref：此類型通常出現(xiàn)在多表的join查詢，表示對(duì)于前表的每一個(gè)結(jié)果，都只能匹配到后表的一行結(jié)果，并且查詢的比較操作通常是=，查詢效率較高ref：此類型通常出現(xiàn)在多表的join查詢，針對(duì)于非唯一或非主鍵索引，或者是使用了最左前綴規(guī)則索引的查詢range：范圍掃描 這個(gè)類型通常出現(xiàn)在 <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中index：索引樹掃描 ALL：全表掃描（full table scan） possible_keys
可能使用的索引，注意不一定會(huì)使用查詢涉及到的字段上若存在索引，則該索引將被列出來(lái)當(dāng)該列為NULL時(shí)就要考慮當(dāng)前的SQL是否需要優(yōu)化了key
顯示MySQL在查詢中實(shí)際使用的索引，若沒(méi)有使用索引，顯示NULL。 查詢中若使用了覆蓋索引(覆蓋索引：索引的數(shù)據(jù)覆蓋了需要查詢的所有數(shù)據(jù))，則該索引僅出現(xiàn)在key列表中 key_length
索引長(zhǎng)度 ref
表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值 rows
返回估算的結(jié)果集數(shù)目，并不是準(zhǔn)確的值filtered
示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比， filtered 的值越大越好extra
Using where：表示優(yōu)化器需要通過(guò)索引回表，之后到server層進(jìn)行過(guò)濾查詢數(shù)據(jù) Using index：表示直接訪問(wèn)索引就足夠獲取到所需要的數(shù)據(jù)，不需要回表 Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown） Using index for group-by：使用了索引來(lái)進(jìn)行GROUP BY或者DISTINCT的查詢Using filesort：當(dāng) Extra 中有 Using filesort 時(shí), 表示 MySQL 需額外的排序操作, 不?能通過(guò)索引順序達(dá)到排序效果. 一般有 Using filesort, 都建議優(yōu)化去掉, 因?yàn)檫@樣的查詢 CPU 資源消耗大Using temporary 臨時(shí)表被使用，時(shí)常出現(xiàn)在GROUP BY和ORDER BY子句情況下。（sort buffer或者磁盤被使用） 光看 filesort 字面意思，可能以為是要利用磁盤文件進(jìn)行排序，實(shí)則不全然。 當(dāng)MySQL不能使用索引進(jìn)行排序時(shí)，就會(huì)利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對(duì)數(shù)據(jù)進(jìn)行排序，如果內(nèi)存裝載不下，它會(huì)將磁盤上的數(shù)據(jù)進(jìn)行分塊，再對(duì)各個(gè) 數(shù)據(jù)塊進(jìn)行排序，然后將各個(gè)塊合并成有序的結(jié)果集（實(shí)際上就是外排序）。
當(dāng)對(duì)連接操作進(jìn)行排序時(shí)，如果ORDER BY僅僅引用第一個(gè)表的列，MySQL對(duì)該表進(jìn)行filesort操作，然后進(jìn)行連接處理，此時(shí)，EXPLAIN輸出“Using filesort”；否則，MySQL必 須將查詢的結(jié)果集生成一個(gè)臨時(shí)表，在連接完成之后行?filesort操作，此時(shí)，EXPLAIN輸出“Using temporary;Using filesort”。
提高查詢效率
正確使用索引
為解釋方便，來(lái)一個(gè)demo：
DROP TABLE IF EXISTS user;
CREATE TABLE user(
id int AUTO_INCREMENT PRIMARY KEY,
user_name varchar(30) NOT NULL,
gender bit(1) NOT NULL DEFAULT b’1’,
city varchar(50) NOT NULL,
age int NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE user ADD INDEX idx_user(user_name , city , age);
復(fù)制代碼
什么樣的索引可以被使用？
**全匹配：**SELECT * FROM user WHERE user_name='JueJin'AND age='5' AND city='上海';（與where后查詢條件的順序無(wú)關(guān)） 匹配最左前綴：(user_name )、(user_name, city)、(user_name , city , age)（滿足最左前綴查詢條件的順序與索引列的順序無(wú)關(guān)，如：(city, user_name)、(age, city, user_name)） **匹配列前綴：**SELECT * FROM user WHERE user_name LIKE 'W%' **匹配范圍值：**SELECT * FROM user WHERE user_name BETWEEN 'W%' AND 'Z%'什么樣的索引無(wú)法被使用？
**where查詢條件中不包含索引列中的最左索引列，則無(wú)法使用到索引： ** SELECT * FROM user WHERE city='上海';
SELECT * FROM user WHERE age='26';
SELECT * FROM user WHERE age='26' AND city=‘上海';
**即使where的查詢條件是最左索引列，也無(wú)法使用索引查詢用戶名以N結(jié)尾的用戶： ** SELECT * FROM user WHERE user_name LIKE '%N';
**如果where查詢條件中有某個(gè)列的范圍查詢，其右邊的所有列都無(wú)法使用索引優(yōu)化查詢： ** SELECT * FROM user WHERE user_name='JueJin' AND city LIKE '上%' AND age=31;
**索引列不能是表達(dá)式的一部分，也不能作為函數(shù)的參數(shù)，否則無(wú)法使用索引查詢： ** SELECT * FROM user WHERE user_name=concat(user_name,'PLUS');
選擇合適的索引列順序
在組合索引的創(chuàng)建中索引列的順序非常重要，正確的索引順序依賴于使用該索引的查詢的查詢方式對(duì)于組合索引的索引順序可以將選擇性最高的列放到索引最前列，該法則與前綴索引的選擇性方法一致并不是說(shuō)所有的組合索引的順序都使用該法則就能確定，還需要根據(jù)具體的查詢場(chǎng)景來(lái)確定具體的索引順序覆蓋索引條件
如果一個(gè)索引中包含所有要查詢的字段的值，那么就稱之為覆蓋索引 SELECT user_name, city, age FROM user WHERE user_name='Tony' AND age='28' AND city='上海';
因?yàn)橐樵兊淖侄?user_name, city, age)都包含在組合索引的索引列中，所以就使用了覆蓋索引查詢，查看是否使用了覆蓋索引可以通過(guò)執(zhí)行計(jì)劃中的Extra中的值為Using index則證明使用了覆蓋索引，覆蓋索引可以極大的提高訪問(wèn)性能。
使用索引進(jìn)行排序
在排序操作中如果能使用到索引來(lái)排序，那么可以極大地提高排序的速度，要使用索引來(lái)排序需要滿足以下兩點(diǎn)即可：
ORDER BY子句后的列順序要與組合索引的列順序一致，且所有排序列的排序方向(正序/倒序)需一致 所查詢的字段值需要包含在索引列中，及滿足覆蓋索引排序可用demo：
SELECT user_name, city, age FROM user_test ORDER BY user_name;SELECT user_name, city, age FROM user_test ORDER BY user_name,city; SELECT user_name, city, age FROM user_test ORDER BY user_name DESC,city DESC; SELECT user_name, city, age FROM user_test WHERE user_name='Tony' ORDER BY city;排序不可用demo：
SELECT user_name, city, age FROM user_test ORDER BY user_name gender; SELECT user_name, city, age, gender FROM user_test ORDER BY user_name; SELECT user_name, city, age FROM user_test ORDER BY user_name ASC,city DESC; SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'W%' ORDER BY city;數(shù)據(jù)獲取建議
不要返回應(yīng)用戶程序所不需要的數(shù)據(jù)限制返回?cái)?shù)
LIMIT：MySQL并不能按照需求返回?cái)?shù)據(jù)量，也就是MySQL總是會(huì)查詢出全部數(shù)據(jù)，使用LIMIT子句其實(shí)是為了減小網(wǎng)絡(luò)數(shù)據(jù)傳輸?shù)膲毫Γ⒉粫?huì)減小數(shù)據(jù)的讀取行數(shù)。
去掉不需要的列
SELECT * 語(yǔ)句取出表中的所有字段，不論該字段的數(shù)據(jù)對(duì)調(diào)用的應(yīng)用程序是否有用，這會(huì)對(duì)服務(wù)器資源造成浪費(fèi)，甚至?xí)?duì)服務(wù)器的性能產(chǎn)生一定的影響 如果表的結(jié)構(gòu)在以后發(fā)生了改變，那么 SELECT * 語(yǔ)句可能會(huì)取到不正確的數(shù)據(jù) 執(zhí)行 SELECT * 語(yǔ)句時(shí)，首先要查找出表中有哪些列，然后才能開始執(zhí)行 SELECT * 語(yǔ)句，這在某些情況會(huì)產(chǎn)生性能問(wèn)題 使用 SELECT * 語(yǔ)句將不會(huì)使到覆蓋索引，不利于查詢的性能優(yōu)化正確使用索引的優(yōu)點(diǎn)
避免全表掃描 - 單表查詢時(shí)，全表掃描需要查詢每一行
- 多表查詢時(shí)，全表掃描至少需要檢索所有表中每一行
提高速度 - 可以迅速定位結(jié)果集的第一行
- 排除不相關(guān)的結(jié)果
- 對(duì)于MIN()或者M(jìn)AX()值不必檢查每一行
提高排序和分組的效率 在可以使用覆蓋索引的情況下避免row loop-up索引的代價(jià)
如果存在過(guò)多索引，數(shù)據(jù)修改將會(huì)變得緩慢 - 受影響的索引需要被更新
- 對(duì)于寫密集型環(huán)境壓力很大
索引消耗過(guò)多磁盤空間 - InnoDB存儲(chǔ)引擎將索引和數(shù)據(jù)存儲(chǔ)在一起
- 需要監(jiān)控磁盤空間
索引最佳實(shí)踐
對(duì)于如下列考慮使用索引
WHERE子句中的列 ORDER BY或GROUP BY子句中的列 表連接條件列考慮針對(duì)字符串型列使用前綴索引
可以更快速地比較與loop up 減少磁盤I/OSELECT語(yǔ)句效率低下時(shí)考慮
避免全表掃描 嘗試增加索引 - WHERE語(yǔ)句
- 表連接條件
利用ANALYZE TABLE來(lái)收集統(tǒng)計(jì)信息 考慮存儲(chǔ)引擎層的優(yōu)化調(diào)優(yōu)表連接方法
在ON或USING子句的列上增加索引 利用SELECT STRAIGHT_JOIN來(lái)強(qiáng)制表連接順序 在ORDER BY和GROUP BY的列上增加索引 join連接不一定比子查詢效率高更多相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql教程(視頻)
聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
