Optimize 优化

当数据达到稳定大小，表增加了数百兆，或当无法更好的优化索引或优化应用代码的时候，可以考虑使用 optimize table tablename 来优化，重组只需要用较少的磁盘I/O来执行全表扫描，optimize命令作用。

当你对InnoDB表运行OPTIMIZE TABLE命令时，MySQL实际上会执行以下操作：

  1. 创建一个新的空表。
  2. 将旧表的数据复制到新表中。
  3. 删除旧表。
  4. 将新表重命名为旧表的名称。

然后，MySQL会运行ANALYZE TABLE命令来更新表的统计信息，以帮助优化查询性能。

好处是改进了索引中数据的打包，并减少了表空间内和磁盘上的碎片 。因表结构与数据不通，所以每个表重组后的收益也不同，收益会随着时间的推移而减少，直到下次优化表。如果表很大，或者要重新生成的索引不适合缓冲池，则此操作可能会很慢。将大量数据添加到表后，第一次运行通常很慢。

长Primary Key

使用长PRIMARY KEY 会浪费大量磁盘空间，表的所有二级索引都会记录 Primary Key的值，空间会随着二级索引的增加而成倍增加。

使用Varchar数据类型存储可变长度字符串或具有许多Null值的列

  • Char(N)列始终需要N个字符来存储数据，即使字符串较短或其值为Null也是如此，较小的表更适合缓冲池，并减少磁盘I/O
  • 当使用 COMPACT 行格式（默认的 InnoDB 格式）和可变长度字符集（如 utf8mb4 或 sjis）时，CHAR（N） 列占用的空间量可变，但仍至少占用 N 个字节

文本压缩

对于较大的表，或包含大量重复文本或数值数据的表，请考虑使用 Compressed 行格式。将数据带入缓冲池或执行全表扫描所需的磁盘 I/O 较少

  • Compressed行格式对于大量重复数据非常友好。因为它使用了压缩技术，所以当表中存在大量重复数据时，Compressed行格式可以显著减少存储空间的使用。
  • 在Compressed行格式中，InnoDB会尝试压缩每个数据库页以节省磁盘空间。这可以在磁盘空间有限，但CPU资源充足的情况下提高存储效率。然而，压缩和解压缩数据会增加CPU的使用，可能会影响性能。

​

碎片

MySQL数据库中的碎片主要是由于数据的增删改操作引起的。以下是一些具体的原因：

  1. 删除操作：当你从数据库中删除数据时，会在数据文件中留下空余的空间。这些空间可以被后续的插入操作使用，但如果新插入的数据行比空余的空间大，那么数据行将被分割，部分数据将被存储在其他地方，这就产生了碎片。

  2. 更新操作：如果更新操作使数据行变大，而新的数据行无法适应原来的空间，那么数据行将被移动到其他地方，原来的空间将变为空余，产生碎片。

  3. 插入操作：在某些情况下，插入操作也可能导致碎片。例如，如果数据是按非顺序的方式插入的，那么数据库可能需要在数据文件中找到足够大的空间来存储新的数据行，这可能导致数据文件中的空间被不连续地使用，产生碎片。

这些碎片会导致数据库的存储空间使用不连续，影响查询性能。因此，定期对数据库进行优化（如使用OPTIMIZE TABLE命令）以减少碎片是一个好的做法。