mysql的重要知识点

索引底层和索引原理

索引是什么：官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。 索引的优势和劣势： 优势：1.可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。3.被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。4.如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。 劣势：1.索引会占据磁盘空间2.索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

索引类型 1.主键索引:索引列中的值必须是唯一的，不允许有空值。 2.普通索引:MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。 3.唯一索引:索引列中的值必须是唯一的，但是允许为空值。 4.全文索引:只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。 5.空间索引:MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。 6.前缀索引:在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。 其他（按照索引列数量分类） 1.单列索引 2.组合索引:组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

索引的数据结构 1.Hash表:Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。 2,二叉查找树:二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。这个特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。 3.平衡二叉树:平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。就这个特点来看，可能各位会觉得这就很好，可以达到二叉树的理想的情况了。然而依然存在一些问题：时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间2010=0.2s）.平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。 4.B树：改造二叉树 MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+42=16）。因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：1.B树的节点中存储着多个元素，每个内节点有多个分叉。2.节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。3.父节点当中的元素不会出现在子节点中。4.所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块6。 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块6。 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。 相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。 看到这里一定觉得B树就很理想了，但是前辈们会告诉你依然存在可以优化的地方： 1.B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。2.如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。 5.B+树：改造B树.B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

• B树：非叶子节点和叶子节点都会存储数据。

• B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块所存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。 举个例子：等值查询： 假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

过程如图：

范围查询：假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。 1.首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。2.查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。3.第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。4.主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

Mysql的索引实现 介绍完了索引数据结构，那肯定是要带入到Mysql里面看看真实的使用场景的，所以这里分析Mysql的两种存储引擎的索引实现：MyISAM索引和InnoDB索引 MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。 主键索引:

表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。 辅助索引:在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

InnoDB索引 主键索引（聚簇索引）: 每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：1.在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。2.如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。3.如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。 除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值对。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。 辅助索引：除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。以表user_innodb的age列为例，age索引的索引结果如下图。

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。画图分析等值查询的情况：

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。 组合索引：还是以自己创建的一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。

最左匹配原则：最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

覆盖索引： 覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

避免回表： 使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）如果在一个场景下，select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。

联合索引的使用： 联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。 联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引。 1.考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。 2.当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过EXPLAIN，我们可以分析出以下结果：1.表的读取顺序2.数据读取操作的操作类型3.哪些索引可以使用4.哪些索引被实际使用5.表之间的引用6.每张表有多少行被优化器查询

执行计划各字段含义 id:select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 id的结果共有3中情况: (1)id相同，执行顺序由上至下 (2)id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

(3)id相同不同，同时存在

select_type: 常见和常用的值有如下几种：

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。 SIMPLE 简单的select查询，查询中不包含子查询或者UNION PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY SUBQUERY 在SELECT或WHERE列表中包含了子查询 DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中 UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED UNION RESULT 从UNION表获取结果的SELECT

table:指的就是当前执行的表 type:type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

从最好到最差依次是：system > const > eq_ref > ref > range > index > all 一般来说，得保证查询至少达到range级别，最好能达到ref。 system :表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计 const :表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。 eq_ref :唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 ref: 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。 range :只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。 index: Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

id是主键，所以存在主键索引 all: Full Table Scan 将遍历全表以找到匹配的行

possible_keys 和 key:

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效） 查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中 key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。 ref：显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。 rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好 Extra：包含不适合在其他列中显式但十分重要的额外信息 （1）Using filesort（九死一生）说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。 (2)Using temporary（十死无生）使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

(3)Using index（发财了）表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

(4)Using where:表明使用了where过滤(5)Using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。(6)impossible where:where子句的值总是false，不能用来获取任何元组

Mysql什么情况会发生锁表？怎么提高并发性

锁表的原因：当多个连接（数据库连接）同时对一个表的数据进行更新操作，那么速度将会越来越慢，持续一段时间后将出现数据表被锁的现象，从而影响到其它的查询及更新。在mysql中，锁表的原因是一个程序执行了对表的insert、update或者delete操作还未commite时，另一个程序也对同一个表进行相同的操作，则此时会发生资源正忙的异常，也就是锁表。 1、锁表发生在insert update 、delete 中 2、锁表的原理是 数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户.第一、 A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表.第二、锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则） 3、减少锁表的概率：减少insert 、update 、delete 语句执行 到 commite 之间的时间。具体点批量执行改为单个执行、优化sql自身的非执行速度 如果异常对事物进行回滚

