Introduction into MySQL Query Tuning for Dev[Op]s

MySQL Query Tuning for Dev[Op]s. Introduction

•Basics •Indexes How Do They Work? When MySQL Uses Indexes Optimizer Histograms •Diagnostics EXPLAIN: How Optimizer Works Real Numbers: Inside Storage Engine Real Numbers: Inside the Server •How to Affect Query Plans Table of Contents 2 ©2021 Percona

cursor = conn.cursor() q = ’’’UPDATE ‘foo‘ SET my_date=NOW(), subject = %s, msg = %s, address = %s, updated_at = NOW() WHERE id=%s ’’’ cursor.execute(q, [ remote_resp.get(’subject’), remote_resp.get(’msg’), remote_resp.get(’address’), my_id ]) The Query 7 ©2021 Percona

SELECT dept_name, title, gender, min(salary) AS mins, max(salary) AS maxs FROM employees JOIN salaries USING(emp_no) JOIN titles USING(emp_no) JOIN dept_emp USING(emp_no) JOIN departments USING(dept_no) JOIN dept_manager USING(dept_no) WHERE dept_manager.to_date = ’9999-01-01’ GROUP BY dept_name, title, gender ORDER BY gender, maxs DESC; The Query 7 ©2021 Percona

• Mind your data! • 75,000,000 rows • (INT, INT) 75,000,000 * (4 + 4) = 600,000,000 B = 572 MB • (INT, INT, DATETIME, VARCHAR(255), VARCHAR(255)) 75,000,000 * (4 + 4 + 8 + 256 + 256) = 39,600,000,000 bytes = 37 G • 39,600,000,000 / 600,000,000 = 66 Slow is Relative 9 ©2021 Percona

• MySQL performs a job to execute it • In the worst case scenario it will do a full table scan • CREATE INDEX • ANALYZE TABLE ... UPDATE HISTOGRAM ON ... • Incorrect index can be used Why is the Query Slow 10 ©2021 Percona

• Selecting a lot of data • SELECT * FROM many columns table • Badly written • LEFT JOIN instead of INNER JOIN • Many values in IN() • Retrieving large result set, then discarding • Not effective SQL For particular MySQL version You May not Be Able to Change a Slow Query 12 ©2021 Percona

• Selecting a lot of data • SELECT * FROM many columns table • Badly written • LEFT JOIN instead of INNER JOIN • Many values in IN() • Retrieving large result set, then discarding • Not effective SQL For particular MySQL version • We can still improve performance You May not Be Able to Change a Slow Query 12 ©2021 Percona

d001 d003 d008 d009 d003****** d009****** d008****** d009****** d001****** d003****** d009****** d008****** d009****** d001****** d008****** d008****** MySQL Indexes - B-Tree Mostly - LSM Tree - Fractal Tree - R-Tree Spatial - Hash Memory SE - Engine’s 17 ©2021 Percona

Indexes When MySQL Uses Indexes

• WHERE left part = value1 AND right part = value2 • WHERE left part = value1 OR right part = value2 • WHERE right part = value1 AND left part = value2 • WHERE right part = value1 OR left part = value2 Conditions 20 ©2021 Percona

5.7 ORDER BY left part DESC, right part ASC 8.0 ORDER BY left part DESC, right part ASC • left part must be descending • right part must be ascending • the index(left part DESC, right part ASC) ORDER BY 23 ©2021 Percona

• Deterministic, built-in • Return same value for the same argument • WHERE the column = FLOOR(123.45) • Non-deterministic • Return different values for different invocations • WHERE the column = RAND() ∗ 100 Expressions 24 ©2021 Percona

• Deterministic, built-in • Return same value for the same argument • WHERE the column = FLOOR(123.45) • Non-deterministic • Return different values for different invocations • WHERE the column = RAND() ∗ 100 • Stored functions and UDFs • Indexes are not used Use generated column indexes Expressions 24 ©2021 Percona

