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

古城微笑少年丶 2022-07-13 12:45 203阅读 0赞

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)索引，否则反之。