mysql 调优详解_MySQL性能优化详解

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