SQL 如何建立索引来加快数据库的查询

简介: SQL 如何建立索引来加快数据库的查询

1. 什么是索引


索引是一个单独存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针


在数据库中,索引用来加速对表的查询,索引技术通过使用快速路径访问的方法快速定位数据,减少了磁盘的I/O


可以把索引比作书的目录,每一张表都要有索引,就像每一本书都应该有目录一样。有了索引,数据库的检索速度可以大大提高


0a2653c851af460fa595bd959398a8f1.png


2. 索引的特点


索引是作用于表查询的一项技术,因此创建索引时,需要确保该索引是应用在SQL查询语句的条件,一般作为where子句的条件。


在表现形式上,索引表现为一张表,该表保存了主键与索引字段,并指向实体表的记录。索引独立于数据表存放,但不能独立存在,必须属于某个表。索引由数据库自动维护,数据表被删除时,该表上的索引自动被删除。


不过需要注意的是,虽然索引极大地提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete的速度会被降低。 因为更新表时,数据库不仅需要更新表数据,还要更新索

引文件,此外由于索引是独立于表存在的“表”,因此建立索引会占用一定的磁盘空间。


3. 索引的分类


普通索引

最基本的索引,它没有任何限制,用于加速查询

唯一索引(unique)

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

主键索引(primary key)

是一种特殊的唯 一索引,一个表只能有一个主键,不允许有空值,主键索引一般是在表建立的同时自动创建

单列索引

指单个字段上创建索引

组合索引

指条个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合的规则

全文索引(fulltext)

该索引不直接与索引中的值相比较,而是查找文本中的关键字

空间索引(spatial)

MySQL 5.7.4实验室版本中,InnoDB存储引擎新增了对于几何数据空间索引的支持


4. 索引的设计原则


设计索引应遵循如下原则:


只建立适量的索引。 索引并非越多越好,过多的索引会降低表的修改速度以及占用额外的存储空间。

只在需要大量查询的表中建立大量索引。 应该避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。

不在数据量小的表建立索引。 数据量小的表没有必要使用索引

不在不同值少的列上建立索引。 如果表的某一列中大部分数据都是重复的,则没有必要建立索引

指定唯一索引。 当表的某一列数据被指定为unique是,可以建立唯一索引

在频繁操作的列上建立索引。 若想要建立索引,则应选择频繁排序(order by)或分组(group by)的列建立索引


5. 创建索引


5.1. 自动创建索引


当在表上定义primary key或者unique约束条件时,数据库会自动创建一个对应的索引,也就是创建主键索引和唯一索引。


5.2. 手动创建索引


5.2.1. 创建表时创建索引


在建表的同时创建索引的语法为:


create table [表名](
  [属性名1] [数据类型1] [约束1],
  ...
  [属性名n] [数据类型n] [约束n],
  [unique | fulltext | spatial] index|key [索引名](
  [属性名1] [(长度)] [asc | desc],
  ...
  [属性名n] [(长度)] [asc | desc]
  )
);


例如

建立普通索引


create table normal_table(
  id int(20) primary key,
  username varchar(20),
  email varchar(20),
  -- 在id列建立普通索引
  index normal_index(id)
);


建立唯一索引


create table unique_table(
  id int(20) primary key,
  username varchar(20),
  email varchar(20),
  -- 在email列建立唯一索引,并升序排列
  unique index uniquel_index(email asc)
);


建立全文索引


create table fulltext_table(
  id int(20) primary key,
  username varchar(20),
  email varchar(20),
  -- 在username列建立全文索引
  fulltext index fulltext_index(username)
);


建立单列索引


create table single_table(
  id int(20) primary key,
  username varchar(20),
  email varchar(20),
  -- 在username列建立索引,索引长度为9
  index single_index(username(9))
);


建立多列索引


id int(20) primary key,
  username varchar(20),
  email varchar(20),
  -- 在username列建立索引,索引长度为9
  index single_index(username(9))
);


5.2.2. 在已经存在的表上创建索引


5.2.2.1. alter语句创建索引

语法:


alter table [表名] add [unique|fulltext|spatial] [index|key] [索引名](
  [属性名1] [(长度)] [asc | desc],
  ...
  [属性名n] [(长度)] [asc | desc]
  );


