- 博客(44)
- 收藏
- 关注
RSS订阅原创 SQL优化的步骤和方法
这几天看到群里高手发的sql优化的基本思路步骤：增加过滤条件，创建合适索引，简化复杂sql，减少io扫描。下面将对sql优化的步骤和方法进行介绍。
2024-03-08 09:24:31
921
原创 undo日志详解
上一节详细的说了redo日志，redo日志的功能就是把增删改操作都记录着，如果断电导致内存中的脏页丢失，可以根据磁盘中的redo日志文件进行恢复。redo日志被设计出来是为了保证数据库的持久性，undo日志设计出来是为了保证数据库的原子性。一个事务开启之后，做增删改操作，会记录redo日志，但是如果后悔了进行回滚，redo日志是做不到回退的，这时候就需要undo日志了。总结：记住一句话，redo日志保证了数据库的持久性，undo日志保证了数据库的原子性。
2024-02-22 10:40:59
1130
原创 数据库事务id
事务可以分为只读事务和读写事务。~start transaction read only语句开启只读事务只读事务不可以对普通的表进行增删改，但是可以对临时表做增删改操作。~start transaction read write语句开启读写事务，或者使用begin、start transaction语句默认开启读写事务。
2024-02-21 09:41:26
396
原创 redo日志——log buffer与磁盘的交互详解
上面没有介绍怎么确定哪些redo日志可以被覆盖，log buffer刷盘机制有一个就是脏页刷入之前，先将redo日志刷入磁盘，这些就没有必要刷入了，但是伴随着别的机制所以磁盘中可能存在脏页还没有刷入的redo日志，这些是有用的，不能被覆盖，而恢复就是从这个点之后进行顺序恢复，注意这个点之后的redo日志对应的脏页可能被刷入磁盘了，这点我们不能确定，不确定就全刷，mysql也有机制判断是否被刷入。终点我们想当然的觉得终点很容易确定，就是最后的block，但是计算机怎么确定是最后的block呢？
2024-02-20 16:25:57
921
原创 redo日志详细介绍
前面介绍了buffer pool，我们知道内存中的脏页并不是产生就被立即刷入磁盘，而是达到某些条件才会刷入。那这就会导致一个问题，内存中还有脏页这时候断电，重启会导致内存丢失脏页，这样之前的操作就白做了，为了解决这个问题引入redo日志，目的就是记录所有操作，故障后通过redo日志进行恢复buffer pool中的脏页。对于悲观插⼊来说，由于需要新申请数据⻚，还需要改动⼀些系统⻚⾯，⽐⽅说要修改各种段、区的统计信息信息，各种链表的统计信息。总共需要记录的redo⽇志有⼆、三⼗条。
2024-02-19 15:13:45
1045
原创 buffer pool和查询缓存的区别
查询缓存和BufferPool完全是两个东西。查询缓存位于server层，BufferPool是Innodb数据引擎中的核心组件。
2024-01-23 17:41:00
551
原创 InnoDB的Buffer Pool
最近最少使用的原则去淘汰缓存页。当我们需要访问某个页的时候，LRU链表发生了这个情况：1）如果该页不在buffer pool，在把该页从磁盘加载到buffer pool中的缓存页时，就把该缓存页对应的控制块作为节点塞到LRU链表的头部。2）如果该页已经在buffer pool中，直接把控制块移动到头部。上面的两种情况反映出了两个问题：1）加载到buffer pool的页不一定被用到2）用的少的页面把热点页面挤掉。1）InnoDB想操作系统申请一段连续的内存空间作为缓存。
2024-01-22 22:26:56
973
原创 mysql优化器对sql语句的简化（条件简化、外连接消除、子查询优化）
大多数的sql语句由人工写完后都可以转换成某种比较高效的执行形式，称为查询重写。本质上就是mysql优化器觉得你写的sql语句不好，自己再重写一遍。那重写的规则是什么？
2024-01-17 16:32:53
1013
原创 Oracle11g RAC无法使用VIP或SCAN IP连接数据库的解决方案
安装完Oracle 11g rac后，在10g的客户端的 tnsnames.ora中配置好，scanip,但是无法访问，报错为目标主机不存在，折腾了很久，没弄明白，后面查到，系统初始化参数里有local_listener这一项，在这里可以对host的值加以修改，将scanip注册进来、在监听器中使用vip！因此只要把数据库实例参数local_listener内的host改为vip的IP地址即可。另外解决Oracle 11gR2 RAC 无法在客户端通过scanIP连接数据库。节点二上面也是，一样的修改。
2023-12-07 17:47:25
1707
原创 复杂sql分析 以及 索引合并
简单的sql语句我们很简单的就可以分析出来它的执行计划，但是复杂的sql呢。例如这条语句很难分析出来，遇到这样的语句怎么办呢，怎么分析它的范围匹配区间呢？首先我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换成true。之所以把用不到索引的搜索条件替换为true，是因为我们呢不打算使用这些条件进行在该索引上进行过滤，所以不管索引的记录满不满足这些条件，我们都把他们选取出来，带到之后徽标的时候再使用他们过滤。
2023-12-05 18:09:20
1353
原创 navicat某些表为什么不按主键排序
可以发现不是全表扫描而是索引树扫描，由此得知了共性，不按主键顺序排序的表，肯定是在二级索引上就保存着全部数据信息，索引mysql在select *的时候直接就会扫描二级索引树，故此查出来的数据就会按照二级索引的顺序排序。图一二级索引就是name，图二的二级索引是name和age的联合索引。不知道大家注没注意过navicat的这种情况。我们来全表扫描看下他的执行计划。为什么不是按主键排序呢。
2023-12-03 17:59:38
542
原创 limit 10和limit 10000 10效率相同吗
先说结论：不相同，差异很大。对比Query_ID为57和64的语句发现执行时间limit 10比limit 10000，10效率高了10倍。什么原因呢？这就要从limit的底层原理开始讲起。请继续往下看。
2023-12-01 12:04:46
1480
原创 mysql语句性能分析工具——profiling
sql查询慢的情况很常见，对于慢sql的优化有三个步骤，又称优化三板斧。板斧一：查看执行计划explain板斧二：建立合适索引板斧三：使用合适的连接关系和过滤条件来实现sql语句的优化如果执行计划正确sql语句的性能还很慢，可以通过mysql的profiling工具进行定位分析。为了更精准的定位一条sql语句的性能问题，需要清楚的直到这条sql语句运行时消耗多少系统资源。
2023-11-30 17:30:13
1582
原创 一文彻底理解索引下推
索引下推的产生一定围绕着回表，没有回表那就没必要产生索引下推，因为上面也说了索引下推的目的就是减少回表，而不是避免回表。（题外话：避免回表使用索引覆盖——建立覆盖索引）因为%在前面所以不会走索引，当然如果不是select * ，会使用二级索引的index。二级索引取出的数据是依次回表还是一次回表？索引下推是为了什么发明的？extra=using index condition 发生了索引下推。注意：索引下推是为了减少回表而发明的。引入的一个特性,索引下推。MySQL5.6开始。
2023-11-29 18:18:08
1114
原创 MySQL 基于成本的优化
我们现在已经得到了聚簇索引占⽤的⻚⾯数量以及该表记录数的估计值，所以就可以计算全表扫描成本了，但是设计MySQL的⼤叔在真实计算成本时会进⾏⼀ 些微调，这些微调的值是直接硬编码到代码⾥的，由于没有注释，我也不知道这些微调值是个啥⼦意思，但是由于这些微调的值⼗分的⼩，并不影响我们分 析，所以我们也没有必要在这些微调值上纠结了。9693指的是统计数据中表的记录数，对于InnoDB存储引擎来说是⼀个估计值，0.2指的是访问⼀条记录所需的成本常数，后边的1.0是⼀个微调值，我们 不⽤在意。
2023-11-24 18:05:34
1471
原创 连接的原理（待修改）
此处假设使⽤t1作为驱动表，那么就 需要到t1表中找满⾜t1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建⽴⼆级索引，所以此处查询t1表的访问⽅法就设定为all吧，也就是采⽤全 表扫描的⽅式执⾏单表查询。需要注意的是，这个ON⼦句是专⻔为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加⼊结果集这个场景下提出的，所以如果把ON ⼦句放到内连接中，MySQL会把它和WHERE⼦句⼀样对待，也就是说：内连接中的WHERE⼦句和ON⼦句是等价的。
2023-11-23 17:56:39
55
原创 隐式转换导致索引失效的原因
所以，我们在写SQL时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。比如'123abc'会转换为123，'012abc'会转换为012也就是12，'5.3a66b78c'会转换为5.3，其他同理。比如左面是字符型，右边是数值型，字符转换成数值，而字符容易产生歧义，比如'10000f'和'10000'转换成数值类型相同，这样除了会导致查询结果错误还会导致索引失效。隐式转换不一定都导致索引失效，比如左面是数值类型，右边的字符就会转换成数值类型，不会导致索引失效。
2023-11-19 22:41:23
273
原创 linux配置固定ip（两种方法）
4、第二步填写子网ip注意第三位的配置，那个就是你想固定的ip，如果选择DHCP，会自动为你配置ip（固定的），开机ip addr就会显示。BOOTPROTO="DHCP"修改为BOOTPROTO="static"记住这些信息（配置网关ip，不能设置最后以为为1，后面为1是主机ip）这样配置需要切换网络适配器，本来是nat，需要切换仅主机或者~模式。配置完点击确定，就可以开机ip addr，就会出现固定ip。首先刚下载的vm，刚创建的虚拟机，肯定是需要配置ip的。重启网卡，或者重启虚拟机就配置好固定ip。
2023-11-18 22:08:12
1272
原创 数据库的分库分表 详解
一个系统随着用户量上升，产生的数据也越来越多，到达一定程度，数据库就会产生瓶颈。首先单机数据库所能承载的连接数，io和吞吐量都是有限的，并发量上来数据库就渐渐顶不住了。如果单表的数据量过大，查询的性能也会下降，B+Tree层数可能增加，io次数增加，原理可以看我以前的文章。此时就不得不分库分表。
2023-11-17 17:48:58
821
原创 InnoDB的表空间
表空间是⼀个抽象的概念，对于系统表空间来说，对应着⽂件系统中⼀个或多个实际⽂件；对于每个独⽴表空间来说，对应着⽂件系 统中⼀个名为表名.ibd的实际⽂件。⼤家可以把表空间想象成被切分为许许多多个⻚的池⼦，当我们想为某个表插⼊⼀条记录的时候，就从池⼦中捞出⼀个对应的 ⻚来把数据写进去。页多种多样，在InnoDB中我们接触最多的就是索引页⻚⾯通⽤部分 我们前边说过数据⻚，也就是INDEX类型的⻚由7个部分组成，其中的两个部分是所有类型的⻚⾯都通⽤的。当然我不能寄希望于你把我说的话都记住，所以在这
2023-11-16 17:52:33
37
原创 mysql查询成本计算
myisam和innodb都需要将数据和索引存储到磁盘上，当进行查询时，就需要把数据或者索引页加载到内存中，从磁盘加载数据到内存这个过程，损耗的时间就叫io成本。1、rows：表示的是表中的 记录数，是个估值，但是计算成本时候，按照show table status like ’orders'显示的结果来进行计算。读取记录的时候，需要检查记录是否满足对应的搜索条件，对结果集进行排序等等操作发生的损耗为cpu成本。2、iex_expire_time：expire_time属于是范围查询，也可能用到索引。
2023-11-14 17:54:54
248
原创 Linux新增磁盘分区挂载方法（一文搞定磁盘分区挂载）
添加一行信息：UUID=096a15e5-ee05-40f1-be4e-d5efed55d2a0 /fenqu ext4 defaults 0 0。blkid /dev/sdb1（查看分区对应的UUID）mkfs.ext4 /dev/sdb1(格式化）输入：n，p，1，enter，+512，wq。4、fdisk -l后可以看到磁盘已经分区。3、fdisk /dev/sdb(分区）fdisk /dev/sdb1(分区）可以看到/dev/sdb没有分区。
2023-11-12 18:28:34
91
1
原创 sql中exists和in的区别
在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系 ，另外。的区别： 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用。， 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用。主要是造成了驱动顺序的改变（这是性能变化的关键），如果是。遍历循环外表，检查外表中的记录有没有和内表的的数据一致的。，那么以外层表为驱动表，先被访问，如果是。的遍历是在内存中遍历。
2023-11-11 18:28:27
162
1
原创 JOIN算法原理 及 优化
JOIN：mysql中用来进行连表操作，用来匹配两个表的数据，筛选合并出符合我们要求的结果集。insert into student values(1,'10086','大师兄');insert into student values(2,'10087','大祭祀');insert into student values(3,'10088','大司马');insert into student values(4,'10081','大将军');
2023-11-10 17:36:44
344
1
原创 慢日志查询
mysql调优金字塔越向上难度越大，回报减少。1、硬件和os调优特别复杂。2、mysql调优包括调整表结构、优化sql、恰当的使用索引、清除多余的索引等等。3、架构调优属于开发的事情，需要考虑实际的业务场景。可以将非数据库的任务放到缓存。考虑是否使用分布式。考虑是否读写分离。记录运行时间超过阙值的sql的日志就是慢日志。long_query_time参数：该参数会设定一个阙值，超过该值的sql，就是慢查询sql。
2023-11-09 18:02:41
277
1
原创 mysql的数据存在linux的哪个目录——数据目录（有图有真相）
InnoDB不会默认把各个表的数据存储到系统表空间中，而是为每个表建立一个独立表空间，创建多少个表，就有多少独立表空间。这个数据库很核心，存储了mysql的用户账户和权限信息，⼀些存储过程、事件的定义信息，⼀些运行过程中产生的日志信息，一些帮助信息以及时区信息等。捋关系：操作系统用文件系统管理磁盘，mysql产生的数据存到文件系统的某个目录下的某个文件，而启动mysql的某些文件也存在这个目录下，这个目录就叫数据目录。表结构就是该表的名称，表里有多少列，每个列的数据类型，约束条件和索引，字符集和比较规则。
2023-11-08 17:41:41
990
1
原创 导致索引失效的情况汇总（持续更新）
有时建了索引但是实际的执行计划却没有走索引，到底是什么导致索引失效？读完并理解本篇文章你必将有所收获。创建表order_idage int,
2023-11-07 12:00:23
89
原创 索引列中有null值 会不会使索引失效？
上面我说过mysql内部认为null是相等的，所以导致当插入过多null值，造成重复率过多，is null不会走索引。1、null_equal:认为所有的null值都是相等的，也是默认值，这种统计方式，会让优化器认为某个列中的平均一个值的重复次数特别多，倾向于不适用索引去访问。其实这和普通索引上的情况相同，我们把null值当成正常的值，mysql默认认为null是相同的，所以重复率特别高的话，优化器肯定不会走索引，而是走全表扫描。可以看到和上面的条件都是相同的，但是却是走了全表扫描，还没想明白？
2023-11-06 11:14:05
1635
1
原创 如何创建高效的索引
通俗的意思就是创建普通索引选择性一定要高，创建联合索引第一列的选择性一定要高，扫描更少的数据。主键值，不仅是在聚簇索引中存储，其他的二级索引上的节点上会存储主键值，选择较小的主键数据类型，可以节省存储空间。4、避免冗余索引：如果已经有了一个联合索引，在创建一个包含该索引一部分的索引是没必要的，在创建增加维护成本。3、覆盖查询需要的列，创建联合索引的时候，要注意这个联合索引是否是一个覆盖索引，可以避免回表。选择性高的索引，可以让mysql在查询时过滤掉更多的行，唯一性索引的选择性1。
2023-11-05 17:34:55
44
原创 where子句中使用 or 来连接条件的各种情况
1、只要是查询的列可以在二级索引找到，执行计划就是index，扫描索引树。找全部列那就是全表扫描。2、只要两个列是唯一索引或者查询的值占比很少就走两个索引，type是index_meger3、两个列其中一个查找不是唯一值或者占比很多，分两种情况1）查询的列在二级索引可以全部找到，type是index2）查询的列在二级索引上找不到，type是all。
2023-11-04 12:29:31
1235
1
原创 B+Tree根节点固定不变
B+Tree的根节点通常一直存在内存中，除非整个B+Tree被销毁或卸载。因为B+Tree的查询和插入操作都是从根节点开始的，如果根节点不存在内存中，就需要频繁地从磁盘或其他外部存储设备中读取根节点，这会显著影响B+Tree的性能。因此，为了提高B+Tree的查询和插入效率，根节点通常会一直存在内存中。在mysql中目录项记录除了记录索引列和页号，还会额外存储主键值，确保目录项记录时唯一且有序的，索引值相同会按照id排序。以前文章也说过，二级索引的记录是唯一的，就算此索引列不是唯一索引，它也必须唯一。
2023-11-04 10:23:36
51
1
原创 mysql中B+Tree的插入和删除操作
问题：那这样非叶子节点的值是怎么变化的，可以看到页3还是3个记录，也就说明，现在页3指向页6的记录主键已经换成了页6最小的（30），但是怎么验证还没有查到。若是页满了，进行插入会进行页分裂，左叶子结点包含前m/2个记录，右结点包含剩下的记录，将第m/2+1个记录的key进位到父结点中（父结点一定是索引类型结点）网上都在讨论B+Tree的查询操作，B+Tree的叶子节点非叶子节点构成，但是我却有个疑问，B+Tree的插入和删除操作，B+Tree到底是怎么做的。假设是4阶B+Tree。
2023-11-03 18:29:34
183
原创 《MySQL是怎样运行的：从根儿上理解MySQL》阅读笔记————6.B+树索引
相同点：1、页面类型都是索引页（数据页），上一章的File Header的类型2、页的组成都是相同的，上一章介绍的7个部分3、并且在页内都会为主键在Page Directory生成槽，从而可以按照主键值进行查找时，可以使用二分法加速查找。例子查找主键20的页（从目录项记录页开始）：1、先到存储目录项记录的页，上图页30二分快速定位（页内二分不再赘述），因为12
2023-11-03 12:21:01
109
原创 《MySQL是怎样运行的：从根儿上理解MySQL》阅读笔记————5.InnoDB数据页结构
红字是我的读后提炼出的比较重要的信息。黑字速读就可以，红字是需要看懂的信息。数据页概念：数据页是InnoDB管理存储空间的基本单位，一个页大小一般为16KB。InnoDB设计了不通类型的页，比如：存放表空间头部信息的页，存放Inert Buffer信息的页，存放INOOE信息的页，存放undo日志信息的页等等。下面我们只讨论存放我们表中记录的那种类型的页，这种存放表中记录的页就叫索引页，也是我们常说的数据页。我们要清楚一个概念再InnoDB下，索引就是数据，数据就是索引。
2023-11-02 11:18:45
127
1
原创 explain性能详细分析
（6） union：分为union和union all两种，若第二个select出现在union之后，则被标记为union，如果union被from子句的子查询包含，那么第一个select被标记为derived。union会去重，union all不会去重。问题发现1：驱动表users，类型是all，看rows为8，因为需要users的id列的所有值所以全表扫描，但是如果建辅助索引，类型就变为index（这里index的原因是直接扫描了辅助索引不用回表就可以获取到全部的主键值，这很好理解）。
2023-10-31 17:22:58
873
11
原创 聊聊唯一约束和唯一索引 的 区别和联系
首先唯一约束和唯一索引是两个概念，千万不要混淆。唯一约束：保证在一个字段或者一组字段里的数据都与表中其它行的对应数据不同。可插入null，并且可以插入多个null。唯一索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。也可插入null。
2023-10-30 16:34:36
722
4
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人