开发者社区> 技术小胖子> 正文

mysql性能优化

简介:
+关注继续查看

MYSQL性能优化 慢查询分析

1)性能瓶颈定位

Show 命令 慢查询日志 explain 分析查询  profiling 分析查询

2)索引及查询优化 3)配置优化

MySQL 数据库是常见的两个瓶颈是 CPU 和 I/O 的瓶颈,CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

可以用mpstat, iostat, sar 和 vmstat 来查看系统的性能状态。

wKiom1lU-7fyAlJHAAAby-eo9Oo511.png-wh_50

iostat,

wKiom1lU-82AEv-JAAAwK-ttvUA918.png-wh_50

wKiom1lU-9yw6ZBZAAAhAXgeES8001.png-wh_50

优化数据库的性

能,通常有三种:使用索引,使用 EXPLAIN 分析查询以及调整 MySQL 的内部配置。

、 查询与索引优化分析

在优化 MySQL 时,通常需要对数据库进行分析EXPLAIN 分析查询,profiling 分析以及 show 命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

查看 MySQL 服务器配置信息 mysql> show variables;

查看 MySQL 服务器运行的各种状态值 mysql> show global status;

# mysqladmin variables -u username -ppassword——显示系统变量

wKiom1lU_AaB_Km4AAAM0Ztf2Po010.png-wh_50

# mysqladmin extended-status -u username -ppassword——显示状态信息

wKiom1lU_CeR5msjAAAi13cH8is401.png-wh_50

比较全的 show 命令的使用可参考:mysql>help show

 http://dev.mysql.com/doc/refman/5.7/en/show.html

慢查询日志开启:

在配置文件 my.cnf 中在 [mysqld] 一行下面加入 个配置参数,并重启 mysql 服务

slow_query_log = 1 //0 关闭 开启

slow_query_log_file = /usr/local/mysql/data/slow-query.log //慢查询日志存放地点

long_query_time = 1 //表示查询超过 秒才记录

 my.cnf 中添加 log-queries-not-using-indexes 参数,表示向慢查询日志中记录下没有使用索引的查询。

 查看慢查询的设置信息

mysql> show variables like '%slow_query_log%';

wKiom1lU_EaDpTiDAAAT-g0315g403.png-wh_50

mysql> show variables like '%long_query_time%';

化这个 sql 查询语句

mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';

explain 分析查询

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理你的

SQL 语句的。

wKiom1lU_F3ThuExAAAdu-nFkm8796.png-wh_50

explain 模拟优化器执行 sql 语句来看是没有使用索引查询的,而是全表扫描

优化方法: stuname 列上创建索引

mysql> create index index_stuname on test1.tb1(stuname);

再次执行 explain

mysql> explain select * from test1.tb1 where stuname='admin'\G;

wKioL1lU_HnxOHIeAAAwZ_sCt0c980.png-wh_50

profiling 分析查询

通过慢日志查询可以知道哪些 SQL 语句执行效率低下,通过 explain 我们可以得知 SQL 语句具体执行情况,索引使用等,还可以结合 show 命令查看执行状态。

wKiom1lU_Jfj5JjGAAAQg_Ct_UU432.png-wh_50wKioL1lU_KXCi-ATAAAQg_Ct_UU937.png-wh_50

select @@profiling; //0 表示未开启

wKiom1lU_L7z2BobAAAJe2U8YNQ611.png-wh_50

执行要测试的 sql 语句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

 

mysql> show profiles\G; //可以得到被执行的 SQL 语句的时间和 ID

status:是 profile 里的状态,duration:是 status 状态下的耗时。

   Mysql配置优化

Mysql 参数优化对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试才有可能得到最佳效果。

1) 连接请求的变量:

1. max_connections

数值过小会经常出现 ERROR 1040: Too many connections 错误,可以过 mysql> show status like

'connections';通配符查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数),以定夺该值的大小。

wKioL1lU_NrQ5VgxAAAPm79dJM8199.png-wh_50

最大连接数

wKiom1lU_PWBNpNcAAAPst6zoCM106.png-wh_50

max_used_connections / max_connections * 100% (理想值≈ 85%

如果 max_used_connections 跟 max_connections 相同那么就是 max_connections 设置过低或者超过服务器负载上限了,低于 10%则设置过大。

2.back_log

MySQL 能暂存的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求,它就会起作用。

如何设置 back_log?

修改/etc/my.cnf 文件,在[mysqld]下面添加如下内容,如设置最大连接数为 1024back_log = 数值  重启

3.wait_timeout 和 interactive_timeout

wait_timeout -- 指的是 MySQL在关闭一个非交互的连接之前所要等待的秒数interactive_time -- 指的是 mysql 在关闭一个交互的连接之前所要等待的秒数

wait_timeout

1)如果设置大小,那么连接关闭的很快,从而使一些持久的连接不起作用

