MySQL 分支 MariaDB 5.3.2-beta 发布

     <div id="p_fullcontent" class="detail">     <p><a href="/misc/goto?guid=4958185304610299112" target="_blank">MariaDB </a>是一个采用 Maria 存储引擎的 MySQL 分支版本，是由原来 MySQL 的作者 <strong>Michael Widenius </strong>创办的公司所开发的免费开源的数据库服务器。</p>     <p>与 MySQL 相比较，MariaDB 更强的地方在于：</p>     <ul>      <li>Maria 存储引擎 </li>      <li>PBXT 存储引擎 </li>      <li>XtraDB 存储引擎 </li>      <li>FederatedX 存储引擎 </li>      <li>更快的复制查询处理 </li>      <li>线程池 </li>      <li>更少的警告和bug </li>      <li>运行速度更快 </li>      <li>更多的 Extensions (More index parts, new startup options etc) </li>      <li>更好的功能测试 </li>      <li>数据表消除 </li>      <li>慢查询日志的扩展统计 </li>      <li>支持对 Unicode 的排序 </li>     </ul> 下载：     <a href="/misc/goto?guid=4958193600161792420" target="_blank">http://downloads.askmonty.org/mariadb/5.3</a>     <br />     <p>MariaDB 发布了 5.3.2 beta 版，这是 5.3.1 和 5.3.0 的 bug 修复版本。详细的改进列表请看：</p>     <p>The focus for MariaDB 5.3 is to radically improve performance for subqueries, as well as for joins and single-table queries over large data sets.</p>     <p>MariaDB 5.3 is based on MariaDB 5.2 and thus on MariaDB 5.1 and MySQL 5.1.</p>     <p>Some of the code was backported from MySQL 6.0 (a MySQL version that was never released as GA by Oracle), some was re-engineered and enriched by new features, and some code was written from scratch.</p>     <p>This is the biggest redesign of the MariaDB optimizer in 10 years and it finally makes subqueries usable in practice. We listened to the MariaDB/MySQL community, and we are taking a conservative approach in the way we are introducing all of the new features. In the beta releases of MariaDB 5.3 all optimizer-related features that are not fully tested are disabled by default. Any feature or combination of features can be enabled dynamically via the<a href="/misc/goto?guid=4958193600901993257">optimizer_switch</a> system variable.</p>     <p>MariaDB 5.3 is in beta (feature complete) phase since July 26, 2011. You can download<a href="/misc/goto?guid=4958193601638401649">the latest binaries of MariaDB 5.3 here</a>, or get the latest<a href="/misc/goto?guid=4958193602380393950">source code from launchpad</a>.</p>     <h2 id="query-optimizer" class="anchored_heading">Query optimizer</h2>     <h3 id="subquery-optimizations" class="anchored_heading">Subquery optimizations</h3>     <p>One of the major performance improvements in MariaDB 5.3 is subqueries which are finally usable in practice. It is no longer necessary to rewrite subqueries manually into joins or into separate queries, or to forbid their use altogether in your applications. MariaDB 5.3 will take care of this, providing in many cases the best theoretically achievable performance, both when queries are run and <code>EXPLAIN</code>-ed.</p>     <ul start="1">      <li><a href="/misc/goto?guid=4958193603113475623">Semi-join subquery optimizations</a><br /> Semi-join optimizations transform subqueries into joins whenever possible. These transformations allow the database to choose among many efficient strategies and to reuse the optimizations available for regular joins. The semi-join optimizations not only allow MariaDB to do what developers do with manual rewrites, but they also provide new and more efficient execution methods which are impossible to imitate outside the database server.       <ul start="1">        <li>Duplicate elimination</li>        <li>Pull out</li>        <li>Loose scan</li>        <li>First match</li>        <li>Materialization-scan</li>        <li>Inside-out execution for non-semi-join materialized subqueries</li>        <li>Cost-based choice between all semi-join optimizations</li>       </ul> </li>     </ul>     <ul start="1">      <li><a href="/misc/goto?guid=4958193603853644294">Non-semi-join optimizations</a><br /> Certain kinds of subqueries such as ones with <code>GROUP BY</code>, or in the <code>SELECT</code> clause cannot be transformed into joins. Such queries are handled by:       <ul start="1">        <li>Materialization for non-correlated subqueries, with <a href="/misc/goto?guid=4958193604593981148">efficient NULL-aware execution</a> </li>        <li>IN-to-EXISTS transformation (the only optimization inherited from MariaDB 5.2 and MySQL 5.1)</li>        <li>Cost-based choice between Materialization and IN->EXISTS</li>       </ul> </li>     </ul>     <ul start="1">      <li><a href="/misc/goto?guid=4958193605332038409">Subquery Cache</a><br /> The subquery cache makes sure that subqueries are re-executed as few times as possible, improving performance of already optimized subqueries.</li>     </ul>     <ul start="1">      <li>Subqueries are never executed during <code>EXPLAIN</code>, thus resulting in almost instant <code>EXPLAIN</code>.</li>     </ul>     <h3 id="optimizations-for-derived-tables-and-views" class="anchored_heading">Optimizations for derived tables and views</h3>     <ul start="1">      <li>No early materialization of derived tables (e.g. subqueries in a <code>FROM</code> clause) and materialized views (<code>EXPLAIN</code> is always instantaneous)</li>      <li>Merge-able derived tables are merged like merge-able views</li>      <li>Optimizer can create keys to access derived tables and views materialized in temporary tables</li>      <li>Fields of merge-able views and derived tables are involved now in all optimizations employing equalities </li>     </ul>     <h3 id="disk-access-optimization" class="anchored_heading">Disk access optimization</h3>     <ul start="1">      <li><a href="/misc/goto?guid=4958193606073472803">Index Condition Pushdown</a> </li>      <li><a href="/misc/goto?guid=4958193606813836705">Multi-Range-Read optimization (MRR)</a>       <ul start="1">        <li>Key-ordered retrieval</li>       </ul> </li>     </ul>     <h3 id="join-optimizations" class="anchored_heading">Join optimizations</h3>     <ul start="1">      <li>Block Nested Loop algorithm can be used for outer joins</li>      <li>Block Hash Join (classic algorithm) is implemented and can be used for any equi-joins</li>      <li>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 <a href="/misc/goto?guid=4958193606813836705">MRR</a> </li>      <li>All block based algorithms for joins can use the benefits of new incremental join buffers</li>      <li>All block based algorithms fully support outer joins including nested outer joins</li>      <li>All block based algorithms can use the benefits of the first match optimization for semi-joins and the non-exist optimization for outer joins</li>      <li>All block based algorithms for joins can exploit the benefits of index condition push-down.</li>      <li>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).</li>      <li>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)</li>      <li>Early checks for nulls for the fields from any null-rejecting conditions are performed</li>     </ul>     <h3 id="index-merge-improvements" class="anchored_heading">Index Merge improvements</h3>     <ul start="1">      <li>Correct optimization of index_merge vs range access: <a href="/misc/goto?guid=4958193608225444151">Fair choice between range and index_merge optimizations</a> </li>      <li><a href="/misc/goto?guid=4958193608960867174">index_merge/sort_intersection</a> strategy</li>     </ul>     <h3 id="optimizer-control" class="anchored_heading">Optimizer control</h3>     <ul start="1">      <li><a href="/misc/goto?guid=4958193600901993257">@@optimizer_switch variable</a> can be used to turn on/off all new optimizations.</li>     </ul>     <h2 id="nosql-style-interfaces" class="anchored_heading">NoSQL-style interfaces</h2>     <ul start="1">      <li><a href="/misc/goto?guid=4958193610382227751">HandlerSocket</a> plugin included.</li>      <li>Faster <a href="/misc/goto?guid=4958193611116570244">HANDLER</a> commands; <a href="/misc/goto?guid=4958193611116570244">HANDLER READ</a> now also work with prepared statements.</li>      <li><a href="/misc/goto?guid=4958193612530994626">Dynamic Columns</a> support.</li>     </ul>     <h2 id="replication-and-binary-logging" class="anchored_heading">Replication and binary logging</h2>     <ul start="1">      <li><a href="/misc/goto?guid=4958193613277154343">Group commit for the binary log</a> <span>—</span> 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).</li>      <li><a href="/misc/goto?guid=4958193614007794515">Annotation of row-based replication events with the original SQL statement</a> <span>—</span> When using row-based replication, the binary log does not contain SQL statements, only discrete single-row insert/update/delete <em>events</em>. 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.</li>      <li><a href="/misc/goto?guid=4958193614759365174">Checksums for binlog events</a>. This is a backport of the same feature in MySQL 5.6. It was implemented in <a href="/misc/goto?guid=4958193615493423325">MWL#180</a>.</li>      <li><a href="/misc/goto?guid=4958193616230966384">Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT</a> <span>—</span> In MariaDB 5.3, <code>START TRANSACTION WITH CONSISTENT SNAPSHOT</code> 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 "<code>mysqldump</code> <code><code>--</code>single-transaction</code> <code><code>--</code>master-data</code>" to do a fully non-blocking backup which can be used to provision a new slave. "<code>START</code> <code>TRANSACTION</code> <code>WITH</code> <code>CONSISTENT</code> <code>SNAPSHOT</code>" now also works consistently between transactions involving more than one storage engine (currently XTraDB and PBXT support this).</li>      <li><a href="/misc/goto?guid=4958193616963863281">Row-based replication for tables with no primary key</a> <span>—</span> 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 <code>ANALYZE TABLE</code> is considered when selecting the index to use (before this feature is implemented, the first index was selected unconditionally).</li>      <li><code>mysqlbinlog</code> will now omit redundant <code>use</code> statements around <code>BEGIN</code>, <code>SAVEPOINT</code>, <code>COMMIT</code>, and <code>ROLLBACK</code> events when reading MySQL 5.0 binlogs.</li>     </ul>     <h2 id="datatypes" class="anchored_heading">Datatypes</h2>     <ul start="1">      <li><a href="/misc/goto?guid=4958193617710588639">Microsecond</a> support for <a href="/misc/goto?guid=4958193618452821946">NOW()</a> and <a href="/misc/goto?guid=4958193619192660431">timestamp</a>, <a href="/misc/goto?guid=4958193619934450557">time</a>, and <a href="/misc/goto?guid=4958193620682957183">datetime</a> columns.</li>      <li><a href="/misc/goto?guid=4958193621423347190">CAST()</a> now supports AS DECIMAL[(M,D)] and AS INT.</li>      <li><a href="/misc/goto?guid=4958193621423347190">CAST()</a> and all other datetime/time functions now supports microsecond fully.</li>     </ul>     <h2 id="windows-performance-improvements" class="anchored_heading">Windows performance improvements</h2>     <ul start="1">      <li>Backported <a href="/misc/goto?guid=4958193622842798649">Windows performance patches</a> from MySQL 5.5.</li>      <li>Asynchronous IO in XtraDB is <a href="/misc/goto?guid=4958193623580208841">redesigned</a> and is now faster, due to the use of IO completion ports.</li>      <li>Additional durability option for XtraDB : <code>innodb_flush_method</code> can now be <code>O_DSYNC</code>, like on Unixes. The effect of using this option is that the log file is opened with <code>FILE_FLAG_WRITETHROUGH</code>, and <code>FlushFileBuffers()</code> is not done. This may improve speed in write-heavy scenarios.</li>      <li>A new Windows <a href="/misc/goto?guid=4958193624323471870">MSI installer</a>.</li>      <li>Includes a GUI-tool, <a href="/misc/goto?guid=4958193625056687977">HeidiSQL</a>.</li>     </ul>     <h2 id="miscellaneous" class="anchored_heading">Miscellaneous</h2>     <ul start="1">      <li>New status variables: <code>Rows_tmp_read</code>, <code>Handler_tmp_write</code>, and <code>Handler_tmp_update</code> which count what happens with internal temporary tables. <code>Rows_read</code>, <code>Handler_write</code> and <code>Handler_update</code> no longer count operations on internal temporary tables.</li>      <li>New variable 'in_transaction' that is 1 if you are in a transaction, 0 otherwise.</li>      <li><a href="/misc/goto?guid=4958193625800918281">Progress reports</a> for <code>ALTER TABLE</code> and <code>LOAD DATA INFILE</code>. In addition Aria tables gives progress reports for <code>REPAIR TABLE</code> and <code>CHECK TABLE</code>. The progress can be seen in <code>SHOW PROCESSLIST</code>, <code>INFORMATION_SCHEMA.PROCESSLIST</code> and is sent to MariaDB clients that calls <code>mysql_real_connect()</code> with the new <code>CLIENT_PROGRESS</code> flag. <code>mysql</code> command line client supports the new progress indications.</li>      <li><a href="/misc/goto?guid=4958193616230966384">PBXT consistent commit ordering</a> <span>—</span> This feature implements the new commit ordering storage engine API in PBXT. With this feature, it is possible to use "<code>START TRANSACTION WITH CONSISTENT SNAPSHOT</code>" 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 "<code>START TRANSACTION WITH CONSISTENT SNAPSHOT</code>" it was still possible for the InnoDB/XtraDB part of some transaction <em>T</em> to be visible and the PBXT part of the same transaction <em>T</em> to not be visible.)</li>      <li>MariaDB unique error numbers now start from <code>1900</code> to not clash with MySQL error numbers.</li>      <li><code>/*M!##### */</code> new <a href="/misc/goto?guid=4958193627211424629">executed comment syntax</a> that can be used when you want use new MariaDB syntax but still want your program to be compatible with MySQL.</li>      <li>A MariaDB optimized version of <a href="/misc/goto?guid=4958193627955248912">mytop</a> is included in the MariaDB distribution.</li>      <li>Enhanced <a href="/misc/goto?guid=4958193628690479988">KILL syntax</a>:       <div>        <pre class="highlight fixed">KILL [HARD | SOFT] [CONNECTION | QUERY] [thread_id | USER user_name]</pre>       </div> </li>      <li><code style="white-space:pre-wrap;" class="highlight fixed">max_user_connections</code> (both the global variable and the <code>GRANT</code> option) can be set to <code>-1</code> to stop users from connecting to the server. The global <code style="white-space:pre-wrap;" class="highlight fixed">max_user_connections</code> variable does not affect users with the <code style="white-space:pre-wrap;" class="highlight fixed">SUPER</code> privilege.</li>     </ul>     <p>For a more specific list and for things that are still planned, see the<a href="/misc/goto?guid=4958193629441621637">MariaDB 5.3 TODO page</a>.</p>     <p>You can access the MariaDB 5.3 tree from<a href="/misc/goto?guid=4958193630178757422">launchpad</a>.</p>     <p> </p>     <p> </p>    </div>