首页 > 数据库 >MySQL索引

MySQL索引

时间:2024-03-31 10:44:06浏览次数:30  
标签:Hash 查询 索引 MySQL NULL 数据 主键

随笔参考:

一. 什么是索引

索引对于表中的记录而言起到一个目录的作用,通过查询索引,我们可以快速的找到想要的记录,而不需要对表进行全面的扫描。就和查字典的原理类似。

二. 索引的优缺点

优点:

  • 减少查找数据时需要扫描的数据量,从而大大加快数据的检索速度。(最主要原因)
  • 避免查询时进行排序创建临时表
  • 可以将随机IO变成顺序IO。
    • 随机IO:数据存储位置不连续。因为数据库中的数据在磁盘上实际是随机存储。
    • 顺序IO:数据存储位置连续。索引所需空间较小,可以申请一块连续的物理空间,减少磁盘读取时间。
  • 索引可以加速表和表之间的连接。例如join t1,t2 on ...,通过索引连接表。
  • 唯一性索引可以保证表中每一行数据的唯一性。
  • 索引可以加速分组和排序子句的速度。
  • InnoDB、索引、锁:InnoDB中,二级索引(非主键索引)使用共享锁(读锁),主键索引使用排他锁(写锁)。

缺点:

  • 索引的创建和维护需要耗费时间,且这种时间随着数据量的增加而增加。
  • 索引要占用物理空间。如果是聚簇索引,那么占用的空间会更大(聚簇索引的叶子节点包含的该索引对应的记录)。
  • 在对表中数据进行增、删、该时,该表的索引也要动态维护,这降低了维护的速度。(索引是B+树结构,主要作用是查询,修改的话需要修改数据和索引,速度较慢)
  • 对于非常小的表,大部分情况下简单的全表扫描更加有效。

三. 索引的使用时机

1. 应该创建索引的列

  • 经常需要查询的列。
  • 作为主键的列。
  • 经常作为连接依据的列(join操作),这些列一般是外键,可以加速连接的速度。
  • 在经常需要进行范围判断、排序操作的列建立索引(<、<=、>、>=、between andin、order by)。因为索引是B+树结构,已经排好顺序了。
  • where子句中经常使用的列创建索引,加快条件判断速度。

2. 不应该创建索引的列

  • 查询中很少使用的列不应该创建索引。
  • 经常修改的列不应当创建索引。
  • 对于很少数据值或者重复值多的列也不应该创建索引。例如性别列,在查询时,结果集中的数据行占据了表中数据行很大一部分,此时增加索引并不能明显加快检索速度。
  • 对于text、image、bit类型的列不应当添加索引。这些列要么数量很大,要么取值很少。

四. 索引的结构

MySQL中索引主要有两种结构:

  • B+树
  • Hash表。

1. B+树

此处见博客园数据结构——B树、B+树:

B树、B+树 - Beasts777 - 博客园 (cnblogs.com)

2 Hash表

2.1 思想

通过hash算法,将索引作为关键字进行hash运算,对应的值就是数据。

通过使用Has表,我们不需要像B+树那样从叶子节点开始遍历,而是通过hash运算直接定位,速度极快。

Memory存储引擎使用的就是Hash作为索引结构。

2.2 使用场景

基于Hash本身的特性,Hash只能进行等值查询,例如=,in,<>

而不能进行范围查询,这是因为原先是有序的键值,经过Hash算法后,很可能变成不连续的了。

因此凡是涉及范围运算的操作,Hash都会失效,例如>,<,between a and b,like 'xxx%'。(like操作实际上也是范围查询)

3. B+树索引和Hash索引的区别

  • B+树索引可以进行范围查询,都要查询到叶子节点。而Hash只能进行等值查询。
  • B+树索引的检索效率比较平均。而对于Hash索引而言,如果键值大都唯一,那么Hash索引查找速度极快,但如果有大量重复键值的情况下,Hash索引在找到该键所在位置需要对链表进行扫描,时间复杂度退化回\(O(n)\)
  • B+树索引可以通过索引快速排序。而Hash索引不支持。
  • B+树索引支持多列联合索引的最左匹配原则。而Hash索引不支持。

五. 索引的分类

根据不同的标准,索引有不同的划分:

1. 按照功能划分

  • 主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL;
  • 唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
  • 普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入;
  • 全文索引:它查找的是文本中的关键词,主要用于全文检索。下面有介绍。

2. 按照列数划分

  • 单列索引。
  • 组合索引:一个索引由多列组成。查询时遵守Mysql的最左前缀原则,也就是使用where时判断条件要按照建立索引时字段的排列方式放置索引,索引才会生效。

3. 按照物理实现划分

按照索引和数据的物理关系进行划分。

3.1 聚簇索引

3.1.1 定义:

依赖于B+树,将表的主键构造为一棵B+树,树的叶子节点存放的是表的行记录数据,那么该主键索引就是聚簇索引。

即:将数据和索引存放在了一棵树上,索引是非叶子节点,数据是叶子节点。找到了索引,也就找到了数据。

