MySQL explain - 版本历史

2023年12月19日 (二) 10:02 Riguz

2023-12-19T10:02:37Z

←上一版本		2023年12月19日 (二) 10:02的版本
第51行：		第51行：
	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:Database]]		[[Category:Database]]
			[[Category:MySQL]]

Riguz：Riguz移动页面MySQL:执行计划至MySQL explain，不留重定向

2023-12-19T10:02:18Z

Riguz移动页面MySQL:执行计划至MySQL explain，不留重定向

←上一版本	2023年12月19日 (二) 10:02的版本
（没有差异）

Riguz：/* 例子 */

2021-05-03T14:00:00Z

例子

←上一版本		2021年5月3日 (一) 14:00的版本
第46行：		第46行：
	*using where：A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.		*using where：A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
	*using index condition：Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.		*using index condition：Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.
	*using index：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.		*using index：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

	For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
	*using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。		*using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:Database]]		[[Category:Database]]

Riguz：/* 例子 */

2021-05-03T13:59:36Z

例子

←上一版本		2021年5月3日 (一) 13:59的版本
第44行：		第44行：

	Extra显示的索引扫描方式:		Extra显示的索引扫描方式:
	*using ~~where：查询使用索引的情况下，需要回表去查询所需的数据。~~		*using where：A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
	*using index ~~condition：查询使用了索引，但是需要回表查询数据。~~		*using index condition：Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.
	*using ~~index：查询使用覆盖索引的时候会出现。~~		*using index：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

			For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
	*using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。		*using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:Database]]		[[Category:Database]]

Riguz：/* 例子 */

2021-05-03T13:57:25Z

例子

←上一版本		2021年5月3日 (一) 13:57的版本
第42行：		第42行：
	It is possible for key to name an index that is not present in the <span class="article-label">possible_keys</span> value. This can happen if none of the <span class="article-label">possible_keys</span> indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.		It is possible for key to name an index that is not present in the <span class="article-label">possible_keys</span> value. This can happen if none of the <span class="article-label">possible_keys</span> indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.


			Extra显示的索引扫描方式:
			*using where：查询使用索引的情况下，需要回表去查询所需的数据。
			*using index condition：查询使用了索引，但是需要回表查询数据。
			*using index：查询使用覆盖索引的时候会出现。
			*using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:Database]]		[[Category:Database]]

Riguz：/* 例子 */

2021-05-03T11:17:11Z

例子

←上一版本		2021年5月3日 (一) 11:17的版本
第39行：		第39行：
	;filtered:Percentage of rows filtered by table condition		;filtered:Percentage of rows filtered by table condition
	;Extra:Additional information		;Extra:Additional information

			It is possible for key to name an index that is not present in the <span class="article-label">possible_keys</span> value. This can happen if none of the <span class="article-label">possible_keys</span> indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.


	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:Database]]		[[Category:Database]]

Riguz：/* 例子 */

2021-05-03T11:13:51Z

例子

←上一版本		2021年5月3日 (一) 11:13的版本
第24行：		第24行：
	;id:The SELECT identifier		;id:The SELECT identifier
	;select_type:The SELECT type		;select_type:The SELECT type
			*SIMPLE:Simple SELECT (not using UNION or subqueries)
			*PRIMARY:Outermost SELECT
			*UNION:Second or later SELECT statement in a UNION
			*SUBQUERY:First SELECT in subquery
			* ...
	;table:The table for the output row		;table:The table for the output row
	;partitions:The matching partitions		;partitions:The matching partitions

Riguz：/* 例子 */

2021-05-03T11:10:19Z

例子

←上一版本		2021年5月3日 (一) 11:10的版本
第22行：		第22行：

	其中：		其中：
	*id ~~select_id~~ The SELECT identifier		;id:The SELECT identifier
	*select_type ~~None~~ The SELECT type		;select_type:The SELECT type
	*table ~~table_name~~ The table for the output row		;table:The table for the output row
	*partitions partitions The matching partitions		;partitions:The matching partitions
	*type ~~access_type~~ The join type		;type:The join type
	*possible_keys possible_keys The possible indexes to choose		;possible_keys:The possible indexes to choose
	*key key The index actually chosen		;key:The index actually chosen
	*key_len ~~key_length~~ The length of the chosen key		;key_len:The length of the chosen key
	*ref ref The columns compared to the index		;ref:The columns compared to the index
	*rows rows Estimate of rows to be examined		;rows:Estimate of rows to be examined
	*filtered filtered Percentage of rows filtered by table condition		;filtered:Percentage of rows filtered by table condition
	*Extra ~~None~~ Additional information		;Extra:Additional information


	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:Database]]		[[Category:Database]]

2021年5月3日 (一) 10:19 Riguz

2021-05-03T10:19:17Z

←上一版本		2021年5月3日 (一) 10:19的版本
第1行：		第1行：
	= 例子 =		= Explain =
			Explain用来查看执行计划。可用于<span class="article-label">SELECT</span>, <span class="article-label">DELETE</span>, <span class="article-label">INSERT</span>, <span class="article-label">REPLACE</span>, and <span class="article-label">UPDATE</span> statements

			== 例子 ==
	<pre>		<pre>
	mysql> explain SELECT COUNT(*) FROM vehicle_prod WHERE fin LIKE '%00000' \G		mysql> explain SELECT COUNT(*) FROM vehicle_prod WHERE fin LIKE '%00000' \G

Riguz：/* 例子 */

2021-05-03T10:18:17Z

例子

←上一版本		2021年5月3日 (一) 10:18的版本
第34行：		第34行：

	#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html		#https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
	[[Category:~~Datebase~~]]		[[Category:Database]]

MySQL explain - 版本历史

2023年12月19日 (二) 10:02 Riguz

Riguz：​Riguz移动页面MySQL:执行计划至MySQL explain，不留重定向

Riguz：​/* 例子 */

Riguz：​/* 例子 */

Riguz：​/* 例子 */

Riguz：​/* 例子 */

Riguz：​/* 例子 */

Riguz：​/* 例子 */

2021年5月3日 (一) 10:19 Riguz

Riguz：​/* 例子 */

Riguz：Riguz移动页面MySQL:执行计划至MySQL explain，不留重定向

Riguz：/* 例子 */

Riguz：/* 例子 */

Riguz：/* 例子 */

Riguz：/* 例子 */

Riguz：/* 例子 */

Riguz：/* 例子 */

Riguz：/* 例子 */