MySQL 5.1 |
MariaDB 5.1 (link), 5.2 (link) and 5.3 (link) |
- Partitioning
- Row-based replication
- Plugin API
- Event scheduler.
- Server log tables.
- Upgrade program
mysql_upgrade .
- Improvements to
INFORMATION_SCHEMA .
- XML functions with
Xpath support.
|
MariaDB 5.1
- Storage Engines
- Aria (Crash-safe MyISAM)
- XtraDB plugin (Branch of InnoDB)
- PBXT (transactional Storage Engine)
- FederatedX (replacement for Federated).
- Performance
- Faster
CHECKSUM TABLE .
- Character Set conversion improvement/elimination.
- Speed-up of complex queries using Aria SE for temporary tables.
- Optimizer: Table elimination.
- Upgrade from MySQL 5.0 improved.
- Better testing.
- Microseconds precision in
PROCESSLIST .
MariaDB 5.2
- Storage Engines
- OQGRAPH (Graph SE)
- SphinxSE (Full-text search engine)
- Performance
- Segmented MyISAM key cache (instances)
- Group Commit for Aria SE
- Virtual columns
- Extended user statistics
- Storage Engine specific
CREATE TABLE
- Enhancements to
INFORMATION_SCHEMA.PLUGINS table
MariaDB 5.3
- Performance
- Subquery Optimization
- Semi-join subquery optimizations
- Non-semi-join optimizations
- Subquery Cache
- Subquery is not materialized any more in
EXPLAIN
- Optimization for derived tables and views
- No early materialization of derived tables
- Derived Table Merge optimization
- Derived Table with Keys optimization
- Fields of mergeable views and derived tables are involved in optimization
- Disk access optimization
- Index Condition Pushdown (ICP)
- Multi-Range-Read optimization (MRR)
- Join optimizations
- Block-based Join Algorithms: Block Nested Loop (BNL) for outer joins, Block Hash Joins, Block Index Joins (Batched Key Access (BKA) Joins)
- Index Merge improvements
- Replication
- Group Commit for Binary Log
- Annotation of row-based replication events with the original SQL statement
- Checksum for binlog events
- Enhancements for
START TRANSACTION WITH CONSISTENT SNAPSHOT
- Performance improvement for row-based replication for tables with no primary key
- Handler Socket Interface included.
HANDLER READ works with prepared statements
- Dynamic Column support for Handler Interface
- Microsecond support
CAST extended
- Windows performance improvements
- New status variables
- Progress reports for some operations
- Enhanced
KILL command
|
MySQL 5.5 (link) |
MariaDB 5.5 (link) |
- InnoDB
- InnoDB Version 5.5
- Default storage engine switched to InnoDB.
- InnoDB fast
INDEX DROP/CREATE feature added.
- Multi-core scalability. Focus on InnoDB, especially locking and memory management.
- Optimizing InnoDB I/O subsystem to more effective use of available I/O capacity.
- Performance
- MySQL Thread Pool plugin (Enterprise)
- Security
- MySQL Audit plugin (Enterprise)
- MySQL pluggable authentication (Enterprise) for LDAP, Kerberos, PAM and Windows login
- Replication
- Semi-synchronous replication.
- Partitioning
- 2 new partition types (
RANGE COLUMNS, LIST COLUMNS ).
TRUNCATE PARTITION .
- Proxy Users
- Diagnostic improvements to better access execution an performance information including
PERFORMANCE_SCHEMA , expanded SHOW ENGINE INNODB STATUS output and new status variables.
- Supplementary Unicode characters (
utf16, utf32, utf8mb4 ).
CACHE INDEX and LOAD INDEX INTO CACHE for partitioned MyISAM tables.
- Condition Handling:
SIGNAL and RESIGNAL .
- Introduction of Metadata locking to prevent DDL statements from compromising transactions serializability.
- IPv6 Support
- XML enhancement
LOAD_XML_INFILE .
- Build chain switched to CMake to ease build on other platforms including Windows.
- Deprecation and remove of features.
|
- Storage Engines
- SphinxSE updated to 2.0.4
- PBXT Storage Engine is deprecated.
- XtraDB
- MariaDB uses XtraDB 5.5 as compiled in SE and InnoDB 5.5 as plugin.
- Extended Keys support for XtraDB
- Performance
- Thread pool plugin
- Non-blocking client API Library
- Replication
- Updates on P_S tables are not logged to binary log.
replicate_* variables are dynamically.
Skip_replication option
LIMIT ROWS EXAMINED
- New status variables for features.
- New plugin to log SQL level errors.
|
MySQL 5.6 (link) |
MariaDB 10.0 (link) |
- InnoDB
- InnoDB Version 5.6
- InnoDB full-text search.
- InnoDB transportable tablespace support
- Different InnoDB pages size implementation (4k, 8k, 16k)
- Improvement of InnoDB adaptive flushing algorithm to make I/O more efficient.
- NoSQL style Memcached API to access InnoDB data.
- InnoDB optimizer persistent statistics.
- InnoDB read-only transactions.
- Separating InnoDB UNDO tablespace from system tablespace.
- Maximum InnoDB transaction log size increased from 4G to 512G.
- InnoDB read-only capability for read-only media (CD, DVD, etc.)
- InnoDB table compression.
- New InnoDB metadata table in
INFORMATION_SCHEMA .
- InnoDB internal performance enhancements.
- Better InnoDB deadlock detection algorithm. Deadlock can be written to MySQL error log.
- InnoDB buffer pool state saving and restoring capabilities.
- InnoDB Monitor dynamically disable/enable.
- Online and in-place DDL operations for normal and partitioned InnoDB Tables to reduce application downtime.
- Optimizer
ORDER BY non-index-column for simple queries and subqueries
- Disk-Sweep Multi-Range Read (MRR) optimization for secondary index/table access to reduce I/O
- Index Condition Pushdown (ICP) optimization by pushing down the
WHERE filter to the storage engine.
EXPLAIN also works for DML statements.
- Optimizing of subqueries in derived tables (
FROM (...) ) by postponing or indexing derived tables.
- Implementation of semi-join and materialization strategies to optimize subquery execution.
- Batched Key Access (BKA) join algorithm to improve join performance during table scanning.
- Optimizer trace capabilities.
- Performance Schema (P_S)
- Instrumentation for Statements and stages
- Configuration of consumers at server start-up
- Summary tables for table and index I/O and for table locks
- Event filtering by table
- Various new instrumentation.
- Security
- Encrypted authentication credentials
- Stronger encryption for passwords (SHA-256 authentication plugin)
- MySQL User password expiration.
- Password validation plugin to check password strength
mysql_install_db can create secure root password by default
- cleartext password is not written to any log file any more.
- MySQL Firewall (Enterprise)
- Replication
- Transaction based replication using global transaction identifiers (GTID)
- Row Image Control to reduce binary log volume.
- Crash-safe replication with checksumming and verifying.
- IO and SQL thread information can be stored in an transactional table inside the DB.
- MySQL binlog streaming with
mysqlbinlog possible.
- Delayed replication
- Parallel replication on schema level.
- Partitioning
- Number of partitions including subpartitions increased to 8192.
- Exchange partition with a normal table.
- Explicit selection of specific partiton is possible.
- Partition lock prunining for DML and DDL statements.
- Condition handling:
GET DIAGNOSTICS and SET DIAGNOSTICS
- Server defaults changes.
- Data types
TIME , DATETIME and TIMESTAMP with microseconds
- Host cache exposure and connection errors status information for finding connection problems.
- Improvement in GIS functions.
- Deprecation and remove of features.
|
- Storage Engine
- Cassandra Storage Engine
- Connect Storage Engine
- Sequence Storage Engine
- Better table discovery (FederatedX)
- Spider Storage Engine
- TokuDB Storage Engine
- Mroonga fulltext search Storage Engine
- XtraDB
- XtraDB Version 5.6
- Async commit checkpoint in XtraDB and InnoDB
- Support for atomic writes on FusionIO DirectFS
- Replication
- Parallel Replication
- Global Transaction ID (GTID)
- Multi Source Replication
- Performance
- Subquery Optimization (
EXISTS to IN )
- Faster
UNIQUE KEY generation
- Shutdown performance improvement for MyISAM/Aria table (adjustable hash size)
- Security
- Roles
- MariaDB Audit Plugin
- Optimizer
EXPLAIN for DML Statements
- Engine independent table statistics
- Histogram based statistics
QUERY_RESPONSE_TIME plugin
SHOW EXPLAIN for running connections
EXPLAIN in the Slow Query Log
- Per thread memory usage statistics
SHOW PLUGINS SONAME
SHUTDOWN command
- Killing a query by query id not thread id.
- Return result set of delete rows with
DELETE ... RETURNING
ALTER TABLE IF (NOT) EXISTS
CREATE OR REPLACE TABLE
- Dynamic columns referenced by name
- Multiple use locks (
GET_LOCK ) in one connection
- Better error messages
- New regular expressions (PCRE)
REGEXP_REPLACE , REGEXP_INSTR , REGEXP_SUBSTR
- Metadata lock information in
INFORMATION_SCHEMA
- Priority queue optimization visibility
FLUSH TABLE ... FOR EXPORT flushes changes to disk for binary copy
CURRENT_TIMESTAMP as DEFAULT for DATETIME
- Various features backported from MySQL 5.6
|
MySQL 5.7 (link) |
MariaDB 10.1 (link) |
- InnoDB
- InnoDB Version 5.7
VAR CHAR size increase can be in-place in some cases.
- DDL performance improvements for temporary InnoDB tables (
CREATE DROP TRUNCATE, ALTER )
- Active InnoDB temporary table metadata are exposed in table
INNODB_TEMP_TABLE_INFO .
- InnoDB support spatial data type (GIS,
DATA_GEOMETRY )
- Separate tablespace for temporary InnoDB tables.
- Support for InnoDB Full-text parser plugins was added.
- Multiple page cleaner threads were added.
- Regular an partitioned InnoDB tables can be rebuilt using online in-place DDL commands (
OPTIMZE, ALTER TABLE FORCE )
- Automatic detection, support and optimization for Fusion-io NVM file system to support atomic writes.
- Better support for Transportable Tablespaces to ease backup process.
- InnoDB Buffer Pool size can be configured dynamically.
- Multi-threaded page cleaner support for shutdown and recovery phase.
- InnoDB spatial index support for online in place operation (
ADD SPATIAL INDEX )
- InnoDB sorted index builds to improve bulk loads.
- Identification of modified tablespaces to increase crash recovery performance.
- InnoDB UNDO log truncation.
- InnoDB native partition support.
- InnoDB general tablespace support for databases with a huge amount of tables.
- InnoDB data at rest encryption for file-per-table tablespaces.
- Performance
EXPLAIN for running connections (FOR CONNECTIONS )
- Finer Control of optimizer hints.
- Security
- Old password support has been removed.
- Automatic password expiry policies.
- Lock and unlock of accounts.
- SSL and RSA certificate and key file generation.
- SSL enabled automatically if available.
- MySQL will be initialized secure by default (= hardened)
STRICT_TRANS_TABLES sql_mode is now enabled by default.
ONLY_FULL_GROUP_BY sql_mode made more sophisticated to only prohibit non deterministic query.
- Replication
- Master dump thread was refactored to improve throughput.
- Replication Master change without
STOP SLAVE .
- Multi-source replication introduced.
- Partitioning
HANDLER statement works now on partitioned tables.
- Index Condition Pushdown (ICP) works for partitioned InnoDB and MyISAM tables.
ALTER TABLE EXCHANGE PARTITION WITHOU VALIDATION is possible to improve performance of exchnage.
- Native JSON support
- Data type JSON.
- JSON functions:
JSON_ARRAY, JSON_MERGE, JSON_OBJECT, JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_EXTRACT, JSON_KEYS, JSON_SEARCH, JSON_APPEND, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_INSERT, JSON_QUOTE, JSON_REMOVE, JSON_REPLACE, JSON_SET, JSON_UNQUOTE, JSON_DEPTH, JSON_LENGTH, JSON_TYPE, JSON_VALID
- System and status variables moved from
INFORMATION_SCHEMA to PERFORMANCE_SCHEMA .
- Sys Schema created by default.
- Condition handling:
GET STACKED DIAGNOSTICS
- Multiple triggers per event are possible now.
- Native logging to syslog possible.
- Generated Column support.
- Database rewriting in
mysqlbinlog .
- Control+C in mysql client does not exit any more but interrupts query only.
- New China National Standard GB18030 character set.
RENAME INDEX is online inplace without a table copy.
- Chinese, Japanese and Korean (CJK) full-text parser implemented (ngram MeCab full-test parser plugins).
- Deprecation and remove of features.
|
- XtraDB
- Allow up to 64K pages in InnoDB (old limit was 16K).
- Defragmenting InnoDB Tablespaces improved which uses
OPTIMIZE TABLE to defragment InnoDB tablespaces.
- XtraDB page compression
- Performance
- Page compression for FusionIO
- Do not create
.frm files for temporary tables.
UNION ALL works without usage of a temporary table.
- Scalability fixes for Power8.
- Performance improvements on simple queries.
- Performance Schema tables no longer use .frm files.
- xid cache scalability was significantly improved.
- Replication
- Optimistic mode of in-order parallel replication
domain_id based replication filters
- Enhanced semisync replication: Wait for at least one slave to acknowledge transaction before committing.
- Triggers can now be run on the slave for row-based events.
- Dump Thread Enhancements: Makes multiple slave setups faster by allowing concurrent reading of binary log.
- Throughput improvements in parallel replication.
RESET_MASTER is extended with TO .
- Optimizer
ANALYZE statement provides output for how many rows were actually read, etc.
EXPLAIN FORMAT=JSON
ORDER BY optimization is improved.
MAX_STATEMENT_TIME can be used to automatically abort long running queries.
- Security
- Password validation plugin API.
- Simple password check password validation plugin.
- Cracklib_password_check password validation plugin.
- Table, Tablespace and Log at-rest encryption (TDE)
SET DEFAULT ROLE
- New columns for the
INFORMATION_SCHEMA.APPLICABLE_ROLES table.
- Galera Cluster plugin becomes standard in MariaDB.
- Wsrep information in
INFORMATION_SCHEMA: WSREP_MEMBERSHIP and WSREP_STATUS
- Consistent support for
IF EXISTS and IF NOT EXISTS and OR REPLACE for: CREATE DATABASE, CREATE FUNCTION UDF, CREATE ROLE, CREATE SERVER, CREATE USER, CREATE VIEW, DROP ROLE, DROP USER, CREATE EVENT, DROP EVENT, CREATE INDEX, DROP INDEX, CREATE TRIGGER, DROP TRIGGER
- Information Schema plugins can now support
SHOW and FLUSH statements.
GET_LOCK() now supports microseconds in the timeout.
- The number of rows affected by a slow
UPDATE or DELETE is now recorded in the slow query log.
- Anonymous Compound Statents blocks are supported.
- SQL standards-compliant behavior when dealing with Primary Keys with Nullable Columns.
- Automatic discovery of
PERFORMANCE_SCHEMA tables.
INFORMATION_SCHEMA.SYSTEM_VARIABLES, enforce_storage_engine, default-tmp-storage-engine, mysql56-temporal-format, Slave_skipped_errors, silent-startup
- New status variables to show the number of grants on different object.
- Set variables per statement:
SET STATEMENT
- Support for Spatial Reference systems for the GIS data.
- More functions from the OGC standard added:
ST_Boundary, ST_ConvexHull, ST_IsRing, ST_PointOnSurface, ST_Relate
- GIS
INFORMATION_SCHEMA tables: GEOMETRY_COLUMNS , SPATIAL_REF_SYS
|
MySQL 8.0 (link) |
MariaDB 10.2 (link) |
- InnoDB
- InnoDB Version 8.0
AUTO_INCREMENT values are persisted across server restarts.
- Index corruption and in-memory corruption detection written persistently to the transaction log.
- InnoDB Memcached plugin supports multiple get operations.
- Deadlock detection can be disabled and leads to a lock timeout to increase performance.
- Index pages cached in buffer pool are listed in
INNODB_CACHED_INDEXES .
- All InnoDB temporary tables are created in InnoDB shared temporary tablespace.
- JSON
- Inline path operator ->> added.
- Column path operator -> improved.
- JSON aggregation functions
JSON_ARRAYAGG() and JSON_OBJECTAGG() added.
- Security
- Account management supports roles.
- Atomicity in User Management DDLs.
- Transactional data dictionary (DD).
- Common Table Expressions (CTE, recursive SQL, Series creation)
- Descending Indexes
- Scaling and Performance of
INFORMATION_SCHEMA (1 Mio table problem)
- Deprecation and remove of features.
MySQL 8.0 is currently in a very early stage (DMR) so this list will increase over time!
|
- Security
SHOW CREATE USER
CREATE USER and ALTER USER extended for limiting resources and TLS/SSL support.
- Performance
- Connection creation speed-up by separate thread.
- Optimizer
EXPLAIN FORMAT=JSON improved.
- Partition
- Catchall partition for
LIST partitions.
- Introduction of Window functions:
CUME_DIST, DENSE_RANK, NTILE, PERCENT_RANK, RANK, ROW_NUMBER
SHOW CREATE USER statement and limiting user resource usage introduced
- Common Table Expression (CTE)
WITH clause for recursive queries.
CHECK CONSTRAINT support.
- Support for
DEFAULT with expression.
BLOB and TEXT can now have default values.
- Virtual computed columns restrictions lifted.
- Supported decimals in
DECIMAL increased from 30 to 38.
- Temporary tables can be referred to several times in the same query.
- Multiple triggers for the same event.
- InnoDB/XtraDB 5.7.14 was merged.
ANALYZE TABLE implemented lock free.
- CONNECT engine supports JDBC table type.
- NO PAD collation support.
- Table cache can auto-partition introduced.
- New Window functions:
LEAD, LAG, NTH_VALUE, FIRST_VALUE, LAST_VALUE
- Slave binary log read throttling, delayed replication, and binary log compression implemented.
- JSON functions added
- Oracle style
EXECUTE IMMEDIATE .
PREPARE STATEMENT understand most expressions.
TRIGGERS enhanced by FOLLOWS/PRECEDES clauses.
I_S.USER_VARIABLES introduced as plugin.
- New status information:
Com_alter_user, Com_multi, Com_show_create_user .
- New variables:
innodb_tmpdir, read_binlog_speed_limit .
- DML flashback introduced on instance, database and table level.
- GeoJSON functions added.
- To come soon
- MariaDB Column store (ex. InfiniDB)
- MyRocks?
MariaDB 10.2 is currently in a early stage (beta release) so this list will increase over time...
|
MySQL 9.0 |
MariaDB 10.3 (link) and 10.4 |
No details are known yet. MySQL developer meeting took place in November 2016.
|
- Suggested features
- Hidden columns
- Long unique constraints
- SQL based
CREATE AGGREGATE FUNCTION
- New data types:
IPv6 , UUID , pluggable data-type API
- Better support for CJK (Chinese, Japanese, and Korean) languages. Include the ngram full-text parser and MeCab full-text parser .
- Improvement of Spider SE.
- Support for
SEQUENCES
- Additional PL/SQL parser
- Support for
INTERSECT
- Support for
EXCEPT
MariaDB 10.3 is currently in a very early stage so this list will increase over time!
|