只是甲的博客

mysql 处理json数据

文章目录一.环境准备二.数据迁移开始2.1 mysqldump导出2.2 select ... into outfile导出2.3 mysql 导入2.4 load data导入2.5 总结参考:一.环境准备一直想测试下mysqldump与load data的性能比较，今天抽出部分时间，在测试环境进行测试。如下所示，我有一个测试表fact_sale，有7亿多行数据，数值类型、字符类型、时间类型的字段均有。下面我们测试将这个表的数据分别通过mysqldump与load data进行导出，然后在目标环境

文章目录一. 问题描述二.解决方案参考:一. 问题描述前两天在帮朋友在CentOS 8.5 安装了最新的MySQL 8.0.27，安装方式为编译安装。才刚上线2天，就发现CPU长期负载100%，而且不管是高峰期还是低峰期，都是100%。通过FinalShell查看:通过top命令查看:输入1 查看每一个cpu的使用率二.解决方案top 然后按b，排序，发现最耗CPU的确实是mysql排名第一的是mysql用户下的xri命令，这个非常可疑。排名第二的是mysql用户下的mysqld命

文章目录一. 需求二. 解决方案一. 需求今天网上看到一个问题，觉得有点意思，然后尝试回答了下。像这样的数据，字段左右相反，但要算作同一条数据去重怎么写sql二. 解决方案测试数据:create table test2(id int,name varchar(20), col_a varchar(20), col_b varchar(20));insert into test2 values (1,'张三','A2','A1');insert into test2 values (2,'

备注:MySQL版本 5.7文章目录一. 问题描述二. 解决方案2.1 修改sql_mode2.2 修改SQL语句一. 问题描述今天帮朋友写了一个存储过程，通过主键id分批更新一个大表，里面的update及delete语句大概有30多个，开启事务，一个批次更新完成后，再提交。突然存储过程运行的时候出现如下报错:2021-12-18T22:24:05.171910-05:00 22592 [ERROR] Event Scheduler: [root@localhost][**.****] Inc

文章目录一. 问题描述二. 解决方案2.1 排查page_cleaner的报错2.2 排查慢SQL2.3 停应用 分批更新大表2.4 测试磁盘性能一. 问题描述今天朋友的数据库出现了一些连接失败查看MySQL的错误日志，发现有如下报错:2021-12-16T09:34:13.091556Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4177ms. The settings might not be optimal. (flus

一. 问题描述朋友的数据库应用最近反馈比较慢。他的需求是首先获取表的一行数据，为了避免并发的冲突，先锁定，然后再更新数据的状态。二. 解决方案锁定语句SELECT id,col1FROM tab_nameWHERE status=0 and isread=0 and id >=501 ORDER BY col2 LIMIT 1 FOR UPDATE;这个语句的需求是更新前把行锁住，避免丢失更新或重复更新。那么问题来了，status和isread两列都没索引，导致这个语句是全表锁

一. 问题描述今天朋友的数据库出现了一些连接失败，如下图:查看MySQL的错误日志，发现有如下报错:2021-12-13T22:31:33.921945Z 163060 [Warning] IP address '*.*.*.*' could not be resolved: Name or service not known2021-12-14T01:22:46.151797Z 163180 [Warning] IP address '*.*.*.*' has been resolved to

文章目录一.问题描述二.解决方案2.1 字符集问题2.2 sql_mode的问题参考:一.问题描述测试数据:drop table test;create table test (pid varchar(200),id varchar(200));insert into test values (null,'中国');insert into test values ('中国','广东');insert into test values ('中国','广西');insert into tes

文章目录一.问题描述二.解决方案参考:一.问题描述今天朋友突然问了一个问题，他想把今天hive里面表字段的备注多行转换为单行显示。例如表t1的col1列，注释为:1-有效0-无效想要的结果是:1-有效,0-无效二.解决方案CHAR(10): 换行符CHAR(13): 回车符MySQL中，CHAR(10)是换行符，通过replace函数进行替换即可。代码:create table t2(id int,name varchar(100));insert into t2 (id

备注:OS :CentOS 7.9DB : MySQL 5.7.31文章目录一.问题描述二.解决方案2.1 增加--lc_messages_dir参数2.2 拷贝errmsg.sys到系统参考:一.问题描述MySQL 初始化的时候遇到这个报错:奇了怪了，MySQL 二进制安装也安装了很多生产实例了，一直没遇到过这个问题，为什么今天安装MySQL出了这么多的问题。二.解决方案2.1 增加–lc_messages_dir参数通过网络搜索，解释说是系统环境变量的问题。需要增加如下两个参数

文章目录一.问题描述二. 解决方案2.1 调整索引2.2 调整参数2.3 重新导入数据2.4 重建索引2.5 恢复mysql配置文件一.问题描述  今天一个做Java开发的哥们向我咨询了一个问题。  他想把一个300多万条数据数据的表迁移到本地测试环境，他处理的方案是 先通过mysqldump命令将该表做一个备份，然后传输到测试环境，最后执行备份文件。  理想很丰满，现实很骨感，300多万的数据不大，备份下来也就2.5GB左右的sql文件，可是在测试环境居然执行了12个小时才执行了一半，无奈只能终止

备注:MySQL 5.7.31文章目录一.问题描述二. 解决方案2.1 从整库备份中找到单表的备份文件2.1.1 直接通过sed命令处理2.1.2 切分后通过grep来查找2.2 恢复到一个新表2.3 还原到源环境参考:一.问题描述之前帮朋友搭建的MySQL数据库，说是有个表的数据被误删除了，现在需要进行恢复。二. 解决方案还好我之前给他安装MySQL数据库的时候，特意做了备份，每天凌晨都会把整库进行备份。2.1 从整库备份中找到单表的备份文件2.1.1 直接通过sed命令处理在网上找到了

一. 7亿数据的测试表MySQL 测试数据准备:-- 建表语句CREATE TABLE fact_sale ( id bigint(8) NOT NULL AUTO_INCREMENT, sale_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, prod_name varchar(50) DEFAULT NULL, sale_nums int(11) DEFAULT NULL,

一. 修改配置文件首先找到mysql的配置文件，如: /etc/my.cnf在[mysqld]字段下新增如下内容，然后保存退出。skip-grant-tables然后重启mysql服务service mysqld restart二. 修改root密码使用空密码登陆mysql mysql -uroot -p -- 不输入密码直接回车 USE mysql; UPDATE user SET Password = password('YourPassword') where user =

文章目录MySQL SQL modes概述一. 设置SQL模式二. 最重要的SQL模式三. 完整的SQL模式列表3.1 ALLOW_INVALID_DATES3.2 ANSI_QUOTES3.3 ERROR_FOR_DIVISION_BY_ZERO3.4 HIGH_NOT_PRECEDENCE3.5 IGNORE_SPACE3.6 NO_AUTO_VALUE_ON_ZERO3.7 NO_BACKSLASH_ESCAPES3.8 NO_DIR_IN_CREATE3.9 NO_ENGINE_SUBSTITUTI

一.问题描述MySQL8.0开始支持分析函数，MySQL8.0之前如果有排名需求，不使用分析函数的情况下会比较麻烦。我们可以通过变量、子查询、表的自连接来实现排名的功能。数据准备:scott建表及录入数据sql脚本二.解决方案此处我选择变量来实现rownum，用标量子查询的方式来实现rank及dese_rank.代码:set @x=0;SELECT e.ename, e.sal, @x:=ifnull(@x,0)+1 as rownum1, (se

备注:测试数据库版本为MySQL 8.0文章目录一.概述二. 常见问题三.web服务器问题3.1 寻找最优并发度四.缓存4.1 应用层以下的缓存4.2 应用层缓存4.3 缓存控制策略五.MySQL的替代品参考:一.概述如果在提高MySQL的性能上花费太多时间，容易使视野局限于MySQL本身，而忽略了用户体验。回过头来看，也许可以意识到，或许MySQL已经足够优化，对于用户看到的响应时间而言，其所占的比重已经非常之小，此时应该关注下其他部分了。这是个很不错的观点，尤其是对DBA而言，这是很值得去做的正确

备注:测试数据库版本为MySQL 8.0文章目录一.优化select语句1.1 where子句优化1.2 IS NULL 优化1.3 ORDER BY优化1.4 group by 优化1.4.1 宽松的索引扫描1.4.2 紧索引扫描1.5 distinct优化1.6 Limit优化1.7 避免全表扫描二.优化DML语句2.1 优化insert语句2.2 优化update语句2.3 优化delete语句参考:一.优化select语句1.1 where子句优化剔除重复的条件 ((a AND b

备注:测试数据库版本为MySQL 8.0文章目录一. 内部锁定方法1.1 行锁1.2 表锁1.3 选择锁定类型二.表锁问题2.1 性能考虑优先考虑InnoDB2.2 锁定性能问题的解决方法三. 并发插入四.元数据锁4.1 元数据锁获取4.2 元数据锁释放五. 外部锁参考:一. 内部锁定方法本节讨论内部锁定;也就是说，在MySQL服务器内部执行锁定，以管理多个会话对表内容的争用。这种类型的锁定是内部的，因为它完全由服务器执行，不涉及其他程序。1.1 行锁MySQL为InnoDB表使用行级锁来支持多个

备注:测试数据库版本为MySQL 8.0文章目录一. 优化InnoDB表的存储布局二.优化InnoDB事务管理三.优化InnoDB只读事务四. 优化InnoDB重做日志五. InnoDB表的批量数据加载六. 优化InnoDB查询七. 优化InnoDB DDL操作八. 优化InnoDB磁盘I/O九. 优化InnoDB配置变量十. 优化InnoDB系统与许多表参考:一. 优化InnoDB表的存储布局一旦您的数据达到稳定的大小，或者一个不断增长的表增加了几十或几百兆字节，请考虑使用OPTIMIZE tabl

备注:测试数据库版本为MySQL 8.0文章目录一. MySQL如何使用内存二.监控MySQL内存使用三.开始large page支持参考:一. MySQL如何使用内存MySQL分配缓冲区和缓存来提高数据库操作的性能。默认配置被设计为允许MySQL服务器在拥有大约512MB内存的虚拟机上启动。您可以通过增加某些缓存和缓冲区相关的系统变量的值来提高MySQL的性能。您还可以修改默认配置，以便在内存有限的系统上运行MySQL。下面的列表描述了MySQL使用内存的一些方式。在适用的情况下，引用了相关的系统

备注:测试数据库版本为MySQL 8.0文章目录一. Nested Loop Join算法1.1 普通的Nested Loop Join算法1.3 Batched Key Access 算法二.Hash Join三.表连接实例3.1 Nest Loop Join3.2 hash join参考:一. Nested Loop Join算法1.1 普通的Nested Loop Join算法将外层表的结果集作为循环的基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件去查询数据，然后合并结果。

备注:测试数据库版本为MySQL 8.0文章目录一.分区表简介二.分区的类型2.1 range分区2.2 list分区2.3 colums分区2.3.1 RANGE COLUMNS分区2.3.2 LIST COLUMNS2.4 哈希分区2.4.1 LINEAR HASH分区2.5 key 分区2.6 子分区2.7 MySQL分区如何处理null值三.分区管理3.1 管理range和list分区3.2 管理hash和key分区3.3 交换分区和子分区3.3.1 与非分区表交换分区3.3.2 未匹配的行3.

备注:测试数据库版本为MySQL 8.0文章目录一. MySQL游标简介二.绑定变量2.1 绑定变量的优化2.2 SQL接口的绑定变量2.3 绑定变量的限制参考:一. MySQL游标简介MySQL在服务器端提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端API中使用。因为MySQL游标中指向的对象都是存储在临时表中而不是实际查询到的数据，所以MySQL游标总是只读的。它可以逐行指向查询结果，然后让程序做进一步的处理。在一个存储过程中，可以有多个游标，也可以在循环中“嵌套”地使用游标。MyS

备注:测试数据库版本为MySQL 8.0一.MySQL InnoDB并行查询介绍MySQL经过多年的发展已然成为最流行的数据库，广泛用于互联网行业，并逐步向各个传统行业渗透。之所以流行，一方面是其优秀的高并发事务处理的能力，另一方面也得益于MySQL丰富的生态。MySQL在处理OLTP场景下的短查询效果很好，但对于复杂大查询则能力有限。最直接一点就是，对于一个SQL语句，MySQL最多只能使用一个CPU核来处理，在这种场景下无法发挥主机CPU多核的能力。MySQL没有停滞不前，一直在发展，新推出的8.0

备注:测试数据库版本为MySQL 8.0文章目录一.控制查询计划评估二. 可切换的优化三. 优化器的Hints3.1 优化器Hints概述3.2 优化器Hints语法3.3 连接顺序优化器Hints3.4 表级别的优化器Hints3.5 索引级别优化器Hints3.6 子查询相关优化器的Hints3.7 语句执行时间优化器Hints3.8 可变设定Hints语法3.9 资源组 Hint 语法3.10 命名查询块的优化器Hints四.索引Hints参考:一.控制查询计划评估查询优化器的任务是找到执行SQ

备注:测试数据库版本为MySQL 8.0文章目录一.使用EXPLAIN优化查询1.1 Explain语法及概述1.1.1 获取表结构信息1.1.2 获取执行计划信息1.1.3 使用EXPLAIN ANALYZE获取信息二.Explain输出格式2.1 EXPLAIN 输出列2.1.1 id (JSON name: select_id)2.1.2 select_type (JSON name: none)2.1.3 table (JSON name: table_name)2.1.4 partitions

文章目录一.InnoDB的统计信息概述二. 配置持久化优化统计参数2.1 为持久优化器统计配置自动统计计算2.2 为单个表配置优化器统计参数2.3 配置InnoDB优化器统计抽样页数2.4 在持久统计计算中包括删除标记的记录2.5 InnoDB持久化统计表2.6 InnoDB Persistent Statistics Tables示例2.7 使用innodb_index_stats表获取索引大小三.配置非持久性优化器统计参数3.1 优化器数据更新3.2 配置采样页面数四.估计分析InnoDB表的复杂性五.

一.需求今天在论坛上看到有人提了一个正则相关的问题，刚好有时间，就给他提供了一个解决方案。需求如下:字符串:‘我爱(xxx)中华(qq)人民(aac啊)共和国’想达到的效果，凡是()中的内容都不需要了，包括()本身，以上字符串希望返回结果：我爱中华人民共和国。二.解决方案2.1 Oracle解决方案首先想到的是regexp_replace进行替换，题目意思已经很明白，是要删除()及()里面的内容。代码:with tmp1 as(select '我爱(xxx)中华(qq)人民(aac啊)共

备注:测试数据库版本为MySQL 8.0文章目录一.索引介绍1.1 索引的类型1.1.1 B-Tree索引1.1.2 B+Tree索引1.2.3 B*Tree索引1.1.4 哈希索引1.1.5 空间数据索引1.1.6 全文索引二.如何创建高性能的索引2.1 独立的列2.2 前缀索引和索引选择性2.3 多列索引2.4 选择合适的索引列顺序2.5 聚簇索引2.6 覆盖索引2.7 使用索引扫描来做排序2.8 压缩（前缀压缩）索引2.9 冗余和重复索引2.10 未使用的索引2.11 索引和锁2.12 索引下推2.

备注:测试数据库版本为MySQL 8.0文章目录一.Schema与数据类型优化概述二.选择优化的数据类型2.1 整数类型2.2 实数类型2.3 字符类型2.4 日期和时间类型2.5 其它类型三.范式和反范式四.计数器表五.加快ALTER TABLE操作的速度5.1 预留列5.2 更改表定义文件5.3 MySQL 8.0 快速加列5.3.1 快速加列支持类型5.3.2 立刻加列的限制5.3.3 立刻加列的实现参考:一.Schema与数据类型优化概述良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要

备注:测试数据库版本为MySQL 8.0文章目录一.MySQL压力测试概述二.压力测试的工具2.1 测试工具-fio2.1.1 FIO安装2.1.2 fio参数说明2.1.3 测试用例2.2 TPCC测试2.2.1 环境安装2.2.2 bug修复2.2.3 开始测试2.3 Sysbench2.3.1 sysbench安装2.3.2 sysbench参数2.3.3 测试CPU2.3.4 测试IO2.3.5 对mysql事务型OLTP的测试参考:一.MySQL压力测试概述为什么压力测试很重要？因为压力测试

备注:测试数据库版本为MySQL 8.0文章目录一.查看CPU信息1.1 查看物理CPU个数1.2 查看每个物理CPU中core的个数(即核数)1.3 查看逻辑CPU的个数1.4 查看CPU信息（型号）1.5 查看CPU的负载二.查看内存及交换空间2.1 /proc/meminfo2.2 free 命令2.3 释放cache三.磁盘信息3.1 fdisk命令3.2 查看磁盘的性能四.网络资源4.1 查看网卡基本信息4.2 查看网卡接口的速度4.3 网卡流量监控工具 iptraf4.4 netstat查看网

备注:测试数据库版本为MySQL 8.0文章目录一.选择操作系统二.选择文件系统三.选择磁盘队列调度策略3.1 Noop算法3.2 Deadline算法3.3 Anticipatory算法4.4 CFQ算法五.线程六.内存交换区参考:一.选择操作系统GNU/Linux如今是高性能MySQL最常用的操作系统，但是MySQL本身可以运行在很多操作系统上。MySQL官网 8.0和 5.7 支持的操作系统(https://www.mysql.com/support/supportedplatforms/da

一.需求描述 今天朋友咨询一个正则的需求，对于连续超过3个数字进行输出,如果有多个连续数字，都需要输出。 例如 123abc1234 输出为 123.1234,中间用’.'进行分隔。二.解决方案2.1 Oracle解决方案看到这个需求，首先想到的是使用Oracle 正则表达式的regexp_replace函数了。多个匹配表达式用()进行关联，例如(.*?)([0-9]{3,})其中 *? 是匹配0次或多次，非贪婪模式。[0-9]{3,} 是匹配连续3个及以上数字。代码:with tmp

一.需求描述近期收到朋友的一个需求，需要在一堆字符串中查找连续3个数字的字符，连续数字可能出现在开头、中间、结尾，而且如果连续数字超过3个，也不符合条件。二.解决方案2.1 Oracle解决方案^ 匹配每一行的开头，单行模式下等价于字符串的开头$ 匹配每一行的结尾，单行模式下等价于字符串的结尾字母可以使用 [a-zA-Z]数字可以用 \d 或者 [0-9]我测试匹配开头使用\d居然未匹配出来，只能使用[0-9]代码:with tmp1 as(select 'abc12' str fr

备注:测试数据库版本为MySQL 8.0文章目录一.硬件优化概述二.CPU2.1 CPU密集型2.2 I/O密集型三.内存四.硬盘4.1 存储简介4.2 RAID4.3 硬盘三大种类4.4 找到有效的内存/磁盘比例五.网络资源参考:一.硬件优化概述MySQL的硬件有:CPU内存硬盘网络资源对于硬件的选择与调优，在系统上线前就需要考虑起来。当然我们都知道:好的CPU，可以让SQL语句解析得更快，进而加快SQL语句的执行速度。大的内存，可以缓存更多的MySQL数据在内存中，进而加快M

文章目录一.问题描述二.解决方案一.问题描述今天在解决一个大的事务回滚的时候，mysql实例宕机了，等重启后，就发现create table出现了问题:mysql> CREATE TABLE `fact_sale_new` ( -> `id` bigint NOT NULL AUTO_INCREMENT, -> `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRE

一.问题描述今天突然碰到一个mysql数据库重启后登陆不上的问题:[16:37:02] [root@hp2 ~]# mysql -uroot -p[16:37:04] Enter password: [16:37:04] ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)二.解决方案因为mysql.sock是mysql启动后才会生成的，所以这个文件其实是不存在的