Movatterモバイル変換

Introduction into MySQL Query TuningApril, 17, 2018Sveta Smirnova

•Before We Start•What Aﬀects Query Execution•EXPLAIN: how Optimizer Works•What Really HappenedInside Storage EngineInside the Server•How to Aﬀect Query PlansTable of Contents2

• Enterprise-class•Support•Consulting• Managed Services• SoftwareAbout Percona3

• Enterprise-class• Platforms• MySQL• MariaDB• MongoDB•Other open source databases platformsOn-premiseCloud-basedAbout Percona3

• Enterprise-class• Platforms• Percona optimizes databases to maximizeapplication performanceAbout Percona3

• MySQL Support engineer• Author of• MySQL Troubleshooting• JSON UDF functions• FILTER clause for MySQL• Speaker• Percona Live, OOW, Fosdem,DevConf, HighLoad...Sveta Smirnova4

$system = System::factory()->setName($this->form->get(Field::NAME))->setDescription($this->form->get(Field::DESCRIPTION));DAO::system()->take($system);Slow Query6

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])Slow Query6

SELECT dept_name, title, gender,min(salary) AS mins, max(salary) AS maxsFROM employeesJOIN 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, genderORDER BY gender, maxs DESC;Slow Query6

• PMM QANAllways Tune Raw Query7

• PMM QAN• Slow Query LogAllways Tune Raw Query7

• PMM QAN• Slow Query Log• Application logAllways Tune Raw Query7

• PMM QAN• Slow Query Log• Application log• ...Allways Tune Raw Query7

• MySQL performs a job to execute a queryWhy Query can be Slow8

• MySQL performs a job to execute a query• In worst case scenario it will do a full table scan•CREATE INDEXWhy Query can be Slow8

• MySQL performs a job to execute a query• In worst case scenario it will do a full table scan•CREATE INDEX• Incorrect index can be usedWhy Query can be Slow8

• Mind you data!•75,000,000 rows• (INT, INT)75,000,000 * (4 + 4) = 600,000,000 bytes = 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 = 66Slow is relative9

• Mind you data!• Mind use case• Popular website•Admin interface• Weekly cron jobSlow is relative9

• Mind you data!• Mind use case• Mind location• Server, used by multiple connections•Dedicated for OLAP queriesSlow is relative9

Query sentConnection Pool: Authentication, Caches; SQL interface; ParserOptimizerStorage enginesHardwareQuery Execution Workﬂow11

• B-Tree (Mostly)• Fractal Tree• LSM Tree• R-Tree (Spatial)• Hash (Memory SE)• Engine-dependentd001d003d008d009d003******d009******d008******d009******d001******d003******d009******d008******d009******d001******d008******d008******d001******MySQL Indexes12

select * from table select * from table where id=121 2 5 6 7 9 12 16 18 21 22 23 24 25Full Scan13

select * from table select * from table where id=121 2 5 6 7 9 12 16 18 21 22 23 24 25After Index Added14

•EXPLAIN• Estimates what happens during query execution•EXTENDED• FORMAT=JSON• PARTITIONSHow to Find how MySQL Uses Indexes16

•EXPLAIN• Estimates what happens during query execution•EXTENDED• FORMAT=JSON• PARTITIONS•INFORMATION SCHEMA.OPTIMIZER TRACE• Real data, collected after query was executed•Advanced topicHow to Find how MySQL Uses Indexes16

mysql> explain select * from t1G*************************** 1. row ***************************...rows: 12Extra: NULLmysql> explain select * from t1 where f2=12G*************************** 1. row ***************************...key: NULL...rows: 12Extra: Using whereSame number of examined rows for both queriesEﬀect of Indexes: Before17

mysql> alter table t1 add index(f2);Query OK, 12 rows affected (0.07 sec)Records: 12 Duplicates: 0 Warnings: 0mysql> explain select * from t1 where f2=12G*************************** 1. row ***************************...key: f2key_len: 5ref: constrows: 1Extra: NULL1 row in set (0.00 sec)Much more eﬀective!Only 1 row examinedEﬀect of Indexes: After18

