MySQL 是怎样运行的：从根儿上理解 MySQL（下）.pdf
10墨值下载
EXPLAIN
Extra MySQL MySQL
Notablesused
FROM
mysql>EXPLAINSELECT1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
---------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|
Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
---------------+
|1|SIMPLE|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|
Notablesused|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
---------------+
1rowinset,1warning(0.00sec)
ImpossibleWHERE
WHERE FALSE
mysql>EXPLAINSELECT*FROMs1WHERE1!=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
-----------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|
Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
-----------------+
|1|SIMPLE|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|
ImpossibleWHERE|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
-----------------+
1rowinset,1warning(0.01sec)
Nomatchingmin/maxrow
MIN MAX WHERE
mysql>EXPLAINSELECTMIN(key1)FROMs1WHEREkey1='abcdefg';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
------------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|
Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
------------------------+
|1|SIMPLE|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|
Nomatchingmin/maxrow|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
------------------------+
1rowinset,1warning(0.00sec)
Usingindex
Extra
idx_key1
mysql>EXPLAINSELECTkey1FROMs1WHEREkey1='a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+-------
---+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|
filtered|Extra|
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+-------
---+-------------+
|1|SIMPLE|s1|NULL|ref|idx_key1|idx_key1|303|const|8|
100.00|Usingindex|
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+-------
---+-------------+
1rowinset,1warning(0.00sec)
Usingindexcondition
SELECT*FROMs1WHEREkey1>'z'ANDkey1LIKE'%a';
key1>'z' key1LIKE'%a' MySQL
key1>'z' idx_key1
key1LIKE'%a'
key1LIKE'%a' range key1 MySQL
key1>'z' idx_key1
key1LIKE'%a'
key1LIKE'%a'
IO MySQL
IndexConditionPushdown
Extra Usingindexcondition
mysql>EXPLAINSELECT*FROMs1WHEREkey1>'z'ANDkey1LIKE'%b';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+-----
-----+-----------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|
filtered|Extra|
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+-----
-----+-----------------------+
|1|SIMPLE|s1|NULL|range|idx_key1|idx_key1|303|NULL|266|
100.00|Usingindexcondition|
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+-----
-----+-----------------------+
1rowinset,1warning(0.01sec)
Usingwhere
WHERE Extra
mysql>EXPLAINSELECT*FROMs1WHEREcommon_field='a';
of 115
10墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论