MySql - WAMP - 大表很慢(2000 万行) [英] MySql - WAMP - Huge Table is very slow (20 million rows)

问题描述

所以我发布了这个！昨天得到了一个完美的答案，需要先运行这段代码:ALTER TABLE mytable AUTO_INCREMENT=10000001;

So I posted this! yesterday and got a perfect answer, which required running this code first: ALTER TABLE mytable AUTO_INCREMENT=10000001;

我运行了几次，但在几个小时后无法正常工作后重新启动了 WAMP.运行一夜(12 小时)后，代码仍然没有运行.

I ran it several times, but restarted WAMP after a couple of hours of it not working. After running overnight (12 hours), the code still hadn't run.

我想知道我的数据库表大小是否超过了 mysql 或我的计算机或两者的限制.

I am wondering if my database table size is past the limits of mysql or my computer or both.

但是，我有一个偷偷摸摸的怀疑，正确的索引或其他一些因素可能会极大地影响我的表现.我知道 2000 万行是很多行，但是太多了吗?

However, I have a sneaky suspicion that proper indexing or some other factor could greatly impact my performance. I know 20 million is a lot of rows, but is it too much?

我对索引知之甚少，只知道它们很重要.我尝试将它们添加到 name 和 state 字段中，我相信我成功了.

I don't know much about indexes, except that they are important. I attempted to add them to the name and state fields, which I believe I did successfully.

顺便说一下，我想添加一个唯一的 ID 字段，这就是我昨天的帖子的全部内容.

Incidentally, I am trying to add a unique ID field, which is what my post yesterday was all about.

那么，问题是:2000 万行是否超出了 MySql 的范围?如果没有，我是否缺少有助于更好地处理这 2000 万行的索引或其他设置?我可以在所有列上放置索引并使其速度超快吗?

So, the question is: Is 20 million rows outside the scope of MySql? If not, am I missing an index or some other setting that would help better work with this 20 million rows? Can I put indexes on all the columns and make it super fast?

一如既往，提前致谢...

As always, thanks in advance...

以下是规格:

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

My PC is XP, running WAMPSERVER, Win32 NTFS, Intel Duo Core, T9300 @ 2.50GHz, 1.17 GHz, 1.98 GB or RAM

DB:1 个表，2000 万行表的大小是:数据 4.4 Gigs，索引 1.3 Gigs，总计 5.8 Gigs

DB: 1 table, 20 million rows The size of the tables is: Data 4.4 Gigs, Indexes 1.3 Gigs, Total 5.8 Gigs

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

The indexes are set up on the 'BUSINESS NAME' and 'STATE' fields

表格字段是这样的:

`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 

推荐答案

一些答案​​:

• 2000 万行完全在 MySQL 的能力范围内.我在一个数据库中工作，其中一个表中有超过 5 亿行.重构一张表可能需要几个小时，但只要有索引辅助，普通查询就不是问题.

• 20 million rows is well within the capability of MySQL. I work on a database that has over 500 million rows in one of its tables. It can take hours to restructure a table, but ordinary queries aren't a problem as long as they're assisted by an index.

您的笔记本电脑已经过时并且功能不足，无法用作大型数据库服务器.进行表重组需要很长时间.低内存量和通常较慢的笔记本电脑磁盘可能会限制您.您可能也在使用 MySQL 的默认设置，这些设置旨在在非常旧的计算机上运行.

Your laptop is pretty out of date and underpowered to use as a high-scale database server. It's going to take a long time to do a table restructure. The low amount of memory and typically slow laptop disk is probably constraining you. You're probably using default settings for MySQL too, which are designed to work on very old computers.

我不建议对每一列使用TEXT数据类型.对于大多数这些列，您没有理由需要 TEXT.

I wouldn't recommend using TEXT data type for every column. There's no reason you need TEXT for most of those columns.

不要对每一列都创建索引，尤其是当您坚持使用 TEXT 数据类型时.除非您定义了前缀索引，否则您甚至无法索引TEXT 列.通常，选择索引以支持特定查询.

Don't create an index on every column, especially if you insist on using TEXT data types. You can't even index a TEXT column unless you define a prefix index. In general, choose indexes to support specific queries.

基于上述内容，您可能还有许多其他问题，但在一篇 StackOverflow 帖子中无法涵盖的内容太多了.如果您要使用数据库，您可能需要接受培训或阅读一本书.
我推荐 高性能 MySQL，第二版.

You probably have many other questions based on the above, but there's too much to cover in a single StackOverflow post. You might want to take training or read a book if you're going to work with databases.
I recommend High Performance MySQL, 2nd Edition.

回复您的后续问题:

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

For MySQL tuning, here's a good place to start: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

许多 ALTER TABLE 操作会导致表重组，这意味着基本上锁定表，复制整个表并应用更改，然后重命名新表和旧表并删除旧表.如果表非常大，这可能需要很长时间.

Many ALTER TABLE operations cause a table restructure, which means basically lock the table, make a copy of the whole table with the changes applied, and then rename the new and old tables and drop the old table. If the table is very large, this can take a long time.

一个 TEXT 数据类型最多可以存储 64KB，这对于电话号码或状态来说是多余的.我会使用 CHAR(10) 作为典型的美国电话号码.我将 CHAR(2) 用于美国州.通常，请使用支持给定列中所需数据范围的最紧凑和节俭的数据类型.

A TEXT data type can store up to 64KB, which is overkill for a phone number or a state. I would use CHAR(10) for a typical US phone number. I would use CHAR(2) for a US state. In general, use the most compact and thrifty data type that supports the range of data you need in a given column.

这篇关于MySql - WAMP - 大表很慢(2000 万行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！