MySQL 怼了架构师关于 optimize table 需求与为什么反对 optimize table

2024年1月某些星象的原因，导致我个人的星盘在1月大概率要和某些人要有不愉快。这不就来了，在一次关于mysql 数据库数据表清理后，关于optimize table 的问题上，我毫无悬念的和架构师们进行了一次非常不nice 的沟通。

随意就有了此篇的文章，因为我这个人比较的要通过实际的情况来说明问题，而不是用权威来压制，那样没有品。事情简单的说一下，几十个库，几百张表，要进行数据的清理，这没有问题，我们会使用自动的手段来进行，而后面一个架构师提出，希望能对表进行optimize table 的操作，并且提出这样的好处多多。

但是，但是，但是，说话办事就怕光站在自己的角度来说问题，我对此要求是拒绝的，并且我拿出了 PG SQL SERVER ORACLE 甚至 IBM DB2 的一些理论对于对方无礼的需求进行驳斥，因为这个架构师提出，optimize table 很快，MYSQL8 有新功能，大致的意思我翻译一下，optimize table 的任何过程中，不会对表产生影响，产生锁，产生业务影响。因为我们是 7*24小时的业务，所以DB 对于表在一些操作的中，是非常忌讳，产生TABLE LOCK 并且是无预估的长时间的表不可用的情况。

那么我们来看看到底MYSQL8 是否如这个架构师讲的，很快不会对业务有什么影响。

我们使用一个，说新不新，说旧不旧的MySQL数据库版本，8.031 来验证MYSQL8 对这个optimize table 并没有多少改进。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

1 我们验证当数据库有事务在操作数据表时，是否会对optimize table 的命令产生影响，也就是之间的操作是互斥的。结果可以参见下图，必然是互相影响。optimize table 被一个对表进行数据插入的事务卡主了。

mysql> select * from schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: test
                 object_name: test
           waiting_thread_id: 71
                 waiting_pid: 7
             waiting_account: db_admin@mysql830
           waiting_lock_type: SHARED_NO_READ_WRITE
       waiting_lock_duration: TRANSACTION
               waiting_query: optimize table test
          waiting_query_secs: 1245
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 81
                blocking_pid: 8
            blocking_account: db_admin@mysql830
          blocking_lock_type: SHARED_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 8
sql_kill_blocking_connection: KILL 8
1 row in set (0.01 sec)

同时在genernal log 中跟踪相关操作，这里可以看到操作本身是没有分解操作的，原子性的语句进行传递，如有从库也会进行传递到从库执行。

在MySQL 8 中越来越智能的sys 库中的lock_waits 视图可以清晰的看到，optimize table 到底上了什么锁，，那么这个锁是什么这个锁属于metadata_locks 中的其中一种

INTENTION_EXCLUSIVE,

SHARED,

SHARED_HIGH_PRIO,

SHARED_READ,

SHARED_WRITE,

SHARED_UPGRADABLE,

SHARED_NO_WRITE,

SHARED_NO_READ_WRITE,

EXCLUSIVE

那么我们在执行了这个操作后，出现什么情况，从下面的图中可以清晰的看到，我们在一个就只有1行的数据表中，进行了optimize table 的操作，然后我们毫无悬念的发现在执行完毕这个命令后，表的文件的日期更新了，这里可以证明，在8.031 版本的mysql 中与我们之前MYSQL 的版本对于optimize table 的原理毫无实质的变化。

+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+


mysql> select * from test;
+----+--------------+--------------+
| id | name         | title        |
+----+--------------+--------------+
|  1 | å°æŽ       | å°æŽ       |
+----+--------------+--------------+
1 row in set (0.00 sec)


[mysql@mysql830 test]$ ll -ah
total 3.4G
drwxr-x---  2 mysql mysql  190 Nov 17 12:58 .
drwxr-x--- 11 mysql mysql 4.0K Nov 17 12:38 ..
-rw-r-----  1 mysql mysql 112K Apr 14  2023 app_user_1000.ibd
-rw-r-----  1 mysql mysql 3.4G Apr 14  2023 app_user.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 05:04 bm_card_account_recharge_
-rw-r-----  1 mysql mysql 144K Apr 15  2023 orders_copy.ibd
-rw-r-----  1 mysql mysql 160K Apr 15  2023 orders.ibd
-rw-r-----  1 mysql mysql 128K Apr 14  2023 payments.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 12:35 read_table.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 12:58 test.ibd
[mysql@mysql830 test]$ ll -ah
total 3.4G
drwxr-x---  2 mysql mysql  190 Nov 17 13:32 .
drwxr-x--- 11 mysql mysql 4.0K Nov 17 12:38 ..
-rw-r-----  1 mysql mysql 112K Apr 14  2023 app_user_1000.ibd
-rw-r-----  1 mysql mysql 3.4G Apr 14  2023 app_user.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 05:04 bm_card_account_recharge_ext.ibd
-rw-r-----  1 mysql mysql 144K Apr 15  2023 orders_copy.ibd
-rw-r-----  1 mysql mysql 160K Apr 15  2023 orders.ibd
-rw-r-----  1 mysql mysql 128K Apr 14  2023 payments.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 12:35 read_table.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 13:32 test.ibd

