Indexes - What you need to know part 2

下载 0

空白

发布于

5049

人观看

#信息技术

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

展开查看详情

4 . About MySQL and Indexes The number one goal is to have faster queries. The process is: We ﬁrst 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

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 ﬁrst 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

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

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

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. Deﬁne the index so the leftmost columns ﬁlter 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 ﬁltering 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

21 . Indexes are Multi-Purpose So far indexes have only been used for ﬁltering. 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 . Preﬁx 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 Preﬁx-Index Emulate Hash Index © 2011 - 2017 Percona, Inc. 29 / 52

10点赞

4收藏

0下载