MySql - WAMP - 巨大表格非常缓慢（2000万行）

Question

昨天我发布了这个问题，并得到了完美的答案，需要先运行此代码：ALTER TABLE mytable AUTO_INCREMENT=10000001;

我运行了几次，但在几小时后重新启动WAMP后仍然无法正常工作。经过一夜（12小时）的运行，代码仍未运行。

我想知道我的数据库表大小是否超出了mysql或计算机的限制，还是两者都有。

不过，我怀疑适当的索引或其他因素可能会极大地影响我的性能。我知道2000万行是很多的，但是它太多了吗？

我不太了解索引，只知道它们很重要。我尝试添加名称和状态字段的索引，我相信我已经成功了。

顺便说一句，我正在尝试添加一个唯一的ID字段，这就是我昨天发布的帖子的全部内容。

所以，问题是：2000万行是否超出了MySql的范围？如果没有，我是否错过了某些索引或其他设置，可以更好地处理这2000万行？我可以在所有列上建立索引使其变得非常快吗？

谢谢您的帮助...

以下是规格：

我的PC是XP，运行WAMPSERVER，Win32 NTFS，Intel Duo Core，T9300 @ 2.50GHz，1.17 GHz，1.98 GB或RAM

数据库：1个表，20,000,000行 表的大小为： 数据4.4 GB，索引1.3 GB，合计5.8 GB

索引设置在“BUSINESS NAME”和“STATE”字段上

表字段如下：

`BUSINESS NAME` TEXT NOT NULL, 
`ADDRESS` TEXT NOT NULL, 
`CITY` TEXT NOT NULL, 
`STATE` TEXT NOT NULL, 
`ZIP CODE` TEXT NOT NULL, 
`COUNTY` TEXT NOT NULL, 
`WEB ADDRESS` TEXT NOT NULL, 
`PHONE NUMBER` TEXT NOT NULL, 
`FAX NUMBER` TEXT NOT NULL, 
`CONTACT NAME` TEXT NOT NULL, 
`TITLE` TEXT NOT NULL, 
`GENDER` TEXT NOT NULL, 
`EMPLOYEE` TEXT NOT NULL, 
`SALES` TEXT NOT NULL, 
`MAJOR DIVISION DESCRIPTION` TEXT NOT NULL, 
`SIC 2 CODE DESCRIPTION` TEXT NOT NULL, 
`SIC 4 CODE` TEXT NOT NULL, 
`SIC 4 CODE DESCRIPTION` TEXT NOT NULL 

- Kevin

---

数据库非常喜欢快速的磁盘，我看到你的电脑是一台笔记本电脑（Txxxx CPU），几乎所有笔记本电脑的磁盘性能都很差。并不是说20M条记录很少，但也不是一个庞大的表。增加允许MySQL使用的内存也可能会有很大帮助。 - fvu

Answer 1

一些答案：

• 2000万行数据在MySQL中是完全能够处理的。我曾经在处理一个拥有5亿行数据的表格时工作过。重构一个表格可能需要几个小时，但只要辅以索引，普通查询并不会有问题。

• 你的笔记本电脑已经相当老旧和配置不足以用作高性能数据库服务器。重构表格可能需要很长时间。内存较少和通常较慢的笔记本电脑硬盘很可能是瓶颈。同时，你可能使用了MySQL的默认设置，这些设置是为非常老的计算机设计的。

• 我不建议对每个列都使用TEXT数据类型。多数情况下，这些列并不需要TEXT。

• 不要为每个列都创建索引，特别是当你坚持使用TEXT数据类型时。你甚至不能在TEXT列上建立索引，除非定义一个前缀索引。一般来说，选择支持特定查询的索引。

基于以上观点，你可能有许多其他问题，但这里没有足够的空间来解决所有问题。如果你想要处理数据库，可能需要接受培训或者阅读相关书籍。
我推荐 《MySQL高性能》（第2版）。

---

关于你的后续问题：

对于MySQL优化，这里是一个很好的开始：http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

