下列mysql会用到索引的是_面试系列 mysql索引

数据结构介绍(理解为主)

红黑树

(红黑树需要二次整理干净一些吧，现在看起来还是有点乱)

红黑树和AVL树区别AVL树是严格的平衡二叉树，平衡条件必须满足(所有节点的左右子树高度差不超过1)。不管我们是执行插入还是删除操作，只要不满足上面的条件，就要通过旋转来保持平衡。它的高度会更低，所以AVL树适合用于插入与删除次数比较少，但查找多的情况。

红黑树是弱平衡二叉树，它通过着色限制的关系，确保没有一条路径会比其它路径长出两倍，相对于AVL树来说，它的旋转次数少，所以对于搜索，插入，删除操作较多的情况下，我们就用红黑树。

红黑树的性质(主要回答点)：红色结点不可能相连，黑色节点可以相连

根节点是黑色节点

每个红色节点的两个子节点都是黑色，叶子节点都是黑色(Null节点)

从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

插入的点默认都是红色

左旋

左旋的理解不要纠结于字面记忆，只要记住向左旋转，记住它的动态过程，自己旋一下

右旋

千万别死记步骤，记动态过程，留下根深蒂固印象

插入规则

插入的过程是递归向上检查并变换的过程，并不是插一个节点变化一次。

变颜色当前结点的父亲是红色，且它的爷爷结点的另一个结点(叔叔)也是红色：把父结点设为黑色

把叔叔结点设为黑色

把爷爷结点设为红色

把指针定位到爷爷节点继续往上分析变换规则

左旋当前父节点是红色，叔叔是黑色的时候，且当前的结点是右子树，左旋以父结点作为左旋

右旋当前父结点是红色，叔叔是黑色的时候，且当前节点是左子树：把父点变成黑色

把爷爷节点变成红色

以爷爷节点进行右旋

以XX节点进行左旋/右旋，XX节点对应着E节点

红黑树插入删除实例 红黑树的删除很复杂，面试的时候只介绍上面规则，剩下就说不懂？

B树

select * from user where user_id=100 # 索引查找

select * from user where user_id<100 and user_id>0 # 范围查找

select * from user where user_id = 100 and name = "赵云" # 联合索引查找

M阶Btree的性质：结点最多含有m颗子树(指针)，m-1个关键字(存的数据，空间)(m>=2)

除根结点和叶子结点外，其他每个结点至少有m/2(向上取整)个子节点

若根节点不是叶子节点，则至少有两颗子树

插入规则

B+树

B+树特点叶子节点用指针连在一起形成双向链表

非叶子节点仅用作索引，子节点存储数据，它的非叶子节点和叶子节点有重复元素

概要，关键字和Key值，数据存储的地方，双向链表

B+树的阶数M=磁盘页大小16K/字段

这是mysql为了尽可能地利用连续空间，一次刚好能全部拿出一个节点的数据data块存储内

数据库中一行记录大小10K，一个表只有主键索引，可以存多少条数据？你有没有试过数据库一个表中最多可以插入多少数据

补充说明：

每个节点都是一个二元数组: [key, data]，所以才会有data域，叶子节点的地方有两种不同的颜色

为什么用B+树(必背)--要结合整个演变过程的优缺点回答

Hash

mysql是提供了BTREE和HASH的索引方法优点：查询性能快，只需要O(1)定位时间，只适用于等值查询

缺点：无法进行范围查询

重复键会造成哈希冲突，影响性能

二叉查找树优点：查找性能快，O(logn)

缺点：不平衡情况下会退化为链表 O(n)

时间复杂度为log(n) ,n为节点。(计算公式：时间复杂度为树的高度x：2^x=n>>x=logn)

红黑树优点：保证了不会出现不平衡情况，适合用于内存型数据结构(为什么hashmap用红黑树)

缺点：索引是存储在磁盘文件上，磁盘上的存储是随机存储，每次都需要转圈去读取一个节点，这样当树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低效的情况

磁盘每次读取是读一页，一页只是为了获取一个节点的数据，也会造成性能浪费

B树优点：一个节点存储多个数据，更好的利用磁盘页的大小

n叉树树的高度降低，磁盘读取次数降低

缺点非叶节点存储数据，查询效率不稳定

非叶节点存储key和data指针，导致一个节点内能存的key数量降低，导致树的高度增高

B树不适合做范围查询，必须用中序遍历的方法按序扫库

B+树优点：叶子节点用指针连在一起形成双向链表，增加了遍历的高效性

非叶节点不带数据，可以更高效使用索引空间

缺点：B+树的数据只出现在叶子节点上，单次查询性能不如B树

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。

B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操

聚簇索引和非聚集索引

