![](https://img-blog.csdnimg.cn/20201014180756919.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
mysql
文章平均质量分 85
深山猿
不断进化的深山猿
展开
-
Server层和存储引擎层
我们先来写一条最普通的SQL查询语句:mysql> select * from T where ID=10;没问题,这个语句只要我们稍微有点数据库基础应该都可以看懂,那就是从表T中找到ID=10的数据,并且输出该行所有字段。那么问题来了,这个语句在MySQL内部是如何执行的?下边通过介绍MySQL的基本逻辑架构图来详细阐述。MySQL基本逻辑架构图: 从上图可以看出,MySQL内部逻辑架构包括Server层以...转载 2021-11-10 15:15:15 · 3270 阅读 · 0 评论 -
重复数据产生原因:分布式锁超时与主从延迟
原本的逻辑如下:针对一个题目,学生只可以插入或更新记录,插入的前提是答题结果不存在,所以按照正常流程应该是一个学生针对一个题目肯定只有一条记录的。但是发现,偶尔会产生一个学生针对同一个题目有多条记录。原因1:可能表执行了alter等语句,阻塞了insert,然后分布式锁超时自动释放,此时用户再次点击保存就会执行插入操作;原因2:没有alter阻塞insert,insert是正常执行,执行完毕后释放分布式锁;由于主从延迟,select的是从库,如果两次保存操作非常近,时间间隔小于了数据库的主.原创 2021-08-26 15:24:10 · 2008 阅读 · 0 评论 -
mysql和redis拆分实例与过程
1 redis当前实例1.1目前配置与使用状态现象:redis目前上课等高峰期cpu使用率达到60%-80%,所以单实例目前已经不能满足要求,需要拆分成两个实例,拆分原则和结论与下面mysql类似。1.2 redis从4到5的主要优化:1)内存优化:主动碎片整理 3.0只能定期重启 4.0主动或手动个清理 5.0增强版(更快速、低延迟)主动碎片整理HyperLogLog算法得到改进,优化了计数统计时的内存使用效率。INFO命令返回信息更加详实。2)RDB支持存储LFU、LR..原创 2020-12-16 17:54:44 · 393 阅读 · 0 评论 -
mysql慢sql导致SQLException: com/mysql/cj/CancelQueryTaskImpl$1$1,定时任务执行失败
场景:定时任务汇总数据,即将多个表的数据汇总到一个表(数据量在千万级别)中,最近几天老是发现定时任务执行失败,报的异常摘抄如下(详细异常见文末):2020-12-06 21:03:29.255[ERROR][ XNIO-2 task-18] c.t.c.p.w.e.GlobalExceptionHandler : [Handle_Exception]org.springframework.dao.TransientDataAccessResourceException:### E...原创 2020-12-10 10:56:06 · 342 阅读 · 0 评论 -
Mysql规范
基础规约1.必须使用InnoDB存储引擎说明:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。2.必须使用utf8mb4字符集说明:真正的utf8,万国码,无需转码,无乱码风险,节省空间现在绝大部分表使用utf8mb4字符集,course_mapping表使用的是utf8表3.禁止使用存储过程、视图、触发器、Event说明:高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到原创 2020-08-17 19:30:05 · 351 阅读 · 0 评论 -
mysql索引分享
查询,是数据库所提供的一个重要功能,快速获取到目标数据很重要。sql查询很慢怎么处理?建索引!!!!那索引是如何加快查询?从数据结构的角度来看,大量数据中,快速查询数据的方式有哪些?1 )数组下标定位 2)hash实现的散列表 3)树 4)跳表 .....而mysql中主要采用了B+和hash两种方式,其中以树的方式最常用。btree索引的底层数据结构 -----B+树经典B+树示意图如下:一般在数据库系统或文件系统中使用的B+Tree结构都在经典...原创 2020-08-03 09:52:08 · 310 阅读 · 0 评论 -
mysql乐观锁与悲观锁
悲观锁悲观锁(Pessimistic Lock),顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block直到它拿到锁。悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。Java synchronized 就属于悲观锁的一种实现,每次线程要修改数据时都先获得锁,保证同一时刻只有一个线程能操作数据,其他线程则...原创 2020-03-14 18:26:01 · 506 阅读 · 0 评论 -
mysql 主从复制 主从延迟 分库分表 分区
为甚分库分表?先集群:主从,且读写分离。分表:数据量过大 查询慢 锁事物冲突。一般先垂直分,再考虑水平分分库:机器的写压力大,并发量数千级别,读压力大,太多slave 分库分表后:降低磁盘使用率 单表数据量少 并发冲突低主从复制延迟解决:show status查看 Seconds_Behind_Master,可以看到从库复制主库的数据落后了几 ms打开MySQL的并行复制:多个库并...原创 2020-03-11 10:17:23 · 502 阅读 · 0 评论 -
MySQL主从复制的线程描述
复制线程概述:MySQL完成主从复制,需要三个线程的参与:一个在(Master)主节点上,另外两个在(Slave)从节点上1、(Master)Binlog Dump线程:当有从节点连接到主节点时,主节点就创建一个线程将该主节点的bin-log内容发送到主动连接的从节点上。创建的这个线程可以使用下面的命令在主节点的标准输出上以“Binlog Dump”名字显示。SHOW PROCES...转载 2020-02-25 20:21:51 · 573 阅读 · 0 评论 -
几种数据库的timeout含义及配置
不同数据库中socketTimeout作用于配置JDBC使用socket与数据库连接,数据库并不对应用与数据库间的连接超时进行处理。JDBC的socket timeout在数据库被突然停掉或是发生网络错误(由于设备故障等原因)时十分重要。由于TCP/IP的结构原因,socket没有办法探测到网络错误,因此应用也无法主动发现数据库连接断开。如果没有设置socket timeout的话,应用在数据...原创 2019-10-17 17:08:04 · 7064 阅读 · 0 评论 -
mysql联合索引和索引优化的理解
首先介绍联合索引的最左原则,即假设某张表test,有四个字段,id,a,b,c;id是主键,a和b是联合索引,建表和值如下:CREATE TABLE `test` ( `id` int(11) NOT NULL, `a` varchar(32) NOT NULL DEFAULT '', `b` varchar(32) NOT NULL DEFAULT '', `c` varc...原创 2018-11-16 18:45:59 · 8008 阅读 · 3 评论 -
binlog的row\statement\mixed模式与具体内容
主从复制的流程主库接收到客户端的更新请求后,执行内部事务的更新逻辑,同时写 binlog。备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程,专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的:在备库 B 上通过 change master 命令,设置主库 A 的 IP、端口、用户名、密码,以及要从哪个位置开始请求 binlog,这个位置包含文件名和日...原创 2019-01-28 18:14:22 · 4466 阅读 · 1 评论 -
怎么查看mysql数据库的当前状态?
1查看mysql所在服务器的性能主要是看当前机器cpu的使用,磁盘使用,io等状态。这个需要使用linux的一些指令,如top,iostat,不了解这两个指令的可以借鉴https://blog.csdn.net/h2604396739/article/details/905213472 mysql自身状态一、查看MySQL运行情况SHOW STATUS;二、查看INNODB数据库...原创 2019-05-24 18:48:04 · 14556 阅读 · 0 评论 -
sql查询结果太大导致oom解决方案
执行的查询sql结果集太大,导致oom的现象很常见,下面是一些解决方案条数限制的逻辑限制最多查询10万条,设置maxRow为10万+1条,如果实际查询到的rowNum为10万+1,那么说明超过最大限制条数,报错 public static int resultLimit = 100000; statement.setMa...原创 2019-05-27 11:40:42 · 10195 阅读 · 0 评论 -
tomcat假死探索与解决,tomcat connector配置
上一篇博客介绍了解决oom导致的程序假死,这里探索解决程序假死的问题。排除oom首先怀疑还是内存溢出导致的,但是:没有有异常日志没有dump文件生成gc.log,证明jvm堆内存还多着呢,如下:[PSYoungGen: 246097K->8179K(240640K)] 1093342K->869809K(4172800K), 0.0522768 secs] [Times...原创 2019-07-05 18:32:11 · 496 阅读 · 0 评论 -
group by的理解和优化
因为最近接触到数据分析的一些东西,会经常用到group by.1 group by的字段加索引是否有效?explain select sum(lastweekcomratio),sum(thisweekcomratio) fromzjf_channel_comparedappid group by comratiogap ;如果comratiogap 是索引,时间1.6-3,...原创 2019-08-26 18:48:42 · 1107 阅读 · 0 评论 -
理解mysql中的join
1 两表join时要小表驱动大表,为什么?user表10000条数据,class表20条数据select * from user u left join class c on u.userid=c.userid;上面的结果是循环10000次,每次获取user的userid到class中找与user表userid相同的记录,但是如果写成下面的sql,仅仅需要循环20次select * f...原创 2019-08-26 20:10:47 · 624 阅读 · 1 评论 -
数据库连接池的选择和CommunicationsException
背景,本人多个项目最近用到了多个数据源,但是偶尔会出现如下异常:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure1 druid:mysql异常重启导致的CommunicationsException项目一直正常,突然早晨反馈,系统不可用了,看日志内容如下:Caused...原创 2019-08-30 10:17:01 · 5039 阅读 · 0 评论 -
mysql缓存
1. MySQL缓存简介1. MySQL缓存机制说明MySQL缓存机制即缓存sql 文本及缓存结果,用KV形式保存再服务器内存中,如果运行相同的sql,服务器直接从缓存中去获取结果,不需要再去解析、优化、执行sql2. MySQL缓存失效在表的结构或数据发生改变时,查询缓存中的数据不再有效,查询缓存值的相关条目将被清空 INSERT、UPDATE、 DELETE、TR...转载 2019-08-30 15:42:03 · 171 阅读 · 0 评论 -
mysql Innodb_buffer_pool的原理
InnoDB 的 Buffer Pool1.innodb存储数据,都是存放在表空间,表空间实际对应着一个或者几个的实际文件。2.访问数据的时候,innodb只能以页为单位。3.innodb通过Buffer pool 把加载进入内存的页,缓存起来,避免立即释放。Buffer Pool的简介InnoDB的缓冲池缓存什么?有什么用?缓存表数据与索引数据,把磁盘上的数据加载到缓冲池,避免每次...转载 2019-09-02 14:55:13 · 2140 阅读 · 0 评论 -
mysql IO性能参数--binLog和redoLog写日志方式的参数
binlogd的写入方式与参数binlog cache 分配了一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小,就要暂存到磁盘。事务提交的时候,执行器把 binlog cache 里的完整事务写入到文件系统的page catche中,并清空 binlog cache,然后在合适的机会进...原创 2019-01-21 20:03:58 · 371 阅读 · 1 评论 -
短连接风暴/慢查询/QPS突增问题 空事务获取与事务判断sql
短连接风暴问题问题:业务高峰期,生产环境的 MySQL 压力太大,没法正常响应,需要短期内、临时性地提升一些性能方法一:优先断开事务外空闲太久的连接;如果这样还不够,再考虑断开事务内空闲太久的连接。kill id;首先获取事务空闲太久的连接:show processlist,command为sleep的就会处于空闲状态的连接事务内连接和事务外连接区分:select * from inf...原创 2019-01-21 15:46:39 · 1168 阅读 · 0 评论 -
mysql索引的原理和底层结构
索引的底层结构使用的B+树和hash,但是我们一般推荐使用B+树,原因可能很少有人知道。为什么索引的实现是B+树,而不是散列表,二叉树,B-等B+B-相对于二叉树:首先需要明确索引也是需要存储到磁盘的,然后B+-树相对于二叉树,最明显存在两点区别1 二叉树深度较深,而深度越大,就说明指针的移动越多,需要读取的节点越多,这样就会导致磁盘IO是寻道时间多,IO次数增多耗时2 B+-...原创 2018-12-13 17:17:22 · 7554 阅读 · 0 评论 -
mysql的MyISAM和InnoDB对比,底层索引结构
先整体概述下 InnoDB MyISAM 事务 支持 不支持,回滚将造成不完全回滚,不具有原子性 mvcc 支持,辅助事务,可用于全库备份 不支持,全库备份需要使用全局锁 锁 默认行锁,也支持表锁 仅支持表锁 外键 支持 不支持外键 delete操作 一行一行的删除 ...原创 2018-12-13 14:09:54 · 7284 阅读 · 1 评论 -
mysql日志系统和事务的理解
本文主要讲的是日志系统,以及日志系统和事务的关系。事务的特性(acid)Atomic:原子性,指的是事务是一个不可再分割的单位,要么全部成功,要么全部失败Consistency:一致性,在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。Isolation:隔离性,事务之间的操作互不影响,通过锁实现Durability:持久性,事务一旦提交,其结果就是稳定的,即使发生了...原创 2018-12-06 10:15:03 · 6358 阅读 · 1 评论 -
mysql order/group by过程解析,排序与索引,联合索引中的范围查询,大量数据带条件的分页
一条SQL的执行实际上可以分为三步。1.得到数据 利用 where 条件2.处理数据3.返回处理后的数据比如语句: select city,name,age from t where city='杭州' order by name limit 1000,100; 第一步:根据where条件和统计信息生成执行计划,得到数据。第二步:将得到的数据排序。当...原创 2018-12-12 14:47:21 · 10319 阅读 · 2 评论 -
MySQL Replication 二进制日志主从复制
1.1 主从复制基础概念 在了解主从复制之前必须要了解的就是数据库的二进制日志(binlog),主从复制架构大多基于二进制日志进行,二进制日志相关信息参考:http://www.cnblogs.com/clsn/p/8087678.html#_label61.1.1 二进制日志管理说明 二进制日志在哪?如何设置位置和命名? 在my.cnf文件中使用 log-bin =...转载 2018-10-22 14:08:32 · 328 阅读 · 0 评论 -
MySQL索引结构之btree和hash,主键唯一普通索引区分,changeBuffer
MySQL的btree索引和hash索引的区别Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像BTree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。可能很多人又有疑问了,既然 Hash 索引的效率要比 B-Tree 高很多,为什么大家不都用 Hash 索引而还要使用 BTre...原创 2018-09-17 20:28:05 · 12155 阅读 · 0 评论 -
Mysql int(n),char(n),varchar(n),decimal中n的含义
int(n),tinyint(n)n的含义Tinyint(n):此处的n仅仅与显示有关,和底层存储实际占用空间大小无关tinyint(1) 这里的1表示的是 最短显示一个字符。tinyint(2) 这里的2表示的是 最短显示两个字符,但这里光设置m是没有意义,你还需要指定当数据少于长度m时用什么来填充,比如zerofill(表示有0填充)。设置tinyint(2) zerofill 你...原创 2018-09-12 14:55:48 · 2281 阅读 · 0 评论 -
mysql分区和分表
mysql分区和分表?分区1)为什么要分区?如果一张表的数据量太大的话,索引和数据空间会很大,查找数据就会变的很慢。这个时候我们可以利用mysql的分区功能2)分区的具体实现:分为range分区,list,hash,keyList分区实现:mysql> CREATE TABLE IF NOT EXISTS `list_part` ( -> `id`...原创 2018-07-17 21:33:18 · 21274 阅读 · 0 评论 -
Innodb中count的理解,count(*)存储使用缓存或者事务
首先需要声明,下面的内容主要是基于innodb;myIsam中会单独存储count(*)的值,因此会直接返回,效率最高。innodb为什么不单独存储count(*)的值这是因为innodb支持事务和mvcc,同一个时刻,存在多个事务,然后每个事务都有插入或者删除操作,那么这个count(*)的值就没有办法维护了。其实我的观点是innodb完全可以将mvcc用于count(*)的值维护,这样...原创 2018-12-21 19:39:25 · 9492 阅读 · 0 评论 -
查一条记录sql也很慢的原因
#数据准备 CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;delimiter ;;create procedure idata()begindeclare i int;set i=1;while(i<=100000)d...原创 2019-01-15 19:21:50 · 2093 阅读 · 3 评论 -
mysql死锁 DeadLock定位与解决
mysql死锁问题定位与解决java后台报错如下java.util.concurrent.ExecutionException: org.springframework.dao.DeadlockLoserDataAccessException:### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQ...原创 2019-01-24 19:41:33 · 26570 阅读 · 2 评论 -
幻读、间隙锁、行锁、next-key lock、加锁规则、间隙锁导致的死锁、隔离级别设置、for update的理解
假设存在如下表:CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `c` (`c`)) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,1...原创 2019-01-17 10:22:50 · 8530 阅读 · 1 评论 -
mysql 慢查询日志参数设置
慢查询日志是否开启show variables like '%slow_query_log%';#如果结果中包含slow_query_log | OFF ,则说明慢日志已经关闭#开启慢查询日志的方式:set global slow_query_log=1;慢查询sql的设置时间查看慢查询sql的设置时间,默认10s,sql执行时间大于该时间的才是慢sql,才会记录到慢查询...原创 2019-01-10 14:14:28 · 4390 阅读 · 0 评论 -
insert into***** on duplicate key update的使用
问题是这样的:业务上有这样的需求,A、B 两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是 like 表,一个是 friend 表,like 表有 user_id、liker_id 两个字段,我设置为复合唯一索引即 uk_user_id_liker_id。语句执行逻辑是这样的: 以 A 关注 B 为例: 第一步,先查询对方有没有关注自己(B 有没有关注 A)...原创 2018-12-24 15:43:02 · 7879 阅读 · 4 评论 -
大量数据带筛选条件的正确分页方案
场景:clickhouse中两千万条记录,需要用查询条件筛选记录,条件命中的条数从几十到几百万不等,然后分页展示筛选到的记录第一种分页做法:先根据筛选条件获取满足条件记录的id,并且对id在sql中排序(避免大量数据内存中排序,很慢、很耗性能),取到分页对应的id;再根据id查询对应的记录,此时如果id对应的记录在库中很离散,那么会再次全表遍历;问题:获取满足条件的所有id会有一次全表扫描,...原创 2018-12-29 15:27:55 · 12640 阅读 · 0 评论 -
mysql索引创建规则、联合与一般索引、执行计划、索引选择,索引重建与下推
数据库索引怎么建,什么时候用到索引优缺点:为主键外建where子句建立索引可以加速数据库查询,但是索引占用内存,同时update和insert的时候需要同步修改;索引的实现通常使用其变种B+树。建立索引方式:create index 索引名 on 表名(列名);细节问题:)如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命性的结果下降,每次查找一条特定的数据...原创 2018-12-12 14:00:30 · 7453 阅读 · 0 评论 -
Spring中Transactional注解的使用详解和实例
Spring中Transactional注解的使用: @Transactional(propagation=Propagation.REQUIRED,rollbackFor = Exception.class) 已经有事务则直接加入,没有事务则新建事务;遇到异常则回滚1)定义事物传播行为介绍: @Transactional(propagation=Propagation.REQ...原创 2018-12-12 11:13:54 · 12428 阅读 · 1 评论 -
mysql事务、隔离级别、长事务的处理、mvcc
事物的概念:4个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。 1) 原子性(atomicity)。一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。redoLog实现 2)一致性(consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。undoLog实现 3)隔离性(iso...原创 2018-12-12 11:09:20 · 7177 阅读 · 1 评论