mysql sql优化心得

1、尽量避免使用子查询

例如：
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE name=‘wyt’);
子查询在MySQL5.5版本里，内部执行计划器是这样执行的:
先查外表再匹配内表，而不是我们认为的先查出整个内表t2，作为临时表给外表使用

mysql在5.6版本里面做了优化，在执行sql优化器，优化成 join
SELECT * FROM t1 LEFT JOIN t2 on t1.id=t2.id where t2. name='wyt;

mysql对子查询优化技术
a semi-join(5.6)：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做join/semi-join的操作。关键词是“上拉”。
b Materialization：物化子查询，子查询的结果通常缓存在内存或临时表中
c EXISTS strategy：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词是“下推”
具体规则
一定条件会优化成半连接
如果不满足优化器会（采用EXISTS策略优化子查询）
1） 子查询必须是一个简单子查询，不能包括：UNION/GROUP BY/HAVING/聚集函数。如果包含有ORDER BY则不可以带有LIMIT子句。
2）子查询位于WHERE/JOIN-ON子句中，且首层不存在OR/NOT操作（即首层的条件子句中只能是AND操作符连接的表达式。如果与OR操作在同层的子查询不可以被半连接优化，但可以被“物化策略”优化）
3）如果子查询的目标列，使用了主键或唯一键，进一步优化为内连接
4）不可以是not in
5) 不是UPDATE/DELETE命令（在UPDATE/DELETE命令子查询不被半连接优化）。

2、用IN来替换OR,连续的数值，能用 between 就不要用 in 了

mysql 对IN列表中的数值进行排序。
对于查询的匹配，每次使用二分查找去匹配IN列表的数值。
所以对于第[2]步，每次比较的算法复杂度大概为O(log n)。相反，对于同样逻辑的OR列表，每次都要遍历，所以OR相应的算法复杂度为O(n)(因此对于遍历非常大的OR列表，会很缓慢！)。
也可以用UNION,来使用索引提高性能
总结
1)尽量将能使用IN来代替OR查询。
2)对IN列表中的数据，写SQL的时候排好序，避免MySQL来做这个工作。

3、like左匹配

B+树的节点存储索引顺序是从左向右存储，在检索匹配的时候也要满足自左向右匹配。

4、分组统计可以禁止排序sort

5、千万级的分页查询

select * from test1 where id>=(select id from test1 limit 9000000,1) limit 10

6、不要让数据类型出现隐式转化

1）当操作符左右两边的数据类型不一致时，会发生隐式转换。
2）当where查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3）当where查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
4）字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

select * from t where str=100;
这样就会失去索引，
查阅相关资料发现规则如下：
不以数字开头的字符串都将转换为0。如’abc’、‘a123bc’、'abc123’都会转化为0；
以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如’123abc’会转换为123，'012abc’会转换为012也就是12，'5.3a66b78c’会转换为5.3，其他同理。
所以发生隐式转换 str 可以是 0100 a100 100a等都能匹配到，所以会失去索引效果
反过来
select * from t where num=“100”;
这样就不会 num 在左边，右边可以是 字符串 a100可以匹配，索引不会失效

7、order by关键字排序优化

1）无过滤不索引
EXPLAIN select * from old_house_master where dic_id=16712 ORDER BY house_id;

EXPLAIN select * from old_house_master where dic_id=16712 ORDER BY house_id;

EXPLAIN select * from old_house_master ORDER BY house_id ;

using filesort说明进行了手工排序。原因在于没有where 作为过滤条件！

EXPLAIN select * from old_house_master ORDER BY house_id limit 10;

2） 顺序错，必排序

EXPLAIN select * from old_house_master ORDER BY company_type,origin_house_id limit 10 ;

EXPLAIN select * from old_house_master ORDER BY origin_house_id,company_type limit 10 ;

3） 方向反，必排序
就是order by后面需要2字段一样，需要排序一直，都是倒叙或者都是正序

4）mysql根据索引规则有的时候回选错索引，需要们强制使用哪个索引（force index）

5）filesort
排序出现filesort的优化，
using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。
此时，可以进行的优化：
1、修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。
2、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。
3、减少select后面的查询的字段
4、参数优化。sort_buffer_size，max_length_for_sort_data_size
mysql 会从 sort_buffer取数据，如果sort_buffer 的容量不够，导致每次只能取 sort_buffer 容量大小的数据，所以可以优化sort_buffer_size来提高 filesot
mysql双路排序,和单路排序2种，mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。所以可以通过max_length_for_sort_data_size来优化

8、Group By优化

group by 实质是：先排序后进行分组
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了
尽量不要使用 distinct 关键字去重，可以通过 group来优化，可以使用到索引

9、索引的失效情况

1.有or必全有索引（ 语句里面如果带有or条件, myisam表能用到索引， innodb不行）;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）

10、mysql索引优化合并

MySQL在 5.0版本中引入新特性：索引合并优化(Index merge optimization)，当查询中单张表可以使用多个索引时，同时扫描多个索引并将扫描结果进行合并。

以SELECT * FROM TB1 WHERE c1=“xxx” AND c2="“xxx” 为例：
当c1列或c2列选择性较差且统计信息不准时，比如整表数据量2000万，按照c2列条件返回1500万数据，按照c1列返回1000条数据，此时按照c2列条件进行索引扫描+聚集索引查找的操作成本极高(可能是整表扫描的百倍消耗)，对1000条数据和1500万数据求交集的成本也极高，最终导致整条SQL需要消耗大量CPU和IO资源且相应时间超长，而如果值使用c1列的索引，查询消耗资源较少且性能较高。
所以不建议不开启Index merge，所有不要用or，用union 代替