Mysql索引（二） 索引类型及优化

三、Mysql的索引

1. 聚集索引

说完了索引的数据结构，来看一下我们经常用到的数据库mysql，是怎样使用个索引的。我们在建表时，通常会把表的某个字段设为主键，这个主键就是一个索引。通常如果建表的时候不设置主键，那么表中的数据是无序的一行行的排列在磁盘中的，如果给表加上了主键，那存储数据的二结构就变成了树状结构，整个表就变成了一个索引，成做聚集索引。所以一个表只能有一个主键，主键的作用就是把表的数据格式转换成索引的格式（B+树格式）放置。如下图，使用id作为主键的索引表结构：

  

（此图是抄的，太懒了，不想画）

上图中叶子节点的第一航就是id,余下的所有行是每条记录的其他字段信息。从这棵树看，如果要查询id=49的数据，那就是通过B+树的查询定位到49的数据行，进而取出所有数据。所以这里只要经过三次查询即可得到想要的数据。

假如这里有上亿条数据，按照正常的逻辑去匹配，最坏的情况需要匹配上亿次，这是无法去接受的。而且上亿条数据明显不能一次性加载到内存中进行处理。换成B+树进行处理后，假设这棵树有10层，那么所需要的IO开销次数就是log(10)N次，大该十几次，速度得到了极大提升。

既然索引可以极大优化查询速度，那是不是索引越多越好呢，答案肯定故事否定的，者设计到几个问题：

1. 由于索引是按照B+树的顺序存储数据，那么在集进行插入的时候，为了不破坏B+树的结构，就要进行重新梳理结构，这回带来一些性能上的损耗。越多的索引以为这插入时需要维护的结构越多。
2. 索引是以文件形式存储的，变的数据lain更大的话，索引本身也会很大，会占据一定空间。
3. 索引的选取是有一定原理的，好的索引能够加大提升查询速度，而价值不大的索引非但不能提升查询性能，还加大了性能损耗和空间占用。

2. 非聚集索引

非聚集索引同样是使用平衡树结构，索引字段来源于表中的其它字段（非主键），每给一个字段加索引，该字段就会被赋值出来一份，生成索引，索引给表的字段增加索引会增加标的体积，占用更多的空间。

非聚集索引与聚集索引的区别在于：

通过聚集索引可以直接查找到需要的数据

非聚集索引则是西安查找到对应的的主键值，在使用主键值通过聚集索引查找，找到需要的数据。使用非聚集索引，不管是以什么方式查询表，最终都是先定位到主键值，在使用主键值的聚集索引查找数据。如下图：

（此图也是抄的，实在懒得画）

所以，从上面的区别可以看出，聚集索引（主键）是查找数据的唯一路径。

3. 联合索引

     相对于一般索引只有一个字段，联合索引是指为多个字段建立索引。假设我们为表中的（id，name）创建索引，则索引先会按照id字段进行排序，然后再按照name字段进行排序。在查询的时候，现根据第一个字段id查询，然后再根据第二个字段name进行查询，不能跳过id,直接从name字段开始查询，这也是联合索引的一个原则，叫做：最左前缀原则。

   举个例子，我们建立一个三个字段的索引（a,b,c），用一下方式都能使用到索引：

   select * from table where a = 1;

select * from table where a = 1 and b = 2;

select * from table where a = 1 and b=2 and c=3;

 

如果查询语句是：

    select * from table where a = 1 and c = 3;

那么只会用到索引a。如果查询语句是：

select * from table where c = 1 and a=2 and b=3;

 

可以看到，这里的条件顺序和索引顺序不一样，但是他同样会用到索引，因为mysql在进行查询的时候，查询优化器会判断语句该以什么样的顺序执行效率最高，最后才执行。

联合索引有一个提升查询效率的做法，称之为覆盖索引，就是将主键和其他字段设置成一个联合索引，这样在进行查询的时候由于索引中已经包含了主键，只需要一次查询就可一定味道数据位置，而不用像普通的非聚集索引一样，要先定位到主键，在通过主键索引定位导数据。节省了一次查询的时间。

联合索引总结：

1. 最左优先，以索引最左边字段未查询条件的都能使用索引
2. 当创建了索引（a,b,c）,相当于创建了索引（a）单列索引,(a,b)联合索引和（a,b，c）联合索引，共三个索引。如果要想索引生效，在条件中必须包含a字段。

1. 前缀索引

使用列的前缀代替整个列作为索引，当选择了合适的前缀长度时，就可以满足前缀索引接近于该字段的索引，并且因为索引变短减少了索引的开销和维护。

