The Main Features of MySQL

By 谷多 on April 19, 2008 12:03 PM | No Comments | No TrackBacks

http://dev.mysql.com/doc/refman/5.1/en/features.html

MySQL的主要特性：

先看看从4.0到4.1, 5.0, 5.1各个版本的主要特性变更：

4.0没用过，从2005年选型时使用4.1的版本，现在5.1已经从2007年9月份就Candidate Release

C.1.6. Changes in MySQL 5.1.22 (24 September 2007: Release Candidate) 到现在已经快一年了，期待哪天我们能顺利升级到5.1进行一些新的尝试！

 

不过看看5.1的主要特性，分区部分，正如 介绍 的那样，我们在应用层已经实现了，对我们而言没有太大的吸引力，还有现在挺热的 MySQL Proxy ，我们同样在应用层已经进行了实现。

那我们为啥要升级？我得找一些升级的理由！

 

先看看MySQL官方的一些 Nutshell，大概了解，顺便放在这里备忘。

http://dev.mysql.com/doc/refman/4.1/en/mysql-4-0-nutshell.html

1.5.1. MySQL 4.0 in a Nutshell

MySQL 4.0 is available for download at http://dev.mysql.com/ and from our mirrors. MySQL 4.0 has been tested by a large number of users and is in production use at many large sites.

The following features were added in MySQL 4.0:

  • Speed enhancements

    • MySQL 4.0 implemented a query cache that can give a major speed boost to applications with repetitive queries. See Section 7.5.3, "The MySQL Query Cache".

    • MySQL 4.0 further increased the speed of MySQL Server in a number of areas, such as bulk INSERT statements, searching on packed indexes, full-text searching (using FULLTEXT indexes), and COUNT(DISTINCT).

  • InnoDB storage engine as standard

    • The InnoDB storage engine began to be offered as a standard feature of the MySQL server. This provided full support for ACID transactions, foreign keys with cascading UPDATE and DELETE, and row-level locking as standard features. See Section 13.2, "The InnoDB Storage Engine".

  • New functionality

    • The enhanced FULLTEXT search capabilities of MySQL Server 4.0 enabled FULLTEXT indexing of large text masses with both binary and natural-language searching logic. It became possible to customize minimal word length and define your own stop word lists in most human languages, enabling a broader class of applications to be built with MySQL Server. See Section 11.8, "Full-Text Search Functions".

  • Standards compliance, portability, and migration

    • MySQL Server added support for the UNION statement, a standard SQL feature.

    • Starting with version 4.0, MySQL runs natively on Novell NetWare 6.0 and higher. See Section 2.7, "Installing MySQL on NetWare".

    • Features to simplify migration from other database systems to MySQL Server include TRUNCATE TABLE (as in Oracle) .

  • Internationalization

    • German-speaking users should note that MySQL 4.0 added support for a new character set, latin1_de, which ensures that words with umlauts are sorted in the same order as in German telephone books.

  • Usability enhancements

    • As of version 4.0, most mysqld parameters (startup options) can be set without taking down the server. This is a convenient feature for database administrators. See Section 12.5.3, "SET Syntax".

    • Multiple-table DELETE and UPDATE statements were added.

    • On Windows, symbolic link handling at the database level was enabled by default. On Unix, the MyISAM storage engine added support for symbolic linking at the table level (and not just the database level as before).

    • The addition of the SQL_CALC_FOUND_ROWS and FOUND_ROWS() functions made it possible to find out the number of rows a SELECT query that includes a LIMIT clause would have returned without that clause.

  • The Embedded MySQL Server

    The embedded server library added in this release can easily be used to create standalone and embedded applications. The embedded server provides an alternative to using MySQL in a client/server environment.

    The libmysqld embedded server library made MySQL Server suitable for a wider range of applications. Using this library, developers can embed MySQL Server into various applications and electronics devices, where the end user has no knowledge of there actually being an underlying database. Embedded MySQL Server is ideal for use in Internet appliances, public kiosks, turnkey hardware/software combination units, high performance Internet servers, self-contained databases distributed on CD-ROM, and so on.

    The embedded MySQL library uses the same interface as the normal client library. See Section 17.1, "libmysqld, the Embedded MySQL Server Library". Embedded MySQL is available under the same dual-licensing model as the MySQL Server; see http://www.mysql.com/company/legal/licensing/ for more information.

    On Windows there are two different libraries:

    libmysqld.lib Dynamic library for threaded applications.
    mysqldemb.lib Static library for not threaded applications.

 

http://dev.mysql.com/doc/refman/4.1/en/mysql-nutshell.html

1.5.2. MySQL 4.1 in a Nutshell

MySQL Server 4.0 laid the foundation for new features implemented in MySQL 4.1, such as subqueries and Unicode support, which were desired by many of our customers.

