BridgeLi's Blog

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

一、数据库优化简介

1. 数据库优化的目的

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

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

所谓一图胜千言，所以我们先看一下，下面这张图：

从这张图上，我们可以清晰的看到，我们优化的重点和代价，另外这几个方面我们分别可以做些什么呢？
①．在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