许多ALTER TABLE操作会导致表格重构，这基本上意味着锁定表格，制作整个表格的副本并应用更改，然后重命名新旧表格并删除旧表格。如果表格非常大，则此过程可能需要很长时间。

TEXT数据类型可以存储高达64KB的数据，这对于电话号码或州名来说已经太大了。美国典型的电话号码可以使用CHAR(10)表示，而州名可以使用CHAR(2)表示。一般来说，请使用最紧凑和节省空间的数据类型，在给定列中支持所需范围的数据。

- Bill Karwin

---

感谢您的评论。事实上，我使用mysql/php已经很多年了，只是我从来没有处理过这么大的表格。过去我处理的大部分表格都是少于一百万行的。话虽如此，还有很多东西需要学习。 - Kevin

如果不使用“文本”数据类型，您会建议用什么标准数据类型来存储电话号码或地址字段？这个数据类型可以是字母数字混合的，但不是整数。 - Kevin

我已经按照以下方式更改了php.ini/mysql.ini的设置：post_max_size = 750M，upload_max_filesize = 750M，max_execution_time = 5000，max_input_time = 5000，memory_limit = 1000M，max_allowed_packet = 200M（在my.ini中）。是否还有其他明显的更改需要进行？如果有，您能指导我正确的方向吗？最后，我没有遇到普通查询（select * from table where field = x;）的问题，而是添加索引字段、添加新字段似乎使我的电脑崩溃了。这就是您所说的“重构表”可能需要花费数小时的操作吗？ - Kevin

Answer 2

由于您只有2GB RAM和6GB的数据/索引，所以这将需要很长时间，并且会在RAM和磁盘之间强制进行大量的交换。虽然没有太多可以做的事情，但您可以尝试分批运行此操作。创建一个包含auto_increment列的单独空表，然后每次插入一定数量的记录（例如，一次插入1个状态）。这可能会使它更快，因为您应该能够完全在内存中处理这些较小的数据集，而不是分页到磁盘上。如果您在dba.stackexchange.com上发布此问题，您可能会得到更好的回复。

- Derek Kromm

Answer 3

我相信硬件没问题，但你需要更好地节约资源。

数据库结构优化！

• 不要使用TEXT！
• 对于电话号码，请使用bigint unsigned。任何符号或字母都必须被解析和转换。
• 对于任何其他字母数字列，请使用例如varchar([32-256])。
• 邮政编码当然是mediumint unsigned。
• 性别应该是enum('男'，'女')
• 销售额可以是int unsigned
• 州应该是enum('阿拉斯加',...)
• 国家应该是enum('阿尔巴尼亚',...)

在构建大型索引时，最快的方法是创建一个新表并执行INSERT INTO ... SELECT FROM ...而不是ALTER TABLE ...。

将州和国家字段更改为枚举类型将大大减少索引大小。

- Gustav

---

你真的不想把电话号码存储在int字段中。 - ypercubeᵀᴹ

还是不对。对于像Facebook这样的大公司来说，我猜现在要改成有意义的数据类型可能更加困难。因为他们还必须更改所有将它们视为int的代码。 - ypercubeᵀᴹ

每个电话号码都可以用数字写成，因此没有比存储它更节省空间的其他方式。 - Gustav

我同意，从全局根级别开始存储是唯一的解决方案。我们不同意的是，这是一个由于将“int”用于应该存储为“char”的内容而创建的问题的解决方案。当需要进行加法、乘法、除法等运算时，应使用整数。电话“号码”不会相加或相减。它们有时会添加或删除前缀，这些操作可以更好地使用字符串函数处理。 - ypercubeᵀᴹ

你的回答几乎是正确的。但正如crazyhat所说，Int类型用于进行计算，例如年龄、年份、孩子数量、价格、数量等。但电话只是一个唯一的代码，其中使用数字，以便普通人可以更轻松地使用它。因此，请编辑您的答案，以便程序员投票并帮助其他人找到更好的答案。 - KeitelDOG