What is MariaDB 5.3?

Главным фокусом развития MariaDB 5.3 остается увеличение производительности для подзапросов, таких как "JOIN" или одно-табличные запросы применяемые к большим наборам данных.

MariaDB 5.3 базируется на MariaDB 5.2, а она на MariaDB 5.1, которая основана на MySQL 5.1. Она больше находиться в активной разработке, и будет получать критические исправления безопасности только до Февраля 2017 года.

Некоторая часть кода была портирована из MySQL 6.0 (версия которая не была допущена к релизу как GA компанией Oracle), который был переработан и обогащен новыми возможностями, ну и конечно некоторая часть кода была написана с нуля.

Все новые возможности и их комбинации могут быть включены или выключены динамически через системную переменную "optimizer_switch"

Первый стабильный (GA) релиз MariaDB 5.3 был MariaDB 5.3.5, который был выпущен 29 Февраля 2012 года.

Вы можете скачать последнюю бинарную сборку MariaDB 5.3 тут, или получить последнюю версию исходного кода из Launchpad.

Сравнительные таблицы дополнений

Для оптимизаторов была создана таблица Сравнительная таблица дополнений-оптимизаторов показывающая что новые дополнения для оптимизации в MariaDB 5.5 и 5.3 подходят к MySQL 5.5 и 5.6.

Оптимизация запросов

Оптимизация подзапросов

Подзапросы окончательно вошли в нашу практику. Больше не требуется переписывать подзапросы в ручную в "JOIN" или разделять запросы. MariaDB 5.3 нацелен на обеспечение обоснованной обработки для всех типов подзапросов Все возникшие проблемы с EXPLAIN полученные в процессе уже были решены.

• Semi-join subquery optimizations
  Оптимизатор трансформирует подзапросы в 'полу-join' запросы, это такие единицы похожие на 'inner join' запросы и тогда оптимизатор выбирает лучшую стратегию выполнения 'полу-join' запросов. Глобально процесс сходный с тем как выполняется join в MySQL, MariaDB и других базах данных.
  • Table pullout optimization
  • FirstMatch execution strategy
  • "Полу-join" Materialization стратегия выполнения
  • LooseScan стратегия выполнения
  • DuplicateWeedout стратегия выполнения
• Не "полу-join" оптимизация подзапросов
  If a subquery is not a semi-join, MariaDB 5.3 will make a cost-based choice between these two strategies:
  • Materialization for non-correlated subqueries, with efficient NULL-aware execution
  • IN-to-EXISTS transformation (the only optimization inherited from MariaDB 5.2 and MySQL 5.1/5.5)

• Subquery Cache
  The subquery cache makes sure that subqueries are re-executed as few times as possible, improving performance of already optimized subqueries.

• Subqueries are never executed during EXPLAIN, thus resulting in almost instant EXPLAIN.

• DISTINCT and GROUP BY without HAVING are optimized away from subqueries.

The Subquery Optimizations Map shows new subqueries optimizations graphically.

Карта оптимизации подзапросов показывает новую схему оптимизации в графическом виде.

Оптимизация для доставки таблиц и представлений(запросов)

• No early materialization of derived tables (e.g. subqueries in a FROM clause) and materialized views (EXPLAIN is always instantaneous)
• Thanks to Derived Table Merge optimization, mergeable derived tables are now processed like mergeable VIEWs.
• Derived Table with Keys optimization gives the optimizer an option to create indexes over materialized derived tables
• Fields of merge-able views and derived tables are involved now in all optimizations employing equalities

Оптимизация доступа к диску

• Index Condition Pushdown
• Multi-Range-Read optimization (MRR)
  • Key-ordered retrieval

Оптимизация 'Join' запросов

• Block-based Join Algorithms
• Block Nested Loop algorithm can be used for outer joins
• Block Hash Join (classic algorithm) is implemented and can be used for any equi-joins
• Block Index Join (Batch Key Access Join) is supported and can exploit the benefits of ordered retrievals for primary and secondary keys provided by the new implementation of MRR
• All block based algorithms for joins can use the benefits of new incremental join buffers
• All block based algorithms fully support outer joins including nested outer joins
• All block based algorithms can use the benefits of the first match optimization for semi-joins and the non-exist optimization for outer joins
• All block based algorithms for joins can exploit the benefits of index condition push-down.
• The total memory space used by the query for join buffers can be limited now, and block based algorithms can allocate join buffers up to their needs (not exceeding the set limits).
• Condition over outer tables extracted from ON expressions of outer joins are evaluated before inner tables are accessed (supported for both regular index join and block index join)
• Early checks for nulls for the fields from any null-rejecting conditions are performed

Index Merge improvements

• Correct optimization of index_merge vs range access: Fair choice between range and index_merge optimizations
• index_merge/sort_intersection strategy

Optimizer control

• @@optimizer_switch variable can be used to turn on/off all new optimizations.

NoSQL-style interfaces

