mysql 大表性能_提高大型MySQL表的性能

我想问一个关于如何使用innodb引擎提高大型MySQL表的性能的问题：

目前我的数据库中有一个表,大约有2亿行.该表定期存储由不同传感器收集的数据.该表的结构如下：

CREATE TABLE sns_value (

value_id int(11) NOT NULL AUTO_INCREMENT,

sensor_id int(11) NOT NULL,

type_id int(11) NOT NULL,

date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

value int(11) NOT NULL,

PRIMARY KEY (value_id),

KEY idx_sensor id (sensor_id),

KEY idx_date (date),

KEY idx_type_id (type_id) );

起初,我想过在几个月内对桌子进行分区,但由于新传感器的不断增加,它将在一个月左右达到目前的尺寸.

我想出的另一个解决方案是通过传感器对表进行分区.但是,由于MySQL的1024个分区的限制不是一个选项.

我相信正确的解决方案是使用每个传感器具有相同结构的表：

sns_value_XXXXX

这样,将有超过1000个表,估计每年大小为3000万行.同时,这些表可以在几个月内进行分区,以便最快地访问数据.

这个解决方案会带来什么问题？有更正常的解决方案吗？

使用其他信息进行编辑

我认为该表与我的服务器有关：

>云2xCPU和8GB内存

> LAMP(CentOS 6.5和MySQL 5.1.73)

每个传感器可能有多种变量类型(CO,CO2等).

我主要有两个慢查询：

1)每个传感器和类型的每日摘要(平均值,最大值,最小值)：

SELECT round(avg(value)) as mean, min(value) as min, max(value) as max, type_id

FROM sns_value

WHERE sensor_id=1 AND date BETWEEN '2014-10-29 00:00:00' AND '2014-10-29 12:00:00'

GROUP BY type_id limit 2000;

这需要超过5分钟.

2)垂直到水平视图和导出：

SELECT sns_value.date AS date,

sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 101)))))) AS one,

sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 141)))))) AS two,

sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 151)))))) AS three

FROM sns_value

WHERE sns_value.sensor_id=1 AND sns_value.date BETWEEN '2014-10-28 12:28:29' AND '2014-10-29 12:28:29'

GROUP BY sns_value.sensor_id,sns_value.date LIMIT 4500;

这也需要5分钟以上.

其他考虑

>由于插入特性,可能会重复时间戳.

>周期性插入必须与选择共存.

>不对表执行更新或删除.

对“每个传感器的一个表”方法做出了假设

>每个传感器的表格会小得多,因此访问速度会更快.

>每个传感器仅在一个表上执行选择.

>选择来自不同传感器的混合数据不是时间关键的.

更新02/02/2015

我们为每年的数据创建了一个新表,我们也每天对其进行分区.每个表有大约2.5亿行,有365个分区.使用的新索引是Ollie建议的(sensor_id,date,type_id,value),但查询仍需要30秒到2分钟.我们不使用第一个查询(每日摘要),只使用第二个查询(垂直于水平视图).

为了能够对表进行分区,必须删除主索引.

我们错过了什么吗？有没有办法改善表现？

非常感谢！

解决方法:

根据问题的变化进行编辑

事实上,每个传感器一张表确实是一个非常坏的想法.有几个原因：

>普通操作系统上的MySQL服务器很难有数千个表.大多数操作系统无法同时处理多个同时进行的文件访问.

>每次添加(或删除)传感器时都必须创建表格.

>涉及来自多个传感器的数据的查询将是缓慢且复杂的.

我之前的这个答案版本建议按时间戳划分范围.但这不适用于您的value_id主键.但是,对于您显示的查询以及对表的正确索引,可能不需要进行分区.

(如果可以的话,请避免列名称日期：这是一个保留字,你在编写查询时会遇到很多麻烦.相反,我建议你使用ts,意思是时间戳.)

注意：int(11)值对于value_id列来说不够大.你将耗尽ids.对该列使用bigint(20).

你提到了两个问题.即使您将所有值保存在单个表中,这两个查询都可以通过适当的复合索引非常高效.这是第一个.

SELECT round(avg(value)) as mean, min(value) as min, max(value) as max,

type_id

FROM sns_value

WHERE sensor_id=1

AND date BETWEEN '2014-10-29 00:00:00' AND '2014-10-29 12:00:00'

GROUP BY type_id limit 2000;

对于此查询,您首先使用常量查找sensor_id,然后查找一系列日期值,然后按type_id进行聚合.最后,您要提取值列.因此,所谓的compound covering index on(sensor_id,date,type_id,value)将能够通过索引扫描直接满足您的查询.这应该对你来说非常快 – 即使有一张大桌子,肯定会超过5分钟.

在第二个查询中,类似的索引策略将起作用.

SELECT sns_value.date AS date,

sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 101)))))) AS one,

sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 141)))))) AS two,

sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 151)))))) AS three

FROM sns_value

WHERE sns_value.sensor_id=1

AND sns_value.date BETWEEN '2014-10-28 12:28:29' AND '2014-10-29 12:28:29'

GROUP BY sns_value.sensor_id,sns_value.date

LIMIT 4500;

再次,您从sensor_id的常量值开始,然后使用日期范围.然后提取type_id和value.这意味着我提到的相同的四列索引应该适合您.

CREATE TABLE sns_value (

value_id bigint(20) NOT NULL AUTO_INCREMENT,

sensor_id int(11) NOT NULL,

type_id int(11) NOT NULL,

ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

value int(11) NOT NULL,

PRIMARY KEY (value_id),

INDEX query_opt (sensor_id, ts, type_id, value)

);

标签：mysql,sql-server,innodb

来源： https://codeday.me/bug/20190708/1403101.html