目录
一、索引概述
Mysql索引的简历对于Mysql的高效运行是很重要的，索引可以大大提高Mysql的检索速度！
创建索引时，需要确保索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件）！
建立索引会占用磁盘空间的索引文件！
每种搜索引擎支持的索引是不同的，如下：
- MylSAM、InnoDB支持btree索引；
- Memory支持btree和hash索引；
1.1 索引的优势
- 加快查询速度；
- 创建唯一索引可以保证数据表中数据的唯一性；
- 实现数据的完整性，加速表和表之间的链接；
- 减少分组和排序的时间
1.2 索引的劣势
- 创建索引和维护索引需要耗费大量的时间，并且随着数据量的增加所耗费的时间也会有所增加；
- 索引需要占用磁盘空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸；
- 当对表中的数据进行增加、删除和修改时，索引也要动态地维护，这样就降低了数据的维护速度；
二、索引的分类
2.1 唯一索引和普通索引
- 普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值；
- 唯一索引：索引列的值必须唯一，但允许是空值。如果是组合索引，则列值的组合必须唯一；
- 主键索引：是一种特殊的唯一索引，不允许有空格；
2.2 单列索引和组合索引
- 单列索引：即一个索引只包含单个列，一个表中可以有多个单列索引；
- 组合索引：指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合；
2.3 全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。Mysql5.7之前只有MylSAM存储引擎支持全文索引。
2.4 空间索引
空间索引是对空间数据类型的字段简历的索引，Mysql中的空间数据类型有4种，分别是：geometry、point、linstring和polygon。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为NOT NULL，同样，在MySQL5.7之前，空间索引只能在存储引擎为MyISAM的表中创建。
2.5 创建索引的规则
- 创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能，因为当表中的数据更改是，索引也会进行调整和更新；
- 数据量小的表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的数据还要长；
- 避免对经常更新的数据创建索引。而经常用于查询的字段应该创建索引；
- 在条件表达式中经常用到的不同值较多的列创建索引；
- 当唯一性是某种数据本地的特征时，我们创建唯一索引；
- 在频繁进行排序或分则的列上建立索引，如果排序的列有多个，可以创建组合索引；
三、创建表的同时创建索引
3.1 创建普通索引
create table book (
bookid int,
bookname varchar(255),
authors varchar(255),
info varchar(255),
comment varchar(255),
year_publication year,
index(year_publication) );
#创建year_publication列为索引列
show create table bookG;
#查看索引
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`bookid` int(11) DEFAULT NULL,
`bookname` varchar(255) NULL,
`authors` varchar(255) NULL,
`info` varchar(255) NULL,
`comment` varchar(255) NULL,
`year_publication` year(4) DEFAULT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
explain select * from book where year_publication=1999G;
#使用explain判断索引是否正在被使用
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: book
partitions: NULL
type: ref
possible_keys: year_publication #表示使用的索引名称，没有定义名称会使用字段名为索引名
key: year_publication
key_len: 2
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
3.2 创建唯一索引
唯一索引主要原因就是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则该列值的组合必须唯一。
create table t1(
id int not null,
name char(30),
unique index Uniqidx(id));
#创建带唯一索引的表
show create table t1G;
#查看索引
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` char(30) NULL,
UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
3.3 创建单列索引
单列索引：是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。
create table t2 (
id int not null,
name char(50) null,
index singleidx(name) );
#创建单列索引
show create table t2G;
#查看创建的索引
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` char(50) NULL,
KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
3.4 创建组合索引
组合索引：是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行。
create table t3 (
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index multiidx(id,name,age) );
#创建组合索引
show create table t3 G;
#查看组合索引
*************************** 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) NULL,
KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
注意：组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
四、在已经存在的表上创建索引
4.1 添加唯一索引
alter table book add unique index idx_book(bookid);
#针对book表添加唯一索引，索引名称为idx_book（可自定义），针对bookid列建立索引
4.2 添加单列（前缀）索引
alter table book add index idx_comment(comment(50));
4.3 添加全文索引
alter table book add fulltext index idx_info(info);
4.4 添加组合索引
alter table book add index idx_auth_info(authors(20),info);
4.5 添加空间索引
create table t7(g geometry not null);
alter table t7 add spatial index idx_spatial(g);
4.6 查看索引
desc book;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| bookid | int(11) | YES | UNI | NULL | |
| bookname | varchar(255) | YES | | NULL | |
| authors | varchar(255) | YES | MUL | NULL | |
| info | varchar(255) | YES | MUL | NULL | |
| comment | varchar(255) | YES | MUL | NULL | |
| year_publication | year(4) | YES | MUL | NULL | |
+------------------+--------------+------+-----+---------+-------+
# UNI为唯一索引，MUL为非唯一索引，PRI为主键索引
show index from book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 0 | idx_book | 1 | bookid | A | 0 | NULL | NULL | YES | BTREE | | |
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | YES | BTREE | | |
| book | 1 | idx_comment | 1 | comment | A | 0 | 50 | NULL | YES | BTREE | | |
| book | 1 | idx_auth_info | 1 | authors | A | 0 | 20 | NULL | YES | BTREE | | |
| book | 1 | idx_auth_info | 2 | info | A | 0 | NULL | NULL | YES | BTREE | | |
| book | 1 | idx_info | 1 | info | NULL | 0 | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#可以清楚的看到创建的索引，及索引与列的对应关系
show index from bookG;
#效果同上
4.7 删除索引
alter table book drop index idx_book;
#使用alter的方式删除索引
drop index idx_info on book;
#使用drop的方式删除