别啰嗦，开门见山。问题是这样的。请问下面的sql语句，为了加快查询速度，如何建立索引？以下以mysql数据库为准。select*fromtestwherea=?andb>?orderbyclimit0,100结果可能会让你大吃一惊。我们先准备好运行环境，然后根据最左前缀原则和explain关键字进行验证。结果真的颠覆了xjjdog多年的认知。准备阶段为了验证，我们创建一个简单的数据表。有三个简单的int字段a、b和c。创建表`test`(`id`int(11)NOTNULL,`a`int(11)DEFAULTNULL,`b`int(11)DEFAULTNULL,`c`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8接下来写一个简单的存储过程插入10w条数据。等待1分钟左右，数据插入完成。DROPPROCEDUREIFEXISTStest_initData;DELIMITER$CREATEPROCEDUREtest_initData()BEGINDECLAREiINTDEFAULT1;WHILEi<=100000DOINSERTIINTOtest(id,a,b,c)VALUES(i,i*2,i*3,i*4);SETii=i+1;ENDWHILE;END$CALLtest_initData();由于mysql有最左前缀原则，所以我们将abc的三列全部排好，创建了6个索引。这6个索引涵盖了所有基于abc查询的情况。createINDEXidx_a_b_contest(a,b,c);createINDEXidx_a_c_bontest(a,c,b);createINDEXidx_b_a_contest(b,a,c);createINDEXidx_b_c_aontest(b,c,a);createINDEXidx_c_a_bontest(c,a,b);createINDEXidx_c_b_a,a);使用Explain验证1.自动选择索引explainselect*fromtestwherea>10andb>10orderbyc首先，我们使用上面的sql语句来验证。原来查询使用了索引idx_a_b_c，而且只使用了前缀a和b。extra部分使用了filesort，这是一种性能很差的方法。让我们尝试更改查询参数的位置。explainselect*fromtestwherec>10andb>10orderbya这次索引自动选择了idx_b_a_c，但是还是用了filesort，查询效果是一样的。按照上面的逻辑，不应该选择idx_b_c_a吗？2.指定索引接下来，使用forceindex方法强制指定索引。这里直接给出结果，就是下面的sql。explainselect*fromtestFORCEINDEX(idx_c_b_a)wherea>10andb>10orderbyc结果如下。我们使用强制索引来指定要使用的索引。这次效果很好，显示使用了索引，使用了where，只在索引上完成了操作。但是扫描的行数增加了。然而，这与我们的经历相反。idx_c_b_a的索引是在字段(c,b,a)上创建的。根据最左原则，支持的搜索条件为：c、cb、cba。本例中将orderby后面的参数作为前缀的头信息。我们删除其他索引，只留下idx_c_b_a，然后去掉强制索引部分。原来mysql现在可以自动选择索引了。再看另一种情况，orderby上有两个参数。explainselect*fromtestFORCEINDEX(idx_b_c_a)wherea>10orderbyb,c结果如上，使用idx_b_c_a，不要使用filesort。其他指标都不是最优的。3.explain部分返回值的含义。我们根据mysql自身提供的explain工具得出以上结论。这个工具可以输出一些有用的信息。下面是返回值相关部分的含义。(1)select_type表示SELECT的类型。常见的值有：SIMPLE不使用表连接或子查询的简单表。PRIMARY主查询，即外层的查询。UNIONUNION中的第二个或后续查询语句。SUBQUERY子查询中的第一个。(2)type表示MySQL在表中查找所需行的方式，或者访问类型。常见的访问类型如下，从下到上，性能越来越差。system，const表只有一行记录（相当于system表），是const类型的特殊列。eq_ref唯一索引扫描，对于每个索引键，表中只有一条记录与其匹配。ref非唯一索引扫描，返回匹配单个值的所有行，本质上是索引访问，它返回匹配单个值的所有行，但是，它可能会找到多个符合条件的行，所以它应该是find和扫描。range仅检索给定范围的行，索引用于选择行，键列显示使用了哪个索引。这种范围扫描索引比全表扫描要好，因为它只需要从索引中的一个点开始，到另一个点结束，而不需要扫描整个索引。indexFullIndexScan，Index和All的区别在于索引类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。allFulltablescan，性能最差(3)Extrausingindex：表示对应的select操作使用覆盖索引避免访问表的数据行，效率不错。如果同时出现usingwhere，则表示该索引用于执行索引键值的查找；如果没有同时使用where，说明该索引是用来读取数据的，而不是执行查找动作的。usingfilesort：表示mysql会使用外部索引对数据进行排序，而不是按照表中的索引顺序读取。MySQL中不能用索引完成的排序操作称为“文件排序”。usingtemporary：使用临时表保存中间结果，mysql在对查询结果进行排序时使用临时表。常见于排序orderby和分组查询groupby。End可以看出，当我们创建多个索引时，mysql的查询优化可能无法进行智能分析，使用最优的方法，需要使用forceindex来指定索引。mysql中的索引主要用在where条件和排序动作中。有两种情况。先过滤后排序，会使用过滤条件中的index参数，但是排序会使用较慢的外部排序。因为这个结果集是经过过滤的，没有涉及到索引。先排序，再过滤，可以使用相同的索引，排序的优先级高于过滤。选择合适的指标，边过滤边做。但是扫描的行数会增加。我想，mysql无法理解这两个过程，谁快谁慢，所以我选择了最普通的方式，直接选择了第一个。甚至在索引很多的时候，直接就晕了。如果建立过多的索引，可能会间接损坏mysql。这是一个现象。至于深层次的原因，欢迎看过mysql相关源码的朋友解释。对于频繁更改排序字段的代码来说，这不是一个好兆头。考虑到程序的稳定性，我觉得有必要尽量减少where条件过滤的结果集。在这种情况下，创建(a,b)的联合索引可能是一种妥协。