MyISAM存储引擎非聚集索引/稀疏索引主索引：MyISAM索引文件和数据文件是分离的,叶子节点存放的是数据文件对应的磁盘文件地址指针，从存储文件也可以看出。因此多了一个间接查询的过程(只能有一个)

辅助索引/普通索引：MyISAM的主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。(辅助索引并不是指联合索引)

InnoDB存储引擎聚集索引/密集索引主索引的区别：InnoDB索引文件和数据文件是在一起的，表数据文件本身就是按照B+Tree组织的一个索引结构。因此InnoDB的表必须有主键(MyISAM可以没有)，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。(只能有一个)

辅助索引/普通索引：InnoDB的辅助索引同样是一颗B+Tree,只是data域为主键

联合索引包含了多列值的非聚集索引。多个字段组合成一个索引。根据最左匹配原则进行排序插入

叶子节点包括一条具有a,b,c和主键的记录

最左前缀

1.如果你创建一个联合索引, 那 这个索引的任何前缀都会用于查询, (col1, col2, col3)这个联合索引的所有前缀 就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索 引查询.

2.其他所有不在最左前缀里的列都不会启用索引, 即使包含了联合索引里的部分列也不行. 即上述中的(col2), (col3), (col2, col3) 都不会启用索引去查询.

注意, (col1, col3)会启用(col1)的索引查询

select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到

select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引

select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引

select * from T1 where b = 12 and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化

select * from T1 where b = 12 and d = 3;-- 应用到一列索引 因为不能跨列使用索引 没有c列 连不上

select * from T1 where c = 14 and d = 3;-- 无法应用索引，违背最左匹配原则

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，效率提升可想而知

覆盖索引

什么样的查询会有回表查询?

回表查询

InnoDB的辅助索引需要扫描两遍索引树，先定位主键值，再定位行记录。

覆盖索引概念：一棵索引树覆盖了查询语句中字段与条件的数据，只需要一遍索引树扫描即可，往往这样的索引树都是联合索引。

遇到以下情况，执行计划不会选择覆盖查询select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。

(默认使用索引下推，所以不需要背这个情况)

where条件中不能含有对索引进行非等值查询。比如：

select * from xxx where a>1 and b=2

非等值查询后，后续字段无法直接通过索引树确定范围。(因为比如a>1 a的范围是(1,∞),mysql就会认为这样的话需要对所有a下的记录进行遍历查询那还不如回表遍历查询)

MySQL 5.6 引入的索引下推优化(index condition pushdown)， 可以在索引遍历过程中，可以同时对索引字段做判断，直接过滤掉不满足条件的记录，减少二次查找遍历数。即允许非等值查询后具有其他索引字段

可以有多个聚簇索引吗

不可以，一个表只能有一个聚簇索引，可以有多个辅助索引。

为什么mysql主键不能太大

因为每个索引都存储这个值，在磁盘有限，内存珍贵的情况下，MySQL存储的索引与数据会减少，磁盘IO的概率会增加。新建一列id作为主键。

把字段hash为另外一个字段存起来，每次校验hash就好了，hash的索引也不大。

采用倒序，或者删减字符串这样的情况去建立我们自己的区分度。(比如邮箱www可以删掉)

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键/为什么不推荐使用UUID为了满足MySQL的索引数据结构B+树的特性，必须要有索引作为主键，可以有效提高查询效率。如果不手动指定主键，InnoDB会从插入的数据中找出不重复的一列作为主键索引，如果没找到不重复的一列，InnoDB会在后台增加一列rowId做为主键索引。

整型的储存空间更小；整数比较过程比UUID要快，因为字符串要转ASCII码再比较

如果非自增，插入的过程为无序插入，很容引起B+树的节点分裂和平衡，影响插入效率，如果是自增主键，只需要尾节点插入

什么时候创建索引？(高频面试题)

一般来说，应该在这些列上创建索引:在经常需要搜索、排序、WHERE的列上，可以加快对应操作的速度。

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。

同样，对于有些列不应该创建索引(因为索引是用空间换时间的一种方式)。一般来说，不应该创建索引的的这些列具有下列特点：对于那些在查询中很少使用或者参考的列不应该创建索引。

对于那些只有很少数据值的列也不应该增加索引。当数据量不大时，全表扫描也是可以接受的。

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

当修改操作比大于检索操作更频繁时，不应该创建索引。因为需要频繁的维护索引结构。

整理来源https://www.cnblogs.com/aspirant/p/9214485.html​www.cnblogs.com

https://www.bilibili.com/video/BV1hE41147tP?p=7​www.bilibili.com

联合索引在B+树上的存储结构及数据查找方式​juejin.cn

官方文档解释MySQL最左匹配(最左前缀)原则​juejin.cn