Movatterモバイル変換

[0]ホーム
URL:

PDF, PPTX13,846 views

Sql query patterns, optimized

This document discusses various techniques for optimizing MySQL queries, including queries for exclusion joins, random selection, and greatest per group. For a query seeking movies without directors, solutions using NOT EXISTS, NOT IN, and outer joins are examined. The outer join solution performed best by taking advantage of a "not exists" optimization. For random selection of a movie, an initial naive solution using ORDER BY RAND() is shown to be inefficient, prompting discussion of alternative approaches.

Embed presentation

Download as PDF, PPTX
MySQL Query Patterns, Optimized © 2014 PERCONA Bill Karwin, Percona
© 2014 PERCONA Welcome Everybody! • Bill Karwin – Senior Knowledge Manager in Percona Support – Joined Percona in 2010 • Percona offers MySQL services – Consulting – Support for MySQL and variants – Remote DBA – Development – Training 2
© 2014 PERCONA How Do We Optimize? • Identify queries. • Measure optimization plan and performance. – EXPLAIN – SHOW SESSION STATUS – SHOW PROFILES • Add indexes and/or redesign the query.
© 2014 PERCONA Testing Environment • MySQL 5.7.5-m15 • CentOS 6.5 running under VirtualBox on my Macbook Pro (non-SSD) • MySQL Workbench 6.2
© 2014 PERCONA Example Database cast_info name char_name kind_type >tle role_type
© 2014 PERCONA Common Query Patterns 1. Exclusion Joins 2. Random Selection 3. Greatest per Group 4. Dynamic Pivot 5. Relational Division
Query Patterns EXCLUSION JOINS © 2014 PERCONA
© 2014 PERCONA Assignment: “I want to find recent movies that had no director.”
© 2014 PERCONA Not Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8 /* director */! );! Movies In the range of recent years Correlated subquery to find a director for each movie
© 2014 PERCONA Not Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8! );! ???s I gave up after waiting > 1 hour
EXPLAIN: the Not-Exists Solution id select_type table type key ref rows Extra 1 PRIMARY t ALL NULL NULL 1598319 Using where 2 DEPENDENT SUBQUERY © 2014 PERCONA c ALL NULL NULL 24149504 Using where The correlated subquery is executed 1.6 M times! Both tables are table scans And scans 24 M rows each time, totalling 3.8 × 1013 row comparisons Dependent subquery executes once for each set of values in outer
Indexes: the Not-Exists Solution CREATE INDEX k_py ! ON title (kind_id, production_year);! ! CREATE INDEX m_r! ON cast_info (movie_id, role_id);! © 2014 PERCONA
EXPLAIN: the Not-Exists Solution id select_type table type key ref rows Extra 1 PRIMARY t range k_py NULL 189846 Using index © 2014 PERCONA condi>on; Using where 2 DEPENDENT SUBQUERY c ref m_r t.id, const 3 Using index The correlated subquery is executed 189k times! At least both table references use indexes A covering index is best—if the index fits in memory Dependent subquery executes once for each set of values in outer
Visual Explain in Workbench © 2014 PERCONA
© 2014 PERCONA Not Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8! );! 5.34s Better, but when the indexes aren’t in memory, it’s still too slow
© 2014 PERCONA Buffer Pool • It’s crucial that queries read an index from memory; I/O during an index scan kills performance. [mysqld]! innodb_buffer_pool_size = 64M # wrong! innodb_buffer_pool_size = 2G # better!
© 2014 PERCONA Not Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8! );! 3.25s much faster after increasing size of buffer pool
© 2014 PERCONA SHOW SESSION STATUS • Shows the real count of row accesses for your current session. mysql> FLUSH STATUS;! mysql> ... run a query ...! mysql> SHOW SESSION STATUS LIKE 'Handler%';!
Status: the Not-Exists Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 186489 |! | Handler_read_last | 0 |! | Handler_read_next | 93244 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 93244 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+! © 2014 PERCONA read_key: lookup by index, e.g. each lookup in cast_info, plus the first row in title read_next: advancing in index order, e.g. the range query for rows in title after the first row read_rnd: fetch a row from a table based on a row reference (probably by MRR)
© 2014 PERCONA SHOW PROFILE • Enable query profiler for the current session. mysql> SET PROFILING = 1;! • Run a query. mysql> SELECT t.title FROM title t ...! • Query the real execution time. mysql> SHOW PROFILES;! • Query detail for a specific query. mysql> SHOW PROFILE FOR QUERY 1;!
Profile: the Not-Exists Solution +----------------+----------+! | Status | Duration |! +----------------+----------+! | Sending data | 0.000029 |! | executing | 0.000004 |! | Sending data | 0.000075 |! . . .! | executing | 0.000004 |! | Sending data | 0.000023 |! | executing | 0.000004 |! | Sending data | 0.000023 |! | executing | 0.000004 |! | Sending data | 0.000081 |! | executing | 0.000009 |! | Sending data | 0.000029 |! | end | 0.000007 |! | query end | 0.000022 |! | closing tables | 0.000028 |! | freeing items | 0.000344 |! | cleaning up | 0.000025 |! +----------------+----------+! © 2014 PERCONA Thousands of iterations of correlated subqueries caused the profile information to overflow!
© 2014 PERCONA Not-In Solution SELECT title! FROM title! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND id NOT IN (! SELECT movie_id FROM cast_info! WHERE role_id = 8! );! 1.62s Not a correlated subquery
Indexes: the Not-In Solution CREATE INDEX k_py ! ON title (kind_id, production_year);! ! CREATE INDEX m_r! ON cast_info (movie_id, role_id);! © 2014 PERCONA
EXPLAIN: the Not-In Solution id select_type table type key ref rows Extra 1 PRIMARY >tle range k_py NULL 189846 Using index © 2014 PERCONA condi>on; Using where; Using MRR 1 DEPENDENT SUBQUERY cast_ info index_subquery m_r func, const 1 Using index; Using where But somehow MySQL doesn’t report a different select type picks only 1 row per subquery execution
© 2014 PERCONA Visual Explain
© 2014 PERCONA Status: the Not-In Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 186489 |! | Handler_read_last | 0 |! | Handler_read_next | 93244 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 93244 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+!
© 2014 PERCONA Profile: the Not-In Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000128 |! | checking permissions | 0.000014 |! | checking permissions | 0.000012 |! | Opening tables | 0.000043 |! | init | 0.000059 |! | System lock | 0.000030 |! | optimizing | 0.000022 |! | statistics | 0.000127 |! | preparing | 0.000036 |! | optimizing | 0.000013 |! | statistics | 0.000028 |! | preparing | 0.000017 |! | executing | 0.000008 |! | Sending data | 1.623482 |! | end | 0.000024 |! | query end | 0.000025 |! | closing tables | 0.000020 |! | freeing items | 0.000329 |! | cleaning up | 0.000029 |! +----------------------+----------+! Most of the time spent in “Sending data” (moving rows around)
© 2014 PERCONA Outer-Join Solution SELECT t.title! FROM title t! LEFT OUTER JOIN cast_info c ! ON t.id = c.movie_id ! AND c.role_id = 8! WHERE t.kind_id = 1 ! AND t.production_year >= 2005 ! AND c.movie_id IS NULL;! 1.58s Try to find a director for each movie using a join If no director is found, that’s the one we want
Indexes: the Outer-Join Solution CREATE INDEX k_py ! ON title (kind_id, production_year);! ! CREATE INDEX m_r! ON cast_info (movie_id, role_id);! © 2014 PERCONA
EXPLAIN: the Outer-Join Solution id select_type table type key ref rows Extra 1 SIMPLE t range k_py NULL 189846 Using index © 2014 PERCONA condi>on 1 SIMPLE c ref m_r t.id, const 1 Using where; Using index; Not exists Special “not exists” optimization
© 2014 PERCONA Visual Explain
Status: the Outer-Join Solution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 93245 |! | Handler_read_last | 0 |! | Handler_read_next | 93244 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-------+! © 2014 PERCONA Fewest row reads
Profile: the Outer-Join Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000161 |! | checking permissions | 0.000014 |! | checking permissions | 0.000013 |! | Opening tables | 0.000051 |! | init | 0.000056 |! | System lock | 0.000040 |! | optimizing | 0.000000 |! | statistics | 0.000263 |! | preparing | 0.000064 |! | executing | 0.000012 |! | Sending data | 1.581615 |! | end | 0.000021 |! | query end | 0.000022 |! | closing tables | 0.000016 |! | freeing items | 0.000324 |! | cleaning up | 0.000026 |! +----------------------+----------+! © 2014 PERCONA
© 2014 PERCONA Summary: Exclusion Joins Solu7on Time Notes Not-­‐Exists 3.25s dependent subquery Not-­‐In 1.62s dependent subquery Outer-­‐Join 1.58s “not exists” op>miza>on
Query Patterns RANDOM SELECTION © 2014 PERCONA
© 2014 PERCONA Assignment: “I want a query that picks a random movie.”
© 2014 PERCONA Naïve Order-By Solution SELECT *! FROM title! WHERE kind_id = 1 /* movie */! ORDER BY RAND()! LIMIT 1;! 0.98s
EXPLAIN: the Order-By Solution id select_type table type key ref rows Extra 1 SIMPLE >tle ref k_py const 790882 Using temporary; © 2014 PERCONA Using filesort
© 2014 PERCONA Visual Explain
Status: the Order-By Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 2 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 2 |! | Handler_read_last | 0 |! | Handler_read_next | 947164 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 2 |! | Handler_read_rnd_next | 947166 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 947164 |! +----------------------------+--------+! © 2014 PERCONA What is this?
Profile: the Order-By Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000074 |! | checking permissions | 0.000032 |! | Opening tables | 0.000035 |! | System lock | 0.000012 |! | init | 0.000025 |! | optimizing | 0.000004 |! | statistics | 0.000014 |! | preparing | 0.000010 |! | Creating tmp table | 0.000245 |! | executing | 0.000003 |! | Copying to tmp table | 4.875666 |! | Sorting result | 3.871513 |! | Sending data | 0.000059 |! | end | 0.000005 |! | removing tmp table | 0.058239 |! | end | 0.000018 |! © 2014 PERCONA | query end | 0.000064 |! | closing tables | 0.000034 |! | freeing items | 0.000210 |! | logging slow query | 0.000003 |! | cleaning up | 0.000005 |! +----------------------+----------+! MySQL 5.5 saves to a temp table, then sorts by filesort.
Profile: the Order-By Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000111 |! | checking permissions | 0.000015 |! | Opening tables | 0.000030 |! | init | 0.000045 |! | System lock | 0.000019 |! | optimizing | 0.000013 |! | statistics | 0.000106 |! | preparing | 0.000019 |! | Creating tmp table | 0.000050 |! | Sorting result | 0.000007 |! | executing | 0.000005 |! | Sending data | 0.836566 |! | Creating sort index | 0.145061 |! | end | 0.000018 |! | query end | 0.000021 |! | removing tmp table | 0.002427 |! © 2014 PERCONA | query end | 0.000516 |! | closing tables | 0.000037 |! | freeing items | 0.000234 |! | cleaning up | 0.000023 |! +----------------------+----------+! MySQL 5.7 creates a sort index for the temp table on the fly
© 2014 PERCONA Offset Solution SELECT ROUND(RAND() * COUNT(*)) ! FROM title! WHERE kind_id = 1;! ! SELECT *! FROM title! WHERE kind_id = 1! LIMIT 1 OFFSET $random;! 0.45s
© 2014 PERCONA Indexes: the Offset Solution CREATE INDEX k! ON title (kind_id);!
EXPLAIN: the Offset Solution id select_type table type key ref rows Extra 1 SIMPLE >tle ref k const 787992 © 2014 PERCONA
© 2014 PERCONA Visual Explain
© 2014 PERCONA Status: the Offset Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 2 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 1 |! | Handler_read_last | 0 |! | Handler_read_next | 473582 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+! Query must read OFFSET + COUNT rows. A high random value makes the query take longer.
© 2014 PERCONA Profile: the Offset Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000099 |! | checking permissions | 0.000012 |! | Opening tables | 0.000034 |! | init | 0.000045 |! | System lock | 0.000019 |! | optimizing | 0.000013 |! | statistics | 0.000114 |! | preparing | 0.000021 |! | executing | 0.000005 |! | Sending data | 0.459230 |! | end | 0.000018 |! | query end | 0.000018 |! | closing tables | 0.000017 |! | freeing items | 0.000194 |! | cleaning up | 0.000025 |! +----------------------+----------+! Many rows moving from storage layer to SQL layer, only to be discarded.
© 2014 PERCONA Primary Key Solution SELECT ROUND(RAND() * MAX(id)) ! FROM title! WHERE kind_id = 1;! ! SELECT *! FROM title! WHERE kind_id = 1 AND id > $random! LIMIT 1;! 0.0008s
EXPLAIN: the Primary Key Solution id select_type table type key ref rows Extra 1 SIMPLE >tle ref k NULL 268254 Using index © 2014 PERCONA condi>on Strange that the optimizer estimates this many rows
© 2014 PERCONA Visual Explain
Status: the Primary Key Solution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 2 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 1 |! | Handler_read_last | 0 |! | Handler_read_next | 0 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-------+! © 2014 PERCONA Just one row read after the index lookup.
Profile: the Primary Key Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000119 |! | checking permissions | 0.000016 |! | Opening tables | 0.000036 |! | init | 0.000053 |! | System lock | 0.000027 |! | optimizing | 0.000021 |! | statistics | 0.000180 |! | preparing | 0.000112 |! | executing | 0.000016 |! | Sending data | 0.000175 |! | end | 0.000010 |! | query end | 0.000017 |! | closing tables | 0.000015 |! | freeing items | 0.000028 |! | cleaning up | 0.000028 |! +----------------------+----------+! © 2014 PERCONA Everything is fast when we search a index by value rather than by position.
Summary: Random Selection Solu7on Time Notes Order-­‐By Solu>on 0.98s Offset Solu>on 0.45s Requires the COUNT() Primary Key Solu>on 0.0008s Requires the MAX() © 2014 PERCONA
Query Patterns GREATEST PER GROUP © 2014 PERCONA
© 2014 PERCONA Assignment: “I want the last episode of every TV series.”
© 2014 PERCONA Getting the Last Episode This is not the title of the last episode! SELECT tv.title, ep.title, MAX(ep.episode_nr) AS last_ep ! FROM title ep! JOIN title tv ON tv.id = ep.episode_of_id! WHERE ep.kind_id = 7 /* TV show */! GROUP BY ep.episode_of_id ORDER BY NULL;!
© 2014 PERCONA Why Isn’t It? • The query doesn’t necessarily return the title from the row where MAX(ep.episode_nr) occurs. • Should the following return the title of the first episode or the last episode? SELECT tv.title, ep.title, MIN(ep.episode_nr) AS first_ep ! MAX(ep.episode_nr) AS last_ep ! FROM . . .!
© 2014 PERCONA Exclusion Join Solution SELECT tv.title, ep1.title, ep1.episode_nr! FROM title ep1! LEFT OUTER JOIN title ep2 ! ON ep1.kind_id = ep2.kind_id! AND ep1.episode_of_id = ep2.episode_of_id! AND ep1.episode_nr < ep2.episode_nr! JOIN title tv ON tv.id = ep1.episode_of_id! WHERE ep1.kind_id = 7 ! AND ep1.episode_of_id IS NOT NULL! AND ep1.episode_nr >= 1! AND ep2.episode_of_id IS NULL;! 71.20 Try to find a row ep2 for the same show with a greater episode_nr If no such row is found, then ep1 must be the last episode for the show
Indexes: the Exclusion-Join Solution CREATE INDEX k_ep_nr ! ON title (kind_id, episode_of_id, episode_nr);! © 2014 PERCONA
EXPLAIN: the Exclusion-Join Solution id select_type table type key ref rows Extra 1 SIMPLE ep1 ref k_py const 787992 Using where 1 SIMPLE tv eq_ref PRIMARY ep1.episode_of_id 1 1 SIMPLE ep2 ref k_ep_nr const, © 2014 PERCONA ep1.episode_of_id 22 Using where; Using index
© 2014 PERCONA Visual Explain
Status: the Exclusion-Join Solution +----------------------------+-----------+! | Variable_name | Value |! +----------------------------+-----------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 693046 |! | Handler_read_last | 0 |! | Handler_read_next | 254373071 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-----------+! © 2014 PERCONA Unfortunately, this seems to be O(n2)
Profile: the Exclusion-Join Solution +----------------------+-----------+! | Status | Duration |! +----------------------+-----------+! | starting | 0.000147 |! | checking permissions | 0.000009 |! | checking permissions | 0.000004 |! | checking permissions | 0.000009 |! | Opening tables | 0.000038 |! | init | 0.000049 |! | System lock | 0.000032 |! | optimizing | 0.000025 |! | statistics | 0.000195 |! | preparing | 0.000045 |! | executing | 0.000006 |! | Sending data | 71.195693 |! | end | 0.000021 |! | query end | 0.000021 |! | closing tables | 0.000017 |! | freeing items | 0.000864 |! | logging slow query | 0.000135 |! | cleaning up | 0.000027 |! +----------------------+-----------+! © 2014 PERCONA A lot of time is spent moving rows around
© 2014 PERCONA Derived-Table Solution SELECT tv.title, ep.title, ep.episode_nr! FROM (! SELECT kind_id, episode_of_id, ! MAX(episode_nr) AS episode_nr! FROM title! WHERE kind_id = 7! GROUP BY kind_id, episode_of_id! ) maxep! JOIN title ep USING (kind_id, episode_of_id, episode_nr)! JOIN title tv ON tv.id = ep.episode_of_id;! 0.60s Generate a list of the greatest episode number per show
Indexes: the Derived-Table Solution CREATE INDEX k_ep_nr ! ON title (kind_id, episode_of_id, episode_nr);! © 2014 PERCONA
EXPLAIN: the Derived-Table Solution id select_type table type key ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL 751752 Using where 1 PRIMARY tv eq_ref PRIMARY maxep.episode_of_id 1 NULL 1 PRIMARY ep ref k_ep_nr maxep.kind_id, © 2014 PERCONA maxep.episode_id, maxep.episode_nr 2 NULL 2 DERIVED >tle range k_ep_nr NULL 24544 Using where; Using index for group-­‐ by
© 2014 PERCONA Visual Explain
Status: the Derived-Table Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 110312 |! | Handler_read_last | 1 |! | Handler_read_next | 28989 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 30324 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 30323 |! +----------------------------+--------+! © 2014 PERCONA Evidence of a temporary table, even though EXPLAIN didn’t report it
Profile: the Derived-Table Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000600 |! | checking permissions | 0.000013 |! | checking permissions | 0.000007 |! | checking permissions | 0.000009 |! | Opening tables | 0.000111 |! | init | 0.000037 |! | System lock | 0.000027 |! | optimizing | 0.000006 |! | optimizing | 0.000011 |! | statistics | 0.000196 |! | preparing | 0.000030 |! | Sorting result | 0.000012 |! | statistics | 0.000057 |! | preparing | 0.000023 |! | executing | 0.000016 |! | Sending data | 0.000010 |! © 2014 PERCONA | executing | 0.000004 |! | Sending data | 0.607434 |! | end | 0.000020 |! | query end | 0.000028 |! | closing tables | 0.000005 |! | removing tmp table | 0.000014 |! | closing tables | 0.000070 |! | freeing items | 0.000235 |! | cleaning up | 0.000029 |! +----------------------+----------+! Evidence of a temporary table, even though EXPLAIN didn’t report it
Summary: Greatest per Group Solu7on Time Notes Exclusion-­‐join solu>on 71.20 Bad when each group has © 2014 PERCONA many entries. Derived-­‐table solu>on 0.60s
Query Patterns DYNAMIC PIVOT © 2014 PERCONA
© 2014 PERCONA Assignment: “I want the count of movies, TV, and video games per year—in columns.”
© 2014 PERCONA Not Like This SELECT k.kind, t.production_year, COUNT(*) AS Count! FROM kind_type k! JOIN title t ON k.id = t.kind_id ! WHERE production_year BETWEEN 2005 AND 2009! GROUP BY k.id, t.production_year;! ! +-------------+-----------------+--------+! | kind | production_year | Count |! +-------------+-----------------+--------+! | movie | 2005 | 13807 |! | movie | 2006 | 13916 |! | movie | 2007 | 14494 |! | movie | 2008 | 18354 |! | movie | 2009 | 23714 |! | tv series | 2005 | 3248 |! | tv series | 2006 | 3588 |! | tv series | 2007 | 3361 |! | tv series | 2008 | 3026 |! | tv series | 2009 | 2572 |!
© 2014 PERCONA Like This +----------------+-----------+-----------+-----------+-----------+-----------+! | kind | Count2005 | Count2006 | Count2007 | Count2008 | Count2009 |! +----------------+-----------+-----------+-----------+-----------+-----------+! | episode | 36138 | 24745 | 22335 | 16448 | 12917 |! | movie | 13807 | 13916 | 14494 | 18354 | 23714 |! | tv movie | 3541 | 3561 | 3586 | 3025 | 2778 |! | tv series | 3248 | 3588 | 3361 | 3026 | 2572 |! | video game | 383 | 367 | 310 | 300 | 215 |! | video movie | 7693 | 7671 | 6955 | 5808 | 4090 |! +----------------+-----------+-----------+-----------+-----------+-----------+!
© 2014 PERCONA Do It in One Pass SELECT k.kind,! SUM(production_year=2005) AS Count2005, ! SUM(production_year=2006) AS Count2006, ! SUM(production_year=2007) AS Count2007, ! SUM(production_year=2008) AS Count2008, ! SUM(production_year=2009) AS Count2009 ! FROM title t! JOIN kind_type k ON k.id = t.kind_id! GROUP BY t.kind_id ORDER BY NULL;! ! 0.77s SUM of 1’s = COUNT where condition is true
Indexes: the One-Pass Solution CREATE INDEX k_py ! ON title (kind_id, production_year);! © 2014 PERCONA
EXPLAIN: the One-Pass Solution id select_type table type key ref rows Extra 1 SIMPLE k index kind NULL 7 Using index; © 2014 PERCONA Using temporary 1 SIMPLE t ref k_py k.id 167056 Using index reading title table second unfortunately causes the group by to create a temp table
© 2014 PERCONA Visual Explain
Status: the One-Pass Solution +----------------------------+---------+! | Variable_name | Value |! +----------------------------+---------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 1 |! | Handler_read_key | 1543727 |! | Handler_read_last | 0 |! | Handler_read_next | 1543726 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 7 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 1543713 |! | Handler_write | 6 |! +----------------------------+---------+! © 2014 PERCONA title table has 1.5M rows; that’s how many times it increments counts in the temp table
Profile: the One-Pass Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000162 |! | checking permissions | 0.000034 |! | checking permissions | 0.000011 |! | Opening tables | 0.000043 |! | init | 0.000052 |! | System lock | 0.000030 |! | optimizing | 0.000017 |! | statistics | 0.000112 |! | preparing | 0.000025 |! | Creating tmp table | 0.000069 |! | executing | 0.000009 |! | Sending data | 0.772317 |! | end | 0.000025 |! | query end | 0.000022 |! | removing tmp table | 0.000036 |! | query end | 0.000007 |! © 2014 PERCONA | closing tables | 0.000018 |! | freeing items | 0.000485 |! | cleaning up | 0.000030 |! +----------------------+----------+! majority of time spent building temp table
© 2014 PERCONA One-Pass with Straight-Join Optimizer Override SELECT STRAIGHT_JOIN k.kind,! SUM(production_year=2005) AS Count2005, ! SUM(production_year=2006) AS Count2006, ! SUM(production_year=2007) AS Count2007, ! SUM(production_year=2008) AS Count2008, ! SUM(production_year=2009) AS Count2009 ! FROM title t! JOIN kind_type k ON k.id = t.kind_id! GROUP BY t.kind_id ORDER BY NULL;! ! 7.18s
Indexes: the Straight-Join Solution CREATE INDEX k_py ! ON title (kind_id, production_year);! © 2014 PERCONA
EXPLAIN: the Straight-Join Solution id select_type table type key ref rows Extra 1 SIMPLE t index k_py NULL 1537429 Using index 1 SIMPLE k eq_ref PRIMARY t.kind_id 1 © 2014 PERCONA no "Using temporary" because forcing title table to be read first means it scans the index in index order, avoiding the temp table—in this case
© 2014 PERCONA Visual Explain
Status: the Straight-Join Solution +----------------------------+---------+! | Variable_name | Value |! +----------------------------+---------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 1 |! | Handler_read_key | 7 |! | Handler_read_last | 0 |! | Handler_read_next | 1543719 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+---------+! ! © 2014 PERCONA really one-pass
Profile: the Straight-Join Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000172 |! | checking permissions | 0.000176 |! | checking permissions | 0.000012 |! | Opening tables | 0.000169 |! | init | 0.000067 |! | System lock | 0.000034 |! | optimizing | 0.000018 |! | statistics | 0.000055 |! | preparing | 0.000036 |! | Sorting result | 0.000016 |! | executing | 0.000007 |! | Sending data | 7.277106 |! | end | 0.000022 |! | query end | 0.000024 |! | closing tables | 0.000019 |! | freeing items | 0.000236 |! © 2014 PERCONA | cleaning up | 0.000026 |! +----------------------+----------+! no temporary table! majority of time spent just moving rows
© 2014 PERCONA Scalar Subquery Solution 0.001 SELECT k.kind,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2005) AS Count2005,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2006) AS Count2006,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2007) AS Count2007,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2008) AS Count2008,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2009) AS Count2009! FROM kind_type k;!
Indexes: the Scalar Subquery Solution CREATE INDEX k_py ! ON title (kind_id, production_year)! ! CREATE UNIQUE INDEX kind ! ON kind_type (kind);! © 2014 PERCONA
EXPLAIN: the Scalar Subquery Solution id select_type table type key ref rows Extra 1 PRIMARY k index kind NULL 7 Using index 6 DEPENDENT SUBQUERY © 2014 PERCONA >tle ref k_py k.id, const 1781 Using index 5 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index 4 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index 3 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index 2 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index
© 2014 PERCONA Visual Explain
Status: the Scalar Subquery Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 12 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 1 |! | Handler_read_key | 36 |! | Handler_read_last | 0 |! | Handler_read_next | 262953 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+! © 2014 PERCONA good use of indexes
Profile: the Scalar Subquery Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | checking permissions | 0.000005 |! | checking permissions | 0.000010 |! | Opening tables | 0.000143 |! | init | 0.000071 |! | System lock | 0.000047 |! | optimizing | 0.000007 |! | statistics | 0.000022 |! | preparing | 0.000020 |! | executing | 0.000005 |! | Sending data | 0.000620 |! . . .! | executing | 0.000013 |! | Sending data | 0.001620 |! | executing | 0.000008 |! | Sending data | 0.000978 |! | executing | 0.000010 |! | Sending data | 0.000384 |! | end | 0.000011 |! | query end | 0.000027 |! | closing tables | 0.000019 |! | freeing items | 0.000460 |! | cleaning up | 0.000030 |! +----------------------+----------+! © 2014 PERCONA scary long profile, but still fast
© 2014 PERCONA Summary: Dynamic Pivot Solu7on Time Notes One-­‐pass solu>on 0.77s Straight-­‐join solu>on 7.18s was much bejer in 5.5 Scalar Subquery solu>on 0.001s
Query Patterns RELATIONAL DIVISION © 2014 PERCONA
© 2014 PERCONA Assignment: “I want to see movies with all three of keywords espionage, nuclear-bomb, and ejector-seat.”
Not Movies with One Keyword SELECT t.title, k.keyword FROM keyword k ! JOIN movie_keyword mk ON k.id = mk.keyword_id ! JOIN title t ON mk.movie_id = t.id ! WHERE k.keyword IN ('espionage', 'nuclear-bomb', 'ejector-seat');! ! +--------------------------+--------------+! | title | keyword |! +--------------------------+--------------+! | 2 Fast 2 Furious | ejector-seat |! | Across the Pacific | espionage |! | Action in Arabia | espionage |! . . .! | You Only Live Twice | espionage |! | Zombie Genocide | nuclear-bomb |! | Zombies of the Strat | espionage |! +--------------------------+--------------+! 705 rows in set (12.97 sec)! © 2014 PERCONA
© 2014 PERCONA This Won’t Work SELECT t.title, k.keyword ! FROM keyword k ! JOIN movie_keyword mk ON k.id = mk.keyword_id ! JOIN title t ON mk.movie_id = t.id ! WHERE k.keyword = 'espionage'! AND k.keyword = 'nuclear-bomb'! AND k.keyword = 'ejector-seat';! ! 0 rows in set (12.97 sec)! ! It’s impossible for one column to have three values on a given row
© 2014 PERCONA Only Movies with All Three +------------+-------------------------------------+! | title | keywords |! +------------+-------------------------------------+! | Goldfinger | ejector-seat,espionage,nuclear-bomb |! +------------+-------------------------------------+!
© 2014 PERCONA Group-by Solution 0.02s SELECT t.title, GROUP_CONCAT(k.keyword) AS keywords! FROM title t ! JOIN movie_keyword mk ON t.id = mk.movie_id ! JOIN keyword k ON k.id = mk.keyword_id! WHERE k.keyword IN ! ('espionage', 'nuclear-bomb', 'ejector-seat')! GROUP BY mk.movie_id! HAVING COUNT(DISTINCT mk.keyword_id) = 3 ! ORDER BY NULL;! !
© 2014 PERCONA Indexes CREATE INDEX k_i! ON keyword (keyword, id);! ! CREATE INDEX k_m! ON movie_keyword (keyword_id, movie_id);!
EXPLAIN: the Group-by Solution id select_type table type key ref rows Extra 1 SIMPLE k range k_i NULL 3 Using where; © 2014 PERCONA Using index; Using temporary; Using filesort 1 SIMPLE mk ref k_m k.id 23 Using index 1 SIMPLE t eq_ref PRIMARY mk.movie_id 1 NULL
© 2014 PERCONA Visual Explain
Status: the Group-by Solution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 710 |! | Handler_read_last | 0 |! | Handler_read_next | 708 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 705 |! | Handler_read_rnd_next | 706 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 705 |! +----------------------------+-------+! © 2014 PERCONA building and reading a temporary table
Profile: the Group-by Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000267 |! | checking permissions | 0.000010 |! | checking permissions | 0.000004 |! | checking permissions | 0.000008 |! | Opening tables | 0.000041 |! | init | 0.000078 |! | System lock | 0.000037 |! | optimizing | 0.000024 |! | statistics | 0.000166 |! | preparing | 0.000028 |! | Creating tmp table | 0.000199 |! | Sorting result | 0.000013 |! | executing | 0.000005 |! | Sending data | 0.009532 |! © 2014 PERCONA | Creating sort index | 0.010310 |! | end | 0.000066 |! | query end | 0.000027 |! | removing tmp table | 0.000176 |! | query end | 0.000021 |! | removing tmp table | 0.000010 |! | query end | 0.000006 |! | closing tables | 0.000022 |! | freeing items | 0.000016 |! | removing tmp table | 0.000007 |! | freeing items | 0.000009 |! | removing tmp table | 0.000005 |! | freeing items | 0.000449 |! | cleaning up | 0.000028 |! +----------------------+----------+! building & tearing down temp table
© 2014 PERCONA Self-Join Solution SELECT t.title, CONCAT_WS(',', k1.keyword, k2.keyword, k3.keyword) AS keywords ! FROM title t ! JOIN movie_keyword mk1 ON t.id = mk1.movie_id ! JOIN keyword k1 ON k1.id = mk1.keyword_id ! JOIN movie_keyword mk2 ON mk1.movie_id= mk2.movie_id ! JOIN keyword k2 ON k2.id = mk2.keyword_id ! JOIN movie_keyword mk3 ON mk1.movie_id = mk3.movie_id ! JOIN keyword k3 ON k3.id = mk3.keyword_id ! WHERE (k1.keyword, k2.keyword, k3.keyword) ! = ('espionage', 'nuclear-bomb', 'ejector-seat');! 0.015s
EXPLAIN: the Self-Join Solution id select_type table type key ref rows Extra 1 SIMPLE k1 ref k_i const 1 Using index 1 SIMPLE k2 ref k_i const 1 Using index 1 SIMPLE k3 ref k_i const 1 Using index 1 SIMPLE mk1 ref k_m k1.id 17 Using index 1 SIMPLE t eq_ref PRIMARY mk1.movie_id 1 NULL 1 SIMPLE mk2 ref k_m k2.id, © 2014 PERCONA mk1.movie_id 1 Using index 1 SIMPLE mk3 ref k_m k3.id, mk1.movie_id 1 Using index
© 2014 PERCONA Visual Explain
Status: the Self-Join Solution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 14 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 1218 |! | Handler_read_last | 0 |! | Handler_read_next | 613 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-------+! © 2014 PERCONA minimal rows, good index usage
Profile: the Self-Join Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000205 |! | checking permissions | 0.000011 |! | checking permissions | 0.000006 |! | checking permissions | 0.000005 |! | checking permissions | 0.000005 |! | checking permissions | 0.000005 |! | checking permissions | 0.000005 |! | checking permissions | 0.000010 |! | Opening tables | 0.000061 |! | init | 0.000069 |! | System lock | 0.000056 |! | optimizing | 0.000029 |! | statistics | 0.000106 |! | preparing | 0.000049 |! | executing | 0.000008 |! | Sending data | 0.013485 |! © 2014 PERCONA | end | 0.000018 |! | query end | 0.000022 |! | closing tables | 0.000020 |! | freeing items | 0.000683 |! | cleaning up | 0.000034 |! +----------------------+----------+! who says joins are slow?
Summary: Relational Division Solu7on Time Notes Group-­‐by solu>on 0.02s much improved in 5.7 Self-­‐join solu>on 0.015s © 2014 PERCONA
Query Patterns PERFORMANCE SCHEMA © 2014 PERCONA
© 2014 PERCONA Performance Schema • New tools to instrument and profile queries. – Use PERFORMANCE_SCHEMA with MySQL 5.6+ • SHOW PROFILES is deprecated in 5.6.7+ and will be removed in a future release
© 2014 PERCONA Sys Schema • Get the MySQL-sys schema for handy views, functions, and procedures. – https://github.com/MarkLeith/mysql-sys
© 2014 PERCONA Sys Schema Caveats • However, it’s still incomplete and tricky… – Installation fails; the views reference some P_S tables that don’t exist yet as of MySQL 5.7.5 (e.g. memory_%). – Documentation includes at least one sys procedure that isn’t in the download (ps_trace_statement_digest()). • We all need to use it and give feedback to Mark!
© 2014 PERCONA Sys Schema Setup mysql> SOURCE sys_57.sql;! ! mysql> CALL sys.ps_setup_enable_instrument('stage/%');! +-------------------------+! | summary |! +-------------------------+! | Enabled 108 instruments |! +-------------------------+! ! mysql> CALL sys.ps_truncate_all_tables(false);! +---------------------+! | summary |! +---------------------+! | Truncated 31 tables |! +---------------------+! !
© 2014 PERCONA Statement Analysis mysql> select * from sys.statement_analysis limit 1G! *************************** 1. row ***************************! query: SELECT `t` . `title` FROM `ti ... id` AND `c` . `role_id` = ? ) ! db: imdb! full_scan: ! exec_count: 1! err_count: 0! warn_count: 0! total_latency: 2.27 s! max_latency: 2.27 s! avg_latency: 2.27 s! lock_latency: 296.00 us! rows_sent: 6056! rows_sent_avg: 6056! rows_examined: 180432! rows_examined_avg: 180432! tmp_tables: 0! tmp_disk_tables: 0! rows_sorted: 0! sort_merge_passes: 0! digest: 4f8e3695ecf7c8518a1e1defe2ff323c! first_seen: 2014-09-28 02:21:21! last_seen: 2014-09-28 02:21:21! similar to pt-query-digest output with Percona’s verbose slow query log
SHOW PROFILE Workalike mysql> select * from sys.x$user_summary_by_stages;! +------+--------------------------------+-------+---------------+-----------+! | user | event_name | total | wait_sum | wait_avg |! +------+--------------------------------+-------+---------------+-----------+! | root | stage/sql/Sending data | 93246 | 2053496638000 | 22022000 |! | root | stage/sql/executing | 93247 | 214310855000 | 2298000 |! | root | stage/sql/System lock | 27 | 6200477000 | 229647000 |! | root | stage/sql/Opening tables | 126 | 3940994000 | 31277000 |! | root | stage/sql/checking permissions | 31 | 2309620000 | 74503000 |! | root | stage/sql/closing tables | 126 | 697965000 | 5539000 |! | root | stage/sql/statistics | 3 | 452872000 | 150957000 |! | root | stage/sql/freeing items | 2 | 421142000 | 210571000 |! | root | stage/sql/query end | 126 | 384577000 | 3052000 |! | root | stage/sql/starting | 2 | 271533000 | 135766000 |! | root | stage/sql/preparing | 3 | 119699000 | 39899000 |! | root | stage/sql/init | 3 | 82758000 | 27586000 |! | root | stage/sql/optimizing | 4 | 49758000 | 12439000 |! | root | stage/sql/removing tmp table | 1 | 8568000 | 8568000 |! | root | stage/sql/cleaning up | 2 | 7443000 | 3721000 |! | root | stage/sql/Sorting result | 1 | 7039000 | 7039000 |! | root | stage/sql/end | 2 | 6986000 | 3493000 |! +------+--------------------------------+-------+---------------+-----------+! © 2014 PERCONA totals for all queries by user, not just in current session
Query Patterns CONCLUSIONS © 2014 PERCONA
© 2014 PERCONA Conclusions • Use all tools to measure query performance – EXPLAIN – Session Status – Query Profiler – Performance Schema and Sys Schema • Test with real-world data, because the best solution depends on the volume of data you’re querying. • Allocate enough memory to buffers so the indexes you need stay resident in RAM.
© 2014 PERCONA License and Copyright Copyright 2014 Percona Released under a Creative Commons 3.0 License: http://creativecommons.org/licenses/by-nc-nd/3.0/ You are free to share—to copy, distribute and transmit this work, under the following conditions: Attribution. You must attribute this work to Percona Noncommercial. You may not use this work for commercial purposes. No Derivative Works. You may not alter, transform, or build upon this work.

