本文介绍了如何判断查询语句是否为点查,以及如何将查询优化为点查

点查是应用访问OLTP数据库的一种常见方式,特点是返回结果前只扫描表中的少量数据,在淘宝上查看订单或者商品信息对应到数据库上的操作就是点查。PolarDB-X对点查的响应时间(Response Time, RT)和资源占用做了较多优化,能够支持较高的吞吐,适合高并发读取场景使用。

什么是点查

顾名思义,点查是只扫描少量数据的查询。注意这里说的是扫描少量数据而不是返回少量数据,比如select * from t1 order by c1 limit 1虽然只返回了一条数据,但如果c1上没有索引,需要先扫描t1上所有数据排序后才能返回结果,不符合点查的定义。

单机数据库中,最常见的点查是按照主键(Primary Key,PK)查询数据,通过扫描主键索引快速得到结果,平均只需要扫描log(n)(n为整张表的行数)条记录。如果通过其他条件查询,可以增加局部二级索引(Local Secondary Index,LSI),首先扫描局部二级索引得到主键,然后回表查出完整记录。特殊场景下,如果局部二级索引中包含了查询涉及的所有列,则回表的步骤也可以省略。

PolarDB-X是一个分布式数据库,为了将数据分散到不同数据节点(Data Node,DN)上,引入了分区表的概念,预先将数据切分成多个分区,然后建立分区和DN的映射,其中切分数据需要选取一个或多个列作为切分维度,这些列因此被称为分区键。分布式数据库中,查询性能除了与扫描的数据量线性相关,还与扫描的分片数量正相关,因此点查的定义还需要加上扫描少量分区

PolarDB-X具备透明分布式能力,默认使用主键作为分区键,按照主键查询时首先定位到数据所在的分区,然后通过分区上的主键索引得到结果,性能最高。如果通过其他条件查询,可以增加全局二级索引(Global Secondary Index,GSI)。使用GSI优化查询的原理与LSI相同,首先查到主键然后回表获得完整记录,主要区别在于GSI本身也是一张分区表,数据与主表保存在不同DN上,回表操作大概率需要经过网络,回表代价高于单机数据库。因此,PolarDB-X支持创建聚簇索引来消除回表,达到与主键查询相同的性能。

注意事项

LSI和GSI本质上是以额外存储空间和写入开销为代价,换取查询性能的方案,使用时需要谨慎评估对写入性能的影响。索引表与主表的数据分布不同,为了保证GSI的数据与主表强一致,所有涉及GSI的写入操作都默认被包装在分布式事务中。相比没有GSI的场景,写入RT会增加2~3倍,同时由于索引表和主表混合并行写入,高并发写入场景下产生分布式死锁的概率会增加。综上所述,建议每张逻辑表上创建不超过3个GSI。

如何识别点查

如上所述,分布式数据库中的点查,是指扫描少量分片和数据的查询。通过查看执行计划,可以确认一个查询语句扫描的分片数,更多执行计划介绍请参见调优原理和执行计划。以下为一个点查的示例:
  1. 执行以下语句,查看执行计划:
    explain select c_custkey, c_name, c_address from customer where c_custkey = 42;

    返回信息如下:

    +------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | LOGICAL EXECUTIONPLAN                                                                                                                                            |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | LogicalView(tables="TEST1_000002_GROUP.customer_IVgG_10", sql="SELECT `c_custkey`, `c_name`, `c_address` FROM `customer` AS `customer` WHERE (`c_custkey` = ?)") |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. 执行以下语句,汇总展示DN上的执行计划,判断查询在DN上是否命中正确的索引。DN节点基于MySQL实现,执行计划与MySQL相同,更多介绍参考MySQL 官方文档
    explain execute select c_custkey, c_name, c_address from customer where c_custkey = 42;

    返回信息如下:

    +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
    +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
    | 1  | SIMPLE      | customer | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      | Using pk access |
    +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

如何将查询优化为点查

不是所有查询语句都可以优化为点查,例如没有任何条件的数据抽取查询select * from t1,不合理的分页查询select * from t1 where c1 = 1 limit 100000, 10,参数数量随着业务增长而增长的IN查询等。能够优化为点查的语句可以概括为以下两类:
  • 固定范围扫描的查询:条件中包含等值条件(或可以简化为等值条件),小范围BETWEEN AND条件 ,参数数量固定的IN条件的查询;
  • 结果行数固定的TopN查询:例如select * from t1 where c1 > 42 limit 10 和 select * from t1 order by c1 limit 10select * from t1 order by c1 limit 10

