MySQL优化实战

        联合索引第一个字段用范围查询不会走索引，MySQL判断第一个字段就用范围查询，结果集可能太大，回表的效率不高还不如全表扫描，即使force index 强制使用索引，效率可能也不高。

        n和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

        索引下推：使用联合索引扫描时，where字句第一个字段检索条件是like 'cc%'时，因最左前缀原则导致只用到了联合索引的第一个字段，where子句的其它条件需要回表返回结果之后再进行过滤，索引下推则是在扫描联合索引的同时根据索引内其它字段做过滤掉不符合条件的记录后再去回表查数据，可以有效的减少回表的次数。

        为什么范围查找Mysql没有用索引下推优化？ 可能Mysql认为范围查找过滤的结果集过大，like 'cc%' 过滤后的结果集相对可能会比较小，所以这里Mysql选择给 like 'cc%' 用了索引下推优化，这也不是绝对的，有时like KK% 也不一定就会走索引下推。

4.order by满足最左前列条件时才会使用index排序，否则使用filesort排序，filesort文件排序方式有：

         单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

        双路排序（回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行ID，然后在 sort buffer 中进行排序，排序完后需要回表获取其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

MySQL通过比较系统变量 max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。 如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式；如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式，如果MySQL的sort_buffer比较小且没有条件扩容可以适当将max_length_for_sort_data调小使MySQL用双路排序模式来排序，一次排序sort_buffer可以放更多行。

5分页查询优化：在实际业务中经常会通过‘select * from g_temp limit 10000,30’这样的语句去获取第10000行开始的30条数据，看似只查询了30条数据，实际上MySQL是先查询处10030条数据，再把前10000条数据抛弃，然后读后面30条。因此要查询一张大表比较靠后的数据时，性能会很差。

通常有以下优化手段：

        使用自增且连续的主键分页查询，例如‘select * from g_temp where id > 10000 limit 30’ ,因为主键id是连续递增的，所以可以改写成从主键第10000开始查30条，这样改写后的sql可以走主键索引，所以效率好很多，但是这种方法只适用于小部分场景，如果中间删除了记录导致递增id空缺结果就会不一致,且该方法只适用与按主键排序，如果order by其它字段也会结果不一致。

        排序时查尽量少的字段，再通过排序好的结果去查需要的字段 ‘select * from g_temp t inner join (select id from g_temp order by name limit 10000,30) tt on tt.id = t.id’使排序时可以适用索引。

        join查询优化，mysql常见的关联查询算法有以下两种：

嵌套循环连接（NLJ）：一行一行的循环第一张表（驱动表）读取行，在这行中取到关联字段，根据关联字段去另一张表（被驱动表）里取出符合条件的行，然后取出两张表结果的合集。

基于块的嵌套循环连接(BNL)：把驱动表的数据读取到join_buffer中,然后扫描被驱动表，把被驱动表每一行取出来和join_buffer中的数据比对，当join_buffer的容量不足以容纳驱动表的所有数据时（join_buffer_size设定大小），会通过分段的方式分批次的把驱动表数据放到join_buffer中去和被驱动表的记录比对。

        join查询优化手段主要有：对关联字段加索引，尽量让mysql适用NLJ算法，小表驱动大表，写多表联合查询时如果明确知道哪张表是小表，可以用straight_jion写法固定驱动连接方式，省去mysql判断大小表的时间（straight_jion只适用与inner join，因为left right join已经指定了表的执行顺序）。

        in 和 exist优化：小表驱动大表，即小的数据集驱动大的数据集,'select * from A in (select ID form B)' 当A的数据集大于B时，in要优与exist，'select * from A exist (select 1 from B where B.ID = A.ID)',当A的数据集小于B时，exist要优与in。

        count优化：有索引时count(*)~count(1)>count(字段)>count(主键索引),二级索引比主键索引存的字段少，索引count(字段)>count(主键索引)，没有索引时count(*)~count(1)>count(主键)>count(索引)，字段没有索引需要全表扫描。mysql对count(*)做了优化，并不是取所以字段，而是按行累计，效率很好，对于count(id)，mysl最终会选择二级索引而不是主键聚集索引，因为二级索引并不是存储所有字段，查询效率会更高。