mysql - 查询 firebird slow order by/distinct

Question

我对 Firebird 中的查询速度有疑问。慢在排序和区分。

如果我在 MySQL 中尝试查询，那么他会快一秒。

Firebird -> 1,3s a 1,6sMySQL -> 0,3s 到 0,4s

我们在网络服务器/网站上使用 Firebird 数据库，因此速度很重要。

规范:- Firebird 2.5.1 或 2.5.2 (SuperClassic) 64 位- 2,13 Ghz(2 个处理器)- 内存 4,00 GB

我能做什么？

我有以下表格:

============================================= =====

CREATE TABLE ARTICLE3_1
(
  IDARTICLE Integer NOT NULL,
  ITEMSTATUS Integer,
  ITEMENTRYDATE Integer,
  ITEMFILTER Integer,
  ARTIKELNUMMER Varchar(250),
  ARTIKELNAAM1 Varchar(250),
  ARTIKELNAAM2 Varchar(250),
  OMSCHRIJVING_DETAIL Blob sub_type 1,
  OMSCHRIJVING1 Varchar(250),
  OMSCHRIJVING2 Varchar(250),
  ARTIKELNR_LEVERANCIER Varchar(250),
  MERK Varchar(250),
  LEVERANCIER Varchar(250),
  EAN Varchar(250),
  LINKAANGROEP Varchar(250),
  LINKAANAANBIEDINGGROEP Varchar(250),
  LINKAANPOPULAIRGROEP Varchar(250),
  LINKAANART Varchar(250),
  ARTGRPNR Varchar(250),
  SUBGROEP Varchar(250),
  PRIJSPER Integer,
  VERKOOPPRIJS Float,
  ADVIESPRIJS Float,
  BTWPERC Float,
  ONLINE Varchar(250),
  TUSGROEPBIJLINK Varchar(250),
  AFBEELDINGKLEIN Varchar(250),
  AFBEELDINGMIDDEL Varchar(250),
  AFBEELDINGGROOT Varchar(250),
  ICECATLINK Varchar(250),
  LINKAANHOMEPAGEGROEP Varchar(250),
  LINKAANMIJNACCOUNTGROEP Varchar(250),
  SORTEER Varchar(250),
  AFBEELDING Varchar(100),
  FLASH Blob sub_type 1,
  EENHEID Varchar(250),
  ALTARTNR1 Varchar(250),
  ALTARTNR2 Varchar(250),
  BESTELLENPER Float,
  INFEED Varchar(250),
  GOOGLE_TAXONOMIE Varchar(250),
  FEED_TITEL Varchar(250),
  FEED_OMSCHRIJVING Blob sub_type 1,
  PRIMARY KEY (IDARTICLE)
);
CREATE INDEX IDX_ARTICLE3_1_2 ON ARTICLE3_1 (MERK);
CREATE INDEX IDX_ARTICLE3_1_3 ON ARTICLE3_1 (ARTIKELNUMMER);
CREATE INDEX IDX_ARTICLE3_1_4 ON ARTICLE3_1 (ARTIKELNR_LEVERANCIER);
CREATE INDEX IDX_ARTICLE3_1_5 ON ARTICLE3_1 (ALTARTNR2);
CREATE INDEX IDX_ARTICLE3_1_6 ON ARTICLE3_1 (ARTIKELNAAM1);
CREATE INDEX IDX_ARTICLE3_1_7 ON ARTICLE3_1 (EAN);

   CREATE TABLE TREE3
(
  IDLINK Integer NOT NULL,
  LINKTYPE Integer,
  IDITEM Integer,
  ITEMTYPE Integer,
  IDTARGETLINK Integer,
  NODEPOSITION Integer,
  NODELEVEL Integer,
  IDLAYOUTDATA Integer,
  IDTEMPLATE Integer,
  ACTIONDATE Integer,
  MARKET1 Integer,
  PRIMARY KEY (IDLINK)
);
CREATE INDEX IDX_TREE3_2 ON TREE3 (IDITEM);
CREATE INDEX IDX_TREE3_3 ON TREE3 (MARKET1);
CREATE INDEX ITREE13 ON TREE3 (IDTARGETLINK,NODEPOSITION);
CREATE INDEX ITREE53 ON TREE3 (IDITEM,ITEMTYPE);

====================================================

FireBird 中的查询:

SELECT FIRST 30 SKIP 0 distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem
WHERE tr.ItemType = 2 AND tr.Market1 = 1
AND  ((art.IDARTICLE > 0) AND (  (LOWER(art.Artikelnummer) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnummer) like 'a4' )
AND (LOWER(art.Artikelnummer) like 'papier'))  OR  (LOWER(art.Artikelnaam1) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam1) like '%a4%' )
AND (LOWER(art.Artikelnaam1) like '%papier%'))  OR  (LOWER(art.Artikelnaam2) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam2) like '%a4%' )
AND (LOWER(art.Artikelnaam2) like '%papier%'))  OR  (LOWER(art.Artikelnr_leverancier) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnr_leverancier) like '%a4%' )
AND (LOWER(art.Artikelnr_leverancier) like '%papier%'))  OR  (LOWER(art.Merk) like '%a4 papier%' ) OR ( (LOWER(art.Merk) like '%a4%' )
AND (LOWER(art.Merk) like '%papier%'))  OR  (LOWER(art.EAN) like '%a4 papier%' ) OR ( (LOWER(art.EAN) like '%a4%' )
AND (LOWER(art.EAN) like '%papier%'))  OR  (LOWER(art.AltArtnr1) like '%a4 papier%' ) OR ( (LOWER(art.AltArtnr1) like '%a4%' )
AND (LOWER(art.AltArtnr1) like '%papier%'))  OR  (LOWER(art.AltArtnr2) like '%a4 papier%' ) OR ( (LOWER(art.AltArtnr2) like '%a4%' )
AND (LOWER(art.AltArtnr2) like '%papier%')) ))
AND tr.NODELEVEL =5  and tr.LINKTYPE <> 5
ORDER BY tr.NodePosition

