Cabbage Java self study room covers core knowledge

Cross-library/cross-instance Join does not have to rely on middleware

1. Vertical data segmentation

Some electricity company will original members, orders, goods and other data are stored in a database instance, but business development rapidly, traffic speed growth, lead to database capacity and performance bottlenecks, so users decide to vertical resolution of architecture, the vertical members, goods, order data split to three database instance. At this time, the business needs to show the sales order quantity of a certain category of goods, and the query originally in the same database should be changed into the query across two database instances. How to carry out associated query in business?

Cross-library Join requirements

The first approach that comes to mind is to refactor the existing business code, query the data from the two databases separately, and then join the business code. The problem is that if this solution is adopted, there are so many queries in the business that it is extremely difficult to split and operate. There is no very efficient method for cross-library join operation, which requires iterative query from various business libraries, and the query efficiency will also have a certain impact.

When the scheme was rejected, the user thought of another method, whether to use Mycat, Sharding-JDBC and other database middleware to achieve, of course, this itself is a feasible scheme, but also a good solution. But if the company’s systems are complex, architecture is difficult to change, code corruption is serious, and there is no way to solve the problem in a short period of time, is there nothing you can do without database middleware?

2. Principle of MySQL FEDERATED engine

As shown, the FEDERATED engine works as follows:

  1. Enable FEDERATED engine support for remote servers;
  2. When the local server queries the FEDERATED engine table, it sends the query statement to the remote server.
  3. The remote server queries the result through the transmitted query statement and returns it to the local calling server.

3. MySQL starts the FEDERATED engine

The pre-installation of MySQL is omitted here, and the recommended version is MySQL5.7.

Docker method installation process can refer to the author article: Canal solves MySQL and Redis data synchronization problem

2.1. Run the SHOW ENGINES command.

Check whether the Federated engine is started in the mysql database. As shown in the following figure, it is not enabled.

SHOW ENGINES;
Copy the code

2.2 Modifying the MySQL Configuration File and restarting the service

Modify the my.ini file at the root of the mysql folder (for Linux, modify the my. CNF file at the root of the mysql folder) : (Docker installation only needs to modify the external configuration file and restart the container. Aliyun and Huawei CLOUD DMS can find and modify the configuration mode by themselves, and even have their own cross-library Link function)

federated
Copy the code
Docker restart (container ID)Copy the code

2.3. Run SHOW ENGINES again.

Check whether the Federated engine is enabled in mysql database.

SHOW ENGINES;
Copy the code

2.4. Create two test databases, database tables

Here readers build their own database, free to play:

Then create two tables prd_SKU (SKU information table) and PRd_SKu_STOCK (SKU inventory table) respectively in the two databases:

CREATE TABLE `prd_sku`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'SKU name',
  `code` varchar(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'SKU code'.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
Copy the code
CREATE TABLE `prd_sku_stock`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `sku_id` int(11) NOT NULL DEFAULT 0 COMMENT 'SKU的ID',
  `stock` int(11) NOT NULL DEFAULT 0 COMMENT 'SKU inventory '.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
Copy the code

Let’s add some data to the table:

INSERT INTO `prd_sku` VALUES (1.'Yellow S code'.'Y00S00');
INSERT INTO `prd_sku` VALUES (2.'Red M code'.'R00M00');
Copy the code
INSERT INTO `prd_sku_stock` VALUES (1.1.96);
INSERT INTO `prd_sku_stock` VALUES (2.2.98);
Copy the code

2.5. Create A remote table in database A to connect to database B

CREATE TABLE `mycat_db1`.`prd_sku_stock`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `sku_id` int(0) NOT NULL,
  `stock` int(0) NOT NULL.PRIMARY KEY (`id`)
) ENGINE = FEDERATED CONNECTION = 'mysql://root:88021120@localhost:3306/mycat_db2/prd_sku_stock';
Copy the code
  • Root :88021120 Account and password of the remote database
  • Localhost: IP address and port number of the remote database
  • Mycat_db2 The name of the remote database
  • Prd_sku_stock Table name of the remote database

Here are a few points to note in particular:

  1. Join table fields must be the same as the real table, allowing subset;
  2. Join tables are automatically updated and vice versa;
  3. The real table structure changes, the join table does not change;
  4. Delete join table, real table will not be deleted;
  5. The join table cannot be changed with the ALTER statement, so you can delete and rebuild the join table if you want to synchronize requirements.

2.6. Run the join query in database A to view the result

SELECT * FROM prd_sku t1, prd_sku_stock t2 WHERE t1.id=t2.sku_id;
Copy the code

3. Aliyun DMS cross-instance query service

Aliyun DMS (Data management) cross-instance query service not only covers the scenario of associated query of heterogeneous data sources, but also solves the problem of cross-region and cross-cloud database associated query. Not only that, but query performance has been greatly optimized so that most queries can be completed in milliseconds. Users can implement cross-query across instances through standard SQL without data aggregation.

DBLink

For those familiar with Oracle, we can create a DBLink on the currently logged Oracle database to point to another remote Oracle database table. In the cross-instance query service, the concept of DBLink is redefined, which is a virtual connection to any database instance of the user and an alias of the database instance. For example, for MySQL, DBLink corresponds to IP /port one-to-one. With DBLink, SQL access to any data source can be achieved.

Compatible with standard SQL

Cross-instance queries can be implemented using standard SQL statements. Cross-instance query services are also highly compatible with MySQL, supporting the MySQL protocol, as well as a variety of common functions and syntax. You can connect to the cross-instance query service through the JDBC/ODBC driver; You can also use various MySQL GUI tools to manage various data sources.

Serverless architecture

Cross-instance query is a serverless online database associated query service. Users do not need to pre-purchase computing resources, no maintenance resources, no operation and upgrade costs, anytime, anywhere.

High performance low latency

The underlying cross-instance query service is based on the powerful MPP computing engine, which continuously optimizes SQL queries, including pushdown, join algorithm, execution plan cache, Meta cache, local scheduling, connection pooling and other technologies. Currently, single-table queries and multi-table associative queries across instances can be performed in milliseconds.

Support for multiple relational databases

Currently, it supports MySQL, SQLServer, PostgreSQL and other relational databases.

Supports SQL access to NoSQL

In addition to relational databases, cross-instance queries also support SQL access to NoSQL databases such as Redis. Because SQL syntax is supported, associated queries between RDBMS and NoSQL can also be implemented. Yes, you read that right, a single SQL can implement associated query between MySQL and Redis.

Supports cross-geographic and hybrid cloud queries

When an enterprise reaches a certain stage of development and the number of users and services keeps increasing, the original capacity of a single room cannot meet the requirements of service development. In combination with factors such as DISASTER recovery (Dr) and high availability (HA), the enterprise usually chooses cross-region deployment, also known as unitary deployment. At the same time, many enterprises also need to expand their business overseas and provide better experience for foreign users through local deployment. The problem with this kind of horizontal split is how to make a unified summary and associated query for the global business data.

With the DMS cross-instance query service, no matter which region of Ali Cloud your database instance is deployed in, you can query data of all regions in a unified manner without cross-region data migration. In addition to Ali Cloud RDS, it also supports various databases that users deploy on Ali Cloud ECS. Moreover, if your database is deployed in the local IDC room, or even other cloud vendors, cross-instance query services can be used to achieve cross-instance associated query in these hybrid cloud scenarios.

Cross-library/cross-instance Join does not have to rely on middleware