文档章节

MySQL优化

mywd
 mywd
发布于 2017/08/19 14:22
字数 1060
阅读 28
收藏 1
点赞 1
评论 1

一、目的

  1. 解决由于慢查询造成的页面加载慢或者无法加载
  2. 解决阻塞造成数据无法提交

二、优化方式

  • SQL 及索引优化

这种方式是我们平时使用的最多也是代价最低效果最为明显的一种方式

  • 定位哪些SQL需要优化

    • 开启mysql慢查日志

            在MySQL客户端中输入命令:

                show variables like '%quer%';

                -slow_query_log是否记录慢查询。用long_query_time变量的值来确定“慢查询”。

                -slow_query_log_file慢日志文件路径

                -long_query_time慢日志执行时长(秒),超过设定的时间才会记日志

            Linux:

                在/etc/my.cnf配置文件的[mysqld]选项下增加:

                slow_query_log=TRUE

                slow_query_log_file=/usr/local/mysql/slow_query_log.txt

                long_query_time=3

            Windows:

                在my.ini配置文件的[mysqld]选项下增加:

                slow_query_log=TRUE

                slow_query_log_file=c:/slow_query_log.txt

                long_query_time=3

  • 慢查日志所包含的内容

        1、执行SQL的主机信息

            

         2、SQL的执行信息

            

         3、SQL执行时间    

            

          4、SQL的内容

            

  • 慢查日志的分析工具

            1、mysqldumpslow

                 mysqldumpslow [opts] [logs]
                    logs -具体的慢查日志
                    opts  -t  分析的条数  -s排列方式

              结果:

                

            2、pt-query-digest  

                pt-query-digest  [opts] [logs]

                还可以定义慢查询结果输出到文件:

                

                输出查询结果到数据库表:

                

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

                1、查询次数多且每次查询占用时间长的SQL

                        通常为pt-query-digest分析的前几个查询

                2、IO大的SQL

                        注意pt-query-digest分析中的Rows examine项数值大的

                3、未命中索引的SQL

                        注意pt-query-digest分析中的Rows examine 和Rows send的对比

  • 使用explain查询SQL的执行计划

                

                

                

  • 常见的SQL优化

            1、Max()的优化

              例如:  

                        为max()字段添加索引

                        

                        

                        只查询索引,不用查询表,应为索引是有序的与数据量无关,大大加快了查询速度

            2、Count()

                        count(*)时会包含为null的值,count(XX)不包含xx为null时的值

            3、子查询的优化

                       通常情况下需要把子查询优化为join查询,在优化时需要注意关联键是否有一对多的关系,要注意重复数据

            4、limit   

                        limit常用于分页处理,时常会伴随order by使用,因此大多时候会使用Filesorts造成大量的IO问题,通常使用有索引的列或者主键进行order by操作来解决

  • 选择合适的列建立索引

            在where从句,group by从句,order by从句,on从句出现的列建立索引、索引字段越小越好、离散度大(具体就是字段数值相差越大离散度就越大)的列放到联合索引的前面

  • 索引的维护和优化

            1、重复索引、冗余索引

                相同的列以相同的顺序建立的同类型的索引

                    

                多个索引的前缀列相同,或是在联合索引中包含了主键的索引

                    

                主键索引会自动添加上

            2、优化方式

                使用pt-duplicate-key-checker工具检查重复及冗余索引

                

                结果:

                    

                查找未使用的索引:

                    

  • 数据库表结构优化

  • 选择合适的数据类型

            

           例如:

             

            

  • 表的范式化设计

            范式化一般是指第三设计范式,也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式

  • 表的反范式化

            为了查询效率的考虑把原本符合第三范式的表适当的增加冗余,已达到优化查询效率的额目的,反范式化是一种以空间来换时间的操作

  • 表的垂直拆分

            

  • 表的水平拆分

            为了解决单表的数据量过大查询效率低的问题,水平拆分表的每一个表的结构都是完全一致的

            缺点:跨分区表进行数据查询、统计及后台报表的操作

  • 系统配置优化

  • 操作系统的配置优化

            1、

                2、

  • MySQL配置优化

            1、MySQL配置文件位置

                

            2、重要参数

                

                

                

                

                

            3、第三方配置工具

                 如果不清楚具体如何配置,可以通过第三方配置工具快捷帮你配置

                     https://tools.percona.com/wizard

  • 硬件优化

            

