MySQL数据库SQL优化第八篇：介绍两个简单实用的优化方法

  1.定期分析表和检查表

  分析表的语法如下：ANALYZE  [LOCAL | NO_WRITE_TO_BINLOG]  TABLE table_name  [,table_name]....

  本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。

在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM、BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk  -a 相当。

 下例中对表sales做了表分析：

mysql> analyze table payment;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sakila.payment | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (3.35 sec)

      检查表的语法如下:

 CHECK TABLE table_name [,table_name]  ...[option] ...option ={QUICK | FASST | MEDIUM |  EXTENDED | CHANGED}

  检查表的作用事检查一个表或多个表是否有错误。

CHECK TABLE 对MyISAM 和InnoDB 表有作用。对于MyISAM 表，关键字统计数据将被更新。例如：

mysql> check table payment_myisam;
+-----------------------+-------+----------+---------------------------------------------+
| Table                 | Op    | Msg_type | Msg_text                                    |
+-----------------------+-------+----------+---------------------------------------------+
| sakila.payment_myisam | check | Error    | Table 'sakila.payment_myisam' doesn't exist |
| sakila.payment_myisam | check | status   | Operation failed                            |
+-----------------------+-------+----------+---------------------------------------------+
2 rows in set (0.05 sec)

CHECK TABLE 也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在，举例如下：

1） 首先创建一个视图

   

mysql> create view v_payment_myisam as select * from payment_myisam;
Query OK, 0 rows affected (0.12 sec)

2）然后CHECK一下该视图，发现没有问题。

mysql> check table v_payment_myisam;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| sakila.v_payment_myisam | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (0.00 sec)

3） 现在删除视图依赖的表：

mysql> drop table payment_myisam;
Query OK, 0 rows affected (0.95 sec)

4）再次CHECK一下刚才的视图，发现报错了，并且提示出错的原因是Table sakila.payment_myisam不存在了；

mysql> check table v_payment_myisam;
+-------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Op    | Msg_type | Msg_text                                                                                                                                  |
+-------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sakila.v_payment_myisam | check | Error    | Table 'sakila.payment_myisam' doesn't exist                                                                                               |
| sakila.v_payment_myisam | check | Error    | View 'sakila.v_payment_myisam' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
| sakila.v_payment_myisam | check | error    | Corrupt                                                                                                                                   |
+-------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

2.定期优化表

   优化表的语法如下

     OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name [,table_name]....

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR 、 BLOB或TEXT 列的表） 进行了很多更改，则应使用OPTIMIZE TABLE 命令来进行表优化。

这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令 只对MyISAM 、BDB、和InnoDB表起作用。

 以下例子显示了优化表payment_myisam  的过程：

mysql> optimize table payment_myisam;
+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table                 | Op       | Msg_type | Msg_text                                                          |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| sakila.payment_myisam | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sakila.payment_myisam | optimize | status   | OK                                                                |
+-----------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (4.51 sec)

  对于InnoDB引擎的表来说，通过设置innodb_file_per_table 参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的ibd 文件，用于存储表的数据和索引，这样可以一定程度上减轻InnoDB表的空间回收问题。

另外，在删除大量数据后，InnoDB表看可以通过alter table 但是不修改引擎的方式来回收不用的空间。

mysql> alter table payment engine=innodb;
Query OK, 0 rows affected (6.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

注意：ABALYZE 、CHECK、OPTIMIZE、 ALTER  TABLE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关操作。