一、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

 

八、索引的维护及优化---删除不用索引