MySQL Server 4.1 is currently in production status, and binaries are available for download at http://dev.mysql.com/downloads/mysql/4.1.html. All binary releases pass our extensive test suite without any errors on the platforms on which we test. See Section B.1, "Changes in release 4.1.x (Production)".

For those wishing to use the most recent development source for MySQL 4.1, we also make our BitKeeper repositories publicly available. See Section 2.9.3, "Installing from the Development Source Tree".

The following features are implemented in MySQL 4.1. Features that are available in MySQL 5.0 are described in Section 1.5.3, "What's New in MySQL 5.0".

  • Support for subqueries and derived tables:

    • A "subquery" is a SELECT statement nested within another statement. A "derived table" (an unnamed view) is a subquery in the FROM clause of another statement. See Section 12.2.8, "Subquery Syntax".

  • Speed enhancements:

    • Faster binary client/server protocol with support for prepared statements and parameter binding. See Section 17.2.4, "C API Prepared Statements".

    • BTREE indexing is supported for HEAP tables, significantly improving response time for non-exact searches.

  • Added functionality:

    • CREATE TABLE tbl_name2 LIKE tbl_name1 allows you to create, with a single statement, a new table with a structure exactly like that of an existing table.

    • The MyISAM storage engine added support for OpenGIS spatial types for storing geographical data. See Chapter 16, Spatial Extensions.

    • Support was added for replication over SSL connections.

    • Support for a number of additional storage engines was implemented in the MySQL 4.1 release series:

      Note

      These engine were implemented at different points in the development of MySQL 4.1. Please see the indicated sections for particulars in each case.

  • Standards compliance, portability, and migration:

    • The enhanced client/server protocol available beginning with MySQL 4.1.1 provides the ability to pass multiple warnings to the client, rather than only a single result, making it much easier to track problems that occur in operations such as bulk data loading.

    • SHOW WARNINGS shows warnings for the last command. See Section 12.5.4.21, "SHOW WARNINGS Syntax".

  • Internationalization and Localization:

    • To support applications that require the use of local languages, the MySQL software added extensive Unicode support through the utf8 and ucs2 character sets.

    • Definition of character sets by column, table, and database. This allows for a high degree of flexibility in application design, particularly for multi-language Web sites. See Section 9.1, "Character Set Support".

    • Per-connection time zones support, allowing individual clients to select their own time zones when necessary.

  • Usability enhancements:

    • The addition of a server-based HELP command that can be used to get help information for SQL statements. This information is always applicable to the particular server version being used. Because this information is available by issuing an SQL statement, any client can access it. For example, the help command of the mysql command-line client has been modified to have this capability.

    • The improved client/server protocol allows multiple statements to be issued with a single call, and for returning multiple result sets. See Section 17.2.9, "C API Handling of Multiple Statement Execution".

    • The syntax INSERT ... ON DUPLICATE KEY UPDATE ... was implemented. This allows you to update an existing row if the insert would have caused a duplicate value for a primary or unique index. See Section 12.2.4, "INSERT Syntax".

    • The aggregate function GROUP_CONCAT(), added the capability to concatenate column values from grouped rows into a single result string. See Section 11.11, "Functions and Modifiers for Use with GROUP BY Clauses".

 

http://dev.mysql.com/doc/refman/5.0/en/mysql-nutshell.html

1.5.1. What's New in MySQL 5.0

