以下代码,实现对数据进行标记,在某carPlate出现后(第一次设置为2),1小时内再出现标记为无效(设置为1),超过1小时的第一个再设置为2,以此类推。
主要问题在我有若干个表,数据结构、索引都一样,结果在9000万行的表,这个代码执行大概300多秒;
在1.5亿行的表,执行居然要7000多秒,实在是搞不明白,已经加大了
join_buffer_size
key_buffer_size
myisam_sort_buffer_size
sort_buffer_size
等buffer为原来的4倍(改buffer前后都是上边的结果,9000万行就300多秒,1.5亿行就6000-7000秒),不起作用。
SET @lastCarPlate='',@lastCheckTime='',@state=0;
update foreignAna_source_data_cameraPoint_201801NA t
set
t.tripState=@state:=case when @lastCarPlate<>t.carPlate or TIMESTAMPDIFF(MINUTE,@lastCheckTime,t.throughTime)>=60 then 2 else 1 end,
t.tripState= case when 'x'=(@lastCarPlate:=t.carPlate) or 'x'=( @lastCheckTime:=case when @state=1 then @lastCheckTime else t.throughTime end) then @state else @state end
where t.dataValid>0 and t.cameraPoint_id in (SELECT addressID from baseService_camerapoint_base_info where crange=2)
ORDER BY
t.dayOfMonth,t.carPlate,t.throughTime;
表结构如下:
CREATE TABLE `NewTable` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`carPlate` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`plateColorTail` tinyint(4) NOT NULL ,
`cameraPoint_id` mediumint(9) NOT NULL ,
`throughTime` datetime NOT NULL ,
`driveDirection` tinyint(4) NOT NULL ,
`dataValid` tinyint(4) NOT NULL ,
`dayOfMonth` tinyint(4) NULL DEFAULT NULL ,
`zoneOfDay` smallint(6) NULL DEFAULT NULL ,
`tripState` tinyint(4) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
;
在MySQL中,执行时间的多少受到许多因素的影响,包括但不限于表大小、索引的使用、查询类型、子查询、连接的开销、磁盘I/O、硬件配置等。以下是一些可能导致MySQL执行时间增加20倍的原因:
数据量:如果一个表包含大量的数据,那么查询操作的时间也会增加。如果你正在处理的数据量是原始表的1.5倍,那么查询的时间会翻倍。这可能是因为MySQL需要遍历表中的所有数据,每次查询都需要进行索引扫描、复制数据、执行计算等操作,这些操作的时间都会增加。
索引:如果你的表包含索引,那么查询的时间会减少。索引可以提高查询速度,因为它允许MySQL快速定位到需要的数据行,而无需遍历整个表。但是,如果你的表中有大量的索引,那么索引的维护和管理也会增加查询时间。
查询类型:不同的查询类型对执行时间的影响也不同。例如,全表扫描(SELECT * FROM table_name)通常比子查询(SELECT column_name FROM table_name WHERE condition)更快,因为全表扫描只需要查询表中的数据,而子查询需要执行一个子查询,子查询再执行一次查询。但是,如果查询中有子查询或嵌套查询,那么执行时间可能会增加。
子查询:子查询是指在主查询中嵌套的查询。子查询会执行子查询本身,而不会返回结果,而是返回一个结果集。子查询的执行时间取决于子查询的复杂性、查询结果的数量以及MySQL的查询优化器是否对子查询进行了优化。如果子查询的执行时间比主查询长,那么整个查询的时间也会增加。
连接的开销:MySQL连接数据库时会消耗一定的时间和资源。连接包含许多步骤,如创建连接、发送数据、关闭连接等,这些步骤可能会消耗一定的时间。如果连接的打开和关闭频繁,那么连接的开销也会增加查询时间。
磁盘I/O:MySQL读取或写入数据时会消耗磁盘I/O操作,这些操作的时间可能会消耗大量的CPU时间。例如,当你写入一个大表时,MySQL可能需要读取和写入大量的数据到磁盘上,这可能会导致磁盘I/O操作的时间增加。
硬件配置:MySQL的硬件配置,如CPU、内存、磁盘空间等,也可能影响查询时间。如果你的硬件配置不足,如CPU和内存不足,那么MySQL可能会使用更慢的算法,或者使用更多的内存,这可能会导致查询时间增加。
总的来说,如果MySQL的表大小、索引使用、查询类型、子查询、连接的开销、磁盘I/O、硬件配置等都比原始表大20倍,那么查询的时间会增加20倍以上。为了避免这种情况,可以采取以下措施:
优化索引:优化索引可以大大提高查询速度。例如,你可以创建精确的索引,只包含你需要的列,避免创建复杂的索引。
分页查询:如果你的表中的数据量较大,你可以使用分页查询来减少查询的次数。例如,你可以使用LIMIT子句来指定查询的页数,这样MySQL就可以从表的上一页开始查询,直到达到指定的页数。
使用游标:游标是一种用于读取和写入表中的数据的机制,可以提高查询的效率。你可以使用游标来遍历表中的数据,或者在更新表时一次性更新多个数据行。
分批查询:如果你的数据量非常大,可以将查询分成多个批次进行处理。例如,你可以将查询分成几个部分,先处理一部分数据,然后再处理剩余的数据。
使用索引合并:索引合并是一种将多个索引合并为一个索引的方法,可以减少索引的大小,从而提高查询的效率。例如,你可以创建一个索引合并表,将多个索引合并为一个索引,然后在查询时使用这个索引合并表。
使用数据库优化器:MySQL的查询优化器可以优化查询的性能,包括减少索引扫描、优化查询算法、避免使用游标等。你可以使用MySQL的优化器来优化你的查询,从而提高查询的效率。
以上只是一些可能的因素,具体情况可能会因数据库管理系统(DBMS)、表结构、查询算法和硬件配置的不同而有所不同。因此,建议你在实际应用中进行测试和分析,找出导致查询时间增加的具体原因,并根据需要采取相应的优化措施。