MySQL 索引详解

---

引言

在企业信息化的过程中，数据库中表的数据量越来越大，性能会急剧下降，创建索引对于保持良好的性能非常关键。
索引是对查询性能优化最有效的手段，能够轻易地将查询性能提高几个数量级。

---

一、数据库索引

数据库索引是数据库系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据

1. 索引的概念

• 索引是一种特殊的文件，包含着对数据表中所有记录的引用指针。通俗点说，索引就好比是一本书的目录，能加快数据库的查询速度。
• 例如需要遍历 200 条数据，在没有索引的情况下，数据库会遍历全部 200 条数据后选择符合条件的；而有了相应的索引后，数据库会直接在索引中查找符合条件的选项。
• 数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录

2. 索引的作用

2.1 作用

建立索引的目的是为了加快对表中记录的查找或排序，设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建所有的最主要的原因。

• 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度
• 可以降低数据库的 IO 成本，并且索引还可以降低数据库的排序成本
• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性
• 可以加快表与表之间的连接
• 在使用分组和排序字句进行数据查询时，可大大减少分组和排序的时间

2.2 负作用

• 索引需要占用额外的磁盘空间：
  对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址，而 InnoDB 引擎的表数据文件本身就是索引文件。
• 在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

3. 索引的分类

• 普通索引
  这是最基本的索引类型，而且它没有唯一性之类的限制

• 唯一性索引
  与普通索引类似，但有一个区别是唯一索引列的所有值都只能出现一次，即必须唯一；
  当现有数据库中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存；
  数据库还可能防止添加将在表中创建重复键值的新数据。
  例如，如果在员工信息统计表中对员工的姓（name）上创建了唯一索引，则任何两个员工都不能同姓

• 主键索引
  主键是一种唯一性索引，但它必须指定为 “PRIMARY KEY”，在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型，该索引要求主键中每个值都唯一。

• 全文索引
  索引类型为 FULLTEXT ，适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息
  全文索引可以在 CHAR、VARCHAR 或 TEXT 类型的列上创建
  每个表只允许有一个全文索引

• 组合索引（单列索引与多列索引）
  索引可以是单列上创建的索引，也可以是在多列上创建的索引；
  多列索引可以区分其中一列可能有相同值的行，如果经常同时搜索两列、多列、按两列或多列排序时，索引也很有帮助。
  例如，如果经常在同一查询中为姓和名两列设置查询条件，那么这两列上创建多列索引将很有意义。

4. 创建索引的原则依据

索引可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

• 创建索引的原则依据：

① 表的主键、外键必须有索引；主键具有唯一性，索引值也是有唯一的，查询时可以快速定位到数据行；外键一般关联的是另一个表的主键，所以在多表查询时也可以快速定位。

② 数据量超过300行的表应该有索引；数据量较大时，如果没有索引，需要把表遍历一遍，严重影响数据库的性能

③ 经常与其他表进行连接的表，在连接字段上应该建立索引

④ 唯一性太差的字段不适合建立索引

⑤ 更新太频繁的字段不适合创建索引。在表中进行增、删、改、查时，索引也会有相应的操作产生；字段更新的过于频繁，对于系统资源占用也会更多。

⑥ 经常出现在 Where 子句中的字段，特别是大表的字段，应该建立索引。

⑦ 索引应该建在选择性高的字段上。如果很少的字段拥有相同值，即有很多独特值，则选择性很高。

⑧ 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

二、创建索引

在 MySQL 中，可以使用命令创建多种类型的索引，也可以查看索引。

1. 创建索引的方法

根据企业的需求选择了合适的索引之后，可以使用 CREATE INDEX 创建索引，加上各个关键字，便可创建各个类型的索引。

• 创建普通索引

#命令格式如下
CREATE INDEX <索引名> ON tablename (列的列表)
#(列名(length))：length是可选项。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾，容易识别

#先创建数据库price，数据表list
mysql> create database price;
mysql> use price;
#表中插入信息
mysql> insert into list values(5,'gouwa','123456','19132323','杭州','this is 213');
mysql> select * from list;
+------+----------+--------+-------------+---------+-------------+
| id   | name     | cardid | phone       | address | remark      |
+------+----------+--------+-------------+---------+-------------+
|    1 | zhangsan | 123456 | 19181716565 | 南京    | hehe        |
|    2 | wangw    | 123456 | 19181565    | 北京    | hhhhhhh     |
|    3 | liu      | 12345  | 19186543    | 南京    | hehehe      |
|    4 | wangwang | 123456 | 19132323    | 杭州    | this is sb  |
|    5 | gouwa    | 123456 | 19132323    | 杭州    | this is 213 |
+------+----------+--------+-------------+---------+-------------+
5 rows in set (0.00 sec)
#创建索引
mysql> create index phone_index on list(phone);
mysql> show index from list;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| list  |          1 | phone_index |            1 | phone       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

