day4 多连联表慢查询sql查询优化

1.Explain分析sql语句出现的字段是什么意思

id:

查询的序列号，表示查询中 select 子句或操作表的顺序。
如果 id 相同，则执行顺序从上到下。
如果 id 不同，如果是子查询，id 的值会递增，id 值越大优先级越高，越先被执行。

select_type:

查询的类型，主要用于区分普通查询、联合查询、子查询等。
常见的值有：SIMPLE（简单查询）、PRIMARY（最外层查询）、UNION（联合查询中的第二个或后面的查询）、SUBQUERY（子查询中的第一个 SELECT）等。

table:

显示这一行数据是关于哪张表的。

type:

联接类型，表示 MySQL 决定如何查找表中的行。
常见的类型有：ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（使用非唯一索引或唯一索引的前缀进行查找）、eq_ref（使用唯一索引进行查找）、const/system（单表中最多只有一行匹配，查询起来非常快）等。

possible_keys:

指出 MySQL 能在该表中使用哪些索引来优化查询。

key:

实际使用的索引。如果为 NULL，则没有使用索引。

key_len:

使用的索引的长度。在不损失精确性的情况下，长度越短越好。

ref:

显示索引的哪一列被使用了，如果可能的话，是一个常数。

rows:

MySQL 估计为了找到所需的行而要读取的行数。

Extra:
包含 MySQL 在解析查询时的详细信息，例如是否使用了索引、是否排序、是否使用了临时表等。
常见的值有：Using index（使用覆盖索引）、Using where（使用 WHERE 子句）、Using temporary（使用临时表）、Using filesort（使用文件排序）等。
理解这些字段可以帮助你分析和优化 SQL 查询的性能。例如，如果你看到一个查询的 type 是 ALL，那么可能意味着查询没有有效地使用索引，可能需要添加索引来优化。

今日任务 ：优化sql多连表查询  查询入库表

    <select id="queryEnterMaterialsWarehousing" resultType="com.shzj.managesystem.pcToolManagement.vo.ToolMaterialsStock">

        select

        tmew.id,

        tmew.name,

        tmew.status,

        tmew.code,

        GROUP_CONCAT(tmsr.rfid) as rfids,

        tmew.classification_id,

        tmew.classification_code,

        (select tmc.classification_name from tool_materials_classification as tmc

        where tmc.id=tmew.classification_id) as classificationName,

        tmew.warehouse_id,

        tmew.warehouse_code,

        (select tmc.wms_warehouse_name from wms_warehouse as tmc

        where tmc.wms_warehouse_id=tmew.warehouse_id) as warehouseName,

        tmew.create_time,

        tmew.create_user_id,

        tmew.attribute1,

        tmew.attribute2,

        tmew.attribute3,

        tmew.attribute4,

        tmew.attribute5,

        tmew.attribute6,

        tmew.attribute7,

        tmew.attribute8,

        tmew.attribute9,

        tmew.attribute10,

        tmew.belong_group,

        DATE_FORMAT(tmew.new_inspection_time, '%Y-%m-%d') as newInspectionTime,

        DATE_FORMAT(tmew.next_inspection_time, '%Y-%m-%d') as nextInspectionTime

        from tool_materials_enter_warehousing as tmew

        left join tool_materials_stock as tms on tmew.code = tms.code

        left join tool_materials_stock_rfid as tmsr on tms.id = tmsr.tool_id

        where tmew.belong_group=#{vo.belongGroup}

        <if test="vo.condition!=null and vo.condition !=''">

            and (tmew.name like concat('%',#{vo.condition},'%')

            or tmew.code like concat('%',#{vo.condition},'%')

            or tmsr.rfid like concat('%',#{vo.condition},'%')

            or tmew.classification_code like concat('%',#{vo.condition},'%')

            or tmew.classification_name like concat('%',#{vo.condition},'%')

            or (select user_name from sys_user where user_id=tmew.create_user_id and del='N') like concat('%',#{vo.condition},'%')

            or tmew.warehouse_code like concat('%',#{vo.condition},'%')

            or tmew.warehouse_name like concat('%',#{vo.condition},'%')

            or tmew.attribute1 like concat('%',#{vo.condition},'%')

            or tmew.attribute2 like concat('%',#{vo.condition},'%')

            or tmew.attribute3 like concat('%',#{vo.condition},'%')

            or tmew.attribute4 like concat('%',#{vo.condition},'%')

            or tmew.attribute5 like concat('%',#{vo.condition},'%')

            or tmew.attribute6 like concat('%',#{vo.condition},'%')

            or tmew.attribute7 like concat('%',#{vo.condition},'%')

            or tmew.attribute8 like concat('%',#{vo.condition},'%')

            or tmew.attribute9 like concat('%',#{vo.condition},'%')

            or tmew.attribute10 like concat('%',#{vo.condition},'%')

            )

        </if>

        <if test="vo.status!=null and vo.status !=''">

            and tmew.status like concat('%',#{vo.status},'%')

        </if>

        <if test="vo.classification!=null and vo.classification !=''">

            and tmew.classification_id =#{vo.classification}

        </if>

        <if test="vo.warehouse!=null and vo.warehouse !=''">

            and tmew.warehouse_id =#{vo.warehouse}

        </if>

        <if test="vo.codeCondition !=null and vo.codeCondition !='' and vo.codeList.size>0">

            and tmew.code in

            <foreach collection="vo.codeList" item="item" open="(" close=")" separator=",">

                #{item}

            </foreach>

        </if>

        GROUP BY

        tmew.id, tmew.name, tmew.status, tmew.code,

        tmew.classification_id, tmew.classification_code,

        tmew.warehouse_id, tmew.warehouse_code,

        tmew.create_time, tmew.create_user_id,

        tmew.attribute1, tmew.attribute2, tmew.attribute3,

        tmew.attribute4, tmew.attribute5, tmew.attribute6,

        tmew.attribute7, tmew.attribute8, tmew.attribute9,

        tmew.attribute10, tmew.belong_group,

        tmew.new_inspection_time, tmew.next_inspection_time

        ORDER BY tmew.create_time desc

    </select>

    入库表查询需要 库存表的库存 rfid表的rfid 物料表的物料 仓库表的仓库信息 用户表的创建人信息 涉及了六张表

    两个联表查询 三个子查询

    首先explain分析 通过type字段判断出入库表和库存表为All 证明这两个表做了全表扫描  possible_keys，keys

    ,key_len显示为null 所以这两张表没有用到索引 row是看扫描过的行数

    这个SQL查询主要涉及到以下几张表，以及它们之间的关系：

1.tool_materials_enter_warehousing (别名 tmew)

这是主表，包含了入库物料的主要信息。

2.tool_materials_stock (别名 tms)

与 tool_materials_enter_warehousing 表通过物料编码 code 进行左连接。这个表可能包含了物料的库存信息。

3.tool_materials_stock_rfid (别名 tmsr)

与 tool_materials_stock 表通过物料ID tool_id 进行左连接。这个表可能包含了物料的RFID标签信息。

4.tool_materials_classification (别名 tmc)

在一个子查询中，通过物料分类ID classification_id 与 tool_materials_enter_warehousing 表连接，用于获取物料分类的名称。

5.wms_warehouse (别名 tmc)

在另一个子查询中，通过仓库ID wms_warehouse_id 与 tool_materials_enter_warehousing 表连接，用于获取仓库的名称。

6.sys_user

在一个子查询中，通过创建人ID create_user_id 与 tool_materials_enter_warehousing 表连接，用于获取创建人的用户名。

关系分析：

tool_materials_enter_warehousing (tmew) 是查询的主表，包含了物料入库的基本信息。

tool_materials_stock (tms) 通过 code 列与 tmew 表连接，表示库存信息与入库信息通过物料编码关联。

tool_materials_stock_rfid (tmsr) 通过 tool_id 列与 tms 表连接，表示RFID标签信息与库存信息通过物料ID关联。

子查询关联 tool_materials_classification (tmc) 用于获取物料的分类名称。

子查询关联 wms_warehouse (tmc) 用于获取物料入库的仓库名称。

子查询关联 sys_user 用于获取创建物料入库记录的用户名。

查询中的 GROUP_CONCAT(tmsr.rfid) 用于将同一个物料ID的所有RFID标签合并为一个字符串。查询还使用了 GROUP BY 子句来对结果进行分组，确保每个 tmew.id 只出现一次，即使它可能关联了多个RFID标签。

ORDER BY tmew.create_time desc 表示结果将按照创建时间降序排列。

这个查询是一个复杂的SQL语句，它结合了多表连接、子查询、条件过滤、分组和排序等操作。在实际应用中，这样的查询可能需要优化以提高性能，尤其是在处理大量数据时。

优化点： 1.将两个子查询全部转为left join查询 以为子查询在查询每行数据都会执行一次而JOIN只执行一次

        2.将belong_group过滤条件 create_time排序字段添加上联合索引 只需要一次扫描就可以同时满足两个条件

        避免了额外排序操作 减少了io操作

        3.遵循了最左匹配原则 将where条件放在了order之前  

        4.将连表查询的链接条件code加上索引 入库表和库存表通过code进行连表查询 提升链接性能

最左匹配原则：最左前缀原则的定义：

对于复合索引 (belong_group, create_time)，索引会按照从左到右的顺序进行匹配。        

单独索引和复合索引的区别

使用单独索引：  ----需要额外的排序操作

1. 使用belong_group索引找到所有belong_group=1的记录（假设20万条）

2. 对这20万条记录进行内存排序（filesort）

3. 返回结果

使用复合索引：

1. 使用复合索引直接找到belong_group=1的记录

2. 数据已经按create_time排序，直接返回结果

联合查询查询过程：

1. 先定位belong_group = 1的记录（使用索引第一列）

2. 这些记录已经按create_time排好序（索引第二列）

3. 直接获得排序后的结果        

1.优化线上慢sql 对同一个业务场景上的慢查询语句 在不同量级的数据量情况下 通过设计联合索引优化sql结构实现了查询效率的

显著提升 有效缩短了接口的响应时长

业务背景：入库数据查询时接口反应速度过慢

问题原因：六张表连表查询 嵌套多个子查询 导致接口反应过慢 典型慢sql

优化点： 1.将两个子查询全部转为left join查询 以为子查询在查询每行数据都会执行一次而JOIN只执行一次

        2.将belong_group过滤条件 create_time排序字段添加上联合索引 只需要一次扫描就可以同时满足两个条件

        避免了额外排序操作 减少了io操作

        3.遵循了最左匹配原则 将where条件放在了order之前  

        4.将连表查询的链接条件code加上索引 入库表和库存表通过code进行连表查询 提升链接性能