MySql数据库优化

 

 

 

 

查看是否开启慢查询日志

查看变量设置情况

我们要记录未使用索引的查询就要把变量设置为ON 用以下命令设置

 

再确认慢查询时间的变量情况

把慢查询日志设置为开启状态

下面演示如下：

记录的位置如下图所示

查看执行的sql语句是否记录在了日志文件中

下图是慢查询日志文件记录的sql语句

慢查询日志的存储格式

 

 

MySQL慢查日志分析工具之mysqldumpslow

 

查看下工具可使用的参数

用工具查看慢查询日志记录的 前三条 用more查看

查看结果如下

MySQL慢查日志分析工具之pt-query-digest（第二款工具）

 

查看pt-query-digest工具参数情况

演示分析上述日志文件

分析

 

如何通过慢查日志发现有问题的SQL 

 

通过explain查询和分析SQL的执行计划

 

下面用具体例子分析慢查询以及优化

Count()和Max()的优化

上面看到sql语句执行要扫描15422行，这严重占用IO,下面我们创建一个索引来提高性能

再来看下执行计划

 在看下Count()如何优化的例子

下面是正确的查询方式

 

 演示执行效果

 

子查询的优化

 

通常我们优化成join的方式

 

当表之前出现1对多的关系的时候有可能会出现重复，所以要去重

group by的优化

 

以上查询会出现对临时表的查询操作，我们把它优化成如下图情况 （执行explain来查看执行计划）

 Limit查询的优化

 

看下上面sql语句的执行计划

上面sql执行计划结果中采用表扫描的方式扫描了1030行，我们进行如下优化看下

看下执行计划

上图使用了主键key:PRIMARY和索引type:index查询,

接下来我们进一步优化

 

看下执行计划

上面执行计划中我们可以看到只扫描了5行，这样就大大提高了SQL执行效率

 

如何选择合适的列建立索引

 

 索引优化SQL的方法 

 

 

用工具查重复和...索引，输入命令如下图红框

查看结果

 

索引维护的方法

此处省略......

数据库表结构的优化：选择合适的数据类型

 

数据库表的范式化优化

 

我们如何做呢，我们分析后可以把表拆分成三张表来处理

数据库表的反范式化优化

 

上面的查询语句显然增加了IO操作，大大降低查询性能，我们接下来就要进行反范式化的操作

数据库表的垂直拆分

 

 尽量避免text属性，能不用就不用，是在无法避免就把这些大的字段拆分到另一张表中

 

拆分后的结果：主表film和表file_text

 

 

数据库表的水平拆分

 

由于前台业务量比后台要求要高，咱们可以把前后台查询需要的表分开，后台统计报表操作总表

数据库系统配置优化

MySQL配置文件优化

 

 

 

建议把关键参数innodb_file_per_table 设置为ON

 

第三方配置工具使用

 

 服务器硬件优化