MySQL--数据库索引

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

MySQL数据库--索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度

一、索引简介

1、索引的含义和特定

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引的优点主要有以下：

• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
• 可以大大加快数据的查询速度，这也是创建索引的主要原因
• 在实现数据的参考完整性方面，可以加速表和表之间的连接
• 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

增加索引也有不利的方面：

• 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
• 索引需要占磁盘空间，除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

2、索引的分类

2.1、普通索引和唯一索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯主键索引是一种特殊的唯一索引，不允许有空值。

2.2、单列索引和组合索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引 组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

2.3、全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MVSOL中只有MyISAM存储引擎支持全文索引。

2.4、空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOTNULL，空间索引只能在存储引擎为MyISAM的表中创建。

3、索引的设计原则

设计数据库索引时应遵循以下原则以确保性能优化：

• 索引数量应适中，过多索引会占用空间并降低数据更新操作的性能
• 对于频繁更新的表，应避免创建过多索引，且索引列应尽量精简，仅对常用于查询的字段建立索引
• 对于数据量小的表，索引可能不会带来性能提升。应针对条件表达式中常用且具有不同值较多的列建立索引，避免在值少的列上创建索引
• 对于具有唯一性特征的数据，使用唯一索引以确保数据完整性并提高查询速度

• 对于经常需要排序或分组的列，建立索引或组合索引以优化性能。

二、创建索引

1、创建表时创建索引

其基本语法格式如下：

 create table table_name [col_name data_type]
 [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name] (col_name [length]) [ASC | DESC]

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；index与key为同义词，作用相同，用来指定创建索引；col_name表示为要创建索引的字段列；index_name为索引值；length为可选值，表示索引的长度，ASC或DESC表示升序或降序

