mysql optimize innodb 优化

最新推荐文章于 2025-09-17 11:22:46 发布

转载最新推荐文章于 2025-09-17 11:22:46 发布 · 1.7k 阅读

MYSQL 专栏收录该内容

22 篇文章

订阅专栏

本文介绍了MySQL Innodb存储引擎的优化方法，包括使用ALTER TABLE进行数据优化，调整my.cnf配置提升性能，以及优化前后数据长度和索引长度的对比。

如果对mysql表，进行大范围删除后，最好做一个表优化，这样会快一些。以前写过一篇类似的，针对myisam存储引擎的。请参考：实例说明optimize table在优化mysql时很重要

1、删除数据，并尝试优化

mysql> delete FROM `test` WHERE d_id>397136;  

Query OK, 306356 rows affected (19.48 sec)  

  

mysql> optimize table test;  

+------------+----------+----------+-------------------------------------------------------------------+  

| Table      | Op       | Msg_type | Msg_text                                                          |  

+------------+----------+----------+-------------------------------------------------------------------+  

| test1.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |  

| test1.test | optimize | status   | OK                                                                |  

+------------+----------+----------+-------------------------------------------------------------------+  

2 rows in set (0.35 sec)

执行optimize的时候，非常慢，可能会卡死。msg_text信息的意思是innodb引擎，不支持optimize。在官网找了一下，发现以下内空

Table does not support optimize, doing recreate + analyze instead.
It is because the table that you are using is InnoDB.
You can optimize the InnoDB tables by using this.
ALTER TABLE table.name ENGINE='InnoDB';

一般情况下，由myisam转成innodb，会用alter table table.name engine='innodb'进行转换，优化也可以用这个

2、alter优化数据

mysql> alter table test engine='innodb';  
Query OK, 384781 rows affected (19.88 sec)  
Records: 384781  Duplicates: 0  Warnings: 0

alter如果很慢，优化一下my.cnf，[mysqld]加上以下内容，并重新加载。

innodb_buffer_pool_size=1G  
innodb_file_io_threads=4

innodb_file_io_threads常规配置，小于等CPU核数。innodb_buffer_pool_size小于等于物理内存的1/2，原则上够用就好。

3、优化后的对比

mysql> use information_schema  

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,  

 -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  

 -> from tables where  

 -> table_schema='test1'  

 -> and table_name = 'test';  

+----------------+-----------------+  

| data_length_MB | index_length_MB |  

+----------------+-----------------+  

| 20.55MB        | 27.55MB         |       //优化前  

+----------------+-----------------+  

1 row in set (0.01 sec)  

  

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,  

 -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  

 -> from tables where  

 -> table_schema='test1'  

 -> and table_name = 'test';  

+----------------+-----------------+  

| data_length_MB | index_length_MB |  

+----------------+-----------------+  

| 20.55MB        | 16.55MB         |    //优化后  

+----------------+-----------------+  

1 row in set (0.00 sec)

data_length_MB，第一列是数据；index_length_MB，第二列是索引