We are pleased to announce the availability of the MariaDB 11.1 preview release, MariaDB 11.1.0.
Candidate features for MariaDB 11.1
Preview releases are designed to get features into the hands of users more quickly, and should not be used for production. Features in a preview release may not all make the Generally Available (GA) release – only those that pass testing will be merged into MariaDB Server 11.1.1.
Features under consideration for 11.1 include:
Index usage with YEAR and DATE
With MDEV-8320, some queries using the DATE or the YEAR function will be much faster, as the optimizer is now able to make use of an index in certain cases. Take the following (starting with creating a table t3 of 1000 dates).
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int);
insert into t1 select A.a + B.a* 10 from t0 A, t0 B;
create table t2 (pk int primary key, a datetime, b date, key(a), key(b));
insert into t2
select
A.a*10+B.a,
date_add(date_add('2017-01-01', interval A.a*8 day), interval B.a hour),
date_add('2017-01-01', interval A.a*7 day)
from t1 A, t0 B;
SELECT * FROM t2 LIMIT 3;
+----+---------------------+------------+
| pk | a | b |
+----+---------------------+------------+
| 0 | 2017-01-01 00:00:00 | 2017-01-01 |
| 1 | 2017-01-01 01:00:00 | 2017-01-01 |
| 2 | 2017-01-01 02:00:00 | 2017-01-01 |
...
| 997 | 2019-03-04 07:00:00 | 2018-11-25 |
| 998 | 2019-03-04 08:00:00 | 2018-11-25 |
| 999 | 2019-03-04 09:00:00 | 2018-11-25 |
+-----+---------------------+------------+
explain select * from t2 where date(a) <= '2017-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: range
possible_keys: a
key: a
key_len: 6
ref: NULL
rows: 10
Extra: Using index condition
Up until MariaDB 11.0, the optimizer wouldn't make use of the index:
explain select * from t2 where date(a) <= '2017-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
The index can be used with both the YEAR and the DATE functions, and with any of the >, <, >=, <= or = operators:
explain select * from t2 where year(a) < 2017\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: range
possible_keys: a
key: a
key_len: 6
ref: NULL
rows: 1
Extra: Using index condition
explain select * from t2 where year(a) = 2019\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: range
possible_keys: a
key: a
key_len: 6
ref: NULL
rows: 80
Extra: Using index condition
Semi-join optimization for UPDATE/DELETE
MariaDB has a number of semi-join optimizations. Previously, single-table UPDATE/DELETE statements could not take advantage of these because semi-join optimizations are the kind of subquery optimizations that cannot be used for single-table UPDATE/DELETEs. Now, the optimizer can automatically convert single-table UPDATEs and DELETEs to multi-table UPDATE/DELETEs, enabling the semi-join optimizations for them. If you use subqueries in UPDATE or DELETE, these statements will likely be much faster (MDEV-7487) For example, compare these two EXPLAIN results from a sample dataset. First, prior to MariaDB 11.1:
explain delete from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU'))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: partsupp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 700
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: nation
type: ref
possible_keys: PRIMARY,i_n_regionkey,i_n_name
key: i_n_name
key_len: 26
ref: const
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: part
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: supplier
type: eq_ref
possible_keys: PRIMARY,i_s_nationkey
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
Then, the MariaDB 11.1 EXPLAIN on the equivalent query:
explain delete from partsupp where (ps_partkey, ps_suppkey) in (select p_partkey, s_suppkey from part, supplier where p_retailprice between 901 and 910 and s_nationkey in (select n_nationkey from nation where n_name='PERU'))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: nation
type: ref
possible_keys: PRIMARY,i_n_name
key: i_n_name
key_len: 26
ref: const
rows: 1
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: supplier
type: ref
possible_keys: PRIMARY,i_s_nationkey
key: i_s_nationkey
key_len: 5
ref: test.nation.n_nationkey
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: partsupp
type: ref
possible_keys: PRIMARY,i_ps_partkey,i_ps_suppkey
key: i_ps_suppkey
key_len: 4
ref: test.supplier.s_suppkey
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: part
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.partsupp.ps_partkey
rows: 1
Extra: Using where
JSON schema validation
The JSON_SCHEMA_VALID function has been implemented (MDEV-27128) according to the JSON Schema Draft 2020. If a given json is valid against a schema, the function returns true, otherwise returns false.
SET @schema= '{
"properties" : {
"number1":{ "maximum":10 },
"string1" : { "maxLength": 3}
}
}';
SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }');
+----------------------------------------------------------------+
| JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }') |
+----------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------+
SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }');
+----------------------------------------------------------------+
| JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }') |
+----------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------+
InnoDB defragmentation
InnoDB defragmentation was a rarely-used feature to make OPTIMIZE TABLE not rebuild the table as usual, but instead cause the index B-trees to be optimized in place. However, the option used excessive locking (exclusively locking index trees), never covered SPATIAL INDEXes or FULLTEXT INDEXes, and storage space was never reclaimed. Since it was not particularly useful, did not work in many cases, and caused a maintenance burden, it has been removed (MDEV-30545).
Other features
- MDEV-16329 ALTER ONLINE TABLE has been implemented above the storage engine layer, mimicking what InnoDB has done since MariaDB 10.0. A separate post on this topic may follow.
- Mariabackup is tool for performing physical online backups. It was originally a fork from Xtrabackup, which did not support MariaDB 10.1's data at rest encryption. Files were still however named
xtrabackup_*. They are now namedmariadb_backup_*(MDEV-18931)
We look forward to receiving your comments!
Links
- Download MariaDB 11.1.0
- Release notes
- Container Image: quay.io/mariadb-foundation/mariadb-devel:11.1-preview