The following features are implemented in MySQL 5.0.

  • BIT Data Type: Can be used to store numbers in binary notation. See Section 10.1.1, "Overview of Numeric Types".

  • Cursors: Elementary support for server-side cursors. For information about using cursors within stored routines, see Section 19.2.9, "Cursors". For information about using cursors from within the C API, see Section 24.2.7.3, "mysql_stmt_attr_set()".

  • Information Schema: The introduction of the INFORMATION_SCHEMA database in MySQL 5.0 provided a standards-compliant means for accessing the MySQL Server's metadata; that is, data about the databases (schemas) on the server and the objects which they contain. See Chapter 22, INFORMATION_SCHEMA Tables.

  • Instance Manager: Can be used to start and stop the MySQL Server, even from a remote host. See Section 4.6.9, "mysqlmanager -- The MySQL Instance Manager".

  • Precision Math: MySQL 5.0 introduced stricter criteria for acceptance or rejection of data, and implemented a new library for fixed-point arithmetic. These contributed to a much higher degree of accuracy for mathematical operations and greater control over invalid values. See Chapter 23, Precision Math.

  • Storage Engines: Storage engines added in MySQL 5.0 include ARCHIVE and FEDERATED. See Section 13.8, "The ARCHIVE Storage Engine", and Section 13.7, "The FEDERATED Storage Engine".

  • Stored Routines: Support for named stored procedures and stored functions was implemented in MySQL 5.0. See Chapter 19, Stored Procedures and Functions.

  • Strict Mode and Standard Error Handling: MySQL 5.0 added a strict mode where by it follows standard SQL in a number of ways in which it did not previously. Support for standard SQLSTATE error messages was also implemented. See Section 5.1.6, "SQL Modes".

  • Triggers: MySQL 5.0 added limited support for triggers. See Chapter 20, Triggers, and Section 1.8.5.3, "Stored Routines and Triggers".

  • VARCHAR Data Type: The effective maximum length of a VARCHAR column was increased to 65,535 bytes, and stripping of trailing whitespace was eliminated. (The actual maximum length of a VARCHAR is determined by the maximum row size and the character set you use. The maximum effective column length is subject to a row size of 65,535 bytes, which is shared among all columns.) See Section 10.4, "String Types".

  • Views: MySQL 5.0 added support for named, updatable views. See Chapter 21, Views, and Section 1.8.5.5, "Views".

  • XA Transactions: See Section 12.4.7, "XA Transactions".

    MySQL Enterprise.  For assistance in maximizing your usage of the many new features of MySQL, subscribe to MySQL Enterprise. For more information see http://www.mysql.com/products/enterprise/advisors.html.

  • Performance enhancements: A number of improvements were made in MySQL 5.0 to improve the speed of certain types of queries and in the handling of certain types. These include:

    • MySQL 5.0 introduces a new "greedy" optimizer which can greatly reduce the time required to arrive at a query execution plan. This is particularly noticeable where several tables are to be joined and no good join keys can otherwise be found. Without the greedy optimizer, the complexity of the search for an execution plan is calculated as N!, where N is the number of tables to be joined. The greedy optimizer reduces this to N!/(D-1)!, where D is the depth of the search. Although the greedy optimizer does not guarantee the best possible of all execution plans (this is currently being worked on), it can reduce the time spent arriving at an execution plan for a join involving a great many tables -- 30, 40, or more -- by a factor of as much as 1,000. This should eliminate most if not all situations where users thought that the optimizer had hung when trying to perform joins across many tables.

    • Use of the Index Merge method to obtain better optimization of AND and OR relations over different keys. (Previously, these were optimized only where both relations in the WHERE clause involved the same key.) This also applies to other one-to-one comparison operators (>, <, and so on), including = and the IN operator. This means that MySQL can use multiple indexes in retrieving results for conditions such as WHERE key1 > 4 OR key2 < 7 and even combinations of conditions such as WHERE (key1 > 4 OR key2 < 7) AND (key3 >= 10 OR key4 = 1). See Section 7.2.6, "Index Merge Optimization".

    • A new equality detector finds and optimizes "hidden" equalities in joins. For example, a WHERE clause such as 

      t1.c1=t2.c2 AND t2.c2=t3.c3 AND t1.c1 < 5

      implies these other conditions 

      t1.c1=t3.c3 AND t2.c2 < 5 AND t3.c3 < 5

      These optimizations can be applied with any combination of AND and OR operators. See Section 7.2.9, "Nested Join Optimization", and Section 7.2.10, "Outer Join Simplification".

    • Optimization of NOT IN and NOT BETWEEN relations, reducing or eliminating table scans for queries making use of them by mean of range analysis. The performance of MySQL with regard to these relations now matches its performance with regard to IN and BETWEEN.

    • The VARCHAR data type as implemented in MySQL 5.0 is more efficient than in previous versions, due to the elimination of the old (and nonstandard) removal of trailing spaces during retrieval.

    • The addition of a true BIT column type; this type is much more efficient for storage and retrieval of Boolean values than the workarounds required in MySQL in versions previous to 5.0.

    • Performance Improvements in the InnoDB Storage Engine:

      • New compact storage format which can save up to 20% of the disk space required in previous MySQL/InnoDB versions.

      • Faster recovery from a failed or aborted ALTER TABLE.

      • Faster implementation of TRUNCATE.

      (See Section 13.2, "The InnoDB Storage Engine".)

    • Performance Improvements in the NDBCluster Storage Engine:

      • Faster handling of queries that use IN and BETWEEN.

      • Condition pushdown: In cases involving the comparison of an unindexed column with a constant, this condition is "pushed down" to the cluster where it is evaluated in all partitions simultaneously, eliminating the need to send non-matching records over the network. This can make such queries 10 to 100 times faster than in MySQL 4.1 Cluster.

        See Section 12.3.2, "EXPLAIN Syntax", for more information.

 

http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html

1.5.1. What's New in MySQL 5.1

