Open-source database MySQL optimization tips (1) [figure] _ MySQL

Open-source database MySQL optimization tips (1) [figure] database optimization is a complex task, because it requires a good understanding of system optimization. Although the optimization effect is good if you do not know much about the system or application system, you need to know more about it if you want to optimize it better.

1. Optimization overview

The most important factor in making the system run faster is the basic design of the database. You must also know what your system is going to do and the bottlenecks.

The most common system bottlenecks are:

Disk search. It slowly searches for data blocks in the disk. For modern disks, the usual search time is basically less than 10 milliseconds, so theoretically 100 disk searches can be performed per second. This time is not much improved for new disks, and it is also true if there is only one table. The method to accelerate the search time is to store data separately to 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-20 MB/second. This is easier than disk search optimization because data can be read in parallel from multiple media.

CPU cycle. The data is stored in the primary memory (or it is already in the primary memory), so you need to process the data to get the desired result.

Memory bandwidth. When the CPU needs to store more data in the CPU cache, the bandwidth of the main memory is the bottleneck. In most systems, this is not a common bottleneck, but it is also a factor worth attention.

1.1 Limitations of MySQL design

When the MyISAM storage engine is used, MySQL uses a quick data table lock to allow multiple reads and one write at a time. The biggest problem with this storage engine is that it performs stable update operations and slow queries on a single table at the same time. If this condition exists in a table, you can use another table type.

MySQL can work in both transactions and non-transaction tables. To smoothly use non-transaction tables (rollback is not allowed when an error occurs), there are several rules:

All fields have default values.

If an "error" value is inserted in the field, for example, an excessively large value is inserted in a numeric field, mySQL sets this field value to "the most likely value" instead of an error. The value of the numeric type is 0, the minimum or maximum possible value. String type. if it is not a null string, it is the maximum length that the field can store.

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

These rules imply that you cannot use MySQL to check the field content. On the contrary, you must check in the application before storing it to the database.

1.2 portability of application design

Because different databases implement their own SQL standards, we need to use portable SQL applications as much as possible. The query and insert operations can be easily transplanted, but more constraints make it more difficult. It becomes more difficult to make an application run quickly on various database systems.

To make a complex application portable, you must first look at the database system on which the application runs, and then see what features these database systems support. Each database system has some shortcomings. That is to say, some design compromises lead to performance differences.

You can use the MySQL crash-me program to view the functions, types, and restrictions that can be used on the selected database server. Crash-me does not check various possible features, but it is still reasonable to understand that about 450 tests were performed. An example of the crash-me information type is that it tells you that if you want to use Informix or DB2, the field name cannot exceed 18 characters.

The crash-me program and MySQL benchmark enable each quasi-database. By reading how these benchmark programs are written, you can probably think about how to make the program independent of various databases. These programs can be found in the 'SQL-encoding' Directory of the MySQL source code. Most of them are written in Perl and use the DBI interface. Because it provides various access methods independent from databases, DBI is used to solve various portability problems.

If you want to achieve database independence, you need to have some good ideas on the bottlenecks of various SQL servers. For example, MySQL is very fast in searching and updating records for tables of the MyISAM type, but there are some problems when reading and writing records at a slow speed in concurrency. As an Oracle database, it has a major problem accessing the newly updated records (until the results are refreshed to the disk ). The transaction database generally does not perform very well in generating summary tables from the log table, because in this case, the row record lock is almost useless.

To allow applications to be truly independent from databases, you must define simple and scalable interfaces for operating data. Since C ++ can be used in many systems, it is appropriate to use C ++ as the base class result of the database.

If you use specific functions that are unique to some databases (for example, REPLACE statements are unique only in MySQL), you need to compile an alternative method to implement this function in other databases. Although these alternatives may be slow, they allow other databases to implement the same functionality.

In MySQL, you can use/* in the query statement /*! */Syntax to add MySQL-specific keywords. However, in many other databases,/**/is treated as a comment (and ignored ).

If higher performance is more important than data results accuracy, as in some Web applications, a single application layer can be used to cache results, which may have higher performance. Update the cache reasonably by allowing the old data to expire after a certain period of time. This is a method for handling load peaks. in this case, you can increase the cache capacity and expiration time until the load tends to be normal.

In this case, the table creation information includes the capacity of the cache initialization and the frequency of refreshing data tables normally. An optional solution for caching at the application layer is to use the query cache of MySQL ). After the query cache is enabled, the database determines which results can be reused based on some details. It greatly simplifies the application.