Mysql索引

最新推荐文章于 2024-06-16 00:29:40 发布

暴走的小帅

最新推荐文章于 2024-06-16 00:29:40 发布

阅读量504

点赞数

文章标签： mysql 数据库 java Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_71243923/article/details/129241064

版权

为什么我们要有索引呢?

假设一张表中有100万条数据, 这些数据都是存储在硬盘的数据页上的, 一页数据大小为 16K，存储 100 万条数据需要很多数据页，假设其中有一条数据是 id='7900'，如果要查询这条数据，其 SQL 是 SELECT *FROM 表名称 WHERE id = 7900。mysql 需要扫描全表来查找 id=7900 的记录。全表扫描就是从“数据页 1”开始，向后逐页查询。对于少量的数据，查询的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100 万条数据逐页查询的时间是无法被用户接受的。

索引: 索引就是排好序的帮助mysql高效查询数据的数据结构

数据库在存储数据本身之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址, 为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引的查找原理都是缩小查找数据的范围, 来筛选出想要的结果, 当然也可以吧随机的时间变成有序的, 借助索引就不必吧全表进行查找一遍.

索引优势: 提高检索的效率, 降低数据库IO的成本, 通过索引对数据库进行排序, 降低排序的成本, 降低了CPU的消耗.

索引劣势: 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录, 所以索引列也是要占用磁盘空间的。虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行INSERT,UPDATE 和 DELETE,因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

索引的分类

1.主键索引

设定为主键后数据库会自动建立索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除建主键索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

2.单值索引

一个索引只包含单个列，一个表可以有多个单列索引创建单值索引

CREATE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名;

3.唯一索引:索引列的值必须唯一，允许为 null

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

删除索引

DROP INDEX 索引名 ON 表名;

4.组合索引(复合索引)：

即一个索引包含多个列,在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引.

创建复合索引

CREATE INDEX 索引名 ON 表名(列 1,列 2...);

删除索引：

DROP INDEX 索引名 ON 表名;

(1)组合索引最左前缀原则

列如表中有 a,b,c 3 列,为 a,b 两列创建组合索引,那么在使用时需要满足最左侧索引原则, 在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则组合索引不生效.

列如 select * from table where a=’’and b=’’索引生效

select * from table where b=’’and a=’’索引生效

select * from table where a=’’and c=’’索引生效

select * from table where b=’’and c=’’索引不生效

5.全文索引

需要模糊查询时，一般索引无效，这时候就可以使用全文索引了。

(模糊查询)只要表中出现了带有搜索词的内容就可以查到

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

查看索引：

SHOW INDEX FROM 表名;

索引的创建原则

哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引(where 后面的语句)

3.查询中与其它表关联的字段，外键关系建立索引

4.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

5.分组中的字段

哪些情况不要创建索引

1.表记录太少

2.经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE,因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件

3.Where 条件里用不到的字段不创建索引

4.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果

索引数据结构

学习B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。排好序的,一个节点可以存储多个数据, 非叶子节点不存储数据,只存储索引,可以放更多的索引, 数据记录都存放在叶子节点中, 所有叶子节点之间都有一个链指针.

Mysql 索引使用的是 B+树，因为索引是用来加快查询的，而 B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得 B+树的高度不会太高.并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句.

1.二叉查找树

从图中可以看到，我们为user表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键(key)和数据(data)。

键对应user表中的id，数据对应user表中的行数据。二叉查找树的特点就是：任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。

顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

1. 将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。

2. 继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。

3. 把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=12,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

二叉树存在的弊端

二叉树可能存在以下一种形式:

二叉查找树的平均查询次数=(1+2+3+4+5+6+6)/7=3.857次，与顺序查询平均次数4次基本没有太大差距。这个二叉树的效率就会非常的低.

2.平衡二叉树

二叉树可能出现上图中不平衡的现象, 导致其向是一个链表.

为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。

平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。

下面是平衡二叉树和不平衡二叉树的对比:

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。

平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

3.B树

因为内存的易失性，一般情况下，我们都会选择将user表中的数据和索引，都存储在磁盘这种外围设备中。

但是磁盘与内存相比，读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。

因此，如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。

那说明什么？

说明每个磁盘块仅仅存储一个键值和数据！

那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会非常高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。

B树的每个节点，都是存多个值的，不像二叉树那样，一个节点就一个值，B树把每个节点都给了一点的范围区间，区间更多的情况下，搜索也就更快了，比如：有1-100个数，二叉树一次只能分两个范围，0-50和51-100，而B树，分成4个范围 1-25， 25-50，51-75，76-100一次就能筛选走四分之三的数据。所以作为多叉树的B树是更快的.

图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。

图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页，所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。

基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：

1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。

2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。

3. 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

4.B+树

B+树是对B树的进一步优化

1.B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

2. 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找、排序查找、分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

上图中B+树的索引结构就是innodb索引的真正实现, 在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

聚簇索引和非聚簇索引

在mysql中, 根据B+树索引的存储方式的不同,将其分为聚簇索引和非聚簇索引

1. 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

2. 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

如何利用聚簇索引和非聚簇索引查找数据?