GreatSQL社区

搜索

MySQL为什么"错误"选择代价更大的索引?

406 1 2022-8-18 17:53
MySQL为什么"错误"选择代价更大的索引?
全部回复(1)
GreatSQL万答 2022-8-18 18:01:10
MySQL优化器索引选择迷思。
高鹏(八怪)对本文亦有贡献。

1. 问题描述
群友提出问题,表里有两个列c1、c2,分别为INT、VARCHAR类型,且分别创建了unique key。
SQL查询的条件是 WHERE c1 = ? AND c2 = ?,用EXPLAIN查看执行计划,发现优化器优先选择了VARCHAR类型的c2列索引。
他表示很不理解,难道不应该选择看起来代价更小的INT类型的c1列吗?
2. 问题复现
创建测试表t1:
  1. [root@yejr.run]> CREATE TABLE `t1` (
  2.   `c1` int NOT NULL AUTO_INCREMENT,
  3.   `c2` int unsigned NOT NULL,
  4.   `c3` varchar(20) NOT NULL,
  5.   `c4` varchar(20) NOT NULL,
  6.   PRIMARY KEY (`c1`),
  7.   UNIQUE KEY `k3` (`c3`),
  8.   UNIQUE KEY `k2` (`c2`)
  9. ) ENGINE=InnoDB;
复制代码
利用 mysql_random_data_load 写入一万行数据:
  1. mysql_random_data_load -h127.0.0.1 -uX -pX yejr t1 10000
复制代码
查看执行计划:
  1. [root@yejr.run]> EXPLAIN SELECT * FROM t1 WHERE
  2. c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: t1
  7.    partitions: NULL
  8.          type: const
  9. possible_keys: k3,k2
  10.           key: k3
  11.       key_len: 82
  12.           ref: const
  13.          rows: 1
  14.      filtered: 100.00
  15.         Extra: NULL
复制代码
可以看到优化器的确选择了 k3 索引,而非"预期"的 k2 索引,这是为什么呢?
3. 问题分析
其实原因很简单粗暴:优化器认为这两个索引选择的代价都是一样的,只是优先选中排在前面的那个索引而已。
再建一个相同的表 t2,只不过把 k2、k3 的索引创建顺序对调下:
  1. [root@yejr.run]> CREATE TABLE `t2` (
  2.   `c1` int NOT NULL AUTO_INCREMENT,
  3.   `c2` int unsigned NOT NULL,
  4.   `c3` varchar(20) NOT NULL,
  5.   `c4` varchar(20) NOT NULL,
  6.   PRIMARY KEY (`c1`),
  7.   UNIQUE KEY `k2` (`c2`),
  8.   UNIQUE KEY `k3` (`c3`)
  9. ) ENGINE=InnoDB;
复制代码
再查看执行计划:
  1. [root@yejr.run]> EXPLAIN SELECT * FROM t2 WHERE
  2. c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: t1
  7.    partitions: NULL
  8.          type: const
  9. possible_keys: k2,k3
  10.           key: k2
  11.       key_len: 4
  12.           ref: const
  13.          rows: 1
  14.      filtered: 100.00
  15.         Extra: NULL
复制代码
我们利用 EXPLAIN ANALYZE 来查看下两次执行计划的代价对比:
  1. -- 查看t1表执行计划代价
  2. [root@yejr.run]> EXPLAIN ANALYZE SELECT * FROM t1 WHERE
  3.   c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
  4. *************************** 1. row ***************************
  5. EXPLAIN: -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

  6. -- 查看t2表执行计划代价
  7. [root@yejr.run]> EXPLAIN ANALYZE SELECT * FROM t2 WHERE  c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
  8. *************************** 1. row ***************************
  9. EXPLAIN: -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)
复制代码
可以看到,很明显代价都是一样的。
再利用 OPTIMIZE_TRACE 查看执行计划,也能看到两个SQL的代价是一样的:
  1. ...
  2.           {
  3.             "rows_estimation": [
  4.               {
  5.                 "table": "`t1`",
  6.                 "rows": 1,
  7.                 "cost": 1,
  8.                 "table_type": "const",
  9.                 "empty": false
  10.               }
  11.             ]
  12.           },
  13. ...
