MySQL 索引(一)：分类及语法

一、索引初了解

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

所有MySQL列类型可以被索引。

1.1 索引的用处

对相关列使用索引是提高SELECT 操作性能的最佳途径。

如果不使用索引，MySQL 必须从第1 条记录开始读完整个表直到找出相关的行。表越大，花费的时间越多。
如果表中查询的列有一个索引，MySQL 能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。如果一个表有1000 行，这比顺序读取至少快100 倍。

1.2 索引的缺点

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

索引可以提高查询速度，但是会影响插入记录的速度。
因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。
这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

二、索引分类

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY 表还支持hash 索引。

2.1 按实现分类

MySQL 中的索引是在存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL 目前提供了以下几种索引：

• BTREE 索引：最常见的索引类型，大部分引擎都支持BTREE 索引，例如MyISASM、InnoDB、MEMORY 等。
• HASH 索引：只有MEMORY 和InnoDB 引擎支持，适用于简单场景。
• RTREE 索引（空间索引）：空间索引是MylSAM 的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
• FULLTEXT（全文索引）：全文索引也是MylSAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从MySQL 5.6版本开始提供对全文索引的支持。

不同存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，有些存储引擎支持更多的索引数和更大的索引长度。

2.2 单列索引

• 普通索引：
  在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表t_student 的字段stuid 上建立一个普通索引，查询记录时就可以根据该索引进行查询。
• 唯一索引
  普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，在为这个数据索引创建索引的时候就应该用关键字UNIQE 把它定义为一个唯一索引，唯一索引可以保证数据记录的唯一性。
• 全文索引
  该索引可以用于全文搜索。主要用来查找文本中的关键字，而不是直接与索引中的值相比较。FULLTEXT 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。FULLTEXT 索引配合match against 操作使用，而不是一般的where 语句加like。
  它可以在create table，alter table ，create index 使用，不过目前只有char、varchar，text 列上可以创建全文索引，并且只有MyISAM 存储引擎支持FULLTEXT 索引。

值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index 创建FULLTEXT 索引，要比先为一张表建立FULLTEXT 然后再将数据写入的速度快很多。

索引总是对整个列进行，不支持局部(前缀)索引。

2.3 主键索引

一种特殊的唯一索引，在一张表中只能定义一个主键索引，用于唯一标识一条记录，用关键字PRIMARY KEY来创建。
主键索引可以是单列索引，也可以是多列索引。

2.4 多列索引

如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

如果列不构成索引最左面的前缀，MySQL 不能使用局部索引。假定有下面显示的SELECT语句。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
 
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1，col2，col3)有一个索引，只有前2个查询使用索引。第3个和第4个查询确实包括索引的列，但(col2)和(col2，col3)不是 (col1，col2，col3)的最左边的前缀。

也可以在表达式通过=、>、>=、<、<= 或者BETWEEN 操作符使用B-树索引进行列比较。如果LIKE 的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE 比较。例如，下面的SELECT 语句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第1个语句中，只考虑带’Patrick’ <=key_col < ‘Patricl’ 的行。在第2个语句中，只考虑带’Pat’ <=key_col < ‘Pau’ 的行。

下面的SELECT语句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

三、索引的相关操作

3.1 创建和添加索引

3.1.1 使用 CREATE INDEX 语句

CREATE INDEX indexName ON table_name (column_name); 

3.1.2 使用 ALTER TABLE 语句

ALTER table tableName ADD INDEX indexName(columnName); 

3.1.3 创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length))  
); 

3.2 删除索引

3.2.1 使用 DROP INDEX 语句

DROP INDEX [indexName] ON mytable; 

3.2.2 使用 ALTER TABLE 语句

ALTER table tableName ADD INDEX indexName; 

3.3 显示索引信息

SHOW INDEX FROM table_name;

3.4 查看是否有索引生效

EXPLAIN [EXTENDED] SELECT select_options;

借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

至此，本文结束。我是陈冰安，一个Java学习者。欢迎关注我的公众号【暗星涌动】，愿与你一同进步。