鞍山网站开发公司,做代理需要自己的网站吗,wordpress 书 主题,北京网站优化软件1. MySQL体系结构从概念上讲#xff0c;数据库是文件的集合#xff0c;是依照某种数据模型组织起来并存放于二级存储器中的数据集合#xff1b;数据库实例是程序#xff0c;是位于用户与操作系统之间的一层数据管理软件#xff0c;用户对数据库数据的任何操作#xff0c;…1. MySQL体系结构从概念上讲数据库是文件的集合是依照某种数据模型组织起来并存放于二级存储器中的数据集合数据库实例是程序是位于用户与操作系统之间的一层数据管理软件用户对数据库数据的任何操作包括数据库定义、查询、维护、运行控制等都是在数据库实例下进行的应用程序只有通过数据库实例才能和数据库打交道。MySQL的体系结构如上图所示主要由以下部分组成连接池组件管理服务和工具组件SQL接口组件查询分析器组件优化器组件缓冲组件插件式存储引擎物理文件InnoDB存储引擎设计目标是面向在线事务处理(Online Transaction ProcessingOLTP)特点是支持事务、行锁设计、支持外键支持非锁定读即默认读取操作不会产生锁现在MySQL的默认引擎是InnoDB。MyISAM存储引擎不支持事务、支持全文索引主要面向一些OLAP数据库应用。2. InnoDB存储引擎关键特性插入缓冲(Insert Buffer)当索引是辅助索引(secondary index)和索引不是唯一索引的时候就会使用插入缓冲。对于非聚集索引的插入或更新操作不是每一次直接插入到索引页中而是先判断插入的非聚集索引是否在缓冲池中若在则直接插入如偶在则先放入到一个Insert Buffer中。数据结构是一棵B树。两次写(Double Write)解决了部分写失效(partial write)问题。由内存中的doublewrite buffer和磁盘上共享表空间中连续的128个页(2个区(extent))组成两个部分的大小都是2M。自适应哈希索引(Adaptive Hash Index)引擎会监控对表上个索引页的查询如果观察到建立哈希索引可以带来查询速度的提升就会建立哈希索引因此叫做自适应哈希索引(AHI)。异步IO(Asynchronous IOAIO)目的是为了提高磁盘操作性能。Sync IO和Async IO前一个得等到一个IO操作完成才会进行下一个IO操作而AIO则是发出一个IO请求后会继续发出下一个IO请求发送完所有请求后等所有IO操作的完成。刷新邻接页(Flush Neighbor Page)当属新一个脏页时引擎会检测改业所在区的所有页如果是脏页则进行一起刷新通过AIO将多个IO进行合并为一个IO在机械硬盘中有优势在SSD中则me这个必要可以通过设置参数innodb_flush_neighbors为0关闭该特性。3. 文件文件包括MySQL数据库和InnoDB存储引擎表的一部分文件。参数文件MySQL实例启动时在哪里可以找到数据文件并指定某些初始化参数这些参数定义了某种内存结构的大小等设置日志文件记录MySQL实例对某种条件作出响应时写入的文件如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等socket文件当用UNIX域套接字方式进行连接时需要的文件pid文件MySQL实例的进程ID文件MySQL表结构文件用来存放MySQL表结构定义的文件存储引擎文件每个存储引擎都会有自己的文件用来保存各种数据。这些存储文件存储了记录和索引的数据。日志文件错误日志MySQL的启动、运行、关闭过程进行记录。从中可以得到一些关于数据库优化等等信息。慢查询日志定位可能存在问题的SQL语句从SQL语句层面进行优化。比如可以把运行时间超过某个阈值的SQL语句记录到慢查询日志文件中然后每过一段时间对其检查是否需要对语句进行优化。阈值可以通过参数long_query_time设置。1. 当某条SQL语句的时间刚好为阈值的值的时候是不会被记录的只记录大于long_query_time的语句。2. 现在(版本5.1开始)long_query_time的值一般是一微秒为值。查询日志记录了所有对MySQL数据库请求的信息无论这些请求是否得到了正确的执行默认文件名为主机名.log。二进制日志记录了所有对MySQL数据库执行更改的操作只有更改数据库的操作才进行记录因此select、show等操作是没有的。如果是update尽管没有更改也会记录。主要作用有恢复数据库、复制数据库、审计(对二进制日志中的信息进行审计判断是否有对输入库的注入攻击)。4. 表主要是InnoDB存储引擎表的逻辑存储及实现。4.1 索引组织表(Index Organized Table)在InnoDB存储引擎中表都是根据主键顺序组织存放的这种存储方式的表称为索引组织表。在InnoDB存储引擎表中每张表都有个主键(primary key)如果在创建表时没有显示的定义主键则InnoDB存储引擎会根据如下方式选择或创建主键首先判断表中是否有非空唯一索引(Unique NOT NULL)如果有则该列为主键如果没有InnoDB存储引擎会自动创建一个6字节大小的指针作为主键。当表中有多个非空唯一索引时引擎会选择建表时第一个定义的非空唯一索引为主键。注主键的选择根据的是定义索引的顺序而不是建表时列的顺序。4.2 InnoDB逻辑存储结构从InnoDB存储引擎逻辑存储结构看所有数据都放在一个空间中成为表空间(tablespace)。表空间由段(segment)、区(extent)、页(page有的也叫块block)组成。InnoDB存储引擎逻辑结构如上图。表空间可以看做是InnoDB存储引擎逻辑结构的最高层所有的数据都存放在表空间中。表空间中的段由各个部分组成常见的有数据段、索引段、回滚段等等因为InnoDB存储引擎表时索引组织因此数据即索引索引即数据所以数据段为B树的叶子结点(图中的Leaf node segment)索引为B树的非叶子结点(图中的Non-leaf node segment)。段中的区是由连续的页组成在任何情况下每个区的大小都为1MB默认的页大小为16KB因此一个区有64个连续的页因此为了保证区中页的连续性引擎一次会从磁盘申请4~5个区。4.3 约束机制数据完整性关系型数据库本身保证了存储数据的完整性。通过Primary Key或Unique Key约束来保证完整性。还可以编写触发器保证数据的完整性。约束的创建和查找可以建表时就进行约束定义或是利用Alter Table命令创建约束。约束和索引的区别创建了一个唯一索引就创建了一个唯一的约束。约束是一个逻辑概念用来保证数据的完整性而索引则是一个数据结构既有逻辑上的概念也代表了数据库中的物理存储方式。对错误数据的约束如向NOT NULL的字段插入一个NULL值MySQL数据库会将其更改为0在进行插入。外键约束用来保证参照完整性关键字为foreign key。4.4 视图MySQL数据库中视图(View)是一个命名的虚表由一个SQL查询来定义可以当做表使用。与持久表(permanent table)不同的是视图中的数据没有实际的物理存储。作用1)被用作一个抽象装置可以起到一个安全层的作用。某些应用程序本身不关心基表(base table)的结构只是按照视图定义来取数据或更新数据即通过视图来更新基本表。5. 索引与算法索引太多影响性能索引太少也影响性能。InnoDB存储引擎支持的几种常见索引B树索引全文索引哈希索引注B树中的B不是代表二叉(binary)而是代表平衡(balance)因为B树是从最早的平衡二叉树演化而来并且B树不是二叉树。B树索引并不能找到一个给定键值的具体行智能查到数据行所在的页然后把页读入到内存再从内存中进行查找最后得到查找的数据。5.1 数据结构与算法关于索引中的B树数据结构和相关算法如下链接。因为每页的数据是按照主键的顺序存放因此在内存中的查询就是通过对页的二分查找而获取。5.2 B树索引B树索引的本质就是B树在数据库中的实现。在数据库中B树的高度一般是在2~4层即查找某一个键值的行记录时最多只需要2~4次IO。MySQL中的B树索引分为聚集索引(clustered index)和辅助索引(secondary index)两个索引的内部实现都是B树 叶子结点存放所有的数据。两个索引的区别是叶子结点存放的数据是否是一整行数据信息。聚集索引InnoDB存储引擎表是索引组织表即表中数据按照主键顺序存放聚集索引就是按照每张表的主键构造一棵B树同时叶子节点中存放的为整张表的行记录数据另外也将聚集索引的叶子节点称为数据页。因此索引组织表中的数据也是索引的一部分么个索引页都通过一个双向链表进行链接。每张表只有一个聚集索引。辅助索引辅助索引也称非聚集索引叶子结点并不包含行记录的全部数据。叶子结点除了包含键值之外每个叶子节点中的索引行还包含一个书签(bookmark)概述前用来高数引擎那里可以找到与索引相对应的行数据因此辅助索引的书签就是相应行数据的聚集索引键。辅助索引不影响数据在聚集索引中的组织每张表上可以有多个辅助索引。通过辅助索引来寻找数据时InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针来获得指向主键索引的主键然后通过主键索引来找到一个完整的行记录。B树索引的使用联合索引指对表上的多个列进行索引。本质也是一棵B树不同的地方在于键值数量大于等于2。联合索引(a, b)首先是根据a,b进行排过序的并且查询键值(a)(a, b)都可以使用联合索引(a, b)但是(b)则不能使用联合索引。覆盖索引也称索引覆盖即从辅助索引中可以查询的记录就不需要查询聚集索引中的记录辅助索引不包含整行的信息大小会小于句句索引可以减少IO操作。6. 锁InnoDB存储引擎不需要所升级因为一个锁和多个锁的开销是相同的。InnoDB的3中行锁算法Record Lock: 锁定单个行记录Gap Lock: 锁定一个范围并且不锁定记录本身Next-Key Lock: Gap Lock Record Lock锁定一个范围并且锁定记录本身。lock与latch的区别InnoDB实现的 两种标准的行级锁共享锁(S Lock)允许事务读一行数据排它锁(X Lock)允许事务删除或更新一行数据InnoDB存储引擎支持多粒度锁定这种锁定允许事务在行级上的锁和表级上的锁同时存在。若要对最细粒度的对象进行上锁那么首先要对粗粒度的对象上锁。如需要对也上的r行进行上X锁分别需要对数据库、表、页上锁最后对行r上X锁。一致性非锁定读(consistent nonlocking read)指InnoDB通过多版本并发控制(Multi Version Concurrency ControlMVCC)的方式读取当前执行时间数据库中的行数据即如果读取行正在执行DELETE或UPDATE操作这时的读操作不会去等待行锁的释放而是去读取行数据的一个快照数据。快照数据指该行之前版本的数据实现通过undo段完成undo用来在事务中进行回滚数据。在InnoDB存储引擎默认设置下一致性非锁定读是默认的读取方式。在事务隔离级别中RREAD COMMITTED中使用一致性非锁定读读取被锁定行的最新一份快照数据。REPEATABLE READ(InnoDB默认事务隔离级别)使用一致性非锁定读读取事务开始时的行数据。一致性锁定读对于语句加锁即使是SELECT的只读操作。SELECT语句有两种一致性锁定读(locking read)操作SELECT...FOR UPDATE对读取行加X锁后续事务不能对锁定的行再加上任何所SELECT...LOCK IN SHARE MODE对读取行加S锁其他事物可以对该行继续加S锁当不能加X锁不然会被阻塞。锁引发的几个问题1. 脏读脏数据指事务对缓冲池中行记录的修改并且修改后还没有被提交。脏页指在缓冲池已经被修改的页但还没有刷新到磁盘中即数据库实例内存中的页和磁盘中的页的数据不一致在刷新到磁盘之前日志已经写到重做日志文件中。主要因为数据库实例内存和磁盘的异步造成的不影响数据的一致性。脏读在不同的事务下当前事务读取到另外事务未提交的数据。一般不发生因为该种操作需要的事务隔离级别为READ UNCOMMITTED但是一般情况下至少都是READ COMMITTED级别而InnoDB的默认级别是READ REPEATABLE级别。违反了数据库的隔离性。2. 不可重复读(幻读)指在一个事务内多次读取同一数据集合的时候在该事务还没有结束前另外一个事务也访问了同一数据集合并作了DML操作结果第一个事务的两次读取结果不同。违反了数据库事务一致性的要求。InnoDB的READ REPEATABLE通过Next-Key Lock算法避免了该情况。与脏读的区别是脏读读取的是未提交的数据不可重复读读取的是已经提交的数据。3. 丢失更新另一个锁导致的问题即一个事务的更新操作被另一个事务的更新操作所覆盖导致数据的不一致。几个事务隔离级别都不会导致丢失更新的问题。但是有另一种逻辑意义上的丢失更新问题1)事务T1查询一行记录显示给USER12)事务T2查询同一行记录显示给USER23)USER1修改这行记录更新数据库并提交4)USER2修改这行记录更新数据库并提交。如银行账户有1000块T1转了900因为网路和数据关系需要等待此时T2操作该账户转走1元当量比操作都成功的时候账户剩下的不是99元而是999元操作T2将T1覆盖。因此避免丢失更新发生让事务在该种情况下的操作变成串行化而不是并行化。即在操作1)中加一个排它锁X锁2)中也加一个排它锁X锁那么2)会等1)提交后才会继续读取。死锁指两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象。一种是通过超时机制解决根据FIFO顺序选择回滚操作更普遍的是采用等待图(wait-for graph)的方式进行死锁检测通过数据库保存的锁的信息链表和事务等待链表可以构造出一张图当图中存在回路的时候就代表存在死锁这时InnoDB选择回滚undo量最小的事务。所升级指将当前锁的粒度降低。比如将一个表的1000个行锁升级为一个页锁或将页锁升级为表锁。7. 事务事务会把数据库从一种一致状态转换为另一种一致状态。事务符合ACID特性原子性(Atomicity)——redo log一致性(Consistency)——undo log隔离性(Isolation)——锁持久性(Durability)——redo logredo恢复提交事务修改的页操作undo回滚行记录到某个特定版本。redo一般是物理日志记录页的物理修改操作undo是逻辑日志根据每行记录进行行记录。参照《MySQL技术内幕——InnoDB存储引擎》第2版所写。
