MySQL索引面试题六连击

川长思鸟来 2023-07-07 04:58 12阅读 0赞

## MySQL索引面试题六连击 ##

最近在微信公众号上看到一篇推文，关于MySQL索引面试题的，觉得写得挺好，整理了一下形成博客总结。  
原文链接：[关于MySQL索引面试题的6连炮！招架的住吗？][MySQL_6]

#### 1、面试真题 ####

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

#### 2、面试题剖析 ####

1.索引的数据结构是什么？  
MySQL的索引说白了就是用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构去找到那一列的值，然后找到对应的行的物理地址即可。那么回答面试官的一个问题，MySQL的索引是怎么实现的？答案是**B+树**！  
2.然后面试官一定会追问，那么你能聊聊b+树吗？  
但是说b+树之前，咱们还是先来聊聊b-树是啥，从数据结构的角度来看，b-树要满足下面的条件：  
（1）d为大于1的一个正整数，称为B-Tree的度。  
（2）h为一个正整数，称为B-Tree的高度。  
（3）每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。  
（4）每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。  
（5）所有叶节点具有相同的深度，等于树高h。  
（6）key和指针互相间隔，节点两端是指针。  
（7）一个节点中的key从左到右非递减排列。  
（8）所有节点组成树结构。  
（9）每个指针要么为null，要么指向另外一个节点。  
（10）如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。  
（11）如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。  
（12）如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。  
上面那段规则，我也是从网上找的，说实话，没几个java程序员能耐心去看明白或者是背下来，大概知道是个树就好了。就拿个网上的图给大家示范一下吧：  
比如说我们现在有一张表：

(
    id int
    name varchar
    age int
    )

我们现在对id建个索引：15、56、77、20、49

select * from table where id = 49
    select * from table where id = 15

![在这里插入图片描述][20200221090016238.png]  
以上就是b-树的数据结构，查找的时候，就是从根节点开始二分查找。

b+树跟b-树不太一样的地方在于：  
（1）每个节点的指针上限为2d而不是2d+1。  
（2）内节点不存储data，只存储key；  
（3）叶子节点不存储指针。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]

select * from table where id = 15
    select * from table where id>=18 and id<=49

一般数据库的索引都对b+树进行了优化，加了顺序访问的指针，如网上弄的一个图，这样在查找范围的时候，就很方便，比如查找18~49之间的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 1]  
3. myism存储引擎的索引实现  
先来看看myisam存储引擎的索引实现。就拿上面那个图，咱们来现场手画一下这个myisam存储的索引实现，在myisam存储引擎的索引中，每个叶子节点的data存放的是数据行的物理地址，比如0x07之类的东西，然后我们可以画一个数据表出来，一行一行的，每行对应一个物理地址。  
索引文件：  
![在这里插入图片描述][20200221090415872.png]  
id=15，data：0x07，0a89，数据行的物理地址  
数据文件单独放一个文件  
![在这里插入图片描述][20200221090431892.png]

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

myisam最大的特点是数据文件和索引文件是分开的，大家看到了么，先是索引文件里搜索，然后到数据文件里定位一个行的。  
4. innodb存储引擎的索引  
好了，再来看看innodb存储引擎的索引实现，跟myisam最大的区别在于说，innodb的数据文件本身就是个索引文件，就是主键key，然后叶子节点的data就是那个数据的所在行。我们还是用上面那个索引起来现场手画一下这个索引好了，给大家来感受一下。  
![在这里插入图片描述][20200221090515942.png]  
innodb存储引擎，要求必须有主键，会根据主键建立一个默认索引，叫做聚簇索引，innodb的数据文件本身同时也是个索引文件，索引存储结构大致如下：  
15，data：0x07，完整的一行数据，（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+树分裂后重新组织，会浪费时间。  
5. 索引的使用规则  
一般来说跳槽时候，索引这块必问，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在联合索引中查询

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

我们给的建议，尽量创建少的索引，比如说一个表一两个索引，两三个索引，十来个，20个索引，高并发场景下还可以。

字段，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的事务支持

[MySQL_6]: https://mp.weixin.qq.com/s/6se7cEwqB9Ko9ciePIz7rw
[20200221090016238.png]: https://img-blog.csdnimg.cn/20200221090016238.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200221090137843.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200221090216532.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ==,size_16,color_FFFFFF,t_70
[20200221090415872.png]: https://img-blog.csdnimg.cn/20200221090415872.png
[20200221090431892.png]: https://img-blog.csdnimg.cn/20200221090431892.png
[20200221090515942.png]: https://img-blog.csdnimg.cn/20200221090515942.png