mysql语句问题

Question

insert、update、select、count、max，

sql语句里面，以上那个比较快呢？

Answer 1

。。。。没有可比性啊

Answer 2

为什么聚合要和增查改一起比较呢

Answer 3

有可比性吗？

Answer 4

很有意思的问题，一直以来，我们都知道：

• 查询： 主键查询最快，索引查询次之，where条件中有函数再次之；扫描行数越少越快。

• 插入：根据索引数量，索引越多插入越慢。插入行数越多越慢，因为会导致重建索引。

• 删除：行数越多越慢，索引数量越多越慢，因为会导致重建索引

• 更新：同删除，因为索引列数据改变会导致重建索引

但是以上这些，都是我们从教科书中得到的经验。在座的有几个真正去测试过这些经验呢？

我们来做个试验，下面是对1条数据做的增删改查统计测试

sql> INSERT INTO articles(id, title, body) VALUES (1, 'title', 'body')
[2017-02-11 19:54:53] 1 row affected in 9ms

sql> SELECT * FROM articles WHERE id = 1
[2017-02-11 19:54:56] 1 row retrieved starting from 1 in 14ms 
(execution: 7ms, fetching: 7ms)

sql> UPDATE articles SET title = 'new_title'
[2017-02-11 19:54:59] 1 row affected in 13ms

sql> DELETE FROM articles WHERE id = 1
[2017-02-11 19:55:01] 1 row affected in 13ms

sql> SELECT COUNT(*) as count FROM articles WHERE id = 1
[2017-02-11 19:57:21] 1 row retrieved starting from 1 in 18ms (execution: 12ms, fetching: 6ms)

sql> SELECT max(id) as count FROM articles WHERE id = 1
[2017-02-11 19:57:49] 1 row retrieved starting from 1 in 30ms (execution: 9ms, fetching: 21ms)

• 插入：9 ms

• 查询：7 ms

• 更新：13 ms

• 删除：13 ms

• 统计：9、12 ms

因为数据量小。所以几乎看不出实质差别，且测试结果可能因为主机繁忙程度而产生误差。

所以我们需要用更多的数据，才能得出实实在在的无可辩驳的数据。

下面，我们用1W条数据来测试

Answer 5

drop table if exists t_atz;

create table t_atz(
id serial not null,
num int not null,
num_x int not null,
title varchar(255) not null,
title_x varchar(255) not null,
content text not null,
primary key(id)
);
create index ix_atz_num on t_atz(num_x);
create index ix_atz_title on t_atz(title_x);


drop function if exists fun_save_atz(int);

create or replace function fun_save_atz(integer) returns integer as 
$$
declare
loop_count alias for $1;
begin
while loop_count>0 loop
insert into t_atz(num, num_x, title, title_x, content) values (loop_count, loop_count, md5(loop_count::varchar), md5(loop_count::varchar), 
(select string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') from   generate_series(1, 45))
);
loop_count :=loop_count-1;
end loop;
return loop_count;
end;
$$ 
language plpgsql;

select fun_save_atz(1000000);
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 110.89, 構造輸出: 110.89

insert into t_atz(num, num_x, title, title_x, content) values ( (random()*100000)::int,  (random()*100000)::int, 'Test', 'Test_x', md5(random()::varchar));
-- 插入了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.014, 構造輸出: 0.014
-- 插入了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.005, 構造輸出: 0.005
-- 插入了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.011, 構造輸出: 0.011
-- 插入了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.009, 構造輸出: 0.009
-- 插入了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.02, 構造輸出: 0.02

select * from t_atz where id=945621;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0, 構造輸出: 0
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0

select * from t_atz where num=568741;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.091, 構造輸出: 0.091
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.086, 構造輸出: 0.085
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.085, 構造輸出: 0.085
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.085, 構造輸出: 0.084
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.086, 構造輸出: 0.085

select * from t_atz where num_x=568741;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0, 構造輸出: 0

update t_atz set title=md5(random()::varchar) where id=654684;
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.009, 構造輸出: 0.009
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.005, 構造輸出: 0.005
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.01, 構造輸出: 0.01
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.018, 構造輸出: 0.018

update t_atz set title_x=md5(random()::varchar) where id=654684;
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.017, 構造輸出: 0.017
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.012, 構造輸出: 0.012
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.008, 構造輸出: 0.008
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.009, 構造輸出: 0.009

update t_atz set content=md5(random()::varchar) where id=635546;
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.01, 構造輸出: 0.01
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.012, 構造輸出: 0.012
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.028, 構造輸出: 0.028
-- 更新了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.007, 構造輸出: 0.007

delete from t_atz where  id=585458;
-- 刪除了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.031, 構造輸出: 0.031
-- 刪除了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.015, 構造輸出: 0.015
-- 刪除了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.009, 構造輸出: 0.009
-- 刪除了 1 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 0.016, 構造輸出: 0.016

select count(*) from t_atz;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.08, 構造輸出: 0.079
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.081, 構造輸出: 0.081
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.083, 構造輸出: 0.083
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.079, 構造輸出: 0.079

select count(1) from t_atz;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.099, 構造輸出: 0.099
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.09, 構造輸出: 0.09
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.107, 構造輸出: 0.106
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.088, 構造輸出: 0.088

select max(id) from t_atz;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001

select max(num) from t_atz;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.097, 構造輸出: 0.097
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.098, 構造輸出: 0.097
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.102, 構造輸出: 0.102
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.098, 構造輸出: 0.098

select max(num_x) from t_atz;
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 0.001, 構造輸出: 0.001

update t_atz set content='last';
-- 更新了 1,000,016 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 94.176, 構造輸出: 94.176
-- 更新了 1,000,016 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 83.466, 構造輸出: 83.466
-- 更新了 1,000,016 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 212.706, 構造輸出: 212.706

update t_atz set title_x='last';
-- 更新了 1,000,016 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 13.46, 構造輸出: 13.46
-- 更新了 1,000,016 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 18.434, 構造輸出: 18.434

delete from t_atz;
-- 刪除了 1,000,016 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 2.109, 構造輸出: 2.109

select fun_save_atz(1000000);
-- 查詢 1/1 用時(秒) - 共計: 1, SQL 查詢: 141.26, 構造輸出: 141.26

delete from t_atz;
-- 刪除了 1,000,000 行
-- 查詢 1/1 用時(秒) - 共計: 0, SQL 查詢: 5.197, 構造輸出: 5.197