Mysql语句及索引优化浅谈

1、尽量避免使用子查询
Mysql5.5对于子查询会先查外表再匹配内表，当外表数据量大时，查询速度会很慢；
MariaDB10/Mysql5.6采用join方式进行优化，但优化只针对select，对update、delete子查询无效。join连接效率高的原因是mysql不需要在内存中创建临时表。

2、用IN来替换OR
mysql对in做了优化，将in中的常量全部存储在一个数组里面，而且数组是排好序的，但是如果数值较多，产生的消耗也较大，对于连续的数值，能用between就不要用in了，或者使用连接来替换。

3、读取适当的记录LIMIT M,N，而不要读多余的记录
对于limit m,n的分页查询，m越大sql的耗时越来越大，对于这种应先取出主键id，然后通过主键id跟原表进行join关联查询。mysql并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，当offset特别大的时候效率就非常低，要么控制返回的总页数，要么对超出特定阈值的页数进行SQL改写。优化方法如下，可以取前一页最大行数的id，然后根据最大id来限制下一页的起点：
select id,name from table_name where id> 866612 limit 20

4、禁止不必要的Order By排序
对结果没有排序要求，尽量少用排序；
排序字段没有用到索引，尽量少用排序；
分组统计查询时可以禁止其默认排序，通过order by null禁止排序：
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL

5、总和查询可以禁止排重用union all
union和union all的差异，主要是前者需要将结果集合并后，再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU计算，加大资源消耗及延迟。union all的前提条件是两个结果集没有重复数据。

6、将多次插入换成批量Insert插入
insert into t1(id,name) values(‘1’,‘aaa’),(‘2’,‘bbb’);

7、只返回必要的列，用具体的字段列表代替 select * 语句
两者在数据库表中需要访问的数据量其实是一样的，但是如果查询的字段都在索引中，也就是覆盖索引，name可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少IO操作，当存在order by操作的时候，select子句中的字段多少会在很大程度上影响到我们的排序效率。

8、区分in和exists
区分in和exists主要是造成了驱动顺序的改变，如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询。所以in适用于外表大而内表小的情况，exists适用于外表小而内表大的情况。在有确定且有限的集合时，使用in。

9、优化Group By语句
如果对group by语句的结果没有排序要求，要在语句后面加order by null，group默认会排序；
尽量让group by过程用上表的索引，确认方法是explain结果里没有using temporary和using filesort；
如果group by需要统计的数据量不大，尽量只是用内存临时表，也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
如果数据量大，是用SQL_BIG_RESULT这个提示，让优化器直接使用排序算法得到group by结果。

10、尽量使用数字型字段
引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就足以。