MySQL之索引(入门级讲解)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: MySQL之索引(入门级讲解)

一.索引的概念  

1.1索引的简介

       索引是什么?本质而言是一种数据结构, 实现通常使用B树及其变种B+树 。

       抽象点:我们把索引比作一本书的目录,而目录的作用就是使我们迅速地找到我们需要的内容,而为什么需要索引?当数据表中含有上亿条数据时,我们进行查询是通过遍历表,一条一条筛选时,是不现实的,但是我们可以使用索引,就可知我们需要的数据大概的位置。


       常见的索引方式:字典,可以有两种方式进行查询,第一种是拼音,第二种是部首,而这就是两种不同的索引方式。

1.2.索引的优缺点

优点:

  1. 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
  2. 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

  3. 缺点:
  4. 创建索引需要消耗额外的空间
  5. 有可能会拖慢 增删改 的速度,试想一下,当你修改了数据表的信息时,你的目录也就是索引也可能需要修改的。

结:个人而言,缺点相比较与优点,我们还是利大于弊的,在很多情况下,我们都是以查询操作为主,而增删改较少。


二.MySQL索引语法

       索引分为:普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

2.1查看索引

       查看索引,有人会问,我未创建索引,也会有索引嘛?在数据库中有三种约束类型存在时会自动生成索引,因此在查询时,即使未创建索引,若有这种条件存在,依然会生成索引。

三种约束类型主键、unique、外键

也称:主键索引、唯一索引

语法:show index from 表名;

存在主键时

主键不允许重复,因此进行插入或者修改时,需要先查询,查看插入/修改后结果是否存在。

存在unique时

存在外键约束时

当然除了以上的三种,我们还可以查看到手动创建的索引。

2.2创建索引

  • 索引创建分为2种,第一种是创建表时创建索引,第二种是给存在的表创建索引
  • 索引的创建,前提是表中的数据较少或者无数据,若含有大量数据会引起非常大规模的硬盘IO操作,进一步导致数据库卡死。

2.2.1 创建表时创建索

语法格式: CREATE TABLE 表名(

                       字段名称 字段类型 [完整性约束条件],

               ...,

[UNIQUE / FULLTEXT / SPATIAL] INDEX / KEY [索引名称](字段名称[(长度)] [ASC|DESC])

);

  • 创建普通索引

创建了2个索引,分别为id和username

CREATE TABLE test1(
id int,
username varchar(20),
index in_id(id),
KEY in_username(username)
);
  • 创建唯一索引
CREATE TABLE test2(
id int,
username varchar(20) unique;    //自动生成
);
  • 创建全文索引
CREATE TABLE test3(
id int
username VARCHAR(20) ,
FULLTEXT INDEX full_userDese(username)
);
  • 创建单列索引
CREATE TABLE test4(
id int UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
INDEX in_test1(test1),
UNIQUE in_test1(test1)
);
  • 创建多列索引
CREATE TABLE test4(
id int UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)  //二者都可以
);
  • 创建空间索引
CREATE TABLE test1(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

2.2.2存在的表上创建索引

概念:在存在的表上创建索引,可以直接创建,也可以将某列修改为某种索

直接创建:CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX 索引名称 ON 表名 {字段名称[(长度)] [ASC|DESC]};

简化:create index 索引名 on 表名(字段名)  //其他条件不设置

  • 创建普通索引

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

2、ALTER TABLE 表名  ADD INDEX  索引名(列名);

  • 创建唯一索引

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

2、ALTER TABLE 表名  ADD   UNIQUE INDEX  索引名(列名);

  • 创建全文索引

1、CREATE  FULLTEXT INDEX 索引名  ON 表名(列名);

  • 创建全文索引

1、CREATE  FULLTEXT INDEX 索引名  ON 表名(列名);

2.3删除索引

  • 索引的删除,只能删除手动创建的索引,而自动生成的索引无法删除哦

语法:drop index 索引名 on 表名;

三.索引的数据结构      

   MySQL的索引的数据结构到底是什么样的呢?其实并不是定式的,取决于MySQL使用的存储引擎。

  • 存储引擎: 在mysql程序中,拥有很多模块,有的负责解析sql语句、有的负责网络通信、有的负责存储数据等等。存储引擎就是负责存储数据的,本质而言就是代码中的一个模块(包含了若干个代码文件...以及一大堆具体的代码)

具体如何存储数据,MySQL提供了多种存储方案。而目前最为流行的便是Innodb存储引擎。而Innodb引擎选择使用B+tree索引结构。而至于为什么不选别的索引结构呢?下解:

适合索引的数据结构有 B+tree索引、Hash索引等。

3.1B+tree索引

B+tree数据结构的演变:二叉树-->红黑树-->B-tree树-->B+tree树,而之所以会逐渐演变,是因为有缺点存在,而为了提高效率,就不断演化。

1.二叉树的缺点

  • 顺序插入时,会形成一个链表,查询性能大大降低。
  • 大数据量情况下,层级较深,检索速度慢。

2.红黑树的缺点

  • 大数据量情况下,层级较深,检索速度慢。

3.B-tree树

  又名:B-树,是一种多叉路衡查找树,对比二叉树,B-树每个结点可以存放多个数值,也就是说一个结点有多个分支,即多叉。

 如图:B-树更趋向于区间查找,根结点是30、40、50、60,所以对应的孩子结点范围为[最小值,30)、[30,40)、[40,50)、[50,60)、[60,最大值].


查询方法:例如查询23,在根结点中找到区间小于30,往下个结,[15,25]区间,找然后又落在了[20,25]区间,再往下找,最终找到叶子结点[21,23],存在。


优点:高度低于红黑树,查询效率更高


缺点:无法进行范围查询


4.B+tree树


       B+Tree是B-Tree的变种,所有的数据都会出现在叶子节点。 叶子节点形成一个单向链表。所有的数据都会出现在叶子节点。 叶子节点形成一个单向链表。

叶子结点,会按照链表的方式,首尾相连,注链表是双向链表,画是单向的,因此只要得到开头和结尾,然后将这段链表单独拉出来便是查询的结果

优点:擅长范围查询,查询速度不快不慢,但是很稳定。

3.2Hash索引

哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在 hash表中。如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。


缺点:


Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…)

无法利用索引完成排序操作


优点:查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引

3.4Hash索引和B+tree索引的对比

因此InnoDB存储引擎选择使用B+tree索引结构的原因:

  • 层级更少,搜索效率高
  • 相对Hash索引,B+tree支持范围匹配及排序操作;
  • 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,但是存储索引结构的一个页的大小有限,这样导致一页中存储 的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;

               当你看到这里时,MySQL的索引知识就已经完成了,本文需要细心理解。


                                                                                                      ---------------懒惰的tq02

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
114 4
|
5月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
7月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
3月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
4月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
129 9
|
10月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
1901 10
|
5月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
114 12
|
9月前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
612 81
|
6月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
142 3

推荐镜像

更多