tidb 版本为4.0
sql语句：SELECT tudun_copyright_industry_tort_img.*, tudun_project_pic.pic_url, tudun_project_pic.project_id, tudun_project_pic.details_url, tudun_project_pic.upload_time, tudun_project_pic.right_type, tudun_project_pic.id AS pid, tudun_project_pic.class_pid, tudun_project_pic.upload_uid
FROM tudun_copyright_industry_tort_img LEFT JOIN tudun_project_pic ON tudun_copyright_industry_tort_img.self_img_id=tudun_project_pic.id
WHERE 1 and tudun_copyright_industry_tort_img.project_id = 1
ORDER BY tudun_copyright_industry_tort_img.score, tudun_copyright_industry_tort_img.id DESC LIMIT 10
执行时只需要0.1s ,当时当在where 中添加and tudun_project_pic.class_pid = 2 这个条件时，执行时间为11s，这个字段已经加入了索引，为何那么慢？
- 麻烦发下两个表的表结构，要看下列是否是主键之类的
- 麻烦把 id 展现一下，需要看下具体的执行步骤，多谢。
没有class_pid时
Projection_10 10.00 10 root time:103.27827ms, loops:2, Concurrency:OFF
└─TopN_13 10.00 10 root time:103.265929ms, loops:2
└─IndexMergeJoin_23 12.50 10 root time:103.229164ms, loops:2, Concurrency:OFF
├─TopN_29(Build) 10.00 10 root time:101.572633ms, loops:3
│ └─IndexLookUp_41 10.00 110 root time:101.483951ms, loops:3, rpc num: 1, rpc time:10.821605ms, proc keys:19790
│ ├─IndexRangeScan_38(Build) 19746.00 19790 cop[tikv] table:tudun_copyright_industry_tort_img, index:project_id(project_id) time:9ms, loops:24
│ └─TopN_40(Probe) 10.00 110 cop[tikv] proc max:53ms, min:0s, p80:26ms, p95:53ms, iters:29, tasks:11
│ └─TableRowIDScan_39 19746.00 19790 cop[tikv] table:tudun_copyright_industry_tort_img proc max:49ms, min:0s, p80:26ms, p95:49ms, iters:29, tasks:11
└─TableReader_21(Probe) 1.00 10 root time:1.415926ms, loops:2, rpc num: 3, rpc max:1.347723ms, min:1.256456ms, avg:1.303909ms, p80:1.347723ms, p95:1.347723ms, proc keys max:8, p95:8
└─TableRangeScan_20 1.00 10 cop[tikv] table:tudun_project_pic proc max:0s, min:0s, p80:0s, p95:0s, iters:3, tasks:3
有class_pid 时
| Projection_9 | 10.00 | 10 | root | time:11.434870185s, loops:2, Concurrency:OFF | |
|---|---|---|---|---|---|
| └─TopN_12 | 10.00 | 10 | root | time:11.43485373s, loops:2 | |
| └─IndexHashJoin_42 | 36.41 | 3227 | root | time:11.434058749s, loops:6, Concurrency:4 | |
| ├─IndexLookUp_71(Build) | 10.00 | 1395843 | root | time:1.776153519s, loops:1369, rpc num: 3, rpc max:434.053968ms, min:137.87215ms, avg:332.600867ms, p80:434.053968ms, p95:434.053968ms, proc keys max:637831, p95:637831 | |
| │ ├─IndexRangeScan_69(Build) | 10.00 | 1395843 | cop[tikv] | table:tudun_project_pic, index:class_pid(class_pid) | proc max:391ms, min:120ms, p80:391ms, p95:391ms, iters:1377, tasks:3 |
| │ └─TableRowIDScan_70(Probe) | 10.00 | 1395843 | cop[tikv] | table:tudun_project_pic | proc max:373ms, min:1ms, p80:172ms, p95:281ms, iters:1892, tasks:116 |
| └─IndexLookUp_32(Probe) | 3.64 | 3227 | root | time:40.568832472s, loops:99, rpc num: 116, rpc max:1.003607041s, min:3.527106ms, avg:355.932967ms, p80:733.418839ms, p95:881.37002ms, proc keys max:2231, p95:957 | |
| ├─Selection_30(Build) | 89.15 | 20673 | cop[tikv] | proc max:977ms, min:2ms, p80:711ms, p95:835ms, iters:254, tasks:116 | |
| │ └─IndexRangeScan_28 | 89.15 | 20673 | cop[tikv] | table:tudun_copyright_industry_tort_img, index:self_img_id(self_img_id, outside_img_id) | proc max:977ms, min:2ms, p80:711ms, p95:835ms, iters:254, tasks:116 |
| └─Selection_31(Probe) | 3.64 | 3227 | cop[tikv] | proc max:17ms, min:0s, p80:6ms, p95:9ms, iters:209, tasks:65 | |
| └─TableRowIDScan_29 | 89.15 | 20673 | cop[tikv] | table:tudun_copyright_industry_tort_img | proc max:17ms, min:0s, p80:6ms, p95:9ms, iters:209, tasks:65 |
表机构：
CREATE TABLE tudun_copyright_industry_tort_img (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
self_img_id int(11) unsigned DEFAULT 0 COMMENT ‘项目图片id’,
outside_img_id int(11) unsigned DEFAULT 0 COMMENT ‘爬取的图片id’,
score int(4) unsigned DEFAULT 0 COMMENT ‘vgg等分’,
company_id int(11) DEFAULT 0 COMMENT ‘公司id’,
right_type tinyint(1) DEFAULT 0 COMMENT ‘0无版权 1有版权’,
project_id smallint(3) DEFAULT 0 COMMENT ‘项目组id’,
task_id int(11) DEFAULT 0 COMMENT ‘任务id’,
add_time int(11) NOT NULL DEFAULT 0 COMMENT ‘记录添加时间’,
PRIMARY KEY (id),
UNIQUE KEY self_img_id (self_img_id,outside_img_id),
KEY add_time (add_time),
KEY project_id (project_id),
KEY company_id (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=43985484 COMMENT=‘同行业图片侵权表’
CREATE TABLE tudun_project_pic (
id int(11) NOT NULL AUTO_INCREMENT,
pic_id int(11) DEFAULT 0,
project_id smallint(3) DEFAULT 0,
pic_url varchar(255) DEFAULT ‘’,
upload_uid int(11) DEFAULT 0,
details_url varchar(255) DEFAULT ‘’,
right_type tinyint(1) DEFAULT 0,
type varchar(100) DEFAULT ‘’,
status tinyint(1) DEFAULT 0,
is_agent tinyint(1) DEFAULT 0,
class_pid int(11) DEFAULT 0,
class_id int(11) DEFAULT 0,
class_cid int(11) DEFAULT 0,
upload_time int(11) DEFAULT 0,
copyright_time int(11) DEFAULT 0,
create_time timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY project_id (project_id,pic_id,class_pid),
KEY upload_uid (upload_uid),
KEY class_pid (class_pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=160686052
谢谢，辛苦了
这个计划能否更新一下格式？这个看起来比较吃力
通过手动 ignore tudun_project_pic.class_pid 相关索引，同时用 hint 指定 merge join，看看这种跟没有这个条件相近的计划执行时间。
因为这里 indexLoopUp join 的匹配率似乎不高，与估计出入较大。
另外也可以试试 hint 指定 hash join。
请把这两张表的统计信息上传这里，方便进一步分析。