搞懂mysql索引有这一篇就够（呕心狂写一万字，保姆级mysql索引技巧）_mysql 索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

一、索引概述

1.1、什么是索引

索引是用于快速找出在某个列中拥有特定值的行。

如果没有索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。

如果拥有索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

1.2、为什么建立索引

如果有一张产品表，记录着4W产品的信息。有一个品牌的字段记录产品的品牌，现在想要查询出这个品牌的产品。

如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。

如果拥有索引，那么会将该品牌字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历4W条产品数据。

1.3、索引存储分类

其中MySQL中的索引的存储类型有两种：BTREE、HASH。要想知道在这二种存储类型中是如何查找的，那么就必须学会算法的知识，对于现在的我们只需要了解索引的作用及功能就好了。

1.4、索引优缺点

优点：

所有的字段都可以被索引，也就是可以给任意字段设置索引

加快数据的查询速度

缺点：

创建索引和维护索引要耗费时间

数据量的增加，所耗费的时间也会增加

索引也需要占空间，如果我们的索引量越来越大的话，那么索引文件可能达到我们数据的最大线值

表中数据发生变化时，索引也需要动态维护，降低数据维护效率

1.5、索引的使用原则

通过上面说的优点和缺点，我们可以看出，索引并不是越多越好，而是需要自己合理的使用。那么那些表应该去使用索引呢？

避免使用过多索引： 经常更新的表、数据量小的表、相同值少的字段上等

使用索引： 经常查询的表、不同值较多的字段上等

一个表中很够创建多个索引，这些索引会被存放到一个索引文件中(专门存放索引的地方)

二、索引类型

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE

MEMORY/HEAP存储引擎：支持HASH和BTREE索引

MySQL目前主要有以下几种索引类型：

单列索引（普通索引、唯一索引、主键索引）

组合索引

全文索引

空间索引

2.1、单列索引-普通索引

MySQL中普通索引并没有什么限制，纯粹为了查询数据更快一点。

可以在定义索引的字段中插入重复值和空值

创建表时创建索引

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  index/key 索引名(字段名(长度))
);
1
2
3
4
5
6
7
8

案例：

create table book(
  bid int primary key auto_increment,
  bname varchar(200),
  bcbs varchar(200),
  index(bname)
);

create table book1(
  bid int primary key auto_increment,
  bname varchar(200),
  bcbs varchar(200),
  key(bname)
);

mysql> show create table book;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bid` int(11) NOT NULL AUTO_INCREMENT,
  `bname` varchar(200) DEFAULT NULL,
  `bcbs` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`bid`),
  KEY `bname` (`bname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.01 秒)

mysql> show create table book1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book1 | CREATE TABLE `book1` (
  `bid` int(11) NOT NULL AUTO_INCREMENT,
  `bname` varchar(200) DEFAULT NULL,
  `bcbs` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`bid`),
  KEY `bname` (`bname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.01 秒)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

测试查询：

mysql> explain select * from book where bname='计算机';
1

id： select识别符。这是select的查询序列号，也就是一条语句中，该select是第几次出现。比如：在这条语句中select只出现了1次。

select_type： select查询类型。

simple：表示为简单的select，没有使用union或子查询，就是简单的select。

primary：表示最外面的select ，拥有子查询时，就会出现2次select。

union：表示第二层。在select 之后使用了 union

subquery：在子查询中，第二SELECT。

table： 数据库表的名字。他们按被读取的先后顺序排列

type： 指定本数据表和其他数据表之间的关联关系。

ref： 就是连接程序无法根据键值只取得一条记录的情况。

system： 表只有一行记录（等于系统表）。

可能的取值有 const、eq_ref、index和all等

possible_keys： MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，bname

key： 实际选用的索引

key_len： 显示了mysql使用索引的长度(也就是使用的索引个数)，当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。

ref： 给出关联关系中另一个数据表中数据列的名字。常量（const）

rows： MySQL在执行这个查询时预计会从这个数据表里读出的记录条数。

extra： 提供了与关联操作有关的信息，没有则什么都不写。

2.2、单列索引-唯一索引

唯一索引是索引列中的值必须是唯一的，允许为空值。在使用唯一索引时要加入记录。

创建表时创建索引

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  unique index 索引名(字段名(长度))
);
1
2
3
4
5
6
7
8

案例：

create table book2(
  bid int,
  bname varchar(200),
  bcbs varchar(200),
  unique index unique_bid(bid)
);

mysql> show create table book2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book2 | CREATE TABLE `book2` (
  `bid` int(11) DEFAULT NULL,
  `bname` varchar(200) DEFAULT NULL,
  `bcbs` varchar(200) DEFAULT NULL,
  UNIQUE KEY `unique_bid` (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.02 秒)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试查询

mysql> explain select * from book2 where bid=1;
1

注意：

要查看其中查询使用的索引，必须先往表中插入数据，然后在查询数据，不然查找一个没有的bid值，是不会使用索引的。

mysql> insert into book2 values(1,'计算机基础','清华出版社');
Query OK, 1 rows affected (0.06 秒)
mysql> explain select * from book2 where bid=1;
1
2
3

可以看到，通过id查询时，会使用唯一索引。并且还实验了查询一个没有的id值，则不会使用索引。

2.3、单列索引-主键索引

是一种特殊的唯一索引，不允许有空值。

创建表时创建索引

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  primary key 索引名(字段名(长度))
);
1
2
3
4
5
6
7
8

案例：

create table book3(
  bid int,
  bname varchar(200),
  bcbs varchar(200),
  primary key(bid)
);

mysql> show create table book3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book3 | CREATE TABLE `book3` (
  `bid` int(11) NOT NULL,
  `bname` varchar(200) DEFAULT NULL,
  `bcbs` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.02 秒)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试查询

mysql> insert into book3 values(1,'计算机基础','清华出版社');
Query OK, 1 rows affected (0.12 秒)

mysql> explain select * from book3 where bid=1;
1
2
3
4

主键索引就是：我们以前声明的主键约束，就是一个主键索引。

2.4、组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

创建表时创建索引

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  index 索引名(字段名1(长度),字段名2(长度),...字段名n(长度))
);
1
2
3
4
5
6
7
8

案例：

create table stu1(
  sid int,
  sname varchar(20),
  age int,
  sex varchar(2),
  index index_sid_sname_age(sid,sname,age)
);

mysql> show create table stu1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu1  | CREATE TABLE `stu1` (
  `sid` int(11) DEFAULT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  KEY `index_sid_sname_age` (`sid`,`sname`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.01 秒)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

注意：

最左前缀

组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。

例如，这里由sid、sname和age 3个字段构成的索引，索引行中就按sid/sname/age的顺序存放，索引可以索引下面字段组合(sid，sname，age)、（sid,age）、(sid，sname)或者(sid)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（sname，age）组合就不会使用索引查询

测试查询

mysql> explain select * from stu1 where sid=1 and sname="job";



mysql> explain select * from stu1 where age=17 and sname="job";
1
2
3
4
5

2.5、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

比如：有学员的备注信息里有"成绩很好，是一个好学生" 通过“好学生”，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节。

创建表时创建索引

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  fulltext index 索引名(字段名(长度))
)ENGINE=MyISAM;
1
2
3
4
5
6
7
8

案例：

create table stu2(
  sid int,
  sname varchar(20),
  age int,
  sex varchar(2),
  info text,
  fulltext index full_info(info)
)ENGINE=MyISAM;

mysql> show create table stu2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu2  | CREATE TABLE `stu2` (
  `sid` int(11) DEFAULT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `info` text,
  FULLTEXT KEY `full_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.02 秒)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

