性能优化之MySQL优化

目前数据库是大多数系统进行数据存储的基础组件,数据库的效率对系统的稳定和效率有着至关重要的影响;为了有更好的用户体验,数据库的优化显得异常重要。那么我们要从那些方面对我们的数据库进行优化呢?让我们在这门课程中共同探讨一起学习吧!

一、数据库优化的目的

1)避免出现页面访问错误
  • 由于数据库连接timeout产生页面5xx错误
  • 由于慢查询造成页面无法加载
  • 由于阻塞造成数据无法提交
2)增加数据库的稳定性
  • 很多数据库问题都是由于低效的查询引起的
3)优化用户体验
  • 流畅页面的访问速度
  • 良好的网站功能体验

二、数据库优化的几个方面

  1. SQL及索引
  2. 数据库表结构
  3. 系统配置
  4. 硬件

下面是实际演示部分,需要mysql数据库,如果你还没安装mysql,请参照之前文章 《CentOS7 64位下MySQL5.7源码编译安装与配置》 先安装好mysql。

三、开启MySQL慢查询日志

使用MySQL慢查询日志可以对有效率问题的SQL进行监控
先使用自己的账号密码登录到mysql数据库

# mysql -uroot -p

1)查看mysql是否开启慢查询日志
# show variables like 'slow_query_log';

value为off,这里并没有开启慢查询日志

2)设置没有索引的记录到慢查询日志

先查看是否设置了将没有使用索引的查询记录到慢查询日志中

# show variables like 'log_queries_not_using_indexes';

我这里已是开启了,如你的为off,使用下面命令开启:

# set global log_queries_not_using_indexes=on;
3)查看超过多长时间的sql进行记录到慢查询日志
# show variables like 'long_query_time';

这里由于我之前设置过,为了演示需要,先设置为0;注意:在生产中不能设置得过小,不然会产生大量慢查询日志。

# set global long_query_time=0;

这里需要先退出mysql,再重新登录mysql才能看到修改后效果

4)开启慢查询日志
# set global slow_query_log=on;

5)查看日志记录位置
# show variables like 'slow_query_log_file';

6)查看日志

在查看日志前,先执行一些查询操作:

# show databases;
# use laravel-jianshu;
# show tables;
# select * from users;

退出mysql环境,使用tail命令查看慢日志,-50表示显示前50条

# tail -50 /var/lib/mysql/mysql-slow.log

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

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

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

2. IO大的SQL

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

3. 未命中索引的SQL

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

慢查日志的分析工具:
  • mysqldumpslow
  • pt-query-digest
慢查询分析工具1:mysqldumpslow

mysql官方慢查询工具,安装mysql就默认安装了mysqldumpslow;

# mysqldumpslow -t 3 /var/lib/mysql/mysql-slow.log

慢查询分析工具2:pt-querey-diget

pt-query-digest是用于分析mysql慢查询的一个工具,它可以分析binlog、General log、slowlog,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。

可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。pt-query-digest是一个perl脚本,只需下载并赋权即可执行。

pt-query-digest包含在percona-toolkit里面,如果已经安装过percona-toolkit则可以直接使用,下面是centos系统中pt-query-digest的单独安装方法:

安装过程:下载pt-query-disgest 、 授权 、 将其放到/usr/bin下:

# wget percona.com/get/pt-query-digest
# chmod u+x pt-query-digest
# mv /usr/src/pt-query-digest /usr/bin/

使用--help检测是否安装成功

# pt-query-digest --help

pt-query-disgest用法示例

(1)直接分析慢查询文件:

# pt-query-digest /var/lib/mysql/mysql-slow.log

如何分析SQL查询

五、SQL语句优化

5.1 Count()和Max()的优化

1、MAX()的优化方法:在要MAX的字段上加索引(执行计划不SELECT直接出结果)

2、COUNT()的优化方法:
在一条SQL中同时查出2006年和2007年电影的数量__优化count()函数

错误的方式:

SELECT COUNT(release_year='2006' OR release_year='2007') from film;

正确的方式:

SELECT COUNT(release_year='2006' OR NULL) as '2006 files',COUNT(release_year='2007' OR NULL) as '2007 files' from film;

count(*)和count(id)的区别:

count(*)会包含null的列,count(字段)不会包含null的列

5.2 子查询优化

MySQL从4.1版本开始支持子查询,使用子查询进行SELECT语句嵌套查询,可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然很灵活,但是执行效率并不高。

执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响。

优化前:

# select * from t where t.id in (select tid from t1);

优化后:

# select * from t join t1 on t.id=t1.tid;

