This is the third day of my participation in the August More text Challenge. For details, see:August is more challenging

Preface: This is my third article on MySQL. The article is relatively shallow, suitable for beginners. The first layer of the MySQL logical structure in the last article is the connection layer that this article will expand on. This level is often overlooked, as previously it was a simple matter of copying several configuration files. I’ve been getting to know it lately.

I. Connection specification

For database clients to connect to the server, they generally need a set of protocols and corresponding apis to call.

In Java, for example, you use the JDBC driver to connect to MySQL

1. JDBC(Java DataBase Connectivity)

Java database connection: Java defines the specification for connecting to/accessing different relational databases and provides the interface through which they execute SQL commands. It can also be called a baseline on which developers can write database applications and connection pool configurations.

2. ODBC(Open Database Connectivity)

Open database connection: it is produced to solve The data sharing between heterogeneous databases, and has become The Main part of The Windows Open System Architecture(WOSA) and a database access interface standard based on Windows environment. ODBC provides a unified interface for heterogeneous database access, allowing applications to access data managed by different DBMSS using SQL as the data access standard. Allows applications to manipulate data in DB directly, without changing with DB.

ODBC is an integral part of Windows Open Services, which provides a standard API for accessing databases, independent of specific programming languages. ODBC was later listed as part of the SQL standard.

Like PyODBC in Python, pyODBC is a Python wrapper for ODBC that allows Python on any platform to have the ability to use THE ODBC API.

3. JDBC-ODBC

Jdbc-odbc bridge: If the database is programmed with an ODBC driver, JDBC is currently required. The jDBC-ODBC bridge is required.

This way is actually connected to the database using ODBC to drive, and then through JDBC to call ODBC. Although the goal is achieved, but equivalent to an extra middleman to earn the difference. This method is only used at specific times.

Connection pool

When connecting to the server through the above connection drivers, each connection has a separate thread. This single thread will serve all queries/writes for the connection. The server’s CPU runs the thread randomly.

Database connection pooling is a feature supported by the MySQL5.5 update that allows a large number of connections to be served by a small number of threads without the need to create or destroy threads for each new connection.

Common database thread pools include DBCP, C3P0, and Druid. The first two ages together should be older than the age of each of you.

1. DBCP (Database Connection Pool)

DBCP is an open source Apache project that Tomcat uses for its data source and relies on the database connection pool of the Jakarta Commons-Pool object pool mechanism. DBCP 2 is based on shared pool 2 and offers better performance, JMX support, and many other new features compared to DBCP 1.x.

DBCP supports all Java versions 1.3 to 8.0 from 1.3 to 2.7.

  • DBCP cannot automatically reclaim idle connections
  • The maximum number of connections is configured. If the maximum number of connections is exceeded, all connections will be disconnected.

2. C3P0

C3p0 is an open source JDBC connection pool, which is used by Spring, Hibernate and other open source projects. It also supports the JDBC3 specification and JDBC2 standard extensions.

  • The maximum connection time configuration is provided. If the maximum connection time is exceeded, the connection will be disconnected.

3. Druid

Druid is taobao Pay treasure dedicated database connection pool incubation products, it is in addition to the connection pool. More provides a lot of extensions.

Including but not limited to: log monitoring, ping detection, cache memory optimization, SQL filtering. And support all JDBC-compliant databases.

Iii. Security

When the application connects to the MySQL server through the driver, it is validated. The authentication information includes the original host information and password. For SSL connections, X.509 can be used for certificate verification.

Don’t think that’s the end of the verification

When the client connects successfully, the server verifies whether the client has permission to perform certain operations. These specific configurations can be configured in the database connection pool above. Of course, too much validation can also affect SQL performance.

Resources for this article

  • High Performance MySQL
  • Introduction to Open Source China