MySQL优化 -- SQL及索引优化（二）

一、Count()和Max()的优化方法

查找最后的支付时间：

对支付时间建立索引：
create index idx_paydate on payment(payment_date);
查询：
select max(payment_date) from payment;

在一条SQL中同时查出2006年和2007年电影的数量 - 优化count()函数：

对count函数使用的列做处理：
select count(release_year='2006' or null) as '2006年电影数量', 
	count(release_year='2007' or null) as '2007年电影数量'
	from film;

二、子查询的优化

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

查出sandra出演的所有影片：
select title from film where film_id in (select film_id from film_actor where actor_id in (
	select actor_id from actor where first_name='sandra'));
优化为join查询：
select distinct title from (film f join film_actor fa on f.id=fa.film_id) 
	join actor t on t.actor_id=fa.actor_id;

三、group by查询

group by 可能会出现临时表，文件排序等，影响效率。可以通过关联的子查询，让group by走索引，来避免产生临时表和文件排序，减少IO。

查出每个演员出演的影片数量：
explain select actor.first_name,count(*)
	from film_actor
	inner join actor using(actor_id)
	group by film_actor.actor_id;
优化后查询：
explain select actor.first_name,c.cnt
	from actor inner join
	(select actor_id,count(*) as cnt from film_actor group by actor_id)
	as c using(actor_id);

四、优化limit查询

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用到Filesorts这样会造成大量的IO问题。

select film_id,description from film order by title limit 50,5;
优化1：使用有索引的列或主键进行order by操作; 缺点：需要从头开始，扫到50，如果数量大，时间也会长。
select film_id,description from film order by film_id limit 50,5;
优化2：记录上次返回的主键，在下次查询时使用主键过滤。好处是要多少扫多少，避免数据量大时扫描过多的记录。
缺点是主键必须连续，不能空缺。
select film_id,description from film where film_id>55 and film_id<=60 order by film_id limit 1,5;

五、如何选择合适的列建立索引？

1、在where从句，group by从句，order by从句，on从句中出现的列
2、索引字段越小越好
3、离散度大的列放到联合索引的前面，例如

select * from payment where staff_id = 2 and customer_id = 548;
由于customer_id有599个不同的值，离散度更大，所以联合索引为index(customer_id,staff_id)。

索引的维护及优化：消除重复及冗余索引。
重复索引是指相同的列以相同的顺序建立的同类型的索引，如下图primary key和id列上的索引就是重复索引。

如何查找重复及冗余索引？
使用pt-duplicate-key-checker工具检查重复及冗余索引：

pt-duplicate-key-checker -uroot -p root -h 127.0.0.1

结果分析：指出哪些索引是重复的，结尾还给出了删除重复索引的操作。

删除不用的索引：
可能因业务变更，有些索引不会再使用到了。可以通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析。

pt-index-usage -uroot -proot mysql-slow.log