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



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 秒左右，该怎么优化呢。

null

vin

default

comment

16 replies • 2020-09-28 17:46:15 +08:00

urlk

Sep 28, 2020

20w 数据 x 几百辆车, 这么点数据要 10s , 感觉不太对 . 应该是 Vin 字段的问题, between 只有几百条数据, 有没有索引影响不大 . 帖个 sql 执行计划看看

linuxvalue

Sep 28, 2020

看这个命名好难受大小写乱用毫无规范

huntcool001

Sep 28, 2020

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

Sep 28, 2020

这个是走了索引的, 我猜是最后排序引起的临时文件耗时太长了. 要看一下执行时间才知道.

CodeCodeStudy

Sep 28, 2020

建议：
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

Sep 28, 2020

order by 没用上索引。把 order by 去掉试试

CodeCodeStudy

Sep 28, 2020

9 、先用 EXPLAIN 看一下是不是有 Using filesort 或 Using temporary ；
10 、一个表很小，另一个很大的话，可以考虑单独查询，然后在程序里处理；

sanggao

Sep 28, 2020 via iPhone

楼上老哥说出了我的心里话

nulIptr

Sep 28, 2020

@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

Sep 28, 2020

@xx6412223 对呀，去掉 orderby 就是 1 秒内了，但是需求要求分页。

@CodeCodeStudy 我自己喜欢 sql 全大写，但是到现在这个公司已经变成这个现在的形状了。。。
对于组织 id 字段如果重新设计的话应该怎么搞？核心需求就是父级部门能看到子部门的数据，平级部门之间互相隔离。
用 vin 做关联好像是通用做法，据说 vin 就等于车辆唯一标识。
`vehicle_vin_IDX` (`vin`,`Dr`,`OrganizationId`,`LicensePlateNo`)这个索引属于有病乱投医。

xx6412223

Sep 28, 2020

@nulIptr 这个简单处理就是分两步查询，先找出 vehicle.vin ，再去 trip 里查询
复杂的也有，不过效益和这个方法比不大，需要很多调试：用 vin 去做基础表，可能需要 force index

xx6412223

Sep 28, 2020

@xx6412223 上面有错别字，是用 trip 做基准表

huntcool001

Sep 28, 2020

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

Sep 28, 2020

设计有问题，有层级关系，用 pid 不更合适么

nulIptr

Sep 28, 2020

@dog82 如果你说的 pid 指的是 parentid，那查起来可太费劲了，比这个还费劲

pkupyx

Sep 28, 2020

explain 看一下呗