索引在数据库中可以说是相当重要的一块知识点了,也是面试经常被问的,这篇文章就总结一下索引相关的知识点,包括索引的底层实现原理,索引的分类,最左匹配原则等。
索引能快速找出在某一列中有一特定值的行。不使用索引,mysql必须从第一条记录开始查找,直到找到相关的行。如果表中查询的列有一个索引,mysql能快速到达某个位置去搜索数据文件,而不必查看所有数据。索引是对数据库中一列或多列的值进行排序的一种结构。
(1)在创建表指定索引列。
(2)使用alter table语句在存在的表上创建和删除索引
// 创建索引
alter table table_name add index(column_name);
// 删除索引
alter table table_name drop index index_name;
(3)使用create index语句在已存在的表上创建和删除索引
// 创建索引
create index index_name on table_name(column_name);
// 删除索引
drop index index_name on table_name;(内部被映射成alter table语句)
(1)索引可以加快数据库的检索速度。 (2)索引会降低插入,删除,修改的速度。 (3)索引需要占用物理和数据空间。 (4)索引的最左匹配原则。 (5)索引的分类:聚集索引和非聚集索引。 (6)MySQL支持Hash索引和B+树索引。
这还得从MySQL的存储结构说起。MySQL的基本存储结构是页。(记录都存在页里面)
所以说,如果我们写select * from user where username = 'CodeTiger'这样没有进行任何优化的sql语句,默认会这样做: (1)定位到记录所在的页。 (2)需要遍历双向链表,找到所在的页。 (3)从所在的页中找到对应的记录。 (4)由于不是根据主键查询,只能遍历所在页的单链表。
很明显,在数据量很大的情况下这样检索会很慢。
那么索引究竟是怎么提高检索速度的呢?
其实就是将无序的数据转换成有序的(相对)。
要找到id为8的简要步骤:
很明显的是:没有用索引我们是需要遍历双向链表来定位对应的页,现在通过目录就可以很快地定位到对应的页上了!
其实底层结构就是B+树,B+树作为树的一种实现,能够让我们很快地查找出对应的记录。
B+树是平衡树的一种。
平衡树:它是一棵空树或它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一棵平衡二叉树。
一棵普通的树,在极端的情况下, 会退化成单链表。
B+树是平衡树的一种,是不会退化成链表的,树的高度都是相对比较低的(基本符合矮矮胖胖(均衡)的结构)【这样一来我们检索的时间复杂度就是O(logn)】!从上面我们也可以看见,建立索引实际上就是建立一颗B+树。
综上,我们可以得到下面的知识点
除了B+树之外,还有一种常见的是哈希索引。
哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
看起来Hash索引挺快的,但其实它有一些局限性
主流的还是使用B+树索引比较多,对于哈希索引,InnoDB是自适应哈希索引的(hash索引的创建由InnoDB存储引擎引擎自动优化创建,我们干预不了)!
简单概括
区别
非聚集索引也叫做二级索引。非聚集索引在建立的时候也未必是单列的,可以多个列来创建索引。
在创建多列索引的时候,也涉及到一种特殊的索引---->覆盖索引。
比如说:
(1)使用or做连接字时,若or左右两边有一边不是组合索引中的字段,失效。
(2)最左匹配原则。
(3)模糊查询时,当%在前缀时,索引失效。当前缀没有%,后缀有%时,索引失效。
(4)如果列类型为字符串,则where查询时一定要用引号括起来,否则索引失效。
(5)当全表扫描速度比索引速度快时,MySQL会使用全表扫描,索引失效。
END