前提数据库版本是5.7.17。创建一张user表，预设500w条数据，每个字段的值为前缀加上从0开始的数字，加起来为5000000，如图：然后用explain分析是否有索引。最左匹配原则我们先建一个组合索引，username，jobno，company三个字段：首先执行如下sql：EXPLAINSELECT*FROM`user`WHEREusername='cxj1000000'ANDjobno='jn1000000'ANDcompany='com1000000';EXPLAINSELECT*FROM`user`WHEREusername='cxj1000000';EXPLAINSELECT*FROM`user`WHEREusername='cxj1000000'ANDjobno='jn1000000';EXPLAINSELECT*FROM`n00`WHEREjobno=0'ANDusername='cxj1000000';EXPLAINSELECT*FROM`user`WHEREusername='cxj1000000'ANDcompany='com1000000';发现所有的索引都没有了。接下来，再执行几条sql：EXPLAINSELECT*FROM`user`WHEREjobno='jn1000000'ANDcompany='com1000000';EXPLAINSELECT*FROM`user`WHEREcompany='com1000000';EXPLAINSELECT*FROM`user`WHERE作业号='jn1000000';发现没有索引。结论：最左匹配原则要求查询SQL语句必须包含最左字段。在username、jobno、company的组合索引中，username是最左边的字段，所以查询sql语句中的where条件必须包含包含username字段，而不管sql语句中username的使用顺序。索引列上有计算，根据主键ID查询。毫无疑问会用到主键索引，但是如果像下面这样：EXPLAINSELECT*FROM`user`WHEREid+1=2，可以看出没有用到索引。总结：如果索引列参与计算，则不会命中索引。这种情况下，可以换个等式，把运算放在等号右边，索引就命中了。EXPLAINSELECT*FROM`user`WHEREid=2-1查询条件withor在上面提到的最左匹配原则中，只要查询语句中包含username，就会使用索引，但是如果我们将and条件替换为or，即：SELECT*FROM`user`WHEREusername='cxj13'ORjobno='jn13'可以看到没有使用索引，而是扫描全表，所以在带or的查询语句中，索引会是除非所有条件都满足索引，否则无效。也就是说，username是有索引的，jobno也必须建立索引才能生效。like查询在username字段上新建索引user_idx_normal_username，不使用%模糊查询：SELECT*FROM`user`WHEREusernameLIKE'cxj'使用前面%：使用post%：前后使用%：结论：在模糊查询，只要使用%就不会使用索引，不使用%符号就可以使用索引。不同的字段类型仍然使用用户名来查询。用户名字段类型为字符串类型。我们知道下面的语句：SELECT*FROM`user`WHEREusername='cxj13'肯定会去索引，但是如果我们不把它用引号括起来，而是：SELECT*FROM`user`WHEREusername=100会找到那就是没有索引：结论：字符串的索引字段在查询的时候需要用引号括起来，否则索引失败。比较特殊的是查询语句中包含in。是否使用索引不是绝对的，与查询的数据量占全表数据量的比例有关。我们创建一个新表：CREATETABLE`test`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`name`varchar(50)DEFAULTNULL,`dept`bigint(20)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8然后往这张表中插入10条数据：先执行如下语句：SELECT*FROMtestWHEREidIN(1)执行结果：可以看到索引没有了，你需要注意的是类型。我们知道type代表的是要取的索引的一个效率值。它的结果顺序是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。一般来说，至少要达到射程级别。当type=index或ALL时，表示效率低，需要优化。我们看到，当只查询一条记录时，不仅使用了索引，还使用了type=const，效率更高。我们来扩大范围：SELECT*FROMtestWHEREidIN(1,2,3)从执行结果看，索引也没有了，但是此时type=range，效率降低了。然后扩大范围：SELECT*FROMtestWHEREidIN(1,2,3,4,5)可以看到没有使用索引，type=ALL，全表扫描。结论：mysql优化器会根据查询的数据量来决定是使用索引还是全表扫描。Mysql选择了错误的索引。让我们构建一个演示表来说明这种情况：CREATETABLE`demo`(`id`int(11)NOTNULL,`a`int(11)NOTNULLdefault0,`b`int(11)NOTNULLdefault0,PRIMARYKEY(`id`),KEY`a`(`a`),KEY`b`(`b`))ENGINE=InnoDB;然后插入100w条数据，执行如下sql:select*fromdemowhere(abetween1000and2000)and(bbetween50000and100000)limit1看执行：可以看到索引a没有了，只有扫描了1001行，这正是我们需要的。但是如果我们加上排序，就变成了这样：SELECT*FROMdemoWHERE(aBETWEEN1000AND2000)AND(bBETWEEN50000AND100000)ORDERBYbLIMIT1再看执行：可以看到索引b是没了，而且扫描了5万多行数据，效率会明显下降。你为什么使用索引b？因为在多索引的情况下，mysql优化器一般会比较扫描的行数，是否需要临时表，是否需要排序等，作为判断选择索引的依据。在这个例子中，优化器看到排序是基于b的，认为使用b的效率更高，所以使用了索引b。实际上，我们应该使用索引。这种情况下可以使用forceindex强制使用索引a。SELECT*FROMdemoFORCEINDEX(a)WHERE(aBETWEEN1000AND2000)AND(bBETWEEN50000AND100000)ORDERBYbLIMIT1;可以看出查询使用了索引a，只扫描了1001行。