Recommended

PDF
How to Use JSON in MySQL Wrong
PDF
Advanced MySQL Query Tuning
PDF
How to Design Indexes, Really
PDF
Extensible Data Modeling
PDF
Trees and Hierarchies in SQL
KEY
Trees In The Database - Advanced data structures
PDF
Practical Object Oriented Models In Sql
PDF
More mastering the art of indexing
PDF
Database Anti Patterns
PPTX
Introduction to SQL Antipatterns
PDF
Recursive Query Throwdown
PDF
Sql Antipatterns Strike Back
PDF
PDF
The MySQL Query Optimizer Explained Through Optimizer Trace
PDF
Models for hierarchical data
PDF
MySQL Index Cookbook
PDF
InnoDB Locking Explained with Stick Figures
PDF
Introduction to Redis
PDF
InnoDB Internal
PDF
PostgreSQL Tutorial For Beginners | Edureka
PDF
PostgreSQL Performance Tuning
PDF
Deep dive into PostgreSQL statistics.
PPTX
mongodb와 mysql의 CRUD 연산의 성능 비교
PDF
[pgday.Seoul 2022] PostgreSQL구조 - 윤성재
PDF
B+Tree Indexes and InnoDB
PDF
C* Summit 2013: The World's Next Top Data Model by Patrick McFadin
PDF
[Pgday.Seoul 2018] 이기종 DB에서 PostgreSQL로의 Migration을 위한 DB2PG
PDF
An introduction to MongoDB
PDF
PDF
Survey of Percona Toolkit

