MySQL Query Optimization

1 . MySQL Query Optimization Peter Zaitsev, CEO, Percona March 8, 2019 SCALE 17x Pasadena,CA © 2019 Percona. 1 

2 .About Percona Open Source Database Solutions Company Support, Managed Services, Consulting, Training, Engineering Focus on MySQL, MariaDB, MongoDB, PostgreSQL Support Cloud DBaaS Variants on major clouds Develop Database Software and Tools Release Everything as 100% Free and Open Source © 2019 Percona. 2 

3 .Widely Deployed Open Source Software 5,000,000+ downloads 175,000+ downloads 4,500,000+ downloads 450,000+ downloads 2,000,000+ downloads 1,500,000+ downloads © 2019 Percona. 3 

4 .About the Presentation Cover the Basics How MySQL Executes Queries How To Find Queries to Optimize How to Optimize Them © 2019 Percona. 4 

5 .The Basics © 2019 Percona. 5 

6 .Grand Goal Application which Has a Great Performance © 2019 Percona. 6 

7 .Great Performance Defined Responds With Low Response Time At All Times For All Users © 2019 Percona. 7 

8 .Response Time and Database Database is not always at fault © 2019 Percona. 8 

9 .Database Making your Application Slow Dev Issues Ops Issues © 2019 Percona. 9 

10 .Dev Issues: Many Queries executed serially Expensive Queries Poorly Designed Queries Poorly Optimized Queries Saturation with Additional Load © 2019 Percona. 10 

11 .Ops Issues Problems with System, Storage or Network Saturation with Additional Load Capacity Planning © 2019 Percona. 11 

12 .Mind Network Latency © 2019 Percona. 12 

13 .Query Optimization Goals Specific User Interaction Application As a Whole Improving Efficiency Assuring Scalability © 2019 Percona. 13 

14 .Not Query Optimization Alone General Architecture Right Choice of Technology (Not Only MySQL) Hardware/Instance Properties OS and MySQL Configuration Database Schema © 2019 Percona. 14 

15 .How MySQL Executes Queries © 2019 Percona. 15 

16 .Execution Basics Single Server Single Thread (Using Single CPU Core) No Intermediate Results Caching Between Query Executions © 2019 Percona. 16 

17 .Query Execution Diagram © 2019 Percona. 17 

18 .Added Complexities UDFs (User Defined Functions) Stored Programs VIEWs © 2019 Percona. 18 

19 .Use the LIMIT Do not just stop fetching rows at the application side MySQL Client-Server Protocol is NOT cursor based © 2019 Percona. 19 

20 .Join Order Permanent and “Derived” Tables are going to be “Joined in Order” MySQL Starts from one table, finding all needed rows in it, and iterating finding matching rows from the next one Join Order Is Critical For Performance SELECT STRAIGHT_JOIN to force join order © 2019 Percona. 20 

21 .Indexes Proper Indexes are must have for Optimal Query Execution Can improve Query Performance 1000x or more Expensive to Maintain… so Do not Overdo Covering Indexes to speed up data reads © 2019 Percona. 21 

22 .Indexes are not Free Space on Disk Space in Memory Extra Optimizer Load to Evaluate Them Expensive to Maintain with Updates © 2019 Percona. 22 

23 .Columns MySQL (Innodb, MyISAM, MyRocks etc) store data row by row All columns must be read on every row access (excluding Blobs for Innodb) Number of Total Columns, Their Size Impacts Query Performance a Lot Covering Indexes are great to reduce amount of data query Touches © 2019 Percona. 23 

24 .Character Sets https://per.co.na/MySQLCharsetImpact © 2019 Percona. 24 

25 .Less impact In MySQL 8 © 2019 Percona. 25 

26 .Grouping and Sorting Can use Index, External Sort, Temporary File Temporary Table can be in memory or on disk Amount of Data you Sort, Group Matters A Lot Too many Different Algorithms to Cover in Details © 2019 Percona. 26 

27 .The Mysterious Optimizer No one knows how MySQL Optimizer Really works Designed to Choose Best Plan Based on Cost Cost Model is just a model Relies on Statistics which can be very wrong © 2019 Percona. 27 

28 .Learn what MySQL Execution Can Do Not Everything you can imagine can be done by MySQL during execution Though it also has tricks in its sleeve you may not aware of © 2019 Percona. 28 

29 .Are you Smarter than Optimizer ? Use Optimizer Hints to Execute Query The way you Like Often the plan you think is faster is not https://dev.mysql.com/doc/refman/8.0/en/optimizer- hints.html © 2019 Percona. 29