数据库构造大表或慢查询SQL语句
Tags: GreenPlumMSSQLMySQLOraclePGSQL Server大表慢查询测试
Oracle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | -- 一个查询慢的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; 。。。。 |
MSSQL
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; |
MySQL
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 | -- 延迟 select sleep(9999999); -- 大表 create table t1 SELECT CONCAT('id', seq), CONCAT('name', seq), RAND() FROM (SELECT @seq := @seq + 1 AS seq FROM information_schema.columns, (SELECT @seq := 0) init LIMIT 1000000) s; insert into t1 select * from t1; DELIMITER $$ CREATE PROCEDURE insert_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 100000 DO INSERT INTO my_table (col1, col2, col3) VALUES (i, CONCAT('name', i), NOW()); SET i = i + 1; END WHILE; END$$ DELIMITER ; -- sysbench sysbench /usr/share/sysbench/oltp_common.lua --time=300 \ --mysql-host=127.0.0.1 --mysql-port=3417 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ --table-size=500000 --tables=1 --threads=16 --events=999999999 prepare sysbench /usr/share/sysbench/oltp_common.lua --time=300 \ --mysql-host=127.0.0.1 --mysql-port=3417 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ --table-size=1000000 --tables=10 --threads=16 --events=999999999 prepare |
PostgreSQL、GreenPlum
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 | select pg_sleep(9999); -- 大表 -- select pg_size_pretty(pg_table_size('t2')); CREATE TABLE t_hash AS SELECT id, md5(id::text) FROM generate_series(1, 2000000) AS id; insert into t_hash select * from t_hash; CREATE TABLE BigTable ( id SERIAL PRIMARY KEY, 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 BOOLEAN ); -- GreenPlum插入慢一些 INSERT INTO BigTable (name, age, email, phone, address, salary, hire_date, is_active) SELECT 'Name ' || generate_series(1,10000), (random()*100)::int, 'email'|| generate_series(1,10000) || '@example.com', '555-555-' || to_char((random()*10000)::int, 'FM0000'), 'Address ' || generate_series(1,10000) || ', City ' || (random()*1000)::int || ', State ' || chr((random()*26)::int + 65) || chr((random()*26)::int + 65) || ', ' || (random()*10000)::int, (random()*10000)::numeric(10,2), NOW() - ((random()*3650)::int || ' days')::interval, (random() > 0.5) FROM generate_series(1, 10000) limit 10000000; |