MySQL语句优化

1.主要想办法如何使用到索引：

2.orderBy排序，应该尽量走索引（左前缀原则已经自动排好序）；而不是filesort；如果是fileSort,应该尽量调高sorted_buffer的内存大小，让其尽量在内存中排序。filesort又分为全字段和row_id排序。

 

3.join优化

t1是小表，t2是大表

（1）join，小表驱动大表（1.走索引，起决定因素的是驱动表行数N， 2.被驱动表不走索引join_buffer_size可以放更多，），被驱动表走索引；

（2）使用BKA算法，为了回表的时候顺序读

（3）BNLJ会导致bufferpool里面的LRU数据中冷数据区域被这个冷表占据，会导致其他命中率下降。（引申一个问题就是，新开发一个功能，使用了连表语句，结果导致了其他访问数据的能力下降）

（4）创建一个临时表，把t2中满足的数据放在临时表(临时表是为了减少全表扫描的次数)

（5）到业务端进行Join

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

 

3.group by 优化

（1）应该让分组的那个字段尽量走索引，这样在扫描的过程中就自动统计好了，也不需要排序，用到临时表了

（2）不适合优化，且数据量很大的情况下

  在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

4.=，is null,函数，覆盖索引，%开头，不满足左前缀