MySQL
文章平均质量分 77
oldGarlic
看看世界有多大!!!
展开
-
MySQL-41:自增主键用完怎么办
41.1 表定义自增id表定义的自增值达到上限后的逻辑是:再申请下一个 id 时,得到的值保持不变。可以用下面的sql语句验证:create table t( id int unsigned auto_increment primary key) auto_increment=4294967295;insert into t values(null);insert into t values(null);//Duplicate entry '4294967295' for key原创 2021-01-06 21:42:35 · 191 阅读 · 1 评论 -
MySQL-40:表的复制
常见的复制方法有三种: mysqldump 方法,CSV 文件 , 物理拷贝方法 。40.1 mysqldump使用mysqldump命令将数据导出成一组insert语句,也就是sql文件。命令如下:mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/cli原创 2021-01-04 23:42:03 · 95 阅读 · 0 评论 -
MySQL-39:insert语句常见问题
39.1 insert…select 语句建表如下: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`) ) ENGINE=InnoDB; insert into t原创 2021-01-03 22:02:31 · 188 阅读 · 1 评论 -
MySQL-38:自增主键
使用自增主键的目的: 可以让数据插入数据页时尽量保持按递增顺序插入,使得数据页数据更加紧凑,避免页分裂。我们会讨论一下问题:自增主键如何保存?我们先来建个表:create table t3( id int(11) not null auto_increment PRIMARY key, a int(11) not null, b int(11) not null, UNIQUE key a(a))ENGINE = INNODB38.1 自增主键如何保存表的结构定义语句保存在.fm的原创 2021-01-02 01:16:21 · 198 阅读 · 0 评论 -
MySQL基本数据类型
int(11)的解释整型:类型大小tinyint1字节smallint2字节mediumint3字节int/integer4字节bigint8字节整数类型有可选的unsigned属性,表示存储的是无符号数。比如说tinyint表示的数值范围为-128~127,那unsigned tinyint表示的数值范围为0~255。整数类型可以指定显示宽度,我们常见到的int(11)对其能表示的数值范围不会变,当我们设置 zerofill ,它会将0的位原创 2020-12-31 00:04:38 · 109 阅读 · 0 评论 -
MySQL-37:内存临时表
37.1 临时表的组织结构InnoDB引擎: B+树方式的组织结构,前面是索引,节点是索引+数据,节点是有序的。Memory引擎: 索引是hash索引,数组以数组的方式存放,先来先插入到结尾,数组中有空洞,则下次的数据插入会补到该位置,数组中数据是无序,索引也是无序的。从上可见Memory引擎和InnoDB引擎的组织结构是不一样的:Memory引擎采用数据和索引分开存放InnoDB引擎采用索引组织表的形式Memory引擎和InnoDB引擎的不同点:数据和索引的组织方式当数据出现空洞原创 2020-12-30 22:47:56 · 224 阅读 · 0 评论 -
MySQL-36:内部临时表
36.1 MySQL中的两种临时表外部临时表通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这种临时表的命名与非临时表可以同名。内部临时表内部临时表会被MySQL自动创建并用来存储某些操作的中间结果,通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表有两种类型:**①:内存临时表:**所有数据都会存在内存中。**②:磁原创 2020-12-29 21:43:53 · 144 阅读 · 0 评论 -
MySQL-35:临时表
35.1 临时表与内存表的区别:内存表:指的是使用 Memory 引擎的表, 建表语法是 create table … engine=memory。 表结构放到磁盘上,数据放内存中。临时表: 可以使用各种引擎类型 ,如果使用的引擎为MyISAM或者InnoDB, 写数据的时候是写到磁盘上的 ,同时。也是可以使用memory引擎的。临时表特点:创建语法为create temporary table…临时表只对当前session可见,session结束后,会删除临时表临时表可与库内表重名,且语句执原创 2020-12-28 00:25:00 · 101 阅读 · 0 评论 -
MySQL-34:join语句优化
join 语句的两种实现算法分别是 Index Nested-Loop Join和 Block Nested-Loop Join,针对于这两个算法都还有相应的优化方法。在这之前,我们先介绍下MRR优化。34.1Multi-Range Read 优化Multi-Range Read优化 (MRR) :优化的目的是尽量顺序读盘,MySQL认为磁盘顺序读比磁盘随机读的性能更加好。这里我们需要明白**“回表”**的概念,MRR优化主要针对于这个过程。例如下面这个sql语句,假设需要进行回表的情况:sel原创 2020-12-23 23:58:59 · 103 阅读 · 0 评论 -
MySQL-33:join语句原理
现在有表t,r,表t有a,b字段,表r有c,d字段,a字段和c字段有建立索引,表t有100000行数据,表r有6行数据。我们先来普通的join语句,在join语句中,存在驱动表和被驱动表,MySQL会已小表已用来驱动大表,小表就是数据量较小的那个。例如执行如下语句,我们就会看来是用表r驱动表t的EXPLAIN select * from t join r on r.c = t.a;看到的情况如下:表r走全表扫描,表t走索引c,我们就可以想到join的执行流程如下:表r走全表扫描,从表r中取原创 2020-12-22 23:50:02 · 133 阅读 · 0 评论 -
MySQL-32:全表扫描
全表扫描的概念: 数据库服务器用来搜寻表的每一条记录的过程,直到所有符合给定条件的记录返回为止。32.1 全表扫描流程例如,我们执行如下sql,对表t进行全表扫描,这条sql的目的就是将全表的结果发送给客户端。select * from t流程如下:服务端内存中有个net_buffer,大小由参数net_buffer_length控制,默认大小为16K。读取一行,写入net_buffer中,直到写满,调用网络接口发送出去。如果发送成功,则清空net_buffer,重复上述动作。如果发送函原创 2020-12-21 21:43:09 · 1448 阅读 · 0 评论 -
MySQL-31:Kill语句
在MysQL中存在两个kill语句,如下:kill query + 线程id:表示终止这个线程正在执行的语句kill conection + 线程id,conection可以缺省:表示断开这个线程的连接在正常情况,执行kill query会停止请求;执行kill conection 会停止连接,如下图,就是能很顺人意地终止请求。31.1执行kill后,线程做什么现假设一个请求申请并拥有MDL锁,我们将它kill掉,那它是不是应该将MDL锁释放掉?kiil命令的意思并不是立即杀死线程或者请求原创 2020-12-21 21:01:49 · 889 阅读 · 0 评论 -
MySQL-30:误删数据后如何自救
首先,先将误删数据的操作分下类,具体如下:使用 delete 语句误删数据行;使用 drop table 或者 truncate table 语句误删数据表;使用 drop database 语句误删数据库;使用 rm 命令误删整个 MySQL 实例。30.1 delete 语句误删数据行在使用delete语句删除数据行后,我们可以通过Flasgback工具来进行数据恢复。Flashback的原理是修改binlog的内容,拿到原库重放。但是,前提是binlog_format = row和b原创 2020-12-20 23:15:46 · 170 阅读 · 0 评论 -
MySQL-29:如何判断数据库出问题
29.1外部检测1. select 1通过发送sqlselect 1来检测数据库实例是否可用,但是这仅仅只针对于数据库实例是否还存在,如果是如下这种状况(在并发数量上限为3时,通过参数innodb_thread_concurrency可以设置,MySQL默认为0),则无用。2. 查表判断我们通过在数据库中创建一个表,定期去轮询查询它,用简单的查询语句,select * from heath;在binlog写满的情况下,是要进行刷脏页和将binlog同步到数据库上的,这时候更新语句不可用,但是原创 2020-12-20 21:11:19 · 376 阅读 · 0 评论 -
MySQL-28:一主多从的过期读问题
正常的一主多从的状态如下:主库负责主要写请求和部分读,从分担大部分的读请求。但是因为主从可能出现延迟,如果客户端执行完一个事务之后马上发起查询,就可能会出现 “过期读” 的现象,也就是读到一个不是最新的更新数据。我们有如下方案来解决这个问题:强制走主库方案;sleep 方案;判断主备无延迟方案;配合 semi-sync 方案;等主库位点方案;等 GTID 方案。28.1强制走主库强制走主库的方案其实就是将请求做分类,大致将请求分为两类:对于需要最新数据的请求就走主库对于可以使用原创 2020-12-18 00:14:53 · 202 阅读 · 0 评论 -
MySQL-27:一主多从的切换问题
下图是一张正常的数据库一主多从架构示意图: A和A’互为主备,负责所有的写入和部分读取;从库B,C,D主库A负责剩余的读取请求。当主库A出现问题,需要进行主备切换,切换示意图如下: 27.1基于位点的主备切换从库B需要切换主库时,我们需要执行change master命令,具体如下:CHANGE MASTER TO # IP地址MASTER_HOST=$host_name # 端口MASTER_PORT=$port # 用户名MASTER_USER=$user_name # 密原创 2020-12-16 23:07:23 · 358 阅读 · 1 评论 -
MySQL-26:备库并行复制
如果备库执行日志的速度持续低于主库生成日志的速度,那这个延迟就有可能成了小时级别。 而且对于一个压力持续比较高的主库来说,备库很可能永远都追不上主库的节奏。那么备库并行复制能力就是关键了。谈到主备的并行复制能力,我们要关注的是图中黑色的两个箭头。一个箭头代表了客户端写入主库,另一箭头代表的是备库上 sql_thread 执行中转日志(relay log)。在主库上,影响并发度的原因就是各种锁了。由于 InnoDB 引擎支持行锁,除了所有并发事务都在更新同一行(热点行)这种极端场景外,它对业务并发度原创 2020-12-15 22:19:17 · 107 阅读 · 0 评论 -
MySQL-25:高可用性
25、MySQL的高可用性首先,我们需要理解高可用的概念,在百度百科上如此写到, “高可用性”(High Availability)通常来描述一个系统经过专门的设计,从而减少停工时间,而保持其服务的高度可用性 。哇,那就很明显了,就是一直打工,尽量避免休息。我们来看下数据库主备切换流程图:在状态1时,客户端访问节点A,节点B为备库,将节点A的binlog同步到节点B上执行。当需要切换的时候,就切换状态2。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QO6uRD9z-原创 2020-12-14 22:44:40 · 216 阅读 · 0 评论 -
MySQL-24:MySQL主从同步
binlog是归档日志,可以用主从同步, 但它的内容是什么样的呢?为什么备库执行了 binlog 就可以跟主库保持一致了呢?24.MySQL主备的基本原理如图就是主备切换流程:在状态1时,客户端访问节点A,节点B为备库,将节点A的binlog同步到节点B上执行。当需要切换的时候,就切换状态2。备库建议设置为只读模式(readonly),理由有三:根据readonly判断角色查询类的语句可以去备库上查询防止切换过程中出现双写问题readonly对超级用户是无效的,负责主备库之间的同步线程原创 2020-12-14 00:13:12 · 211 阅读 · 0 评论 -
MySQL-23:redo log和binlog的写入机制
23.1 binlong 的写入机制binlog的写入逻辑: 事务执行过程中,先把日志写到binlog cache中,事务提交的时候,再把binlog cache写到binlog文件中,并清空binlog cache。同一个事务的binlog无法进行拆分,必须保证能一次性写入磁盘文件。系统给binlog cache分配了内存,每个线程一个,binlog cache的大小由参数binlog_cache_size控制,如果超过设定参数大小,就暂存到磁盘中。每个线程都有自己的binlog cache,但原创 2020-12-11 23:21:39 · 768 阅读 · 0 评论 -
MySQL-22:短暂提高性能的方法
22.1短链接风暴正常的短连接模式就是连接到数据库后,执行很少的 SQL 语句就断开,下次需要的时候再重连。MySQL建立连接的过程:①TCP连接建立,连接器进行身份验证;②身份验证成功,到权限表中加载数据;两个参数: max_connection 和wait_timeout。1.处理sleep的连接通过show processlist 可以获取程序列表,以及状态。通过information_schema 库的 innodb_trx 表 来查看事务的状态,这个能表现出kill这个连接是否会有损。原创 2020-12-11 00:45:40 · 184 阅读 · 1 评论 -
MySQL-21:next-key lock规则及案例
next-key lock加锁规则:原则一:加锁的基本单位为next-key lock且范围为前开后闭。原则二:查找过程中只有访问到的对象才会加锁,例如走全表扫描的情况,这种在扫描前就会给全表加上next-key lock。优化一:索引上的等值查询,在给唯一索引加锁时,next-key lock会退化为行锁,因为主键是唯一的。优化二:索引上的等值查询, 继续向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。一个bug: 唯一索引上的范围查询会访问到不满足条件的原创 2020-12-09 21:59:02 · 2175 阅读 · 3 评论 -
MySQL-20:幻读如何解决?
先建个表,看下面sql:CREATE TABLE `r` ( `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 r values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);问题:原创 2020-12-08 23:52:46 · 129 阅读 · 0 评论 -
MySQL-19:查询一行数据却很慢的情况
19.1 查询长时间不返回这里先说下基础:元数据锁(MDL)是server层的表级锁,主要用于隔离DML和DDL操作之间的干扰。DDL操作需要MDL写锁,DML操作需要MDL读锁,MDL加锁过程是系统自动控制,无法直接干预,且读读共享,读写互斥,写写互斥,当事务确定需要DML锁时,会在事务开始的时候获取锁,事务结束释放锁。先在有表t,有三个字段id,a,b,如下:我们依次执行如下的操作,先执行Session A操作,再执行Session B# Session A# 语句的意思为获取写锁,限制其他原创 2020-12-08 00:19:39 · 347 阅读 · 0 评论 -
MySQL-18:SQL逻辑相同,性能却差距巨大
18、SQL逻辑相同,性能却差距巨大先来将重点:对索引字段做函数操作会导致优化器放弃走树搜索功能不同类型的字段比较,会先转换为同类型再比较不同字符集的字段比较,会将较小的字符集转型为较大的字符集18.1 字段函数操作现在,有一张,如下图,有100000行数据,同时在a,b字段上建了不同索引。现在,我们要查询a = 1的数据行,以下有两种写法,当结果却不一样EXPLAIN SELECT a,b from t WHERE a+999 = 1000EXPLAIN SELECT原创 2020-12-07 00:42:47 · 149 阅读 · 0 评论 -
MySQL:数据库的一致性读
MySQL 在不同的事务隔离级别下提供两种读模式 一致性读 (非加锁) , 当前读 (加锁读) 。一致性读: 利用MVCC机制读取到某个事务已经提交的数据,其实是从undo里面获取的数据快照 。理解一致性读,我们需要明白如下概念:事务的概念:由一组DML语句组成,要么全部成功,要么全部失败。事务的ACID:Atomicity:事务要么全部成功,要么全部失败,事务在执行过程中发生错误就回滚。Conssistency:事务开始前和结束后的数据库完整性没有改变Isolation:不同事务之间不原创 2020-12-06 21:44:18 · 294 阅读 · 0 评论 -
MySQL-17:order by rand()
场景: 从一张单词表中随机刷选出三个单词建表语句和存储过程如下:mysql> CREATE TABLE `words` ( `id` int(11) NOT NULL AUTO_INCREMENT, `word` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;delimiter ;;create procedure idata()begin declare i int; set i=0;原创 2020-12-05 22:12:40 · 2164 阅读 · 0 评论 -
MySQL-16:order by如何运作
order by:根据提供的字段进行排序;用法:order by column[ASC(默认) | DESC]。limit: 从结果集上再进行刷选;用法:limit offset ,rows。如下语句建表:给city建索引CREATE table a( id int(11) not null, name varchar(16) not null, age VARCHAR(16) not null, city VARCHAR(16) not null, PRIMARY key(i原创 2020-12-05 00:41:51 · 243 阅读 · 0 评论 -
MySQL-15:日志系统
14.1 binlogbinlog的作用:binlog是MySQL serve 层的日志,任何引擎都可以使用。binlog(归档日志)是以二进制形式保存语句的原始逻辑的,如果单纯依靠binlog,数据库是不具有crash-safe能力的,binlog日志仅仅用于归档,用于日后的数据备份使用。binlog有两种记录模式,①statement模式:记录的内容是sql语句;②row模式:记录更新前后行的内容,共两条,所以binlog也叫做逻辑日志。那binlog是以二进制文件的方式保存,那如何查看bin原创 2020-12-03 14:12:41 · 121 阅读 · 0 评论 -
MySQL-14:count()函数
13.1 count(*)函数的实现count(*)在MySQL不同的引擎中存在不同的实现方式:MyISAM引擎:把表的总行数存在磁盘上,能直接返回count(*)的结果InnoDB引擎:需要对数据进行一行一行地累加计数前提都是没有where子句,如果MyISAM也不能这么快返回结果。InnoDB为什么不学学MyISAM: InnoDB由于MVVC特性的存在,一个事务不知道这个数据的当前版本是否对它来说是可见的,需要一个一个去核对。MySQL对count(*)的优化: 在保证逻辑正确的情况下原创 2020-12-01 23:48:04 · 169 阅读 · 0 评论 -
MySQL-13:数据库表回收
12.1表结构一个InnoDB表包含表结构定义和数据。表结构定义: 在MySQL 8.0 版本之前,表结构是存储在以.frm为后缀的文件里,在MySQL 8.0 之后,表结构定义在系统数据表中。表数据: 表数据即可以存在系统共享表空间中,也可以是单独文件。这个行为由innodb_file_per_table参数控制,当参数为on时,表数据存在以.ibd为后缀的文件中;当参数为off时,表数据存在系统共享空间。在MySQL 5.6.6版本之后,参数默认值为on。以下的讨论基于innodb_filr_p原创 2020-11-30 01:34:33 · 199 阅读 · 0 评论 -
MySQL-12:数据库的flush操作
WAL机制: Write-Ahead Logging, 先写日志,再写磁盘。 当有一条记录需要更新的时候,InnoDB引擎会先把记录写到redo log里面,并更新内存,这个时候更新计算完成了。InnoDB引擎会在在系统空闲的时候,将这个操作记录更新到磁盘里。WAL机制的益处: redo log 和 binlog 都是顺序写,磁盘的顺序写比随机写速度要快,可以大幅度降低磁盘的 IOPS 消耗。数据库的flush操作就是将内存中的数据更新到磁盘中并将redo log的checkpoint前推,保持磁盘和内原创 2020-11-29 00:33:54 · 1466 阅读 · 0 评论 -
MySQL-11:怎么给字符串字段加索引
例如字段email,如果没有在email字段上加索引,就会走全表扫描。在email字段上创建索引的方式: mysql> alter table SUser add index index1(email); 或 mysql> alter table SUser add index index2(email(6));第一种: 索引包含整个email字符串;第二种: 索引只包含email的前6个字节,这个是基于MySQL支持前缀索引。l两种方式存储结构上的区别: 第二种原创 2020-11-28 00:05:29 · 222 阅读 · 0 评论 -
MySQL-10:MySQL的索引选择
目的: MySQL在执行一个sql时,我们并没有主动指定使用哪个索引,具体的索引选择工作是由MySQL的优化器做的。忘记的可以去看下MySQL的基础架构。第一步: 创建一个表并往里面插入数据,我们插入的数据数量为100000条 CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`),原创 2020-11-27 00:39:53 · 133 阅读 · 0 评论 -
MySQL-09:普通索引和唯一索引
索引是数据库优化中常用的一种手段,索引有多种种类,有普通索引,唯一索引,主键索引,组合索引,全文索引等,下面主要是来区别普通索引和唯一索引。普通索引的构建: ALTER TABLE `T1` ADD INDEX `k_title` (`title`) USING BTREE ;唯一索引的构建: ALTER TABLE `T2` ADD UNIQUE INDEX `un_index_title` (`title`) USING BTREE ;MySQL的主键不易过长 →追根究底还是非原创 2020-11-25 00:29:04 · 309 阅读 · 0 评论 -
MySQL-08:事务:可重复读和读提交
事务隔离级别中存在可重复读,在事务启动时会生成一个视图read-view,之后事务中的任何需求数据都会来源于该视图,即使其他事务对某些数据进行了修改,那可重复读隔离又是如何实现的呢?如果一个事务在可重复读隔离级别下,其他事务对某行数据进行了修改,而后当前事务又对那行事务进行修改并读取,那读取的数值还是和事务启动时看见的一样吗?现在,我们就是来来探究这两个问题的,来看图: mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL,原创 2020-11-24 00:22:50 · 651 阅读 · 0 评论 -
MySQl-07:行锁
MySQL的行锁是由各个引擎自己实现的,但不是所有引擎都支持,例如MyISAM就不支持行锁,在处理并发性问题的时候就只能使用表锁的方式。InnoDB是支持行锁,这也是它取代MyISAM引擎的原因之一。1.1两阶段锁协议例有如下事务发生,事务A在执行过程中会优先持有id=1和id=2的行锁,等待事务提交的时候才会将持有的行锁释放,事务B才能继续执行,这就是两阶段锁协议。两阶段锁协议: 在事务执行中,会因需请求行锁,请求到锁继续执行,请求不到则阻塞挂起,直到事务提交,再将持有的行锁释放出去。知道上述设原创 2020-11-21 22:25:13 · 105 阅读 · 0 评论 -
MySQL-06:全局锁和表锁
数据库在处理并发性请求的需要,避免出现并发性问题,需要对数据库资源指定合理的访问规则,锁就是用来实现这些访问规则的数据结构。根据加锁的访问,MySQL中的锁分为全局锁,表级锁,行级锁。1.1全局锁全局锁: 一旦加锁,会对整个数据库实例加锁。MySQL提供加锁命令:Flush tables with read lock (FTWRL),解锁命令:unloack tables或者客户端断开与数据库的连接。在全局加锁的情况下,整个库处于只读的状态下,以下命令都会被阻塞:数据库定义语句(DDL),数据库更原创 2020-11-20 16:06:46 · 193 阅读 · 1 评论 -
MySQL-05:数据库索引(二)
先来看个语句,当前sql语句 select * from T where k between 3 and 5 会执行几次树的搜索操作,会扫描多少行?mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, index k(k))engine=InnoDB;insert into T values(100,1),(200,2),(300,3),(500,5),(600,6),(700,7)原创 2020-11-19 22:29:00 · 84 阅读 · 0 评论 -
MySQL-04:索引(一)
1、索引索引的出现其实就是为了提高数据查询的效率,实质上就是将存储的数据按数据结构的方式存储起来。对于数据库的表而言,每一张表可能是多个索引实现。2、索引的简单实现索引常见的实现:哈希表,有序数组和搜索树。**哈希表:**哈希表方式,处理哈希碰撞用的是链表法,主要还是说说优缺点:单个数据查询或者新增数据很快,但是要区间查询数据就要遍历全部数据,所以哈希表适用于等值查询。**有序数组:**在进行等值查询和区间查询的时候效率很好,不过新增数据时就要挪动插入位置后面的数据,数组的特性嘛,适用于静态存储原创 2020-11-19 00:03:42 · 153 阅读 · 0 评论