四、如何分析SQL查询？

使用explain查询SQL的执行计划

mysql> explain select * from store \G;

*************************** 1. row **************************

id: 1

select_type: SIMPLE

table: store  显示这一行的数据时关于哪张表的

type: ALL   显示连接使用何种类型。从最好到最差的连接类型有：

const：针对主键、唯一索引精确的常数查找

eq_reg：针对主键和唯一索引的范围查找

ref：针对索引的连接查询

range：索引的范围查找

index：索引的扫描

ALL：表扫描

possible_keys: NULL  显示可能应用在这张表中的索引。如果为NULL，则没有可能的索引

key: NULL 实际使用的索引。如果为NULL，则没有使用索引

key_len: NULL 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref: NULL 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows: 2  MySQL认为必须检查的用来返回请求数据的行数

Extra: 当看到这个选项的时候，查询就必须要优化了。

Using filesort:  MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类 型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using temporary:  MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集 进行ORDER BY上，而不是GROUP BY上

第一、count() 和 max()的优化方法

查询最后支付时间-优化max()函数

root@localhost sakila>explain select max(payment_date) from payment;

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

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

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

| 1 | SIMPLE    | payment | ALL  | NULL          | NULL | NULL    | NULL | 16451 |       |

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

分析：type为ALL说明是全表扫描，扫描的行数rows为16451，可见查询的代价比较高

解决：在payment_date上建立索引，这个时候在查询的时候就不是全表扫描而是通过索引直接锁定

到了最后一个。

mysql>create index index_paydate on payment(payment_date);

root@localhost sakila>explain select max(payment_date) from payment;

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

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

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

| 1 | SIMPLE  |  NULL | NULL | NULL  | NULL | NULL | NULL | NULL | Select tables optimized away |

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

分析：Select tables optimized away代表查询表的时候得到了尽可能的优化。

在一条SQL中同时查出2006年和2007年电影的数量--max优化

错误方式：

1、SELECT COUNT(release_year=’2006’ OR release_year=’2007’) FROM film;

分析：无法分开计算2006和2007年的电影数量

2、SELECT COUNT(*) FROM file WHERE release_year=’2006’ AND release_year=’2007’;

分析：Release_year不可能同时为2006和2007，此因有逻辑错误

正确方法：

SELECT COUNT(release_year=’2006’ OR NULL) as ‘2006年电影数量’ ,COUNT(release_year=’2007’ OR NULL) as ‘2007年电影数量’ FROM film