超！详细的Mysql索引介绍

MySQL索引

前言

一、数据库索引

1.1：索引及分类

1.1.1：索引的概念

1.1.2：索引的作用

1.1.3：索引的分类

1.1.4：创建索引的原则依据

1.2：创建及查看索引

1.2.1：创建索引的方法

1.2.1.1：创建普通索引

1.2.1.2：创建唯一性索引

1.2.1.3：创建主键索引

1.2.1.4：创建全文索引

1.2.1.5：创建多列索引

1.2.1.6：删除索引

1.2.2：索引查看方法

前言

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

执行数据库操作时，有时需要多个操作表示一个操作流程，要么都执行，要么都不执行，这就是数据库的事务。

一、数据库索引

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

1.1：索引及分类

1.1.1：索引的概念

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

1.1.2：索引的作用

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价：一是增加了数据库的存储空间，二是在插入和修改数据时要化肥较多的时间（因为索引也会随之变动）。但是相比付出的代价，索引的作用显得更为重要:

• 设置了合适的索引之后，数据库利用各种快速的定位技术，可以大大加快数据的查询速度，这也是创建索引最主要的原因。
• 当表很大时，或者查询涉及多个表时，使用索引开始使查询速度加快成千上万倍。
• 可以降低数据库的I/O成本，并且索引还可以降低数据库的排序成本。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以加快表和表之间的连接。
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

1.1.3：索引的分类

MySQL的索引可以分为下面几类:

• ***普通索引：***这是最基本的索引类型，而且它没有唯一性之类的限制；
• ***唯一性索引：***这种索引和上面的“普通索引”基本相同。但是有一个区别：索引列的所有值只能出现一个，即必须唯一。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，在班级人员表中学生的学号上创建了唯一索引，则任何两个学生的学号不能相同。可以为空（NULL）
• ***主键索引：***主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每一个值都为唯一，且不能为空（NOT NULL）。
• ***全文索引：***索引类型为FULLTEXT，全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
• ***单列索引与多列索引：***索引可以是单列上创建的索引，也可以是在多列上创建的索引，多列索引可以区分其中一列可能有相同值的行，如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置查询条件，那么在这两列上创建多列索引将很有意义。

1.1.4：创建索引的原则依据

索引可以提升数据库查询的速度，但并不是任何情况都需要创建索引。因为索引本身会消耗系统资源，更重要的是在有索引的情况下，数据库查询会先进行索引查询，然后定位到具体的数据行，如果索引使用不当。反而会增加数据库的负担，这边我们就来看看创建索引的原则依据：

• 表的主键、外键必须有索引。主键具有唯一性，索引值也是唯一的，查询时可以快速定位到数据行。外键一般关联的是另一个表的主键，所以在多表查询时也可以快速定位。
• 数据量超过300行的表应该有索引。数据量较大时，如果没有索引，需要把表遍历一遍，严重影响数据库的性能。
• 经常与其他表进行表连接的表，在连接字段上应该建立索引。
• 唯一性太差的字段不适合建立索引。
• 更新太频繁的字段不适合做索引。
• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。
• 索引应该建在选择性高的字段上，如果很少的字段拥有相同值，即有很多独特值，则选择性高。
• 索引应该建在小字段上，对于大的文本字段甚至超长字段。不要建立索引。

1.2：创建及查看索引

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

1.2.1：创建索引的方法

使用CREATE INDEX创建索引，加上各种关键字，便可创建各类索引

1.2.1.1：创建普通索引

命令格式为

CREATE INDEX <索引名称> ON tablename(列的列表)

首先我们创建一个class的数据库以及数据表student

mysql> select * from student;
+--------+--------+--------+
| 学号   | 姓名   | 成绩   |
+--------+--------+--------+
|      1 | 张三   |     99 |
|      2 | 李四   |     98 |
|      3 | 王五   |     97 |
|      4 | 赵六   |     95 |
+--------+--------+--------+
4 rows in set (0.00 sec)

使用索引命令创建“姓名”字段的普通索引,索引的名字是 姓名_index 为了是索引的名字便于记忆，一般我们会用 _index结尾。

mysql> create index 成绩_index on student(成绩);

用show index可以查看是否已经建立了索引，这条命令我们会在后面具体介绍

mysql> show index from student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY      |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | 成绩_index   |            1 | 成绩        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

KEY_name对应的是索引名字，可以看出姓名_index这个索引已经存在，Non_unique对应的值为1，表示不是唯一性索引。

1.2.1.2：创建唯一性索引

命令格式为

