别裁员!别裁员!别裁员! 一招降低IT数据库成本

export PGPASSWORD=xxxxxxxx!      
psql -h pgm-bp1z26gbo3gx893a129310.pg.rds.aliyuncs.com -p 1433 -U user123 db1      
MySQL:
mysql -h rm-bp1wv992ym962k85888370.mysql.rds.aliyuncs.com -P 3306 -u user123 --password=xxxxxx! -D db1

yum install -y mysql-*      
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm      
yum install -y postgresql12

CREATE TABLE employees (      
id INT NOT NULL,      
  fname VARCHAR(30),      
  lname VARCHAR(30),      
  birth TIMESTAMP,      
  hired DATE NOT NULL DEFAULT '1970-01-01',      
  separated DATE NOT NULL DEFAULT '9999-12-31',      
  job_code INT NOT NULL,      
  store_id INT NOT NULL
);

DROP PROCEDURE IF EXISTS BatchInsert;      


delimiter    -- 把界定符改成双斜杠      
CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)  -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数      
  BEGIN      
DECLARE Var INT;      
DECLARE ID INT;      
      SET Var = 0;      
      SET ID = init;      
WHILE Var < loop_time DO
          insert into employees      
          (id, fname, lname, birth, hired, separated, job_code, store_id)       
          values       
          (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID);      
          SET ID = ID + 1;      
          SET Var = Var + 1;      
      END WHILE;      
  END;      
//      
delimiter ;  -- 界定符改回分号

mysql> select count(distinct id) from employees ;      
+--------------------+      
| count(distinct id) |      
+--------------------+      
|             200000 |      
+--------------------+      
1 row in set (16.67 sec)

mysql> select count(*) from (select id from employees group by id) t;      
+----------+      
| count(*) |      
+----------+      
|   200000 |      
+----------+      
1 row in set (15.52 sec)

begin;      
CALL BatchInsert(1, 2000000);      
commit;

CREATE TABLE employees1 (      
  id INT NOT NULL,      
  fname VARCHAR(30),      
  lname VARCHAR(30),      
  birth TIMESTAMP,      
  hired DATE NOT NULL DEFAULT '1970-01-01',      
  separated DATE NOT NULL DEFAULT '9999-12-31',      
  job_code INT NOT NULL,      
  store_id INT NOT NULL
);      


DROP PROCEDURE IF EXISTS BatchInser1;      


delimiter    -- 把界定符改成双斜杠      
CREATE PROCEDURE BatchInsert1(IN init INT, IN loop_time INT)  -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数      
  BEGIN      
DECLARE Var INT;      
DECLARE ID INT;      
      SET Var = 0;      
      SET ID = init;      
WHILE Var < loop_time DO
          insert into employees1      
          (id, fname, lname, birth, hired, separated, job_code, store_id)       
          values       
          (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID);      
          SET ID = ID + 1;      
          SET Var = Var + 1;      
      END WHILE;      
  END;      
//      
delimiter ;  -- 界定符改回分号

-- 开启事务插入,否则会很慢      begin;      CALL BatchInsert1(1, 2000000);      commit;      Query OK, 1 row affected (1 min 7.06 sec)

select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10;

CREATE TABLE employees2 (      
id INT NOT NULL,      
fname VARCHAR(30),      
lname VARCHAR(30),      
birth TIMESTAMP,      
hired DATE NOT NULL DEFAULT '1970-01-01',      
separated DATE NOT NULL DEFAULT '9999-12-31',      
job_code INT NOT NULL,      
store_id INT NOT NULL      
);      


DROP PROCEDURE IF EXISTS BatchInser2;      


delimiter    -- 把界定符改成双斜杠      
CREATE PROCEDURE BatchInsert2(IN init INT, IN loop_time INT)  -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数      
BEGIN      
DECLARE Var INT;      
DECLARE ID INT;      
SET Var = 0;      
SET ID = init;      
WHILE Var < loop_time DO      
insert into employees2      
(id, fname, lname, birth, hired, separated, job_code, store_id)       
values       
(ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID);      
SET ID = ID + 1;      
SET Var = Var + 1;      
END WHILE;      
END;      
//      
delimiter ;  -- 界定符改回分号      


