Mysql optimization strategy-related database command _mysql

The next thing we want to discuss is the optimization of database performance on the other hand, the use of database server built tools to help 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. Please visit http://www.mysql.com/doc/S/H/SHOW.html for more information.
▲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 (10)
TT ASSIGNEDPC CHAR (10)
TT ClientID CHAR (10)
ET Employid CHAR (15)
Do custnmbr CHAR (15)

※ 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 74
Do all PRIMARY null null 2135
Et_1 all PRIMARY NULL NULL 74
TT all ASSIGNEDPC,CLIENTID,ACTUALPC 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 NULL 3872 where used
Do all PRIMARY null null 2135
Range checked for each record (key map:1)
Et_1 all PRIMARY 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 (15),
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 74
TT ref ASSIGNEDPC,CLIENTID,ACTUALPC ACTUALPC et. Employid where used
Et_1 eq_ref PRIMARY 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 NULL 3872 where used
ET eq_ref PRIMARY 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: From compiling the database server to start, throughout the management process, can improve the MySQL performance of the factors are very much, this article only involved a small part of it. Nevertheless, we hope that the content discussed in this article will help you.
Copy person Note:
Time is not enough, so there is a problem with the format ~ ~, please see the detailed English original: http://www.devshed.com/Server_Side/MySQL/Optimize/
Or take a look at Chinabyte's article:
Http://www.chinabyte.com/builder/detail.shtm?buiid=1012&parid=1
haha ~ from this point can see that I wholeheartedly for the people to serve