sql语句的优化-CSDN博客

本文链接：https://blog.csdn.net/persistence_psh/article/details/128984990

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(*)快一些。

优化关联查询：