定期分析表
ANALYZE?[LOCAL?|?NO_WRITE_TO_BINLOG]?TABLE?tbl_name?[,?tbl_name]
本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。
MySQL使用已存储的关键字分布来决定，当您对除常数以外的对象执行联合时，表按什么顺序进行联合。
mysql>?analyze?table?a;
+--------+---------+----------+-----------------------------+
|?Table??|?Op??????|?Msg_type?|?Msg_text????????????????????|
+--------+---------+----------+-----------------------------+
|?test.a?|?analyze?|?status???|?Table?is?already?up?to?date?|?
+--------+---------+----------+-----------------------------+
1?row?in?set?(0.00?sec)
+--------+---------+----------+-----------------------------+
|?Table??|?Op??????|?Msg_type?|?Msg_text????????????????????|
+--------+---------+----------+-----------------------------+
|?test.a?|?analyze?|?status???|?Table?is?already?up?to?date?|?
+--------+---------+----------+-----------------------------+
1?row?in?set?(0.00?sec)
定期检查表
CHECK?TABLE?tbl_name?[,?tbl_name]?![]()
option?=?{QUICK?|?FAST?|?MEDIUM?|?EXTENDED?|?CHANGED}
option?=?{QUICK?|?FAST?|?MEDIUM?|?EXTENDED?|?CHANGED}
检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。
mysql>?check?table?a;
+--------+-------+----------+----------+
|?Table??|?Op????|?Msg_type?|?Msg_text?|
+--------+-------+----------+----------+
|?test.a?|?check?|?status???|?OK???????|?
+--------+-------+----------+----------+
1?row?in?set?(0.00?sec)
+--------+-------+----------+----------+
|?Table??|?Op????|?Msg_type?|?Msg_text?|
+--------+-------+----------+----------+
|?test.a?|?check?|?status???|?OK???????|?
+--------+-------+----------+----------+
1?row?in?set?(0.00?sec)
CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
我们为上面的表a创建一个视图
mysql>?create?view?a_view?as?select?*?from?a;
Query?OK,?0?rows?affected?(0.02?sec)
Query?OK,?0?rows?affected?(0.02?sec)
然后CHECK一下该视图，发现没有问题
mysql>?check?table?a_view;
+-------------+-------+----------+----------+
|?Table???????|?Op????|?Msg_type?|?Msg_text?|
+-------------+-------+----------+----------+
|?test.a_view?|?check?|?status???|?OK???????|?
+-------------+-------+----------+----------+
1?row?in?set?(0.00?sec)
+-------------+-------+----------+----------+
|?Table???????|?Op????|?Msg_type?|?Msg_text?|
+-------------+-------+----------+----------+
|?test.a_view?|?check?|?status???|?OK???????|?
+-------------+-------+----------+----------+
1?row?in?set?(0.00?sec)
现在删掉视图依赖的表
mysql>?drop?table?a;
Query?OK,?0?rows?affected?(0.01?sec)
Query?OK,?0?rows?affected?(0.01?sec)
再CHECK一下刚才的视图，发现报错了
mysql>?check?table?a_view\G;
***************************?1.?row?***************************
???Table:?test.a_view
??????Op:?check
Msg_type:?Error
Msg_text:?Table?'test.a'?doesn't?exist
***************************?2.?row?***************************
???Table:?test.a_view
??????Op:?check
Msg_type:?Error
Msg_text:?View?'test.a_view'?references?invalid?table(s)?or?column(s)?or?function(s)?or?definer/invoker?of?view?lack?rights?to?use?them
***************************?3.?row?***************************
???Table:?test.a_view
??????Op:?check
Msg_type:?error
Msg_text:?Corrupt
3?rows?in?set?(0.00?sec)
ERROR:?
No?query?specified
***************************?1.?row?***************************
???Table:?test.a_view
??????Op:?check
Msg_type:?Error
Msg_text:?Table?'test.a'?doesn't?exist
***************************?2.?row?***************************
???Table:?test.a_view
??????Op:?check
Msg_type:?Error
Msg_text:?View?'test.a_view'?references?invalid?table(s)?or?column(s)?or?function(s)?or?definer/invoker?of?view?lack?rights?to?use?them
***************************?3.?row?***************************
???Table:?test.a_view
??????Op:?check
Msg_type:?error
Msg_text:?Corrupt
3?rows?in?set?(0.00?sec)
ERROR:?
No?query?specified
<
