MySQL performance optimization strategy-related database commands

We want to discuss 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. 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;