怎么提高并发性?高并发大多的瓶颈在后台，在存储mysql的正常的优化方案如下：（1）代码中sql语句优化（2）数据库字段优化，索引优化（3）加缓存，redis/memcache等（4）主从，读写分离（5）分区表（6）垂直拆分，解耦模块（7）水平切分 1、方法1和方法2是最简单，也是提升效率最快的方式。因为每条语句都命中了索引，是最高效的。但是如果是为了使sql达到最优而去建索引，那么索引就泛滥了，对于千万级以上的表来说，维护索引的成本大大增加，反而增加了数据库的内存的开销。 2、数据库字段的优化。曾经发现一高级程序员在表字段的设计上，一个日期类型，被设计为varchar类型，不规范的同时，无法对写入数据校验，做索引的效率也有差别 3、缓存适合读多写少更新频度相对较低的业务场景，否则缓存异议不大，命中率不高。缓存通常来说主要为了提高接口处理速度，降低并发带来的db压力以及由此产生的其他问题。 4、分区不是分表，结果还是一张表，只不过把存放的数据文件分成了多个小块。在表数据非常大的情况下，可以解决无法一次载入内存，以及大表数据维护等问题。 5、垂直拆分将表按列拆成多表，常见于将主表的扩展数据独立开，文本数据独立开，降低磁盘io的压力。 6、水平拆，水平拆分的主要目的是提升单表并发读写能力(压力分散到各个分表中)和磁盘IO性能(一个非常大的.MYD文件分摊到各个小表的.MYD文件中)。如果没有千万级以上数据，为什么要拆，仅对单表做做优化也是可以的；再如果没有太大的并发量，分区表也一般能够满足。所以，一般情况下，水平拆分是最后的选择，在设计时还是需要一步一步走。

索引和主键的关系

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。 1、主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。 2、唯一性索引列允许空值，而主键列不允许为空值。 3、主键列在创建时，已经默认为空值 + 唯一索引了。 4、主键可以被其他表引用为外键，而唯一索引不能。 5、一个表最多只能创建一个主键，但可以创建多个唯一索引。 6、主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。 7、在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

Mysql的主从复制如何实现？

一般应用对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做主从架构、进行读写分离，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何优化SQL和索引，这种方式简单有效；其次才是采用缓存的策略，比如使用 Redis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用主从架构，进行读写分离。 主从复制的作用： 第 1 个作用：读写分离。我们可以通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处 理能力。第 2 个作用就是数据备份。我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机 制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。第 3 个作用是具有高可用性。数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。 主从复制的原理：三个线程 实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

注意:不是所有版本的MySQL都默认开启服务器的二进制日志。在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。除非特殊指定，默认情况下从服务器会执行所有主服务器中保存的事件。也可以通过配置，使从服务器执行特定的事件。 复制三步骤 步骤 1 ：Master将写操作记录到二进制日志（binlog）。 步骤 2 ：Slave将Master的binary log events拷贝到它的中继日志（relay log）； 步骤 3 ：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。 复制的最大问题：延时 复制的基本原则：每个Slave只有一个Master；每个Slave只能有一个唯一的服务器ID；每个Master可以有多个Slave

Mysql主从延迟的原因

主从同步的要求：读库和写库的数据一致(最终一致)；写数据必须写到写库；读数据必须到读库(不一定，例如主从可能会切换)； 理解主从延迟问题：进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟（比如 500 ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。 主从延迟问题原因： 在网络正常的时候，日志从主库传给从库所需的时间是很短的，即T 2 - T 1 的值是非常小的。即，网络正 常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。 主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。 造成 原因：1 、从库的机器性能比主库要差2 、从库的压力大3 、大事务的执行 举例 1 ： 一次性用delete语句删除太多数据 结论：后续再删除数据的时候，要控制每个事务删除的数据量，分成多次删除。 举例 2 ： 一次性用insert...select插入太多数据 举例: 3 ： 大表DDL：比如在主库对一张500W的表添加一个字段耗费了 10 分钟，那么从节点上也会耗费 10 分钟。