-- 开启事务插入,否则会很慢      


begin;      
CALL BatchInsert2(1, 2000000);      
commit;      


Query OK, 1 row affected (1 min 7.06 sec)

create index idx_employees2_1 on employees2(id);

DROP PROCEDURE IF EXISTS select1;      


delimiter    -- 把界定符改成双斜杠      
CREATE PROCEDURE select1(IN init INT, IN loop_time INT)  -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数      
BEGIN      
DECLARE Var INT;      
DECLARE ID1 INT;      
DECLARE vid INT;      
DECLARE vfname VARCHAR(30);      
DECLARE vlname VARCHAR(30);      
DECLARE vbirth TIMESTAMP;      
DECLARE vhired DATE;      
DECLARE vseparated DATE;      
DECLARE vjob_code INT;      
DECLARE vstore_id INT;      
SET Var = 0;      
SET ID1 = init;      
WHILE Var < loop_time DO      
select t.id,t.fname,t.lname,t.birth,t.hired,t.separated,t.job_code,t.store_id       
into       
vid,vfname,vlname,vbirth,vhired,vseparated,vjob_code,vstore_id      
from employees2 t       
where t.id=id1;      
SET ID1 = ID1 + 1;      
SET Var = Var + 1;      
END WHILE;      
END;      
//      
delimiter ;  -- 界定符改回分号

-- 开启事务查询      


begin;      
CALL select1(1, 2000000);      
commit;      




Query OK, 1 row affected (1 min 10.23 sec)

mysql> insert into employees select * from employees;    Query OK, 27600000 rows affected (4 min 38.62 sec)    Records: 27600000  Duplicates: 0  Warnings: 0    mysql> insert into employees select * from employees;    Query OK, 55200000 rows affected (11 min 13.40 sec)    Records: 55200000  Duplicates: 0  Warnings: 0    mysql> select count(*) from employees;    +-----------+    | count(*)  |    +-----------+    | 110400000 |    +-----------+    1 row in set (28.00 sec)    mysql> select count(distinct id) from employees ;      +--------------------+    | count(distinct id) |    +--------------------+    |            2000000 |    +--------------------+    1 row in set (1 min 17.73 sec)    mysql> select count(*) from (select id from employees group by id) t;      +----------+    | count(*) |    +----------+    |  2000000 |    +----------+    1 row in set (1 min 24.64 sec)

mysql> update employees set lname=lname||'new';  
Query OK, 110400000 rows affected, 65535 warnings (21 min 30.34 sec)  
Rows matched: 110400000  Changed: 110400000  Warnings: 220800000

select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10;

mysql> create index idx_employees_1 on employees(id);    
Query OK, 0 rows affected (3 min 49.04 sec)    
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE employees (      
id INT NOT NULL,      
  fname VARCHAR(30),      
  lname VARCHAR(30),      
  birth TIMESTAMP,      
  hired DATE NOT NULL DEFAULT '1970-01-01',      
  separated DATE NOT NULL DEFAULT '9999-12-31',      
  job_code INT NOT NULL,      
  store_id INT NOT NULL
);

\timing      


insert into employees      
    (id, fname, lname, birth, hired, separated, job_code, store_id)       
select
ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID
from generate_series(1,200000) id;      


INSERT 0 200000
Time: 355.652 ms

create or replace function BatchInsert(IN init INT, IN loop_time INT)  -- 第一个参数为初始ID号（可自定义），第二个位生成记录个数      
returns void as $$      
DECLARE
Var INT := 0;      
begin
for id in init..init+loop_time-1 loop
insert into employees      
    (id, fname, lname, birth, hired, separated, job_code, store_id)       
values
    (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID);      
end loop;      
end;      
$$ language plpgsql strict;      




db1=# select batchinsert(1,200000);      
 batchinsert       
-------------      


(1 row)      
Time: 1292.559 ms (00:01.293)