2)如果设置太大,容易造成连接打开时间过长,在 show processlist 时,能看到太多的 sleep

状态的连接,从而造成 too many connections 错误

3)一般希望 wait_timeout 尽可能地低

interactive_timeout 的设置将要对你的 web application 没有多大的影响

查看 wait_timeout 和 interactive_timeout

mysql> show variables like '%wait_tmeout%';

wKiom1lU_R7DsnSTAAASV6VgVBQ068.png-wh_50

mysql> show variables like '%interactive_timeout%';

wKiom1lU_UOiXmgCAAAPUK02e5M656.png-wh_50

如何设置 wait_timeout 和 interactive_timeout ?

修改/etc/my.cnf 文件,在[mysqld]下面添加如下内容

 wait_timeout=100

interactive_timeout=100

重启 MySQL Server 进入后,查看设置已经生效。

 全局缓冲:

4.key_buffer_size

key_buffer_size 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值 Key_read_requests 和 Key_reads,可以知道 key_buffer_size 设置是否合理。

wKioL1lU_YqzG5bZAAAQVHxvSgw884.png-wh_50

一共有 6 个索引读取请求,有 个请求在内存中没有找到直接从硬盘读取索引,计算索引命中缓存的概率:

key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表,但是内部的临时磁盘表是 MyISAM 表,也要使用该值。可以使用检查状态值 created_tmp_disk_tables 

wKiom1lU_aKj2w4iAAAHUfiPTPA903.png-wh_50

 key_buffer_size

默认配置数值是 8388608(8M),主机有 4GB 内存,可以调优值为 268435456(256MB)修改/etc/my.cnf 文件,在[mysqld]下面添加如下内容

5. query_cache_size(查询缓存简称 QC)

使用查询缓冲,MySQL 将查询结果存放在缓冲区中,今后对于同样的 SELECT 语句(区分大小写),将直接从缓冲区中读取结果。

wKiom1lU_brD5q1fAAAajPDg1w4348.png-wh_50

如果该值显示较大,则说明 Query Cache 中的内存碎片较多了,FLUSH QUERY CACHE 会对缓存中的碎片进行整理

Qcache_queries_in_cache:当前 Query Cache 中 cache 的 Query 数量;Qcache_total_blocks:当前 Query Cache 中的 block 数量;。查询服务器关于 query_cache 的配置:

wKiom1lU_dTwqIMuAAAZ_UL5Usg846.png-wh_50

query_cache_limit:超过此大小的查询将不缓存

query_cache_min_res_unit:缓存块的最小大小 query_cache_min_res_unit 的配置是一柄双刃剑,默认是 4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费。

在优化之前执行 mysqlslap 工具进行测试

wKioL1lU_e7DupP5AAA_NClABzM051.png-wh_50

优化之后执行 mysqlslap 工具进行测试

wKioL1lU_gPwwrS8AABiHb0jvRs225.png-wh_50






     本文转自柴鑫旺 51CTO博客,原文链接:http://blog.51cto.com/chaixinwang/1943286,如需转载请自行联系原作者









版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL 性能优化
网站访问量越来越大,MySQL 自然成为瓶颈,因此最近我一直在研究MySQL的优化,第一步自然想到的是MySQL系统 参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库 系统,不可能指望MySQL默认的系统参数能够让MySQL运行得非常顺畅。
660 0
【MySQL性能优化】Mysql基本性能分析命令详解-EXPLAIN
【MySQL性能优化】Mysql基本性能分析命令详解-EXPLAIN 原文地址https://blog.csdn.net/moshenglv/article/details/52093736  一、MySQL 查询优化器是如何工作的         MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严&#26684;的索引来消除尽可能多的数据行。
1653 0
mysql性能优化-简易版
mysql性能优化 sql语句优化 如何发现有问题的sql? 开启mysql慢查询 show variables like 'slow_query_log' set global slow_query_log_file='/var/mysql/mysql_log/mysql-slow.
948 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
18986 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
25226 0
【MySQL】性能优化之 straight_join
   研究过或者熟悉oracle性能调优的朋友都知道oracle 提供很多hint 指定from 后的表的连接顺序,如use_hash  ordered ,leading 等,而MySQL 对表的连接只支持 nested loop Join, 提供的表连接驱动的hint 只有--straight_join(相当于Oracle里面的use_nl).
754 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
20688 0
21117
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载