Downloaded 29 times
![© 2017 Percona21Explain Plan (JSON)> EXPLAIN format=JSON SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title FROM rental INNER JOIN customer ON rental.customer_id =customer.customer_id INNER JOIN address ON customer.address_id = address.address_id INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id INNER JOIN film ON inventory.film_id =film.film_id WHERE rental.return_date IS NULL AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE() LIMIT 5G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"nested_loop": [{"table": {"table_name": "film","access_type": "ALL","possible_keys": ["PRIMARY"],"rows": 1000,"filtered": 100}},……](/image.pl?url=https%3a%2f%2fimage.slidesharecdn.com%2fmysqlqueryoptimizationbestpracticesandindexing-180916184428%2f75%2fMysql-query-optimization-best-practices-and-indexing-21-2048.jpg&f=jpg&w=240)
Uploaded byAlkin Tezuysal
PPTX, PDF724 views
Mysql query optimization best practices and indexing
The document outlines best practices for MySQL query optimization and indexing, emphasizing the importance of indexes for efficient data access. It discusses techniques for identifying slow queries, optimizing query execution plans, and the various types of indexes available in MySQL. Additionally, it provides tips on query structure and execution strategies to enhance performance while maintaining optimal index usage.
![[Đồ án môn học] - Đề tài: Tìm hiểu Git và Github](/image.pl?url=https%3a%2f%2fcdn.slidesharecdn.com%2fss_thumbnails%2fbaocaodamn-200912082034-thumbnail.jpg%3fwidth%3d640%26amp%3bheight%3d640%26amp%3bfit%3dbounds&f=jpg&w=240)
More Related Content
What's hot
Similar to Mysql query optimization best practices and indexing
![Introduction into MySQL Query Tuning for Dev[Op]s](/image.pl?url=https%3a%2f%2fcdn.slidesharecdn.com%2fss_thumbnails%2fqtdevops-210717011329-thumbnail.jpg%3fwidth%3d640%26amp%3bheight%3d640%26amp%3bfit%3dbounds&f=jpg&w=240)
More from Alkin Tezuysal
Mysql query optimization best practices and indexing
- 1.© 2017 Percona1MySQLQuery Optimization BestPracticesand IndexingAlkin Tezuysal – Sr. Technical ManagerPercona
- 2.© 2017 Percona2Whoam I? @ask_dba
- 3.© 2017 Percona3AboutPerconaSolutions for your success with MySQL , MongoDB and PostgreSQLSupport, Managed Services, SoftwareOur Software is 100% Open SourceSupport Broad Ecosystem – MySQL, MariaDB, Amazon RDSIn Business for 12 yearsMore than 3000 customers, including top Internet companies and enterprises
- 4.© 2017 Percona4AboutThis PresentationIndexing BasicsFinding andIdentifyingSlow QueriesUtilizing ExplainPlanAdvancedIndexingTooling andmore
- 5.© 2017 Percona5IndexingBasics• What it does?• Increase speed of given lookup (SQL)• Access and maintain changes• Helps Optimizer to reach its goal
- 6.© 2017 Percona6Whydo we need indexes?• Data persists on disks• Disks cheap but slow• Data can be in memory• Memory fast but expensiveIndex is the answer to access data fast.CREATE INDEX part_of_name ON customer (name(10));
- 7.© 2017 Percona7Traversal1.Tree Traversal2. Follow leaf node chain3. Fetch the table data
- 8.© 2017 Percona8LeafNodes1. Establish doubly linked list2. Connect index leaf nodes3. Indexed columns
- 9.© 2017 Percona9B-treeStructure
- 10.© 2017 Percona10SlowIndex Lookups• Low cardinality• Large data sets• Multiple index traversal• Index column used as argument• Looking for suffix• Non-leading column lookup• Data type mismatch• Character Set / Collation mismatch• MySQL Bug
- 11.© 2017 Percona11TheOptimizer
- 12.© 2017 Percona12MySQLOptimizer• Cost based• Assign costs to select operations• Assign costs to partial or alternate plans• Seek for lowest costAccess Method Join Order Subquery Strategy
- 13.© 2017 Percona13CostModel
- 14.© 2017 Percona14Findingand Identifying Slow Queries• Slow Query Log• PMM/QAN• Network sniff• Others (Licensed)• MySQL EM• Vividcortex• Solarwinds• Monyog• Others
- 15.© 2017 Percona15SlowQuery Tools• Explain Plan• Tabular• JSON (5.7)• Visual (Workbench)• Running Query (5.7)• pt-query-digest• pt-visual-explain• Performance Schema• MySQL Sys Schema• Optimizer Trace• MySQL Workbench• Status Variables• show status like ‘Sort%’• show status like ‘Handler%’
- 16.
- 17.
- 18.
- 19.© 2017 Percona19PMMDemo - https://pmmdemo.percona.com/
- 20.
- 21.© 2017 Percona21ExplainPlan (JSON)> EXPLAIN format=JSON SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title FROM rental INNER JOIN customer ON rental.customer_id =customer.customer_id INNER JOIN address ON customer.address_id = address.address_id INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id INNER JOIN film ON inventory.film_id =film.film_id WHERE rental.return_date IS NULL AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE() LIMIT 5G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"nested_loop": [{"table": {"table_name": "film","access_type": "ALL","possible_keys": ["PRIMARY"],"rows": 1000,"filtered": 100}},……
- 22.© 2017 Percona22ExplainPlan (pt-visual-explain)JOIN+- Bookmark lookup| +- Table| | table address| | possible_keys PRIMARY| +- Unique index lookup| key address->PRIMARY| possible_keys PRIMARY| key_len 2| ref sakila.customer.address_id| rows 1+- JOIN+- Bookmark lookup| +- Table| | table customer| | possible_keys PRIMARY,idx_fk_address_id| +- Unique index lookup| key customer->PRIMARY| possible_keys PRIMARY,idx_fk_address_id| key_len 2| ref sakila.rental.customer_id| rows 1...
- 23.© 2017 Percona23CostBased Access Method1. Find the optimal method2. Check if access method useful3. Estimate the cost of using access method4. Select low cost access method
- 24.© 2017 Percona24QueryExecutionTable ScanIndex ScanIndexLookupRangeScanIndexMergeLooseIndex Scan
- 25.© 2017 Percona25IndexingBest Practices• Always have Primary Key• Physical order of table, if not created explicitly, MySQL will createhidden one (Global Mutex)• Fastest lookup is PK
- 26.© 2017 Percona26IndexingBest Practices• Single index with multiple columns• Left most first and each additional field in a composite key• Composite indexes better a.k.a Covering indexes• PK is already part of composite indexes
- 27.© 2017 Percona27IndexingBest Practices• Equality first, range next• Ex:select first_name, last_name, birth_date fromemployeeswhere date_of_birth => to_date (?, `YYYY-MM-DD`)and date_of_birth <= to_date (?, `YYYY-MM-DD`)and branch_id = ?
- 28.© 2017 Percona28IndexingBest Practices• One index scan is faster than two• Avoid duplicate indexes pt-duplicate-key-checker
- 29.© 2017 Percona29IndexingBest Practices• Data types matter. Numeric for numbers.• Ex:select …from …where numeric_value = `48`
- 30.© 2017 Percona30QueryOptimization Best Practices• Negative clauses and subqueries aren’t as good as positiveclauses• Ex:• IS NOT• IS NOT NULL• NOT IN• NOT LIKE
- 31.© 2017 Percona31QueryOptimization Best Practices• User INNER instead of LEFT where you can
- 32.© 2017 Percona32QueryOptimization Best Practices• UNION ALL is better than UNIONUNIONUNION ALL
- 33.© 2017 Percona33QueryOptimization Best Practices• ORDER BY can be expensiveSELECT * FROM t1ORDER BY idx_c1, idx_c2;• Avoid while sorting small set of data (Use code)cust_idfirst_namelast_nameemail1 Billy Joel bb7@bluenot.com2 Jane Fondajf1950@yahoo.com3 Mark WeltonmarkW1912@gmail.com4 Linda Joey linda.joey@yandex.com5 Sidney Travorsidney.travor@icloud.com6 Jordan Velez jordanv@amazon.com
- 34.© 2017 Percona34QueryOptimization Best Practices• Watch out those ORDER BY + LIMIT operations• These usually return small set of data with big cost (filesort)SELECT col1, ... FROM t1 ... ORDER BY name LIMIT10;SELECT col1, ... FROM t1 ... ORDER BY RAND()LIMIT 15;
- 35.© 2017 Percona35QueryOptimization Best Practices• Watch out those ORDER BY + LIMIT operations• These usually return small set of data with big cost (filesort)SELECT col1, ... FROM t1 ... ORDER BY name LIMIT10;SELECT col1, ... FROM t1 ... ORDER BY RAND()LIMIT 15;
- 36.© 2017 Percona36MySQLIndex Types• B-tree (Common)• Fractal Tree• LSM Tree• R-Tree (Spatial)• Hash (Memory)• Engine-dependent
- 37.© 2017 Percona37AdvancedIndexing• Optimizer hints• Global: The hint affects the entire statement• Query block: The hint affects a particular query block within a statement• Table-level: The hint affects a particular table within a query block• Index-level: The hint affects a particular index within a table• Index hints• SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FORORDER BY (i2) ORDER BY a;
- 38.© 2017 Percona38Ifindexes not enoughQuery Re-write• ProxySQL• https://www.percona.com/blog/2018/05/02/proxysql-query-rewrite-use-case/• MySQL 5.7: Query Rewrite Plugin• Add hints• Modify join order
- 39.© 2017 Percona39AdvancedQueries with ProxySQL – Query rewriteengine• Most wanted feature by DBAs• Rewrite queries overloading the database on the fly.Application AProxySQL• Simply buy time until application can be modifiedApplication BMySQLMasterMySQLSlaveMySQLSlaveMySQLSlaveQueryRewritingMySQLSlaveMySQLSlave
- 40.© 2017 Percona40FinalThoughtsOptimizer is not smart as DBAs• Help to choose best possible path• Improve throughputAdd only indexes you need• Avoid duplicate indexing• Avoid overhead disk space, extra i/o opsStay on current version of MySQL• Several bugs fixed• Optimizer and Engine improvements in place
- 41.© 2017 Percona41Referencesand Credits• Markus Winand (2018) - SQL Performance Explained (2018)• Otstein Grovlen (2017- How to Analyze and Tune MySQL Queries forBetter Performance• Sveta Smirnova (2018) – Introduction into MySQL Query Tuning• Oracle Reference Manual• Jeremy Cole (2013) - How does InnoDB behave without a PrimaryKey?• Tata McDaniel (2018) - Visualize This! MySQL Tools That ExplainQueries• Reviewers: Daniel G Burgos, Tate McDaniel, Janos Ruszo
- 42.DATABASE PERFORMANCEMATTERSDatabase PerformanceMattersDatabase Performance MattersDatabase Performance MattersDatabase Performance MattersDatabase Performance Matters