• HandlerSocket plugin included.
• Faster HANDLER commands; HANDLER READ now also work with prepared statements.
• Dynamic Columns support.

Replication and binary logging

• Group commit for the binary log — MariaDB 5.3 implements group commit which works when using XtraDB with the binary log enabled. (In previous MariaDB releases, and all MySQL releases at the time of writing, group commit works in InnoDB/XtraDB when the binary log is disabled, but stops working when the binary log is enabled).
• Annotation of row-based replication events with the original SQL statement — When using row-based replication, the binary log does not contain SQL statements, only discrete single-row insert/update/delete events. This can make it harder to read mysqlbinlog output and understand where in an application a given event may have originated, complicating analysis and debugging.This feature adds an option to include the original SQL statement as a comment in the binary log (and shown in mysqlbinlog output) for row-based replication events.
• Checksums for binlog events. This is a backport of the same feature in MySQL 5.6. It was implemented in MWL#180.
• Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT — In MariaDB 5.3, START TRANSACTION WITH CONSISTENT SNAPSHOT now also works with the binary log. This means it is possible to obtain the binlog position corresponding to a transactional snapshot of the database without blocking any other queries. This is used by the command "mysqldump --single-transaction --master-data" to do a fully non-blocking backup which can be used to provision a new slave. "START TRANSACTION WITH CONSISTENT SNAPSHOT" now also works consistently between transactions involving more than one storage engine (currently XTraDB and PBXT support this).
• Row-based replication for tables with no primary key — This feature can improve the performance of row-based replication on tables that do not have a primary key (or other unique key), but which do have another index that can help locate rows to update or delete. With this feature, index cardinality information from ANALYZE TABLE is considered when selecting the index to use (before this feature is implemented, the first index was selected unconditionally).
• mysqlbinlog will now omit redundant use statements around BEGIN, SAVEPOINT, COMMIT, and ROLLBACK events when reading MySQL 5.0 binlogs.

Datatypes

• Microsecond support for NOW() and timestamp, time, and datetime columns.
• CAST() now supports AS DECIMAL[(M,D)] and AS INT.
• CAST() and all other datetime/time functions now supports microsecond fully.

Windows performance improvements

• Backported Windows performance patches from MySQL 5.5.
• Asynchronous IO in XtraDB is redesigned and is now faster, due to the use of IO completion ports.
• Additional durability option for XtraDB : innodb_flush_method can now be O_DSYNC, like on Unixes. The effect of using this option is that the log file is opened with FILE_FLAG_WRITETHROUGH, and FlushFileBuffers() is not done. This may improve speed in write-heavy scenarios.
• A new Windows MSI installer.
• Includes a GUI-tool, HeidiSQL.

Miscellaneous

• GIS precise operations
• New status variables: Rows_tmp_read, Handler_tmp_write, and Handler_tmp_update which count what happens with internal temporary tables. Rows_read, Handler_write and Handler_update no longer count operations on internal temporary tables.
• New status variable Handler_read_rnd_deleted, which is number of deleted rows found and skipped while scanning a table. Before this was part of Handler_read_rnd_next.
• New variable 'in_transaction' that is 1 if you are in a transaction, 0 otherwise.
• Progress reports for ALTER TABLE and LOAD DATA INFILE. In addition Aria tables gives progress reports for REPAIR TABLE and CHECK TABLE. The progress can be seen in SHOW PROCESSLIST, INFORMATION_SCHEMA.PROCESSLIST and is sent to MariaDB clients that calls mysql_real_connect() with the new CLIENT_PROGRESS flag. mysql command line client supports the new progress indications.
• PBXT consistent commit ordering — This feature implements the new commit ordering storage engine API in PBXT. With this feature, it is possible to use "START TRANSACTION WITH CONSISTENT SNAPSHOT" and get consistency among transactions which involve both XtraDB and InnoDB. (Without this feature, there is no such consistency guarantee. For example, even after running "START TRANSACTION WITH CONSISTENT SNAPSHOT" it was still possible for the InnoDB/XtraDB part of some transaction T to be visible and the PBXT part of the same transaction T to not be visible.)
• MariaDB unique error numbers now start from 1900 to not clash with MySQL error numbers.
• /*M!##### */ new executed comment syntax that can be used when you want use new MariaDB syntax but still want your program to be compatible with MySQL.
• A MariaDB optimized version of mytop is included in the MariaDB distribution.
• Enhanced KILL syntax:

  KILL [HARD | SOFT] [CONNECTION | QUERY] [thread_id | USER user_name]
  

• max_user_connections (both the global variable and the GRANT option) can be set to -1 to stop users from connecting to the server. The global max_user_connections variable does not affect users with the SUPER privilege.
• The IGNORE directive does not ignore all errors (like fatal errors), only things that are safe to ignore.

You can access the MariaDB 5.3 tree from launchpad.

Исправление уязвимостей безопасности в MariaDB 5.3