mysql的语句啰嗦_【MySQL求优化指导】几十万条记录就查询了一分多钟

1、问题说得有点啰嗦，但我希望把问题前因后果阐述清楚，Come on~,其实第一条就是问题，执行语句及其结果如下，请问有什么优化操作呢：sql> SELECT alarm.name as 'name', `type`, count(`type`) as 'count'

from (select driver.name from driver left join city on driver.city_id = city.adcode where city.name like concat('%','成都', '%')) as driver,

(select alarm.id, alarm.driver, alarm.type, alarm.name from alarm where alarm.createTime > '2020-06-13 00:00:00' and alarm.createTime < '2020-07-13 23:59:59'

order by createTime

)alarm

WHERE driver.name=alarm.name

group by alarm.name, type

[2020-07-14 10:52:55] 14 rows retrieved starting from 1 in 1 m 36 s 66 ms (execution: 1 m 36 s 54 ms, fetching: 12 ms)

耗时 1 m 36 s 66 ms(execution: 1 m 36 s 54 ms, fetching: 12 ms)，导致网页老是提示请求超时....

sql的descripbe如下：

2、接下来，我们先看一下表结构：---1、alarm报警表---

create table alarm

(

id int auto_increment primary key,

driver int null comment '司机id',

type varchar(8) null comment '行为类别',

carNumber varchar(10) null,

deviceCode varchar(10) null,

createTime datetime null,

path varchar(50) null comment '行为图片地址',

name varchar(10) null comment '司机名字',

telephone varchar(12) null,

alarmVideo varchar(100) null comment '行为视频，5s',

videoId varchar(50) null comment '由设备号和视频的id组成'

) comment '记录表';

create index alarm_time__index on alarm (createTime);

---2、driver司机表---

create table driver

(

id int auto_increment primary key,

name varchar(5) not null comment '司机名字',

telephone varchar(12) null comment '司机电话',

age int null comment '司机年龄',

avatar char(50) not null comment '头像',

staffNumber varchar(10) not null comment '工号',

driverDuration int null comment '驾龄',

route varchar(30) null comment '司机驾驶的路线',

city_id int not null comment '司机所在城市,城市编号',

constraint telephone_UNIQUE unique (telephone)

) comment '司机表';

---3、城市表---

create table city

(

name varchar(45) not null comment '城市名称' primary key,

adcode int not null comment '城市编号',

spell varchar(45) not null comment '城市拼音',

constraint name_UNIQUE unique (name)

) comment '城市以及城市编号列表';

3、简单的select * from alarm响应时间约execution: 93 ms, fetching: 198 ms,共231046条记录。

4、网上看分页优化，条件字段建索引、做个排序等，性能也很差，执行语句以及耗时如下：sql> select * from alarm

where createTime >'2020-06-13 00:00:00' and alarm.createTime < '2020-07-13 23:59:59'

group by createTime

order by createTime

[2020-07-14 11:39:06] 500 rows retrieved starting from 1 in 1 m 37 s 199 ms (execution: 1 m 35 s 672 ms, fetching: 1 s 527 ms)select * from alarm

where alarm.createTime in (

select createTime from alarm

where createTime >'2020-06-13 00:00:00' and alarm.createTime < '2020-07-13 23:59:59'

group by createTime

order by createTime)

[2020-07-14 11:32:34] 500 rows retrieved starting from 1 in 34 s 503 ms (execution: 34 s 375 ms, fetching: 128 ms)

5、最后一点说明： 数据库运行在本机虚拟机上，2GB内存，2核，20GB硬盘，简单查询的结果应该以及排除了服务器故障以及性能吧？

6、提问：所以，求大佬指教，1中的查询语句我该怎么优化呢？表设计、sql语句优化方面都说嘛，不胜感激！