代码之家 › 专栏 › 技术社区 › Haris Khan

如何通过多连接获取大量数据

sql mysql php

-2

Haris Khan · 技术社区 · 6 年前

我要从12张桌子上取超过20k的记录。我正在使用Datatables jquery插件进行服务器端分页。每次我重新加载页面或跳转到另一个页面需要25秒,这太奇怪了。我的问题是:

SELECT 
    * 
FROM 
    `bp` 
left join 
    pdbp on bp.id = pdbp.bp_id 
left join 
    cities on pdbp.city = cities.CityId 
left join 
    regions on pdbp.region = regions.RegionId 
left join 
    countries on pdbp.country = countries.CountryId  
left join (
     SELECT 
         bid, 
         GROUP_CONCAT(subc_name SEPARATOR ', ') as cat 
     FROM 
         sub_categories sc 
     left join 
         `bp_categories` bc on sc.subc_cod = bc.subc_cod group by bid
     ) categories on bp.id = categories.bid 
left join (
     SELECT 
         bid as mbid, 
         GROUP_CONCAT(material SEPARATOR ', ') as mat 
     FROM 
         product_material pm 
     left join 
         `bp_materials` bm on pm.material_cod = bm.mat_cod group by bid 
) materials on bp.id = materials.mbid
left join (
    SELECT 
        bid as cbid, 
        GROUP_CONCAT(description SEPARATOR ', ') as crts from cert ct 
    left join 
        `bp_certificate` bct on ct.id = bct.cid group by cbid
    ) certificate on bp.id = certificate.cbid

我真的需要让它快,它正在消耗我。任何帮助都将不胜感激

3 回复 | 直到 6 年前

Mefenamic 6 年前

(1) 使用视图表。

CREATE 
    VIEW `sample_view` 
    AS
(
SELECT * FROM `bp` LEFT JOIN pdbp ON bp.id = pdbp.bp_id 
LEFT JOIN cities ON pdbp.city = cities.CityId 
LEFT JOIN regions ON pdbp.region = regions.RegionId 
LEFT JOIN countries ON pdbp.country = countries.CountryId  
LEFT JOIN(SELECT bid, GROUP_CONCAT(subc_name SEPARATOR ', ') AS cat FROM sub_categories sc 
LEFT JOIN `bp_categories` bc ON sc.subc_cod = bc.subc_cod GROUP BY bid) categories ON bp.id = categories.bid 
LEFT JOIN (SELECT bid AS mbid, GROUP_CONCAT(material SEPARATOR ', ') AS mat FROM product_material pm 
LEFT JOIN `bp_materials` bm ON pm.material_cod = bm.mat_cod GROUP BY bid ) materials ON bp.id = materials.mbid
LEFT JOIN (SELECT bid AS cbid, GROUP_CONCAT(description SEPARATOR ', ') AS crts FROM cert ct 
LEFT JOIN `bp_certificate` bct ON ct.id = bct.cid GROUP BY cbid ) certificate ON bp.id = certificate.cbid  
);

试着在你身边跑。然后获取具有分页限制的视图表。对于前端,执行时间有限,超过1000条记录将耗尽内存。

select * from sample_view limit 0, 20

(2) 在SELECT子句中避免不必要的列。在SELECT子句中指定列,而不是使用SELECT*。不必要的列会给数据库增加额外的负载,这不仅会降低单个SQL的速度,还会降低整个系统的速度。

(3) 嵌套查询会降低性能。尝试将所有嵌套查询直接左连接到主sql查询。你没有告诉我们你的数据库图表,所以我不能提供你的sql语句。

(4) 为JOIN、WHERE、ORDER BY和GROUP BY子句中的所有谓词编制索引在很大程度上依赖索引来提高SQL性能和可伸缩性。使用

SELECT EXPLAIN

优化查询。检查此链接以了解解释功能

https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

(5) 如果需要排序结果集,则ORDERBY子句在SQL中是必需的。请注意添加ORDERBY子句对性能的影响,因为数据库需要对结果集进行排序,这会导致SQL执行中最昂贵的操作之一。

San K 6 年前

是的,有很多方法

用户服务器端对数据表进行分页并获取大约10到20条记录

如果它已被禁用,因为这只会减少要显示的数据量。除此之外,用于加载数据的方法通常会对性能产生最显著的影响。

Client-side processing - DOM sourced data: ~5'000 rows. Speed options: orderClasses.

Client-side processing - Ajax sourced data (ajax): ~50'000 rows. Speed options: deferRender

Server-side processing (serverSide): millions of rows.

Rafael 6 年前

有几件事会影响查询的性能。

例如,我相信如果您使用DataTables插件,您必须确切地知道要输出哪些字段。

将适当的字段添加到 SELECT 而不是 *
尽可能考虑使用内部连接
使用limit和offset从后端对其分页
缓存结果。。。