本文详细介绍了MySQL数据库中索引的类型，包括普通索引、唯一索引、单列索引、组合索引、全文索引和空间索引。讨论了如何在创建表时、已有表上创建索引，以及如何删除索引。通过实例展示了索引的创建和使用，强调了创建索引的注意事项，如最左前缀原则和适用场景。
索引是一个单独的，存储在磁盘上的数据库结构，包含对数据表里所有记录的引用指针。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]`  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;
扫一扫
440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
