SQL数据库性能优化

• 1.查询尽量避免使用select *
  • 1.1 增加磁盘开销：数据库本质上是将记录存储在磁盘上，查询操作就是一种进行磁盘IO的行为,我们查询的字段越多，读取的内容也就越多，对IO磁盘的开销也就会增大，特别是某些字段，如TEXT、MEDIUMTEXT或者BLOB等类型，磁盘IO开销增加会更加明显
  • 1.2 加重网络时延：数据每次都通过socket send buffer发送到客户端，查询一次数据量不大，如果一旦有人使用*将TEXT、MEDIUMTEXT或者BLOB 类型查出来，数据量会成指数级上升，网络传输的次数会增加，时间也就会增加
  • 1.3 无法使用覆盖索引：
  • 1.4 多表联合查询会变慢
• 2.union all代替union

  • unionunionunion: 对两个结果集进行并集操作, 不包括重复行,相当于distinct排重, 同时进行默认规则的排序;

  • union all: 对两个结果集进行并集操作, 包括重复行, 结果全部显示, 不管是不是重复;

  • 因此union all会比union多一个排除重复数据的操作，排除重复数据需要遍历，排序和比较，耗时和CPU资源

• 3.小表驱动大表(用小的数据集去驱动(可理解为匹配)大的数据集)
  • in子查询：in适用于左边大表，右边小表（优先执行in里面的子查询语句，然后再执行外面的语句），in中值太多导致查询变慢
     
  • exists条件筛选：exists适用于左边小表，右边大表（优先查询主查询语句，再匹配exists条件）
     
• 4.数据库的批量操作(批量插入、批量更新)
  • 每次插入一条数据，使用循环插入多条数据，需要多次请求数据库，会消耗更多性能
  • 一次插入多条数据，如果数据太多，数据库响应也会很慢，建议批数据控制在500条以内
     
• 5.多用limit（高效的分页）
  •  SELECT TOP 10 *  FROM (SQL语句) AS T  ORDER BY ID ASC 
• 6.增量查询
  • 全量查询是你要查询一个数据，你需要输入完整的数据字符然后按回车程序才开始查询，而增量查询只要你输入此数据的第一个字符或字母，程序就将自动查询到它并使之高亮度显示，等待你的选择
• 7.用连接查询代替子查询
  • 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代
  • join表的数量不应该超过3个
  • left join关联时，左边要用小表，右边可以用大表，能用inner join的地方，尽量少用left join
     
• 8.控制索引的数量
  • 索引能够显著的提升查询sql的性能，但索引数量并非越多越好，因为表中新增数据时，需要同时为它创建索引，而索引是需要额为的存储空间的，而且还会有一定的性能消耗。单表索引数量应该控制在5个以内。
  •  如果系统并发量不高，表中的数据量也不多，超过5个也可以，不要超过太多就行。    
  • 对于高并发的系统，单表索引数不要超过5个。能建联合索引，就不要建单个索引。可以将部分查询功能迁移到其他类型的数据库中（？？？），比如：Elastic Search,HBase等，在业务表中只需要建几个关键索引即可。
     
• 9.选择合理的字段类型
  • 能用数字类型就不要字符串，字符处理往往比数字要慢
  • 尽可能使用小的类型，比如，用bit存布尔值，用tinyint存枚举值等。长度固定的字符串字段，用char类型，长度可变的字符串字段，用varchar类型。金额字段用decimal，避免精度丢失问题……
• 10.提升group by 的效率
  • 使用分组时，先缩小数据范围，再进行分组