© 著作权归作者所有

共有 人打赏支持
mywd
粉丝 3
博文 9
码字总数 4255
作品 0
加载中

评论(1)

nikeodong
nikeodong
污杨王牛逼...:smile:

暂无相关文章

聊聊spring cloud的RequestRateLimiterGatewayFilter

序 本文主要研究一下spring cloud的RequestRateLimiterGatewayFilter GatewayAutoConfiguration @Configuration@ConditionalOnProperty(name = "spring.cloud.gateway.enabled", matchIfMi......

go4it ⋅ 49分钟前 ⋅ 0

Spring JavaConfig 注解

JavaConfig注解允许开发者将Bean的定义和配置放在Java类中。它是除使用XML文件定义和配置Bean外的另一种方案。 配置: 如一个Bean如果在XML文件可以这样配置: <bean id="helloBean" class="...

霍淇滨 ⋅ 57分钟前 ⋅ 0

Spring clound 组件

Spring Cloud技术应用从场景上可以分为两大类:润物无声类和独挑大梁类。 润物无声,融合在每个微服务中、依赖其它组件并为其提供服务。 Ribbon,客户端负载均衡,特性有区域亲和、重试机制。...

英雄有梦没死就别停 ⋅ 58分钟前 ⋅ 0

Confluence 6 重新获得站点备份文件

Confluence 将会创建备份,同时压缩 XML 文件后存储熬你的 <home-directory>/backups> 目录中。你需要自己访问你安装的 Confluence 服务器,并且从服务器上获得这个文件。 运行从 Confluence...

honeymose ⋅ 今天 ⋅ 0

informix的常用SQL语句

1、创建数据库 eg1. 创建不记录日志的库testdb,参考语句如下: CREATE DATABASE testdb; eg2. 创建带缓冲式的记录日志的数据库testdb(SQL语句不一定在事务之中,拥有者名字不被用于对象的解...

wangxuwei ⋅ 今天 ⋅ 0

matplotlib画图

最简单的入门是从类 MATLAB API 开始,它被设计成兼容 MATLAB 绘图函数。 from pylab import *from numpy import *x = linspace(0, 5, 10)y = x ** 2figure()plot(x, y, 'r')...

Dr_hu ⋅ 今天 ⋅ 0

RabbitMQ学习以及与Spring的集成(三)

本文介绍RabbitMQ与Spring的简单集成以及消息的发送和接收。 在RabbitMQ的Spring配置文件中,首先需要增加命名空间。 xmlns:rabbit="http://www.springframework.org/schema/rabbit" 其次是模...

onedotdot ⋅ 今天 ⋅ 0

JAVA实现仿微信红包分配规则

最近过年发红包拜年成为一种新的潮流,作为程序猿对算法的好奇远远要大于对红包的好奇,这里介绍一种自己想到的一种随机红包分配策略,还请大家多多指教。 算法介绍 一、红包金额限制 对于微...

小致dad ⋅ 今天 ⋅ 0

Python 数电表格格式化 xlutils xlwt xlrd的使用

需要安装 xlutils xlwt xlrd 格式化前 格式化后 代码 先copy读取的表格,然后按照一定的规则修改,将昵称中的学号提取出来替换昵称即可 from xlrd import open_workbookfrom xlutils.copy ...

阿豪boy ⋅ 今天 ⋅ 0

面试题:使用rand5()生成rand7()

前言 读研究生这3 年,思维与本科相比变化挺大的,这几年除了看论文、设计方案,更重要的是学会注重先思考、再实现,感觉更加成熟吧,不再像个小P孩,人年轻时总会心高气傲。有1 道面试题:给...

初雪之音 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部