07 Table Access Design - Should You Choose SQL or NoSQL #

So far, I have guided you through the selection of field types and the physical storage design of tables. In this lesson, we will continue to learn about table access design. With this, the selection of field types + physical storage design + table access design completes all aspects of table structure design.

In the previous 6 lessons, I demonstrated accessing tables through SQL. However, starting from MySQL version 5.6, there is support for other access methods besides SQL, such as NoSQL. You can even customize MySQL to become a high-concurrency Key-Value (KV) database or document database.

In today’s lesson, I will give you a global perspective on the access methods of tables in the MySQL database and their respective use cases. I hope you can learn something from it.

Access Methods of Tables in MySQL #

SQL is a universal interface for accessing databases. Although there are many types of databases, the SQL used in databases is similar because SQL has standards such as SQL92 and SQL2003.

Although some databases extend support for syntax outside of the SQL standard, 90% of the syntax is compatible. Therefore, the learning cost of different databases at the SQL level is relatively low. Also, because of the above reasons, the migration cost from one relational database to another is not high. For example, when migrating from Oracle to MySQL at IOE, SQL syntax is usually not a problem.

Before MySQL 8.0, many students complained about MySQL’s support for the SQL standard. However, in the current version 8.0, MySQL’s support for SQL syntax has become better and even surpasses that of the commercial database Oracle in some areas.

The above figure shows the assessment of different databases’ support for SQL. It can be seen that MySQL 8.0 is very comprehensive in this aspect, especially in terms of supporting the functionality of JSON_TABLE.

Usually, MySQL databases are used in OLTP online systems, which do not require complex SQL syntax support. However, the complete SQL support of MySQL 8.0 means that MySQL will gradually patch its shortcomings in the OLAP business field in the future. Let’s wait and see.

Of course, you are already familiar with accessing tables through SQL, and that is not the focus of this lesson. Next, I will specifically introduce how MySQL can access table data through NoSQL methods.

Let’s first take a look at the different table access methods currently supported in MySQL:

Three table access methods in MySQL

As shown in the figure, besides the standard SQL access, MySQL 5.6 and later versions also support accessing table data through the Memcached communication protocol. In this case, MySQL can be used as a KV database. In addition, starting from MySQL 5.7, it also supports accessing table data through the new MySQL X communication protocol. In this case, MySQL can be used as a document database.

However, regardless of the NoSQL access method used, the accessed data is still stored in tables. SQL and NoSQL are bound by a certain mapping relationship.

Compared to traditional NoSQL databases (such as Memcached and MongoDB), MySQL’s access method is more flexible. It can guarantee performance through simple NoSQL interfaces, while also enriching data queries through SQL. In addition, MySQL’s mature transaction features and high availability solutions can make up for the shortcomings of NoSQL databases in this regard.

Next, let’s take a look at the NoSQL access method based on the Memcached protocol.

Accessing Tables Through Memcached Protocol #

Starting from MySQL version 5.6, support for accessing tables as KV data using the Memcached plugin is available. In this case, MySQL can be viewed as a Memcached KV database.

Data access is no longer through the SQL interface but through common operations in a KV database, such as get, set, and incr requests.

But why access data through the KV method? This is because for some businesses, accessing the database is essentially a KV operation. For example, user login systems are mostly used for information confirmation. In these cases, most SQL operations query data through primary keys or unique indexes, like:

SELECT * FROM User WHERE PK = ?

In a highly concurrent system with massive accesses, accessing these tables through SQL is fast because it uses the primary key index. However, SQL parsing, even if it is soft parsing, still takes a considerable amount of time, which limits the performance of a single database instance.

Based on Memcached’s KV access, SQL parsing can be bypassed, and accessing data stored in the InnoDB engine directly through the mapping relationship can greatly improve the overall performance of the database without incurring additional costs.

To enable Memcached protocol access to MySQL, two things need to be done:

• Enable the Memcached plugin.

• Mapping between configuration tables and KV.

The specific operations are as follows:

-- Install the mapping table

mysql> source MYSQL_HOME/share/innodb_memcached_config.sql

-- Install the plug-in, which will start port 11211 by default

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

After executing the above operations, a new database innodb_memcache will be created, and the table containers in it is the KV mapping table that needs to be configured. If the common primary key query SQL for the business is as follows, where the column user_id is the primary key:

