浅析MySQL数据碎片的产生

【独家译文】本文浅析MySQL数据碎片的产生：定义，时间及成因。

十年的龙华网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。网络营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整龙华建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“龙华网站设计”,“龙华网站推广”以来，每个客户项目都认真落实执行。

MySQL列表，包括MyISAM和InnoDB这两种最常见的类型，而根据经验来说，其碎片的产生及消除都是随机的。碎片会在你的表格中留下明显的空白，而这会给列表扫描工作带来相当大的困扰。对你的列表进行优化，这样会使列表的全面及分区扫描工作进行得更有效率。

碎片——实例

MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

这种额外的破碎的存储空间在读取效率方面比正常占用的空间要低得多。让我们看一个实例。

我们将创建一个数据库（有时也称其为大纲）及一个测试用的列表:

 
 
 
    
  
  
  
  1. (root@localhost) [test]> create database frag_test;  
    2.   
  
  
  2. Query OK, 1 row affected (0.03 sec)  
    3.   
  
  
  3.  
    4.   
  
  
  4. (root@localhost) [test]> use frag_test;  
    5.   
  
  
  5. Database changed  
    6.   
  
  
  6.  
    7.   
  
  
  7. (root@localhost) [frag_test]> create table frag_test (c1 varchar(64));  
    8.   
  
  
  8. Query OK, 0 rows affected (0.05 sec) 
    9.

现在让我们在列表中加入如下几行:

 
 
 
    
  
  
  
  1. (root@localhost) [frag_test]> insert into frag_test values ('this is row 1');  
    2.   
  
  
  2. Query OK, 1 row affected (0.01 sec)  
    3.   
  
  
  3.  
    4.   
  
  
  4. (root@localhost) [frag_test]> insert into frag_test values ('this is row 2');  
    5.   
  
  
  5. Query OK, 1 row affected (0.00 sec)  
    6.   
  
  
  6.  
    7.   
  
  
  7. (root@localhost) [frag_test]> insert into frag_test values ('this is row 3');  
    8.   
  
  
  8. Query OK, 1 row affected (0.00 sec) 
    9.

现在我们进行碎片查看:

 
 
 
    
  
  
  
  1. (root@localhost) [frag_test]> show table status from frag_test\G;  
    2.   
  
  
  2. *************************** 1. row ***************************  
    3.   
  
  
  3.            Name: frag_test  
    4.   
  
  
  4.          Engine: MyISAM  
    5.   
  
  
  5.         Version: 10  
    6.   
  
  
  6.      Row_format: Dynamic 
    7.   
  
  
  7.            Rows: 3  
    8.   
  
  
  8.  Avg_row_length: 20  
    9.   
  
  
  9.     Data_length: 60  
    10.   
  
  
  10. Max_data_length: 281474976710655  
    11.   
  
  
  11.    Index_length: 1024  
    12.   
  
  
  12.       Data_free: 0  
    13.   
  
  
  13.  Auto_increment: NULL 
    14.   
  
  
  14.     Create_time: 2011-02-23 14:55:27  
    15.   
  
  
  15.     Update_time: 2011-02-23 15:06:55  
    16.   
  
  
  16.      Check_time: NULL 
    17.   
  
  
  17.       Collation: latin1_swedish_ci  
    18.   
  
  
  18.        Checksum: NULL 
    19.   
  
  
  19.  Create_options:   
    20.   
  
  
  20.         Comment:   
    21.   
  
  
  21. 1 row in set (0.00 sec) 
    22.