More Related Content

PDF
How to Use JSON in MySQL Wrong
PDF
Advanced MySQL Query Tuning
PDF
How to Design Indexes, Really
PDF
Extensible Data Modeling
PDF
Trees and Hierarchies in SQL
KEY
Trees In The Database - Advanced data structures
PDF
Practical Object Oriented Models In Sql
PDF
More mastering the art of indexing
How to Use JSON in MySQL Wrong
Advanced MySQL Query Tuning
How to Design Indexes, Really
Extensible Data Modeling
Trees and Hierarchies in SQL
Trees In The Database - Advanced data structures
Practical Object Oriented Models In Sql
More mastering the art of indexing

What's hot

PDF
Database Anti Patterns
PPTX
Introduction to SQL Antipatterns
PDF
Recursive Query Throwdown
PDF
Sql Antipatterns Strike Back
PDF
PDF
The MySQL Query Optimizer Explained Through Optimizer Trace
PDF
Models for hierarchical data
PDF
MySQL Index Cookbook
PDF
InnoDB Locking Explained with Stick Figures
PDF
Introduction to Redis
PDF
InnoDB Internal
PDF
PostgreSQL Tutorial For Beginners | Edureka
PDF
PostgreSQL Performance Tuning
PDF
Deep dive into PostgreSQL statistics.
PPTX
mongodb와 mysql의 CRUD 연산의 성능 비교
PDF
[pgday.Seoul 2022] PostgreSQL구조 - 윤성재
PDF
B+Tree Indexes and InnoDB
PDF
C* Summit 2013: The World's Next Top Data Model by Patrick McFadin
PDF
[Pgday.Seoul 2018] 이기종 DB에서 PostgreSQL로의 Migration을 위한 DB2PG
PDF
An introduction to MongoDB
Database Anti Patterns
Introduction to SQL Antipatterns
Recursive Query Throwdown
Sql Antipatterns Strike Back
The MySQL Query Optimizer Explained Through Optimizer Trace
Models for hierarchical data
MySQL Index Cookbook
InnoDB Locking Explained with Stick Figures
Introduction to Redis
InnoDB Internal
PostgreSQL Tutorial For Beginners | Edureka
PostgreSQL Performance Tuning
Deep dive into PostgreSQL statistics.
mongodb와 mysql의 CRUD 연산의 성능 비교
[pgday.Seoul 2022] PostgreSQL구조 - 윤성재
B+Tree Indexes and InnoDB
C* Summit 2013: The World's Next Top Data Model by Patrick McFadin
[Pgday.Seoul 2018] 이기종 DB에서 PostgreSQL로의 Migration을 위한 DB2PG
An introduction to MongoDB

