8.8 Understanding the Query Execution Plan

explain 与 desc、describe 同义词（大部分场景通用），EXPLAIN也可用于获取有关表中列的信息。

explain 语法

  1. {EXPLAIN | DESCRIBE | DESC}
  2.     tbl_name [col_name | wild]
  4. {EXPLAIN | DESCRIBE | DESC}
  5.     [explain_type]
  6.     {explainable_stmt | FOR CONNECTION connection_id}
  8. explain_type: {
  9.     EXTENDED
  10.   | PARTITIONS
  11.   | FORMAT = format_name
  12. }
  14. format_name: {
  15.     TRADITIONAL
  16.   | JSON
  17. }
  19. explainable_stmt: {
  20.     SELECT statement
  21.   | DELETE statement
  22.   | INSERT statement
  23.   | REPLACE statement
  24.   | UPDATE statement
  25. }

什么是查询计划

Google 翻译：
根据表、列、索引和WHERE子句中的条件的详细信息，MySQL 优化器会考虑许多技术来有效地执行 SQL 查询中涉及的查找。可以在不读取所有行的情况下执行对大表的查询；可以在不比较每个行组合的情况下执行涉及多个表的连接。优化器选择执行最高效查询的一组操作称为“查询执行计划”，也称为 EXPLAIN计划。你的目标是认识到 EXPLAIN 表明查询优化良好的计划，并学习 SQL 语法和索引技术以在您发现一些低效操作时改进计划。

explain 结果（输出格式）

Google 翻译：
EXPLAIN为SELECT语句中使用的每个表返回一行信息 。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。MySQL 使用嵌套循环连接方法解析所有连接。这意味着 MySQL 从第一个表中读取一行，然后在第二个表中找到匹配的行，第三个表，依此类推。处理完所有表后，MySQL 输出选定的列并通过表列表回溯，直到找到具有更多匹配行的表。从此表中读取下一行，然后处理下一个表。
EXPLAIN输出包括分区信息。此外，对于SELECT 语句，EXPLAIN产生可与被显示扩展信息 SHOW WARNINGS之后的 EXPLAIN（见 第8.8.3，“扩展EXPLAIN输出格式”）。

结果列（json key）含义

image.png
EXPLAIN 输出列

列名 JSON key 名称 意义
id select_id 该SELECT标识符
select_type 没有 该SELECT类型
table table_name 输出行的表
partitions partitions 匹配的分区
type access_type 联接类型
possible_keys possible_keys 可供选择的可能索引
key key 实际选择的索引
key_len key_length 所选密钥的长度
ref ref 与索引比较的列
rows rows 估计要检查的行数
filtered filtered 按表条件过滤的行百分比
Extra 没有 附加信息

说明：列名显示在表的第一列；第二列提供了FORMAT=JSON使用时输出中显示的等效属性名称；

id 列含义（JSON名： select_id）

select 的标识符，查询中的序号。序号可能为空（在使用 union 时）

  1. DESC SELECT
  2.     bc.NAME 
  3. FROM
  4.     bom_category bc UNION
  5. SELECT
  6.     bb.NAME 
  7. FROM
  8.     bom_brand bb;

结果：

  1. mysql> DESC SELECT bc.NAME FROM    bom_category bc UNION SELECT bb.NAME FROM bom_brand bb;
  2. +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  3. | id   | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
  4. +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  5. |    1 | PRIMARY      | bc         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1092 |   100.00 | NULL            |
  6. |    2 | UNION        | bb         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL            |
  7. | NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary |
  8. +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  9. 3 rows in set (0.03 sec)
  11. mysql>

select_type 列含义（JSON 名称：无）

值可以是列表中的任何一种：

select_type 的值 JSON 中的值 意义
SIMPLE 没有 简单SELECT（不使用 UNION或子查询）
PRIMARY 没有 最外面 SELECT
UNION 没有 中的第二个或以后的SELECT
语句 UNION
DEPENDENT UNION dependent( true) a 中的第二个或后面的SELECT
语句 UNION
，取决于外部查询
UNION RESULT union_result 的结果UNION
。
SUBQUERY 没有 首先SELECT
在子查询
DEPENDENT SUBQUERY dependent( true) 首先SELECT
在子查询中，依赖于外部查询
DERIVED 没有 派生表
MATERIALIZED materialized_from_subquery 物化子查询
UNCACHEABLE SUBQUERY cacheable( false) 无法缓存结果并且必须为外部查询的每一行重新评估的子查询
UNCACHEABLE UNION cacheable( false) UNION 属于不可缓存子查询的第二个或以后的选择（请参阅 UNCACHEABLE SUBQUERY）

