MySQL技术内幕
MySQL技术内幕
一直不懂
笔记
展开
-
【MySQL技术内幕】64-性能优化
性能优化不是一项简单的工作,但也不是复杂的难事,关键在于对 InnoDB存储引擎特性的了解。如果之前各章的内容读者已经完全理解并掌握了,那就应该基本掌握了如何使 InnoDB存储引擎更好地工作。本章将从以下几个方面集中讲解 InnodB存储引擎的性能问题:选择合适的CPU 内存的重要性 硬盘对数据库性能的影响 合理地设置RAID 操作系统的选择也很重要 不同文件系统对数据库的影响 ...转载 2018-11-05 18:09:54 · 450 阅读 · 0 评论 -
【MySQL技术内幕】63-复制
1、复制的工作原理复制(replication)是MySQL数据库提供的一种高可用高性能的解决方案,一般用来建立大型的应用。总体来说, replication的工作原理分为以下3个步骤:主服务器(master)把数据更改记录到二进制日志(binlog)中。 从服务器(save)把主服务器的二进制日志复制到自己的中继日志(relay log)中。 从服务器重做中继日志中的日志,把更改应用...转载 2018-11-05 16:05:26 · 202 阅读 · 0 评论 -
【MySQL技术内幕】62-快照备份
MySQL数据库本身并不支持快照功能,因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一文件分区中,然后对该分区进行快照操作。支持快照功能的文件系统和设备包括 FreeBSD的UFS文件系统, Solaris的ZFS文件系统, GNU/Linux的逻辑管理器( Logical Volume Manager,LVM)等。这里以LVM为例进行介绍,UFS和...转载 2018-11-05 16:05:06 · 304 阅读 · 0 评论 -
【MySQL技术内幕】61-热备份
1、ibbackupibbackup是 InnoDB存储引擎官方提供的热备工具,可以同时备份 MyISAM存储引擎和 InnoDB存储引擎表。对于 InnoDB存储引擎表其备份工作原理如下:记录备份开始时, InnoDB存储引擎重做日志文件检查点的LSN。 复制共享表空间文件以及独立表空间文件。 记录复制完表空间文件后, InnoDB存储引擎重做日志文件检查点的LSN 复制在备份时产...转载 2018-11-05 16:04:50 · 1064 阅读 · 0 评论 -
【MySQL技术内幕】60-二进制日志备份与恢复
二进制日志非常关键,用户可以通过它完成 point-in-time的恢复工作。 MySQL数据库的replication同样需要二进制日志。在默认情况下并不启用二进制日志,要使用二进制日志首先必须启用它。如在配置文件中进行设置:[mysqld]log-bin=mysql-bin对于InnoDB存储引擎只简单启用二进制日志是不够的,还需要启用一些其他参数来保证最为安全和正确地记录二进制日志,因...转载 2018-11-05 16:04:35 · 181 阅读 · 0 评论 -
【MySQL技术内幕】59-逻辑备份
1、mysqldumpmysqldump备份工具最初由Igor Romanenko编写完成,通常用来完成转存(dump)数据库的备份及不同数据库之间的移植,如从MySQL低版本数据库升级到 MySQL高版本数据库,又或者从 MySQL数据库移植到 Oracle、 Microsoft SQL Server数据库等。mysqldump的语法如下:mysqldump [arguments] ...转载 2018-11-05 16:04:14 · 306 阅读 · 0 评论 -
【MySQL技术内幕】58-冷备份
对于 InnodB存储引擎的冷备非常简单,只需要备份 MySQL数据库的frm文件,共享表空间文件,独立表空间文件(*bd),重做日志文件。另外建议定期备份 MySQL数据库的配置文件my.cnf,这样有利于恢复的操作。通常DBA会写一个脚本来进行冷备的操作,DBA可能还会对备份完的数据库进行打包和压缩,这都并不是难事。关键在于不要遗漏原本需要备份的物理文件,如共享表空间和重做日志文件,少了这些...转载 2018-11-05 16:03:43 · 209 阅读 · 0 评论 -
【MySQL技术内幕】57-备份与恢复概述
可以根据不同的类型来划分备份的方法。根据备份的方法不同可以将备份分为:Hot Backup(热备) Cold Backup(冷备) Warm Backup(温备)Hot Backup是指数据库运行中直接备份,对正在运行的数据库操作没有任何的影响。这种方式在 MySQL官方手册中称为Online Backup(在线备份)。 Cold Backup是指备份操作是在数据库停止的情况下,这种备份...转载 2018-11-04 20:49:45 · 233 阅读 · 0 评论 -
【MySQL技术内幕】56-长事务
长事务(Long- Lived transactions),顾名思义,就是执行时间较长的事务。比如,对于银行系统的数据库,每过一个阶段可能需要更新对应账户的利息。如果对应账号的数量非常大,例如对有1亿用户的表 account,需要执行下列语句:UPDATE account SET account_total=account_total +(1 + interest_rate)这时这个事务可能需...转载 2018-11-04 20:35:41 · 687 阅读 · 0 评论 -
【MySQL技术内幕】55-不好的事物习惯
1、在循环中提交开发人员非常喜欢在循环中进行事务的提交,下面是他们可能常写的一个存储过程:CREATE PROCEDURE load1(count INT UNSIGNED)BEGINDECLARE s INT UNSIGNED DEFAULT 1;DECLARE c CHAR(80) DEFAULT REPEAT('a',80);WHILE s < count DOIN...转载 2018-11-04 20:31:07 · 221 阅读 · 0 评论 -
【MySQL技术内幕】54-分布式事物
1、MySQL数据库分布式事务InnoDB存储引擎提供了对XA事务的支持,并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactional resources)参与到一个全局的事务中。事务资源通常是关系型数据库系统,但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交,要么都回滚,这对于事务原有的ACID要求又有了提高。另外,在使用分布...转载 2018-11-04 19:43:59 · 221 阅读 · 0 评论 -
【MySQL技术内幕】53-事物的隔离级别
令人惊讶的是,大部分数据库系统都没有提供真正的隔离性,最初或许是因为系统实现者并没有真正理解这些问题。如今这些问题已经弄清楚了,但是数据库实现者在正确性和性能之间做了妥协。ISO和 ANIS SQL标准制定了四种事务隔离级别的标准,但是很少有数据库厂商遵循这些标准。比如 Oracle数据库就不支持READ UNCOMMITTED和 REPEATABLE READ的事务隔离级别。SQL标准定义的四...转载 2018-11-04 19:06:58 · 269 阅读 · 0 评论 -
【MySQL技术内幕】52-事物操作的统计
由于 InnoDB存储引擎是支持事务的,因此 InnoDB存储引擎的应用需要在考虑每秒请求数(Question Per Second,QPS)的同时,应该关注每秒事务处理的能力(Transaction Per Second,TPS)。计算TPS的方法是(com_commit+com_rollback)/time。但是利用这种方法进行计算的前提是:所有的事务必须都是显式提交的,如果存在隐式地提交和...转载 2018-11-04 16:12:24 · 235 阅读 · 0 评论 -
【MySQL技术内幕】51-隐式提交的SQL语句
以下这些SQL语句会产生一个隐式的提交操作,即执行完这些语句后,会有一个隐式的 COMMIT操作DDL语句: ALTER DATABASEUPGRADE DATA DIRECTORY NAME,ALTER EVENT, ALTER PROCEDURE, ALTER TABLE, ALTER VIEW,CREATE DATABASE, CREATE EVENT, CREATE INDEX, CR...转载 2018-11-04 01:13:55 · 635 阅读 · 0 评论 -
【MySQL技术内幕】50-事务控制语句
在 MySQL命令行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句后就会马上执行 COMMIT操作。因此要显式地开启一个事务需使用命令BEGIN、 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,禁用当前会话的自动提交。每个数据库厂商自动提交的设置都不相同,每个DBA或开发人员需要非常明白这一点,这对之后的SQL编程会有非凡的意义,...转载 2018-11-04 01:06:07 · 297 阅读 · 0 评论 -
【MySQL技术内幕】49-事务的实现之group commit
若事务为非只读事务,则每次事务提交时需要进行一次 fsync操作,以此保证重做日志都已经写入磁盘。当数据库发生宕机时,可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能,然而磁盘的fyne性能是有限的。为了提高磁盘 fsync的效率,当前数据库都提供了 group commit的功能,即一次 fsync可以刷新确保多个事务日志被写入文件。对于 InnoDB存储引擎来说,事务提交时会进行两...转载 2018-11-04 00:23:14 · 1306 阅读 · 0 评论 -
【MySQL技术内幕】48-事务的实现之purge
delete和 update操作可能并不直接删除原有的数据。例如,对上一小节所产生的表t执行如下的SQL语句:DELETE FROM t WHERE a=1;表t上列a有聚集索引,列b上有辅助索引。对于上述的 delete操作,通过前面关于undo log的介绍已经知道仅是将主键列等于1的记录delete flag设置为1,记录并没有被删除,即记录还是存在于B+树中。其次,对辅助索引上a等于1,...转载 2018-11-04 00:02:18 · 1141 阅读 · 0 评论 -
【MySQL技术内幕】47-事务的实现之undo log
1、基本概念重做日志记录了事务的行为,可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作,这时就需要undo。因此在对数据库进行修改时, InnoDB存储引擎不但会产生redo,还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了,又或者用户用一条 ROLLBACK语句请求回滚,就可以利用这些undo信息将数据回滚到修改之前的样子。redo存放在重做日志...转载 2018-11-01 00:09:05 · 1676 阅读 · 0 评论 -
【MySQL技术内幕】46-事务的实现之redo log
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志,用来保证事务的原子性和持久性。undo log用来保证事务的一致性。有的DBA或许会认为undo是redo的逆过程,其实不然。redo和undo的作用都可以视为是一种恢复操作,redo恢复提交事务修改的页操作,而undo回滚行记录到某个特定版本。因此两者记录的内容不同,...转载 2018-10-28 23:39:51 · 1232 阅读 · 2 评论 -
【MySQL技术内幕】45-认识事务
1、概述事务可由一条非常简单的SQL语句组成,也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作,要么都做修改,要么都不做,这就是事务的目的,也是事务模型区别与文件系统的重要特征之一。理论上说,事务有着极其严格的定义,它必须同时满足四个特性,通常所说的事务的ACID特性。值得注意的是,虽然理论上定义了严格的事务要求,但是数据库厂商出于各种...转载 2018-10-28 11:04:31 · 272 阅读 · 0 评论 -
【MySQL技术内幕】44-锁升级
锁升级(Lock Escalation)是指将当前锁的粒度降低。举例来说,数据库可以把一个表的1000个行锁升级为一个页锁,或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源,而且想避免锁的开销,那数据库中会频繁出现锁升级现象。Microsoft SQL Server数据库的设计认为锁是一种稀有的资源,在适合的时候会自动地将行、键或分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源...转载 2018-10-28 11:04:23 · 1136 阅读 · 0 评论 -
【MySQL技术内幕】43-死锁
1、死锁的概念死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。若无外力作用,事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待,将任何的等待都转化为回滚,并且事务重新开始。毫无疑问,这的确可以避免死锁问题的产生。然而在线上环境中,这可能导致并发性能的下降,甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重,因为这很难被发现并且浪费资源。...转载 2018-10-28 11:04:16 · 569 阅读 · 0 评论 -
【MySQL技术内幕】42-阻塞
因为不同锁之间的兼容性关系,在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源,这就是阻塞。阻塞并不是一件坏事,其是为了确保事务可以并发且正常地运行。在 InnoDB存储引擎中,参数innodb_lock_wait_timeout用来控制等待的时间(默认是50秒), innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是OF...转载 2018-10-28 11:04:05 · 148 阅读 · 0 评论 -
【MySQL技术内幕】41-锁问题
通过锁定机制可以实现事务的隔离性要求,使得事务可以并发地工作。锁提高了并发,但是却会带来潜在的问题。不过好在因为事务隔离性的要求,锁只会带来三种问题,如果可以防止这三种情况的发生,那将不会产生并发异常。1、脏读在理解脏读(Dirty Read)之前,需要理解脏数据的概念。但是脏数据和之前所介绍的脏页完全是两种不同的概念。脏页指的是在缓冲池中已经被修改的页,但是还没有刷新到磁盘中,即数据库实...转载 2018-10-27 22:54:23 · 216 阅读 · 0 评论 -
【MySQL技术内幕】40-锁的算法
1、行锁的3种算法InnoDB存储引擎有3种行锁的算法,其分别是Record Lock:单个行记录上的锁 Gap Lock:间隙锁,锁定一个范围,但不包含记录本身 Next-Key Lock:Gap Lock+ Record Lock,锁定一个范围,并且锁定记录本身Record Lock总是会去锁住索引记录,如果 InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时Inn...转载 2018-10-27 22:19:46 · 440 阅读 · 0 评论 -
【MySQL技术内幕】39-外键和锁
外键主要用于引用完整性的约束检查。在 InnoDB存储引擎中,对于一个外键列,如果没有显式地对这个列加索引, InnoDB存储引擎自动对其加一个索引,因为这样可以避免表锁——这比 Oracle数据库做得好, Oracle数据库不会自动添加索引,用户必须自已手动添加,这也导致了 Oracle数据库中可能产生死锁。对于外键值的插入或更新,首先需要查询父表中的记录,即 SELECT父表。但是对于父表的...转载 2018-10-27 21:19:44 · 335 阅读 · 0 评论 -
【MySQL技术内幕】38-自增长与锁
自增长在数据库中是非常常见的一种属性,也是很多DBA或开发人员首选的主键方式。在 InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,执行如下的语句来得到计数器的值:SELECT MAX (auto_inc_col) FROM t FOR UPDATE;...转载 2018-10-27 21:09:35 · 212 阅读 · 0 评论 -
【MySQL技术内幕】37-一致性锁定读
在默认配置下,即事务的隔离级别为 REPEATABLE READ模式下, InnoDB存储引擎的 SELECT操作使用一致性非锁定读。但是在某些情况下,用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句,即使是对于SELECT的只读操作。 InnoDB存储引擎对于 SELECT语句支持两种一致性的锁定读(locking read)操作:SELECT… FO...转载 2018-10-27 20:42:24 · 245 阅读 · 0 评论 -
【MySQL技术内幕】36-一致性的非锁定读
一致性的非锁定读( consistent nonlocking read)是指 InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE或 UPDATE操作,这时读取操作不会因此去等待行上锁的释放。相反地, InnoDB存储引擎会去读取行的一个快照数据。如图所示。图直观地展现了 InnoDB存储引擎一致性...转载 2018-10-27 20:35:35 · 271 阅读 · 0 评论 -
【MySQL技术内幕】35-锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁:共享锁( S Lock),允许事务读一行数据。 排他锁( X LocK),允许事务删除或更新一行数据。如果一个事务T1已经获得了行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,称这种情况为锁兼容( Lock Compatible)。但若有其他的事务T3想获得行r的排他锁,则其必须等待事务T1、T2释放...转载 2018-10-27 18:31:45 · 305 阅读 · 0 评论 -
【MySQL技术内幕】34-lock与latch
这里还要区分锁中容易令人混淆的概念lock与 latch。在数据库中,lock与 latch都可以被称为“锁”。但是两者有着截然不同的含义,本章主要关注的是lock。latch一般称为闩锁(轻量级的锁),因为其要求锁定的时间必须非常短。若持续的时间长,则应用的性能会非常差。在 InnoDB存储引擎中, latch又可以分为 mutex(互斥量)和 relock(读写锁)。其目的是用来保证并发线...转载 2018-10-27 12:09:14 · 1484 阅读 · 0 评论 -
【MySQL技术内幕】33-什么是锁
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。 InnoDB存储引擎会在行级别上对表数据上锁,这固然不错。不过 InnoDB存储引擎也会在数据库内部其他多个地方使用锁,从而允许对多种不同资源提供并发访问。例如,操作缓冲池中的LRU列表,删除、添加、移动LRU列表中的元素,为了保证一致性,必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完...转载 2018-10-27 11:22:20 · 204 阅读 · 0 评论 -
【MySQL技术内幕】32-全文检索
1、概述全文检索( Full-Text Search)是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息,也可以进行各种统计和分析。在之前的 MySQL数据库中, InnoDB存储引擎并不支持全文检索技术。大多数的用户转向 MyISAM存储引擎,这可能需要进行表的拆分,并将需要进行全文检索的数据存储为 MyISAM表。这样...转载 2018-10-27 00:25:00 · 471 阅读 · 0 评论 -
【MySQL技术内幕】31-InnoDB存储引擎中的哈希算法
InnodB存储引擎使用哈希算法来对字典进行查找,其冲突机制采用链表方式,哈希函数采用除法散列方式。对于缓冲池页的哈希表来说,在缓冲池中的Page页都有争chain指针,它指向相同哈希函数值的页。而对于除法散列,m的取值为略大于2倍的缓冲池页数量的质数。例如:当前参数 innodb buffer pool size的大小为10M,则共有640个16KB的页。对于缓冲池页内存的哈希表来说,需要分配6...转载 2018-10-26 22:18:36 · 341 阅读 · 0 评论 -
【MySQL技术内幕】30-B+树索引的使用
1、不同应用中B+树索引的使用在了解了B+树索引的本质和实现后,下一个需要考虑的问题是怎样正确地使用B+树索引,这不是一个简单的问题。这里所总结的可能并不适用于所有的应用场合。我所能做的只是概括一个大概的方向。在实际的生产环境使用中,每个DBA和开发人员,还是需要根据自己的具体生产环境来使用索引,并观察索引使用的情况,判断是否需要添加索引。不要盲从任何人给你的经验意见, Think diffe...转载 2018-10-26 18:32:05 · 297 阅读 · 0 评论 -
【MySQL技术内幕】29-Cardinality值
1、什么是 Cardinality并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般的经验是,在访问表中很少一部分时使用B+树索引才有意义。对于性别字段地区字段、类型字段,它们可取值的范围很小,称为低选择性。如:SELECTFROM student Where sex='M';按性别进行查询时,可取值的范围一般只有"M、F"。因此上述SQL语句得到的结...转载 2018-10-24 21:28:37 · 1158 阅读 · 0 评论 -
【MySQL技术内幕】28-树索引的管理
1、索引管理索引的创建和删除可以通过两种方法,一种是 ALTER TABLE,另一种是 CREATE/DROP INDEX。通过 ALTER TABLE创建索引的语法为:CREATE/ DROP INDEX的语法同样很简单:用户可以设置对整个列的数据进行索引,也可以只索引一个列的开头部分数据,如alter table t add key ids_b (b(100));若用户想...转载 2018-10-22 15:32:44 · 247 阅读 · 0 评论 -
【MySQL技术内幕】27-B+树索引的分裂
B+树索引页的分裂并不总是从页的中间记录开始,这样可能会导致页空间的浪费。原因参考https://blog.csdn.net/shenchaohao12321/article/details/83243314InnoDB存储引擎的 Page Header中有以下几个部分用来保存插入的顺序信息:PAGE_LAST_INSERT PAGE_DIRECTION PAGE_N_DIRECTION...转载 2018-10-21 18:07:24 · 2651 阅读 · 0 评论 -
【MySQL技术内幕】26-聚集索引与辅助索引
聚集索引Innodb中每张表都会有一个聚集索引,其行记录存在该索引的叶子节点上。 叶子节点通过双向链表链接,按照主键的顺序排序 页中的记录也是双向链表进行维护,物理上可以不按照顺序存储。 所有索引只能定位到页,不能通过索引定位到具体的行,到页后通过Page Directory确定行。聚集索引的另一个好处是,它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。...转载 2018-10-21 17:55:14 · 1359 阅读 · 0 评论 -
【MySQL技术内幕】25-B+树的操作
B+树中,所有记录节点都按照键值的大小顺序放在同一层叶子节点,各个叶子节点指针进行连接。图中指针是单向的,但是书上的图是双向的,而且旋转应该也是双向才能完成)B+树插入处理Leaf Page满 Index Page 满 操作 No No 直接插入到叶子节点 Yes No 1. 拆分Leaf Page 2.将中间的节点值加入到I...转载 2018-10-21 17:38:27 · 266 阅读 · 0 评论