Viewers also liked

PDF
PDF
Survey of Percona Toolkit
PDF
Mentor Your Indexes
PDF
MySQL 5.5 Guide to InnoDB Status
PDF
Full Text Search In PostgreSQL
PDF
Hierarchical data models in Relational Databases
PDF
Sql Injection Myths and Fallacies
PPT
Entity Attribute Value (Eav)
 
PDF
SQL Outer Joins for Fun and Profit
PDF
Eav Data Model Concepts
PPTX
dotnetMALAGA - Sql query tuning guidelines
PDF
MySQL Query Tuning for the Squeemish -- Fossetcon Orlando Sep 2014
PDF
Requirements the Last Bottleneck
PPT
Building High Performance MySql Query Systems And Analytic Applications
PDF
Boost Performance With My S Q L 51 Partitions
PDF
Sql server 2016 queries
PPTX
DOAG: Visual SQL Tuning
PDF
Zurich2007 MySQL Query Optimization
Survey of Percona Toolkit
Mentor Your Indexes
MySQL 5.5 Guide to InnoDB Status
Full Text Search In PostgreSQL
Hierarchical data models in Relational Databases
Sql Injection Myths and Fallacies
Entity Attribute Value (Eav)
 
SQL Outer Joins for Fun and Profit
Eav Data Model Concepts
dotnetMALAGA - Sql query tuning guidelines
MySQL Query Tuning for the Squeemish -- Fossetcon Orlando Sep 2014
Requirements the Last Bottleneck
Building High Performance MySql Query Systems And Analytic Applications
Boost Performance With My S Q L 51 Partitions
Sql server 2016 queries
DOAG: Visual SQL Tuning
Zurich2007 MySQL Query Optimization