现在我们删除一行，并再次检测:

 
 
 
    
  
  
  
  1. (root@localhost) [frag_test]> delete from frag_test where c1 = 'this is row 2';  
    2.   
  
  
  2. Query OK, 1 row affected (0.00 sec)  
    3.   
  
  
  3.  
    4.   
  
  
  4. (root@localhost) [frag_test]> show table status from frag_test\G;  
    5.   
  
  
  5. *************************** 1. row ***************************  
    6.   
  
  
  6.            Name: frag_test  
    7.   
  
  
  7.          Engine: MyISAM  
    8.   
  
  
  8.         Version: 10  
    9.   
  
  
  9.      Row_format: Dynamic 
    10.   
  
  
  10.            Rows: 2  
    11.   
  
  
  11.  Avg_row_length: 20  
    12.   
  
  
  12.     Data_length: 60  
    13.   
  
  
  13. Max_data_length: 281474976710655  
    14.   
  
  
  14.    Index_length: 1024  
    15.   
  
  
  15.       Data_free: 20  
    16.   
  
  
  16.  Auto_increment: NULL 
    17.   
  
  
  17.     Create_time: 2011-02-23 14:55:27  
    18.   
  
  
  18.     Update_time: 2011-02-23 15:07:49  
    19.   
  
  
  19.      Check_time: NULL 
    20.   
  
  
  20.       Collation: latin1_swedish_ci  
    21.   
  
  
  21.        Checksum: NULL 
    22.   
  
  
  22.  Create_options:   
    23.   
  
  
  23.         Comment:   
    24.   
  
  
  24. 1 row in set (0.00 sec) 
    25.

需要注意的是，“data_free”一栏显示出了我们删除第二行后所产生的留空空间。想象一下如果你有两万行指令的话，结果是什么样的。以此推算，它们将耗费四十万字节的存储空间。现在如果你将两万条命令行删到只剩一行，列表中有用的内容将只占二十字节，但MySQL在读取中会仍然将其视同于一个容量为四十万字节的列表进行处理，并且除二十字节以外，其它空间都被白白浪费了。

清理碎片

幸运的是一旦你锁定了这一问题，MySQL提供了一种简便的修正方法。这就是所谓的优化列表，具体内容如下:

 
 
 
    
  
  
  
  1. (root@localhost) [frag_test]> optimize table frag_test;  
    2.   
  
  
  2. +---------------------+----------+----------+----------+  
    3.   
  
  
  3. | Table               | Op       | Msg_type | Msg_text |  
    4.   
  
  
  4. +---------------------+----------+----------+----------+  
    5.   
  
  
  5. | frag_test.frag_test | optimize | status   | OK       |   
    6.   
  
  
  6. +---------------------+----------+----------+----------+  
    7.   
  
  
  7. 1 row in set (0.00 sec)  
    8.   
  
  
  8.  
    9.   
  
  
  9. (root@localhost) [frag_test]> show table status from frag_test\G;  
    10.   
  
  
  10. *************************** 1. row ***************************  
    11.   
  
  
  11.            Name: frag_test  
    12.   
  
  
  12.          Engine: MyISAM  
    13.   
  
  
  13.         Version: 10  
    14.   
  
  
  14.      Row_format: Dynamic 
    15.   
  
  
  15.            Rows: 2  
    16.   
  
  
  16.  Avg_row_length: 20  
    17.   
  
  
  17.     Data_length: 40  
    18.   
  
  
  18. Max_data_length: 281474976710655  
    19.   
  
  
  19.    Index_length: 1024  
    20.   
  
  
  20.       Data_free: 0  
    21.   
  
  
  21.  Auto_increment: NULL 
    22.   
  
  
  22.     Create_time: 2011-02-23 14:55:27  
    23.   
  
  
  23.     Update_time: 2011-02-23 15:11:05  
    24.   
  
  
  24.      Check_time: 2011-02-23 15:11:05  
    25.   
  
  
  25.       Collation: latin1_swedish_ci  
    26.   
  
  
  26.        Checksum: NULL 
    27.   
  
  
  27.  Create_options:   
    28.   
  
  
  28.         Comment:   
    29.   
  
  
  29. 1 row in set (0.00 sec) 
    30.

