MySQL优化笔记之基本命令

基本的命令

前提概念

QEP(Query Execution Plan)查询执行计划

当MySQL执行一个SQL查询的时候，它首先会对该SQL语句进行语法检查，然后构造一个QEP，QEP决定了MySQL从低层存储引擎中获取信息的方式。

命令

EXPLAIN

用于查看MySQL查询优化器为SQL语句构造的QEP。

mysql> EXPLAIN SELECT * FROM CC;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | CC    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

QEP是通过EXPLAIN命令生成的，语法为：

EXPLAIN [EXTENDED | PARTITIONS]  SELECT...或者 EXPLAIN table;

EXPLAIN语法可以运行在SELECT 语句或特定表上，如果作用在表上，则命令等同于DESC表命令，UPDATE和DELETE命令如果需要进行性能改进，当这些命令不是直接在表的主码上运行时，确保最优化的索引使用率，需要把它们改写成SELECT语句。

eg:

UPDATE CC 
SET id = 123,name = 'xixixi'
WHERE id = 7;
==================================
重写为：
SELECT id,name
FROM CC
WHERE id=123;

详细解读QEP:

id列:QEP中展示的表的连续引用，就是编号而已。

select_type列：提供各种表示table列引用的使用方式的类型，最常见的是SIMPLE,PRIMARY,DERIVED,UNION.其他的UNION RESULT,DEPENDENT SUBQUERY,DEPENDENT UNION,UNCACHEABLE UNION,UNCACHEABLE QUERY.

 ⑴SIMPLE:不包含子查询和其他复杂语法的简单查询。

⑵PRIMARY:复杂查询而创建的首要表，这个类型通常可以在DERIVED 和UNION 类型混合使用。

⑶DERIVED:当一个表不是物理表的时候，即表的查询是建立在别的表的查询结果之上,eg:EXPLAIN SELECT MAX(ID) FROM (SELECT ID FROM USER ) C;

⑷DEPENDENT SUBQUERY:使用子查询而定义的。

⑸UNION:语句中的一个SQL元素。

⑹UNION RESULT:定义在UNION语句中的表的返回结果，

table:生成QEP结果的标识符，可能是表名，表的别名，临时表的标识符；

partitions:代表给定表所使用的分区，这一列只会在EXPLAIN PARTITIONS 语句中出现；

type:代表QEP中指定的表使用的连接方式，常用的方式有，const,system,eq_ref,ref,range,all.fulltext,ref_or_null,...。

⑴const:这个表最多只有一行匹配的行时出现；

⑵system:当表只有一个row是会出现

⑶eq_ref:表示有一行是为了每个之前确定的表而读取的；

⑷ref:所有具有匹配的索引值的行都被用到

⑸range:所有符合一个给定范围值的索引行都被用到

⑹all:表示需要一次全盘扫描；

possible_keys:指出优化器为查询选定的索引；

key:列出优化器选项使用的索引，一般情况，SQL查询中每个表都仅使用一个索引，当然也存在使用多个索引；

key_len:定义了用于SQL语句的连续条件的键的长度，此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要，eg：key_len: 4  -> INT NOT NULL  , key_len :5  ->INT NULL , key_len:30 ->CHAR(30) NOT NULL, key_len :32 ->VARCHAR(30) NOT NULL;key_len列的值只和用在连接和WHRER条件中的索引的列有关；

ref:用来标识那些用来进行索引比较的列或常量；

rows:提供所有存在于累计结果集中的行数目的MySQL优化器估计值，查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。

filtered:给出一个百分比的值，这个值和rows列的值一起使用，可以估计出那些将要和QEP中的前一个表进行连接的行的数目，这一列只有在EXPLAIN EXTENDED语句中才出现。

Extra:提供了一系列额外的信息，Extra列可以包含多个值，也可以有很多不同的取值。

⑴Using where:表示查询使用了where语句来处理结果

⑵Using temporary:表示使用了内部临时表

⑶Using filesort:ORDER BY语句的结果

⑷Using index:强调了只需要使用索引就可以满足查询表的要求

⑸Using join buffer：强调了在获取连接条件时没有使用索引，并需要连接缓冲区来存储中间结果

⑹Impossible where:强调where语句会导致没有符合条件的行

⑺Select tables optimized away:仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

⑻Distinct:Mysql在找到第一个匹配的行之后会停止搜索其他行。

EXPLAIN PARTITIONS:

用于满足在partitons列中的查询的特定表分区提供附加信息。

EXPLAIN EXTENDED:

用来提供额外的filtered列。

SHOW CREATE TABLE:

可以向用户呈现表中的当前列和索引定义的全部细节，告诉用户如何准确的语法来重新创建数据库表。

SHOW INDEXES:

查看索引的信息，这些信息包括索引的类型和当前报告的MySQL索引的基数。

SHOW TABLE STATUS:

查询数据库里所有的信息：SHOW TABLE STATUS FROM DB_NAME;

查询特定数据库里的特定的表的信息：SHOW TABLE FROM DB_NAME LIKE 'TABLE_NAME';

查看数据库表的底层大小以及表结构，包裹存储引擎，版本，数据和索引大小，行的平均长度以及行数。用于也可以从INFORMATION_SCHEMA.TABLES表中获取底层表的信息。

SHOW STATUS:

SHOW [GLOBAL | SESSION ] STATUS命令可以查看MySQL服务器的当前内部状态信息。

SHOW VARIABLES:

SHOW [ GLOBAL | SESSION ] VARIABLES命令可查看MySQL系统变量的当前值，当没有指定SHOW VARIABLES命令的范围时，默认在SESSION范围内执行。

 

INFORMATION_SCHEMA数据库下包含很多MySQL数据的信息，其包含的表的数量取决于用户的MySQL版本。

 

 

参考《Effective MySQL之SQL语句最优化》   建议亲读此书，加深理解。