一、简介
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;
网友评论