mysql单列索引、多列索引的使用

一、简介     

  索引是数据库优化的最重要手段，数据库的索引可以加快查询速度，原因是索引使用特定的数据结构(B-Tree/B+tree等)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度。

  如果查询语句使用索引（通常是where条件匹配索引)就会利用树的结构加快查找，索引会按值查找到要查找的行在表中位置，不需回表查询数据的就是聚簇索引(索引和数据存放在一起)。通常是需要回表再查数据，需要消耗额外的磁盘IO。所以有些时候（如按顺序读取数据）全表扫描会比使用索引快的原因就在于此。

   查询条件只有一个字段时，在该字段建立索引即可，可优化的地方是对于text blob字段使用前缀索引。

   当查询条件有多个字段时，单列索引和多列索引有很大的区别。如果使用多列索引，where条件中字段的顺序非常重要，需要满足最左前缀列。最左前缀：查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。

二、多列索引适合的场景

  1.全字段匹配

  2.匹配部分最左前缀

  3.匹配第一列

  4.匹配第一列范围查询(可用用like ｀a%｀,但不能使用like ｀%b｀)

  5.精确匹配某一列和和范围匹配另外一列

  order by操作中出现的字段同样适用于按值查找的规则，where+order by中出现的字段需可以建立满足如上五种规则多列索引。使用多列所需需要按照最左索引列查找；不能跳过中间列；如果某一列是范围查询，那么其右边所有列无法使用索引。IN什么情况下是范围查询，什么情况下是多个等值查询？如果有order by排序时，多个等于条件查询就是范围查询，没有order by排序就没有限制。

  例如,建立多列索引(name, age, id),只能使用索引的前两列。in是范围查询
select * from table where name=｀nginx.cn｀ and age in(15,16,17) order by id

  可以使用整个索引，in是按值查询
select * from table where name=｀nginx.cn｀ and age in(15,16,17) and id =｀3｀

三、复合索引的建立以及最左前缀原则

  索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。
例如，如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间。你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。
  假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：
   state, city, zip
   state, city
   state
  MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。
  如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了(例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引)。

四、通过实例理解单例索引、多列索引以及最左前缀原则

  实例：现在我们想查出满足以下条件的用户id：
  mysql>SELECT ｀uid｀ FROM people WHERE lname｀='Liu'  AND ｀fname｀='Zhiqun' AND ｀age｀=26 ； 因为我们不想扫描整表，故考虑用索引。
  1、单列索引：
  ALTER TABLE people ADD INDEX lname (lname);
  将lname列建索引，这样就把范围限制在lname='Liu'的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun'的结果集  2，再扫描结果集2，找到 age=26的结果集3，即最终结果。
  由于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。
  2、多列索引：
  ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

  为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。

  注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

  3.最左前缀：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

  注：在创建多列索引时，要根据业务需求，当不需要考虑排序和分组时，where子句中使用最频繁的一列放在最左边，因为这种选择性最高。

五、为什么where条件只使用一列索引

  与其说是“数据库查询只能用到一个索引”，倒不是说是和全表扫描只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是用一个索引。

如这条语句：

select count(1) from table where column1 = 1 and column2 = 'foo' and column3 = 'bar'

我们来想象一下当数据库有N个索引并且查询中分别都要用上他们的情况：
查询优化器（针对用户的请求进行内部优化，生成或重用执行计划并传输给存储引擎来操作数据，最终返回结果给用户的组件）需要进行N次主二叉树查找[这里主二叉树的意思是最外层的索引节点]，此处的查找流程大概如下：
查出第一条column1主二叉树等于1的值，然后去第二条column2主二叉树查出foo的值并且当前行的coumn1必须等于1，最后去column主二叉树查找bar的值并且column1必须等于1和column2必须等于foo。这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生，所以当遇到以下语句的时候，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。

所以如上条的情况，最佳推荐是使用index(column1,column2,column3） 这种联合索引，此联合索引可以把b+tree结构的优势发挥得淋漓尽致：
一条主二叉树（column=1），查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询，在二级二叉树查询到column2=foo后，去三级二叉树column3=bar查找。