MySQL

1.MySQL MylSAM 和 InnoDB存储引擎的区别是啥？

myisam,不支持事务，不支持外键约束，索引文件和数据文件分开，这样在内存中可以缓存更多的索引，对查询的性能会更好，适用于那种少量插入、大量查询的场景。

innodb是现在最常用的存储引擎，是mysql5.5之后的默认存储引擎。主要特点就是支持事务，走聚簇索引，强制要求有主键，支持外键约束，高并发、大数据量、高可用等相关成熟的数据库架构，分库分表、读写分离、主备切换，全部都可以基于innodb存储引擎来实现。到这里就可以展开来说一说是怎么支撑大数据的，怎么进行读写分离的。

2.MySQL索引的原理和数据结构能介绍一下么？b+树和b-树有什么区别？MySQL聚簇索引和非聚簇索引的区别是什么？他们分别是如何存储的？使用MySQL索引都有哪些原则？MySQL复合索引如何使用？

索引的数据结构是什么

其实就是让你聊聊mysql的索引底层是什么数据结构实现的，弄不好现场还会让你画一画索引的数据结构，然后会问问你mysql索引的常见使用原则，弄不好还会拿个SQL来问你，就这SQL建个索引一般咋建？

至于索引是啥？这个问题太基础了，大家都知道，mysql的索引说白了就是用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构去找到那一列的值，然后找到对应的行的物理地址即可。

那么回答面试官的一个问题，mysql的索引是怎么实现的？

答案是，不是二叉树，也不是一颗乱七八糟的树，而是一颗b+树。这个很多人都会这么回答，然后面试官一定会追问，那么你能聊聊b+树吗？

但是说b+树之前，咱们还是先来聊聊b-树是啥

B-树是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(logn)的时间复杂度进行查找、顺序读取、插入和删除等操作。

B-树中允许一个结点中包含多个key，可以是3个、4个、5个甚至更多，并不确定，需要看具体的实现。现在我们选择一个参数M，来构造一个B-树，我们可以把它称作是M阶的B-树，那么该树会具有如下特点：

• 每个结点最多有M-1个key，并且以升序排列；
• 每个结点最多能有M个子结点；
• 根结点至少有两个子结点；

在实际应用中B-树的阶数一般都比较大（通常大于100），所以，即使存储大量的数据，B-树的高度仍然比较小，这样在某些应用场景下，就可以体现出它的优势。

若参数M选择为5，那么每个结点最多包含4个键值对，我们以5阶B-树为例，看看B-树的数据存储。

mysql里面一般就是b+树来实现索引，所以b+树很重要。

B+树是对B树的一种变形树，它与B树的差异在于：

1. 非叶结点仅具有索引作用，也就是说，非叶子结点只存储key，不存储value；
2. 树的所有叶结点构成一个有序链表，可以按照key排序的次序遍历全部数据。

若参数M选择为5，那么每个结点最多包含4个键值对，我们以5阶B+树为例，看看B+树的数据存储。

B+树的优点在于：

1. 由于B+树在非叶子结点上不包含真正的数据，只当做索引使用，因此在内存相同的情况下，能够存放更多的key。
2. B+树的叶子结点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。

B-树的优点在于：

由于B-树的每一个节点都包含key和value，因此我们根据key查找value时，只需要找到key所在的位置，就能找到value，但B+树只有叶子结点存储数据，索引每一次查找，都必须一次一次，一直找到树的最大深度处，也就是叶子结点的深度，才能找到value。

B+树在数据库中的应用

在数据库的操作中，查询操作可以说是最频繁的一种操作，因此在设计数据库时，必须要考虑到查询的效率问题，在很多数据库中，都是用到了B+树来提高查询的效率；
在操作数据库时，我们为了提高查询效率，可以基于某张表的某个字段建立索引，就可以提高查询效率，那其实这个索引就是B+树这种数据结构实现的。

执行select * from user where id=18,需要从第一条数据开始，一直查询到第6条，发现id=18，此时才能查询出目标结果，共需要比较6次；

执行select * from user where id>=12 and id<=18 ,如果有了索引，由于B+树的叶子结点形成了一个有序链表，所以我们只需要找到id为12的叶子结点，按照遍历链表的方式顺序往后查即可，效率非常高。

接着来聊点稍微高级点的，因为上面说的只不过都是最基础和通用的b-树和b+树罢了，但是mysql里不同的存储引擎对索引的实现是不同的。

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree：从两个方面来回答

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

• 虽然可以快速定位，但是没有顺序，IO复杂度高；
• 基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；
• 适合等值查询，如=、in()、<=>，不支持范围查询 ；
• 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序；
• Hash索引在查询等值时非常快 ；
• 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找；
• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

