How do I select a query method when I use SQL to query data?-Tablestore(Tablestore)-阿里云帮助中心

How Tablestore routes SQL queries

Tablestore stores large amounts of structured data and supports multiple index schemas for different query and analytics scenarios:

Primary keys on data tables, which support single-row reads (GetRow) and range reads (GetRange)
Secondary index tables with custom primary key columns
Search indexes built on inverted indexes and spatio-temporal indexes

The SQL query feature lets you query data through either a mapping table created for a data table or one created for a search index. When you use a data table mapping table, Tablestore can automatically select a search index for execution depending on your configuration. For details, see Index selection policy.

Sample scenario

The following examples use a data table named exampletable with these columns:

Column	Type	Role
`id`	Integer	Primary key
`name`	String	Attribute column
`context`	String	Attribute column

A search index named exampletable_searchindex is created for this table. It covers two columns: id (Integer) and context (Text).

Key fact: name is not in the search index. This determines when Tablestore falls back to the data table for query execution.

Choose a method based on your requirements:

Use a mapping table for a data table
Use a mapping table for a search index

Use a mapping table for a data table

When you create a mapping table for a data table, two parameters control query behavior:

Parameter	Values	Effect
`data_consistency`	`eventual` (default) / `strong`	Controls whether results reflect the latest writes
`allow_inaccurate_aggregation`	`true` (default) / `false`	Controls whether Tablestore may route aggregation queries through a search index, which can return approximate results

The combination of these two parameters determines which of the three methods below applies.

Note

For instructions on creating a mapping table for a data table, see Create a mapping table for a table.

Method 1: Eventual consistency, aggregation accuracy not required

Use this method when your queries don't require the latest data and you can accept approximate aggregation results. Tablestore automatically uses a search index when all queried columns are covered by the index, improving query performance.

How automatic index selection works: Tablestore checks whether all columns in the SELECT list are covered by a search index.

If all columns are covered, Tablestore routes the query through the search index.
If any column is missing from the index, Tablestore uses the data table.
1. Create a mapping table named exampletable for the data table. Use default settings for all parameters.
```
CREATE TABLE `exampletable` (`id` BIGINT, `name` MEDIUMTEXT, `context` MEDIUMTEXT, PRIMARY KEY(id)) ENGINE='Tablestore';
```
2. Query data.
  
  The following statement selects all columns (*). Because name is not in exampletable_searchindex, the index cannot cover the full query. Tablestore routes the query to the data table.
```
SELECT * FROM exampletable LIMIT 10;
```
  The following statement selects only id and context, both of which are in exampletable_searchindex. Tablestore routes the query to the search index.
```
SELECT id,context FROM exampletable LIMIT 10;
```

Method 2: Strong consistency

Use this method when your queries must reflect the latest writes. Tablestore never routes queries through a search index in this mode, because search indexes use eventual consistency and cannot guarantee that results include the most recent data.

Create a mapping table named exampletable for the data table and set data_consistency to strong.

CREATE TABLE `exampletable` (`id` BIGINT, `name` MEDIUMTEXT, `context` MEDIUMTEXT, PRIMARY KEY(id)) ENGINE='Tablestore', ENGINE_ATTRIBUTE='{"data_consistency": "strong"}';

Query data. For instructions, see Query data.

Search indexes use eventual consistency and cannot guarantee strong consistency. Therefore, Tablestore does not use search indexes for data query.

Method 3: Eventual consistency, aggregation accuracy required

Use this method when you need eventual consistency but require accurate aggregation results. Because search indexes may return approximate aggregation results, Tablestore does not use them for query execution in this mode.

Create a mapping table named exampletable for the data table, set data_consistency to eventual, and set allow_inaccurate_aggregation to false.

CREATE TABLE `exampletable` (`id` BIGINT, `name` MEDIUMTEXT, `context` MEDIUMTEXT, PRIMARY KEY(id)) ENGINE='Tablestore', ENGINE_ATTRIBUTE='{"data_consistency": "eventual", "allow_inaccurate_aggregation": false}';

Query data. For instructions, see Query data.

Search indexes may return approximate aggregation results. Therefore, Tablestore does not use search indexes for data query.

Use a mapping table for a search index

Create a mapping table directly for a search index when you want to query using full-text search or other capabilities specific to that index—for example, using the text_match function on a Text-type column. Queries always run against the specified search index, regardless of column coverage.

Note

For instructions on creating a mapping table for a search index, see Create mapping tables for search indexes.

Create a mapping table named exampletable_searchindex_test for the search index.

CREATE TABLE `exampletable_searchindex_test` (`id` BIGINT, `context` MEDIUMTEXT) ENGINE='searchindex' ENGINE_ATTRIBUTE='{"index_name": "exampletable_searchindex", "table_name": "exampletable"}'

Query data. The following example uses text_match to run a full-text search on the context column.

SELECT id,context FROM exampletable_searchindex_test WHERE text_match(context, "tablestore cool") LIMIT 10;