MYSQL数据库优化之SQL及索引优化-学习笔记（2）

1. Count()和Max()的优化方法：

   查询2006年和2007年电影的数量

   Select count(release_year=’2006’ OR NULL) as ‘2006’,count(release_year=’2007’OR NULL) as ‘2007’ from film;

2. 子查询的优化方法

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

    

   Explain select titile from film where film_id in (selectfilm_id from film_actor where actor_id in (select actor_id from actor wherefirst_name=’zhang’))  \G

    

3. Group BY 的优化

4. Limit 优化查询

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

   Select film_id,description FROM file order by title limit50,5;

    

   优化步骤：

1. 使用索引列或者主键进行order by操作。

2. 返回上次返回的主键，在下次查询时使用主键过滤。

   Select film_id,description FROM film where film_id>55and film_id<=60 order by film_id limit 1,5;

   避免了数据量大时扫描过多的记录。

    

   Explain:

   Id:1

   Table:film

   Type:index

   Possible_key:NULL

   Key:primary

   Key_len:2

   Rows:5

   Extra: Using filesort。

    

    

   索引优化：

1. 在合适的列建立索引

1. 在where从句，group by从句，order by从句，on从句中出现的列

2. 索引字段越小越好（数据存储是以列为单位的，越小一列存储越多）

3. 离散度大的列放到联合索引前面

   例如：select * from payment where staff_id=1 and customer_id=121;

   建立索引 index(staff_id,customer_id)????index(customer_id,staff_id)????

    

   判断列的离散度：

   Select count(distinct cutomer_id),count(distinct staff_id)from payment;

   列的唯一值越多，列的离散度越大

    

   若Costomer_id离散度大，用后面的index(customer_id,staff_id)索引，否则反之。