Mysql索引优化实战

这里给大家补充一个概念，索引下推（IndexConditionPushdown，ICP）,likeKK%其实就是用到了索引下推优化什么是索引下推了？对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT*FROMemployeesWHEREnamelike’LiLei%'ANDage=22ANDposition='manager’这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是’LiLei’开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是’LiLei’开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。为什么范围查找Mysql没有用索引下推优化？估计应该是Mysql认为范围查找过滤的结果集过大，likeKK%在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给likeKK%用了索引下推优化，当然这也不是绝对的，有时likeKK%也不一定就会走索引下推

我们先看单路排序的详细过程：1.从索引name找到第一个满足name=‘zhuge’条件的主键id2.根据主键id取出整行，取出所有字段的值，存入sort_buffer中3.从索引name找到下一个满足name=‘zhuge’条件的主键id4.重复步骤2、3直到不满足name=‘zhuge’5.对sort_buffer中的数据按照字段position进行排序6.返回结果给客户端我们再看下双路排序的详细过程：1.从索引name找到第一个满足name=‘zhuge’的主键id2.根据主键id取出整行，把排序字段position和主键id这两个字段放到sortbuffer中3.从索引name取下一个满足name=‘zhuge’记录的主键id4.重复3、4直到不满足name=‘zhuge’5.对sort_buffer中的字段position和主键id按照字段position进行排序6.遍历排序好的id和字段position，按照id的值回到原表中取出所有字段的值返回给客户端其实对比两个排序模式，单路排序会把所有需要查询的字段都放到sortbuffer中，而双路排序只会把主键和需要排序的字段放到sortbuffer中进行排序，然后再通过主键回到原表查询需要的字段。如果MySQL排序内存sort_buffer配置的比较小并且没有条件继续增加了，可以适当把max_length_for_sort_data配置小点，让优化器选择使用双路排序算法，可以在sort_buffer中一次排序更多的行，只是需要再根据主键回到原表取数据。如果MySQL排序内存有条件可以配置比较大，可以适当增大max_length_for_sort_data的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到sort_buffer中，这样排序后就会直接从内存里返回查

索引设计原则1、代码先行，索引后上不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。2、联合索引尽量覆盖条件比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、orderby、groupby的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。3、不要在小基数字段上建立索引索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。4、长字符串我们可以采用前缀索引尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于KEYindex(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是orderbyname，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的，groupby也是同理。所以这里大家要对前缀索引有一个了解。5、where与orderby冲突时优先where在where和orderby出现索引设计冲突时，到底是针对where去设计索引，还是针对orderby设计索引？到底是让where去用上索引，还是让orderby用上索引?