MySQL的索引

什么是索引

索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制，MYSQL目前提供了一下4种存储类型的索引。常用的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MYISAM和InnoDB存储引擎只支持BTREE索引；MEMORY和HEAP存储引擎可以支持HASH和BTREE索引。

• B-Tree索引：最常见的索引类型，大部分引擎都支持B树索引。
• HASH 索引：只有Memory引擎支持，使用场景简单。
• R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
• Full-text(全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

BTREE索引与HASH索引

1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算

3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4）Hash 索引在任何时候都不能避免表扫描

Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

下列范围查询适用于BTREE索引和HASH索引：

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20); 

下列范围查询只适用于BTREE索引：

SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'abc%' OR key_col BETWEEN 'lisa' AND 'simon'; 

Mysql 目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符进行索引，这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序Order By和分组Group By 操作的时候无法使用。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

语法：

CREATE INDEX idx_title ON film (title(10)) 

索引的优点

1、通过创建唯一索引，保证数据库表每行数据的唯一性

2、大大加快数据查询速度

3、在使用分组和排序进行数据查询时，可以显著减少查询中分组和排序的时间

索引的缺点

1、维护索引需要耗费数据库资源

2、索引需要占用磁盘空间，索引文件可能比数据文件更快达到最大文件尺寸

3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

索引的分类

1.普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = ...)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引,MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

创建普通索引

 

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)
); 

 

使用SHOW CREATE TABLE查看表结构；可以发现，book表的year_publication字段成功建立了索引其索引名字为year_publication，我们向表插入一条数据，然后使用EXPLAIN语句查看索引是否有在使用

mysql> INSERT INTO BOOK VALUES(12,'NIHAO','NIHAO','文学','henhao',1990);

因为语句比较简单，系统判断有可能会用到索引或者全表扫描

 

mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1990\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

 

EXPLAIN语句输出结果的各个行的解释如下：

select_type： 表示查询中每个select子句的类型（简单 OR复杂） 
type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：（从上至下，效果依次变好） 
possible_keys ：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用 
key： 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL 
key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度 
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 
rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数 
Extra：包含不适合在其他列中显示但十分重要的额外信息 如using where，using index

在EXPLAIN命令的输出结果里，第2列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关 联关系(JOIN)。在各种类型的关联关系当中，效率最高的是system，然后依次是const、eq_ref、ref、range、index和 All(All的意思是：对应于上一级数据表里的每一条记录，这个数据表里的所有记录都必须被读取一遍–这种情况往往可以用一索引来避免)。

possible_keys 数据列给出了MySQL在搜索数据记录时可选用的各个索引。key数据列是MySQL实际选用的索引，这个索引按字节计算的长度在key_len数据列里给出。比如说，对于一个INTEGER数据列的索引，这个字节长度将是4。如果用到了复合索引，在key_len数据列里还可以看到MySQL具体使用了它的哪些部分。作为一般规律，key_len数据列里的值越小越好(意思是更快)。

ref 数据列给出了关联关系中另一个数据表里的数据列的名字。row数据列是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。row 数据列里的所有数字的乘积可以让我们大致了解这个查询需要处理多少组合。

extra 数据列提供了与JOIN操作有关的更多信息，比如说，如果MySQL在执行这个查询时必须创建一个临时数据表，就会在extra列看到 using temporary字样。

2.唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。主键索引是一种特殊的唯一索引，不允许有空值，必须指定为primary key。

（1）主键一般在创建表的时候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ) ”。

（2）但是，我们也可以通过修改表的方式加入主键：“ALTER TABLE 表名 ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）

注：不能用CREATE INDEX语句创建PRIMARY KEY索引

 

mysql> CREATE TABLE t1(
 id INT NOT NULL,
 NAME CHAR(30) NOT NULL,
 UNIQUE INDEX UniqIdx(id));
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE t1;

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `NAME` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引

--重复插入相同id的2条数据报错
mysql> INSERT INTO t1 values (1,'xiaohua');
Query OK, 1 row affected
mysql> INSERT INTO t1 values (1,'xiaohua');
1062 - Duplicate entry '1' for key 'UniqIdx' 

3.单列索引和多列索引

单列索引只包含单个列，多列索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用多列索引时遵循最左前缀集合

 

mysql> CREATE TABLE t2(
  id INT NOT NULL,
  NAME CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR (255),
  INDEX MultiIdx (id, NAME, info(100))
);
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE t2;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `NAME` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`NAME`,`info`(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 

 

结果可以看到id,name,age字段上已经成功建立了一个名为MultiIdx的复合索引，我们向表插入两条数据

 

mysql> INSERT INTO t2(id ,NAME,age,info) VALUES(1,'小明',12,'nihao'),(2,'小芳',16,'nihaoma');

--可以看到possible_keys和key为MultiIdx证明使用了复合索引
mysql> EXPLAIN SELECT * FROM t2 WHERE id=1 AND NAME='小芳'\G
*********** 1. row ***********
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 94
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)
--如果我们只指定name而不指定id
mysql> EXPLAIN SELECT * FROM t2 WHERE  NAME='小芳'\G
*********1. row ************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

 

