MySQL optimization overview related database commands

Next we'll discuss another aspect of database performance optimization, which is the use of tools built into the database server to aid in performance analysis and optimization.    
  ▲   SHOW    
  executes the following command to see how the server is doing:    
mysql   > show   status;  
The   command displays a long list of state variables and their corresponding values, including: number of users aborted, number of connections aborted, number of connections attempted, maximum number of concurrent connections, and many other useful information. This information is useful in determining the causes of system problems and inefficiencies.    
In addition to being able to display information about the overall state of the MySQL server, the   SHOW command is able to 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 can analyze the processing of SELECT command. This is useful not only for deciding whether to index a table, but also for understanding how MySQL handles complex joins.    
  the following example shows how to optimize a join query step by step with the information provided by EXPLAIN. (in this case from MySQL document, see http: / / www mysql. com/doc/E/X/EXPLAIN html. It seems a bit sloppy to write here, but here is an example.    
  assumes that the SELECT command analyzed 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 that appears in the   SELECT command is defined as    
The   ※ table defines    
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 values are not evenly distributed    
  before any optimization, EXPLAIN performed an analysis of SELECT with the following results:    
table   type   possible_keys                 key   key_len   ref   rows   Extra  
et     ALL   PRIMARY                       NULL   NULL     NULL   74  
do     ALL   PRIMARY                       NULL   NULL     NULL   2135  
et_1   ALL   PRIMARY                       NULL   NULL     NULL   74  
tt     ALL   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 joined for each table! This operation is quite time consuming because the number of rows to be processed is the product of the number of rows per table! That is, the total number of rows processed here is 74   *   2135   *   74   *   3872   =   45,268,558,720.    
One of the problems with   here is that MySQL (yet) 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 different lengths. Since tt.ActualPC declares CHAR(10) and et.EMPLOYID declares CHAR(15), there is a column length mismatch.    
To solve the length mismatch between the two columns,   expands the ActualPC column from 10 characters to 15 characters with the ALTER   TABLE command, as shown below:    
mysql   >   ALTER   TABLE   tt   MODIFY   ActualPC   VARCHAR(15);  

  is now tt.ActualPC and et.EMPLOYID are both VARCHAR(15) 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     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   15       tt.ActualPC   1  

  this isn't perfect, but it's much better (the product of rows is now a factor of 74 less). The SQL command now takes a few seconds to execute.    
To avoid column length mismatches in tt.AssignedPC =   et_1.EMPLOYID and tt.ClientID   =   do.CUSTNMBR comparisons, we can make the following change:    
mysql   >   ALTER   TABLE   tt   MODIFY   AssignedPC   VARCHAR(15),  
                      MODIFY   ClientID     VARCHAR(15);  

  now displays EXPLAIN as follows:    
table   type     possible_keys     key       key_len   ref             rows       Extra  
et     ALL     PRIMARY           NULL     NULL     NULL             74  
tt     ref     AssignedPC,ClientID,ActualPC   ActualPC   15   et.EMPLOYID   52   where   used  
et_1   eq_ref   PRIMARY           PRIMARY   15       tt.AssignedPC   1  
do     eq_ref   PRIMARY           PRIMARY   15       tt.ClientID     1  

  is already satisfactory.  
The remaining problem with   is that, by default, MySQL assumes that the values of the tt.ActualPC column are evenly distributed, which is not the case with the tt table. Fortunately, we can easily let MySQL know this:    
shell   >   myisamchk   --analyze   PATH_TO_MYSQL_DATABASE/tt  
shell   >   mysqladmin   refresh  

  now this join operation is very good, EXPLAIN analysis results 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   15       tt.ActualPC     1  
et_1   eq_ref   PRIMARY           PRIMARY   15       tt.AssignedPC   1  
do     eq_ref   PRIMARY           PRIMARY   15       tt.ClientID     1  

  ▲   OPTIMIZE    
  OPTIMIZE has the ability to recover and defragment disk space and data, which is necessary once a large number of updates or deletions have been made to tables containing longer rows. OPTIMIZE is currently only available for MyISAM and BDB tables.    
  conclusion: there are many factors that can improve the performance of MySQL, starting with compiling the database server and throughout the administration process. Nevertheless, we hope that the discussion in this article has been helpful.    
//copy note:  
    didn't have enough time, so the format is a little problem ~ ~, please see detailed English texts: http: / / www devshed. com Server_Side/MySQL Optimize/ 
Or check out chinabyte's article:  
http://www.chinabyte.com/builder/detail.shtm?buiid=1012&parid=1  
Haha ~ from this can see that I am wholeheartedly for everyone