MySQL - 索引类型详解

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 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)的索引,那么只需要修改原来的索引即可。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
11 0
|
16天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
|
21天前
|
存储 自然语言处理 关系型数据库
ElasticSearch索引 和MySQL索引那个更高效实用那个更合适
ElasticSearch索引 和MySQL索引那个更高效实用那个更合适
35 0
|
22天前
|
SQL 存储 关系型数据库
MySQL not exists 真的不走索引么
MySQL not exists 真的不走索引么
24 0
|
25天前
|
SQL 存储 关系型数据库
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
索引下推是MySQL 5.6引入的优化,允许部分WHERE条件在索引中处理,减少回表次数。例如,对于索引(zipcode, lastname, firstname),查询`WHERE zipcode='95054' AND lastname LIKE '%etrunia%'`时,索引下推先过滤zipcode,然后在索引中应用lastname条件,降低回表需求。索引下推可在EXPLAIN的`Using index condition`中看到。
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
|
10天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
76 0
|
16天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
16天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
|
7天前
|
存储 关系型数据库 MySQL
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
【4月更文挑战第9天】InnoDB数据库使用B+树作为索引模型,其中主键索引的叶子节点存储完整行数据,非主键索引则存储主键值。主键查询只需搜索一棵树,而非主键查询需两次搜索,因此推荐使用主键查询以提高效率。在插入新值时,B+树需要维护有序性,可能导致数据页分裂影响性能。自增主键在插入时可避免数据挪动和页分裂,且占用存储空间小,通常更为理想。然而,如果场景仅需唯一索引,可直接设为主键以减少查询步骤。
13 1
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
|
9天前
|
关系型数据库 MySQL 数据库
6. 了解过Mysql的索引嘛 ?
了解MySQL的索引类型,包括单列索引(普通、唯一、主键和全文索引)和组合索引。单列索引用于一列,如普通索引允许重复值,唯一索引和主键索引不允许,后者不允许空值。全文索引适用于特定文本字段。组合索引是多列的,遵循左前缀原则,通常推荐用于提高查询效率,除非是主键。
12 0