Similar to Sql query patterns, optimized

PPTX
MySQL Indexing - Best practices for MySQL 5.6
PDF
Query Optimization with MySQL 5.6: Old and New Tricks - Percona Live London 2013
PDF
MySQL Database Monitoring: Must, Good and Nice to Have
PDF
Introduction into MySQL Query Tuning for Dev[Op]s
PDF
Mysql query optimization
PDF
Percona Live 2012PPT: MySQL Query optimization
PPTX
Mysql query optimization best practices and indexing
PDF
Query Optimization with MySQL 5.6: Old and New Tricks
PDF
Tools and Techniques for Index Design
PPTX
Alkin Tezuysal "MySQL Query Optimization Best Practices and Indexing"
 
PDF
MySQL Query Optimisation 101
PDF
Advanced query optimization
PDF
介绍 Percona 服务器 XtraDB 和 Xtrabackup
PDF
Quick Wins
PPT
Explain that explain
PDF
56 Query Optimization
PDF
Troubleshooting MySQL Performance
PPTX
Работа с индексами - лучшие практики для MySQL 5.6, Петр Зайцев (Percona)
 
PDF
U C2007 My S Q L Performance Cookbook
PDF
query optimization
MySQL Indexing - Best practices for MySQL 5.6
Query Optimization with MySQL 5.6: Old and New Tricks - Percona Live London 2013
MySQL Database Monitoring: Must, Good and Nice to Have
Introduction into MySQL Query Tuning for Dev[Op]s
Mysql query optimization
Percona Live 2012PPT: MySQL Query optimization
Mysql query optimization best practices and indexing
Query Optimization with MySQL 5.6: Old and New Tricks
Tools and Techniques for Index Design
Alkin Tezuysal "MySQL Query Optimization Best Practices and Indexing"
 
MySQL Query Optimisation 101
Advanced query optimization
介绍 Percona 服务器 XtraDB 和 Xtrabackup
Quick Wins
Explain that explain
56 Query Optimization
Troubleshooting MySQL Performance
Работа с индексами - лучшие практики для MySQL 5.6, Петр Зайцев (Percona)
 
U C2007 My S Q L Performance Cookbook
query optimization