如何减少主从延迟： 1.降低多线程大事务并发的概率，优化业务逻辑 2.优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。 3.提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。 4.尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网 络延时。 5.实时性要求的业务读强制走主库，从库只做灾备，备份。

执行事务会涉及到哪些过程、组件（锁、MVCC）（待解决）

 

为什么要用B+树

1.文件很大，不可能全部存储在内存中，故要存储到磁盘上 2.索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数（为什么使用B-/+Tree，还跟磁盘存取原理有关。） 3.局部性原理与磁盘预读，预读的长度一般为页（page）的整倍数，（在许多操作系统中，页得大小通常为4k） 4.数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，(由于节点中有两个数组，所以地址连续)。而红黑树这种结构，*h*明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性

事务四大特性（ACID）

原子性（atomicity）： 原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样?就会出现数据不一致的情形，A账户减去100元，而B账户增加1o0元操作失败，系统将无故丢失100元。 一致性（consistency）： （国内很多网站上对一致性的阐述有误，具体你可以参考 Wikipedia 对Consistency的阐述）根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是 语义上 的而不是语法上的，跟具体的业务有关。那什么是合法的数据状态呢？满足 预定的约束 的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务 #案例:AA用户给BB用户转账100 update account set money = money - 100 where name = 'AA'; #服务器宕机 update account set money = money + 100 where name = 'BB'; 操作之前的状态。 举例1:A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什么呢?因为你定义了一个状态，余额这列必须>=0。 举例2∶A账户20o元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢?因为你定义了一个状态，要求A+B的总余额必须不变。 举例3∶在数据表中我们将姓名字段设置为唯一性约束，这时当事务进行提交或者事务发生回滚的时候，如果数据表中的姓名不唯一，就破坏了事务的一致性要求。 隔离型（isolation）： 事务的隔离性是指一个事务的执行 不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对并发 的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 持久性（durability）： 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

三大日志，undo_log、redo_log、bin_log作用

日志系统主要有redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。

redo log日志模块 redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。 在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。 有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe。 redo log日志的大小是固定的，即记录满了以后就从头循环写。

该图展示了一组4个文件的redo log日志，checkpoint之前表示擦除完了的，即可以进行写的，擦除之前会更新到磁盘中，write pos是指写的位置，当write pos和checkpoint相遇的时候表明redo log已经满了，这个时候数据库停止进行数据库更新语句的执行，转而进行redo log日志同步到磁盘中。

binlog日志模块:binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的 redo log和binlog区别:

• redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

• redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑

• redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

• binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。 undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志： 1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。 2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

一、重做日志（redo log）作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。 二、回滚日志（undo log）作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读 三、二进制日志（binlog）：作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？ 事务的隔离性由 锁机制 实现。而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。 REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。 UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致 性。有的DBA或许会认为 UNDO 是 REDO 的逆过程，其实不然。其实不然。REDO和UNDO都可以视为是一种恢复操作 redo log:是存储引擎层(innodb)生成的日志，记录的是"物理级别"上的页修改操作，比如页号xx、偏移量ywy写入了'zzz'数据。主要为了保证数据的可靠性; undo log:是存储引擎层(innodb)生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一条与之相反的DELETE操作。主要用于事务的回滚(undolog 记录的是每个修改操作的逆操作)和一致性非锁定读(undo log回滚行记录到某种特定的版本---MVCC，即多版本并发控制)。

redo日志：InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前需要把在磁盘上的页缓存到内存中 的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（ checkPoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

