MySQL: Vliv pohledů na výkon?

Není to tak dávno, co jsem psal o opomíjených pohledech v MySQL a tvrdil jsem, že mohou ušetřit čas. Jak se zdá, tak ušetří čas leda programátorovi, pokud je použije vhodným způsobem. Reálně mohou pohledy způsobit pokles výkonnosti. Otázka tedy pak tedy zní, kudy z problému ven, jestli přechodnou tabulkou, které se vytvoří pro otevřenou session, nebo ručním napsáním všech podmínek, jimiž jsme vytvořili pohled, a (?menší?) nepohodlí pro programátora.

Ten hlavní problém se schovává v indexech, MySQL neumožňuje vytvoření indexů pro pohled, ale pohled dědí indexy z tabulek, které jsou zdrojem pro pohled. V praxi to znamená, že programátor si vzpomene na vhodnost volby takových indexů, které jsou ve WHERE a ORDER BY části dotazu, jímž je index vytvořený, ale na ty ostatní podmínky zapomene. Protože MySQL používají především vývojáři webových aplikací, kde nejsou miliony vět v jednotlivých tabulkách, není možná výkonostní pokles nijak patrný.

Datová struktura je poměrně triviální (slouží pro jeden miniaturní web s přibližně 1000 přístupy denně):

 

