索引是一个单独的,存储在磁盘上的数据库结构,包含对数据表里所有记录的引用指针。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 KEY
UniqIdx(
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)
KEY
singleidx(
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;