MySQL数据库优化

MySQL数据库优化

数据库优化的目的:

避免出现页面访问错误

· 由于数据库连接tomeout产生页面5xx错误(服务器内部错误,由web、中间件、数据库等引起) · 由于慢查询造成页面无法加载(web及数据库的慢速查询到时页面无法加载避免慢速查询和事物阻塞) · 由于阻塞造成数据无法提交(服务器内部锁的原因,在大并发更新某一个字段时产生阻塞,轻则影响服务器性能,数据库中有锁超时,阻塞超过一定的时间,事物就会被回滚,影响到业务,及收入

增加数据库的稳定性

• 很多数据库问题都是由于低效的查询引起的

优化用户体验

• 流畅页面的访问速度 • 良好的网站功能体验

优化方面:

3 系统配置(linux对mysql打开文件数的限制等)
4 硬件(提高文件io的速度,大内存,但是成本最高)
io的优化并不能减少阻塞  ,但sql索引没有优化好,则会产生大量的慢查询或阻塞,这是由于mysql的内部锁机制造成,所以硬件再好效果也不大
mysql对内核是有限制的

1、SQL及索引优化提升性能最大,效率最高

结构良好的SQL(选择最优的SQL)、 有效索引(索引越多不但会造成写操作的效率下降、而且也会造成读操作效率下降)

2、数据库表结构(满足范式、考虑到查询语句的写法)

(根据数据库设计范式,设计出简洁明了的表结构、减少数据的冗余; a、在设计表结构时候要想到怎么样对这个表数据进行查询 b、怎么样设计表结构才是有益于SQL写法的 SQL及索引的优化也是日常工作中所涉及到的最多的一种优化方式

3、系统配置

大多数情况下我们的mysql都是跑在linux上的,系统本身也是会有些限制: a、tcp/ip连接数的限制 b、打开文件数限制(重点) mysql都是基于文件的,每查询一个表时,需要打开一些文件, 一旦开的文件数超过上限,文件就会无法打开、就会平分IO操作 c、安全性限制

4、 硬件优化

内存:越大越好、mysql查询修改都是load在内存中进行的。 CPU:并不是越多,性能提升就越好的、mysql会对cpu核数进行限制、甚至有些查询只会用到单核 硬盘:会影响IO,可以考虑换用SSD,固态硬盘等等 所以这种IO设备对数据库肯定是有良好的影响的,但是这只是表面、并不能解决mysql内部锁的问题 lock锁是保证数据完整性的一种机制, 虽然IO很快并不能解决阻塞,所以说硬件优化、其实是成本最高,效果最不明显的 如果没有良好的SQL及有效的索引,数据库查询造成大量的慢查询、大量的阻塞,随之并发量就会上去、并发量一上去loading 就会高,会造成应用缓慢

查看mysql是否开启慢查日志

show variables like 'slow_query_log';是否开启满查询日志
show variables like '%log%';  查询设置
show variables like '%slow%';   查询慢查询日志所在位置
tail -50 /mydata/mysql/data/localhost-slow.log; 查看慢查询日志

set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log'   指定慢查询日志的位置
set global log_queries_not_using_indexes=on;    是否要把没有索引的sql记录到慢查询日志中
show variables like 'long_query_time';查看设置的超时时间
set global long_query_time=0.01;   把超过多少秒的sql记录到慢查询日志中
set global slow_query_log=on; 开启慢查日志

在优化过程中主要是优化表索引以及查询所使用的方式,所以需要把set global log_queries_not_using_indexes变量设置为on,以备后面来进行优化
慢查询日志所包含的内容

# Time: 2016-10-28T15:59:22.416695Z 解释:慢查询执行的时间点
# User@Host: root[root] @ localhost []  
解释:执行SQL的主机信息 哪个用户在哪个主机执行
# Query_time: 0.003073  Lock_time: 0.001320 Rows_sent: 2  Rows_examined: 2  
解释:SQL执行的信息,Query_time指查询所有时间,Lock_time指锁定时间,Rows_sent发送的行数,Rows_examined扫描的行数
SET timestamp=1477670362;   解释:以时间戳的形式记录了此SQL执行的时间
select * from store limit 10;   解释:SQL的具体内容

用mysql官方提供的日志分析工具查看TOP3慢日志

mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more

慢查询分析工具2:pt-query-digest

输出到文件:pt-query-digest show-log > slow_log.report 输出到数据库表 pt-query-digest show.log -review \ h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query_review \ --create-reviewtable \ --review-history t=hostname_show

pt-query-digest慢查询日志的输出有三部分

第一部分: 显示了日志的时间范围,以及总的SQL数量和不同的SQL数量 第二部分: 一些表的统计信息 Response time:响应时间占比 Calls : sql执行次数 第三部分:具体的SQL是什么

解决:

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

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

2.IO大的SQL

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

3.未命中索引的SQL

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

explain分析和返回各列的含义

table :显示这一行的数据是哪张表的
type:这是重要的列, 显示连接使用了何种类型。从最好到最差的连接类型为
     const  常数查找 唯一索引或主键查找
     eq_reg 范围查找 唯一索引主键范围查找等
     ref   连接查找 一个表是基于某一个表的查找
     range  基于索引的范围查找
     index  基于索引的扫描
     ALL    全表扫描
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引
key:实际使用的索引。如果为NULL,则没有使用索引
key_len:使用索引的长度。在不损失精确性的情况下,长度越短越好
ref:显示索引的哪一列被使用了如果可能的话,是一个常数
rows:MySQL认为必须检查的用来请求数据的的行数
Extra:扩展列 需要注意的返回值

如果extra列的返回值为Using filesort或Using temporary则查询需要优化,一般都出现在ORDER BY 或是GROUP BY上.

子查询优化

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

group by查询

如果涉及到表联接的过程中有group by这样的语句,可以先通过group by做为子查询,统计出结果后,再与其它表进行关联查询
group by可能会出现临时表(Using temporary),文件排序(Using filesort)等,影响效率。
可以通过关联的子查询,来避免产生临时表和文件排序,可以节省io<br>
改写前
select actor.first_name,actor.last_name,count(*)
注意:在数据库表中针对索引的查询往往比较快,所以,我们的查询或对于SQL语句优化要想到怎么才能利用上索引才好!
经验丰富了才能更好的判断哪一种方式比较好,关键是思想要灵活要变通要学习,要有判断力要能把握住机会!
from sakila.film_actor
inner join sakila.actor using(actor_id)
group by film_actor.actor_id;
改写后
select actor.first_name,actor.last_name,c.cnt
from sakila.actor inner join(
select actor_id,count(*) as cnt from sakila.film_actor group by
actor_id
)as c using(actor_id);

limit优化

limit常用于分页处理,时常会伴随order by从句使用,因此大多时候会使用Filesorts这样会造成大量的io问题
1.使用有索引的列或主键进行order by操作
2.记录上次返回的主键,在下次查询时使用主键过滤
使用这种方式有一个限制,就是主键一定要顺序排序和连续的,如果主键出现空缺可能会导致最终页面上显示的列表不足5条,解决办法是附加一列,保证这一列是自增的并增加索引就可以了

选择合适的索引列

1.在where,group by,order by,on从句中出现的列 2.索引字段越小越好(因为数据库的存储单位是页,一页中能存下的数据越多越好 ) 3.离散度大得列放在联合索引前面 离散度查询,唯一值越多离散度越好:select count(distinct customer_id), count(distinct staff_id) from payment;

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

冗余索引:多个索引的前缀列相同,或者是在联合索引中包含了主键索引
pt-duplicate-key-checker \
-uroot \
-p '' \
-h 127.0.0.1

通过慢查询日志配合pt-index-usage来删除不用索引:pt-index-usage -uroot -p '' mysql-slow.log


数据库表结构设计,

1.选择合适的数据类型,重点在于合适二字

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


.使用int来存储日起时间,利用from_unixtime(),unix_timestamp()两个函数来进行转换
from_unixtime() 将int转换为时间格式  unix_timestamp()时间格式转换为int
.使用bigint来存储IP地址,利用inet_aton(),inet_ntoa()两个函数转换

2.大数据字段可以考虑分表,或做附加表。

利用Mysql内置函数对日期或者ip地址等进行转换.存储最小数据类型到数据库中.范式和反范式化的操作,
数据库结构优化-表的范式化和反范式化
范式化是指数据库设计的规范,目前说道范式化一般是指第三设计范式,也就是要求数据库表中 不存在非关键字段对任意 候选关键字段的传递函数依赖
则符合第三范式

商品名称    价格   重量   有效期   分类    
----------------------------
可乐        3     250ml  201406  饮料    酸性饮料

存在以下传递函数依赖关系
商品名称->分类->分类描述

也就是说存在非关键字段 分类描述关键字段商品名称的传递函数的依赖

不符合第三范式存在下面问题:
1.数据冗余
2.数据插入异常
3.数据更新异常
4.数据删除异常

不符合第三范式的设计  需要对表拆分来解决

反范式化: 增加冗余信息,少关联表,以空间换时间

表的垂直拆分

(解决表宽度问题,将部分列提取出一个新表)
所谓垂直拆分,就是把原来一个有很多列的表拆分成多个表解决表的宽度问题,通常拆分原则如下:
1、把不常用的字段单独存放到一个表中
2、把大字段独立存放到一个表中
3、把经常一起使用的字段放到一起
当表的宽度过宽的时候,我们需要对表进行垂直拆分,具体的建议如下(原则上是人以群分,列以表分):
一表变多表,物理上不在一起,逻辑上是在一起的!

水平拆分

(解决表数据量的问题,前后台分开查询,通过汇总表来解决后台用户的查询统计一般时效要求不高).
为了解决单表数据量过大的问题,每个水平拆分表的结构完全一致
方法
1.对id进行hash运算,可以取mod
2.针对不同的hashId把数据放到不同的表中
水平拆分之后的挑战
1.跨分区进行数据查询
2.统计及后台报表操作
前后台使用的表进行分开,前台要求查询效率,所以可以说会用拆分之后的表,后台在统计数据时可以使用汇总表。

操作系统配置优化

数据库是基于操作系统的,目前大多数的MySQL都是安装在linux系统之上,
所以对于操作系统的一些参数配置也影响到MySQL的性能,下面就列出一些常用到
的系统配置。

网络方面的配置,要修改/etc/sysctl.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  //timewait

打开文件数的限制,可以使用ulimit -a 查看目录的各位限制,可以修改
/etc/security/limits.conf文件,增加以下内容以修改打开文件数量的限制
*soft nofile 65535
*hard nofile 65535
除此之外最好在MySQL服务器上关闭iptables,selinux等防火墙软件

MySQL配置优化


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

下面是检测语句

select engine,round(sum(data_ength + index_length)/1024/1024,1) as 'Total MB',
from information_schema.tables where table_schema not in ('information_schema',
'performance_schema') group by engine;

innodb_buffer_pool_size >= Total MB


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表的统计信息

mysql第三方配置工具:

https://tools.percona.com/wizard

硬件优化:

CPU: 单核频率高,不要超过32核。
IO: raid0 多个磁盘连接成一个硬盘使用,IO最好
raid1 单个备份
raid5 奇偶校验恢复
raid1+0 1和0的结合。

磁盘阵列: 1. 常用于高可用 2. 某些存储阵列顺序读写效率高,但是随机读写不如人意。


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值