myisam存储引擎的索引实现

先来看看myisam存储引擎的索引实现。就拿上面那个图，咱们来现场手画一下这个myisam存储的索引实现，在myisam存储引擎的索引中，每个叶子节点的data存放的是数据行的物理地址，比如0x07之类的东西，然后我们可以画一个数据表出来，一行一行的，每行对应一个物理地址。

索引文件

id=15，data：0x07，0a89，数据行的物理地址

数据文件单独放一个文件

select * from table where id = 15 -> 0x07物理地址 -> 15，张三，22

myisam最大的特点是数据文件和索引文件是分开的，大家看到了么，先是索引文件里搜索，然后到数据文件里定位一个行的。

innodb存储引擎的索引

好了，再来看看innodb存储引擎的索引实现，跟myisam最大的区别在于说，innodb的数据文件本身就是个索引文件，就是主键key，然后叶子节点的data就是那个数据的所在行。我们还是用上面那个索引起来现场手画一下这个索引好了，给大家来感受一下。

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据：

15，data：完整的一行数据，（15,张三,22）

22，data：完整的一行数据，（22,李四,30）

就是因为这个原因，innodb表是要求必须有主键的，但是myisam表不要求必须有主键。另外一个是，innodb存储引擎下，如果对某个非主键的字段创建个索引，那么最后那个叶子节点的值就是主键的值，因为可以用主键的值到聚簇索引里根据主键值再次查找到数据，即所谓的回表，例如：

select * from table where name = ‘张三’

先到name的索引里去找，找到张三对应的叶子节点，叶子节点的data就是那一行的主键，id=15，然后再根据id=15，到数据文件里面的聚簇索引（根据主键组织的索引）根据id=15去定位出来id=15这一行的完整的数据

所以这里就明白了一个道理，为啥innodb下不要用UUID生成的超长字符串作为主键？因为这么玩儿会导致所有的索引的data都是那个主键值，最终导致索引会变得过大，浪费很多磁盘空间。

还有一个道理，一般innodb表里，建议统一用auto_increment自增值作为主键值，因为这样可以保持聚簇索引直接加记录就可以，如果用那种不是单调递增的主键值，可能会导致b+树分裂后重新组织，会浪费时间。

讲一讲MySQL的最左前缀原则?

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

索引的使用规则

一般来说跳槽时候，索引这块必问，b+树索引的结构，一般是怎么存放的，出个题，针对这个SQL，索引应该怎么来建立

select * from table where a=1 and b=2 and c=3，你知道不知道，你要怎么建立索引，才可以确保这个SQL使用索引来查询

好了，各位同学，聊到这里，你应该知道具体的myisam和innodb索引的区别了，同时也知道什么是聚簇索引了，现场手画画，应该都ok了。然后我们再来说几个最最基本的使用索引的基本规则。

其实最基本的，作为一个java码农，你得知道最左前缀匹配原则，这个东西是跟联合索引（复合索引）相关联的，就是说，你很多时候不是对一个一个的字段分别搞一个一个的索引，而是针对几个索引建立一个联合索引的。

给大家举个例子，你如果要对一个商品表按照店铺、商品、创建时间三个维度来查询，那么就可以创建一个联合索引：shop_id、product_id、gmt_create

一般来说，你有一个表（product）：shop_id、product_id、gmt_create，你的SQL语句要根据这3个字段来查询，所以你一般来说不是就建立3个索引，一般来说会针对平时要查询的几个字段，建立一个联合索引

后面在java系统里写的SQL，都必须符合最左前缀匹配原则，确保你所有的sql都可以使用上这个联合索引，通过索引来查询

create index (shop_id,product_id,gmt_create)

（1）全列匹配

这个就是说，你的一个sql里，正好where条件里就用了这3个字段，那么就一定可以用到这个联合索引的：

select * from product where shop_id=1 and product_id=1 and gmt_create=’2018-01-01 10:00:00’

（2）最左前缀匹配

这个就是说，如果你的sql里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列就行了：

select * from product where shop_id=1 and product_id=1，这个是没问题的，可以用上这个索引的

（3）最左前缀匹配了，但是中间某个值没匹配

这个是说，如果你的sql里，就用了联合索引的第一个列和第三个列，那么会按照第一个列值在索引里找，找完以后对结果集扫描一遍根据第三个列来过滤，第三个列是不走索引去搜索的，就是有一个额外的过滤的工作，但是还能用到索引，所以也还好，例如：

select * from product where shop_id=1 and gmt_create=’2018-01-01 10:00:00’

