Mysql索引的使用

索引用来快速地寻找那些具有特定值的记录，索引的目的在于提高查询效率。可以类比字典，如果要查 mysql这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。

一、Mysql索引的分类

1、普通索引 

最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建： 

1）、create index index_name on table_name（id）;

2）、alter table table_name add index index_name(id)；

3）、create table table_name( [......], index[index_name] (id) ); 

2、唯一性索引 

此索引和普通索引的唯一区别是：索引列的所有值都只能出现一次，即必须唯一。唯一索引可以用以下几种方式创建： 

1）、create unique index index_name on table_name（id）;

2）、alter table table_name add unique(id);

3）、create table table_name( [......], unique[index_name] (id) ); 

3、主键 

主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。创建方式：

1）、create table table_name( [......], primary key(id) ); 

2）、alter table table_name add primary key(id); 

4、全文索引 

在Mysql中，全文索引的索引类型为fulltext。全文索引可以在varchar或者text类型的列上创建。它可以通过create table命令创建，也可以通过alter table或create index命令创建。对于大规模的数据集，通过alter table（或者create index）命令创建全文索引要比把记录插入带有全文索引的空表更快。

另外索引既可以是单列索引，也可以是多列索引。多列索引的创建方式为：

alter table table_name add index index_name(id,name,age);

二、索引设计原则

1、搜索的索引列，不一定是所要选择的列。也就是说，最适合索引的列是出现在where子句中的列，或者连接子句中指定的列，而不是出现在select关键字后的选择列表中的列
2、使用唯一索引。考虑某列的分布，索引的列的基数越大，索引的效果越好。例如，对性别列做索引没多大用处
3、使用短索引。如果是对字符串进行索引，如果有可能应该指定前缀长度
4、利用最左前缀。尽量将使用频繁且过滤效果好的字段放左边
5、不要过度索引
6、Innodb默认会按照一定的顺序保存数据，如果明确定义了主键，则按照主键顺序保存。如果没有主键，但有唯一索引，就按照唯一索引的顺序保存。如果有几个列都是唯一的，都可以作为主键的时候，为了提高查询效率，应选择最常用访问的列作为主键。另外，Innodb的secondary index都会保存主键的键值，所有主键要尽可能选择较短的数据类型。可以看出，应当尽量避免对主键的修改。经过DBA的测试，保证主键的递增可以提高插入性能


 三、Mysql如何使用索引
1、对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用
2、对于使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能被使用
3、如果对大文本进行搜索，应该使用全文索引，而不是使用like'%......%'. 但不幸的是Innodb不支持全文索引
4、如果列名是索引，使用 index_column is null将使用索引。Oracle是不行的
5、如果mysql估计使用索引比全表扫描更慢，最不会使用索引
6、如果使用memory/head表并且where条件中不使用”=”进行索引列，那么不会用到索引。Head表只有在"="的时候才会使用索引
7、用or分割开的条件，如果or前的条件中的列有索引，而后面列中没有索引，那么涉及到的索引都不会被用到
8、不是多列索引的第一部分不会走索引
9、以%开始的like不会走索引
10、如果列是字符串，那么一定要在where条件中把字符串常量值用引号引起来，否则不能走索引。因为，Mysql默认把输入的常量值进行转换以后才进行检索
11、经过普通运算或函数运算后的索引字段不能使用索引
12、不等于操作不能使用索引，<、>、not in等
13、Order by 优化：某些情况下，Mysql可以使用一个索引满足order by,而不需要额外的排序。Where条件与order by 使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序
以下情况不使用索引：
1）、order by 的字段混合 ASC 和 DESC

2）、用于查询行的关键字与 ORDER BY 中所使用的不相同

3）、 对不同的关键字使用 ORDER BY