mysql
文章平均质量分 75
其实系一个须刨
这个作者很懒,什么都没留下…
展开
-
EXPLAIN用法和结果分析
idid相同,执行顺序由上至下 id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 id相同不同,同时存在原创 2021-06-19 17:31:05 · 179 阅读 · 0 评论 -
MySQL的复制原理及流程
MySQL复制:为保证主服务器和从服务器的数据一致性,在向主服务器插入数据后,从服务器会自动将主服务器中修改的数据同步过来。主从复制的原理:主从复制主要有三个线程:binlog线程,I/O线程,SQL线程。binlog线程:负责将主服务器上的数据更改写入到二进制日志(Binary log)中。 I/O线程:负责从主服务器上读取二进制日志(Binary log),并写入从服务器的中继日志(Relay log)中。 SQL线程:负责读取中继日志,解析出主服务器中已经执行的数据更改并在从服务器中重原创 2021-06-19 17:07:55 · 641 阅读 · 0 评论 -
varchar、int与char的区别
char的特点char表示定长字符串,长度是固定的; 如果插入数据的长度小于char的固定长度时,则用空格填充; 因为长度固定,所以存取速度要比varchar快很多,甚至能快50%,但正因为其长度固定,所以会占据多余的空间,是空间换时间的做法; 对于char来说,最多能存放的字符个数为255,和编码无关varchar的特点varchar表示可变长字符串,长度是可变的; 插入的数据是多长,就按照多长来存储; varchar在存取方面与char相反,它存取慢,因为长度不固定,但正因如此,不占原创 2021-06-04 20:51:31 · 4835 阅读 · 0 评论 -
mysql中 in 和 exists 区别
mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要区分环境的。如果查询的两个表大小相当,那么用in和exists差别不大。 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。 not in 和not exists:如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引;而not extsts.原创 2021-06-04 20:50:29 · 100 阅读 · 0 评论 -
说出一些数据库优化方面的经验?
1、有外键约束的话会影响增删改的性能,如果应用程序可以保证数据库的完整性那就去除外键2、Sql语句全部大写,特别是列名大写.因为数据库的机制是这样的.sql语句发送到数据库服务器,数据库首先就会把sql编译成大写再执行,如果一开始就编译成大写就不需要了把Sql编译成大写这个步骤了。3、如果应用程序可以保证数据库的完整性.可以不需要按照三大范式来设计数据库4、其实可以不必要创建很多索引护索引可以加快查询速度,但是索引会消耗磁盘空间5、如果是jdbc的话,使用Preparedstateme...原创 2021-06-04 20:47:24 · 724 阅读 · 1 评论 -
临时表
临时表与内存表的区别:内存表,指的是使用Memory引擎的表,建表语法是create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。 临时表,可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用Memory引擎。临时表的特性看看以下的操作序列:临时表在使用上有以下几个特点:1. 建表语法是create temporary table …。原创 2021-05-07 23:55:57 · 134 阅读 · 0 评论 -
join的使用与优化
先创建两个表,结构一样:下面讲解集中join语句的情况。Index Nested-Loop Joinselect * from t1 straight_join t2 on (t1.a=t2.a);用straight_join让MySQL使用固定的连接方式执行查询,这样优化器只会按照我们指定的方式去join。在这个语句里,t1 是驱动表,t2是被驱动表。先遍历表t1,然后根据从表t1中取出的每行数据中的a值,去表t2中查找满足条件的记录,并且可以用被驱动表上的索引。这种就是“In原创 2021-05-03 22:07:08 · 416 阅读 · 0 评论 -
Mysql的全表扫描策略
全表扫描对server层的影响现在要对一个大的数据库进行全表扫描,实际上是直接扫描表的主键索引。查到的每一行都可以直接放到结果集里面,然后返回给客户端。实际上,服务端不需要保存一个完整的结果集。流程如下:1. 获取一行,写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的,默认是16k。2. 重复获取行,直到net_buffer写满,调用网络接口发出去。3. 如果发送成功,就清空net_buffer,然后继续取下一行,并写入net_buffer。4.原创 2021-05-03 17:24:35 · 1170 阅读 · 0 评论 -
Mysql的kill命令
在MySQL中有两个kill命令:一个是kill query +线程id,表示终止这个线程中正在执行的语句;一个是kill connection +线程id,这里connection可缺省,表示断开这个线程的连接,当然如果这个线程有语句正在执行,也是要先停止正在执行的语句的。kill并不是马上停止的意思,而是告诉执行线程说,这条语句已经不需要继续执行了,可以开始“执行停止的逻辑了”,例如释放锁。当用户执行kill query thread_id_B时,MySQL里处理kill命令的线程做了两件事:原创 2021-05-03 16:24:05 · 9234 阅读 · 2 评论 -
mysql一主多从的主备切换
如图,原本一个一主多从的服务是这样子的主备切换后的结果基于位点的主备切换把节点B设置成节点A’的从库的时候,需要执行一条change master命令,这条命令有以下6个参数:MASTER_HOST、MASTER_PORT、MASTER_USER和MASTER_PASSWORD四个参数,分别代表了主库A’的IP、端口、用户名和密码。 最后两个参数MASTER_LOG_FILE和MASTER_LOG_POS表示,要从主库的master_log_name文件的master_log_..原创 2021-05-02 21:29:44 · 851 阅读 · 0 评论 -
Mysql主备高可用保证
主备延迟与数据同步有关的时间点主要包括以下三个:1. 主库A执行完成一个事务,写入binlog,我们把这个时刻记为T1;2. 之后传给备库B,我们把备库B接收完这个binlog的时刻记为T2;3. 备库B执行完成这个事务,我们把这个时刻记为T3。所谓主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是T3-T1。你可以在备库上执行show slave status命令,它的返回结果里面会显示seconds_behind_master,用于表示当前备库延原创 2021-05-02 20:10:46 · 176 阅读 · 0 评论 -
Mysql如何保证主备一致
先看看mysql主备切换的流程在状态1中,客户端的读写都直接访问节点A,而节点B是A的备库,只是将A的更新都同步过来,到本地执行。这样可以保持节点B和A的数据是相同的。当需要切换的时候,就切成状态2。这时候客户端读写访问的都是节点B,而节点A是B的备库。主备同步过程以上是一个update语句在节点A执行,然后同步到节点B的完整流程图。备库B跟主库A之间维持了一个长连接。主库A内部有一个线程,专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的:1. 在备库B上通..原创 2021-05-02 18:34:13 · 214 阅读 · 0 评论 -
redo log 和binlog的写入机制
binlog的写入机制事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。一个事务的binlog是不能被拆开的,因此不论这个事务多大,也要确保一次性写入。系统给binlog cache分配了一片内存,每个线程一个,参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小,就要暂存到磁盘。事务提交的时候,执行器把binlog cache里的完整事务写入到binlog中原创 2021-05-02 17:35:19 · 849 阅读 · 0 评论 -
Mysql应急处理
短连接过多处理掉那些占着连接但是不工作的线程。在show processlist的结果里,踢掉显示为sleep的线程图中id=4和id=5的两个会话都是Sleep 状态。而要看事务具体状态的话,你可以查information_schema库的innodb_trx表。trx_mysql_thread_id=4,表示id=4的线程还处在事务中。优先断开事务外空闲太久的连接。减少连接过程的消耗使用–skip-grant-tables参数启动。这样,整个MySQL会跳过所有的权限..原创 2021-05-02 16:03:50 · 446 阅读 · 0 评论 -
幻读
现在有一个表,初始化如下:现在有一条语句select * from t where d=5 for update,因为在d上没有索引,所以它需要扫描整个表,那么,其他扫描到的d不是5的行,需要加锁吗?看以下场景:幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。1. 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。2. 上面session B的修改结果,被session A之后的s原创 2021-05-02 01:13:22 · 101 阅读 · 0 评论 -
mysql在这些语句特殊执行逻辑里面无法使用索引
函数计算select count(*) from tradelog where month(t_modified)=7;如果对字段做了函数计算,就用不上索引了。对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。但是,并不是放弃使用这个索引,优化器可以选择遍历主键索引,也可以选择遍历索引t_modified,优化器对比索引大小后发现,索引t_modified更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。隐式类型转换se原创 2021-05-02 00:18:53 · 100 阅读 · 2 评论 -
mysql查询慢示例
select * from t where id=1;select * from t where id=1 lock in share mode差别说lock in share mode还要加锁,返回得竟然更快。原创 2021-05-01 18:30:12 · 55 阅读 · 0 评论 -
随机消息
如何从一个表中,随机选择3个记录。内存临时表select word from words order by rand() limit 3;Extra字段显示Using temporary,表示的是需要使用临时表;Using filesort,表示的是需要执行排序操作。因此这个Extra的意思就是,需要临时表,并且需要在临时表上排序。对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。优化器没有了这一层顾虑,那么它会优先考虑的,就是用于排序的行原创 2021-05-01 17:59:20 · 80 阅读 · 0 评论 -
mysql的order by实现
现有一个表:有一条sql语句:select city,name,age from t where city='杭州' order by name limit 1000 ;为了避免全扫描,我们在city上加上索引。全字段排序用explain命令来看看这个语句的执行情况。“Using filesort”表示的就是需要排序,MySQL会给每个线程分配一块内存用于排序,称为sort_buffer。一个全字段排序的流程如下:1. 初始化sort_buffer,确定放入nam原创 2021-05-01 16:34:45 · 148 阅读 · 0 评论 -
Mysql的计数实现
在不同的mysql引擎中,count(*)的执行逻辑是不一样的:MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高; InnoDB引擎执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。当然如果执行的语句有where条件限制,那么它也是不可能返回这么快的。为什么InnoDB不把数字存起来我们看几个事务的运行状况:这三个事务返回的结果是不一样的,这个和InnoDB的可重复读的事务设计有关系,每一行记录的东西原创 2021-05-01 14:49:11 · 1727 阅读 · 0 评论 -
mysql数据删除策略
首先我们表数据的存放,他可以存在共享空间里面,也可以是单独的文件,这个行为是由参数innodb_file_per_table控制的。它有以下两个值:OFF:表的数据存放在系统共享表空间。ON:每个InnoDB表数据存储在一个以.ibd为后缀的文件中。默认是ON数据删除流程接下来删除流程的讲解时基于innodb_file_per_table是ON来描述的。下面我们看看一个索引结构图现在如果我们要删除R4这个记录,那么就把R4这个记录成删除,之后如果要插入一个ID在300和600之间的记原创 2021-04-30 00:44:09 · 377 阅读 · 0 评论 -
InnoDB刷脏页策略
当内存数据页跟磁盘数据页内容不一致的时候,称这个内存页为“脏页”。下面模拟一个店家记录赊账的过程,本来赊账10块钱,现在变成19块钱。有几种需要flush的场景:Redo log写满,这个时候系统就会停止所有的更新操作,把checkpoint往前推进,redo log流出空间继续写:例如上图的把cp移动到cp’,那么就要对两点之间的脏页都刷到磁盘上。内存不足,这时候就要淘汰一些数据页,如果淘汰的是脏页,那么久要刷盘。 系统空闲时。 Mysql正常关闭。我们看前两种场景对性能的影响原创 2021-04-29 00:14:28 · 370 阅读 · 0 评论 -
给字符串字段加索引
如果我们要给身份证作索引,身份证号码太长了,那么做索引会花费很大的空间。Mysql是支持前缀索引的。比如,这两个在email字段上创建索引的语句:mysql> alter table SUser add index index1(email);mysql> alter table SUser add index index2(email(6));看看以下两个索引结构,分别是接下来看看语句的执行。select id,name,email from SUser whe原创 2021-04-28 00:47:30 · 267 阅读 · 0 评论 -
mysql优化器索引选择
本文会讲述优化器是如何选择索引的,例如有十万行数据,表结构如下:正常来说,如果执行select * from t where a between 10000 and 20000,那么优化器选择的是索引a,如果又选择执行如下两个事务。分别看以下两个个语句选择的是哪个索引select * from t where a between 10000 and 20000; /*Q1*/ select * from t force index(a) where a between 1000原创 2021-04-26 23:35:17 · 568 阅读 · 0 评论 -
唯一索引与普通索引
本文分析一下普通索引和唯一索引的区别和选择,还是以以下表为例。查询如果执行的查询语句是select id from T where k=5;那么普通索引:查到满足条件的(5,500)后,查找下一条记录,一直到碰到第一个满足k不等于5的记录。 唯一索引:找到第一条记录后,停止检索。这两个查询带来的差距实际上是很小的。InnoDB是按照数据页为单位读写的,那么一般你要查询下一个的话,开销不大。当然,如果你刚好查到的k=5是数据页的最后一条,就要复杂一点了,但是概率很低。总体来说,在查询上,二原创 2021-04-26 00:59:16 · 190 阅读 · 0 评论 -
mysql可重复读的实现
以下面一个表举例A,B,C三个事务,执行的顺序如下,这默认autocommit = 1:这里出现了一个语句start transaction with consistent snapshot,其实begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作InnoDB表的语句,事务才真正启动。如果要马上启动一个事务,可以使用start transaction with consistent snapshot 这个命令。 先说明执行的结果...原创 2021-04-23 21:57:45 · 298 阅读 · 0 评论 -
数据库锁
根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。全局锁全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。FTWRL一般是要备份数据库的时候,需要用的。当然可以采用可重复读的机制,在可重复读隔离级别下开原创 2021-04-21 23:50:00 · 72 阅读 · 0 评论 -
mysql索引
在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。B+树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数。假设,我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。Index语句表示在k上建立索引。如表的具体记录为:那么他就能建立两颗B+树根据叶子节点的内容,索引类型分为主键索引和非主键索引。主键索引的叶子节点存..原创 2021-04-20 21:29:00 · 98 阅读 · 0 评论 -
SQL的事务隔离
SQL标准的事务隔离级别包括:读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable )。读未提交是指,一个事务还没提交时,它做的变更就能被别的事务看到。 读提交是指,一个事务提交之后,它做的变更才会被其他事务看到。 可重复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。 串行化,顾名思义是对于同一行原创 2021-04-20 17:06:13 · 348 阅读 · 1 评论 -
一条mysql语句是如何执行的
MYSQL可以分成server层和存储引擎层两部分,server层包括连接器、查询缓存、分析器、优化器、执行器,他包含了大多数的核心服务,还有一些内置的函数,如时间,算数,加密等。储存引擎层负责数据的存储和提取,他支持InnoDB、MyISAM、Memeory等多个储存引擎,现在最常用的是InnoDB,当前是Mysql的默认的引擎。下面介绍一下各个组件的作用。连接器 用来跟客户端进行连接的,他建立连接、权限认证、保持心跳等。连接是有最长时间限制的,wait_ti...原创 2021-04-20 10:58:57 · 140 阅读 · 0 评论