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