性能考量

 “优化列表”功能在进行中会对整个列表进行锁定。对于小型列表，这一功能的效果非常好，因为整个列表的读取和修改速度都会很快。但对于那些体积巨大的列表来说，这一过程将消耗很长时间，并且其间会中断或减少可用的应用程序数量。怎么办？

再一次，MySQL幸运地提供了一项堪称伟大的功能，名为“主-主复制”。在这种配置之下，你的后台数据库实际上成为两个单独的数据库，一个主动可调用的，一个被动可调整的。这两个数据库在各方面来说都是完全相同的。要实现各种在线操作——包括“优化列表”操作——只需在你的被动数据库中即可进行。这将不会对你的应用程序造成丝毫影响。一旦优化操作完成，主、被动数据库将互相转换，以便应用程序直接指向二号数据库，对还未进行优化的主动数据库部分自动开始优化工作。

这时，两套数据库的角色已经互换，而应用程序也将顺利指向二号数据库，执行与在一号数据库上相同的列表优化。而现在主动已经转换为被动，因此不会中断主要任务处理。

其它命令

显示你数据库中存在碎片的全部列表:

 
 
 
    
  
  
  
  1. (root@localhost) [(none)]> select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;  
    2.   
  
  
  2. +--------------+-----------------------------+-----------+--------+  
    3.   
  
  
  3. | table_schema | table_name                  | data_free | engine |  
    4.   
  
  
  4. +--------------+-----------------------------+-----------+--------+  
    5.   
  
  
  5. | aitc         | wp_comments                 |    346536 | MyISAM |   
    6.   
  
  
  6. | aitc         | wp_options                  |     64308 | MyISAM |   
    7.   
  
  
  7. | aitc         | wp_postmeta                 |       124 | MyISAM |   
    8.   
  
  
  8. | cactidb      | poller_item                 |       160 | MyISAM |   
    9.   
  
  
  9. | cactidb      | poller_output               |       384 | MyISAM |   
    10.   
  
  
  10. | drupal       | sessions                    |     30976 | MyISAM |   
    11.   
  
  
  11. | drupal       | users                       |        92 | MyISAM |   
    12.   
  
  
  12. | drupal       | variable                    |        20 | MyISAM |   
    13.   
  
  
  13. | gg           | wp_comments                 |       232 | MyISAM |   
    14.   
  
  
  14. | gg           | wp_options                  |       696 | MyISAM |   
    15.   
  
  
  15. | gg           | wp_postmeta                 |       560 | MyISAM |   
    16.   
  
  
  16. | ihi          | wp_comments                 |       536 | MyISAM |   
    17.   
  
  
  17. | ihi          | wp_options                  |       444 | MyISAM |   
    18.   
  
  
  18. | ihi          | wp_postmeta                 |       288 | MyISAM |   
    19.   
  
  
  19. | ihi          | wp_redirection_items        |      1292 | MyISAM |   
    20.   
  
  
  20. | ihi          | wp_redirection_logs         |    140352 | MyISAM |   
    21.   
  
  
  21. | nds          | wp_comments                 |      4704 | MyISAM |   
    22.   
  
  
  22. | nds          | wp_options                  |    150580 | MyISAM |   
    23.   
  
  
  23. | nds          | wp_postmeta                 |        76 | MyISAM |   
    24.   
  
  
  24. | oos          | wp_comments                 |    317124 | MyISAM |   
    25.   
  
  
  25. | oos          | wp_options                  |     88196 | MyISAM |   
    26.   
  
  
  26. | oos          | wp_postmeta                 |        76 | MyISAM |   
    27.   
  
  
  27. | phplist      | phplist_listuser            |       252 | MyISAM |   
    28.   
  
  
  28. | phplist      | phplist_sendprocess         |        52 | MyISAM |   
    29.   
  
  
  29. | phplist      | phplist_user_user           |     32248 | MyISAM |   
    30.   
  
  
  30. | phplist      | phplist_user_user_attribute |       120 | MyISAM |   
    31.   
  
  
  31. | phplist      | phplist_user_user_history   |       288 | MyISAM |   
    32.   
  
  
  32. | phplist      | phplist_usermessage         |      1428 | MyISAM |   
    33.   
  
  
  33. | pn_nds       | nuke_session_info           |     12916 | MyISAM |   
    34.   
  
  
  34. | psa          | exp_event                   |     10024 | MyISAM |   
    35.   
  
  
  35. | test         | active_sessions             |     30144 | MyISAM |   
    36.   
  
  
  36. +--------------+-----------------------------+-----------+--------+  
    37.   
  
  
  37. 31 rows in set (0.26 sec) 
    38.

