Use related database commands to optimize MySQL _ MySQL

Tips for optimizing MySQL using related database commands: we are discussing the optimization of database performance, that is, using built-in tools on the database server to assist in performance analysis and optimization.

We are talking about the performance optimization of the database, 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.

▲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.

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:

Since the database server is compiled and throughout the management process, there are many factors that can improve mysql Performance. This article only involves a small part. However, we hope that the content discussed in this article will be helpful to you.

Var yahoocnadconfig = new array (); yahoocnadconfig ['adid'] = 710 yahoocnadconfig ['wid'] = 50397 yahoocnadconfig ['w'] = 468 yahoocnadconfig ['H'] = 60 var yahoocustconfig = new array (); yahoocustconfig ['ad _ width'] = 468 yahoocustconfig ['ad _ height'] = 60 yahoocustconfig ['default _ keyword_number '] = 8 yahoocustconfig ['keyword _ bg_color'] =' b99cdd 'yahoocustconfig ['keyword _ fr_color '] = 'ffffff' yahoocustconfig ['border _ color'] = '9c73cf'