mysql查看表执行效率_MySQL之Explain（查看sql的执行效率）

谁借莪１个温暖的怀抱￠ 2023-01-11 03:34 166阅读 0赞

前记：很多东西看似简单，那是因为你并未真正了解它。

Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用，如果你想查看update，delete类型语句中的索引效果，也不是太难的事情，只要保持条件不变，把类型转换成select就行了。

explain的语法如下：

explain \[extended\] select ... from ... where

...

如果使用了extended，那么在执行完explain语句后，可以使用show warnings语句查询相应的优化信息。

==============================================================

mk-visual-explain工具扩展了explain，它提供了一种更直观的树形表现形式，使用方法很简单：

mk-visual-explain

mk-visual-explain -c

mysql -e "explain select \* from mysql.user" |

mk-visual-explain

也可以在MySQL命令行里通过设置pager的方式来执行：

mysql> pager mk-visual-explain

mysql> explain \[extended\] select ... from ... where

...

==============================================================

进入正题，为了让例子更具体化，我们先来建一个表，插入一点测试数据：

CREATE TABLE IF NOT EXISTS \`article\` (

\`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,

\`author\_id\` int(10) unsigned NOT NULL,

\`category\_id\` int(10) unsigned NOT NULL,\`views\` int(10) unsigned NOT NULL,\`comments\` int(10) unsigned NOT NULL,

\`title\` varbinary(255) NOT NULL,

\`content\` text NOT NULL,

PRIMARY KEY (\`id\`)

);

INSERT INTO \`article\`

(\`author\_id\`, \`category\_id\`,

\`views\`,\`comments\`,\`title\`,

\`content\`) VALUES

(1, 1,1,1,'1', '1'),

(2, 2, 2,2,'2',

'2');

缺省只建了一个主键，没有建其他的索引。测试时，如果你时间充裕，应该尽可能插入多一点的测试数据，怎么说也应该保证几千条。如果数据量过少，可能会影响MySQL在索引选择上的判断。如此一来，一旦产品上线，数据量增加。索引往往不会按照你的预想工作。

下面让我们设置一个任务：查询category\_id为1且comments大于1的情况下，views最多的article\_id。

问题很简单，SQL也很简单：

SELECT author\_id

FROM \`article\`

WHERE category\_id = 1 AND comments > 1

ORDER BY views DESC

LIMIT 1

下面让我们用explain命令查看索引效果：

EXPLAIN SELECT

author\_id

FROM \`article\`

WHERE category\_id = 1

AND comments > 1

ORDER BY views DESC

LIMIT 1

这时explain部分结果如下：

type: ALL

key: NULL

Extra: Using where; Using filesort

显示数据库进行了全表扫描，没有用到索引，并且在过程中文件排序。这样的结果肯定是糟糕的，下面让我们通过建立索引优化一下它：

ALTER TABLE \`article\` ADD INDEX x ( \`category\_id\` , \`comments\`,

\`views\` ) ;

这时explain部分结果如下：

type: range

key: x

Extra: Using where; Using filesort

虽然不再是全表扫描了，但是仍然存在文件排序。一般来说，文件排序都是由于ORDER

BY语句一起的，而我们已经把views字段放到了联合索引里面，为什么没有效果呢？这是因为按照BTree的工作原理，先排序category\_id，如果遇到相同的category\_id则再排序comments，如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时，因为comments

1条件是一个范围值(所谓range)，MySQL目前无法利用索引再对后面的views部分进行检索，如果换成是是comments in

('a', 'b', 'c')这样的多等情况则可以，关于这一点，在High Performance

MySQL一书中专门有过叙述，名为Avoiding Multiple Range

Conditions，在复合索引里，仅仅只能保存一个range类型的查询字段，并且要放到复合索引的末尾，否则，range类型查询字段后面的索引无效。详细的介绍大家可以自己查阅。从这个意义上来说，此时的category\_id,

comments, views复合索引的效果不会比category\_id, comments复合索引的效果好。

文件排序是否会引起性能问题要视数据分布情况而定。这里有一个案例可供参考：Using index for

ORDER BY vs restricting number of rows.

多数情况下应该避免出现它。此时可以这样设置索引：

ALTER TABLE \`article\` ADD INDEX y ( \`category\_id\` ,

\`views\` ) ;

这时explain部分结果如下：

type: range