Learn how to Analyze and Tune MySQL Queries for Better Performance

下载 0

无羡

发布于

1593

人观看

#信息技术

查询性能对于任何应用程序的成功都至关重要。为了精细地调整查询，首先需要了解MySQL是如何执行它们的，以及有哪些工具可以帮助识别问题。
在本课程中，您将学习：
1）研究问题查询的常用工具
2）什么是索引，为什么要使用索引
3）指标限制
4）何时重写查询而不只是添加新索引

展开查看详情

5 .EXPLAIN Fields Field JSON FIELD Description id select_id The sequential number of the SELECT within the query select_type The Type of select being performed. table table_name The name, or alias of the table related to this step of the queries The partitions from which records would be matched by the query. NULL for non partitioned partitions partitions tables type access_type The type of access being used to retrieve data or join tables possible_keys possible_keys Which indexes are being reviewed for use in the query key key The index which will be used for the query The length of the key that MySQL has decided to use. This enables you to determine how many key_len key_length parts of a composite index are being used.* Shows which columns or constants are compared to the index named in ref ref the key column to select rows from the table rows rows The number of rows MySQL estimates will be need to be reviewed An estimated percentage of table rows that will be filtered by the condition. 100% means all filtered filtered the rows returned by the condition will be joined with previous tables extra Additional information related to the optimizer’s chosen path. 5 © 2018 Percona

8 .Access Types Type Description constant The table has at most one matching row which is read at the start of the query system A type of constant table used for systems tables eq_ref Used for table joins with a 1:1 relationship. Requires the joining index by a Primary or Unique Key All rows with matching index values are read from this table for each combination of rows from the previous ref tables fulltext A fulltext Index is being used ref_or_null Similar to ref, but will find null values in addition to the selected value index_merge Index Merge is being used for the join unique_subquery Replaces eq_ref for certain subqueries using IN index_subquery Similar to unique_subquery but for when the index does not have a unique constraint Only rows that are in a given range are retrieved, using an index to select the rows. The key column range in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type. index The entire index is scanned all A full table scan is required 8 © 2018 Percona

10 .Performance Schema Profiling cont. use performance_schema; SELECT event_name AS Stage, round(timer_wait/ pow(10,12),6) AS Duration FROM events_stages_history_long WHERE nesting_event_id = 54; 10 © 2018 Percona

15 .Primary Key Requirements and constraints ▪ Field(s) MUST be UNIQUE ▪ NULL is not valid ▪ Only 1 per table ▪ Can be a single or multiple fields Controls how data is stored physically Is utilized as part of ALL secondary indexes 15 © 2018 Percona

18 .Multiple Tables SELECT f.rating,sum(p.amount) as income FROM film f JOIN inventory i ON i.film_id = f.film_id JOIN rental r ON r.inventory_id = i.inventory_id JOIN payment p ON p.rental_id=r.rental_id GROUP BY f.rating ORDER BY income DESC; 18 © 2018 Percona

19 .Multiple Tables SELECT f.rating,sum(p.amount) as income FROM film f JOIN inventory i ON i.film_id = f.film_id JOIN rental r ON r.inventory_id = i.inventory_id JOIN payment p ON p.rental_id=r.rental_id GROUP BY f.rating ORDER BY income DESC; 19 © 2018 Percona

20 .Index Limitations Functions ▪ Aggregates • SUM,AVG • Does not include MIN,MAX ▪ Multiple Range scans • multiple OR statements • multiple less than or greater than ▪ Character Set Differences ▪ Data Type Differences 20 © 2018 Percona

21 .Multiple Range Scans SELECT count(1) FROM rental WHERE customer_id=148 AND return_date BETWEEN '2005-08-29 00:00:00' AND '2005-09-05 23:59:59' AND last_update BETWEEN '2006-02-15 00:00:00' AND '2006-02-15 23:59:59'; Add a composite index on customer_id,return_date,last_update 21 © 2018 Percona

23 .Multiple Range Scans SELECT COUNT(1) FROM rental WHERE customer_id=148 AND (return_date BETWEEN '2005-08-29 00:00:00' AND '2005-09-05 23:59:59' OR last_update BETWEEN '2006-02-15 00:00:00' AND '2006-02-15 23:59:59'); 23 © 2018 Percona

24 .Multiple Range Scans SELECT COUNT(1) FROM rental WHERE customer_id=148 AND return_date BETWEEN '2005-08-29 00:00:00’ AND '2005-09-05 23:59:59' UNION SELECT COUNT(1) FROM rental WHERE customer_id=148 AND last_update BETWEEN '2006-02-15 00:00:00' AND '2006-02-15 23:59:59'; Indexes are read left to right so a new index is needed on customer_id,last_update 24 © 2018 Percona

26 .References https://dev.mysql.com/doc/sakila/en/sakila-installation.html https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.7/en/performance-schema-query- profiling.html https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html https://www.percona.com/software/database-tools/percona-toolkit https://bugs.mysql.com/bug.php?id=83062 26 © 2018 Percona

0点赞

0收藏

0下载