ANALYZE TABLE 和 OPTIMIZE TABLE 有什么区别？

含义区分：ANALYZE TABLE
https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
ANALYZE TABLE performs a key distribution analysis and stores the distribution for the named table or tables.
执行键分布分析，并存储指名表的分布。更新表的索引统计信息

 

含义区分：OPTIMIZE TABLE
https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.
重新组织表数据和相关索引数据的物理存储，以减少访问表时的存储空间并提高I/O效率。会重新组织数据文件中的数据

 

ANALYZE TABLE 要比 OPTIMIZE TABLE 快很多

mysql > 
SELECT 
  database_name, 
  table_name, 
  n_rows 'Rows', 
  clustered_index_size 'DataPages', 
  round(clustered_index_size * 16 / 1024) 'TableSizeMB', 
  sum_of_other_index_sizes 'IndexPages', 
  round(
    sum_of_other_index_sizes * 16 / 1024
  ) 'IndexSizeMB' 
FROM 
  mysql.innodb_table_stats 
WHERE 
  database_name not IN (
    'mysql', 'test', 'information_schema', 
    'performance_schema', 'sys'
  ) 
ORDER BY 
  TableSizeMB DESC 
limit 
  20;

 

 

mysql> ANALYZE TABLE user_orders;
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| user.user_orders  | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.11 sec)

+---------------+-------------------------------------+------------+-----------+-------------+------------+-------------+
| database_name | table_name                          | Rows       | DataPages | TableSizeMB | IndexPages | IndexSizeMB |
+---------------+-------------------------------------+------------+-----------+-------------+------------+-------------+
| user          |  user_orders                        | 1174573511 |  29897344 |      467146 |   11743093 |      183486 |

+---------------+-------------------------------------+------------+-----------+-------------+------------+-------------+
| database_name | table_name                          | Rows       | DataPages | TableSizeMB | IndexPages | IndexSizeMB |
+---------------+-------------------------------------+------------+-----------+-------------+------------+-------------+
| user          |  user_orders                        | 1143188977 |  30906497 |      482914 |   13862255 |      216598 |