数据库索引

本文详细介绍了MySQL数据库中索引的类型,包括普通索引、唯一索引、单列索引、组合索引、全文索引和空间索引。讨论了如何在创建表时、已有表上创建索引,以及如何删除索引。通过实例展示了索引的创建和使用,强调了创建索引的注意事项,如最左前缀原则和适用场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

索引是一个单独的,存储在磁盘上的数据库结构,包含对数据表里所有记录的引用指针。mysql的索引存储类型有两种,BTREE和HASH.

索引是在存储引擎中实现的,每种存储引擎的索引不一定都相同。所有的存储引擎都支持每个表至少16个索引,索引长度至少为256个字节。

索引的分类
1.普通索引和唯一索引
普通索引为基本索引类型,允许在定义的索引列中插入重复值和空值。
唯一索引只索引值必须唯一,但允许空值,主键索引是一种特殊的的唯一索引,不允许有空值。
2.单列索引和组合索引
单列索引是指索引只包含单个列,一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建索引,只有在查询条件中使用了这些字段中的左边字段时,索引才会被使用。使用组和人索引时遵循最左前缀集合。
3.全文索引
全文索引的类型为FULLTEXT,在定义索引的列上支持值得全文查找,允许在这索引列中插入重复值和空值。全文索引可以在CHAR,VARCHAR或者TEXT类型上创建,在mysql中只有MyISAM支持引擎全文索引。
4.空间索引
空间索引是对空间数据类型的字段建立的索引,mysql的空间数据类型只有四中,分别是GEOMETRY,POINT,LINESTRING,POLYGON.在MySQL中使用SPATIAL关键字进行扩展,创建空间索引的列,必须声明为NOT NULL,只有MyISAM支持引擎全文索引。

创建表时创建索引

在创建表的时候,无论创建的哪些约束,都会在定义约束的同时在指定列上创建了一个索引。 `create table table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name ([length]) [ASC|DESC]` ![](https://img-blog.csdnimg.cn/2020021417432769.png) 1.创建普通索引 最基本的索引,作用只是为了加快数据访问速度。
mysql> create table book(bookid INT NOT NULL,bookname VARCHAR(255) NOT NULL,authers VARCHAR(255) NOT NULL,info VARCHAR(2
55) NOT NULL,comment VARCHAR(255) NULL,year_publication YEAR NOT NULL,INDEX(year_publication));

查看创建的表

mysql> show create table book \G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `authers` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `info` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

year_publication为索引,索引名为year_publication,mysql自动添加的。
用explain语句查看索引是否被使用

mysql> explain select * from book1 where `year_publication`=1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table

EXPLAIN列的解释:

table:显示这一行的数据是关于哪张表的

type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

rows:MYSQL认为必须检查的用来返回请求数据的行数

但是这里出现了NULL,说明索引没有被使用,查了资料解释只有一个表时,都是列举,显示所有数据 所以为 NULL 多个表时,第一个表 也是需要列举的,也会 是NULL个人感觉还有其他原因,求路过大佬解释。

创建唯一索引
唯一索引主要为了减少索引列操作的执行时间。唯一索引与普通索引类似,但是索引列的值必须唯一,但允许有空值。如果是组合索引,则组合必须唯一。

mysql> create table t1(id INT NOT NULL,name CHAR(30) NOT NULL,UNIQUE INDEX UniqIdx(id));
mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

可以看出创建了 从 UNIQUE KEYUniqIdx(id)可以看出创建了名为UniqIdx的唯一索引。

创建单列索引
单列索引是在数据表的一个字段创建索引,一个表中可以有多个单列索引,前面的两个都属于单列索引。
例如:

create table t2(id INT NOT NULL,name CHAR(50) NULL,INDEX singleidx(name(20)));

查看表信息

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `singleidx` (`name`(20))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)

KEYsingleidx(name(20))可以看到单列的表创建完成。

创建组合索引
组合索引是在多个字段上创建索引
例如:

mysql> create table t3(id INT NOT NULL,name CHAR(30) NOT NULL,age INT NOT NULL,info VARCHAR(255),INDEX aaaaa(id,name,age
(100)));

这条语句执行是会出错的,原因有两个。
如下:
Error: 1089 - Incorrect sub part key; the used key part isn’t a string, the used length is longer than the key part, or the storage engine doesn’t support unique prefix keys

谷歌翻译:错误:1089 - 不正确的子部分关键; 使用的关键部分不是字符串,使用的长度比关键部分长,或者存储引擎不允许。
(1)使用的关键部分不是字符串

可能试图在不是字符串的列上创建索引前缀。例如,如果索引前缀所基于的列是非字符串(即INT,TIMESTAMP等),则会收到此错误
例如:

mysql> create table t7(id INT ,INDEX col(id(10)));
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part,
 or the storage engine doesn't support unique prefix keys

而选用

mysql> create table t7(id varchar(100) ,INDEX col(id(10)));
Query OK, 0 rows affected (0.52 sec)

(2)使用的长度比关键部分长
例如:

