MySql 杂记 之OPTIMIZE TABLE操作

1：MySQL OPTIMIZE TABLE操作

1. MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可，可以写成定时任务来做，这是因为3在OPTIMIZE TABLE运行过程中，MySQL会锁定表，其本质是将当前表复制到临时表操作后再删除当前表，最后将临时表改名；
2. OPTIMIZE TABLE只对MyISAM，Archive，InnoDB，和NDB表起作用，尤其是MyISAM表的作用最为明显,对于MyISAM，Archive和NDB，OPTIMIZE TABLE被映射为analyze table xxx操作。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。另外，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL以支持其他存储引擎，只不过这时候被映射为alter table xxx操作来实现；
3. 默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息，但这并不代表optimize不支持InnoDB引擎，只是因为optimize对InnoDB引擎的表操作时，是分成两步的：

optimize对InnoDB引擎的表操作时无法作为a single operation，实际的操作是：
1：ALTER TABLE test ENGINE=InnoDB;
2：ANALYZE TABLE test;
注：MySQL5.7已经推荐对于InnoDB的table使用 alter table table_name engine=innodb;语句的方式来进行表碎片优化，也就是说其效果与OPTIMIZE TABLE test一样了。

注意：如果觉得OPTIMIZE TABLE操作锁表不好的话，可以借助工具pt-online-schema-change。

2：show table status like ‘test’\G;

这个操作用来查看表状态

mysql> show table status  like 'test'\G;
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 41709294
 Avg_row_length: 428
    Data_length: 17871028224
Max_data_length: 0
   Index_length: 12445794304
      Data_free: 20971520  #已分配但未使用的磁盘空间字节数。
 Auto_increment: NULL
    Create_time: 2021-01-27 08:09:55
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: partitioned
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

每个值的解释看 mysql 8.0 SHOW TABLE STATUS语句 。

注意 Data_free这个参数，它的产生是因为频繁写或一次大量写造成的，比如1000W数据的表一次删除500W,就会使Data_free激增。也就是表空间（磁盘）碎片化，不利于I/O,
这是因为删除数据后mysql并不会释放这部分磁盘空间，而是有新的插入时直接放到这里，这也就是为什么需要定期 OPTIMIZE TABLE 操作了。

3：show processlist

1. show processlist;只列出前100条，如果想全列出请使用show full processlist;
2. show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果：

select * from information_schema.processlist limit 100;
select * from information_schema.processlist where db=test;#数据库test下正在执行的sql

这样就可以通过sql定位问题了，如果遇到慢查询，获取该查询所在的线程ID，然后 kill ID

4：Mysql查看连接数（连接总数、活跃数、最大并发数）

见此文章

参考

1： optimize table 的一些坑
2：MySQL 大数据操作注意事项
3：mysql进阶：optimize table命令
4：官网 optimize table命令
5：show processlist
6：Mysql show processlist 排查问题