SQL语句及索引优化
一、Count()和Max()的优化方法
1、查询最后支付时间-优化max()函数
1)语句:select max(payment_date) from payment;
2)查看执行计划:explain select max(payment_date) from payment \G
3)优化方案(建立索引):create index idx_paydate on payment(payment_date);
2、 在一条SQL中同时查出2006年和2007年电影的数量-优化count()函数
错误的方式:
1)select count(release_year = '2006' or release_year = '2007') from film; // 无法分开计算2006年和2007年的电影数量
2)select count(*) from film where release_year = '2006' and release_year = '2007'; // release_year不可能同时为2006和2007,逻辑错误
正确的方式:
select count(release_year = '2006' or null) as '2006年电影数量',count(release_year = '2007' or null) as '2007年电影数量' from film;
count(*)和count(某一列)讨论:
1)它们值可能不同,count(某一列)所结果是不包含空值(null)的行,而count(*)是包含空值(null)的那行。
二、子查询的优化
通常情况下,需要把子查询优化为join查询,但在优化时要注意关联键是否有一对多的关系,要注意重复数据(使用distinct去重)。
三、优化group by查询
优化前:explain select actor.first_name, actor.last_name, count(*) from skila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id;
优化后:explain select actor.first_name, actor.last_name, c.cnt from sakila.actor inner join ( select actor_id, count(*) as cnt from sakila.film_actor group by actor_id ) as c using(actor_id);
四、优化limit查询
limit常用于分页处理,时常会伴随order by从句使用,因此大多时候会使用filesorts这样会造成大量的IO问题。
优化前:select fiilm_id, description from sakila.film order by title limit 50, 5;
优化步骤1:使用有索引的列或主键进行order by操作
select film_id, description from sakila.film order by film_id limit 50, 5;
优化步骤2:记录上次返回的主键,在下次查询时使用主键过滤
select film_id, des机cription from sakila.flim where film_id > 55 and film_id <= 60 order by film_id limit 1, 5; // 避免了数据量大时扫描过多的记录(要求主键是顺序增长)
五、如何选择合适的列建立索引
1、在where从句,group by从句,order by从句,on从句中出现的列
2、索引字段越小越好(原因:MySQL的每次读取都以页为单位,如果页中存储的数量越大,则一次IO操作获取的数据量就越大,查询的效率就越高)
3、离散度大的列放到联合索引的前面(离散度越大的列的可选择性越高,因为放在联全索引的前面效率就越好)
select * from payment where staff_id = 2 and customer_id = 584;
选择index(staff_id,customer_id)还是index(customer_id,staff_id)? 由于customer_id的离散度更大,所以应该使用Index(customer_id,staff_id)
判断列的离散程度:
select count ( distinct customer_id ), count ( distinct staff_id ) from payment; // 唯一值越多则离散度越大
ps:若个索引包含了查询中的所有列,则称该索引为覆盖索引。当我们查询的执行频率非常高,并且查询中所包含的列比较少时,可使用覆盖索引对SQL进行优化。
六、索引的维护及优化---重复及冗余索引
增加索引能提高查询(select)效率,但会影响写入操作(insert、update、delete)的效率。
过多的索引会影响写入操作的效率,同样也会影响查询效率。
重复索引是指相同的列以相同的顺序建立的同类型的索引,如下表中primary key 和 id 列上的索引就是重复索引
create table test(
id int not null primay key,
name varchar(10) not null,
title varchar(50) not null,
unique(id)
)engine=innodb;
冗余索引是指多个索引的前缀列相同,或是在联合索引中包含了主键的索引,下面这个列子中key(name,id)就是一个冗余索引
create table test(
id int not null primay key,
name varchar(10) not null,
title varchar(50) not null,
key(name,id)
)engine=nonodb;
七、索引的维护及优化---查找重复及冗余索引(使用工具更为方便)
select a.table_schema as '数据名', a.table_name as '表名',
a.index_name as '索引1', b.index_name as '索引2',
a.column_name as '重复列名' from statistics a join statistics b
on a.table_schema=b.table_schema and a.table_name=b.table_name and a.seq_in_index=b.seq_in_index
and a.column_name=b.column_name where a.seq_in_index=1 and a.index_name<>b.index_name
八、索引的维护及优化---删除不用索引