3.1.2 在引擎中的应用:

  • InnoDB默认只有主键才是聚簇索引。其余都是非聚簇索引。
  • 每张表最多只能有一个聚簇索引。

3.1.3 优缺点

优点:

  • 数据访问更快。因为叶子节点就是行数据。
  • 对于逐渐的排序查找和范围查找速度更快。

缺点:

  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是最快的方式,否则将会出现页分裂现象,严重影响性能。因此对于InnoDB,我们一般会定义一个自增的ID列作为主键。
  • 更新主键的代价很高。因为则还会导致被更新的行的移动。所以在InnoDB中,我们一般定义主键不可更新。

3.2 非聚簇索引

定义:

依赖于B+树,但是叶子节点存放的不是数据,而是该索引对应的该行记录的主键值。随后还需要通过该主键值在主键构成的聚簇索引B+树上再次进行查找才能找到数据。因此非聚簇索引又称为二级索引

在引擎中的应用:

  • InnoDB中除了主键都是非聚簇索引。
  • MyISAM只支持非聚簇索引。

六. 索引实战

1. explain

作用:

通过explain命令,可以模拟优化器执行SQL语句的过程,从而了解MySQL是如何处理SQL语句的。

命令格式:

explain SQL语句

EG:

mysql> explain select * from test;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

其中的列含义为:

  • id:选择标识符
  • select_type:表示查询的类型
  • table:输出结果集的表
  • partitions:匹配的分区
  • type:表示表的连接类型
  • possible_keys:表示查询时,可能使用的索引
  • key:表示实际使用的索引
  • key_len:索引字段的长度。
  • ref:列与索引的比较。
  • rows:扫描出的行数(估算的行数)。
  • filtered:按表条件过滤的百分比。
  • Extra:直接情况的描述和说明。

详情暂时省略,留待后续补充

2. 最左前缀原则

3. 前缀索引

4. 全文索引

5. 索引覆盖与回表

6. 索引失效

标签:Hash,查询,索引,MySQL,NULL,数据,主键
From: https://www.cnblogs.com/beasts777/p/18106475

相关文章

  • java毕业设计社团物品租赁小程序(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义一、选题背景:随着校园文化的繁荣和学生社团活动的增多,各类社团对于特定物品的需求日益增长。这些物品包括活动器材、会议设备、表演服装等,购买成本高且使用频率不......
  • java毕业设计汽车服务系统(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景:随着科技的进步和汽车产业的迅猛发展,汽车行业的竞争已经从单纯的价格竞争逐渐转向服务竞争。消费者对汽车服务的需求日益增长,不仅关注汽车的性能、外观和......
  • java毕业设计社团管理系统(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景:随着高校教育的不断发展,学生社团作为校园文化的重要组成部分,承担着丰富学生课余生活、培养学生兴趣爱好、提升学生实践能力的重要职能。然而,传统的社团管......
  • java毕业设计实验室资源管理(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义一、选题背景:在高等教育和科研机构中,实验室是进行教学和科学研究的重要场所。一个现代化的实验室通常拥有大量昂贵的设备、仪器和材料。如何有效地管理这些资源,确......
  • java毕业设计青少年视力筛查系统(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景:在当今社会,随着科技的发展和生活节奏的加快,青少年的视力健康问题日益凸显。长时间使用电子产品、不合理的阅读习惯以及缺乏户外活动等因素导致青少年近视......
  • java毕业设计上门医疗服务小程序(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景:随着社会的发展和人口老龄化的加剧,人们对医疗服务的需求日益增长,特别是对于便捷、高效的上门医疗服务。传统的医疗服务模式要求患者亲自前往医院或诊所就......
  • java毕业设计汽车租赁系统(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景:随着共享经济的兴起和汽车产业的发展,汽车租赁作为一种新兴的出行方式逐渐受到人们的欢迎。传统的汽车租赁业务多依赖于线下门店操作,顾客需要到店选车、签......
  • java毕业设计天勤人力资源管理(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义一、选题背景:在当今竞争激烈的商业环境中,人力资源管理(HRM)对于组织的成功至关重要。有效的HRM不仅能够提高员工的工作效率和满意度,而且可以促进企业的整体发展战......
  • java毕业设计数字家谱管理系统(Springboot+mysql+jdk1.8+maven3.39)
    本系统(程序+源码)带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义一、选题背景:在快速变化的社会中,人们越来越意识到家族历史和文化传承的重要性。家谱作为记录家族血脉和历史的重要文献,承载着丰富的文化价值和历史信息。然而,传统......
  • 搜索引擎语法
    百度语法1.搜索A屏蔽B【A-B】这里的'-'前要有空格2.搜索包含A的信息或者包含B的信息【A|B】3.将搜索范围限定在网页标题【Aintitle:B】也就是必须有A且B的内容必须出现在标题中;'intitle:'后不能有空格4.将搜索范围界定在指定网站中【Asite:站点域名】也就是站点域名......