MySQL & MariaDB

Storage Engines

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html Compare: Table 15.1 Storage Engines Feature Summary

Feature

MyISAM

Memory

InnoDB

Archive

NDB

Storage limits

256TB

RAM

64TB

None

384EB

Transactions

No

No

Yes

No

Yes

Locking granularity

Table

Table

Row

Row

Row

MVCC

No

No

Yes

No

No

Geospatial data type support

Yes

No

Yes

Yes

Yes

Geospatial indexing support

Yes

No

Yes[a]

No

No

B-tree indexes

Yes

Yes

Yes

No

No

T-tree indexes

No

No

No

No

Yes

Hash indexes

No

Yes

No[b]

No

Yes

Full-text search indexes

Yes

No

Yes[c]

No

No

Clustered indexes

No

No

Yes

No

No

Data caches

No

N/A

Yes

No

Yes

Index caches

Yes

N/A

Yes

No

Yes

Compressed data

Yes[d]

No

Yes[e]

Yes

No

Encrypted data[f]

Yes

Yes

Yes

Yes

Yes

Cluster database support

No

No

No

No

Yes

Replication support[g]

Yes

Limited[h]

Yes

Yes

Yes

Foreign key support

No

No

Yes

No

Yes[i]

Backup / point-in-time recovery[j]

Yes

Yes

Yes

Yes

Yes

Query cache support

Yes

Yes

Yes

Yes

Yes

Update statistics for data dictionary

Yes

Yes

Yes

Yes

Yes

CREATE TABLE t1 (i INT) ENGINE = INNODB;
InnoDB: default in MySQL 5.7.
transaction-safe (ACID compliant):commit, rollback, and crash-recovery
row-level locking (without escalation to coarser granularity locks)
Oracle-style consistent nonlocking reads
clustered indexes to reduce I/O for common queries based on primary keys
FOREIGN KEY referential-integrity constraints
MyISAM:
Table-level locking.
often used in read-only or read-mostly workloads.
Merge:
logically group a series of identical MyISAM tables and reference them as one object.
Memory or HEAP:
Its use cases are decreasing:
InnoDB with its buffer pool memory area
NDBCLUSTER provides fast key-value lookups for huge distributed data sets
NDB or NDBCLUSTER: highest possible degree of uptime and availability.
Federated: link separate MySQL servers to create one logical database
CSV: Its tables are really text files with comma-separated values.
Archive: compact, unindexed tables
Blackhole:
not store data, Queries always return an empty set.
can be used in replication configurations
Example: illustrates how to begin writing new storage engines.

MySQL Cluster

http://severalnines.com/blog/mysql-docker-introduction-docker-swarm-mode-and-multi-host-networking https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-ndb-innodb-engines.html

JSON

MySQL 5.7+

https://dev.mysql.com/doc/refman/5.7/en/json.html

CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
SELECT JSON_ARRAY('a', 1, NOW());
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
SET @j = JSON_OBJECT('key', 'value');
SELECT @j;
# escape quote character
'{"mascot": "... \\"Sakila\\"."}'
JSON_OBJECT("mascot", "... \"Sakila\".")
JSON_OBJECT('mascot', '... "Sakila".') # NO_BACKSLASH_ESCAPES
# JSON values is case sensitive
SELECT CAST('null' AS JSON); # `null`, `true`, and `false` always lowercase
SELECT col->"$.mascot" FROM qtest; # "... \"Sakila\"."
SELECT sentence->>"$.mascot" FROM facts; # ... "Sakila".
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

MariaDB 10.2+

https://mariadb.com/resources/blog/json-mariadb-102

CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(9,2) NOT NULL,
stock INTEGER NOT NULL,
attr VARCHAR(1024),
CHECK (attr IS NULL OR JSON_VALID(attr)));
INSERT INTO products VALUES(NULL, 'Blouse', 17, 15, '{"colour": "white"}');
UPDATE products SET attr = JSON_REPLACE(attr, '$.colour', 'red') WHERE name = 'Blouse';
ALTER TABLE products ADD attr_colour VARCHAR(32) AS (JSON_VALUE(attr, '$.colour'));
CREATE INDEX products_attr_colour_ix ON products(attr_colour);
EXPLAIN SELECT * FROM products WHERE attr_colour = 'white'; # verify index