MySQL优化(InnoDB分析)
文章平均质量分 84
嗯。
你走吧起风了__
孤独本是常态,逢人何必言深。
展开
-
MySQL中出现using filesort现象的一般情况与分析
在使用explain命令优化SQL语句的时候常常会在Extra列的描述中发现Using filesort选项,其实这个名字很容易造成误解,一开始我以为是“文件排序”的意思,进一步说可能就是使用了磁盘空间来进行排序,但是这个理解是错误的,Using filesort真正含义其实只有sort这一个单词,和file没有什么关系,Mysql一般是通过内存进行排序的,不过,要是超过了配置中的限制,应该会生成临时表。Using filesort表示在索引之外,需要额外进行外部的排序动作。导致该问...原创 2021-01-14 10:49:28 · 23792 阅读 · 7 评论 -
MySQL(InnoDB剖析):---table之(索引组织表、附_rowid)
一、索引组织表的概念在InnoDB中,表都是根据主键顺序组成存放的,这种存储方式的表称为索引组织表二、表主键的确认规则在InnoDB中,每张表都会有个主键 主键的确认的规则如下:①创建表时指定的主键 ②如果在创建表时没有显示地定义主键,则按照如下方式来选择或创建主键:首先判断表中是否有非空的唯一索引(unique、not null)。如果有,则该列即为主键 当表中有多个非空唯一索引时,InnoDB会选择建表时第一个定义的非空唯一索引作为主键。这里需要注意的是:主键的选择根据的是定义索引的原创 2021-03-07 14:43:21 · 271 阅读 · 0 评论 -
数据库三大范式
数据库设计三大范式为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式。在实际开发中最为常见的设计范式有三个:1.第一范式(确保每列保持原子性)第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性,本来直.原创 2021-03-05 09:25:08 · 178 阅读 · 1 评论 -
MySQL(InnoDB剖析):---table之(表空间:段(segment)、区(extent)、页(page))
一、InnoDB逻辑存储结构从InnoDB的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间(tablespace) 表空间又由段(segment)、区(extent)、页(page)组成 页在一些文档中有时也称为块(block)二、表空间表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中默认表空间前面文章已经介绍了在默认情况下InnoDB有一个共享表空间ibdata1,即所有数据都存放在这个表空间内show vari...原创 2021-03-02 18:24:55 · 1610 阅读 · 0 评论 -
MySQL(InnoDB剖析):---文件之(InnoDB存储引擎文件:表空间文件(.ibd)、重做日志文件(redo log))
前面介绍的文件都是MySQL数据库本身的文件,和存储引擎无关。本文介绍与存储引擎有关的独有的文件。本文将介绍与InnoDB密切相关的文件,这些文件包括重做日志文件、表空间文件一、表空间文件功能:InnoDB采用将存储的数据按表空间(tablespace)进行存放的设计 在默认配置下会有一个初始大小为10MB,名为idbata1的文件(见下图),该文件就是默认的表空间文件innodb_data_file_path参数该参数用于指定InnoDB使用的共享表空间文件。设置了这个...原创 2021-03-01 09:37:21 · 566 阅读 · 0 评论 -
MySQL(InnoDB剖析):---文件之(套接字文件.sock、pid文件.pid、表结构定义文件.frm)
一、套接字文件在前面介绍MySQL通信方式时介绍过套接字文件:https://blog.csdn.net/qq_41453285/article/details/104083287在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式,这种方式需要一个套接字文件套接字文件可由参数socket控制,如下所示:show variables like 'socket'\G;二、pid文件当MySQL实例启动时,会将自己的进程ID写入一个文件中——该文件为pid文件 该文件.原创 2021-03-01 09:23:30 · 163 阅读 · 0 评论 -
MySQL(InnoDB剖析):---文件之(日志文件:错误日志(error log)、慢查询日志(slow query log)、查询日志(query log)、二进制日志(bin log))
日志文件记录了影响MySQL数据库的各种类型活动 常见的日志文件有: 错误日志(error log) 慢查询日志(slow query log) 查询日志(query log) 二进制文件(bin log) 一、错误日志错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息,也记录一些警告信息或正确的信息 用户可以通过下面命令来查看错误日志文件的位置:show variables like 'log_e原创 2021-02-24 13:47:05 · 554 阅读 · 0 评论 -
MySQL(InnoDB剖析):---文件之(参数文件.cnf)
一、参数文件概述当MySQL实例启动时,数据库会先去读一个配置参数文件,用来寻找数据库的各种文件所在位置以及制定某些初始化参数 在默认情况下,MySQL实例会按照一定的顺序在指定的位置进行参数文件的读取,用户只需通过下面的命令来进行查看即可: 通过下图我们可以知道,MySQL在启动时读取配置文件的顺序是:/etc/my.cnf->/etc/mysql/my.cnf->/usr/etc/my.cnf->~/.my.cnf mysql --help | grep my.cnf原创 2021-02-23 10:49:25 · 132 阅读 · 1 评论 -
MySQL(InnoDB剖析):---InnoDB的启动、关闭、恢复
一、概述InnoDB是MySQL的存储引擎之一,因此InnoDB的启动和关闭,更精确的是指在MySQL实例的启动过程中对InnoDB存储引擎的处理过程二、数据库的关闭(innodb_fast_shutdown参数)在数据库关闭时,参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为 该参数的取值如下: 0:在MySQL数据库关闭时,InnoDB需要完成所有的full purge和merge insert buffer,并且将所有的脏页刷新回磁盘。这需要一些时间,有原创 2021-02-23 10:46:04 · 313 阅读 · 0 评论 -
MySQL(InnoDB剖析):---InnoDB关键特性(插入缓冲(Insert Buffer)、两次写(doublewrite)、自适应哈希索引(AHI)、异步IO(AIO)、刷新邻接页)
InnoDB关键特性包括:插入缓冲(Inster Buffer) 两次写(Double Write) 自适应哈希索引(Adaptive Hash Index) 异步IO(Async IO) 刷新邻接页(Flush Neighbor Page)一、插入缓冲下面介绍的插入缓冲有: Insert Buffer Change Buffer 聚集索引与辅助(非聚集/二级)索引在介绍Insert Buffer之前,先介绍一些聚集索引与辅助 在InnoDB中,主键是行唯一的标.原创 2021-02-22 10:07:56 · 485 阅读 · 0 评论 -
MySQL(InnoDB剖析):---Master Thread(MySQL后台核心线程)
Master Thread通过前面介绍InnoDB体系架构我们知道,InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成的,本文就介绍该线程的具体实现以及该线程可能存在的问题一、InnoDB 1.0.x版本之前的Master ThreadMaster Thread具有最高的线程优先级别。其内部由多个循环(loop)组成: 主循环(loop)、后台循环(backgrounp loop)、刷新循环(flush loop)、暂停循环(suspend loop)原创 2021-02-21 10:26:15 · 404 阅读 · 3 评论 -
MySQL(InnoDB剖析):---Checkpoint(检查点)技术
一、前言前面一篇文章已经讲到(https://blog.csdn.net/m0_46405589/article/details/113844781),缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。因此页的操作首先都是在缓冲池中完成的。如果一条DML语句,如Update或Delete改变了页中的记录,那么此时页是脏的。即缓冲池中的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘ACID中D(持久性)的性质倘若每次一个页发生变化,就将新页的版本刷新到磁盘,那么这个开销是非常大的原创 2021-02-19 13:01:42 · 766 阅读 · 0 评论 -
MySQL(InnoDB剖析):---InnoDB体系架构(后台线程、内存池、文件)
从上图可以看到,InnoDB体系结构有: ①后台线程:主要负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态 ②内存池:InnoDB有多个内存块,可以认为这些内存块组成了一个大的内存池,负责如下工作 维护所有进程/线程需要访问的多个内部数据结构 缓存磁盘上的数据,方便快速地读取,同时在对磁盘文件的数据修改之前在这里缓存 重做日志(redo log)缓冲 等等....原创 2021-02-18 12:54:35 · 201 阅读 · 2 评论 -
MySQL(InnoDB剖析):---InnoDB存储引擎总体概述、InnoDB的版本发展
一、InnoDB存储引擎总体概述InnoDB存储引擎最早由Innobase Oy公司开发,被包括在MySQL数据库所有的二进制发行版本中,从MySQL 5.5版本开始是默认的表存储引擎(之前的版本InnoDB存储引擎仅在Windows下为默认的存储引擎)。该存储引擎是第一个完整支持ACID事务的MySQL存储引擎(BDB是第一个支持事务的MySQL存储引擎,现在已经停止开发),其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读,同时被设计用来最有效地利用以及使用内存和CPU。InnoDB的原创 2021-02-18 12:38:19 · 344 阅读 · 0 评论 -
MySQL(InnoDB剖析):---MySQL的连接与通信方式(进程间通信(管道、套接字、UNIX域套接字))
一、MySQL的连接与通信方式连接MySQL操作时是一个连接进程和MySQL数据库实例进行通信的过程 通信的本质是进程的通信 进程间通信的方式有:管道、命名管道、共享内存、TCP/IP套接字、UNIX域套接字等二、TCP/IP套接字的连接不同的机器之间,客户端可以通过mysql命令来连接MySQL服务端,此时使用到的是TCP/IP套接字三、命名管道和共享内存命名管道在Windows 2000、Windows XP、Windows 2003和Windows Vista以及在此之上的平原创 2021-02-18 12:36:38 · 337 阅读 · 0 评论 -
MySQL(InnoDB剖析):---MySQL的存储引擎及比较、查看(show engines)
一、存储引擎在前一篇文章中大致了解了MySQL数据库独有的插件式体系结构,并了解到存储引擎是MySQL区别于其他数据库的一个最重要特性。存储引擎的好处是,每个存储引擎都有各自的特点, 能够根据具体的应用建立不同存储引擎表 由于MySQL数据库的开源特性,用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。若用户对某一种存储引擎的性能或功能不满意,可以通过修改源码来得到想要的特性,这就是开源带给我们的方便与力量。比如,eBay的工程师Igor Chernyshev对MySQL Memory存储原创 2021-02-16 11:57:31 · 157 阅读 · 0 评论 -
MySQL(InnoDB剖析):---数据库和实例的概念、启动时读取的配置文件、MySQL体系结构
一、数据库和实例的概念数据库领域中有两个词很容易混淆,就是“数据库(databases)”和“实例(instance)”数据库和实例的概念数据库:物理操作系统文件或其他形式文件类型的集合。在MySQL数据库中,数据库文件可以是frm、MYD、MYI、ibd结尾的文件。当使用NDB引擎时,数据库的文件可能不是操作系统上的文件,而是存放于内存之中的文件,但是定义仍然不变 实例:MySQL数据库有后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。需要牢记的是,数据库实例才是真正用于原创 2021-02-16 11:44:10 · 234 阅读 · 0 评论 -
MySQL(InnoDB剖析):---哈希算法与自适应哈希索引
一、哈希算法哈希算法是一种常见算法,时间复杂度为O(1),且不只存在于索引中,每个数据库应用中都存在该数据库结构二、哈希表哈希表也称散列表,由直接寻址表改进而来.先看直接寻址表 当关键字的全域U比较小时,直接寻址是一种简单而有效的技术。加入某应用要用到一个动态集合,其中每个元素都有一个取自全域U={0,1,...,m-1}的关键字。同时假设没有两个元素具有相同的关键字 用一个数组(即直接寻址表)T[0...m-1]表示动态集合,其中每个位置(或称槽或桶)对应全域U中的一个关键字。图5-38.原创 2021-02-15 13:26:25 · 371 阅读 · 0 评论 -
MySQL(InnoDB剖析):---B+树索引的使用(联合索引、覆盖索引、优化器不使用索引的情况、索引提示、MRR优化、ICP优化)
前言在了解了B+树索引的本质和实现后,下一个需要考虑的问题是怎样正确地使用B+树索引,这不是一个简单的问题。这里所总结的可能并不适用于所有的应用场合。我所能做的只是概括一个大概的方向。在实际的生产环境使用中,每个DBA和开发人员,还是需要根据自己的具体生产环境来使用索引,并观察索引使用的情况,判断是否需要添加索引。不要盲从任何人给你的经验意见 根据前面的介绍,用户已经知道数据库中存在两种类型的应用,OLTP和OLAP应用 在OLTP应用中,查询操作只从数据库中取得一小部分数据,一般可能都在10条记录原创 2021-02-14 09:49:31 · 253 阅读 · 0 评论 -
MySQL(InnoDB剖析):---B+树索引的管理(索引管理、Fast Index Creation、Online Schema Change、Online DDL)
今天是正式的大年初一,小弟在此在祝各位朋友,大佬们,新春快乐,阖家团圆!一、索引管理索引的创建和删除可以通过两种方法: 一种是ALTER TABLE 另一种是CREATE/ DROP INDEX 使用语法例如下面有一个表,创建表时指定了一个主键索引create table t( a int not null, b varchar(8000), primary key(a))engine=innodb;现在添加一个字段c,并对字段.原创 2021-02-12 09:55:19 · 155 阅读 · 1 评论 -
MySQL(InnoDB剖析):---B+树索引(聚集索引与非聚集索引(辅助索引))、B+树索引的分裂
小伙伴们大家好!今天是大年三十,给大家拜个早年!在此小弟祝各位大哥们与家人团团圆圆,和和睦睦,新的一年身体健康,工作顺利!一、B+树索引概述B+树索引的本质就是B+树在数据库中的实现。但是B+索引在数据库中有一个特点就是高扇出性,因此在数据库中,B+树的高度一般都在2~4层,也就是说查找某一键值的行记录最多只需要2~4次IO。因为当前一般的机械磁盘每秒至少可以做100次IO,2~4次IO意味着查询时间只需0.02~0.04秒B+树的大致工作原理先抛开各种实现细节,来说一说B+树索引的大致.原创 2021-02-11 09:01:57 · 660 阅读 · 6 评论 -
MySQL(InnoDB剖析):---InnoDB索引概述、数据结构与算法概述(二分查找、二叉搜索树、平衡二叉树、B+树)
一、索引概述索引是应用程序设计和开发的一个重要方面。若索引太多,应用程序的性能可能会受到影响。而索引太少,对查询性能又会产生影响。要找到一个平衡点二、InnoDB存储引擎索引概述InnoDB支持以下几种常见的索引: B+树索引 全文索引 哈希索引 前面已经提到过,InnoDB支持的哈希索引是自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引,不能认为干预是否在一张表中生成哈希索引 B+树索引的传统意义上的索引,这是目前关系型数据库中查找最为常见和最有效的索引。原创 2021-02-10 10:15:00 · 250 阅读 · 1 评论 -
MySQL(InnoDB剖析):---锁之(自增长与锁、外键和锁)
一、自增长与锁自增长在数据库中是非常常见的一种属性,也是开发人员首选的主键方式自增长计数器在InnoDB的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter),当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化 执行如下的语句可以得到计数器的值select max(auto_inc_col) from t for update;自增长与锁(AUTO-INC Locking)插入操作会依据这个自增长的计数器值加1赋值给原创 2021-02-07 11:12:35 · 202 阅读 · 0 评论 -
MySQL(InnoDB剖析):---锁之(锁的类型(共享锁、排它锁、意向锁)、查看锁的信息)
一、共享锁、排它锁InnoDB实现了如下两种标准的行级锁: 共享锁(S Lock):允许事务读一行数据 排它锁(X Lock):允许事务删除或更新一行数据 锁之间的兼容性 如果一个事务占用行r的共享锁:那么其他事务都可以立即获取这个共享锁,但是如果有的事务像获取排它锁就需要等待,等待对行r的共享锁全部被释放才可以 如果你一个事务占用行r的排它锁:那么其他事务不论是占用改行的共享锁还是排他锁都需等待 下图显示了共享锁和排它锁的兼容性: 二、意向锁此外,原创 2021-02-06 14:13:41 · 159 阅读 · 1 评论 -
MySQL(InnoDB剖析):---锁之(阻塞、死锁、锁升级)
一、阻塞因为不同锁之间的兼容性问题,在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源,这就是阻塞。阻塞并不是一件坏事,其是为了确保事务可以并发且正常地运行nnodb_lock_wait_timeout参数该参数控制锁发生阻塞时等待的时间(默认是50秒) 该参数是动态的,因此可以在数据库运行时设置 在超时之后数据库会抛出一个1025的错误,如下:innodb_roolback_on_timeout参数用来设定是否在等待超时时对进行中的事务进行回滚操作(默认为.原创 2021-02-05 15:40:31 · 311 阅读 · 1 评论 -
MySQL(InnoDB剖析):---锁之(一致性非锁定读、一致性锁定读)
一、一致性非锁定读一致性的非锁定读是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据原理图解如果读取的行正在执行delete或update操作,这时读取操作不会因此去等待行上锁的释放。相反地,InnoDB存储引擎会去读取行的一个快照数据(Snapshot Data) 如下图所示图中直观地展现了InnoDB一致性的非锁定读。之所以称其为非锁定读,因为不需要等到访问的行上X锁的释放快照数据是指该行的之前版本的数据,该.原创 2021-02-04 09:52:57 · 167 阅读 · 0 评论 -
MySQL(InnoDB剖析):---全文检索(倒排索引、全文索引/全文检索)
一、引言演示说明前面介绍过了,B+树索引的特点是可以通过索引字段的前缀进行查找。例如对于下面的查询B+树是支持的select * from blog where content like 'xxx%';但是有时候我们要查询的是博客的内容中含有“xxx”,而不是以“xxx”开头,所以应该是下面的SQL语句select * from blog where content like '%xxx%';因此上述SQL语句即便添加了B+树索引也是需要进行索引的扫描来得到结果全文..原创 2021-02-03 15:07:23 · 4090 阅读 · 0 评论 -
MySQL(InnoDB剖析):---锁之(锁问题:脏读(未提交读)、不可重复读(提交读)、可重复读、丢失更新(可串行化))
一、脏读(未提交读)脏读不是脏页脏页是指在缓冲池中已经被修改的页,但是还没有刷新到磁盘中,即数据库实例内存中的页和磁盘中的页的数据是不一致的,当然在刷新到磁盘之前,日志都已经被写入到了重做日志文件中 而脏数据是指事物对缓冲池中行记录的修改,并且修改过的记录还没有被提交(commit)对于脏页的读取,是非常正常的。脏页时因为数据库实例内存和磁盘的异步造成的,这并不影响数据的一致性(或者说两者最终回达到一致性,即当脏页都刷回到磁盘)。并且因为脏页的刷新时异步的,不影响数据库的可用性,...原创 2021-02-02 13:19:42 · 867 阅读 · 2 评论 -
MySQL(InnoDB剖析):---锁之(行锁的3种算法(Record Lock、Gap Lock、Next-Key Lock)、解决Phantom Problem)
InnoDB支持3种行锁的算法,分别是:Record Lock:单个行记录上的锁 Gap Lock:间隙锁,锁定一个范围,但不包含记录本身 Next-Key Lock:Gap Lock与Record Lock的结合,锁定一个范围,并且锁定记录本身一、Record LockRecord Lock总是会去锁住索引记录 如果InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时InnoDB会使用隐式的主键来进行锁定二、Gap Lock附带在下面介绍三、Next-..原创 2021-02-01 16:44:28 · 464 阅读 · 0 评论 -
MySQL的锁机制和加锁原理
MySQL的锁机制和加锁原理推荐一篇关于MySQL锁机制的好文,我就不在重复写了,站在前人的肩膀能更能。MySQL的锁机制和加锁原理介绍原创 2021-01-26 16:28:30 · 111 阅读 · 0 评论 -
Mysql索引之-cardinality
查看索引show index from tb_name;上面有一个属性Cardinality,可以通过观察它来评估索引是否合理。它会估计索引中不重复记录,如果这个相对值很小,可能就要评估索引是否有意义。索引相对值再看一个模拟真实场景的表然后看一下这张表的总条数观察下面表格列id由于是主键,通过cardinality估算出来的值/总数接近于1;而另外2个索引列,估算出来的值/总数都趋近于0。估算出来的值/总数=占比,我们称占比为相对值。通过上面表格做一个大胆推测,查询id列是很快,原创 2021-01-23 16:44:29 · 232 阅读 · 0 评论 -
MySQL日志系统:redo log、binlog、undo log 区别与作用
推荐一篇较好的文章,让你弄懂redo log、binlog、undo log 区别与作用。文章传送门原创 2021-01-17 13:55:42 · 116 阅读 · 0 评论 -
InnoDB存储引擎新特性---Insert Buffer分析与理解
Insert buffer 是InnoDB存储引擎所独有的功能。通过insert buffer,InnoDB存储引擎可以大幅度提高数据库中非唯一辅助索引的插入性能。数据库对于自增主键值的插入是顺序的,因此插入能有较高的性能。但是实际生产环境中,用户表中主键仅有并且只能有1个,然而表中可能存在多个辅助索引。为了阐述非聚集索引写性能问题,我们先来看一个例子:mysql>create table t ( id int auto_increment, n原创 2021-01-15 16:00:40 · 294 阅读 · 0 评论 -
MySQL中聚簇索引与非聚簇索引的区别
聚簇索引数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引, 因为在一个表中数据的存放方式只有一种。非聚簇索引将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置在innodb中,在聚簇索引之上创建的索引称之为辅助索引,原创 2021-01-15 15:47:30 · 370 阅读 · 0 评论 -
mysql中创建主键的三种方式以及联合主键的创建方式
mysql中创建主键的三种方式方式一给uid设置主键**CREATE TABLE user( uid INT PRIMARY KEY, uname VARCHAR(10), address VARCHAR(20))**方式二给uid设置主键**CREATE TABLE user( uid INT, uname VARCHAR(10), address VARCHAR(20), PRIMARY KEY(uid))**方式三给ui原创 2020-11-04 13:20:02 · 5183 阅读 · 0 评论