char vs varchar在股票数据库中的表现 [英] char vs varchar for performance in stock database

问题描述

我正在使用mySQL建立股票期权数据库.大约有330,000行(每行是1个选项).我是SQL的新手，所以我试图确定诸如选项符号(从4到5个字符不等)，股票符号(从1到5个字符不等)，公司名称(从5到60不等)之类的字段类型.字符).

I'm using mySQL to set up a database of stock options. There are about 330,000 rows (each row is 1 option). I'm new to SQL so I'm trying to decide on the field types for things like option symbol (varies from 4 to 5 characters), stock symbol (varies from 1 to 5 characters), company name (varies from 5 to 60 characters).

我想优化速度.两者都创建数据库(随着新价格数据的出现，每5分钟发生一次-我没有实时数据Feed，但由于我获得了一个新的文本文件，其中包含330,000行的数据传递，因此几乎是实时的)每5分钟；此新数据将完全替换之前的数据)，并提高查找速度(将有一个基于Web的前端，许多用户可以在其中运行临时查询).

I want to optimize for speed. Both creating the database (which happens every 5 minutes as new price data comes out -- i don't have a real-time data feed, but it's near real-time in that i get a new text file with 330,000 rows delivered to me every 5 minutes; this new data completely replaces the prior data), and also for lookup speed (there will be a web-based front end where many users can run ad hoc queries).

如果我不关心空间(由于db寿命为5分钟，并且每行可能包含300个字节，那么整个行可能包含100MB)，那么构造字段的最快方法是什么?

If I'm not concerned about space (since the db lifetime is 5 minutes, and each row contains maybe 300 bytes, so maybe 100MBs for the whole thing) then what is the fastest way to structure the fields?

对于数字字段，实际上存在相同的问题:int(11)和int(7)之间是否存在性能差异?在查询和排序时，一种长度比另一种长度更好吗?

Same question for numeric fields, actually: Is there a performance difference between int(11) and int(7)? Does one length work better than another for queries and sorting?

谢谢！

推荐答案

在MyISAM中，制作固定宽度记录有一些好处. VARCHAR是可变宽度. CHAR是固定宽度的.如果您的行仅具有固定宽度的数据类型，则整行都是固定宽度的，并且MySQL在计算该表中行的空间要求和偏移量时会获得一些好处.就是说，优点可能很小，并且拥有固定宽度的填充CHAR列(VARCHAR将更紧凑地存储在其中)，几乎不值得获得任何其他微小的好处(例如高速缓存效率)所抵消的微小收益.

In MyISAM, there is some benefit to making fixed-width records. VARCHAR is variable width. CHAR is fixed-width. If your rows have only fixed-width data types, then the whole row is fixed-width, and MySQL gains some advantage calculating the space requirements and offset of rows in that table. That said, the advantage may be small and it's hardly worth a possible tiny gain that is outweighed by other costs (such as cache efficiency) from having fixed-width, padded CHAR columns where VARCHAR would store more compactly.

提高断点效率的断点取决于您的应用程序，只有通过测试这两种解决方案并在应用程序的使用情况下使用最适合您的数据的解决方案，这是无法解决的.

The breakpoint where it becomes more efficient depends on your application, and this is not something that can be answered except by you testing both solutions and using the one that works best for your data under your application's usage.

关于INT(7)与INT(11)，这与存储或性能无关.一个常见的误解是，MySQL的INT类型参数与数据大小有关，而与数据大小无关. MySQL的INT数据类型始终为32位.括号中的参数表示如果您使用ZEROFILL显示值，则应填充几位数字.例如. INT(7)将显示0001234，而INT(11)将显示00000001234.但是，此填充仅在显示值时发生，而不是在存储或数学计算期间发生.

Regarding INT(7) versus INT(11), this is irrelevant to storage or performance. It is a common misunderstanding that MySQL's argument to the INT type has anything to do with size of the data -- it doesn't. MySQL's INT data type is always 32 bits. The argument in parentheses refers to how many digits to pad if you display the value with ZEROFILL. E.g. INT(7) will display 0001234 where INT(11) will display 00000001234. But this padding only happens as the value is displayed, not during storage or math calculation.

这篇关于char vs varchar在股票数据库中的表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！