MySQL

From DocForge

MySQL is a popular relational database server. It's often used for dynamic web sites due to its fast performance for small queries and quick connectivity from languages such as PHP. MySQL is the M in LAMP. It can use multiple storage engines, each tuned for performance in different situations. For scalability across multiple servers it supports database replication. MySQL supports most of the SQL-92 standard.

Contents 1 Supported Operating Systems 2 Scalability 2.1 Replication 3 General Usage (Linux/UNIX) 4 SQL 4.1 SQL Functions 5 Performance 5.1 Optimize Joins and Where Clauses 5.2 Strings 5.3 Delayed Inserts 5.4 Match Data Types 5.5 explain Statement 6 Versions 7 See Also 8 External Links

[edit] Supported Operating Systems

MySQL can run on the following operating systems:

• BSD
• Linux
• Mac OS X
• Microsoft Windows
• UNIX (Solaris, HP-UX, AIX)

[edit] Scalability

[edit] Replication

When one server becomes too busy it's often desirable to add more servers to increase overall application performance. While one large multi-processor server may work well, multiple smaller servers are often more cost effective.

Standard MySQL features master/slave database replication. Master/slave replication is one-way and asynchronous. Both single and multi-master replication are possible, but singe-master replication is generally simpler to maintain and develop with. With single-master replication, one server acts as the master while one or more other servers act as slaves.

In single-master replication, the master server writes updates to binary log files. The logs are tracked with system tables. The binary log files store every update made to the database, both to data and structure. When a slave connects to its master, it informs the master of the position up to which it read the logs at its last successful update. The slave receives any updates that have taken place since that time, and then waits for the master to notify it of new updates.

A chain of servers can be set up by making a slave master to other servers.

When using replication all updates to the database schema that are replicated should be performed on the master server. This avoids conflicts by updating the slave and master separately.

While setting up master/slave replication is relatively easy, applications can be written in various ways to use the multiple servers properly. Special considerations must be made when building an application to scale with database replication. See Programming for multiple databases.

[edit] General Usage (Linux/UNIX)

The server is started by launching the daemon, usually as root. The MySQL processes can be configured to run under a limited user account, which is typically a good idea for security and maintenance.

$ mysqld_safe &

Command line client for executing SQL:

$ mysql -h host -u user -p database

Typical command line options:

-h <host>       : Host to connect to; defaults to localhost
-u <username>   : Username to connect as; defaults to your local user name
-p              : Request password (required if account has a password)
-e "<command>"  : Execute command (SQL) and exit immediately

[edit] SQL

MySQL supports most of the SQL-92 standard. It also has its own set of SQL features in addition to the standard. Using extensions to standard SQL can make code less portable to other database systems.

[edit] SQL Functions

MySQL implements additional and differing scalar functions:

CONCAT(string1, string2, ...)

Concatenates all of the parameters to return one string. If any parameter is null the return value is null. This function differs from the standard CONCATENATE function.

IFNULL(expression, value)

If the expression evaluates to null, return value, else return the evaluated expression.

SUBSTRING(string,position), SUBSTRING(string,position,length)

Returns part of string, starting from position. MySQL implements these in addition to the standard, which use a FROM syntax.

[edit] Performance

While each database needs to be tuned for performance based on the scenario in which it's used, there are some general best practices which can improve performance in most situations.

[edit] Optimize Joins and Where Clauses

• Join tables on indexed columns.
• Avoid OR when possible.

MySQL can sometimes perform poorly when nesting INNER JOINs inside of OUTER JOINs (see SQL#From_Clause for syntax). One way to optimize is to create a temporary table of the nested INNER JOINed tables first, then outer join to the temporary table. For example:

SELECT a.one, b.two, c.three, d.four
FROM a.one 
     LEFT OUTER JOIN (b
          INNER JOIN (c 
               INNER JOIN d ON d.id = c.id
          ON c.id = b.id)
     ON b.id = a.id)

is functionally equivalent to:

CREATE TEMPORARY TABLE b_temp ENGINE=MEMORY
SELECT b.two, c.three, d.four
FROM b INNER JOIN (c 
     INNER JOIN d ON d.id = c.id
     ON c.id = b.id);
 
SELECT a.one, b_temp.two, b_temp.three, b_temp.four
FROM a.one 
     LEFT OUTER JOIN b_temp
     ON b_temp.id = a.id
 
DROP TABLE b_temp;

In some instances the latter will perform much faster.

[edit] Strings

When possible, used fixed length strings. As a general rule, when it's knows that data will have an exact or almost exact length (such as country codes or zip codes), use the CHAR data type. This saves a very small amount of storage space, but also requires less work to join and format for output.

[edit] Delayed Inserts

Use insert delayed when it's not necessary to know when data is written. This reduces some MySQL overhead.

[edit] Match Data Types

Joining columns of identical type reduces query overhead by letting the database quickly match bit-for-bit instead of casting or performing more complex comparisons. So, for example, make all ID columns the same integer size.

[edit] explain Statement

The explain statement shows which indexes are used with a query, along with other information which can help improve query performance.

EXPLAIN SELECT * FROM table_name

Output:

• table
• type - Join type
• possible_keys - Indexes MySQL might use to find the rows in this table. NULL indicates no indexes would help with this query.
• key - The key used in this query. NULL if no index was used.
• key_len
• ref - Columns used with key to retrieve a result.
• rows - The number of rows MySQL must examine to execute the query.
• extra

[edit] Versions

MySQL database server versions 3.23, 4.0, and 4.1 have reached the end of their product life cycle with MySQL AB. There will no longer be active development and general support by the company. Only what MySQL AB calls "Security Level 1" issues will still be fixed for these versions.

Therefore developers should consider the 5.x versions, especially for new development.

[edit] See Also

• Relational database
• SQL

[edit] External Links

• MySQL 5.1 Reference Manual