经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MySQL » 查看文章
复现MySQL的索引选择失误以及通过OPTIMIZER_TRACE分析过程
来源：cnblogs　　作者：starmoon1900　　时间：2022/12/2 11:02:54　　对本文有异议

复现MySQL的索引选择失误以及通过OPTIMIZER_TRACE分析过程

验证环境：MySQL 5.7.39 windows-pc

一、构造数据（生成150万数据）

构建一张账户表，带有一级部门id和二级部门id，并且建立有索引。比较典型的业务场景，根据部门id进行各类查询。

  1. CREATE TABLE `TM_ACCOUNT` (
  2.   `account_id` bigint(20)  NOT null ,
  3.   `name` varchar(32) DEFAULT '',
  4.   `address` varchar(32) DEFAULT '',
  5.   `org_first_id` int(10) DEFAULT 0,
  6.   `org_second_id` int(10) DEFAULT 0,
  7.   `biz_date` date DEFAULT null,
  8.   `last_modify_dt` datetime DEFAULT null,
  9.   PRIMARY KEY (`account_id`),
  10.   KEY IDX_org_id_combine(org_first_id,org_second_id),
  11.   KEY IDX_last_modify_dt_org_first_id_name(last_modify_dt,org_first_id,org_second_id)
  12. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

1. 构造数据

此处直接通过jdbc批量插入数据。

数据分布，保证数据无倾斜，索引数据均匀：

  1. org_first_id和org_second_id字段都是在1-100间随机分布
  2. last_modify_dt在25天间随机分布

代码可以直接使用，详情见附件3

二、通过explain验证语句的索引使用

查看表的基本情况

  1. show index from TM_ACCOUNT ;  -- 看索引

执行结果，可以看到org_first_id/org_second_id的区分度，都很不错。

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
tm_account 0 PRIMARY 1 account_id A 1408599 BTREE
tm_account 1 IDX_org_id_combine 1 org_first_id A 101 YES BTREE
tm_account 1 IDX_org_id_combine 2 org_second_id A 10611 YES BTREE
tm_account 1 IDX_last_modify_dt_org_first_id_name 1 last_modify_dt A 24 YES BTREE
tm_account 1 IDX_last_modify_dt_org_first_id_name 2 org_first_id A 2497 YES BTREE
tm_account 1 IDX_last_modify_dt_org_first_id_name 3 org_second_id A 251724 YES BTREE 
  1. show table status like '%TM_ACCOUNT%'; -- 看表状态，有数据大小、索引大小、大概行数

可看到使用了InnoDB引擎，大概行数是1408599，实际行数是1500000整。

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
tm_account InnoDB 10 Dynamic 1408599 83 118128640 0 128253952 7340032 2022-09-13 10:49:36 utf8mb4_general_ci

常规的查询

explain SELECT * from TM_ACCOUNT where ACCOUNT_ID = '10'; -- 典型的主键字段查询，非常快，type=const

explain SELECT * from TM_ACCOUNT where ACCOUNT_ID = '10'; -- 典型的主键字段查询，非常快，type=const

explain SELECT * from TM_ACCOUNT where NAME = 'name-11'; -- 典型的非索引字段查询，全表扫描

explain SELECT * from TM_ACCOUNT where ADDRESS = 'QR3xHEOpaLAVNFCtAKXY'; -- 典型的非索引字段查询，全表扫描

explain SELECT * from TM_ACCOUNT where LAST_MODIFY_DT = '2100-09-13 00:00:00' and ACCOUNT_ID > 100 LIMIT 2; -- 典型的范围查询，扫描索引。单速度也很快

通过改变查询条件，引导MySQL优化器，选择错误的索引、规则

下面通过3个SQL查询的结果对比，来复现MySQL优化器如何选错优化场景。（这里不讨论为何不换种写法，直接规避劣化SQL。往往出现这类SQL时，一是业务场景复杂，二是开发时数据量少并未发现，在生产环境才能出现）

  1. -- SQL-1 
  2. explain
  3. SELECT * from TM_ACCOUNT where org_first_id >= 99 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02')and ACCOUNT_ID > '120306' order by ACCOUNT_ID  desc LIMIT 5000;

查询结果：可见使用了IDX_org_id_combine索引，并用到索引范围扫描、回表查询、临时文件排序。不算是一个很好的查询语句，但实际业务中的查询条件，只会更复杂。直接查询耗时140ms。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine IDX_org_id_combine 18 33942 4.0 Using index condition; Using where; Using filesort 
  1. -- SQL-2 坏案例-全表扫描； 
  2. explain
  3. SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 5000;

查询结果：改变org_first_id条件，扩大查询范围，结果变成了主键索引的大范围扫描，预估扫描行数70万行，几乎是表总数的一半。直接查询耗时3900ms。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine PRIMARY 8 704299 1.68 Using where 
  1. -- SQL-3 与SQL-1基本相同，但limit数量减少。
  2. explain  
  3. SELECT * from TM_ACCOUNT where org_first_id >= 99 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 500;

查询结果：与SQL-1基本相同，但limit数量减少，即查询条件范围缩小，劣化成主键大范围扫描。 直接查询耗时1210ms。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine PRIMARY 8 704299 0.19 Using where

三、复现索引选择劣化、并尝试分析OPTIMIZER_TRACE

执行相关命令，获取OPTIMIZER_TRACE过程。

  1. /* 打开optimizer_trace，只对本线程有效 */
  2. SET optimizer_trace='enabled=on'; 
  3. #你的sql
  4. -- select ......; 
  5. SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 5000;
  6. #查看优化器追踪链
  7. select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
  8. #关闭优化器追踪
  9. SET optimizer_trace='enabled=off';

关键过程：通过对潜在查询方式的预估，分别对PRIMARY/IDX_org_id_combine的开销进行评估，这里开销并不仅看扫描行数，还会看排序等情况。可以看到虽然走主键索引的行数更多，但总开销更小。由此可知在【预估】过程，误导了整个优化器。

共有2个潜在选项，分别标出了rowid是否排序、行数rows、预估开销cost

  1. PRIMARY，范围是"120306 < account_id"
  2. IDX_org_id_combine，范围是"90 <= org_first_id"

截取部分OPTIMIZER_TRACE结果，完整json参考附录1

  1.     // 分析可供选择的范围条件
  2.     "analyzing_range_alternatives": {
  3.         "range_scan_alternatives": [
  4.         {
  5.             "index": "PRIMARY",
  6.             "ranges": [
  7.             "120306 < account_id"
  8.             ],
  9.             "index_dives_for_eq_ranges": true,
  10.             "rowid_ordered": true,
  11.             "using_mrr": false,
  12.             "index_only": false,
  13.             "rows": 704299,
  14.             "cost": 141880,
  15.             "chosen": true
  16.         },
  17.         {
  18.             "index": "IDX_org_id_combine",
  19.             "ranges": [
  20.             "90 <= org_first_id"
  21.             ],
  22.             "index_dives_for_eq_ranges": true,
  23.             "rowid_ordered": false,
  24.             "using_mrr": false,
  25.             "index_only": false,
  26.             "rows": 295138,
  27.             "cost": 354167,
  28.             "chosen": false,
  29.             "cause": "cost"
  30.         }
  31.         ],
  32.         "analyzing_roworder_intersect": {
  33.         "usable": false,
  34.         "cause": "too_few_roworder_scans"
  35.         }
  36.     },
  37.     // 最终选择的路径
  38.     "chosen_range_access_summary": {
  39.         "range_access_plan": {
  40.         "type": "range_scan",
  41.         "index": "PRIMARY",
  42.         "rows": 704299,
  43.         "ranges": [
  44.             "120306 < account_id"
  45.         ]
  46.         },
  47.         "rows_for_plan": 704299,
  48.         "cost_for_plan": 141880,
  49.         "chosen": true
  50.     }

这里怀疑是order by ACCOUNT_ID影响了优化器选择，但通测试发现，即使移除了'order by ACCOUNT_ID desc LIMIT 5000'，explain结果仍然是走PRIMARY索引。由此可见，还有些隐藏的信息，OPTIMIZER_TRACE没有展示全。这里暂不深入讨论。

  1. explain
  2. SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine PRIMARY 8 704299 1.68 Using where

结果：实际查询耗时912ms。在【## 附录2 OPTIMIZER_TRACE原始信息2】中也能看到选择实际索引，仍然是PRIMARY，与explain结果一致。

四、如何优化？

改写SQL：

  1. 通过配置、distinct org_first_id等方式，将org_first_id的范围固定下来，并缓存
  2. 改写SQL，将org_first_id >= 90 改写为 org_first_id IN (xxxxx)

下面来看效果

  1. explain
  2. SELECT * from TM_ACCOUNT where org_first_id in ('90','91','92','93','94','95','96','97','98','99') and org_second_id in (1,2,3,60) and BIZ_DATE  in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc   LIMIT 5000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE TM_ACCOUNT range PRIMARY,IDX_org_id_combine IDX_org_id_combine 18 5543 20.0 Using index condition; Using where; Using filesort

结果：实际查询耗时59ms。explain结果可看到虽然也用了IDX_org_id_combine索引，但仍然是range查询、回表、filesort，好在扫描行数较少，最终耗时很小。

思考，改写SQL是最佳解决方案吗？

随着数据量的增大，无论多么简单的SQL，最终仍然会变慢。

其他方式：

  1. 数据归档。 建立历史表、大数据抽数归档冷数据。
  2. 引入专门的OLAP系统，不在OLTP系统做复杂的业务查询。引入ES、hive、HBASE等组件，专业的事交给专业的人去做。

其他

  1. 打开optimizer_trace，只对本线程有效。建议使用命令行窗口，直连db。通过Navicat等客户端，可能会记录失败。
  2. 一般optimizer_trace只在root用户下才能使用
  3. mariadb直到10.4版本才有Optimizer Trace， 之前的版本执行'SET optimizer_trace='enabled=on'; '会返回错误 。官网链接https://mariadb.com/resources/blog/optimizer-trace-in-mariadb-server-10-4/

附录1 OPTIMIZER_TRACE原始信息1

以下语句的执行优化过程
SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' order by ACCOUNT_ID desc LIMIT 5000;

  1. {
  2.     "steps": [
  3.       {
  4.         "join_preparation": {
  5.           "select#": 1,
  6.           "steps": [
  7.             {
  8.               "IN_uses_bisection": true
  9.             },
  10.             {
  11.               "IN_uses_bisection": true
  12.             },
  13.             {
  14.               "expanded_query": "/* select#1 */ select `tm_account`.`account_id` AS `account_id`,`tm_account`.`name` AS `name`,`tm_account`.`address` AS `address`,`tm_account`.`org_first_id` AS `org_first_id`,`tm_account`.`org_second_id` AS `org_second_id`,`tm_account`.`biz_date` AS `biz_date`,`tm_account`.`last_modify_dt` AS `last_modify_dt` from `tm_account` where ((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306)) order by `tm_account`.`account_id` desc limit 5000"
  15.             }
  16.           ]
  17.         }
  18.       },
  19.       {
  20.         "join_optimization": {
  21.           "select#": 1,
  22.           "steps": [
  23.             {
  24.               "condition_processing": {
  25.                 "condition": "WHERE",
  26.                 "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
  27.                 "steps": [
  28.                   {
  29.                     "transformation": "equality_propagation",
  30.                     "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  31.                   },
  32.                   {
  33.                     "transformation": "constant_propagation",
  34.                     "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  35.                   },
  36.                   {
  37.                     "transformation": "trivial_condition_removal",
  38.                     "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  39.                   }
  40.                 ]
  41.               }
  42.             },
  43.             {
  44.               "substitute_generated_columns": {}
  45.             },
  46.             {
  47.               "table_dependencies": [
  48.                 {
  49.                   "table": "`tm_account`",
  50.                   "row_may_be_null": false,
  51.                   "map_bit": 0,
  52.                   "depends_on_map_bits": []
  53.                 }
  54.               ]
  55.             },
  56.             {
  57.               "ref_optimizer_key_uses": []
  58.             },
  59.             {
  60.                 // 行数预估
  61.               "rows_estimation": [
  62.                 {
  63.                   "table": "`tm_account`",
  64.                   "range_analysis": {
  65.                     "table_scan": {
  66.                       "rows": 1408599,
  67.                       "cost": 288932
  68.                     },
  69.                     "potential_range_indexes": [
  70.                       {
  71.                         "index": "PRIMARY",
  72.                         "usable": true,
  73.                         "key_parts": [
  74.                           "account_id"
  75.                         ]
  76.                       },
  77.                       {
  78.                         "index": "IDX_org_id_combine",
  79.                         "usable": true,
  80.                         "key_parts": [
  81.                           "org_first_id",
  82.                           "org_second_id",
  83.                           "account_id"
  84.                         ]
  85.                       },
  86.                       {
  87.                         "index": "IDX_last_modify_dt_org_first_id_name",
  88.                         "usable": false,
  89.                         "cause": "not_applicable" // 直接标明不适用
  90.                       }
  91.                     ],
  92.                     "setup_range_conditions": [],
  93.                     "group_index_range": {
  94.                       "chosen": false,
  95.                       "cause": "not_group_by_or_distinct"
  96.                     },
  97.                     // 分析可供选择的范围条件
  98.                     "analyzing_range_alternatives": {
  99.                       "range_scan_alternatives": [
  100.                         {
  101.                           "index": "PRIMARY",
  102.                           "ranges": [
  103.                             "120306 < account_id"
  104.                           ],
  105.                           "index_dives_for_eq_ranges": true,
  106.                           "rowid_ordered": true,
  107.                           "using_mrr": false,
  108.                           "index_only": false,
  109.                           "rows": 704299,
  110.                           "cost": 141880,
  111.                           "chosen": true
  112.                         },
  113.                         {
  114.                           "index": "IDX_org_id_combine",
  115.                           "ranges": [
  116.                             "90 <= org_first_id"
  117.                           ],
  118.                           "index_dives_for_eq_ranges": true,
  119.                           "rowid_ordered": false,
  120.                           "using_mrr": false,
  121.                           "index_only": false,
  122.                           "rows": 295138,
  123.                           "cost": 354167,
  124.                           "chosen": false,
  125.                           "cause": "cost"
  126.                         }
  127.                       ],
  128.                       "analyzing_roworder_intersect": {
  129.                         "usable": false,
  130.                         "cause": "too_few_roworder_scans"
  131.                       }
  132.                     },
  133.                     "chosen_range_access_summary": {
  134.                       "range_access_plan": {
  135.                         "type": "range_scan",
  136.                         "index": "PRIMARY",
  137.                         "rows": 704299,
  138.                         "ranges": [
  139.                           "120306 < account_id"
  140.                         ]
  141.                       },
  142.                       "rows_for_plan": 704299,
  143.                       "cost_for_plan": 141880,
  144.                       "chosen": true
  145.                     }
  146.                   }
  147.                 }
  148.               ]
  149.             },
  150.             {
  151.               "considered_execution_plans": [
  152.                 {
  153.                   "plan_prefix": [],
  154.                   "table": "`tm_account`",
  155.                   "best_access_path": {
  156.                     "considered_access_paths": [
  157.                       {
  158.                         "rows_to_scan": 704299,
  159.                         "access_type": "range",
  160.                         "range_details": {
  161.                           "used_index": "PRIMARY"
  162.                         },
  163.                         "resulting_rows": 11806,
  164.                         "cost": 282740,
  165.                         "chosen": true
  166.                       }
  167.                     ]
  168.                   },
  169.                   "condition_filtering_pct": 100,
  170.                   "rows_for_plan": 11806,
  171.                   "cost_for_plan": 282740,
  172.                   "chosen": true
  173.                 }
  174.               ]
  175.             },
  176.             {
  177.               "attaching_conditions_to_tables": {
  178.                 "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
  179.                 "attached_conditions_computation": [
  180.                   {
  181.                     "table": "`tm_account`",
  182.                     "rechecking_index_usage": {
  183.                       "recheck_reason": "low_limit",
  184.                       "limit": 5000,
  185.                       "row_estimate": 11806,
  186.                       "range_analysis": {
  187.                         "table_scan": {
  188.                           "rows": 1408599,
  189.                           "cost": 1690000
  190.                         },
  191.                         "potential_range_indexes": [
  192.                           {
  193.                             "index": "PRIMARY",
  194.                             "usable": true,
  195.                             "key_parts": [
  196.                               "account_id"
  197.                             ]
  198.                           },
  199.                           {
  200.                             "index": "IDX_org_id_combine",
  201.                             "usable": false,
  202.                             "cause": "not_applicable"
  203.                           },
  204.                           {
  205.                             "index": "IDX_last_modify_dt_org_first_id_name",
  206.                             "usable": false,
  207.                             "cause": "not_applicable"
  208.                           }
  209.                         ],
  210.                         "setup_range_conditions": [],
  211.                         "group_index_range": {
  212.                           "chosen": false,
  213.                           "cause": "cannot_do_reverse_ordering"
  214.                         },
  215.                         "analyzing_range_alternatives": {
  216.                           "range_scan_alternatives": [
  217.                             {
  218.                               "index": "PRIMARY",
  219.                               "ranges": [
  220.                                 "120306 < account_id"
  221.                               ],
  222.                               "index_dives_for_eq_ranges": true,
  223.                               "rowid_ordered": true,
  224.                               "using_mrr": false,
  225.                               "index_only": false,
  226.                               "rows": 704299,
  227.                               "cost": 141880,
  228.                               "chosen": true
  229.                             }
  230.                           ]
  231.                         },
  232.                         "chosen_range_access_summary": {
  233.                           "range_access_plan": {
  234.                             "type": "range_scan",
  235.                             "index": "PRIMARY",
  236.                             "rows": 704299,
  237.                             "ranges": [
  238.                               "120306 < account_id"
  239.                             ]
  240.                           },
  241.                           "rows_for_plan": 704299,
  242.                           "cost_for_plan": 141880,
  243.                           "chosen": true
  244.                         }
  245.                       }
  246.                     }
  247.                   }
  248.                 ],
  249.                 "attached_conditions_summary": [
  250.                   {
  251.                     "table": "`tm_account`",
  252.                     "attached": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  253.                   }
  254.                 ]
  255.               }
  256.             },
  257.             {
  258.               "clause_processing": {
  259.                 "clause": "ORDER BY",
  260.                 "original_clause": "`tm_account`.`account_id` desc",
  261.                 "items": [
  262.                   {
  263.                     "item": "`tm_account`.`account_id`"
  264.                   }
  265.                 ],
  266.                 "resulting_clause_is_simple": true,
  267.                 "resulting_clause": "`tm_account`.`account_id` desc"
  268.               }
  269.             },
  270.             {
  271.               "reconsidering_access_paths_for_index_ordering": {
  272.                 "clause": "ORDER BY",
  273.                 "steps": [],
  274.                 "index_order_summary": {
  275.                   "table": "`tm_account`",
  276.                   "index_provides_order": true,
  277.                   "order_direction": "desc",
  278.                   "index": "PRIMARY",
  279.                   "plan_changed": false
  280.                 }
  281.               }
  282.             },
  283.             {
  284.               "refine_plan": [
  285.                 {
  286.                   "table": "`tm_account`"
  287.                 }
  288.               ]
  289.             }
  290.           ]
  291.         }
  292.       },
  293.       {
  294.         "join_execution": {
  295.           "select#": 1,
  296.           "steps": []
  297.         }
  298.       }
  299.     ]
  300.   }

附录2 OPTIMIZER_TRACE原始信息2

SELECT * from TM_ACCOUNT where org_first_id >= 90 and org_second_id in (1,2,3,60) and BIZ_DATE in ('2100-09-01','2100-09-02') and ACCOUNT_ID > '120306' ;

  2. {
  3.   "steps": [
  4.     {
  5.       "join_preparation": {
  6.         "select#": 1,
  7.         "steps": [
  8.           {
  9.             "IN_uses_bisection": true
  10.           },
  11.           {
  12.             "IN_uses_bisection": true
  13.           },
  14.           {
  15.             "expanded_query": "/* select#1 */ select `tm_account`.`account_id` AS `account_id`,`tm_account`.`name` AS `name`,`tm_account`.`address` AS `address`,`tm_account`.`org_first_id` AS `org_first_id`,`tm_account`.`org_second_id` AS `org_second_id`,`tm_account`.`biz_date` AS `biz_date`,`tm_account`.`last_modify_dt` AS `last_modify_dt` from `tm_account` where ((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  16.           }
  17.         ]
  18.       }
  19.     },
  20.     {
  21.       "join_optimization": {
  22.         "select#": 1,
  23.         "steps": [
  24.           {
  25.             "condition_processing": {
  26.               "condition": "WHERE",
  27.               "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
  28.               "steps": [
  29.                 {
  30.                   "transformation": "equality_propagation",
  31.                   "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  32.                 },
  33.                 {
  34.                   "transformation": "constant_propagation",
  35.                   "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  36.                 },
  37.                 {
  38.                   "transformation": "trivial_condition_removal",
  39.                   "resulting_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  40.                 }
  41.               ]
  42.             }
  43.           },
  44.           {
  45.             "substitute_generated_columns": {}
  46.           },
  47.           {
  48.             "table_dependencies": [
  49.               {
  50.                 "table": "`tm_account`",
  51.                 "row_may_be_null": false,
  52.                 "map_bit": 0,
  53.                 "depends_on_map_bits": []
  54.               }
  55.             ]
  56.           },
  57.           {
  58.             "ref_optimizer_key_uses": []
  59.           },
  60.           {
  61.             "rows_estimation": [
  62.               {
  63.                 "table": "`tm_account`",
  64.                 "range_analysis": {
  65.                   "table_scan": {
  66.                     "rows": 1408599,
  67.                     "cost": 288932
  68.                   },
  69.                   "potential_range_indexes": [
  70.                     {
  71.                       "index": "PRIMARY",
  72.                       "usable": true,
  73.                       "key_parts": [
  74.                         "account_id"
  75.                       ]
  76.                     },
  77.                     {
  78.                       "index": "IDX_org_id_combine",
  79.                       "usable": true,
  80.                       "key_parts": [
  81.                         "org_first_id",
  82.                         "org_second_id",
  83.                         "account_id"
  84.                       ]
  85.                     },
  86.                     {
  87.                       "index": "IDX_last_modify_dt_org_first_id_name",
  88.                       "usable": false,
  89.                       "cause": "not_applicable"
  90.                     }
  91.                   ],
  92.                   "setup_range_conditions": [],
  93.                   "group_index_range": {
  94.                     "chosen": false,
  95.                     "cause": "not_group_by_or_distinct"
  96.                   },
  97.                   "analyzing_range_alternatives": {
  98.                     "range_scan_alternatives": [
  99.                       {
  100.                         "index": "PRIMARY",
  101.                         "ranges": [
  102.                           "120306 < account_id"
  103.                         ],
  104.                         "index_dives_for_eq_ranges": true,
  105.                         "rowid_ordered": true,
  106.                         "using_mrr": false,
  107.                         "index_only": false,
  108.                         "rows": 704299,
  109.                         "cost": 141880,
  110.                         "chosen": true
  111.                       },
  112.                       {
  113.                         "index": "IDX_org_id_combine",
  114.                         "ranges": [
  115.                           "90 <= org_first_id"
  116.                         ],
  117.                         "index_dives_for_eq_ranges": true,
  118.                         "rowid_ordered": false,
  119.                         "using_mrr": false,
  120.                         "index_only": false,
  121.                         "rows": 295138,
  122.                         "cost": 354167,
  123.                         "chosen": false,
  124.                         "cause": "cost"
  125.                       }
  126.                     ],
  127.                     "analyzing_roworder_intersect": {
  128.                       "usable": false,
  129.                       "cause": "too_few_roworder_scans"
  130.                     }
  131.                   },
  132.                   "chosen_range_access_summary": {
  133.                     "range_access_plan": {
  134.                       "type": "range_scan",
  135.                       "index": "PRIMARY",
  136.                       "rows": 704299,
  137.                       "ranges": [
  138.                         "120306 < account_id"
  139.                       ]
  140.                     },
  141.                     "rows_for_plan": 704299,
  142.                     "cost_for_plan": 141880,
  143.                     "chosen": true
  144.                   }
  145.                 }
  146.               }
  147.             ]
  148.           },
  149.           {
  150.             "considered_execution_plans": [
  151.               {
  152.                 "plan_prefix": [],
  153.                 "table": "`tm_account`",
  154.                 "best_access_path": {
  155.                   "considered_access_paths": [
  156.                     {
  157.                       "rows_to_scan": 704299,
  158.                       "access_type": "range",
  159.                       "range_details": {
  160.                         "used_index": "PRIMARY"
  161.                       },
  162.                       "resulting_rows": 704299,
  163.                       "cost": 282740,
  164.                       "chosen": true
  165.                     }
  166.                   ]
  167.                 },
  168.                 "condition_filtering_pct": 100,
  169.                 "rows_for_plan": 704299,
  170.                 "cost_for_plan": 282740,
  171.                 "chosen": true
  172.               }
  173.             ]
  174.           },
  175.           {
  176.             "attaching_conditions_to_tables": {
  177.               "original_condition": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))",
  178.               "attached_conditions_computation": [],
  179.               "attached_conditions_summary": [
  180.                 {
  181.                   "table": "`tm_account`",
  182.                   "attached": "((`tm_account`.`org_first_id` >= 90) and (`tm_account`.`org_second_id` in (1,2,3,60)) and (`tm_account`.`biz_date` in ('2100-09-01','2100-09-02')) and (`tm_account`.`account_id` > 120306))"
  183.                 }
  184.               ]
  185.             }
  186.           },
  187.           {
  188.             "refine_plan": [
  189.               {
  190.                 "table": "`tm_account`"
  191.               }
  192.             ]
  193.           }
  194.         ]
  195.       }
  196.     },
  197.     {
  198.       "join_execution": {
  199.         "select#": 1,
  200.         "steps": []
  201.       }
  202.     }
  203.   ]
  204. }

附录3 java构造数据

  2. public final class JdbcUtils {
  4.     private static String url = "jdbc:mysql://localhost:3306/xxxx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8";
  5.     private static String user = "root";
  6.     private static String password = "123";
  8.     private JdbcUtils() {
  10.     }
  12.     static {
  13.         try {
  14.             Class.forName("com.mysql.jdbc.Driver");
  15.         } catch (ClassNotFoundException e) {
  16.             throw new ExceptionInInitializerError(e);
  17.         }
  18.     }
  21.     public static void main(String args[]) {
  22.         insertBatch();
  23.     }
  25.     public static void insertBatch() {
  27.         Connection conn = null;
  28.         PreparedStatement pst = null;
  29.         ResultSet rs = null;
  30.         try {
  31.             String sql = "INSERT into TM_ACCOUNT values(?,?,?,?,?,?,?);";
  33.             // 1. 获取链接，预处理语句
  34.             conn = getConnection();
  35.             conn.setAutoCommit(false);
  36.             pst = conn.prepareStatement(sql);
  38.             // 2. 开始插入，总插入150万
  39.             Random random = new Random();
  40.             int a_id_start = 1;
  41.             for (int i = 0; i < 5 * 150; i++) {
  42.                 // 每2000条执行一次批量插入
  43.                 for (int loop = 0; loop < 2000; loop++) {
  45.                     a_id_start++;
  46.                     pst.setInt(1, a_id_start);
  47.                     pst.setString(2, "name-" + a_id_start);
  48.                     pst.setString(3, RandomString.make(20));
  49.                     pst.setInt(4, random.nextInt(100));
  50.                     pst.setInt(5, random.nextInt(100));
  51.                     pst.setDate(6, new Date(200, 8, random.nextInt(25) + 1));
  52.                     pst.setDate(7, new Date(200, 8, random.nextInt(25) + 1));
  53.                     pst.addBatch();
  54.                 }
  55.                 pst.executeBatch();
  56.                 conn.commit();
  57.                 System.out.println(" done ！！！！！！" + i);
  58.             }
  59.             
  60.         } catch (Exception e) {
  61.             e.printStackTrace();
  62.         } finally {
  63.             free(rs, pst, conn);
  64.         }
  65.     }
  67.     public static Connection getConnection() throws SQLException {
  68.         return DriverManager.getConnection(url, user, password);
  69.     }
  71.     public static void free(ResultSet rs, Statement st, Connection conn) {
  72.         try {
  73.             if (rs != null)
  74.                 rs.close();
  75.         } catch (Exception e) {
  76.             e.printStackTrace();
  77.         } finally {
  78.             try {
  79.                 if (st != null)
  80.                     st.close();
  81.             } catch (Exception e2) {
  82.                 e2.printStackTrace();
  83.             } finally {
  84.                 try {
  85.                     conn.close();
  86.                 } catch (Exception e3) {
  87.                     e3.printStackTrace();
  88.                 }
  89.             }
  90.         }
  91.     }
  92. }

原文链接：https://www.cnblogs.com/starmoon1994/p/16942181.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MySQL热门文章

  • 大项目之网上书城（九）——订单Demo
  • CentOS7下MySQL服务启动失败原因及解决方法
  • MySQL从删库到跑路_高级（六）——索引
  • 55 道MySQL基础题
  • MySQL与PHP的基础与应用专题之自连接
  • 树形结构的菜单表设计与查询
  • MYSQL语句
  • 《高性能MySQL（第3版）》PDF免费下载
  • 《MySQL必知必会》知识汇总二
  • 【必知必会的MySQL知识】④DCL语言

    • MySQL推荐文章

  • 如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）
  • MySQL中为什么要使用索引合并(Index Merge)？
  • MySQL派生表合并优化的原理和实现
  • 使用EF 连接 数据库 SQLserver、MySql 实现 CodeFirst
  • 升级到 MySQL 8.4，MySQL 启动报错：io_setup() failed with EAGAIN
  • TiKV 源码分析之 PointGet
  • 异构数据源同步之数据同步 → DataX 使用细节
  • 一文搞懂 MySQL 日志
  • 浅析MySQL 8.0直方图原理
  • 分布式任务调度内的 MySQL 分页查询优化

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号