何为索引?有什么作用?
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树, B+树和 Hash。
索引的优缺点
优点 :
- 使用索引可以大大加快 数据的检索速度(大大减少检索的数据量), 这也是创建索引的最主要的原因。
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
缺点 :
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候,如果数据有索引,那么索引也需要动态的修改,会降低 SQL 执行效率。
- 索引需要使用物理文件存储,也会耗费一定空间。
但是,使用索引一定能提高查询性能吗?
大多数情况下,索引查询都是比全表扫描要快的。但是如果数据库的数据量不大,那么使用索引也不一定能够带来很大提升。
索引的底层数据结构
Hash表
1.Hash 冲突问题 :多个不同的 key 最后得到的 index 相同。将哈希冲突数据存放在链表/红黑树
2.Hash 索引不支持顺序和范围查询
B 树& B+树两者有何异同呢?
- B 树的每个节点包含key值,指针,data,而 B+树非叶子节点存放 key值 和 指针,叶子节点存放key 和data。
- B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找,可能还没有到达叶子节点,检索就结束了。而 B+树的检索效率就很稳定了,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显。
为什么这样设计,比起B树有什么好处呢?
首先,因为B+树的中间节点只是保存子树的最大数据和子树的子针,本身的占用空间较小,因此可以容纳更多节点元素,也就是说同样数据情况下,B+ 树会 B 树更加“矮胖”,因此查询效率更快。其次,查找某个范围的数据,只需在B+树的叶子节点链表中遍历即可,不需要像B 树那样挨个中序遍历比较大小。
- 层级更低,IO 次数更少;
- 每次都需要查询到叶子节点;
- 查询性能稳定叶子节点形成有序链表,范围查询方便
磁盘IO是非常昂贵的操作,所以计算机操作系统对此做了优化:预读;每一次IO时,不仅仅把当前磁盘地址的数据加载到内存,同时也把相邻数据也加载到内存缓冲区中。每次磁盘IO读取的数据我们称之为一页(page)。一般为4k/8k(与操作系统有关)。读取一页内数据的时候,实际上发生了一次磁盘IO。
数据库索引是存储在磁盘上,当表中的数据量比较大时,索引的大小也跟着增长,达到几个G甚至更多。当我们利用索引进行查询的时候,不可能把索引全部加载到内存中,只能逐一加载每个磁盘页,这里的磁盘页就对应索引树的节点。磁盘IO的次数由树的高度来决定
索引都有哪些类型?
主键索引:由数据库自动创建,不允许有空值。
单例索引 普通索引:一个索引只包含一个列,一个表可以有多个索引
唯一索引:索引列中的值必须是唯一的,但是允许为空值。
复合索引:同一个索引包含多个列
全文索引:只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引:字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引
索引的创建
1.主键索引 自动创建
create table t_user(id varchar(20) primary key,name varchar(20));
查看索引
show index from t_user;
2.单例索引
建表时创建:create table t_user(id varchar(20) primary key,name varchar(20),key(name));
建表后创建create index nameindex on t_user(name);
3.唯一索引
建表时创建:create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
建表后创建create unique index nameindex on t_user(name);
4.复合索引
建表时创建:create table t_user(id varchar(20) primary key,name varchar(20),key(name,age));
建表后创建create index nameageindex on t_user(name,age);
聚集索引与非聚集索引
聚集索引:数据和索引存储在一起。主键索引属于聚集索引。
非聚簇索引:数据和索引没有存储在一起。非聚簇索引叶子节点存储的是主键值,不是物理地址,所以非聚集索引访问数据总是需要二次查找。 如果存储的是物理地址,进行增删改 物理地址会发生该表
更新代价比聚集索引要小 。因为非聚集索引的叶子节点是不存放数据的
- 跟聚集索引一样,非聚集索引也依赖于有序的数据
- 可能会二次查询(回表) :聚簇索引比非聚簇索引的查询速度快,聚簇索引的叶子节点直接就是我们要查询的行数据。非聚簇索引查到的叶子节点是主键的值,再通过主键进行回表查询
非聚簇索引一定会查询多次吗?
不一定,通过覆盖索引也可以只查询一次
覆盖索引
查询的列包含在索引列中
索引下推
MySQL 5.6 引入的索引下推优化(index condition pushdown),可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
select * from table where name='zhangsan'and age='20';
在5.6之前,只能通过最左前缀索引规则对索引中包含的字段先做出判断,找到name,再通过回表找到下一个过滤条件。
最左前缀
根据索引创建的顺序从左向右进行匹配
复合索引:遵循最左前缀规则,但引擎在查询过程中会动态调整查询字段的顺序