Considerations when using Amoeba as a Proxy in MySQL

AndMySQLCompared with the official MySQL Proxy,AmoebaConfiguration is very convenient. It is easier to use SQLJEP syntax to write rules based on XML configuration files than MySQL Proxy Based on lua scripts. However, it is not perfect, and there are many precautions in actual use. Next we will introduce some precautions for Amoeba.

Amoeba does not support transactions

Currently, Amoeba does not support transactions. Any SQL statements related to transactions will be blocked by Amoeba. If you must use transactions and all the processing operations in the transaction can be routed to the same MySQL instance, you can consider using transactions for processing in the stored procedure. In addition, Amoeba is expected to support transactions in the future.

Amoeba does not support cross-database join and sorting.

Cross-database join and sorting consume a lot of resources, causing serious performance degradation. Amoeba is not supported.

The Insert statement must specify the table column name.

Amoeba is parsed based on SQL statements. If the SQL statement does not contain keywords in the segmentation rules, Amoeba will not perform horizontal segmentation according to the rules. For example, the following statement will not be correctly split when data is horizontally split by city ID:

Insert into tb_Shop VALUES (2011, 'shop name', 'shop address ', 1, '2017-07-30 ');

The following statement can be correctly split:

Insert into tb_Shop (Shop_ID, ity_ID, ShopName, Address, ShopStatus, CreateTime) VALUES (, 'shop name', 'shop address', 1, '2017-07-30 ');

That is to say, the keyword City_ID In the splitting rule must appear in the SQL statement.

Amoeba does not support database/table sharding.

Amoeba does not support database/table sharding and can only be divided into MySQL instances.

Amoeba does not support querying large data volumes.

Massive Data Queries consume a lot of memory, And Amoeba performs very poorly when querying large data volumes. Of course, it is rare or unnecessary to query a large amount of data in the actual business. The large data volume query refers to a query with more than 100,000 rows.

Amoeba requires stricter SQL statement specifications

If the From keyword is not followed by a subquery, no parentheses () "are allowed "()";

If the field name in the table is the same as the keyword or function name, it must contain the character 'mytable. 'order ').

Here are some precautions for Amoeba. I hope this introduction will be helpful to you!