MySQL optimization-related database commands
MySQL optimization-related database commands
Next we will discuss another aspect of database performance optimization, that is, using the built-in tools of the database server to assist in performance analysis and optimization.
▲Show
Run the following command to check the running status of the server:
Mysql> show status;
This command displays a long list of status variables and their corresponding values, including: the number of users that are being accessed, the number of dropped connections, and the number of attempts to connect, the maximum number of concurrent connections and many other useful information. This information is useful for determining the cause of system problems and inefficiency.
The SHOW command not only displays the overall status of the MySQL server, but also displays valuable information about log files, specified databases, tables, indexes, processes, and permission tables. For more information, visit http://www.mysql.com/doc/s/h/show.html.
▲Explain
EXPLAIN can analyze the processing process of the SELECT command. This is not only useful for deciding whether to add an index to a table, but also for understanding how MySQL processes complex connections.
The following example shows how to use the information provided by EXPLAIN to gradually optimize the connection query. (For more information, see http://www.mysql.com/doc/e/x/explain.html. The original article seems to be a bit sloppy here. this example is added .)
Assume that the SELECT command for analyzing With EXPLAIN is as follows:
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 definition in the SELECT command is as follows:
※Table definition
Table 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 employee ID (primary key)
Do CUSTNMBR (primary key)
※Tt. ActualPC value distribution is uneven.
Before any optimization, the EXPLAIN results for the SELECT statement are as follows:
Table type possible_keys key key_len ref rows Extra
Et all primary null 74
Do all primary null 2135
Et_1 all primary null 74
Tt ALL AssignedPC, ClientID, ActualPC 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 quite time-consuming, because the number of rows to be processed reaches the product of the number of rows in each table! That is, the total number of rows processed here is 74*2135*74*3872 = 45,268,558,720.
One of the problems here is that, if the declaration of the database column is different, MySQL (also) cannot effectively use the column index. In this case, VARCHAR and CHAR are the same unless they have different declared lengths. Because tt. ActualPC declares CHAR (10), and et. employee declares CHAR (15), column length mismatch exists.
To solve the length mismatch problem between the two columns, use the alter table command to extend the ActualPC column from 10 characters to 15 characters, as shown below:
Mysql> alter table tt MODIFY ActualPC VARCHAR (15 );
Now tt. ActualPC and et. EMPLOYID are both VARCHAR (15). The analysis result is as follows:
Table type possible_keys key key_len ref rows Extra
Tt ALL AssignedPC, client ID, ActualPC NULL 3872 where used
Do all primary null 2135
Range checked for each record (key map: 1)
Et_1 all primary null 74
Range checked for each record (key map: 1)
Et eq_ref PRIMARY 15 tt. ActualPC 1
This is not perfect, but it is much better (the product of the number of rows is now less than a coefficient of 74 ). It may take several seconds to execute this SQL command.
To avoid mismatched column lengths in tt. AssignedPC = et_1.EMPLOYID and tt. ClientID = do. CUSTNMBR, we can make the following changes:
Mysql> alter table tt MODIFY AssignedPC VARCHAR (15 ),
MODIFY ClientID VARCHAR (15 );
Now the EXPLAIN result is as follows:
Table type possible_keys key key_len ref rows Extra
Et all primary null 74
Tt ref AssignedPC, ClientID, ActualPC 15 et. EMPLOYID 52 where used
Et_1 eq_ref PRIMARY 15 tt. AssignedPC 1
Do eq_ref PRIMARY 15 tt. ClientID 1
The results are satisfactory.
The remaining problem is that, by default, MySQL assumes that the values in the tt. ActualPC column are evenly distributed, but not in the tt table. Fortunately, we can easily let MySQL know this:
Shell> myisamchk -- analyze PATH_TO_MYSQL_DATABASE/tt
Shell> mysqladmin refresh
The connection operation is ideal now. the EXPLAIN analysis result is as follows:
Table type possible_keys key key_len ref rows Extra
Tt ALL AssignedPC, client ID, ActualPC NULL 3872 where used
Et eq_ref PRIMARY 15 tt. ActualPC 1
Et_1 eq_ref PRIMARY 15 tt. AssignedPC 1
Do eq_ref PRIMARY 15 tt. ClientID 1
▲Optimize
OPTIMIZE can restore and organize disk space and data fragments. once a large number of tables with long rows are updated or deleted, this operation is necessary. Currently, OPTIMIZE can only be used for MyISAM and BDB tables.
Conclusion: there are many factors that can improve MySQL Performance starting from compiling the database server and throughout the management process. This article only involves a small part. However, we hope that the content discussed in this article will be helpful to you.
// Note for the copy operator:
Time is not enough, so the format is incorrect ~~, Please see the detailed English original: http://www.devshed.com/Server_Side/MySQL/Optimize/
Let's take a look at chinabyte's article:
Http://www.chinabyte.com/builder/detail.shtm? Buiid = 1012 & parid = 1
Haha ~ From this point, can you tell me that I serve everyone wholeheartedly?
