环境:
mysql5.6.25
现在我有三张表:
services_log(日志表,大约10万条数据),customer(客户表),sys_dict(字典表)
需要显示日志信息、客户名、字典值
原来sql:
[quote]受影响的行: 0
时间: 1.612s
[/quote]
分析发现联表查询导致查询时间变长。
更改如下:
[quote]受影响的行: 0
时间: 0.451s[/quote]
如果需要加入查询条件:
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
[quote]受影响的行: 0
时间: 1.612s
[/quote]
分析发现联表查询导致查询时间变长。
更改如下:
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
[quote]受影响的行: 0
时间: 0.451s[/quote]
如果需要加入查询条件:
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功底较差,不知有没有其他优化方式?