MySQL数据库优化

https://dev.mysql.com/doc/index-other.html
https://dev.mysql.com/doc/sakila/en/sakila-installation.html

MySQL慢启日志的开启方式和存储格式

count()和Max()的优化

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

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

这里最后加了个\G 能使输出按列打印
这个查询rows 如果IO负荷很大的话，则不是一个很好的SQL，需要优化
那怎么优化呢？
在payment_date列上建立索引

mysql>create index idx_paydate on payment(pay_date);

再次expain这条查询语句，性能明显有很大的提高，因为索引是顺序排列的，通过索引的信息，查询很快就能知道哪条最大

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
在一条SQL中同时查出2006年和2007年电影的数量－－优化count()
错误的方式：

select count(release_year='2007' or release_year='2007') from film;

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

select count(*) from film where release_year='2006' and release_year='2007';

明显有错误，发布年份不能同时是2006和2007

有很多同学会疑问是用count(*) 好呢 还是count(id)好， 还是count(1)好？ 这两种的选择有时候执行结果是不一样的。

e.g:

mysql>create table t(id int);mysql>insert into t(id) values(1);mysql>insert into t(id) values(2);mysql>insert into t(id) values(null);mysql>select * from t;+------------+|   id       |+------------+|    1       ||    2       ||    NULL|mysql>select count(*),count(1)，count(id) from t;

结果：count(*)=3条 ; count(1)=3条 count(id)=2条

因为count(*) ,count(1)包括null值，count(id)忽略null值

最后：count的正确语句是：

select count(release_year='2006' or NULL) ,            count(release_year='2007' or NULL)  from film;

参考视频：http://www.imooc.com/video/3711
count(*)有可能包含空值的记录;
count(column_name)查询的结果都不为空,如果中间有NULL值,count是不会统计数量的，而count(*)是包含了Null值。

Max(column_name)在聚合查询的列上建立索引
在非主键列上建立普通索引
create index idx_attrname on tablename(attrname)
在主键上建立唯一索引
create uniqe index index_name on table_name(primary_key)

子查询的优化

通常情况下，需要把子查询优化为join查询，但是优化时要注意关联键是否有
一对多关系，要注意重复数据。
（查询sandra出演的所有影片）

explain select title,release_year,lengthfrom filmwhere film_id in (    select film_id from film_actor where actor_id in (       select actor_id from actor where film_name='sandra'    ))

e.g:

table t:        table t1:id              tid-----           -------- 1               1                 1select * from t where t.id in (select tid from t1);返回一条数据select t.id from t join t1 on t.id = t1.tid;返回两条数据    出现数据重复  可用distinct解决

使用子查询没有重复记录;
使用join的查询方式会出现重复记录，需要使用distinct关键字
select distinct t.id from t join t1 on t.id=t1.tid;

＊参考视频http://www.imooc.com/video/3718

group by的优化

优化group by查询

explain 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;

---

Extra:Using temporary; Using filesort
group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io
用到了临时表和文件排序

改写前select actor.first_name,actor.last_name,count(*)from sakila.film_actorinner join sakila.actor using(actor_id)group by film_actor.actor_id;改写后select actor.first_name,actor.last_name,c.cntfrom sakila.actor inner join(select actor_id,count(*) as cnt from sakila.film_actor group byactor_id)as c using(actor_id);

但是优化也不是一成不变的，增加查询条件的时候，我们相应的需要作出调整，将条件加在子查询里面， 缩小匹配的范围

＊参考视频 http://www.imooc.com/video/3719

http://wisebank.com/topic/MySQL%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96