• Identify queries not using indexes • Status variables mysql select * from performance_schema.session_status - where variable_name in (’Created_tmp_tables’, - ’Created_tmp_disk_tables’, ’Select_full_join’, - ’Select_full_range_join’, ’Select_range’, - ’Select_range_check’, ’Select_scan’, - ’Sort_merge_passes’, ’Sort_range’, ’Sort_rows’, - ’Sort_scan’) and variable_value 0; +------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------------+----------------+ | Select_scan | 2 | +------------------------+----------------+ 1 row in set (0,00 sec) When to Add Indexes? 25 ©2021 Percona

• Identify queries not using indexes mysql show global status like ’Handler_read%’; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 31 | | Handler_read_key | 1909 | | Handler_read_last | 0 | | Handler_read_next | 4393 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 1135 | +-----------------------+-------+ When to Add Indexes? 25 ©2021 Percona

• Identify queries not using indexes • PERFORMANCE SCHEMA.EVENTS STATEMENTS * mysql select * from events_statements_history_long - where sql_text like ’select count(*) from employees join %’G *************************** 1. row **************************** ... ROWS_SENT: 1 SELECT_RANGE_CHECK: 0 ROWS_EXAMINED: 541058 SELECT_SCAN: 1 CREATED_TMP_DISK_TABLES: 0 SORT_MERGE_PASSES: 0 CREATED_TMP_TABLES: 0 SORT_RANGE: 0 SELECT_FULL_JOIN: 0 SORT_ROWS: 0 SELECT_FULL_RANGE_JOIN: 0 SORT_SCAN: 0 SELECT_RANGE: 0 NO_INDEX_USED: 0 When to Add Indexes? 25 ©2021 Percona

• Identify queries not using indexes • Slow query log # Time: 2020-10-11T23:34:03.701871Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 506 # Query_time: 0.024106 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 1000 SET timestamp=1602459243; SELECT c FROM sbtest1 WHERE id=996; When to Add Indexes? 25 ©2021 Percona

• Identify queries not using indexes • Analyze if adding an index will help • Add the index • Test first! • ADD INDEX is an expensive operation Use pt-online-schema-change When to Add Indexes? 25 ©2021 Percona

• Since version 8.0 • Collected and used by the Optimizer • Visible in Information Schema mysql select HISTOGRAM from information_schema.column_statistics - where table_name=’example’G *************************** 1. row *************************** HISTOGRAM: {buckets: [[1, 0.6], [2, 0.8], [3, 1.0]], data-type: int, null-values: 0.0, collation-id: 8, last-updated: 2018-11-07 09:07:19.791470, sampling-rate: 1.0, histogram-type: singleton, number-of-buckets-specified: 3} 1 row in set (0.00 sec) Histogram Statistics 27 ©2021 Percona

• Since version 8.0 • Collected and used by the Optimizer • Visible in Information Schema • Affects query execution plan, but not access • SELECT ... FROM a JOIN b vs SELECT ... FROM b JOIN a Histogram Statistics 27 ©2021 Percona

• Data distribution is far from uniform • Query accesses two or more tables • Indexes cannot be used • Maintaining them is too expensive • They are already used for different conditions Create Histograms 32 ©2021 Percona

Diagnostics EXPLAIN: How Optimizer Works

• EXPLAIN • Estimates the query execution plan 5.6- EXTENDED 5.6- PARTITIONS 5.6+ FORMAT=JSON 8.0+ FORMAT=TREE • INFORMATION SCHEMA.OPTIMIZER TRACE • Real data, collected when query was executing • Advanced topic How to Find how MySQL Uses Indexes 35 ©2021 Percona

