InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。

叶子节点的数据就是用户所要查询的数据如:用户需要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出10条记录 SELECT * FROM Profile ORDER BY id LIMIT 10; 虽然使用ORDER BY对主键id记录进行排序，但是在实际过程中并没有进行所谓的filesort操作，而这就是因为聚集索引的特点。另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。如：SELECT * FROM Profile where id > 1 and id <100

4.1.5 什么是辅助索引

它是指定所在列为索引构成的一颗B+Tree，非叶子节点是列值，叶子节点是列值+主键值。当通过辅助索引查询后，再通过主键值去聚簇索引中获取整行数据。

对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉 InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此 InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页

4.1.6 什么是联合索引

多个字段联合作为索引构成的B+Tree。

4.1.7 什么是覆盖索引

一种特效。innodb存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，然后不需再去聚集索引中获取数据，从而减少大量的IO操作。

举例：name是索引，select name，primary key1，primary key2 form t where name like '张%'，此时就满足覆盖索引，不会再去聚集索引中查询。

某些统计也是直接通过辅助索引查询，不会去聚集索引统计。因为辅助索引小于聚集索引。

4.1.8 如何选索引列

看数据值的随机性
- 正例：时间
- 反例：性别，低选择性
通过sql语句，show index from table
- 查看cardinality的值，表示索引中不重复记录数量的预估值，内部采用采样算法，Cardinality / n_rows_in_table（表的总行数）应尽可能地接近1。

4.1.9 MySQL是如何获取索引所在内存中的数据页的

哈希表，利用hash哈希算法。冲突时使用链表方式，hash函数采用除法散列方式。

4.1.10 索引何时会失效？

优化数据库查询，就是要避免索引失效，尽可能的使用到索引查询。

快记方法：LOL、+-*/、not、null、method、not concat not convert、join、不一样的我，不一样的版本。

L：like，满足最左匹配原则并不会打破索引，开头前带%会打破索引。
O：or，or前后有一个不是索引的话就会索引失效。
L：联合索引，abc，不满足最左匹配也会引起索引失效
+-*/：如果让字段进行计算，必定会打破索引
not：非，不等于，取反的结构都失效
null：is null，is not null，不会百分百失效。MySQL中允许存在null值。
- MySQL不会对null值进行索引，null的值在外面漂离。如果都不会null，也会使用到索引
- 避免字段为null，字段的原则，比如让age字段不会为空，null，保证数据都在索引上。
method：使用函数时会导致索引失效
not concat、convert：比如：数据库字段类型是a varchar，在查询的时候where a = 1。那么数据库查询的时候会默认使用convert函数，导致索引失效。
join：俩个表进行关联时，如果字段类型不一样和都不是索引键的话也会索引失效。
不一样的我，不一样的版本：例如：select * from t where a>10，a为索引键，辅助索引。
- 5.6之前，即使a为索引，*也不会用到聚集索引。
- 5.6包含之后，a为索引，会使用到聚集索引。
- 引入离散读的问题

离散读现象：

5.6之前：

例如：索引字段a，在对a进行范围查找（select * from t where a>1and a<100000），join连接等操作时，在执行explain查看执行计划，结果显示并没有走辅助索引a，而是选择primary id聚集索引也就是聚集索引。因为如果强制使用a字段索引，就会造成离散度。具体原因是在于查询的数据是整行数据，而c1作为辅助索引不能覆盖到我们要查询的信息，因此在对c1索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然c1索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散度操作。如果要求访问的数据量很小，则优化器还是回选择辅助索引，但是当访问的数据占整个表中数据蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

5.6包含且开始：优化离散读

MRR：MySQL5.6版本开始支持multi-range read优化，目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。MRR优化可适用于range，ref，eq_ref类型的查询。

在查询辅助索引的时，对查询结果的主键排序，然后根据排序的顺序进行书签查找。

我是如何优化的：在非必要的情况下，拒绝使用select * ；在必须select * 的情况下，尽量使用MySQL5.6+的版本并开启MRR；在必须select * 且MySQL小于5.6版本下，可以根据数据量进行离散读和聚集索引俩种情况下的性能进行对比，必要时采用force index语句强制指定索引。

ICP优化：index condition pushdown简称ICP；和MRR一样，5.6版本开始支持的一种根据索引进行查询的优化方式。在老版本不支持ICP进行索引查询时，首先根据索引查找记录，然后再根据where条件来过滤记录。在支持ICP后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是where的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。

