Mysql占用CPU过高怎么☺优化

1、Mysql占用CPU过高怎么优化

1138 浏览 7 回答

2、mysql中怎样对大批量级的数据查询进行

MySQL 一直以来都支持正则匹配，不过对于正则替换则一直到MySQL 8.0 才支持。对于这类场景，以前要么在MySQL端处理，要么把数据拿出来在应用端处理。

比如我想把表y1的列str1的出现第3个action的子 串替换成dble，怎么实现？

1. 自己写SQL层的存储函数。代码如下写死了3个，没有优化，仅仅作为演示，MySQL 里非常不建议写这样的函数。

mysql

DELIMITER $$

USE `ytt`$$

DROP FUNCTION IF EXISTS `func_instr_simple_ytt`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `func_instr_simple_ytt`(

f_str VARCHAR(1000), — Parameter 1

f_substr VARCHAR(100), — Parameter 2

f_replace_str varchar(100),

f_times int — times counter.only support 3.

) RETURNS varchar(1000)

BEGIN

declare v_result varchar(1000) default 'ytt'; — result.

declare v_substr_len int default 0; — search string length.

set f_times = 3; — only support 3.

set v_substr_len = length(f_substr);

select instr(f_str,f_substr) into @p1; — First real position .

select instr(substr(f_str,@p1+v_substr_len),f_substr) into @p2; Secondary virtual position.

select instr(substr(f_str,@p2+ @p1 +2v_substr_len – 1),f_substr) into @p3; — Third virtual position.

if @p1 > 0 && @p2 > 0 && @p3 > 0 then — Fine.

select

concat(substr(f_str,1,@p1 + @p2 + @p3 + (f_times – 1) v_substr_len – f_times)

,f_replace_str,

substr(f_str,@p1 + @p2 + @p3 + f_times v_substr_len-2)) into v_result;

else

set v_result = f_str; — Never changed.

end if;

— Purge all session variables.

set @p1 = null;

set @p2 = null;

set @p3 = null;

return v_result;

end;

$$

DELIMITER ;

— 调用函数来更新：

mysql> update y1 set str1 = func_instr_simple_ytt(str1,'action','dble',3);

Query OK, 20 rows affected (0.12 sec)

Rows matched: 20 Changed: 20 Warnings: 0

2. 导出来用sed之类的工具替换掉在导入，步骤如下：（推荐使用）1）导出表y1的记录。

mysqlmysql> select from y1 into outfile '/var/lib/mysql-files/y1.csv';Query OK, 20 rows affected (0.00 sec)

2）用sed替换导出来的数据。

shellroot@ytt-Aspire-V5-471G:/var/lib/mysql-files# sed -i 's/action/dble/3' y1.csv

3）再次导入处理好的数据，完成。

mysql

mysql> truncate y1;

Query OK, 0 rows affected (0.99 sec)

mysql> load data infile '/var/lib/mysql-files/y1.csv' into table y1;

Query OK, 20 rows affected (0.14 sec)

Records: 20 Deleted: 0 Skipped: 0 Warnings: 0

以上两种还是推荐导出来处理好了再重新导入，性能来的高些，而且还不用自己费劲写函数代码。那MySQL 8.0 对于以上的场景实现就非常简单了，一个函数就搞定了。

mysqlmysql> update y1 set str1 = regexp_replace(str1,'action','dble',1,3) ;Query OK, 20 rows affected (0.13 sec)Rows matched: 20 Changed: 20 Warnings: 0

还有一个regexp_instr 也非常有用，特别是这种特指出现第几次的场景。比如定义 SESSION 变量@a。

mysqlmysql> set @a = 'aa bb cc ee fi lucy 1 1 1 b s 2 3 4 5 2 3 5 561 19 10 10 20 30 10 40';Query OK, 0 rows affected (0.04 sec)

拿到至少两次的数字出现的第二次子串的位置。

mysqlmysql> select regexp_instr(@a,'[:digit:]{2,}',1,2);+————————————–+| regexp_instr(@a,'[:digit:]{2,}',1,2) |+————————————–+| 50 |+————————————–+1 row in set (0.00 sec)

那我们在看看对多字节字符支持如何。

mysql

mysql> set @a = '中国 美国 俄罗斯 日本 中国 北京 上海 深圳 广州 北京 上海 武汉 东莞 北京 青岛 北京';

Query OK, 0 rows affected (0.00 sec)

mysql> select regexp_instr(@a,'北京',1,1);

+——————————-+

| regexp_instr(@a,'北京',1,1) |

+——————————-+

| 17 |

+——————————-+

1 row in set (0.00 sec)

mysql> select regexp_instr(@a,'北京',1,2);

+——————————-+

| regexp_instr(@a,'北京',1,2) |

+——————————-+

| 29 |

+——————————-+

1 row in set (0.00 sec)

mysql> select regexp_instr(@a,'北京',1,3);

+——————————-+

| regexp_instr(@a,'北京',1,3) |

+——————————-+

| 41 |

+——————————-+

1 row in set (0.00 sec)

