mysql - 如何减少mysql查询运行时间

Question

这是我在网站的一个页面上运行的查询

SELECT 
    DISTINCT b.CruisePortID,
    b.SailingDates,
    b.CruisePortID,
    b.ArriveTime,
    b.DepartTime,
    b.PortName,
    b.DayNumber
FROM
    cruise_itineraries a,
    cruise_itinerary_days b,
    cruise_ports c
WHERE
a.ID = b.CruiseItineraryID
AND a.CruisePortID = c.ID
AND a.ID = '352905'
AND b.CruisePortID != 0
GROUP BY b.DayNumber;

在 phpmy admin 中运行此查询需要 3.20 秒，因为 cruise_itineraries 有超过 300 000 条记录在索引显示 2.92 秒后，我也尝试了索引。是否有可能将查询时间减少 0.10 秒。它有助于我的网站性能

这里是详细信息

 CREATE TABLE IF NOT EXISTS `cruise_itineraries` (
  `cl` int(11) NOT NULL,
  `ID` bigint(20) NOT NULL,
  `Description` varchar(500) NOT NULL,
  `SailingPlanID` varchar(100) NOT NULL,
  `VendorID` varchar(100) NOT NULL,
  `VendorName` varchar(100) NOT NULL,
  `ShipID` varchar(100) NOT NULL,
  `ShipName` varchar(100) NOT NULL,
  `Duration` int(11) NOT NULL,
  `DestinationID` varchar(100) NOT NULL,
  `Date` datetime NOT NULL,
  `CruisePortID` varchar(100) NOT NULL,
  `TradeRestriction` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `cruise_itinerary_days` (
  `cld` int(11) NOT NULL,
  `CruiseItineraryID` varchar(100) NOT NULL,
  `SailingDates` datetime NOT NULL,
  `VendorID` int(11) NOT NULL,
  `VendorName` varchar(100) NOT NULL,
  `ShipID` int(11) NOT NULL,
  `ShipName` varchar(100) NOT NULL,
  `SailingPlanID` int(11) NOT NULL,
  `PlanName` varchar(100) NOT NULL,
  `DayNumber` bigint(20) NOT NULL,
  `PortName` varchar(100) NOT NULL,
  `CruisePortID` varchar(100) NOT NULL,
  `ArriveTime` varchar(100) NOT NULL,
  `DepartTime` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `cruise_ports` (
  `cp` int(11) NOT NULL,
  `ID` varchar(100) NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Description` varchar(1000) NOT NULL,
  `NearestAirportCode` varchar(100) NOT NULL,
  `UNCode` varchar(100) NOT NULL,
  `Address` varchar(500) NOT NULL,
  `City` varchar(100) NOT NULL,
  `StateCode` varchar(100) NOT NULL,
  `CountryCode` varchar(100) NOT NULL,
  `PostalCode` varchar(100) NOT NULL,
  `Phone` varchar(50) NOT NULL,
  `Fax` varchar(100) NOT NULL,
  `Directions` varchar(1000) NOT NULL,
  `Content` varchar(1000) NOT NULL,
  `HomePageURL` varchar(100) NOT NULL,
  `Longitude` varchar(100) NOT NULL,
  `Latitude` varchar(500) NOT NULL,
  `CarnivalID` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    ALTER TABLE `cruise_itineraries`
  ADD PRIMARY KEY (`cl`),
  ADD KEY `ID_2` (`ID`);

    ALTER TABLE `cruise_itineraries`
  ADD PRIMARY KEY (`cl`),
  ADD KEY `ID_2` (`ID`);


ALTER TABLE `cruise_itinerary_days`
  ADD PRIMARY KEY (`cld`);

ALTER TABLE `cruise_ports`
  ADD PRIMARY KEY (`cp`);


ALTER TABLE `cruise_itineraries`
  MODIFY `cl` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `cruise_itinerary_days`
  MODIFY `cld` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `cruise_ports`
  MODIFY `cp` int(11) NOT NULL AUTO_INCREMENT;

解释结果:

+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows    | Extra                                                  |
+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------------------------------------+
|  1 | SIMPLE      | a     | ref  | ID_2          | ID_2 | 8       | const |       1 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL  |    3267 | Using where; Using join buffer (Block Nested Loop)     |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL  | 2008191 | Using where; Using join buffer (Block Nested Loop)     |
+----+-------------+-------+------+---------------+------+---------+-------+---------+--------------------------------------------------------+

+----+-------------+-------+------+------------------------------------+------------------------------------+---------+-------+------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys                      | key                                | key_len | ref   | rows | Extra                                                        |
+----+-------------+-------+------+------------------------------------+------------------------------------+---------+-------+------+--------------------------------------------------------------+
|  1 | SIMPLE      | b     | ref  | Idx_CruiseItineraryID_CruisePortID | Idx_CruiseItineraryID_CruisePortID | 9       | const |   12 | Using index condition; Using temporary; Using filesort       |
|  1 | SIMPLE      | a     | ref  | ID_2                               | ID_2                               | 8       | const |    1 | Distinct                                                     |
|  1 | SIMPLE      | c     | ALL  | NULL                               | NULL                               | NULL    | NULL  | 3267 | Using where; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+------------------------------------+------------------------------------+---------+-------+------+--------------------------------------------------------------+

Answer 1

首先我想声明尽量避免IMPLICIT MySQL JOINS。请改用INNER JOINS。

I personally think the INNER JOIN is better, because it is more readable. It shows better the relations between the table. You got those relations in the join, and you do the filtering in the WHERE clause. This separation makes the query more readable.

我发现的错误:

• cruise_itinerary.ID的数据类型为BIGINT，cruise_itinerary_days.CruiseItineraryID的数据类型为varchar 。但是您在查询中匹配它们。因此，无论您是否在 cruise_itinerary_days 表中的 cruise_itinerary_days.CruiseItineraryID 上使用索引，它都会运行缓慢。

  修改cruise_itinerary_days.CruiseItineraryID的数据类型为BIGINT。

  ALTER TABLE cruise_itinerary_days MODIFY CruiseItineraryID BIGINT;

• 接下来，您必须根据您的查询在 cruise_itinerary_days 表上创建一个复合索引。

  ALTER TABLE cruise_itinerary_days ADD INDEX Idx_CruiseItineraryID_CruisePortID ( CruiseItineraryID , CruisePortID )`

• 现在在 cruise_ports 表的 cruise_ports.ID 字段上创建一个索引。

  修改表 cruise_ports添加索引 Idx_cruise_ports_ID ( ID );

最后，查询是使用 INNER JOINS 制定的，因为我已经在上面说明了这个选择背后的原因:

SELECT 
    DISTINCT b.CruisePortID,
    b.SailingDates,
    b.CruisePortID,
    b.ArriveTime,
    b.DepartTime,
    b.PortName,
    b.DayNumber
FROM cruise_itineraries a
INNER JOIN cruise_itinerary_days b ON a.ID = b.CruiseItineraryID
INNER JOIN cruise_ports c ON a.CruisePortID = c.ID
WHERE a.ID = 352905
AND b.CruisePortID != 0
GROUP BY b.DayNumber;