日志流水表应该怎么建索引？

Question

MYSQL 数据库

有一个消息日志 MESSAGELOG 表，系统每隔2秒会向里面插入大概10条记录

现在这个表里面有30万条记录 有 id，time，label_id,tower_id,station_id 5个字段，id是主键

这个表的特点是 label_id,tower_id,station_id的重复非常多， 很多记录不同的地方就是时间time
不一样(当然还有id不一样)

比如label_id=226的记录有27216条，这个 27216 条记录的 label_id,tower_id,station_id都一样，就time不一样

这个查询：

select count(1)
from MESSAGELOG log
LEFT  join LABEL label
on log.label_id = label.binary_id
LEFT join DEVICE tower

on log.tower_id = tower.binary_id
LEFT join DEVICE station
on log.station_id = station.binary_id
where tower.type in ('TYPE_ONEWAYTOWER','TYPE_ALLWAYTOWER')
and station.type  = 'TYPE_STATION'

需要4秒。

MESSAGELOG 表 time,label_id,tower_id,station_id有索引，还有一个(label_id,tower_id,station_id)联合索引

MESSAGELOG 30万记录，LABEL 表20条记录，DEVICE 24条记录

上面的查询要用4秒，属于正常，还是慢？ 索引设计的合理么，该怎么设计？

另外，这个日志表的数据会越来越多，对于300万，500万，乃至1000万的数据量，继续用MYSQL合适么？

希望有经验的朋友说说。

Answer 1

原始sql:
[code="sql"]
select count(1)
from MESSAGELOG log
LEFT join LABEL label on log.label_id = label.binary_id
LEFT join DEVICE tower on log.tower_id = tower.binary_id
LEFT join DEVICE station on log.station_id = station.binary_id
where tower.type in ('TYPE_ONEWAYTOWER','TYPE_ALLWAYTOWER')
and station.type = 'TYPE_STATION'
[/code]
这条sql种DEVICE表的left join先成inner join 会更明确,
对于选择度不高的适合建立联合索引
在time,label_id,tower_id,station_id有索引，还有一个(label_id,tower_id,station_id)联合索引 , 这个索引太多了 影响你的insert 速度 好在你的插入压力不达， 建立去掉一些索引,则么去 结合你所有的查询类型去
sql可以改写为:
[code="sql"]select count(log.id)
from MESSAGELOG log
LEFT join LABEL label on log.label_id = label.binary_id
inner join DEVICE tower on
(log.tower_id = tower.binary_id or log.station_id = tower.binary_id)
where tower.type in
('TYPE_ONEWAYTOWER','TYPE_ALLWAYTOWER', 'TYPE_STATION') [/code]
在大表 连接中 限制标得数量
MESSAGELOG 30万记录，LABEL 表20条记录，DEVICE 24条记录
对于这条查询只需要:MESSAGELOG表的tower_id,station_id label_id这三列单独索引
最好看是否可以把LABEL换成inner join
因为mysql 时loop join 小外表 先连接在连接大表会提高性能,如果你是left join
mysql优化器是没有办法做连接表顺序替换的优化
可以看看高性能MYSQL一书第四章.