例如:

建立普通索引


-- 在normal_table表的id列建立名为normal_index的普通索引
alter table normal_table add index normal_index(id);


建立唯一索引


-- 在unique_table表的username列建立名为unique_index的唯一索引
alter table unique_table add unique index unique_index(username);


建立多列索引


-- 在multi_table表的username和email列建立名为multi_index的多列索引
alter table multi_table add index multi_index(username, email);


其他类型的索引同理


5.2.2.2. create语句创建索引

语法:


create unique [unique|fulltext|spatial] index [索引名] ON [表名](
  [属性名1] [(长度)] [asc | desc],
  ...
  [属性名n] [(长度)] [asc | desc]
  );


例如

建立普通索引


-- 在normal_table表的id列建立名为normal_index的普通索引
create index normal_index on normal_table(id);


建立唯一索引


-- 在unique_table表的username列建立名为unique_index的唯一索引
create unique index unique_index on unique_table(username); 


建立多列索引


-- 在multi_table表的username和email列建立名为multi_index的多列索引
create index multi_table on multi_table(username, email);


其他类型的索引同理


6. 查看索引


6.1. explain关键字查看索引


explain关键字用于查看和分析SQL索引使用情况

语法:


explain select * from [表名];


使用里explain语句后,会返回一张表:



image.png


例如:

-- 查看multi_table表中的所有使用情况
explain select * from multi_table;


结果为:


image.png


6.2. show index关键字查看索引


show index关键字用于查看某一表中的所有索引

语法:


show index from [表名];


使用里show index语句后,会返回一张表:


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression

其中对表中字段的说明可以参考这篇博客:MySQL SHOW INDEX 的语法解析


例如:


-- 查看multi_table表中的全部索引
show index from multi_table;


结果为:


image.png


7. 删除索引


7.1. alter关键字删除索引


语法:


alter table [表名] drop index [索引名];


例如


-- 从multi_table表中删除multi_index索引
alter table multi_table drop index multi_index;


7.2. drop关键字删除索引


语法:


drop index [索引名] on [表名];


例如


-- 从multi_table表中删除multi_index索引
drop index multi_index on multi_table;


相关文章
|
9天前
|
SQL 安全 数据库
如何在Django中正确使用参数化查询或ORM来避免SQL注入漏洞?
如何在Django中正确使用参数化查询或ORM来避免SQL注入漏洞?
103 77
|
2天前
|
SQL NoSQL Java
Java使用sql查询mongodb
通过MongoDB Atlas Data Lake或Apache Drill,可以在Java中使用SQL语法查询MongoDB数据。这两种方法都需要适当的配置和依赖库的支持。希望本文提供的示例和说明能够帮助开发者实现这一目标。
31 17
|
4天前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
47 6
|
29天前
|
SQL NoSQL Java
Java使用sql查询mongodb
通过使用 MongoDB Connector for BI 和 JDBC,开发者可以在 Java 中使用 SQL 语法查询 MongoDB 数据库。这种方法对于熟悉 SQL 的团队非常有帮助,能够快速实现对 MongoDB 数据的操作。同时,也需要注意到这种方法的性能和功能限制,根据具体应用场景进行选择和优化。
86 9
|
1月前
|
SQL 存储 人工智能
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
Vanna 是一个开源的 Python RAG(Retrieval-Augmented Generation)框架,能够基于大型语言模型(LLMs)为数据库生成精确的 SQL 查询。Vanna 支持多种 LLMs、向量数据库和 SQL 数据库,提供高准确性查询,同时确保数据库内容安全私密,不外泄。
205 7
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
|
4天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
52 0
|
1月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
81 11
|
1月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
1月前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制,旨在检测潜在威胁和漏洞,监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息,帮助管理员追踪数据库中的特定活动,确保数据安全和合规性。SQL Server审核分为服务器级和数据库级,涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警,帮助快速响应安全事件。
|
1月前
|
SQL JavaScript 程序员
数据库LIKE查询屡试不爽?揭秘大多数人都忽视的秘密操作符!
本文分析了因数据库中的不可见空白字符导致的数据查询问题,探讨了问题的成因与特性,并提出了使用 SQL 语句修复问题的有效方案。同时,总结了避免类似问题的经验和注意事项。
39 0