在复杂表上加速查询(~ 100万行)

Question

首先感谢您花时间阅读本文。

我正在开发一个PHP web应用程序，它处理以下数据，因为现在有明显的时间当试图检索数据时，特别是当钻取选项。目前主要的选项有:车型、品牌、型号、年份、互联网价格和里程。最终会有更多的列被使用。此列表中的其他列用于显示整个页面的数据，因为在一个实例中显示10条记录。

表结构:

<>之前创建表"vehicles"' id ' int(12) NOT NULL;' DealerID ' int(6)默认NULL，' VIN ' varchar(17)默认为NULL，' StockNumber ' varchar(10)默认为NULL，'状态' varchar(1)默认为NULL，'车辆类型' int(1)默认NULL，' Year ' int(4) DEFAULT NULL，' Make ' varchar(13)默认为NULL' Model ' varchar(24)默认为NULL，' Trim ' varchar(35)默认为NULL，' Body ' varchar(25)默认为NULL，varchar(50)默认为NULL，varchar(6)默认为NULL，'里程' int(6) DEFAULT NULL，' Transmission ' varchar(24) DEFAULT NULL，' EngineDisplacement ' varchar(7)默认为NULL，' engineesize ' varchar(15)默认为NULL，' Induction ' varchar(25)默认为NULL，' DriveTrain ' varchar(3)默认为NULL，'燃料类型' varchar(9)默认为NULL，' FuelEconomyCity ' int(2) DEFAULT NULL，' FuelEconomyHighway ' int(2) DEFAULT NULL，' FuelEconomyCombined ' int(1) DEFAULT NULL，' Doors ' int(1) DEFAULT NULL，' oemcolorcodeexternal ' varchar(10)默认NULL，' OEMColorCodeInterior ' varchar(10)默认NULL，' oemcolornameexternal ' varchar(49)默认NULL，' OEMColorNameInterior ' varchar(10)默认为NULL，' genericcoloreextor ' varchar(35)默认为NULL，' GenericColorInterior ' varchar(38)默认为NULL，' InternetPrice ' int(6) DEFAULT NULL，' comparonprice ' int(6) DEFAULT NULL，' WholeSalePrice ' varchar(10) DEFAULT NULL，' MSRP ' varchar(10)默认为NULL，' InternetSpecial ' varchar(1)默认为NULL' OemModelCode ' varchar(12)默认为NULL，' HasWarranty ' varchar(1)默认为NULL' CertificationWarranty ' int(3) DEFAULT NULL，' warantymonth ' int(1) DEFAULT NULL，' warantymiles ' int(1) DEFAULT NULL，' CertificationNumber ' varchar(7) DEFAULT NULL，' ServiceContract ' varchar(1)默认为NULL，' InServiceDate ' varchar(19)默认为NULL，' CertificationDate ' varchar(19) DEFAULT NULL，' DateManufactured ' varchar(19) DEFAULT NULL，' DateCreated ' varchar(19)默认为NULL，' DateUpdated ' varchar(19)默认为NULL，' datermoved ' varchar(19)默认为NULL，' datephotosupdate ' varchar(19)默认为NULL，' Photos ' int(2) DEFAULT NULL，' SuperSizePhotos ' int(2) DEFAULT NULL，' AddendumDetails ' varchar(10)默认为NULL，' DepartmentComments ' varchar(239) DEFAULT NULL，varchar(1987)默认为NULL，' Options ' varchar(2264)默认为NULL，' purchasepaypayment ' decimal(5,2) DEFAULT NULL，' PurchaseDownPayment ' decimal(6,2) DEFAULT NULL，' PurchaseTerm ' int(2) DEFAULT NULL，' PurchaseDisclosure ' varchar(10) DEFAULT NULL，' PurchaseRate ' decimal(3,2) DEFAULT NULL，' leasepaypayment ' decimal(2,2) DEFAULT NULL，' LeaseDownPayment ' decimal(2,2) DEFAULT NULL，' LeaseTerm ' int(1) DEFAULT NULL，' LeaseDisclosure ' varchar(10) DEFAULT NULL，' LeaseRate ' decimal(2,2) DEFAULT NULL，' leaseresual ' decimal(2,2) DEFAULT NULL，' Reserved1 ' varchar(10) DEFAULT NULL，' Reserved2 ' varchar(10)默认为NULL，' reserve3 ' varchar(10)默认为NULL，' reserve4 ' varchar(10)默认为NULL，' reserve5 ' varchar(10)默认为NULL，' reserve6 ' varchar(10)默认为NULL，' sitecert ' int(11) DEFAULT NULL，主键(' id ')，键vin (vin)，键' Make ' (' Make ')，键' StockNumber ' (' StockNumber ')，键' Model ' (' Model ')，键' Trim ' (' Trim ')，键' Body ' (' Body ')，关键字"车辆类别"("车辆类别")，键"传输"("传输")，键' DealerID ' (' DealerID ')) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=46527428;

和一个示例查询:

SELECT *
FROM vehicles
WHERE Make = 'Audi'
    AND VehicleType = '0'
    AND Model = 'A4'
    AND InternetPrice BETWEEN 0 AND 999999
    AND Year BETWEEN 1983 AND 2015
    AND Mileage > 0
    AND DealerID IN ('AA'
        ,'156553'
        ,'229602'
        ,'160483'
        ,'2953'
        ,'161712'
        ,'228576'
        ,'228921'
        ,'7590'
        ,'20813'
        ,'158105'
        ,'160286'
        ,'164479'
        ,'164487'
        ,'182543'
        ,'158860'
        ,'186479'
        ,'227170'
        ,'226327'
        )
ORDER BY sitecert DESC
    ,InternetPrice DESC 
LIMIT 0, 10

有没有人有任何建议来优化这个查询或表?当然，整个页面中还有其他代码，但我已经将其计时到导致延迟的这个查询。

谢谢你的帮助!

更新:

MySQL解释:

id select_type表类型possible_keys key key_len ref rows额外1简单车辆refmake,Model,DealerID Model 75 const 1675 Using where;使用filesort 之前
Year被更新为INT

Answer 1

根据您的数据库，您应该在以下字段上添加索引:

Make
VehicleType
Model
InternetPrice
Year
Mileage
DealerId

Year应该是DATE类型，而不是text (INT也可以)。

Answer 2

我的建议:

通过make使用组
• 分区您的表，列可能会使用(make,Year)
• 将表存储在indb引擎中，而不是myisam自定义一些聚集索引来参与谓词!

Answer 3

对于这种情况，您必须阅读EXPLAIN结果并相应地调整索引。

然而，对于只有1600行排序，我根本不会麻烦。如果你还想让它更快，似乎你需要一个组合键，其中包括一个Model和一个sitecert，后面跟着一个price，两者的顺序都是相反的。

意味着您必须有两个额外的列，其中存储sitecert和price的负值。然后在(Model,nsitecert, nInternetPrice)上建立索引，并在没有DESC的情况下命令消除文件排序

如果您的搜索查询不涉及模型，而是任何参数组合-那么是时候考虑像Sphinx search这样的外部搜索引擎了。