MySQL索引类型详解

前语：
索引是对数据库表中一列或许多列的值进行排序的一种结构，运用索引可进步数据库中特定数据的查询速度。
索引是一个独自的、存储在磁盘上的数据库结构，它们包括着对数据表里一切记载的引证指针。运用索引证于
快速找出在某个或多个列中有一特定值得行，一切MySQL列类型都能够被索引，对相关列运用索引是进步查询
操作速度的最佳途径。

索引的优势：

1. 加快查询速度
2. 创立仅有索引来确保数据表中数据的仅有性
3. 完成数据的完整性，加快表和表之间的衔接
4. 削减分组和排序的时刻

增加索引也有许多晦气，首要表现在如下几个方面：

1. 创立索引和保护索引要消耗时刻，并且跟着数据量的增加所消耗的时刻也会增加。
2. 索引需求占磁盘空间，除了数据表占数据空间之外，每一个索引还要占必定的物理空间，假如有很多的
   索引，索引文件或许比数据文件更快到达最大文件尺度。
3. 当对表中的数据进行增加、删去和修正的时分，索引也要动态地保护，这样就降低了数据的保护速度。

一、索引的分类
1、仅有索引和一般索引
一般索引：是MySQL中的根本索引类型，答应在界说索引的列中刺进重复值和空值。
仅有索引：索引列的值有必要仅有，但答应有空值。假如是组合索引，则列值的组合有必要仅有。
主键索引：是一种特别的仅有索引，不答应有空值。
2、单列索引和组合索引
单列索引：即一个索引只包括单个列，一个表能够有多个单列索引；
组合索引：指在表的多个字段组合上创立的索引。只要在查询条件中运用了这些字段的左面字段时，索引才会被运用。运用组合索引时遵从最左前缀调集。
3、全文索引（ fulltext）
全文索引类型为FULLTEXT，在界说索引的列上支撑值得全文查找，答应在这些索引列
中刺进重复值和空值。全文索引能够在CHAR、VARCHAR或许TEXT类型的列上创立。MySQL 5.7.xx之前只要MyISAM存储引擎支撑全文索引。
4、空间索引
空间索引是对空间数据类型的字段树立的索引，MySQL中的空间数据类型有4中，分别是：
geometry、point、linstring和polygon 。MySQL运用SPATIAL关键字进行扩展，使得能够用于创立空间索引的列，有必要将其声明为NOT NULL，相同，在MySQL 5.7.xx之前，空间索引只能在存储引擎为MyISAM的表中创立。
5、创立索引的规矩

(1)创立索引并非是越多越好，一个表中假如有很多的索引，不只占用磁盘空间，并且会影响
insert、delete、update等句子的功能。因为当表中的数据更改时，索引也会进行调整和更新。
(2)数据量小得
表最好不要创立索引，因为数据较少，查询花费的时刻或许比遍历索引的时刻还要长。
(3)防止对常常更新的数
据创立索引。而对常常用于查询的字段应该创立索引。
(4)在条件表达式中常常用到的不同值较多的列创立索引
(5)当仅有性是某种数据自身的特征时，咱们创立仅有性索引 (6)在频频进行排序或分组的列上树立索引，假如排
序的列有多个，能够创立组合索引

二、创立表的一起创立索引
1、创立一般索引

mysql> 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)
-> );
Query OK, 0 rows affected (0.37 sec)
检查创立的索引
mysql> show create table bookG
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=utf8
1 row in set (0.00 sec)

运用explain判别索引是否正在被运用

mysql> explain select * from book where year_publication=1999G
1. row
id: 1
select_type: SIMPLE
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、仅有索引
创立仅有索引 仅有索引首要原因是削减查询索引列操作的执行时刻。尤其是比照比较巨大的数据
表。与一般索引相似，不同点在于：索引列的值有必要仅有，但答应有空值。假如是组合索引，则列值的组合必
须仅有

mysql> create table t1
-> (
-> id int not null,
-> name char(30) not null,
-> unique index uniqidx(id)
-> );
Query OK, 0 rows affected (0.11 sec)
检查
mysql> show create table t1G
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=utf8
1 row in set (0.00 sec)