CREATE UNIQUE INDEX <索引名称> ON tablename(列的列表)

我们对该表中的姓名进行唯一性索引

mysql> create unique index 姓名_index on student(姓名);

接着我们再次查看索引

mysql> show index from student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY      |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | 姓名_index   |            1 | 姓名        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | 成绩_index   |            1 | 成绩        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

名字为姓名_index的索引已经建立，它的Non_unique值是0，表示是唯一索引。

1.2.1.3：创建主键索引

有两种方法

• 我们在创建表的同时，主键索引自动创建，命令格式如下

CREATE TABLE tablename([...],PRIMARY KEY(列的列表))；

• 另一种是已经创建了表，没有指定主键，然后修改表假如主键，主键索引自动创建，命令格式如下

ALTER TABLE tablename ADD PRIMARY KEY(列的列表)；

我们可以用show index命令查看主键索引

mysql> show index from student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY      |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | 姓名_index   |            1 | 姓名        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | 成绩_index   |            1 | 成绩        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

这里需要注意，主键索引是设置主键后自动创建，并没有指定名字，系统自动生成名字为PRIMAY。

1.2.1.4：创建全文索引

在MySQL数据库中使用全文索引(FULLTEXT index),目前只有使用了MyISAM类型的表时候有效(在MySQL数据库版本5.5之前，默认的数据库引擎是MyISAM，而5.5版本之后默认的存储引擎为InnoDB)；全文索引可以建立在TEXT、CHAR或者VARCHAR类型的字段上或者字段组合上，创建表时指定或修改表时指定全文索引，命令格式如下:

CREATE TABLE 表名( 列名 TEXT，FULLTEXT(列名)) engine=MyISAM;
ALTER TABLE 表名 ADD FULLtext(列名)；

我们可以指定存储引擎来创建全文索引

mysql> create table student_desc(descript TEXT,FULLTEXT(descript))engine=MyISAM;

最后用show index命令进行查看，可以看到索引类型为FULLTEXT，即表示全文索引

mysql> show index from student_desc\G;
*************************** 1. row ***************************
        Table: student_desc
   Non_unique: 1
     Key_name: descript
 Seq_in_index: 1
  Column_name: descript
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

1.2.1.5：创建多列索引

多列索引创建时只需要在创建索引时指定多列即可，命令格式为

CREATE INDEX <索引名称> ON tablename(列的列表1,列的列表2...)

我们来创建一个“姓名”和"成绩"的索引

mysql> CREATE INDEX 姓名_成绩_index ON student(姓名,成绩);    ####注意“,”,一定是英文符号

使用show index命令查看创建的索引

mysql> show index from student;
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY             |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | 姓名_成绩_index     |            1 | 姓名        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | 姓名_成绩_index     |            2 | 成绩        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

1.2.1.6：删除索引

不需要使用索引时，应该删除索引

命令格式

DROP INDEX index_name ON tablename;
ALTER TABLE tablename DROP INDEX index_name;
ALTER TABLE tablename DROP PRIMARY KEY;

第一条命令是直接删除所以，第二条是修改表时删除索引，第三条是删除主键索引。

下面我们来演示下直接删除索引的方式

mysql> drop index 姓名_成绩_index on student;

然后我们查看下所以是否存在

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

可以看出，就剩下最后的主键索引（前面的几个索引我已经提前删除了）。

当我们修改表结构时，比如删除某一列，如果这一列存在索引，索引也会被删除

首先查看下手机表的索引

mysql> show index from student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY      |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | 成绩_index   |            1 | 成绩        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

然后我们删除成绩这一列

mysql> alter table student drop column 成绩;

接着我们查看下该表的索引

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

可以看出，删除了这一列的字段后，这个字段的索引也被删除了，但是主键索引依旧存在。

1.2.2：索引查看的方法

我们上面已经介绍了索引如何去查看，用show index命令，格式如下

SHOW INDEX FROM tablename；

或者是

SHOW KEYS FROM tablename；

例如

mysql> show keys from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | 学号        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

可以看出两条命令输出的结果一致。

在命令后面加上"\G“，输出索引信息是竖行

mysql> show index from student\G;
*************************** 1. row ***************************
        Table: student
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: 学号
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

下面我们说一下索引的显示参数

显示参数	描述
TABLE	表的名称
Non_unique	索引值的唯一性，0表示唯一性，1表示非唯一性
Key_name	索引名称
Seq_in_index	索引中的列序列号，从1开始
Column_name	列名称

这边我们就将MySQL数据库索引介绍完毕，下次我们接着带来数据库的事务介绍。