mysql SQL语句优化

这里使用mysql官方示例数据库sakila做测试

cd /
wget downloads.mysql.com/docs/sakila-db.zip
unzip sakila-db.zip

登陆mysql创建数据

mysql> source /sakila-db/sakila-schema.sql;
mysql> source /sakila-db/sakila-data.sql;

Max()优化

-- 查询最后支付时间的记录
mysql> explain select max(payment_date) from payment \G

显示

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到扫描了16086行，扫描行数越多，I/O效率越低

优化方案：给payment_date字段加索引，索引是顺序排列的

mysql> create index idx_paydate on payment(payment_date);

再次

mysql> explain select max(payment_date) from payment \G

显示

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)

其中Extra: Select tables optimized away的意思是没有更好的可优化的了，意思就是这是最优查询了

子查询
通常情况需要把子查询优化成join查询

join using简化join on

#查询演员出演的电影数量
mysql> select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor on film_actor.actor_id = actor.actor_id
group by film_actor.actor_id \G
mysql> select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) 
 group by film_actor.actor_id \G

Limit优化

explain select * from sakila.film limit 50,5 ;

加入order by 索引列进行优化

explain select * from sakila.film order by film_id limit 50,5;