mysql EXPLAIN SELECT first_name, last_name, title, salary FROM employees - JOIN salaries USING(emp_no) JOIN titles USING(emp_no) - WHERE salary = (SELECT MAX(salary) FROM salaries - JOIN titles WHERE titles.to_date CURDATE()); +----+-------------+-----------+------------+------+---------------+---------+---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | +----+-------------+-----------+------------+------+---------------+---------+---------+ | 1 | PRIMARY | employees | NULL | ALL | PRIMARY | NULL | NULL ... | 1 | PRIMARY | salaries | NULL | ref | PRIMARY | PRIMARY | 4 ... | 1 | PRIMARY | titles | NULL | ref | PRIMARY | PRIMARY | 4 ... | 2 | SUBQUERY | titles | NULL | ALL | NULL | NULL | NULL ... | 2 | SUBQUERY | salaries | NULL | ALL | NULL | NULL | NULL ... +----+-------------+-----------+------------+------+---------------+---------+---------+ +----+-...-+----------------------------+---------+----------+-------------------------------+ | id | ... | ref | rows | filtered | Extra | +----+-...-+----------------------------+---------+----------+-------------------------------+ | 1 | ... | NULL | 299113 | 100.00 | NULL | | 1 | ... | employees.employees.emp_no | 9 | 10.00 | Using where | | 1 | ... | employees.employees.emp_no | 1 | 100.00 | Using index | | 2 | ... | NULL | 442189 | 33.33 | Using where | | 2 | ... | NULL | 2838426 | 100.00 | Using join buffer (hash join) | +----+-...-+----------------------------+---------+----------+-------------------------------+ 5 rows in set, 1 warning (0,01 sec) 299113 * 9 * 1 * 442189 * 2838426 = 3,378,806,408,204,514,738 EXPLAIN: rows 36 ©2021 Percona

ALL Read all rows index Read all rows from the index range Range: N ... M subquery Subqueries merge Combination of two indexes ref Comparision with single value ref or null ref eq ref Query Execution Type 38 ©2021 Percona

ALL Read all rows index Read all rows from the index range Range: N ... M subquery Subqueries merge Combination of two indexes ref Comparision with single value fulltext Fulltext index Query Execution Type 38 ©2021 Percona

ALL Read all rows index Read all rows from the index range Range: N ... M subquery Subqueries merge Combination of two indexes ref Comparision with single value fulltext Fulltext index const Single row Query Execution Type 38 ©2021 Percona

ALL Read all rows index Read all rows from the index range Range: N ... M subquery Subqueries merge Combination of two indexes ref Comparision with single value fulltext Fulltext index const Single row system Table has single row Query Execution Type 38 ©2021 Percona

mysql EXPLAIN SELECT first_name, last_name, title, salary FROM employees - JOIN salaries USING(emp_no) JOIN titles USING(emp_no) - WHERE salary IN (SELECT MAX(salary) FROM salaries - JOIN titles WHERE titles.to_date CURDATE()); ... 5 rows in set, 1 warning (0,01 sec) mysql SHOW WARNINGSG *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select ‘employees‘.‘employees‘.‘first_name‘ AS ‘first_name‘, ‘employees‘.‘employees‘.‘last_name‘ AS ‘last_name‘,‘employees‘.‘salaries‘.‘salary‘ AS ‘salary‘ from ‘employees‘.‘employees‘ join ‘employees‘.‘salaries‘ where ((‘employees‘.‘salaries‘.‘emp_no‘ = ‘employees‘.‘employees‘.‘emp_no‘) and in_optimizer(‘employees‘.‘salaries‘.‘salary‘,‘employees‘.‘salaries‘.‘salary‘ in ( materialize (/* select#2 */ select max(‘employees‘.‘salaries‘.‘salary‘) from ‘employees‘.‘salaries‘ join ‘employees‘.‘titles‘ where (‘employees‘.‘titles‘.‘to_date‘ cache(curdate())) having true ), primary_index_lookup(‘employees‘.‘salaries‘.‘salary‘ in temporary table on auto_distinct_key where ((‘employees‘.‘salaries‘.‘salary‘ = ‘materialized_subquery‘.‘MAX(salary)‘)))))) 1 row in set (0,00 sec) Actual Query 41 ©2021 Percona