就是先根据shop_id=1在索引里找，找到比如100行记录，然后对这100行记录再次扫描一遍，过滤出来gmt_create=’2018-01-01 10:00:00’的行

这个我们在线上系统经常遇到这种情况，就是根据联合索引的前一两个列按索引查，然后后面跟一堆复杂的条件，还有函数啥的，但是只要对索引查找结果过滤就好了，根据线上实践，单表几百万数据量的时候，性能也还不错的，简单SQL也就几ms，复杂SQL也就几百ms。可以接受的。

（4）没有最左前缀匹配

那就不行了，那就在搞笑了，一定不会用索引，所以这个错误千万别犯

select * from product where product_id=1，这个肯定不行

（5）前缀匹配

这个就是说，如果你不是等值的，比如=，>=，<=的操作，而是like操作，那么必须要是like ‘XX%’这种才可以用上索引，比如说

select * from product where shop_id=1 and product_id=1 and gmt_create like ‘2018%’

（6）范围列匹配

如果你是范围查询，比如>=，<=，between操作，你只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引了

select * from product where shop_id>=1 and product_id=1

这里就在联合索引中根据shop_id来查询了

（7）包含函数

如果你对某个列用了函数，比如substring之类的东西，那么那一列不用索引

select * from product where shop_id=1 and 函数(product_id) = 2

上面就根据shop_id在联合索引中查询

索引的缺点以及使用注意

索引是有缺点的，比如常见的就是会增加磁盘消耗，因为要占用磁盘文件，同时高并发的时候频繁插入和修改索引，会导致性能损耗的。

尽量创建少的索引，比如说一个表一两个索引、两三个索引，这样在高并发场景下还可以接受。

字段，status，100行，status就2个值，0和1

你觉得你建立索引还有意义吗？几乎跟全表扫描都差不多了

select * from table where status=1，相当于是把100行里的50行都扫一遍

你有个id字段，每个id都不太一样，建立个索引，这个时候其实用索引效果就很好，你比如为了定位到某个id的行，其实通过索引二分查找，可以大大减少要扫描的数据量，性能是非常好的

在创建索引的时候，要注意一个选择性的问题，select count(discount(col)) / count(*)，就可以看看选择性，就是这个列的唯一值在总行数的占比，如果过低，就代表这个字段的值其实都差不多，或者很多行的这个值都类似的，那创建索引几乎没什么意义，你搜一个值定位到一大坨行，还得重新扫描。

就是要一个字段的值几乎都不太一样，此时用索引的效果才是最好的

还有一种特殊的索引叫做前缀索引，就是说，某个字段是字符串，很长，如果你要建立索引，最好就对这个字符串的前缀来创建，比如前10个字符这样子，要用前多少位的字符串创建前缀索引，就对不同长度的前缀看看选择性就好了，一般前缀长度越长选择性的值越高。

好了，各位同学，索引这块能聊到这个程度，或者掌握到这个程度，其实普通的互联网系统中，80%的活儿都可以干了，因为在互联网系统中，一般就是尽量降低SQL的复杂度，让SQL非常简单就可以了，然后搭配上非常简单的一个主键索引（聚簇索引）+ 少数几个联合索引，就可以覆盖一个表的所有SQL查询需求了。更加复杂的业务逻辑，让java代码里来实现就ok了。

大家要明白，SQL达到95%都是单表增删改查，如果你有一些join等逻辑，就放在java代码里来做。SQL越简单，后续迁移分库分表、读写分离的时候，成本越低，几乎都不用怎么改造SQL。

对互联网公司而言，多用MySQL当在线的即时存储，存数据，简单的取出来。不要用MySQL来计算，特别是在高并发场景下，如写join、子查询、函数放MySQL里等；应该把计算放java内存里，通过写java代码来做；可以合理利用mysql的事务支持

3、说说事务的几个特性是啥？有哪几种隔离级别？

事务的ACID

这个先说一下ACID，必须得知道：

（1）Atomic：原子性，就是一堆SQL，要么一起成功，要么都别执行，不允许某个SQL成功了，某个SQL失败了，这就是扯淡，不是原子性。

（2）Consistency：一致性，这个是针对数据一致性来说的，就是一组SQL执行之前，数据必须是准确的，执行之后，数据也必须是准确的。别搞了半天，执行完了SQL，结果SQL对应的数据修改没给你执行，那不是坑爹么。

（3）Isolation：隔离性，这个就是说多个事务在跑的时候不能互相干扰，别事务A操作个数据，弄到一半儿还没弄好呢，结果事务B来改了这个数据，导致事务A的操作出错了，那不就搞笑了。

