Instance analysis: MySQL database optimization details

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.

This chapter describes several methods to optimize MySQL and provides examples. Remember, there are always ways to make the system run faster. Of course, this requires more efforts.

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. Multiple? Where can I find myself? Is there a high frequency capsule? Pregnancy "does this bear? Why?

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. For details, see "15 MySQL Storage Engines and Table Types ".

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. For details, see "1.8.6 How MySQL Deals with Constraints and" 14.1.4 INSERT Syntax ".

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.

• Four pages in total:
• Previous Page
• 1
• 2
• 3
• 4
• Next Page