MySQL查询优化

查询优化常用策略

创新互联从2013年开始，公司以网站建设、做网站、系统开发、网络推广、文化传媒、企业宣传、平面广告设计等为主要业务，适用行业近百种。服务企业客户上千余家，涉及国内多个省份客户。拥有多年网站建设开发经验。为企业提供专业的网站建设、创意设计、宣传推广等服务。 通过专业的设计、独特的风格，为不同客户提供各种风格的特色服务。

  1、优化数据访问：应用程序应该减少对数据库的数据访问，数据库应该减少实际扫描的记录数

     例如，redis缓存，避免"select * from table"

  2、重写SQL

     对于需要进行大量数据的操作，可以分批执行，以减少对生产系统的影响，从而缓解复制超时

MySQL join 严重降低了并发性，应该尽量连接太多的表，建议在应用层实现部分的连接功能

  3、重新设计库表

      在没有其他的优化办法下，可以考虑更改表结构设计，增加缓存表，暂存统计数据，或者增加冗余列，以减少连接

  4、索引

     索引能解决80%的问题

优化器介绍

  优化器的不足

   1、数据的统计信息可能是错误的

   2、CPU，内存、数据是否在缓存，都会影响优化器

   3、优化器不会考虑并发的情况，资源的郑永可能会导致性能问题

   提示：

   1、使用索引

   select * from table1 use index(col1_index,col2_index) where col1=1 and col2=2 and col3=3;

   2、不使用索引

      select * from table1 ignore index(col3_index) where col1=1 and col2=2 and col3=3;;

   3、强制使用索引

     select * from table1 force index(col3_index) where col1=1 and col2=2 and col3=3;;

    注意：use index,ignore index,force index 只会影响MySQL表中检索记录和连接要使用的索引，不影响order by或group by或group by

   4、不使用查询缓存

     SQL_NO_CACHE

   5、使用查询缓存  explicit_mode,query_cache_type=2 ，指明SQL需要缓存，才缓存

    SQL_CACHE

  6 、Straight_join

     按照FROM字句描述的表的顺序进行连接

MySQL的连接机制

   Nested Loop join

   MySQL优化器一般会选择小表来做驱动表（外部表）

各种语句的优化

连接的优化

 1、连接的表不要超过4个

 2、ON，using子句的列要有索引

 3、最好能转换为inner join，left join的成本比inner join高很多

 4、explain检查连接，如果输出的rows列太高，考虑索引或连接表顺序是否不当

 5、反范式设计

group by、distinct、order by语句优化

1、尽量对较少的行进行排序

2、连接了多张表，order by 的列应该属于连接顺序的第一张表

3、 利用索引排序

4、group by,order by的列尽量是第一表中的列，如果不是，考虑冗余列

5、保证索引列和order by的列相同，而且按相同的方向进行排序

6、增加sort_rnd_buffer_size

7、改变tempdir变量指向基于内存的文件系统或者其他更快的磁盘

8、指定Order by null

  默认情况下MySQL将排序所有的Group by的查询，如果要避免排序结果，可以指定Order by null;

9、优化Group by with rollup

   考虑在应用层实现

10、使用非group by的列来替代group by的列

   比如 group by x,y，如果group by z 能到到相同的结果，则尽量少出现group by

11、考虑用Sphinx替代 group by语句

优化子查询

   大多数情况下，连接会比子查询快，子查询生成的临时表没有索引

   select distinct col1 from t1 where col1 in (select col1 from t2);

   改写为：

   select distinct t1.col1 from t1,t2 where t1.col1=t2.col1;

select * from t1 where id not in (select id from t2);

改写：

select * from t1 where not exists (select id from t2 where t1.id=t2.id) 

也可以改写为：

select table1.* from table1 left join table2 on table1.id=table2.id where table2.id is null;

把子句从子查询的外部转移到内部

select * from t1 where s1 in (select s1 from t1) or s1 in (select s1 from t2);

改写

select * from t1 where s1 in (select s1 from t1 union all select s1 from s2);

select (select column1 from t1) +5 from t2;

改写

select (select column1+5 from t1) from t2;

优化limit字句

优化 IN

优化Union

优化带有BLOB、Text类型字段的查询

filesort的优化

优化SQL_CALC_FOUND_ROWS

优化临时表

OLAP业务优化