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 八、索引的维护及优化---删除不用索引

转载于:https://www.cnblogs.com/lusunlufar/p/4217992.html

总结

以上是生活随笔为你收集整理的SQL语句及索引优化的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。