（4）Durability：持久性，事务成功了，就必须永久对数据的修改是有效的，别过了一会儿数据自己没了，不见了，那就好玩儿了。

事务隔离级别

总之，面试问你事务，先聊一下ACID，然后聊聊隔离级别

（1）读未提交，Read Uncommitted：这个很坑爹，就是说某个事务还没提交的时候，修改的数据，就让别的事务给读到了，这就恶心了，很容易导致出错的。这个也叫做脏读。

（2）读已提交，Read Committed（不可重复读）：这个比上面那个稍微好一点，但是一样比较尴尬

就是说事务A在跑的时候， 先查询了一个数据是值1，然后过了段时间，事务B把那个数据给修改了一下还提交了，此时事务A再次查询这个数据就成了值2了，这是读了人家事务提交的数据啊，所以是读已提交。

这个也叫做不可重复读，就是所谓的一个事务内对一个数据两次读，可能会读到不一样的值。如图：

（3）可重复读，Read Repeatable：这个比上面那个再好点儿，就是说事务A在执行过程中，对某个数据的值，无论读多少次都是值1；哪怕这个过程中事务B修改了数据的值还提交了，但是事务A读到的还是自己事务开始时这个数据的值。如图：

（4）幻读：不可重复读和可重复读都是针对两个事务同时对某条数据在修改，但是幻读针对的是插入

比如某个事务把所有行的某个字段都修改为了2，结果另外一个事务插入了一条数据，那个字段的值是1，然后就尴尬了。第一个事务会突然发现多出来一条数据，那个数据的字段是1。

那么幻读会带来啥问题呢？因为在此隔离级别下，例如：事务1要插入一条数据，我先查询一下有没有相同的数据，但是这时事务2添加了这条数据，这就会导致事务1插入失败，并且它就算再一次查询，也无法查询到与其插入相冲突的数据，同时自身死活都插入不了，这就不是尴尬，而是囧了。

串行化：如果要解决幻读，就需要使用串行化级别的隔离级别，所有事务都串行起来，不允许多个事务并行操作。如图：

（6）MySQL的默认隔离级别是Read Repeatable，就是可重复读，就是说每个事务都会开启一个自己要操作的某个数据的快照，事务期间，读到的都是这个数据的快照罢了，对一个数据的多次读都是一样的。

接下来我们聊下MySQL是如何实现Read Repeatable的吧，因为一般我们都不修改这个隔离级别，但是你得清楚是怎么回事儿，MySQL是通过MVCC机制来实现的，就是多版本并发控制，multi-version concurrency control。

当我们使用innodb存储引擎，会在每行数据的最后加两个隐藏列，一个保存行的创建时间，一个保存行的删除时间，但是这儿存放的不是时间，而是事务id，事务id是mysql自己维护的自增的，全局唯一。

事务id，在mysql内部是全局唯一递增的，事务id=1，事务id=2，事务id=3

事务id=121的事务，查询id=1的这一行的时候，一定会找到创建事务id <= 当前事务id的那一行

select * from table where id=1，就可以查到上面那一行

事务id=122的事务，将id=1的这一行给删除了，此时就会将id=1的行的删除事务id设置成122

事务id=121的事务，再次查询id=1的那一行，能查到吗？

能查到，要求创建事务id <= 当前事务id，当前事务id < 删除事务id

事务id=121的事务，查询id=2的那一行，查到name=李四

事务id=122的事务，将id=2的那一行的name修改成name=小李四

事务id=121的事务，查询id=2的那一行，答案是：李四，创建事务id <= 当前事务id，当前事务id < 删除事务id

在一个事务内查询的时候，mysql只会查询创建时间的事务id小于等于当前事务id的行，这样可以确保这个行是在当前事务中创建，或者是之前创建的；

同时一个行的删除时间的事务id要么没有定义（就是没删除），要么是比当前事务id大（在事务开启之后才被删除）；满足这两个条件的数据都会被查出来。

那么如果某个事务执行期间，别的事务更新了一条数据呢？这个很关键的一个实现，其实就是在innodb中，是插入了一行记录，然后将新插入的记录的创建时间设置为新的事务的id，同时将这条记录之前的那个版本的删除时间设置为新的事务的id。

现在get到这个点了吧？这样的话，你的这个事务其实对某行记录的查询，始终都是查找的之前的那个快照，因为之前的那个快照的创建时间小于等于自己事务id，然后删除时间的事务id比自己事务id大，所以这个事务运行期间，会一直读取到这条数据的同一个版本。

记住，聊到事务隔离级别，必须把这套东西给喷出来，尤其是mvcc，说实话，市面上相当大比重的java程序员，对mvcc是不了解的