数据较多 , 而需要联表查询时 , 查询缓慢 --解决
程序员文章站
2022-06-15 19:22:44
...
环境:
mysql5.6.25
现在我有三张表:
services_log(日志表,大约10万条数据),customer(客户表),sys_dict(字典表)
需要显示日志信息、客户名、字典值
原来sql:
分析发现联表查询导致查询时间变长。
更改如下:
如果需要加入查询条件:
SQL功底较差,不知有没有其他优化方式?
mysql5.6.25
现在我有三张表:
services_log(日志表,大约10万条数据),customer(客户表),sys_dict(字典表)
需要显示日志信息、客户名、字典值
原来sql:
SELECT a.id AS "id", a.autorkey AS "autorkey", a.remote_addr AS "remoteAddr", a.service_name AS "serviceName", a.params AS "params", a.create_date AS "createDate", a.remarks AS "remarks", c.hotel_name AS hotelName, b.label AS servicesLabelName FROM services_log a JOIN sys_dict b ON a.service_name = b.value JOIN customer c ON a.autorkey = c.author_key ORDER BY a.create_date DESC LIMIT 30
引用
受影响的行: 0
时间: 1.612s
时间: 1.612s
分析发现联表查询导致查询时间变长。
更改如下:
SELECT a.id AS "id", a.autorkey AS "autorkey", a.remote_addr AS "remoteAddr", a.service_name AS "serviceName", a.params AS "params", a.create_date AS "createDate", a.remarks AS "remarks", (select c.hotel_name from customer c where a.autorkey = c.author_key) as hotelName, (select b.label from sys_dict b where a.service_name = b.value) as servicesLabelName FROM services_log a ORDER BY a.create_date DESC LIMIT 30
引用
受影响的行: 0
时间: 0.451s
时间: 0.451s
如果需要加入查询条件:
SELECT a.id AS "id", a.autorkey AS "autorkey", a.remote_addr AS "remoteAddr", a.service_name AS "serviceName", a.params AS "params", a.create_date AS "createDate", a.remarks AS "remarks", (select c.hotel_name from customer c where a.autorkey = c.author_key) as hotelName, (select b.label from sys_dict b where a.service_name = b.value) as servicesLabelName FROM services_log a WHERE a.autorkey in (select id from customer where hotel_name like '%长青国际酒店%' ) ORDER BY a.create_date DESC LIMIT 30
SQL功底较差,不知有没有其他优化方式?