1、MyISAM存储获与InnoDB存储引擎 

MyISAM存储引擎不支持事务、表锁设计、支持全文索引。缓冲池只缓存索引文件，并不缓存数据文件。 

InnoDB存储引擎支持事务、行锁设计、支持外键，支持一致性的非锁定读，也就是默认读取的操作不会产生锁。通过多版本控制来获得高并发性，并实现了SQL标准的4种隔离级别。 

对于MyISAM来说，data存的是数据地址。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

 

对于InnoDB来说，data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

 

2、MySQL中的InnoDB中有哪些锁？

如下图所示。

上图只是针对InnoDB存储引擎来说的，如对于此引擎来说，意向锁其实是表级别的锁。

行锁在实现时有3种算法，如下：

（1）Record Lock：间个行记录上锁

（2）Gap Lock：间隙锁，锁定一个范围，但不包含记录本身 

（3）Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身，可解决幻读问题

注意，如果走唯一索引，那么Next-Key Lock会降级为Record Lock，即仅锁住索引本身，而不是范围。也就是说Next-Key Lock前置条件为事务隔离级别为RR且查询的索引走的非唯一索引、主键索引。 

下面我们通过具体的例子来模拟上面出现的幻读问题： 

CREATE TABLE T (id int ,name varchar(50),f_id int,PRIMARY KEY (id), KEY(f_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into T SELECT 1,'张三',10;
insert into T SELECT 2,'李四',30;

InnoDB在数据库中会为索引维护一套B+树，用来快速定位行记录。B+索引树是有序的，所以会把这张表的索引分割成几个区间。

 

事务A执行如下语句，需要将张三修改成李四。  

select * from t;
update t set name = '李四' where f_id = 10;

这时SQL语句走非唯一索引，因此使用 Next-Key Lock加锁，不仅会给f_10=10的行加上行锁，而且还会给这条记录的两边添加上间隙锁，即(-∞,10]、(10,30]这2个区间都加了间隙锁。　　

此时如果B事务要执行如下语句，都会报错 [Err] 1205 - Lock wait timeout exceeded; try restarting transaction

INSERT INTO T SELECT 3,'王五'，10;  -- 满足行锁，执行阻塞
INSERT INTO T SELECT 4,'赵六'，8;   -- 满足间隙锁，执行阻塞
INSERT INTO T SELECT 5,'孙七'，18;  -- 满足间隙锁，执行阻塞

不仅插入 f_id = 10 的记录需要等待事务A提交，f_id <10、10< f_id <30 的记录也无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。　　

刚刚讲的是f_id 是索引列的情况，那么如果 f_id不是索引列会怎么样呢？ 

这时候数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 f_id 是否大于等于30，都要等待事务A提交才可以成功插入。  

3、数据库事务隔离级别

总结一下： 

√: 可能出现    ×: 不会出现

事务的隔离级别	Read  uncommitted  未提交读	Read committed 提交读	Repeatable read  可重复读	Serializable 序列化
脏读  事务1更新了记录，但没有提交，事务2读取了更新后的行，然后事务T1回滚，现在T2读取无效。违反隔离性导致的问题，添加行锁实现	√	×	×	×
不可重复读  事务1读取记录时，事务2更新了记录并提交，事务1再次读取时可以看到事务2修改后的记录（修改批更新或者删除）需要添加行锁进行实现	√	√	×	×
幻读   事务1读取记录时事务2增加了记录并提交，事务1再次读取时可以看到事务2新增的记录。需要添加表锁进行实现。InnoDB存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制和间隙锁解决了该问题	√	√	√	×

注意点： 
（1）要分清不可重复读和幻读的区别 一个是更新记录，另外一个是读取了新增的记录，
（2）与脏读比起来，不可重复读和幻读都是读取了事务提交后的记录，脏读是读取了事务未提交的记录。 
（3）脏读和不可重复读采用行锁避免，而幻读采用Next-Key Lock锁来避免。 Innodb默认的repeatable read隔离级别下就可以做到避免幻读的问题。
 
4、事务的ACID属性

事务与锁是不同的。事务具有ACID属性：

（1）原子性（atomicity）：由redo log重做日志来保证事务的原子性

（2）持久性（consistency）：由redo log重做日志来保证事务的持久性

（3）一致性（isolation）：undo log用来保证事务的一致性

（4）隔离性（durability）：一个事务在操作过程中看到了其他事务的结果，如幻读。锁是用于解决隔离性的一种机制。事务的隔离级别通过锁的机制来实现。

5、InnoDB的索引

InnoDB的常见索引如下图所示。

另外还有术语“联合索引”和“覆盖索引”，联合索引可能是聚集索引，也可能是非聚集索引，因为如果在建立表时，通过primary指定多个列为主键，则这多个列也算是联合索引，那么也是聚集索引。覆盖索引是非聚集索引，SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

6、索引建立原则

1、最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c用到了>符号。如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

3、索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = 2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

7、一致性的非锁定读

MVCC（Multiversion concurrency control ）是一种多版本并发控制机制。
（1）插入
同一事务中（假设事务id=1）插入两条记录,记录的数据版本号为事务id=1,删除版本号为null，如下图所示。
 

（2）查询

查询时需要同时满足以下两个条件：

1、查找数据版本号，早于（小于等于）当前事务id的数据行。 这样可以确保事务读取的数据是事务之前已经存在的。或者是当前事务插入或修改的。

2、查找删除版本号为null或者大于当前事务版本号的记录。 这样确保取出来的数据在当前事务开启之前没有被删除。

假如有一个事务中执行查询（假设事务id=2）

事务id为2

start transaction;
select * from user where sex = '男';  --（1）
select * from user where sex = '男';  --（2）
commit;

假设在执行这个事务ID为2的过程中,刚执行到(1)，这时有另一个事务（假设事务id=3）往这个表里插入了一条数据；

事务id为3

start transaction;
INSERT INTO user (name,sex) VALUES ('王五','男');
commit;

此时表中的数据如下：　　

 

然后接着执行事务 id=2 中的(2)，由于id=3的数据的创建时间(事务ID为3)，执行当前事务的ID为2，而InnoDB只会查找事务ID小于等于当前事务ID的数据行，所以 id=3 的数据行并不会在执行事务 id=2 中的 (2) 被检索出来。在事务 id=2 中的两条select 语句检索出来的数据都只会下表：

 

（3）删除

假如有一个事务中执行查询（假设事务id=4）　 

事务id为4

start transaction;
select * from user where sex = '男';  --（1）
select * from user where sex = '男';  --（2）
commit;

假设事务 id=4 刚执行到（1），此时有另外一个事务 id=5 执行了删除语句，会更新数据的删除版本号为当前事务id = 5　　

事务id为5

start transaction;
DELETE FROM user WHERE id = 1;
commit;

此时数据库表中数据如下：

  

接着执行事务 id=4的事务(2)，根据SELECT 检索条件可以知道，它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行，表中id=1的行由于删除时间(删除事务的ID)大于当前事务的ID，所以事务 id=2 的(2)在执行的时候也会把表中 id=1 的数据检索出来，所以事务4中的两条select 语句检索出来的数据都如下：

（4）修改

可以理解为，当一个事务中 修改一条记录时， 是先复制该数据，新数据数据版本号为当前事务id,删除版本号为 null 。然后更新 原来数据的删除版本号为 当前事务id。如下：

假如一个事务 id=6 执行了一条update语句

事务id为6

start transaction;
UPDATE user SET name='李四1' WHERE id = 2
commit;

执行结果如下：　　

 

可参考：https://www.cnblogs.com/ilovejaney/p/14439030.html 

8、MySQL大数据量分页优化 

首先需要新建一张辅助分页的表pagination，有id与page字段，类型都为整数。同步id值与t_report_app表中的id,如下：  

INSERT INTO pagination(id) SELECT id FROM t_report_app

然后使用如下语句插入page值。　 

SET @p:= 0;  // 声明一个变量p并赋初始值为0
UPDATE pagination SET page=CEIL((@p:= @p + 1) / 10) ORDER BY id DESC;

我们按每页10条记录，id降序进行分页。如果插入或删除记录则需要同步pagination表记录。　

这样如果我们查询第10页的记录就可以直接知道t_report_app记录中10条数据的id了，如下：

从SQL本身也可以出发进行优化。

存在SQL：SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的问题最大：取出N+M行，丢弃前N行，返回 N ~ N+M 行的记录，如果N值非常大，效率极差（表记录1500w，N=10000000,M=30 需要9秒）。

解决办法：SQL：SELECT id FROM ttl_product_info WHERE id > N LIMIT M，id 列是索引列，id > N属于 range 级别，效率自然高，然后从位置开始取30条记录，效率极高（表记录1500w，N=10000000,M=30，需要0.9毫秒）。

当然想要实现上述效果的前提是：

id是唯一索引，而且单调递增。

N 的值是上一次查询的记录的最后一条id，（需要前端保存一下，不能直接用传统的方法获得）

不支持跨页查询，只能按照第1，2，3，4页这样查询逐页查询。

9、SQL性能分析 

MySQL的调优手段，主要包括慢日志查询分析与Explain查询分析SQL执行计划。

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。执行计划包含的信息如下：

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 **两种情况** id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行 id值如果为NULL则最后执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple 简单的select查询，查询中不包含子查询或者UNION 2、primary 查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery 在select或where列表中包含了子查询 4、derived 在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union 如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行 注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

---

还可以使用慢查询， MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

使用mysqldumpslow、pt-query-digest进行日志分析，可参考：http://www.shixinke.com/mysql/slow-query-log

10、数据库表水平拆分和垂直拆分

（1）水平拆分举例

假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1…qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

（2）垂直拆分举例 

通常我们按以下原则进行垂直拆分: 
1，把不常用的字段单独放在一张表;, 
2，把text，blob等大字段拆分出来放在附表中; 
3，经常组合查询的列放在一张表中;

例如存储一个文件时，简要的常见信息放到一张表中，而真正存储文件内容的text放到另外一张附表中。　

11、为什么InnoDB用B+树而不用B树？为什么不用AVL树？为什么不用红黑树？

B+中只有叶子节点存储数据信息，所以非叶子节点有更多的空间存储key，可以使得树更矮（扇出大），所以IO操作次数更少。B+的非叶子节点存储数据信息。 

树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
红黑树搜索效率也高，为什么数据库使用b+树，这是因为红黑树仍然是一棵二叉树，相同数量的节点，红黑树更深，不适用与磁盘的IO。红黑树相较于AVL树来说，对平衡要求的宽松，大大减少了旋转带来的耗时问题，平衡树旋转是很耗时的，因此，在内存中使用红黑树效率高，如hashmap等。
对于AVL来说：
（1）维护平衡过程的成本代价很高，因为每次删除一个节点或者增加一个节点的话，需要一次或者多次的左旋，右旋等去维护“平衡”状态，
（2）还有就是节点存储的数据内容太少。没有很好利用操作系统和磁盘数据交换特性，也没有利用好磁盘IO的预读能力。因为操作系统和磁盘之间一次数据交换是已页为单位的，一页 = 4K，即每次IO操作系统会将4K数据加载进内存。但是，在二叉树每个节点的结构只保存一个关键字，一个数据区，两个子节点的引用，并不能够填满4K的内容。幸幸苦苦做了一次的IO操作，却只加载了一个关键字，在树的高度很高，恰好又搜索的关键字位于叶子节点或者支节点的时候，取一个关键字要做很多次的IO。因此平衡二叉树也是不太符合MySQL的查询结构的。
（3）如果节点很多的话，那么这个AVL树的高度还是会很高的，那么查询效率还是会很低

12、主键与聚集索引

由于一个表只能有一个聚集索引，并且如果该表设置了主键，默认主键就是聚集索引。但由于主键列在查询条件中并没有频繁作为查询条件使用，这时候在主键上建立聚集索引就太浪费资源了，这种情况下我们可以删除主键约束，给主键建立唯一约束，保证主键列值没有重复值，删除主键约束后就可以在其他列上建立聚集索引。

如果表中已经建立了其他列为聚集索引，再在一个列上面创建主键时，这时候主键列只能是非聚集索引。 

13、InnoDB什么时候会退化为表级锁

众多资料中都说innodb使用的是行级锁，但实际上是有限制的。只有在你增删改查时匹配的条件字段带有索引时，innodb才会使用行级锁，在你增删改查时匹配的条件字段不带有索引时，innodb使用的将是表级锁。因为当你匹配条件字段不带有所引时，数据库会全表查询，所以这需要将整张表加锁,才能保证查询匹配的正确性。在生产环境中我们往往需要满足多人同时对一张表进行增删改查，所以就需要使用行级锁，所以这个时候一定要记住为匹配条件字段加索引。 

14、MySQL 如何保证持久性？MyISAM 能保证持久性吗

MyISAM 能保证持久性吗，因为MyISAM不支持事务，所以没有ACID属性。

当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log（回滚日志）和binlog内容决定回滚数据还是提交数据。

15、介绍一下mysql redo log和bin log的作用，MySQL binlog 里是逻辑日志还是物理日志？会记录查询SQL吗

重做日志文件为redo log，用来保存原子性和一致性，而bin log为二进制日志。

第一:redo log是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层产生的，并且二进制日志不仅仅针对INNODB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

第二：两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。

第三：两种日志与记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。

二进制日志仅在事务提交时记录，并且对于每一个事务，仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于innodb存储引擎的重做日志，由于其记录是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，其在文件中记录的顺序并非是事务开始的顺序。

第四：binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。

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

16、为什么尽量选择单调递增数值类型的主键

InnoDB中数据记录本身被存于主索引（B+树）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的结点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页。

如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引结点的后续位置，当一页写满，就会自动开辟一个新的页，这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插入到现有索引页的中间某个位置，此时MySQL不得不为了将新记录查到合适位置而移动元素，甚至目标页可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

简单的说：

索引树只能定位到某一页，每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。 

18、如何保证缓存和数据库的一致性  

延时双删除，如下：  

（1）先淘汰缓存  

（2）再写数据库（这两步和原来一样）  

（3）休眠1秒，再次淘汰缓存  

这么做，可以将1秒内所造成的缓存脏数据，再次删除。  

无法做到强一致性，只能做到最终一致性。 

db更新分为两个阶段，更新前及更新后，更新前的删除很容易理解，在db更新的过程中由于读取的操作存在并发可能，会出现缓存重新写入数据，这时就需要更新后的删除。

19、mysql 的主从延迟的原因 

MySQL主从复制延迟是怎样形成的：  

1、主库的worker线程在写binlog的时候是并发工作的（并行写入），而主库的dump线程和从库的IO线程都是单线程推拉binlog，特别是SQL线程是拿着relay log中的event逐一单线程回放的（5.6版本开启slave_parallel_workers支持特定情况下的并行复制，5.7版本之后全面支持并行复制后在复制层面已极大改善了延迟问题）。因此即使不考虑网络延迟，主流MySQL版本在高并发的情况下，消费很可能赶不上生产，采用异步复制的从库很有可能跟不上主库的进度。

2、在复制期间，无论是主库或从库负载高（特别是从库落盘压力大，关系到sync_binlog、innodb_flush_log_at_trx_commit的设置）或者是网络传输慢（特别是跨机房的同步）等情况发生时，都会产生主从延迟，并且是不可避免的。如果要实现强一致性，可采用Semi-sync，但采用该plugin也无法保证持续强一致性（rpl_semi_sync_master_timeout会引起复制模式的降级） 

20、select语句的执行流程 

  

①通过客户端/服务器通信协议与 MySQL 建立连接。 

②查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。 

③预处理器生成新的解析树。 

④查询优化器生成执行计划。 

⑤查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。 

21、组合索引(Composite Index)中多个字段的顺序 

联合索引时会遵循最左前缀匹配的原则，即最左优先。

CREATE INDEX t_idx ON t (column_1,column_2,column_3);

当查询(column_1)、(column_1,column_2)(column_1,column_2,column_3)这三种组合是可以用到我们定义的联合索引的。如果我们查询(column_1,column_3)就只能用到column_1的索引了。我们不用太关心索引的先后顺序，什么意思呢？比如使用(column_1,column_2)和(column_2,column_1)的效果是一样的，数据库的查询优化器会自动帮助我们优化我们的sql，看哪个执行的效率最高

可参考：https://www.cnblogs.com/ricklz/p/12508704.html　

22、那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？ 

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效 

3、组合索引，不是使用第一列索引，索引失效。 

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。 

5、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）。索引列不能参与计算，保持列“干净” 

比如from_unixtime(create_time) = 2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

  

23、b+树可以存储的数据条数

以InnoDB引擎为例，简单计算一下一颗B+树可以存放多少行数据。

B+树特点：只有叶子节点存储数据，而非叶子节点存放的是用来找到叶子节点数据的索引（如上图：key和指针）

InnoDB存储引擎的最小存储单元为16k（就像操作系统的最小单元为4k 即1页），在这即B+树的一个节点的大小为16k 

假设数据库一条数据的大小为1k，则一个节点可以存储16条数据

而非叶子节点，key一般为主键假设8字节，指针在InnoDB中是6字节，一共为14字节，一个节点可以存储 16384/14 = 1170个索引指针。 

可以算出一颗高度为2的树（即根节点为存储索引指针节点，还有1170个叶子节点存储数据），每个节点可以存储16条数据，一共1170*16条数据  = 18720条

高度为3的树，可以存放 1170 * 1170 * 16 = 21902400条记录

两千多万条数据，我们只需要B+树为3层的数据结构就可以完成，通过主键查询只需要3次IO操作就能查到对应记录。

24、主从未完全同步，master挂了，未同步的内容会造成什么影响，怎么恢复　

参考：线上MySQL读写分离，出现写完读不到问题如何解决  

25、数据库死锁

避免死锁最直观的方法就是在两个事务相互等待时，当一个事务的等待时间超过设置的某一阈值，就对这个事务进行回滚，另一个事务就可以继续执行了。这种方法简单有效，在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的。 

26、mysql的Join原理以及索引数据结构和实现原理

只知道mysql innoDB join只支持Nested Loop，不支持Hash Join，就是确定一个驱动表后不断Join得到结果集，再继续往下Join。所以Join的顺序很重要。

  

7、数据库范式与反范式

1、范式

数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式： 

（1）第一范式(确保每列保持原子性) 

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。

比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才满足数据库的第一范式。 

（2）第二范式(确保表中的每列都和主键相关) 

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

 订单信息表

 

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

 

（3）第三范式(确保每列都和主键列直接相关,而不是间接相关) 

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。 

完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。 

（4）反范式  

通过适当的数据冗余，来提高读的效率

如何查询订单详情信息？

SELECT
b.用户名,
b.电话,
b.地址,
a.订单ID,
sum(c.商品价格* c.商品数量)AS 订单价格,
c.商品价格,
d.商品名称
FROM '订单表' a
JOIN '用户表' b ON a.用户ID = b.用户ID
JOIN '订单商品表' c ON c.订单ID = b.订单ID
JOIN '商品表' d ON d.商品ID = c.商品ID
GROUP BY b.用户名,b.电话,b.地址,a.订单ID,c.商品价格,d.商品名称

该查询需要关联多张表，然后再通过sum汇总出价格，查询效率不太高。 如果通过表中部分数据的冗余，进行反范式化设计，如下图：

简化sql的查询 

SELECT
b.用户名,
b.电话,
b.地址,
a.订单ID,
a.订单价格,
c.商品价格,
c.商品名称
FROM '订单表' a
JOIN '用户表' b ON a.用户ID = b.用户ID
JOIN '订单商品表' c ON c.订单ID = b.订单ID

互联网项目中，读写比率大概是3:1或是4:1的关系，读远远高于写，写的时候增加数据冗余，增加了读的效率，这样还是很值得的。

反范式的目的是减少读取数据的开销，那么随之带来的就是更多写数据的开销。因为我们需要预先定稿大量的数据副本。

反范式还会带来数据的不一致，可以通过异步的写来进行定期数据整理，修复不一致的数据。