V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
nulIptr
V2EX  ›  MySQL

mysql 表设计/慢查询求解,详细场景见正文

  •  
  •   nulIptr · 2020-09-28 11:53:42 +08:00 · 2629 次点击
    这是一个创建于 1277 天前的主题,其中的信息可能已经有所发展或是发生改变。
    
    
    CREATE TABLE `vehicle` (
      `Id` char(36) NOT NULL COMMENT 'Id Guid', 
      `Dr` int(11) NOT NULL COMMENT '',
      `OrganizationId` bigint(20) unsigned DEFAULT NULL,
      `DeviceNo` varchar(20) DEFAULT NULL,
      `LicensePlateNo` varchar(10) DEFAULT NULL,
      `vin` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`Id`), 
      KEY `vehicle_vin_IDX` (`vin`,`Dr`,`OrganizationId`,`LicensePlateNo`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
    
    
    
    CREATE TABLE `trip` (
      `Id` char(36) NOT NULL COMMENT '记录 Id',
      `Vin` varchar(50) NOT NULL COMMENT '车辆 vin',
      `EndTime` datetime NOT NULL COMMENT '行程结束时间',   
      -- 还有其余二十个字段
      PRIMARY KEY (`Id`),
      KEY `trip_Vin_IDX` (`Vin`,`EndTime`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行程数据明细';
    
    
    select
    		v.OrganizationId, v.Vin, v.LicensePlateNo,s.*	from
    		vehicle `v`
    	join trip `s` on
    		`s`.`Vin` = `v`.`Vin`
    	where
    		v.dr = 0
    		 and v.OrganizationId between 10000000000000000 and 19999999999999999
    order by
    	EndTime  desc
    limit 0,
    10
    

    先上 sql 语句 业务场景是车辆表大概存了几百辆车,行程跟车辆是多对一的关系,trip 表有 20w 条数据,还在持续增长。。 OrganizationId 为了兼容组织结构层级关系,上级部门能看到下级部门的数据,设计成 18 位 10 进制数字,从高位起每 2 位算一个层级,最多 9 级,查询的时候就是上面 select 语句这样

    但问题是有了范围查询排序似乎就走不了索引了,现在这个查询要 10 秒左右,该怎么优化呢。

    16 条回复    2020-09-28 17:46:15 +08:00
    shenjinpeng
        1
    shenjinpeng  
       2020-09-28 12:04:32 +08:00
    20w 数据 x 几百辆车, 这么点数据要 10s , 感觉不太对 . 应该是 Vin 字段的问题, between 只有几百条数据, 有没有索引影响不大 . 帖个 sql 执行计划看看
    linuxvalue
        2
    linuxvalue  
       2020-09-28 12:07:06 +08:00
    看这个命名好难受 大小写乱用 毫无规范
    huntcool001
        3
    huntcool001  
       2020-09-28 12:19:56 +08:00
    explain ANALYZE select
    v.OrganizationId, v.Vin, v.LicensePlateNo,s.* from
    vehicle `v`
    join trip `s` on
    `s`.`Vin` = `v`.`Vin`
    where
    v.dr = 0
    and v.OrganizationId between 10000000000000000 and 19999999999999999
    order by
    EndTime desc
    limit 0,
    10



    把这个结果贴一下
    huntcool001
        4
    huntcool001  
       2020-09-28 12:58:20 +08:00
    这个是走了索引的, 我猜是最后排序引起的临时文件耗时太长了. 要看一下执行时间才知道.
    CodeCodeStudy
        5
    CodeCodeStudy  
       2020-09-28 13:43:05 +08:00
    建议:
    1 、字段名用下划线分隔,而不是用大写驼峰;
    2 、主键用自增的 INT UNSIGNED,而不是 CHAR(36) ;
    3 、字段尽可能地用 NOT NULL ;
    4 、vehicle 表的别名既然叫了 v,那么 trip 表的别名能不能不要叫 s,叫 t 是不是更好一些?
    5 、OrganizationId 字段能不能不要那么大?组织结构应重新设计;
    6 、trip 表的 Vin 字段加上索引,最好 trip 表存 vehicle 表的自增的主键作为关联的条件,ON 的时候 INT 总比 VARCHAR 好;
    7 、trip 表的 EndTime 字段的顺序是不是都跟插入的顺序一致?都一致的话,排序的时候可以使用自增的主键代替;
    8 、vehicle 表才几百条数据的话,为什么要用 `vehicle_vin_IDX` (`vin`,`Dr`,`OrganizationId`,`LicensePlateNo`) 做索引?
    xx6412223
        6
    xx6412223  
       2020-09-28 13:46:52 +08:00
    order by 没用上索引。把 order by 去掉试试
    CodeCodeStudy
        7
    CodeCodeStudy  
       2020-09-28 13:47:04 +08:00
    9 、先用 EXPLAIN 看一下是不是有 Using filesort 或 Using temporary ;
    10 、一个表很小,另一个很大的话,可以考虑单独查询,然后在程序里处理;
    sanggao
        8
    sanggao  
       2020-09-28 13:47:05 +08:00 via iPhone
    楼上老哥说出了我的心里话
    nulIptr
        9
    nulIptr  
    OP
       2020-09-28 14:17:18 +08:00
    @shenjinpeng
    @huntcool001
    ``` json

    {
    "version": "json",
    "signature": "MySQL Connector Java",
    "date": "2020-09-28T13:58:48.103781",
    "sql": "select\r\n\t\tv.OrganizationId, v.Vin, v.LicensePlateNo,s.*\tfrom\r\n\t\tvehicle `v`\r\n\tjoin trip `s` on\r\n\t\t`s`.`Vin` \u003d `v`.`Vin`\r\n\twhere\r\n\t\tv.dr \u003d 0\r\n\t\t and v.OrganizationId between 10000000000000000 and 19999999999999999\r\norder by\r\n\tEndTime desc\r\nlimit 0,\r\n10",
    "root": [
    {
    "name": "",
    "kind": "Node",
    "type": "select",
    "cond": "",
    "desc": "",
    "attributes": {
    "select_id": "1",
    "query_cost": "3808.72"
    },
    "child": [
    {
    "name": "",
    "kind": "Node",
    "type": "ordering_operation",
    "cond": "",
    "desc": "",
    "attributes": {
    "using_temporary_table": "true",
    "using_filesort": "true",
    "sort_cost": "1684.78"
    },
    "child": [
    {
    "name": "",
    "kind": "Node",
    "type": "nested_loop#1",
    "cond": "",
    "desc": "",
    "attributes": {},
    "child": [
    {
    "name": "v (index)",
    "kind": "Node",
    "type": "table",
    "cond": "",
    "desc": "",
    "attributes": {
    "table_name": "v",
    "access_type": "index",
    "possible_keys": "[\"vehicle_vin_IDX\"]",
    "key": "vehicle_vin_IDX",
    "used_key_parts": "[\"vin\",\"Dr\",\"OrganizationId\",\"LicensePlateNo\"]",
    "key_length": "259",
    "rows_examined_per_scan": "471",
    "rows_produced_per_join": "5",
    "filtered": "1.11",
    "using_index": "true",
    "read_cost": "101.15",
    "eval_cost": "1.05",
    "prefix_cost": "102.20",
    "data_read_per_join": "7K",
    "used_columns": "[\"Id\",\"Dr\",\"OrganizationId\",\"LicensePlateNo\",\"vin\"]",
    "attached_condition": "(((`v`.`Dr` \u003d 0) and (`v`.`OrganizationId` between 10000000000000000 and 19999999999999999)) and (`v`.`vin` is not null))"
    }
    }
    ]
    },
    {
    "name": "",
    "kind": "Node",
    "type": "nested_loop#2",
    "cond": "",
    "desc": "",
    "attributes": {},
    "child": [
    {
    "name": "s (ref)",
    "kind": "Node",
    "type": "table",
    "cond": "",
    "desc": "",
    "attributes": {
    "table_name": "s",
    "access_type": "ref",
    "possible_keys": "[\"trip_Vin_IDX\"]",
    "key": "trip_Vin_IDX",
    "used_key_parts": "[\"Vin\"]",
    "key_length": "202",
    "ref": "[\"orgidchange.v.vin\"]",
    "rows_examined_per_scan": "321",
    "rows_produced_per_join": "1684",
    "filtered": "100.00",
    "read_cost": "1684.78",
    "eval_cost": "336.96",
    "prefix_cost": "2123.94",
    "data_read_per_join": "3M",
    "used_columns": "[\"Id\",\"Vin\",\"LicensePlateNo\",\"20 个其他字段\"]"
    }
    }
    ]
    }
    ]
    }
    ]
    }
    ]
    }

    ```
    上面是 dbeaver 导出的执行计划,可能更详细点,自带的 explain 表格搞了半天格式都会乱,好难用,mysql 版本是 5.7,没有 explain ANALYZE
    nulIptr
        10
    nulIptr  
    OP
       2020-09-28 14:26:21 +08:00
    @xx6412223 对呀,去掉 orderby 就是 1 秒内了,但是需求要求分页。

    @CodeCodeStudy 我自己喜欢 sql 全大写,但是到现在这个公司已经变成这个现在的形状了。。。
    对于组织 id 字段如果重新设计的话应该怎么搞?核心需求就是父级部门能看到子部门的数据,平级部门之间互相隔离。
    用 vin 做关联好像是通用做法,据说 vin 就等于车辆唯一标识。
    `vehicle_vin_IDX` (`vin`,`Dr`,`OrganizationId`,`LicensePlateNo`)这个索引属于有病乱投医。
    xx6412223
        11
    xx6412223  
       2020-09-28 14:31:50 +08:00
    @nulIptr 这个简单处理就是分两步查询,先找出 vehicle.vin ,再去 trip 里查询
    复杂的也有,不过效益和这个方法比不大,需要很多调试:用 vin 去做基础表,可能需要 force index
    xx6412223
        12
    xx6412223  
       2020-09-28 14:33:29 +08:00
    @xx6412223 上面有错别字,是用 trip 做基准表
    huntcool001
        13
    huntcool001  
       2020-09-28 15:26:40 +08:00
    set session sort_buffer_size = 6 *1024 * 1024;
    select
    v.Vin,s.EndTime from
    vehicle `v`
    join trip `s` on
    `s`.`Vin` = `v`.`Vin`
    where
    v.dr = 0
    and v.OrganizationId between 10000000000000000 and 19999999999999999
    order by
    EndTime desc
    limit 0,10;
    set session sort_buffer_size = 256*1024



    这样试试看
    dog82
        14
    dog82  
       2020-09-28 16:10:19 +08:00
    设计有问题,有层级关系,用 pid 不更合适么
    nulIptr
        15
    nulIptr  
    OP
       2020-09-28 16:55:32 +08:00
    @dog82 如果你说的 pid 指的是 parentid,那查起来可太费劲了,比这个还费劲
    pkupyx
        16
    pkupyx  
       2020-09-28 17:46:15 +08:00
    explain 看一下呗
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1167 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 18:29 · PVG 02:29 · LAX 11:29 · JFK 14:29
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.