MySQL Advanced Fourteen - Table Optimization

1. Optimization

1. Periodic analysis table:

analyze table woitems;

+---------------+---------+----------+----------+  
| Table         | Op      | Msg_type | Msg_text |  
+---------------+---------+----------+----------+  
| caiwu.woitems | analyze | status   | OK       |  
+---------------+---------+----------+----------+

2. Periodic inspection table:

check table tt\G

3. Periodic optimization table:

Optimize table only works on MyISAM, BDB and InnoDB tables.

For MyISAM tables, OPTIMIZE TABLE operates as follows:

Repair the table if it has had rows deleted or detached.

If the index page is classified, perform classification.

If the statistics of the table are not updated (and repair cannot be achieved by classifying the index), then update

Note: Whether it is ANALYZE, CHECK, or OPTIMIZE, the table will be updated during execution. Locking is performed, so please note that these operations should be performed when the database is not busy.

2. Obtain related information about the table

show table status;

show table status like 'tableName'\G

*************************** 133. row ***************************
           Name: www_users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2015-12-06 15:25:20
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:

1. name: table name

2. Engine: storage engine of the table

3. Version: Version

4, Row_format: row format. For the MyISAM engine this may be Dynamic, Fixed or Compressed. Dynamic rows have variable row lengths, such as Varchar or Blob type fields. Fixed row length does not change, such as Char and Integer type fields.

5. Rows: The number of rows in the table. For non-transactional tables, this value is exact; for transactional engines, this value is usually an estimate.

6. Avg_row_length: The average number of bytes included in each row

7. Data_length: The amount of data in this table (unit: bytes)

8. Max_data_length: Table The maximum amount of data that can be accommodated

9. Index_length: The space occupied by the index on the disk

10. Data_free: For the MyISAM engine, identifies the space that has been allocated but is now unused, and contains Remove row space.

11. Auto_increment: value

12. Create_time: The creation time of the table

13. Update_time: The last update time of the table

14. Check_time : Use the check table or myisamchk tool to check the latest time of the table

15. Collation: The default character set and character arrangement rules of the table

16. Checksum: If enabled, the contents of the entire table Checksum during calculation

17. Create_options: refers to all other options when creating

18. Comment: contains other additional information. For the MyISAM engine, it contains the comment mark. If The table uses the Innodb engine, which will display the remaining space of the table. If it is a view, the comment contains the word VIEW

. The above is the content of MySQL Advanced Fourteen - Table Optimization. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!