MySQL optimization method
DatabaseOptimization is a complex task.
WorkBecause 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.
Database optimizationIt is a very complicated 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. Faster search timeMethodIs to separately store data in 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.1MySQLDesign limitations
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 stored in the MySQL sourceCodeIn the 'SQL-Hangzhou' directory. 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 is generallyLogsGenerating a summary table in a table is not doing very well, 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.
1.3 What do we use MySQL?
During the initial development of MySQL, MySQL was intended for major customers. They are two of the largest retailers in Sweden and are used for cargo storage data management.
We get a cumulative transaction profit from all the stores every week, so as to provide useful information to shop owners and help them analyze if they better advertise to influence their customers.
The data volume is quite large (the cumulative result of each month is about 7 million), and the data for 4-10 years needs to be displayed. We get the customer's requirements every week, and they want to get the latest data reports in an instant.
We store all the information of each month in a compressed 'transactional 'table to solve this problem. We have some simple macro instruction sets, which can be grouped by field (product group, customer id, store, etc.) in the stored 'transactional 'table based on different standards. We use a small Perl script to dynamically generate reports in the form of Web pages. This script parses the Web page, executes the SQL statement, and inserts the result. We can also use PHP or mod_perl for this job, but we didn't have these two tools yet.
To obtain the graphic data, we also wrote a simple C language tool for executing SQL queries and making the results into GIFImage. This tool is also dynamically executed after Perl scripts parse Web pages.
In many cases, you only need to copy the existing script and simply modify the SQL query statement in it to generate a new report. Sometimes, you need to add more fields to the existing accumulative table or create a new one. This operation is very simple, because we store all the transaction tables on the disk (a total of about 50 GB transaction tables and 20 GB of other customer data ).
We also allow customers to directly access the cumulative table through ODBC. in this way, advanced users can use the data for their own experiments. The system works well and there is no problem processing data on a moderate Sun Ultra iSCSI Workstation (2x200 MHz. Finally, the system is transplanted to Linux.
1.4 MySQL benchmark suite
The benchmark suite is to tell the user what kind of SQL query performance is better or worse. Note that this benchmark is single-threaded, so it measures the minimum operation execution time. We plan to add a benchmark suite for multi-threaded testing in the future.
To use the benchmarking suite, you must meet the following requirements:
The benchmark script is written in Perl. it uses the Perl DBI module to connect to the database. Therefore, the DBI module must be installed. In addition, each server to be tested must have a specific BDB driver. For example, to test MySQL, PostgreSQL, and DB2, you mustInstallDBD: mysql, DBD: Pg and DBD: DB2 module. For details, see "2.7 Perl Installation Note ".
After obtaining the MySQL distribution source code, you can see the reference suite in the 'SQL-quota' directory. To run these benchmark tests, first set up the service, enter the 'SQL-detail' directory, and run the run-all-tests script:
Shell> cd SQL-example
Shell> perl run-all-tests -- server = server_name
Server_name can be any available service. To list all available options and supported services, call the following command:
Shell> perl run-all-tests -- help
The crash-me script is also stored in the 'SQL-encoding' directory. Crash-me tries to determine what features, performance, and restrictions the database supports by executing real queries. For example, it can determine:
· What field types are supported
· Number of indexes supported
· Supported functions
· How many queries are supported
· What types of VARCHAR fields are supported?
1.5 Use your own benchmark
Make sure to benchmark your database or application to discover their bottlenecks. After solving this bottleneck (or using a fake module instead), you can easily find the next bottleneck. Even if the current overall performance of the application is acceptable, you should at least make a good plan to locate each bottleneck. Maybe one day you want the application to have better performance.
You can find a portable and portable benchmark test program from the MySQL benchmark suite. For details, see "7.1.4 The MySQL Benchmark Suite ". You can modify any program in the benchmarking suite to suit your needs. You can solve the problem in different ways and know which program is the fastest.
When the system load is very heavy, problems usually occur. We have many customers who contact us to say that they have a (tested) production system that has also encountered load problems. In many cases, performance problems are caused by factors such as the basic design of the database (for example, poor performance of scanning data tables under high loads), operating system, or library. Many times, these problems are easier to solve before they are officially used for production.
2. optimize SELECT statements and other queries
First, one factor that affects all statements is that the more complex your permission settings are, the higher the overhead is. Using simple GRANT statements allows MySQL to reduce the overhead of permission checks when executing statements on the client. For example, if no table-level or field-level permissions are set, the server does not need to check the records of tables_priv and columns_priv tables. Similarly, if no resource limit is set for the account, the server does not need to perform resource usage statistics. If a large number of queries exist, it is worthwhile to spend some time planning a simple authorization mechanism to reduce the overhead of server permission check.
If the problem lies in some specific MySQL expressions or functions, you can use the BENCHMARK () function in the mysql client to perform a regular test. Its syntax is: BENCHMARK (loop_count, expression ). For example:
All MySQL functions should be optimized, but there are still some function exceptions. BENCHMARK () is a very good tool used to check whether there is a problem in the query statement.