测试全文索引

mysql> explain select * from stu2 where match(info) against('好学生');
1

注意：

在使用全文搜索时，限制较多：

需要使用MATCH函数

只能通过MyISAM引擎

只能在CHAR,VARCHAR,TEXT上设置全文索引

搜索的关键字默认至少要4个字符，搜索的关键字太短就会被忽略掉

2.6、空间索引

空间索引是对空间数据类型的字段建立的索引。

MySQL中的空间数据类型有四种，GEOMETRY（几何类型）、POINT（点）、LINESTRING（线）、POLYGON（面）。在创建空间索引时，也要使用MyISAM引擎，创建空间索引的列也不能为null。

创建表时创建索引

格式：
create table 表名(
  字段名1 字段类型1,
  字段名2 字段类型2,
  ...
  字段名n 字段类型n,
  spatial index 索引名(字段名(长度))
)ENGINE = MyISAM;
1
2
3
4
5
6
7
8

案例：

create table t(
  g geometry not null,
  spatial index s_g(g)
)ENGINE = MyISAM;

mysql> show create table t;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `g` geometry NOT NULL,
  SPATIAL KEY `s_g` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------+
1 行于数据集 (0.01 秒)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

三、索引操作

3.1、创建索引

格式：
alter table 数据表名 add [unique|fulltext|spatial] [index|key] [索引名][字段名][asc|desc]
1
2

查看表中索引

格式：
show index from 数据库表名;
1
2

案例：

mysql> show index from book2;
1

Table:创建索引的表

Non_unique：表示索引非唯一，1代表 非唯一索引， 0代表 唯一索引，意思就是该索引是不是唯一索引

Key_name：索引名称

Seq_in_index 表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要知道单列索引该值就为1，组合索引为别的)

Column_name：表示定义索引的列字段

Sub_part：表示索引的长度

Null：表示该字段是否能为空值

Index_type：表示索引类型

我们继续为book2加一个索引

mysql> alter table book2 add index i_bname(bname);
Query OK, 0 rows affected (0.07 秒)

mysql> show index from book2;
1
2
3
4

3.2、删除索引

格式一：
alter table 数据库表名 drop index 索引名;
1
2

案例：

删除book2中的i_bname索引

mysql> alter table book2 drop index i_bname;
Query OK, 0 rows affected (0.02 秒)

mysql> show index from book2;



格式二：
drop index 索引名 on 数据库表名;
1
2
3
4
5
6
7
8
9

案例：

删除book2中的unique_bid索引

mysql> drop index unique_bid on book2;
Query OK, 0 rows affected (0.12 秒)

mysql> show index from book2;
空的数据集 (0.01 秒)
1
2
3
4
5