語(yǔ)句執(zhí)行后,會(huì)顯示三個(gè)字段: Query_ID(執(zhí)行ID) | Duration(持續(xù)時(shí)間)| Query(查詢語(yǔ)句) ;
創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比伊春網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式伊春網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們,業(yè)務(wù)覆蓋伊春地區(qū)。費(fèi)用合理售后完善,十多年實(shí)體公司更值得信賴。
拿到后Query_ID后,可執(zhí)行 show profile for query Query_ID ,查看詳細(xì)的準(zhǔn)備時(shí)間,執(zhí)行時(shí)間、執(zhí)行結(jié)束( preparing、executing、end )等。
顯示用戶正在運(yùn)行的線程,需要注意的是,除了 root 用戶能看到所有正在運(yùn)行的線程外,其他用戶都只能看到自己正在運(yùn)行的線程,看不到其它用戶正在運(yùn)行的線程。除非單獨(dú)個(gè)這個(gè)用戶賦予了PROCESS 權(quán)限。
顯示字段包含: User| Host| db | Command | Time| State| Info 等。
解析語(yǔ)句,查詢是否命中索引,及,命中何種索引,用以判斷是否符合我們的預(yù)期。
返回字段包含: select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 等。
select_type 常見(jiàn)類型:
(1) SIMPLE(簡(jiǎn)單SELECT,不使用UNION或子查詢等)
(2) PRIMARY(子查詢中最外層查詢,查詢中若包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY)
(3) UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句)
(4) SUBQUERY(子查詢中的第一個(gè)SELECT,結(jié)果不依賴于外部查詢)
table 常見(jiàn)類型:
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的.
有時(shí)不是真實(shí)的表名字,看到的是derivedx(x是個(gè)數(shù)字,我的理解是第幾步執(zhí)行的結(jié)果)
type 常見(jiàn)類型:
對(duì)表訪問(wèn)方式,表示MySQL在表中找到所需行的方式,又稱“訪問(wèn)類型”。
常用的類型有: ALL、index、range、 ref、eq_ref、const、system、NULL (從左到右,性能從差到好)
possible_keys
指出MySQL能使用哪個(gè)索引在表中找到記錄,查詢涉及到的字段上若存在索引,則該索引將被列出,但不一定被查詢使用(該查詢可以利用的索引,如果沒(méi)有任何索引顯示 null)
該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。
如果該列是NULL,則沒(méi)有相關(guān)的索引。在這種情況下,可以通過(guò)檢查WHERE子句看是否它引用某些列或適合索引的列來(lái)提高你的查詢性能。如果是這樣,創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢
key
key列顯示MySQL實(shí)際決定使用的鍵(索引),必然包含在possible_keys中
如果沒(méi)有選擇索引,鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引,在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len
表示索引中使用的字節(jié)數(shù),可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度,非實(shí)際長(zhǎng)度,為最大可能長(zhǎng)度。
注:不損失精確性的情況下,長(zhǎng)度越短越好。
ref
列與索引的比較,表示上述表的連接匹配條件,即哪些列或常量被用于查找索引列上的值。
rows
估算出結(jié)果集行數(shù),表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況,估算的找到所需的記錄所需要讀取的行數(shù);
extra
該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況:
(1).Distinct
一旦MYSQL找到了與行相聯(lián)合匹配的行,就不再搜索了
(2).Not exists
MYSQL優(yōu)化了LEFT JOIN,一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行,就不再搜索了
(3).Range checked for each
Record(index map:#)
沒(méi)有找到理想的索引,因此對(duì)于從前面表中來(lái)的每一個(gè)行組合,MYSQL檢查使用哪個(gè)索引,并用它來(lái)從表中返回行。這是使用索引的最慢的連接之一
(4).Using filesort
看到這個(gè)的時(shí)候,查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行;
(5).Using temporary
看到這個(gè)的時(shí)候,查詢需要優(yōu)化了。這里,MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果,這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上,而不是GROUP BY上;
(6).Using index
列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的,這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候。
(7).Using where
使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行,并且連接類型ALL或index,這就會(huì)發(fā)生,或者是查詢有問(wèn)題。
子查詢優(yōu)化策略
對(duì)于不同類型的子查詢,優(yōu)化器會(huì)選擇不同的策略。
1. 對(duì)于 IN、=ANY 子查詢,優(yōu)化器有如下策略選擇:
semijoin
Materialization
exists
2. 對(duì)于 NOT IN、ALL 子查詢,優(yōu)化器有如下策略選擇:
Materialization
exists
3. 對(duì)于 derived 派生表,優(yōu)化器有如下策略選擇:
derived_merge,將派生表合并到外部查詢中(5.7 引入 );
將派生表物化為內(nèi)部臨時(shí)表,再用于外部查詢。
注意:update 和 delete 語(yǔ)句中子查詢不能使用 semijoin、materialization 優(yōu)化策略
??聚集索引:數(shù)據(jù)行的物理順序與列值(一般是主鍵的那一列)的邏輯順序相同,一個(gè)表中只能擁有一個(gè)聚集索引。 葉子結(jié)點(diǎn)存儲(chǔ)索引和行記錄,聚簇索引查詢會(huì)很快,因?yàn)榭梢灾苯佣ㄎ坏叫杏涗洝?/p>
??非聚集索引:該索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同,一個(gè)表中可以擁有多個(gè)非聚集索引。 葉子節(jié)點(diǎn)存儲(chǔ)聚簇索引值(主鍵id),需要掃碼兩遍索引樹(shù),先通過(guò)普通索引定位到主鍵值id,再通過(guò)聚集索引定位到行記錄。
??回表查詢可以理解為普通索引的查詢,先定位主鍵值,再定位行記錄,它的性能較掃一遍索引樹(shù)更低。
??索引覆蓋,即將查詢sql中的字段添加到聯(lián)合索引里面,只要保證查詢語(yǔ)句里面的字段都在索引文件中,就無(wú)需進(jìn)行回表查詢;
??實(shí)際開(kāi)發(fā)中,不可能把所有字段建立到聯(lián)合索引,可根據(jù)實(shí)際業(yè)務(wù)場(chǎng)景,把經(jīng)常需要查詢的字段建立到聯(lián)合索引中。
?? 在Mysql5.6的版本上推出,用于優(yōu)化查詢。 在索引遍歷過(guò)程中,對(duì)索引中包含的字段先做判斷,直接過(guò)濾掉不滿足條件的記錄,減少回表次數(shù)。
?? 優(yōu)化超多分頁(yè)場(chǎng)景。 查詢條件放到子查詢中,子查詢只查主鍵id,然后使用子查詢中確定的主鍵關(guān)聯(lián)查詢其他的屬性字段。
mysql的優(yōu)化大的有兩方面:
1、配置優(yōu)化
配置的優(yōu)化其實(shí)包含兩個(gè)方面的:操作系統(tǒng)內(nèi)核的優(yōu)化和mysql配置文件的優(yōu)化
1)系統(tǒng)內(nèi)核的優(yōu)化對(duì)專用的mysql服務(wù)器來(lái)說(shuō),無(wú)非是內(nèi)存實(shí)用、連接數(shù)、超時(shí)處理、TCP處理等方面的優(yōu)化,根據(jù)自己的硬件配置來(lái)進(jìn)行優(yōu)化,這里不多講;
2)mysql配置的優(yōu)化,一般來(lái)說(shuō)包含:IO處理的常用參數(shù)、最大連接數(shù)設(shè)置、緩存使用參數(shù)的設(shè)置、慢日志的參數(shù)的設(shè)置、innodb相關(guān)參數(shù)的設(shè)置等,如果有主從關(guān)系在設(shè)置主從同步的相關(guān)參數(shù)即可,網(wǎng)上的相關(guān)配置文件很多,大同小異,常用的設(shè)置大多修改這些差不多就夠用了。
2、sql語(yǔ)句的優(yōu)化
1) ?盡量稍作計(jì)算
Mysql的作用是用來(lái)存取數(shù)據(jù)的,不是做計(jì)算的,做計(jì)算的話可以用其他方法去實(shí)現(xiàn),mysql做計(jì)算是很耗資源的。
2)盡量少 join
MySQL 的優(yōu)勢(shì)在于簡(jiǎn)單,但這在某些方面其實(shí)也是其劣勢(shì)。MySQL 優(yōu)化器效率高,但是由于其統(tǒng)計(jì)信息的量有限,優(yōu)化器工作過(guò)程出現(xiàn)偏差的可能性也就更多。對(duì)于復(fù)雜的多表 Join,一方面由于其優(yōu)化器受限,再者在 Join 這方面所下的功夫還不夠,所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫(kù)前輩還是有一定距離。但如果是簡(jiǎn)單的單表查詢,這一差距就會(huì)極小甚至在有些場(chǎng)景下要優(yōu)于這些數(shù)據(jù)庫(kù)前輩
3)盡量少排序
排序操作會(huì)消耗較多的 CPU 資源,所以減少排序可以在緩存命中率高等 IO 能力足夠的場(chǎng)景下會(huì)較大影響 SQL的響應(yīng)時(shí)間。
對(duì)于MySQL來(lái)說(shuō),減少排序有多種辦法,比如:
通過(guò)利用索引來(lái)排序的方式進(jìn)行優(yōu)化
減少參與排序的記錄條數(shù)
非必要不對(duì)數(shù)據(jù)進(jìn)行排序
4)盡量避免 select *
在數(shù)據(jù)量少并且訪問(wèn)量不大的情況下,select * 沒(méi)有什么影響,但是量級(jí)達(dá)到一定級(jí)別的時(shí)候,在執(zhí)行效率和IO資源的使用上,還是有很大關(guān)系的,用什么字段取什么字段,減少不必要的資源浪費(fèi)。
5)盡量用 join 代替子查詢
雖然 Join 性能并不佳,但是和 MySQL 的子查詢比起來(lái)還是有非常大的性能優(yōu)勢(shì)。MySQL 的子查詢執(zhí)行計(jì)劃一直存在較大的問(wèn)題,雖然這個(gè)問(wèn)題已經(jīng)存在多年,但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在,一直沒(méi)有太大改善。雖然官方也在很早就承認(rèn)這一問(wèn)題,并且承諾盡快解決,但是至少到目前為止我們還沒(méi)有看到哪一個(gè)版本較好的解決了這一問(wèn)題。
如果列比較多的話,建議別用*,
你這種最適合with as這種臨時(shí)表 ,
使用子查詢的方法表被掃描了多次,而使用WITH Clause方法,表僅被掃描一次。這樣可以大大的提高數(shù)據(jù)分析和查詢的效率。
子查詢優(yōu)化策略
對(duì)于不同類型的子查詢,優(yōu)化器會(huì)選擇不同的策略。
1. 對(duì)于 IN、=ANY 子查詢,優(yōu)化器有如下策略選擇:
semijoin
Materialization
exists
2. 對(duì)于 NOT IN、ALL 子查詢,優(yōu)化器有如下策略選擇:
Materialization
exists
3. 對(duì)于 derived 派生表,優(yōu)化器有如下策略選擇:
derived_merge,將派生表合并到外部查詢中(5.7 引入 );
將派生表物化為內(nèi)部臨時(shí)表,再用于外部查詢。
注意:update 和 delete 語(yǔ)句中子查詢不能使用 semijoin、materialization 優(yōu)化策略