[关闭]
@JunQiu 2018-09-27T16:06:37.000000Z 字数 9302 阅读 672

mysql query optimize - explain、查看mysql自身优化后的语句、Impossible WHERE noticed after reading const tables

mysql


1、mysql query optimize - explain

1.1、简介

  1. explain select 语句

1.2、前期准备:建3张表

  1. CREATE TABLE `user_info` (
  2. `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  3. `name` VARCHAR(50) NOT NULL DEFAULT '',
  4. `age` INT(11) DEFAULT NULL,
  5. PRIMARY KEY (`id`),
  6. KEY `name_index` (`name`)
  7. );
  8. INSERT INTO user_info (name, age) VALUES ('xys', 20);
  9. INSERT INTO user_info (name, age) VALUES ('a', 21);
  10. INSERT INTO user_info (name, age) VALUES ('b', 23);
  11. INSERT INTO user_info (name, age) VALUES ('c', 50);
  12. INSERT INTO user_info (name, age) VALUES ('d', 15);
  13. INSERT INTO user_info (name, age) VALUES ('e', 20);
  14. INSERT INTO user_info (name, age) VALUES ('f', 21);
  15. INSERT INTO user_info (name, age) VALUES ('g', 23);
  16. INSERT INTO user_info (name, age) VALUES ('h', 50);
  17. INSERT INTO user_info (name, age) VALUES ('i', 15);
  18. CREATE TABLE `order_info` (
  19. `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  20. `user_id` BIGINT(20) DEFAULT NULL,
  21. `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  22. `productor` VARCHAR(30) DEFAULT NULL,
  23. PRIMARY KEY (`id`),
  24. KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
  25. );
  26. INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
  27. INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
  28. INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
  29. INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
  30. INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
  31. INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
  32. INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
  33. INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
  34. INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
  35. CREATE TABLE `order_info1` (
  36. `id` BIGINT(20) NOT NULL ,
  37. `user_id` BIGINT(20) DEFAULT NULL,
  38. );

1.3、一个简单的例子和explain结果字段:

  1. mysql> explain select * from order_info\G //\G制表,使格式更加美观
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: SIMPLE
  5. table: order_info
  6. type: index
  7. possible_keys: NULL
  8. key: user_product_detail_index
  9. key_len: 254
  10. ref: NULL
  11. rows: 9
  12. Extra: Using index
  13. 1 row in set (0.00 sec)
  14. ## 先大概这些字段的含义:
  15. 1id:查询的标志,每个查询会自动分配一个id,比如使用union合并两个查询结果时没有id,为NULL
  16. 2select_type:当前查询的类型,比如Simple SELECT (not using UNION or subqueries)。
  17. 3table:当前查询所对应的表,比如union对应两个表。
  18. 4type:可能是数据中最重要的字段,展示查询/连接是如何进行的,走索引/或者全表查询等等。
  19. 5possible_keys:当前查询可能会选择的索引,即可以使用该索引找到我们需要的行。
  20. 6key:当前查询实际用到的索引。
  21. 7key_len:所选择索引使用的长度。
  22. 8refjion查询的时候会用到,查询结果同哪个值比较。
  23. 9rows:当前查询必须要检测的行数。
  24. 10extra:一些额外的信息。
  25. 11filtered:被过滤条件过滤掉的数据的百分比。(但是根据文档上的意思应该是:过滤掉的数据为1-filtered)
  26. 12partitions:匹配的分区。

1.4、重要的字段详解

  1. mysql> explain (SELECT * FROM user_info WHERE id IN (1, 2, 3)) UNION (SELECT * FROM user_info WHERE id IN (3, 4, 5))\G
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: PRIMARY
  5. table: user_info
  6. type: range
  7. possible_keys: PRIMARY
  8. key: PRIMARY
  9. key_len: 8
  10. ref: NULL
  11. rows: 3
  12. Extra: Using where
  13. *************************** 2. row ***************************
  14. id: 2
  15. select_type: UNION
  16. table: user_info
  17. type: range
  18. possible_keys: PRIMARY
  19. key: PRIMARY
  20. key_len: 8
  21. ref: NULL
  22. rows: 3
  23. Extra: Using where
  24. *************************** 3. row ***************************
  25. id: NULL
  26. select_type: UNION RESULT
  27. table: <union1,2>
  28. type: ALL
  29. possible_keys: NULL
  30. key: NULL
  31. key_len: NULL
  32. ref: NULL
  33. rows: NULL
  34. Extra: Using temporary
  35. 3 rows in set (0.00 sec)
  1. // 以上面的例子的为例:
  2. id=1的查询最先开始,所以是PRIMARY
  3. id=2的查询是ubion中的第二个slect:所以是UNION
  4. union操作没有idnull,但是select_typeUNION
  5. // 比如写一个SUBQUERY
  6. ???
  1. # system : 当表中只有一条数据时,且是系统表时;如果不只一条数据时,分析与普通表相同。
  2. mysql> explain select * from time_zone\G
  3. *************************** 1. row ***************************
  4. id: 1
  5. select_type: SIMPLE
  6. table: time_zone
  7. type: system
  8. possible_keys: NULL
  9. key: NULL
  10. key_len: NULL
  11. ref: NULL
  12. rows: 1
  13. Extra: NULL
  14. 1 row in set (0.00 sec)
  15. # const : 当使用 PRIMARY KEY or UNIQUE index 去查询数据,因为他们是唯一的只用读取一次。
  16. mysql> explain select * from order_info where id=10 \G
  17. *************************** 1. row ***************************
  18. id: 1
  19. select_type: SIMPLE
  20. table: order_info
  21. type: const
  22. possible_keys: PRIMARY
  23. key: PRIMARY
  24. key_len: 8
  25. ref: const
  26. rows: 1
  27. Extra: NULL
  28. 1 row in set (0.00 sec)
  29. # eq_ref : 在多表join查询中,表示对于前表的每一个结果,都只能匹配到当前表的一行结果.(且通过PRIMARY KEY or UNIQUE NOT NULL index去查找) 并且查询的比较操作通常是 =, 查询效率较高。
  30. // exapmle:user_info.id(PRIMARY KEY)
  31. mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.id\G
  32. *************************** 1. row ***************************
  33. id: 1
  34. select_type: SIMPLE
  35. table: user_info
  36. type: ALL
  37. possible_keys: PRIMARY
  38. key: NULL
  39. key_len: NULL
  40. ref: NULL
  41. rows: 10
  42. Extra: NULL
  43. *************************** 2. row ***************************
  44. id: 1
  45. select_type: SIMPLE
  46. table: order_info
  47. type: eq_ref
  48. possible_keys: PRIMARY
  49. key: PRIMARY
  50. key_len: 8
  51. ref: test.user_info.id
  52. rows: 1
  53. Extra: NULL
  54. 2 rows in set (0.00 sec)
  55. Tips1:如果操作是>,此时typeall,因为并不能走索引。
  56. Tips2:有点意外的是,好像没有mysql并没有像我们想象的那样去连接,有机会了解一下。
  57. # ref : ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index .in other words, if the join cannot select a single row based on the key value.
  58. // user_id is leftmost prefix
  59. mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
  60. *************************** 1. row ***************************
  61. id: 1
  62. select_type: SIMPLE
  63. table: user_info
  64. type: ALL
  65. possible_keys: PRIMARY
  66. key: NULL
  67. key_len: NULL
  68. ref: NULL
  69. rows: 10
  70. Extra: NULL
  71. *************************** 2. row ***************************
  72. id: 1
  73. select_type: SIMPLE
  74. table: order_info
  75. type: ref
  76. possible_keys: user_product_detail_index
  77. key: user_product_detail_index
  78. key_len: 9
  79. ref: test.user_info.id
  80. rows: 1
  81. Extra: Using index
  82. 2 rows in set (0.00 sec)
  83. ## fulltext:连接时使用FULLTEXT index
  84. ## ref_or_null:This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. (相比ref,多了null搜索)
  85. // 和常数连接,但order_info.user_id是leftmost prefix = ref,同时搜索null = ref_or_null
  86. mysql> EXPLAIN SELECT * FROM order_info WHERE order_info.user_id=2 OR order_info.user_id is null\G
  87. *************************** 1. row ***************************
  88. id: 1
  89. select_type: SIMPLE
  90. table: order_info
  91. type: ref_or_null
  92. possible_keys: user_product_detail_index
  93. key: user_product_detail_index
  94. key_len: 9
  95. ref: const
  96. rows: 3
  97. Extra: Using where; Using index
  98. 1 row in set (0.00 sec)
  99. ## index_merge:This join type indicates that the Index Merge optimization is used.
  100. index_merge 可以看mysql optimize index_merge这篇日志
  101. 或者官方:https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html
  102. ## unique_subquery:This type replaces eq_ref for some IN subqueries of the following form:
  103. // value IN (SELECT primary_key FROM single_table WHERE some_expr)
  104. ## index_subquery:This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:
  105. value IN (SELECT key_column FROM single_table WHERE some_expr)
  106. ## range:Only rows that are in a given range are retrieved, using an index to select the rows.(使用索引进行范围查找)
  107. mysql> EXPLAIN SELECT * FROM order_info WHERE id between 10 and 20\G
  108. *************************** 1. row ***************************
  109. id: 1
  110. select_type: SIMPLE
  111. table: order_info
  112. type: range
  113. possible_keys: PRIMARY
  114. key: PRIMARY
  115. key_len: 8
  116. ref: NULL
  117. rows: 10
  118. Extra: Using where
  119. 1 row in set (0.02 sec)
  120. ## index:The index join type is the same as ALL, except that the index tree is scanned. 包含两种情况:
  121. 1、如果索引数据满足要求,仅扫描索引即可,the Extra column says Using index.
  122. 2、通过索引扫描全表。Uses index does not appear in the Extra column.
  123. mysql> EXPLAIN SELECT id FROM order_info\G
  124. *************************** 1. row ***************************
  125. id: 1
  126. select_type: SIMPLE
  127. table: order_info
  128. type: index
  129. possible_keys: NULL
  130. key: user_product_detail_index
  131. key_len: 254
  132. ref: NULL
  133. rows: 10
  134. Extra: Using index
  135. 1 row in set (0.00 sec)
  136. ## all:全表扫描

1.5、总结

  1. ## 查看mysql优化后的SQL:
  2. mysql> EXPLAIN extended SELECT * FROM user_info, order_info1 WHERE user_info.id = order_info1.id\G
  3. *************************** 1. row ***************************
  4. id: 1
  5. select_type: SIMPLE
  6. table: order_info1
  7. type: ALL
  8. possible_keys: NULL
  9. key: NULL
  10. key_len: NULL
  11. ref: NULL
  12. rows: 1
  13. filtered: 100.00
  14. Extra: NULL
  15. *************************** 2. row ***************************
  16. id: 1
  17. select_type: SIMPLE
  18. table: user_info
  19. type: eq_ref
  20. possible_keys: PRIMARY
  21. key: PRIMARY
  22. key_len: 8
  23. ref: test.order_info1.id
  24. rows: 1
  25. filtered: 100.00
  26. Extra: NULL
  27. 2 rows in set, 1 warning (0.00 sec)
  28. mysql> show warnings\G
  29. *************************** 1. row ***************************
  30. Level: Note
  31. Code: 1003
  32. Message: /* select#1 */ select `test`.`user_info`.`id` AS `id`,`test`.`user_info`.`name` AS `name`,`test`.`user_info`.`age` AS `age`,`test`.`order_info1`.`id` AS `id`,`test`.`order_info1`.`user_id` AS `user_id` from `test`.`user_info` join `test`.`order_info1` where (`test`.`user_info`.`id` = `test`.`order_info1`.`id`)
  33. 1 row in set (0.01 sec)
  34. ## 一个奇怪的现象:其实就是extra的信息
  35. mysql> EXPLAIN SELECT * FROM order_info WHERE id =30\G
  36. *************************** 1. row ***************************
  37. id: 1
  38. select_type: SIMPLE
  39. table: NULL
  40. type: NULL
  41. possible_keys: NULL
  42. key: NULL
  43. key_len: NULL
  44. ref: NULL
  45. rows: NULL
  46. Extra: Impossible WHERE noticed after reading const tables
  47. 1 row in set (0.00 sec)
  48. 实际上是:MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

1.6、 参考文档

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注