How the sharding works when no sharding key defined

[Eric <et...@gmail.com>]

Hi,

I am exploring the Sharding Sphere. I may have misunderstanding on the
concepts. Very appreciated for your help on following questions.

1) Is sharding also applied on records update and insert? For example, the
table sharding rules define even and odd numbers records on two different
databases. When updating or inserting records, are these records updated
and inserted on both databases?

2) If sharding also applied on records update and insert, if there are
sharding rules defined on the primary key of a table but the key is
automatically generated by database, inserting a new record would not know
the key beforehand in the SQL Would this record inserted in all the
databases or the database defined by the table sharding rule?

3) If a table has not sharding rule defined but all databases have the
complete records, which database will execute the query on this table?

4) Can we define default rules when there is no sharding rule matched route
the query randomly to a database like simple load balancing?

Best Regards,
Eric Poon

Re: How the sharding works when no sharding key defined

[juan pan <pa...@apache.org>]

Hi, Eric.
Thanks for your attention.

1) Sharding is also  applied on records update, insert, delete, truncate,
etc. ShardingSphere will handle records by using the sharding rule. For
example, when execute `UPDATE tableName SET...Where id =1`, ShardingSphere
will only update the record of id = 1 in odd number database.

2) Firstly, On the condition that sharding rules defined on the primary key
(although sharding key and primary key can be different), you can tell
ShardingSphere by using settings that you want to have global automatically
ID as primary key, then ShardingSphere will generate global ID and put it
into INSERT SQL, then this record will be inserted into correct database
and table. But for you, when write INSERT SQL, you can ignore to set
primary key.

Secondly, if you do not configure global automatically ID as primary key in
sharding rule and want to use the key automatically generated by database,
as you said, ShardingSphere can not know the key beforehand in the SQL,
finally all the databases will have those records.

3) Firstly, If a table has not sharding rule defined but all databases have
the complete records, we suggest you can configure this table as
`broadcastTable` in configuration. As a table is broadcastTable, when
`SELECT FROM broadcastTable`, this query will be executed in random one of
databases; when `DELETE/UPDATE/INSERT` in broadcastTable, SQLs will be
broadcasted and executed in all the databases.

Secondly, If a table has not sharding rule defined, and is not a
`broadcastTable`, but you configure a `default datasource`, ShardingSphere
will send all SQLs of this table to default datasource.

Finally, if a table has not sharding rule defined, and is not a
`broadcastTable`, and there is no `default datasource`, it will throw a
routing exception, for it does not know where this query should be sent.

4) One one hand, you can configure `default datasource` in sharding rule,
so it will throw all SQLs to this default datasource, which is called
sharding scenario. On the other hand, If you just want to load balance,
please configure two or more databases, and tell ShardingSphere which one
is master database and slave databases, and it will choose one database
randomly to route your SQLs, which is called master-slave scenario. Two of
scenarios are supported in ShardingSphere.
---------------------------------
Juan Pan (Trista)
Apache ShardingSphere


Eric <et...@gmail.com> 于2019年6月29日周六 上午5:30写道：

> Hi,
>
> I am exploring the Sharding Sphere. I may have misunderstanding on the
> concepts. Very appreciated for your help on following questions.
>
> 1) Is sharding also applied on records update and insert? For example, the
> table sharding rules define even and odd numbers records on two different
> databases. When updating or inserting records, are these records updated
> and inserted on both databases?
>
> 2) If sharding also applied on records update and insert, if there are
> sharding rules defined on the primary key of a table but the key is
> automatically generated by database, inserting a new record would not know
> the key beforehand in the SQL Would this record inserted in all the
> databases or the database defined by the table sharding rule?
>
> 3) If a table has not sharding rule defined but all databases have the
> complete records, which database will execute the query on this table?
>
> 4) Can we define default rules when there is no sharding rule matched route
> the query randomly to a database like simple load balancing?
>
> Best Regards,
> Eric Poon
>