4.2 数据库无意删除了数据，如何恢复？

保证数据库热备份（xtrabackup）
MySQL数据库开启binlog，通过binlog进行恢复

4.3 锁和事务

4.3.1 innodb中锁是什么级别，有几种

俩种标准的行级锁：

共享锁：允许事务读一行数据。

排它锁：允许事务删除或更新一行数据。占有锁，其他事务不能同时占有

锁兼容：多个事务可以对于同一行数据能获取到锁

意向锁：允许事务在行级上的锁和表级上的锁同时存在。意向锁是将锁定的对象分为多个层次，意味着事务希望在更细粒度（fine granularity）上进行加锁。

意向共享锁：事务想要获得一张表中某几行的共享锁

意向排它锁：事务想要获得一张表总某几行的排他锁

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

MySQL锁的三种算法：

锁本条记录
锁范围
在默认的隔离级别下，可以将单行锁和间隙锁结合起来成为next-key lock锁

4.3.2 事务隔离

a：read uncommitted：读取未提交数据，可能发生脏读，因为未提交，几乎碰不到脏读的问题，除非修改隔离级别。

b：read committed：读取提交数据，可能造成幻读和不可重复读，二者差不多，2次读取查的结果不一样。违背了事务的隔离性。

幻读：重点在于新增和删除，同一个事务中二次查询发现记录数不一样
不可重复读：重点在于修改，同一个事务中二次查询发现数据值不一样

一个事务中先查询了一次数据库，并获取结果，此时开始做其他逻辑，另外一个事务也开始进行新增，修改，删除操作后并且提交事务。事务2提交之后，事务1进行第二次查询，发现数据记录数或数据值不一样就会造成幻读和不可重复读。

c：repeatable read：可以重复读取数据（默认），使用next-key lock锁机制。

d：serializable read：可以序列化串行读取数据，啥都安全，唯一就是慢

4.3.3 MVCC

multi version concurrency control 简称MVCC，一致性非锁定读和多版本并发控制。

基于MySQL自己的回滚机制为并发场景下无锁生成读视图进行的读操作，读取的一个优化。

是为读的多版本

基于undo log（是对事务回滚的log）机制，是mvcc的底层原理。共识1
1. 事务回滚log包含：data_trx_id 记录事务ID 和 data_roll_ptr上一版的指针
并发场景读操作不需要加锁的目的，共识2
1. 一致性视图，记录未提交的事务ID数组，

4.3.4 MVCC在不同隔离级别下的表现

场景：3个线程，t1、t2、t3。t1和t2都为写操作，t3是读操作。三个线程同时开启事务：

t3读操作开始，保留一份快照S1，开始sleep

t1开始执行，提交后生成一个新的快照，S2

t2开始执行，提交后生成第二个新的快照，S3

a：MVCC不起作用，有写有读，t1线程未提交数据，t3就已经读出来了。

b：MVCC的表现：读取的最新快照S3，t2执行后提交的快照。

c：MVCC的表现：读取的是最原始的快照版本即S1

d：MVCC不起作用，读写都是串行的，单独执行（读写按照顺序执行，无并发）

4.3.5 ACID：数据库事务的四个特性。

A：atomicity原子性：一个事务中要么一起成功要么一起失败，底层使用undo log实现

C：consistency一致性：一个事务执行之前和之后数据库必须处于一致性状态

I：isolation隔离性：并发环境中，并发的事务是相互隔离的，MVCC在read committed中违背了隔离性。应保证一个线程一个事务。

D：durability持久性：一旦事务提交，数据永久保存到数据库中。

4.3.6 说说innodb是如何进行一致性非锁定读的（MVCC的原理）

一致性非锁定读是指innodb存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行delete或update操作，这是读取操作不会因此去等待上锁的释放。相反地，innodb存储引擎会去读取行的一个快照数据。

之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行之前的版本，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（multi version concurrency control，MVCC）

4.4 扩展

4.4.1 redo log、bin log、undo log

redo log：重做日志，在innodb存储引擎层产生，物理格式日志，记录的是每个页的修改

redo buffer，是个数据缓存池，数据库定期将buffer数据刷新到redo log中，查询时，首先去buffer查询，如果没有则添加到buffer中。