Sql query patterns, optimized

  • 1.
    MySQL Query Patterns,Optimized © 2014 PERCONA Bill Karwin, Percona
  • 2.
    © 2014 PERCONAWelcome Everybody! • Bill Karwin – Senior Knowledge Manager in Percona Support – Joined Percona in 2010 • Percona offers MySQL services – Consulting – Support for MySQL and variants – Remote DBA – Development – Training 2
  • 3.
    © 2014 PERCONAHow Do We Optimize? • Identify queries. • Measure optimization plan and performance. – EXPLAIN – SHOW SESSION STATUS – SHOW PROFILES • Add indexes and/or redesign the query.
  • 4.
    © 2014 PERCONATesting Environment • MySQL 5.7.5-m15 • CentOS 6.5 running under VirtualBox on my Macbook Pro (non-SSD) • MySQL Workbench 6.2
  • 5.
    © 2014 PERCONAExample Database cast_info name char_name kind_type >tle role_type
  • 6.
    © 2014 PERCONACommon Query Patterns 1. Exclusion Joins 2. Random Selection 3. Greatest per Group 4. Dynamic Pivot 5. Relational Division
  • 7.
    Query Patterns EXCLUSIONJOINS © 2014 PERCONA
  • 8.
    © 2014 PERCONAAssignment: “I want to find recent movies that had no director.”
  • 9.
    © 2014 PERCONANot Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8 /* director */! );! Movies In the range of recent years Correlated subquery to find a director for each movie
  • 10.
    © 2014 PERCONANot Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8! );! ???s I gave up after waiting > 1 hour
  • 11.
    EXPLAIN: the Not-ExistsSolution id select_type table type key ref rows Extra 1 PRIMARY t ALL NULL NULL 1598319 Using where 2 DEPENDENT SUBQUERY © 2014 PERCONA c ALL NULL NULL 24149504 Using where The correlated subquery is executed 1.6 M times! Both tables are table scans And scans 24 M rows each time, totalling 3.8 × 1013 row comparisons Dependent subquery executes once for each set of values in outer
  • 12.
    Indexes: the Not-ExistsSolution CREATE INDEX k_py ! ON title (kind_id, production_year);! ! CREATE INDEX m_r! ON cast_info (movie_id, role_id);! © 2014 PERCONA
  • 13.
    EXPLAIN: the Not-ExistsSolution id select_type table type key ref rows Extra 1 PRIMARY t range k_py NULL 189846 Using index © 2014 PERCONA condi>on; Using where 2 DEPENDENT SUBQUERY c ref m_r t.id, const 3 Using index The correlated subquery is executed 189k times! At least both table references use indexes A covering index is best—if the index fits in memory Dependent subquery executes once for each set of values in outer
  • 14.
    Visual Explain inWorkbench © 2014 PERCONA
  • 15.
    © 2014 PERCONANot Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8! );! 5.34s Better, but when the indexes aren’t in memory, it’s still too slow
  • 16.
    © 2014 PERCONABuffer Pool • It’s crucial that queries read an index from memory; I/O during an index scan kills performance. [mysqld]! innodb_buffer_pool_size = 64M # wrong! innodb_buffer_pool_size = 2G # better!
  • 17.
    © 2014 PERCONANot Exists Solution SELECT t.title! FROM title t! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND NOT EXISTS (! SELECT * FROM cast_info c! WHERE c.movie_id = t.id ! AND c.role_id = 8! );! 3.25s much faster after increasing size of buffer pool
  • 18.
    © 2014 PERCONASHOW SESSION STATUS • Shows the real count of row accesses for your current session. mysql> FLUSH STATUS;! mysql> ... run a query ...! mysql> SHOW SESSION STATUS LIKE 'Handler%';!
  • 19.
    Status: the Not-ExistsSolution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 186489 |! | Handler_read_last | 0 |! | Handler_read_next | 93244 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 93244 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+! © 2014 PERCONA read_key: lookup by index, e.g. each lookup in cast_info, plus the first row in title read_next: advancing in index order, e.g. the range query for rows in title after the first row read_rnd: fetch a row from a table based on a row reference (probably by MRR)
  • 20.
    © 2014 PERCONASHOW PROFILE • Enable query profiler for the current session. mysql> SET PROFILING = 1;! • Run a query. mysql> SELECT t.title FROM title t ...! • Query the real execution time. mysql> SHOW PROFILES;! • Query detail for a specific query. mysql> SHOW PROFILE FOR QUERY 1;!
  • 21.
    Profile: the Not-ExistsSolution +----------------+----------+! | Status | Duration |! +----------------+----------+! | Sending data | 0.000029 |! | executing | 0.000004 |! | Sending data | 0.000075 |! . . .! | executing | 0.000004 |! | Sending data | 0.000023 |! | executing | 0.000004 |! | Sending data | 0.000023 |! | executing | 0.000004 |! | Sending data | 0.000081 |! | executing | 0.000009 |! | Sending data | 0.000029 |! | end | 0.000007 |! | query end | 0.000022 |! | closing tables | 0.000028 |! | freeing items | 0.000344 |! | cleaning up | 0.000025 |! +----------------+----------+! © 2014 PERCONA Thousands of iterations of correlated subqueries caused the profile information to overflow!
  • 22.
    © 2014 PERCONANot-In Solution SELECT title! FROM title! WHERE kind_id = 1 ! AND production_year >= 2005 ! AND id NOT IN (! SELECT movie_id FROM cast_info! WHERE role_id = 8! );! 1.62s Not a correlated subquery
  • 23.
    Indexes: the Not-InSolution CREATE INDEX k_py ! ON title (kind_id, production_year);! ! CREATE INDEX m_r! ON cast_info (movie_id, role_id);! © 2014 PERCONA
  • 24.
    EXPLAIN: the Not-InSolution id select_type table type key ref rows Extra 1 PRIMARY >tle range k_py NULL 189846 Using index © 2014 PERCONA condi>on; Using where; Using MRR 1 DEPENDENT SUBQUERY cast_ info index_subquery m_r func, const 1 Using index; Using where But somehow MySQL doesn’t report a different select type picks only 1 row per subquery execution
  • 25.
    © 2014 PERCONAVisual Explain
  • 26.
    © 2014 PERCONAStatus: the Not-In Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 186489 |! | Handler_read_last | 0 |! | Handler_read_next | 93244 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 93244 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+!
  • 27.
    © 2014 PERCONAProfile: the Not-In Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000128 |! | checking permissions | 0.000014 |! | checking permissions | 0.000012 |! | Opening tables | 0.000043 |! | init | 0.000059 |! | System lock | 0.000030 |! | optimizing | 0.000022 |! | statistics | 0.000127 |! | preparing | 0.000036 |! | optimizing | 0.000013 |! | statistics | 0.000028 |! | preparing | 0.000017 |! | executing | 0.000008 |! | Sending data | 1.623482 |! | end | 0.000024 |! | query end | 0.000025 |! | closing tables | 0.000020 |! | freeing items | 0.000329 |! | cleaning up | 0.000029 |! +----------------------+----------+! Most of the time spent in “Sending data” (moving rows around)
  • 28.
    © 2014 PERCONAOuter-Join Solution SELECT t.title! FROM title t! LEFT OUTER JOIN cast_info c ! ON t.id = c.movie_id ! AND c.role_id = 8! WHERE t.kind_id = 1 ! AND t.production_year >= 2005 ! AND c.movie_id IS NULL;! 1.58s Try to find a director for each movie using a join If no director is found, that’s the one we want
  • 29.
    Indexes: the Outer-JoinSolution CREATE INDEX k_py ! ON title (kind_id, production_year);! ! CREATE INDEX m_r! ON cast_info (movie_id, role_id);! © 2014 PERCONA
  • 30.
    EXPLAIN: the Outer-JoinSolution id select_type table type key ref rows Extra 1 SIMPLE t range k_py NULL 189846 Using index © 2014 PERCONA condi>on 1 SIMPLE c ref m_r t.id, const 1 Using where; Using index; Not exists Special “not exists” optimization
  • 31.
    © 2014 PERCONAVisual Explain
  • 32.
    Status: the Outer-JoinSolution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 93245 |! | Handler_read_last | 0 |! | Handler_read_next | 93244 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-------+! © 2014 PERCONA Fewest row reads
  • 33.
    Profile: the Outer-JoinSolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000161 |! | checking permissions | 0.000014 |! | checking permissions | 0.000013 |! | Opening tables | 0.000051 |! | init | 0.000056 |! | System lock | 0.000040 |! | optimizing | 0.000000 |! | statistics | 0.000263 |! | preparing | 0.000064 |! | executing | 0.000012 |! | Sending data | 1.581615 |! | end | 0.000021 |! | query end | 0.000022 |! | closing tables | 0.000016 |! | freeing items | 0.000324 |! | cleaning up | 0.000026 |! +----------------------+----------+! © 2014 PERCONA
  • 34.
    © 2014 PERCONASummary: Exclusion Joins Solu7on Time Notes Not-­‐Exists 3.25s dependent subquery Not-­‐In 1.62s dependent subquery Outer-­‐Join 1.58s “not exists” op>miza>on
  • 35.
    Query Patterns RANDOMSELECTION © 2014 PERCONA
  • 36.
    © 2014 PERCONAAssignment: “I want a query that picks a random movie.”
  • 37.
    © 2014 PERCONANaïve Order-By Solution SELECT *! FROM title! WHERE kind_id = 1 /* movie */! ORDER BY RAND()! LIMIT 1;! 0.98s
  • 38.
    EXPLAIN: the Order-BySolution id select_type table type key ref rows Extra 1 SIMPLE >tle ref k_py const 790882 Using temporary; © 2014 PERCONA Using filesort
  • 39.
    © 2014 PERCONAVisual Explain
  • 40.
    Status: the Order-BySolution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 2 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 2 |! | Handler_read_last | 0 |! | Handler_read_next | 947164 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 2 |! | Handler_read_rnd_next | 947166 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 947164 |! +----------------------------+--------+! © 2014 PERCONA What is this?
  • 41.
    Profile: the Order-BySolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000074 |! | checking permissions | 0.000032 |! | Opening tables | 0.000035 |! | System lock | 0.000012 |! | init | 0.000025 |! | optimizing | 0.000004 |! | statistics | 0.000014 |! | preparing | 0.000010 |! | Creating tmp table | 0.000245 |! | executing | 0.000003 |! | Copying to tmp table | 4.875666 |! | Sorting result | 3.871513 |! | Sending data | 0.000059 |! | end | 0.000005 |! | removing tmp table | 0.058239 |! | end | 0.000018 |! © 2014 PERCONA | query end | 0.000064 |! | closing tables | 0.000034 |! | freeing items | 0.000210 |! | logging slow query | 0.000003 |! | cleaning up | 0.000005 |! +----------------------+----------+! MySQL 5.5 saves to a temp table, then sorts by filesort.
  • 42.
    Profile: the Order-BySolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000111 |! | checking permissions | 0.000015 |! | Opening tables | 0.000030 |! | init | 0.000045 |! | System lock | 0.000019 |! | optimizing | 0.000013 |! | statistics | 0.000106 |! | preparing | 0.000019 |! | Creating tmp table | 0.000050 |! | Sorting result | 0.000007 |! | executing | 0.000005 |! | Sending data | 0.836566 |! | Creating sort index | 0.145061 |! | end | 0.000018 |! | query end | 0.000021 |! | removing tmp table | 0.002427 |! © 2014 PERCONA | query end | 0.000516 |! | closing tables | 0.000037 |! | freeing items | 0.000234 |! | cleaning up | 0.000023 |! +----------------------+----------+! MySQL 5.7 creates a sort index for the temp table on the fly
  • 43.
    © 2014 PERCONAOffset Solution SELECT ROUND(RAND() * COUNT(*)) ! FROM title! WHERE kind_id = 1;! ! SELECT *! FROM title! WHERE kind_id = 1! LIMIT 1 OFFSET $random;! 0.45s
  • 44.
    © 2014 PERCONAIndexes: the Offset Solution CREATE INDEX k! ON title (kind_id);!
  • 45.
    EXPLAIN: the OffsetSolution id select_type table type key ref rows Extra 1 SIMPLE >tle ref k const 787992 © 2014 PERCONA
  • 46.
    © 2014 PERCONAVisual Explain
  • 47.
    © 2014 PERCONAStatus: the Offset Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 2 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 1 |! | Handler_read_last | 0 |! | Handler_read_next | 473582 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+! Query must read OFFSET + COUNT rows. A high random value makes the query take longer.
  • 48.
    © 2014 PERCONAProfile: the Offset Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000099 |! | checking permissions | 0.000012 |! | Opening tables | 0.000034 |! | init | 0.000045 |! | System lock | 0.000019 |! | optimizing | 0.000013 |! | statistics | 0.000114 |! | preparing | 0.000021 |! | executing | 0.000005 |! | Sending data | 0.459230 |! | end | 0.000018 |! | query end | 0.000018 |! | closing tables | 0.000017 |! | freeing items | 0.000194 |! | cleaning up | 0.000025 |! +----------------------+----------+! Many rows moving from storage layer to SQL layer, only to be discarded.
  • 49.
    © 2014 PERCONAPrimary Key Solution SELECT ROUND(RAND() * MAX(id)) ! FROM title! WHERE kind_id = 1;! ! SELECT *! FROM title! WHERE kind_id = 1 AND id > $random! LIMIT 1;! 0.0008s
  • 50.
    EXPLAIN: the PrimaryKey Solution id select_type table type key ref rows Extra 1 SIMPLE >tle ref k NULL 268254 Using index © 2014 PERCONA condi>on Strange that the optimizer estimates this many rows
  • 51.
    © 2014 PERCONAVisual Explain
  • 52.
    Status: the PrimaryKey Solution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 2 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 1 |! | Handler_read_last | 0 |! | Handler_read_next | 0 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-------+! © 2014 PERCONA Just one row read after the index lookup.
  • 53.
    Profile: the PrimaryKey Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000119 |! | checking permissions | 0.000016 |! | Opening tables | 0.000036 |! | init | 0.000053 |! | System lock | 0.000027 |! | optimizing | 0.000021 |! | statistics | 0.000180 |! | preparing | 0.000112 |! | executing | 0.000016 |! | Sending data | 0.000175 |! | end | 0.000010 |! | query end | 0.000017 |! | closing tables | 0.000015 |! | freeing items | 0.000028 |! | cleaning up | 0.000028 |! +----------------------+----------+! © 2014 PERCONA Everything is fast when we search a index by value rather than by position.
  • 54.
    Summary: Random SelectionSolu7on Time Notes Order-­‐By Solu>on 0.98s Offset Solu>on 0.45s Requires the COUNT() Primary Key Solu>on 0.0008s Requires the MAX() © 2014 PERCONA
  • 55.
    Query Patterns GREATESTPER GROUP © 2014 PERCONA
  • 56.
    © 2014 PERCONAAssignment: “I want the last episode of every TV series.”
  • 57.
    © 2014 PERCONAGetting the Last Episode This is not the title of the last episode! SELECT tv.title, ep.title, MAX(ep.episode_nr) AS last_ep ! FROM title ep! JOIN title tv ON tv.id = ep.episode_of_id! WHERE ep.kind_id = 7 /* TV show */! GROUP BY ep.episode_of_id ORDER BY NULL;!
  • 58.
    © 2014 PERCONAWhy Isn’t It? • The query doesn’t necessarily return the title from the row where MAX(ep.episode_nr) occurs. • Should the following return the title of the first episode or the last episode? SELECT tv.title, ep.title, MIN(ep.episode_nr) AS first_ep ! MAX(ep.episode_nr) AS last_ep ! FROM . . .!
  • 59.
    © 2014 PERCONAExclusion Join Solution SELECT tv.title, ep1.title, ep1.episode_nr! FROM title ep1! LEFT OUTER JOIN title ep2 ! ON ep1.kind_id = ep2.kind_id! AND ep1.episode_of_id = ep2.episode_of_id! AND ep1.episode_nr < ep2.episode_nr! JOIN title tv ON tv.id = ep1.episode_of_id! WHERE ep1.kind_id = 7 ! AND ep1.episode_of_id IS NOT NULL! AND ep1.episode_nr >= 1! AND ep2.episode_of_id IS NULL;! 71.20 Try to find a row ep2 for the same show with a greater episode_nr If no such row is found, then ep1 must be the last episode for the show
  • 60.
    Indexes: the Exclusion-JoinSolution CREATE INDEX k_ep_nr ! ON title (kind_id, episode_of_id, episode_nr);! © 2014 PERCONA
  • 61.
    EXPLAIN: the Exclusion-JoinSolution id select_type table type key ref rows Extra 1 SIMPLE ep1 ref k_py const 787992 Using where 1 SIMPLE tv eq_ref PRIMARY ep1.episode_of_id 1 1 SIMPLE ep2 ref k_ep_nr const, © 2014 PERCONA ep1.episode_of_id 22 Using where; Using index
  • 62.
    © 2014 PERCONAVisual Explain
  • 63.
    Status: the Exclusion-JoinSolution +----------------------------+-----------+! | Variable_name | Value |! +----------------------------+-----------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 693046 |! | Handler_read_last | 0 |! | Handler_read_next | 254373071 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-----------+! © 2014 PERCONA Unfortunately, this seems to be O(n2)
  • 64.
    Profile: the Exclusion-JoinSolution +----------------------+-----------+! | Status | Duration |! +----------------------+-----------+! | starting | 0.000147 |! | checking permissions | 0.000009 |! | checking permissions | 0.000004 |! | checking permissions | 0.000009 |! | Opening tables | 0.000038 |! | init | 0.000049 |! | System lock | 0.000032 |! | optimizing | 0.000025 |! | statistics | 0.000195 |! | preparing | 0.000045 |! | executing | 0.000006 |! | Sending data | 71.195693 |! | end | 0.000021 |! | query end | 0.000021 |! | closing tables | 0.000017 |! | freeing items | 0.000864 |! | logging slow query | 0.000135 |! | cleaning up | 0.000027 |! +----------------------+-----------+! © 2014 PERCONA A lot of time is spent moving rows around
  • 65.
    © 2014 PERCONADerived-Table Solution SELECT tv.title, ep.title, ep.episode_nr! FROM (! SELECT kind_id, episode_of_id, ! MAX(episode_nr) AS episode_nr! FROM title! WHERE kind_id = 7! GROUP BY kind_id, episode_of_id! ) maxep! JOIN title ep USING (kind_id, episode_of_id, episode_nr)! JOIN title tv ON tv.id = ep.episode_of_id;! 0.60s Generate a list of the greatest episode number per show
  • 66.
    Indexes: the Derived-TableSolution CREATE INDEX k_ep_nr ! ON title (kind_id, episode_of_id, episode_nr);! © 2014 PERCONA
  • 67.
    EXPLAIN: the Derived-TableSolution id select_type table type key ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL 751752 Using where 1 PRIMARY tv eq_ref PRIMARY maxep.episode_of_id 1 NULL 1 PRIMARY ep ref k_ep_nr maxep.kind_id, © 2014 PERCONA maxep.episode_id, maxep.episode_nr 2 NULL 2 DERIVED >tle range k_ep_nr NULL 24544 Using where; Using index for group-­‐ by
  • 68.
    © 2014 PERCONAVisual Explain
  • 69.
    Status: the Derived-TableSolution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 110312 |! | Handler_read_last | 1 |! | Handler_read_next | 28989 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 30324 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 30323 |! +----------------------------+--------+! © 2014 PERCONA Evidence of a temporary table, even though EXPLAIN didn’t report it
  • 70.
    Profile: the Derived-TableSolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000600 |! | checking permissions | 0.000013 |! | checking permissions | 0.000007 |! | checking permissions | 0.000009 |! | Opening tables | 0.000111 |! | init | 0.000037 |! | System lock | 0.000027 |! | optimizing | 0.000006 |! | optimizing | 0.000011 |! | statistics | 0.000196 |! | preparing | 0.000030 |! | Sorting result | 0.000012 |! | statistics | 0.000057 |! | preparing | 0.000023 |! | executing | 0.000016 |! | Sending data | 0.000010 |! © 2014 PERCONA | executing | 0.000004 |! | Sending data | 0.607434 |! | end | 0.000020 |! | query end | 0.000028 |! | closing tables | 0.000005 |! | removing tmp table | 0.000014 |! | closing tables | 0.000070 |! | freeing items | 0.000235 |! | cleaning up | 0.000029 |! +----------------------+----------+! Evidence of a temporary table, even though EXPLAIN didn’t report it
  • 71.
    Summary: Greatest perGroup Solu7on Time Notes Exclusion-­‐join solu>on 71.20 Bad when each group has © 2014 PERCONA many entries. Derived-­‐table solu>on 0.60s
  • 72.
    Query Patterns DYNAMICPIVOT © 2014 PERCONA
  • 73.
    © 2014 PERCONAAssignment: “I want the count of movies, TV, and video games per year—in columns.”
  • 74.
    © 2014 PERCONANot Like This SELECT k.kind, t.production_year, COUNT(*) AS Count! FROM kind_type k! JOIN title t ON k.id = t.kind_id ! WHERE production_year BETWEEN 2005 AND 2009! GROUP BY k.id, t.production_year;! ! +-------------+-----------------+--------+! | kind | production_year | Count |! +-------------+-----------------+--------+! | movie | 2005 | 13807 |! | movie | 2006 | 13916 |! | movie | 2007 | 14494 |! | movie | 2008 | 18354 |! | movie | 2009 | 23714 |! | tv series | 2005 | 3248 |! | tv series | 2006 | 3588 |! | tv series | 2007 | 3361 |! | tv series | 2008 | 3026 |! | tv series | 2009 | 2572 |!
  • 75.
    © 2014 PERCONALike This +----------------+-----------+-----------+-----------+-----------+-----------+! | kind | Count2005 | Count2006 | Count2007 | Count2008 | Count2009 |! +----------------+-----------+-----------+-----------+-----------+-----------+! | episode | 36138 | 24745 | 22335 | 16448 | 12917 |! | movie | 13807 | 13916 | 14494 | 18354 | 23714 |! | tv movie | 3541 | 3561 | 3586 | 3025 | 2778 |! | tv series | 3248 | 3588 | 3361 | 3026 | 2572 |! | video game | 383 | 367 | 310 | 300 | 215 |! | video movie | 7693 | 7671 | 6955 | 5808 | 4090 |! +----------------+-----------+-----------+-----------+-----------+-----------+!
  • 76.
    © 2014 PERCONADo It in One Pass SELECT k.kind,! SUM(production_year=2005) AS Count2005, ! SUM(production_year=2006) AS Count2006, ! SUM(production_year=2007) AS Count2007, ! SUM(production_year=2008) AS Count2008, ! SUM(production_year=2009) AS Count2009 ! FROM title t! JOIN kind_type k ON k.id = t.kind_id! GROUP BY t.kind_id ORDER BY NULL;! ! 0.77s SUM of 1’s = COUNT where condition is true
  • 77.
    Indexes: the One-PassSolution CREATE INDEX k_py ! ON title (kind_id, production_year);! © 2014 PERCONA
  • 78.
    EXPLAIN: the One-PassSolution id select_type table type key ref rows Extra 1 SIMPLE k index kind NULL 7 Using index; © 2014 PERCONA Using temporary 1 SIMPLE t ref k_py k.id 167056 Using index reading title table second unfortunately causes the group by to create a temp table
  • 79.
    © 2014 PERCONAVisual Explain
  • 80.
    Status: the One-PassSolution +----------------------------+---------+! | Variable_name | Value |! +----------------------------+---------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 1 |! | Handler_read_key | 1543727 |! | Handler_read_last | 0 |! | Handler_read_next | 1543726 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 7 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 1543713 |! | Handler_write | 6 |! +----------------------------+---------+! © 2014 PERCONA title table has 1.5M rows; that’s how many times it increments counts in the temp table
  • 81.
    Profile: the One-PassSolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000162 |! | checking permissions | 0.000034 |! | checking permissions | 0.000011 |! | Opening tables | 0.000043 |! | init | 0.000052 |! | System lock | 0.000030 |! | optimizing | 0.000017 |! | statistics | 0.000112 |! | preparing | 0.000025 |! | Creating tmp table | 0.000069 |! | executing | 0.000009 |! | Sending data | 0.772317 |! | end | 0.000025 |! | query end | 0.000022 |! | removing tmp table | 0.000036 |! | query end | 0.000007 |! © 2014 PERCONA | closing tables | 0.000018 |! | freeing items | 0.000485 |! | cleaning up | 0.000030 |! +----------------------+----------+! majority of time spent building temp table
  • 82.
    © 2014 PERCONAOne-Pass with Straight-Join Optimizer Override SELECT STRAIGHT_JOIN k.kind,! SUM(production_year=2005) AS Count2005, ! SUM(production_year=2006) AS Count2006, ! SUM(production_year=2007) AS Count2007, ! SUM(production_year=2008) AS Count2008, ! SUM(production_year=2009) AS Count2009 ! FROM title t! JOIN kind_type k ON k.id = t.kind_id! GROUP BY t.kind_id ORDER BY NULL;! ! 7.18s
  • 83.
    Indexes: the Straight-JoinSolution CREATE INDEX k_py ! ON title (kind_id, production_year);! © 2014 PERCONA
  • 84.
    EXPLAIN: the Straight-JoinSolution id select_type table type key ref rows Extra 1 SIMPLE t index k_py NULL 1537429 Using index 1 SIMPLE k eq_ref PRIMARY t.kind_id 1 © 2014 PERCONA no "Using temporary" because forcing title table to be read first means it scans the index in index order, avoiding the temp table—in this case
  • 85.
    © 2014 PERCONAVisual Explain
  • 86.
    Status: the Straight-JoinSolution +----------------------------+---------+! | Variable_name | Value |! +----------------------------+---------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 4 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 1 |! | Handler_read_key | 7 |! | Handler_read_last | 0 |! | Handler_read_next | 1543719 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+---------+! ! © 2014 PERCONA really one-pass
  • 87.
    Profile: the Straight-JoinSolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000172 |! | checking permissions | 0.000176 |! | checking permissions | 0.000012 |! | Opening tables | 0.000169 |! | init | 0.000067 |! | System lock | 0.000034 |! | optimizing | 0.000018 |! | statistics | 0.000055 |! | preparing | 0.000036 |! | Sorting result | 0.000016 |! | executing | 0.000007 |! | Sending data | 7.277106 |! | end | 0.000022 |! | query end | 0.000024 |! | closing tables | 0.000019 |! | freeing items | 0.000236 |! © 2014 PERCONA | cleaning up | 0.000026 |! +----------------------+----------+! no temporary table! majority of time spent just moving rows
  • 88.
    © 2014 PERCONAScalar Subquery Solution 0.001 SELECT k.kind,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2005) AS Count2005,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2006) AS Count2006,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2007) AS Count2007,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2008) AS Count2008,! (SELECT COUNT(*) FROM title WHERE kind_id = k.id AND production_year = 2009) AS Count2009! FROM kind_type k;!
  • 89.
    Indexes: the ScalarSubquery Solution CREATE INDEX k_py ! ON title (kind_id, production_year)! ! CREATE UNIQUE INDEX kind ! ON kind_type (kind);! © 2014 PERCONA
  • 90.
    EXPLAIN: the ScalarSubquery Solution id select_type table type key ref rows Extra 1 PRIMARY k index kind NULL 7 Using index 6 DEPENDENT SUBQUERY © 2014 PERCONA >tle ref k_py k.id, const 1781 Using index 5 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index 4 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index 3 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index 2 DEPENDENT SUBQUERY >tle ref k_py k.id, const 1781 Using index
  • 91.
    © 2014 PERCONAVisual Explain
  • 92.
    Status: the ScalarSubquery Solution +----------------------------+--------+! | Variable_name | Value |! +----------------------------+--------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 12 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 1 |! | Handler_read_key | 36 |! | Handler_read_last | 0 |! | Handler_read_next | 262953 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+--------+! © 2014 PERCONA good use of indexes
  • 93.
    Profile: the ScalarSubquery Solution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | checking permissions | 0.000005 |! | checking permissions | 0.000010 |! | Opening tables | 0.000143 |! | init | 0.000071 |! | System lock | 0.000047 |! | optimizing | 0.000007 |! | statistics | 0.000022 |! | preparing | 0.000020 |! | executing | 0.000005 |! | Sending data | 0.000620 |! . . .! | executing | 0.000013 |! | Sending data | 0.001620 |! | executing | 0.000008 |! | Sending data | 0.000978 |! | executing | 0.000010 |! | Sending data | 0.000384 |! | end | 0.000011 |! | query end | 0.000027 |! | closing tables | 0.000019 |! | freeing items | 0.000460 |! | cleaning up | 0.000030 |! +----------------------+----------+! © 2014 PERCONA scary long profile, but still fast
  • 94.
    © 2014 PERCONASummary: Dynamic Pivot Solu7on Time Notes One-­‐pass solu>on 0.77s Straight-­‐join solu>on 7.18s was much bejer in 5.5 Scalar Subquery solu>on 0.001s
  • 95.
    Query Patterns RELATIONALDIVISION © 2014 PERCONA
  • 96.
    © 2014 PERCONAAssignment: “I want to see movies with all three of keywords espionage, nuclear-bomb, and ejector-seat.”
  • 97.
    Not Movies withOne Keyword SELECT t.title, k.keyword FROM keyword k ! JOIN movie_keyword mk ON k.id = mk.keyword_id ! JOIN title t ON mk.movie_id = t.id ! WHERE k.keyword IN ('espionage', 'nuclear-bomb', 'ejector-seat');! ! +--------------------------+--------------+! | title | keyword |! +--------------------------+--------------+! | 2 Fast 2 Furious | ejector-seat |! | Across the Pacific | espionage |! | Action in Arabia | espionage |! . . .! | You Only Live Twice | espionage |! | Zombie Genocide | nuclear-bomb |! | Zombies of the Strat | espionage |! +--------------------------+--------------+! 705 rows in set (12.97 sec)! © 2014 PERCONA
  • 98.
    © 2014 PERCONAThis Won’t Work SELECT t.title, k.keyword ! FROM keyword k ! JOIN movie_keyword mk ON k.id = mk.keyword_id ! JOIN title t ON mk.movie_id = t.id ! WHERE k.keyword = 'espionage'! AND k.keyword = 'nuclear-bomb'! AND k.keyword = 'ejector-seat';! ! 0 rows in set (12.97 sec)! ! It’s impossible for one column to have three values on a given row
  • 99.
    © 2014 PERCONAOnly Movies with All Three +------------+-------------------------------------+! | title | keywords |! +------------+-------------------------------------+! | Goldfinger | ejector-seat,espionage,nuclear-bomb |! +------------+-------------------------------------+!
  • 100.
    © 2014 PERCONAGroup-by Solution 0.02s SELECT t.title, GROUP_CONCAT(k.keyword) AS keywords! FROM title t ! JOIN movie_keyword mk ON t.id = mk.movie_id ! JOIN keyword k ON k.id = mk.keyword_id! WHERE k.keyword IN ! ('espionage', 'nuclear-bomb', 'ejector-seat')! GROUP BY mk.movie_id! HAVING COUNT(DISTINCT mk.keyword_id) = 3 ! ORDER BY NULL;! !
  • 101.
    © 2014 PERCONAIndexes CREATE INDEX k_i! ON keyword (keyword, id);! ! CREATE INDEX k_m! ON movie_keyword (keyword_id, movie_id);!
  • 102.
    EXPLAIN: the Group-bySolution id select_type table type key ref rows Extra 1 SIMPLE k range k_i NULL 3 Using where; © 2014 PERCONA Using index; Using temporary; Using filesort 1 SIMPLE mk ref k_m k.id 23 Using index 1 SIMPLE t eq_ref PRIMARY mk.movie_id 1 NULL
  • 103.
    © 2014 PERCONAVisual Explain
  • 104.
    Status: the Group-bySolution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 6 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 710 |! | Handler_read_last | 0 |! | Handler_read_next | 708 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 705 |! | Handler_read_rnd_next | 706 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 705 |! +----------------------------+-------+! © 2014 PERCONA building and reading a temporary table
  • 105.
    Profile: the Group-bySolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000267 |! | checking permissions | 0.000010 |! | checking permissions | 0.000004 |! | checking permissions | 0.000008 |! | Opening tables | 0.000041 |! | init | 0.000078 |! | System lock | 0.000037 |! | optimizing | 0.000024 |! | statistics | 0.000166 |! | preparing | 0.000028 |! | Creating tmp table | 0.000199 |! | Sorting result | 0.000013 |! | executing | 0.000005 |! | Sending data | 0.009532 |! © 2014 PERCONA | Creating sort index | 0.010310 |! | end | 0.000066 |! | query end | 0.000027 |! | removing tmp table | 0.000176 |! | query end | 0.000021 |! | removing tmp table | 0.000010 |! | query end | 0.000006 |! | closing tables | 0.000022 |! | freeing items | 0.000016 |! | removing tmp table | 0.000007 |! | freeing items | 0.000009 |! | removing tmp table | 0.000005 |! | freeing items | 0.000449 |! | cleaning up | 0.000028 |! +----------------------+----------+! building & tearing down temp table
  • 106.
    © 2014 PERCONASelf-Join Solution SELECT t.title, CONCAT_WS(',', k1.keyword, k2.keyword, k3.keyword) AS keywords ! FROM title t ! JOIN movie_keyword mk1 ON t.id = mk1.movie_id ! JOIN keyword k1 ON k1.id = mk1.keyword_id ! JOIN movie_keyword mk2 ON mk1.movie_id= mk2.movie_id ! JOIN keyword k2 ON k2.id = mk2.keyword_id ! JOIN movie_keyword mk3 ON mk1.movie_id = mk3.movie_id ! JOIN keyword k3 ON k3.id = mk3.keyword_id ! WHERE (k1.keyword, k2.keyword, k3.keyword) ! = ('espionage', 'nuclear-bomb', 'ejector-seat');! 0.015s
  • 107.
    EXPLAIN: the Self-JoinSolution id select_type table type key ref rows Extra 1 SIMPLE k1 ref k_i const 1 Using index 1 SIMPLE k2 ref k_i const 1 Using index 1 SIMPLE k3 ref k_i const 1 Using index 1 SIMPLE mk1 ref k_m k1.id 17 Using index 1 SIMPLE t eq_ref PRIMARY mk1.movie_id 1 NULL 1 SIMPLE mk2 ref k_m k2.id, © 2014 PERCONA mk1.movie_id 1 Using index 1 SIMPLE mk3 ref k_m k3.id, mk1.movie_id 1 Using index
  • 108.
    © 2014 PERCONAVisual Explain
  • 109.
    Status: the Self-JoinSolution +----------------------------+-------+! | Variable_name | Value |! +----------------------------+-------+! | Handler_commit | 1 |! | Handler_delete | 0 |! | Handler_discover | 0 |! | Handler_external_lock | 14 |! | Handler_mrr_init | 0 |! | Handler_prepare | 0 |! | Handler_read_first | 0 |! | Handler_read_key | 1218 |! | Handler_read_last | 0 |! | Handler_read_next | 613 |! | Handler_read_prev | 0 |! | Handler_read_rnd | 0 |! | Handler_read_rnd_next | 0 |! | Handler_rollback | 0 |! | Handler_savepoint | 0 |! | Handler_savepoint_rollback | 0 |! | Handler_update | 0 |! | Handler_write | 0 |! +----------------------------+-------+! © 2014 PERCONA minimal rows, good index usage
  • 110.
    Profile: the Self-JoinSolution +----------------------+----------+! | Status | Duration |! +----------------------+----------+! | starting | 0.000205 |! | checking permissions | 0.000011 |! | checking permissions | 0.000006 |! | checking permissions | 0.000005 |! | checking permissions | 0.000005 |! | checking permissions | 0.000005 |! | checking permissions | 0.000005 |! | checking permissions | 0.000010 |! | Opening tables | 0.000061 |! | init | 0.000069 |! | System lock | 0.000056 |! | optimizing | 0.000029 |! | statistics | 0.000106 |! | preparing | 0.000049 |! | executing | 0.000008 |! | Sending data | 0.013485 |! © 2014 PERCONA | end | 0.000018 |! | query end | 0.000022 |! | closing tables | 0.000020 |! | freeing items | 0.000683 |! | cleaning up | 0.000034 |! +----------------------+----------+! who says joins are slow?
  • 111.
    Summary: Relational DivisionSolu7on Time Notes Group-­‐by solu>on 0.02s much improved in 5.7 Self-­‐join solu>on 0.015s © 2014 PERCONA
  • 112.
    Query Patterns PERFORMANCESCHEMA © 2014 PERCONA
  • 113.
    © 2014 PERCONAPerformance Schema • New tools to instrument and profile queries. – Use PERFORMANCE_SCHEMA with MySQL 5.6+ • SHOW PROFILES is deprecated in 5.6.7+ and will be removed in a future release
  • 114.
    © 2014 PERCONASys Schema • Get the MySQL-sys schema for handy views, functions, and procedures. – https://github.com/MarkLeith/mysql-sys
  • 115.
    © 2014 PERCONASys Schema Caveats • However, it’s still incomplete and tricky… – Installation fails; the views reference some P_S tables that don’t exist yet as of MySQL 5.7.5 (e.g. memory_%). – Documentation includes at least one sys procedure that isn’t in the download (ps_trace_statement_digest()). • We all need to use it and give feedback to Mark!
  • 116.
    © 2014 PERCONASys Schema Setup mysql> SOURCE sys_57.sql;! ! mysql> CALL sys.ps_setup_enable_instrument('stage/%');! +-------------------------+! | summary |! +-------------------------+! | Enabled 108 instruments |! +-------------------------+! ! mysql> CALL sys.ps_truncate_all_tables(false);! +---------------------+! | summary |! +---------------------+! | Truncated 31 tables |! +---------------------+! !
  • 117.
    © 2014 PERCONAStatement Analysis mysql> select * from sys.statement_analysis limit 1G! *************************** 1. row ***************************! query: SELECT `t` . `title` FROM `ti ... id` AND `c` . `role_id` = ? ) ! db: imdb! full_scan: ! exec_count: 1! err_count: 0! warn_count: 0! total_latency: 2.27 s! max_latency: 2.27 s! avg_latency: 2.27 s! lock_latency: 296.00 us! rows_sent: 6056! rows_sent_avg: 6056! rows_examined: 180432! rows_examined_avg: 180432! tmp_tables: 0! tmp_disk_tables: 0! rows_sorted: 0! sort_merge_passes: 0! digest: 4f8e3695ecf7c8518a1e1defe2ff323c! first_seen: 2014-09-28 02:21:21! last_seen: 2014-09-28 02:21:21! similar to pt-query-digest output with Percona’s verbose slow query log
  • 118.
    SHOW PROFILE Workalikemysql> select * from sys.x$user_summary_by_stages;! +------+--------------------------------+-------+---------------+-----------+! | user | event_name | total | wait_sum | wait_avg |! +------+--------------------------------+-------+---------------+-----------+! | root | stage/sql/Sending data | 93246 | 2053496638000 | 22022000 |! | root | stage/sql/executing | 93247 | 214310855000 | 2298000 |! | root | stage/sql/System lock | 27 | 6200477000 | 229647000 |! | root | stage/sql/Opening tables | 126 | 3940994000 | 31277000 |! | root | stage/sql/checking permissions | 31 | 2309620000 | 74503000 |! | root | stage/sql/closing tables | 126 | 697965000 | 5539000 |! | root | stage/sql/statistics | 3 | 452872000 | 150957000 |! | root | stage/sql/freeing items | 2 | 421142000 | 210571000 |! | root | stage/sql/query end | 126 | 384577000 | 3052000 |! | root | stage/sql/starting | 2 | 271533000 | 135766000 |! | root | stage/sql/preparing | 3 | 119699000 | 39899000 |! | root | stage/sql/init | 3 | 82758000 | 27586000 |! | root | stage/sql/optimizing | 4 | 49758000 | 12439000 |! | root | stage/sql/removing tmp table | 1 | 8568000 | 8568000 |! | root | stage/sql/cleaning up | 2 | 7443000 | 3721000 |! | root | stage/sql/Sorting result | 1 | 7039000 | 7039000 |! | root | stage/sql/end | 2 | 6986000 | 3493000 |! +------+--------------------------------+-------+---------------+-----------+! © 2014 PERCONA totals for all queries by user, not just in current session
  • 119.
  • 120.
    © 2014 PERCONAConclusions • Use all tools to measure query performance – EXPLAIN – Session Status – Query Profiler – Performance Schema and Sys Schema • Test with real-world data, because the best solution depends on the volume of data you’re querying. • Allocate enough memory to buffers so the indexes you need stay resident in RAM.
  • 121.
    © 2014 PERCONALicense and Copyright Copyright 2014 Percona Released under a Creative Commons 3.0 License: http://creativecommons.org/licenses/by-nc-nd/3.0/ You are free to share—to copy, distribute and transmit this work, under the following conditions: Attribution. You must attribute this work to Percona Noncommercial. You may not use this work for commercial purposes. No Derivative Works. You may not alter, transform, or build upon this work.
[8]ページ先頭
©2009-2025 Movatter.jp