0


MySQL数据库中的索引(含SQL语句)

文章目录

为什么要用索引

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

索引是什么

索引是帮助MySQL高效获取数据的数据结构。
是排好序的快速查找的数据结构。

索引就是类似于书的目录。

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

索引的原理

索引的目的在于提高查询效率,与我们查询图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典、查火车车次,飞机航班等。

本质上都是:通过不断地缩小想要获取数据的范围来筛选出最最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们就可以总是用同一种查找方式来锁定数据。

索引类似于书的目录,在一本书前面加上目录,查找内容时就不必逐页翻阅就能够快速地找到所要查找的内容。借助索引,执行查询时就不必扫描整个表就能够快速地找到所需要的数据。

优点

索引提高了数据检索的效率,降低了数据库IO成本。

通过索引列对数据进行排序,降低了数据排序的成本,降低了CPU的消耗。

缺点

实际上,索引本质上也是一张表,这个表保存了主键与索引之间的字段,并指向实体表的记录,所以索引列也是要占用磁盘的空间。

虽然索引大大提高了插叙的速度,但同时也会降低更新表数据的速度。例如:INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一次索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

创建索引的原则

索引虽好,但也不能乱用。

什么情况下需要索引

  1. 主键自动建立唯一索引。
  2. 频繁作为查询条件的字段应该创建索引(WHERE 后面的语句)。
  3. 查询中与其它表关联的字段,外键关系建立索引。
  4. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度分组中的字段。

什么情况下不需要索引

  1. 表记录过少
  2. 经常性的增删改的表。虽然有提高了查询的速度,但同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一下索引的文件。(所以在遇到这种情况下,就会分表,分表就是将读、写的数据进行分离)
  3. WHERE条件里用不到的字段不会创建索引
  4. 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列表建立索引,某个数据列包含许多重复的内容,建立索引没有太大的实际效果。

索引的分类

主键索引

就是设定主键后数据库会自动建立索引,一个表只能有一个主键。

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

删除主键索引:

ALTER TABLE 表名 DROP PRIMARY KEY;

单值索引

就是一个索引只包含单个列,一个表可以有多个单列索引。
加这个索引肯定是比不加索引要快的。

创建单值索引:

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

删除索引:

DROP INDEX 索引名;

唯一索引

索引列的值必须唯一,允许为null;

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

删除索引:

DROP INDEX 索引名 ON 表名;

组合索引(复合索引)

即一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销是更小的(对于相同的多个列键索引)。

当表的行数远大于索引列的数目时可以使用复合索引。

创建复合索引

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

删除索引

DROP INDEX 索引名 ON 表名;

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

例如:

SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; #索引生效
SELECT * FROM 表名 WHERE b = ' ' AND b = ' ' ; #索引生效
SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; # 索引生效
SELECT * FORM 表名 WHERE b = ' ' AND c = ' ' ; # 索引不生效

全文索引(仅在MySQL8之后有)

是需要模糊查询的,一般索引是无效的,这个时候就可以使用全文索引。

比如: WHERE name LIKE %J%,这样查询的时候,即使name列添加了索引,但也会索引失效的。所以是不建议使用的,在MySQL8当中就建议使用全文索引。

创建全文索引列:

CREATE FULLTEXT INDEX 索引名 ON 表名(列名) WITH TARSER ngram;

使用全文索引:

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST(‘输入的参数’);

全文索引的插叙效率是比使用LIKE来查询要快的,所以在MySQL8之后是建议使用全文索引的。

查找索引:

SHOW INDEX FROM 表名;

索引的数据结构

在MySQL数据库中的InnoDB引擎是使用B+树来进行数据存储的。

B+树由二叉查找树、平衡二叉树(AVLTree)和平衡多落查找树(B-Tree)逐步优化而来的。使其更适合实现外存索引结构。

B+数的特点:

  1. 它是排好序的,一个节点可以存储多个数据。
  2. 非叶子节点是不存储数据的,只存储索引,是可以放更多的索引。
  3. 数据都是存放在在叶子节点中的。
  4. 所有的叶子节点之间都是有一个链指针的。

就是使用B+树使得树是横向扩展的,让树的高度降低了。在叶子节点也可以存储多个数据。
在这里插入图片描述

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

聚簇索引和非聚簇索引

聚簇索引
就是找到了索引就找到了需要的数据,那么这个索引就是聚簇索引,所以主键就是聚簇索引。

非聚簇索引
索引的存储和数据的存储时分离的,也就是意味着找到了索引但没有找到数据,需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

举例:
先创建一个表,用来说明说明情况下是聚簇索引和非聚簇索引:

CREATETABLE student (`id`INTPRIMARYKEYNOTNULLAUTO_INCREMENTCOMMENT'主键id',`name`VARCHAR(50)COMMENT'学生姓名',`age`INTNOTNULLDEFAULT0COMMENT'学生年龄',KEY`idx_name`(`name`))

创建的数据库表中,主键id是一个索引,给age添加了索引。

① 直接根据主键查询获取所有字段数据,此时主键就是聚簇索引。因为主键对应的索引叶子节点存储的就是id的所有字段值。

SELECT * FROM student WHERE id = 1;

在这里插入图片描述

② 根据name查询所有信息,name本身就是一个唯一索引,但查询的数据包括了所有的数据,所以当命中name索引的时候,该索引的节点的数据存储的就是主键的ID,然后需要ID再查询一次。
这就是非聚簇索引。

SELECT * FROM student WHERE name = “张三”;

索引就是再单独的创建一张表,在这个表中查找数据,然后拿到ID在返回所有数据的表中查询所有的数据。

③ 根据name查询的时候,我们只查询name的值,不查询其他的信息。这种查询就是命中name索引,直接返回name的值,因为所需要的数据就是索引,此时不需要会表查询。这种场景就是非聚簇索引。

SELECT name FROM student WHERE name = “张三”;


拓展
在MySQL中InnoDB引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计。(默认是聚簇设计的,当你加上其他的索引,根据变化就成为了非聚簇式)

而MyISAM引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中。(不管怎么创建,它本质上就是非聚簇的设计)

因为在MyISAM当中,它是在MYD中存储文件的数据;在MYI中存储文件的索引;在sdi中存储表的结构。
在这里插入图片描述


本文转载自: https://blog.csdn.net/weixin_45970271/article/details/124304965
版权归原作者 星辰与晨曦 所有, 如有侵权,请联系我们删除。

“MySQL数据库中的索引(含SQL语句)”的评论:

还没有评论