MySQL optimization strategy-related database commands

We discussed the database performance optimization on the other hand, that is, the use of database server built-in tools to analyze and optimize.

▲show

Perform the following command to understand the running state of the server: MySQL >show status;

This command displays a long list of state variables and their corresponding values, including the number of users who are aborted, the number of connections aborted, the number of attempts to connect, the maximum number of concurrent connections, and many other useful information. This information is useful for determining the causes of system problems and inefficiencies.

In addition to displaying the overall state information of the MySQL server, the show command can display valuable information about log files, specified databases, tables, indexes, processes, and permission tables.

▲explain

Explain is able to analyze the process of the Select command. This is useful not only for determining whether to index tables, but also for understanding the process of MySQL handling complex connections.

The following example shows how to progressively optimize a connection query with the information provided by explain. (This example comes from the MySQL document, see http://www.mysql.com/doc/E/X/EXPLAIN.html.) It seems to be a bit of a scrawl here, especially in this case. ）

The Select command assumed to be parsed with explain looks like this:

EXPLAIN SELECT TT. Ticketnumber, TT. Timein, TT. Projectreference, TT. Estimatedshipdate, TT. Actualshipdate, TT. ClientID, TT. Servicecodes, TT. Repetitiveid, TT. Currentprocess, TT. Currentdpperson, TT. Recordvolume, TT. Dpprinted, et. COUNTRY, et_1.country, do. CustName from TT, ET, et as et_1, do WHERE tt. Submittime is NULL and TT. ACTUALPC = et. Employid and TT. ASSIGNEDPC = Et_1.employid and tt. ClientID = do. CUSTNMBR;

The table that appears in the Select command is defined as follows:

Table Definition

Table column column type tt ACTUALPC char (a) TT ASSIGNEDPC char (a) TT ClientID char (+) et employid char ( 15) do custnmbr Char

Index

Table index TT ACTUALPC tt ASSIGNEDPC tt ClientID et Employid (primary key) Do CUSTNMBR (primary key)

Tt. ACTUALPC value distribution is uneven

Before any optimizations are made, the results of explain's analysis of the Select are as follows:

Table type Possible_keys key Key_len ref rows Extra et all PRIMARY null NULL NULL All PRIMARY null NULL null 2135 et_1 all PRIMARY null NULL NULL ASSIGNEDPC,CLIENTID,ACTUALPC null-null NULL 3872 range checked for each record (key map:35)

The type of each table is all, which indicates that MySQL is fully connected to each table! This operation is time-consuming, because the number of rows to be processed reaches the product of each table row number! That is, the total number of processing lines here is 74 * 2135 * 74 * 3872 = 45,268,558,720.

One of the problems here is that MySQL (also) cannot effectively use the index of a column if the declaration of the database column is different. In this case, varchar and char are the same unless they declare a different length. Due to TT. The ACTUALPC is declared as char (10) and ET. Employid is declared as char (15), so there is a problem with column length mismatch.

To resolve the length mismatch of these two columns, the ALTER Table command extends the ACTUALPC column from 10 characters to 15 characters, as follows: mysql > ALTER table tt MODIFY ACTUALPC VARCHAR (15);

Now TT. ACTUALPC and Et.employid are varchar (15), and the results of performing explain analysis are as follows:

Table type possible_keys key key_len ref rows Extra tt all Assignedpc,clientid , ACTUALPC null null 3872 where used do all PRIMARY null NULL null 2135 range checked for each record (key map:1) et_1 all PRIMARY null NULL NULL 74 range checked for each record (key map:1) et eq_ref PRIMARY PRIMARY tt. ACTUALPC 1

It's not perfect, but it's much better (the product of the number of rows is now less than a factor of 74). This SQL command now takes about a few seconds to execute. To avoid TT. ASSIGNEDPC = Et_1.employid and Tt.clientid = do. The column lengths in the CUSTNMBR comparison do not match, and we can make the following changes:

MySQL > ALTER TABLE tt MODIFY assignedpc VARCHAR, MODIFY ClientID VARCHAR (15);

Now the results of the explain display are as follows:

Table type possible_keys key key_len ref rows Extra et all PRIMARY Null -NULL - null -ASSIGNEDPC,CLIENTID,ACTUALPC TT-ref-ACTUALPC et. Employid where used et_1 eq_ref PRIMARY tt. ASSIGNEDPC 1 do eq_ref PRIMARY PRIMARY tt. ClientID 1

The result has been more satisfying. The remaining problem is that, by default, MySQL assumes that the value of the TT.ACTUALPC column is evenly distributed, whereas in fact the TT table is not. Luckily, we can easily let MySQL know this:

Shell > Myisamchk--analyze path_to_mysql_database/tt shell > Mysqladmin Refresh

Now that the connection operation is ideal, the results of the explain analysis are as follows:

Table type possible_keys key key_len ref rows Extra tt all assignedpc, CLIENTID,ACTUALPC null null 3872 where used et eq_ref PRIMARY tt. ACTUALPC 1 et_1 eq_ref PRIMARY PRIMARY tt. ASSIGNEDPC 1 do eq_ref PRIMARY PRIMARY tt. ClientID 1

▲optimize

Optimize can recover and defragment disk space and data fragmentation, which is necessary once a large number of updates or deletions are made to the table that contains the variable-length rows. Optimize is currently available only for MyISAM and BDB tables.

Conclusion:

Starting from compiling the database server and throughout the management process, there are many factors that can improve MySQL performance, and this article only covers a small part of it. Nevertheless, we hope that the content discussed in this article will help you.