那总结下，这里我提到了 MySQL 8.0 的两个最有用的正则匹配函数 regexp_replace 和 regexp_instr。针对以前类似的场景算是有一个完美的解决方案。

3、如何优化MySQL千万级大表

概述

使用阿里云rds for MySQL数据库（就是MySQL5.6版本），有个用户上网记录表6个月的数据量近2000万，保留最近一年的数据量达到4000万，查询速度极慢，日常卡死，严重影响业务。

老系统，当时设计系统的人大概是大学没毕业，表设计和SQL语句写的不仅仅是垃圾，简直无法直视。原开发人员都已离职，到我来维护，这就是传说中的维护不了就跑路，然后我就是掉坑的那个！！！

方案概述

方案一：优化现有MySQL数据库。优点：不影响现有业务，源程序不需要修改代码，成本最低。缺点：有优化瓶颈，数据量过亿就玩完了。

方案二：升级数据库类型，换一种100%兼容MySQL的数据库。优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能，缺点：多花钱。

方案三：一步到位，大数据解决方案，更换newSQL/noSQL数据库。优点：没有数据容量瓶颈，缺点：需要修改源程序代码，影响业务，总成本最高。

优化现有MySQL数据库数据库设计

表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。

尽量使用INT而非BIGINT，如☞果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。

尽量使用TIMESTAMP而非DATETIME。

单表不要有太多字段，建议在20以内。

用整型来存IP。

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描。

应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。

值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段。

字符字段最好不要做主键。

不用外键，由程序保证约束。

尽量不用UNIQUE，由程序保证约束。

使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引。

使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob

使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数。

使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar。

尽可能使用not null定义字段。

尽量少用text，非用不可最好分表。

查询频繁的列，在where，group by，order by，on从句中出现的列。

where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列。

长度小的列，索引◐字段越小越好，因为数据库的存储单位是页，一页中能☢存下的数据越多越好。

离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高。

SQL编写

使用limit对查询结果的记录进行限定。

避免select ，将需要查找的字段列出来。

使用连接（join）来代替子查询。

拆分大的delete或insert语句。

可通过开启慢查询日志来找出较慢的SQL。

不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

SQL语句尽可能简单：一条SQL只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大SQL可以堵死整个库。

OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内。

不用函数和触发器，在应用程序实现。

避免%xxx式查询。

少用JOIN。

使用同类型进行比较，比如用'123'和'123'比，123和123比。

尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5。

列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大。

分区

分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。

部分查询能够从查询条件确定只落在少数分区上，速度会很快。

分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备。

可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争。

可以备份和恢复单个分区。

一个表最多只能有1024个分区。

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。NULL值会使分区过滤无效。

所有分区必须使用相同的存储引擎。

分表

分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。

分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表：表名为 tableName_id%100。

但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高！！！而且选择这个方案，都不如选择我提供的第二第三个方案的成本低！故不建议采用。

分库升级数据库

开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品。如tiDB pingcap/tidb，Cubrid Open Source Database With Enterprise Features。

阿里云POLARDB，POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达 100T，性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征，又具有开源数据库简单、可扩展、持续迭代的优势，而成本只需商用数据库的 1/10。

阿里云OcenanBase，淘宝使用的，扛得住双十一，性能卓著，但是在公测中，我无法尝试，但值得期待。

阿里云HybridDB for MySQL (原PetaData)，云数据库HybridDB for MySQL （原名PetaData）是同时支持海量数据在线事务（OLTP）和在线分析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）关系型数据库。

腾讯云DCDB，DCDB又名TDSQL，一种兼容MySQL协议和语法，支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表，数据却均匀的拆分到多个分片中；每个分片默认采用主备架构，提供灾备、恢复、监控、不停机扩容等全套解决方案，适用于TB或PB级的海量数据场景。

hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，没十万投入是不会有很好的产出的！

我选择了阿里云的MaxCompute配合DataWorks，使用超级舒服，按量付费，成本极低。

MaxCompute可以理解为开源的Hive，提供SQL/mapreduce/ai算法/python脚本/shell脚本等操作数据，数据以表格的形式展现，以分布式存储，采用定时任务和批处理的处理数据。DataWorks提供了一种工作流的管理你的数据处理任务和调度监控。

当然你也可以选择阿里云hbase等其他产品，我这里主要是离☣线处理，故选择MaxCompute，基本都是图形界面操作，大概写了300行SQL，费用不超过100块钱就解决了数据处理问题。

4、mysql数据库设计与优化，应不应该使用

外键约束可以保证数据的正确性和有效性，防止出现不符合预期数据，从数据的角度来说是必要的。为了保证约束，数据库会在更新数据时，对相应表的数据进行检查，这就带来了很大的性能开销。

目前的互联网应用实践来说，一般在开发测试环境使用外键，而生产环境则不使用外键。目的是在开发测试阶段通过数据库的外键机制来验证程序的正确性，而在生产环境则不使用外键，来提升数据库的性能。

非空约束推荐使用，以mysql来说，是有好处的。

如果一个字段它的数据要求每一行数据都是唯一，并且会频繁用于查询，那♞么推荐增加唯一索引