Technology sharing | MySQL is at the forefront of NoSQL

Time:2020-2-26

Author: Yang Taotao

background

The content of the article is also a problem from customers.

Problem Description:

We are now all MySQL in the background and familiar with MySQL in front-end development. But now there are new businesses coming in. The data model is very flexible and has no fixed table structure. At first, we considered the memcached API of MySQL. But to be honest, it’s not easy to use it completely. In this part, we have considered NoSQL database, but if we want to deploy NoSQL database, we have to go through a series of tests. Do you have a good solution for MySQL?

My answer:

Yes, absolutely!MySQL x API can completely replace NoSQL database. And this function is available from MySQL 5.7. MySQL 5.7 has been released for several years and can be used directly.

MySQL X

To use MySQL x protocol, the parameter mysqlx port must be added to the configuration file when MySQL is started

#my.cnf
[mysqld]
Port = 3305 -- original traditional MySQL port
MySQL x_port = 33050 -- MySQL x protocol port

Compare mongodb, the hottest NoSQL database, with MySQL. Let’s start with a simple example of inserting a table:

Insert 10 records into mongodb’s table F1 (field x y z),

# mongodb shell
> use ytt
> switched to db ytt

#Define a JS array,
> var c1 = [] 
> for (var i = 0;i < 10;i++){
> ... c1.push({'x':i,'y':i*2,'z':i+100}) 
> ... }
> 10
 
#Insert the array just now
> db.f1.insert(c1);
> BulkWriteResult({
> "writeErrors" : [ ],
> "writeConcernErrors" : [ ],
> "nInserted" : 10,
> "nUpserted" : 0,
> "nMatched" : 0,
> "nModified" : 0,
> "nRemoved" : 0,
> "upserted" : [ ]
> })

#Now the number of records is 10
> db.f1.count()
> 10

#Take out the first
> db.f1.find().limit(1).pretty();
{
    "_id" : ObjectId("5e0066a54af3d32384342edd"),
    "x" : 0,
    "y" : 0,
    "z" : 100
}

The implementation of mongodb in MySQL is very simple.

# mysql-shell
#Create a table F1
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.createCollection('f1')
<Collection:f1>

#Still define the same array C1
MySQL  ytt-pc:33050+ ssl  ytt  JS > var c1 = []
MySQL  ytt-pc:33050+ ssl  ytt  JS > for (var i = 0;i<10;i++) { c1.push({'x':i,'y':i*2,'z':i+100})}
10

#Insert the record just now
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.add(c1)
Query OK, 10 items affected (0.0058 sec)    
Records: 10  Duplicates: 0  Warnings: 0

#View total rows
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.count();
10

#Take out a record
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find().limit(1);
{
    "x": 0,
    "y": 0,
    "z": 100,
    "_id": "00005e006018000000000000000b"
}
1 document in set (0.0003 sec)

That’s a basic insert. Let’s look at other operations, such as update and delete

# mysql-shell
#Update the record with field x value of 1 to MySQL 

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify('x=1').set('x','mysql')
Query OK, 1 item affected (0.0047 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#Retrieve the record of x ='mysql '
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find("x='mysql'")
{
    "x": "mysql",
    "y": 2,
    "z": 101,
    "_id": "00005e006018000000000000000c"
}
1 document in set (0.0006 sec)

#Update field y value to 'dble' without where filter condition, that is, update the whole table
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify("true").set('x','dble')
Query OK, 10 items affected (0.0075 sec)    
Rows matched: 10  Changed: 10  Warnings: 0

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find();
{
    "x": "dble",
    "y": 0,
    "z": 100,
    "_id": "00005e006018000000000000000b"
}
...
{
    "x": "dble",
    "y": 18,
    "z": 109,
    "_id": "00005e0060180000000000000014"
}
10 documents in set (0.0010 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > 
 
#For example, now we need to embed the C1 array into field x, what should we do?
 
#Make x an empty array
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify("true").set("x",[])
Query OK, 10 items affected (0.0048 sec)
Rows matched: 10  Changed: 10  Warnings: 0

#Update field x with array push

 MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify("true").arrayAppend("$.x",c1);
Query OK, 10 items affected (0.0064 sec)

Rows matched: 10  Changed: 10  Warnings: 0

#View the results just updated (simplified display)
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find()
{
    "x": [
        [
            {"x": 0, "y": 0, "z": 100},
            { "x": 1,"y": 2,"z": 101},
            {"x": 2,"y": 4,"z": 102},
            {"x": 3,"y": 6,"z": 103},
            {"x": 4,"y": 8,"z": 104},
            {"x": 5,"y": 10,"z": 105},
            {"x": 6,"y": 12,"z": 106},
            {"x": 7,"y": 14,"z": 107},
            {"x": 8, "y": 16,"z": 108},
            {"x": 9,"y": 18,"z": 109}
        ]
    ],
    "y": 0,
    "z": 100,
    "_id": "00005e006018000000000000000b"
},
...    
10 document in set (0.0003 sec)

#Look at the delete operation

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.remove('true');
Query OK, 10 items affected (0.0032 sec)

#Data cleared
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find()
Empty set (0.0003 sec)

The most important thing is the query. The query is filtered through the find () method. Look at the following example.

#mysql-shell
#Insert 10W records again
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.remove('true');
Query OK, 10 items affected (0.0043 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > var c1 = []
MySQL  ytt-pc:33050+ ssl  ytt  JS > for (var i =0;i< 100000;i++){c1.push({'x':i,'y':2*i,'z':i+100})}
100000

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.add(c1)
Query OK, 100000 items affected (2.5686 sec)
Records: 100000  Duplicates: 0  Warnings: 0

#Take out the records whose filter conditions are x > 100 and x < 200, and output the first two records in reverse order
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x >100 and x < 200').sort(' x desc').limit(2)
{
    "x": 199,
    "y": 398,
    "z": 299,
    "_id": "00005e00601800000000000000e6"
}
{
    "x": 198,
    "y": 396,
    "z": 298,
    "_id": "00005e00601800000000000000e5"
}
2 documents in set (0.0766 sec)

#Query time 0.0766 seconds

#Adding an index to field x is also very convenient
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.createIndex('idx_x',{fields:[{'field':'$.x','type':'int'}]});
Query OK, 0 rows affected (0.2854 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x >100 and x < 200').sort(' x desc').limit(2)
{
    "x": 199,
    "y": 398,
    "z": 299,
    "_id": "00005e00601800000000000000e6"
}
{
    "x": 198,
    "y": 396,
    "z": 298,
    "_id": "00005e00601800000000000000e5"
}
2 documents in set (0.0004 sec)
#Query time 0.0004 seconds


#It's easier to execute blocks of things, instead using session objects
#Similar to start transaction statement
MySQL  ytt-pc:33050+ ssl  ytt  JS > session.startTransaction()
Query OK, 0 rows affected (0.0002 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.remove('x=1')
Query OK, 1 item affected (0.0004 sec)
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x=1')
Empty set (0.0004 sec)

#Similar to rollback statement
MySQL  ytt-pc:33050+ ssl  ytt  JS > session.rollback()
Query OK, 0 rows affected (0.0014 sec)

#This record is still there
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x=1')
{
    "x": 1,
    "y": 2,
    "z": 101,
    "_id": "00005e0060180000000000000020"
}
1 document in set (0.0004 sec)

summary

I’ve given several classic examples to illustrate that MySQL can be used as a NoSQL database directly, and it has the functions it should have. For example, add, delete, modify, query, index, transaction, etc. It can even replace mongodb completely.