使用 json 格式：

  1. mysql> explain format=JSON SELECT bc.NAME FROM    bom_category bc UNION SELECT bb.NAME FROM bom_brand bb;
  2. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
  4. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | {
  6.   "query_block": {
  7.     "union_result": {
  8.       "using_temporary_table": true,
  9.       "table_name": "<union1,2>",
  10.       "access_type": "ALL",
  11.       "query_specifications": [
  12.         {
  13.           "dependent": false,
  14.           "cacheable": true,
  15.           "query_block": {
  16.             "select_id": 1,
  17.             "cost_info": {
  18.               "query_cost": "229.40"
  19.             },
  20.             "table": {
  21.               "table_name": "bc",
  22.               "access_type": "ALL",
  23.               "rows_examined_per_scan": 1092,
  24.               "rows_produced_per_join": 1092,
  25.               "filtered": "100.00",
  26.               "cost_info": {
  27.                 "read_cost": "11.00",
  28.                 "eval_cost": "218.40",
  29.                 "prefix_cost": "229.40",
  30.                 "data_read_per_join": "14M"
  31.               },
  32.               "used_columns": [
  33.                 "name"
  34.               ]
  35.             }
  36.           }
  37.         },
  38.         {
  39.           "dependent": false,
  40.           "cacheable": true,
  41.           "query_block": {
  42.             "select_id": 2,
  43.             "cost_info": {
  44.               "query_cost": "1.40"
  45.             },
  46.             "table": {
  47.               "table_name": "bb",
  48.               "access_type": "ALL",
  49.               "rows_examined_per_scan": 2,
  50.               "rows_produced_per_join": 2,
  51.               "filtered": "100.00",
  52.               "cost_info": {
  53.                 "read_cost": "1.00",
  54.                 "eval_cost": "0.40",
  55.                 "prefix_cost": "1.40",
  56.                 "data_read_per_join": "19K"
  57.               },
  58.               "used_columns": [
  59.                 "name"
  60.               ]
  61.             }
  62.           }
  63.         }
  64.       ]
  65.     }
  66.   }
  67. } |
  68. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  69. 1 row in set (0.12 sec)
  71. mysql>

table 列含义（JSON名： table_name）

输出行所引用的表的名称。这也可以是以下值之一：

possible_keys（JSON名： possible_keys）

该possiblekeys列指示 MySQL 可以选择从中查找该表中行的索引。请注意，此列完全独立于从 的输出中显示的表的顺序 EXPLAIN。这意味着某些键possible_keys在实际中可能无法与生成的表顺序一起使用。
如果此列是NULL（或在 JSON 格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查WHERE 子句来检查它是否引用了适合编制索引的某些列或多列，从而提高查询的性能。如果是这样，请创建适当的索引并EXPLAIN再次检查查询 。见 第 13.1.8 节，“ALTER TABLE 语句”。
要查看表具有哪些索引，请使用. SHOW INDEX FROM tbl_name_

key（JSON名：key）

该key列表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用其中一个possible_keys 索引来查找行，则该索引将作为键值列出。
可以key命名值中不存在的索引 possible_keys。如果没有任何possible_keys索引适合查找行，但查询选择的所有列都是某个其他索引的列，就会发生这种情况。也就是说，命名索引覆盖了选定的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。
对于InnoDB，二级索引可能会覆盖选定的列，即使查询也选择了主键，因为InnoDB将主键值与每个二级索引一起存储。如果 key是NULL，则 MySQL 找不到可用于更有效地执行查询的索引。
要强制MySQL使用或忽略列出的索引 possible_keys列，使用 FORCE INDEX，USE INDEX或IGNORE INDEX在您的查询。见第 8.9.4 节，“索引提示”。
对于MyISAM表，运行 ANALYZE TABLE有助于优化器选择更好的索引。对于 MyISAM表，myisamchk —analyze执行相同的操作。请参阅 第 13.7.2.1 节，“ANALYZE TABLE 语句”和 第 7.6 节，“MyISAM 表维护和崩溃恢复”。

