sql语句的优化

sql优化

优化数据访问

• 查询性能低下最基本的原因是访问的数据太多，大部分性能低下的查询都可以通过减少访问的数据量来优化
• 所以关于低效的查询，需要确认是否检索了大量不需要的数据，以及mysql服务器层是否在分析大量不需要的数据
  • 因为有些查询会请求超过实际需要的数据，然后这些多余数据又会被程序丢弃，这会带来额外的负担，并增加网络开销，最简单的解决办法就是加上limit
  • 还有就是select * 返回全部列，会让优化器无法完成覆盖索引等优化，还会带来额外的I/O、内存和CPU消耗
  • 再比如，如果重复查询相同的数据，最好能将这个数据缓存起来

衡量查询开销的三个指标：

• 响应时间，又分为服务时间和排队时间
• 扫描的行数
• 返回的行数
  • 理想情况下，扫描的行数和返回的行数应该是相等的，但是实际上往往是不同的
  • 如果发现查询需要扫描大量的行，却只返回少量的数据，
    • 可以使用索引覆盖，把所有需要用到的列放在索引中
    • 或者重写sql，让sql语句更加合理

一个复杂查询和多个简单查询：

• 在其他条件都相同的时候，尽可能少的查询当然更好，

• 但是将一个复杂的sql，分解为多个简单的sql能够将压力分散到一个时间段内，降低对服务器的影响

• 对于关联查询，也可以对每一个表进行一次单表查询，然后将结果在程序中关联，这种操作的好处有：

  • 分解查询后，执行单个查询可以减少锁的竞争

  • 而且很方便缓存单表的数据，如果这个表很少改变，基于这个表的查询就可以重复利用缓存结果，提高缓存的效率 ，

  • 另外在应用层做关联，一条数据只需要查询一次，而在数据库查询，可能需要重复的访问一部分数据，查询本身的效率可能会有所提升

优化排序：

• 排序是成本很高的操作，从性能角度考虑，应该尽可能避免排序
• mysql排序有两种：一种是排序操作，一种是按索引顺序扫描

  • 索引排序：

    • 索引排序同样要满足最左前缀，而且只有当索引的列顺序和order by 字句的顺序完全一样，并且列的排序方向都一样时，才能使用索引来排序

    • 而且如果查询关联了多张表，只有order by 字句引用的字段全部为第一张表时，才能使用索引做排序

    • 扫描索引是很高效的，但是如果索引不能覆盖查询所需要的列，就需要回表去查询对应的列，这基本上就是随机i/o，这种比顺序全表扫描还慢

  • 排序操作

    • 当不能使用索引生成排序结果的时候，就需要自己进行排序，mysql将这个过程统一称为文件排序
    • 如果数据量小于 “排序缓冲区” 就会在内存中进行快速排序，
    • 如果数据量大内存不够就会先将数据分块，对每个独立的块使用快速排序，然后将每个块的排序结果放在磁盘上，接着将排序好的块进行合并，最后返回排序结果。
    • mysql的排序算法有两种：
      • 两次传输排序：
        • 先只读取需要排序的字段，对其进行排序，然后根据排序结果，在读取所需要的行数据
        • 这样需要进行两次数据传输，而且第二次会产生大量的随机i/o，传输成本很高，但是这样在排序的时候，可以让排序缓冲区尽可能容纳更多的行数进行排序
      • 单词传输排序：
        • 读取查询需要的所有列，再进行排序，最后直接返回排序结果
        • 这个算法只需要一次顺序i/o，避免了随机i/o的产生
        • 但是，如果返回的列非常多，非常大，会有很多列对于排序来说没有任何作用，而且还会额外占用大量的空间，就可能会有更多的排序块需要合并
    • mysql在进行文件排序的时候，需要使用的临时存储空间可能会非常大，因为每一个排序记录，都会分配一个足够长的定长空间来存放

union（合并）

• 如果希望union的各个字句，先排好序再合并结果集的话，就需要再各个子句中分别使用这些字句，分页也是同里
• 除非一定要消除重复的行，否则一定要使用 union all ,如果没有all关键字，mysql会给临时表加上distinct选项，这回导致对整个临时表数据做唯一性检查，代价很高

count（）：

• 可以用于count(*)统计某个列值的数量，也可以count(列名)统计行数，统计列值时，要求列值是非空的

• 如果想要统计的是结果集的行数，count（*）会忽略所有的列，直接统计行数，语义清晰，性能也会很好，MyISAM保存了表的行数，直接使用 count（ *）会很快，但是前提是没有where条件

• 如果统计列值时，mysql知道某列不可能为空，mysql会将count（某列）转换为count（*）

• count(*) 、count(1)

  • 有主键或联合主键的情况下，count(*)略比count(1)快一些。
  • 没有主键的情况下count(1)比count(*)快一些。

优化关联查询：

• 确保ON或者USING的列上有索引，一般来说只需要再关联顺序的第二张表的对应字段上创建索引
• 确保order by 和 group by 字句中只涉及一个表中的列

对于子查询，最好使用关联查询替代

group by和distinct:

• 两种操作都可以使用索引来优化，这也是最有效的优化方式