（1）创建普通索引

 # 在book表中的year_publication字段上建立普通索引
 create table book(
 bookid       int not null,
 bookname     varchar(255) not null,
 authors      varchar(255) not null,
 info         varchar(255) null,
 comment      varchar(255) null,
 year_publication    year not null,
 index(year_publication)
 );
 # 使用show create table查看表结构
 mysql> show create table book \G
 *************************** 1. row ***************************
        Table: book
 Create Table: CREATE TABLE `book` (
   `bookid` int(11) NOT NULL,
   `bookname` varchar(255) NOT NULL,
   `authors` varchar(255) NOT NULL,
   `info` varchar(255) DEFAULT NULL,
   `comment` varchar(255) DEFAULT NULL,
   `year_publication` year(4) NOT NULL,
   KEY `year_publication` (`year_publication`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)
 # 发现year_publication字段成功建立了索引，使用explain语句查看所有是否正在使用
 mysql> explain select * from book where year_publication=1990 \G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE   # SELECT查询类型
         table: book     # 数据表名字
    partitions: NULL 
          type: ref      # 本数据表与其他表之间的关联关系
 possible_keys: year_publication     # 可选用的各个索引
           key: year_publication     # 实际选用的索引
       key_len: 1        # 索引按字节计算的长度
           ref: const    # 关联关系中另一个数据表里的数据列名
          rows: 1        # 预计从数据表读出的数据行的个数
      filtered: 100.00   
         Extra: Using index condition    # 与关联操作有关的信息
 1 row in set, 1 warning (0.00 sec)

（2）创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

 # 创建一个表t1，在表的id字段上使用unique关键字创建唯一索引
 create table t1(
 id     int not null,
 name   char(30) not null,
 unique  index uniqIdx(id)
 );
 # 发现id字段上成功建立了一个名为UniqIdx的唯一索引
 show create table t1 \G
 *************************** 1. row ***************************
        Table: t1
 Create Table: CREATE TABLE `t1` (
   `id` int(11) NOT NULL,
   `name` char(30) NOT NULL,
   UNIQUE KEY `uniqIdx` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)

（3）创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。前面两个例子中创建的索引都为单列索引。

 # 创建一个表t2，在表的name字段上创建单列索引
 create table t2(
 id    int not null,
 name  char(50) null,
 index  SingleIdx(name(20))
 );
 # 可以看到，id字段上成功建立一个名为Single的单列索引，长度为20
 mysql> show create table t2 \G
 *************************** 1. row ***************************
        Table: t2
 Create Table: CREATE TABLE `t2` (
   `id` int(11) NOT NULL,
   `name` char(50) DEFAULT NULL,
   KEY `SingleIdx` (`name`(20))
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)

（4）创建组合索引

组合索引是在多个字段上创建一个索引

 # 创建表t3，在表中的id、name和age字段上建立组合索引
 create table t3(
 id     int(11) not null,
 name   char(30) not null,
 age    int(11) not null,
 info   varchar(255),
 index  MultiIdx(id,name,age)
 );
 mysql> show create table t3 \G
 *************************** 1. row ***************************
        Table: t3
 Create Table: CREATE TABLE `t3` (
   `id` int(11) NOT NULL,
   `name` char(30) NOT NULL,
   `age` int(11) NOT NULL,
   `info` varchar(255) DEFAULT NULL,
   KEY `MultiIdx` (`id`,`name`,`age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

（5）创建全文索引

FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。

 # 创建表4，在表中的info字段建立全文索引
 create table t4(
 id     int not null,
 name   char(30) not null,
 age    int not null,
 info   varchar(255),
 fulltext index fullTxtIdx(info)
 ) engine=MyISAM;

（6）创建空间索引

空间索引必须在 MyISAM 类型的表中创建，且空间类型的字段必须为非空。

 # 创建表t5，在空间类型为GEOMETRY的字段上创建空间索引
 create table t5(
 g   geometry not null,
 SPATIAL  index spatIdx(g)
 )engine=MyISAM;

2、在已经存在的表删创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE或者CREATE INDEX语句，

（1）使用alter table语句创建索引

其基本语法如下：

 alter table table_name add [unique|fulltext|spatial] [index|key]
 [index_name] (col_name[length],...) [ASC|DESC]

# 向book表中的bookname字段上建立名为BkNameIdx的普通索引
# 添加索引之前，使用show index语句查看指定表中创建的索引
mysql> show index from book \G
*************************** 1. row ***************************
        Table: book			# 创建索引的表
   Non_unique: 1			# 1代表非唯一索引，0代表唯一索引
     Key_name: year_publication	# 索引名称
 Seq_in_index: 1				# 该字段在索引中的位置，单列索引该值为1
  Column_name: year_publication	# 定义索引的列字段
    Collation: A	
  Cardinality: 0
     Sub_part: NULL			# 索引的长度
       Packed: NULL	
         Null: 				# 表示该字段能否为空值
   Index_type: BTREE		# 索引类型
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
------# 添加索引
alter table book add index BkNameIdx( bookname(30));

# 在book表的bookId字段上建立名称为UniqidIdx的唯一索引
alter table book add unique index UniqidIdx(bookId);

# 在book表的comment字段上建立单列索引
alter table book add index BkcmtIdx(comment(50));

# 在book表的authors和info字段上建立组合索引
alter table book add index BkAuAndInfoIdx(authors(30),info(50));

# 创建表t6，在表上使用alter table创建全文索引
create table t6(
id       int not null,
info     char(255)
)engine=MyISAM;		# MySQL默认引擎InnoDB不支持全文索引
alter table t6 add fulltext index infoFTIdx(info);

# 创建表t7，在表的空间数据类型字段g上创建名称为spatIdx的空间索引
create table t7(g GEOMETRY NOT NULL)engine=MyISAM;
alter table t7 add spatial index spatIdx(g);

（2）使用create index创建索引

CREATE INDEX语句可以在已经存在的表上添加索引。在MySQL中，CREATE INDEX被映射到一个ALTER TABLE 语句上，基本语法结构为：

create [unique|fulltext|spatial] index index_name
on table_name (col_name[length],...) [ASC|DESC]
# 把原book删除，再次创建一个无索引值的book表
create table book(
bookid       int not null,
bookname     varchar(255) not null,
authors      varchar(255) not null,
info         varchar(255) null,
comment      varchar(255) null,
year_publication    year not null
);
# 在book表中的bookname字段建立名为BkNameIdx的普通索引
create index BkNameIdx ON book(bookname);

# 在book表的bookId字段上建立名称为UniqidIdx的唯一索引
create unique index UniqidIdx ON book(bookId); 

# 在book表的comment字段上建立单列索引
create index BkcmtIdx on book(comment(50));

# 在book表的authors和info字段上建立组合索引
create index BkAuAndInfoIdx on book(authoris(20),info(50));

# 在t6表上使用alter table创建infoFTIdx全文索引
create fulltext index infoFTIdx on t6(info);

# 重新创建表t7，在表的空间数据类型字段g上创建名称为spatIdx的空间索引
create spatial index spatIdx on t7(g);

三、删除索引

MYSOL中删除索引使用ALTER TABLE或者DROP INDEX语句，两者可实现相同的功能DROPINDEX语句在内部被映射到一个ALTER TABLE 语句中。

1、使用alter table删除索引

其基本语法如下：

alter table table_name drop index index_name

# 删除book表中名称为UniqidIdx的唯一索引
alter table book drop index UniqidIdx;

2、使用drop index语句删除索引

其基本语法格式如下：

​drop index index_name on table_name;

# 删除book表中名称为BkAuAndInfoIdx的组合索引
drop index BkAuAndInfoIdx on book;

致谢

在此，我要对所有为知识共享做出贡献的个人和机构表示最深切的感谢。同时也感谢每一位花时间阅读这篇文章的读者，如果文章中有任何错误，欢迎留言指正。 

学习永无止境，让我们共同进步！！