对于这两类查询,优化的思路是添加合适的索引,将全表扫描转化为索引扫描,示例如下:

  1. 创建customer表。
    CREATE TABLE `customer` (
      `c_custkey` int(11) NOT NULL,
      `c_name` varchar(25) NOT NULL,
      `c_address` varchar(40) NOT NULL,
      `c_nationkey` int(11) NOT NULL,
      `c_phone` varchar(15) NOT NULL,
      `c_acctbal` decimal(15,2) NOT NULL,
      `c_mktsegment` varchar(10) NOT NULL,
      `c_comment` varchar(117) NOT NULL,
      PRIMARY KEY (`c_custkey`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`c_custkey`) tbpartition by hash(`c_custkey`) tbpartitions 4;
  2. 查看select * from customer where c_phone = "11";语句的执行计划。
    explain select * from customer where c_phone = "11";

    返回信息如下:

    +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                            |
    +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Gather(concurrent=true)                                                                                                                                                                                                                          |
    |   LogicalView(tables="[000000-000003].customer_[00-15]", shardCount=16, sql="SELECT `c_custkey`, `c_name`, `c_address`, `c_nationkey`, `c_phone`, `c_acctbal`, `c_mktsegment`, `c_comment` FROM `customer` AS `customer` WHERE (`c_phone` = ?)") |
    +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. customer表上只有主键索引,因此虽然c_phone指定了等值条件,依然需要扫描全部分片,此处可以通过添加GSI来优化。
    create global index g_i_phone on customer(c_phone) dbpartition by hash(c_phone);
  4. 添加GSI后,查看执行计划。
    explain select * from customer where c_phone = "11";

    返回信息如下:

    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                                                  |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Project(c_custkey="c_custkey", c_name="c_name", c_address="c_address", c_nationkey="c_nationkey", c_phone="c_phone", c_acctbal="c_acctbal", c_mktsegment="c_mktsegment", c_comment="c_comment")                                                                        |
    |   BKAJoin(condition="c_custkey = c_custkey", type="inner")                                                                                                                                                                                                             |
    |     IndexScan(tables="TEST1_000000_GROUP.g_i_phone_2CSp", sql="SELECT `c_custkey`, `c_phone` FROM `g_i_phone` AS `g_i_phone` WHERE (`c_phone` = ?)")                                                                                                                   |
    |     Gather(concurrent=true)                                                                                                                                                                                                                                            |
    |       LogicalView(tables="[000000-000003].customer_[00-15]", shardCount=16, sql="SELECT `c_custkey`, `c_name`, `c_address`, `c_nationkey`, `c_acctbal`, `c_mktsegment`, `c_comment` FROM `customer` AS `customer` WHERE ((`c_phone` = ?) AND (`c_custkey` IN (...)))") |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    添加GSI后,查询变为索引表上的点查加回表,回表操作只访问一个分片(执行计划中回表显示为主表上的全表扫描,这是因为确定需要扫描的主表分片依赖索引表的查询结果,explain阶段无法确定)。

  5. 使用聚簇索引代替GSI。
    drop index g_i_phone on customer;
    create clustered index g_i_phone on customer(c_phone) dbpartition by hash(c_phone);
  6. 创建聚簇索引后,查看执行计划。
    explain select * from customer where c_phone = "11";

    返回信息如下:

    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | LOGICAL EXECUTIONPLAN                                                                                                                                                                                                            |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | IndexScan(tables="TEST1_000000_GROUP.g_i_phone_fHmZ", sql="SELECT `c_custkey`, `c_name`, `c_address`, `c_nationkey`, `c_phone`, `c_acctbal`, `c_mktsegment`, `c_comment` FROM `g_i_phone` AS `g_i_phone` WHERE (`c_phone` = ?)") |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    使用聚簇索引代替GSI后,由于索引表中包含了主表上的所有列,不再需要回表,执行计划变为索引表上的点查。

以上示例阐述了通过索引优化点查性能的一般过程,其中的关键点是根据查询特征找到适合添加索引的列。对于包含多个条件比较复杂的查询,可以通过PolarDB-X内置的索引推荐功能来找到合适的LSI和GSI,详情请参考智能索引推荐