MySQL 中的查询:

SELECT  distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem
WHERE tr.ItemType = 2 AND tr.Market1 = 1
AND  ((art.IDARTICLE > 0) AND (  (LCASE(art.Artikelnummer) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnummer) like 'a4' )
AND (LCASE(art.Artikelnummer) like 'papier'))  OR  (LCASE(art.Artikelnaam1) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam1) like '%a4%' )
AND (LCASE(art.Artikelnaam1) like '%papier%'))  OR  (LCASE(art.Artikelnaam2) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam2) like '%a4%' )
AND (LCASE(art.Artikelnaam2) like '%papier%'))  OR  (LCASE(art.Artikelnr_leverancier) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnr_leverancier) like '%a4%' )
AND (LCASE(art.Artikelnr_leverancier) like '%papier%'))  OR  (LCASE(art.Merk) like '%a4 papier%' ) OR ( (LCASE(art.Merk) like '%a4%' )
AND (LCASE(art.Merk) like '%papier%'))  OR  (LCASE(art.EAN) like '%a4 papier%' ) OR ( (LCASE(art.EAN) like '%a4%' )
AND (LCASE(art.EAN) like '%papier%'))  OR  (LCASE(art.AltArtnr1) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr1) like '%a4%' )
AND (LCASE(art.AltArtnr1) like '%papier%'))  OR  (LCASE(art.AltArtnr2) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr2) like '%a4%' )
AND (LCASE(art.AltArtnr2) like '%papier%')) ))
AND tr.NODELEVEL =5  and tr.LINKTYPE <> 5
ORDER BY tr.NodePosition LIMIT 30;

============================================= =====

我用 FlameRobin 执行了查询:

> Prepare time: 0.016s Field #01: TREE3.IDLINK Alias:IDLINK Type:INTEGER
> Field #02: TREE3.IDTARGETLINK Alias:IDTARGETLINK Type:INTEGER Field
> #03: TREE3.IDITEM Alias:IDITEM Type:INTEGER Field #04: TREE3.NODEPOSITION Alias:NODEPOSITION Type:INTEGER PLAN SORT (SORT
> (JOIN (TR INDEX (IDX_TREE3_2, IDX_TREE3_3), ART INDEX
> (RDB$PRIMARY2))))
> 
> 873424 fetches, 0 marks, 12892 reads, 0 writes. 0 inserts, 0 updates,
> 0 deletes, 380580 index, 0 seq. Delta memory: 1784 bytes. Total
> execution time: 1.311s

谢谢!

Answer 1

Yip 尽可能避免使用 DISTINCT 和 LIKE，不同的优化 http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

尝试使用 group by 而不是 distinct 的嵌套查询。我用它来解决使用 group by 和 order by 时的问题。

select * from ({the rest of the query}) as some_table group by {my distinct column};

我也看不到你的表引擎，但 MyIsam 更适合全文搜索(而不是 InnoDB)。也可能值得查看 Solr 以进行全文搜索。设置有点学习曲线，但您可以索引 mysql 表，然后跨多个列执行部分匹配搜索。有了提升和令人敬畏之类的东西。

看看下面的查询是否有任何性能优势。

select * from (SELECT tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem
WHERE tr.ItemType = 2 AND tr.Market1 = 1
AND  ((art.IDARTICLE > 0) AND (  (LCASE(art.Artikelnummer) like '%a4 papier%' ) OR (
(LCASE(art.Artikelnummer) like 'a4' )
AND (LCASE(art.Artikelnummer) like 'papier'))  OR  (LCASE(art.Artikelnaam1) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam1) like '%a4%' )
AND (LCASE(art.Artikelnaam1) like '%papier%'))  OR  (LCASE(art.Artikelnaam2) like '%a4 papier%' ) OR ( (LCASE(art.Artikelnaam2) like '%a4%' )
AND (LCASE(art.Artikelnaam2) like '%papier%'))  OR  (LCASE(art.Artikelnr_leverancier)
like '%a4 papier%' ) OR ( (LCASE(art.Artikelnr_leverancier) like '%a4%' )
AND (LCASE(art.Artikelnr_leverancier) like '%papier%'))  OR  (LCASE(art.Merk) like '%a4 papier%' ) OR ( (LCASE(art.Merk) like '%a4%' )
AND (LCASE(art.Merk) like '%papier%'))  OR  (LCASE(art.EAN) like '%a4 papier%' ) OR (
(LCASE(art.EAN) like '%a4%' )
AND (LCASE(art.EAN) like '%papier%'))  OR  (LCASE(art.AltArtnr1) like '%a4 papier%' ) OR    
( (LCASE(art.AltArtnr1) like '%a4%' )
AND (LCASE(art.AltArtnr1) like '%papier%'))  OR  (LCASE(art.AltArtnr2) like '%a4 papier%' ) OR ( (LCASE(art.AltArtnr2) like '%a4%' )
AND (LCASE(art.AltArtnr2) like '%papier%')) ))
AND tr.NODELEVEL =5  and tr.LINKTYPE <> 5
ORDER BY tr.NodePosition LIMIT 30)
as some_table group by IdLink;