MySQL索引

索引是对数据库表中一个或多个字段的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

索引的意义

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个字段中有特定值的行。如果不使用索引，MySQL必须从第一条记录开始检索表中的每一条记录，直到找出相关的行。那么表越大，查询数据所花费的时间就越多。如果在表中查询的字段有索引，MySQL能够快速到达一个位置去检索数据文件，而不需要再去查看所有数据，那么将会节省很大一部分查询时间。
比如说emp表中1W个员工的记录，要查询工号为7566的员工信息select * from emp where empno=7566，如果没有索引，服务器会从表中第一条记录开始，一条条往下遍历，直到找到empno=7566的员工信息。如果在empno这个字段上创建索引，就可以索引文件里面找empno=7566这一行的位置，而不需要再遍历1W条记录了。

索引的优缺点

所有MySQL的字段类型都可以添加索引，但是索引也不是越多越好，而是要根据业务数据合理的使用。

优点

• 通过索引对数据进行检索，大大提高了数据的查询效率。
• 通过创建唯一索引，可以保证数据库表中每一条记录的唯一性。
• 在实现数据的参照完整性方面，可以加速表和表之间的连接。
• 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点

• 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引也需要占磁盘空间的，创建的索引太多，索引文件也会占用一定的存储空间。
• 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，这样就降低了数据的维护速度。

创建索引的原则

索引设计不合理或缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。
需要创建索引的情况：

• 主外键和唯一约束的字段自动创建索引
• 频繁作为查询条件的字段应该创建索引
• 查询中排序的字段应该创建索引
• 查询中分组或统计的字段应该创建索引

不需要创建索引的情况：

• 表中记录太少不需要创建索引
• 需要频繁增删改的字段不适合创建索引
• where子句中用不到的字段不需要创建索引
• 重复值较多的字段不需要创建索引

索引的结构

索引是在存储引擎中实现的，使用不同的存储引擎，所支持的索引也是不同的。
在mysql中常用两种索引结构BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引。

MySQL的InnoDB存储引擎是支持hash索引的，不过我们必须启用，hash索引的创建由InnoDB存储引擎自动优化创建，我们干预不了。

索引的类型

索引的类型可以分类以下几种：

• 普通索引：最基本的索引，没有任何限制
• 唯一索引：索引字段的取值不能重复，可以有空值，但空值也只能出现一次。
• 主键索引：索引字段的取值不能为空，也不能重复。
• 组合索引：一个索引包含多个字段，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
• 全文索引：通过关键字符，就能找到该字段所属的记录行。仅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT类型的字段上使用。
• 空间索引：对空间数据类型（GEOMETRY、POINT、LINESTRING、POLYGON）的字段建立的索引，仅限MyISAM引擎，且要求索引字段的取值不能为空。

索引的操作

实际上索引也是一张表，创建索引时，数据库管理系统会在本地磁盘建立索引文件，里面保存了索引字段，并指向实体表的记录。

创建索引

创建表的同时须指定索引名、表名和字段名。
语法：

create index <索引名> on <表名>(<字段名>);

自动创建索引：

• 在表中定义了主键约束时，会自动创建一个对应的主键索引。
• 在表中定义了外键约束时，会自动创建一个对应的普通索引。
• 在表中定义了唯一约束时，会自动创建一个对应的唯一索引。

示例：emp表中的job添加普通索引

mysql> create index job_index on emp(job);

查看索引：

语法：

show index from <表名>;

示例：查看emp表中的索引

mysql> show index from emp;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY   |            1 | empno       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | ename     |            1 | ename       | A         |          14 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | deptno    |            1 | deptno      | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | job_index |            1 | job         | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

使用索引

在查询语句中使用索引会大大提升数据的检索速度。
示例：

mysql> select ename,job,deptno from emp where job='salesman';
+--------+----------+--------+
| ename  | job      | deptno |
+--------+----------+--------+
| allen  | salesman |     30 |
| ward   | salesman |     30 |
| martin | salesman |     30 |
| turner | salesman |     30 |
+--------+----------+--------+

删除索引

删除索引只是删除了表中的索引对象，表中的数据不会被删除。
语法：

drop index <索引名> on <表名>;

示例：

mysql> drop index job_index on emp;
mysql> select ename,job,deptno from emp where job='salesman';
+--------+----------+--------+
| ename  | job      | deptno |
+--------+----------+--------+
| allen  | salesman |     30 |
| ward   | salesman |     30 |
| martin | salesman |     30 |
| turner | salesman |     30 |
+--------+----------+--------+