mysql优化

count 和 max 优化

• max 当只获取一个max的结果时，给参与max字段的函数建立索引，这样就是覆盖索引，结果完全可以从索引中获取，不需要扫描表。
• count count(*)会计空值，count(id)不会计空值。

子查询的优化

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。

group by 的优化

如果出现多表关联，最好选用同一表中的列进行 group by，否则容易出现使用临时表，就会增加IO操作，影响效率。

limit 优化

limit 常用于分页处理，时常会伴随 order by 从句使用，因此大多时候会使用 filesorts，这样会造成大量 IO 的问题。

• 使用有索引的列或主键进行 order by 操作
• 记录上次返回的主键，在下次查询时使用主键过滤，避免出现数据量大时扫描过多的记录，缺点在于 id 必须是连续的，不断的。

索引优化

在合理的 SQL 下，索引是提升效率的关键。

如何选择合适的列建立索引

• 在 where 从句，group by 从句，order by 从句，on 从句中出现的列。
• 索引字段越小越好，因为索引存储是页为单位的，一页里存储的索引数据越多，效果越好。
• 离散度大的列放到联合索引的前面，唯一值越多，离散值越好。

当一个索引包括了查询中所有的列，这种索引为覆盖索引，效率最高，直接从索引返回数据。

索引的维护及优化

索引可以提高查询效率，但是会降低写入效率。而且，当出现重复及冗余索引时，查询时需要选择使用哪个索引，也会降低查询效率。

• 重复索引

  相同顺序建立的同类型索引

• 冗余索引

  多个索引的前缀列相同，歌者在联合索引中包含了主键的索引，InnoDB 的一个特性就是在所有的索引后面添加主键信息，所以人为加上主键，就变成了冗余索引。

• 清除不再使用的索引

  不再使用的索引要及时清除，不过要注意一主多从的表，要把所有主从表放在一起分析，因为有些在一个从上不使用，在其它从上还有使用，或者在主上不使用，在从上还在使用。

表结构优化

选择合适的数据类型

• 使用可以存下你的数据的最小的数据类型。比如时间即可以用 varchar，也可以使用日期时间型，也可以使用时间戳，也可以使用 int，显然 int 最小，时间戳也是可以的，在 MySQL 里时间戳与 int 是一样大小的。使用 int 来存储，使用时可以用 from_unixtime、unix_timestamp 这些函数进行转换。int 8 个字节，varchar 15 字节，这在大数据量存储下就非常可观了。
• 使用简单的数据类型。同样保存时间，int 要比 varchar 简单，也比时间戳简单。
• 尽可能使用 not null 定义字段，给出默认值。因为 InnoDB 的存储特性决定对非 not null 的字段，需要一些额外存储，同时会增加 IO 和存储的开销。
• 尽量少用 text 类型，非用不可时最好考虑分表。

反范式优化

我们现在设计的表，一般需要符合第三范式，即非关键字段对做任意候选字段的传递函数依赖，简单来说，就是减少冗余。 反范式优化就是为了查询效率，适当增加冗余，这样查询时多表关联就可以变成单表查询。反范式优化是一种以空间来换取时间的操作。

表的拆分

表拆分分为垂直拆分与水平拆分，垂直拆分解决表的宽度问题，水平拆分解决单表数据量过大的问题。 在优化上，特别是垂直拆分，通过下面三个原则，来提高效率。

• 把不常用的字段单独存放到一个表中。
• 把大字段独立存放到一个表中。
• 把经常一起使用的字段放到一起。

总结

1. 优化的第一原则就是针对有问题的 SQL 进行优化，不要为优化而优化，过早优化，谁知道优化的是对的还是错的。
2. 尽量保持简单 SQL，一些关联、计算的操作，可以拉到 JVM 进程里来做。
3. SQL 语句要关注是否是全表或大数据量扫描，是否使用临时表等，出现这些查询就需要优化了。
4. 索引是效率神器，合理使用索引。
5. 表的字段，要选择最优的数据类型，避免 null。表结构要进行合理的拆分，常用的放一起，大字段与不常用的应该拆分出去。