MySQL - 索引类型详解

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL - 索引类型详解

通俗的来说,索引就相当于一个字典的目录,为了方便查找书中的内容,通过对内容建立索引形成目录,索引是一个文件,它是要占据物理内存的。

官方的来说索引它是一种数据结构,数据库索引是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据表中的数据。

1. 索引的分类

MySQL目前主要有以下几种索引类型:

  1. 普通索引
  2. 唯一索引
  3. 主键索引
  4. 组合索引
  5. 全文索引

1.1 普通索引

普通索引是 MySQL 中最基本的索引类型,它没有任何限制,唯一任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值。 创建普通索引时,通常使用的关键字是 INDEXKEY

创建普通索引的方式:

  • 创建索引:
create index index_name on table_name (column_name);

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

  • 修改表结构(添加索引)
alter table table_name add index index_name (column_name);
  • 创建表的时候直接指定
create table mytable
(
    id       int         not null,
    username varchar(16) not null,
    index index_name (column_name)
);
  • 删除索引的语法
drop index index_name on table_name;

1.2 唯一索引

唯一索引与普通索引类似,不同的是创建唯一性索引的目的不是为了提高访问速度,而是为了避免数据出现重复。

唯一索引列的值必须唯一,允许有空值。如果是组合索引,则列值的组合必须唯一。

创建唯一索引通常使用 UNIQUE 关键字。

创建唯一索引的方式:

  • 创建索引
create unique index index_name on table_name (column_name);
  • 修改表结构
alter table table_name add unique index_name (column_name);
  • 创建表的时候直接指定
create table table_name
(
    id       int         not null,
    username varchar(16) not null,
    unique index_name (column_name)
);

1.3 主键索引

主键索引就是专门为主键字段创建的索引,也属于索引的一种。

主键索引是一种特殊的唯一索引,不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

创建主键索引的方式:

  • 创建索引
create table table_name
(
    id       int         not null,
    username varchar(16) not null,
    primary key (column_name)
);
  • 修改索引
alter table table_name add primary key (column_name);

1.4 组合索引

组合索引也称为复合索引或多列索引。相对于单列索引来说,组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。

  • 创建索引
create index index_name on table_name (column_name1, column_name2);

1.5 全文索引

全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

全文索引允许在索引列中插入重复值和空值。

不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。

  • 创建索引
create fulltext index index_name on table_name (column_name);
  • 修改索引
alter table table_name add fulltext index_name (column_name);

和常用的like模糊查询不同,全文索引有自己的语法格式,使用 match 和 against 关键字,比如:

select * from user where match(name) against('aaa');

全文索引使用注意事项:

  • 全文索引必须在字符串、文本字段上建立。
  • 全文索引字段值必须在最小字符和最大字符之间的才会有效。(innodb:3-84;myisam:4-84)
  • 全文索引字段值要进行切词处理,按syntax字符进行切割,例如b+aaa,切分成b和aaa
  • 全文索引匹配查询,默认使用的是等值匹配,例如a匹配a,不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against('a*' in boolean mode);

2. MySQL索引的优缺点

2.1 优点

(1)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

(2)可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

(3)可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

(4)在使用分组和排序子句进行数据检索时,同时可以显著减少查询中分组和排序的时间。

2.2 缺点

(1)创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大。

(2)创建索引和维护索引需要空间成本,每一条索引都要占据数据库的物理存储空间,数据量越大,占用及空间也越大(数据表占据的是数据库的数据空间)

(3)会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间边长。

3. 索引的使用场景

3.1 选择唯一性索引

唯一性索引的值是唯一的,可以更加速的通过该索引来确定某条记录。

就比如学生表中学生的学号是具有唯一性的字段,为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话可能会存在同名现象,从而降低查询速度。

3.2 经常需要排序,分组,联合操作的字段建立索引

经常需要 order by,group by ,distinct , union 等操作的字段,排序操作会浪费很多时间,为其建立索引可以有效地避免排序操作。

3.3 常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度,为这样的字段建立索引,可以提高整个表的查询速度。

3.4 限制索引的数目

索引的数目不是越多越好,每个索引都需要占用磁盘空间,索引越多需要的磁盘空间就会越大。修改表的时候对索引的重构和更新都会很麻烦;索引越多更新表就会越浪费时间。

3.5 尽量使用数据量少的索引

索引的值越长查询的速度会越慢。例如一个char(100)类型的字段进行全文检索需要的时间比char(10)类型的字段需要的时间更多。

3.6 删除不再使用或很少使用的索引

表中的数据大量更新或者是数据的使用方式被改变后,原有的一些索引可能不在需要,数据库管理员应当定期找出这些索引,将他们删除,从而减少索引对更新操作的影响。

3.7 尽量的扩展索引,不要新建索引

比如表中已经有a 的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
100 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覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
124 9
|
5月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
110 12
|
6月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
138 3
|
7月前
|
关系型数据库 MySQL Java
【YashanDB知识库】崖山BIT类型对MYSQL兼容问题
【YashanDB知识库】崖山BIT类型对MYSQL兼容问题
|
7月前
|
自然语言处理 关系型数据库 MySQL
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。

推荐镜像

更多