网站标准规范建设,怎么做领券网站,七牛云wordpress+代码,短视频平台开发第1章 MySQL体系结构和存储引擎1.1数据库和实例数据库#xff1a;物理操作系统文件或其他形式文件类型的集合。实例#xff1a;MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行 的后台线程所共享。数据库实例才是真正用于操作数据库文件的。启动MySQL数据库… 第1章 MySQL体系结构和存储引擎1.1数据库和实例数据库物理操作系统文件或其他形式文件类型的集合。实例MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行 的后台线程所共享。数据库实例才是真正用于操作数据库文件的。启动MySQL数据库实例并通过命令ps观察MySQL数据库启动后的进程情况./mysqld_safeps -ef I grep mysqld读取配置文件顺序以读取到的最后一个配置文件中的参数为准mysql --help | grep my.cnf #/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf1.2 MySQL体系结构从概念上来说数据库是文件的集合是依照某种数据模型组织起来并存放于二级存储器中的数据集合数据库实例是程序是位 于用户与操作系统之间的一层数据管理软件用户对数据库数据的任何操作包括数据 库定义、数据査询、数据维护、数据库运行控制等都是在数据库实例下进行的应用程序只有通过数据库实例才能和数据库打交道。MySQL数据库的体系结构。MySQL由以下几部分组成:连接池组件管理服务和工具组件SQL接口组件査询分析器组件优化器组件缓冲(Cache)组件插件式存储引擎物理文件MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。存储引擎是基于表的而不是数据库。1.3 MySQL存储引擎MySQL数据库独有的插件式体系结构。存储引擎的好处是每个存储引擎都有各自的特点能够根据具体的应用建立不同存储引擎表。InnoDB存储引擎InnoDB存储引擎支持事务其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键并支持类似于Oracle的非锁定读即默认读取操作不会产生锁。InnoDB通过使用多版本并发控制(MVCC)来获得高并发性并且实现了 SQL 标准的4种隔离级别默认为REPEATABLE级别。同时使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。除此之外InnoDB储存引擎还提供了插入缓冲(insert buffer)二次写(double write)、自适应哈希索引(adaptive hash index).预读(read ahead)等高性能和高可用的功能。对于表中数据的存储InnoDB存储引擎采用了聚集(clustered)的方式因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键InnoDB存 储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。MylSAM存储引擎MylSAM存储引擎不支持事务、表锁设计支持全文索引主要面向一些OLAP 数据库应用。在MySQL 5.5.8版本之前MylSAM存储引擎是默认的存储引擎(除 Windows版本外)。MylSAM存储引擎的缓冲池只缓存(cache)索引文件而不缓冲数据文件。MylSAM存储引擎表由MYD和MYI组成MYD用来存放数据文件MYI用来存 放索引文件。1.4各存储引擎之间的比较常用MySQL存储引擎之间的不同之处包括存储容量的限制、事务支持、锁的粒度、MVCC支持、支持的索引、备份和复制等。查看当前使用的MySQL数据库所支持的存储引擎SHOW ENGINES\G示例数据库来简单显示各存储引擎之间的不同CREATE TABLE mytest EngineMyISAM AS SELECT * FROM A;ALTER TABLE mytest EngineInnoDB;ALTER TABLE mytest EngineARCHIVE;从表的大小方面简单地揭示了各存储引擎的不同InnoDBMyISAMARCHIVE第2章InnoDB存储引擎InnoDB是事务安全的MySQL存储引擎。2.1 InnoDB存储引擎概述MySQL 5.5版本开始是默认的表存储引擎。支持ACID事务其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读同时被设计用来最有效地利用以及使用内存和CPU。2.3 InnoDB体系架构InnoDB存储引擎有多个内存块可以认为这些内存块组成了一个大的内存池负责 如下工作维护所有进程/线程需要访问的多个内部数据结构。缓存磁盘上的数据方便快速地读取同时在对磁盘文件的数据修改之前在这里 缓存。重做日志(redo log)缓冲...后台线程的主要作用是负责刷新内存池中的数据保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件同时保证在数据库发生异常的情 况下InnoDB能恢复到正常运行状态。2.3.1后台线程InnoDB存储引擎是多线程的模型因此其后台有多个不同的后台线程负责处理不Master ThreadMaster Thread是一个非常核心的后台线程主要负责将缓冲池中的数据异步刷新到磁盘保证数据的一致性包括脏页的刷新、合并插入缓冲(INSERT BUFFER UNDO页的回收等IO Thread在InnoDB存储引擎中大量使用了 AIO (Async IO)来处理写IO请求这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(callback) 处理。分别是 write、read、insert buffer 和 log 10 thread分别使用 innodb_read_io_threads 和 innodb_write_io_threads 参数进行设置SHOW VARIABLES LIKE innodb_%io_threads\G观察 InnoDB 中的 IO ThreadSHOW ENGINE INNODB STATUS\GIO Thread 0 为 insert buffer threadIO Thread 1 为 log thread之后就是根据参数innodb_read_io_threads及innodb_write_io_threads来设置的读写线程并且读线 程的ID总是小于写线程。Purge ThreadPurgeThread来回收已经使用并分配的undo页purge操作可以独立到单独的线程中进行以此来减轻Master Thread的工作。从InnoDB 1.2版本开始InnoDB支持多个Purge Thread,可以加快undo页的回收。同时由于Purge Thread需要离散地读取undo页这样也褪更进 一步利用磁盘的随机读取性能。如用户可以设置4个Purge ThreadSHOW VARIABLES LIKE innodb_purge_threads\GPage Cleaner Thread作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作 及对于用户査询线程的阻塞进一步提高InnoDB存储引擎的性能。2.3.2内存缓冲池InnoDB存储引擎是基于磁盘存储的并将其中的记录按照页的方式进行管理。基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。缓冲池简单来说就是一块内存区域通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作首先将从磁盘读到的页存放在缓冲池中 这个过程称为将页FIX”在缓冲池中。下一次再读相同的页时首先判断该页是否在缓冲池中。若在缓冲池中称该页在缓冲池中被命中直接读取该页。否则读取磁盘 上的页。对于数据库中页的修改操作则首先修改在缓冲池中的页然后再以一定的频率刷新到磁盘上。这里需要注意的是页从缓冲池刷新回磁盘的操作并不是在每次页发生变更时触发而是通过一种称为Checkpoint的机制刷新回磁盘。缓冲池的配置通过参数innodb_buffer_pool_size来设置。SHOW VARIABLES LIKE innodb_buffer_pool_size \G缓冲池中缓存的数据页类型有索引页、数据页、undo页、插入缓冲 (insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB 存储的锁信息(lock info)、数据字典信息(data dictionary)等。不能简单地认为缓冲池只是缓存索引页和 数据页它们只是占缓冲池很大的一部分而已。InnoDB存储引擎中内存的结构情况。配置多个缓冲池实例。每个页根据哈希值平均分配到 不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争增加数据库的并发处理能力。SHOW VARIABLES LIKE innodb_buffer_pool_instances\G观察到每个缓冲池实例对象运行的状态SHOW ENGINE INNODB STATUS\GSELECT POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS\GLRU List、Free List 和 Flush List数据库中的缓冲池是通过LRU (Latest Recent Used)算法来进行管理的。即最频繁使用的页在LRU列表的前端而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时将首先释放LRU列表中尾端的页。在InnoDB存储引擎中缓冲池中页的大小默认为16KB,同样使用LRU算法对缓冲池进行管理。InnoDB的存储引擎中LRU列表中还加入了 midpoint位置。新读取到的页虽然是最新访问的页但并不是直接放入到LRU列表的首部而是放入到LRU列表的midpoint 位置。这个算法在InnoDB存储引擎下称为midpoint insertion strategy。在默认配置下该位置在LRU列表长度的5/8处。midpoint位置可由参数innodb_old_blocks_pct控制如SHOW VARIABLES LIKE innodb_old_blocks_pct\G默认值为37,表示新读取的页插入到LRU列表尾端的37%的位置(差不多3/8的位置)。在InnoDB存储引擎中把 midpoint之后的列表称为old列表之前的列表称为new列表。可以简单地理解为new 列表中的页都是最为活跃的热点数据。那为什么不采用朴素的LRU算法直接将读取的页放入到LRU列表的首部呢因为某些SQL操作可能会使缓冲池中的页被刷新出从而影响缓冲池的效率。常见扫描操作并不是活跃的热点数据。如果页被放入LRU列表的首部那么非常可能将所需要的热点数据页从LRU列表中移除。InnoDB存储引擎引入了另一个参数来进一步管理LRU列表表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。可以通过下面的方法尽可能使LRU列表中热点数据不被刷出。SHOW VARIABLES LIKE innodb_old_blocks_time\GSET GLOBAL innodb_old_blocks_time1000;####data or index scan operationSET GLOBAL innodb_old_blocks_time1000;如果用户预估自己活跃的热点数据不止63%。SHOW VARIABLES LIKE innodb_old_blocks_pct\GSET GLOBAL innodb_old_blocks_pct20;LRU列表用来管理已经读取的页但当数据库刚启动时LRU列表是空的即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时首先从Free列表中查找是否有可用的空闲页若有则将该页从Free列表中删除放入到LRU列表中。否则根据LRU算法淘汰LRU列表末尾的页将该内存空间分配给新的页。当页从 LRU列表的old部分加入到new部分时称此时发生的操作为page made young,而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为 page not made young通过命令 来观察 LRU 列 表及Free列表的使用情况和运行状态。SHOW ENGINE INNODB STATUS\G当前Buffer 8191个页。Free buffers表示当前Free列表中页的数量Database pages表示LRU列表中页的数量。可能的情况是Free buffers与 Database pages的数量之和不等于Buffer pool size因为缓冲池 中的页还可能会被分配给自适应哈希索引、Lock信息、Insert Buffer等页而这部分页 不需要LRU算法进行维护因此不存在于LRU列表中。pages made young显示 LRU列表中页移到前端的次数因为该服务器在运行阶段没改innodb_old_blocks_time 的值因此 not young 为 0。youngs/snon-youngs/s 表示每秒这两类操作的次数。Buffer pool hit rate,表示缓冲池的命中率100%说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Buffer pool hit rate的值小于95%这种情况需要观察是否是由于全表扫描引起的LRU列表被污染的问题。还可以通过表INNODB_BUFFER_POOL_STATS来观察缓冲池的运行状态SELECT POOL_ID, HIT_RATE,PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNGFROM information_schema.INNODB_BUFFER_POOL_STATS\GInnoDB存储引擎从1.0.x版本开始支持压缩页的功能即将原本16KB的页压缩 为1KB、2KB、4KB和8KB。而由于页的大小发生了变化LRU列表也有了些许的改变。对于非16KB的页是通过unzip_LRU列表进行管理的。LRU中的页包含了 unzip_LRU列表中的页。在LRU列表中的页被修改后称该页为脏页(dirty page),即缓冲池中的页和磁盘 上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘, 而Flush列表中的页即为脏页列表。需要注意的是脏页既存在于LRU列表中也存在 于Flush列表中。LRU列表用来管理缓冲池中页的可用性Flush列表用来管理将页刷新 回磁盘二者互不影响。前面例子中Modified db pages 就显示了脏页的数量。脏页同样存在于LRU列表中SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION 0;重做日志缓冲内存区域除了有缓冲池外还有重做日志缓冲(redo log buffer)。InnoDB存储引撃首先将重做日志信息先放入到这个缓冲区然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。SHOW VARIABLES LIKE innodb_log_buffer_size\G重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。Master Thread每一秒将重做日志缓冲刷新到重做日志文件每个事务提交时会将重做日志缓冲刷新到重做日志文件当重做日志缓冲池剩余空间小于1/2时重做日志缓冲刷新到重做日志文件。额外的内存池在InnoDB存储引擎中对内存的管理是通过一种称为内存堆(heap) 的方式进行的。在对一些数据结构本身的内存进行分配时需要从额外的内存池中进行申请2.4 Checkpoint 技术缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。将脏页从缓冲池刷新到磁盘。若每次一个页发生变化就刷新到磁盘开销非常大。若热点数据集中在某几个页中那么数据库的性能将变得非常差。若在从缓冲池将页的新版本刷新到磁盘时发生了宕机发生数据丢失的问题。当前事务数据库系统普遍都采用了 Write Ahead Log策略即当事务提交时先写重做日志再修改页。当由于发生宕机而导致数据丢失时通过重做日志来 完成数据的恢复。这也是事务ACID中D (持久性)的要求。Checkpoint的目的缩短数据库的恢复时间缓冲池不够用时将脏页刷新到磁盘重做日志不可用时刷新脏页。两种CheckpointShaip Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘这是默认的工作方式Fuzzy Checkpoint进行页的刷新即只刷新一 部分脏页而不是刷新所有的脏页回磁盘。脏页的数量太多当缓冲池中脏页的数量占据75%时强制进行Checkpoint。SHOW VARIABLES LIKE innodb_max_dirty_pages_pct\G2.6 InnoDB关键特性InnoDB存储引擎的关键特性包括插入缓冲(Insert Buffer)两次写(DoubleWrite)自适应哈希索引(Adaptive Hash Index)异步 IO (Async IO)刷新邻接页(Flush Neighbor Page)2.6.1插入缓冲Insert BufferInsert Buffer和数据页一样也是物理页的一个组成部分。在InnoDB存储引擎中主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此插入聚集索引(Primary Key) 一般是顺序的不需要随机读取另一个页中的记录。因此速度是非常快的。若主键类是UUID这样的类在进行插入操作时数据页的存放还是按主键进行顺序存放的但是对于非聚集索引叶子节点的插入不再是顺序的了这时就离散地访问非聚集索引页由于随机读取的存在而导致了插入操作 性能下降。因为B树的特性决定了非聚集索引插入的离散性。Insert Buffer,对于非聚集索引的插入或更新操作 不是每一次直接插入到索引页中而是先判断插入的非聚集索引页是否在缓冲池中若在则直接插入若不在则先放入到一个Insert Buffer对象中好似欺骗。数据库这 个非聚集的索引已经插到叶子节点而实际并没有只是存放在另一个位置。然后再以 一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge (合并)操作这时通 常能将多个插入合并到一个操作中(因为在一个索引页中)这就大大提高了对于非聚 集索引插入的性能。然而Insert Buffer的使用需要同时满足以下两个条件索引是辅助索引(secondary index)索引不是唯一(unique)的。当满足以上两个条件时InnoDB存储引擎会使用Insert Buffer,这样就能提高插入操作的性能了。Change BufferInnoDB存储引擎可以对DML操作——INSERT, DELETE, UPDATE都进行缓冲将记录标记为已删除;真正将记录删除。Insert Buffer的内部实现Insert Buffer是一棵B树因此其也由叶节点和非叶节点组成。非叶节点存放的是査询的search key (键值)由space-marker-offset组成space表示待插入记录所在表的表空间id,每个表有一个唯一的space id,可以通过space id査询得知是哪张表。space占用4字节。marker占用1字节用来兼容老版本的Insert Bufferoffset 表示页所在的偏移量占用4字节。当一个辅助索引要插入到页(space, offset)时将这条记录插入到Insert Buffer B树的叶子节点中。由space-marker-offset-matadata组成,前面一样matadata会记录进入 Insert Buffer的顺序。第5列开始就是实际插入记录的各个字段了。因此较之原插入记录Insert Buffer B树的叶子节点记录需要额外13字节的开销。Merge Insert BufferInsert/Change Buffer是一棵B树。合并(merge)Insert Buffer中的记录到真正的辅助索引中2.6.2两次写doublewrite (两次写)带给InnoDB存储引擎的是数据页的可靠性。当发生数据库宕机时可能InnoDB存储引擎正在写入某个页到表中而这个页只写了一部分发生了宕机导致数据丢失的情况。可以通过重做日志进行恢复。这是一 个办法。但是必须清楚地认识到重做日志中记录的是对页的物理操作如偏移量800, 写-aaaa-记录。如果这个页本身已经发生了损坏再对其进行重做是没有意义的。这 就是说在应用(apply)重做日志前用户需要一个页的副本当写入失效发生时先 通过页的副本来还原该页再进行重做这就是doublewrite。doublewrite由两部分组成一部分是内存中的doublewrite buffer,大小为2MB,另一部分是物理磁盘上共享表空间中连续的128个页即2个区(extent),大小同样为 2M在对缓冲池的脏页进行刷新时并不直接写磁盘而是会通过memcpy函数将 脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次每次 1MB顺序地写入共享表空间的物理磁盘上然后马上调用fsync函数同步磁盘避免 缓冲写带来的问题。在这个过程中因为doublewrite页是连续的因此这个过程是顺序2.6.3自适应哈希索引而B树的查找次数取决于B树的高度在生产环境中B树的高度一般为3〜4层故需要3~4次的査询。InnoDB存储引擎会监控对表上各索引页的査询。如果观察到建立哈希索引可以带 来速度提升则建立哈希索引称之为自适应哈希索引(Adaptive Hash Index, AHI)。2.6.4 异步为了提高磁盘操作性能当前的数据库系统都采用异步IO (Asynchronous IO, AI0)的方式来处理磁盘操作。2.6.5刷新邻接页InnoDB存储引擎还提供了 Flush Neighbor Page (刷新邻接页)的特性。其工作原理 为当刷新一个脏页时InnoDB存储引擎会检测该页所在区(extent)的所有页如果是脏页那么一起进行刷新。2.7启动、关闭与恢复在关闭时参数innodb_fast_shutdown影响着InnoDB的行为0表示在MySQL数据库关闭时InnoDB需要完成所有的fiill purge和merge insert buffer,并且将所有的脏页刷新回磁盘。这需要一些时间有时甚至需要几 个小时来完成。如果在进行InnoDB升级时必须将这个参数调为0,然后再关闭数据库。1是参数innodb fast shutdown的默认值表示不需要完成上述的fiill purge和 merge insert buffer操作但是在缓冲池中的一些数据脏页还是会刷新回磁盘。2表示不完成full purge和merge insert buffer操作也不将缓冲池中的数据脏页写回磁盘而是将日志都写入日志文件。这样不会有任何事务的丢失但是下次 MySQL数据库启动时会进行恢复操作(recovery)。第3章 文件参数文件告诉MySQL实例启动时在哪里可以找到数据库文件并且指定某些初始化参数日志文件用来记录MySQL实例对某种条件做出响应时写入的文件如错误日志文件、二进制日志文件、慢査询日志文件、查询日志文件等。socket文件当用UNIX域套接字方式进行连接时需要的文件。pid文件MySQL实例的进程ID文件。MySQL表结构文件用来存放MySQL表结构定义文件。存储引擎文件因为MySQL表存储引擎的关系每个存储引擎都会有自己的文 件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。3.1参数文件启动时数据库会先去读一个配置参数文件用来寻找数据库的各种文件所在位置以及指定某些初始化参数mysql --help | grep my.cnf3.1.2参数类型MySQL数据库中的参数可以分为两类动态(dynamic)参数静态(static)参数全局/当前会话SELECT session.read_buffer_size\GSELECT global.read_buffer_size\G3.2日志文件常见的日志文件有错误日志(error log)二进制日志(binlog)慢査询日志(slow query log)査询日志(log)对运行状态进行诊断从而更好地进行数据库层面的优化。3.2.1错误日志错误日志文件对MySQL的启动、运行、关闭过程进行了记录。不仅记录了所有的错误信息也记录一些警告信息或正确的信息。SHOW VARIABLES LIKE log_error\Gtail -n 50 /var/lib/mysql/node3.err3.2.2慢查询日志慢査询日志定位可能存在问题的SQL语句从而进行SQL语句层面的优化。可以在MySQL启动时设一个阈值将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中,默认关闭SHOW VARIABLES LIKE long_query_time\GSHOW VARIABLES LIKE log_slow_queries\G如果运行的SQL 语句没有使用索引则MySQL数据库同样会将这条SQL语句记录到慢査询日志文件。SHOW VARIABLES LIKE log_queries_not_using_indexes\GDBA可以通过慢查询日志来找出有问题的SQL语句对其进行优化。提供的mysqldumpslow命令分析该文件mysqldumpslow xxx-190-slow.log如果用户希望得到执行时间最长的10条SQL语句可以运行如下命令mysqldumpslow -s al -n 10 david.log将慢査询的日志记录放入一张表中査询更加方便和直观。select * from mysql.slow_log;参数long_query_io将超过指定逻辑10次数的SQL语句记录到 slow log中。表示对于逻辑读取次数大于100的SQL语句记录到 slow log中。3.2.3查询日志査询日志记录了所有对MySQL数据库请求的信息无论这些请求是否得到了正确的执行。默认文件名为主机名.log,如査看一个査询日志3.2.4二进制日志二进制日志(binary log)记录了对MySQL数据库执行更改的所有操作但是不包括SELECT和SHOW这类操作SHOW MASTER STATUS\GSHOW BINLOG EVENTS IN mysql-bin.000002\G二进制日志主要有以下几种作用。恢复、复制、审计show variables like datadir;mysql-bin.000001即为二进制日志文件mysql-bin.index为二进制的索引文件来存过往产生的二进制日志序号以下配置文件的参数影响着二进制日志记录的信息和行为max_binlog_size指定了单个二进制日志文件的最大值如果超过该值则产生 新的二进制日志文件后缀名1并记录到.index文件。当使用事务的表存储引擎时所有未提交的二进制日志会被记录到一个缓存中去等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件而该缓冲的大小由binlog_cache_size决定默认大小为32K。在默认情况下二进制日志并不是在每次写的时候同步到磁盘。sync_binlog [N]表示每写缓冲 多少次就同步到磁盘。如果将N设为1,即sync_binlog 1表示采用同步写磁盘的方式来 写二进制日志参数binlog-do-db和binlog-ignore-db表示需要写入或忽略写入哪些库的日志。默认为空表示需要同步所有库的日志到二进制日志。binlog_format参数十分重要它影响了记录二进制日志的格式。要査看二进制日志文件的内容通过MySQL提供的工具mysqlbinlogo对于STATEMENT格式的二进制日志文件在 使用mysqlbinlog后看到的就是执行的逻辑SQL语句mysqlbinlog --start-position203 test.0000043.3套接字文件在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式这种方式需要一个套接字(socket)文件。套接字文件可由参数socket控制。一般在/tmp 目录下名为mysql.sock3.4 pid文件当MySQL实例启动时会将自己的进程ID写入一个文件中该文件即为pid文 件。该文件可由参数pid_file控制默认位于数据库目录下文件名为主机名.pidshow variables like pid_file\G3.5表结构定义文件MySQL数据的存储是根据表进行 的每个表都会有与之对应的文件。但不论表采用何种存储引擎MySQL都有一个以 frm为后缀名的文件这个文件记录了该表的表结构定义。frm还用来存放视图的定义如用户创建了一个v_a视图那么对应地会产生v_a.frm文件3.6 InnoDB存储引擎文件与InnoDB存储引擎密切相关的文件这些文件包括重做日志文件、表空间文件。表空间文件InnoDB采用将存储的数据按表空间(tablespace)进行存放的设计。在默认配置 下会有一个初始大小为10MB,名为ibdatal的文件。该文件就是默认的表空间文件 (tablespace file)设置innodb_data_file_path参数后所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_Ele_per_table,则用户可以将每个基于 InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为表名.ibd。通过 这样的方式用户不用将所有数据都存放于默认的表空间中。下面这台MySQL数据库 服务器设置了 innodb_file_per_table,故可以观察到SHOW VARIABLES LIKE innodb_file_per_table\G这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息其余信息还是存放在默认的表空冋中。3.6.2重做日志文件在默认情况下在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_ logfile1的文件。它们记录了对于InnoDB存储引擎的事务日志。主机掉电导致实例失败InnoDB存储引擎会使用重做日志恢复到掉电前的时刻以此来保证数据的完整性。每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组下至少有 2个重做日志文件如默认的ibJogfile0和ibJogfile1为了得到更高的可靠性innodb_log_file_size指定每个重做日志文件的大小。1.2.x版本将该限制扩大为了 512Ginnodb_log_files_in_group指定了日志文件组中重做日志文件的数量默认为 2参数innodb_mirrored_log_groups指定了日志镜像文件组的数量默认为1,表示只 有一个日志文件组没有镜像innodb_log_group_home_dir指定了日志文 件组所在路径默认为./,表示在MySQL数据库的数据目录下innodb_flush_log_at_trx_commit设置为1因此为了保证事务的ACID中的持久性也就是每当有事务提交时就必须确保事务都已经写入重做日志文件第4章 表4.1索引组织表在InnoDB存储引擎中表都是根据主键顺序组织存放的这种存储方式的表称为索引组织表(index organized tabl每张表都有个主键 (Primary Key),如果在创建表时没有显式地定义主键则InnoDB存储引擎会按如下方 式选择或创建主键首先判断表中是否有非空的唯一索引(Unique NOT NULL),如果有则该列即为主键。如果不符合上述条件InnoDB存储引擎自动创建一个6字节大小的指针。当表中有多个非空唯一索引时InnoDB存储引擎将选择建表时第一个定义的非空唯 一索引为主键通过下面的SQL语句判断表的主键值SELECT a,b,c,_rowid FROM z;rowid可以显示表的主键rowid R能用于查看单个列为主键的情况如4.2 InnoDB逻辑存储结构从InnoDB存储引擎的逻辑存储结构看所有数据都被逻辑地存放在一个空间中称 之为表空间(tablespace)。表空间又由段(segment)区(extent)、页(page)组成。页在 一些文档中有时也称为块(block)4.2.1 表空间默认情况下InnoDB存储引擎有一个共享表空间ibdatal, 即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页其他类的数据如回滚(undo)信息插入缓冲 索引页、系统事务信息二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。 4.2.2 段表空间是由各个段组成的常见的段有数据段、索引段、回滚段等。因为前面已经介绍过了 InnoDB存储引擎表是索引组织的(index organized),因此数据即索引索引即数据。那么数据段即为B树的叶子节点(图4-1的Leaf node segment), 索引段即为B树的非索引节点(图4-1的Non-leaf node segment),回滚段较为特殊,后面单独介绍。4.2.3 区区是由连续页组成的空间在任何情况下每个区的大小都为1MB。为了保证区中页 的连续性InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下InnoDB存储引擎页的大小为16KB,即一个区中一共有64个连续的页。InnoDB 1.0.x版本开始引入压缩页即每个页的大小可以通过参数KEY_BLOCK_ SIZE设置为2K、4K、8K,因此每个区对应页的数量就应该为512、256、128。InnoDB 1.2.x版本新增了参数innodb_page_size.通过该参数可以将默认页的大小设置为4K、8K,但是页中的数据不是压缩。这时区中页的数量同样也为256、128。总之 不论页的大小怎么变化区的大小总是为IM.在每个段开始时先用32个页大小的碎片页(fragment page)来存放数据 在使用完这些页之后才是64个连续页的申请。这样做的目的是对于一些小表在开始时申请较少的空间节省磁盘容量的开销。4.2.4 页页是InnoDB 磁盘管理的最小单位。在InnoDB存储引擎中默认每个页的大小为16KB。而从 InnoDB 1.2.x版本开始可以通过参数innodb_page_size将页的大小设置为4K、8K、 16K。若设置完成则所有表中页的大小都为innodb_page_size,不可以对其再次进行修改。常见的页类型有数据页(B-treeNode)undo 页(undo Log Page)系统页(System Page)事务数据页(Transaction system Page)插入缓冲位图页(Insert Buffer Bitmap )插入缓冲空闲列表页(Insert Buffer Free List)未压缩的二进制大对象页(Uncompressed BLOB Page)压缩的二进制大对象页(compressed BLOB Page)4.2.5 行InnoDB存储引擎是面向列的(row-oriented),也就说数据是按行进行存放的。4.3 InnoDB行记录格式InnoDB存储引擎是以行的形式存储的。这意味着页中保存着表中一行行的数据。在InnoDB 1.0.x版本之前提供了 Compact和Redundant两种格式来存放行记录数据在MySQL 5.1版本中默认设置为Compact行格式。查看当前表使用的行格式SHOW TABLE STATUS like user\GCompressed 和 Dynamic 行记录格式InnoDB 1.0.x版本开始引入了新的文件格式称为Barracuda 文件格式。Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic。4.4 InnoDB数据页结构页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-treeNode的页存放的即是表中行的实际数据了。在这一节中我们将从底层具体地介绍InnoDB数据页的内部存储结构。4.4.1 File Header记录页的一些头信息共占用38字节。4.4.2 Page Header该部分用来记录数据页的状态信息由14个部分组成共占用56字节4.4.3 Infimum 和 Supremum Record每个数据页中有两个虚拟的行记录用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值Supremum指比任何可能大的值还要大的值。4.4.4 User Record 和 Free Space实际存储行记录的内容。再次强调InnoDB 存储引擎表总是B树索引组织的。Free Space很明显指的就是空闲空间同样也是个链表数据结构。4.4.5 Page Directory存放了记录的相对位置(存放的是页相对位置而不是偏移量)由于在InnoDB存储引擎中Page Direcotry是稀疏目录二叉査找的结果只是一个粗略的结果因此InnoDB存储引擎必须通过recorder header中的next_record来继续査找相关记录。需要牢记的是B树索引本身并不能找到具体的一条记录能找到只是该记录所在的页。数据库把页载入到内存然后通过Page Directory再进行二叉査找。只不过二 叉查找的时间复杂度很低同时在内存中的査找很快4.4.6 File Trailer为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏、机器关 机等)InnoDB存储引擎的页中设置了 File Trailer部分。保证页的完整性(not corrupted)。4.7视图在MySQL数据库中视图(View)是一个命名的虚表视图中的数据没有实际的物理存储。4.8分区表4.8.1分区概述分区功能并不是在存储引擎层完成的,分区的过程是将一个表或索引分解为多个更小、更可管理的部分。MySQL 数据库的分区是局部分区索引一个分区中既存放了数据又存放了索引。而全局分区是指数据存放在各个分区中但是所有数据的索引放在一个对象中。目前MySQL数据不支持全局分区。查看当前数据库是否启用了分区功能SHOW VARIABLES LIKE %partition%\G几种类型的分区。RANGE分区行数据基于属于一个给定连续区间的列值被放入分区。5.5 开始支持RANGE COLUMNS的分区。LIST分区和RANGE分区类型只是LIST分区面向的是离散的值。5.5 开始支持LIST COLUMNS的分区。HASH分区根据用户自定义的表达式的返回值来进行分区返回值不能为负数。KEY分区根据MySQL数据库提供的哈希函数来进行分区。不论创建何种类型的分区如果表中存在主键或唯一索引时分区列必须是唯一索 引的一个组成部分4.8.2分区类型PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (10),PARTITION p1 VALUES LESS THAN (20))PARTITION BY LIST(b)(PARTITION p0 VALUES IN (1,3,5,7,9),PARTITION p1 VALUES IN (0,2,4,6,8));PARTITION BY HASH (YEAR(b))PARTITIONS 4;PARTITION BY KEY (b)PARTITIONS 4;第5章索引与算法5.1 InnoDB存储引擎索引概述几种常见的索引B树索引全文索引哈希索引InnoDB存储引擎支持的哈希索引是自适应的InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引B树索引就是传统意义上的索引这是目前关系型数据库系统中最有效的索引。B树索引并不能找到一个给定键值的具体行。B树索引能找到的只是被査找数据行所在的页。然后数据库通过把页读入到内存再在内存中进行查找最后得到要查找的数据。5.4 B树索引B树索引的本质就是B树在数据库中的实现。但B索引在数据库中有一个特点是高扇出性因此在数据库中B 树的高度一般都在2-4层数据库中的B树索引可以分为聚集索引(clustered inex)和辅助索引(secondary index)。其内部都是B树的即高度平衡的叶子 节点存放着所有的数据。聚集索引与辅助索引不同的是叶子节点存放的是否是一整行的信息。5.4.1聚集索引InnoDB存储引擎表是索引组织表即表中数据按照主键顺序存 放。而聚集索引(clustered index)就是按照每张表的主键构造一棵B树同时叶子节点中存放的即为整张表的行记录数据也将聚集索引的叶子节点称为数据页。聚集索引 的这个特性决定了索引组织表中数据也是索引的一部分。同B树数据结构一样每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B树进行排序因此每张表只能拥有一个聚集索引。在多数情况下查询优化器倾向于采用聚集索引。因为聚集索引能够在B树索引 的叶子节点上直接找到数据。能够特别快 地访问针对范围值的查询。数据页上存放的是完整的每行的记录 而在非数据页的索引页中存放的仅仅是键值及指向数据页的偏移量而不是一个完整的行记录。聚集索引的存储并不是物理上连续的而是逻辑上连续的。这其中有两点一是页通过双向链表链接页按照主键的顺序排序另一点是每个页中的记录也是通过双向链表进行维护的物理存储上可以同样不按照主键存储。5.4.2 辅助索引对于辅助索引(Secondary Index,也称非聚集索引)叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外每个叶子节点中的索引行中还包含了一个书签 (bookmark),辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时InnoDB存储引擎会遍历辅助索引并通过页级别的指针获得指向主键索引的主键然后再通过主键索引来找到一个完整的行记录。5.4.4 B树索引的管理1 .索引管理索引的创建和删除可以通过两种方法一种是ALTER TABLE,另一种是CREATE/ DROP INDEX.ALTER TABLE user ADD KEY idx_id (id); SHOW INDEX FROM user;ALTER TABLE user DROP INDEX idx_id ;create index idx_id on user(id);drop index idx_id on user;#联合索引ALTER TABLE user ADD KEY idx_id_bc (id,bc);mysql show index from user\G*************************** 1. row *************************** Table: user Non_unique: 1 Key_name: idx_id Seq_in_index: 1 Column_name: id Collation: A #A或者nullB树总是A即排序的 Cardinality: 1 #表示索引中唯一值的数目的估值应尽可能接近1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 优化器会根据Cardinality值来判断是否使用这个索引 但他不是实时更新的,更新analyze table user\G5.5 Cardinality 值什么时候添加B树索 取值的范围很小称为低选择性这时添加B树索引是完全没有必要的。取值范围很广几乎没有重复即属于高选择性使用B树索引是最适合的Cardinality值非常关键表示索引中不重复记录数量的预估值。是一个预估值而不是一个准确值应尽可能地接近1。怎样来统计Cardinality信息的呢数据库对于Cardinality的统计都是通过采样(Sample)的方法来完成的。Cardinality统计信息的更新发生在两个操作中INSERT 和UPDATE。更新Cardinality 的策略为表中1/16的数据已发生过变化。stat_modified_counter 2000000000怎样来进行Cardinality信息的统计和更新操作同样是通过采样的方法。默认InnoDB存储引擎对8个叶子节点(Leaf Page)进 行采用。采样的过程如下取得B树索引中叶子节点的数量记为A。统计每个页不同记录的个数即为P1,…P8。给出 Cardinality 的预估值Cardinality (P1P2...P8) *A/8。5.6 B树索引的使用5.6.1不同应用中B树索引的使用B树索引建立后对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B树索引才是有意义的否则即使建立了优化器也可能选择不使用索引。5.6.2联合索引联合索引是指对表上的多个列进行索引。CREATE TABLE t (a INT,b INT,PRIMARY KEY (a),KEY idx_a_b (a,b))ENGINEINNODB联合索引也是一棵 B树不同的是联合索引的键值的数量不是1,而是大于等于2。和单个键值的B树 并没有什么不同键值都是排序的通过叶子节点可以逻辑上顺序地读出所有数据数据按(a, b)的顺序进行了存放。对于b列的査询使用不到(a, b)的索引。联合索引的第二个好处是已经对第二个键值进行了排序处理。索引本身在叶子节点已经排序了。5.6.3覆盖索引InnoDB存储引擎支持覆盖索引即从辅助索引中就可以得到査询的记录而不需要査询聚集索引中的记录。好处是辅助索引不包含整行记录的所有信息故其大小要远小于聚集索引因此可以减少大量 的IO操作。对于InnoDB存储引擎的辅助索引而言由于其包含了主键信息SELECT COUNT (*) FROM buy_logInnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引而辅助索引远小于聚集索引选择辅助索引可以减少IO操作故优化器的选择为userid索引而列Extra列的Using index就是代表了优化器进行了覆盖索引操作。5.6.4优化器选择不使用索引的情况优化器并没有选择索引去査找数据而是通过扫描聚集索引也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。用户要选取的数据是整行信息而辅助索引不能覆盖到我们要査询的信息因此在对OrderlD索引查询到指定数据后还需要一次书签访问来査找整行数据的信息。虽然OrderlD索引中数据是顺序存放的但是再一次进行书签查找的数据则是无序的因此变为了磁盘上的离散读操作。如果要求访问的数据量很小则优化器还是会选择辅助索引但是当访问的数据占整个表中数据的蛮大一部分时 (一般是20%左右)优化器会选择通过聚集索引来査找数据。因为顺序读要远远快于离散读。因此对于不能进行索引覆盖的情况优化器选择辅助索引的情况是通过辅助索引 查找的数据是少量的。也可以强制使用辅助索引select * from t force index(id) where id 10000 nad id 10200;5.6.5索引提示SELECT * FROM t USE INDEX(a) WHERE al AND b 2;5.6.6 Multi-Range Read 优化目的就是为了减少磁盘的随机访问并且将随机访问转化为较为顺序的数据访问适用于range, ref, eq_ref类型的查询。好处MRR使数据访问变得较为顺序减少缓冲池中页被替换的次数批量处理对键值的査询操作MRR的工作方式如下将査询得到的辅助索引键值存放于一个缓存中这时缓存中的数据是根据辅助索 引键值排序的。将缓存中的键值根据RowID进行排序。根据RowID的排序顺序来访问实际的数据文件。在列Extra会看见Using MRR选项。若启用了 Multi-RangeRead优化优化器会先将査询条件进行拆分然后再进行数据査询。优化器会将查询条件拆分为(1000, 1000), (1001, 1000),...最后再根据这些拆分出的条件进行数据的査询。5.6.7 Index Condition Pushdown (ICP)优化根据索引进行査询的优化方式。之前的MySQL进行索引査询时首先根据索引来查找记录然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown后MySQL数据库会在取出索引的同时 判断是否可以进行WHERE条件的过滤也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取(fetch),从而提高数 据库的整体性能。当优化器选择Index Condition Pushdown优化时 在Extra看到Using index condition提示。5.7哈希算法5.7.1 哈希表哈希表技术很好地解决了直接寻址遇到的问题但是会碰撞在数据库中一般采用最简单的碰撞解决技术这种技术被称为链接法 (chaining).在链接法中把散列到同一槽中的所有元素都放在一个链表中槽i中有一个指针它指向由所有散列到i的元素构成的链表的头如果不存在这样的元素 则i中为NULL5.7.2 InnoDB存储引擎中的哈希算法InnoDB存储引擎使用哈希算法来对字典进行查找其冲突机制采用链表方式哈希函数采用除法散列方式。对于缓冲池页的哈希表来说在缓冲池中的页都有一个chain指针它指向相同哈希函数值的页。而对于除法散列m的取值为略大于2倍的缓冲池页数量的质数。例如当前参数innodb_buffer_pool_size的大小为10M,则共有 640个16KB的页。对于缓冲池页内存的哈希表来说需要分配640X21280个槽但 是由于1280不是质数需要取比1280略大的一个质数应该是1399,所以在启动时会 分配1399个槽的哈希表用来哈希查询所在缓冲池中的页。那么InnoDB存储引擎的缓冲池对于其中的页是怎么进行査找的呢上面只是给出 了一般的算法怎么将要查找的页转换成自然数呢其实也很简单InnoDB存储引擎的表空间都有一个space_id,用户所要査询的应该 是某个表空间的某个连续16KB的页即偏移量offset。InnoDB存储引擎将space_id左 移 20 位然后加上这个 space_id 和 offset,即关键字 Kspace_i20space_idoffset, 然后通过除法散列到各个槽中去。5.7.3自适应哈希索引自适应哈希索引是数据库自身创建并使用的自适应哈希索引经哈希函数映射到一个哈希表中因此对于字典类型的査找非常快速但是对于范围査找就无能为力了。哈希索引只能用来搜索等值的査询innodb_adaptive_hash_index来禁用或启动此特性默认为开启。5.8全文检索5.8.1概述B树索引可以通过索引字段的前缀 (prefix)进行查找。比如 a like xxx%只要a添加了 B 树索引就能利用索引进行快速査询。但是 a like %xxx%就不行了根据B树索引的特性上述SQL语句即便添加了 B树索引也是需要进行索引的扫描来得到结果。从InnoDB 1.2.x版本开始InnoDB存储引擎开始支持全文检索其支持MylSAM存储引擎的全部功能SELECT * FROM fts_a WHERE body LIKE %Pease%;SELECT * FROM fts_a WHERE MATCH(body) AGAINST (Pease):type这列显示了 fulltext,即表示使用全文检索的倒排索引而key这 列显示了 idx_Rs,表示索引的名字。第六章 锁开发多用户、数据库驱动的应用时最大的一个难点是一方面要最大程度地利用数据库的并发访问另外一方面还要确保每个用户能以一致的方式读取和修改数据。为 此就有了锁(locking)的机制6.1什么是锁锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问InnoDB存储引擎会在行级别上对表数据上锁数据库系统使用锁是为了支持对共享资源进行并发访问 提供数据的完整性和一致性。对于MylSAM引擎其锁是表锁设计。并发情况下的读没有问题但是并发插入时的性能就要差一些了InnoDB存储引擎锁的实现提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销并可以同时得到并发性和一致性。6.2 lock 与 latchlatch一般称为闩锁(轻量级的锁)因为其要求锁定的时间必须非常短。若持续的时间长则应用的性能会非常差。在InnoDB存储引擎中latch又可以分为mutex (互斥量)和rwlock (读写锁)。其目的是用来保证并发线程操作临界资源的正确性并且通常没有死锁检测的机制。lock的对象是事务用来锁定的是数据库中的对象如表、页、行。并且一般lock 的对象仅在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同)。此外lock是有死锁机制的show engine innodb mutex;#latch6.3 InnoDB存储引擎中的锁6.3.1锁的类型InnoDB存储引擎实现了如下两种标准的行级锁共享锁(SLock),允许事务读一行数据。排他锁(XLock),允许事务删除或更新一行数据。如果一个事务T1已经获得了行r的共享锁那么另外的事务T2可以立即获得行r 的共享锁因为读取并没有改变行r的数据称这种情况为锁兼容(Lock Compatible)。但若有其他的事务T3想获得行r的排他锁则其必须等待事务Tl、T2释放行r上的共享锁——这种情况称为锁不兼容。S和X锁都是行锁兼容是指对同一记录(row)锁的兼容性情况。此外InnoDB存储引擎支持多粒度(granular)锁定这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作InnoDB存储引擎支持 一种额外的锁方式称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁6.3.2 一致性非锁定读一致性的非锁定读是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中 行的数据。如果读取的行正在执行DELETE或UPDATE操作这时读取操作不会因此去等待行上锁的释放。而是会去读取行的一个快照数据。快照数据是指该行的之前版本的数据该实现是通过undo段来完成。非锁定读机制极大地提高了数据库的并发性。这是默认的读取方式即读取不会占用和等待表上的锁。但是在不同事务隔离级别下读取的方式不同并不是在每个事务隔离级别下都是采用非锁定的一致性读。快照数据其实就是当前行数据之前的历史版本每行记录可能有多个版本。一个行记录可能有不止一个快照数据一般称这种技术为行多版本技术。由此带来的并发控制称之为多版本并发控制(Multi Version Concurrency Control. MVCC)。在事务隔离级别READ COMMITTED和REPEATABLE READ (默认事务隔离级别)下InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义却不相同。在READ COMMITTED事务隔离级别下对于快照数据非一致 性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别 下对于快照数据非一致性读总是读取事务开始时的行数据版本。6.3.3 一致性锁定读即事务的隔离级别为REPEATABLE READ模 式下InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句即使是对于SELECT的只读操作。持两种一致性的锁定读(locking read)操作SELECT...FOR UPDATESELECT...LOCK IN SHARE MODESELECT...FOR UPDATE对读取的行记录加一个X锁其他事务不能对已锁定的行加上任何锁。SELECT...LOCK IN SHARE MODE对读取的行记录加一个S锁其他事务可以向被锁定的行加S锁但是如果加X锁则会被阻塞。6.3.4自増长与锁在InnoDB存储引擎的内存结构中对每个含有自增长值的表都有一个自增长计数器(auto-increment counter),当对含有自增长的计数器的表进行插入操作时这个计数器会被初始化执行如下的语句来得到计数器的值SELECT MAX(auto_inc_col) FROM t FOR UPDATE;插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做 AUTO-INC Locking.这种锁其实是采用一种特殊的表锁机制为了提高插入的性能锁不是在一个事务完成后才释放而是在完成对自增长值插入的SQL语句后立即释放。6.5锁问题6.5.1脏读脏页指的是在缓冲池中已经被修改的页但是还没有刷新到磁盘中即数据库实例内存中的页和磁盘中的页的数据是不一致的当然在刷新到磁盘之前日志都已经被写入到了重做日志文件中。脏数据是指事务对缓冲池中行记录的修改并且还没有被提交。对于脏页的读取是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的, 这并不影响数据的一致性(或者说两者最终会达到一致性即当脏页都刷回到磁盘)。并且因为脏页的刷新是异步的不影响数据库的可用性因此可以带来性能的提高。脏数据是指未提交的数据如果读到了脏数据即一个事务可脏读指的就是在不同的事务下当前事务可以读到另外事务未提交的数据简单来说就是可以读到脏数据。违反了事务的隔离性。6.5.2不可重复读不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时, 另外一个事务也访问该同一数据集合并做了一些DML操作。在第一个事务中的两次读数据之间由于第二个事务的修改发生了在一个事务内两次读到的数据是不一样的情况这种情况称为不可重复读。不可重复读和脏读的区别是脏读是读到未提交的数据而不可重复读读到的却是已经提交的数据但是其违反了数据库事务一致性的要求。InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE,采用Next-Key Lock算法避免了不可重复读的现象。6.5.3丢失更新丢失更新是另一个锁导致的问题简单来说其就是一个事务的更新操作会被另一个事务的更新操作所覆盖从而导致数据的不一致。6.6阻塞因为不同锁之间的兼容性关系在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源这就是阻塞。阻塞并不是一件坏事其是为了确保事务可以 并发且正常地运行。innodb_lock_wait_timeout用来控制等待的时间(默认 是50秒)innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是OFF,代表不回滚)。6.7死锁6.7.1死锁的概念死锁是指两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象。解决死锁问题最简单的一种方法是超时即当两个事务互相等待时当一个等待时间超过设置的某一阈值时其中一个事务进行回滚另一个等待的事务就能继续进行。参数innodb_lock_wait_timeout用来设置超时的时间。除了超时机制当前数据库还都普遍采用wait-for graph (等待图)的方式来进行死锁检测。较之超时的解决方案这是一种更为主动的死锁检测方式。6.8锁升级锁升级(Lock Escalation)是指将当前锁的粒度降低。举例来说数据库可以把一 个表的1000个行锁升级为一个页锁或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源而且想避免锁的开销那数据库中会频繁出现锁升级现象。InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的相反其根据每个事务访问的每个页对锁进行管理的采用的是位图的方式。因此不管一 个事务锁住页中一个记录还是多个记录其开销通常都是一致的。第7章 事务数据库系统引入事务的主要目的事务会把数据库从一种一致状态转换为另 一种一致状态。在数据库提交工作时要么所有修改都已经保存了要么所有修改都不保存。InnoDB存储引擎中的事务完全符合ACID的特性原子性(atomicity)一致性(consistency)隔离性(isolation )持久性(durability)7.1认识事务7.1.1概述事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作要么都做修改要么都不做这就是事务的目的也是事务模型区别与文件系统的重要特 征之一。原子性。只有使事务中所有的数据库操作都执行成功才算整个事务成功。事务中任何一个SQL语句执行失败已经执行成功的 SQL语句也必须撤销数据库状态应该退回到执行事务前的状态。一致性。一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后数据库的完整性约束没有被破坏事务是一致性的单位如果事务中某个动作失败了系统可以自动撤销事务——返 回初始化的状态。隔离性。隔离性还有其他的称呼如并发控制(concurrency control)、 可串行化(serializability)、锁(locking)等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离即该事务提交前对其他事务都不可见通常这使用锁来实现。当前数据库系统中都提供了一种粒度锁(granular lock)的策略允许事务仅锁住一个实体对象的子集以此来提高事务之间的并发度。持久性。事务一旦提交其结果就是永久性的。即使发生宕机等故障数据库也能将数据恢复。7.1.2分类从事务理论的角度来说可以把事务分为以下儿种类型扁平事务(Flat Transaction)所有操作都处于同一层次其由BEGIN WORK开始由COMMIT WORK或ROLLBACK WORK结束其间的操作是原子的要么都执行要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。扁平事务的主要限制是不能提交或者回滚事务的某一部分或分几个步骤提交。带有保存点的扁平事务(Flat Transactions with Savepoint),除了支持扁平事务支 持的操作外允许在事务执行过程中回滚到同一事务中较早的一个状态。保存点(Savepoint)用来通知系统应该记住事务当前的状态以便 当之后发生错误时事务能回到保存点当时的状态。链事务(Chained Transaction)可视为保存点模式的一种变种。链事务的思想是在提交一个事务时释放不需要的数据对象将必要的处理上下 文隐式地传给下一个要开始的事务。注意提交事务操作和开始下一个事务操作将合并为一个原子操作。链事务与带有保存点的扁平事务不同的是带有保存点的扁平事务能回滚到任意正 确的保存点。而链事务中的回滚仅限于当前事务即只能恢复到最近一个的保存点。嵌套事务(Nested Transaction)是一个层次结构框架。由一个顶层事务(top- level transaction)控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务 (subtransaction),其控制每一个局部的变换。分布式事务(Distributed Transactions)通常是一个在分布式环境下运行的扁平事务因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务其同样需要满足ACID特性要么都发生要么都失效。7.2事务的实现事务隔离性锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志用来保证事务的原子性和持久性。undo log用来保证事务的一致性。redo恢复提交事务修改的页操作而undo回滚行记录到某个特定版本。因此两者记录的内容不同redo通常是物理日志记录的是页的物理修改操作。undo是逻辑日志根据每行记录进行记录。7.2.1 redo1.基本概念重做日志用来实现事务的持久性由两部分组成一是内存中的重做日志缓冲(redo log buffer),其是易失的二是重做日志文件(redo log file), 其是持久的。InnoDB是事务的存储引擎其通过Force Log at Commit机制实现事务的持久性即当事务提交(COMMIT)时必须先将该事务的所有日志写入到重做日志文件进行持久化待事务的COMMIT操作完成才算完成。这里的日志是指重做日志在 InnoDB存储引擎中由两部分组成即redo log和undo log。redo log用来保证事务的持久性undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的 在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。为了确保每次日志都写入重做日志文件在每次将重做日志缓冲写入重做日志文件后InnoDB存储引擎都需要调用一次fsync操作。由于重做日志文件打开并没有使用 O_DIRECT选项因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能因此磁盘的性能确定了事务提交的性能也就是数据库的性能。InnoDB存储引擎允许用户手工设置非持久性的情况发生以此提高数据库的性能。即当事务提交时日志不写入重做日志文件而是等待一个时间周期后再执行fsync操 作。由于并非强制在事务提交时进行一次fsync操作显然这可以显著提高数据库的性能。但是当数据库发生宕机时由于部分日志未刷新到磁盘因此会丢失最后一段时间的事务。参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。该参数 的默认值为1,表示事务提交时必须调用一次fsync操作。还可以设置该参数的值为0和 2。表示事务提交时不进行写入重做日志操作这个操作仅在master thread中完成而 在master thread中每1秒会进行一次重做日志文件的fsync操作。2表示事务提交时将 重做日志写入重做日志文件但仅写入文件系统的缓存中不进行fsync操作。在这个 设置下当MySQL数据库发生宕机而操作系统不发生宕机时并不会导致事务的丢失。而当操作系统宕机时重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部 分事务。在MySQL数据库中还有一种二进制日志(binlog),其用来进行PONIT-IN-TIME (PIT)的恢复及主从复制(Replication)环境的建立。从表面上看其和重做日志罪常相似都是记录了对于数据库操作的日志。首先重做日志是在InnoDB存储引擎层产生而二进制日志是在MySQL数据库的上层产生的并且二进制日志不仅仅针对于InnoDB存储引擎MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。其次两种日志记录的内容形式不同。MySQL数据库上层的二进制日志是一种逻辑日志其记录的是对应的SQL语句。而InnoDB存储引擎层面的重做日志是物理格式日志其记录的是对于每个页的修改。此外两种日志记录写入磁盘的时间点不同。二进制日志只在事务提交完成后进行一次写入。而InnoDB存储引擎的重做日志在事务进行中不断地被写入 这表现为日志并不是随事务提交的顺序进行写入的。7.2.2 undo1 .基本概念重做日志记录了事务的行为可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作这时就需要undo.因此在对数据库进行修改时InnoDB存储引擎不但会产生redo,还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了又或者用户用一条ROLLBACK语句请求回滚就可以利用这些undo信息将数据回滚到修改之前的样子。redo存放在重做日志文件中与redo不同undo存放在数据库内部的一个特殊段 (segment)中这个段称为undo段undo段位于共享表空间内。undo是逻辑日志因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了但是数据结构和页本身在回滚之后可能大不相同。不能将一个页回滚到事务开始的样子因为这样会影响其他事务正在进行的工作。除了回滚操作undo的另一个作用是MVCC,即在InnoDB存储引擎中MVCC的 实现是通过undo来完成。当用户读取一行记录时若该记录已经被其他事务占用当前事务可以通过undo读取之前的行版本信息以此实现非锁定读取。最后也是最为重要的一点是undo log会产生redo log,也就是undo log的产生会伴随着redo log的产生这是因为undo log也需要持久性的保护。7.6事务的隔离级别SQL标准定义的四个隔离级别为READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLEREAD UNCOMMITTED称为浏览访问(browse access),仅仅针对事务而言 READ COMMITTED 称为游标稳定 REPEATABLE READ 是 2.9999° 的隔离没有幻读的保护。SERIALIZABLE称为隔离或3°的隔离。SQL和SQL2标 准的默认事务隔离级别是SERIALIZABLEInnoDB存储引擎默认支持的隔离级别是REPEATABLE READ,但是与标准SQL不同的是InnoDB存储引擎在REPEATABLE READ事务隔离级别下使用Next-Key Lock锁的算法因此避免幻读的产生。InnoDB存储引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求即达到SQL标准的SERIALIZABLE隔离级别。隔离级别越低事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ COMMITTED在InnoDB存储引擎中可以使用以下命令来设置当前会话或全局的事务隔离级别如果想在MySQL数据库启动时就设置事务的默认隔离级别那就需要修改MySQL 的配置文件在[mysqld]中添加如下行[mysqld]transaction-isolation READ-COMMITTED查看当前会话的事务隔离级别SELECT tx_isolation\G査看全局的事务隔离级别SELECT global.tx_isolation\G7.7分布式事务7.7.1 MySQL数据库分布式事务InnoDB存储引擎提供了对XA事务的支持并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactional resources)参与到一个全局的事务中。事务资源通常是关系型数据库系统但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交要么都回滚这对于事务原有的ACID要 求又有了提高。另外在使用分布式事务时InnoDB存储引擎的事务隔离级别必须设置 为SERIALIZABLEXA事务允许不同数据库之间的分布式事务如一台服务器是MySQL数据库的另 一台是Oracle数据库的又可能还有一台服务器是SQL Server数据库的只要参与在 全局事务中的每个节点都支持XA事务。分布式事务使用两段式提交(two-phase commit)的方式。在第一阶段所有参与全局事务的节点都开始准备(PREPARE),告诉事务管理器它们准备好提交了。在第二阶段事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交则所有的节点都被告知需要回滚。第8章备份与恢复8.1备份与恢复概述可以根据不同的类型来划分备份的方法。根据备份的方法不同可以将备份分为Hot Backup (热备)Cold Backup (冷备)Warm Backup (温备)Hot Backup是指数据库运行中直接备份对正在运行的数据库操作没有任何的影响。这种方式在MySQL官方手册中称为Online Backup (在线备份)。Cold Backup是指备份操作是在数据库停止的情况下这种备份最为简单一般只需要复制相关的数据库物理文件即诃。这种方式在MySQL官方手册中称为Offline Backup (离线备份)。Warm Backup备份同样是在数据库运行中进行的但是会对当前数据库的操作有所影响如加 一个全局读锁以保证备份数据的一致性。按照备份后文件的内容备份又可以分为逻辑备份裸文件备份逻辑备份是指备份出的文件内容是可读的一般是文本 文件。内容一般是由一条条SQL语句或者是表内实际数据组成。如mysqldump和SELECT*INTO OUTFILE的方法。这类方法的好处是可以观察导出文件的内容一般适 用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往较长。裸文件备份是指复制数据库的物理文件既可以是在数据库运行中的复制(如 ibbackup. xtrabackup这类工具)也可以是在数据库停止运行时直接的数据文件复制。这类备份的恢复时间往往较逻辑备份短很多。若按照备份数据库的内容来分备份又可以分为完全备份增量备份日志备份完全备份是指对数据库进行一个完整的备份。增量备份是指在上次完全备份的基础上对于更改的数据进行备份。日志备份主要是指对MySQL数据库二进制日志的备份 通过对一个完全备份进行二进制日志的重做(replay)来完成数据库的point-in-time的恢复工作。MySQL数据库复制(replication)的原理就是异步实时地将二进制日志重做传送并应用到从(slave/standby)数据库。8.2冷备对于InnoDB存储引擎的冷备非常简单只需要备份MySQL数据库的fan文件共享表空间文件独立表空间文件(*.ibd),重做日志文件。另外建议定期备份MySQL数据库的配置文件my.cnf,这样有利于恢复的操作冷备的优点是备份简单只要复制相关文件即可。备份文件易于在不同操作系统不同MySQL版本上进行恢复。恢复相当简单只需要把文件恢复到指定位置即可。恢复速度快不需要执行任何SQL语句也不需要重建索引。冷备的缺点是InnoDB存储引擎冷备的文件通常比逻辑文件大很多因为表空间中存放着很多其他数据冷备也不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写敏感 和浮点数格式都会成为问题。8.3逻辑备份8.3.1 mysqldump通常用来完成转存(dump) 数据库的备份及不同数据库之间的移植如从MySQL低版本数据库升级到MySQL高版本数据库,又或者从MySQL数据库移植到Oracle、Microsoft SQL Server数据库等。mysqldump的语法如下shellmysqldump (arguments] file_naine备份所有的数据库可以使用-all-databases选项mysqldump --all-databases dump.sql备份指定的数据库可以使用-databases选项mysqldump --databases dbl db2 db3 dump.sql如果想要对test这个架构进行备份可以使用如下语句mysqldump --single-transaction test test_backup.sql 8.3.2 SELECT...INTO OUTFILESELECT...INTO语句也是一种逻辑备份的方法更准确地说是导出一张表中的数据。 #默认导出的文件是以TAB进行列分割的 select * into outfile /home/mysql/a.txt from a; cat /home/mysql/a.txt #使用其他分割符 mysql test -e select * into outfile /home/mysql/a.txt fields terminated by *, * from a;8.3.3逻辑备份的恢复mysqldump的恢复操作比较简单因为备份的文件就是导出的SQL语句一般只需要执行这个文件就可以了mysql -uroot -p source /home/mysql/test_backup.sql8.3.4 LOAD DATA INFILE若通过mysqldump-tab,或者通过SELECT INTO OUTFILE导出的数据需要恢复,可以通过命令LOAD DATA INFILE来进行导入。load data infile /home/mysql/a.txt into table a;8.4二进制日志备份与恢复二进制日志非常关键用户可以通过它完成point-in-time的恢复工作。MySQL数据库的replication同样需要二进制日志。在默认情况下并不启用二进制日志要使用二进制日志首先必须启用它。如在配置文件中进行设置[mysqld]log-binmysql-bin只简单启用二进制日志是不够的 还需要启用一些其他参数来保证最为安全和正确地记录二进制日志,推荐的二进制日志的服务器配置应该是[mysqld]log-bin mysql-binsync_binlog 1innodb_support_xa 1在备份二进制日志文件前可以通过FLUSH LOGS命令来生成一个新的二进制日志文件然后备份之前的二进制日志。要恢复二进制日志也是非常简单的通过mysqlbinlog即可。mysqlbinlog的使用方法如下#恢复二进制日志mysqlbinlog binlog. 0000001 I mysql-uroot -p test#恢复多个二进制日志文件mysqlbinlog binlog.[0-10]* I mysql -u root -p test也可以先通过mysqlbinlog命令导出到一个文件然后再通过SOURCE命令来导入mysqlbinlog binlog.000001 /tmp/statements.sqlmysqlbinlog binlog.000002 /tmp/statements.sqlmysql -u root -p -e source /tmp/statements.sql8.5热备8.5.1 ibbackupibbackup是InnoDB存储引擎官方提供的热备工具可以同时备份MylSAM存储引擎和InnoDB存储引擎表8.5.2 XtraBackupXtraBackup备份工具是由Percona公司开发的开源热备工具。支持MySQL5.0以上的版本。8.5.3 XtraBackup实现增量备份MySQL数据库本身提供的工具并不支持真正的增量备份更准确地说二进制日志的恢复应该是point-in-time的恢复而不是增量备份。而XtraBackup工具支持对于 InnoDB存储引擎的增量备份第9章性能调优9.1选择合适的CPUInnoDB存储引擎一般都应用于OLTP的数据库应用这种应用的特点如下用户操作的并发量大事务处理的时间一般比较短查询的语句较为简单一般都走索引复杂的査询较少本身对CPU的要求并不是很高可以说OLAP是CPU密集型的操作而OLTP是IO密集型的操作。在采购设备时将更多的注意力放在提高IO的配置上。此外为了获得更多内存的支持用户采购的CPU必须支持64位否则无法支持64位操作系统的安装。因此为新的应用选择64位的CPU是必要的前提。如果CPU是多核的可以通过修改参数innodb_read_io_ threads和innodb_write_io_threads来增大IO的线程充分有效地利用CPU 的多核性能。9.2内存的重要性内存的大小是最能直接反映数据库的性能。InnoDB存储引擎既缓存数据又缓存索引并且将它们缓存于一个很大的缓冲池中即 InnoDB Buffer PooL因此内存的大小直接影响了数据库的性能。应该在开发应用前预估“活跃”数据库的大小是多少并以此确定数据库服 务器内存的大小。如何判断当前数据库的内存是否已经达到瓶颈了呢比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率通常InnoDB存储引擎的缓冲池的命中率不应该小于99%SHOW GLOBAL STATUS LIKE innodb%read%\G; 缓冲池的命中率Innodb_buffer_pool_read_requests/Innodb_buffer_pool_read_ahead_evictedInnodb_buffer_pool_read_requestsInnodb_buffer_pool_reads9.3硬盘对数据库性能的影响9.3.1传统机械硬盘机械硬盘有两个重要的指标一个是寻道时间另一个是转速。传统机械硬盘最大的问题在于读写磁头读写磁头的设计使硬盘可以不再像磁带一样只能进行顺序访问而是可以随机访问。但是机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找因此顺序访问的速度要远高于随机访问。9.3.2固态硬盘固态硬盘更准确地说是基于闪存的固态硬盘9.4合理地设置RAID9.4.1 RAID 类型RAID (Redundant Array of Independent Disks,独立磁盘冗余数组)的基本思想就是把多个相对便宜的硬盘组合起来成为一个磁盘数组使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区RAID看起来就像一个单独的硬盘或逻辑存储单元因此操作系统只会把它当作一个硬盘。RAID的作用是增强数据集成度 增强容错功能增加处理量或容量9.7选择合适的基准测试工具基准测试工具可以用来对数据库或操作系统调优后的性能进行对比。MySQL数据 库本身提供了一些比较优秀的工具这里将介绍另外两款更为优秀和常用的基准测试工 具sysbench 和 mysql-tpcco9.7.1 sysbenchsysbench是一个模块化的、跨平台的多线程基准测试工具主要用于测试各种不同 系统参数下的数据库负载情况。它主要包括以下几种测试方式CPU性能、磁盘IO性能、调度程序性能、内存分配及传输速度、POSIX线程性能、数据库OLTP基准测试9.7.2 mysql-tpccTPC是一个用来评价大型数据库系统软硬件性能的非盈利组织。TPC-C是TPC协会制定的目前在学术界和工业界普遍采用TPC-C 来评价OLTP应用的性能。tpcc-mysql由以下两个工具组成。tpcc_load根据仓库数量生成9张表中的数据。tpcc_start根据不同选项进行TPC-C测试。