4.全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

 

mysql> CREATE TABLE t3 (
  id INT NOT NULL,
  NAME CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR (255),
  FULLTEXT INDEX FulltxtIdx (info)
) ENGINE = MYISAM;
Query OK, 0 rows affected

mysql> show create table t3;

CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `NAME` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FulltxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
 
–修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
–直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

 

由结果可以看到，info字段上已经成功建立名为FulltxtIdx的FULLTEXT索引,FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

5.空间索引

空间索引必须在MYISAM类型的表中创建，而且空间类型的字段必须为非空

 

mysql> CREATE TABLE t4(
         g GEOMETRY NOT NULL,
         SPATIAL INDEX spatIdx(g)
)ENGINE=MYISAM;
Query OK, 0 rows affected

mysql> show create table t4;

CREATE TABLE `t4` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

可以看到，t4表的g字段上创建了名称为spatIdx的空间索引。注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MYISAM。

已经存在的表上创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE或者CREATE INDEX语句

1、使用ALTER TABLE语句创建索引，语法如下：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]
[index_name](col_name[length],...)[ASC|DESC]

与创建表时创建索引的语法不同，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list) 

a）在t1表中的name字段上建立NameIdx普通索引，添加索引之后，使用SHOW INDEX语句查看指定表中创建的索引

mysql> ALTER TABLE t1 ADD INDEX NameIdx(NAME);
mysql> show index from t1; 

各个参数的含义：

　　1、TABLE：要创建索引的表 
　　2、Non_unique：索引非唯一，1代表是非唯一索引，0代表唯一索引 
　　3、Key_name：索引的名称 
　　4、Seq_in_index：该字段在索引中的位置，单列索引该值为1，复合索引为每个字段在索引定义中的顺序 
　　5、Column_name：定义索引的列字段 
　　6、Sub_part：索引的长度 
　　7、NULL：该字段是否能为空值 
　　8、Index_type：索引类型

b）在t2表的age和info字段上创建复合索引

mysql> ALTER TABLE t2 ADD INDEX t2AgeAndInfo(age,info);
mysql> show index from t2;

c）创建表t5，然后在t5表上创建全文索引

mysql> CREATE TABLE t5(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;
Query OK, 0 rows affected 

注意修改ENGINE参数为MYISAM，MYSQL默认引擎InnoDB不支持全文索引，使用ALTER TABLE 语句在info字段上创建全文索引

mysql> ALTER TABLE t5 ADD FULLTEXT INDEX infoFTIdx(info);
Query OK, 0 rows affected
mysql> SHOW INDEX FROM t5 

d）创建表t6，然后在t6表上创建名称为spatIdx的空间索引

CREATE TABLE t6(g GEOMETRY NOT NULL)ENGINE=MYISAM; 

使用ALTER TABLE在表t6的g字段建立空间索引

mysql> ALTER TABLE t6 ADD SPATIAL INDEX spatIdx(g);
Query OK, 0 rows affected
mysql> SHOW INDEX FROM t6; 

2、使用CREATE INDEX语句创建索引，语法如下

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name
ON table_name(col_name[length],...)  [ASC|DESC] 

可以看到CREATE INDEX语句和ALTER INDEX语句的基本语法一样，只是关键字不同。

 

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

--我们建立一个books表
CREATE TABLE books (
  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
) 

--建立普通索引
mysql> CREATE INDEX BkNameIdx ON book(bookname);
--建立唯一索引
mysql> CREATE UNIQUE INDEX UniqidIdx ON book(bookId)
--建立复合索引
mysql> CREATE INDEX BkAuAndInfoIdx ON book(AUTHORS(20),info(50));
--建立全文索引
mysql> CREATE FULLTEXT INDEX infoFTIdx ON t5(info);
--建立空间索引
mysql> CREATE SPATIAL INDEX spatIdx  ON t6(g); 

 

3、删除索引

 MYSQL中使用ALTER TABLE或者DROP INDEX语句来删除索引，两者实现相同功能

 a）使用ALTER TABLE 删除索引

ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY 
mysql> ALTER TABLE books DROP INDEX BkNameIdx; 

因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

注意：AUTO_INCREMENT约束字段的唯一索引不能被删除，如果只指定PRIMARY KEY则可以正常删除！

 b）使用DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name
mysql> DROP INDEX BkNameIdx ON books;

提示：删除表中的某列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果索引中的所有列都被删除，则整个索引将被删除！