bin log：二进制日志，任何数据库引擎在mysql上层产生的，记录SQL语句

undo log：用于事务回滚，undo存放在数据库内部的一个特殊段（segment）中，成为undo段，位于共享表空间内。除了回滚操作另一个作用是MVCC，既在innodb存储引擎中MVCC实现是通过undo来完成。当用户读取一行数据是，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取，最后也是最为重要的一点是，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

4.4.2 redo log 持久化日志

redo log持久化，在事务提交之前，会生成一个redo buffer log，在事务执行之后，然后再把redo buffer log磁盘刷新到redo log中。

插入性能瓶颈？

a：ID自增，ID有锁，5.1.22之后将AUTO-INC Locking轻量级锁，改为三种策略，1：原来的轻量级锁，记号器，2：对于已知的插入数据，提前分配好id，互斥量。如果对于insert select 插入为只的插入数据，还是用1，3：不管什么情况都使用互斥量，那么可能会导致ID不连续，当然不会导致重复，因为他是unique的。如果ID不连续，验证影响主从同步问题，如果id不一致，主从同步数据就不对了。

b：commit，mysql执行完之前，是redo buffer log缓存形式，在执行commit时之后，才会将缓存里面的redo buffer log进行（fille synchronized ）file flush磁盘刷新，最终生成redo log，

插入性能提升：

采用批量的方式，jdbc中有addBatch（）方法。放到同一个事务中进行执行。

redo log 磁盘刷新持久化数据有3种策略：

1：每提交一个事务，就磁盘刷新一次，保存一次（默认使用）
2：每隔一秒，磁盘刷新一次，通过当前执行的线程执行（不推荐，如果1秒内出现问题，对于系统来说也是不安全的）
3：从来不刷新，以为着没有log，如果服务器宕机，就没有数据了。

4.4.3 数据库查询缓存

MySQL提供了这种“按需使用”的方式，可以将my.cnf参数query_cache_type 设置成2，query_cache_type有3个值：0代表关闭查询缓存，1代表开启，2代表当sql语句中有SQL_CACHE关键词时才缓存。确定要使用查询缓存的语句，用 SQL_CACHE显式指定，比如，select SQL_CACHE * from user where ID=5；第三步，如果没有命中查询缓存，就要开始真正执行语句了。MySQL 需要知道你要做什么，需要对 SQL 语句做解析。

如果设置为1，缓存遇到修改时，删除时会对缓存情况，那么遇到频繁的事务操作时，缓存的性能也降低了。

4.5 数据库单表查询如何优化？

字段优化：

尽量使用 TINYINT（1）、SMALLINT（2）、MEDIUMINT（3）作为整数类型而非INT（4），如果非负加上UNSIGNED。小字节的类型。
VARCHAR的长度只分配真正需要的空间
使用枚举或者整数代替字符串类型
尽量使用timestap（4个字节）而非datetime（8个字节）
单表不要有太多字段，建议20以内
避免使用NULL字段，很难查询优化且占用额外索引空间
用整形来存IP

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成有一个预定义的集合。MySQL存储枚举时非常紧凑，会根据列表值得数量压缩到一个或者俩个字节中。MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的.frm 文件中保存 “数字 - 字符串” 映射关系的 “查找表”。如enum（’man‘，'woman'）实际存储为 1 和 2，减少数据占用空间。
TIMESTAMP 占用 4 字节，DATETIME占用8字节，且TIMESTAMP在多数场景下容易转换。
可为 NULL 的列会使索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要做特殊处理。当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM 里面甚至可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
整型字段的比较比字符串效率高很多，这也符合一项优化原则：字段类型定义使用最合适（最小），最简单的数据类型。 inet_aton () 算法，其实借用了国际上对各国 IP 地址的区分中使用的 ip number。
索引优化，读写分离

查询SQL优化：

可通过开启慢查询来找出比较慢的SQL
不做列运算：select id where age + 1 = 10 ，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
SQL语句尽可能简单：一条sql只能在一个cpu运算；大语句拆成小语句，减少锁时间；一条大SQL可以堵死整个库
不用select *
or 改写成in； or的效率是n级别，in的效率是log（n）级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%XXX式查询,最左匹配原则
少用join
使用同类型进行比较，比如用'123'和'123'比，123和123比
尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行权标扫描
对于连续数值，使用between不用in，select id from t where num between 1 and 5；
列表数据不要拿全表，要是用limit来分页，每页数量也不要太大

