数据库构造大表或慢查询SQL语句
Tags： GreenPlumMSSQLMySQLOraclePGSQL Server大表慢查询测试
👉 本文共约409个字，系统预计阅读时间或需2分钟。
本页目录 隐藏
Oracle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | -- 一个查询慢的sql例子 select count(1) from dba_objects a inner join user_objects b on 1=1 inner join user_objects c on 1=1 ; exec dbms_lock.sleep(5); -- 构造大表 select level,level from dual connect by level<=1000; DROP TABLE T_YH_20170705_LHR; CREATE TABLE T_YH_20170705_LHR NOLOGGING AS SELECT 137 || (LPAD(ROWNUM, 8,'0')) x FROM DUAL CONNECT BY LEVEL <= 99999999; create table t1 as select * from dba_objects; insert into t1 select * from t1; insert into t1 select * from t1; 。。。。 -- Oracle 生成20000张表，共包含约40万个字段 DECLARE sql1 VARCHAR2(4000); sql2 VARCHAR2(4000); v_sql VARCHAR2(32767); BEGIN FOR i IN 1..20000 LOOP -- 表 sql1 :='' ; sql1 := 'CREATE TABLE ' || 'tb_' || i || ' ('; -- 列 sql2:= ''; FOR j IN 1..20 LOOP sql2 := sql2 || 'c_' || j || ' number,'; END LOOP; sql2 := RTRIM(sql2, ',') || ')'; v_sql := sql1 || sql2; -- dbms_output.put_line(v_sql); EXECUTE IMMEDIATE v_sql; END LOOP; END; / select count(*) from dba_tables ; select count(*) from dba_tab_cols; select d.owner,COUNT(1) from dba_tables d GROUP BY d.owner ORDER BY 2 desc; |
SQL Server
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | -- 慢查询 -- 延时 '00时:00分:10秒:00毫秒' 执行下一步 waitfor delay '00:00:10:00' -- 大表 drop table test; select * into test from sys.tables; declare @i int set @i=0 while @i<=25 begin RAISERROR('%s:%d', 0, 1, '变量i的值：', @i) WITH NOWAIT insert into test select * from test set @i=@i +1 end -- 3355万行，7g数据 -- 100万 CREATE TABLE BigTable ( id INT NOT NULL, name VARCHAR(50) NOT NULL, age INT, email VARCHAR(100), phone VARCHAR(20), address VARCHAR(200), salary DECIMAL(10,2), hire_date DATE, is_active BIT, PRIMARY KEY (id) ); INSERT INTO BigTable (id, name, age, email, phone, address, salary, hire_date, is_active) SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) AS id, a.name, ABS(CHECKSUM(NEWID())) % 100 AS age, CONCAT(a.name, '@example.com') AS email, '555-555-' + RIGHT('0000' + CAST(ABS(CHECKSUM(NEWID())) % 10000 AS VARCHAR(4)), 4) AS phone, CONCAT(a.column_id, ', ', a.is_identity, ', ', a.is_column_set, ' ', a.collation_name) AS address, CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS DECIMAL(10, 2)) AS salary, DATEADD(day, -ABS(CHECKSUM(NEWID())) % 3650, GETDATE()) AS hire_date, CAST(ABS(CHECKSUM(NEWID())) % 2 AS BIT) AS is_active FROM sys.all_columns a CROSS JOIN sys.all_columns b; |
或者使用存储过程:
在 SQL Server 中，可以通过编写包含大量复杂查询和逻辑的存储过程来模拟长时间运行的存储过程。以下是一个示例存储过程，该存储过程使用循环和延迟来模拟长时间运行的过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE PROCEDURE LongRunningProcedure AS BEGIN SET NOCOUNT ON; DECLARE @Counter INT = 0; WHILE @Counter < 1000000 BEGIN -- Perform complex queries and logic here WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds SET @Counter = @Counter + 1; END END LongRunningProcedure |
在这个示例存储过程中，我们使用了一个简单的循环来模拟长时间运行的过程。在每次循环中，我们可以执行一些复杂的查询和逻辑，然后使用 WAITFOR DELAY 命令在每次循环之间延迟5秒钟。
当我们执行这个存储过程时，它将运行100万次循环，每次循环都需要等待5秒钟。这将导致存储过程运行非常长的时间，从而模拟长时间运行的过程。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
