mysql 索引介绍

在大数据表里，同一条mysql语句，使用索引与不使用索引是具有极大的区别。索引作用相当于书的目录，可以加快搜索的速度。但是目录是需要用额外的纸张来保存，建立索引也是需要额外的空间。所以过多的索引可能会导致索引文件比数据文件还要庞大。索引是可以加快，但是要慎用。下面介绍mysql的几种索引。

1. 普通索引

最基本的索引，创建方式有：

//直接创建索引
CREATE INDEX index_name ON table(column(length))
//修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
//创建表时建立索引
CREATE TABLE table_name(
`id` int(11) not null auto_increment,
`name` varchar(30) default '',
primary key (`id`),
index index_name(`name`)
);
//删除索引
DROP INDEX index_name ON table_name

2. 唯一索引

这种索引的索引列的值必须是唯一的，可以是null。

//直接创建索引
CREATE UNIQUE INDEX index_name ON table_name(column(length))
//修改表结构创建索引
ALTER TABLE table_name ADD UNIQUE index_name ON (column(length))
//创建表时建立索引
CREATE TABLE table_name(
`id` int(11) not null auto_increment,
`name` varchar(30) default '',
primary key (`id`),
UNIQUE  index_name(`name`)
);
//删除索引(没有变化)
DROP INDEX index_name ON table_name

3. 主键索引

在唯一索引基础上，主键列的值不能会null。

4. 全文索引

MySQL 4.0以上 MyISAM引擎就支持了full text search 全文搜索，MySQL 5.6.4里才添加了InnoDB引擎的Full-Text索引支持。mysql支持在CHAR、VARCHAR、TEXT类型的列上定义全文索引。
全文索引是指利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。并不是所有字符串都可以使用到全文索引。在全文索引里，有一个叫暂停词的概念（STOP WORD），你查找的字符串必须是暂停词才会使用索引，而暂停词由mysql来生成。还要注意的是，查询的字符串必须超过ft_min_word_len的大小（最短的索引字符串，默认值为4），而且该字符串应该有较低的阈值（一般低于50%，表示该词没有非常多）
建立全文索引时，应该先把数据放到数据库，再建立索引。当查询多列数据时：建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。

//直接创建索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
//修改表结构创建索引
ALTER TABLE table_name ADD FULLTEXT index_name ON (column(length))
//创建表时建立索引
CREATE TABLE table_name(
`id` int(11) not null auto_increment,
`name` varchar(30) default '',
primary key (`id`),
FULLTEXT  index_name(`name`)
);
//删除索引(没有变化)
DROP INDEX index_name ON table_name
//使用全文索引
MATCH (column) AGAINST ('string')

5. 组合索引

组合索引指的是多个列组合成为一个索引。组合索引必须使用最左前缀，否则索引失效。如索引（a，b，c，d），在查询时where a=1 and b=2 and c=3会使用索引，where a=1 and c=3不会使用索引，where a=1 and b=2 and c>4 and d=2会使用索引abc。对于最后一种情况，当某个列使用范围查询时，其后面所有列都不会使用查询（注，列指的是索引中的列）。

6. 建立索引原则

1. 最左前缀匹配。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
3. 尽量不要对该列进行运算，不要调用函数，这样会使索引失效。

7. 索引失效

1. 单列索引不存储null值，复合索引不存储全为null的值。索引不能存储Null，所以对这列采用is null条件时，因为索引上根本没null值，不能利用到索引，只能全表扫描。
2. 前导模糊查询不能利用索引。（like ‘%a’）
3. or条件查询时，有其中一个不是索引列则其他所有索引失效。如有索引(a),(b)，查询条件where a=1 or b=3 会使用索引，where a=1 or b=1 or c=3全部索引失效。
4. 组合索引没有使用最左前缀进行查询。
5. 索引列是字符串，在查询时没有使用单引号引住查询内容。如where string=’188’会使用索引，where string=188不会使用索引。
6. 全表扫描比索引快不会使用索引。
7. 在索引列上进行运算或使用函数，索引失效。如where id=id+1 ，where id=rand_num()都会索引失效。
8. 使用<>，not in运算，索引失效。
9. 使用hash类型索引时，进行范围查询或order by，索引失效。
10. 表关联时，关联字段类型，长度不一致会使索引失效。

最好的做法是，将写出的sql语句使用explain来查看一下该语句的执行情况。