MySQL manual version 5.0.20-mysql optimization (i)

mysql| optimization

7 MySQL Optimization

Database optimization is a complex task because it ultimately requires a good understanding of system optimization. Even though the system or application system does not know much about the optimization effect is good, but if you want to optimize the effect of better, then you need to know more about it.

This chapter mainly explains several methods of optimizing MySQL, and gives examples. Remember, there are always ways to make the system run faster, and of course, it takes more effort.

7.1 Optimization Overview

The most important factor in making the system run fast is the basic design of the database. And you have to be aware of what your system is going to do, and the bottlenecks that exist.

The most common system bottlenecks are the following:

Disk Search. It slowly searches the disk for blocks of data. For modern disks, the average search time is basically less than 10 milliseconds, so it's theoretically possible to do 100 disk searches per second. This time is not much improved for new disks, and this is true for only one table. The way to speed up search time is to separate the data into multiple disks.

Disk read/write. When the disk is in the correct position, you need to read the data. For modern disks, the disk throughput is at least 10-20mb/seconds. This is easier than the optimization of disk search because data can be read in parallel from multiple media.

CPU cycles. The data is stored in main memory (or it is already in main memory), which requires processing the data to get the desired results. There are more than one? Shang naphthalene ke This is the  of the Meconematidae of the Sac and  pregnant "砝 this  Minamata tough ǔ2 king Qiao 狻?"

Memory bandwidth. When the CPU is to store more data in the CPU cache, the main memory bandwidth is the bottleneck. This is not a common bottleneck in most systems, but it is also a factor to be aware of.

Limitations of 7.1.1 MySQL design

When using the MyISAM storage engine, MySQL uses a fast data table lock to allow multiple reads and one write at a time. The biggest problem with this storage engine is that it takes place on a single table with stable update operations and slow queries. If this situation exists in a table, you can use a different table type. Please see "MySQL Storage engines and Table Types" for details.

MySQL can work in both transaction and non transaction tables. In order to be able to use a non-transaction table smoothly (cannot be rolled back when an error occurs), there are several rules:

All fields have default values

If you insert an "error" value into a field, such as inserting an excessive value into a numeric Type field, MySQL will place the field value as the "most likely value" instead of giving an error. The value of a numeric type is 0, the smallest or largest possible value. String type, not an empty string is the maximum length that a field can store.

All evaluation expressions return a value and report a condition error, for example, 1/0 returns NULL.

These rules implicitly mean that you cannot use MySQL to check the contents of a field. Instead, it must be checked in the application before it is stored in the database. For details, see "1.8.6 how MySQL deals with Constraints and" 14.1.4 INSERT Syntax ".

Portability of 7.1.2 application design

Because the various databases implement their own SQL standards, this requires that we try to use portable SQL applications. Query and insert operations are easy to migrate, but are more difficult because of the more restrictive requirements. It becomes more difficult to make an application run fast on a variety of database systems.

In order for a complex application to be portable, look first at what kind of database system the application is running on, and then see what features the database system supports.

There are some deficiencies in each database system. In other words, due to some compromise in design, the performance difference is caused.

You can use the MySQL crash-me program to look at the selected database server can be used on functions, types, restrictions and so on. Crash-me does not examine the various possible features, but it is still a reasonable understanding that about 450 tests have been done.

An example of a crash-me type of information is that it tells you that you cannot make a field name longer than 18 characters if you want to use Informix or DB2.

The CRASH-ME program and MySQL benchmarks are implemented for each quasi-database. By reading how these benchmark programs are written, you can probably do what you can to make the program independent of the various database ideas. These programs can be found in the ' sql-bench ' directory of the MySQL source code. Most of them are written in Perl and use the DBI interface. Because it provides a variety of access methods independent of the database, DBI is used to solve various porting problems.

To see the results of crash-me, you can access: http://dev.mysql.com/tech-resources/crash-me.php. Access Http://dev.mysql.com/tech-resources/benchmarks can see the results of the benchmark.