MySQL数据库索引

索引
数据库
发布日期:   2020-06-05
文章字数:   2.1k
阅读时长:   7 分
阅读次数:  

1、什么是索引？

定义：索引是一种用于快速查询和检索数据的数据结构。

常见的索引结构: B树， B+树和Hash。

作用：相当于目录，比如我们字典的目录页，我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

2、为什么要使用索引？

  1. 可以大大加快数据的检索速度（大大减少的检索的数据量）。
  2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  3. 帮助服务器避免排序和临时表。
  4. 将随机IO变为顺序IO
  5. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

3、为什么索引能提高查询速度？

MySQL的基本存储结构是页，记录都存在页里边。

  • 各个数据页可以组成一个双向链表

  • 每个数据页中的记录又可以组成一个单向链表

    每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

    以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

使用索引之前

如果我们写select * from user where indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

  1. 定位到记录所在的页：需要遍历双向链表，找到所在的页
  2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

使用索引之后

底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

如果我们要找到id为8的记录，步骤如下：

从上述查找过程可以看出，没有用索引时，我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

4、Mysql索引主要使用的两种数据结构

哈希索引

哈希索引底层的数据结构就是哈希（Hash）表，在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快，能够在很短的时间内，根据Hash函数定位到数据所在的位置；其余大部分场景，建议选择BTree索引。

B Tree索引

数据库索引使用树结构进行存储，查询效率高，可以保持有序。

数据库索引为什么不使用二叉查找树实现？

从算法逻辑上来说，二叉查找树的查询速度和比较次数是最小的，但是需要考虑现实问题，就是磁盘IO的问题，数据库索引是存储在磁盘上的，数据量很大的时候，索引大小就很多，当利用索引查询的时候，我们不能把整个索引加载到内存，能做的只有逐一加载每一个磁盘页，这里的磁盘页对应着索引树的节点。当使用二叉树作为索引节点查询值的时候，在最坏情况下，磁盘IO次数等于索引树的高度，为了减少磁盘IO次数，就需要降低树的高度，所以就要引入B Tree。

B树和B+树比较？

  • B+树单一节点存储更多的元素，使得查询的IO次数更少：B树的所有节点既存放键(key) 也存放数据(data);而B+树只有叶子节点存放 key 和 data，其他内节点只存放key。
  • B+树所有叶子节点形成有序链表，便于范围查询：B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B+树的查询性能稳定：B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

B+树和红黑树的比较？

  • B+树有更少的查找次数：因为红黑树的树高很明显比 B+ Tree 大非常多，查找的次数也就更多。

  • B+树能够利用磁盘预读特性：为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

MyISAM和InnoDB实现BTree索引方式的区别？

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB

其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

Hash索引和 B+ Tree索引比较

Hash索引存在Hash冲突的问题；不支持顺序和范围查询，而B+树支持。

5、索引的缺点？

  1. 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低SQL执行效率。
  2. 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

6、Mysql中如何为表字段添加索引？

主键索引

# 添加PRIMARY KEY
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

唯一索引

# 添加UNIQUE
ALTER TABLE `table_name` ADD UNIQUE ( `column` )

普通索引

# 添加INDEX
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

全文索引

# 添加FULLTEXT
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

参考

[MySQL索引](https://snailclimb.gitee.io/javaguide/#/docs/database/MySQL Index)

索引
评论
评论
  目录