MySQL优化之索引优化

为查询使用索引

频繁作为查找条件的字段应该建索引。

频繁更新的字段不应该建索引，因为更新数据的同时，还需要更新索引，效率低。

单表索引优化：不要在需要范围查找的字段上建索引，会导致索引失效。

两表索引优化：左连接在右表的连接字段上建立索引；右连接在左表的连接字段上建立索引。

join优化：
尽可能减少join语句中NestedLoop（嵌套循环）的循环总次数。
用小的结果集驱动大的结果集。
优先优化NestedLoop的内层循环。
保证join语句中被驱动表上join条件字段已经被索引。
当无法保证被驱动表的join条件字段被索引且内存资源充足的情况下，不要太吝啬joinBuffer的设置。

一般性建议：
对于单建索引，尽量选择针对当前查询过滤性更好的字段。
在选择组合索引时，当前查询中过滤性越好的字段，位置应该越靠左。
在选择组合索引时，尽量选择能够包含当前查询条件中更多的字段。
尽可能通过分析统计信息和调整查询语句的写法来达到选择合适索引的目的。

为排序使用索引

order by 满足以下两种情况，会使用索引方式排序：
1.order by 语句使用索引最左前列。
2.使用where子句与order by 子句条件组合满足索引最左前列匹配。

filesort的2种排序算法：

1.双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。双路排序的过程：先读取数据行指针和order by 列，对他们进行排序，然后扫描排好序的表，按照列表中的值重新从列表中读取对应的数据输出。简言之，从磁盘读取排序字段，在内存缓冲区中进行排序，再从磁盘读取其它字段。

2.单路排序：MySQL4.1之后，出现的改进文件排序算法。从磁盘读取查询需要的所有列，按照order by 列在内存缓冲区中对它们进行排序，然后扫描排序后的列表进行输出，比双路排序少了一次到磁盘中读取数据的步骤，效率更高了，但是单路排序会使用更多的内存空间，因为它把每一行要查询的数据都保存在内存中了。所以为了节约内存空间，使用order by时，尽量不要使用select * 。

使用order by时，select * 是大忌，要只查询需要的字段。原因：当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT\BLOB类型时，会用改进后的算法：单路排序，否则用老算法：多路排序。效率会下降。