mysql语句调优

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

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

  1. 设计阶段
  2. 索引命中
  3. 通用最佳实践写法

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. 通用最佳实践写法

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

  1. 按需拿取。完全避免 select * 这种写法，需要多少字段拿多少。
  2. 最强过滤条件前置。 例如 depart_id = ? and age > 30 , 很明显 depart_id这种字段能过滤大部分数据，应该前置，帮助查询分析器减少扫描范围。
  3. 唯一返回则加limit 1. 如果确定查询只会返回一条记录，则加上 limit 1会高效很多。
  4. 对于in子句，应该用exists子句替代。
  5. join 中的关联字段应该保证它们类型一致，同时都加上索引。
  6. 尽量让查询分析器缓存你的SQL语句。我们知道，一个SQL执行次数比较多话，执行引擎会自动缓存它，但是如果SQL中使用了curdate()这样的函数，则无法缓存，应该去掉函数，由外部传入参数。

关于SQL优化的话题实在太多太多，先写这么些以后慢慢加吧，我觉得这样分类梳理以后更加清晰，不像某些大牛的一下子给你100个招式，看完以后一个也记不住。