性能文章>【译】作为程序员应该知道的最基本SQL优化建议>

【译】作为程序员应该知道的最基本SQL优化建议转载

2年前
344845

当我们谈论系统性能优化时,除了代码级别的各种有针对性的优化外,还有一个非常重要的手段来优化数据库的性能。

在互联网系统中,当系统访问越来越多,数据量越来越多时,数据库的压力将越来越大。如果数据库表结构设计不正确,SQL语句写得不好,代码性能可能极高,但系统会被数据库拖下来。

因此,我们程序员有必要了解数据库和数据库访问优化,以便设计一个高性能系统。

作为一名程序员,我们可能不知道生产环境的服务器硬件配置,并且我们无法像DBA那样专业地在数据库上执行各种实际测试和摘要。然而,我们应该很好地理解我们的SQL业务逻辑以及我们访问表和字段的数据。

事实上,我们不想知道数据库的高可用性架构以及如何访问数据。我们只关心我的SQL是否可以尽快返回结果。那么,程序员应该如何优化数据库呢?我们如何快速定位SQL性能问题并找到正确的优化方向?面对这些问题,我总结了程序员的一些基本优化知识(本文基于MySQL数据库)。

系统性能优化的基本方向

为了优化计算机系统的性能,我们需要知道系统运行的位置,并快速定位性能的瓶颈。在大多数情况下,最慢的设备将是瓶颈。众所周知,计算机系统的CPU运行速度比缓存快得多,缓存比内存快得多。因此,在许多情况下,磁盘IO和网络IO是系统的性能瓶颈。

根据数据库操作原理,这些设备在数据库运行期间的主要工作内容如下:

  • CPU:事务控制、并发控制、SQL解析、函数或逻辑计算。
  • 内存:缓存数据的读写。
  • 网络:数据响应和查询结果传输。
  • 磁盘:数据读取和写入、日志记录、海量数据排序、表连接。

对于数据库,上述四点可以转化为以下四个优化建议:

  • 减少磁盘使用和数据访问(设计适当的表结构并创建高性能索引)
  • 减少网络访问(批量请求,返回更少的数据)
  • 减少CPU开销(减少聚合函数调用并合理使用排序)

适当的表格结构设计

良好的表结构设计是高数据库性能的基础。表结构设计的核心是选择现场数据类型。选择正确的数据类型至关重要。选择数据类型有一些一般原则:

  • 强烈建议为每个表创建一个自动增量主键。自动增量主键可以将随机io提升到顺序io,并可以应用于索引页面,以使索引页面紧凑,并减少页面拆分对性能的影响
  • 如果长度符合要求,较小的数据类型会更好,并使用更少的磁盘、内存和cpu缓存。对于数字数据,如果您可以选择无符号类型,则可以选择无符号类型。无符号类型可以存储比有符号类型多一倍的正数。
  • 确定字符类型长度。请用char代替varchar。相同的字符长度字符节省了更多的存储空间。
  • 如果您可以使用时间戳,则不需要日期时间。时间戳只需要4个字节,日期时间需要8个字节。
  • 尽量避免空字段。当MySQL中的字段为NULL时,它仍然占用空间,并使索引和索引统计信息更加复杂。更新NULL字段时,很容易拆分索引页面,这将影响性能。应该使用有意义的值而不是NULL。

创建高性能索引

顾名思义,数据库索引是一种用于优化查询的辅助数据结构。它可以被视为为提高查询速度而创建的另一个冗余数据。该指数相对简单。就MySQL的innodb引擎而言,它使用B+tree数据结构进行存储。然而,尽管索引很简单,但很少有人能在复杂的表格中正确使用索引。

索引将大大增加表记录的DML(更新、插入、删除)成本。优秀的索引可以提高数据库性能数百倍,但不合理的索引可能会降低数百倍的性能。因此,有必要平衡业务需求,以便在表格中创建索引。一般来说,关于在哪些字段上创建索引,有几种经验:

  • 一个经常用于查询的字段,该字段过滤的记录约占总记录的10%。
  • 建议为主键、表关联的外键和具有标识意义的字段(如用户名、电子邮件等)创建索引。
  • 状态标志类似于order_ status,is_ Delete和性别字段不适合创建索引,大文本、大字段和描述字段不适合创建索引。

在以下情况下,即使创建了索引,也不会使用该索引:

  • 当索引字段使用<>not inis null时,索引将不被使用,例如Index_column <> ?。您可以使用union来聚合搜索结果,而不是<>

例如select id, product_name from order where amount!=1000

(select id, product_name from order where amount>1000) union all (select id, product_name from order where amount<1000)

  • 索引不能用于普通算术运算或函数运算后的索引字段,例如function(Index_column)=?Index_column+1=?
  • 具有前导模糊查询的LIKE语法不能使用索引,例如index_column like '%?%'

分页查询

当查询的数据量超过总数的30%时,MySQL将不会使用索引,因此分页查询非常重要。但是,您也应该小心分页查询。例如,select * from table limit 100000 10;MySQL将查询前100010记录并丢弃前100000条记录,因此在分页到比较页面时,查询速度会越来越慢。我们可以通过使用延迟相关性来解决这个问题。

select * from table where id in (select id from table limit 1000000 10)

这种方法巧妙地使用集群索引来减少大量后台查询的执行时间,从而提高执行效率。

合理使用排序

数据库排序通常在内存中进行。对于数据库,排序是一种耗CPU的操作。由于现代CPU的高性能,数万个数据的排序可能对数据库的影响很小。但是,如果您的表中有数十万数据,您需要考虑如何处理排序。对大型数据集进行排序不仅会消耗内存和CPU,而且如果内存不足,也会发生硬盘排序,导致排序性能急剧下降。因此,一般来说,如果不排序,你就无法排序。如果您必须排序,请尝试为排序字段创建一个索引,因为索引本身是有序的。

还有一些简单的建议,例如只返回所需的数据和批处理。本文旨在分析数据库的一些常见优化方法,并提出一些面向程序员的SQL优化建议,希望提高你的SQL优化能力,感谢支持。

原文作者:lance

点赞收藏
一只菌

简简单单,认真生活。

请先登录,查看4条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

日常Bug排查-偶发性读数据不一致

日常Bug排查-偶发性读数据不一致

5
4