为什么需要REDO日志？ 一方面，缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟，checkpoint机制可以保证数据的最终落盘，然而由于checkpoint 并不是每次变更的时候就触发 的，而是master线程隔一段时间去处理的。所以最坏的情况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。另一方面，事务包含 持久性 的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失 REDO日志的好处、特点： 好处：1.redo日志降低了刷盘频率 2.redo日志占用空间小，存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。 特点：1.redo日志是顺序写入磁盘的 .在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序Io，效率比随机Io快。 2.事务执行过程中，redo log不断记录 redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据阵层广生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中。 Undo日志 redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作 其实是要先写入一个 undo log 。 如何理解Undo日志 事务需要保证 原子性 ，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如： 情况一：事务执行过程中可能遇到各种错误，比如 服务器本身的错误 ， 操作系统错误 ，甚至是突然 断电 导的错误。 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。 以上情况出现，我们需要把数据改回原先的样子，这个过程称之为 回滚 ，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合 原子性 要求。 每当我们要对一条记录做改动时(这里的改动可以指INSERT、DELETE、UPDATE)，都需要"留一手"—把回滚时所需的东西记下来。比如: 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。(对于每个INSERT, InnoDB存储引擎会完成一个DELETE)你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。(对于每个DELETE,InnoDB存储引擎会执行一个INSERT)你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。(对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去)

MySQL把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志(即undo log)。注意，由于查询操作( SELECT）并不会修改任何用户记录，所以在杳询操作行时，并不需要记录相应的undo日志。此外，undo log 会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。 Undo日志的作用 作用1：回滚数据 用户对undo日志可能有误解: undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。 作用2：MVCC undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo_log的底层实现，数据结构

undo的存储结构 1.回滚段与undo页 InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了1024 个 undo log segment ，而在每个undo log segment段中进行 undo页 的申请。在 InnoDB1.1版本之前 （不包括1.1版本），只有一个rollback segment，因此支持同时在线的事务限制为 1024 。虽然对绝大多数的应用来说都已经够用。从1.1版本开始InnoDB支持最大 128个rollback segment ，故其支持同时在线的事务限制提高到了 128*1024 。 undo页的重用 当我们开启一个事务需要写undo log的时候，就得先去undo log segment中去找到一个空闲的位置，当有空位的时候，就去申请undo页，在这个申请到的undo页中进行undo log的写入。我们知道mysql默认一页的大小是16k。为每一个事务分配一个页，是非常浪费的(除非你的事务非常长)，假设你的应用的TPS(每秒处理的事务数目)为1000，那么1s就需要1000个页，大概需要16M的存储，1分钟大概需要1G的存储。如果照这样下去除非MySQL清理的非常勤快，否则随着时间的推移，磁盘空间会增长的非常快，而且很多空间都是浪费的。于是undo页就被设计的可以重用了，当事务提交时，并不会立刻删除undo页。因为重用，所以这个undo页可能混杂着其他事务的undo log。undo log在commit后，会被放到一个链表中，然后判断undo页的使用空间是否小于3/4，如果小于3/4的话，则表示当前的undo页可以被重用，那么它就不会被回收，其他事务的undo log可以记录在当前undo页的后面。由于undo log是离散的，所以清理对应的磁盘空间时，效率不高。 2.2. 回滚段与事务 1.每个事务只会使用一个回滚段（rollback segment），一个回滚段在同一时刻可能会服务于多个 事务。 2.当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数 据会被复制到回滚段。 3.在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用， 事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者 在回滚段允许的情况下扩展新的盘区来使用。 4.回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo 表空间。 5.当事务提交时，InnoDB存储引擎会做以下两件事情： (1)将undo log放入列表中，以供之后的purge操作.purge: 清除，清洗 (2)判断undo log所在的页是否可以重用(低于3/4可以重用)，若可以分配给下个事务使用

了解哪些关系型数据库

Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL等。

myisam与innodb的区别

1.InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3.InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。 4.InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；那为什么InnoDB没有这个变量呢？ 因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。 5.Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了。 6.MyISAM表格可以被压缩后进行查询操作。 7.InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。 8.InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有。 9.Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。Innodb：frm是表定义文件，ibd是数据文件； Myisam：frm是表定义文件，myd是数据文件，myi是索引文件。 如何选择？ 1、是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM； 2、如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。 3、系统奔溃后，MyISAM恢复起来更困难，能否接受； 4、MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

表锁与行锁区别，还有什么锁

根据加锁的范围，MySQL里面的锁大致可以分为全局锁、表级锁、行锁； 1.MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)； 2.InnoDB存储引擎既支持行级锁( row-level locking)，也支持表级锁，但默认情况下是采用行级锁。 表级锁： 开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

