MySQL优化20G表
我有一个 20 GB 的表，每天都有大量的插入和更新。这张表也经常被搜索。我想知道 MySQL 索引是否会变得碎片化，并且可能需要重建或类似的东西。
我发现很难弄清楚检查表、维修表或类似的表中的哪一个？
任何指导表示赞赏，我是一个数据库新手。
I have a 20 gig table that has a large amount of inserts and updates daily. This table is also frequently searched. I'd like to know if the MySQL indices can become fragmented and perhaps need to be rebuilt or something similar.
I'm finding it difficult to figure out which of the CHECK TABLE, REPAIR TABLE or something similar?
Any guidance appreciated, I'm a db newb.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据您使用的 MySQL 版本，您可以考虑创建表分区。您可以通过多种方式使用它，可以进行 id 哈希分区，也可以进行日期分区。
还有一些方法可以从逻辑上将“工作”数据与档案数据分开。考虑一个代表提要的大表。您使用的数据中 95% 可能是小于 14 或 28 天的数据，其余数据通常可以放入存档表中。
如果能够将数据块划分为多个服务器，那么有一些关于如何使用 MySQL 创建联合数据存储系统的精彩演示。 Brad Fitpatrick 对 LiveJournal 的概述是一个很好的起点。
根据您存储的数据类型，您甚至可能根本不需要 MySQL。如果您的大部分查找都是主键获取，您可能需要研究 Redis 或 Cassandra 等键/值存储系统，看看它们是否满足您的需求。
Depending on the version of MySQL you are using, you could consider creating table partitions. There are several ways that you can go about using it, either by doing id hash paritioning or date paritioning.
There are also ways to logically seperate 'working' data from archival data. Consider a large table that represents a feed. It may be that data less than 14 or 28 days old is 95% of your used data and the rest can routinely be put in an archive table.
If being able to partition into multiple servers representing chunks of data is possible, there are some great presentations about how to create federated data storage systems using MySQL. Brad Fitpatrick's overview of LiveJournal is a great place to start.
Depending on the type of data you are storing, you may not even need MySQL at all. If most of your lookups are primary key gets, you may want to investigate key/value storage systems like Redis or Cassandra to see if they meet your needs.