玄幻小说排行榜完本,盗墓笔记同人小说

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

mysql執(zhí)行計(jì)劃知識點(diǎn)有哪些

這篇文章主要講解了“MySQL執(zhí)行計(jì)劃知識點(diǎn)有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“mysql執(zhí)行計(jì)劃知識點(diǎn)有哪些”吧！

創(chuàng)新互聯(lián)專注于多倫企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè),成都商城網(wǎng)站開發(fā)。多倫網(wǎng)站建設(shè)公司,為多倫等地區(qū)提供建站服務(wù)。全流程定制網(wǎng)站建設(shè)，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

The DESCRIBE and EXPLAIN statements are synonyms, used either to obtain information about table structure or query execution plans.

DESCRIBE和EXPLAIN語句是同義詞，用于獲得表結(jié)構(gòu)信息和SQL語句的執(zhí)行計(jì)劃。

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query). The following discussion uses the DESCRIBE and EXPLAIN keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

DESCRIBE和EXPLAIN語句是同義詞，實(shí)際上在平時(shí)使用過程中DESCRIBE多用于獲取表結(jié)構(gòu)的信息，然后EXPLAIN多用于獲取SQL語句的執(zhí)行計(jì)劃。MySQL解析器對這兩個(gè)語句是完全作為同義詞對待的。

mysql> desc mysql.plugin;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| name | varchar(64) | NO | PRI | | |

| dl | varchar(128) | NO | | | |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> explain mysql.plugin;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| name | varchar(64) | NO | PRI | | |

| dl | varchar(128) | NO | | | |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> desc select * from mysql.plugin;

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

1 row in set (0.07 sec)

mysql> explain select * from mysql.plugin;

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

1 row in set (0.00 sec)

EXPLAIN和DESCRIBE的語法（DESC是DESCRIBE 的縮寫）

{EXPLAIN | DESCRIBE | DESC}

tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}

[explain_type]

{explainable_stmt | FOR CONNECTION connection_id}

explain_type: {

EXTENDED

| PARTITIONS

| FORMAT = format_name

}

format_name: {

TRADITIONAL

| JSON

}

explainable_stmt: {

SELECT statement

| DELETE statement

| INSERT statement

| REPLACE statement

| UPDATE statement

}

1）EXPLAIN和DESCRIBE同樣可以查看表字段

{EXPLAIN | DESCRIBE | DESC}

tbl_name [col_name | wild]

mysql> desc mysql.plugin name;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| name | varchar(64) | NO | PRI | | |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

2）解析類型

{EXPLAIN | DESCRIBE | DESC}

[explain_type]

{explainable_stmt | FOR CONNECTION connection_id}

explain_type: {

EXTENDED

| PARTITIONS

| FORMAT = format_name

}

EXPLAIN EXTENDED：獲取執(zhí)行計(jì)劃額外的信息

EXPLAIN PARTITIONS ：是用于涉及到分區(qū)表的語句

EXPLAIN FORMAT

mysql> EXPLAIN FORMAT=JSON select * from mysql.user where user='root';

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN |

