从何入手
性能优化可以从以下几方面入手,性能优化收益占比由高到低:
- 业务需求和架构优化:55% (商业需求合理变通,系统架构最优化)
- Query语句的优化:30% (逻辑实现精简规范化)
- 数据库自身的优化:15% (硬件设施、服务器配置合理最优化)
优先考虑
- 业务设计层面:我们在商业需求设计时应该避免那些画蛇添足的不必要的需求设计,避免系统资源浪费,另外必要的时候,可以通过降低某些统计数据的精确度,或者利用异步请求的方式,来保证页面的响应速度和良好的用户体验。
- 架构设计层面:在系统架构体系方面做文章,来实现系统性能的可扩展性,常见的设计模式有:读写分离、水平切分(分表)、垂直切分(分库)。
索引的使用
- 索引的个数限制:单表索引个数尽量控制在5个以内,复合索引的字段个数不要超过5个。
- 适宜索引的情况:索引通常建立在where、order by、group by、distinct从句和join关联列的字段上。
- 不宜索引的情况:数据类别少、频繁更新、text、bit类型、UUID\MD5\HASH字符串的字段,不适合建立索引。
- 最左原则:对于联合索引,字段长度小、使用频繁、区分读(列中不同值的数量/列的总行数)高的列,适宜在最左侧。
- 如何避免索引失效:
- 字段值中的NULL值会导致索引失效。
- 模糊匹配百分号前置,会导致索引失效,譬如LIKE “%aa”。
- 负向查询会导致索引失效,譬如<>、!= 、not in 、not exist。
- 内置函数或表达式会导致索引无效,譬如where score-10 > 60、where abs(x) > 10。
- 强制转换数据类型会导致索引无效,譬如where userid=34987576。
- 复合索引中的第一个字段没有出现在条件语句中,会导致索引无效。
- JOIN的字段的数据类型不一致,会导致索引无效。
SQL调优
-
控制检索量
- 避免使用select *,select count(*)。
- 避免limit 偏移量(offset)过大,减少检索量。
-
如何连接效率更高
- 关联的表个数不宜多,过多可能会出现内存溢出,影响数据库性能
- 用INNER JOIN替换LEFT JOIN(INNER JOIN比LEFT JOIN效率高)
- 用UNION ALL替换UNION(UNION 会去重和排序,效率低)
-
避免临时表
- 避免子查询:通常子查询结果集会被存储在临时表里中而无法使用索引,可以用JOIN替换子查询。
- 使用索引,如果不能使用索引,数据库会创建临时表用于排序。
- 垂直切分,将长字段,如TEXT、BLOG,拆分到单独的表存储。
- 避免去重,能不去重就别用DISTINCT;能用UNION ALL,就别用UNION。
-
其它方面
- 不用OR:or大多数情况下很少能利用上索引,in能更有效地利用索引。
- IN个数:IN包含的值不宜过多,建议少于500个。
- 预编译:预编译可提高性能,而且可以防止SQL注入。
- 冗余:冗余数据可以让查询执行更快,避免表关联。
- 缓存表:利用缓存表存储查询结果,提升查询效率。
- 拆大变小:将复杂的sql拆分成小的sql并行执行,效率更高。
Schema设计
- 存储引擎,没有特殊情况,尽量选择InnoDB引擎,支持事务,行锁。
- 单表大小,控制单表数据量在500万以内,另外不要在库中存储图片。
- 冷热分离,将不常使用的数据从主表中分离出去,保证主表的访问速度。
- 字段冗余,适当冗余字段,减少表连接。
- 数据类型的选择:
- 尽量选择短小的数据类型,避免使用TEXT、BLOG类型,可分离到扩展表中去。
- 尽量避免ENUM枚举类型,排序执行效率低,禁止使用数值作为枚举值。
- 尽量用DATATIME和TIMESTAMP存储日期,不要用字符串型来存储日期,会占用更多的空间。
- 存储金额请使用Decimal精准浮点型,不会损失精度,而float、double是非精准浮点型。