OReilly - Manage & Using Mysql.pdf - MySQL - kaktusss_82

DRAFT, 8/20/01

Chapter 1

MySQL

It was not too long ago that you were simply out of luck if you wanted to build an

application backed by a robust database on a small budget. Your choices were

quite simple: shell out thousands—or even tens or hundreds of thousands—of dol-

lars on Oracle or Sybase or build the application against “toy” databases like

Access and FileMakerPro. Thanks to databases like mSQL, ProgreSQL, and MySQL,

however, you now have a variety of choices that suit different needs. This book,

of course, is the story of MySQL.

The History of MySQL

This story actually goes back to 1979 when MySQL’s inventor, Michael Widenius

(a.k.a. Monty) developed an in-house database tool called UNIREG for managing

databases. UNIREG is a tty interface builder that uses a low-level connection to an

ISAM storage with indexing. In the years that have followed, UNIREG has been

rewritten in several different languages and extended to handle big databases. It is

still available today, but it is largely supplanted by MySQL.

The Swedish company TcX * began developing Web-based applications in 1994

and used UNIREG to support this effort. Unfortunately, UNIREG created too much

overhead to be successful in dynamically generating Web pages. TcX thus began

looking at alternatives.

TcX looked at SQL and mSQL. mSQL was a cheaply available database manage-

ment system that gave away its source code with database licenses—almost Open

Source. At the time, mSQL was still in its 1.x releases and had even fewer features

* For most of its existence, TcX had a single employee: Monty.

DRAFT, 8/20/01

than the currently available version. Most important to Monty, it did not support

any indices. mSQL’s performance was therefore poor in comparison to UNIREG.

Monty contacted David Hughes, the author of mSQL, to see if Hughes would be

interested in connecting mSQL to UNIREG’s B+ ISAM handler to provide indexing

to mSQL. Hughes was already well on his way to mSQL 2, however, and had his

indexing infrastructure in place. TcX decided to create a database server that was

more compatible with its requirements.

TcX was smart enough not to try to reinvent the wheel. It built upon UNIREG and

capitalized on the growing number of third-party mSQL utilities by writing an API

into its system that was, at least initially, practically identical to the mSQL API. As a

result, an mSQL user who wanted to move to TcX’s more feature-rich database

server would only have to make trivial changes to any existing code. The code

supporting this new database, however, was completely original.

By May 1995, TcX had a database that met its internal needs—MySQL 1.0. A busi-

ness partner, David Axmark at Detron HB, began pressing TcX to release this

server on the Internet and follow a business model pioneered by Aladdin’s L. Peter

Deutsch. Specifically, this business model enabled TcX developers to work on

projects of their own choosing and release the results as free software. Commer-

cial support for the software generated enough income to create a comfortable lif-

estyle. The result is a very flexible copyright that makes MySQL “more free” than

mSQL. Eventually, Monty released MySQL under the GPL so that MySQL is now

“free as in speech” and “free as in beer”.

As for the name MySQL, Monty says, “It is not perfectly clear where the name

MySQL derives from. TcX’s base directory and a large amount of their libraries and

tools have had the prefix ’my’ for well over 10 years. However, my daughter

(some years younger) is also named My. So which of the two gave its name to

MySQL is still a mystery.”

A few years ago, TcX evolved into the company MySQL AB at http://www.mysql.

com . This change better enables its commercial control of the development and

support of MySQL. MySQL AB is a Swedish company run by MySQL’s core devel-

opers. MySQL AB owns the copyright to MySQL as well as the trademark “MySQL”.

Since the initial Internet release of MySQL, it has been ported to a host of Unix

operating systems (including Linux, FreeBSD, and Mac OS X), Win32, and OS/2.

MySQL AB estimates that MySQL runs on about 500,000 severs.

MySQL Design

Working from the legacy of mSQL, TcX decided MySQL had to be at least as fast

as mSQL with a much greater feature set. At that time, mSQL defined database per-

DRAFT, 8/20/01

www.mysql.com vs. www.mysql.org

As this book is being written, MySQL AB has no control over the mysql.org

domain. Until June 2001, mysql.org was owned by someone else who had sim-

ply pointed it to the mysql.com domain. On June 4, 2001, a company called

NuSphere bought the mysql.org domain and put up a different site. MySQL AB

requested the domain be transferred to them, but NuSphere refused.

MySQL AB claims that NuSphere is distributing non-GPL, proprietary code with

MySQL in violation of MySQL’s GPL license in addition to hijacking the MySQL

trademark. NuSphere, on the other hand, claims it is not violating the GPL and

that MySQL signed a contract to allow NuSphere to use the MySQL trademark.

Both sides have sued each other, and the matter is now before the courts.

