![](https://img-blog.csdnimg.cn/20201014180756930.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
MySQL挑战面试官之路
文章平均质量分 71
skye_fly
这个作者很懒,什么都没留下…
展开
-
MySQL的一些相关问题,B+树高度,分库分表,以金字塔形调优
到这篇为止,丁奇大佬的专栏《MySQL实战45讲》已经看完了,再次感谢前人的经验总结。之后会在这篇文章持续更新自己在实际中碰到,学到的MySQL其他相关知识。1.join操作驱动表的选择使用join时MySQL优化器会自动选择使用那张表作为驱动表;使用left join时如果没有where判断那么左侧的就是驱动表,如果存在where判断那么MySQL经过优化器分析会选择最合适的表作为驱动表(不一定时用户指定的左侧的表)。2.group by与distinct执行效率的问题group by默认原创 2021-08-13 14:36:01 · 231 阅读 · 0 评论 -
MySQL的自增主键
查看自增主键值:show create table指令中查到的AUTO_INCREMENT值就是下一次要插入的数据行的主键值。自增主键的持久化MySQL8.0之后会将自增主键进行持久化(写入redo log),所以数据库重启后,可以接着表中当前数据行后继续插入自增主键。自增主键的计算方法当用户没有指定插入数据行的主键id时,系统默认用AUTO_INCREMENT的值。当用户指定了插入数据行的主键id时,就不用MySQL默认计算得到的AUTO_INCREMENT值,(插入数据后系统会重新计算下一个原创 2021-08-12 11:44:55 · 1277 阅读 · 0 评论 -
MySQL的InnoDB引擎与Memory引擎
区别Memory引擎中使用的是内存表;InnoDB中表存放在磁盘中。A) InnoDB使用“索引组织表”:是将数据存放在主键索引上的(数据位置在磁盘中改变,只需要修改主键索引),其他索引指向主键id;B) Memory使用的是“堆组织表”:是将数据单独存放在一块内存中,每一个索引都指向数据 在内存中的位置(数据位置在内存中改变后每一个索引都要修改)。C) 所以查询数据时,InnoDB需要走一次或者两次索引,Memery统一走一次索引,再根据数据位置去内存中找数据。InnoDB表支持变长数据(va原创 2021-08-12 11:25:07 · 442 阅读 · 0 评论 -
MySQL的临时表
1. 临时表的特点临时表为当前线程可见,对其他线程不可见;临时表可以与普通表重名(命名时底层会加上线程id),MySQL会优先使用临时表;show table命令不显示临时表;线程结束后临时表会自动被清理2. 临时表的应用—复杂查询操作比如分库分表时进行跨库跨表查询,每个分库分表会先将各自的查询结果放到临时表中,然后再对临时表中的数据进行筛选。3. 临时表和主备复制当bin log的格式为row时,bin log中可以不记录临时表相关操作。因为bin log中记录了每次操作的行数据的最终原创 2021-08-11 11:19:02 · 1317 阅读 · 0 评论 -
MySQL的join操作
先放上三点结论:如果可以使用被驱动表的索引,就使用join(MySQL当前不支持hash索引);如果被驱动表的索引无法使用,MySQL就只能使用Block Nested-Loop Join算法,就不建议使用join(如果一定要使用join的话可以建立一个临时表复制原本的被驱动表,然后在临时表上建立该字段的索引,也就是BLA算法);使用join时,用小表作为驱动表(MySQL优化器会自动选择驱动表与被驱动表),因为被驱动表可以走索引,这样可以少扫描几行数据。接下来介绍以下概念:1. 两张表的jo原创 2021-08-10 21:42:49 · 242 阅读 · 0 评论 -
MySQL如何将查询结果发送给客户端
MySQL的边查边发机制,可以结合以下文字和图片阅读:当要执行一个大查询时,MySQL会将查询结果分批次发送给客户端,并不是在服务端查到所有数据后一次性发送给客户端;Server层:MySQL通过将查询到的结果依次存入net_buffer中,按页发送给客户端,并且在net_buffer发送数据给客户端的过程中,MySQL继续执行查询操作(边查边发),但是只有等客户端完全接受了net_buffer中的数据后,net_buffer才会清空,MySQL才能将最近查到的数据写入net_buffer中。所以原创 2021-08-10 10:44:45 · 960 阅读 · 0 评论 -
MySQL的一主多从机制的问题
一主多从导致主备切换的问题:一主多从进行主备切换时,其他从库要将主库切换到新的主库(如图1和图2所示),此时需要在新主库上查找当前备份的位点(从bin log的哪个位置开始继续执行事务)一般为了保证数据不丢失,都是往前找一段距离,这样就会出现重复执行同几个事务的情况,MySQL对其进行了优化,采用GTID会自动过滤已经执行过备份的事务(如以下图三所示)。一主多从导致的读写分离的问题数据库的负载均衡为了缓解主库的读写压力,客户端会在主库与备库之间选择一个库进行数据的读取(有两种机制)一种机制原创 2021-08-08 12:25:59 · 542 阅读 · 0 评论 -
MySQL的高可靠和高可用机制
概念一:主备延迟(seconds behind master)1. 主备延迟的概念时间点T1:主库执行完事务写入bin log;时间点T2:备库从主库的bin log中读取该事务的日志记录到自己的bin log中(速度很快);时间点T3:备库读取该事务的bin log日志进行归档(主备延时的主要耗时阶段)。主备延时时间 = T3 - T1。2. 主备延迟的原因备库压力大(一些非业务sql语句会放到备库执行)----解决方法是设置一主多从,多个备库分担备库的压力;执行的是大事务,那么主原创 2021-08-08 12:15:43 · 603 阅读 · 0 评论 -
MySQL的主从备份(可靠性)
一、备库的备份MySQL依赖bin log进行备库的备份(bin log既支持归档,又支持备份)MySQL支持主备切换,作为备库要设置成readonly(这样可以避免sql语句对备库的误操作),而且因为备库从主库的bin log读取数据进行备份时,操作者为超级用户,read only对超级用户无效,所以仍然可以写入备份数据。二、主备的执行过程:主库与从库建立一个长连接,主库安排一个线程负责维护来连接;备库执行start slave命令,建立一个io_thread线程负责维护与主库的链接,一个sq原创 2021-08-06 11:09:03 · 1220 阅读 · 0 评论 -
MySQL的幻读
定义:也就是同样的select读取sql语句读到的数据,后一次比前一次读到更多的数据行(事务在执行两句select语句中间时间,有其他事务往数据库中加入了数据行)事务执行过程中单独给每一行加写锁并不能阻止幻读,因为事务不能给当前没有记录的行加锁,所以其他事务可以加入新的数据,那么前后两次select就可能查到多出的数据行。如下图所示:幻读的解决办法:使用next-key lock(间隙锁 Gap key + 行锁的合称),间隙所就是锁住相邻两行记录之间的空行(前开后闭区间)。比如:(0, 10],原创 2021-08-05 11:22:50 · 380 阅读 · 0 评论 -
MySQL慢查询的原因(一)
实际中有时候虽然只查询一行或者几行记录,查询的速度也很慢原因:主要有以下三个原因其他线程在执行操作,表被锁住了其他事务占据了该行的写锁执行的sql语句为一致性读,需要回滚较长的undo log日志(或者查询没走索引树)处理方式:1) 对于表锁,找到堵住的线程,将其kill;可以通过指令show processlist,查看所有当前正在执行的线程进一步的可以用指令select blocking_pid from sys.schema_table_lock_waits直接找到造成阻塞的线原创 2021-08-04 10:43:48 · 348 阅读 · 0 评论 -
sql语句索引相关性能调优
准则:要使用索引就不能破坏索引的原有顺序情况一:显示函数操作对索引字段进行函数操作function(index)可能会破坏原有索引字段的有序性,为了方便起见,MySQL不会判断使用的函数是否有改变索引顺序(偷懒了),而是默认只要使用了函数就改变了原索引的顺序,所以以下sql语句不会走索引,mysql> select * from table where id + 1 = 10000;此处id上有索引,但是它是函数中的变量,所以不走索引树(即使没有破坏原索引字段的顺序),可以改成如下语句:m原创 2021-08-03 11:35:40 · 173 阅读 · 0 评论 -
MySQL如何随机查询表中的几行记录
ps: 推荐使用以下的方法三.方法一:使用order by rand(),命令比较简单,只需要一行就行,但是执行效率低,扫描表记录行数多。具体指令如下:mysql> select word from words order by rand() limit 3;该sql的意思就是随机选取表中的三条记录,只需要这么一句命令行。这里用到的是order by的排序方法,需要临时表,其具体执行流程如下强调:order by rand使用的是rowid排序方法,只要内存临时表空间够,就把所有字段放原创 2021-08-02 14:26:04 · 2017 阅读 · 0 评论 -
MySQL中order by的工作原理
涉及概念:sort_buffer、全字段排序、rowid排序、联合索引查询结果排序问题、覆盖索引以下文章都考虑一个场景:查询一张表中城市字段是“杭州”的所有人的名字,并且按照姓名排序返回前 1000 个人的姓名、年龄。ID为主键,且在姓名字段上加普通索引,对应的sql语句如下:select city,name,age from t where city='杭州' order by name limit 1000 ;全字段排序:全字段排序就是一次性将数据行中被select的字段值都读取出原创 2021-08-01 17:26:33 · 218 阅读 · 0 评论 -
MySQL如何统计表格的总行数
使用count来统计表格的行数按照效率排序如下:count(字段)<count(主键 id)<count(1)≈count(*)原因如下:count(字段):遍历每一行 + 读取字段值 + 判断为null + 判断为not nulla) 如果这个“字段”是定义为 not null 的话,需要从每一行中读取这个字段,但不需要判断是否为null,直接累计行数加一。b) 如果这个“字段”定义允许为 null,那么执行的时候,需要先从每行中取出该字段,然后判断是否为null,不为null那么原创 2021-07-31 10:20:38 · 2293 阅读 · 0 评论 -
MySQL是怎么清理表空间的(垃圾回收,释放磁盘空间)
有点类似与JVM的垃圾回收机制,当删除表中的行数据时,MySQL不会自动回收垃圾内存(没有JVM那么强大的垃圾回收机制),于是结果内存空间如下:我们删除2,4,6行数据后,数据页中只是会将对应行的数据抹去,但是其仍然占有磁盘空间,所以我们发现有时候删除了大量的数据行后,可用的磁盘空间并没有增加,之后用户如果想插入ID值为2,4,6(要在删除行的ID区间内)的记录的话可以直接加在这张表上,但是不能在这张表中加入其余ID值不在区间内的记录行。重建表于是MySQL为了释放删除数据后的空间想出了一个办法,就原创 2021-07-30 22:06:47 · 3576 阅读 · 0 评论 -
MySQL专业术语
记录一下自己之前总结归纳的MySQL相关术语以及对应的文章,便于以后复习回顾:WAL / bin log / redo log(crash safe) / 两阶段提交undo log / MVCC / read viewchange buffer / mergeforce index(a)flush / innodb_io_capacity / innodb_flush_neighbors / 刷脏页后续等待更新…...原创 2021-07-30 10:55:17 · 253 阅读 · 0 评论 -
MySQL有时候同样的语句执行特别慢
主要原因:就是此时MySQL在执行flush操作(将数据持久化到磁盘中)。一个很形象的比喻:掌柜的记忆是内存,临时记账的黑板是redo log,账本是磁盘。flush:就是将redo log中的内容更新到账本上,不一定是等店铺打样了才这么做。或者是将内存中脏页数据持久化到磁盘中,将其变为干净页或者释放其在内存中占有的空间。执行redo log操作和刷脏页不是同一个操作。**脏页:**指的是存在内存中的数据页,但是还没有持久化到磁盘中(记录在redo log中,还没有记录在bin log中,有可能丢失)原创 2021-07-30 10:44:57 · 756 阅读 · 0 评论 -
MySQL怎么给字符串字段加索引
字符串索引的类别:全字符串长度完整索引前缀索引字段倒序存储 + 前缀索引(相当于倒序后缀索引)新建字符串hashcode字段作为索引首先考虑是建立整个字符串长度的索引还是使用前缀索引前缀索引的优点:索引字段长度短,索引文件占用空间小前缀索引的缺点:增加额外的扫描行数:因为前缀索引截取了字符串字段的部分字符,字段具有的唯一性会被破坏,索引树上可能存在多个相同的字段,必须进行多次回表查询,比如在以下用户的邮箱上分别建立全字符串索引和前缀索引结合下图进行分析,顺便回顾MyS原创 2021-07-29 10:26:07 · 493 阅读 · 0 评论 -
MySQL为什么有时候会选错索引
当一条语句中涉及多个索引时,MySQL会选择使用哪个索引,但是有时候它选择的索引执行速度不是最优的(也就是优化器得到的优化结果不为最优)解决方案1:如果你知道使用哪个索引更好,可以使用**force index(a)**来指定sql语句强行使用某个索引,使用explain来查看sql执行情况explain select * from t force index(a) where a between 10000 and 20000;解决方案2:使用analyze table t指令(具体看下面原创 2021-07-28 22:23:33 · 394 阅读 · 0 评论 -
MySQL普通索引和唯一索引的选择
建议:尽量使用普通索引一、查询性能比较:基本没差别在字段K上建立普通和唯一索引,执行sql语句:select id from T where k=5对于普通索引来说,查找到满足条件的第一个记录 k = 5后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录;对于唯一索引来说,由于索引定义了唯一性,表中字段k=5最多出现在一行记录中。查找到第一个满足条件的记录后,就会停止继续检索。由于InnoDB查询时是先将表中的一页数据读取到内存,所以普通索引查询好几行数据都是从内存中读取,内原创 2021-07-28 10:44:37 · 137 阅读 · 0 评论 -
MySQL不同事务中数据的可见性
不同事务之间数据的可见性要结合事务的隔离性级别(不同隔离性创建视图的时机不同)、事务的读写锁(一致性读)进行判断。注:以下是本人目前的理解,正确性有待验证,在这里先做一下记录复习一下:可重复读是在事务开始就建立视图,已提交读是在执行sql语句时才会建立视图;写锁是独占的,其他事务必须等占有写锁的事务提交后才能对数据进行读或者写;读锁共享,所有事务都可以同时读取数据。接下来用下图解释数据可见性:如果隔离级别是可重复读,那么在两个事务刚开始就需要建立一致性视图,而建立视图需要获得读锁,因为T原创 2021-07-27 10:51:38 · 1138 阅读 · 0 评论 -
MySQL的锁(为什么有时候使用行锁for update会造成表锁)
根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。全局锁使用命令:Flush tables with read lock 或者set global readonly=true(推荐使用前者,因为后者的readonly字段可能要被用来做逻辑判断,并且前者锁支持手动和自动释放,但后者必须再次手动设置readonly,容易造成长事务)使用场景:全库逻辑备份拥有事务功能的引擎使用single-transaction(事务+MVCC)进行全局锁,不支持事务就用Flush tables原创 2021-07-26 11:08:33 · 217 阅读 · 0 评论 -
MySQL的索引
MySQL索引中包含有三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。这里补充记录一下自己对Hash的重新认识:hash是为了将任意长度的一个输入转化成固定长度(比如用一个短的长度为3的hash字符串来表示一个由10000字节的文本,这样只需要比较两个hash字符串就可以判断两份上万字节的文本是否一样了),一般都是用较短的输出去表示较长的输入,是一种压缩算法,也因此HashMap支持快速查找,但增删没有LinkedList快。以上hash字符串就是hashcode,再对该hashc原创 2021-07-25 16:11:19 · 90 阅读 · 0 评论 -
事务的隔离
不同的隔离级别为什么读到的数据不同?因为不同隔离级别的事务读取数据的时间点是不一样的,这里我把他叫做切入时机,当事务要去数据库中取数据时,数据库会创建一张当前时机的视图表供事务读取数据。比较如下:已提交读:在事务中要执行sql语句的时候才去数据库中创建视图并读取数据;可重复读:在事务一开始就在数据库中建立一张视图,并且直到事务提交前都不会改变该视图(快照读);可串行化:数据库任意时刻只支持建立一张视图,且这张视图只能被创建它的事务读写。所以其他事务必须等前一个事务提交了才可以访问数据库;未提交读原创 2021-07-25 12:21:30 · 175 阅读 · 0 评论 -
MySQL更新语句以及redo/bin log双日志
首先给两张图解释MySQL更新语句执行过程:update 语句执行操作前的流程和查询语句一致,执行操作具体过程如下:图中浅色框表示是在 InnoDB 内部执行的(我觉得是系统分配给MySQL有内存资源和磁盘资源,比如分给它有1G内存和100G磁盘空间),深色框表示是在执行器(server层)中执行的。其中涉及两个重要日志:redo log 和 bin log。redo log: 记录在InnoDB(特有)引擎中,也就是存储引擎部分;用来防止数据库发生crash时来不及操作未执行的sql语句而丢失原创 2021-07-24 11:25:03 · 200 阅读 · 2 评论 -
MySQL之查询语句
今天开始跟着“丁奇”大佬重新认识MySQL,为了马上开始的秋招可以愉快的和面试官击剑O(∩_∩)O哈哈~文章引自:https://time.geekbang.org/column/article/68319以下是个人的笔记以及总结,首先给一张图解释Mysq的查询语句执行过程:大体来说,MySQL 可以分为 Server 层和存储引擎层(MySQL自带引擎为MyISAM,InnoDB是另一个公司以插件形式引入的)两部分。按查询操作的顺序分别介绍如下几点:连接器:1)在完成经典的 TCP原创 2021-07-24 11:04:56 · 90 阅读 · 1 评论