key_len（JSON名： key_length）

该key_len列表示 MySQL 决定使用的键的长度。的值 key_len使您能够确定 MySQL 实际使用的多部分键的多少部分。如果key列说 NULL，key_len 列也说NULL。
由于密钥存储格式的原因，列的密钥长度NULL 比列的长度NOT NULL大一。

单位是字节，varchar 类型是 3n+2，如果该字段允许为空，还需要加一，即 3n+3

ref（JSON名：ref）

该ref列显示哪些列或常量与列中指定的索引进行比较以 key从表中选择行。
如果值为func，则使用的值是某个函数的结果。要查看哪个功能，请使用 SHOW WARNINGS以下内容 EXPLAIN查看扩展 EXPLAIN输出。该函数实际上可能是一个运算符，例如算术运算符。

rows（JSON名： rows）

该rows列表示 MySQL 认为它必须检查以执行查询的行数。
对于InnoDB表格，这个数字是一个估计值，可能并不总是准确的。

filtered（JSON名： filtered）

该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有发生行过滤。从 100 开始减小的值表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示与下表连接的行数。例如，如果 rows是 1000 和 filtered50.00 (50%)，则与下表连接的行数为 1000 × 50% = 500。

Extra （JSON 名称：无）

此列包含有关 MySQL 如何解析查询的附加信息。有关不同值的说明，请参阅 EXPLAIN额外信息。
没有与Extra列对应的单个 JSON 属性 ；但是，此列中可能出现的值会作为 JSON 属性或作为属性的文本公开message。

解释联接类型

该type列 EXPLAIN输出介绍如何联接表。在 JSON 格式的输出中，这些作为access_type属性的值被找到。下面的列表描述了连接类型，从最好的类型到最差的类型：

  • system该表只有一行（= 系统表）。这是const连接类型的一个特例 。
  • const该表最多有一个匹配行，在查询开始时读取。因为只有一行，该行中该列的值可以被优化器的其余部分视为常量。 const表非常快，因为它们只被读取一次。const用于将 aPRIMARY KEY或 UNIQUE索引的所有部分与常量值进行比较。在以下查询中，tbl_name可以用作const 表：SELECT FROM tbl_name WHERE primary_key=1; SELECT FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
  • eq_ref对于前面表中的每个行组合，从该表中读取一行。除了 systemand const类型之外，这是最好的连接类型。当连接使用索引的所有部分并且索引是一个 PRIMARY KEY或UNIQUE NOT NULL索引时使用它。eq_ref可用于使用=运算符进行比较的索引列 。比较值可以是常量或表达式，该表达式使用在此表之前读取的表中的列。在以下示例中，MySQL 可以使用 eq_ref连接来处理 ref_table：SELECT FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
  • ref对于先前表中的每个行组合，从该表中读取具有匹配索引值的所有行。ref如果联接仅使用键的最左前缀或键不是 aPRIMARY KEY或 UNIQUE索引（换句话说，如果联接无法根据键值选择单行），则使用。如果使用的键只匹配几行，这是一个很好的连接类型。ref可用于使用=or<=> 运算符进行比较的索引列 。在以下示例中，MySQL 可以使用 ref连接来处理 ref_table：SELECT FROM ref_table WHERE key_column=expr; SELECT FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
  • fulltext连接是使用FULLTEXT 索引执行的。
  • ref_or_null这种连接类型类似于 ref，但另外，MySQL 会额外搜索包含NULL值的行。这种连接类型优化最常用于解析子查询。在以下示例中，MySQL 可以使用 ref_or_null连接来处理ref_table：SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;请参阅第 8.2.1.13 节，“IS NULL 优化”。
  • index_merge此连接类型表示使用了索引合并优化。在这种情况下，key输出行中的列包含所使用索引的列表，并key_len包含所使用索引 的最长关键部分的列表。有关更多信息，请参阅 第 8.2.1.3 节，“索引合并优化”。
  • unique_subquery这种类型替代 了以下形式的eq_ref一些 IN子查询：value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery 只是一个索引查找函数，完全替换子查询以提高效率。
  • index_subquery这种联接类型类似于 unique_subquery. 它取代了IN子查询，但它适用于以下形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range仅检索给定范围内的行，使用索引选择行。的key 输出行中的列指示使用哪个索引。将keylen包含已使用的时间最长的关键部分。该ref列适用 NULL于这种类型。range当使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, 或 IN()运算符中的任何一个将键列与常量进行比较时，可以使用 ：SELECT * FROM _tbl_name WHERE key_column = 10; SELECT FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
  • index该index联接类型是一样的 ALL，只是索引树被扫描。这有两种方式：当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。
    • 如果索引是查询的覆盖索引，可以满足表中所有需要的数据，则只扫描索引树。在这种情况下，该Extra列显示 Using index。仅索引扫描通常比ALL索引的大小通常小于表数据的大小要快 。
    • 使用从索引中读取来执行全表扫描以按索引顺序查找数据行。 Uses index不会出现在 Extra列中。
  • ALL对先前表中的每个行组合进行全表扫描。如果该表是第一个未标记的表 const，这通常不好，并且在所有其他情况下通常 非常糟糕。通常，您可以ALL通过添加索引来避免 基于常量值或早期表中的列值从表中检索行。

