![](https://img-blog.csdnimg.cn/2019091813595558.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
MySQL
文章平均质量分 69
mysql
拾牙慧者
会点c++、python;
展开
-
mysqld进程 ut_delay 占用率过高
采用性能分析工具perf top -pmysqld进程在测试mysql数据库时,用perf top如果看到热点函数是ut_delay或者_raw_spin_lock的话,说明锁争用比较严重。ut_delay这是innodb的一个自旋琐。也就是说,在这里由于锁等待,innodb不停地在作cpu空转.由于cpu速度远高于io速度,线程之间需要等待。在压力环境下,多个cpu就会互相等待资源。使用自旋琐的本意是尽量减少调用mutesx锁的时间来提升性能。若自旋等待超过仍未获取资源,则使用mutex。ut原创 2022-03-14 20:35:56 · 1706 阅读 · 0 评论 -
关于mysql的binlog写满磁盘而导致mysql无法连接的问题。
问题描述与追踪首先是mysql连不上:[root@VM-90-225-centos ~]# mysql -uroot -pEnter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)然后查看mysql的状态:[root@VM-90-225-centos ~]# service mysqld status ERROR原创 2022-01-24 11:15:56 · 2250 阅读 · 0 评论 -
mysql启动时报错:Starting MySQL ERROR The server quit without updating PID file
https://blog.csdn.net/eagle89/article/details/79813405转载 2022-01-24 11:12:15 · 627 阅读 · 0 评论 -
Fatal error: Please read “Security“ section of the manual to find out how to run mysqld as root
.通过在命令后面加上–user=root 进行强制使用root账号启动。cd /etc/init.dmysqld --user=root参考:https://blog.csdn.net/huo_wa/article/details/117550307?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-2.pc_relevant_payc转载 2022-01-23 14:05:27 · 500 阅读 · 0 评论 -
Mysql的undo、redo、bin log分析
undo log :记录数据被修改之前的样子redo log:记录数据被修改之后的样子bin log:记录整个操作。关于undo log关于undo log:在执行一条涉及数据变更的sql时,在数据加载到buffer Pool时,同时会在undo log写入原来的记录。关于redolog关于redo logmysql对于数据操作是在内存(buffer Pool)中的。但是断电时,内存数据会全部丢失。redo log 记录数据修改之后的值,不管事务是否提交都会记录下来。当redo log原创 2021-08-31 22:08:39 · 388 阅读 · 0 评论 -
半连接&反连接
半连接&反连接1. 半连接半连接返回左表中与右表至少匹配一次的数据行,通常体现为 EXISTS 或者 IN 子查询。左表驱动右表。只返回左表的数据,右表作为筛选条件。可以用 EXISTS、 IN 或者 =ANY举例:表t1和表t2做半连接,t1是驱动表,t2是被驱动表,半连接条件为t1.x=t2.y。这里"t1.x semi= t2.y"的含义是只要在表t2中找到一条记录满足t1.x=t2.y,则马上停止搜索表t2,并直接返回表T1中满足条件t1.x=t2.y的记录。表t2中满足半连接条件转载 2021-06-02 16:21:47 · 2244 阅读 · 0 评论 -
《MySQL8.0.22:Lock(锁)知识总结以及源码分析》
事务加锁方式:两阶段锁:整个事务分为两个阶段,前一个阶段加锁,后一个阶段为解锁。在加锁阶段,事务只能加锁,也可以操作数据,但是不能解锁,直到事务释放第一个锁,就进入了解锁阶段,此阶段事务只能解锁,也可以操作数据,不能再加锁。两阶段协议使得事务具有比较高的并发度,因为解锁不必发生在事务结尾。不过它没有解决死锁问题,因为它在加锁阶段没有顺序要求,如果两个事务分别申请了A,B锁,接着又申请对方的锁,此时进入死锁状态。Innodb事务隔离在MVCC并发控制中,读操作可以分为两类:快照读和当前读。快照读原创 2021-05-16 16:43:14 · 1245 阅读 · 3 评论 -
《DBNotes:Buffer Pool刷脏页细节以及改进》
本笔记知识沿用之前DBNotes: Buffer Pool对于缓冲页的链表式管理的部分知识目录获取一个空闲页的源码逻辑Page_Cleaner_ThreadLRU_Manager_ThreadHazard Pointer作为驱逐算法改进参考获取一个空闲页的源码逻辑任何一个读写请求都需要从Buffer pool来获取所需页面。如果需要的页面已经存在于Buffer pool,那么直接利用当前页面进行操作就行。但是如果所需页面不在Buffer pool,比如UPDATE操作,那么就需要从Buffer p原创 2021-05-06 18:51:30 · 511 阅读 · 0 评论 -
《DBNotes:Join算法的前世今生》
目录NestLoopJoin算法Simple Nested-Loop JoinIndex Nested-Loop JoinBlock Nested-Loop JoinBatched Key AccessHash Join算法In-Memory Join(CHJ)On-Disk Hash Join参考链接在8.0.18之前,MySQL只支持NestLoopJoin算法,最简单的就是Simple NestLoop Join,MySQL针对这个算法做了若干优化,实现了Block NestLoop Join,.原创 2021-05-04 14:10:58 · 310 阅读 · 1 评论 -
《DBNotes: Buffer Pool对于缓冲页的链表式管理》
目录Buffer Pool回顾Buffer Pool内部组成freelistflushlistLRU链表管理以及改进Buffer Pool回顾我们知道针对数据库的增删改删操作都是在Buffer Pool中完成的,一条sql的执行步骤可以认为是这样的:1、innodb存储引擎首先在缓冲池中查询有没有对应的数据,有就直接返回2、如果不存在,则去磁盘进行加载,并加入缓冲池3、同时该记录会被加上独占锁,防止多人修改,出现数据不一致而且我们知道,可以通过设置my.cnf配置中的innodb_buf.原创 2021-04-29 12:17:20 · 233 阅读 · 0 评论 -
《DBNotes:single_table访问方法、MRR多范围读取优化、索引合并》
目录single_table访问方法constrefref_or_nullrangeindexallMRR多范围读取优化索引合并intersectionunionsort-unionsingle_table访问方法const在主键列或者unique二级索引与一个常数进行等值比较时才有效。如果主键或者unique二级索引的索引列由多个列构成,则只有在索引列中的每个列都与常数进行等值比较时,才是const访问ref搜索条件为二级索引(非unique)与常数进行等值比较,形成的扫描区间为单点扫描区.原创 2021-04-28 19:26:45 · 183 阅读 · 0 评论 -
《MySQL——分区表小记》
分区表的组织形式以年份为分割方式,对表进行分割:CREATE TABLE `t` ( `ftime` datetime NOT NULL, `c` int(11) DEFAULT NULL, KEY (`ftime`)) ENGINE=InnoDB DEFAULT CHARSET=latin1PARTITION BY RANGE (YEAR(ftime))(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB, PARTIT原创 2021-04-09 13:33:05 · 182 阅读 · 0 评论 -
《MySQL——关于grant赋权以及flush privileges》
先上总结图:对于赋予权限或者收回权限还是创建用户,都会涉及两个操作:1、磁盘,mysql.user表,用户行所有表示权限的字段的值的修改2、内存,acl_users找到用户对应的对象,将access值修改grant授权创建用户 ’ua’@’%’,密码是 pacreate user 'ua'@'%' identified by 'pa';全局权限grant all privileges on *.* to 'ua'@'%' with grant option;grant命令对于全局权限原创 2021-04-09 12:47:09 · 636 阅读 · 0 评论 -
《MySQL——InnoDB与Memory以及临时表》
InooDB与Memory数据组织方式不同:InnoDB引擎把数据放在主键索引上,其他索引上保存的是主键id。为索引组织表Memory引擎把数据单独存放,索引上保存数据位置。为堆组织表典型不同处:1、InnoDB表的数据总是有序存放的,而Memory表数据是按照写入顺序存放、2、当数据文件有空洞时,InnoDB表在插入新数据时,为了保证数据有序性,总是放在右侧。而Memory表找到空位就可以插入3、数据位置发生变化的时候,InnoDB表修改的是主键索引,Memory表需要修改索引值4、I原创 2021-04-08 14:52:33 · 656 阅读 · 0 评论 -
《MySQL——group by使用tips》
1、如果对group by语句结果没有排序要求,在语句后面加order by null2、尽量让group by 过程用上索引,确认方法是explain结果里没有Using temporary 和Using filesort3、如果group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大tmp_table_size参数,来避免用到磁盘临时表4、如果数据量很大,使用SQL_BIG_RESULT提示,来告诉优化器直接使用排序算法得到group by 的结果...原创 2021-04-08 14:08:42 · 149 阅读 · 0 评论 -
《MySQL——临时表》
内存表与临时表区别临时表,一般是人手动创建。 内存表,是mysql自动创建和销毁的。内存表,指的是使用Memory引擎的表,建表语法:create table ... engine = memeory表的数据存在内存里,系统重启后会被清空,但是表的结构还在。临时表,可以使用各种引擎类型。如果使用的是InnoDB或者MyISAM引擎,写数据是写在磁盘上的。当然临时表也可以使用Memory引擎。临时表特性1、一个临时表只能被创建它的session访问,对于其他线程不可见,当此session结束时,会原创 2021-04-08 14:02:02 · 411 阅读 · 0 评论 -
《MySQL——join语句优化tips》
目录要不要用joinJoin驱动表选择Multi-Range Read优化Batched Key Access (BKA)对NLJ进行优化BNL算法性能问题BNL转BKA要不要用join1、如果使用的是Index Nested-Loop Join算法,即可以用上被驱动表的索引,可以用2、如果使用的是Block Nested-Loop Join算法。扫描行数过多,尤其是大表join会导致扫描多次被驱动表,会占用大量系统资源,这种Join尽量不要用Join驱动表选择1、如果是Index Neste.原创 2021-04-07 22:16:12 · 247 阅读 · 0 评论 -
《MySQL——Innodb改进LRU算法》
Innodb改进LRU.算法,实质上将内存链表分成两段。靠近头部的young和靠近末尾的old,取5/12段为分界。 新数据在一定时间内只能在old段的头部,当在old段保持了一定的时间后被再次访问才能升级到young。实质上是分了两段lru,这样做的好处是防止大表扫描时,内存数据被全量替换,导致内存命中率急剧下降而造成性能雪崩。...原创 2021-04-07 18:21:22 · 602 阅读 · 0 评论 -
《MySQL——恢复数据-误删行、表、库》
目录误删行事前预防误删行数据方法误删表/库延迟复制备库事前预防误删库/表方法传统的架构不能预防误删数据,因为主库的一个drop table命令,会通过binlog传给所有从库和级联从库,进而导致整个集群的实例都会执行这个命令。MySQL相关的误删除数据分类:1、使用delete语句误删数据行2、使用drop table 或者 truncate table 语句误删数据表3、使用drop database语句误删数据库4、使用rm命令误删整个MySQL实例误删行若使用delete语句误删数.原创 2021-04-07 15:42:13 · 1562 阅读 · 0 评论 -
《MySQL——外部检测与内部统计 判断 主库是否出现问题》
目录select1判断查表判断更新判断外部检测弊端内部统计一主一备的双M架构里,主备切换只需要把客户端流量切换到备库。在一主多从的架构里,主备切换要把客户端流量切换到备库,也需要把从库接到新主库上。切换有两种场景:1、主动切换 2、被动切换。被动切换是由于主库出问题了,下面是几种判断主库出问题的方法:select1判断select 1 成功返回,说明这个库的进程孩子啊,但是不能说明主库没问题。如果在执行语句超过了设置的innodb_thread_concurrency,此时select 1.原创 2021-04-07 13:02:39 · 227 阅读 · 2 评论 -
《MySQL tips:并发查询与并发连接区别》
并发连接与并发查询,并不是一个概念。在执行show processlist的结果里,看到了几千个连接,指的是并发连接。而"当前正在执行"的语句,才是并发查询。并发连接数多影响的是内存。并发查询太高对CPU不利。一个机器的CPU核数有限,线程全冲进来,上下文切换的成本就会太高。所以需要设置参数:innodb_thread_concurrency用来限制线程数。当线程数达到该参数,InnoDB就会认为线程数用完了,会阻止其他语句进入引擎执行。需要注意的是,在线程进入锁等待以后,并发线程计数减一,所原创 2021-04-07 12:18:44 · 495 阅读 · 0 评论 -
《MySQL——如何解决一主多从的读写分离的过期读问题》
目录两种架构两种架构特点强制走主库方案Sleep方案判断主备无延迟方案配合semi-sync等主库位点方案GTID方案两种架构基于一主多从的读写分离,如何处理主备延迟导致的读写分离问题。读写分离的主要目标:分摊主库压力。有两种架构:1、客户端主动做负载均衡,把数据库的连接信息放在客户端的连接层。由客户端选择后端数据库进行查询。2、MySQL和客户端之间加上一个中间代理层proxy,客户端只连接proxy,由proxy根据请求类型和上下文决定请求的分发路由两种架构特点1、客户端直连方案.原创 2021-04-06 23:01:28 · 292 阅读 · 0 评论 -
《MySQL——基于位点orGTID的主备切换协议》
一主多从的设置,用于读写分离,主库负责所有的写入和一部分读,其他读请求则由从库分担。一主多从架构下,主库故障后的主备切换问题。相比于一主一备,多了从库指向新主库的过程。基于位点的主备切换同步把节点B设置为节点A‘的从库,执行change master命令:CHANGE MASTER TOMASTER_HOST = $host_nameMASTER_POST = $portMASTER_USER = $user_nameMASTER_PASSWORD = $passwordMASTER_L原创 2021-04-06 19:26:35 · 259 阅读 · 0 评论 -
《MySQL——备库多线程复制策略。》
目录备库并行复制能力MySQL5.6版本 并行复制策略MariaDB 并行复制策略MySQL5.7版本 并行复制策略MySQL5.7.22版本 并行复制策略总结备库并行复制能力主要涉及两个方面的并行度:1、客户端写入主库的能力2、备库上sql_thread执行中转日志relay log1的并行能力比2强。主库上由于InnoDB支持行锁,对业务并行度的支持比较友好。备库上如果用单线程,会导致备库应用日志不够快,造成主备延迟。现在MySQL使用的是多线程复制coordinator 就是原.原创 2021-04-06 13:45:51 · 363 阅读 · 0 评论 -
《MySQL——主备切换流程与主备延迟》
目录主备切换主备延迟的原因可靠性优先策略的主备切换流程可用性优先策略的主备切换流程主备切换主备切换分为主动运维与被动操作。软件升级、主库所在机器按计划下线为主动运维。主库所在机器掉电为被动操作。同步延迟1、主库A执行完一个事务,写入binlog,时刻T12、传给备库B,备库B接受完这个binlog,时刻T2;3、备库B执行完这个事务,时刻T3;同一个事务,在备库执行完成的时间和主库执行完成的时间之差为T3-T1,又称主备延迟。在备库执行show slave status命令会显示se.原创 2021-04-05 20:24:12 · 484 阅读 · 0 评论 -
《MySQL——主备一致性六问六答》
目录备库为什么要设置为只读模式?备库设置为只读,如何与主库保持同步更新?A到B的内部流程如何?binlog内容是什么?`row`格式对于恢复数据有何好处M-M结构的循环复制问题以及解决方案备库为什么要设置为只读模式?有这样几点考虑:1、有时候一些运营类的查询语句会被放到备库上去查,设置为只读可以防止误操作2、防止切换逻辑有bug,比如切换过程中出现双写( 同时写两个库(A、B )),造成主备不一致3、可以用 readonly 状态,来判断节点的角色备库设置为只读,如何与主库保持同步更新?r.原创 2021-04-05 16:15:15 · 207 阅读 · 0 评论 -
《MySQL——redo log 与 binlog 写入机制》
目录binlog写入机制redo log写入机制组提交机制实现大量的TPS理解WAL机制如何提升IO性能瓶颈WAL机制告诉我们:只要redo log与binlog保证持久化到磁盘里,就能确保MySQL异常重启后,数据可以恢复。下面主要记录一下MySQL写入binlog和redo log的流程。binlog写入机制1、事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。2、binlog cache,系统为每个线程分配了一片bi.原创 2021-04-05 14:27:56 · 2314 阅读 · 0 评论 -
《MySQL—— 业务高峰期的性能问题的紧急处理的手段 》
catalog短连接风暴先处理占着连接但是不工作地线程减少连接过程的消耗慢查询性能问题索引没有设计好语句没写好选错索引QPS突增问题短连接风暴正常的短连接:执行很少sql语句就断开,下次需要的时候再重连。MySQL建立连接的过程成本很高,包含:网络连接三次握手、登录权限判断、获得连接的数据读写权限。短连接模型存在风险:一旦数据库处理得慢一些,连接数就会暴涨。有两个方法能够有损地解决短链接风暴问题先处理占着连接但是不工作地线程如果连接数,可以优先断开事务外空闲太久地连接。如果这样还不够,再考.原创 2021-04-04 21:18:39 · 349 阅读 · 1 评论 -
《MySQL——加锁规则(待补全,有些没看懂)》
catalog加锁规则等值查询间隙锁非唯一索引等值锁主键索引范围锁非唯一索引范围锁唯一索引范围锁 bug非唯一索引上存在"等值"的例子limit语句加锁关于死锁总结1、查询过程中访问到的对象才会加锁,而加锁的基本单位是next-key lock(前开后闭);2、等值查询上MySQL的优化:索引上的等值查询,如果是唯一索引,next-key lock会退化为行锁,如果不是唯一索引,需要访问到第一个不满足条件的值,此时next-key lock会退化为间隙锁;3、范围查询:无论是否是唯一索引,范围.原创 2021-04-04 18:48:53 · 728 阅读 · 0 评论 -
《MySQL——幻读与next-key lock与间隙锁带来的死锁》
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,10),(15,15,15),(20,20,20),(25,25,25);该表除了主键id,原创 2021-04-04 13:25:51 · 574 阅读 · 0 评论 -
《MySQL——查询长时间不返回的三种原因与查询慢的原因》
这里是引用构造一张表,表有两个字段id和c,再里面插入了10万行记录create table 't' ( 'id' int(11) not null, 'c' int(11) default null, primary key ('id')) engine = InnoDB;delimiter ;;create procedure idata()begin declare i int; set i = 1; while( i <= 100000) .原创 2021-04-03 21:53:36 · 1779 阅读 · 0 评论 -
《MySQL tips:隐式类型转换与隐式字符编码转换对查询效率的影响》
维护一个交易系统,交易记录表tradelog包含交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段。create table 'tradelog' ( 'id' int(11) not null, 'tradeid' varchar(32) default null, 'operator' int(11) default null, 't_modified' datetime default null, primary key ('id'),原创 2021-04-03 20:38:30 · 354 阅读 · 0 评论 -
《MySQL tips:查询时,尽量不要对字段进行操作》
维护一个交易系统,交易记录表tradelog包含交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段。建表语句如下:create table 'tradelog' ( 'id' int(11) not null, 'tradeid' varchar(32) default null, 'operator' int(11) default null, 't_modified' datetime default null, primary key (原创 2021-04-03 19:05:03 · 189 阅读 · 0 评论 -
《MySQL——使用联合索引、覆盖索引,避免临时表的排序操作》
联合索引避免临时表排序在上一篇笔记(MySQL——order by逻辑(全字段排序与rowid排序))中,讲到查询语句查询多个字段的时候使用order by语句实现返回值是有序的,而order by是使用到了临时表的,会带来时间和空间损失。其实使用联合索引,就可以避免临时表的排序操作。只要保证city这个索引上取出来的行天然就是按照name递增排序的话,就可以不用再排序了。alter table t add index city_user(city,name);在这个索引里面,通过树搜索的方式定原创 2021-04-03 14:01:50 · 830 阅读 · 0 评论 -
《MySQL——order by逻辑(全字段排序与rowid排序)》
create table 't' ( 'id' int(11) not null, 'city' vachar(16) not null, 'name' vachar(16) not null, 'age' vachar(16) not null, 'addr' varchar(128) default null, primary key('id'), key 'city'('city'))engine = InnoDB;select city,name,age from t where原创 2021-04-03 12:54:33 · 1308 阅读 · 0 评论 -
《MySQL——count()逻辑》
count()用法count()语义:该函数为一个聚合函数,对于返回的结果集一行行地判断,如果count函数地参数不是NULL,累计值就加1,否则不加。最后返回累计值。所以count(*),count(主键id)和count(1)都表示返回满足条件地结果集地总行数;而count(字段)则表示返回满足条件地数据行里面,参数“字段”不为NULL的总个数。count(主键id):InnoDB引擎会遍历整张表,把每一行的id值都取出来,返回给server层。sever层拿到id后,判断id是不可能为空的原创 2021-04-02 18:52:52 · 746 阅读 · 0 评论 -
《MySQL——给长字符串加索引》
对于长字符串,可用如下方式建立索引:(1)前缀索引(2)字符串倒叙+前缀索引(3)添加hash字段+并在hash字段上加索引(4)字段拆分(一个字段可拆分为两个以上)假设现在表User 中存在email字段,为长字符串。如果email字段上没有索引,那么这个语句只能做全表扫描select f1,f2 from User where email = '1540984562@qq.com';在email字段上创建索引–创建的index1索引包含整个字符串alter table User .原创 2021-04-01 20:08:59 · 2527 阅读 · 1 评论 -
《MySQL——选错索引,该如何做》
如果不断地删除历史数据和新增数据,MySQL有时会选错索引。选择索引是优化器的工作,优化器优化时会考虑的因素:扫描行数、是否需要排序、是否使用临时表MySQL通过统计索引上的基数,作为索引的区分度。统计方法时采样统计,InnoDB默认选择N个数据页,统计这些页面上的不同值,得到一个平均值然后乘以这个索引的页面数,就得到了这个索引的基数。举例:取n = 3个数据页统计,假设共有10个索引数据页page1:10个不同值page2: 20个不同值page3: 15个不同值索引基数:(10+20原创 2021-04-01 18:58:30 · 202 阅读 · 0 评论 -
<MySQL>何时使用普通索引,何时使用唯一索引
如果能够保证业务代码不会写入重复数据,就可以继续往下看。 如果业务不能保证,那么必须创建唯一索引。关于查询能力普通索引和唯一索引在查询能力上是没有很大差别的。如:select id from T where k=51、普通索引查找到满足条件的第一个记录(5,500)后需要查找下一个记录,直到碰到第一个不满足k=5条件的记录。2、对于唯一索引,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止搜索。InnoDB的数据按照数据页来读写,每一个数据页大小默认为16KB.对于普通索引来说.原创 2021-04-01 14:40:08 · 489 阅读 · 0 评论 -
《MySQL——锁》
全局锁是什么?全局锁有什么用?全局锁怎么用?全局锁主要用在逻辑备份过程中,对于InnoDB 引擎的库,使用–single-transaction;MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL),让整个库处于只读状态。表锁是什么?表锁有什么用?表锁怎么用?表锁一般是在数据库引擎不支持行锁的时候才会被用到的.表锁的语法是 lock tables … read/write;加上读锁,不会限制别的线程读,但会限制别的线程写。加上写锁原创 2021-04-01 12:19:49 · 174 阅读 · 0 评论