mysql> create table t7(id varchar(20) ,INDEX col(id(21)));
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part,
 or the storage engine doesn't support unique prefix keys

所以,经过测试,改变索引的长度,将长度值加在字符串类型的字段就可以成功了

mysql> create table t9(id int(30) NOT NULL,name VARCHAR(30) NOT NULL,age INT NOT NULL,info VARCHAR(255),INDEX aaa(id,nam
e(25),age));
Query OK, 0 rows affected (0.51 sec)

注·:组合索引可以起到几个索引的作用,但是使用时并不是随便查询那个字段都会使用索引,而是遵从最左前缀的原则,例如上个例子,会按照(id,name,age),(id,name)或者(id)

查看索引的使用情况

mysql> explain select * from t9 where id=1 AND name='ccc' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t9
   partitions: NULL
         type: ref
possible_keys: aaa
          key: aaa
      key_len: 81
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以解释上面的索引为空的问题,因为表是空的,索引为NULL,插入数据就ok了。
用(age,name)组合不会用到索引

mysql> explain select * from t9 where age=19 AND name='ccc' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t9
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

创建全文索引
全文索引可以进行全文搜索,只有MyISAM支持FULLTEXT索引,并且只能数据类型为char(),varchar(),和TEXT的列上创建全文索引。
例如:

mysql> create table t10(id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> FULLTEXT INDEX FullTxtIdx(info))ENGINE=MyISAM;
mysql> show create table t10 \G
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8_unicode_ci NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

创建空间索引
空间索引必须创建在MyISAM类型的表中,且空间类型的字段不能为空。
创建表t11,在空间类型为GEOMETRY的字段上创建索引,sql语句如下:

mysql> create table t11(g GEOMETRY NOT NULL,SPATIAL INDEX spatIdx(g)) ENGINE=MyISAM;
mysql> SHOW CREATE table t11 \G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

在已有的表上创建索引

可以用alter table 或者create index 语句来实现在已有的表上创建索引。

alter语法如下:
alter table table_name ADD [UNIQUE|FULLTEXT |SPATAL] [INDEX|KEY] [INDEX_Name] (col_name[length],....) [ASC| DESC]

例如:
在之前的book表中添加索引
首先查看表中的索引情况

show index from book \G
mysql> show index from book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

Non_unique :表示唯一性,1为非唯一,0为唯一
Key_name :表示索引名称
Seq_in_index:表示该字段在索引中的位置,单列索引该值为1,组合索引为每个字段在索引定义中的顺序
Column_name:定义索引的列名
Sub_part:索引长度
Null: 表示字段是否能为空值
Index_type:索引类型

在book表的bookname字段创建索引

mysql> alter table book ADD INDEX BKNameIdx(bookname(30));

查看索引情况

mysql> show index from book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: NULL
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

在book表中创建唯一索引
alter table book ADD UNIQUE INDEX UNiqIdIdx(bookId)

*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: UNiqIdIdx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************

在book表的auther和ifo字段创建组合索引
alter table book ADD INDEX BKAuAndInfoIdx(authers(20),info(50))

*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKAuAndInfoIdx
 Seq_in_index: 1
  Column_name: authers
    Collation: A
  Cardinality: NULL
     Sub_part: 20
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKAuAndInfoIdx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: NULL
     Sub_part: 50
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
5 rows in set (0.00 sec)

此时的索引序列号为2

创建t12,在t12表上创建全文索引

create table t12
(id INT NOT NULL,
info CHAR(255)
)ENGINE=MyISAM;
 ALTER TABLE t12 ADD FULLTEXT INDEX infoFTIdx(info);
 mysql> show index from t12 \G
*************************** 1. row ***************************
        Table: t12
   Non_unique: 1
     Key_name: infoFTIdx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment:
1 row in set (0.00 sec)

**创建表t13,并创建空间索引 **
创建表t13

create table t13 (g GEOMETRY NOT NULL) ENGINE=MyISAM;
 ALTER TABLE t13 ADD SPATIAL INDEX spatIdx(g);
 mysql> show index from t13 \G
*************************** 1. row ***************************
        Table: t13
   Non_unique: 1
     Key_name: spatIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:
1 row in set (0.33 sec)

使用create index 创建索引
create [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length],......) [ ASC|DESC]
1.create index 语句创建普通索引
create index BKNameIdx ON book(bookname);
2.create index 语句创建唯一索引
create UNIQUE INDEX UquidIdx ON book(bookId);
3.create index 语句创建组合索引
create index BKAuAndInfo ON book(authers(20),info(50));
4create index 语句创建全文索引
create FULLTEXT INDEX ON t12(info)
5.create index 语句创建空间索引
create SPATIAL INDEX spatIdx ON t7(g);

删除索引

两个语句

1.alter语句
alter table table_name drop index index_name
例如:
删除book表中的唯一索引UNiqIdIdx

alter table book drop index UNiqIdIdx

2.直接用drop语句
drop index index_name ON table_name;
例如:
删除book表中的组合索引

drop index BKAuAndInfoIdx on book;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值