SELECT user_id,cellphone,last_login
FROM test.User
WHERE user_id = ?

Then we can insert a record into the containers table:

INSERT INTO containers
VALUES ('User','test','user_id','user_id|cellphone|last_login','0','0','0','PRIAMRY')

The above mapping relationship means that it is accessed through Memcached’s KV method. In essence, it accesses the key value through the PRIMARY index. The key is user_id, and the returned value is a string consisting of the columns user_id, cellphone, and last_login combined with the delimiter “|”.

Finally, by comparing the performance between SQL and KV, it can be found that accessing through KV is much faster. The results on my test server are as follows:

From the test results, it can be seen that the KV access method based on Memcached is 54.33% faster than the traditional SQL method, and CPU overhead is even 20% lower.

Of course, the above operations only access the table User as KV. If you want to access other tables through KV, you can continue to configure them in the containers table. But when using, be sure to first specify the table you want to access through the GET command:

# Python pseudo code

mc = Client('127.0.0.1:11211')

mc.get('@@User') # Read the mapping table User

mc.get('key1')

mc.get('@@sbtest1') # Read the mapping table sbtest1

mc.set('sb1_key1','aa|bbb|ccc')

......

Another scenario for using the Memcached Plugin is for users who originally used the native Memcached KV database. These users can consider migrating the Memcached database to MySQL. The benefits of doing so are:

• Performance accessed through MySQL is better than native Memcached because the database concurrency optimization is better.
• Storage can be persisted, supporting transactions, and providing better data consistency and security.
• Using MySQL replication technology can compensate for the lack of data replication support in native Memcached.

Accessing Tables through X Protocol #

Starting with MySQL 5.7, native support for JSON binary data types is provided, and tables can be mapped to a JSON document. Additionally, MySQL provides NoSQL access methods such as X Protocol. As a result, MySQL can now be transformed into an SQL & NoSQL document database.

Compared to MongoDB, the advantages of transforming MySQL into a document database are:

As can be seen, apart from MySQL currently not supporting data sharding, MySQL has greater advantages in other aspects, especially as MySQL stores JSON data in two-dimensional tables, enabling it to provide document database functionality. This allows for more complex dimensional queries using SQL, especially when combined with MySQL 8.0’s features such as Common Table Expression (CTE) and Window Function, which cannot be natively implemented in MongoDB.

Unlike the Memcached Plugin, the X Plugin is enabled by default in MySQL. You can then access the data in MySQL using the new X Protocol, with the default port being 33060. You can view the configuration related to the X Plugin using the following command:

mysql> SHOW VARIABLES LIKE '%mysqlx%';

To manage MySQL using the X Protocol, you need to use the new MySQL Shell command, which is not installed by default and needs to be installed separately. You can download it from the following address: https://dev.mysql.com/downloads/shell/. After installation, you can access the MySQL database using the new X Protocol through the mysqlsh command:

root@MBP-Windows:# mysqlsh root@localhost/test

The X Protocol supports managing and accessing MySQL using JS, Python, and SQL. You can refer to the official documentation for specific operations.

If developers want to manage document data using the X Protocol, they also need to download the new MySQL Connector and import the new X driver library, such as the Python driver:

import mysqlx

# Connect to server on localhost

session = mysqlx.get_session({

    'host': 'localhost',

    'port': 33060

})

schema = session.get_schema('test')

# Use the collection 'my_collection'

collection = schema.get_collection('my_collection')

# Specify which document to find with Collection.find()

result = collection.find('name like :param').bind('param', 'S%').limit(1).execute()

# Print document

docs = result.fetch_all()

print('Name: {0}'.format(docs[0]['name']))

session.close()

Summary #

In this tutorial, I introduced accessing tables in MySQL using SQL, Memcache protocol, and X Protocol. This allows us to transform MySQL into a relational database, KV database, or document database, with the underlying data stored in the InnoDB engine.

If you are still using Memcached or MongoDB, consider migrating the data to MySQL. This way, you can use advanced features of the InnoDB storage engine such as high concurrency, transaction safety, data replication, while keeping compatibility with your existing business.

You might ask why KV databases and document databases are not used separately. This is because enterprise data center departments require technology stacks to be as consolidated as possible. This approach provides greater benefits in terms of stability, personnel training, and long-term architecture.