优化语句性能

概述

为了获得良好的性能，需要通过几个镜头来查看您是如何访问数据库的：

SQL 语句性能：这是性能问题的最常见原因，表名应该从哪里着手。
架构设计：根据 SQL架构和工作负载的数据访问模式，可能需要进行更改以避免创建事务争用或热点。
集群拓扑：作为一个分布式系统，hubble要求您权衡延迟与弹性。这需要根据需要选择正确的集群拓扑。

sql语句执行原则

要获得良好的 SQL 语句性能，请遵循以下规则：

1.扫描尽可能少的行。如果您的应用程序扫描的行数超过了给定语句所需的行数，则将难以扩展。
2.使用正确的索引。WHERE您的语句应该在子句中的列上使用索引。您希望避免全表扫描对性能的影响。
3.使用正确的连接类型。根据您查询的表的相对大小，连接的类型可能很重要。

使用explain进行语句优化

全表扫描

查询缓慢的最常见原因是次优SELECT语句，包括全表扫描和不正确使用索引。当基于不在主键或任何二级索引中的列从大表中检索少量行时，通常会获得较差的性能：

SELECT * FROM users WHERE name = 'Cheyenne Smith';


                   id                  |   city    |      name      |           address            | credit_card
---------------------------------------+-----------+----------------+------------------------------+--------------
  326d3a95-fe03-4600-8000-00000003c1d0 | boston    | Cheyenne Smith | 84252 Bradley Coves Suite 38 | 2369363335
  c6b45c93-7ecd-4800-8000-0000000ecdfd | amsterdam | Cheyenne Smith | 29149 Jane Lake              | 6072991876
  5edbdde1-c806-4400-8000-00000007114a | seattle   | Cheyenne Smith | 90016 Anthony Groves         | 3618456173
  60edde95-4c2d-4000-8000-0000000738c7 | seattle   | Cheyenne Smith | 70310 Knight Roads Suite 36  | 9909070365
  00024e8e-d94c-4710-8000-00000000002c | new york  | Cheyenne Smith | 8550 Kelsey Flats            | 4374468739
  0c777227-64c8-4780-8000-00000000edc8 | new york  | Cheyenne Smith | 47925 Cox Ways               | 7070681549
(1 row)

Time: 981ms total (execution 981ms / network 0ms)

要了解此查询为何表现不佳，使用EXPLAIN：

EXPLAIN SELECT * FROM users WHERE name = 'Cheyenne Smith';

                                         info
-----------------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • filter
  │ estimated row count: 3
  │ filter: name = 'Cheyenne Smith'
  │
  └── • scan
        estimated row count: 1,259,634 (100% of the table; stats collected 3 minutes ago)
        table: users@primary
        spans: FULL SCAN

table: users@primary primary表示使用( )来扫描表( )的索引users。spans: FULL SCAN向您展示，在列上没有二级索引的情况下name，hubble会扫描表的每一行，users按主键 (city/ id) 排序，直到找到具有正确name值的行。

情况一：二级索引过滤

CREATE INDEX on users (name);

查询将更快的返回

SELECT * FROM users WHERE name = 'Cheyenne Smith';

                   id                  |   city    |      name      |           address            | credit_card
---------------------------------------+-----------+----------------+------------------------------+--------------
  c6b45c93-7ecd-4800-8000-0000000ecdfd | amsterdam | Cheyenne Smith | 29149 Jane Lake              | 6072991876
  326d3a95-fe03-4600-8000-00000003c1d0 | boston    | Cheyenne Smith | 84252 Bradley Coves Suite 38 | 2369363335
  00024e8e-d94c-4710-8000-00000000002c | new york  | Cheyenne Smith | 8550 Kelsey Flats            | 4374468739
  0c777227-64c8-4780-8000-00000000edc8 | new york  | Cheyenne Smith | 47925 Cox Ways               | 7070681549
  5edbdde1-c806-4400-8000-00000007114a | seattle   | Cheyenne Smith | 90016 Anthony Groves         | 3618456173
  60edde95-4c2d-4000-8000-0000000738c7 | seattle   | Cheyenne Smith | 70310 Knight Roads Suite 36  | 9909070365
(6 rows)


Time: 24ms total (execution 3ms / network 0ms)

这表明hubble从二级索引 (users@users_name_idx)开始。因为是按排序的name，所以查询可以直接跳转到相关值(/'Cheyenne Smith' - /'Cheyenne Smith')。但是，查询需要返回不在二级索引中的值，因此hubble 抓取与该值一起存储的主键(city/id)name(主键始终与二级索引中的条目一起存储)，跳转到主索引中的该值，然后返回整行。

情况二：按存储列的二级索引进行过滤

当有一个按特定列过滤但检索表总列的子集的查询时，可以通过将这些附加列存储在二级索引中来提高性能，以防止查询也需要扫描主索引。例如:假设您经常检索用户的姓名和信用卡号：

SELECT name,credit_card FROM users WHERE name = 'Cheyenne Smith';

    name      | credit_card
-----------------+--------------
  Cheyenne Smith | 6072991876
  Cheyenne Smith | 2369363335
  Cheyenne Smith | 4374468739
  Cheyenne Smith | 7070681549
  Cheyenne Smith | 3618456173
  Cheyenne Smith | 9909070365
(1 row)

Time: 14ms total (execution 4ms / network 0ms)

在当前二级索引开启的情况name下，hubble仍然需要扫描一级索引来获取信用卡号：

EXPLAIN SELECT name, credit_card FROM users WHERE name = 'Cheyenne Smith';

                                      info
-------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 3
  │ table: users@primary
  │
  └── • scan
        estimated row count: 3 (<0.01% of the table; stats collected 2 minutes ago)
        table: users@users_name_idx
        spans: [/'Cheyenne Smith' - /'Cheyenne Smith']

在上删除并重新创建索引name，这次将credit_card值存储在索引中：

DROP INDEX users_name_idx;

CREATE INDEX ON users (name) STORING (credit_card);

现在credit_card值存储在索引中name，hubble只需要扫描该索引：

EXPLAIN SELECT name, credit_card FROM users WHERE name = 'Cheyenne Smith';

                                    info
---------------------------------------------------------------------------------
  distribution: local
  vectorized: true

    scan
    estimated row count: 3 (<0.01% of the table; stats collected 27 seconds ago)
    table: users@users_name_idx
    spans: [/'Cheyenne Smith' - /'Cheyenne Smith']

这会带来更快的性能：

SELECT name, credit_card FROM users WHERE name = 'Cheyenne Smith';

     name      | credit_card
-----------------+--------------
  Cheyenne Smith | 6072991876
  Cheyenne Smith | 2369363335
  Cheyenne Smith | 4374468739
  Cheyenne Smith | 7070681549
  Cheyenne Smith | 3618456173
  Cheyenne Smith | 9909070365
(6 rows)

Time: 2ms total (execution 2ms / network 0ms)

下载PDF

本页导航

概述
sql语句执行原则
使用explain进行语句优化
- 全表扫描