• 修改表的方式创建

#语法格式
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

mysql> alter table list add index id_index(id);
mysql> select id from list;                        	#查询索引
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> select id,name from list;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | wangw    |
|    3 | liu      |
|    4 | wangwang |
|    5 | gouwa    |
+------+----------+
5 rows in set (0.00 sec)

• 创建表时指定索引

#语法格式
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

mysql> create table test(id int(4) not null,name varchar(20) not null,cardid varchar(18) not null,index id_index(id));

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE "test" (
  "id" int(4) NOT NULL,
  "name" varchar(20) NOT NULL,
  "cardid" varchar(18) NOT NULL,
  KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 创建唯一索引

创建唯一索引必须得满足每个值都是唯一的，否则无法创建

• 直接创建

#语法格式
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

mysql> create unique index name_index on list (name);
show create table member;

• 修改表方式创建

#语法格式
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

mysql> alter table test add unique name_index (name);

mysql> select name from test;
+--------+
| name   |
+--------+
| laod   |
| sanba  |
| wangba |
+--------+
3 rows in set (0.00 sec)

• 创建表的时候指定索引

#语法格式
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

create table test3 (id int,name varchar(20),unique id_index (id));
show create table test3;

3. 创建主键索引

• 创建表的时候指定

#语法格式
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

#用了两种方式创建，一种是在字段中直接指定，第二种是在字段结束后指定
mysql> create table test4 (id int primary key,name varchar(30));
mysql> create table test5 (id int,name varchar(20),primary key (id));

show create table test4;
show create table test5;

• 修改表方式创建

#语法格式
ALTER TABLE 表名 ADD PRIMARY KEY (列名); 


mysql> alter table list add primary key (id);
mysql> show create table list;

4. 创建全文索引

• 直接创建索引

#语法格式
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

#全文索引 fulltext，全文只可在 char、varchar、text 类型上创建，且每个表只允许有一个
mysql> create fulltext index remark_index on list (remark);

mysql> show create table list;

• 修改表方式创建

#语法格式
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

• 创建表的时候指定索引

#语法格式
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

• 使用全文索引查询

#语法格式
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

select * from list where match(remark) against('this is vip');

5. 创建组合索引

#语法格式
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';


create table test001 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));

show create table test001;

insert into test001 values(1,'wangsansan','123123');

select * from test001 where name='wangsansan' and id=1;

6. 删除索引

#直接删除索引
DROP INDEX 索引名 ON 表名;

drop index name_index on member;

#修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

alter table member drop index id_index;
show index from member;

#删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

三、查看索引

1. 查看索引的方法

show index from 表名;
show index from 表名\G;								#在命令后面加上“\G”，可以竖向显示索引的信息
或													#两种方法的显示结果相同
show keys from 表名;
show keys from 表名\G;								

2. 索引详细信息解析

显示参数	描述
Table	表名称
Non_unique	索引值的唯一性，0 表示唯一性，1 表示非唯一性
Key_name	索引的名称
Seq_in_index	索引中的列序号，从 1 开始
Column_name	列名称
Collation	列以什么方式存储在索引中。在 MySQL 中，有值 A（升序）或 NULL（无分类）
Cardinality	索引中唯一值数目的估计值
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目；如果整列被编入索引，则为 NULL
Packed	指示关键字如何被压缩；如果没有被压缩，则为 NULL
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
Comment	备注

总结

• 数据库索引可以协助快速查询表中的数据，但并不是任何字段都需要创建索引

• 索引分为：
  ① 普通索引 ：针对所有字段，没有特殊的需求/规则
  ② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值
  ③ 组合索引 (多列/多字段组合形式的索引)
  ④ 全文索引（varchar char text）
  ⑤ 主键索引 ：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

• 创建索引的方法：
  ① 在创建表的时候，直接指定index
  ② alter修改表结构的时候，进行add 添加index
  ③ 直接创建索引index