首页 > MySQL > MySQL优化初步

MySQL优化初步

这一段时间看了学长推荐的一本书:《高性能MySQL》,感觉还不错,今天写一篇读书笔记,供自己和需要的人参考。

一、数据库优化简介

1. 数据库优化的目的

①. 避免出现页面访问错误,主要是数据库连接timeout产生页面5XX错误、由于慢查询造成页面无法加载和由于阻塞造成数据无法提交;
②. 增加数据库的稳定性,很多数据库问题都是由于低效的查询引起的;
③. 优化用户体验,流畅页面的访问速度、良好的网站功能体验

2. 从哪几个方面对数据库优化

所谓一图胜千言,所以我们先看一下,下面这张图:
20150614220801
从这张图上,我们可以清晰的看到,我们优化的重点和代价,另外这几个方面我们分别可以做些什么呢?
①.在SQL及索引层面,首先是我们要写出一些结构良好的SQL和建立有效的索引
②.数据库表结构,我们应该根据数据库的一些范式,减少冗余,建立一个利于查询的库表结构
③.系统配置,例如TCP/IP的连接数、文件的打开数等等
④. 硬件,包括内存、CPU等,IO设备等等

二、SQL语句优化

在SQL优化之前,我们首先肯定是要查询到哪些SQL需要优化,所以下面让我们先打开MySQL的慢查询日志

1. MySQL慢查日志的开启方式和存储格式

我们先看几个命令:


show variables like 'slow_query_log';

set global slow_query_log=on;

set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log';

set global log_queries_not_using_indexes=on;

set global long_query_time=1;

这几条命令相信不用说,大家都能看得懂,需要说明的是最后一条的时间单位是“秒”,本例中意思是说大于“1秒”的都认为是慢查询,当然实际情况下“1秒”很多时候也是太长了,记录下来这些信息之后我们就可以分析和查看这些日志了,分析和查看MySQL的慢查询日志有两个常见的工具,分别是mysqldumpslow和pt-query-digest,第一个是MySQL官方提供的,第二个是一个商业软件,至于他们怎么用,这里就不多说了,找到这些用问题的SQL之后,我们就可以对这些SQL进行优化了,一般有问题的SQL主要是一下这些:

①. 查询次数多且每次查询占用时间长的SQL,通常是pt-query-digest分析的前几个查询;
②. IO大的SQL,注意pt-query-digest分析中的Rows examine项
③. 未命中索引的SQL,注意pt-query-digest分析中Rows examine和Rows Send的对比

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

explain返回各列的含义


table: 		显示这一行数据是关于那张表的
type:			这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和All
possible_keys	显示可能应用在这张表中的索引。如果为空,没有可能的索引。
key:			实际使用的索引
key_len:		使用索引的长度,在不损失精确性的情况下,长度越短越好
ref:			显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:			MySQL认为必须检查的用来返回请求数据的行数
extra:			看到Using filesort和Using temporary的时候,查询就需要优化了,Using temporary需要MySQL常见一个临时表来存储结果,这通常发生在不同的列集进行ORDER BY上,而不是GROUP BY上

3. count()和max()的优化

max函数通常是查询最后一个数据,我们可以对max函数包含的列添加一个索引;
count函数是用来计数的,我们只需要了解count(*)和count(id)的差别就好了,count(*)返回的是有多少行,count(id)返回的是id不为null的有多少行,所以这是有差别的

4. 子查询的优化

通常情况下,需要把子查询优化成join查询,但在优化时要注意关连建是否有一对多的关系,要注意重复数据(需要我们使用distinct去重)

5. limit的优化

三、索引优化

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

①. 在where从句、group by从句、order by从句、on从句中出现的列
②. 索引字段越小越好
③. 离散度大的列放到联合索引的前面

2. 索引的维护及优化 — 重复及冗余索引

重复索引是指相同的列以相同的顺序建立的同类型的索引
我们可以使用pt-duplicate-key-checker工具检查重复及冗余所以

需要说明的是,我们一般认为索引是可以加快查询速度,但是对更新有影响,实际情况当我们系统中有很多重复索引的时候,数据库要分析使用哪个索引,所以重复索引多了之后也会对查询稍有影响

