引言

MySQL中的索引就是一个高效组织的数据结构，能够提高数据检索的速度。

文章目录

• 0×1.MySQL索引简介
• 0×2.索引实例
• a.普通索引
• b.主键索引
• c.唯一索引
• d.全文索引
• e.多列索引
• 0×3.索引管理

0×1.MySQL索引简介

索引的存放位置，对于Innodb储存引擎的表，索引会根据MySQL配置，集中存放在一个文件中，前面已经介绍过，而对于Myisam储存引擎的表，索引信息单独储存在表的.MYI文件中；

Index文件中不仅仅储存了要查询的数据的索引值，在索引值的下面还有这个索引所对应数据的指针信息，指向数据库文件真实数据位置，简单的说，索引就相当于书本的目录章节和对应页码，数据库可以根据章节标题和页码寻找对应数据，加快查询速度；

MySQL有以下几种常用索引：

普通索引
主键索引
唯一索引
全文索引
多列索引

一般将索引添加在查询频繁且重复度低的列上，实际上主键索引和唯一索引就是本系列文章前面所介绍的"主键约束"和"Unique约束"，这两种约束就是两种特殊索引。

0×2.索引实例

a.普通索引

创建普通索引：

					--在所有列声明后面，添加"key 索引名称(要索引的列名称)"，一般索引名称和被索引的列名称相同即可
					mysql> create table t52(id int,tx text,key id(id));

					--将id列设置成主键索引，将tx列设置成普通索引并设置索引长度，tx(6)表示只取tx字段前六个字符成为索引依据（相当于一本书的目录，而目录每一行标题都只有六个字后面是对应页码）
					mysql> create table t54(id int primary key,tx text,key tx(tx(6)));

					--普通索引在表结构中Key字段是MUL，主键不用多说自然是PRI了
					mysql> desc t54;
					+-------+---------+------+-----+---------+-------+
					| Field | Type    | Null | Key | Default | Extra |
					+-------+---------+------+-----+---------+-------+
					| id    | int(11) | NO   | PRI | NULL    |       |
					| tx    | text    | YES  | MUL | NULL    |       |
					+-------+---------+------+-----+---------+-------+
					

b.主键索引

主键索引又叫"主键约束"，前面的文章都有详细说明，实例：

					--将id列设置为主键索引列，两种写法都可以
					mysql> create table t54(id int,tx text,primary key(id));
					--或
					mysql> create table t54(id int primary key,tx text);
					

c.唯一索引

唯一索引又叫"Unique约束"前面已经详细介绍过，实例：

					--将id列设置为唯一索引列，两种写法都可以
					mysql> create table t55(id int,tx text,unique key(id));
					--或
					mysql> create table t55(id int unique key,tx text);
					

d.全文索引

全文索引只对使用空格分隔字符的语言环境有效，比如英文是一条句子中每个单词中间有空格，而中文字符之间是没有空格的，另外，全文索引在MySQL5.6.4之前的版本中，只有Myisam引擎的表才能支持全文索引，但MySQL5.6.10版本后，InnoDB也能支持这种索引技术了，请看下面的实例：

					--显示当前的MySQL版本
					mysql> select version();
					+-------------------------+
					| version()               |
					+-------------------------+
					| 5.5.43-0ubuntu0.14.04.1 |
					+-------------------------+

					--因为本地的版本低于5.6.4，所以只好在创建表的时候指定Myisam引擎，否则是不能使用fulltext参数的，全文索引的添加与普通索引语法没什么区别
					mysql> create table t56(id int primary key,tx text,fulltext key tx(tx)) engine=myisam;

					mysql> desc t56;
					+-------+---------+------+-----+---------+-------+
					| Field | Type    | Null | Key | Default | Extra |
					+-------+---------+------+-----+---------+-------+
					| id    | int(11) | NO   | PRI | NULL    |       |
					| tx    | text    | YES  | MUL | NULL    |       |
					+-------+---------+------+-----+---------+-------+
					

e.多列索引

