How X Plugin Works Under the Hood

How X Plugin Works Under the Hood

PREVIOUS POST
NEXT POST

X PluginIn this blog post, we’ll look at what MySQL does under the hood to transform NoSQL requests to SQL (and then store them in InnoDB transactional engine) when using the X Plugin.

X Plugin allows MySQL to function as a document store. We don’t need to define any schema or use SQL language while still being a fully ACID database. Sounds like magic – but we know the only thing that magic does is make planes fly! 🙂

Alexander already wrote a blog post exploring how the X Plugin works, with some examples. In this post, I am going to show some more query examples and how they are transformed.

I have enabled the slow query log to see what it is actually being executed when I run NoSQL queries.

Creating our first collection

We start the MySQL shell and create our first collection:

What is a collection in SQL terms? A table. Let’s check what MySQL does by reading the slow query log:

As we correctly guessed, it creates a table with two columns. One is called “doc” and it stores a JSON document. A second column named “_id” and is created as a virtual column from data extracted from that JSON document. _id is used as a primary key, and if we don’t specify a value, MySQL will choose a random UUID every time we write a document.

So, the basics are clear.

  • It stores everything inside a JSON column.
  • Indexes are created on virtual columns that are generated by extracting data from that JSON. Every time we add a new index, a virtual column will be generated. That means that under the hood, an alter table will run adding the column and the corresponding index.

Let’s run a getCollections that would be similar to “SHOW TABLES” in the SQL world:

This is what MySQL actually runs:

This time, the query is a bit more complex. It runs a query on information_schema.tables joining it, with information_schema.columns searching for tables that have “doc” and “_id” columns.

Inserting and reading documents

I am going to start adding data to our collection. Let’s add our first document:

In the background, MySQL inserts a JSON object and auto-assign a primary key value.

Ok, this is supposed to be schemaless. So let’s add someone else using different fields:

Same as before, MySQL just writes another JSON object (with different fields):

Now we are going to read the data we have just inserted. First, we are going to find all documents stored in the collection:

MySQL translates to a simple:

And this is how filters are transformed:

It uses a SELECT with the WHERE clause on data extracted from the JSON object.

Updating documents

Thrall decided that he doesn’t want to belong to the Horde anymore. He wants to join the Alliance. We need to update the document:

MySQL runs an UPDATE, again using a WHERE clause on the data extracted from the JSON. Then, it updates the “Faction”:

Now I want to remove my own document:

As you can already imagine, it runs a DELETE, searching for my name on the data extracted from the JSON object:

Summary

The magic that makes our MySQL work like a document-store NoSQL database is:

  • Create a simple InnoDB table with a JSON column.
  • Auto-generate the primary key with UUID values and represent it as a virtual column.
  • All searches are done by extracting data JSON_EXTRACT, and passing that info to the WHERE clause.

I would define the solution as something really clever, simple and clean. Congrats to Oracle! 🙂

PREVIOUS POST
NEXT POST

Share this post

Comments (9)

  • Paul DuBois Reply

    “I have enabled the slow query log to see what it is actually being executed when I run NoSQL queries.”

    Why would you enable the slow query log (rather than the general query log)? Every NoSQL query is slow enough to show up in the slow log? 🙂

    September 16, 2016 at 7:05 pm
    • Miguel Angel Nieto Reply

      Hehe. To avoid all the “connect – disconnect – connect – disconnect” noise 😛

      September 16, 2016 at 7:07 pm
    • Manjot Singh Reply

      we like to set long_query_time=0; this also works better with a lot of tools

      September 22, 2016 at 5:49 pm
  • jk Reply

    thanks!

    September 18, 2016 at 4:56 am
  • Alex Reply

    Seems that using a UUID without including caveats would negatively impact performance rather quickly .

    http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

    September 19, 2016 at 6:16 pm
  • Vasiliy Lyk'yanchikov Reply

    Hi Miguel,
    Good post, thanks for that. I want to translate it on russian language. Please let me know if you have any objection.

    December 29, 2016 at 9:12 am

Leave a Reply