四、 数据库结构的优化

1. 选择合适的数据类型

①. 使用可以存下你的数据的最小的数据类型
②. 使用简单的数据类型。int要比varchar类型在MySQL上的处理要简单
③. 尽可能的使用not null定义字段
④. 尽量少用text类型,非用不可时最好考虑分表

例如:使用int存储日期时间,利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转化
使用bigint来存储IP地址,利用INET_ATON(),INET_NTOA()两个函数进行转换

2. 范式化优化和反范式化优化

我们在设计表时,一般来说要满足第三范式,即消除传递依赖,不满足第三范式的表一般会存在以下问题:
①. 数据冗余
②. 数据插入异常
③. 数据更新异常
④. 数据删除异常

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

3. 表的垂直拆分

表的垂直拆分就是把原来一个有很多列的表拆分成多个表,这解决了表的宽度问题。通常垂直拆分按以下原则进行:

①. 把不常用的字段单独存放到一个表中;
②. 把大字段独立存放到一个表中;
③. 把经常使用的一些字段放在一起

4. 表的水平拆分

表的水平拆分是为了解决单表的数据量过大的问题,水平拆分的表的每一个表的结构都是完全一致的,常用的水平拆分方法:

①. 对ID进行hash运算,如:拆分成5张表,则使用mod(id, 5)取出0-4个值
②. 针对不同的hashID把数据存到不同的表中

表的水平拆分的挑战:

①. 跨分区进行数据查询;
②. 统计及后台报表操作;

五、系统配置优化

1. 数据库是基于操作系统的,目前大多数MySQL都是安装在Linux上的,所以对于操作系统的一些参数的配置也会影响到MySQL的性能,下面是一些常用的配置:
网络方面的配置,要修改/etc/sysctl.conf文件


#增加tcp支持的队列数
net.ipv4.tcp_max_backlog = 65535
#减少断开连接时,资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv3.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

打开文件数的限制,可以使用ulimit -a查看,通过修改/etc/security/limits.conf文件,增加以下内容以修改打开文件数量的限制:


* soft nofile 65535
* hard nofile 65535

除此之外最好在MySQL服务器上关闭iptables、selinux等软件防火墙软件,转而用硬件防火墙

2. MySQL配置文件优化


innodb_buffer_pool_size			非常重要的一个参数,用于配置innodb的缓冲池,如果数据库中只有innodb的表,则推荐配置量为总内存的75%
innodb_buffer_pool_instances	MySQL5.5新增参数,可以控制缓冲池的个数,默认情况下只有一个
innodb_log_buffer_size			innodb log缓冲的大小,由于日志最长每秒就会刷新,所以不用太大
innodb_flush_log_at_trx_commit	关键参数,对innodb的IO效率影响很大,默认为 1 ,可以取 0、1、2 三个值,一般建议设为 2 , 但如果数据安全性要求比较高则使用默认值 1
innodb_read_io_threads
innodb_write_io_threads			以上两个参数决定了innodb读写的IO进程数,默认为 4
innodb_file_per_table			关键参数,控制innodb每一个表首页独立的表空间,默认为 OFF ,也就是所有的表都建立在共享表空间中
innodb_stats_on_metadata		决定了MySQL在什么情况下会刷新innodb表的统计信息,我们可以设为 OFF

这些配置文件,我们也可以借助于第三方工具:Percon Configuration Wizard来配置,网址:https://tools.percona.com/wizard,这个怎么用相信大家都看得懂,就不多说了

六、服务器硬件优化

MySQL有一些工作只能使用到单核CPU,例如SQL的执行
MySQL对CPU合数的支持并不是越多越快,MySQL5.5使用的服务器不要超过32核
还有磁盘的选择,一般建议使用:RAID1+0

分享到:
作 者: BridgeLi,http://www.bridgeli.cn/
原文链接:https://www.bridgeli.cn/archives/187
版权声明:非特殊声明均为本站原创作品,转载时请注明作者和原文链接。
分类: MySQL 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.