如果你更改了某个列表的存储引擎，你也应该对这一列表进行碎片清理。这是因为MySQL的工作原理导致其必须读取整个列表，然后利用新的存储引擎将内容写回磁盘，而在此过程中碎片所在的位置及影响到的数据都对执行效率造成了严重的不良影响。

上述情况如下所示:

 
 
 
    
  
  
  
  1. (root@localhost) [frag_test]> alter table frag_test engine = innodb;  
    2.   
  
  
  2. Query OK, 2 rows affected (0.17 sec)  
    3.   
  
  
  3. Records: 2  Duplicates: 0  Warnings: 0  
    4.   
  
  
  4.  
    5.   
  
  
  5. (root@localhost) [frag_test]> show table status from frag_test  
    6.   
  
  
  6.     -> \G;  
    7.   
  
  
  7. *************************** 1. row ***************************  
    8.   
  
  
  8.            Name: frag_test  
    9.   
  
  
  9.          Engine: InnoDB  
    10.   
  
  
  10.         Version: 10  
    11.   
  
  
  11.      Row_format: Compact  
    12.   
  
  
  12.            Rows: 2  
    13.   
  
  
  13.  Avg_row_length: 8192  
    14.   
  
  
  14.     Data_length: 16384  
    15.   
  
  
  15. Max_data_length: 0  
    16.   
  
  
  16.    Index_length: 0  
    17.   
  
  
  17.       Data_free: 0  
    18.   
  
  
  18.  Auto_increment: NULL 
    19.   
  
  
  19.     Create_time: 2011-02-23 15:41:12  
    20.   
  
  
  20.     Update_time: NULL 
    21.   
  
  
  21.      Check_time: NULL 
    22.   
  
  
  22.       Collation: latin1_swedish_ci  
    23.   
  
  
  23.        Checksum: NULL 
    24.   
  
  
  24.  Create_options:   
    25.   
  
  
  25.         Comment: InnoDB free: 7168 kB  
    26.   
  
  
  26. 1 row in set (0.00 sec) 
    27.

结论

如果你发现一些列表中包含了大量的数据留空现象，那么对其进行优化是绝对值得的，因为这一过程会大大提升列表的读取性能及应用表现。

原文地址：http://www.databasejournal.com/features/mysql/article.php/3927871/article.htm

 

【编辑推荐】

  1. MySQL数据库的优化（下）MySQL数据库的高可用架构方案
  2. MySQL数据库的优化（上）单机MySQL数据库的优化
  3. MySQL技巧:结合相关参数 做好Limit优化
  4. 详解MySQL limit查询优化的实际操作步骤

文章标题：浅析MySQL数据碎片的产生
浏览地址：http://www.shufengxianlan.com/qtweb/news7/6257.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

网站导航知识

同城分类信息

app软件开发　　 　培训网站方案　　 　企业网站制作　　 　温江服务器托管　　 　成都酒店服定制　　 　成都网站维护公司　　 　仿古建筑设计　　 　华泰海航　　 　品牌设计　　 　成都移动服务器托管　　 　贵州义龙电信机房　　 　四川国泰民安消防　　 　创新互联重庆　　 　网站seo优化　　 　注册域名　　 　光华服务器托管　　 　成都门窗定制　　 　网站ssl证书　　 　成都名片印刷　　 　南充机房托管