分析表:MyISAM、InnoDB、BDB

mysql> analyze table payment; #MyISAM 使用myisamchk -a

+----------------+---------+----------+----------+

| Table          | Op      | Msg_type | Msg_text |

+----------------+---------+----------+----------+

| sakila.payment | analyze | status   | OK       |

+----------------+---------+----------+----------+

1 row in set (0.10 sec)


mysql> 

mysql> check table payment_myisam ; #MyISAM、InnoDB #也可以检查视图

+-----------------------+-------+----------+----------+

| Table                 | Op    | Msg_type | Msg_text |

+-----------------------+-------+----------+----------+

| sakila.payment_myisam | check | status   | OK       |

+-----------------------+-------+----------+----------+

1 row in set (0.00 sec)


mysql> 

优化表:MyISAM、InnoDB、BDB

mysql> optimize table  payment_myisam ;

+-----------------------+----------+----------+----------+

| Table                 | Op       | Msg_type | Msg_text |

+-----------------------+----------+----------+----------+

| sakila.payment_myisam | optimize | status   | OK       |

+-----------------------+----------+----------+----------+

1 row in set (0.01 sec)


mysql> 

使用独立表空间:

mysql> show variables like "innodb_file_per_table";

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)


mysql> set GLOBAL innodb_file_per_table = on;      

Query OK, 0 rows affected (0.00 sec)


mysql>


删除大量数据后InnoDB表可以通过alter table:

mysql> alter table payment engine=innodb;

Query OK, 0 rows affected (4.63 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> 


关于字符集的设置:

character_set_server=utf8mb4

default_character-set=utf8mb4