The following features have been added to MySQL 5.1.

  • Partitioning.  This capability enables distributing portions of individual tables across a filesystem, according to rules which can be set when the table is created. In effect, different portions of a table are stored as separate tables in different locations, but from the user point of view, the partitioned table is still a single table. Syntactically, this implements a number of new extensions to the CREATE TABLE, ALTER TABLE, and EXPLAIN ... SELECT statements. As of MySQL 5.1.6, queries against partitioned tables can take advantage of partition pruning. In some cases, this can result in query execution that is an order of magnitude faster than the same query against a non-partitioned version of the same table. See Chapter 18, Partitioning, for further information on this functionality. (Author: Mikael Ronström)

  • Row-based replication.  Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication. As of MySQL 5.1.5, another basis for replication is available. This is called row-based replication. Instead of sending SQL statements to the slave, the master writes events to its binary log that indicate how individual table rows are effected. As of MySQL 5.1.8, a third option is available: mixed. This will use statement-based replication by default, and only switch to row-based replication in particular cases. See Section 16.1.2, "Replication Formats". (Authors: Lars Thalmann, Guilhem Bichot, Mats Kindahl)

  • Plugin API.  MySQL 5.1 adds support for a very flexible plugin API that enables loading and unloading of various components at runtime, without restarting the server. Although the work on this is not finished yet, plugin full-text parsers are a first step in this direction. This allows users to implement their own input filter on the indexed text, enabling full-text search capability on arbitrary data such as PDF files or other document formats. A pre-parser full-text plugin performs the actual parsing and extraction of the text and hands it over to the built-in MySQL full-text search. See Section 29.2, "The MySQL Plugin Interface". (Author: Sergey Vojtovich)

  • Event scheduler.  MySQL Events are tasks that run according to a schedule. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a "cron job") or the Windows Task Scheduler. See Chapter 22, Event Scheduler. (Author: Andrey Hristov)

  • Server log tables.  Before MySQL 5.1, the server writes general query log and slow query log entries to log files. As of MySQL 5.1, the server's logging capabilities for these logs are more flexible. Log entries can be written to log files (as before) or to the general_log and slow_log tables in the mysql database. If logging is enabled, either or both destinations can be selected. The --log-output option controls the destination or destinations of log output. See Section 5.2.1, "Selecting General Query and Slow Query Log Output Destinations". (Author: Petr Chardin)

  • Upgrade program.  The mysql_upgrade program (available as of MySQL 5.1.7) checks all existing tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade. See Section 4.4.8, "mysql_upgrade -- Check Tables for MySQL Upgrade". (Authors: Alexey Botchkov, Mikael Widenius)

  • MySQL Cluster replication.  Replication between MySQL Clusters is now supported. It is now also possible to replicate between a MySQL Cluster and a non-cluster database. See Section 17.12, "MySQL Cluster Replication".

  • MySQL Cluster disk data storage.  In MySQL versions previous to 5.1.6, the NDBCluster storage engine was strictly in-memory; beginning with MySQL 5.1.6, it is possible to store Cluster data (but not indexes) on disk. This allows MySQL Cluster to scale upward with fewer hardware (RAM) requirements than previously. In addition, the Disk Data implementation includes a new "no-steal" restoration algorithm for fast node restarts when storing very large amounts of data (terabyte range). See Section 17.13, "MySQL Cluster Disk Data Tables".

  • Improved backups for MySQL Cluster.  A fault arising in a single data node during a Cluster backup no longer causes the entire backup to be aborted, as occurred in previous versions of MySQL Cluster.

  • Backup of tablespaces.  The mysqldump utility now supports an option for dumping tablespaces. Use -Y or --all-tablespaces to enable this functionality.

  • Improvements to INFORMATION_SCHEMA.  MySQL 5.1 provides much more information in its metadata database thasn was available in MySQL 5.0. New tables in the INFORMATION_SCHEMA database include FILES, EVENTS, PARTITIONS, PROCESSLIST, ENGINES, and PLUGINS.

  • XML functions with XPath support.  ExtractValue() returns the content of a fragment of XML matching a given XPath expression. UpdateXML() replaces the element selected from a fragment of XML by an XPath expression supplied by the user with a second XML fragment (also user-supplied), and returns the modified XML. See Section 11.10, "XML Functions". (Author: Alexander Barkov)

  • Load emulator.  The mysqlslap program is designed to emulate client load for a MySQL server and report the timing of each stage. It works as if multiple clients were accessing the server. See Section 4.5.7, "mysqlslap -- Load Emulation Client". (Authors: Patrick Galbraith, Brian Aker)

 

 

--EOF--

Categories:

Tags:

No TrackBacks

TrackBack URL: http://www.guduo.net/cgi-bin/mt/mt-tb.cgi/66

Leave a comment

Categories

Monthly Archives

Pages

快速链接

May 2016

Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Search

About this Entry

This page contains a single entry by 谷多 published on April 19, 2008 12:03 PM.

家有小女：谷多 was the previous entry in this blog.

MySQL5.1 InnoDB引擎自增列的重大更新特性 New InnoDB Auto-Increment Functionality (MySQL 5.1.22) is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.