image.png
image.png
image.png
image.png

  1. org.apache.catalina.connector.ClientAbortException: java.io.IOException: Broken pipe
  2.     at org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:351)
  3.             4 lines skipped for [org.apache.catalina]
  4.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl.importData(DataImportServiceImpl.java:96)
  5.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl$$FastClassBySpringCGLIB$$a2b180bd.invoke(<generated>)
  6.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  7.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  8.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  9.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  10.     at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
  11.     at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
  12.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  13.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  14.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  15.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl$$EnhancerBySpringCGLIB$$c7386de.importData(<generated>)
  16.     at com.hbte.sharp.web.mat.MaterielDataImportController.materialDetailsRecord(MaterielDataImportController.java:79)
  17.     at com.hbte.sharp.web.mat.MaterielDataImportController$$FastClassBySpringCGLIB$$ea44ae70.invoke(<generated>)
  18.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  19.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  20.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  21.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  22.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  23.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  24.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  25.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  26.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  27.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  28.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  29.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  30.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  31.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  32.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  33.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  34.     at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
  35.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  36.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  37.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  38.     at com.hbte.sharp.web.mat.MaterielDataImportController$$EnhancerBySpringCGLIB$$7387d86b.materialDetailsRecord(<generated>)
  39.             3 lines skipped for [sun., java.lang.reflect.Method]
  40.     at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
  41.     at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
  42.     at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
  43.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
  44.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
  45.     at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
  46.     at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
  47.     at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
  48.     at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
  49.     at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
  50.             1 line skipped for [javax.servlet]
  51.     at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
  52.             6 lines skipped for [javax.servlet, org.apache.catalina, org.apache.tomcat]
  53.     at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
  54.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  55.             2 lines skipped for [org.apache.catalina]
  56.     at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
  57.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  58.             2 lines skipped for [org.apache.catalina]
  59.     at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
  60.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  61.             15 lines skipped for [org.apache.catalina, org.apache.tomcat, org.apache.coyote]
  62.     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
  63.     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
  64.             1 line skipped for [org.apache.tomcat]
  65.     at java.lang.Thread.run(Thread.java:748)
  66. Caused by: java.io.IOException: Broken pipe
  67.             17 lines skipped for [org.apache.catalina, sun., org.apache.tomcat, org.apache.coyote]
  68.     ... 89 more

