Comparison of the middleware of the sub-database sub-tables

(i) Key issues

1. Read and write separation

2. Sub-database sub-table

3. Categories

• LIB Library

1) business directly to the database, less a layer of proxy more efficient

2) Single point problem with LVs without proxy

• Proxy

1) Unified management of all connections to the database, connection multiplexing

2) Basic Query function abstraction, reduce code coupling

3) Easy to implement monitoring, data migration, connection management and other functions

(ii) SHARDING-JDBC (open source, Lib)

When applying framework ddframe, the database horizontal shard framework separated from the relational database module Dd-rdb

• Function

1) Provision of services in the form of a jar package

2) Flexible sharding, supports equal sign, between, in and other multi-dimensional shards

3) SQL parsing, support aggregation, grouping, sorting, limit, or, etc.

• Structure

• Characteristics

1) Shard rule: Policy custom, plural number of shards

2) JDBC Specification rewrite: Encapsulated for DataSource, Connection, Statement, PreparedStatement, and ResultSet interfaces

3) SQL parsing: Druid

4) SQL Overwrite: Modify the name of the real table with the logical table name; Replace unsupported features such as: Avg->sum and Count

5) SQL Routing: single-table routing, binding table routing, Cartesian product routing

6) SQL execution: multithreaded concurrent execution SQL

7) Result Merge: Traverse class, Sort class (merge sort), aggregate class (comparison type, cumulative type, average type), grouping class

(c) Mycat (open source, proxy)

Community enthusiasts in Ali Cobar on the basis of two times to develop, solve the Cobar some problems, and added new features.

• Function

1) comply with MySQL native protocol

2) automatic failover based on heartbeat

3) Support read/write separation, support MySQL master/slave

4) Support aggregation of Sum, COUNT, Max, etc., support cross-Library paging

5) Support Service downgrade

6) Security, IP Whitelist, SQL injection attack interception, prepare precompilation

• Principle

Interception: Shard Analysis, routing analysis, read-write separation analysis, cache analysis

(iv) Dbproxy (open source, proxy)

Improvements to Atlas have created new high-reliability, high-availability enterprise-class database middleware Dbproxy

• Function

1) Read/write separation

2) Load Balancing

3) Slave Fault sensing & removal

4) Connection Pool

5) Custom SQL interception & filtering

6) Traffic Grouping & control

7) Monitoring Status

(v) Atlas

360 team based on MySQL proxy to the LUA with C rewrite, in high concurrency will often hang out.

(vi) Oneproxy (non-open source, proxy)

Database middleware based on MySQL protocol. Developed with C, focusing on performance and stability.

• Function

Transparent SQL routing (enabling clustered deployments of back-end MySQL databases) and traffic analysis (provides rich performance monitoring capabilities for upper-level applications and underlying database clusters).

1) Reuse database connection, reduce the number of database concurrent connections

2) Instant discovery and culling of unavailable backend nodes, forwarding application requests for efficient fault isolation

3) built-in daemon mode and HA VIP mechanism to ensure high availability

4) Query statement detach, merge across Shard result set, execute SQL in parallel based on Shard

5) Read/write separation

6) Perform security checks on SQL statements and reject dangerous DDL operations

7) Set the SQL request frequency of the front end application and the back-end database respectively, realize the QoS control

8) Real-time transparent analysis of traffic, real-time statistics of SQL and transaction uptime, analysis of the SQL structure of transactions

• Characteristics

Language: c&c++ Development

Network events: Libevent framework

Memory allocation: Jemalloc optimization

QPS: Single Instance support 40W

Connection pooling: Transparent connection pooling, with MYSQK Enterprise Edition connection pool effect

Master Preparation: Transparent read-write separation routing

Security: SQL request category, request IP, real-time check & intercept for each SQL request

Traffic Analysis: IP dimension, transaction dimension presentation & Analysis

Stability: Built-in high-availability, can also be combined with zookeeper, etc.

(vii) Vitess

Used on YouTube, complex architecture

Comparison of the middleware of the sub-database sub-tables