mysql - 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 BY DDATA”扫描行，则变为 1130。

2)如果我使用“WHERE”子句而不是“HAVING”子句，扫描将不再使用索引。怎么可能？

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