mysql explain select * from t1G *************************** 1. row *************************** ... rows: 12 Extra: NULL mysql explain select * from t1 where f2=12G *************************** 1. row *************************** ... key: NULL ... rows: 12 Extra: Using where Effect of Indexes: Before 42 ©2021 Percona

mysql alter table t1 add index(f2); Query OK, 12 rows affected (0.07 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql explain select * from t1 where f2=12G *************************** 1. row *************************** ... key: f2 key_len: 5 ref: const rows: 1 Extra: NULL 1 row in set (0.00 sec) Effect of Indexes: After 43 ©2021 Percona

Diagnostics Real Numbers: Inside Storage Engine

• EXPLAIN is optimistic mysql explain select * from ol - where thread_id=10432 and site_id != 9939 - order by id limit 3G *************************** 1. row *************************** id: 1 | ref: NULL select_type: SIMPLE | rows: 33 table: ol | filtered: 8.07 partitions: NULL | Extra: Using where type: index possible_keys: thread_id key: PRIMARY key_len: 4 1 row in set, 1 warning (0,00 sec) Handler * Status Variables 45 ©2021 Percona

Diagnostics Real Numbers: Inside the Server

• Can be seen in the PROCESSLIST mysql show processlistG ************************ 1. row ************************ Id: 7 User: root Host: localhost:48799 db: employees Command: Query Time: 2 State: Sending data Info: select count(*) from employees join titles using(emp_no) where title=’Senior Engineer’ ... Execution Stages 48 ©2021 Percona

• PERFORMANCE SCHEMA.EVENTS STAGES * mysql select eshl.event_name, substr(sql_text, 1, 15) ‘sql‘, - eshl.timer_wait/1000000000000 w_s - from events_stages_history_long - eshl join events_statements_history_long esthl on - (eshl.nesting_event_id = esthl.event_id) where - esthl.current_schema=’employees’ and sql_text like - ’select count(*) from employees%’ - order by eshl.timer_start asc; +--------------------------------+-----------------+--------+ | event_name | sql | w_s | +--------------------------------+-----------------+--------+ | stage/sql/starting | select count(*) | 0.0002 | | stage/sql/checking permissions | select count(*) | 0.0000 | ... Execution Stages 48 ©2021 Percona

• Status variables mysql flush status; Query OK, 0 rows affected (0,01 sec) mysql select count(*) from employees join titles using(emp_no) - where title=’Senior Engineer’; +----------+ | count(*) | +----------+ | 97750 | +----------+ 1 row in set (5,44 sec) Temporary Tables and Other Job 49 ©2021 Percona

• Status variables mysql select * from performance_schema.session_status - where variable_name in (’Created_tmp_tables’, - ’Created_tmp_disk_tables’, ’Select_full_join’, - ’Select_full_range_join’, ’Select_range’, - ’Select_range_check’, ’Select_scan’, ’Sort_merge_passes’, - ’Sort_range’, ’Sort_rows’, ’Sort_scan’) - and variable_value 0; +------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------------+----------------+ | Select_scan | 2 | +------------------------+----------------+ 1 row in set (0,00 sec) Temporary Tables and Other Job 49 ©2021 Percona

• PERFORMANCE SCHEMA.EVENTS STATEMENTS * mysql select * from events_statements_history_long - where sql_text like - ’select count(*) from employees join %’G *************************** 1. row **************************** ... ROWS_SENT: 1 SELECT_RANGE_CHECK: 0 ROWS_EXAMINED: 541058 SELECT_SCAN: 1 CREATED_TMP_DISK_TABLES: 0 SORT_MERGE_PASSES: 0 CREATED_TMP_TABLES: 0 SORT_RANGE: 0 SELECT_FULL_JOIN: 0 SORT_ROWS: 0 SELECT_FULL_RANGE_JOIN: 0 SORT_SCAN: 0 SELECT_RANGE: 0 NO_INDEX_USED: 0 Temporary Tables and Other Job 49 ©2021 Percona

• sys.statement analysis mysql select * from statement_analysis where query like - ’SELECT COUNT ( * ) FROM ‘emplo%’ and db=’employees’G *************************** 1. row *************************** query: SELECT COUNT ( * ) FROM ‘emplo ... ‘emp_no‘ ) WHE... db: employees max_latency: 5.59 s full_scan: avg_latency: 5.41 s exec_count: 7 lock_latency: 2.24 ms err_count: 0 rows_sent: 7 warn_count: 0 rows_sent_avg: 1 total_latency: 37.89 s rows_examined: 3787406 Temporary Tables and Other Job 49 ©2021 Percona

