MySQL性能优化总结

1. 数据库优化目的

1.1. 避免出现页面访问错误

1). 由于数据库连接timeout产生页面5xx错误；

2). 由于慢查询造成页面无法加载；

3). 由于阻塞造成数据无法提交；

1.2. 增加数据库的稳定性

1). 很多数据库的问题都是由于低效的查询引起的；

1.3. 优化用户体验

1). 流畅页面的访问速度

2). 良好的网站功能体验

 

2. 数据优化的几个方面

 

1). SQL和索引，写出健壮的sql，索引不是越多越好

2). 数据表结构（存储引擎，字段大小，字段类型，索引，第三规范）

3). 系统配置（打开文件系统次数，文件安全性）

4). 硬件，选择最适合数据库的cpu，更快的IO，更大的内存，cpu不是越多越好，IO并不能减少锁的机制， 也就是不能减少阻塞，所以说硬件的优化成本最高，效果最差。

2.1. SQL及索引

2.1.1. 如何发现有问题的SQL？

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

　　　　使用MySQL慢查日志对有效问题的SQL进行监控

　　　　• show variables like '%slow_query_log%' 

　　　　　　可查出两条： slow_query_log ——查看是否开启慢查日志  ON开启  OFF关闭

　　　　　　　　　　　　 slow_query_log_file——日志存储位置

　　　　• set global slow_query_log = on    开启慢查日志

　　　　• show variables like 'long_query_time'  什么样的SQL才会被记录到慢日志中由long_query_time控制，默认情况下为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运  行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。如果我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。你用show variables like 'long_query_time'查看是当前会话的变量值，你也可以不用重新连接会话，而是用show global variables like 'long_query_time'

　　　　• set global log_queries_not_using_indexes = on　　设置没有索引的记录到慢查询日志中

#使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

　　　　慢查日志中所包含的内容：

　　

　　　　执行SQL的主机信息：　　# User@Host: root[root] @ localhost [127.0.0.1]

　　　　SQL的执行信息：　　# Query_time: 0.001000  Lock_time: 0.000000 Rows_sent: 9  Rows_examined: 9

　　　　SQL执行时间：　　SET timestamp=1557234880

　　　　SQL的内容：　　SELECT * from broker_message_log;

　　　　如果每天数据量很大，想在特别多的sql中查找到有问题的sql工作量特别大，几乎是不可能的，所以在实际应用中我们使用慢查日志分析工具，常用的有mysqldumpslow、pt-query-digest两种，下面分别进行简单介绍：

　　　　mysqldumpslow： 最常用的工具，但是统计的结果数据比较少，所包含的信息对于优化来说还不够充足

　　　　查看帮助（可以查看到参数都有哪些）：

　　　　

　　　　查看前3条：

　　　　

　　　　上方命令输入后分析报表的样式：

　　　　

　　　　Count： sql所执行的次数

　　　　Time：sql所执行的时间

　　　　Lock：锁定时间

　　　　Rows：发生的行数

 　　　　pt-query-digest：数据结果非常全

　　　　查看帮助，获取使用方式：

　　　　

　　　　查看分析日志：

　　

　　　　分析日志：分为3个部分

　　　　第一部分：查看整体

　　　　

 

　　　　第二部分：查看具体SQL

 　　

　　　　

　　　　第三部分：具体单个SQL分析

　　　　

　　　　分析后的结果可以输出到外部：

　　　　

2.1.2. 什么样的SQL有问题，需要优化呢？

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

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

2).IO大的SQL

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

3).未命中索引的SQL

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

2.1.3. 如何分析SQL查询？

　　　　使用explain查询SQL的执行计划：语句前面加上explain就可以得到下图样式

 　　

　　　　explain返回各列含义：

　　　　table：显示这一行的数据是关于哪张表的

　　　　type：这是重要的列，显示连接使用了何种类型，从最好到最差的连接类型为const、eq_req、ref、range、index和ALL

　　　　possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引

　　　　key：实际使用的索引。如果为NULL，则没有使用索引

　　　　key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

　　　　ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

　　　　rows：MySQL认为必须检查的用来返回请求数据的行数

　　　　extra列需要注意的返回值：

　　　　　　Using filesort：看到这个的时候，查询就需要优化了。MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

　　　　　　Using temporary：看到这个的时候，查询需要优化了。这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上

 　　　　Count()和Max()的优化方法：

　　　　　　1.查询最后支付时间——优化Max()函数

