TDSQL-C for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Suggestions on Usage Specifications
- Kernel Features
- Kernel Version Release Notes
- Optimized Kernel Version
- Functionality Features
- Performance Features
- Security Features
- Stability Feature
- Analysis Engine Features
- Purchase Guide
- Value-Added Services Billing Overview
- Database Audit
- Audit Rule Template
- Serverless Service
- Serverless Introduction
- Serverless Resource Pack
- Configuration Change
- Operation Guide
- Database Connection
- Configuring Network Address
- Instance Management
- Configuration Adjustment
- Instance Mode Management
- Cluster Management
- Scaling Instance
- Database Proxy
- Database Proxy Kernel Features
- Managing Database Proxy
- Automatic Read/Write Separation
- Connection Pool Feature
- Other Features
- Account Management
- Console Account
- Database Account
- Configuring Custom Password Strength
- Database Management
- Database Management Tool
- Columnar Storage Index (CSI)
- Utilization of Columnar Storage Indexes
- Read-Only Analysis Engine
- Feature Limits and Compatibility
- Description of Functions and Operators
- Read-Only Analysis Engine Management
- Data Loading
- Executing Queries
- Exclusive Features
- Performance Optimization
- Performance White Paper
- Parameter Configuration
- Multi-AZ Deployment
- Backup and Restoration
- Performing Backup
- Managing Backup
- Rollback
- Restoring Data from Logical Backups
- Restoring Data Through the Console
- Restoring Data from Snapshot Backup
- Data Migration
- Parallel Query
- Setting Parallel Query
- Database Security and Encryption
- Access and Authorization
- Security Group Management
- TencentDB Security Group Management
- Monitoring and Alarms
- Monitoring
- Alarms
- Monitoring Metric Alarm
- Practical Tutorial
- Data Rollback
- Database/Table-Level Rollback
- Rolling back Entire Cluster
- White Paper
- Security White Paper
- Performance White Paper
- Troubleshooting
- Connection Issues
- API Documentation
- Making API Requests
- Instance APIs
- Account APIs
- Audit APIs
- Database Proxy APIs
- Backup and Restoration APIs
- Parameter Management APIs
- Performance Analysis APIs
- Serverless APIs
- ResourcePackage APIs
- Other APIs
- FAQs
- Service Agreement
- TDSQL-C Policy
- General References
DocumentationTDSQL-C for MySQLOperation GuideColumnar Storage Index (CSI)IntroductionSupported Statements and Restrictions
Supported Statements and Restrictions
Last updated: 2024-06-07 16:53:57
This document describes the supported statements of and restrictions on the Column Store Index (CSI) feature.
Prerequisites
The kernel version is TDSQL-C for MySQL version 8.0 3.1.14 or later.
Note:
For read-only instances that meet the version requirements, the CSI feature can be enabled only on those with four or more CPU cores.
Supported Statements
TDSQL-C for MySQL supports queries using SQL statements involving the following scenarios. More scenarios will be gradually supported in the future.
Scenario | Description |
Single-table scan | Forward and reverse scans are supported for full-table scan, index scan, index range scan, and other types of single-table scans. |
Multi-table join | A variety of join algorithms such as nested loop join, hash join, and sort-merge join are supported for multi-table joins. |
Subquery | Non-correlated subqueries are supported. |
Data type | Queries of various types of data are supported, including integer, character, floating-point, and datetime data. |
Operation formula | Arithmetic expressions (+, -, *, %, /, |, &), conditional expressions (<, <=, >, >=, <>, between/and, In), logical operations (or, and, not), general functions (character, integer, and datetime functions), and aggregate functions (count, sum, avg, min, max) are supported. |
Viewing of execution plan pushed down | The EXPLAIN statement can be used to view the execution plan that is pushed down. However, the COLUMNSTORE flag is only displayed when using the EXPLAIN FORMAT=TREE statement. |
Restrictions
TDSQL-C for MySQL will continue to enhance the CSI-based querying capability. Currently, restrictions on the CSI feature are as follows:
Index Creation
Only one column store index can be created for each table.
Column store indexes cannot be created when Generated Column, BLOB, TEXT, JSON, BIT, and GEOMETRY are used.
The COPY algorithm cannot be used to create or delete a column store index or to delete an index first and then add again.
Column store indexes cannot be created for non-InnoDB tables.
DDL-Based Operation on Tables with Column Store Indexes
Partitioning operations on tables with column store indexes are not supported.
Use of Column Store Indexes in SELECT Statements
DML SELECT statements, including INSERT ... SELECT and REPLACE ... SELECT, are not supported.
Common table expressions (CTEs) are not supported.
Window functions are not supported.
WITH ROLLUP is not supported.
UNION is not supported.
Transactions with isolation levels other than ReadCommit are not supported.
Statements for addling locks during data read, such as SELECT FOR UPDATE and SELECT ... FOR SHARE, are not supported.
Stored Procedures are not supported.
SQL_MODE statements, such as MODE_PAD_CHAR_TO_FULL_LENGTH and MODE_INVALID_DATES, are not supported.
GIS-related functions such as SP_WITHIN_FUNC and ST_Distance are not supported.
Custom functions created by users are not supported.
XML functions such as XML_STR are not supported.
Lock-related functions, such as IS_FREE_LOCK, IS_USED_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS, and GET_LOCK, are not supported.
Precautions
CSI-based queries may have the following issues:
Unordered Result Set
When no ORDER is specified, the result set order of CSI-based queries may be different from that of B-tree queries due to the data storage difference. In fact, this is in accordance with ANSI standards. However, it is better to specify a sorting rule to obtain results consistent with those of serial queries if the user is sensitive to the result set order.
Precision and Truncation Issues
Use of CSI can lead to subtle differences in the final results, as the storage and computation precision of certain data types may not completely be the same as MySQL, potentially causing precision or truncation issues of the floating-point data.
Increase in Errors or Alerts
For queries that report errors or alerts during serial execution, the overall number of errors or alerts may increase when CSI is used for queries because each working thread may report errors or alerts during parallel execution.
Impact on Resources
The buffer pool will occupy system memory space when CSI is used.
The parallel execution of CSI-based queries will consume more threads, memory, and I/O resources, compared with serial queries that use only one thread.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No