7
回答
我的sql语句执行非常慢,应该如何优化?
终于搞明白,存储TCO原来是这样算的>>>   

mysql 5.7.18

表DDL如下
```
CREATE TABLE `roadnet_monitor_flowdata` (
  `id` bigint(20) DEFAULT NULL,
  `交通类型` text,
  `出站人数` bigint(20) DEFAULT NULL,
  `日期` text,
  `站点` text,
  `纬度` double DEFAULT NULL,
  `线路` text,
  `经度` double DEFAULT NULL,
  `结束时间` text,
  `起始时间` text,
  `进站人数` bigint(20) DEFAULT NULL,
  KEY `ix_dpsplatform_roadnet_monitor_flowdata_id` (`id`),
  KEY `idx_roadnet_monitor_flowdata_起始时间` (`起始时间`(8))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
模样如下:

数量大概是500W

我的sql查询语句


```
select sum(进站人数),sum(出站人数) 
from roadnet_monitor_flowdata 
where 日期= '2017-03-16' 
group by 起始时间;
```
在这上面运行的非常缓慢,**请问如何优化**?(我建了索引index,但是没有卵用。。。)
索引如下:

举报
芝麻糖人
发帖于6天前 7回/205阅
共有7个答案 最后回答: 5天前

优化方案:

1,修正表结构字段类型

日期【varchar(10)】

结束时间【varchar(19)或改为时间戳】

起始时间【varchar(19)或改为时间戳】

2,创建索引字段

日期

 

你可以复制一张表,然后修改表字段,同样的语句你可以测试下效果。另外对于数据较大的表,表结构的设计非常关键,否则就是给后人挖坑。

--- 共有 1 条评论 ---
芝麻糖人多谢指点,请转战这里。 https://www.oschina.net/question/2277123_2254626 5天前 回复
一方面是中文字段,另一方面是bigint和text这两个类型都是最占空间的,类型要结合实际使用,然后就是日期字段用date类型
CREATE TABLE `roadnet_monitor_flowdata` (
  `id` bigint(20) DEFAULT NULL,
  `交通类型` text,
  `出站人数` bigint(20) DEFAULT NULL,
  `日期` text,
  `站点` text,
  `纬度` double DEFAULT NULL,
  `线路` text,
  `经度` double DEFAULT NULL,
  `结束时间` text,
  `起始时间` text,
  `进站人数` bigint(20) DEFAULT NULL,
  KEY `ix_dpsplatform_roadnet_monitor_flowdata_id` (`id`),
  KEY `idx_roadnet_monitor_flowdata_起始时间` (`起始时间`(8)),
  KEY `idx_date` (`日期`(10)) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

把'日期'字段也建立index,500w数据本地测试每次查询在30毫秒左右.

 

--- 共有 2 条评论 ---
notmmao 回复 @OSC首席键客 : 看下面例子 5天前 回复
OSC首席键客你哪里搞得500w数据? 6天前 回复
#! /usr/bin/env python
# -*- coding: utf-8 -*-
#
# gen_sql.py

import random
import time

template = '''INSERT INTO `roadnet_monitor_flowdata` VALUES(%d, "", %d, "%s", "", "", "", "", "%s", "%s", %d);''';

def random_date():
    y = random.randint(2000,2017)
    m = random.randint(1,12)
    d = random.randint(1,30)
    
    date = "%04d-%02d-%02d" % (y,m,d)
    
    return date

def main():
    random.seed(time.clock())
    max = 5000000
    for i in range(max):
        id = random.randint(1,max)
        num_in = random.randint(1,max/10)
        num_out = random.randint(1,max/10)
        date = random_date()
        start = random_date()
        end = random_date()
        
        print template % (id, num_in, date, start, end, num_out);

if __name__ == "__main__":
    main()

 

python gen_sql.py >i500w.sql

顶部