Thanks to the ShardingSphere community’s steady evaluate and suggestions to develop options similar to data sharding and browse/write splitting, our crew discovered that some customers create many shards when utilizing the information sharding characteristic.
In such instances, there could be 1,000 bodily tables comparable to a sharding logical desk, which disturbs customers.
For occasion, a SELECT * FROM t_order
assertion will result in a full-route, which is clearly not the case for OLTP. This SQL could be positioned in one other Proxy to keep away from blocking different requests.
However, if customers will not be acquainted with Proxy or the best way to write a the place
situation and do not know that sharding is just not supported on this situation, a full-route remains to be required.
A full-route can decrease the efficiency of Proxy and even consequence within the failure of an inexpensive request. Imagine that there are 1,000 shards in a bodily database. If they’re executed in parallel, 1,000 connections are wanted, and if in serial, the request can result in a timeout. For this cause, neighborhood customers requested whether or not the unreasonable request might be intercepted instantly.
Our crew thought of the difficulty for some time. One choice is to easily block the full-route operation. Doing so requires a verify within the code and including a swap to the configuration file. On the opposite hand, if the consumer later must set a desk to read-only or requires the replace operation to hold a restrict
, does that imply the code and configuration change once more? This strategy clearly goes towards the pluggable logic of Proxy.
In response to the above issues, the not too long ago launched Apache ShardingSphere 5.2.0 offers customers with auditing for the SQL sharding perform. The audit can both be an interception operation or a statistical operation. Similar to the sharding and distinctive key era algorithms, the audit algorithm is plugin-oriented, user-defined, and configurable.
[ Related read 5 new improvements in Apache ShardingSphere ]
Next, I’ll elaborate on the implementation logic for auditing knowledge sharding with particular SQL examples.
Audit for sharding interface
The entrance to Apache ShardingSphere’s audit is within the org.apache.shardingsphere.infra.executor.verify.SQLCheckEngine
class, which can invoke the verify
methodology of the SQLChecker
interface. Currently, the ShardingSphere audit incorporates an audit for permission (confirm username and password) and an audit for sharding.
This instance focuses on the mum or dad interface applied within the ShardingAuditChecker
of audit for sharding.
You can study its working rules rapidly by viewing the verify
code of org.apache.shardingsphere.sharding.checker.audit.ShardingAuditChecker
.
public interface ShardingAuditAlgorithm extends ShardingSphereAlgorithm {
/**
* Sharding audit algorithm SQL verify.
*
* @param sqlStatementContext SQL assertion context
* @param parameters SQL parameters
* @param grantee grantee
* @param database database
* @return SQL verify consequence
*/
SQLCheckResult CHECK(SQLStatementContext<?> sqlStatementContext, List<Object> parameters, Grantee grantee, ShardingSphereDatabase DATABASE);
}
This methodology obtains the audit methods of all of the sharding tables concerned and invokes the audit algorithms configured in every sharding desk audit technique. An exception is exhibited to the consumer if an audit algorithm fails to move.
Some customers could marvel what disableAuditNames
does right here. The sharding audit additionally permits customers to skip this course of. In some instances, customers could have to execute SQL that ought to have been blocked by the audit, and they’re conscious of the impression of this SQL.
Users can make the most of the Hint: disableAuditNames
to skip audit interception, which shall be described with sensible examples later. The Proxy Administrators can configure enableHintDisable
to regulate whether or not to permit customers to skip this course of. The default worth is true
, indicating {that a} Hint-based skip is permitted.
Audit for sharding algorithm
The audit for sharding algorithm interface org.apache.shardingsphere.sharding.spi.ShardingAuditAlgorithm
is inherited from SPI class ShardingSphereAlgorithm
. It inherits sort
and props
properties and defines its personal verify
methodology. If you need to customise your audit algorithm, simply implement the interface and add it to INF.providers
.
public interface ShardingAuditAlgorithm extends ShardingSphereAlgorithm {
/**
* Sharding audit algorithm SQL verify.
*
* @param sqlStatementContext SQL assertion context
* @param parameters SQL parameters
* @param grantee grantee
* @param database database
* @return SQL verify consequence
*/
SQLCheckResult CHECK(SQLStatementContext<?> sqlStatementContext, List<Object> parameters, Grantee grantee, ShardingSphereDatabase DATABASE);
}
Apache ShardingSphere implements a normal audit for sharding algorithm org.apache.shardingsphere.sharding.algorithm.audit.DMLShardingSituationsShardingAuditAlgorithm
, particularly the above-mentioned SQL assertion that intercepts the full-route.
The algorithm makes choices by figuring out whether or not the sharding situation is null
. Of course, it will not intercept broadcast tables and non-sharding tables.
public remaining class DMLShardingSituationsShardingAuditAlgorithm implements ShardingAuditAlgorithm {
@Getter
non-public Properties props;
@Override
public void init(remaining Properties props) {
this.props = props;
}
@SuppressWarnings({"rawtypes", "unchecked"})
@Override
public SQLCheckResult CHECK(remaining SQLStatementContext<?> sqlStatementContext, remaining List<Object> parameters, remaining Grantee grantee, remaining ShardingSphereDatabase DATABASE) {
IF (sqlStatementContext.getSqlStatement() instanceof DMLStatement) {
ShardingRule rule = DATABASE.getRuleMetaData().getSingleRule(ShardingRule.class);
IF (rule.isAllBroadcastTables(sqlStatementContext.getTablesContext().getTableNames())
|| sqlStatementContext.getTablesContext().getTableNames().stream().noneMatch(rule::isShardingDesk)) {
RETURN NEW SQLCheckResult(TRUE, "");
}
ShardingConditionEngine shardingConditionEngine = ShardingConditionEngineFactory.createShardingConditionEngine(sqlStatementContext, DATABASE, rule);
IF (shardingConditionEngine.createShardingSituations(sqlStatementContext, parameters).isEmpty()) {
RETURN NEW SQLCheckResult(FALSE, "Not allow DML operation without sharding conditions");
}
}
RETURN NEW SQLCheckResult(TRUE, "");
}
@Override
public String getType() {
RETURN "DML_SHARDING_CONDITIONS";
}
}
I’d prefer to introduce one other audit for the sharding algorithm: LimitRequiredShardingAuditAlgorithm
. This algorithm can intercept SQL with out carrying a restrict
within the replace
and delete
operations.
As this algorithm is much less common, it’s not at the moment built-in into Apache ShardingSphere. As you may see, it is extremely simple to implement a customized algorithm, which is why the audit for sharding framework is required. Thanks to its plugin-oriented structure, ShardingSphere boasts nice scalability.
public remaining class LimitRequiredShardingAuditAlgorithm implements ShardingAuditAlgorithm {
@Getter
non-public Properties props;
@Override
public void init(remaining Properties props) {
this.props = props;
}
@SuppressWarnings({"rawtypes", "unchecked"})
@Override
public SQLCheckResult CHECK(remaining SQLStatementContext<?> sqlStatementContext, remaining List<Object> parameters, remaining Grantee grantee, remaining ShardingSphereDatabase DATABASE) {
IF (sqlStatementContext instanceof UpdateStatementContext && !((MySQLUpdateStatement) sqlStatementContext.getSqlStatement()).getLimit().isPresent()) {
RETURN NEW SQLCheckResult(FALSE, "Not allow update without limit");
}
IF (sqlStatementContext instanceof DeleteStatementContext && !((MySQLDeleteStatement) sqlStatementContext.getSqlStatement()).getLimit().isPresent()) {
RETURN NEW SQLCheckResult(FALSE, "Not allow delete without limit");
}
RETURN NEW SQLCheckResult(TRUE, "");
}
@Override
public String getType() {
RETURN "LIMIT_REQUIRED";
}
}
Use audit for sharding
Audit for sharding requires you to configure an audit technique for logical tables. To assist you to get began rapidly, its configuration is identical as that of the sharding algorithm and the sharding key worth generator.
There is an algorithm definition and technique definition, and a default audit technique can be supported. If the audit technique is configured within the logical desk, it impacts solely that logical desk.
If defaultAuditTechnique
is configured within the logical desk, it takes impact for all of the logical tables beneath the sharding rule. Auditors
are just like ShardingAlgorithms
, auditStrategy
to databaseStrategy
, and defaultAuditTechnique
to defaultDatabaseStrategy
or defaultTableStrategy
.
Please check with the next instance. Only the configuration of the audit for sharding is displayed. You should configure the sharding algorithm and knowledge supply your self.
guidelines:
- !SHARDING
TABLES:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
auditStrategy:
auditorNames:
- sharding_key_required_auditor
enableHintDisable: TRUE
defaultAuditTechnique:
auditorNames:
- sharding_key_required_auditor
enableHintDisable: TRUE
auditors:
sharding_key_required_auditor:
TYPE: DML_SHARDING_CONDITIONS
Step 1: Execute a question operation. An error is displayed because the audit technique for intercepting the full-database route is configured.
mysql> SELECT * FROM t_order;
ERROR 13000 (44000): SQL CHECK failed, error message: NOT enable DML operation WITHOUT sharding circumstances
Step 2: Add HINT
. The title of the HINT
is /* ShardingSphere trace: disableAuditNames */
,and disableAuditNames
is adopted by the auditorsNames
configured within the previous command.
If a number of names exist, separate them with areas similar to/* ShardingSphere trace: disableAuditNames=auditName1 auditName2*/
. After utilizing HINT
, you may see that the SQL operation is efficiently executed.
mysql> /* ShardingSphere trace: disableAuditNames=sharding_key_required_auditor */ SELECT * FROM t_order;
+----------+---------+------------+--------+
| order_id | user_id | address_id | STATUS |
+----------+---------+------------+--------+
| 30 | 20 | 10 | 20 |
| 32 | 22 | 10 | 20 |
+----------+---------+------------+--------+
2 ROWS IN SET (0.01 sec)
Note: HINT
requires you to switch the server.yaml
configuration of Proxy. In addition, if you’re utilizing MySQL terminal to hook up with Proxy instantly, you must add the -c
property—in any other case, HINT
feedback shall be filtered out of the MySQL terminal and won’t be parsed by Proxy on the backend.
guidelines:
- !SQL_PARSER
sqlCommentParseEnabled: TRUE
sqlStatementCache:
initialCapacity: 2000
mostSize: 65535
parseTreeCache:
initialCapacity: 128
mostSize: 1024
props:
proxy-trace-enabled: TRUE
mysql -uroot -proot -h127.0.0.1 -P3307 -c
DistSQL with audit for sharding
As you may see from the release notes, Apache ShardingSphere 5.2.0 helps the next DistSQL with audit for sharding perform:
CREATE SHARDING AUDITOR
ALTER SHARDING AUDITOR
SHOW SHARDING AUDIT ALGORITHMS
The following DistSQL shall be supported IN future releases:DROP SHARDING AUDITOR
SHOW UNUSED SHARDING AUDIT ALGORITHMS
CREATE SHARDING TABLE RULE # together with AUDIT_STRATEGY
This publish launched how audit for sharding works with particular examples. I imagine you have already got a fundamental understanding of this perform and may use it everytime you want or use a customized algorithm.
You are additionally welcome to submit normal algorithms to the neighborhood. If you might have any concepts to contribute or encounter points together with your ShardingSphere, be happy to publish them on GitHub.
This article initially appeared on ShardingSphere 5.2.0: Audit for sharding intercepts unreasonable requests in multi-shards scenarios and is republished with permission.