MySQL的:加入对位运算符,以及它们的性能

有许多关于这个主题的问题,但我的是更具体的性能问题。

至于对象,我要跟踪的众多'属性',每一个离散的“价值”(每个属性有3个和16之间有效的“价值”。)对于实例的群众,考虑跟踪军事人员。 属性/值可能是(不是真实的,我完全做这些了):

属性:{}值
languages_spoken: {english, spanish, russian, chinese, …. }
certificates: {infantry, airborne, pilot, tank_driver…..}
approved_equipment: {m4, rocket_launcher, shovel, super_secret_radio_thingy….}
approved_operations: {reconnaissance, logistics, invasion, cooking, ….}
awards_won: {medal_honor, purple_heart, ….}

… 等等。

一一做到这一点- 我想这样做的方式-是有一个工作人员表和属性表:

personnel table => [id, name, rank, address…..]
personnel_attributes table => [personnel_id, attribute_id, value_id]

与相关联的属性,并沿值表。

所以,如果pesonnel_id = 31415已被批准用于物流,也将在personnel_attributes表如下条目:

personnel_id | attribute_id | value_id
31415 | 3 | 2

其中,3 = attribute_id为“approved_operations”和2 =为“物流”value_id(抱歉格式位没有排队。)

然后,搜索,找到谁讲英语或西班牙语的所有人员,谁是步兵或空中,而且可以操作一个铲super_secret_radio_thingy会是这样的:

SELECT t1.personnel_id FROM personnel_attributes t1, personnel_attributes t2, personnel_attributes t3
WHERE ((t1.attribute_id = 1 and t1.value_id = 1) OR (t1.attribute_id = 1 and t1.value_id = 2))
AND ((t2.attribute_id = 2 and t1.value_id = 1) OR (t2.attribute_id = 2 and t1.value_id = 2))
AND ((t3.attribute_id = 3 and t1.value_id = 3) OR (t3.attribute_id = 3 and t1.value_id = 4))
AND t2.personnel_id = t1.personnel_id
AND t3.personnel_id = t1.personnel_id;

假设这是不写的SQL查询完全愚蠢的方式,问题是,它的速度很慢(即使看似相关的索引。)

所以我正在使用位运算符来代替,每个属性是表中的一列,每个值是有点玩弄。 同样的搜索将是:

SELECT personnel_id FROM personnel_attributes
WHERE language & b'00000011'
AND certificates & b'00000011'
AND approved_operations & b'00001100';

我知道这并不全表扫描,但在我的实验35万样本的人员,每16个属性,第一种方法了20秒,而按位法用了38毫秒!

难道我做错了什么吗? 这些绩效考核的结果,我应该期待什么?

谢谢!

--------------解决方案-------------

使用按位运算需要评估所有行。 我相信你的问题可以用一个改变你原有的SELECT语句,以及如何你joing你的表来解决:

为了更容易一点阅读,我已经改变了属性值的话,而不是整数,因此它不太混乱通过我的例子读一段时间,但很明显,你可以把它们作为整数和它的概念,仍能正常工作:

CREATE TABLE PERSONNEL (
ID INT,
NAME VARCHAR(20)
)

CREATE TABLE PERSONNEL_ATTRIBUTES (
PERSONNEL_ID INT,
ATTRIB_ID INT,
ATTRIB_VALUE VARCHAR(20)
)

INSERT INTO PERSONNEL VALUES (1, 'JIM SMITH')
INSERT INTO PERSONNEL VALUES (2, 'JANE DOE')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (1, 1, 'English')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (1, 1, 'Spanish')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (1, 1, 'Russian')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (1, 3, 'Logistics')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (1, 3, 'Infantry')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (2, 1, 'English')
INSERT INTO PERSONNEL_ATTRIBUTES VALUES (2, 3, 'Infantry')

SELECT P.ID, P.NAME, PA1.ATTRIB_VALUE AS DESIRED_LANGUAGE, PA2.ATTRIB_VALUE AS APPROVED_OPERATION
FROM PERSONNEL P
JOIN PERSONNEL_ATTRIBUTES PA1 ON P.ID = PA1.PERSONNEL_ID AND PA1.ATTRIB_ID = 1
JOIN PERSONNEL_ATTRIBUTES PA2 ON P.ID = PA2.PERSONNEL_ID AND PA2.ATTRIB_ID = 3
WHERE PA1.ATTRIB_VALUE = 'Spanish' AND (PA2.ATTRIB_VALUE = 'Infantry' OR PA2.ATTRIB_VALUE = 'Airborne')

在使用Django的位域或标志单独的表的同样的问题。

通过实验的启发,我用了一个3.5米记录表(InnoDB的),并提出数()和检索的这两种方式查询。 结果是惊人的:约5秒与40秒位域胜。

分类:MySQL的 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.769 (s). 10 q(s)