表分区：

分区类型：
- range，基于属于一个给定连续区间的列值
- list分区，类似range分区
- hash分区，基于用户定义的表达式的返回值进行选择分区
- key分区，类似于hash分区，
分区适合的场景有：
- 最适合的场景数据的时间序列性比较强，则可以按照时间来分区
如果单表存储数据太大，分区表也不支持
- 使用垂直拆分。
- 使用水平拆分。
- 分库分表
  - 唯一id: UUID,数据库自增ID，使用redis生成ID。

4.6 数据库死锁了，生产环境如何避免死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。

然而在线上环境中，这可能导致并发性能的下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的undo log，这时采用FIFO的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

因此，除了超时机制，当前数据库还都普遍采用wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息：锁的信息链表和事务等待链表

事务T1指向T2边的定义为：

事务T1等待事务T2所占用的资源
事务T1最终等待T2所占用的资源，也就是事务之间在等待相同的资源，而事务T1发生在事务T2的后面

通过等待图可以发现存在回路（t1，t2），因此存在死锁。通过上述的介绍，可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

4.7 如何选择表的列作为索引更加高效

并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，称为低选择性不建议添加索引，当然也要根据自身项目和场景的需求。如：

SELECT*FROM student WHERE sex='M' 按性别进行查询时，可取值的范围一般只有'M'、'F'。因此上述SQL语句得到的结果可能是该表50%的数据（假设男女比例1∶1），这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最适合的。

怎样查看索引是否是高选择性的呢？可以通过SHOW INDEX结果中的列Cardinality来观察。Cardinality值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引

4.8 主从复制

复制是MySQL数据库提供的一种可用高性能的解决方案。复制的工作原理大致分为三个步骤：

主服务器master把数据更改记录到二进制bin log中
从服务器slave把主服务器的二进制日志复制到自己的中继日志relay log
1. 有俩个线程，一个是IO线程，负责读取主服务器的bin log，并将其写入到中继日志中。
2. 另一个是SQL线程，复制执行中继日志。
从服务器重做中继日志的日志，把更改应用到自己的数据库中，已达到最终的数据一致性。

复制不是完全实时同步的，而是异步实时。这中间存在主从服务器间的执行延时，如果主服务器压力过大，则可能导致主从复制延时。

主从复制bin log日志记录方式：

SBR，statement-based- replication，master将SQL statementI写入到bin log中，默认方式
RBR，row-based-replication，将每行的变更信息写入到bin log中
mix-format replication，混合模式，在这种模式下，master将根据根据存储引擎、变更操作类型等，从SBR、RBR中来选择更合适的日志格式，默认为 SBR；具体选择那种格式，这取决于变更操作发生的存储引擎、statement 的类型以及特征，优先选择 “数据一致性” 最好的方式（RBR），然后才兼顾性能，比如 statement 中含有 “不确定性” 方法或者批量变更，那么将选择 RBR 方式，其他的将选择 SBR 以减少 binlog 的大小。我们建议使用 mix 方式。

主从复制方式：

异步复制：
半同步复制
增强半同步复制

主从复制的好处：

在从服务器可以执行查询工作，降低主服务器压力；主库写，从库读，降压，读写分离
在从服务器进行备份，避免备份期间影响主服务器服务，容灾
当主服务器出现问题是，可以切换到从服务器，提高可用性

4.9 innodb和myisam

是否支持行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁 (rowlevel locking) 和表级锁 , 默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性 , 其执行速度比 InnoDB 类型更快，但是不提供事务支持。但是 InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表。
是否支持外键： MyISAM 不支持，而 InnoDB 支持。
是否支持 MVCC：仅 InnoDB 支持。应对高并发事务 , MVCC 比单纯的加锁更高效 ;MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作 ;MVCC 可以使用乐观 (optimistic) 锁和悲观 (pessimistic) 锁来实现 ; 各数据库中 MVCC 实现并不统一。

致敬河北王校长

MySQL

4.1 索引B+数相关

4.1B+树？

4.1.1计算B+树能存储多少数据

4.1.2为什么不选择B树，二叉树，hashmap，跳表等结构

4.1.3最左前缀原则

4.1.4 什么是聚集索引