唯一索引导致死锁

  1. com.alibaba.excel.exception.ExcelAnalysisException: org.springframework.dao.CannotAcquireLockException: 
  2. ### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  3. ### The error may exist in class path resource [mapping/MaterielBaseMapper.xml]
  4. ### The error may involve com.hbte.sharp.mapper.MaterielBaseMapper.insertSelective-Inline
  5. ### The error occurred while setting parameters
  6. ### SQL: insert into materiel_base      ( name,                              catalog_id,                              materiel_code,                       unit,                       package_flag,                       model,                       warehouse_id,                       flag,                       type,                              curr_stock,                       create_id,                       create_name,                       create_time,                       modify_id,                       modify_name,                       modify_time )       values ( ?,                              ?,                              ?,                       ?,                       ?,                       ?,                       ?,                       ?,                       ?,                              ?,                       ?,                       ?,                       ?,                       ?,                       ?,                       ? )
  7. ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  8. ; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  9.     at com.alibaba.excel.analysis.v07.XlsxSaxAnalyser.parseXmlSource(XlsxSaxAnalyser.java:183)
  10.     at com.alibaba.excel.analysis.v07.XlsxSaxAnalyser.execute(XlsxSaxAnalyser.java:201)
  11.     at com.alibaba.excel.analysis.ExcelAnalyserImpl.analysis(ExcelAnalyserImpl.java:115)
  12.     at com.alibaba.excel.ExcelReader.readAll(ExcelReader.java:162)
  13.     at com.alibaba.excel.read.builder.ExcelReaderBuilder.doReadAll(ExcelReaderBuilder.java:198)
  14.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl.importData(DataImportServiceImpl.java:112)
  15.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl.importData(DataImportServiceImpl.java:49)
  16.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl$$FastClassBySpringCGLIB$$a2b180bd.invoke(<generated>)
  17.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  18.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  19.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  20.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  21.     at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
  22.     at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
  23.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  24.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  25.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  26.     at com.hbte.sharp.service.impl.data.DataImportServiceImpl$$EnhancerBySpringCGLIB$$c7386de.importData(<generated>)
  27.     at com.hbte.sharp.web.mat.MaterielDataImportController.materialDetailsRecord(MaterielDataImportController.java:79)
  28.     at com.hbte.sharp.web.mat.MaterielDataImportController$$FastClassBySpringCGLIB$$ea44ae70.invoke(<generated>)
  29.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  30.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  31.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  32.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  33.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  34.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  35.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  36.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  37.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  38.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  39.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  40.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  41.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  42.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
  43.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  44.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  45.     at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
  46.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  47.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  48.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  49.     at com.hbte.sharp.web.mat.MaterielDataImportController$$EnhancerBySpringCGLIB$$7387d86b.materialDetailsRecord(<generated>)
  50.             3 lines skipped for [sun., java.lang.reflect.Method]
  51.     at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
  52.     at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
  53.     at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
  54.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
  55.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
  56.     at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
  57.     at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
  58.     at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
  59.     at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
  60.     at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
  61.             1 line skipped for [javax.servlet]
  62.     at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
  63.             6 lines skipped for [javax.servlet, org.apache.catalina, org.apache.tomcat]
  64.     at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
  65.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  66.             2 lines skipped for [org.apache.catalina]
  67.     at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
  68.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  69.             2 lines skipped for [org.apache.catalina]
  70.     at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
  71.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  72.             15 lines skipped for [org.apache.catalina, org.apache.tomcat, org.apache.coyote]
  73.     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
  74.     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
  75.             1 line skipped for [org.apache.tomcat]
  76.     at java.lang.Thread.run(Thread.java:748)
  77. Caused by: org.springframework.dao.CannotAcquireLockException: 
  78. ### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  79. ### The error may exist in class path resource [mapping/MaterielBaseMapper.xml]
  80. ### The error may involve com.hbte.sharp.mapper.MaterielBaseMapper.insertSelective-Inline
  81. ### The error occurred while setting parameters
  82. ### SQL: insert into materiel_base      ( name,                              catalog_id,                              materiel_code,                       unit,                       package_flag,                       model,                       warehouse_id,                       flag,                       type,                              curr_stock,                       create_id,                       create_name,                       create_time,                       modify_id,                       modify_name,                       modify_time )       values ( ?,                              ?,                              ?,                       ?,                       ?,                       ?,                       ?,                       ?,                       ?,                              ?,                       ?,                       ?,                       ?,                       ?,                       ?,                       ? )
  83. ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  84. ; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  85.     at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
  86.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  87.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
  88.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
  89.             1 line skipped for [com.sun]
  90.     at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
  91.     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
  92.     at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
  93.     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
  94.             1 line skipped for [com.sun]
  95.     at com.hbte.sharp.po.data.listener.MaterialDetailsDataImportListener.invoke(MaterialDetailsDataImportListener.java:234)
  96.     at com.hbte.sharp.po.data.listener.MaterialDetailsDataImportListener.invoke(MaterialDetailsDataImportListener.java:33)
  97.     at com.alibaba.excel.read.processor.DefaultAnalysisEventProcessor.dealData(DefaultAnalysisEventProcessor.java:100)
  98.     at com.alibaba.excel.read.processor.DefaultAnalysisEventProcessor.endRow(DefaultAnalysisEventProcessor.java:47)
  99.     at com.alibaba.excel.analysis.v07.handlers.RowTagHandler.endElement(RowTagHandler.java:47)
  100.     at com.alibaba.excel.analysis.v07.handlers.sax.XlsxRowHandler.endElement(XlsxRowHandler.java:89)
  101.             10 lines skipped for [com.sun]
  102.     at com.alibaba.excel.analysis.v07.XlsxSaxAnalyser.parseXmlSource(XlsxSaxAnalyser.java:178)
  103.     ... 90 more
  104. Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  105.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
  106.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  107.     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  108.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  109.     at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
  110.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
  111.     at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:627)
  112.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
  113.     at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
  114.     at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
  115.             3 lines skipped for [sun., java.lang.reflect.Method]
  116.     at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
  117.             1 line skipped for [com.sun]
  118.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
  119.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  120.     at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
  121.     at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  122.     at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
  123.             3 lines skipped for [sun., java.lang.reflect.Method]
  124.     at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
  125.             1 line skipped for [com.sun]
  126.     at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
  127.     at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
  128.             3 lines skipped for [sun., java.lang.reflect.Method]
  129.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
  130.     ... 113 more

