MySQL's optimize

Source: Internet
Author: User

Test the optimize today, found good, the following is the test process:

create table  ' Moe '   (   ' Phonenum '  varchar (one)  NOT NULL,   ' Citynum '  int (one)  NOT NULL DEFAULT  ' 0 ',   ' phonetype '  int (one)   default null,  primary key  (' Phonenum ', ' Citynum ')) load data infile  '/ Tmp/hun.txt '  into table moe FIELDS TERMINATED BY  ', '  enclosed by   ' '  LINES TERMINATED BY  ' \ n '; mysql> select count (*)  from moe; +----------+| count (*)  |+----------+|  4232081 |+----------+1 row in  set  (1.75 SEC) [[EMAIL PROTECTED] MOE]$ LL |GREP MOE-RW-RW---- 1  Mysql mysql      8644 9 Month   19 15:03  MOE.FRM-RW-RW---- 1 mysql mysql 310378496 9 Month   19 15:15  Moe.ibdmysql> show index from moe;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+- -------+------+------------+---------+---------------+| table | non_unique | key_name  | Seq_in_index | Column_name | Collation | Cardinality |  Sub_part | packed | null | index_type | comment | index_ comment |+-------+------------+----------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+| moe   |           0 | PRIMARY  |             1 | phonenum    | A          |     4219450 |      null | null   |      | btree      |          |                | |  moe   |          0 | primary   |            2 | citynum      | A         |      4219450 |     NULL | NULL   |       | BTREE      |          |                |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set  (0.00 sec) mysql> delete from moe limit 2100000;         Query OK, 2100000 rows affected, 1 warning  (18.06 sec) mysql>  select count (*)  from moe;+----------+| count (*)  |+----------+|   2132081 |+----------+1 row in set  (2.14 sec) mysql> show index  from moe;+-------+------------+----------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+| table | non_unique | key_ name | seq_in_index | column_name | collation | cardinality |  Sub_part | Packed | Null | Index_type | Comment |  index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+| moe   |           0 | PRIMARY  |             1 | phonenum    | A          |     2272822 |     NULL |  null   |      | btree      |          |                | |  moe   |          0 | primary   |            2 | citynum     | a         |      2272822 |     null | null   |       | BTREE      |          |                |+-------+------------+----------+--------------+-------------+-----------+-------------+--------- -+--------+------+------------+---------+---------------+2 rows in set  (0.00 sec) Index reduced by almost half [[EMAIL PROTECTED] MOE]$ LL |GREP MOE-RW-RW---- 1 mysql  Mysql      8644 9 month   19 15:03 MOE.FRM-RW-RW---- 1  mysql mysql 310378496 9 month   19 15:26 MOE.IBD table size unchanged optimize table  moe;mysql> show index from moe;+-------+------------+----------+--------------+-------------+-----------+-- -----------+----------+--------+------+------------+---------+---------------+| table | non_ unique | key_name | seq_in_index | column_name | collation |  Cardinality | Sub_part | Packed | Null | Index_type |  comment | index_comment |+-------+------------+----------+--------------+-------------+-------- ---+-------------+----------+--------+------+------------+---------+---------------+| moe    |          0 | PRIMARY  |             1 | phonenum     | a         |     2126976  |    &nBsp null | null   |      | btree       |         |                | |  moe   |          0 | primary   |            2 | citynum      | A         |      2126976 |     NULL | NULL   |       | BTREE      |          |                |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in  set  (0.00 SEC) mysql> select count (*)  from moe; +----------+| count ( *)  |+----------+|  2132081 |+----------+1 row in set  (0.84 sec) [[ EMAIL PROTECTED] MOE]$ LL |GREP MOE-RW-RW---- 1 mysql mysql      8644 9 Moon   19 15:27 MOE.FRM-RW-RW---- 1 mysql  Mysql 92274688 9 Month   19 15:28 MOE.IBD

The official documents cited are as follows:

13.5.2.5. OPTIMIZE TABLE Syntax

OPTIMIZE [LOCAL |no_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

If you have deleted a large part of the table, or if you have a table containing variable-length rows (containing VARCHAR, BLOB, or TEXT column.

table), you should use the OPTIMIZE TABLE. The deleted records are kept in the linked list, and subsequent INSERT operations

re-use the old record location. You can use OPTIMIZE TABLE to re-use unused space and defragment the data file.

in most settings, you don't need to run OPTIMIZE TABLE. Even if you have a large number of updates to variable-length lines, you do not need to

to run frequently, once a week or once a month, run only on specific tables.

optimizetable only to MyISAM, BDB and the InnoDB table works.

for InnoDB table,OPTIMIZE table is mapped to the ALTER tables, which rebuilds the sheet.

Note that the OPTIMIZE TABLE During the run, MySQL The table is locked.

The OPTIMIZE TABLE statement is written to the binary log unless you use the optional no_write_to_binlog keyword (or

name LOCAL). has already done so, the OPTIMIZE TABLE command for MySQL Server acts as a replication master service

By default, these commands will be copied to the replication subordinate server.


This article is from the "just out of the shell of the Birds" blog, please be sure to keep this source http://qhd2004.blog.51cto.com/629417/1555194

MySQL's optimize

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.