ต้องการคำแนะนำเกี่ยวกับโครงสร้างข้อมูล MySQL

ฉันต้องการคำแนะนำเกี่ยวกับวิธีการจัดระเบียบข้อมูลของฉันเพื่อการค้นหาข้อความที่มีประสิทธิภาพและรวดเร็ว

พื้นหลัง

ฉันมีแอปพลิเคชัน (ใน PHP) ที่ผู้ใช้สามารถจัดระเบียบบทความและสร้างแบบฟอร์มและฟิลด์แบบไดนามิกเพื่อจุดประสงค์นั้นได้ หมายความว่าบทความหนึ่งสามารถมีแอตทริบิวต์ Type,Brand,Color และอีกบทความหนึ่งสามารถมี Type,Material,Color,Content เป็นแอตทริบิวต์ได้ โดยพื้นฐานแล้วผู้ใช้สามารถสร้างคุณลักษณะได้มากเท่าที่เขาชอบ...

จากนั้น ฉันจะต้องสามารถค้นหาและจัดเรียงแอตทริบิวต์ "ที่ไม่รู้จัก" เหล่านี้ได้ ฉันยังต้องสามารถอ่านแอตทริบิวต์ทั้งหมดได้ในกรณีที่ผู้ใช้ต้องการแก้ไขบทความ

ทางออกของฉัน

แนวคิดแรกของฉัน (และแนวคิดเดียวจนถึงขณะนี้) คือการเข้ารหัสคุณลักษณะทั้งหมดลงในฟิลด์ TEXT เดียวด้วยดัชนี FULLTEXT (ต้องเป็น MyISAM จึงจะทำงานได้) เช่น:

__Type="3",__Brand="Nokia",__Color="6"
__Type="2",__Material="7",Color="2",Content="MP3 Player,2 Apples, 1 book: Larry King"

คุณลักษณะจะได้รับคำนำหน้าและ/หรือคำนำหน้าเพื่อไม่ให้สับสนกับค่าในแอตทริบิวต์ หรือทำให้แอตทริบิวต์เป็นอนุกรมด้วย JSON

จากนั้น ฉันสามารถสร้างแบบสอบถามตามแอตทริบิวต์ที่เลือก เช่น:

SELECT * FROM Articles a
WHERE Attribute LIKE '%__TYPE="2"%'
AND Attribute LIKE '%__Color="2"%'

หากแอตทริบิวต์ว่างเปล่า จะไม่รวมไว้ และทำให้สามารถรวมการค้นหาในบทความทั้งหมดที่มีชุดคุณลักษณะเฉพาะ โดยไม่คำนึงถึงค่า

ปัญหา

ปัญหาหรือไม่สิ่งที่ฉันกังวลคือประสิทธิภาพการค้นหาเมื่อฐานข้อมูลเต็มไปด้วยบทความนับพัน

ปัญหาอีกประการหนึ่งคือการค้นหาคำเฉพาะภายในคุณลักษณะเฉพาะ เช่น:

Content="เครื่องเล่น MP3,แอปเปิ้ล 2 ลูก, 1 เล่ม: Larry King"

สมมติว่าฉันต้องการรับแถวที่เนื้อหาแอตทริบิวต์มีวลี "Larry King" อยู่ที่ไหนสักแห่ง ฉันไม่คิดว่าจะทำอย่างนั้นได้ในคำถาม SQL เดียวกันโดยไม่ได้รับการจับคู่ในทุกแถวที่มี "Larry King" อยู่ที่ไหนสักแห่ง

ฉันเปิดรับข้อเสนอแนะ/การอภิปรายเกี่ยวกับตาราง ฟิลด์ และความสัมพันธ์ที่ฉันควรสร้างเพื่อให้บรรลุเป้าหมายที่อธิบายไว้

ขอบคุณ


mysql php sql full-text-search database-design
person Max Kielland    schedule 12.01.2011    source แหล่งที่มา
comment
เป็น type=3 มียี่ห้อและสีเสมอ; type=2 เสมอ: วัสดุ, สี, เนื้อหา?   -  person    schedule 12.01.2011
comment
จะมีแอตทริบิวต์ root อยู่เสมอ แต่คุณลักษณะสามารถจัดเรียงเป็นแผนผังได้หลายระดับ ดังนั้น Type ที่นี่จึงอาจลงเอยด้วยชุด/รูปแบบแอตทริบิวต์ที่แตกต่างกัน   -  person Max Kielland    schedule 12.01.2011

คำตอบ (1)


arrow_upward
4
arrow_downward

หากคุณกำลังจะค้นหาค่าของแอตทริบิวต์เฉพาะบ่อยๆ ทำไมไม่ทำให้แอตทริบิวต์เหล่านั้นมีคอลัมน์เป็นของตัวเองในตารางล่ะ หรือถ้าคุณต้องการโครงสร้างที่ยืดหยุ่นมากขึ้น ให้สร้างตารางที่สองดังนี้:

CREATE TABLE attributes (
 my_id int unsigned not null default 0,
 attribute_key varchar(255) not null default '',
 attribute_value varchar(255) not null default '',
 KEY (my_id),
 KEY (attribute_key),
 KEY (attribute_value)
);

ในกรณีนี้ ช่อง my_id จะเป็นคีย์หลักของตารางหลักของคุณ ดังนั้นแทนที่จะทำให้สตริงเป็นอนุกรมเช่น:

__Type="2",__Material="7",Color="2",Content="MP3 Player,2 Apples, 1 book: Larry King"

คุณจะสร้างแถวแทนเช่น:

INSERT INTO attributes VALUES (1, 'Type', '2');
INSERT INTO attributes VALUES (1, 'Color', '2');
INSERT INTO attributes VALUES (1, 'Content', 'MP3 Player,2 Apples, 1 book: Larry King');

จากนั้นคุณจะต้องกำหนดคำค้นหาของคุณเช่น:

SELECT * FROM mytable 
LEFT JOIN attributes ON mytable.my_id = attributes.my_id 
WHERE attributes.attribute_key = 'Type' AND attributes.attribute_value = '2';

วิธีนี้ไม่ได้แก้ปัญหาที่สองของคำถามของคุณได้อย่างแม่นยำ แต่จะทำงานได้ดีกว่าการค้นหาข้อความแบบเต็มในหลายพันแถว แน่นอนว่าคุณสามารถเพิ่มดัชนี FULLTEXT ในฟิลด์ attribute_value ได้เช่นกันเพื่อค้นหาส่วนย่อยของข้อความ เช่น ตัวอย่าง "Larry King" ของคุณ

person futureal    schedule 12.01.2011
comment
+1 เป็นความคิดที่ดีมาก ฉันต้องลองดูว่าจะครอบคลุมความต้องการทั้งหมดของฉันหรือไม่ ฉันยังคงเปิดรับข้อเสนอแนะเพิ่มเติม... - person Max Kielland; 12.01.2011
comment
@futureal แต่คุณสมบัติของตารางจะมีขนาดใหญ่กว่าตารางบทความจริงประมาณ 5 หรือ 8 เท่า แนวทางนี้ยังคงมีประสิทธิภาพเหนือกว่าความคิดเดิมของฉันหรือไม่? - person Max Kielland; 12.01.2011
comment
ใช่มากๆ เลย การมีแถวจำนวนมากไม่ใช่ปัญหาหากได้รับการจัดทำดัชนีอย่างเหมาะสม หากคุณมี n แถวในตารางหลัก และคุณเรียกใช้แบบสอบถามในแอตทริบิวต์เฉพาะ คุณลักษณะนั้นจะยังคงตรวจสอบเพียง n แถวเท่านั้น (สูงสุด) จะดูเฉพาะชุดย่อยของข้อมูลที่เกี่ยวข้องกับแบบสอบถามเท่านั้น - person futureal; 12.01.2011
comment
ฉันพบวิธีแก้ปัญหาของคุณเพื่อเป็นตัวเลือกที่ดีที่สุดที่นี่ ขอบคุณ! - person Max Kielland; 12.01.2011
comment
ไม่มีปัญหา นี่เป็นรูปแบบการออกแบบ SQL มาตรฐานที่ค่อนข้างดี ดังนั้นจึงเป็นเรื่องดีที่จะรู้และฝึกฝน ขอให้โชคดี! :) - person futureal; 12.01.2011
comment
@futureal ความคิด... ดูเหมือนว่าแอตทริบิวต์จะมีสองประเภทหลักๆ คือ ตัวเลขเท่านั้นและข้อความ ฉันจะได้อะไรจากการมีสองตารางแอตทริบิวต์ หนึ่งตารางสำหรับจำนวนเต็มอย่างเคร่งครัด (เช่นสีหรือประเภท) โดยที่จำนวนเต็มเป็น ID ในตารางสตริง ด้วยวิธีการนี้ ฉันสามารถรวมเพื่อรับค่าข้อความในคำถามเดียวกันเมื่อแสดงบทความ ฉันเดาว่ามันขึ้นอยู่กับตารางที่จะค้นหาเท่านั้น ฉันเดาว่าดัชนีตัวเลขดัชนี specifib นั้นมีประสิทธิภาพมากกว่า - person Max Kielland; 12.01.2011
comment
ดูเหมือนว่าวิธีแก้ปัญหา - แต่เมื่อดึงข้อมูลฉันจะใช้ GROUP_CONCAT - มันจะรวมคุณลักษณะทั้งหมดใน 1 ฟิลด์ (ตามที่คุณต้องการ) ใน 1 ระเบียน - person ; 12.01.2011
comment
@Joanna ฉันได้จัดตารางด้วยวิธีอื่นและใช้ UNION ALL เพื่อดึงข้อมูลทั้งหมดเข้าด้วยกัน สิ่งนี้ดำเนินต่อไปใน stackoverflow.com/questions/4668747/ - person Max Kielland; 12.01.2011