mysql> explain extended select * from t1 join t2 where t1.int_key=1;+----+-------------+-------+-------+---------------+---------+---------+-------+------+------+---------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | f... | Extra+----+-------------+-------+-------+---------------+---------+---------+-------+------+------+---------| 1 | SIMPLE | t1 | ref | int_key,ik | int_key | 5 | const | 4 | 100. | NULL| 1 | SIMPLE | t2 | index | NULL | pk | 9 | NULL | 6 | 100. | Using inUsing join(Block Nest+----+-------------+-------+-------+---------------+---------+---------+-------+------+------+---------2 rows in set, 1 warning (0.00 sec)Note (Code 1003): /* select#1 */ select ‘test‘.‘t1‘.‘pk‘ AS ‘pk‘,‘test‘.‘t1‘.‘int_key‘ AS ‘int_key‘,‘teAS ‘pk‘,‘test‘.‘t2‘.‘int_key‘ AS ‘int_key‘ from ‘test‘.‘t1‘ join ‘test‘.‘t2‘ where (‘test‘.‘t1‘.‘int_keNumber of selectSelect typeTables, for which information is printedHow data is accessedPossible keysKey, which was actually usedLength of the keyWhich columns were compared with the indexNumber of examined rows% of ﬁltered rowsrows x ﬁltered / 100 — number of rows,which will be joined with another tableAdditional informatioTable, for which information is printedProduct of rows here: how many rows in all tables will be accessedFor this example estimated value is 4*6 = 24Actual (optimized) query as executed by MySQL ServerEXPLAIN: overview19

mysql> explain extended select * from t1 join t2 where...+----+-------------+-------+-------+***| id | select_type | table | type |***+----+-------------+-------+-------+***| 1 | SIMPLE | t1 | ref |***| 1 | SIMPLE | t2 | index |***+----+-------------+-------+-------+***2 rows in set, 1 warning (0.00 sec)SIMPLE;PRIMARY;UNION;DEPENDENT UNION;UNION RESULT;SUBQUERY;DEPENDENT SUBQUERY;DERIVED;MATERIALIZEDsystemconsteq refreffulltextref or nullindex mergeunique subqueryindex subqueryrangeindexALLEXPLAIN in Details20

mysql> explain extended select * from t1 join t2 where t1.int_key=1;***+---------------+---------+---------+-------+******| possible_keys | key | key_len | ref |******+---------------+---------+---------+-------+******| int_key,ik | int_key | 5 | const |******| NULL | pk | 9 | NULL |******+---------------+---------+---------+-------+***2 rows in set, 1 warning (0.00 sec)Keys, which can be used for resolving the queryOnly one key was actually usedActual length of the key (Important for multiple-column keys)Which columns were compared with the indexConstantNumeric in our caseIndex usedto resolve rowsEXPLAIN in Details: keys21

mysql> explain extended select * from t1 join t2 where t1.int_key=1;***+-------+----------+----------------------------------------------------+***| rows | filtered | Extra |***+-------+----------+----------------------------------------------------+***| 4 | 100.00 | NULL |***| 6 | 100.00 | Using index; Using join buffer (Block Nested Loop) |***+-------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec)Number of rows accessed% of rows ﬁlteredAdditional information:how query is resolvedUsing ﬁlesortUsing temporaryetc.4X6=24All rows usedEXPLAIN in Details: rows22

mysql> explain select count(*) from employees where hire_date > ’1995-01-01’********************** 1. row **********************id: 1select_type: SIMPLEtable: employeestype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 300157Extra: Using where1 row in set (0.00 sec)All rows in the table examinedWorst plan ever!EXPLAIN Type by Example: ALL23

• We need to add index to table employees ﬁrstmysql> alter table employees add index(hire_date);Query OK, 0 rows affected (3.48 sec)Records: 0 Duplicates: 0 Warnings: 0EXPLAIN Type by Example: range24

mysql> explain select count(*) from employees where hire_date>’1995-01-01’G********************** 1. row **********************id: 1select_type: SIMPLEtable: employeestype: rangepossible_keys: hire_datekey: hire_datekey_len: 3ref: NULLrows: 68654Extra: Using where; Using index1 row in set (0.00 sec)Only rows from given range usedCompare with ALL:300157/68654 = 4.37204 times less rows examined!EXPLAIN Type by Example: range25

• Consists of two or more columnsCombined Indexes26

• Consists of two or more columns• Only leftmost part usedmysql> alter table City add key-> comb(CountryCode, District, Population),-> drop key CountryCode;Combined Indexes26

mysql> explain select * from City where CountryCode = ’USA’G********************** 1. row ******************table: Citytype: refpossible_keys: combkey: combkey_len: 3ref: constrows: 273Uses ﬁrst ﬁeld from the comb keyCombined Indexes: example 127

mysql> explain select * from City where -> District = ’California’ and population > 10000G********************** 1. row ******************table: Citytype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 3868Can’t use combined index:not a leftmost partDoes not have the CountryCodein the where clause= can’t use comb indexCombined Indexes: example 228

• Key len = total size (in bytes)• Index• comb(CountryCode, District, Population)Explain: Fields:key: comb CountryCode char(3)key len: 3 District char(20)Population int(11)3 ->Char(3) ->First ﬁeld is usedCombined Indexes: key len29

mysql> explain select count(*) from titles where title=’Senior Engineer’G********************** 1. row **********************id: 1select_type: SIMPLEtable: titlestype: indexpossible_keys: NULLkey: emp_nokey_len: 4ref: NULLrows: 444033Extra: Using where; Using index1 row in set (0.11 sec)No row in the table was accessed to resolve the query!Only index usedStill all records in the index were scannedEXPLAIN Type by Example: index30

•Covered index = cover all ﬁelds in the queryselect name from Citywhere CountryCode = ’USA’ and District = ’Alaska’ and population > 10000mysql> alter table City add key-> cov1(CountryCode, District, population, name);Uses all ﬁelds in the query in particular order1. Where part 2. Group By/Order (not used now) 3. Select partCovered Indexes31

mysql> explain select name from City where CountryCode = ’USA’ -> and District = ’Alaska’ and population > 10000G*************************** 1. row ***********table: Citytype: rangepossible_keys: cov1key: cov1key_len: 27ref: NULLrows: 1Extra: Using where; Using indexCovered index is usedMySQL will only use indexWill not go to the data ﬁleEXPLAIN by Example: Covered Indexes32

•We need to modify table to run this testmysql> create table dept_emp_copy (emp_no int, dept_no int);Query OK, 0 rows affected (0.13 sec)mysql> alter table dept_emp_copy add key(dept_no);Query OK, 0 rows affected (1.32 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> insert into dept_emp_copy(emp_no)-> select distinct emp_no from dept_emp;Query OK, 300024 rows affected (4.63 sec)Records: 300024 Duplicates: 0 Warnings: 0EXPLAIN Type by Example: index merge33

mysql> alter table dept_emp_copy add primary key(emp_no);Query OK, 300024 rows affected (5.48 sec)Records: 300024 Duplicates: 0 Warnings: 0mysql> update dept_emp_copy, dept_emp-> set dept_emp_copy.dept_no=dept_emp.dept_no-> where dept_emp_copy.emp_no=dept_emp.emp_no;Query OK, 300024 rows affected, 65535 warnings (15.66 sec)Rows matched: 300024 Changed: 300024 Warnings: 0EXPLAIN Type by Example: index merge34

mysql> explain select * from dept_emp_copy where-> dept_no > 5 or (emp_no > 10000 and emp_no < 20000)G************************ 1. row ************************id: 1select_type: SIMPLEtable: dept_emp_copytype: index_mergepossible_keys: PRIMARY,dept_nokey: dept_no,PRIMARYkey_len: 5,4ref: NULLrows: 21103Extra: Using sort_union(dept_no,PRIMARY); Using whereEXPLAIN Type by Example: index merge35

mysql> explain select * from dept_emp where dept_no in (’d005’, ’d006’,-> ’d007’, ’d008’, ’d009’) or (emp_no > 10000 and emp_no < 20000)G************************ 1. row ************************id: 1select_type: SIMPLEtable: dept_emptype: ALLpossible_keys: PRIMARY,emp_no,dept_nokey: NULLkey_len: NULLref: NULLrows: 332289Extra: Using whereEXPLAIN Type by Example: original table36

• Limitations•It shows estimates onlyEXPLAIN limitations and extensions37

• Limitations•It shows estimates only•Extensions• EXTENDED•PARTITIONS•FORMAT=JSONEXPLAIN limitations and extensions37

mysql> explain partitions select count(*) -> from employees_part where hire_date > ’1991-01-01’G************************ 1. row ************************id: 1select_type: SIMPLEtable: employees_partpartitions: p1,p2type: indexpossible_keys: NULLkey: PRIMARYkey_len: 7ref: NULLrows: 135214Extra: Using where; Using indexEXPLAIN PARTITIONS38

mysql> explain extended select count(*) from employees join titles-> using(emp_no) where title=’Senior Engineer’G************************ 1. row ************************...2 rows in set, 1 warning (0.00 sec)mysql> show warningsG************************ 1. row ************************Level: NoteCode: 1003Message: select count(0) AS ‘count(*)‘ from ‘employees‘.‘employees‘ join‘employees‘.‘titles where ((‘employees‘.‘titles‘.‘emp_no‘ = ‘employees‘.‘employees‘.‘emp_no‘) and (‘employees‘.‘titles‘.‘title‘ = ’Senior Engineer’)1 row in set (0.01 sec)EXPLAIN EXTENDED39

• More information than in regular EXPLAIN•Cost statistics"duplicates_removal": {..."cost_info": {"read_cost": "1252.00","eval_cost": "88544.80","prefix_cost": "89796.80","data_read_per_join": "27M"},EXPLAIN FORMAT = JSON40

• More information than in regular EXPLAIN•Cost statistics• Which part of index chosenmysql> explain format=json SELECT first_name, last_name FROM employee-> WHERE first_name=’Steve’ and last_name like ’V%’-> and hire_date > ’1990-01-01’G*************************** 1. row ***************************EXPLAIN: {..."used_key_parts": ["first_name","last_name"],EXPLAIN FORMAT = JSON40

• More information than in regular EXPLAIN•Cost statistics• Which part of index chosen• Columns, used to resolve querymysql> explain format=json select count(*) from Country-> where Continent=’Africa’ and Population > 1000000G*************************** 1. row ***************************..."used_columns": ["Continent","Population"],EXPLAIN FORMAT = JSON40

• More information than in regular EXPLAIN• Better structured view•Clear distinction for which of operationsparticular optimization usedmysql> explain format=json select distinct last_name-> from employees order by last_name ascG..."ordering_operation": {"using_filesort": false, - No temporary table here!"duplicates_removal": {"using_temporary_table": true,"using_filesort": true,EXPLAIN FORMAT = JSON40

• More information than in regular EXPLAIN• Better structured view•Clear distinction for which of operationsparticular optimization used• Easier to ﬁnd out ”which table belongs to whichsubselect” for complicated queriesEXPLAIN FORMAT = JSON40

• More information than in regular EXPLAIN• Better structured view•Clear distinction for which of operationsparticular optimization used• Easier to ﬁnd out ”which table belongs to whichsubselect” for complicated queries•Separate member for each kind of optimization:grouping, ordering, duplicates removal, etc.EXPLAIN FORMAT = JSON40

mysql> explain insert into salaries(emp_no, from_date) -> select emp_no, min(from_date) from titles-> group by emp_noG********************** 1. row **********************id: 1select_type: SIMPLEtable: titlestype: indexpossible_keys: NULLkey: emp_noEXPLAIN for DML: not only SELECT41

mysql> explain insert into salaries(emp_no, from_date)-> select emp_no, min(from_date) from titles-> group by emp_noG...key_len: 4ref: NULLrows: 444058Extra: Using index1 row in set, 2 warnings (0.00 sec)Warning (Code 1364): Field ’salary’ doesn’t have a default valueWarning (Code 1364): Field ’to_date’ doesn’t have a default valueEXPLAIN for DML: not only SELECT42

What Really HappenedInside Storage Engine

• EXPLAIN is optimisticmysql> explain select * from ol-> where thread_id=10432 and site_id != 9939 order by id limit 3G*************************** 1. row ***************************id: 1 | ref: NULLselect_type: SIMPLE | rows: 33table: ol | filtered: 8.07partitions: NULL | Extra: Using wheretype: indexpossible_keys: thread_idkey: PRIMARYkey_len: 41 row in set, 1 warning (0,00 sec)Handler * Status Variables45

• Status variables ’Handler *’ show truthmysql> flush status; select * from ol-> where thread_id=10432 and site_id != 9939 order by id limit 3;mysql> show status like ’Handler%’;+----------------------------+--------+| Variable_name | Value |+----------------------------+--------+...| Handler_read_first | 1 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 100000 |...Handler * Status Variables45

What Really HappenedInside the Server

•SHOW [FULL] PROCESSLIST•INFORMATION SCHEMA.PROCESSLIST•performance schema.THREADSPROCESSLIST47

•SHOW [FULL] PROCESSLIST•INFORMATION SCHEMA.PROCESSLIST•performance schema.THREADS• Your ﬁrst alert in case of performance issuePROCESSLIST47

•SHOW [FULL] PROCESSLIST•INFORMATION SCHEMA.PROCESSLIST•performance schema.THREADS• Your ﬁrst alert in case of performance issue• Shows all queries, running at the momentPROCESSLIST47

• Can be seen in PROCESSLISTmysql> show processlistG************************ 1. row ************************Id: 7User: rootHost: localhost:48799db: employeesCommand: QueryTime: 2State: Sending dataInfo: select count(*) from employees join titles using(emp_no)where title=’Senior Engineer’...Execution Stages48

• Can be seen in PROCESSLIST• Very useful when you need to answer on question:”What is my server doing now?”Execution Stages48

•PERFORMANCE SCHEMA.EVENTS STAGES *mysql> select eshl.event_name, substr(sql_text, 1, 15) as ‘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 Stages48

•PERFORMANCE SCHEMA.EVENTS STAGES *...| stage/sql/checking permissions | select count(*) | 0.0000 || stage/sql/Opening tables | select count(*) | 0.0000 || stage/sql/init | select count(*) | 0.0001 || stage/sql/System lock | select count(*) | 0.0000 || stage/sql/optimizing | select count(*) | 0.0000 || stage/sql/statistics | select count(*) | 0.0001 || stage/sql/preparing | select count(*) | 0.0000 || stage/sql/executing | select count(*) | 0.0000 || stage/sql/Sending data | select count(*) | 5.4915 || stage/sql/end | select count(*) | 0.0000 |...Execution Stages48

• Status variablesmysql> 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 job49

• Status variablesmysql> 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 job49

• PERFORMANCE SCHEMA.EVENTS STATEMENTS *mysql> select * from performance_schema.events_statements_history_long-> where sql_text like ’select count(*) from employees join %’G*************************** 1. row ****************************...ROWS_SENT: 1 SELECT_RANGE_CHECK: 0ROWS_EXAMINED: 541058 SELECT_SCAN: 1CREATED_TMP_DISK_TABLES: 0 SORT_MERGE_PASSES: 0CREATED_TMP_TABLES: 0 SORT_RANGE: 0SELECT_FULL_JOIN: 0 SORT_ROWS: 0SELECT_FULL_RANGE_JOIN: 0 SORT_SCAN: 0SELECT_RANGE: 0 NO_INDEX_USED: 0Temporary tables and other job49

•sys.statement analysismysql> 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 sfull_scan: avg_latency: 5.41 sexec_count: 7 lock_latency: 2.24 mserr_count: 0 rows_sent: 7warn_count: 0 rows_sent_avg: 1total_latency: 37.89 s rows_examined: 3787406Temporary tables and other job49

•sys.statement analysisrows_examined_avg: 541058rows_affected: 0rows_affected_avg: 0tmp_tables: 0tmp_disk_tables: 0rows_sorted: 0sort_merge_passes: 0digest: 4086bc3dc6510a1d9c8f2fe1f59f0943first_seen: 2016-04-14 15:19:19last_seen: 2016-04-14 16:13:14Temporary tables and other job49

• Index statistics• Optimizer switches• Bugs in optimizerWhat has Eﬀect on Query Optimizer Plans?51

• Collected by storage engineIndex Statistics52

• Collected by storage engine• Used by OptimizerIndex Statistics52

• Can be examined by SHOW INDEX commandmysql> 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 Statistics52

• Can be updated• ANALYZE TABLE•If does not help: rebuild tableOPTIMIZE TABLEALTER TABLE ENGINE=INNODB; ANALYZE TABLEIndex Statistics52

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=on1 row in set (0,00 sec)Optimizer Switches53

• Turn ON and OFF particular optimizationOptimizer Switches53

• Turn ON and OFF particular optimization• Can be not helpful• Especially for queries, tuned for previous versionsOptimizer Switches53

• Turn ON and OFF particular optimization• Can be not helpful• Work with them as with any other option• Turn OFF and trySET optimizer_switch = ’use_index_extensions=off’;SELECT ...EXPLAIN SELECT ...Optimizer Switches53

• Turn ON and OFF particular optimization• Can be not helpful• Work with them as with any other option•If helps implement in queriesSELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;Optimizer Switches53

• Optimizer choses wrong index for no reasonBugs in Optimizer54

• Optimizer choses wrong index for no reason• Statistics is up to dateBugs in Optimizer54

• Optimizer choses wrong index for no reason• Statistics is up to date• Solution• Use index hintsFORCE INDEXIGNORE INDEXBugs in Optimizer54

• Optimizer choses wrong index for no reason• Statistics is up to date• Solution• On every upgrade• Remove index hints• Test if query improved•You must do it even for minor version upgrades!Bugs in Optimizer54

• EXPLAIN is essential for query tuning• Real job is done by storage engine• Index statistics aﬀect query execution plan• All index hints, optimizer hints and otherworkarounds must be validated on eachupgradeSummary55

EXPLAIN SyntaxEXPLAIN FORMAT=JSON is Cool! seriesTroubleshooting Performance add-onsOptimizer HintsTracing the OptimizerMore information56

Alexander Rubin for combined and covered index examplesSpecial thanks57

http://www.slideshare.net/SvetaSmirnovahttps://twitter.com/svetsmirnovahttps://github.com/svetasmirnovaThank you!58

Movatterモバイル変換

Change Language

Introduction into MySQL Query Tuning

Embed presentation

Recommended

More Related Content

What's hot

Similar to Introduction into MySQL Query Tuning

More from Sveta Smirnova

Recently uploaded

Introduction into MySQL Query Tuning