【MYSQL优化之道摘抄】mysql常见的SQL优化方法_mysql慢sql优化五个原则

前言

无论对于开发人员、运维人员或者测试人员，数据库的优化都是绕不开的话题。而在数据库的优化中SQL的优化又是最为关键的一项。再牛逼的服务器硬件也扛不住百万次的oracle硬解析，再快的SSD硬盘和raid，碰到千万级数据的全表扫描也一样会趴窝。这就是SQL优化的重要性，但是也恰恰是最容易被忽视的。SQL优化虽然深邃，但是入门不难，这里整理了以前的一些MYSQL的优化笔记，遵循这些优化原则的话，至少能保证数据库不会被慢SQL跑死。

关于优化的一些原则

• 所有的优化，其本质都是在时间、空间、成本、可容忍度四个要素之间取得的一种平衡。
• 优化没有绝对的银弹，不存在100分效果的优化策略。总是要做取舍。
• 任何数据库的优化，都要从SQL的优化开始
• 80%的慢SQL问题都是因为：1、没有索引；2、索引不合理；3、有索引但是实际执行却没有走索引
• ORACLE一定要注意避免硬解析；MYSQL的硬解析足够快，但也要注意使用绑定变量。

关于MYSQL中的索引优化原则：

• 联合索引遵循最左侧原则，将where使用最频繁的列放在最左侧
• 联合索引的效率一般高于单列索引
• 字段使用函数，将不能使用索引
• 无引号导致全表扫描，无法使用索引
• 当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描
• 涉及到order by gourp by的语句，将where条件和order group的字段作为联合索引，排序方法要一致

MYSQL优化实践

not in子查询优化

在生产环境中应该尽量避免子查询，可以用left join表连接代替。
优化前：

select SQL_NO_CACHE count(*) from test1 where id not in (select id from test2);
not exists:
select SQL_NO_CACHE ount(*) from test1 where not exists (select * from test2 where test2.id=test1.id);
1
2
3

优化后：

select SQL_NO_CACHE count(*) from test1 left join test2 on test1.id=test2.id where test2.id is not null;
1

通过子查询删除已查询的记录时会报错：1093，例如

mysql > delete from t where id in (select id from t where id <5);
1

执行后，会报错error 1093,you can`t specify taret table ‘t’ fo update in from clause，需要修改为：

mysql > delete from t where id in (select * from (select id from t where id < 5) tmp);
1

继续优化为表连接方式：

mysql> delete t from t join (select id from t where id < 5) tmp on t.id=tmp.id
1

模式匹配 like '%xxx%'优化

mysql里 like 'xxx%'可以用到索引，但是like '%xxx%'会走全表扫描。解决方法：覆盖索引，也就是select的字段正好就是索引
优化前：

select count(*) from artist where name like '%Queen%'
1

优化后：

select count(*) from artist a join (select aritst_id from artist where name like '%Queen%') b  on a.artist_id = b.artist_id
1

其中aritst_id是主键索引

limit 分页优化

优化前：

mysql > select sql_no_cache * frm test1 order by id limit 99999,10
1

SQL相当于执行了全表扫描，要先定位到99999，再扫描出后10行。优化的方法其实很简单，首先通过id定位到99999行，然后再执行limit：

select sql_no_cache * frm test1 where id > 99999 order by id limit 10;
1

关于这种优化一定注意和代码的协同，大多数开发人员会在代码中把d > 99999作为入参传递给JDBC，但是也见过直接在底层的DAO层写死了固定条件，这样的话排查问题的时候容易被忽视，从而导致不必要的工作量。

count(*)优化

老生常谈的优化。我倾向于对于count(*)，首先通过缓存优化，如果要求强一致性，可以考虑做二级缓存自动更新数据。这里只考虑SQL层面的优化方法。
通过辅助索引的方式，这种方式其实是增加了一个永远为TRUE的where条件，但是执行效率却是天上地下。同时这个方法需要注意索引的额外开销。

-- 优化前
select count(*) from up_user;
-- 优化后
select count(*) from up_user where sid >=0;
1
2
3
4

通过distinct的方式，但是有局限性，有可能distinct本身就会成为一个瓶颈

--优化前： 
select count(disinct(k)) from sbtest;
-- 优化后：
select count(*) from (select distinck k from sbtest) tmp;
1
2
3
4

类似count sum的函数尽量不要在主库上执行，生产环境用的是InnoDB引擎，不想MyISAM引擎（OLAP)内置了计数器

OR 条件优化

使用OR的SQL语句都不会走索引。可以用union all合并or两端的结果集，这种做法还一个好处是他属于相同语义转换。优化前后的返回结果一定是一致的。

-- 优化前：
select * from user where name='d' or age=41;
-- 优化后：
select * from user where name='d' union all select * from user where age=41;
1
2
3
4

高效解决主键冲突

mysql中主键冲突带来的性能损耗容易被忽视。其实处理也简单，on DUPLICATE KEY UPDATE即可，但是要注意这个是MYSQL才有的语法，如果涉及到多数据库支持的话，需要在代码上考虑对数据库类型的兼容性

insert into up_relation(ownerid,contactidisbuddy,ischatfriend,isblacklist) values (value1,value2,1,0,0)
on DUPLICATE KEY UPDATE isBuddy=1,IschatFriend=0
1
2

避免不要必要的排序

在count,sum等聚合操作中去掉无用的order by

-- 优化前：
select * from  (
select a.id,a.title,a.content,b.log_time,b.name from a,b 
      where a.content like 'rc_%' and a.id = b.id order by a.title desc
) as rs_table limit 0,30
1
2
3
4
5

像这种子查询里的order by就完全没有意义，但是我经常在生产环境的监控中看到这种SQL，一方面是有开发同学的粗心大意和复制粘贴大法，另外也有DAO框架生成SQL不合理的原因。

-- 优化后：
select a.id,a.title,a.content,b.log_time,b.name from a join b on a.id = b.id and a.content like 'rc_%' order by a.title desc liit 0,30
1
2

用where 字句替换having子句

having只会在检索出所有记录后才对结果集进行过滤，一般情况下，having子句中的条件用于对一些集合函数的比较，如count等，除此以外，都应该写在where子句中