MariaDB 11.1.0 preview release now available
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 conditionUp 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 whereThe 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 conditionSemi-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 whereThen, 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 whereJSON 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 named mariadb_backup_* ( MDEV-18931)
The MariaDB 11.1.0 preview release is now available for testing.
