Chinaunix首页 | 论坛 | 博客

dingjun123的blog

首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1466046
  • 博文数量： 180
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3926
  • 用 户 组： 普通用户
  • 注册时间： 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文（180）

文章存档

2025年（8）

2024年（27）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
MySQL丢失order by cost计算导致走错索引

分类： Mysql/postgreSQL

2025-03-16 18:26:51

在MySQL里，如果order by + limit,where或join条件还有可选索引，有可能走错索引，默认的是偏向于走order by 列索引。

结构如下：

点击(此处)折叠或打开

  1. show create table empm\G 这个可以看索引创建的先后顺序
  2. *************************** 1. row ***************************
  3.        Table: empm
  4. Create Table: CREATE TABLE `empm` (
  5.   `emp_no` int NOT NULL,
  6.   `birth_date` date NOT NULL,
  7.   `first_name` varchar(100) CHARACTER SET utf8mb3 NOT NULL,
  8.   `last_name` varchar(16) CHARACTER SET utf8mb3 DEFAULT NULL,
  9.   `gender` enum('M','F') CHARACTER SET utf8mb3 NOT NULL,
  10.   `hire_date` date NOT NULL,
  11.   `upper_first_name` varchar(100) CHARACTER SET utf8mb3 DEFAULT NULL,
  12.   PRIMARY KEY (`emp_no`),
  13.   KEY `idx_empm` (`first_name`,`last_name`),
  14.   KEY `idx1_empm` (`first_name`),
  15.   KEY `idx2_empm` (`first_name`,`last_name`,`hire_date`),
  16.   KEY `idx3_empm` (`first_name`,`hire_date`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  18. 1 row in set (0.01 sec)


  21. *************************** 1. row ***************************
  22.            Name: empm
  23.          Engine: InnoDB
  24.         Version: 10
  25.      Row_format: Dynamic
  26.            Rows: 199422
  27.  Avg_row_length: 65
  28.     Data_length: 13107200
  29. Max_data_length: 0
  30.    Index_length: 23920640
  31.       Data_free: 5242880
  32.  Auto_increment: NULL
  33.     Create_time: 2024-12-18 17:15:17
  34.     Update_time: NULL
  35.      Check_time: NULL
  36.       Collation: utf8mb4_0900_ai_ci
  37.        Checksum: NULL
  38.  Create_options:
  39.         Comment:
  40. 1 row in set (0.00 sec)

  42. 索引如下：
  43.  show index from empm; 这个可以看索引创建的先后顺序
  44. +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  45. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
  46. +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  47. | empm | 0 | PRIMARY | 1 | emp_no | A | 199422 | NULL | NULL | | BTREE | | | YES | NULL |
  48. | empm | 1 | idx_empm | 1 | first_name | A | 1260 | NULL | NULL | | BTREE | | | YES | NULL |
  49. | empm | 1 | idx_empm | 2 | last_name | A | 147521 | NULL | NULL | YES | BTREE | | | YES | NULL |
  50. | empm | 1 | idx1_empm | 1 | first_name | A | 1334 | NULL | NULL | | BTREE | | | YES | NULL |
  51. | empm | 1 | idx2_empm | 1 | first_name | A | 1299 | NULL | NULL | | BTREE | | | YES | NULL |
  52. | empm | 1 | idx2_empm | 2 | last_name | A | 143264 | NULL | NULL | YES | BTREE | | | YES | NULL |
  53. | empm | 1 | idx2_empm | 3 | hire_date | A | 199422 | NULL | NULL | | BTREE | | | YES | NULL |
  54. | empm | 1 | idx3_empm | 1 | first_name | A | 1269 | NULL | NULL | | BTREE | | | YES | NULL |
  55. | empm | 1 | idx3_empm | 2 | hire_date | A | 156396 | NULL | NULL | | BTREE | | | YES | NULL |
  56. +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  57. 9 rows in set (0.00 sec)

  59. 这个不能看索引创建的先后顺序：
  60. select * from mysql.innodb_index_stats where table_name='empm';
  61. +---------------+------------+------------+---------------------+--------------+------------+-------------+---------------------------------------+
  62. | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
  63. +---------------+------------+------------+---------------------+--------------+------------+-------------+---------------------------------------+
  64. | employees | empm | PRIMARY | 2024-12-18 17:15:23 | n_diff_pfx01 | 199422 | 20 | emp_no |
  65. | employees | empm | PRIMARY | 2024-12-18 17:15:23 | n_leaf_pages | 691 | NULL | Number of leaf pages in the index |
  66. | employees | empm | PRIMARY | 2024-12-18 17:15:23 | size | 800 | NULL | Number of pages in the index |
  67. | employees | empm | idx1_empm | 2024-12-18 17:15:23 | n_diff_pfx01 | 1334 | 20 | first_name |
  68. | employees | empm | idx1_empm | 2024-12-18 17:15:23 | n_diff_pfx02 | 189886 | 20 | first_name,emp_no |
  69. | employees | empm | idx1_empm | 2024-12-18 17:15:23 | n_leaf_pages | 203 | NULL | Number of leaf pages in the index |
  70. | employees | empm | idx1_empm | 2024-12-18 17:15:23 | size | 288 | NULL | Number of pages in the index |
  71. | employees | empm | idx2_empm | 2024-12-18 17:15:23 | n_diff_pfx01 | 1299 | 20 | first_name |
  72. | employees | empm | idx2_empm | 2024-12-18 17:15:23 | n_diff_pfx02 | 143264 | 20 | first_name,last_name |
  73. | employees | empm | idx2_empm | 2024-12-18 17:15:23 | n_diff_pfx03 | 200569 | 20 | first_name,last_name,hire_date |
  74. | employees | empm | idx2_empm | 2024-12-18 17:15:23 | n_diff_pfx04 | 199408 | 20 | first_name,last_name,hire_date,emp_no |
  75. | employees | empm | idx2_empm | 2024-12-18 17:15:23 | n_leaf_pages | 352 | NULL | Number of leaf pages in the index |
  76. | employees | empm | idx2_empm | 2024-12-18 17:15:23 | size | 417 | NULL | Number of pages in the index |
  77. | employees | empm | idx3_empm | 2024-12-18 17:15:23 | n_diff_pfx01 | 1269 | 20 | first_name |
  78. | employees | empm | idx3_empm | 2024-12-18 17:15:23 | n_diff_pfx02 | 156396 | 20 | first_name,hire_date |
  79. | employees | empm | idx3_empm | 2024-12-18 17:15:23 | n_diff_pfx03 | 199752 | 20 | first_name,hire_date,emp_no |
  80. | employees | empm | idx3_empm | 2024-12-18 17:15:23 | n_leaf_pages | 240 | NULL | Number of leaf pages in the index |
  81. | employees | empm | idx3_empm | 2024-12-18 17:15:23 | size | 289 | NULL | Number of pages in the index |
  82. | employees | empm | idx_empm | 2024-12-18 17:15:23 | n_diff_pfx01 | 1260 | 20 | first_name |
  83. | employees | empm | idx_empm | 2024-12-18 17:15:23 | n_diff_pfx02 | 147521 | 20 | first_name,last_name |
  84. | employees | empm | idx_empm | 2024-12-18 17:15:23 | n_diff_pfx03 | 207457 | 20 | first_name,last_name,emp_no |
  85. | employees | empm | idx_empm | 2024-12-18 17:15:23 | n_leaf_pages | 342 | NULL | Number of leaf pages in the index |
  86. | employees | empm | idx_empm | 2024-12-18 17:15:23 | size | 466 | NULL | Number of pages in the index |
  87. +---------------+------------+------------+---------------------+--------------+------------+-------------+---------------------------------------+
  88. 23 rows in set (0.00 sec)


问题如下：
SQL1竟然默认走idx_empm(first_name,last_name)，那么order by emp_no desc这部分就走不了索引，因为没有last_name。
应该走SQL2的所有idx1_empm(first_name)才对，因为所以默认包含主键emp_no,前导列等值，可以利用reverse扫描，消除排序。


从估算的cost看，两条SQL cost一样，都是cost=10291.95，所以按照索引谁先创建的走谁。 idx_empm先于idx1_emp创建，所以走idx_empm,
为什么cost一样呢？因为比较访问路径时候不考虑排序，可以从optimizer trace看出，这个和limit分页里丢掉order by 列是一样的问题，是BUG
###############
这个BUG主要是order by pk或order by pk desc上，在索引访问路径选择的时候没有考虑cost,然后计算order by的时候丢掉了排序列，有undefined。


单表，使用index dive计算选择率。


###########
mysql不计算访问路径不考虑排序列，等到排序优化，看能不能通过索引消除排序，直接启发式选择。

点击(此处)折叠或打开

  1. SQL1:
  2. explain analyze select * from empm where first_name = 'Aamer' order by emp_no desc\G
  3. *************************** 1. row ***************************
  4. EXPLAIN: -> Sort: empm.emp_no DESC (cost=10291.95 rows=96912) (actual time=136.452..143.969 rows=50106 loops=1)
  5.     -> Index lookup on empm using idx_empm (first_name='Aamer') (actual time=0.070..113.065 rows=50106 loops=1)

  7. 1 row in set (0.15 sec)

  9. SQL2:
  10. explain analyze select/*+index(empm idx1_empm)*/ * from empm where first_name = 'Aamer' order by emp_no desc\G
  11. *************************** 1. row ***************************
  12. EXPLAIN: -> Index lookup on empm using idx1_empm (first_name='Aamer') (reverse) (cost=10291.95 rows=96912) (actual time=1.158..85.386 rows=50106 loops=1)

  14. 1 row in set (0.10 sec)

看看trace:
可以看到，因为先比较访问路径时候，没有考虑排序成本，然后idx_empm先计算的，和idx1_empm cost一样，所以选择了idx_empm,
{BANNED}最佳后考虑索引排序，发现排序字段是undefined,中间过程丢掉了，这个是mysql源代码部分丢掉了中间过程，是代码BUG。

这个BUG主要是order by pk或order by pk desc上，order by非主键不会有这个问题。


点击(此处)折叠或打开

  1. set optimizer_trace=1;
  2. explain analyze select * from empm where first_name = 'Aamer' order by emp_no desc\G
  3. select * from information_schema.optimizer_trace\G


  6.  {
  7.             "rows_estimation": [
  8.               {
  9.                 "table": "`empm`",
  10.                 "range_analysis": {
  11.                   "table_scan": {
  12.                     "rows": 193825,
  13.                     "cost": 19584.8 --- 全表扫描cost
  14.                   },
  15.                   "potential_range_indexes": [
  16.                     {
  17.                       "index": "PRIMARY",
  18.                       "usable": false,
  19.                       "cause": "not_applicable"
  20.                     },
  21.                     {
  22.                       "index": "idx_empm",
  23.                       "usable": true,
  24.                       "key_parts": [
  25.                         "first_name",
  26.                         "last_name",
  27.                         "emp_no"
  28.                       ]
  29.                     },
  30.                     {
  31.                       "index": "idx1_empm",
  32.                       "usable": true,
  33.                       "key_parts": [
  34.                         "first_name",
  35.                         "emp_no"
  36.                       ]
  37.                     },
  38.                     {
  39.                       "index": "idx2_empm",
  40.                       "usable": true,
  41.                       "key_parts": [
  42.                         "first_name",
  43.                         "hire_date",
  44.                         "emp_no"
  45.                       ]
  46.                     }
  47.                   ],
  48.                   "setup_range_conditions": [
  49.                   ],
  50.                   "group_index_range": {
  51.                     "chosen": false,
  52.                     "cause": "not_group_by_or_distinct"
  53.                   },
  54.                   "skip_scan_range": {
  55.                     "potential_skip_scan_indexes": [
  56.                       {
  57.                         "index": "idx_empm",
  58.                         "usable": false,
  59.                         "cause": "query_references_nonkey_column"
  60.                       },
  61.                       {
  62.                         "index": "idx1_empm",
  63.                         "usable": false,
  64.                         "cause": "query_references_nonkey_column"
  65.                       },
  66.                       {
  67.                         "index": "idx2_empm",
  68.                         "usable": false,
  69.                         "cause": "query_references_nonkey_column"
  70.                       }
  71.                     ]
  72.                   },
  73.                   "analyzing_range_alternatives": {
  74.                     "range_scan_alternatives": [
  75.                       {
  76.                         "index": "idx_empm",
  77.                         "ranges": [
  78.                           "first_name = 'Aamer'"
  79.                         ],
  80.                         "index_dives_for_eq_ranges": true,
  81.                         "rowid_ordered": false,
  82.                         "using_mrr": false,
  83.                         "index_only": false,
  84.                         "in_memory": 1,
  85.                         "rows": 96912,
  86.                         "cost": 33919.5, -- 索引扫描cost >全表扫描的cost
  87.                         "chosen": false, --不选择
  88.                         "cause": "cost"
  89.                       },
  90.                       {
  91.                         "index": "idx1_empm",
  92.                         "ranges": [
  93.                           "first_name = 'Aamer'"
  94.                         ],
  95.                         "index_dives_for_eq_ranges": true,
  96.                         "rowid_ordered": true,
  97.                         "using_mrr": false,
  98.                         "index_only": false,
  99.                         "in_memory": 0.349754,
  100.                         "rows": 96912,
  101.                         "cost": 33919.5, -- 索引扫描cost >全表扫描的cost
  102.                         "chosen": false, --不选择
  103.                         "cause": "cost"
  104.                       },
  105.                       {
  106.                         "index": "idx2_empm",
  107.                         "ranges": [
  108.                           "first_name = 'Aamer'"
  109.                         ],
  110.                         "index_dives_for_eq_ranges": true,
  111.                         "rowid_ordered": false,
  112.                         "using_mrr": false,
  113.                         "index_only": false,
  114.                         "in_memory": 0,
  115.                         "rows": 96912,
  116.                         "cost": 33919.5,
  117.                         "chosen": false,
  118.                         "cause": "cost"
  119.                       }
  120.                     ],
  121.                     "analyzing_roworder_intersect": {
  122.                       "usable": false, -- range scan没有被选择
  123.                       "cause": "too_few_roworder_scans"
  124.                     }
  125.                   }
  126.                 }
  127.               }
  128.             ]
  129.           },
  130.           {
  131.             "considered_execution_plans": [
  132.               {
  133.                 "plan_prefix": [
  134.                 ],
  135.                 "table": "`empm`",
  136.                 "best_access_path": {
  137.                   "considered_access_paths": [
  138.                     {
  139.                       "access_type": "ref", --- 计算ref
  140.                       "index": "idx_empm",
  141.                       "rows": 96912,
  142.                       "cost": 10292, -- 先计算的idx_empm,cost=10292,比全表扫描的小，所以走idx_empm的ref扫描
  143.                       "chosen": true -- 选择这个访问路径，问题是排序成本没有加上去
  144.                     },
  145.                     {
  146.                       "access_type": "ref",
  147.                       "index": "idx1_empm",
  148.                       "rows": 96912,
  149.                       "cost": 10292, -- idx1_empm的ref扫描，cost和idx_empm一样，所以不用，这里其实因为idx_empm ref，
  150.                       "chosen": false --问题是排序成本没有加上去
  151.                     },
  152.                     {
  153.                       "access_type": "ref",
  154.                       "index": "idx2_empm",
  155.                       "rows": 96912,
  156.                       "cost": 10292,
  157.                       "chosen": false
  158.                     },
  159.                     {
  160.                       "rows_to_scan": 193825,
  161.                       "filtering_effect": [
  162.                       ],
  163.                       "final_filtering_effect": 0.499997,
  164.                       "access_type": "scan",
  165.                       "resulting_rows": 96912,
  166.                       "cost": 19582.8,
  167.                       "chosen": false
  168.                     }
  169.                   ]
  170.                 },
  171.                 "condition_filtering_pct": 100,
  172.                 "rows_for_plan": 96912,
  173.                 "cost_for_plan": 10292,
  174.                 "chosen": true
  175.               }
  176.             ]
  177.           },
  178.           {
  179.             "attaching_conditions_to_tables": {
  180.               "original_condition": "(`empm`.`first_name` = 'Aamer')",
  181.               "attached_conditions_computation": [
  182.               ],
  183.               "attached_conditions_summary": [
  184.                 {
  185.                   "table": "`empm`",
  186.                   "attached": "(`empm`.`first_name` = 'Aamer')"
  187.                 }
  188.               ]
  189.             }
  190.           },
  191.           {
  192.             "optimizing_distinct_group_by_order_by": {
  193.               "simplifying_order_by": {
  194.                 "original_clause": "`empm`.`emp_no` desc",
  195.                 "items": [
  196.                   {
  197.                     "item": "`empm`.`emp_no`"
  198.                   }
  199.                 ],
  200.                 "resulting_clause_is_simple": true,
  201.                 "resulting_clause": "`empm`.`emp_no` desc"
  202.               }
  203.             }
  204.           },
  205.           {
  206.             "reconsidering_access_paths_for_index_ordering": { --- 使用索引的排序。。。
  207.               "clause": "ORDER BY",
  208.               "steps": [
  209.               ],
  210.               "index_order_summary": {
  211.                 "table": "`empm`",
  212.                 "index_provides_order": false,
  213.                 "order_direction": "undefined", -- 发现这部分是undefined，中间order by部分丢掉了，所以这个索引cost计算丢掉了排序成本
  214.                 "index": "idx_empm",
  215.                 "plan_changed": false
  216.               }
  217.             }
  218.           },
  219.           {
  220.             "finalizing_table_conditions": [
  221.               {
  222.                 "table": "`empm`",
  223.                 "original_table_condition": "(`empm`.`first_name` = 'Aamer')",
  224.                 "final_table_condition ": null
  225.               }
  226.             ]
  227.           },
  228.           {
  229.             "refine_plan": [
  230.               {
  231.                 "table": "`empm`"
  232.               }
  233.             ]
  234.           },
  235.           {
  236.             "considering_tmp_tables": [
  237.               {
  238.                 "adding_sort_to_table": "empm"
  239.               }
  240.             ]
  241.           }
  242.         ]
  243.       }
  244.     },
  245.     {
  246.       "join_execution": {
  247.         "select#": 1,
  248.         "steps": [
  249.           {
  250.             "sorting_table": "empm",
  251.             "filesort_information": [
  252.               {
  253.                 "direction": "desc",
  254.                 "expression": "`empm`.`emp_no`"
  255.               }
  256.             ],
  257.             "filesort_priority_queue_optimization": {
  258.               "usable": false,
  259.               "cause": "not applicable (no LIMIT)"
  260.             },
  261.             "filesort_execution": [
  262.             ],
  263.             "filesort_summary": {
  264.               "memory_available": 262144,
  265.               "key_size": 8,
  266.               "row_size": 677,
  267.               "max_rows_per_buffer": 387,
  268.               "num_rows_estimate": 18446744073709551615,
  269.               "num_rows_found": 50106,
  270.               "num_initial_chunks_spilled_to_disk": 12,
  271.               "peak_memory_used": 294912,
  272.               "sort_algorithm": "std::stable_sort",
  273.               "sort_mode": ""
  274.             }
  275.           }
  276.         ]
  277.       }
  278.     }
  279.   ]
  280. }





阅读(40) | 评论(0) | 转发(0) |
0

上一篇：oracle,mysql范围越界的选择率估算

下一篇：MySQL的rows_examined指标缺陷总结

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6