MYSQL查询优化及一些常见问题

MYSQL优化方法

1.慢查询基础：优化数据访问

1.1确认是否检索了大量超过需要的数据；
如：查询不需要的记录，可添加limit；
        多表关联时返回全部列；
总是取出全部列：这样会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存及CPU消耗；
重复查询相同的数据；
1.2确认是扫描了额外的记录
最简单的衡量查询开销的三个指标：响应时间，扫描行数，返回行数；
响应时间分为服务时间（真正执行的时间）和排队时间（I/O操作或者等待行锁等）；
        可通过以下语句查看整个查询执行所消耗的时间分布： 
        set profiling = 1;//开启查询剖析工具
        select * from fss_financial_losses_detail where payer_member_id='1001'  ORDER BY losses_fee_type limit 10;
        SHOW PROFILES;

        SHOW PROFILE for QUERY 191;

可通过explain查看扫描行数及访问类型：全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用等；

        DRDS表的执行计划查询用以下语句;

        EXPLAIN EXECUTE select *……;

2.重构查询的方式

2.1一个复杂查询还是多个简单查询；慢SQL可拆分查询；
2.2切分查询
如：批量删除老数据，一次性删除可能会一次锁住很多数据、占满整个事务日志、消耗系统资源、阻塞很多小的但重要的查询，切分多个后减小影响；
2.3分解关联查询
如：代码层对费用类型的转换，先查出挂账数据，在查出费用类型数据字典，在代码层进行匹配；
减少锁竞争，查询本身效率也会提升，可以减少冗余记录的查询；

3.MYSQL执行查询基础

3.1客户端将查询发送到服务器；
3.2服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
  　  3.3服务器解析，预处理和优化查询，生成执行计划。
　　3.4执行引擎调用存储引擎API执行查询。
　　3.5服务器将结果发送回客户端。

4.MySql优化器优化类型

对联接中的表重新排序
代数等价法则
将外连接转换成内连接 
优化COUNT()、MIN()、MAX()：查找某列最大/最小值，该列又有索引，查找最大值，则会直接找最后一行；最小值，则直接找第一行。因为索引已经排好序了
覆盖索引：覆盖索引简单的说就是索引上包含了该列的数据，如果是组合索引的话，就包含多列
等值查询
子查询优化

早期终结等

 用下面的语句可以分析MYSQL优化器优化的优化结果
EXPLAIN EXTENDED select * from fss_pay_balance_detail where 1=1 and lossess_detail_id='0000086ecde745ffbbfd357758b8bd48';
SHOW WARNINGS;

常见问题及注意事项

1.union 查询注意事项
  MYSQL无法将限制条件从外层下推到内层，这使得原本能部分返回结果的条件无法应用到内层查询的优化上；如limit；需要在内层也加上limit；可以减少临时表存取的数据量；
  可以用union all 时不使用union；
2.索引合并优化：
  在合适的列创建索引（where从句，group by 从句，order by 从句中出现的列）
  索引字段越小越好
  离散度打的列放在联合索引的前面
  要避免重复冗余的索引
3.count查询注意事项
  count作用：统计某个列值的数量（不统计NULL）也可以统计行数；常会犯一个错误在count()中添加一列名却希望统计出结果集的行数；
  在数据库中查询总条数时注意去掉排序及不必要字段的查询；
  避免不必要的嵌套查询；（java代码生成的count会）（有些函数列作为查询条件的 可以直接将函数写在查询条件中而不是嵌套查询再加条件）
  子查询字段用聚合函数，且符合条件数据为零时，主查询再查count时结果会是1而不是0；
  select count(1) from (select dict_id,SUM(dict_item_id),count(*) from fam_data_dict WHERE dict_id='12345' GROUP BY dict_id) as A;
  select count(1) from (select dict_id,SUM(dict_item_id),count(*) from fam_data_dict WHERE dict_id='12345') as A;
4.关联查询优化：
  确保ON或者USING子句中的列上有索引；
  确保任何的GROUP BY 和ORDER BY 中的表达式尽量只涉及到一个表中的列，这样使用索引的可能性更大；
  尽量小表驱动大表查询；
5.limit分页优化：
分页一般都采用limit加偏移量的方式实现，但当偏移量非常大的时候入limit 10000,20 此时需要查出来10020条数据却只返回后20条数据，这时的代价是很大的；此时
select * from A order by col1 limit 50,5;
改写为：
select * from A INNER JOIN (select id from A order by col1 limit 50,5) as t1 using(id);
应尽可能采用索引覆盖扫描而不是查所有的列；
6.varchar类型的字段存储数字时排序会出现问题
select * from fam_data_dict where dict_id='pay_mode' ORDER BY dict_item_id;
select * from fam_data_dict where dict_id='pay_mode' ORDER BY CAST(dict_item_id AS SIGNED);
select * from fam_data_dict where dict_id='pay_mode' ORDER BY CONVERT(dict_item_id AS SIGNED);
7.大小写问题
注意mysql查询不区分大小写，但是JAVA代码equles区分，故在校验时注意此处；
8.特殊字符的模糊查询问题
全模糊查询时使用内部函数instr，可代替传统的like方式查询，并且速度更快；instr是内置函数，一般情况下比标准写法LIKE高效，但在表中，索引字段不可以使用函数，否则会使索引失效。
select * from fss_arap_opening having instr(vip_id,"100001%");
select * from fss_arap_opening where vip_id like '100001\%%';