复制代码
所以,优化器认为选择哪个索引都是一样的,就看哪个索引排序更靠前。
从执行SELECT时的debug trace结果也能佐证:
  1. -- 1、 T1表,k3索引在前面
  2.   PRIMARY KEY (`c1`),
  3.   UNIQUE KEY `k3` (`c3`),
  4.   UNIQUE KEY `k2` (`c2`)

  5. T@2: | | | | | | | | opt: (null): starting struct
  6. T@2: | | | | | | | | opt: table: "`t1`"
  7. T@2: | | | | | | | | opt: field: "c3"   (C3在前面,因此最后使用k3)
  8. T@2: | | | | | | | | >convert_string
  9. T@2: | | | | | | | | | >alloc_root
  10. T@2: | | | | | | | | | | enter: root: 0x40a8068
  11. T@2: | | | | | | | | | | exit: ptr: 0x4b41ab0
  12. T@2: | | | | | | | | | <alloc_root 304
  13. T@2: | | | | | | | | <convert_string 2610
  14. T@2: | | | | | | | | opt: equals: "'Louise Garrett'"
  15. T@2: | | | | | | | | opt: null_rejecting: 0
  16. T@2: | | | | | | | | opt: (null): ending struct
  17. T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1
  18. T@2: | | | | | | | | opt: (null): starting struct
  19. T@2: | | | | | | | | opt: table: "`t1`"
  20. T@2: | | | | | | | | opt: field: "c2"
  21. T@2: | | | | | | | | opt: equals: "22896242"
  22. T@2: | | | | | | | | opt: null_rejecting: 0
  23. T@2: | | | | | | | | opt: null_rejecting: 0
  24. T@2: | | | | | | | | opt: (null): ending struct
  25. T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1
  26. T@2: | | | | | | | | opt: (null): starting struct
  27. T@2: | | | | | | | | opt: table: "`t1`"
  28. T@2: | | | | | | | | opt: field: "c2"
  29. T@2: | | | | | | | | opt: equals: "22896242"
  30. T@2: | | | | | | | | opt: null_rejecting: 0
  31. T@2: | | | | | | | | opt: (null): ending struct
  32. T@2: | | | | | | | | opt: ref_optimizer_key_uses: ending struct
  33. T@2: | | | | | | | | opt: (null): ending struct

  34. -- 2、 T2表,k2索引在前面
  35.   PRIMARY KEY (`c1`),
  36.   UNIQUE KEY `k2` (`c2`),
  37.   UNIQUE KEY `k3` (`c3`)

  38. T@2: | | | | | | | | opt: (null): starting struct
  39. T@2: | | | | | | | | opt: table: "`t2`"
  40. T@2: | | | | | | | | opt: field: "c2" (C2在前面因此使用k2索引)
  41. T@2: | | | | | | | | opt: equals: "22896242"
  42. T@2: | | | | | | | | opt: null_rejecting: 0
  43. T@2: | | | | | | | | opt: (null): ending struct
  44. T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1
  45. T@2: | | | | | | | | opt: (null): starting struct
  46. T@2: | | | | | | | | opt: table: "`t2`"
  47. T@2: | | | | | | | | opt: field: "c3"
  48. T@2: | | | | | | | | >convert_string
  49. T@2: | | | | | | | | | >alloc_root
  50. T@2: | | | | | | | | | | enter: root: 0x40a8068
  51. T@2: | | | | | | | | | | exit: ptr: 0x4b41ab0
  52. T@2: | | | | | | | | | <alloc_root 304
  53. T@2: | | | | | | | | <convert_string 2610
  54. T@2: | | | | | | | | opt: equals: "'Louise Garrett'"
  55. T@2: | | | | | | | | opt: null_rejecting: 0
  56. T@2: | | | | | | | | opt: (null): ending struct
  57. T@2: | | | | | | | | opt: ref_optimizer_key_uses: ending struct
  58. T@2: | | | | | | | | opt: (null): ending struct
复制代码
4. 问题延伸
到这里,我们不禁有疑问,这两个索引的代价真的是一样吗?
就让我们用 mysqlslap 来做个简单对比测试吧:
  1. -- 测试1:对c2列随机point select
  2. mysqlslap -hlocalhost -uroot -Smysql.sock --no-drop --create-schema X -i 3 --number-of-queries 1000000 -q "set @xid = cast(round(rand()*2147265929) as unsigned); select * from t1 where c2 = @xid" -c 8
  3. ...
  4.     Average number of seconds to run all queries: 9.483 seconds
  5. ...

  6. -- 测试2:对c3列随机point select
  7. mysqlslap -hlocalhost -uroot -Smysql.sock --no-drop --create-schema X -i 3 --number-of-queries 1000000 -q "set @xid = concat('u',cast(round(rand()*2147265929) as unsigned)); select * from t1 where c3 = @xid" -c 8
  8. ...
  9.     Average number of seconds to run all queries: 10.360 seconds
  10. ...
复制代码
可以看到,如果是走 c3 列索引,耗时会比走 c2 列索引多出来约 7% ~ 9%(在我的环境下测试的结果,不同环境、不同数据量可能也不同)。
看来,MySQL优化器还是有必要进一步提高的哟 :)
测试使用版本:GreatSQL 8.0.25(MySQL 5.6.39结果亦是如此)。
Enjoy GreatSQL

GreatSQL万答

22

主题

0

博客

73

贡献

版主

Rank: 7Rank: 7Rank: 7

积分
117

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-3-29 17:16 , Processed in 0.017973 second(s), 12 queries , Redis On.
快速回复 返回顶部 返回列表