mysql 死锁案例——唯一索引

特别长的字段，可以截取前面几位创建索引（可以通过 select count(distinct left(name, 10))/count(*) 来看离散度，决定到底提取前几位）

A/B 测试？

sql 案例

  1. -- 1 采用关联方式
  2. SELECT
  3.   `bs`.`out_code` AS `code`,
  4.   `bs`.`sku_name` AS `name`,
  5.   `bs`.`sku_supplier` AS `supplier`,
  6.   `bs`.`product_model` AS `model`,
  7.   `bs`.`sku_packag` AS `packag`,
  8.   `bs`.`sku_unit` AS `unit`,
  9.   `bb`.`name` AS `brand_name`,
  10.   GROUP_CONCAT( bc2.`name` ORDER BY length( bc2.path ) SEPARATOR ',' ) AS category 
  11. FROM
  12.   `public_data`.`bom_sku` `bs`
  13.   LEFT JOIN `public_data`.`bom_category` bc ON bs.catalog_id = bc.id
  14.   LEFT JOIN `public_data`.`bom_category` bc2 ON FIND_IN_SET(
  15.     bc2.id,
  16.   CONCAT( bc.path, ',', bc.id ))
  17.   LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id` 
  18. GROUP BY
  19.   bs.out_code;
  21. -- 2 采用子查询方式
  22. SELECT
  23.     `bs`.`out_code` AS `code`,
  24.     `bs`.`sku_name` AS `name`,
  25.     `bs`.`sku_supplier` AS `supplier`,
  26.     `bs`.`product_model` AS `model`,
  27.     `bs`.`sku_packag` AS `packag`,
  28.     `bs`.`sku_unit` AS `unit`,
  29.     `bb`.`name` AS `brand_name`,
  30.     bc1.category 
  31. FROM
  32.     `public_data`.`bom_sku` `bs`
  33.     LEFT JOIN (
  34.     SELECT
  35.         bc.id,
  36.         GROUP_CONCAT( bc2.`name` ORDER BY length( bc2.path ) SEPARATOR ',' ) AS category 
  37.     FROM
  38.         `public_data`.`bom_category` bc
  39.         inner JOIN `public_data`.`bom_category` bc2 ON FIND_IN_SET(
  40.             bc2.id,
  41.         CONCAT( bc.path, ',', bc.id )) 
  42.     GROUP BY
  43.         bc.id 
  44.     ) bc1 ON bs.catalog_id = bc1.id
  45.     LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id`;
  47. -- 3 优化2 采用子查询方式
  48. SELECT
  49.     `bs`.`out_code` AS `code`,
  50.     `bs`.`sku_name` AS `name`,
  51.     `bs`.`sku_supplier` AS `supplier`,
  52.     `bs`.`product_model` AS `model`,
  53.     `bs`.`sku_packag` AS `packag`,
  54.     `bs`.`sku_unit` AS `unit`,
  55.     `bb`.`name` AS `brand_name`,
  56.     CONCAT( bc1.category, ',', bc1.NAME ) AS a 
  57. FROM
  58.     `public_data`.`bom_sku` `bs`
  59.     LEFT JOIN (
  60.     SELECT
  61.         bc.id,
  62.         bc.NAME,
  63.         GROUP_CONCAT( bc2.`name` ORDER BY length( bc2.path ) SEPARATOR ',' ) AS category 
  64.     FROM
  65.         `public_data`.`bom_category` bc
  66.         INNER JOIN `public_data`.`bom_category` bc2 ON FIND_IN_SET( bc2.id, bc.path ) 
  67.     GROUP BY
  68.         bc.id 
  69.     ) bc1 ON bs.catalog_id = bc1.id
  70.     LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id`;
  72. -- 4
  73. SELECT
  74.     `bs`.`out_code` AS `code`,
  75.     `bs`.`sku_name` AS `name`,
  76.     `bs`.`sku_supplier` AS `supplier`,
  77.     `bs`.`product_model` AS `model`,
  78.     `bs`.`sku_packag` AS `packag`,
  79.     `bs`.`sku_unit` AS `unit`,
  80.     `bb`.`name` AS `brand_name`,
  81.     `bcs`.`name` AS `type`,
  82.     `bpath`.`typeName` AS `typeName` 
  83. FROM
  84.     `public_data`.`bom_sku` `bs`
  85.     LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id`
  86.     LEFT JOIN `public_data`.`bom_category` `bc` ON `bc`.`id` = `bs`.`catalog_id`
  87.     LEFT JOIN `public_data`.`bom_category` `bcs` ON `bcs`.`id` = substring_index(
  88.         substring_index( `bc`.`path`, ',', 2 ),
  89.         ',',-(
  90.             1 
  91.         ))
  92.     LEFT JOIN (
  93.     SELECT
  94.         `bcpath`.`id` AS `id`,
  95.         group_concat( `bc`.`name` SEPARATOR '-' ) AS `typeName` 
  96.     FROM
  97.         (
  98.         SELECT
  99.             `public_data`.`bom_category`.`id` AS `id`,
  100.             substr(
  101.                 REPLACE (
  102.                     concat( `public_data`.`bom_category`.`path`, ',', `public_data`.`bom_category`.`id` ),
  103.                     substring_index( concat( `public_data`.`bom_category`.`path`, ',', `public_data`.`bom_category`.`id` ), ',', 2 ),
  104.                     '' 
  105.                 ),
  106.                 2 
  107.             ) AS `path` 
  108.         FROM
  109.             `public_data`.`bom_category` 
  110.         ) `bcpath`
  111.         JOIN `public_data`.`bom_category` `bc` ON find_in_set( `bc`.`id`, `bcpath`.`path` ) 
  112.     GROUP BY
  113.         `bcpath`.`id`,
  114.     `bcpath`.`path` 
  115.     ) `bpath` ON `bpath`.`id` = `bs`.`catalog_id`;
  117. -- 5 优化4
  119. SELECT
  120.     `bs`.`out_code` AS `code`,
  121.     `bs`.`sku_name` AS `name`,
  122.     `bs`.`sku_supplier` AS `supplier`,
  123.     `bs`.`product_model` AS `model`,
  124.     `bs`.`sku_packag` AS `packag`,
  125.     `bs`.`sku_unit` AS `unit`,
  126.     `bb`.`name` AS `brand_name`,
  127.     SUBSTRING_INDEX(bc1.category , ',', 1) as type,
  128.     REPLACE(bc1.category,concat(SUBSTRING_INDEX(bc1.category , ',', 1), ','),'') as typeName
  129. FROM
  130.     `public_data`.`bom_sku` `bs`
  131.     LEFT JOIN (
  132.     SELECT
  133.         bc.id,
  134.         GROUP_CONCAT( bc2.`name` ORDER BY length( bc2.path ) SEPARATOR ',' ) AS category 
  135.     FROM
  136.         `public_data`.`bom_category` bc
  137.         INNER JOIN `public_data`.`bom_category` bc2 ON FIND_IN_SET(
  138.             bc2.id,
  139.         CONCAT( bc.path, ',', bc.id )) 
  140.     GROUP BY
  141.         bc.id 
  142.     ) bc1 ON bs.catalog_id = bc1.id
  143.     LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id`;
  145. -- 6 优化4 
  146. SELECT
  147.     `bs`.`out_code` AS `code`,
  148.     `bs`.`sku_name` AS `name`,
  149.     `bs`.`sku_supplier` AS `supplier`,
  150.     `bs`.`product_model` AS `model`,
  151.     `bs`.`sku_packag` AS `packag`,
  152.     `bs`.`sku_unit` AS `unit`,
  153.     `bb`.`name` AS `brand_name`,
  154.     SUBSTRING_INDEX( bc1.category, ',', 1 ) AS type,
  155.     REPLACE (
  156.         CONCAT( bc1.category, ',', bc1.NAME ),
  157.         concat( SUBSTRING_INDEX( bc1.category, ',', 1 ), ',' ),
  158.         '' 
  159.     ) AS typeName 
  160. FROM
  161.     `public_data`.`bom_sku` `bs`
  162.     LEFT JOIN (
  163.     SELECT
  164.         bc.id,
  165.         bc.NAME,
  166.         GROUP_CONCAT( bc2.`name` ORDER BY length( bc2.path ) SEPARATOR ',' ) AS category 
  167.     FROM
  168.         `public_data`.`bom_category` bc
  169.         INNER JOIN `public_data`.`bom_category` bc2 ON 
  170.     --          FIND_IN_SET( bc2.id, bc.path )  与 instr 效率参不多
  171.         INSTR(bc.path,bc2.id)
  172.     GROUP BY
  173.         bc.id 
  174.     ) bc1 ON bs.catalog_id = bc1.id
  175.     LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id`;

