MySQL解释的使用

Question

我有一个疑问：

EXPLAIN EXTENDED
SELECT DISTINCT 
 PMS_STAGIONI.DINIZVAL,
 PMS_STAGIONI.DFINEVAL,
 PMS_DISPO.DDATA
FROM 
  PMS_DISPO JOIN PMS_STAGIONI
HAVING
  PMS_DISPO.DDATA BETWEEN PMS_STAGIONI.DINIZVAL AND PMS_STAGIONI.DFINEVAL

解释的输出如下：

+----+-------------+--------------+-------+---------------+------------------------------+---------+------+------+----------+--------------------------------+
| id | select_type | table        | type  | possible_keys | key                          | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+--------------+-------+---------------+------------------------------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | PMS_STAGIONI | index | NULL          | IDX_INIZFINEVAL_PMS_STAGIONI | 6       | NULL |    3 |   100.00 | Using index; Using temporary   |
|  1 | SIMPLE      | PMS_DISPO    | index | NULL          | IDX_DDATA_PMS_DISPO          | 3       | NULL | 1199 |   100.00 | Using index; Using join buffer |
+----+-------------+--------------+-------+---------------+------------------------------+---------+------+------+----------+--------------------------------+

我的问题是如何使用explain计算连接的乘积。例如，在本例中是执行3597 (1199x3)扫描，还是只执行1199？

1)如果我在表"PMS_DISPO“中添加"ORDER”行，则为1130。

2)如果我使用"WHERE“子句而不是”WHERE“子句，则scan不再使用索引。这怎么可能呢？

3)如果我想要显示PMS_STAGIONI.CSTAGIONI (主键)，请解释给我看：

+----+-------------+--------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------------+
| id | select_type | table        | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | PMS_STAGIONI | ALL   | NULL          | NULL                | NULL    | NULL |    3 |   100.00 | Using temporary                |
|  1 | SIMPLE      | PMS_DISPO    | index | NULL          | IDX_DDATA_PMS_DISPO | 3       | NULL | 1130 |   100.00 | Using index; Using join buffer |
+----+-------------+--------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------------+ 

如何强制使用其他索引？

提前谢谢。

编辑：

"PMS_DISPO“的结构是：

CREATE TABLE IF NOT EXISTS `PMS_DISPO` (
  `ID` int(11) NOT NULL AUTO_INCREMENT ,
  `CPRENOTA` int(11) NOT NULL,
  `DDATA` date NOT NULL,
  `CCATRIS` int(4) NOT NULL,
  `NQUANT` int(4) NOT NULL,
  `CAZIENDA` int(4) NOT NULL,
  `CAFFILIATO` int(4) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IDX_DDATA_PMS_DISPO` (`DDATA`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1084 ;

"PMS_STAGIONI“是指：

CREATE TABLE IF NOT EXISTS `PMS_STAGIONI` (
  `CSTAGIONE` int(11) NOT NULL,
  `NVALIDI` tinyint(2) NOT NULL,
  `BECCEZIONE` tinyint(1) NOT NULL,
  `AGGSET` varchar(7) DEFAULT NULL,
  `DINIZVAL` date NOT NULL,
  `DFINEVAL` date NOT NULL,
  `CAZIENDA` int(4) NOT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  KEY `CSTAGIONE` (`CSTAGIONE`),
  KEY `IDX_INIZFINEVAL_PMS_STAGIONI` (`DINIZVAL`,`DFINEVAL`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

Answer 1

这类查询通常如下所示，索引与它们一样.

SELECT DISTINCT s.dinizval
              , s.dfineval
              , d.ddata
           FROM pms_dispo d
           JOIN pms_stagioni s
             ON d.ddata BETWEEN s.dinizval AND s.dfineval