时候需要将数据分开储存便于管理，但有时候又需要将他们看成一个整体索引查询，这个时候就可以使用多列索引：

					--创建多列索引，在所有列声明的最后使用"key 多列索引自定义名称(索引列1,索引列2,....)"，用逗号分隔需要一起索引的全部列
					mysql> create table t57(firstname varchar(100),lastname varchar(100),key fandl(firstname,lastname));

					--插入两条记录
					mysql> insert into t57 values('www.qingsword.com','qingsword');
					mysql> insert into t57 values('qingsword.com','qingsword');


					--使用explain查看后面的select语句使用了哪些索引，explain也可以测试上面的普通索引等，看索引到底有没有被数据库用上，possible_keys和key说明这条查询语句用上了我们上面创建的fandl多列索引
					mysql> explain select * from t57 where firstname='qingsword.com' and lastname='qingsword' \G
					************* 1. row *************
					           id: 1
					  select_type: SIMPLE
					        table: t57
					         type: ref
					possible_keys: fandl
					          key: fandl
					      key_len: 606
					          ref: const,const
					         rows: 1
					        Extra: Using where; Using index
					

不论查找过程中列名称如何前后排序，MySQL都能正确的组合，并且使用到正确的索引，前提是多列索引中位于开头的那些数据要包含在其中；假设有三段数据a,b,c都加入了多列索引（key keyName(a,b,c)），而我们仅仅b and c就不会主动去使用多列索引，但我们只要包含a,不论后面是c还是b,或仅仅只有a，都能主动使用多列索引查询；

这就是数据库中"左前缀"的概念，比如有一段数据‘ABCDEF’加入了索引，现在我们查询前面ABC这一部分，数据库可以根据索引去查找ABC开头的数据，但是我只知道DEF这一部分，数据库就不会主动使用索引去查找，因为索引中没有DEF开头的数据。

					--仅使用多列索引后面的部分查询，并没有包含"左前缀"
					mysql> explain select * from t57 where lastname='qingsword' \G
					************* 1. row *************
					           id: 1
					  select_type: SIMPLE
					        table: t57
					         type: index
					possible_keys: NULL  --显示没有可用的索引
					          key: fandl 
					          --那么这里为什么会显示用到了索引呢？数据库中有个概念叫做"索引覆盖"，当被查询的列的值被包含在某个索引中时，数据库会根据这个包含关系找到对应的那个索引，再根据那个索引去找数据，类似于一个被动索引查找的过程，并非一开始就使用索引去查找。

					mysql> explain select * from t57 where firstname='qingsword.com' \G
					************* 1. row *************
					possible_keys: fandl --使用"左前缀"查找，会主动去使用索引
					          key: fandl


					mysql> explain select * from t57 where lastname='qingsword' and firstname='qingsword.com' \G
					************* 1. row *************
					possible_keys: fandl 
					--只要where中包含"左前缀"数据，and前后的位置并不会影响主动使用索引查找
					          key: fandl
					

0×3.索引管理

● 查看表索引信息

					--查看上面创建的t56表的索引
					mysql> show index from t56 \G
					************* 1. row *************
					        Table: t56
					   Non_unique: 0
					     Key_name: PRIMARY --id列，主键索引

					************* 1. row *************
					        Table: t56
					   Non_unique: 1
					     Key_name: tx 
					 Seq_in_index: 1
					  Column_name: tx
					   Index_type: FULLTEXT--tx列，多列索引
					

● 删除表索引信息

					--删除普通索引和多列索引以及全文索引都可以使用下面两种方法
					--方法一
					--语法：drop index 索引名称 on 表名称;
					mysql> drop index tx on t56;

					--方法二
					--语法：alter table 表名称 drop index 索引名称;
					mysql> alter table t52 drop index id;

					--主键索引和唯一索引删除方法相同，以主键索引为例
					--语法：alter table 表名称 drop [primary key|unique key];
					mysql> alter table t56 drop primary key;
					

● 追加索引信息

					--给t52表id列再次添加上普通索引
					mysql> alter table t52 add key id(id);
					--或，效果同上
					mysql> alter table t52 add index id(id);

					--给t56表添加主键索引
					mysql> alter table t56 add primary key(id);
					--或添加唯一索引
					mysql> alter table t56 add unique key(id);

					--多列索引添加语法
					mysql> alter table 表名称 add key(列1,列2,列3,...);

					--全文索引添加语法
					mysql> alter table 表名称 add fulltext key(列名称);