Indexes - What you need to know part 2

正确的索引是数据库性能的关键。利用专家的提示,对您的查询编写和数据库性能进行微调。MySQL提供了几种不同类型的索引,并以各种方式使用它们。要查看第1部分的录音和幻灯片,请单击此处。
在本课程中,您将学习:
1)如何使用复合索引
2)除查找外的其他索引用法
3)如何查找未优化的查询
4)有什么无法解释的?

展开查看详情

1. Indexes - What you need to know Part 2 https://www.percona.com/training/ © 2011 - 2017 Percona, Inc. 1 / 52

2. Table of Contents 1. Query Planning 3. Composite Indexes 2. Explaining the EXPLAIN 4. Other Index Usages 5. Finding Unoptimized Queries © 2011 - 2017 Percona, Inc. 2 / 52

3. Query Optimization QUERY PLANNING © 2011 - 2017 Percona, Inc. 3 / 52

4. About MySQL and Indexes The number one goal is to have faster queries. The process is: We first ask MySQL what is the intended execution plan. If we don't like it, we make a change, and ask again. © 2011 - 2017 Percona, Inc. 4 / 52

5. It All Starts with EXPLAIN Bookmark this manual page: http://dev.mysql.com/doc/refman/5.7/en/explain-output.html It is the best source for anyone getting started. Also watch "Indexes - Part 1" © 2011 - 2017 Percona, Inc. 5 / 52

6. Query Optimization COMPOSITE INDEXES © 2011 - 2017 Percona, Inc. 6 / 52

7. EXERCISE: Add Index(es) to Query mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' AND -> production_year BETWEEN 1997 AND 2009\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3244766 filtered: 1.11 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 7 / 52

8. We Are Spoiled for Choice Which one do we choose? ALTER TABLE title ADD INDEX py (production_year); ALTER TABLE title ADD INDEX t (title(30)); ALTER TABLE title ADD INDEX py_t (production_year, title(30)); ALTER TABLE title ADD INDEX t_py (title(30), production_year); Let's try the first one: mysql> ALTER TABLE title ADD INDEX py (production_year); Query OK, 0 rows affected (4.99 sec) Records: 0 Duplicates: 0 Warnings: 0 © 2011 - 2017 Percona, Inc. 8 / 52

9. Index on (production_year) mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' AND -> production_year BETWEEN 1997 AND 2009\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: py key: NULL key_len: NULL ref: NULL rows: 3244766 filtered: 5.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 9 / 52

10. How about a Smaller Range? mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' AND -> production_year BETWEEN 2008 AND 2009\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: range possible_keys: py key: py key_len: 5 ref: NULL rows: 530320 filtered: 10.00 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 10 / 52

