存储过程批量插入数据和字符串匹配kmp算法和sql使用like的时间性能对比

最新推荐文章于 2022-08-22 00:07:01 发布
最新推荐文章于 2022-08-22 00:07:01 发布
阅读量182 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44690103/article/details/105098099
版权

1.postgresql存储过程插入1k数据

create or replace FUNCTION proc_initData() returns 
boolean AS
$BODY$
DECLARE ii integer;
  begin
  ii:=1;
 FOR ii IN 1..1000 LOOP
  INSERT INTO MRS_JOB (JOB_ID, USER_NAME, JOB_NAME, JOB_TYPE, LAUNCHER_ID, APP_ID, JOB_PROGRESS, JOB_STATE, JOB_RESULT, EXECUTE_SQL, MAIN_JAR, ARGS, PROPERTIES, SUBMIT_TIME, START_TIME, END_TIME, ELAPSED_TIME, QUEUE, TRACKING_URL) VALUES(overlay(overlay(md5(random()::text || ':' || clock_timestamp()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8+1) + 8)::int)::text from 17)::cstring, 'mrs_marketing', 'hbase_test', 9, 'application_1584339543389_0003','', 0, 3, 0, '', '', '[--class, com.huawei.bigdata.spark.examples.TableInputData, --master, yarn-cluster, obs://obs-hjm/tools/test/SparkOnHbaseJavaExample-1.0-base.jar, obs://obs-hjm/tools/test/data.txt]', '{}', 1584350781865, 0, 0, 0, 'default', '');
  end loop;
  return true;
  end;
$BODY$
LANGUAGE plpgsql;
select * from proc_initData() as tab;

2.mysql存储过程插入插入1000条数据：

delimiter $$
CREATE PROCEDURE proc_initData()
BEGIN 
    DECLARE i INT DEFAULT 1;
    WHILE i<=60 DO
        INSERT INTO youyanpingemr.host (host_id, cluster_id, host_ip, internal_ip, node_type, node_size, host_status, create_at, create_by, update_at, update_by, frozen, resource_id, volume_resource_id, host_name, vm_resource_id, billing_type, product_id, server_id, group_id, flavor, charging_start_time, charging_end_time, deploy_job_id, ecs_job_id, ecs_job_result, cpu_type) VALUES('node-ana-coreYZfh.com', '67fec10d-e218-4f2a-8e60-0101add2e15c', '100.66.129.151', '192.168.0.72', 'Analysis_Core', 'd2.xlarge.8.linux.bigdata', 'started', 1578736143, '0572128a7f00d4421f06c01d7e946339', 1579158847, 'Execution tier', 0, 'ac0fab54-b539-4f11-a916-73c16799c46d', '', '67fec10d-e218-4f2a-8e60-0101add2e15c_node_ana_coreYZfh', '', 12, '00301-212011-0--0', '48ba47bb-1026-44ab-871a-8369b229f66e', 'ae1d5520-9cec-4e3f-90da-fa09e478f302', 'd2.xlarge.8', 1578737098, NULL, 'H379yKGmL7', '8abf96486f6422fa016f9404ee676d6e', '{"status":"SUCCESS","entities":{"sub_jobs_total":1,"sub_jobs":[{"status":"SUCCESS","entities":{"volume":null,"server":{"created":"2020-01-11T09:52:29Z","name":"67fec10d-e218-4f2a-8e60-0101add2e15c_node_ana_coreYZfh","id":"48ba47bb-1026-44ab-871a-8369b229f66e","market_image_id":null,"market_image_name":null}},"message":null,"code":null,"begin_time":"2020-01-11T09:52:28.861Z","job_type":"createSingleServer","end_time":"2020-01-11T09:52:59.935Z","error_code":null,"fail_reason":null,"job_id":"8abf96486f6422fa016f9404eebe6d71"}]},"message":null,"code":null,"begin_time":"2020-01-11T09:52:28.773Z","job_type":"createServer","end_time":"","error_code":null,"fail_reason":null,"job_id":"8abf96486f6422fa016f9404ee676d6e"}', 'X86');
        SET i = i+1;
    END WHILE;
END$$
CALL proc_initData();