3、单列索引
单列索引：是在数据表中的某一字段上创立的索引，一个表中能够创立多个单列索引

3：单列索引
mysql> create table t2
-> (
-> id int not null,
-> name char(50) null,
-> index singleidx(name)
-> );
Query OK, 0 rows affected (0.00 sec)
检查
mysql> show create table t2G
1. row
Table: t2
Create Table: CREATE TABLE t2 (
id int(11) NOT NULL,
name char(50) DEFAULT NULL,
KEY singleidx (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4、组合索引
组合索引：是在多个字段上创立一个索引。遵从最左前缀准则。最左前缀 索引最左面的列来匹配行。

mysql> create table t3
-> (
-> id int not null,
-> name char(30) not null,
-> age int not null,
-> info varchar(255),
-> index multidx(id,name,age)
-> );
Query OK, 0 rows affected (0.00 sec)
检查组合索引
mysql> show create table t3G
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 multidx (id,name,age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5、全文索引
全文索引：FULLTEXT，能够用于全文查找，支撑为CHARVARCHAR和TEXT 列。索引总是对整个列进行，不支撑部分索引，合适大型数据的表创立

创立
mysql> create table t4
-> (
-> id int not null,
-> name char(30) not null,
-> age int not null,
-> info varchar(255),
-> fulltext index fullidx(info(100))
-> )engine=myisam;
Query OK, 0 rows affected (0.01 sec)
检查
mysql> show create table t4G
1. row
Table: t4
Create Table: CREATE TABLE t4 (
id int(11) NOT NULL,
name char(30) NOT NULL,
age int(11) NOT NULL,
info varchar(255) DEFAULT NULL,
FULLTEXT KEY fullidx (info)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

6、空间索引
空间索引：有必要在MyISAM类型的表中创立，且空间类型的字段有必要为非空

mysql> create table t5
-> (
-> g geometry not null,
-> spatial index spaidx(g)
-> )engine=myisam;
Query OK, 0 rows affected (0.00 sec)
检查
mysql> show create table t5G
1. row
Table: t5
Create Table: CREATE TABLE t5 (
g geometry NOT NULL,
SPATIAL KEY spaidx (g)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

三、在现已存在的表上创立索引

mysql> alter table book add index bknameidx(bookname(30));

增加仅有索引

mysql> alter table book add unique index uniqidex(bookid);

增加单列索引

mysql> alter table book add index bkidex(comment(50));

增加全文索引

mysql> create table t6
-> (
-> id int not null,
-> info char(255)
-> )engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table t6 add fulltext index infofulidx(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

增加组合索引

mysql> alter table book add index bkauthandinfoidx(authors(20),info(50));

增加控件索引

mysql> CREATE TABLE t7
-> (
-> g GEOMETRY NOT NULL
-> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t7 add spatial index spatidx(g);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

创立一个表

mysql> create table book1 (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 );
Query OK, 0 rows affected (0.01 sec)

一般索引

mysql> create index bknameidex on book1(bookname);
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0

单列索引

mysql> create index bkcmtidex on book1 (comment(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

组合索引

mysql> create index bkauthandinfoidex on book1(authors(30),info(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

全文索引

mysql> drop table t6;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t6 ( id int not null, info char(255))engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FULLTEXT INDEX FullIdex ON t6(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

仅有性索引

mysql> create unique index uniqidx on book1(bookid);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

空间索引

mysql> drop table t7;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t7 ( g geometry not null )engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create spatial index spaidx on t7(g);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

删去索引

检查book中有几个索引预备开端删去
mysql> show create table book1G
1. row
Table: book1
Create Table: CREATE TABLE book1 (
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,
UNIQUE KEY uniqidx (bookid),
KEY bknameidex (bookname),
KEY bkcmtidex (comment(50)),
KEY bkauthandinfoidex (authors(30),info(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
用alter table删去
mysql> ALTER TABLE book1 DROP INDEX BKNameIdex;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
用drop index删去
mysql> show create table t7G
1. row
Table: t7
Create Table: CREATE TABLE t7 (
g geometry NOT NULL,
SPATIAL KEY spaidx (g)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> drop index spaidx on t7;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0