mysql优化-定期分析-定期优化

定期分析表

 

 

执行计划不是预期的执行计划，通过分析表可能得到解决；

analyze table  material_data;

mysql> analyze table  material_data;



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

| Table              | Op      | Msg_type | Msg_text |

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

| gzsp.material_data | analyze | status   | OK       |

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

1 row in set

检查表

check  table material_data;

mysql> check  table material_data;

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

| Table              | Op    | Msg_type | Msg_text |

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

| gzsp.material_data | check | status   | OK       |

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

1 row in set

 

------------------------

创建视图

mysql> create  view view_test as select * from t1;

Query OK, 0 rows affected

检查视图

check  table  view_test;

mysql> check  table  view_test;

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

| Table          | Op    | Msg_type | Msg_text |

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

| gzsp.view_test | check | status   | OK       |

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

1 row in set

没有问题

 

删除依赖

drop table  t1;

mysql> drop table  t1;

Query OK, 0 rows affected

 

 

mysql> check  table  view_test;

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

| Table          | Op    | Msg_type | Msg_text                                                                                                                         |

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

| gzsp.view_test | check | Error    | Table 'gzsp.t1' doesn't exist                                                                                                    |

| gzsp.view_test | check | Error    | View 'gzsp.view_test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |

| gzsp.view_test | check | error    | Corrupt                                                                                                                          |

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

3 rows in set

 

 

 

定期优化表

如果已经删除了表的大部分，或者可变长度的表进行的更改，是用optimize table 命令对表进行优化；

对表中的空间碎片进行合并，可以消除由于删除或者更新造成的空间浪费；

optimize table material_data;

mysql> optimize table material_data;

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

| Table              | Op       | Msg_type | Msg_text                                                          |

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

| gzsp.material_data | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| gzsp.material_data | optimize | status   | OK                                                                |

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

2 rows in set

 

 

对于innodb引擎的表来说，通过设置innodb_file_per_table参数，设置innoDB的表为独立表空间模式，

使每个表生成一个独立的表中建文件  idb文件，用于存储表的数据或者索引，这样可以一定程度的减轻innoDB表的空间回收问题；

在删除大量的数据后，innDB表可以通过alter table t1 engine=innodb;

不修改引擎的方式回收不用的空间；

mysql> alter table  users_b  engine=innodb;

Query OK, 0 rows affected

Records: 0  Duplicates: 0  Warnings: 0