使用kmp算法：
关键搜索字符串为空：从数据库分页获取
关键搜索字符串不为空：一次性从数据库获取出来所有（2000）作业，使用kmp算法，根据关键搜索字匹配出符合条件的记录，再使用分页返回
以下是使用kmp算法(人为将其同时既能匹配到id又能匹配name,并且匹配后的数目是2k)搜索（共2000个作业）单页获取的时间

[2020-03-25 08:56:10,409 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:10,410 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 394192658
[2020-03-25 08:56:21,661 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:56:21,661 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: a) counts:  2000
[2020-03-25 08:56:21,662 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: a) counts:  1780
[2020-03-25 08:56:21,662 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:56:21,663 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:21,663 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 273113669
[2020-03-25 08:56:22,238 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:56:22,239 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: a) counts:  2000
[2020-03-25 08:56:22,240 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: a) counts:  1780
[2020-03-25 08:56:22,240 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:56:22,241 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:22,241 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 249490317
[2020-03-25 08:56:43,596 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:56:43,597 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:56:43,597 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:56:43,598 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:56:43,598 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:43,598 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 284109381
[2020-03-25 08:56:51,641 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:56:51,642 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:56:51,642 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:56:51,642 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:56:51,643 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:51,643 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 230350186
[2020-03-25 08:56:56,313 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:56,313 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 37106210
[2020-03-25 08:56:56,816 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:56:56,817 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:56:56,818 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:56:56,818 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:56:56,819 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:56,819 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 336294697
[2020-03-25 08:56:58,718 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:58,719 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 28235335
[2020-03-25 08:56:59,299 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:56:59,300 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:56:59,300 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:56:59,301 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:56:59,301 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:56:59,302 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 220375620
[2020-03-25 08:57:00,925 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:57:00,925 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 24961473
[2020-03-25 08:57:01,503 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:57:01,504 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:57:01,505 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:57:01,505 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:57:01,506 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:57:01,506 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 238032392
[2020-03-25 08:57:21,758 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:57:21,759 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:57:21,760 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:57:21,760 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:57:21,761 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:57:21,761 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 364050243
[2020-03-25 08:57:51,618 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:57:51,619 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:57:51,620 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:57:51,620 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000

以下是使用kmp算法(只匹配部分数据)搜索（共2000个作业）单页获取的时间

[2020-03-25 08:51:15,894 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 248529915
[2020-03-25 08:51:20,577 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:51:20,579 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:51:20,580 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:51:20,580 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:51:20,586 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:51:20,586 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 220040161
[2020-03-25 08:51:21,943 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:51:21,944 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: t) counts:  2000
[2020-03-25 08:51:21,945 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: t) counts:  0
[2020-03-25 08:51:21,945 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:2000
[2020-03-25 08:51:21,959 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:51:21,959 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 546312854
[2020-03-25 08:51:34,251 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:51:34,252 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: 059) counts:  0
[2020-03-25 08:51:34,252 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: 059) counts:  19
[2020-03-25 08:51:34,253 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:19
[2020-03-25 08:51:34,256 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 19
[2020-03-25 08:51:34,256 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 226277718
[2020-03-25 08:51:45,347 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:51:45,348 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: 418ea) counts:  0
[2020-03-25 08:51:45,350 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: 418ea) counts:  1
[2020-03-25 08:51:45,350 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:1
[2020-03-25 08:51:45,352 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 1
[2020-03-25 08:51:45,352 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 245592444
[2020-03-25 08:51:48,943 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:51:48,943 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 20996414
[2020-03-25 08:51:51,415 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:51:51,416 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 20876058
[2020-03-25 08:51:55,937 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:51:55,939 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: 9f5) counts:  0
[2020-03-25 08:51:55,940 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: 9f5) counts:  13
[2020-03-25 08:51:55,940 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:13
[2020-03-25 08:51:55,943 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 13
[2020-03-25 08:51:55,943 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 298479273
[2020-03-25 08:52:02,498 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:52:02,499 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: ed0cd) counts:  0
[2020-03-25 08:52:02,500 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: ed0cd) counts:  1
[2020-03-25 08:52:02,500 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:1
[2020-03-25 08:52:02,501 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 1
[2020-03-25 08:52:02,501 +0000] [INFO ] [http-bio-20002-exec-9] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 207101883
[2020-03-25 08:52:06,514 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:52:06,514 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 14711891
[2020-03-25 08:52:08,494 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:52:08,494 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 26702163
[2020-03-25 08:52:13,202 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:52:13,203 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: 2db51) counts:  0
[2020-03-25 08:52:13,204 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: 2db51) counts:  1
[2020-03-25 08:52:13,204 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:1
[2020-03-25 08:52:13,205 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 1
[2020-03-25 08:52:13,205 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 201478387
[2020-03-25 08:52:13,744 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 127]  The counts before filtering : 2000
[2020-03-25 08:52:13,745 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 129] Filtered(name: 2db51) counts:  0
[2020-03-25 08:52:13,746 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 132] Filtered(id: 2db51) counts:  1
[2020-03-25 08:52:13,746 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 137] Filtered counts:1
[2020-03-25 08:52:13,746 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 1
[2020-03-25 08:52:13,746 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 205738197
[2020-03-25 08:52:17,368 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:52:17,369 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 16730608
[2020-03-25 08:52:21,456 +0000] [INFO ] [http-bio-20002-exec-8] [com.huawei.bigdata.api.emr.service.v20.JobServiceImpl 157] Filtered totalRecord: 2000
[2020-03-25 08:52:21,456 +0000] [INFO ] [http-bio-20002-exec-8] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 98] kmp time consume 16440412

使用数据库中的正则配like，从数据库分页获取
以下是数据库使用like搜索（共2000个作业）单页获取的时间

[2020-03-25 08:13:45,175 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume **15029194**
[2020-03-25 08:13:55,365 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 15263304
[2020-03-25 08:14:15,299 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 19811270
[2020-03-25 08:14:25,342 +0000] [INFO ] [http-bio-20002-exec-7] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 23871464
[2020-03-25 08:14:45,525 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 17296445
[2020-03-25 08:14:55,298 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 26683817
[2020-03-25 08:15:15,158 +0000] [INFO ] [http-bio-20002-exec-10] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 14179408
[2020-03-25 08:15:25,300 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 13463978
[2020-03-25 08:15:45,183 +0000] [INFO ] [http-bio-20002-exec-7] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 33195611
[2020-03-25 08:15:55,304 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 13828684
[2020-03-25 08:16:02,207 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 23140336
[2020-03-25 08:16:04,775 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 24448815
[2020-03-25 08:16:06,262 +0000] [INFO ] [http-bio-20002-exec-6] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 22427898
[2020-03-25 08:16:08,339 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 22147082
[2020-03-25 08:16:15,090 +0000] [INFO ] [http-bio-20002-exec-10] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 11737834
[2020-03-25 08:16:15,768 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 13653548
[2020-03-25 08:16:18,297 +0000] [INFO ] [http-bio-20002-exec-10] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 16057036
[2020-03-25 08:16:25,189 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 14710841
[2020-03-25 08:16:25,890 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 14273178
[2020-03-25 08:16:29,062 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 20399135
[2020-03-25 08:16:32,066 +0000] [INFO ] [http-bio-20002-exec-5] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 16621312

以下是数据库使用like搜索只匹配部分数据（共2000个作业）单页获取的时间

[2020-03-25 09:07:51,964 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 34156740
[2020-03-25 09:08:21,441 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 44045469
[2020-03-25 09:08:35,698 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 12522760
[2020-03-25 09:08:37,866 +0000] [INFO ] [http-bio-20002-exec-1] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 20108385
[2020-03-25 09:08:43,863 +0000] [INFO ] [http-bio-20002-exec-4] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 14755581
[2020-03-25 09:08:48,033 +0000] [INFO ] [http-bio-20002-exec-10] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 13058789
[2020-03-25 09:08:51,403 +0000] [INFO ] [http-bio-20002-exec-10] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 19465990
[2020-03-25 09:08:55,598 +0000] [INFO ] [http-bio-20002-exec-3] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 14555132
[2020-03-25 09:09:00,289 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 27221298
[2020-03-25 09:09:00,515 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 27966694
[2020-03-25 09:09:21,396 +0000] [INFO ] [http-bio-20002-exec-2] [com.huawei.bigdata.api.emr.controller.v20.JobExecController 81] like time consume 13960608
weixin_44690103
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
kmp算法sql查询实践_有用SQL查询实践
weixin_26711425的博客
09-10 248
kmp算法sql查询实践A database is important in App development. After getting so familiar with commands to build a database in Ruby on Rails, I found it’s necessary to review some common SQL queries. In this ...
通过存储过程批量插入数据
delightK的博客
04-05 1538
创建表： -- tb_item 表结构 CREATE TABLE `tb_item` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id', `title` varchar(100) NOT NULL COMMENT '商品标题', `price` decimal(20,2) NOT NULL COMMENT '商品价格，单位为：元', `num` int(10) NOT NULL COMMENT '库存数量', `categor
Mysql通过存储过程批量插入数据
星空
12-02 585
创建表 DROP TABLE if exists employees_partition; CREATE TABLE if not exists `employees_partition` ( `id` int(11) NOT NULL , `name` varchar(32) DEFAULT NULL COMMENT '员工姓名', `job_no` varchar(16) NOT ...
KMP算法（浅显易懂）
时间慢慢的博客
08-21 689
KMP算法求解什么类型问题字符串匹配。给你两个字符串，寻找其中一个字符串是否包含另一个字符串，如果包含，返回包含的起始位置。讲道理 大串A（长度n） 小串B（长度m） 一般匹配字符串，一个一个比，当前字符对了则比对下一个，不对了再从B的头开始比，移动一个位置；这样的时间复杂度是O(n*m) KMP可以实现复杂度为O(m+n) 改进的点就在于通过当发生不匹配时，之前匹配完成的部分的信息的利用
SQL Server中检索语句中Like的算法实现 (转)
ciya3282的博客
08-14 137
SQL Server中检索语句中Like的算法实现 (转)[@more@]本文主要对字串匹配Like的算法实现，在sql SERVER中Like的匹配中主要有表现为对两个通配符的处理，分别为“_”代表一个字符，“%”代表任意个字...
kmp算法-使用kmp算法在Golang中实现字符串匹配.zip
最新发布
05-16
KMP算法的核心思想是避免对已知不可能产生匹配的字符进行比较，通过构建一个“部分匹配表”（也称为“失败函数”或“跳跃表”），来指导主串与模式串的匹配过程。当在主串中遇到不匹配的情况时，算法能够根据部分...
Python实现字符串匹配的KMP算法
09-19
KMP算法的关键是利用匹配失败后的信息，尽量减少模式串与主串的匹配次数以达到快速匹配的目的。这篇文章主要介绍了Python实现字符串匹配的KMP算法,需要的朋友可以参考下
字符串匹配的KMP算法.rar_KMP_KMP算法_kmp 字符串匹配_字符串匹配_文件
09-24
总的来说，KMP算法是一种高效的字符串匹配方法，它通过避免不必要的字符比较和回溯，大大提高了搜索效率。理解并掌握KMP算法，对于从事编程和相关领域的专业人士来说，是提高工作效率和解决问题的关键技能之一。
kmp算法-基于Python+kmp算法实现模糊文本字符串匹配.zip
03-22
KMP算法的核心在于构造一个部分匹配表，它存储了子串中每个字符之前的所有前缀和后缀的最大公共长度，以此来指导匹配过程，避免回溯。 在Python中实现KMP算法，首先我们需要构建部分匹配表。部分匹配表通常用一个...
KMP(字符串匹配)算法
08-20
程序开发过程中的字符串匹配算法很多，这里出了算法的程序源代码，包括C#，C++, Delphi代码,大家直接下载就可以拷贝到自己程序中使用。
SQL LIKE语句多条件贪婪匹配算法
01-19
在CMS开发中，经常会有类似这样的需求： 　　提问——回答模式，经典的例子是百度提问。 　　提问者提出问题，由其他人回答，其他人可以是用户，也可以是服务商。 　　在这个模式中，如何充分利用历史数据是关键的技术。很多时候，由于客户不擅长使用搜索功能，一上来提问，而这些问题往往早已经有近乎完美的答案，但没有充分利用。这样一来，不仅加大了劳动量，又增加了数据冗余。 　　如果在提问的时候能充分调动历史数据，提交问题之前先看看历史问题能不能解决客户疑问，解决了，好不过，解决不了，再提交。百度提问是采用的这种方案： 　　模式固然好，可怎么实现有些困难了，毕竟这是百度作为搜索引擎的
SQL LIKE 通配符随笔
u014416859的专栏
03-28 264
通配符 说明  _    与任意单字符匹配   %    与包含一个或多个字符的字符串匹配   [ ]  与特定范围（例如，[a-f]）或特定集（例如，[abcdef]）中的任意单字符匹配。   [^]  与特定范围（例如，[^a-f]）或特定集（例如，[^abcdef]）之外的任意单字符匹配。   例子: • WHERE FirstName LIKE '_im' 可以
KMP算法——很详细的讲解
04-16 180
前段时间学习KMP算法，感觉有些复杂，不过好歹是弄懂啦，简单地记录一下，方便以后自己回忆。 引入 首先我们来看一个例子，现在有两个字符串A和B，问你在A中是否有B，有几个？为了方便叙述，我们先给定两个字符串的值 A=”abcaabababaa” B=”abab” 那么普通的匹配是怎么操作...
KMP 算法
Daylight629博客
04-12 118
KMP 算法 字符串匹配问题 字符串匹配问题：： 有一个字符串 str1="“javamysql”"，和一个子串 str2="java " 现在要判断 str1 是否含有 str2, 如果存在，就返回第一次出现的位置, 如果没有，则返回-1 一、暴力匹配算法 如果用暴力匹配的思路，并假设现在 str1 匹配到 i 位置，子串 str2 匹配到 j 位置，则有: 如果当前字符匹配成功（即 str1[i]==str2[j]），则 i++，j++，继续匹配下一个字符 如果失配（即 str1[i]!=st
Mysql创建存储过程--批量插入数据
一个懒鬼的博客
08-22 5146
- 使用date_add()函数将时间进行转换为秒数，并赋值给dt参数。IN dt timestamp -- dt参数用来传入时间戳，开始插入的第一条数据的时间（格式为：'2020-10-24 14:31:44'）-- 由于id是自动增长，不需要额外赋值，所以需要将剩余的其他字段全部列出进行一一对应赋值插入。1）检查库中函数下是否已存在同名的存储过程，如果存在，则需要执行删除存储过程的操作。-- 将number的值赋值为1，代表循环从1开始。-- 如果需要存储过程已存在，则可以先执行删除存储过程操作。
mysql like 原理_全文索引 与 Like 的实现原理
weixin_31170085的博客
01-19 1678
在数据库使用中，DBA都会告诉大家SQL的LIKE条件为%XXX%号时，由于不能使用索引，当数据量变大时(比如超过百万条)，全表扫描会导致性能很差。但是在实际业务中，很难避免MySQL全文检索并Like索引的这种需求。比如模糊搜索用户帐号，昵称之类。既然这个需求必须做，但又不可以直接用LIKE。这里我和大家分享一下我们关于这种需求的一种解决方案。当然别人也可能采用过类似的办法，我不是很清楚。所以也...
SQL多条件模糊查询解决方案(类似百度搜索)
weixin_34273479的博客
10-18 758
参考博文：            SQL LIKE语句多条件贪婪匹配算法          SQL LIKE语句多条件贪婪加权匹配算法(改进版)          SQL LIKE语句多条件贪婪加权匹配(新增必要词指定)   前言：          算法的基本特性在前几篇博客中已经做了详细的说明，经过不断的改进优化，到归仓的时候了，也就是说，该算法告一段落，不再更新...
算法设计技巧——贪婪算法、分治算法、动态规划算法、随机化算法和回溯算法（未完待续）
weixin_42208959的博客
09-03 496
通过贪婪算法、分治算法、动态规划算法、随机化算法、回溯算法5个经典算法的设计思想来介绍下算法的魅力
利用数据库的存储过程批量建表和批量插入数据
sunny05296的博客
12-20 4715
==============MySQL==================== /* 利用MySQL存储过程批量创建MySQL的表： test_1 test_2 test_... */ -- 如果存储过程已存在，先删除 DROP PROCEDURE IF EXISTS `create_tables`; -- 创建存储过程 DELIMITER $$ CREATE PROCEDURE `cr.........
字符串匹配蛮力算法和kmp算法的时间复杂度
12-06
具体实现是从主串的第一个字符开始，依次和模式串的每一个字符进行比较，如果匹配失败，则主串的指针后移一位，重新开始匹配。这种算法的缺点是效率低下，当模式串和主串长度较大时，时间复杂度会非常高。 KMP算法...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值