Архитектура на база от данни за многоезично съдържание

В скоро време проведох някои разговори и спорове на работа за най-добрата архитектура на базата от данни за многоезична поддръжка. Това е въпрос, който е бил задаван много пъти в миналото: Как да се организира базата от данни за поддръжка на многоезични версии на уеб сайтовете ? Ок, тук нашите цели са две:

• перформантност
• лесно менажиране – добавяне на нови полета, изтриване на съществуващи и т.н.

Така както го виждам аз, проблема може да бъде решен по три начина:

• най-простият, използван най-вече в миналото (според мен) – единствена таблица съдържаща всички полета – общи + езикови
• множество езикови таблици – всеки език си има собствена таблица, която допълва общата таблица
• една единствена езикова таблица, допълваща общата таблица – езиковата таблица има primary key по id от общата таблица и езика

И така реших да проверя кое е най-добре да се ползва. Използвах следната структура:

• за първия начин:

CREATE TABLE IF NOT EXISTS `simplest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title_en` varchar(255) NOT NULL DEFAULT ‘ ‘,
`title_bg` varchar(255) NOT NULL DEFAULT ‘ ‘,
`body_en` text NOT NULL,
`body_bg` text NOT NULL,
`sum_en` int(11) NOT NULL DEFAULT ’0′,
`sum_bg` int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8

• за втория начин

