mysql综合查询索引优化_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行)，可见索引的使用大大提高数据库的访问速度，尤其再表很庞大的时候，这种优势更为明显。