哪里白癜风医院较好 https://jbk.39.net/yiyuanfengcai/video_bjzkbdfyy/一、MySQL架构与历史
MySQL的架构
从上图可以看出，MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。需要注意的是，存储引擎是基于表的，而不是数据库的（即同一个数据库中的不同表可以有不同的存储引擎）。
MySQL是一个单进程多线程架构的数据库。
连接MySQL
连接MySQL是一个连接进程和MySQL数据库实例进行通信。从程序设计的角度来说，本质上是进程通信。
连接MySQL的方式有：TCP/IP套接字、命名管道和共享内存、UNIX域套接字。
InnoDB与MyISAM存储引擎对比
InnoDB支持事务、外键、行锁；支持非锁定读，即默认读取操作不会产生锁。InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能。对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表数据的存储都是按照主键的顺序进行存放（这种表称为“索引组织表”）。
MyISAM（发音：my-z[ei]m）不支持事务、表锁设计，支持全文索引（InnoDB已经支持）。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择InnoDB表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择MyISAM表。
二、InnoDB存储引擎概述
内存缓冲池
在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。
（注：上图中左上角的日志缓冲应该为重做日志缓冲）需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。
重做日志缓冲
重做日志缓冲一般不需要设置的很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。默认为8MB.
系统在以下三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：
MasterThread每一秒将重做日志缓冲刷新到重做日志文件；
每个事务提交时会将重做日志缓冲刷新到重做日志文件；
当重做日志缓冲池剩余空间小与1/2时，重做日志缓冲刷新到重做日志文件。
Checkpoint技术
为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了WriteAheadLog策略，即当事务提交时，先写重做日志，再修改页。当由于宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D（Durability持久性）的要求。
Checkpoint技术是用来解决以下几个问题：
缩短数据库的恢复时间；
缓冲池不够用时，将脏页刷新到磁盘；
重做日志不可用时，刷新脏页。
当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。
此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷新回磁盘。
重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这部分重做日志已经不再需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时这部分重做日志还需要使用，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。
对于InnoDB存储引擎而言，其是通过LSN（LogSequenceNumber）来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。
三、文件
日志文件
错误日志
错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQLDBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录了一些警告信息或正确的信息。
慢查询日志
可以在MySQL启动时设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该值默认为10秒。
查询日志
查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。
二进制日志
二进制日志（binarylog）记录了对MySQL数据库执行更改的所有操作。
二进制日志文件默认未开启。手动开启后会使系统性能下降大概1%.
但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以且应该被接受的。
重做日志文件
在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。这两个文件就是重做日志文件，或者事务日志。
重做日志的目的：万一实例或者介质失败，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。
每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。
重做日志与二进制日志的区别：
二进制日志会记录所有与mysql数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志，而InnoDB存储引擎的重做日志只记录有关其本身的事务日志，
记录的内容不同，不管你将二进制日志文件记录的格式设为哪一种，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志；而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况；
写入的时间也不同，二进制日志文件是在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大；而在事务进行的过程中，不断有重做日志条目被写入重做日志文件中。
四、之一表
索引组织表
在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（indexorganizedtable）。在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：
首先判断表中是否存在非空的唯一索引（UniqueNOTNULL），如果有，则该列即为主键；
如果不符合上述条件，InnoDB存储引擎会自动创建一个6字节大小的指针；
对于其他的一些数据库，如MicrosoftSQLServer数据库，其中一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。堆表的特性决定了堆表上的索引都是非聚集的。
需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入到内存，然后通过PageDirectory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。
从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为（block），InnoDB存储引擎的逻辑存储结构大致如图：
VARCHAR
MySQL数据库的VARCHAR类型可以存放字节数据（除去别的开销，实际最大可以存放字节）；
VARCHAR(N)中的N是指字符数；
此外，此处长度是指所有VARCHAR列的长度总和，如果列的长度总和超出这个长度，依然无法创建，如：
CREATETABLEtest(aVARCHAR(),bVARCHAR(),cVARCHAR())CHARSET=latin1
分区表
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，都可独自处理，也可以作为一个更大对象的一部分进行处理。
当前MySQL数据库支持以下几种类型的分区：
RANGE分区：行数据基于属于一个给定连续区间的列值放入分区；
LIST分区：和RANGE类似，只是LIST分区里面是离散的值；
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数；
KEY分区：根据MySQL数据库提供的（即内置的）哈希函数进行分区。
分区和性能
数据库应用分为两类：一类是OLTP（在线事务处理），如Blog，电子商务，网络游戏等；另一类是OLAP（在线分析处理），如数据仓库，数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家的操作的游戏数据库应用就是OLTP的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏，预测玩家的行为等，而这却是OLAP的应用。
对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。
然而对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。
例如：很多开发团队会认为含有W行的表是一张非常大的表，所以他们往往会采用分区，如对主键做10个HASH的分区，这样每个分区就只有W的数据了，因此查询应该变快了，如SELECT*FROMTABLEWHEREPK=
pk。但是有没有考虑过这样一种情况：W和W行的数据本身构成的B+树的层次都是一样的可能都是2层。那么上述走主键分区的索引并不会带来性能的提高。如果W的B+树高度是3,W的B+树的高度是2，那么上述按主键分区的索引可以避免1次IO，从而提高查询效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的，如果还有类似如下的SQL语句：SELECT*FROMTABLEWHEREKEY=key,这时对于KEY的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2~3次IO。
这里，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。
——没有全局的索引，所以才需要遍历每个分区的索引。
第四章之二Schema与数据类型优化
选择优化的数据类型
更小的通常更好；更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；
简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；
尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOTNULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列；
字符串类型
VARCHAR和CHAR
VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。
下面这些情况使用VARCHAR是合适的：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片？）。
慷慨是不明智的
使用VARCHAR(5)和VARCHAR()存储"hello"的空间开销是一样的。那么使用更短的列有什么优势吗？
事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
所以最好的策略是只分配真正需要的空间。
BLOB和TEXT
BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储。
与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。
选择表示符（identifier）
整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT。如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。对于完全随机的字符串也需要多加注意，例如MD5(),SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：
因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致叶分裂、磁盘随机访问。
SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效。
第五章创建高性能的索引索引与算法
B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。
数据库中的B+树索引可以分为聚集索引和辅助索引。聚集索引的叶子结点存放的是一整行记录，而辅助索引叶子结点存放的是主键值。
许多数据库的文档这样告诉读者：聚集索引按照顺序物理地存储数据。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表连接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。（《MySQL技术内幕InnoDB存储引擎》）
InnoDB只聚集在同一个页面中数据，包含相邻键值的页面可能相距甚远。（高性能MySQL）
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。
索引的类型
B-Tree索引
B+树，所有叶子节点在同一层，每一个叶子节点包含指向下一个叶子结点的指针。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
其中，索引对多个值进行排序的顺序是与定义索引时列的顺序一致的。
B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。
全字匹配：和索引中的所有列进行匹配，如查找姓名为CubaAllen、出生于-01-01的人；
匹配最左前缀：即只使用索引的第一列，如查找所有姓为Allen的人；
匹配列前缀：匹配某一列的值的开头部分，如查找所有以J开头的姓的人。这里只使用了索引的第一列；
匹配范围值：如查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列；
精确匹配某一列并范围匹配另一列：如查找所有姓为Allen，并且名字是字母K开头的人。即第一列全匹配，第二列范围匹配；
只访问索引的查询：覆盖索引；
如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人。类似的，也无法查找姓以某个字母结尾的人。
不能跳过索引中的列。也就是说，上述索引无法用于查找姓为Smith并且在某个特定日期出生的人。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查询WHERE姓=SmithAND名LIKEJ%AND出生日期=-12-23，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。（如果范围查询列值的数量有限，那么可以使用多个等于条件来代替范围条件）
到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时保持指向数据行的指针。
在MySQL中，只有Memory引起显示支持哈希索引。
哈希索引数据并不是按照索引数据顺序存储的，所以无法用于排序；
哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
哈希索引只支持等值比较查询，不支持任何范围查询；
InnoDB引擎有个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为。
索引的优点
最常见的B-Tree索引，按照顺序存储数据，所以可以用来做ORDERBY和GROUPBY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成查询。据此特性，总结下来索引有如下三大优点：
索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机IO变为顺序IO；
评价一个索引是否适合某个查询的“三星系统”：
索引将相关的记录放到一起则获得一星；
索引中的数据顺序和查找中的排列顺序一致则获得二星；
索引中的列包含了查询需要的全部列则获得三星；
高性能的索引策略
独立的列
索引列不能是表达式的一部分，也不能是函数的参数，否则不会使用索引。
如：SELECTactor_idFROMactorWHEREactor_id+1=5
SELECT...WHERETO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)=10;
前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？通常可以索引开始的部分字符，这样可以大大节约空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为可以在查询时过滤掉更多的行。唯一索引的选择性是1.
诀窍在与既要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便节约空间。
多列索引
很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序建立多列索引。
对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列（在没有ORDERBY或GROUPBY的情况下）。例如，在超市的销售记录表中：SELECT*FROMpaymentWHEREstaff_id=2ANDcustomer_id=，很自然的customer_id的选择性更高些，所以多列索引的顺序应该是(customer_id,staff_id)。
这样做有一个地方需要注意，查询的结果非常依赖与选定的具体值。例如，一个应用通常都有一个特殊的管理员账号，系统中所有其他用户都是这个用户的好友，所以系统通常通过它向网站的所有其他用户发送状态和其他消息。这个账号巨大的好友列表很容易导致网站出现服务器性能问题。
这实际上是一个非常典型的问题。任何的异常用户，不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题；那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的系统账号同样的问题。
从这个小案例可以看到经验法则和推论在多数情况下是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。
聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。
在InnoDB中，聚簇索引“就是”表。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
InnoDB只能通过主键聚集索引！如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
MySQL中每个表都有一个聚簇索引（clusteredindex），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondaryindexes）。
聚簇索引有一些重要的优点：
可以把相关的数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件可能都会导致一次磁盘I/O。
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
同时，聚簇索引也有一些缺点：
聚簇索引最大限度提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖插入顺序。按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZETABLE命令重新组织一下表。
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表在插入新行，或者主键被更新导致移动行的时候，可能面临叶分裂的问题。
二级索引访问需要两次索引查找，而不是一次。
MyISAM/InnoDB的主键索引和二级索引
MyISAM的主键索引和（所有其他的）二级索引的叶子节点中保存的都是指向行的物理位置的指针。
InnoDB的主键索引的叶子结点是数据行；（所有其他的）二级索引的叶子节点中保存的是主键值。这样的策略减少了当出现行移动或数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无需更新二级索引中的这个“指针”。
如果正在使用的InnoDB表没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就可以使用索引来直接获取列的数据，这样就不再需要读取数据行。我们称这样的索引为覆盖索引。
例如，表inventory有一个多列索引（store_id,film_id），MySQL如果只需要访问这两列，就可以使用这个索引做覆盖索引，如SELECTstore_id,film_idFROMinventory.
利用索引扫描来做排序
只有当索引的列顺序和ORDERBY子句的列顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDERBY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDERBY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。
有一种情况下ORDERBY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。
例如，索引：UNIQUEKEYidx（rental_date,inventory_id,customer_id）
下面这个查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：
WHERErental_date=5-05-25ORDERBYinventory_id,customer_idDESC;
下面这个查询也没问题，因为ORDERBY使用的两列就是索引的最左前缀：
WHERErental_date5-05-25ORDERBYrental_date,inventory_id;
下面是一些不能使用索引做排序的查询：
下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：
WHERErental_date=5-05-25ORDERBYinventory_idDESC,customer_idASC;
下面这个查询的ORDERBY子句中引用了一个不在索引中的列：
WHERErental_date=5-05-25ORDERBYinventory_id,staff_id;
下面这个查询的WHERE和ORDERBY中的列无法组合成索引的最左前缀：
WHERErental_date=5-05-25ORDERBYcustomer_id;
下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：
WHERErental_date5-05-25ORDERBYinventory_id,customer_id;
冗余和重复索引
重复索引是指在相同的列上按相同的顺序创建相同类型的索引。如：
CREATETABLEtest(IDINTNOTNULLPRIMARYKEY,AINTNOTNULL,BINTNOTNULL,UNIQUE(ID),INDEX(ID))ENGINE=InnoDB;
事实上，MySQL的主键约束和唯一约束都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。
冗余索引和重复索引有一些不同。如果创建了索引（A,B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，索引（A,B）也可以当做索引（A）来使用。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引（如扩展索引（A）为（A，B））。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变大太大，从而影响其他使用该索引的查询的性能。
一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
第六章锁
开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另一方面还要确保每个用户能以一致的方式读取和修改数据。为此就有了锁的机制，同时这也是数据库系统区别于文件系统的一个关键特性。
锁机制用于管理对共享资源的并发访问。
InnoDB存储引擎中的锁
锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁：
共享锁（SLock），允许事务读一行数据；
排它锁（XLock），允许事务删除或更新一行数据。
此外，InnoDB存储引擎支持多粒度锁定，这种锁允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（IntentionLock）。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。
例如，如果需要对记录r上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。若其中任何一个步骤导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记录r加X锁之前，已经有事务对表1进行了S锁，而当前事务需要对表1上IX锁，由于不兼容，所以该事务需要等待表锁操作的完成。
一致性非锁定读
一致性的非锁定读（consistentnonlockingread）是指InnoDB存储引擎通过行多版本控制的方式来读取当前时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这是读取操作不会因此去等待行上锁的释放。相反的，InnoDB存储引擎会去读取行的一个快照数据。
快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。
可以看到，非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。
一个行记录可能有不止一个快照数据，一般称这种技术为多版本技术。由此带来的并发控制，称之为多版本并发控制（MultiVersionConcurrencyControl，MVCC）。
在READCOMMITTED事务隔离级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据（违反了事务ACID中的I的特性，即隔离性）。而在REPEATABLEREAD事务隔离级别下，一致性非锁定读总是读取事务开始时的行数据版本。
一致性锁定读
在某些情况下，用户需要显式的对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读操作：
SELECT...FORUPDATE（X锁）
SELECT...LOCKINSHAREMODE（S锁）
锁的算法
行锁的3种算法
InnoDB存储引擎有3种行锁的算法，分别是：
RecordLock：单个行记录上的锁；
GapLock：间隙锁，锁定一个范围，但不包含记录本身；
Next-KeyLock：GapLock+RecordLock，锁定一个范围，并且锁定记录本身；
InnoDB对于行的查询都是采用这种Next-KeyLock锁定算法。
但查询的索引含有唯一属性时，InnoDB存储引擎会对Next-KeyLock进行优化，将其降级为RecordLock，即仅锁住记录本身，而不是范围。如SELECT*FROMtWHEREpk=5FORUPDATE；
解决PhantomProblem
在默认的事务隔离级别下，即REPEATABLEREAD下，InnoDB存储引擎采用Next-KeyLocking机制来避免PhantomProblem（幻读）。
PhantomProblem是指在同一事务下，连续两次执行相同的SQL语句可能会导致不同的结果，第二次的SQL语句可能会返回之前不存在的行（重点在记录数不一样）。
如下SQL语句：SELECT*FROMtWHEREpk2FORUPDATE，第一次返回a=5这条记录；若这时另一个事务插入了4这个值，那么第二次执行时将返回4和5.这与第一次得到的结果不同，违反了事务的隔离性，即当前事务能够看到其他事务的结果。
InnoDB存储引擎采用Next-KeyLocking的算法避免PhantomProblem。对于上述SQL语句，其锁住的不是5这个单值，而是对(2,+∞)这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免PhantomProblem。
锁问题
脏读
所谓脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交。
脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。
脏读在生产环境中并不常见。脏读发生的条件是需要事务的隔离级别为READUNCOMMITTED，而目前绝大多数的数据库都至少设置成READCOMMITTED。
不可重复读
不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该数据集合，并做了一些DML操作。这样，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的，这种情况称为不可重复读。
不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据。
一般来说，不可重复读的问题是可接受的，因为其读到的数据是已经提交的，本身不会带来很大的问题。因此，很多数据库厂商，如Oracle、MicrosoftSQLServer将其数据库事务的默认隔离级别设置为READCOMMITTED，在这种隔离级别下允许不可重复读的现象。
幻读
一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。
注意到，RepeatableRead（可重复读）隔离级别仍然避免不了幻读。
我的理解是说，InnoDB提供了next-keylocks，但需要应用程序自己去（手动）加锁。manual里提供一个例子：
SELECT*FROMchildWHEREidFORUPDATE;
这样，InnoDB会给id大于的行（假如child表里有一行id为），以及-，+的gap都加上锁。可以使用showengineinnodbstatus来查看是否给表加上了锁。
在InnoDB存储引擎中，通过使用Next-KeyLocking算法来避免不可重复读的问题。——避免了不可重复读，即实现了可重复读，亦即实现了事务的隔离性。
第七章事务
事务（Transaction）是数据库区别于文件系统的重要特性之一。
事务概述
理论上说，事务有着极其严格的定义，它必须同时满足四个特性，即通常所说的事务的ACID特性。值得注意的是，虽然理论上定义了严格的事务要求，但是数据库厂商出于各种目的，并没有严格去满足事务的ACID标准。如Oracle数据库，其默认的事务隔离级别是READCOMMITTED，不满足隔离性的要求。对于InnoDB存储引擎而言，其默认的事务隔离级别是READREPEATABLE，完全遵循和满足事务的ACID特性。
A（Atomicity），原子性
原子性指整个数据库事务是不可分割的工作单位。事务中的所有数据库操作要么全部成功，要么全部撤销。
C（Consistency）一致性
一致性指事务将数据库从一种一致的状态转变为下一种一致的状态。在事务开始之前和结束之后，数据库的完整性约束没有破坏。
I（Isolation）隔离性
隔离性还有其他的称呼，如并发控制、可串行化、锁等。
事务的隔离性要求，事务提交前对其他事务不可见。*通常这使用锁来实现。*
D（Durability），持久性
事务一旦提交，其结果就是永久性的。即使发生宕机等事故，数据库也能将数据恢复。
事务的实现
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redolog和undolog来完成。redolog称为重做日志，用来保证事务的原子性和持久性。undolog用来保证事务的一致性。
redo和undo都可以视为一种恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。
redo
重做日志用来实现事务的持久性（所以关于上面原子性的实现，有待商榷）。其由两部分组成：一是内存中的重做日志缓冲，其是易失的；二是重做日志文件，其是持久的。
InnoDB是事务的存储引擎，其通过ForceLogatCommit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。
为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，为了确保日志写入磁盘（因为这里有一个文件系统缓存），必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。
InnoDB存储引擎允许用户手工设置非持久性的情况发生，以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。这可以显著提高数据库的性能，但是当数据库发生宕机时，由于部分日志文件未写入磁盘，因此会丢失最后一段时间的事务。
LSN
LSN是LogSequenceNumber的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN代表的含义有：
重做日志写入的总量
checkpoint的位置
页的版本
LSN表示事务写入重做日志的字节总量。例如，当前重做日志的LSN是，事务T1写入了字节的重做日志，LSN就变成1，又有事务T2写入字节的重做日志，那么LSN变成：。可见LSN记录的是重做日志的总量，其单位是字节。
每个页的头部也有一个LSN，记录的是该页最后刷新时LSN的大小。重做日志记录的是每个页的物理更改日志，因此页中的LSN用来判断是否需要进行恢复操作。例如：页的LSN为0，数据库启动时，写入重做日志的LSN为0，表明该事务已经提交，数据库需要恢复；重做日志中的LSN小于页中的LSN，不需要进行重做，因为页中的LSN表示已经刷新到该位置。
恢复
InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。
undo
重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。
除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过读取之前的行版本信息，以此实现*非锁定读*。
最后也是最为重要的一点是，undolog也会产生redolog，也就是undolog的产生会伴随着redolog的产生，这是因为undolog也需要持久性的保护。
purge
purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。这时其他事务可能正在引用该行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。
group
