Mysql优化系列之查询性能优化前篇2

接前一篇，这一篇主要总结下几个经常要用的命令

命令一：explain+sql

mysql> explain select * from servers;

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

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |

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

|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |

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

row in set (0.03 sec)

上面是一个简单查询，重点观察属性如下：

type：表示MySQL在表中找到所需行的方式

type的值 从上到下，性能依次从差到好 ：

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index：Full Index Scan，index与ALL区别为index类型只遍历索引树

range：只检索给定范围的行，使用一个索引来选择行

ref:：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref:：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表

连接中使用primary key或者 unique key作为关联条件

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表

中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL：优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

key：表示查询中实际使用的索引（键）

rows：找到记录所需扫描的行数

命令二：show engine innodb status

这个命令查看存储引擎输出的内部信息，有很多可用的信息供开发者去发掘，笔者因为有一次碰到一个数据库死锁问题用过该命令

这个死锁的提示被应用程序获取到打印到了web应用的日志中，为了验证这个问题，从这个命令的结果中找到了相应的死锁提示

根据业务代码的逻辑，打开了两个客户端去模拟重现这个死锁，最后发现是一个常见的人为导致的脏数据业务逻辑，简单来说，就

是常见的read-compare-write操作，当多个线程一起过来时很容易产生超时等待死锁

命令三：show full processlist

这是一个当前连接状态下查看Mysql正在做什么的命令，注意观察其中的command 列，表示当前某个命令的执行状态这些状态

包括Sleep,Query,Locked,Copying to tmp table [on disk],Sorting result

注意Copying to tmp table [on disk]，这表示某个线程正在执行查询并将结果集复制到一个临时表，如果后面还有on disk标记，

表示Mysql正在讲一个内存临时表放到磁盘上，笔者处理过一些大表，因为经验不足，做了一个select ……into……操作，出现了该

状态，因为select into语句会先把所有记录查询出来放在临时表中然后再执行insert操作，如果一次查询数据很多，而服务器内存又

不够存储下来，则会往磁盘上写临时表存下来，关于内存和磁盘的问题，属于服务器问题，有时间单独讲讲。一般的企业开发很少

因为内存问题导致数据库优化出现问题。

命令四：show variables like "%query_cache%"

查看mysql是否已开启缓存，图中的箭头所示ON表示开启缓存，前面已经讲到，mysql的缓存作用

命令五：show variables like "Last_query_cost"

这个命令用来评估当前查询的成本，你可以在select 后加sql_no_cache 去掉缓存的影响（不使用缓存），更准确的评估你的

这条查询话费的时间