我有一个 Mysql 表,其架构如下:
CREATE TABLE `historical_pricing` (
`date` date DEFAULT NULL,
`company` varchar(12) DEFAULT NULL,
`price` double(20,5) DEFAULT NULL,
`vol` double DEFAULT NULL,
`id` varchar(25) NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `date_idx` (`date`),
KEY `company_idx` (`company`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
已经加载了大约 12GB 的数据,并且在性能合理的计算机上运行查询以获取不同的代码不会在 15 分钟内完成。
我有几个微服务可以查找:
SELECT distinct(company) from historical_pricing
SELECT * FROM historical_pricing where company = 'AAPL' ORDER BY date desc
我通读了 MySQL 5.7 文档,我认为我可以通过1)按公司名称分区和2)为日期设置另一个索引来加快查询速度。有一个在MySQL 5.7 8192分区的限制,所以我想使用基于起始名分区:a%
,b%
,1%
,2%
,等。
我想弄清楚:
添加分区的正确 MySQL ALTER TABLE 语法是什么?我一直无法弄清楚这是否是列表、范围等。
为 company_date_idx(首先是公司,然后是日期)做一个索引是否有意义,或者 date_idx 是否就足够了,因为索引将在每个分区内创建?
有没有更有效的方法来优化我的查询?
编辑::
我还有另一个索引公司,日期作为多列索引。我注意到,无论如何,每次查找都需要重新运行整个表。如果我应该扩大我的,我运行了以下查询innodb_buffer_pool_size
:
SELECT engine,
count(*) as TABLES,
concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;
结果:
Engine Table rows data idx total_size idxfrac
InnoDB 9 288.85M 36.28G 58.59G 94.87G 1.61
机器只有3.5GB内存,设置为1GB用于Mysql
id
还是company
??)id
或 中的另一个company
。PRIMARY KEY(ticker, date)
This 将比简单地好几个数量级INDEX(ticker, date)
。为什么?因为第二个查询的所有行都彼此相邻。也就是说,您不会在磁盘上为SELECT *
.但是,你应该停在那里。
缩小数据类型有助于避免昂贵的 I/O...
DOUBLE(m,n)
不好。我希望他们能摆脱这种语法。它导致两个舍入。要么说,要么DOUBLE
用DECIMAL
.price
... 感谢 BRK,您需要在小数点左侧至少保留 6 位数字。由于“便士”或几乎退市的股票,您需要几个小数位。也许DECIMAL(12, 6)
会做?这需要 6 个字节(与 8 个相比DOUBLE
)。volume
... 一些指数基金可以超过40亿,所以INT UNSIGNED
是不够的。也许你需要一个 8-byte DOUBLE
,或者用 4-byte 失去一些精度FLOAT
。或者一起去DECIMAL
。created_at
和updated_at
- 它们基本上没用。它们占用 10 个字节。其他查询呢?特别是,您将每天为每个股票行情添加一个新行??我建议的 PK 会奏效,尽管速度很慢。而且,由于您有整晚的时间来进行插入,所以我们不要添加另一个索引。
如果您有任何其他疑问,那么我们可以考虑PARTITIONs
。到目前为止,分区只会减慢速度。
不要担心“向后索引扫描”。查询的主要成本是 I/O,我已经解决了这个问题。
请注意您的每个查询现在如何需要触及某些表中的连续行。这与扫描一个巨大的表(您的查询 1)或在索引和数据之间来回移动(查询 2)相反。因此,无论缓存有多“冷”,我的设计对于两个查询都会快得多。
附注。不需要二级索引。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句