| {

"query_block": {

"select_id": 1,

"table": {

"table_name": "user",

"access_type": "ALL",

"rows": 6,

"filtered": 100,

"attached_condition": "(`mysql`.`user`.`User` = 'root')"

}

} |

1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN FORMAT=TRADITIONAL select * from mysql.user where user='root';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

如果不添加FORMAT默認(rèn)為TRADITIONAL

3）explainable_stmt

EXPLAIN 支持SELECT DELETE INSERT REPLACE UPDATE 語句

EXPLAIN Output Columns（執(zhí)行計(jì)劃輸出的列）

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered（5.7）	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

id (JSON name: select_id)

執(zhí)行計(jì)劃各個(gè)子任務(wù)的序號，這些序號是有序的。如果數(shù)據(jù)行指向其他行的聯(lián)合結(jié)果，該值可以為空，此時(shí)會(huì)顯示去說明指向的數(shù)據(jù)行。

select_type (JSON name: none)

執(zhí)行計(jì)劃各個(gè)子任務(wù)的類型，下面是所有的類型

select_type Value	JSON Name	Meaning
SIMPLE	None	簡單查詢，不使用聯(lián)合查詢和子查詢
PRIMARY	None	最外層的查詢
UNION	None	聯(lián)合查詢中第二個(gè)或者后面的語句
DEPENDENT UNION	dependent (true)	聯(lián)合查詢中第二個(gè)或者后面的語句，取決于外面的查詢
UNION RESULT	union_result	聯(lián)合查詢的結(jié)果
SUBQUERY	None	子查詢中的第一個(gè)查詢
DEPENDENT SUBQUERY	dependent (true)	子查詢中的第一個(gè)查詢，取決于外面的查詢
DERIVED	None	FROM后面的子查詢
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

table (JSON name: table_name)

輸出行的表的名稱，也可以是下面的值

- : The row refers to the union of the rows with id values of M and N.

- : The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

- : The row refers to the result of a materialized subquery for the row with an id value of N. See Section 9.2.2.2, “Optimizing Subqueries with Materialization”.

partitions (JSON name: partitions)

查詢匹配到的分區(qū)名稱，如果值為NULL說明沒有涉及分區(qū)表。

type (JSON name: access_type)

聯(lián)合join的類型，下面是各個(gè)類型：

system 連接系統(tǒng)表，表中只有一行數(shù)據(jù)

const 讀常量，且最多只會(huì)有一條數(shù)據(jù)，一般是使用主鍵或者唯一索引匹配常量（速度非?？欤?/p>

eq_ref 最多只會(huì)有一條匹配結(jié)果，一般是通過主鍵或者唯一鍵索引來訪問或者連接（除system、const最快的連接）

ref Join 語句中被驅(qū)動(dòng)表索引引用查詢

fulltext 使用fulltext索引

ref_or_null 和ref唯一區(qū)別是，多了null值查詢

index_merge 查詢中同時(shí)使用兩個(gè)（或更多）索引，然后對索引結(jié)果進(jìn)行merge 之后再讀取表數(shù)據(jù)

unique_subquery 子查詢中的返回結(jié)果字段組合是主鍵或者唯一約束

index_subquery 子查詢中的返回結(jié)果字段組合是一個(gè)索引（或索引組合），但不是一個(gè)主鍵或者唯一索引

range 索引范圍掃描

index 全索引掃描（1覆蓋索引的全表查詢的情況，2全表查詢，通過先查索引再查數(shù)據(jù)的情況）

ALL 全表掃描

possible_keys (JSON name: possible_keys)

possible_keys列指出MySQL能使用哪個(gè)索引在該表中找到行。注意，該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。

如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

key (JSON name: key)

key列顯示MySQL實(shí)際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len (JSON name: key_length)

key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。

使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref (JSON name: ref)

ref列顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

如果該列的值為func，說明存在額外信息，可以使用SHOW WARNINGS去查看。

rows (JSON name: rows)

MySQL預(yù)估計(jì)的查詢需要執(zhí)行的行數(shù)。

對于InnoDB表，該值不一定準(zhǔn)確。

filtered (JSON name: filtered)（5.7）

預(yù)估的獲取的數(shù)據(jù)量在表中的百分比

Extra (JSON name: none)

這列包含了MYSQL如何處理語句的解決方案的額外信息。

Child of 'table' pushed join@1

const row not found

Deleting all rows

Distinct

FirstMatch(tbl_name)

Full scan on NULL key

Impossible HAVING

Impossible WHERE

Impossible WHERE noticed after reading const tables

LooseScan(m..n)

No matching min/max row

no matching row in const table

No matching rows after partition pruning

No tables used

Not exists

Plan isn't ready yet

Range checked for each record

Scanned N databases

Select tables optimized away

Skip_open_table, Open_frm_only, Open_full_table

Start temporary, End temporary

unique row not found

Using filesort 當(dāng)我們的Query 中包含ORDER BY 操作，而且無法利用索引完成排序操作的時(shí)候，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來實(shí)現(xiàn)

Using index 所需要的數(shù)據(jù)只需要在Index 即可全部獲得而不需要再到表中取數(shù)據(jù)

Using index condition

Using index for group-by 數(shù)據(jù)訪問和Using index 一樣，所需數(shù)據(jù)只需要讀取索引即可，而當(dāng)Query 中使用了GROUPBY 或者DISTINCT 子句的時(shí)候，如果分組字段也在索引中，Extra 中的信息就會(huì)是Using index forgroup-by

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

Using MRR

Using sort_union(...), Using union(...), Using intersect(...)

Using temporary 當(dāng)MySQL 在某些操作中必須使用臨時(shí)表的時(shí)候，在Extra 信息中就會(huì)出現(xiàn)Using temporary 。主要常見于GROUP BY 和ORDER BY 等操作中

Using where 如果我們不是讀取表的所有數(shù)據(jù)，或者不是僅僅通過索引就可以獲取所有需要的數(shù)據(jù)，則會(huì)出現(xiàn)Using where 信息

Using where with pushed condition 這是一個(gè)僅僅在NDBCluster 存儲(chǔ)引擎中才會(huì)出現(xiàn)的信息，而且還需要通過打開ConditionPushdown 優(yōu)化功能才可能會(huì)被使用。控制參數(shù)為engine_condition_pushdown

Zero limit

感謝各位的閱讀，以上就是“mysql執(zhí)行計(jì)劃知識點(diǎn)有哪些”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對mysql執(zhí)行計(jì)劃知識點(diǎn)有哪些這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！

網(wǎng)頁標(biāo)題：mysql執(zhí)行計(jì)劃知識點(diǎn)有哪些
文章鏈接：http://fisionsoft.com.cn/article/piessp.html

新聞中心

其他資訊