{"id":1370,"date":"2013-01-30T10:55:52","date_gmt":"2013-01-30T09:55:52","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1370"},"modified":"2013-01-30T10:55:52","modified_gmt":"2013-01-30T09:55:52","slug":"full-table-scan-vs-full-index-scan-part2-2","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2013\/01\/30\/full-table-scan-vs-full-index-scan-part2-2\/","title":{"rendered":"Full table scan vs Full index scan part2-2"},"content":{"rendered":"

2\/ FTS ou FIS<\/h1>\n

(Aller \u00e0 la 1\u00e8re partie<\/a>)<\/em>
\nAvant de r\u00e9pondre explicitement \u00e0 la question, un petit zoom sur l\u2019une des nombreuses nouveaut\u00e9s de MySQL 5.6. La commande EXPLAIN<\/strong> s\u2019est enrichie de la clause format=json<\/strong>. Elle permet d\u2019avoir une version un peu plus d\u00e9taill\u00e9e que l\u2019EXPLAIN classique.<\/p>\n

Query 1\/ EXPLAIN format=json<\/strong> \u00a0SELECT d,avg(price) FROM bills GROUP BY d\\G
\n*************************** 1. row ***************************
\nEXPLAIN: {
\n\u00ab\u00a0query_block\u00a0\u00bb: {
\n\u00ab\u00a0select_id\u00a0\u00bb: 1,
\n\u00ab\u00a0grouping_operation\u00a0\u00bb: {
\n\u00ab\u00a0using_filesort\u00a0\u00bb: false,
\n\u00ab\u00a0table\u00a0\u00bb: {
\n\u00ab\u00a0table_name\u00a0\u00bb: \u00ab\u00a0bills\u00a0\u00bb,
\n\u00ab\u00a0access_type\u00a0\u00bb: \u00ab\u00a0index\u00a0\u00bb,
\n\u00ab\u00a0key\u00a0\u00bb: \u00ab\u00a0d\u00a0\u00bb,
\n\u00ab\u00a0used_key_parts\u00a0\u00bb: [
\n\u00ab\u00a0d\u00a0\u00bb
\n],
\n\u00ab\u00a0key_length\u00a0\u00bb: \u00ab\u00a05\u00a0\u00bb,
\n\u00ab\u00a0rows\u00a0\u00bb: 176268,
\n\u00ab\u00a0filtered\u00a0\u00bb: 100
\n}
\n}
\n}
\n}<\/p>\n

Query 4\/ EXPLAIN format=json<\/strong> \u00a0SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d\\G
\n*************************** 1. row ***************************
\nEXPLAIN: {
\n\u00ab\u00a0query_block\u00a0\u00bb: {
\n\u00ab\u00a0select_id\u00a0\u00bb: 1,
\n\u00ab\u00a0grouping_operation\u00a0\u00bb: {
\n\u00ab\u00a0using_temporary_table\u00a0\u00bb: true,
\n\u00ab\u00a0using_filesort\u00a0\u00bb: true,
\n\u00ab\u00a0table\u00a0\u00bb: {
\n\u00ab\u00a0table_name\u00a0\u00bb: \u00ab\u00a0bills\u00a0\u00bb,
\n\u00ab\u00a0access_type\u00a0\u00bb: \u00ab\u00a0ALL\u00a0\u00bb,
\n\u00ab\u00a0rows\u00a0\u00bb: 176268,
\n\u00ab\u00a0filtered\u00a0\u00bb: 100
\n}
\n}
\n}
\n}<\/p>\n

Sans surprise, on retrouve les informations de la version classique de l\u2019EXPLAIN, a savoir:
\nFIS d\u2019un cot\u00e9 (choix de l\u2019optimiseur) contre FTS + cr\u00e9ation d\u2019une table temporaire + tri de l\u2019autre. N\u00e9anmoins, pour certains types de requ\u00eates, les informations g\u00e9n\u00e9r\u00e9es par la clause format=json peuvent \u00eatre plus pertinentes.<\/p>\n

Comme nous l\u2019avons \u00e9galement vu dans la premi\u00e8re partie de cet article<\/a>, le GROUP BY de MySQL g\u00e9n\u00e8re syst\u00e9matiquement un tri (\u00ab\u00a0using_filesort\u00a0\u00bb: true,). Ce tri n\u2019\u00e9tant pas toujours n\u00e9cessaire, il y a une astuce pour ne pas le g\u00e9n\u00e9rer. Ajouter la clause ORDER BY NULL<\/strong><\/p>\n

Query 5\/ EXPLAIN format=json \u00a0SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d ORDER BY NULL<\/strong>\\G
\n*************************** 1. row ***************************
\nEXPLAIN: {
\n\u00ab\u00a0query_block\u00a0\u00bb: {
\n\u00ab\u00a0select_id\u00a0\u00bb: 1,
\n\u00ab\u00a0ordering_operation\u00a0\u00bb: {
\n \u00a0\u00a0\u00a0\u00a0 \u00ab\u00a0using_filesort\u00a0\u00bb: false,<\/strong>
\n\u00ab\u00a0grouping_operation\u00a0\u00bb: {
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00ab\u00a0using_temporary_table\u00a0\u00bb: true,<\/strong>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00ab\u00a0using_filesort\u00a0\u00bb: false,<\/strong>
\n\u00ab\u00a0table\u00a0\u00bb: {
\n\u00ab\u00a0table_name\u00a0\u00bb: \u00ab\u00a0bills\u00a0\u00bb,
\n\u00ab\u00a0access_type\u00a0\u00bb: \u00ab\u00a0ALL\u00a0\u00bb,
\n\u00ab\u00a0rows\u00a0\u00bb: 176268,
\n\u00ab\u00a0filtered\u00a0\u00bb: 100
\n}
\n}
\n}
\n}
\n}<\/p>\n

Query 5\/ EXPLAIN \u00a0SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d ORDER BY NULL<\/strong>\\G
\n*************************** 1. row ***************************
\nid: 1
\nselect_type: SIMPLE
\ntable: bills
\ntype: ALL
\npossible_keys: NULL
\nkey: NULL
\nkey_len: NULL
\nref: NULL
\nrows: 176268
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using temporary<\/strong><\/p>\n

SHOW STATUS LIKE ‘Last_query_cost’;
\n+—————–+————–+
\n| Variable_name \u00a0\u00a0| Value \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n+—————–+————–+
\n| Last_query_cost | 35861.599000 |
\n+—————–+————–+<\/p>\n

Il y a une incidence notable sur le co\u00fbt de la requ\u00eate, 35861.599<\/strong> au lieu de 212129.599<\/strong>.
\nPas vraiment surprenant !<\/p>\n

Niveau performance \u00e7a donne quoi ?<\/p>\n

Quick bench (au meilleur des 4 tests ie 4 fois 100 runs):<\/h3>\n

Server version: \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a05.6.9<\/strong>-rc MySQL Community Server (GPL)<\/p>\n

Taille: environs 16 Mo<\/strong> ((Donn\u00e9es + Index))<\/p>\n

Configuration de l\u2019instance:
\nInnodb buffer pool size = 32 Go<\/strong>
\nmax_heap_table_size = tmp_table_size = 512 Mo<\/strong><\/p>\n

Query 1:
\ndaz@mysql:~\/mysql_5_6_5$ .\/mysqlslap –create-schema=test -c1 -i100 -q\u00a0\u00bbSELECT avg(price) FROM test.bills \u00a0GROUP BY d ;\u00a0\u00bb
\nBenchmark
\nAverage number of seconds to run all queries: 0.203 seconds
\n \u00a0\u00a0\u00a0Minimum number of seconds to run all queries: 0.199 seconds<\/strong>
\nMaximum number of seconds to run all queries: 0.224 seconds<\/p>\n

Query 2:
\ndaz@mysql:~\/mysql_5_6_5$ .\/mysqlslap –create-schema=test -c1 -i100 -q\u00a0\u00bbSELECT avg(price) FROM test.bills USE INDEX(d) GROUP BY d ;\u00a0\u00bb
\nBenchmark
\nAverage number of seconds to run all queries: 0.206 seconds
\n \u00a0\u00a0\u00a0Minimum number of seconds to run all queries: 0.198 seconds<\/strong>
\nMaximum number of seconds to run all queries: 0.217 seconds<\/p>\n

Query 3:
\ndaz@mysql:~\/mysql_5_6_5$ .\/mysqlslap –create-schema=test -c1 -i100 -q\u00a0\u00bbSELECT avg(price) FROM test.bills FORCE INDEX(d) GROUP BY d ;\u00a0\u00bb
\nBenchmark
\nAverage number of seconds to run all queries: 0.203 seconds
\n \u00a0\u00a0\u00a0Minimum number of seconds to run all queries: 0.199 seconds<\/strong>
\nMaximum number of seconds to run all queries: 0.219 seconds<\/p>\n

Le temps d\u2019ex\u00e9cution des requ\u00eates 1, 2 et 3 est le m\u00eame ce qui est logique car le plan d\u2019ex\u00e9cution est le m\u00eame, en d\u2019autres termes, forcer l\u2019utilisation de l\u2019index ou non n\u2019a pas d\u2019incidence sur la dur\u00e9e des requ\u00eates.
\nDe plus ce r\u00e9sultat confirme que le co\u00fbt bizarre du FORCE INDEX est bien un bug (cf la premi\u00e8re partie de cet article<\/a>).<\/p>\n

Query 4:
\ndaz@mysql:~\/mysql_5_6_5$ .\/mysqlslap –create-schema=test -c1 -i100 -q\u00a0\u00bbSELECT avg(price) FROM test.bills IGNORE INDEX(d) GROUP BY d ;\u00a0\u00bb
\nBenchmark
\nAverage number of seconds to run all queries: 0.168 seconds
\n \u00a0\u00a0\u00a0Minimum number of seconds to run all queries: 0.166 seconds<\/strong>
\nMaximum number of seconds to run all queries: 0.172 seconds<\/p>\n

Plusieurs indications:<\/p>\n