通常情况下,需要把子查询优化为join查询,注意关联键是否一对多,注意重复数据;
如果是一对多查询,使用join on时要用distinct去重。

# select distinct * from t join t1 on t.id=t1.tid;

5.3 Group by优化

group by可能会出现临时表(Using temporary),文件排序(Using filesort)等,影响效率。

如果涉及到表联接的过程中有group by这样的语句,可以先通过group by做为子查询,统计出结果后,再与其它表进行关联查询。

优化前:

# SELECT actor.first_name,actor.last_name,COUNT(*)
FROM film_actor
INNER JOIN actor USING(actor_id)
GROUP BY film_actor.actor_id;

优化后:

# select actor.first_name,actor.last_name,c.cnt from actor inner join (select actor_id,count(*) AS cnt from film_actor GROUP BY actor_id) AS c USING(actor_id);

可以通过关联的子查询,来避免产生临时表和文件排序,可以节省io。

5.4 limit优化查询

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

优化步骤1:使用有索引的列或主键进行order by操作

# select film_id ,description from sakila.film order by film_id limit 50,5;

优化步骤2:记录上次返回的主键,在下次查询时用主键过滤(避免了数据量大时扫描过多的记录)

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

使用这种方式有一个限制,就是主键一定要顺序排序和连续的,如果主键出现空缺可能会导致最终页面上显示的列表不足5条,解决办法是附加一列,保证这一列是自增的并增加索引就可以了。

六、索引优化

选择合适的索引列:

1. 在where,group by,order by,on从句中出现的列建立索引
2. 索引字段越小越好(因为数据库的存储单位是页,一页中能存下的数据越多越好 )
3. 离散度大的列放在联合索引前面

离散度:数据唯一值越多,离散度越高
查看离散度:通过统计不同的列值来判断,count越大,离散程度越高

# select count(distinct customer_id), count(distinct staff_id) from payment;

查找重复及冗余索引

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

用法:
pt-duplicate-key-checker -uroot -p '123' -h 127.0.0.1;

七、数据库结构优化

7.1 选择合适的数据类型
  1. 使用可存下数据的最小的数据类型
  2. 使用简单地数据类型,Int要比varchar类型处理上简单
  3. 尽可能使用not null定义字段
  4. 尽量少用text类型,非用不可时最好考虑分表
7.2 表的范式化和反范式化

数据表不存在非关键字段对任意关键字段的传递函数依赖,则符合第三范式。可以将一张数据表进行拆分,来满足第三范式的要求。

范式化:

减少数据冗余、减少表的插入、更新、删除异常;

反范式化:

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

7.3 表的垂直拆分

垂直拆分三原则:

  1. 不常用的字段一个表;
  2. 常用字段一个表;
  3. 大字段一个表;
7.4 表的水平拆分

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

表的水平拆分:

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

挑战:

  1. 跨分区表进行数据查询
  2. 统计及后台报表操作

技巧:前台使用分表后的数据进行选择查询,后台通过汇总表进行数据统计

八、系统配置优化


MySQL配置文件
参数推荐配置
innodb_buffer_pool_size用于配置Innodb的缓冲池,如果数据库只有Innodb表,则推荐配置量为总内存的75%
innodb_buffer_pool_instancesMySQL5.5新增参数,可以控制缓冲池的个数,默认是1个

九、服务器硬件优化

如何选择CPU?

MySQL有一些工作只能使用单核CPU:Replicate,SQL….

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

Disk IO优化:

常用RAID级别简介:

  • RAID0:也称为条带,就是把多个磁盘链接成一个硬盘使用,这个级别IO最好
  • RAID1:也称为镜像,要求至少两个磁盘,每组磁盘存储的数据相同
  • RAID5:也是把多个(最少3个)硬盘合并成1个逻辑盘使用,数据读写时会建立奇偶校验信息,并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据发生损坏后,利用剩下的数据和相应的奇偶校验信息取恢复被损坏的数据

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

66 人推荐

声明:本文原创发布于加藤非博客,转载请注明出处:加藤非博客 jiatengfei.com 。如有侵权,请联系本站删除。

加藤非博客
请先登录再发表评论
  • 最新评论

  • 总共3条评论
加藤非博客

人的一生:请大家多多关注博主,有时可能有想不到的收获哦

2018-07-16 17:02:35 回复

加藤非博客

人的一生:博主技术精湛,又有好心教导和指教人也不错,我是得到博主帮助的人特来此发表个人的看法

2018-07-16 17:00:45 回复

加藤非博客

加藤非加藤非博客

2018-06-14 14:45:16 回复