V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
ackermanHu
V2EX  ›  问与答

mysql 单表 283 万数据,如何做到一秒内查询。

  •  
  •   ackermanHu · 2019-10-12 17:55:05 +08:00 · 3329 次点击
    这是一个创建于 1894 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表数据有 283 万, 用索引后,索引先查出来数万到数十万数据, 需要查询表里所有字段,所以复合索引没有效果, 有什么建议? 网上说的百万数据毫秒级查询,是如何做到的?

    16 条回复    2019-10-18 13:00:34 +08:00
    ackermanHu
        1
    ackermanHu  
    OP
       2019-10-12 17:56:46 +08:00
    求大佬们不吝赐教。
    opengps
        2
    opengps  
       2019-10-12 18:00:29 +08:00
    从单块硬盘上取,即使每行只有 1 个字节,这也得接近 3M,事实上多字段怎么说也得 100 字节以上,300M 用 1 秒时间显然有点难度。
    整片数据从内存里取或许还有点希望。但是接口的网络传输还需要占用一定时间。
    linwu
        3
    linwu  
       2019-10-12 18:06:44 +08:00
    上面理解错了,他说的应该是查询满足条件的包含所有字段的行数据,不是所有数据
    sethverlo
        4
    sethverlo  
       2019-10-12 18:10:09 +08:00
    三百万随便优化优化,别不加索引、别瞎搞表结构,1s 内完全没问题啊……是不是有点儿小看 mysql
    sadfQED2
        5
    sadfQED2  
       2019-10-12 18:23:56 +08:00 via Android
    我们公司一大把百万以上数据的表,也没见哪个不能 1 秒以内啊。你把表结构和 sql 贴出来看看
    helloh1n1
        6
    helloh1n1  
       2019-10-12 23:16:27 +08:00
    用 HDFS 可不可行?数据规模大的话应该是用分布式的框架会快一点吧
    Linxing
        7
    Linxing  
       2019-10-13 02:07:25 +08:00
    如果這樣都做不到 還要數據庫幹嘛 但是 dba 說 100W 以上就要考慮分了
    ackermanHu
        8
    ackermanHu  
    OP
       2019-10-14 15:06:29 +08:00
    CREATE TABLE `alarm_record_view` (
    `id` INT(11) NOT NULL,
    `house_id` INT(11) NULL DEFAULT '0',
    `project_id` INT(11) NULL DEFAULT '0',
    `center_id` INT(11) NULL DEFAULT '0' COMMENT '中心 id',
    `create_ts` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '报警时间',
    `network_num` VARCHAR(50) NULL DEFAULT NULL COMMENT '联网编号',
    `network_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '联网名称',
    `user_name` VARCHAR(50) NULL DEFAULT '0' COMMENT '业主名',
    `house_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '房产名称',
    `area_id` INT(11) NULL DEFAULT '0' COMMENT '辖区 id',
    `defence_area_id` VARCHAR(50) NULL DEFAULT '0' COMMENT '防区号',
    `module_id` VARCHAR(50) NULL DEFAULT '0' COMMENT '模块 ID',
    `defence_area` VARCHAR(50) NULL DEFAULT '0' COMMENT '防区位置',
    `probe_version` VARCHAR(50) NULL DEFAULT '0' COMMENT '探头型号',
    `type` VARCHAR(50) NULL DEFAULT '0' COMMENT '类型',
    `content` VARCHAR(50) NULL DEFAULT '0' COMMENT '详情',
    `device_type` INT(11) NULL DEFAULT '0' COMMENT '设备类型:1 烟感报警器 2 温感报警器 3 手报 4 入侵 5 紧急 6 水侵',
    `alarm_er` VARCHAR(50) NULL DEFAULT '0' COMMENT '警情 ER 类型',
    `alarm_code` VARCHAR(50) NULL DEFAULT '0' COMMENT '警情代码',
    `signal` VARCHAR(50) NULL DEFAULT '0' COMMENT '信号',
    `voltage` VARCHAR(50) NULL DEFAULT '0' COMMENT '电量',
    `ar_device_type` INT(11) NULL DEFAULT NULL COMMENT '0 传统 1NB',
    `ao_id` INT(11) NULL DEFAULT NULL,
    `inspect_record_id` INT(11) NULL DEFAULT NULL COMMENT '巡检工单 id',
    `maintainer_id` INT(11) NULL DEFAULT NULL COMMENT '维修工单 id',
    `label` VARCHAR(32) NULL DEFAULT NULL COMMENT '信号标签:设备巡检||设备维修||设备巡检设备维修',
    `operator` VARCHAR(50) NULL DEFAULT NULL COMMENT 'NB 撤布防操作者',
    PRIMARY KEY (`id`),
    INDEX `house_id` (`house_id`),
    INDEX `project_id` (`project_id`),
    INDEX `center_id` (`center_id`),
    INDEX `ao_id` (`ao_id`),
    INDEX `area_id` (`area_id`),
    INDEX `network_num` (`network_num`),
    INDEX `network_name` (`network_name`),
    INDEX `user_name` (`user_name`),
    INDEX `module_id` (`module_id`),
    INDEX `create_ts` (`create_ts`),
    INDEX `ar_device_type` (`ar_device_type`),
    INDEX `inspect_record_id` (`inspect_record_id`),
    INDEX `maintainer_id` (`maintainer_id`)
    )
    COMMENT='报警流水视图'
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;



    SQL:select * from alarm_record_view a where a.area_id= 2;
    数据结果:27 字段*125922rows ;耗时 5.X 秒

    explain: "id""select_type" "table""partitions""type""possible_keys""key" "key_len" "ref" "rows""filtered" "Extra"
    "1" "SIMPLE" "a" "ref" "area_id" "area_id" "5" "const" "260072" "100.00"
    pedia0992
        9
    pedia0992  
       2019-10-15 11:12:55 +08:00
    我可以尝试帮你优化一下,上面就是你全部的 SQL 语句吗? @ackermanHu
    pedia0992
        10
    pedia0992  
       2019-10-15 11:16:13 +08:00
    MySQL 版本是什么?
    ackermanHu
        11
    ackermanHu  
    OP
       2019-10-15 13:55:53 +08:00
    mysql 版本是 5.7。sql 语句就是这样。 @pedia0992 麻烦您了。
    ackermanHu
        12
    ackermanHu  
    OP
       2019-10-15 14:00:05 +08:00
    在补充一下,这张表总数据量 283W 左右,大小 1.3G 。
    ackermanHu
        13
    ackermanHu  
    OP
       2019-10-15 14:03:42 +08:00
    即便是加了索引的单字段查询,sql 耗时与查询结果的数据量成正比。如上 sql 示例,查询结果为 12 万时,耗时已达 5 秒多。根据 Query profile 显示,Sending data 占了 5.0X 秒。
    ackermanHu
        14
    ackermanHu  
    OP
       2019-10-15 14:05:46 +08:00
    数据库是阿里云的 RDS,规格族:通用型 数据库类型:MySQL 5.7 CPU:2 核
    数据库内存:4096MB 最大 IOPS:2000 最大连接数:1200
    pedia0992
        15
    pedia0992  
       2019-10-15 16:59:14 +08:00
    @ackermanHu explain "整个 select 语句" 这个结果完整发出来.
    ackermanHu
        16
    ackermanHu  
    OP
       2019-10-18 13:00:34 +08:00 via iPhone
    Sql 下面就是。走了 key 的,但是扫描行数有 26W。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5916 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 02:09 · PVG 10:09 · LAX 18:09 · JFK 21:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.