什么时候使用表锁？对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

• 表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；

• 而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。

什么是页锁、什么是间隙锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。 页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

常用的行锁类型: 1.记录锁（Record Locks）记录锁也就是仅仅把一条记录锁上 |2.间隙锁（Gap Locks） MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为Gap Locks的锁.

图中id值为 8 的记录加了gap锁，意味着不允许别的事务在id值为 8 的记录前边的间隙插入新记录，其实就是id列的值( 3 , 8 )这个区间的新记录是不允许立即插入的。比如，有另外一个事务再想插入一条id值为 4的新记录，它定位到该条新记录的下一条记录的id值为 8 ，而这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间( 3 , 8 )中的新记录才可以被插入。gap锁的提出仅仅是为了防止插入幻影记录而提出的 。虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁（不论是共享gap锁还是独占gap锁)，并不会限制其他事务对这条记录加记录锁或者继续加gap锁。

3.临键锁（Next-Key Locks）有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录 4.插入意向锁（Insert Intention Locks）

告诉你有一条慢sql，如何找出并优化

数据库中设置SQL慢查询: 方式一：修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）方式二：通过MySQL数据库开启慢查询. # 可以通过如下命令定位低效率执行sql show processlist；# sql 可以用 explain 分析执行计划。

sql查询速度慢的原因: 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化

优化查询方法:面试从这几方面考虑：索引+sql语句+数据库结构优化+优化器优化+架构优化。 索引: 1.尽量覆盖索引，5.6支持索引下推 2.组合索引符合最左匹配原则 3.避免索引失效 4.再写多读少的场景下，可以选择普通索引而不要唯一索引。更新时，普通索引可以使用change buffer进行优化，减少磁盘IO,将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改. 5.索引建立原则（一般建在where和order by，基数要大，区分度要高，不要过度索引，外键建索引）

sql语句: 1.分页查询优化 该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。select * from tb_sku where id>20000 limit 10; 2、优化insert语句:多条插入语句写成一条;在事务中插数据;数据有序插入（主键索引）

数据库结构优化; 1、将字段多的表分解成多个表有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。 2、对于经常联合查询的表，可以考虑建立中间表

架构优化:读/写分离（主库写，从库读）

mvcc

并发问题的解决方案 怎么解决脏读、不可重复读、幻读这些问题呢？其实有两种可选的解决方案： 方案一：读操作利用多版本并发控制（MVCC），写操作进行加锁。所谓的MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本由undo日志构建)。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象；在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。 tips: 这里幻读没解决吧!!!!! 方案二：读、写操作都采用加锁的方式。 采用MVCC方式的话，读-写操作彼此并不冲突，性能更高。采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。MVCC没有正式的标准，在不同的DBMS中MVCC的实现方式可能是不同的，也不是普遍使用的(大家可以参考相关的DBMS文档)。这里讲解InnoDB 中MVCC的实现机制（MySQL其它的存储引擎并不支持它)。 2.快照读与当前读 MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到 即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读, 而非当前读。当前读实际 上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。 2.1 快照读 快照读又叫一致性读，读取的是快照数据。 不加锁的简单的 SELECT 都属于快照读 ，即不加锁的非阻塞读；比如这样：SELECT * FROM player WHERE ... 之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。 2.2 当前读 当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不 能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。 比如：SELECT * FROM student LOCK IN SHARE MODE; # 共享锁 SELECT * FROM student FOR UPDATE; # 排他锁 INSERT INTO student values ... # 排他锁 DELETE FROM student WHERE ... # 排他锁 UPDATE student SET ... # 排他锁

3.复习 3.1 再谈隔离级别,我们知道事务有 4 个隔离级别，可能存在三种并发问题：

