IT虾米网

MySQL性能优化

mate10pro 2018年06月08日 数据库 1264 0

一、MySQL优化前言

为什么要优化?

这里写图片描述
MSYQL是基于文件的,因此当打开文件数达到一定限制,就会进行导致频繁的IO操作,这是属于系统配置

可以从哪些方面着手优化?

这里写图片描述

如何发现有问题可优化的SQL?

这里写图片描述
当我们打开慢查日志文件,就可以看到我们的SQL语句的具体信息,例如:
这里写图片描述
每一项的含义:
这里写图片描述

但是如果慢查日志SQL很多的时候,直接看日志文件就很吃力,因此可以通过工具来分析:例如mysqldumpslow 和 pt-query-degist等

二、 SQL语句优化

1. Explain语句

Explain可以用来分析一个SQL语句的具体信息:
这里写图片描述
这里写图片描述
  1. 对于type,const说明为常量扫描,eq_reg是一个范围扫描,ref基于联合扫描,rangge基于索引的范围扫描,index基于索引扫描,all基于表扫描,性能递减
  2. 对于extra,当看到这个的时候,说明SQL就要优化了

2. Count和Max的优化

这里写图片描述
正确的做法是:
这里写图片描述

3. 子查询优化

这里写图片描述
  例如当两个表需要进行子查询或者join查询时,例如第一个表为t1, 列为id,id值为1;第二个表为t2,列为id,有两列,id值都是1。此时t1和t2的关系为一对多。
  使用子查询:select * from t2 where id in (select id from t1);此时得到的值只有1列,id值为1;
  使用联合查询:select * from t2 a join t1 b where a.id = b.id;此时得到的值有2列,id值都为1,;
  因此,使用联合查询,要注意存在值一对多的关系,如果存在此关系,可以用distinct去重:select distinct * from t2 a join t1 b where a.id = b.id;

4. group by的优化

这里写图片描述
  SQL的目的是:查询出所有演员的名字及参演电影的数量
  这样的分析结果为:
这里写图片描述
  可以看到,由于使用了group by,因此会导致使用临时表和文件排序,这样就大大影响了效率
  我们可以这样来优化:
这里写图片描述
  结果:
  这里写图片描述

5. Limit优化

  首先,为什么要使用Limit?使用Limit可以提高我们的效率,假如一个表中有很多数据,且只有一个值为AA,此时你想查询值为AA的row
  不使用Limit:select * from table where column = AA;这样会全表查询
  使用Limit:select * from table where column = AA limit 1;这样当查询到AA的row时,就不会往下查询了!
  这里写图片描述
  这里写图片描述

三、索引优化

1. 如何建立适当的索引

这里写图片描述
  离散度是指值的发散程度,例如id1 的值有1,2,3,4,5,6,7,而id2的值有1,1,1,2,2,2,那么id1的离散度就比id2的离散度大
  这里写图片描述

2. 索引优化和维护

这里写图片描述
这里写图片描述
这里写图片描述
这里写图片描述

四、数据库结构优化

1. 如何选择合适的数据结构

这里写图片描述
  尽可能用notnull,这是由于innodb的特性决定的,因为对于非 not null的表,innodb需要额外的字段进行存储,io会有一定的开销
  这里写图片描述

2. 除了范式化,也要反范式化,增加一定的数据冗余

  例如对下面这个表,进行查询:
  这里写图片描述
  这里写图片描述
  这样联表查询可能会影响效率,我们可以通过增加数据冗余:
  这里写图片描述
  

3. 表的垂直拆分和水平拆分

这里写图片描述
这里写图片描述
这里写图片描述
数据库表的水平拆分:对于前台,拆表示为了效率,而对于后台使用者,则不需要拆分,因此查询要按前后台业务来查询不同的表

参考:imooc

评论关闭
IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!