首页 > 数据库 > mysql > mysql语句调优
2015
08-12

mysql语句调优

最近在整理一些关于MySQL调优的事情,网络上条条框框的很多,有的内容说的很多,有的说得非常的零碎,说得都对,但总是感觉就是看过就忘记了,这些东西没有系统的分类整理,或者串起来,我按照自己的理解,稍微梳理一下,看看这样子看起来是不是会更有条理一些。

对于SQL的调优,我觉得大概可以从三个方面思考:

设计阶段
索引命中
通用最佳实践写法
1. 设计阶段
表结构的设计其实已经很大程度上决定了程序上SQL编写以及执行性能。所以这个阶段其实很重要。这这里我们只要记住两条原则:
1. 列越小速度越快
2. 表数据越小速度越快

针对列越小原则,我们的很多观念以及习惯是需要纠正一下的。在我们的数据库设计当中,最常见的一些字段如“type,status”等,这些字段都是参数性质的,常常会放到查询条件中来匹配过滤,一般的做法就是定义为int类型。这个实际上造成了很大的浪费,这种参数化字段往往用tinyint或者ENUM就足够描述了,如果严格按照这个原则去设计库表,会节省很多空间,针对这些字段的查询效率也会高很多。

至于表数据越小原则,这里我们要用到垂直拆分。来思考一个应用场景,User表中有一个Address系列的字段,这系列字段存储的都是中文,数据量颇为可观,而且这些字段不是特别重要,只有在用户详情页面需要展示一下地址信息,这里我们就可以可以考虑把Address系列字段拆分到一个独立的表,这样可以大大减少User表的数据量,数据量一小,针对User表相应的查询效率也会提升。

在设计阶段,还有一些原则也是需要遵行的,一般来说我们应该为每个表建立int类型的自增长主键,而不采用像mobile,email这样的业务标示作为主键。

2. 索引命中
索引命中的问题分成两部分来看,我们其实希望尽可能的命中索引,同时希望尽可能的避免使索引失效,这两句话看起来是同一个意思,但是实际上却是两个方向。
1. 尽可能命中索引
2. 尽可能避免使索引失效

对于命中索引这个问题,我们首先要了解索引命中的一个基本原则:最左前缀原则,假设我们在建立了一个复合索引(a,b,c),那么下面这些情况是可以命中这一索引的:
where a = ‘xxxx’
where a = ‘xxxx’ and b = ‘yyyyy’
where a =’xxxx’ and b =’yyyy’ and c =’zzzz’
同时,这样是无法命中索引的:
where b =’yyyy’
where b=’xxxxx’ and c=’yyyy’

看明白了?对于索引的使用,我们要按照索引定义的字段的先后顺序来摆放,所以要特别留意索引字段的先后顺序。

对于避免索引失效,我们需要知道这样的情况是会导致索引无法命中而失效的,尽量要避免这样的一些写法:
1. 索引列上存在Null值,这样的话我们一般需要加 is not null
2. union, between…and , in , != 这样的操作都会导致索引无法命中
3. 索引列上使用了内置函数
4. 索引列上存在隐式类型转换,如字段是int类型,传入的参数是’1’这样的字符串

3. 通用最佳实践写法
这一部分的话基本上就是大家比较认可的一些高效写法了,死记硬背没有任何一样的,关键是要在实际使用中自律的遵循这些原则。

按需拿取。完全避免 select * 这种写法,需要多少字段拿多少。
最强过滤条件前置。 例如 depart_id = ? and age > 30 , 很明显 depart_id这种字段能过滤大部分数据,应该前置,帮助查询分析器减少扫描范围。
唯一返回则加limit 1. 如果确定查询只会返回一条记录,则加上 limit 1会高效很多。
对于in子句,应该用exists子句替代。
join 中的关联字段应该保证它们类型一致,同时都加上索引。
尽量让查询分析器缓存你的SQL语句。我们知道,一个SQL执行次数比较多话,执行引擎会自动缓存它,但是如果SQL中使用了curdate()这样的函数,则无法缓存,应该去掉函数,由外部传入参数。
关于SQL优化的话题实在太多太多,先写这么些以后慢慢加吧,我觉得这样分类梳理以后更加清晰,不像某些大牛的一下子给你100个招式,看完以后一个也记不住。

最后编辑:
作者:saunix
大型互联网公司linux系统运维攻城狮,专门担当消防员

留下一个回复