11. Index on (title) mysql> ALTER TABLE title ADD INDEX t (title(30)); Query OK, 0 rows affected (6.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' AND -> production_year BETWEEN 2008 AND 2009\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title partitions: NULL type: ref possible_keys: py,t key: t key_len: 92 ref: const rows: 3241 filtered: 50.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 11 / 52

12. Comparing the two mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' AND -> production_year BETWEEN 2008 AND 2009\G ************ 1. row ************ ************ 1. row ************ id: 1 id: 1 select_type: SIMPLE select_type: SIMPLE table: title table: title type: range partitions: NULL possible_keys: py type: ref key: py possible_keys: py,t key_len: 5 key: t ref: NULL key_len: 92 rows: 530320 ref: const filtered: 10.00 rows: 3241 Extra: Using index filtered: 50.00 condition; Extra: Using where Using where 1 row in set, 1 warning (0.00 sec) 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 12 / 52

13. Composite Indexes Which is better? INDEX py_t (production_year, title) INDEX t_py (title, production_year) © 2011 - 2017 Percona, Inc. 13 / 52

14. Index on (py_t) mysql> ALTER TABLE title ADD INDEX py_t (production_year, title(30)); Query OK, 0 rows affected (9.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' -> AND production_year BETWEEN 2008 AND 2009\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ref possible_keys: py,t,py_t key: t key_len: 92 ref: const rows: 3241 filtered: 16.34 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 14 / 52

15. Index on (py_t) Visualized © 2011 - 2017 Percona, Inc. 15 / 52

16. Index on (t_py) mysql> ALTER TABLE title ADD INDEX t_py (title(30), production_year); Query OK, 0 rows affected (9.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM title WHERE title = 'Pilot' AND -> production_year BETWEEN 2008 AND 2009\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: range possible_keys: py,t,py_t,t_py key: t_py key_len: 97 ref: NULL rows: 192 filtered: 100.00 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 16 / 52

17. Index on (t_py) Visualized © 2011 - 2017 Percona, Inc. 17 / 52

18. Composite Index Recommendations Don't know what order to specify the columns? RULE: Think about how the equality comparisons narrow down the subset of rows to examine. Define the index so the leftmost columns filter most effectively. EXCEPTION: If you have a range comparison (!=, <, >, BETWEEN, LIKE), those columns should go to the right in the index. © 2011 - 2017 Percona, Inc. 18 / 52

19. Recommendations (cont.) Columns after the range-comparison column can't be used for filtering in MySQL <5.6 We can still push down those extra columns (ICP) to the engine, having a speed up if the condition is very selective © 2011 - 2017 Percona, Inc. 19 / 52

20. Query Optimization OTHER INDEX USAGES © 2011 - 2017 Percona, Inc. 20 / 52

21. Indexes are Multi-Purpose So far indexes have only been used for filtering. This is the most typical case—don't forget it. There are also other ways MySQL can use indexes: Avoiding having to sort. Preventing temporary tables. Avoiding reading rows from the tables. © 2011 - 2017 Percona, Inc. 21 / 52

22. The First Example Again mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title = 'Bambi' ORDER BY production_year\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3244766 filtered: 10.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 22 / 52

23. Index Prevents Sort mysql> ALTER TABLE title ADD INDEX t_py (title(30), production_year); mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title = 'Bambi' ORDER BY production_year\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ref possible_keys: t_py key: t_py key_len: 92 ref: const rows: 11 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 23 / 52

24. Temporary Table in Use mysql> EXPLAIN SELECT COUNT(*) AS c, production_year -> FROM title GROUP BY production_year\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1496878 Extra: Using temporary; Using filesort 1 row in set (0.00 sec) © 2011 - 2017 Percona, Inc. 24 / 52

25. Full Index Scan mysql> ALTER TABLE title ADD INDEX py (production_year); mysql> EXPLAIN SELECT COUNT(*) AS c, production_year FROM title -> GROUP BY production_year\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: index possible_keys: t_py,py key: py key_len: 5 ref: NULL rows: 3244766 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 25 / 52

26. Retrieving Limited Columns mysql> SELECT person_id FROM cast_info WHERE person_role_id = 35722; What's the difference between indexes on (person_role_id) and (person_role_id, person_id)? © 2011 - 2017 Percona, Inc. 26 / 52

27. Retrieving Limited Columns (cont.) mysql> ALTER TABLE cast_info ADD INDEX (person_role_id); mysql> EXPLAIN SELECT person_id FROM cast_info -> WHERE person_role_id = 35722\G ************* 1. row ************* id: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: person_role_id key: person_role_id key_len: 5 ref: const rows: 38 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 27 / 52

28. Covering Index Optimization mysql> ALTER TABLE cast_info -> ADD INDEX person_role_id_person_id (person_role_id, person_id); mysql> EXPLAIN SELECT person_id FROM cast_info -> WHERE person_role_id = 35722\G ************* 1. row ************* id: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: person_role_id,person_role_id_person_id key: person_role_id_person_id key_len: 5 ref: const rows: 38 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.01 sec) © 2011 - 2017 Percona, Inc. 28 / 52

29. Prefix Indexes The problem with this schema, is there's just a couple of outliers with really long names: mysql> SELECT MAX(LENGTH(title)) mysql> SELECT MAX(LENGTH(name)) -> FROM title; -> FROM char_name; +--------------------+ +-------------------+ | MAX(LENGTH(title)) | | MAX(LENGTH(name)) | +--------------------+ +-------------------+ | 334 | | 719 | +--------------------+ +-------------------+ 1 row in set (0.98 sec) 1 row in set (4.18 sec) Two Ways to Solve This Prefix-Index Emulate Hash Index © 2011 - 2017 Percona, Inc. 29 / 52