CREATE TABLE `articles` (
	`articles_id` int(10) unsigned NOT NULL auto_increment,
	`content_id` int(10) unsigned NOT NULL,
	`langs_id` int(10) unsigned NOT NULL,
	`title` varchar(200) collate utf8_czech_ci NOT NULL,
	`name` varchar(200) collate utf8_czech_ci NOT NULL,
	`perex` text collate utf8_czech_ci NOT NULL,
	`article` text collate utf8_czech_ci NOT NULL,
	`last_edit` datetime NOT NULL default '0000-00-00 00:00:00',
	`visible` tinyint(1) unsigned NOT NULL default '0',
	PRIMARY KEY  (`articles_id`),
	KEY `content_id` (`content_id`),
	KEY `content_id_2` (`content_id`,`langs_id`,`visible`),
  KEY `last_edit` (`last_edit`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `articles` VALUES(1, 8, 1, 'Giro de Beer 2009', 
	'a1-giro-de-beer-2009', '', '', '2009-08-27 18:13:05', 1);

INSERT INTO `articles` VALUES(3, 10, 1, 'Bouřňák 2009', 'a3-bournak-2009',
	'Chystáme novou cykloakci nazvanou "Bouřňák", propozice viz. dole',
	'Propozice akce Bouřňák', '2009-08-27 18:28:52', 0);

INSERT INTO `articles` VALUES(5, 12, 1, 'Bouřňák 2009', 'a5-bournak-2009',
	'Chystáme novou cykloakci nazvanou "Bouřňák", propozice viz. ''dole''',
	'Propozice akce Bouřňák2009. Když lidé jezdí po sjezdovce na lyžích dolů, 
	proč by to nemohli na kole vyšlápnout nahoru?', '2009-08-27 19:03:59', 1);

CREATE TABLE `content` (
  `content_id` int(10) unsigned NOT NULL auto_increment,
  `sections_id` int(10) unsigned NOT NULL default '0',
  `content_type` enum('articles','news','photos') collate utf8_czech_ci NOT NULL default 'news',
  PRIMARY KEY  (`content_id`),
  KEY `sections_id` (`sections_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=2 ;

INSERT INTO `content` VALUES(1, 9, 'news');

CREATE TABLE `galleries` (
  `galleries_id` int(10) unsigned NOT NULL auto_increment,
  `content_id` int(10) unsigned NOT NULL,
  `langs_id` int(10) unsigned NOT NULL,
  `title` varchar(200) collate utf8_czech_ci NOT NULL,
  `name` varchar(200) collate utf8_czech_ci NOT NULL,
  `perex` text collate utf8_czech_ci NOT NULL,
  `last_edit` datetime NOT NULL default '0000-00-00 00:00:00',
  `visible` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`galleries_id`),
  KEY `content_id` (`content_id`),
  KEY `content_id_2` (`content_id`,`langs_id`,`visible`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;

CREATE TABLE `news` (
  `news_id` int(10) unsigned NOT NULL auto_increment,
  `content_id` int(10) unsigned NOT NULL,
  `langs_id` int(10) unsigned NOT NULL,
  `title` varchar(200) collate utf8_czech_ci NOT NULL,
  `name` varchar(200) collate utf8_czech_ci NOT NULL,
  `perex` text collate utf8_czech_ci NOT NULL,
  `last_edit` datetime NOT NULL default '0000-00-00 00:00:00',
  `visible` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`news_id`),
  KEY `content_id` (`content_id`),
  KEY `content_id_2` (`content_id`,`langs_id`,`visible`),
  KEY `last_edit` (`last_edit`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=2 ;

CREATE TABLE `sections` (
  `sections_id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned NOT NULL default '0',
  `tree_id` int(10) unsigned NOT NULL,
  `lft` int(10) unsigned NOT NULL default '0',
  `rght` int(10) unsigned NOT NULL default '0',
  `langs_id` int(10) unsigned NOT NULL default '0',
  `title` varchar(200) collate utf8_czech_ci NOT NULL,
  `name` varchar(200) collate utf8_czech_ci NOT NULL,
  `keywords` varchar(255) collate utf8_czech_ci NOT NULL,
  `visible` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`sections_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=10 ;

INSERT INTO `sections` VALUES(1, 0, 1, 1, 2, 1, 'První', 'c1-prvni',
	'key, něco', 1);
INSERT INTO `sections` VALUES(5, 0, 5, 1, 4, 1, 'Druhá kapitola',
	'c5-druha-kapitola', 'accomodation, hotel, Chodska, Praha', 1);
INSERT INTO `sections` VALUES(6, 0, 6, 1, 2, 1, 'aslkj', 'c6-aslkj',
	'kljlkjl', 0);
INSERT INTO `sections` VALUES(9, 5, 5, 2, 3, 1, 'třetí pod druhou',
	'c9-treti-pod-druhou', 'A další klíčová slova', 1);

CREATE TABLE `langs` (
  `langs_id` tinyint(2) unsigned NOT NULL auto_increment,
  `shortcut` varchar(3) collate utf8_unicode_ci NOT NULL default '',
  `description` varchar(100) collate utf8_unicode_ci NOT NULL default '',
  `visorder` tinyint(2) NOT NULL default '1',
  `admin_allowed` enum('yes','no') collate utf8_unicode_ci NOT NULL default 'yes',
  `user_allowed` enum('yes','no') collate utf8_unicode_ci NOT NULL default 'yes',
  `user_default` tinyint(1) unsigned NOT NULL default '0',
  `currencies_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`langs_id`),
  KEY `visorder` (`visorder`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

INSERT INTO `langs` VALUES(1, 'cs', 'Česky', 2, 'yes', 'yes', 1, 2);
INSERT INTO `langs` VALUES(2, 'en', 'english', 3, 'no', 'yes', 0, 3);
INSERT INTO `langs` VALUES(3, 'es', 'Espaňol', 1, 'no', 'no', 0, 3);
INSERT INTO `langs` VALUES(4, 'de', 'deutsch', 4, 'no', 'no', 0, 3);
INSERT INTO `langs` VALUES(5, 'it', 'Italiano', 1, 'yes', 'no', 0, 3);

CREATE VIEW `content_all` AS select `t1`.`content_id` AS `content_id`,
	`t1`.`sections_id` AS `sections_id`,`t1`.`content_type` AS `content_type`,
	`t2`.`langs_id` AS `langs_id`,`t2`.`title` AS `title`,`t2`.`name` AS `name`,
	`t2`.`perex` AS `perex`,`t2`.`last_edit` AS `last_edit`,`t2`.`visible` AS
	`visible` from (`articles` `t2` left join `content` `t1` on
	((`t1`.`content_id` = `t2`.`content_id`))) union select `t1`.`content_id` AS 
	`content_id`,`t1`.`sections_id` AS `sections_id`,`t1`.`content_type` AS 
	`content_type`,`t2`.`langs_id` AS `langs_id`,`t2`.`title` AS `title`,
	`t2`.`name` AS `name`,`t2`.`perex` AS `perex`,`t2`.`last_edit` AS `last_edit`,
	`t2`.`visible` AS `visible` from (`galleries` `t2` left join `content` `t1` on
	((`t1`.`content_id` = `t2`.`content_id`))) union select `t1`.`content_id` AS 
	`content_id`,`t1`.`sections_id` AS `sections_id`,`t1`.`content_type` AS 
	`content_type`,`t2`.`langs_id` AS `langs_id`,`t2`.`title` AS `title`,
	`t2`.`name` AS `name`,`t2`.`perex` AS `perex`,`t2`.`last_edit` AS `last_edit`,
	`t2`.`visible` AS `visible` from (`news` `t2` left join `content` `t1` on
	((`t1`.`content_id` = `t2`.`content_id`)));

 

Datová struktura je snad zřejmá a jednoduchá, kdo zapřemýšlí, tak mu dojde, proč jsem zvolil řešení s prostředníkem content, já to vyzrazovat nebudu :-). No a nyní jdeme testovat:

 

select name from articles;
+----------------------+
| name                 |
+----------------------+
| a1-giro-de-beer-2009 | 
| a3-bournak-2009      | 
| a5-bournak-2009      | 
+----------------------+
3 rows in set (0.00 sec)

SELECT `name`, `last_edit` FROM `articles` UNION SELECT `name`, `last_edit`
	FROM `news` ORDER BY `last_edit`;
+----------------------+---------------------+
| name                 | last_edit           |
+----------------------+---------------------+
| n1-prvni             | 2009-08-25 00:00:00 | 
| a1-giro-de-beer-2009 | 2009-08-27 18:13:05 | 
| a3-bournak-2009      | 2009-08-27 18:28:52 | 
| a5-bournak-2009      | 2009-08-27 19:03:59 | 
| n3-giro-di-beer      | 2009-08-27 20:50:01 | 
+----------------------+---------------------+
5 rows in set (0.00 sec)

EXPLAIN SELECT `name`, `last_edit` FROM `articles` UNION SELECT `name`,
	`last_edit` FROM `news` ORDER BY `last_edit`;
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
|  1   | PRIMARY      | articles   | ALL  | NULL          | NULL | NULL    | NULL |    3 |                | 
|  2   | UNION        | news       | ALL  | NULL          | NULL | NULL    | NULL |    2 |                | 
| NULL | UNION RESULT | <union1>           | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using filesort |
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
3 rows in set (0.00 sec)

 

Výběr z jediné tabulky a UNION mezi více tabulkami přinesl, vzhledem k množství vložených dat vcelku očekávatelný výsledek, nulový čas a minimální nároky.

 

SELECT `t1`.`name`, `t1`.`last_edit`, `t2`.`content_type` FROM
	`articles` AS `t1` LEFT JOIN `content` AS `t2` ON
	`t1`.`content_id`=`t2`.`content_id` UNION SELECT `t1`.`name`,
	`t1`.`last_edit`, `t2`.`content_type` FROM `news` AS `t1` LEFT JOIN
	`content` AS `t2` ON `t1`.`content_id`=`t2`.`content_id` ORDER BY `last_edit`;
+----------------------+---------------------+--------------+
| name                 | last_edit           | content_type |
+----------------------+---------------------+--------------+
| n1-prvni             | 2009-08-25 00:00:00 | news         | 
| a1-giro-de-beer-2009 | 2009-08-27 18:13:05 | articles     | 
| a3-bournak-2009      | 2009-08-27 18:28:52 | articles     | 
| a5-bournak-2009      | 2009-08-27 19:03:59 | articles     | 
| n3-giro-di-beer      | 2009-08-27 20:50:01 | news         | 
+----------------------+---------------------+--------------+
5 rows in set (0.00 sec)

select `name`, `last_edit`, `content_type` FROM `content_all` ORDER BY `last_edit`;
+----------------------+---------------------+--------------+
| name                 | last_edit           | content_type |
+----------------------+---------------------+--------------+
| n1-prvni             | 2009-08-25 00:00:00 | news         | 
| a1-giro-de-beer-2009 | 2009-08-27 18:13:05 | articles     | 
| a3-bournak-2009      | 2009-08-27 18:28:52 | articles     | 
| a5-bournak-2009      | 2009-08-27 19:03:59 | articles     | 
| n3-giro-di-beer      | 2009-08-27 20:50:01 | news         | 
+----------------------+---------------------+--------------+
5 rows in set (0.00 sec)

EXPLAIN SELECT `t1`.`name`, `t1`.`last_edit`, `t2`.`content_type` FROM `articles` AS `t1`
  LEFT JOIN `content` AS `t2` ON `t1`.`content_id`=`t2`.`content_id` UNION SELECT `t1`.`name`,
  `t1`.`last_edit`, `t2`.`content_type` FROM `news` AS `t1` LEFT JOIN `content` AS `t2` ON
  `t1`.`content_id`=`t2`.`content_id` UNION SELECT `t1`.`name`, `t1`.`last_edit`,
  `t2`.`content_type` FROM `galleries` AS `t1` LEFT JOIN `content` AS
  `t2` ON `t1`.`content_id`=`t2`.`content_id` ORDER BY `last_edit`;
+------+--------------+--------------+--------+---------------+---------+---------+---------------------+------+---------------------+
| id   | select_type  | table        | type   | possible_keys | key     | key_len | ref                 | rows | Extra               |
+------+--------------+--------------+--------+---------------+---------+---------+---------------------+------+---------------------+
|  1   | PRIMARY      | t1           | ALL    | NULL          | NULL    | NULL    | NULL                |    3 |                     | 
|  1   | PRIMARY      | t2           | eq_ref | PRIMARY       | PRIMARY | 4       | views.t1.content_id |    1 |                     | 
|  2   | UNION        | t1           | ALL    | NULL          | NULL    | NULL    | NULL                |    2 |                     | 
|  2   | UNION        | t2           | eq_ref | PRIMARY       | PRIMARY | 4       | views.t1.content_id |    1 |                     | 
|  3   | UNION        | t1           | system | NULL          | NULL    | NULL    | NULL                |    0 | const row not found | 
|  3   | UNION        | t2           | eq_ref | PRIMARY       | PRIMARY | 4       | const               |    1 |                     | 
| NULL | UNION RESULT | <union1>     | ALL    | NULL          | NULL    | NULL    | NULL                | NULL | Using filesort      |
+------+--------------+--------------+--------+---------------+---------+---------+---------------------+------+---------------------+
7 rows in set (0.00 sec)

EXPLAIN select `name`, `last_edit`, `content_type` FROM `content_all` ORDER BY `last_edit`

+------+--------------+--------------+--------+---------------+---------+---------+---------------------+------+--------------------------------+
| id   | select_type  | table        | type   | possible_keys | key     | key_len | ref                 | rows | Extra                          |
+------+--------------+--------------+--------+---------------+---------+---------+---------------------+------+--------------------------------+
|  1   | PRIMARY      | <derived2>   | ALL    | NULL          | NULL    | NULL    | NULL                |    5 | Using filesort                 |
|  2   | DERIVED      | t2           | ALL    | NULL          | NULL    | NULL    | NULL                |    3 |                                |
|  2   | DERIVED      | t1           | eq_ref | PRIMARY       | PRIMARY | 4       | views.t2.content_id |    1 |                                |
|  3   | UNION        | NULL         | NULL   | NULL          | NULL    | NULL    | NULL                | NULL | no matching row in const table |
|  4   | UNION        | t2           | ALL    | NULL          | NULL    | NULL    | NULL                |    2 |                                |
|  4   | UNION        | t1           | eq_ref | PRIMARY       | PRIMARY | 4       | views.t2.content_id |    1 |                                |
| NULL | UNION RESULT | <union2>     | ALL    | NULL          | NULL    | NULL    | NULL                | NULL |                                |
+------+--------------+--------------+--------+---------------+---------+---------+---------------------+------+--------------------------------+
7 rows in set (0.01 sec)

 

Teoreticky se časy nijak neliší, malinko více ukázal EXPLAIN a tím zajímavým výsledkem není celkový čas na zpracování (tj. číslo za „7 rows in set“), ale počet procházených řádek jednotlivých kroků při SELECTu z pohledu content_all, v tomto konkrétním případě jich bylo o 50% více.

Závěrem

Pohledy šetří čas, ale spíše čas vývojáře, v praxi se projeví malinkým nárůstem zatížení na server. Pravdou je, že já používám mírně upravenou hierarchickou strukturu dat pro kategorie a neprocházím cyklicky tabulky jen pro sestavení stromové struktury dat, ale mám vytažení celé struktury vyřešené jediným trochu komplikovanějším dotazem. Představa kombinování hierarchické struktury a JOINování na další tabulky s popisy, závisejícími na id jazyka mě absolutně neláká.

Pokud je nutné opravdu detailní ladění výkonu, bylo by nejspíše potřeba vše JOINovat pro každý dotaz a pohled zcela vynechat, ale mám pocit, že to je problém, který nastane až nekdy při stovkách/desítkách tisících návštěvníků denně, pokud vůbec nastane. Ani použití přechodných tabulek není řešením, minimálně MySQL by je neměla umět indexovat (pardon, nekoukal jsem) a myslím, že konkurenční servery na tom nebudou o moc lépe.

Jednoznačné řešení nikomu nenabídnu, sepisuji tu jen to, nač jsem přišel náhodou. Databázařina je opravdu zajímavější, než celý zbytek programování .

13 komentářů u „MySQL: Vliv pohledů na výkon?“

  1. Dobry den,
    tak nevim. Na zacatku tvrdite, ze v pohledu se pracuje s indexy definovane nad tabulkou; a pak tedy nechapu, proc by mel byt pohled vetsi naroky na vykon. Vzdyt staci vytvorit takove indexy, aby je bylo mozne vyuzit i v pohledu a je po problemech.
    Btw. merit cas na setiny sekundy je v oblasti IT tak trochu sarlatanstvi.

    1. eMu: Nelovím setiny sekundy, důležitější jsou čísla, která ukázal Explain. Dokonce jsem snad udělal stejný příkaz, jakým je vytvořený pohled, tak mám pocit, že dokumentace říká něco o indexech kolem vytvoření pohledů a praxe je trochu jinde, alespoň u MySQL. Což mi říká, že bych měl ještě testnout PostgreSQL a FirebirsSQL.

  2. Nerad bych tady mystifikoval, ale mám pocit, že původní účel pohledů je odstínění programátora od vlastní struktůry a tedy i definice k jakým datům ho pustíte. Programátor pak dostal jen data co vypsal pohled a databázový admin věděl, že se k jinejm dostat nemůže.

    Databáze mají k dispozici takzvané materializované pohledy, kdy se prostě z definice vytvoří tabulka, která je jednou za čas obnovena (znova se provede select insert). Nevím jak jsou na tom ostatní DB, ale první kdo to zavedl byl Oracle. Tam tato možnost je. Viz http://en.wikipedia.org/wiki/Materialized_view

    1. JK: Pohledy se dají využít na leccos, možná uvedený příklad není úplně nejšťastnější, ale představa řešení stromové struktury a ještě přijoinování dalších tabulek mi přijde dost nepřehledná. Píšu to z pohledu programátora „vlka samotáře“, který si dělá jak definici databáze, tak logiku programu. Původně jsem si chtěl pohledem ušetřit práci.
      Materializované pohledy, vím o nich, ale zatím také vím, že je umí snad jen opravdu drahá řešení. Bohužel nic pro běžného webaře a pro menší firmu.

  3. Dobrý 🙂

    S pohledy je to o něco složitější – hodně záleží na způsobu implementace – a na výkonu hw. Možná v dřevních dobách – cca 10 a více let zpět se významně projevovala rychlost generování plánu na rychlosti generování dotazu. V některých databázích se pro pohledy používali prepared statements a tudíž použití pohledu mohlo dotaz urychlit. Mám pocit, že od toho se již opouští. Omezuje se tím planer – pokud dotaz obsahuje odkaz na jiný prováděcí plán, pak zvolený plán je v podstatě představuje optimum pro celkový dotaz a optimum pro každý pohled. Analogie s matematikou – máme dvě lokální minima – což nemusí být globální minimum.

    V případě MySQL jsou pohledy řešeny skrze derivované tabulky – a ty jsou až do verze 5.4 mizerně optimalizovány. Takže riskujeme neoptimální prováděcí plán. V 5.4 by tento problém se neměl vyskytovat.

    V případě PostgreSQL jsou pohledy něco tak normálního a běžného, že se neřeší. Zase v případě některých expertů to může znamenat, že používáním pohledů ve výsledku generují extrémní dotazy – což databázi také nedělá dobře. Rozumné použití pohledů je nutnost – ať už kvůli zabezpečení (skrze pohledy můžeme filtrovat přístup k datum) nebo kvůli denormalizaci schématu a zpřístupnění uložených dat uživatelům.

  4. Sakra. 🙂 Omlouvám se, nechtěl jsem aby to vyznělo jako poučování, ale spíše doplnění článku.

    Já pohledy také používal stejným způsobem jako vy. Zjednodušší to práci a ubyde chyb a jak se říká hardware je levný ale práce programátora drahá 🙂

  5. Přehledněji ten pohled vypadá takto: http://www.clipboard.cz/5tv.

    Dotaz bude pomalý, protože se v něm používá union, který není přátelský k indexům. Navíc v něm nemusí být použitý left join protože content_id je stejně not null. Dále ISA potomci tabulky content mají stejné sloupce langs_id, title, name, perex, last_edit, visible které se vyskytují i v pohledu. Proč je nepřidat do content? Pak by tento konkrétní pohled vůbec nebyl potřeba. Ale pravda je taková že neznám další použití a tohle by mohlo zkomplikovat jiné dotazy.
    Databázařina je opravdu zajímavější, než celý zbytek programování!

  6. mám rád views, protože mi šetří čas i nervy. A čas vývojáře je dražší než mašiny 😉

  7. Já sám mám raději temporary table než pohledy. Mám to pěkně v jednom skriptu a jsem na to zvyklý. Pochopitelně nejsem odhadnout, co je rychlejší vzhledem k tomu, že databáze není moc zatížená.

    1. Dalibor: Tady bych dost přemýšlel. U klasické aplikace, která má připojení otevřené delší dobu budou temporary tables rozumné (jen myslím nejsou indexovat), ale třeba webařina (kde je MySQL nejpoužívanější) je trochu jinde, trvalá connection asi moc nehrozí, takže se budou pokaždé vytvářet.

  8. Marek: Jasně, pochopil jsem to .. jestliže se tisíckrát denně otevře příslušná aplikace tak vytváření tabulky při každém spuštění skriptu je jaksi mimo mísu … 🙂
    V mém případě je trochu jiná situace.

Komentáře jsou uzavřeny.