把相关的数据返回，方便查询

  1. SELECT
  2.     `bs`.`out_code` AS `code`,
  3.     `bs`.`sku_name` AS `name`,
  4.     `bs`.`sku_supplier` AS `supplier`,
  5.     `bs`.`product_model` AS `model`,
  6.     `bs`.`sku_packag` AS `packag`,
  7.     `bs`.`sku_unit` AS `unit`,
  8.     `bb`.`name` AS `brand_name`,
  9.     SUBSTRING_INDEX( bc1.category, ',', 1 ) AS type,
  10.     bc1.path,
  11.     REPLACE (
  12.         CONCAT( bc1.category, ',', bc1.NAME ),
  13.         concat( SUBSTRING_INDEX( bc1.category, ',', 1 ), ',' ),
  14.         '' 
  15.     ) AS typeName 
  16. FROM
  17.     `public_data`.`bom_sku` `bs`
  18.     LEFT JOIN (
  19.     SELECT
  20.         bc.id,
  21.         bc.NAME,
  22.         bc.path,
  23.         GROUP_CONCAT( bc2.`name` ORDER BY length( bc2.path ) SEPARATOR ',' ) AS category 
  24.     FROM
  25.         `public_data`.`bom_category` bc
  26.         INNER JOIN `public_data`.`bom_category` bc2 ON 
  27.         -- FIND_IN_SET( bc2.id, bc.path )
  28.         INSTR( bc.path, bc2.id ) 
  29.     GROUP BY
  30.         bc.id 
  31.     ) bc1 ON bs.catalog_id = bc1.id
  32.     LEFT JOIN `public_data`.`bom_brand` `bb` ON `bs`.`brand_id` = `bb`.`id`;