回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我的应用程序出现问题,导致MySQL表被锁定,因为插入需要很长时间,在查看在线文章后,它似乎与自动增量有关,信息如下-</p>
<p>插入数据的Python(不幸的是,每次插入一行,因为我需要自动递增的id以供将来插入时参考)——</p>
<pre><code>for i, flightobj in stats[ucid]['flight'].items():
flight_fk = None
# Insert flights
try:
with mysqlconnection.cursor() as cursor:
sql = "insert into cb_flights(ucid,takeoff_time,end_time,end_event,side,kills,type,map_fk,era_fk) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(sql, (
ucid, flightobj['start_time'], flightobj['end_time'], flightobj['end_event'], flightobj['side'],
flightobj['killnum'], flightobj['type'], map_fk, era_fk))
mysqlconnection.commit()
if cursor.lastrowid:
flight_fk = cursor.lastrowid
else:
flight_fk = 0
except pymysql.err.ProgrammingError as e:
logging.exception("Error: {}".format(e))
except pymysql.err.IntegrityError as e:
logging.exception("Error: {}".format(e))
except TypeError as e:
logging.exception("Error: {}".format(e))
except:
logging.exception("Unexpected error:", sys.exc_info()[0])
</code></pre>
<p>上述操作每2分钟在相同的数据上运行一次,并且应该只插入非重复数据,因为MySQL会由于唯一的ucid_takeofftime索引而拒绝重复数据</p>
<p>MYSQL信息,cb_航班表-</p>
<pre><code> `pk` int(11) NOT NULL AUTO_INCREMENT,
`ucid` varchar(50) NOT NULL,
`takeoff_time` datetime DEFAULT NULL,
`end_time` datetime DEFAULT NULL,
`end_event` varchar(45) DEFAULT NULL,
`side` varchar(45) DEFAULT NULL,
`kills` int(11) DEFAULT NULL,
`type` varchar(45) DEFAULT NULL,
`map_fk` int(11) DEFAULT NULL,
`era_fk` int(11) DEFAULT NULL,
`round_fk` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `ucid_takeofftime` (`ucid`,`takeoff_time`),
KEY `ucid_idx` (`ucid`) /*!80000 INVISIBLE */,
KEY `end_event` (`end_event`) /*!80000 INVISIBLE */,
KEY `side` (`side`)
) ENGINE=InnoDB AUTO_INCREMENT=76023132 DEFAULT CHARSET=utf8;
</code></pre>
<p>现在,从Python代码插入到表中,有时可能需要60秒以上。
我认为这可能与在表上创建锁的自动增量有关,如果是这样,我正在寻找解决方法</p>
<p>innodb信息-</p>
<pre><code>innodb_autoinc_lock_mode 2
innodb_lock_wait_timeout 50
</code></pre>
<p>缓冲区的使用率高达70%左右</p>
<p>感谢应用程序端或MySQL端对此提供的任何帮助</p>
<p>编辑
为cb_kills表添加create语句,该语句也与insert一起使用,但据我所见没有问题,这是对第一个答案的评论的回应</p>
<pre><code>CREATE TABLE `cb_kills` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`time` datetime DEFAULT NULL,
`killer_ucid` varchar(50) NOT NULL,
`killer_side` varchar(10) DEFAULT NULL,
`killer_unit` varchar(45) DEFAULT NULL,
`victim_ucid` varchar(50) DEFAULT NULL,
`victim_side` varchar(10) DEFAULT NULL,
`victim_unit` varchar(45) DEFAULT NULL,
`weapon` varchar(45) DEFAULT NULL,
`flight_fk` int(11) NOT NULL,
`kill_id` int(11) NOT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `ucid_killid_flightfk_uniq` (`killer_ucid`,`flight_fk`,`kill_id`),
KEY `flight_kills_fk_idx` (`flight_fk`),
KEY `killer_ucid_fk_idx` (`killer_ucid`),
KEY `victim_ucid_fk_idx` (`victim_ucid`),
KEY `time_ucid_killid_uniq` (`time`,`killer_ucid`,`kill_id`),
CONSTRAINT `flight_kills_fk` FOREIGN KEY (`flight_fk`) REFERENCES `cb_flights` (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=52698582 DEFAULT CHARSET=utf8;
</code></pre>