当然同样的命令还有 alter table tablename force；和 alter table tablename engine=innodb;

那么我们来说说我们为什么要反对这个事情，

1 这么多数据库，这么多表，并且这些表其中有大表，操作这个部分的时间不可控，业务是不会等你操作完 optimize table 然后在去工作，他要anytime anywhere的运行，如果进行了optimize table DB 无法控制表不可用的时间，因为有些表里面N个索引，实际上这就是重建了一张表。然后改名的原子性操作。

2 操作具有风险性，如果此时由于大量运行optimize table 导致IOPS 上升，或者等待这些表的事务持续的等待，undo log 里面的数据无法及时进行purge,最终是否有可能导致数据库出现，基于探针判定主库不可用的问题，导致的数据库切换，这个问题的责任谁来负责。

3 一组数据库有从库，你的语句在此时会直接binlog 给从库，从库也会进行此操作，主库不可用，从库也不可用，并且统统的 UP ，到时我连切换的库都没有。（当然可以在执行时禁止BINLOG 传输此命令，避免从库一起来做相关的操作）

为什么要写此篇文章，因对一些开发人员和架构师的不负责任和缺乏职业素养的问题，非常憎恶。自己一句话，将别人至于尴尬和危险的境地，你于心何忍，你一句话别人要付出什么，这次我怼你算是轻的，下次我可以更狠，你以为做一个 DB 是光看看几篇文章就可以胜任的，笑话。

附群友的一些对此事的看法

另DB 人员自己也的打铁自身重，如果你技不压人，你就只能被人家压！

最后，下面是关于这块的代码，alter table table name engine= innodb;

    /// Set for ALTER [COLUMN] ... SET DEFAULT ... | DROP DEFAULT
    ALTER_CHANGE_COLUMN_DEFAULT = 1ULL << 8,

    /// Set for DISABLE KEYS | ENABLE KEYS
    ALTER_KEYS_ONOFF = 1ULL << 9,

    /// Set for FORCE
    /// Set for ENGINE(same engine)
    /// Set by mysql_recreate_table()
    ALTER_RECREATE = 1ULL << 10,

    /// Set for ADD PARTITION
    ALTER_ADD_PARTITION = 1ULL << 11,


     Alter_info(const Alter_info &rhs, MEM_ROOT *mem_root);

  bool add_field(THD *thd, const LEX_STRING *field_name,
                 enum enum_field_types type, const char *length,
                 const char *decimal, uint type_modifier, Item *default_value,
                 Item *on_update_value, LEX_CSTRING *comment,
                 const char *change, List<String> *interval_list,
                 const CHARSET_INFO *cs, bool has_explicit_collation,
                 uint uint_geom_type, Value_generator *gcol_info,
                 Value_generator *default_val_expr, const char *opt_after,
                 std::optional<gis::srid_t> srid,
                 Sql_check_constraint_spec_list *check_cons_list,
                 dd::Column::enum_hidden_type hidden, bool is_array = false);

 private:
  Alter_info &operator=(const Alter_info &rhs);  // not implemented
  Alter_info(const Alter_info &rhs);             // not implemented
};

/** Runtime context for ALTER TABLE. */
class Alter_table_ctx {
 public:
  Alter_table_ctx();

  Alter_table_ctx(THD *thd, Table_ref *table_list, uint tables_opened_arg,
                  const char *new_db_arg, const char *new_name_arg);

  ~Alter_table_ctx();

  /**
     @return true if the table is moved to another database, false otherwise.
  */
  bool is_database_changed() const { return (new_db != db); }

  /**
     @return true if the table name is changed, false otherwise.
  */
  bool is_table_name_changed() const { return (new_name != table_name); }

  /**
     @return true if the table is renamed (i.e. its name or database changed),
             false otherwise.
  */
  bool is_table_renamed() const {
    return is_database_changed() || is_table_name_changed();
  }

  /**
     @return path to the original table.
  */
  const char *get_path() const {
    assert(!tmp_table);
    return path;
  }

MySQL 怼了架构师关于 optimize table 需求 与 为什么反对 optimize table

MySQL 怼了架构师关于 optimize table 需求与为什么反对 optimize table