【Mysql】索引的类型最完整介绍

一、简介

Mysql主要的几种索引类型：

• 普通索引

• 唯一索引

• 主键索引

• 综合索引

• 全文索引

语句

create table table_name
[col_name data type] 
[unique|fulltext] 
[index|key] 
[index_name] 
(col_name[length])[asc|desc]

• 1、unique|fulltext :为可选参数，分别表示唯一索引、全文索引

• 2、index|key:为同义词，两者作用相同，用来指定创建索引

• 3、col_name:为需要创建索引的字段列，该列必须从数据库中定义的多个列中选择

• 4、index_name:指定索引的名称，为可选参数，如果不指定，默认将列名col_name作为索引值

• 5、length:为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

• 6、asc|desc:指定升序或者降序的索引值存储

二、索引类型

1、普通索引

是最基本的索引，它没有任何限制，有以下几种创建方式：

（1）直接创建索引

CREATE INDEX index_name ON table_name(col_name(length));

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (col_name(length);

（3）创建表的时创建索引

CREATE TABLE table_name(
    id int(11) NOT NULL AUTO_INCREMENT,
    title varchar(255) CHARACTER NOT NULL,
    content text CHARACTER NULL,
    PRIMARY KEY (id),
    INDEX index_name(title(10))
);

（4）删除索引

DROP INDEX index_name ON table_name;

2、唯一索引

与当前的普通索引类似，不同的是：索引列的值必须唯一，但是允许为空值，如果是组合索引，则列值的组合必须是唯一的，有一下几种创建方式：

（1）创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(col_name(length));

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE index_name ON (col_name(length));

（3）创建表时创建唯一索引

CREATE TABLE table_name(
    id int(11) NOT NULL AUTO_INCREMENT,
    title varchar(255) CHARACTER NOT NULL,
    content text CHARACTER NULL,
    UNIQUE index_name(title(10))
);

3、主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许是空值，一般是在创建表的时就创建主键索引：

CREATE TABLE table_name (
    id int(10) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

4、组合索引

指定多个字段上创建索引，只有在查询条件中使用了创建索引时的第一个字段，索引就会被使用，使用组合索引时，遵循最左前缀集合

ALTER TABLE table_name ADD INDEX name_city_age(name,city,age);

5、全文索引

• 主要用来查找文本中的关键字，而不是直接和索引中的值相比较，fulltext索引跟其他索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
• fulltext索引配合match against操作使用，而不是一般的where语句加理科。它可以在create table、alter table、create index中使用，不过目前只有char、varchar、text系列上可以创建全文索引。
• 注意：先将数据放入一个没有全局索引的表中，然后再用CREATE INDEX 为其创建全文索引fulltext，要比先为一张表创建fulltext索引再添加数据的数据快的多。

（1）创建表时创建全文索引

CREATE TABLE table_name(
    id int(11) NOT NULL AUTO_INCREMENT,
    title varchar(255) CHARACTER NOT NULL,
    content text CHARACTER,
    PRIMARY KEY (id),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_name(col_name);

（3）直接创建全文索引

CREATE FULLTEXT INDEX index_name ON table_name(col_name);

三、索引的缺点

1、会降低更新表速度

虽然索引能够大大的提高查询速度，但是会降低更新表的速度，如对表进行insert、update、delete等，因为更新表时，不仅会保存数据，还要保存索引文件。

2、会占用磁盘空间

建立索引会占用磁盘空间的索引文件，一般情况下这个问题并不严重，但是如果你在一个大表上创建了多种组合索引，索引文件会增长的很快。

四、注意要点

1、索引不会包含有null值的列

只要索引包含null值，都将不会被包含在索引中，组合索引中只要有一列含有null值，那么这一列对于组合索引就是无效的，所以我们在数据库设计时，不要将字段的默认值设置为null。

2、使用短索引

对串列进行索引，如果可能，应该指定一个前缀长度，例如：如果有一个char(255)的列，如果在前10个或者20个字符内，多数据唯一的，那么久不要对整个列进行索引，短索引不仅可以提高查询速度，而且可以节省磁盘空间和I/O操作。

3、索引列排序

查询值使用一个索引，因此如果where字句中已经使用了索引的话，那么order by中的列里不会再使用索引的，因此，数据库默认排序在符合要求的情况下，可以不要使用排序操作；也尽量不要包含多个列的排序，如果需要，最好给这些列创建符合索引。

4、like语句操作

一般情况下，不推荐使用like操作，如果非要使用，要注意：like '%aaa%'是不会使用索引的，但是like 'aaa%'可以使用索引。

5、不要在列上进行运算

如果在列上进行运算，将导致索引失效，而进行全表扫描，例如：

select * from table_name where YEAR(col_name) < 2017;

6、不要使用not in 和 <>操作

五、组合索引相关问题（一）

假定，在表table_name中，有一组合索引index(A,B,C)

1、可以用上该组合索引的查询条件

WHERE 条件：

• A > 5
• A = 5 AND B > 6
• A = 5 AND B = 6 AND C > 7
• A = 5 AND B IN(2,3) AND C > 7

2、能用上部分组合索引的查询

WHERE 条件：

• A > 5
• A > 5 AND B = 2
• A > 5 AND B = 2 AND C = 7
• A > 5 AND B = 2 AND C > 7
• A > 5 AND B > 2
• A > 5 AND B > 2 AND C > 7

当范围查询使用第一列，查询条件仅仅能使用第一列

• A = 5 AND B > 6 AND C = 2
• A = 5 AND B > 6 AND C > 2

当范围查询使用第二列时，查询条件仅仅能够使用第二列

3、不能用上组合索引的查询

（1）查询条件中不包含组合索引的首列字段时，不能够使用组合索引

• B > 5
• B = 6 AND C > 7
• C = 7
• C > 7

六、组合索引相关问题（二）

假定，在表table_name中，有一组合索引index(A,B)

1、可以用上该组合索引的查询条件

（1）首列查询或者排序

• WHERE A = 5
• WHERE A . 5
• ORDER BY A

（2）第一列条件过滤后第二列排序

• WHERE A = 5 ORDER BY B

（3）第一列、第二列同规则排序

此时，两列必须以相同的规则进行排序，要么都是desc，要么都是asc

• ORDER BY A DESC, B DESC

（4）数据检索和排序都在第一列

• WHERE A > 5 ORDER BY A

2、不能用上该组合索引的查询条件

（1）次列排序

• ORDER BY B

（2）第一列为范围查询，第二列排序

• WHERE A > 5 ORDER BY B
• WHERE A IN(2,3) ORDER BY B

（3）第一列、第二列不同规则排序

• ORDER BY A DESC, B ASC

七、使用EXPLAIN查看索引使用情况

EXPLAIN SELECT * FROM table_name;