MySQL database optimization (1)

The database optimization tutorial of the Helper House (www.bkjia.com) is very complicated, 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 ".

　　

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