MySQL-索引
索引简介
在 MySQL 中，通常有以下两种方式访问数据库表的行数据：
- 顺序访问
顺序访问是在表中实行 全表扫描 ，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
- 索引访问
索引访问是通过遍历索引来访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引之所以快，是因为其底层采用 B+ 树，详细内容见：Mysql索引 .
索引最大的优点就是在海量数据下能够大幅提高查询速度。当然，索引也有缺点：1）索引需要占磁盘空间；2）当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。索引可以提高查询速度，但是会影响插入记录的速度 。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序 ，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。
1 | mysql> SELECT * FROM stu_info; |
使用 EXPLAIN 分析查询情况，使用 score 索引查找时，扫描的行数为 1；使用 name 无索引查找时，扫描的行数为 4，为全表扫描。
索引类型
按逻辑划分
-
普通索引 。
普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。1
CREATE INDEX index_id ON tb_student(id);
-
唯一索引 。
创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复 。唯一索引列的值必须唯一，允许有空值，且允许存在多个空值 。如果是组合索引，则列值的组合必须唯一。1
CREATE UNIQUE INDEX index_id ON tb_student(id);
-
主键索引 。
创建主键时，自动成为索引。主键索引是一种特殊的唯一索引，不允许值重复或者值为空 。创建主键索引通常使用PRIMARY KEY关键字，不能使用CREATE INDEX语句创建主键索引。 -
空间索引 。
空间索引主要用于地理空间数据类型 GEOMETRY，不常用。1
CREATE SPATIAL INDEX index_line ON tb_student(line);
-
全文索引 。
全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。 实际开发中，不采用 mysql 自带的全文索引（其效率不高），而使用全文搜索框架 Solr 和 ElasticSearch。1
CREATE FULLTEXT INDEX index_info ON tb_student(info);
按实现划分
-
BTREE索引 。
目前大部分的索引都是采用 B-树索引来存储的。B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：- 查询必须从索引的最左边的列开始。
- 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
- 存储引擎不能使用索引中范围条件右边的列。
-
哈希索引
又叫散列索引。仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。哈希速度比 BTREE 更快，但缺点也很明显：- 相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
- 只支持等值比较，如 “=” ，“IN()” 或 “<=>”。
- 不能使用 HASH 索引排序
- HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的.
存储引擎 允许的索引类型 MyISAM BTREE InnoDB BTREE MEMORY/HEAP HASH, BTREE
按数量划分
-
单列索引 。
单列索引就是索引只包含原表的一个列。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。 -
多列索引 。
也称为复合索引或组合索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引 。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。 例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。1
CREATE INDEX index_na ON tb_student(id,name,sex);
创建索引
使用 CREATE INDEX 语句
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键 。
语法格式：
1 | CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC]) |
-
<长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小 ，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引 。前缀最长为255字节。对于 MyISAM 和 InnoDB 表，前缀最长为1000字节。注意前缀的限长以字节计，而CREATE INDEX语句中的前缀长度指的是字符的数目。对于使用多字节字符集的列，在指定列的前缀长度时，要考虑这一点。
另外，当查询条件的前缀与索引前缀相同时，索引也起不到作用。如下：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
33mysql> SELECT * FROM stu_info;
+-------+------+------+-------+
| ID | name | age | score |
+-------+------+------+-------+
| 12134 | Jack | 18 | 150 |
| 12135 | Mike | 20 | 149 |
| 12136 | Dan | 19 | 130 |
| 12137 | Xuan | 19 | 141 |
| 12345 | Mike | 20 | 120 |
+-------+------+------+-------+
5 rows in set (0.00 sec)
mysql> SHOW INDEX FROM stu_info;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stu_info | 1 | ID | 1 | ID | A | 1 | 4 | NULL | YES | BTREE | |
|
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM stu_info WHERE ID='12134';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | stu_info | NULL | ALL | ID | NULL | NULL | NULL | 5 | 80.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM stu_info WHERE ID='12345';
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | stu_info | NULL | ref | ID | ID | 15 | const | 1 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)注意，ID 索引长度为 4。
-
ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。目前(2005年)，
ASC|DESC关键词被分析，但是被忽略；索引值均以递增顺序存储。
1 | mysql> SELECT * FROM stu_info; |
使用 CREATE TABLE 语句 索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。语法格式：
1 | ###指定主键索引 |
1 | mysql> CREATE TABLE stu ( |
在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现。
使用 ALTER TABLE 语句
使用 ALTER TABLE 语句向已有的表添加索引：
1 | ###添加普通索引，一般用INDEX不用KEY |
1 | mysql> ALTER TABLE stu_info ADD INDEX (score); |
注意，以上创建索引时，不指定索引名则索引名同列名。 同一张表中，索引名不能重复。
查看索引
1 | SHOW INDEX FROM <表名> |
1 | mysql> SHOW INDEX FROM no1sc.staff; |
| 参数 | 说明 |
|---|---|
| Table | 表示创建索引的数据表名，这里是 tb_stu_info2 数据表。 |
| Non_unique | 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。 |
| Key_name | 表示索引的名称。 |
| Seq_in_index | 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。 |
| Column_name | 表示定义索引的列字段。 |
| Collation | 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。 |
| Cardinality | 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。 |
| Sub_part | 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。 |
| Packed | 指示关键字如何被压缩。若没有被压缩，值为 NULL。 |
| Null | 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。 |
| Index_type | 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。 |
| Comment | 显示评注。 |
删除索引
1 | DROP INDEX <索引名> ON <表名> |
1 | mysql> show index from staff; |
注意事项
索引在什么时候会失效
- LIKE 关键字配置的字符串不能以“%”开头；
- 使用多列索引时，查询条件必须要使用这个索引的第一个字段；
- 使用 OR 关键字时，OR 关键字连接的所有条件都必须使用索引 。
提高索引效率
-
选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。 -
为经常需要排序、分组和联合操作的字段建立索引
经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。 -
为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。 -
限制索引的数目
索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。 -
尽量使用数据量少的索引如果索引的值很长，那么查询的速度会受到影响。
例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。 -
数据量小的表最好不要使用索引
.由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。 -
尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 -
删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
