数据库什么查询用到索引数据库使用索引查询

转载

数据挖掘者 2024-03-19 00:05:10

在关系型数据库中，索引是一种可以加快数据检索的数据库结构，主要用于提高性能。因为索引可以从大量的数据中迅速找到所需的数据，不再需要检索整个数据库，所以大大提高了检索的效率。

索引概述

索引是一个单独的、物理的数据库结构，是某个表中一列或者若干列的集合以及相应的标识这些值所在的数据页的逻辑指针清单。索引是依赖于表建立的，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。通常，索引页面相对于数据页面小得多。在检索数据时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。索引一旦创建，将由数据库自动管理和维护。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供了一种快速访问指定记录的方法。

索引可以提高数据的访问速度

只要为适当的字段建立索引，就能大幅度提高下列操作的速度。

查询操作中WHERE子句的数据提取
查询操作中ORDER BY子句的数据排序
查询操作中GROUP BY子句的数据分组
更新和删除数据记录

索引可以确保数据的唯一性

创建唯一性索引可以保证表中数据记录不重复。在MySQL中，索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一样支持所有的索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎有关。MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

虽然索引具有诸多优点，但是仍要注意避免在一个表中创建大量的索引，因为这样不但会影响插入、删除、更新数据的性能，也会在更改表中的数据时增加调整所有索引的操作，降低系统的维护速度。

索引的类型

MySQL的索引可以分为以下几类：

普通索引和唯一索引。普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引是指索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
单列索引和组合索引。单列索引是指一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时要遵循最左前缀集合。
全文索引。全文索引是指在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
空间索引。空间索引是对空间数据类型的字段建立的索引。MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIL关键字扩展，使得能够使用与创建正规索引类似的语法创建空间索引。必须将创建空间索引的列声明为NOT NULL，空间索引只有在存储引擎MyISAM的表中创建。

索引的设计原则

索引设计不合理或缺少索引都会影响数据库的应用性能。高效的索引对于获得良好的性能非常重要。设计索引时，应该遵循以下准则：

索引并非越多越好
避免对经常更新的表建立过多的索引
数据量小的表最好不要使用索引
在不同值少的列上不要建立索引
指定唯一索引是由某种数据本身的特征决定的
为经常需要排序、分组和联合操作的字段建立索引

使用SQL语句创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以提高对表的访问速度。在实际创建索引之前，需注意如下事项：

当给表创建UNIQUE约束时，MySQL会自动创建唯一索引。
索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。
可以在创建表时创建索引，或是给现存表创建索引。
只有表的所有者才能给表创建索引。

创建唯一索引时，应保证创建索引的列不包括重复的数据，并且没有两个或两个以上的空值(NULL)。因为创建索引时将两个空值也视为重复的数据，如果有这种数据，就必须先将其删除，否则不能成功创建索引。创建索引有两种方式：创建表时创建索引、给现存表创建索引。

# 在创建表时创建索引
CREATE TABLE <表名>
(
    ...
    [UNIQUE|FULLTEXT|SPATIAL] <INDEX|KEY> [索引名] (属性名[(长度)] [,...])
);

# 在现存表中创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名> ON <表名> (属性名[(长度)] [,...]);

# 删除索引
DROP INDEX <索引名> ON <表名>;

参数说明如下：