前言废话
人生开始感受到无力,我不是没心没肺的人,可是我心里真的不舒服
sqlserver
--新建一个表
if OBJECT_ID('test') is not null drop table test
go
create table test
(id int identity(1,1),vid int ,v varchar(600),
constraint pk_test_id primary key (id))
go
--添加一点测试数据
insert into test(vid,v)
select 1,REPLICATE('a',600) union all
select 2,REPLICATE('b',600) union all
select 3,REPLICATE('c',600) union all
select 4,REPLICATE('d',600)
go
--测试数据几何增长
begin tran
insert into test(vid,v)
select vid,v
from test
commit tran
go 21
--判断tsb表,若存在删除它,然后将test表导入,新建一个tb_id
if object_id('tsb') is not null Begin
drop table tsb
End
--test数据导入tsb
select tb_id=identity(int,1,1),convert(int,id) as id into tsb from test
--查询一下试试,第一次很慢,后面还可以,不到一秒
SET STATISTICS TIME ON
GO
/*--你的SQL脚本开始*/
select id, vid, v from tsb where tb_id>= 1500000 and tb_id<=1501000
/*你的SQL脚本结束*/
GO
SET STATISTICS TIME OFF
USE [zzkMvc]
GO
/****** Object: StoredProcedure [dbo].[dsjxp] Script Date: 04/05/2020 15:28:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--整理成存储过程,然后在作业里面每天调用一次
-- =============================================
-- Author: 骄傲的zzk
-- Create date: 2020-04-05
-- Description: 测试大数据查询
-- =============================================
ALTER PROCEDURE [dbo].[dsjxp]
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
if object_id('tsb') is not null Begin
drop table tsb
End
select tb_id=identity(int,1,1),convert(int,id) as id, vid, v into tsb from test
select id, vid, v from tsb where tb_id>= 0 and tb_id<=1
END
--哈哈 应该用Top1 的,但是我懒得改
EXEC dsjxp
作业调用
作业右键新建
青年,了解否??
mysql
--创建表
create table tb_dept(
Id int primary key auto_increment,#部门编号 整形 主键 自增长
Name varchar(18),#部门名称
description varchar(100)#描述
);
--添加内容
insert into tb_dept(Name,description)
select '研发部','研发不会倒'
insert into tb_dept(Name,description)
select Name,description
from tb_dept
DELIMITER $$
drop procedure if exists pro_loop$$
CREATE PROCEDURE pro_loop(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 18 THEN
insert into tb_dept(Name,description)
select Name,description
from tb_dept;
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
END$$
call pro_loop(16);--循环插入
select Id,Name,description from tb_dept LIMIT 2000000,1000
create table tmp_cs(select Id,Name,description from tb_dept LIMIT 2000000,1000)
select * from tmp_cs
DROP TABLE IF EXISTS tmp_cs
其实写到这里我发现自己可能搞错了,还是写全文索引吧,下一篇详细介绍全文索引的使用