Liyren's Blog

just do it!

0%

发表于 更新于 分类于

最近维护一个老系统，有一处查询非常慢，细细研究一番，优化了查询 sql。
该 sql 用来统计摄像机所有状态对应的数量，没加联合索引之前很慢，最后加了三个字段的联合索引优化到一秒左右。
check_day, camera_id, diagnosis_result
要注意，group by 的列也要加到组合索引里面去，这样查询才快。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	ifnull(a.diagnosis_result, '') diagnosisResult,
	count(1) total
FROM
	video_camera aa
LEFT JOIN video_intact_diagnosis_status_201912 a ON (
	aa.id = a.camera_id
	AND a.check_day = '20191223'
)
LEFT JOIN unit_project p ON p.id = aa.project_id
WHERE
	aa.del_flag = 0
GROUP BY
	a.diagnosis_result

发表于 更新于 分类于

1. 创建测试表

1
2
3
4
5
6
7
CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;

2. navicat 工具开启事务1窗口

1
2
3
4
BEGIN;                          //第一步，开启事务1
UPDATE ty SET b =1 WHERE a = 2; //第三步，锁定 a = 2 的记录
UPDATE ty SET b =1 WHERE a = 3; //第五步，卡主，等待事务2上 a = 3 的锁
COMMIT;

3. navicat 工具开启事务2窗口

1
2
3
4
BEGIN;                          //第二步，开启事务2
UPDATE ty SET b =1 WHERE a = 3; //第四步，锁定 a = 3 的记录
UPDATE ty SET b =1 WHERE a = 2; //第六步，等待事务1上 a = 2 的锁，事务1和事务2互相等待对方的锁，此时出现死锁，mysql 会回退当前事务
COMMIT;

发表于 更新于 分类于

数据库主从在实际使用之中，竟然由于各种各样的原因导致数据不一致，需要重新配置。

1、连接主库

1
SHOW MASTER STATUS；

记录下 master_log_pos 的位置，后面需要用到。

2、连接从库

2.1、停用从库

1
STOP SLAVE;

2.2、重置从库

1
reset slave;

2.3、修改主从指向配置

1
2
3
4
5
6
7
CHANGE MASTER TO master_host = '172.20.5.20',
 master_user = 'root',
 master_password = 'rootroot',
 master_port = 3306,
 master_log_file = 'edu-mysql-bin.000016',
 master_log_pos = 978213840,
 master_connect_retry = 30;

2.4、开启同步

1
START SLAVE;

另外，测试环境也经常遇到由于表结构修改后不同步，导致数据同步出错，此时可以跳过错误的语句使得测试进行下去。

1
2
3
stop slave; 
set global sql_slave_skip_counter = 1;
start slave;

发表于 更新于 分类于

之前用 kettle 做过专网到公安网的数据同步，踩过一些坑，记录一下。

1、转换关联的 DB 连接可以右击设置为共享，那么新建其他转换时，就可以选中建好的连接。
2、数据库在修改过字段后，要清除 kettle 的数据库缓存，不然获取新字段的时候获取不了。
3、kettle 要设置字符编码，否则同步的时候数据会乱码。
4、数据同步时，获取更新字段，默认会包含 flagfield 要把这个字段删掉，否则报错。
5、数据同步时，要点击高级，选择操作的字段名为 flagfield。
6、kettle 的转换有时候同步不成功，例如新增的数据没有同步过去，但又不打印日志，很有可能是字段的约束问题导致的。可以新建一个转换重试。
7、新增/修改/删除的同步场景，用表输入 -> 合并记录 -> 数据同步即可

发表于 更新于 分类于 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
package com.hdvon.iomp.visual.utils;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanWrapperImpl;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;

/**
 * @author lyle 2019/5/13 14:14.
 */
public class ExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    private ExcelUtils() {}

    /**
     * @param header   excel 的第一行
     * @param dataList 数据集
     * @param fileName 文件名
     * @param response 响应
     * @description 导出 excel，默认只有一个 sheet
     **/
    public static <T> void export(Map<String, String> header, List<T> dataList, String fileName, HttpServletResponse response) throws IOException {
        try (Workbook workbook = new SXSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("sheet1");
            Row headerRow = sheet.createRow(0);
            List<String> values = Lists.newArrayList(header.values());
            for (int i = 0; i < values.size(); i++) {
                headerRow.createCell(i).setCellValue(values.get(i));
            }

            List<String> keys = Lists.newArrayList(header.keySet());
            int dataStart = 1;
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(dataStart++);
                T t = dataList.get(i);
                BeanWrapperImpl beanWrapper = new BeanWrapperImpl(t);
                for (int k = 0; k < keys.size(); k++) {
                    Object propertyValue = beanWrapper.getPropertyValue(keys.get(k));
                    row.createCell(k).setCellValue(propertyValue == null ? "" : propertyValue.toString());
                }
            }

            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            response.setContentType("application/octet-stream; charset=utf-8");
            workbook.write(response.getOutputStream());
        } catch (IOException ex) {
            logger.error("导出execl异常，error：{}", ex.getMessage());
            throw ex;
        }
    }
}