CREATE TABLE `resume_feature_simhash` (
`resume_id` varchar(36) NOT NULL,
`real_name` varchar(50) DEFAULT NULL,
`last_name` varchar(10) DEFAULT NULL ,
`school` varchar(128) DEFAULT NULL,
`pinyin_name` varchar(50) DEFAULT NULL,
`real_name_sim` bigint(11) DEFAULT NULL
`pinyin_name_sim` bigint(11) DEFAULT NULL,
`company_sim` bigint(11) DEFAULT NULL,
`major_sim` bigint(11) DEFAULT NULL,
`school_sim` bigint(11) DEFAULT NULL,
`title_sim` bigint(11) DEFAULT NULL,
`work_start_date` datetime(6) DEFAULT NULL,
`edu_start_date` datetime(6) DEFAULT NULL,
`edu_end_date` datetime(6) DEFAULT NULL,
`union_id` varchar(36) DEFAULT NULL,
`firm_id` varchar(36) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`resume_id`),
KEY `IX_resume_feature_unionId` (`union_id`) USING BTREE,
KEY `IX_resume_feature_firmId` (`firm_id`) USING BTREE,
KEY `IX_resume_feature_name` (`real_name`) USING BTREE,
KEY `IX_resume_feature_pinyin_name` (`pinyin_name`) USING BTREE,
KEY `IX_resume_feature_name_realname_unionid` (`real_name`,`union_id`),
KEY `IX_resume_feature_realname_firmId` (`real_name`,`firm_id`),
KEY `IX_resume_feature_pinyin_unionId` (`pinyin_name`,`union_id`),
KEY `IX_resume_feature_pinyin_firmId` (`pinyin_name`,`firm_id`),
KEY `IX_resume_feature_lastname_unionId` (`last_name`,`union_id`),
KEY `IX_resume_feature_lastname_firmId` (`last_name`,`firm_id`),
KEY `IX_resume_feature_lastname_school_unionId` (`last_name`,`school`,`union_id`),
KEY `IX_resume_feature_lastname_school_firmId` (`last_name`,`school`,`firm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
以上是建表语句,有个查询是从这里拿出相应的记录,如SELECT
*
FROM
resume_feature_simhash
WHERE
union_id IN (
"a7e40258-8591-4a4d-87a8-f307266cd163",
"a7eb0277-5e6b-4cc7-8b14-601e91e67f8b",
"a7eb037d-b491-401a-9ecc-b1bc060679e2",
"a7f5042f-88e1-47b3-9308-2818b0743049",
"a8bc022d-ed81-4bc7-81a3-fe0b0bfabdb6",
"b6462e7b-8962-11e7-b8af-70106fb01274",
"b6464f70-8962-11e7-b8af-70106fb01274",
"b6479575-8962-11e7-b8af-70106fb01274",
"b647a889-8962-11e7-b8af-70106fb01274",
"b64f5539-8962-11e7-b8af-70106fb01274",
"b66494ae-8962-11e7-b8af-70106fb01274",
"b66a3ded-8962-11e7-b8af-70106fb01274",
"b66ac9da-8962-11e7-b8af-70106fb01274",
"b66bfcb0-8962-11e7-b8af-70106fb01274",
"a8ef02fd-c432-4a6e-9526-c5e2606d5ae2"
)
AND last_name = '余'
当目标数据达到3w的时候需要3s,几乎每增加1w时间就需要增加1s。查询都用到了索引,如下
现在问题是,有没有什么办法能将3w(随便一个量)读到内存的时间压缩为1s以内
https://blog.csdn.net/jie_liang/article/details/77340905
SELECT * FROM resume_feature_simhash WHERE union_id = 'a7e40258-8591-4a4d-87a8-f307266cd163' AND last_name = '余'
UNION
SELECT * FROM resume_feature_simhash WHERE union_id = 'a7eb0277-5e6b-4cc7-8b14-601e91e67f8b' AND last_name = '余'
UNION
SELECT * FROM resume_feature_simhash WHERE union_id = 'a7eb037d-b491-401a-9ecc-b1bc060679e2' AND last_name = '余'
……