MySQL数据库SQL优化第五篇：确定问题使用索引提高查询效率

   在EXPLAIN分析SQL中，已经可以确定是对客户表customet的全表扫描导致效率的不理想，那么对客户表customer的email字段创建索引，具体如下:

mysql> create index idx_email on customer(email);

创建索引后，再看一下这条语句的执行计划，具体如下：

mysql> explain select sum(amount) from customer a,payment b where 1=1 and a.customer_id = b.customer_id and email ='JANE.BENNETT@sakiacustomer.org';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ref  | PRIMARY,idx_email  | idx_email          | 153     | const                |    1 |   100.00 | Using index |
|  1 | SIMPLE      | b     | NULL       | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.a.customer_id |   26 |   100.00 | NULL        |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

通过分析SQL的rows的结果可以看出，建立索引后对customer 需要扫的行数明显减少（从583行减少到1行），可见索引的使用大大提高数据库的访问速度，尤其再表很庞大的时候，这种优势更为明显。