Regardless of where your sympathies lie on the issue or the eventual outcome,

the official source of MySQL is MySQL AB at www.mysql.com .

formance, so TcX’s goal was no small task. MySQL’s specific design goals were

speed, robustness, and ease of use. To get this sort of performance, TcX decided

to make MySQL a multithreaded database engine. A multithreaded application per-

forms many tasks at the same time just as if multiple instances of that application

were running simultaneously. Fortunately, multithreaded applications do not pay

the very expensive cost of starting up new processes.

In being multithreaded, MySQL has many advantages. A separate thread handles

each incoming connection with an extra thread always running in order to man-

age the connections. Multiple clients can perform read operations simultaneously

without impacting one another. Write operations, on the other hand, only hold up

other clients that need access to the tables being updated. While any thread is

writing to a table, all other threads requesting access to that table simply wait until

the table is free. Your client can perform any allowed operation without concern

for other concurrent connections. The connection-managing thread prevents other

threads from reading or writing to a table in the middle of an update.

Figure 1-1 illustrates the multithreaded nature of a MySQL database server. Another

advantage of this architecture is inherent to all multithreaded applications: even

though the threads share the same process space, they execute individually.

Because of this separation, multiprocessor machines can spread the load of each

of the threads across the many CPUs.

In addition to the performance gains introduced by multithreading, MySQL has a

richer subset of SQL than mSQL. MySQL supports over a dozen data types and

additionally supports SQL functions. Your application can access these functions

through ANSI SQL statements.

DRAFT, 8/20/01

Local clients

MySQL

Server

Queries

Remote clients

Figure 1-1. The client/sever design of MySQL

The Great Transaction Debate

Transactions are a relatively new feature of the MySQL database engine. It is,

in fact, a feature that is not present unless you set up your tables to support it.

A lot of people wondered for a long time what use MySQL was without trans-

actions and why they would set up tables without support for transactions. The

answer is one word: performance.

The minute you introduce transactions into the picture, the database takes a

performance hit. Transactions add the overhead of complex locking and trans-

action logging. The complex locking includes support for something called

transaction isolation levels. We will discuss transaction isolation levels in

Chapter 9. Basically, however, increasing transaction isolation levels require an

increasing amount of work by the database to support the same functionality.

The more work the database has to do for a task, the slower it performs that

task.

Transactions are important for applications that support complex rules for the

concurrent updating data. They prevent concurrent updates from leaving the

database in an inconsistent state at any point in its life. The most common use

for MySQL, on the other hand, is to drive dynamic Web content. This use is

“heavy read” in that 80% or more of the work is reading from the database. In

such an environment, support for transactions is generally not needed. Perfor-

mance, however, is.

DRAFT, 8/20/01

MySQL actually extends ANSI SQL with a few features. These features include new

functions ( ENCRYPT , WEEKDAY , IF , and others), the ability to increment fields

( AUTO_INCREMENT and LAST_INSERT_ID ), and case sensitivity.

MySQL did intentionally omit some SQL features found in the major database

engines. For the longest time, transactions support was the most notable omis-

sion. The latest releases of MySQL, however, now provide some support for trans-

actions. Stored procedures, another notable omission, are scheduled for a future

release. Finally, MySQL does not support most additions to the SQL standard as of

SQL3. The most important SQL3 feature missing from MySQL is support for object-

oriented data types.

Since 1996, MySQL AB has been using MySQL internally in an environment with

more than 40 databases containing 10,000 tables. Of these 10,000 tables, more than

500 contain over seven million records—about 100 GB of data.

MySQL Features

We have already mentioned multithreading as a key feature to support MySQL’s

performance design goals. It is the core feature around which MySQL is built.

Other features include:

Openness

MySQL is open in every sense of the term. Its SQL dialect uses ANSI SQL2 as

its foundation. The database engine runs on countless platforms, including

Windows 2000, Mac OS X, Linux, FreeBSD, and Solaris. If no binary is avail-

able for your platform, you have access to the source to compile to that plat-

form.

Application support

MySQL has an API for just about any programming language. Specifically, you

can write database applications that access MySQL in C, C++, Eiffel, Java, Perl,

PHP, Python, and Tcl. In this book, we cover C, C++, Java, Perl, and PHP.

Cross-database joins

You can construct MySQL queries that can join tables from different databases.

Outer join support

MySQL supports both left and right outer joins using both ANSI and ODBC

syntax.

Internationalization

MySQL supports several different character sets, including ISO-8859-1, Big5,

and Shift-JIS. It also supports sorting for different character sets and can be

customized easily. Error messages can also be provided in different languages.

OReilly - Manage & Using Mysql.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: