MySQL数据库常见问题

大量数据导入数据的方式

1. 批量新增修改案例

   <insert id="insertOrUpdateBatch">  
       insert into user(uid,name,age)  
       values  
       <foreach collection="list" item="item" separator=",">  
           (#{item.uid},#{item.name},#{item,age})  
       </foreach>  
       on duplicate key update  
       uid= values(uid)  
       name=values(name),  
       age=values(age)  
   </insert>

   注意问题:
   XML拼接SQL语句的方法，会不会导致语句超长报错

优化方式
用preparestatement多线程批量处理，8线程1000w只需要1分钟
库一般每秒写入三百兆数据没问题，实际情况
mybatisplus的savebatch其实加上rewriteBatchedStatements的配置就不是一条一条执行的了，底层会走到jdbc的批处理，而jdbc的批处理毋庸置疑已经做过优化了，其实和原生sql语句性能没差多少，而原生sql还有语句太长报错的可能。

1 常见SQL 问题

1.1 SQL索引问题

疑问:
1. 为什么SQL会走错索引?
2. 为什么SQL走这个索引, 不走那个索引?
3. 为什么有索引不走, 走全表扫描?
4. SQL是依据什么判断走哪个索引的?

1.2 2.存储过程

https://blog.csdn.net/whf_a/article/details/114871866

-- 创建存储过程
create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num<10 do -- 循环开始
set num = num+1;
set sum = sum+num;
INSERT INTO `sys`.`t_user` (`fullName`, `userType`, `addedTime`) VALUES (CONCAT("燕燕",sum), 'vip', '2022-09-17 10:21:28');

end while; -- 循环结束
end;
-- 调用过程
call mypro5(@sum);
-- 查询变量值
select @sum;

1.3 清理binlog日志的方法

1. 查看binlog过期时间
   show variables like 'expire_logs_days';
   expire_logs_days=0：
   这里的值如果为0，表示所有binlog日志永久都不会失效，不会自动删除；
   这里的值如果为30，表示只保留最近30天。

2. 修改binlog过期时间
   永久生效(重启后即生效)
   修改配置文件my.cnf文件： vim /etc/my.cnf

3. 清除binlog日志:

   -- 比如将mysql-bin.009560之前的binlog清掉:
   purge binary logs to 'mysql-bin.009560';
   
   -- 指定时间之前的binlog清掉:
   purge binary logs before '2022-01-01 23:59:59';

2 数据库表设计

2.1 全文索引

2.1.1 表结构

CREATE TABLE `work_order_query` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` varchar(255) NOT NULL COMMENT '工单ID',
  `order_source` tinyint NOT NULL DEFAULT '0' COMMENT '工单类型',
  `belong_team` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '归属团队',
  `belong_group` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '归属处室',
  `system_names` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '涉及系统列表',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_order_source` (`order_source`),
  KEY `idx_order_status` (`order_status`),
  KEY `idx_system_names` (`system_names`),
  FULLTEXT KEY `idx_system_names_fulltext` (`system_names`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='工单查询表';

2.1.2 添加全文索引

ALTER TABLE `work_order_query` 
ADD FULLTEXT INDEX `idx_system_names_fulltext` (`system_names`);

2.1.3 生成数据

DELIMITER $$

CREATE PROCEDURE Generate_Work_Order_Query_Data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE order_sources INT;
    DECLARE belong_teams VARCHAR(50);
    DECLARE belong_groups VARCHAR(50);
    DECLARE system_names VARCHAR(255);

    -- 循环生成3万条数据
    WHILE i <= 30000 DO
        -- 随机工单类型 (order_source)
        SET order_sources = FLOOR(1 + (RAND() * 3)); -- 生成1到3之间的随机数

        -- 随机归属团队 (belong_team)
        IF order_sources = 1 THEN
            SET belong_teams = 'TeamA';
        ELSEIF order_sources = 2 THEN
            SET belong_teams = 'TeamB';
        ELSE
            SET belong_teams = 'TeamC';
        END IF;

        -- 随机归属处室 (belong_group)
        IF order_sources = 1 THEN
            SET belong_groups = 'GroupA';
        ELSEIF order_sources = 2 THEN
            SET belong_groups = 'GroupB';
        ELSE
            SET belong_groups = 'GroupC';
        END IF;

        -- 随机涉及系统 (system_names)
        SET system_names = CONCAT('System', FLOOR(1 + (RAND() * 5)), ', System', FLOOR(1 + (RAND() * 5)));

        -- 插入数据
        INSERT INTO `work_order_query` (`order_id`, `order_source`, `belong_team`, `belong_group`, `system_names`)
        VALUES 
            (CONCAT('WO', LPAD(i, 4, '0')), order_sources, belong_teams, belong_groups, system_names);

        -- 增加计数器
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

2.1.4 查询方式

如果 system_names 列存储了多个系统名称（如逗号分隔的系统名称列表），并且你希望查询多个系统名称，你可以使用 MATCH ... AGAINST 查询来查找包含多个系统名称的记录。我们可以将多个系统名称通过布尔查询的方式进行组合。

2.1.4.1 查询多个系统名称

假设你要查询涉及 System1 和 System2 的工单，可以通过以下方式进行查询：

SELECT * FROM `work_order_query` WHERE MATCH (`system_names`) AGAINST ('+System1 +System2' IN BOOLEAN MODE);

解释：

• +System1 +System2：在布尔模式下，+ 符号表示必须包含 System1 和 System2。

• IN BOOLEAN MODE：布尔模式搜索，允许你使用布尔运算符（如 + 表示必须包含，- 表示排除某些词）。
  xml形式

  <mapper namespace="com.example.mapper.WorkOrderQueryMapper">
  
      <!-- 查询多个系统名称都包含 -->
      <select id="selectOrdersWithAllSystems" resultType="com.example.model.WorkOrderQuery">
          SELECT * FROM work_order_query
          WHERE MATCH (system_names) AGAINST
          <foreach collection="systemNames" item="systemName" separator=" " open="'(" close=")'">
              +#{systemName}
          </foreach>
          IN BOOLEAN MODE
          ORDER BY created_at DESC
      </select>
  
  </mapper>

2.1.4.2 查询任意一个系统名称

如果你希望查询包含任意一个系统名称的工单（比如包含 System1 或 System3），可以使用以下查询：

SELECT * FROM `work_order_query` WHERE MATCH (`system_names`) AGAINST ('System1 System3' IN BOOLEAN MODE);

这个查询将返回包含 System1 或 System3 的工单。

<mapper namespace="com.example.mapper.WorkOrderQueryMapper">

    <!-- 查询包含任意一个系统名称 -->
    <select id="selectOrdersWithAnySystem" resultType="com.example.model.WorkOrderQuery">
        SELECT * FROM work_order_query
        WHERE MATCH (system_names) AGAINST
        <foreach collection="systemNames" item="systemName" separator=" " open="'(" close=")'">
            #{systemName}
        </foreach>
        IN BOOLEAN MODE
        ORDER BY created_at DESC
    </select>

</mapper>

2.1.4.3 查询多个系统名称，且指定一个或多个系统名称必须包含

假设你要查询包含 System1 或 System2，并且同时包含 System3 的工单，可以使用以下查询：

SELECT * FROM `work_order_query` WHERE MATCH (`system_names`) AGAINST ('+System3 +System1 +System2' IN BOOLEAN MODE);

这样查询的结果将会包含所有同时涉及 System1、System2 和 System3 的工单。

<mapper namespace="com.example.mapper.WorkOrderQueryMapper">

    <!-- 查询多个系统名称，且指定一个或多个系统名称必须包含 -->
    <select id="selectOrdersWithMustHaveSystem" resultType="com.example.model.WorkOrderQuery">
        SELECT * FROM work_order_query
        WHERE MATCH (system_names) AGAINST
        <foreach collection="mustHaveSystems" item="systemName" separator=" " open="'(" close=")'">
            +#{systemName}
        </foreach>
        IN BOOLEAN MODE
        AND MATCH (system_names) AGAINST
        <foreach collection="optionalSystems" item="systemName" separator=" " open="'(" close=")'">
            #{systemName}
        </foreach>
        IN BOOLEAN MODE
        ORDER BY created_at DESC
    </select>

</mapper>

2.1.4.4 查询多个系统名称的任意组合

如果查询需要找到包含某些系统名称的组合，且你不关心顺序，只要包含这些系统名称即可。你可以使用 MATCH ... AGAINST 和 OR 语法组合：

SELECT * FROM `work_order_query` WHERE MATCH (`system_names`) AGAINST ('System1 OR System2 OR System3' IN BOOLEAN MODE);

<mapper namespace="com.example.mapper.WorkOrderQueryMapper">

    <!-- 查询多个系统名称的任意组合 -->
    <select id="selectOrdersWithSystemCombinations" resultType="com.example.model.WorkOrderQuery">
        SELECT * FROM work_order_query
        WHERE MATCH (system_names) AGAINST
        <foreach collection="systemNames" item="systemName" separator=" " open="'(" close=")'">
            #{systemName}
        </foreach>
        IN BOOLEAN MODE
        ORDER BY created_at DESC
    </select>

</mapper>

2.1.4.5 总结：

• 使用布尔模式（IN BOOLEAN MODE）可以根据需要查询多个系统名称。
• + 表示必须匹配的词，OR 表示任意匹配一个词，- 表示排除某个词。
• 使用 MATCH ... AGAINST 可以高效地在包含多个系统名称的 system_names 字段中进行查询。
  这种查询方式适用于 system_names 存储多个系统名称的场景，并且能够支持灵活的查询条件。