　　　　　　explain select max(payment_date) from payment; 查询结果如下图：

　　　　　　

　　　　　　上述SQL全表扫描，查询了15422行，如何优化呢？我们可以在此字段上添加一个索引：create index idx_paydate on payment(payment_date);此时查出来结果为：

　　　　　　

　　　　　　为什么会出现上面情况呢，因为索引是顺序排列的，并不需要实际的查询

　　　　　　2.在一条SQL中同时查出2006年和2007年电影的数量——优化count()函数

　　　　　　错误方式：

　　　　　　　　　　select count(release_year = '2006' or release_year = '2007') from film; //无法分开计算2006年和2007年的电影数量

　　　　　　　　　　select count(*) from film where release_year = '2006' and release_year = '2007'; //release_year不可能同时为2006和2007，逻辑上有错误

 　　　　　　正确写法：　　　　　

　　　　　　　　　　select count(release_year = '2006' or null) , count(release_year = '2007' or null) from film;

　　　　　　　　这里包含一个小知识点，count(*) 与count(某一列) 区别，count(*)即使是空值也会被统计出来，count(某一列) 只统计不为空值的行；

　　　　　　　　为什么要加上or null呢？因为 当 release_year不是 2006时 ，release_year='2006' 结果false 不是 NULL，Count在 值是NULL是 不统计数， 至于加上or NULL ， 很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。当release_year不为2006时release_year = '2006' or NULL 的结果是NULL，Count才不会统计上这条记录数。

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

　　　　　　　　创建两张表：

　　　　　　　　　　　　　t 表    t1 表

　　　　　　　　子查询写法：select * from t where id in (select tid from t1);      查询结果

　　　　　　　　优化写法：select t.* from t t  join t1 t1 on t.id = t1.tid;  查询结果，这种写法如果t1表有两个相同的字段如，此条sql查出来的结果为，所以需要去重，只需要在前面加上distinct关键字就可以啦（select distinct t.* from t t  join t1 t1 on t.id = t1.tid）

 　　　　　4.limit优化

limit常用于分页处理，　时常会伴随order by从句使用，因此大多时候会使用Filesorts是这样会造成大量的IO问题，页数越大，IO越大。

优化：记录上次返回的主键，在下次查询的时候用主键过滤，避免了数据量大时扫描过多的记录 

　　select film_id,description from sakila.film where film_id>55 and film_id<=60 order by film_id limit 1,5;

上述这种写法一定要保证film_id是连续的。

2.1.4. 索引优化

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

1). 在where从句，group by从句，order by从句，on从句中出现的列（select）

2). 索引字段越小越好（表每页数据才会更多，IO效率会更高）

3). 离散度大的列放到联合索引的前面

select * from payment where staff_id = 2 and customer_id = 584;

index(staff_id,customer_id)好？还是index(customer_id,staff_id)好？

由于customer_id的离散度更大（重复率小，可选择性更大），所以应该使用index(customer_id,staff_id)

2.1.4.2. 索引优化SQL方法

1). 索引的维护及优化--重复及冗余索引

冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。如下：key(name,id)就是一个冗余的索引

create table test（

　　id int not null primary key,

　　name varchar(10) not null,

　　key(name,id)

）engine=innodb

//可以删除冗余索引，达到优化效果

 

两种方法可以查到冗余索引

第一种：进入 information_schema 库，执行下面语句

select a.table_schema as '数据名',

       a.table_name as '表名',

       a.index_name as '索引1',

       b.index_name as '索引2',

       a.column_name as '重复列名'

from   statistics a

join   statistics b on a.table_schema = b.table_schema

and    a.table_name = b.table_name

and    a.seq_in_index = b.seq_in_index

and    a.column_name = b.column_name

where  a.seq_in_index = 1

and    a.index_name <> b.index_name

 

第二种：使用pt-duplicate-key-checker工具检查重复及冗余数据

pt-duplicate-key-checker \

-uroot \

-p '' \

-h 127.0.0.1

 

2). 索引维护的方法--删除不用索引

目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可通过INDEX_STATISTICS表来查看哪些索引未使用，但在MySQL中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况分析。

pt-index-usage \

    -uroot -p'' \

    mysql-slow.log

 

2.2. 数据库表结构优化

2.2.1. 选择合适的数据类型

1). 使用可以存下你的数据的最小的数据类型

2). 使用简单的数据类型。int要比varchar类型在mysql处理上更简单

