菜单

一条慢查询引发的MYSQL索引思考402.com:

2019年5月19日 - 计算机教程

402.com 1

4、插入完成插入失败,id:123已存在

logger日志报错 插入数据时违反主键唯一约束

KEY `idx_url` (`url`),

  

——提交spring 事务省略代码 ——-

解决方案

5、不要在非null列创建索引,如果值为null时,建议替换成1或-1等常量。本示例start_time、end_time两个字段优化为非空。

初步分析

@来自科多大数据

SELECT DISTINCT
            goodsid,
            proddate,
            v.INVALIDDATE,
            lotno 
        FROM
            BMS_ST_qty_LST_GAOXING_V v 
        WHERE
            storageid = 10

KEY `idx_third_cate` (`third_cate`),

for (SyncBatch erpSyncBatch : erpListSyncBatch) {
            if (erpSyncBatch.getFlag() == null) {
                try {
                    middleService.insertSyncBatch(erpSyncBatch);
                } catch (DuplicateKeyException e) {
                    LOGGER.error("违反唯一约束"+erpSyncBatch.getErpId());
                }catch (Exception e) {
                    LOGGER.error(ERROR_SYNC_BATCH,e);
                    throw new RuntimeException(e);
                }
            }
        }

第四步:查看表索引的创建明细

   

order by weight desc desc

402.com 2

本次事故示例按照上面的原则对索引进行优化:最终去掉了以前的所有索引,根据具体业务,只新建了两个复合索引(其他查询都可以重用复合索引中的部分)。

  经上面查证 证实是数据维护的原因 脑海里第一个想法是修改数据
但是考虑到数据有关联的原因 修改并不合适

发现查询命中索引’idx_status’,看起来像是在一个状态字段上建了索引。进一步确认,证实status字段是一个状态字段(0-正常,1-下线)。

  但假如不修改 将查询到的数据直接插入到sync_batch 还是会报异常

and start_time <= ‘2017-05-31 20:30:00’

发现查到数据有2条

第三步:分析执行计划,以及索引命中情况

 LOTNO中有一条数据后面存在空串导致 去重关键字没办法去重 

4、对无用字段创建索引:url,这个索引根本就没用。

org.springframework.dao.DuplicateKeyException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '189378605' for key 'PRIMARY'
### The error may involve com.ccf.springboot.mapper.middle.MiddleMapper.insertSyncBatch-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO sync_batch ( uniqueId, erpId, manufactureDate, effectivityDate, batchNumber, createdDate, modifyDate )     VALUE    (?,?,?,?,?,NOW( ),NOW( ) )

KEY `idx_valid_query`
(`start_time`,`end_time`,`status`,`valid_flag`)

402.com 3

3、插入id为123的记录等待

  在这个表中主键uniqueId 不是自增长
而是由erpId和batchNumber合并组成主键id

其实不要被事务所欺骗,我们来分析下在高并发情况下,两个相同id的插入请求:

 

问题很明显,就是数据在插入时,发现改主键id
‘xxx’已经存在,报主键重复写入冲突错误。立即提取代码分析问题,这里的主键id是另外一张表X的主键,根据业务查询X表,对满足条件的记录进行加工后插入该新表。其代码逻辑如下:

   当有重复的主键id插入时报了这个异常
即插入的数据可能存在数据重复的问题 ok 那么接下来排查下sql 看看原始数据

到这里突然发现自己应该负很大的责任:对新同事的培养,平时都只是停留在java
coding上,尤其是现在大部分业务都有redis缓存挡在上一层,对sql的基础规范没有组织学习,上线前的code
review也没有覆盖到sql。

    尝试将异常抓起 不予解决 然后记录日志   问题解决

第一步:停服务

接下来去除关键字再试下

xxxDao.update(newInfo);//如果已存在,就修改

一番排查后 找到了原因

新问题Duplicate PRIMARY

在sql中使用了关键字DISTINCT 正常来说不应有重复数据产生 为了排查原因
将报错的erpId 带入本条sql查询(图一中的erpId 对应上图的goodsid) 

查看执行计划:explain select xxx from xxx_pc_act_profile where xxx;

发现查到有3条一模一样的数据  这就说明去重关键字实际上有起作用的
但是为什么有一条数据没有去重呢 

msyql创建索引的基本原则

 

最终的解决办法:采用 insert duplicate
update语句,问题得到解决,语法如下:

3、删除冗余索引,没有用到的索引必须全部删除,避免不必要的空间浪费。本示例中url
索引是无用的。

13、不要使用 not in\like,会导致索引失效。not in可以用not
exists替换。in和or所在列最好有索引

KEY `idx_pre_cate_level` (`pre_cate_level`),

也行还会发现其他很多的问题。

KEY `idx_end_time` (`end_time`),

本周我所负责的一个系统就出来了类似的问题,庆幸的是处理及时,没有造成灾难性的后果。今天抽时间把事情的经过记录下来,在未来的时间里鞭策自己:对新成员必须进行sql语句基础规范的学习,并对每个人每次上线的sql语句必须进行code
review。

通过这个反面教材,快速的总结了在创建索时的注意事项(基本原则):

至此该问题解决,期间丢失部分业务数据,但庆幸的是该系统是一个外围系统,损失还在可控范围内。

(ps:普通java开发,非dba总结,不全的地方,还望有DBA大神补充下)。

INSERT INTO table (xx,xx,xx) VALUES (xx,xx,xx) ON DUPLICATE KEY UPDATE
….

该问题导致的后果:在发现已经存在后,应该执行修改,但冲突后直接抛出异常,修改操作没有被执行,导致修改数据丢失。该问题在高并发的情况下,还会经常出现。

由于mysql的插入是不可分割的指令是原子性的,必须得等待其中一条插入完成后,另一条才能插入。这就导致了上述“主键冲突”异常的发生。

8、创建复合索引,需要注意把区分度最大的放到最前面(如果与第6点冲突,需要自己根据业务平衡下)。

初步看该代码没有问题啊,在一个事务里面,怎么会出现插入时“主键冲突”呢。

PRIMARY KEY (`id`),

KEY `idx_valid_flag` (`valid_flag`),

在解决上述索引问题的过程中,我始终觉得这张表的主键创建方式会导致问题。根据上述主键的创建原则:“主键最好使用自增型”,但上述表的主键不满足该规则:

第二步:初步确定索引问题

从慢查询日志中可以看到,最消耗性能的语句是“SELECT
xxx_pc_act_profile”,该语句在26分钟内(Time range: 2017-05-31
20:20:02 to
20:46:04),执行7618次,平均每次大约113ms,已经到了无法容忍的地步。并且不幸的是,这张表确实是属于我们系统。

2、尽量不要在经常被修改的字段上建索引,会增加插入的成本,以及提高死锁发生的概率。例如本示例不会在weight字段加索引

可以看到这里的where语句里有很多查询条件,还有order
by语句,由于select语句导致的性能问题,可以99%的确定是索引设置不当引起的。

1、不要在低基数列创建索引。浪费索引存储空间,并且不会提高查询效率。

KEY `idx_confirm_flag` (`confirm_flag`),

402.com 4

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图