Mysql数据库高CPU问题定位和优化

本课程的主旨及目标

导致mysql数据库CPU高的常见原因

常见定位问题的方法

一般定位步骤

数据库注意事项

导致mysql数据库CPU高的常见原因

占用CPU过高,可以做如下考虑:

1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL,比如适当建立某字段的索引;
2)打开慢查询日志,将那些执行时间过长且占用资源过多的SQL拿来进行explain分析,导致CPU过高,多数是GroupBy、OrderBy排序问题所导致,然后慢慢进行优化改进。比如优化insert语句、优化group by语句、优化order by语句、优化join语句等等;
3)考虑定时优化文件及索引;

4)定期分析表,使用optimize table;
5)优化数据库对象;
6)考虑是否是锁问题;

7)调整一些MySQL Server参数,比如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等;
8)如果数据量过大,可以考虑使用MySQL集群或者搭建高可用环境。
9)可能由于内存(泄露)导致数据库CPU高
10)在多用户高并发的情况下,任何系统都会hold不住的,所以,使用缓存是必须的,使用memcached或者redis缓存都可以;
11)看看tmp_table_size大小是否偏小,如果允许,适当的增大一点;
12)如果max_heap_table_size配置的过小,增大一点;
13)mysql的sql语句睡眠连接超时时间设置问题(wait_timeout)
14)使用show processlist查看mysql连接数,看看是否超过了mysql设置的连接数

一般定位步骤:

   1】首先看看内存 free –m

目前看没有问题,1G的空闲

2】好了,用我们的必杀技,top看看资源消耗

可以看到服务器负载很高,mysql CPU使用已达到接近400%,基本可以看出mysql是可以进行优化的

3】进入mysql,执行show full processlist;

这里我们要看的其实就2点:线程数和慢sql

现在我们看到线程就5个,可以先排除线程的问题了。

那我们接下来就是来看看慢sql了

4】首先我们先执行show variables like ‘slow_query_log%’;来查看慢日志的路径

5】我们排查该文件中的慢sql,这里我们看出来了是查询语句直接耗时2秒,explain 该sql发现耗时超过10S

数据库注意事项:

1、重要的sql必须被索引,例如:

1selectupdatedelete语句的where条件列;

2order bygroup bydistinct字段

2mysql索引的限制:

1mysql目前不支持函数索引

2)使用不等于(!=或者<>)的时候,mysql无法使用索引

3)过滤字段使用单行函数 (如 abs (column)) , MYSQL无法使用索引。

4) join语句中join条件字段类型不一致的时候MYSQL 无法使用索引

5)使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’), MYSQL无法使用索引。

6)使用非等值查询的时候, MYSQL 无法使用 Hash 索引。

7BLOB 和 TEXT 类型的列只能创建前缀索引

3mysql常见sql规范:

1SQL语句尽可能简单 大SQL语句尽可能拆成小SQL语句,MySQL对复杂SQL支持不好。 

2)事务要简单,整个事务的时间长度不要太长,SQL结束后及时提交。

3)限制单个事务所操作的数据集大小,不能超过 10000 条记录

4)禁止使用触发器、函数、存储过程。 

5)降低业务耦合度,为scale outsharding留有余地 

6)避免在数据库中进行数学运算(数据库不擅长数学运算和逻辑判断) 

7)避免使用select *,需要查询哪几个字段就select这几个字段,避免buffer pool被无用数据填充。

8)条件中使用到ORSQL语句必须改写成用IN()OR的效率比IN低很多) 

9IN()里面的数据个数建议控制在 500 以内,可以用exist代替inexist在某些场景比in效率高,尽量不使

not in。 

10limit分页注意效率。 limit越大,效率越低。可以改写limit,例如:

 select id from test limit 10000,10 可以改写为 select id from test where id > 10000 limit 10 

 11)当只要一行数据时使用LIMIT 1 

12)获取大量数据时,建议分批次获取数据,每次获取数据少于 10000 条,结果集应小于 1M 

13)避免使用大表做 JOIN,使用group by分组、自动排序  

14SQL语句禁止出现隐式转换,例如:select id from test where id=’1’,其中 id  列为 int  等数字

类型。

15)在SQL中,尽量不使用like,且禁止使用前缀是%like匹配。 

16)合理选择union allunion 

17)禁止在OLTP类型系统中使用没有where条件的查询。

18)使用 prepared statement 语句,只传参数,比传递 SQL 语句更高效;一次解析,多次使用;降低SQL

注入概率。

19)禁止使用 order by rand().

20)禁止单条 SQL 语句同时更新多个表。

21)不在业务高峰期批量更新或查询数据库,避免在业务高峰期alter表。

22)禁止在主库上执行 sum,count 等复杂的统计分析语句,可以使用从库来执行。

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值