mysql常用sql语句执行时间记录(sql优化)

1、查询单表

数据量：3200000条

1.1、数量查询

select count(1)
from users
--耗时：12.997s

select count(*)
from users
--耗时：12.449s

select count(id)
from users
--id是主键
--耗时：12.414s

select count(`password`)
from users
--password，字符型，普通字段无索引
--耗时：4.869s

select count(`status`)
from users
--status，整形，普通字段无索引
--耗时：5.042s

select count(`status`)
from users
--status，整形，有索引
--耗时：1.604s

1.2、分页查询

select *
from users
limit 0,10
--耗时：0.148s

select *
from users
limit 320000,10
--数据量的10%
--耗时：0.648s

select *
from users
limit 640000,10
--数据量的20%
--耗时：1.205s
--大约是10%的1.8倍左右

select *
from users
limit 1600000,10
--数据量的50%
--耗时：2.711s
--大约是10%的18.3倍左右

select *
from users
limit 3199990,10
--数据量的100%
--耗时：5.006s
--大约是10%的33.8倍左右

select *
from users
limit 499999,10
--耗时：0.923s
--50万数据出现1s以上的耗时

select id,user_name,`password`,`status`,create_time
from users
limit 3199990,10
--耗时：5.410s
--查询具体的字段，'*'由具体字段名替换

select *
from users
where id > 500000
limit 10
--耗时：0.142s

select *
from users
where id > 320000
limit 10
--耗时：0.035s

select *
from users
where id > 640000
limit 10
--耗时：0.044s

select *
from users
where id > 3199990
limit 10
--耗时：0.030s

1.3、模糊查询

select *
from users
where user_name like '%萤脂%'
--耗时7.157s
--实际数据2条

select *
from users
where match(user_name) against('萤脂')
--耗时：0.193s
（建立全文索引，设置ngram全文解析器，支持版本：>=5.6）
(alter table users add fulltext index name_fulltext(user_name) WITH PARSER NGRAM;)

2、多表查询（小表驱动大表）

users:3200000数据
user_info:3200000数据
mall_order:1600000数据

2.1、多表连接

** mall_order的user_id未建索引前：**

select m.id,n.age,n.name,count(1) orderNum
from users m
left join user_info n
on m.id = n.id
left join mall_order mo
on mo.user_id = n.id
where m.id > 3000000
group by n.id
limit 500
--耗时：2.878s

select m.id,n.age,n.name,count(1) orderNum
from users m
left join user_info n
on m.id = n.id
left join mall_order mo
on mo.user_id = n.id
where m.id > 3000000 and mo.user_id > 3000000
group by n.id
limit 500
--耗时：9.048s

mall_order的user_id建索引后：

select m.id,n.age,n.name,count(1) orderNum
from users m
left join user_info n
on m.id = n.id
left join mall_order mo
on mo.user_id = n.id
where m.id > 3000000
group by n.id
limit 500
--耗时：1.492s

select m.id,n.age,n.name,count(1) orderNum
from users m
left join user_info n
on m.id = n.id
left join mall_order mo
on mo.user_id = n.id
where m.id > 3000000 and mo.user_id > 3000000
group by n.id
limit 500
--耗时：0.765s

select m.id,ui.age,ui.name,ui.orderNum
from users m,
(select id,age,name,orderNum
from user_info n,(select user_id,count(1) orderNum from mall_order where user_id > 3000000 GROUP BY user_id) mo
where n.id > 3000000 and mo.user_id = n.id
limit 500
) ui
where m.id = ui.id and m.id > 3000000
limit 500
--耗时：0.099s