Version 4 of MySQL (http://www.mysql.com) has been in development since 2001. By the time you read this, MySQL 4.0 should be a stable release (or at least be in late-beta -- not finished yet, but still quite suitable for development work that you expect to deploy later this year).
Version 4 of MySQL (http://www.mysql.com) has been in development since 2001. By the time you read this, MySQL 4.0 should be a stable release (or at least be in late-beta — not finished yet, but still quite suitable for development work that you expect to deploy later this year).
Most of the development work in 4.0 has focused on three areas: enhancing existing features and performance, adding new features, and changing the architecture of the MySQL software to provide for future growth. There’s simply not enough room here to discuss all of the changes in MySQL 4.0, so we’ll focus on the major enhancements in 4.0, and dig into the most significant new features.
Before we continue, it’s worth mentioning that virtually all of the changes in MySQL 4.0 are completely transparent. In most cases, you’ll be able to upgrade without changing your code (even early releases of MySQL 4.0 passed an extensive compatibility test suite without error).
Enhancements from 3.23
The MySQL developers have added a ton of minor enhancements aimed at boosting the performance and capabilities of MySQL applications. Here are just few of them:
- The MySQL query optimizer is now smarter about using indexes to resolve queries. Some queries that used to require extra sorting are now significantly faster.
- In MySQL 3.23, you needed to recompile MySQL to adjust the full-text index options, such as minimum word length. In 4.0, the full-text index options have been moved into the standard MySQL configuration file, so you’ll only need to restart MySQL to make changes. Many bugs in full-text search have been fixed as well.
- Changes to the key cache code have yielded significant performance improvements during some index-based queries. This is especially helpful on very busy servers.
- If you’ve ever wanted to delete related records from multiple tables at the same time, you’re going to like 4.0′s multi-table deletes. By specifying multiple tables and the correct WHERE clause, MySQL will happily do what you expect. You can also add ORDER BY and LIMIT options to DELETE queries, getting better control over how many records are removed and the order in which records are deleted.
- MySQL’s replication system has been greatly enhanced. Many of the changes were made in anticipation of the upcoming fail-safe replication system. In 4.0, the replication process itself is multi-threaded on slave servers. If the master goes down, it is now much more likely that each slave will have the necessary data to make itself as up-to-date as the master was. The replication logs now contains the necessary transaction markers to ensure that transactions are replicated properly.
- The number of status variables in MySQL has nearly doubled. You can now get a much clearer picture of what’s going on inside of MySQL. Most of the third-party administration tools for MySQL have already been updated to use these new performance statistics.
InnoDB: ACID Transactions and More
MySQL, like the Linux kernel, is modular. You can disable and even remove pieces that you have no need for. Modularity affords MySQL a very important advantage over many other database management systems: you can choose the type of each table at the time it is created. If a few of your tables need fine-grained locking or transactions, you can choose a table type that best suits your needs. You don’t need to have the overhead of transactions on all of your tables. Few other relational database systems offer multiple table types.
As of version 4.0, MySQL has added InnoDB to the list of table types supported “out of the box”. MySQL 4.0 supports five table types: MyISAM, ISAM, HEAP, BDB (Berkeley DB), and InnoDB. BDB and InnoDB are both transactional table types. You can use the standard BEGIN WORK statement followed by several queries and end with either COMMIT or ROLLBACK to complete the transaction. Or, you can run in AUTOCOMMIT mode, so that each query is effectively a separate transaction.
But that’s just the beginning. InnoDB is a very powerful and full-featured database engine that’s been embedded within MySQL. InnoDB also provides:
- Automatic crash recovery. If MySQL is shutdown abnormally, InnoDB will automatically rollback uncommitted transactions and ensure that committed transactions were indeed applied.
- Referential integrity. You can define foreign key constraints between related InnoDB tables to ensure that records cannot be removed from one table if they’re still being referenced from another.
- Row-level locking. If you’ve ever had a long-running query (against MyISAM tables) prevent other queries from running, give InnoDB a try. It uses row-level locking for impressive throughput on even the busies tables.
- Non-locking SELECTs. As if row-level locking wasn’t good enough, the InnoDB engine uses a technique known as multi-versioning (as does PostgreSQL) that eliminates the need to do any locking on basic SELECT queries. You’ll never have to worry about a simple read-only query being blocked by another query that’s making changes to the same table.
Creating InnoDB tables is no harder than creating any other table type. You simply need to specify Type = InnoDB at the end of the CREATE TABLE statement as shown in Figure One.
Figure One: Creating an InnoDB table
CREATE TABLE mytable (
id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
rank INTEGER NOT NULL,
stuff VARCHAR(255) NOT NULL
) Type = InnoDB;
The InnoDB database engine was modeled after Oracle. Users coming from Oracle or PostgreSQL will find a lot to like with the addition of InnoDB to MySQL. For more information, visit http://www.innodb.com/.
MySQL Version 4 sports native SSL capabilities. You’ll no longer need to setup SSH tunneling or use stunnel to establish an encrypted connection between a MySQL client and server. SSL support wasn’t just hacked on either — it was added to the core privilege system. As a result, there are several new options to the GRANT command.
To require SSL for a user, simply add REQUIRE SSL to the GRANT command that you’d normally use. For example:
GRANT ALL PRIVILEGES ON joe_db.* joe@”%”
IDENTIFIED BY ‘adY%fa890′ REQUIRE SSL
The GRANT command above requires the user joe with password adY%fa890 to connect via SSL. If joe attempts to establish an unencrypted connection, MySQL will reject the connection.
MySQL allows you to be even more picky. The previous example allows anyone who knows joe‘s password to connect and manipulate data. The only catch was that the work must be done via an SSL-encrypted session. If you really care about security, you can require joe to have an X509 certificate to prove his identity:
GRANT ALL PRIVILEGES ON joe_db.* joe@”%”
IDENTIFIED BY ‘adY%fa890′ REQUIRE X509
X509 raises the bar quite a bit. Someone would need to fabricate a certificate before getting at the data. Since it’s pretty easy to generate a certificate using the tools bundled with OpenSSL, it’s best to go even a step further and require a valid certificate that was signed by a particular Certificate Authority (CA). The REQUIRE ISSUER syntax allows you to do just that.
Going even further, you can specify that the certificate subject match a pre-determined string (REQUIRE SUBJECT). You can also use REQUIRE CIPHER to set the list of ciphers you’ll allow. (See http://www.mysql.com/doc/S/e/Secure_ GRANT.html for full details.)
Realizing that MySQL is often used as the back-end for web sites built on systems like PostNuke and Slashcode, the MySQL developers implemented a query cache to speed up commonly executed queries. The cache simply stores recently executed SELECT queries and their results in memory. You can configure the amount of memory allocated to the cache by adjusting the query_cache_size variable in your MySQL configuration file.
On a web site that provides news headlines, such as LinuxToday (http://www.linuxtoday.com), you might imagine a query like this running each time a user visits the site:
SELECT headline, summary, time, author
ORDER BY time DESC LIMIT 10
The query would retrieve some information about the 10 most recently added stories. But stories aren’t added very often — maybe only 20 or 30 times a day. With the query cache enabled, MySQL won’t go to the effort of actually running that query unless the data has recently changed. Instead, it’ll pull the results right from memory. The client can’t tell the difference between cached and non-cached results. The end-user, however, might notice the nice snappy performance.
To prevent stale data, MySQL keeps track of all the tables that have any data in the cache. When you execute a query that changes any data in one of those tables, MySQL invalidates the corresponding segments of the query cache. (Full details about the query cache can be found at http://www.mysql.com/doc/Q/u/Query_Cache.html).
A long requested feature, UNION is the standard SQL way to combine the results of multiple SELECT queries into a single result set. Until now, you’d have to execute multiple, separate queries, retrieve the results, and combine them yourself.
SELECT id, balance FROM accounts
WHERE date_est = ’2002-10-04′
SELECT id, balance FROM old_accounts
WHERE date_est = ’2002-10-04′
Using UNION, you can combine those two queries like this:
(SELECT id, balance FROM accounts
WHERE date_est = ’2002-10-04′)
(SELECT id, balance FROM old_accounts
WHERE date_est = ’2002-10-04′)
The individual queries in a UNION can contain ORDER BY clauses or LIMIT restrictions. You can even apply an ORDER BY or LIMIT to the combined result of the UNION. (See http://www.mysql.com/doc/U/N/UNION.html for more on UNION in MySQL).
Looking to 4.1 and beyond…
Development on MySQL 4.1 started a few months into 2002 and is ongoing. 4.1 is slated to have greatly improved support for mixing character sets in a database, sub-queries, multi-table UPDATE statements, foreign key integrity rules, and the beginnings of stored procedures.
In the meantime, check out 4.0. The new features make an already great system even better.
Jeremy Zawodny uses Open Source tools in Yahoo! Finance by day and is is writing a MySQL book for O’Reilly & Associates by night. Reach him at: Jeremy@Zawodny.com.