• sys.statement analysis rows_examined_avg: 541058 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: 4086bc3dc6510a1d9c8f2fe1f59f0943 first_seen: 2016-04-14 15:19:19 last_seen: 2016-04-14 16:13:14 Temporary Tables and Other Job 49 ©2021 Percona

• Can be examined by SHOW INDEX mysql show index from sbtest1; +---------+----------+-------------+-------------+ | Table | Key_name | Column_name | Cardinality | +---------+----------+-------------+-------------+ | sbtest1 | k_1 | k | 49142 | +---------+----------+-------------+-------------+ mysql select count(distinct id), - count(distinct k) from sbtest1; +--------------------+-------------------+ | count(distinct id) | count(distinct k) | +--------------------+-------------------+ | 100000 | 17598 | +--------------------+-------------------+ Index Statistics 52 ©2021 Percona

• Since version 8.0 • Collected and used by the Optimizer • Visible in Information Schema mysql select HISTOGRAM from information_schema.column_statistics - where table_name=’example’G *************************** 1. row *************************** HISTOGRAM: {buckets: [[1, 0.6], [2, 0.8], [3, 1.0]], data-type: int, null-values: 0.0, collation-id: 8, last-updated: 2018-11-07 09:07:19.791470, sampling-rate: 1.0, histogram-type: singleton, number-of-buckets-specified: 3} 1 row in set (0.00 sec) Histogram Statistics 53 ©2021 Percona

mysql select @@optimizer_switchG *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on, mrr=on,mrr_cost_based=on, block_nested_loop=on,batched_key_access=off, materialization=on,semijoin=on,loosescan=on,firstmatch=on, duplicateweedout=on,subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on, derived_merge=on 1 row in set (0,00 sec) Optimizer Switches 54 ©2021 Percona

• Turn ON and OFF particular optimization • Can be unhelpful • Work with them as with any other option • Turn OFF and try SET optimizer_switch = ’use_index_extensions=off’; SELECT ... EXPLAIN SELECT ... Optimizer Switches 54 ©2021 Percona

• Turn ON and OFF particular optimization • Can be unhelpful • Work with them as with any other option • If helps implement in queries SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...; SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...; Optimizer Switches 54 ©2021 Percona

• Optimizer choses wrong plan for no reason • Statistics are up to date • Histograms are not usable • Solution • On every upgrade • Remove index hints • Test if the query improved • You must do it even for minor version upgrades! Bugs in Optimizer 55 ©2021 Percona

• EXPLAIN is essential for query tuning • Real job is done by storage engine • Index statistics affect query execution plan • All index hints, optimizer hints and other workarounds must be validated on each upgrade Summary 56 ©2021 Percona

Introduction into MySQL Query Tuning for Dev[Op]s

More Related Content

What's hot

Similar to Introduction into MySQL Query Tuning for Dev[Op]s

More from Sveta Smirnova

Recently uploaded

Introduction into MySQL Query Tuning for Dev[Op]s