数据库索引

最新推荐文章于 2022-07-16 15:44:54 发布

舞动的獾

最新推荐文章于 2022-07-16 15:44:54 发布

阅读量546

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/Yu_csdnstory/article/details/104312237

版权

mysql 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了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);