SHARDING-JDBC Source Learning (a) Introduction

Background

For large-scale Internet applications, the number of record rows in a database single table may reach tens or even billion, and the database faces extremely high concurrent access. The MySQL schema with master-slave replication mode can only extend the reading of the database, while the write operation on the database is centralized on master, and the single master mount slave cannot be infinitely more. The number of slave is limited by master capability and load. As a result, the throughput capacity of the database needs to be further expanded to meet the needs of high concurrent access and massive data storage!

Sub-table

The first thing we need to do is to reduce the number of records in a single table in order to reduce the time required for data query and improve the throughput of the database, which is called a sub-table.

The Sub-table is divided into vertical and horizontal sub-table
Vertical Sub-table: Table records are not many, but the field is very long, the table occupies a large space, when retrieving the table need to execute a large number of Io, severely reduced performance. You need to split the large field into another table, and the table is a one-to-a-kind relationship with the original table.
Level sub-table: Table records a lot, seriously affect the query speed. In this case, you need to split a table into multiple tables with the same fields, and distribute the records to each table to reduce the number of records in a single table.

Sub-Library

The Sub-table can solve the problem that the query efficiency of the single-table data is too large, but it can not bring the quality improvement to the concurrency processing ability of the database. In the face of high concurrent read and write access, when the database master server cannot host write operation pressure, no matter how to extend the slave server, there is no point at this time. Therefore, we must change a way of thinking, the database is split, thereby improving the database write ability, this is called the Sub-Library!

Sub-Library + sub-table

Sometimes the database may face the pressure of high concurrent access, but also face the storage problem of massive data, it is necessary to adopt both the sub-table strategy and the sub-Library strategy, so as to expand the concurrent processing capability of the system and improve the query performance of the single table, which is called the sub-database sub-table.

Common Sub-Library sub-table middleware

1. Proxy sharding, currently modified by Cobar,mycat,drds,atlas, the origins of these products are generally mysqlproxy or ameoba, characterized by the MySQL protocol is basically compatible, the business does not need to do too much modification, The disadvantage is that the algorithm of the sub-database is very bad, the business to do their own heap configuration
2. JDBC Middleware sharding, which is similar to the Protocol, is to implement the service for a middleware, the benefit is that the protocol lost time can be mended, the disadvantage is that only Java can be used, open source has when the SHARDING-JDBC
3.mysql Ndbcluster and fabric, this is the MySQL engine level to do sharding, directly with the MySQL protocol layer and plan generation, the benefit is that the native protocol layer is hundred percent support, transaction with MySQL XA support is also so-so, The downside is the single-machine engine, which cannot support big data SQL

Detail https://www.cnblogs.com/wangzhongqiu/p/7100332.html

Project Introduction

SHARDING-JDBC is an open source project that belongs to the lightweight Java framework, uses a direct-attached database of clients, provides services in the form of a jar package, does not use the middle tier, no additional deployment, and no other dependencies.

GitHub Address: Https://github.com/shardingjdbc/sharding-jdbc

Document: Http://shardingjdbc.io/docs/00-overview

List of features available on the website:)

1The Partition table SQL parsing function is perfect, support aggregation, grouping, sorting, limit,top and other queries, and support cascading table and Cartesian product table query support internal and external connection Query Shard policy is flexible, can support=, Between,in and other multi-dimensional shards, can also support multi-shard key sharing, as well as custom shard policy hint-based forced sub-database sub-table routing2Read/write separations independently use read/write separation to support SQL pass-through, a master-slave read-write separation configuration, can be used with the sub-database table using hint-based forced master Seobinggo3. Flexible transaction maximum effort service-type transaction TCC-type Transaction (TBD)4Distributed primary key unified distributed ID generator based on time series5. Compatibility can be applied to any Java-based ORM framework such as: JPA, Hibernate, Mybatis, Spring jdbc template or directly using JDBC can be based on any third-party database connection pool, such as: DBCP, C3P0, BONECP, Druid, etc. can theoretically support any database that implements the JDBC specification. Currently supports Mysql,oracle,sqlserver and PostgreSQL6Flexible configuration Javayamlinline expression Spring namespace Spring Boot starter7. Distributed governance capabilities (2. 0 new features) Configure centralization and dynamics to support dynamic switching of data sources, tables, and sharding policies (2.0.0. M1) Client database governance, data source failure automatic switching (2.0.0. M2) APM information output based on the Open Tracing Protocol (2.0.0. M3)

Overall architecture diagram

Sharding-jdbc Source Learning (a) Introduction