CREATE TABLE IF NOT EXISTS `lang_tbls` (
`id` int(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

CREATE TABLE IF NOT EXISTS `lang_tbls_bg` (
`id` int(11) NOT NULL DEFAULT ’0′,
`title` varchar(255) NOT NULL DEFAULT ‘ ‘,
`body` text NOT NULL,
`sum` int(11) NOT NULL DEFAULT ’0′,
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lang_tbls_en` (
`id` int(11) NOT NULL DEFAULT ’0′,
`title` varchar(255) NOT NULL DEFAULT ‘ ‘,
`body` text NOT NULL,
`sum` int(11) NOT NULL DEFAULT ’0′,
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

• и за третия начин

CREATE TABLE IF NOT EXISTS `one_tbl` (
`id` int(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

CREATE TABLE IF NOT EXISTS `one_tbl_lang` (
`id` int(11) NOT NULL DEFAULT ’0′,
`lang` char(2) NOT NULL DEFAULT ‘en’,
`title` varchar(255) NOT NULL DEFAULT ‘ ‘,
`body` text NOT NULL,
`sum` int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`id`,`lang`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Сега, след като знаем какви са нашите опции в детайли, нека видим кой е най-удобен за лесно управление, промяна и поддръжка. Както може би предполагате, това е последния вариант. Когато трябва да се добави нов език … няма какво да се прави по базата от данни. Когато трябва да се добави ново поле, …. просто се променя една таблица. Между първите два варианта по-добър е вторият, защото таблиците отделят двата езика и е по-лесно за управление, за сметка на наличието на повече таблици, разбира се.

Ок и сега най-важната част – перформантността. Създадох структурата описана по-горе на моя localhost mysql база от данни. След това направих скрипт, който да налее данни – 5000 записа и после 10000 записа. Пуснах няколко заявки, които според мен са ключови и биха послужили, за да се вземе решение. Ето ги и резултатите:

0.0018 sec/5000 rec, 0.0018 sec/10000 rec,
SELECT * FROM `simplest` WHERE 1 LIMIT 20 OFFSET 1000
0.0064 sec/5000 rec, 0.0152 sec/10000 rec,
SELECT * FROM `simplest` WHERE title_en LIKE ‘%ass%’ LIMIT 20
0.0090 sec/5000 rec, 0.0190 sec/10000 rec,
SELECT * FROM `simplest` WHERE title_en LIKE ‘%ass%’ OR title_bg LIKE ‘%ass%’ LIMIT 20

0.0030 sec/5000 rec, 0.0037 sec/10000 rec
SELECT * FROM `lang_tbls` t INNER JOIN `lang_tbls_en` l ON (t.id = l.id) WHERE 1 LIMIT 20 OFFSET 1000
0.0057 sec/5000 rec, 0.0060 sec/10000 rec
SELECT * FROM `lang_tbls` t INNER JOIN `lang_tbls_en` l ON (t.id = l.id) WHERE l.title LIKE ‘%ass%’ LIMIT 10
0.0116 sec/5000 rec, 0.0120 sec/10000 rec
SELECT * FROM `lang_tbls` t INNER JOIN `lang_tbls_en` l1 ON (t.id = l1.id) INNER JOIN `lang_tbls_bg` l2 ON (t.id = l2.id) WHERE 1 LIMIT 20 OFFSET 1000
0.0292 sec/5000 rec, 0.0325 sec/10000 rec
SELECT * FROM `lang_tbls` t INNER JOIN `lang_tbls_en` l1 ON (t.id = l1.id) INNER JOIN `lang_tbls_bg` l2 ON (t.id = l2.id) WHERE l1.title LIKE ‘%ass%’ OR l2.title LIKE ‘%ass%’ LIMIT 10
0.0157 sec/5000 rec, 0.0175 sec/10000 rec
SELECT * FROM `lang_tbls` t LEFT JOIN `lang_tbls_en` l1 ON (t.id = l1.id) LEFT JOIN `lang_tbls_bg` l2 ON (t.id = l2.id) WHERE 1 LIMIT 20 OFFSET 1000
0.0405 sec/5000 rec, 0.0445 sec/10000 rec
SELECT * FROM `lang_tbls` t LEFT JOIN `lang_tbls_en` l1 ON (t.id = l1.id) LEFT JOIN `lang_tbls_bg` l2 ON (t.id = l2.id) WHERE l1.title LIKE ‘%ass%’ OR l2.title LIKE ‘%ass%’ LIMIT 10

0.0098 sec/5000 rec, 0.0099 sec/10000 rec
SELECT * FROM `one_tbl` t INNER JOIN `one_tbl_lang` l ON (t.id = l.id AND l.lang = ‘en’) WHERE 1 LIMIT 20 OFFSET 1000
0.0430 sec/5000 rec, 0.0470 sec/10000 rec
SELECT * FROM `one_tbl` t INNER JOIN `one_tbl_lang` l ON (t.id = l.id AND l.lang = ‘en’) WHERE l.title LIKE ‘%ass%’ LIMIT 10
0.0025 sec/5000 rec, 0.0030 sec/10000 rec
SELECT * FROM `one_tbl` t INNER JOIN `one_tbl_lang` l ON (t.id = l.id AND (l.lang = ‘en’ OR l.lang = ‘bg’)) WHERE 1 LIMIT 20 OFFSET 1000
0.0060 sec/5000 rec, 0.0067 sec/5000 rec
SELECT * FROM `one_tbl` t INNER JOIN `one_tbl_lang` l ON (t.id = l.id AND (l.lang = ‘en’ OR l.lang = ‘bg’)) WHERE l.title LIKE ‘%ass%’ LIMIT 10
0.0170 sec/5000 rec, 0.0184 sec/5000 rec
SELECT * FROM `one_tbl` t LEFT JOIN `one_tbl_lang` l1 ON (t.id = l1.id AND l1.lang = ‘en’) LEFT JOIN `one_tbl_lang` l2 ON (t.id = l2.id AND l2.lang = ‘bg’) WHERE 1 LIMIT 20 OFFSET 1000
0.0457 sec/5000 rec, 0.0485 sec/10000 rec
SELECT * FROM `one_tbl` t LEFT JOIN `one_tbl_lang` l1 ON (t.id = l1.id AND l1.lang = ‘en’) LEFT JOIN `one_tbl_lang` l2 ON (t.id = l2.id AND l2.lang = ‘bg’) WHERE l1.title LIKE ‘%ass%’ OR l2.title LIKE ‘%ass%’ LIMIT 10

Абсолютно ясно е, че първият вариант е най-перформантен. Заявките, които ползвам за другите варианти са повече, защото  има разлика в самите тях в зависимост данните, които ни трябват и начина, по който да ги получим. Та крайния резултат е, че ако искате да наблегнете на перформантността – първият вариант е за вас.

Ok нека минем напред – втория и третия. Третия вариант , както можете да видите е по-добър само в 3та и 4та заявка – даже доста по-добър, но според мен това са заявки, които се използват доста рядко. Могат да бъдат използвани за търсения във всички поддържани езици, което не се предпочита. Във всички останали заявки вторият начин е по-добър. Следователно, ако искате да наблегнете на лесната подръжка  – опция 3 е за вас, но ако искате да останете в златната среда – използвайте второто предложение.

Bookmark It

Прочети статията на: English