3). 尽可能的使用not null定义字段

4). 尽量少用text类型，非用不可时最好考虑分表

*使用int来存储日志时间，利用FROM_UNIXTINE()(得到日期),UNIX_TIMESTAMP()(得到时间戳)两个函数来进行转换

*使用bigint来存ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换

2.2.2. 表的范式化和反范式化

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

　　不符合第三范式要求的表存在下列问题：

　　1). 数据冗余：（分类，分类描述）对于每一个商品都会进行记录

　　2). 数据的插入异常

　　3). 数据的更新异常

　　4). 数据的删除异常

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

2.2.3. 表的拆分

2.2.3.1. 垂直拆分

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

1). 把不常用的字段单独存放到一个表中

2). 把大字段独立存放到一个表中

3). 把经常一起使用的字段放到一起

2.2.3.2. 水平拆分

表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。

常用的水平拆分方法为：

1). 对id进行hash运算，如果要拆分成5个表则使用mod(id,5)去除0-4个值

2). 针对不同的hashID把数据存到不同的表中<br>缺点：后台管理统计的时候会统计汇总的数据，所以会比较慢

2.3. 系统配置优化

2.3.1. 操作系统配置优化

数据库是基于操作系统的，目前大多数MySQL是安装在Linux系统之上，所以对于操作系统的一些参数配置也会影响到MySQL的性能。

 

网络方面的配置，要修改/etc/stysctl.conf文件

#增加tcp支持的队列数

net.ipv4.tcp_max_syn_backlog = 65535

#减少断开链接是，资源回收

net.ipv4.tcp_max_tw_buckets = 8000

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_fin_timeout = 10

 

打开文件数的限制，可以使用ulimit -a 查看目录的各位限制，可以修改/etcsecurity/limitsconf文件，增加一下内容以修改打开文件数量的限制

*soft nofile 65535

*hard nofile 65535

除此之外最好在mysql服务器上关闭iptables，selinux等防火墙软件。

2.3.2. MySQL数据库优化

　　 MySQL配置文件

 

mysql可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在大数情况下配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf在windows系统配置文件可以是位于C:/windows/my.ini文件，mysql查找配置文件的顺序可以通过一下方法获得。

/usr/sbin/mysqld --verbose --help | grep -A 1 ' Default options '

 

　　MySQL配置文件--常用参数说明

1). innodb_buffer_pool_size >= total MB

非常重要的一个参数，用于配置innodb的缓冲池，如果数据库中只有innodb表，则推荐配置量为总内存的75%

 

2). innodb_buffer_pool__instances

MySQL5.5中新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。

 

3). innodb_log_buffer_size

innodb log缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大。

 

4). innodb_flush_log_at_trx_commit

关键参数，对innodb的IO效率影响很大。默认值为1，可取0，1，2三个值，一般建议为2，但如果数据安全性要求比较高则使用默认值1.

 

5). innodb_read_io_threads   innodb_write_io_threads

以上两个参数决定了Innodb读写的IO进程数，默认为4.

 

6). innodb_file_per_table

关键参数，控制innodb每一个表使用独立的表空间，默认为off，也就是所有表都会建立在共享表空间中。

 

7). innodb_stats_on_metadata

决定了mysql在什么情况下会刷新innodb表的统计信息。

2.4. 服务器硬件优化

2.4.1. 如何选择CPU？

1). MySQL有一些工作只能使用到单核cpu，Replicate, SQL...

2). MySQL对cpu核数的支持并不是越多越快。MySQL5.5使用的服务器不要超过32核。

2.4.2. 磁盘IO优化

常用RAID级别简介

RAID0：也称条带，就是把多个磁盘链接成一个硬盘使用，这个级别IO最好

RAID1：也称镜像，要求至少有两个磁盘，每组磁盘存储的数据相同

RAID5：也是把多个硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，分别存储在不同磁盘上。

RAID1+0：就是RAID1和RAID0的结合。同时具备两个级别的优缺点。一般建议数据库使用这个级别。

 

SNA和NAT是否适合数据库？

1). 常用于高可用解决方案

2). 顺序读写效率很高，但是随机读写不如人意

3). 数据库随机读写比率很高

3. 结束语

从五个不同层次优化mysql性能，较为基础，但是也很实用?。

这篇是在学习慕课网《性能优化之MySQL优化》视频时做的笔记，视频地址：https://www.imooc.com/learn/194