数据库构造大表或慢查询SQL语句

由小麦苗 · 发布日期 2021年9月16日 · 已更新 2023年3月7日

0 346 4

Tags： GreenPlum MSSQL MySQL Oracle PG SQL Server 大表慢查询测试

👉 本文共约82个字，系统预计阅读时间或需1分钟。

本页目录 隐藏

-- 一个查询慢的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;
。。。。

-- 一个查询慢的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

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;

。。。。

MSSQL

-- 慢查询
-- 延时 '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;

-- 慢查询

-- 延时 '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

-- 延迟
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

-- 延迟

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


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;

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;

打赏赞(4)

标签： GreenPlum MSSQL MySQL Oracle PG SQL Server 大表慢查询测试

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 3月
一	二	三	四	五	六	日
	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

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

数据库构造大表或慢查询SQL语句

Oracle

MSSQL

MySQL

PostgreSQL、GreenPlum

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

数据库构造大表或慢查询SQL语句

Oracle

MSSQL

MySQL

PostgreSQL、GreenPlum

相关文章

您可能还喜欢...

Oracle 12c中Fetch的用法

在Oracle中，如何清除Shared Pool中某条SQL语句？

新书出版|《Python程序员面试笔试宝典》

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复