开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 7 天，点击查看活动详情
1.索引的本质
索引的本质就相当于"书的目录",通过目录就能快速定位到我们需要的某个章节的位置
索引的主要作用就是为了加快查找的速度
在数据库操作中,查询的频率是非常高的,使用索引可以帮助我们快速查找到所需要的信息
缺点
1.数据库索引提高查询速度的同时也增加了增加删除修改操作的开销,进行增删改操作之后,调整数据之后还要修改索引,因此增加了其他开销,但是这是次要矛盾,主要矛盾是查询的速度,相比之下还是很值得的
2.不仅如此,索引还提高了空间的开销,构造索引需要额外的硬盘空间来保存
虽然有这些缺点,但是他能解决我们的主要矛盾,在软件开发中会经常遇到这样的问题.一般的都没有那个方法能解决所有问题,需要进行取舍,解决主要矛盾
2.索引的使用
2.1查看索引
mysql> show index from student3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
如果表里有主键,主键这列就会自动创建索引
还有unique,foreign key 的列也会自动创建索引
2.2创建索引
mysql> create index index_name on student3(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student3;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| student3 | 1 | index_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
此时就有两个索引,针对name新加了一个索引
在创建索引的时候,最好是在表创建的时候就把索引创建好,否则,如果这个表的记录十分多了,再创建索引,就很危险了!!是因为此时创建索引会花很长的时间,占用了大量的的磁盘IO,此时是无法对数据库进行访问的的,也无法正常使用,那带来的损失就太大了
2.3删除索引
mysql> drop index index_name on student3;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
此时只剩一个索引了,和刚刚创建索引相似的是,删除索引也会有较大的开销,所以在创建表的时候我们就要规划好索引,一旦表里有大量的数据了,再进行操作就需要慎重考虑了!!
那么创建好了索引,是怎么使用索引的呢?
创建好索引之后,是不需要手动的调用的,SQL是通过数据库的执行引擎来执行的,涉及到一些优化操作,执行引擎会自动评估哪种方案成本最低速度最快,可以使用explain关键字显示出查询过程中索引的具体使用情况,结果分析还是比较复杂的
3.索引的数据结构
MySQL中索引的数据结构是什么呢?
索引既然能极大提高搜索的效率,我们肯定能先想到的数据结构就是哈希表,哈希表的查询时间复杂度是O(1),但是哈希表不适合做数据库的索引,原因在于哈希表只能比较相等,无法进行范围查询,像<>这样的操作都不行
3.1B树
其次,二叉搜索树查询元素的时间复杂度是O(N),相比于哈希表,二叉搜索树好像可以进行范围查询了,但是还存在一个问题,当元素数太多时,树的高度就会比较高,而数的高度又决定了树查询的时候比较的次数,数据库比较的时候需要读取硬盘,因此更希望书的高度能降低一点,那么就考虑使用N叉搜索树了
N叉搜索树,每个节点有很多个值,同时有很多的分叉,降低了树的高度,减少了比较的次数
一种典型的实现N叉搜索树的方式就是B树
我们看一下B树的结构
编辑
这种结构降低了树的高度,没有减少比较次数(但是在一个节点上比较多次了),减少了对硬盘的读写次数,节点都是保存在硬盘上的,能一定程度的解决问题,适合做索引
3.2B+树
还有种更适合做索引的数据结构,就是B+树
编辑
B+树的特点:
1.B+树也是一个N叉树,增加了新的特点,每个节点上包含N个Key,N个Key划分出N个区间,每个区间的最后一个key就是最大值
2.父元素的Key会在子元素中出现并且为最大值,重复出现导致了,叶子节点就包含了所有数据的全集!
那么非叶子结点的所有元素都在叶子节点中体现
3.叶子节点用类似于链表的形式相连起来,构成了B+树
B+树这个数据结构做索引好处太明显了
1.既有B树高度比较低的特点,又更适合范围查询,比如查找>6且<15的元素,结果集非常容易取得,效率很高
2.对于所有的查询,都要落在叶子节点上,中间的比较次数是差不多的,查询操作比较均衡
对B树来说,在根节点或者深度不深的元素查询快,别的地方查询慢,不均衡,B+树都是一样的,都落在叶子节点上了
3.由于所有的Key都会在叶子节点中出现,因此非叶子节点不用存表的真实记录,只要把说有的数据行放在叶子节点上即可,非叶子节点只用存索引列的值,比如id这些,非叶子节点占用的空间就很小了,有可能在内存中放进去缓存了,更进一步降低了硬盘IO,提高了查询的速度
综上,B+树是非常适合作为索引的数据结构的
有的表不只是有主键索引,还有别的非主键列也有索引,此时会构造另一个B+树,非叶子节点里面存储这一列的Key,到了叶子节点这一层不再存储完整的数据行了,而是存储主键索引的id,那么使用主键索引查询时只用查一次B+树就好了,使用非主键列索引要先查一遍另外构造的B+树,然后查一次主键列的B+树(这个操作称为回表操作)
当前B+树这个结构适用于MySQL的InnoDB这个数据引擎,不同的数据库,不同的引擎存储数据的数据结构还是有差异的