使用前缀索引的场景为：

  字符串列，需要进行字段匹配或者前缀匹配查询的列

  字符串本身较长，不适合使用全字段建立索引

  前缀字符的索引选择接近于全字段的索引选择

前缀索引的不足之处在于，语句使用了order by或者group by指令之后，前缀索引不会生效。

1. 全文索引

全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位。

全文索引主要在于两点：一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法主要有二元分词发，最大匹配法、统计法。全文索引的数据结构主要是倒排索引。关于呃逆方法有很多的研究，这里不单独说，简单的说一下倒排索引：

倒排索引，将每一个单词作为索引项，根据该索引项查找包含该单词的文本。因此，索引是单词和唯一记录文本的标示是一对多的关系。将索引单词排序，根据排序后的单词定位包含该单词的文本。

对于全文索引，一般用于文本较多的列，因为在对字符进行查询的候，使用like “%xxx%”这样的方法索引是无效的，通过建立全文索引就能加快查询速度。但需要注意的是，建立全文索引会很难用比较大的空间，而且在进行插入、更新等操作时，会对索引进行维护，存在维护成本。其实全文索引一般也用得，使用于表的数据变化不大、没有大量的DML语句操作的情况。

四、索引优化

在表的数据量大的时候，索引的结果尤其重要。但是索引的建立也是基于一定的原则，你的查询语句中是否用到了索引，如何根据建立的索引来优化查询语句，这些都是必须要掌握的。否则以错误的方式使用，即使建立的索引也不会凑效。

1. 正确使用索引

1. 对于创建了多列的索引（即联合索引），只要查询条件中使用该到了索引最左边的列，索引就会生效。

比如 表中字段有a,b,c,d,e,设置了联合索引（a，b，c）,其最左列的字段为a,所以只要查询条件中包含了a,那索引就会生效，如下几条语句：

    select * from table where a = 1 and b = 2;

select * from table where c=2 and a=3;

select * from table where d=2 and a=3;

如果不包含左边的字段a,只包含了之后的索引字段，那么索引不会生效，这也是联合索引的最左前缀原则，如一下语句：

   select * from table where b = 1 and c = 2;

select * from table where c=2 and d=3;

select * from table where b=2 and d=3;

1. 如果使用了前缀索引，在使用like进行查询时，%号的位置决定了索引会不会生效。只有%不是第一个字符索引才会生效。

比如对name字段的前三位建立了索引字段，那么以下面这个语句是生效的：

  select * from table where name like 'cde%'

 

但是以下语句索引就不会生效：

   select * from table where name like '%de'

select * from table where name like 'c%d'

select * from table where name like 'cd%e'

1. 多个索引在多条件查询时指挥生效第一个索引，所以多条件查询最快的是建立联合索引

比如，表中有两个单列索引a和 b，那么以下语句指挥用到一个索引：

   //a在前，只用到了a列索引

select * from table where a = 1 and b = 2;

//b在前，只用到了b列索引

select * from table where b = 2 and a = 1;

但如果是建立了联合索引（a,b），那么a列和b列两个索引都会生效，如以下语句：

  //mysql会自动调优，第二个热语句同样使用到了联合索引

select * from table where a = 1 and b = 2;

select * from table where b = 2 and a = 1;

1. 有索引，但不使用

针对于某些查询语句，可能使用索引和不使用索引效果相同，升甚至更慢，此时就没有很必要使用索引。

1. 例如，以下语句，如果id自增均匀的分布在1到100之间，那下列的查询使用索引就无太大必要：

   select * from table where ID >1 and ID < 100

1. 在语句中使用到or时，必须是在or之前和or之后的条件都存在索引，索引才会生效，否则不生效，比如表中字段a 和b是索引，那么以下or语句索引能生效：

  select * from table where a > 1 or b < 10

而以下语句中索引均不会生效：

 select * from table where a > 1 or c < 10

select * from table where c > 1 or b < 10

1. 如果字段是字符型，但是语句传入的是数字量，如果不加上引号的话，则不会使用索引，比如一个字符型的字段IDNumber,以下语句第一条不会使用索引，第二条会使用索引：
2. select * from table where IDNumber = 132156 select * from table where IDNumber = '136156'

4.使用函数操作索引字段后，索引不会生效。比如设置name字段为索引，下面这条语句则不会生效：

   select * from table where substr(name,1,2) = "SO";

这里的索引字段name加了一层函数，不会使用到索引。同样如果给索引字段使用加减乘除等运算，也不会使用索引，比如索引为number的一下语句就不会使用到索引：

   select * from table where number*2 > 100;