Java教学视频百度云资源，MySQL进阶，秒变大神(2)

对于那些定义为text, image和bit数据类型的列不应该增加索引。（这是因为，这些列的数据量要么相当大，要么取值很少。）

当修改性能远远大于检索性能时，不应该创建索引。（这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。）

17、什么是mysql联合索引？

联合索引是指对表上的多个列做索引。在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

最左前缀匹配原则：

最左优先，在检索数据时从联合索引的最左边开始匹配。

对列col1、列col2和列col3建一个联合索引：KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 相当于建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

（1）

SELECT * FROM test WHERE col1="1" AND clo2="2" AND clo4=|"4" 

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

（2）索引的字段可以是任意顺序的，如：

白嫖资料

 SELECT * FROM test WHERE col1=“1” AND clo2=“2”

　　SELECT * FROM test WHERE col2=“2” AND clo1=“1” 

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都保函索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

（3）如果只查询col2：SELECT * FROM test WHERE col2=2;

第一个col字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个字段col2进行条件判断是用不到索引的。当然是col2字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？在col1字段是等值匹配的情况下，cid才是有序的。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。

为什么要使用联合索引？

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

白嫖资料

从本质上来说，联合索引还是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

对于查询 SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然可以使用(a,b)这个联合索引。对于单个的a列查询 SELECT * FROM TABLE WHERE a=xxx 也是可以使用(a,b)索引。但对于b列的查询 SELECT * FROM TABLE WHERE b=xxx 不可以使用这颗B+树索引。因为叶节点上的b值为1,2,1,4,1,2，显然不是排序的，因此对于b列的查询使用不到(a,b)的索引。

联合索引的第二个好处是，可以对第二个键值进行排序。例如，在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，去除最近3次的购买记录，这是使用联合索引可以避免多一次的排序操作，因为索引本身在叶节点已经排序了。

【注】：对于相同的第一个键值的数据，第二个键值是排好序的。

对于单个列a的查询往往使用单个键的索引，因为其叶节点包含单个键值，能存放的记录更多。

18、说一说 B+树索引、哈希索引？

Hash索引和B+树索引的特点：

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;

B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。

Hash索引与B+树索引区别？

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

19、B树和B+树的区别？

B+树是一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶结点指针进行连接。

白嫖资料

（平衡二叉树AVL：首先符合二叉查找树的定义（左结点的值比根节点小，右结点的值比根结点大），其次必须满足任何节点的左右两个子树的高度最大差为1。）

B树 ：每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。

B+树：所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分。

20、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

（1）B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

（2）B+tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

21、聚集索引和非聚集索引区别?

数据库中的B+索引可以分为聚集索引和辅助聚集索引。不管是聚集索引还是非聚集的索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据，聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。

聚集索引(clustered index)：

聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

最后

给大家送一个小福利

资料都是免费分享的，附送高清脑图，高清知识点讲解教程，以及一些面试真题及答案解析。送给需要的提升技术、准备面试跳槽、自身职业规划迷茫的朋友们。点我免费领取！！！

片转存中…(img-aXdsiOpA-1628284771826)]

资料都是免费分享的，附送高清脑图，高清知识点讲解教程，以及一些面试真题及答案解析。送给需要的提升技术、准备面试跳槽、自身职业规划迷茫的朋友们。点我免费领取！！！

[外链图片转存中…(img-MOBcZRIP-1628284771828)]