db1=> insert into employees select * from employees ;      
INSERT 0 400000      
Time: 322.335 ms      
db1=> insert into employees select * from employees ;      
INSERT 0 800000      
Time: 835.365 ms      
db1=> insert into employees select * from employees ;      
INSERT 0 1600000      
Time: 1622.475 ms (00:01.622)      
db1=> insert into employees select * from employees ;      
INSERT 0 3200000      
Time: 3583.787 ms (00:03.584)      
db1=> insert into employees select * from employees ;      
INSERT 0 6400000      
Time: 7277.764 ms (00:07.278)      
db1=> insert into employees select * from employees ;      
INSERT 0 12800000      
Time: 15639.482 ms (00:15.639)      
db1=> \dt+ employees       
List of relations      
Schema |   Name    | Type  |  Owner  |  Size   | Description       
--------+-----------+-------+---------+---------+-------------      
public | employees | table | user123 | 2061 MB |       
(1 row)

db1=> select count(*) from employees ;      
count         
----------      
25600000      
(1 row)      


Time: 604.982 ms

db1=> select count(*) from (select id from employees group by id) t;      
 count        
--------      
200000
(1 row)      


Time: 2982.907 ms (00:02.983)

insert into employees      
    (id, fname, lname, birth, hired, separated, job_code, store_id)       
select
ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID
from generate_series(1,2000000) id;

CREATE TABLE employees1 (      
id INT NOT NULL,      
  fname VARCHAR(30),      
  lname VARCHAR(30),      
  birth TIMESTAMP,      
  hired DATE NOT NULL DEFAULT '1970-01-01',      
  separated DATE NOT NULL DEFAULT '9999-12-31',      
  job_code INT NOT NULL,      
  store_id INT NOT NULL
);      




insert into employees1      
    (id, fname, lname, birth, hired, separated, job_code, store_id)       
select
ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID
from generate_series(1,2000000) id;      


INSERT 0 2000000
Time: 3037.777 ms (00:03.038)

select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10;      
     lname      | count       
----------------+-------      
 haixiang1      |   129      
 haixiang10     |   129      
 haixiang100    |   129      
 haixiang1000   |   129      
 haixiang10000  |   129      
 haixiang100000 |   129      
 haixiang100001 |   129      
 haixiang100002 |   129      
 haixiang100003 |   129      
 haixiang100004 |   129      
(10 rows)      


Time: 8897.907 ms (00:08.898)

create index idx_employees1_1 on employees1(id);      
CREATE INDEX
Time: 1436.346 ms (00:01.436)

do language plpgsql $$       
declare
begin
for i in 1..2000000 loop
    perform * from employees1 where id=i;      
end loop;      
end;      
$$;      


DO
Time: 9515.728 ms (00:09.516)      
db1=> select 9515.728/2000000;      
        ?column?              
------------------------      
 0.00475786400000000000      
(1 row)

db1=> INSERT INTO employees select * from employees;      INSERT 0 27600000      Time: 25050.665 ms (00:25.051)      db1=> INSERT INTO employees select * from employees;      INSERT 0 55200000      Time: 64726.430 ms (01:04.726)      db1=> select count(*) from employees;      count         -----------      110400000      (1 row)      Time: 7286.152 ms (00:07.286)      db1=> select count(distinct id) from employees;      count        ---------      2000000      (1 row)      Time: 39783.068 ms (00:39.783)      db1=> select count(*) from (select id from employees group by id) t;      count        ---------      2000000      (1 row)      Time: 14668.305 ms (00:14.668)      db1=> select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10;      lname      | count       ----------------+-------      haixiang1      |   516      haixiang10     |   516      haixiang100    |   516      haixiang1000   |   516      haixiang10000  |   516      haixiang100000 |   516      haixiang100001 |   516      haixiang100002 |   516      haixiang100003 |   516      haixiang100004 |   516      (10 rows)      Time: 33731.431 ms (00:33.731)

db1=> update employees set lname=lname||'new';  
UPDATE 110400000  
Time: 385372.063 ms (06:25.372)

db1=> create index idx_employees_1 on employees(id);    
CREATE INDEX    
Time: 70450.491 ms (01:10.450)