在MySQL中，默认的隔离级别是可重复读，可以解决脏读和不可重复读的问题，如果仅从定义的角度来看，它并不能解决幻读问题。如果我们想要解决幻读问题，就需要采用串行化的方式，也就是将隔离级别提升到最高，但这样一来就会大幅降低数据库的事务并发能力。MVCC 可以不采用锁机制，而是通过乐观锁的方式来解决不可重复读和幻读问题!它可以在大多数情况下替代行级锁，降低系统的开销。

3.2 隐藏字段、Undo Log版本链 回顾一下undo日志的版本链，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列(字段)。 trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。 roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。 能不能在两个事务中交叉更新同一条记录呢?不能!这不就是一个事务修改了另一个未提交事务修改过的数 据，脏写。InnoDB使用锁来保证不会有脏写情况的发生，也就是在第一个事务更新了某条记录后，就会给这条记录加锁，另一个事务再次更新时就需要等待第一个事务提交了，把锁释放之后才可以继续更新。每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表：

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。每个版本中还包含生成该版本时对应的事务id。 4.MVCC实现原理之ReadView MVCC 的实现依赖于： 隐藏字段、Undo Log（多版本）、Read View（并发控制和管理） 。 4.1 什么是ReadView 在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。ReadView就是某一个事务在使用MVCC机制进行快照读操作时产生的读视图(即一个ReadView和一个事务是一一对应的)。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID(“活跃"指的就是，启动了但还没提交)。 4.2 设计思路 使用READ UNCOMMITTED隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。使用SERIALIZABLE隔离级别的事务，InnoDB规定使用加锁的方式来访问记录，直接读取记录的最新版本。 使用READ COMMITTED和REPEATABLE READ隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。 这个ReadView中主要包含 4 个比较重要的内容，分别如下： 1.creator_trx_id，创建这个 Read View 的事务 ID。说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为 0 。 2、trx_ids，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。 3.up_limit_id，活跃的事务中最小的事务 ID。 4.low_limit_id，表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为 1 ，2 ， 3 这三个事务，之后id为 3 的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括 1 和 2 ，up_limit_id的值就是 1 ，low_limit_id的值就是 4 。 举例: trx_ids为tr2、tr3、tr:5和trx8的集合，系统的最大事务ID (low_limit_id)为trx8+1(如果之前没有其他的新增事务)，活跃的最小事务ID (up_limit_id)为trx2。

4.3 ReadView的规则 有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。如果被访问版本的trx_id属性值小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_ids列表中。如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

4.4 MVCC整体操作流程 了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过MVCC找到它： 1.首先获取事务自己的版本号，也就是事务 ID；2.针对这个事务生成 ReadView，和这个事务是一一对应的；3.查询得到的数据，然后与 ReadView 中的事务版本号进行比较；4.如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；5.最后返回符合规则的数据。 如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。 InnoDB中，MVCC是通过Undo Log + Read View进行数据读取，Undo Log保存了历史快照，而ReadView规则帮我们判断当前版本的数据是否可见。 在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况，就可能读到不可重复读的数据。 当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

即每次读都使用最初的 Read View，所以每次读都是一样的，所以不会出现不可重复读。

innodb索引

可以用hash索引吗

不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点：1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询1、每次都要全表扫描2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。4、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

mysql设置过参数吗？

innodb_buffer_pool_size（InnoDB）：主要针对InnoDB表性能影响最大的一个参数，可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。可通过以下方法查询是否合理 tmp_table_size：控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定。通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级分组排序操作生成的临时表。注意 max_heap_table_size 比 tmp_table_size 小时，则系统会把 max_heap_table_size 的值作为最大的内存临时表的上限。可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。 innodb_log_buffer_size ：这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer，InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit（日志刷新的频率）参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。这个值的大小主要影响到刷磁盘的次数，设置的过小，Buffer容易满，就会增加fsync的次数，设置过大，占用内存。该值默认是8M，个人觉得目前每次提交都会刷buffer，所以除非有大事务的情况，一般buffer不太可能被占满，所以没必要开的很大， 8M应该是满足需求的。 read_buffer_size：是MySql读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

用的什么版本？不同版本的区别（待解决）

######

事务隔离级别

1.脏写（ Dirty Write ）：对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写

4.幻读

脏读会导致幻读吗（待解决）