关于索引

Question

表里有个复合索引(beginTime,agentID, firstNo)
下面的语句用到了该索引EXPLAIN SELECT * FROM `t_test` WHERE `firstNo` LIKE 'S' AND `beginTime` > '2012-03-01 00:00:00' AND `endTime` <= 'S' LIMIT 0,1000 只把日期改一下beginTime由2012-03-01改了2011-03-01EXPLAIN SELECT * FROM `t_test` WHERE `firstNo` LIKE 'S' AND `beginTime` > '2011-03-01 00:00:00' AND `endTime` <= 'S' LIMIT 0,1000这条进行了全表扫描,请问这是为什么啊?

Answer 1

贴出你的 show index from `t_test` 
和
EXPLAIN SELECT * FROM `t_test` WHERE `firstNo` LIKE 'S' AND `beginTime` > '2011-03-01 00:00:00' AND `endTime` <= 'S' LIMIT 0,1000的结果以供分析。

Answer 2

mysql> show index from `t_toll_universal`;
+------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_toll_universal |          0 | PRIMARY                       |            1 | Id          | A         | NULL        | NULL     | NULL   |      | BTREE      |         |               |
| t_toll_universal |          0 | PRIMARY                       |            2 | beginTime   | A         |    19939540 | NULL     | NULL   |      | BTREE      |         |               |
| t_toll_universal |          1 | invokeid                      |            1 | invokeId    | A         |    19939540 | NULL     | NULL   | YES  | BTREE      |         |               |
| t_toll_universal |          1 | idx_beginTime_agentID_fisrtNo |            1 | beginTime   | A         |    19939540 | NULL     | NULL   |      | BTREE      |         |               |
| t_toll_universal |          1 | idx_beginTime_agentID_fisrtNo |            2 | agentID     | A         |    19939540 | NULL     | NULL   | YES  | BTREE      |         |               |
| t_toll_universal |          1 | idx_beginTime_agentID_fisrtNo |            3 | firstNo     | A         |    19939540 | NULL     | NULL   | YES  | BTREE      |         |               |
| t_toll_universal |          1 | agentID                       |            1 | agentID     | A         |        6996 | NULL     | NULL   | YES  | BTREE      |         |               |
+------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+------

Answer 3

mysql> EXPLAIN SELECT * FROM `t_test` WHERE `firstNo` LIKE 'S' AND `beginTime` > '2011-03-01 00:00:00' AND `endTime` <= 'S' LIMIT 0,1000;
+----+-------------+------------------+------+-------------------------------+------+---------+------+----------+-------------+
| id | select_type | table            | type | possible_keys                 | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------------+------+-------------------------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | t_toll_universal | ALL  | idx_beginTime_agentID_fisrtNo | NULL | NULL    | NULL | 19939540 | Using where |
+----+-------------+------------------+------+-------------------------------+------+---------+------+----------+-------------+

Answer 4

另一条sql用到了索引mysql> EXPLAIN PARTITIONS SELECT count(invokeId) FROM `t_toll_universal` WHERE (managerId = '30092591') AND ((agentID <> '' or secondNo <> '')) AND (callDirection = 0) AND (IsTransferred = 0) AND (beginTime >= '2012-02-11 00:00:00') AND (beginTime < '2012-04-12 00:00:00');
+----+-------------+------------------+---------------------------------+-------+---------------------------------------+-------------------------------+---------+------+---------+-------------+
| id | select_type | table            | partitions                      | type  | possible_keys                         | key                           | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+---------------------------------+-------+---------------------------------------+-------------------------------+---------+------+---------+-------------+
|  1 | SIMPLE      | t_toll_universal | p201107,p201202,p201203,p201204 | range | idx_beginTime_agentID_fisrtNo,agentID | idx_beginTime_agentID_fisrtNo | 8       | NULL | 1208055 | Using where |
+----+-------------+------------------+---------------------------------+-------+---------------------------------------+-------------------------------+---------+------+---------+-------------+我再次把beginTime由2012-02-11改成2012-01-11月，他符合条件的是1千多万条，为什么也会全表扫描?mysql> EXPLAIN PARTITIONS SELECT count(invokeId) FROM `t_toll_universal` WHERE (managerId = '30092591') AND ((agentID <> '' or secondNo <> '')) AND (callDirection = 0) AND (IsTransferred = 0) AND (beginTime >= '2012-01-11 00:00:00') AND (beginTime < '2012-04-12 00:00:00');
+----+-------------+------------------+-----------------------------------------+------+---------------------------------------+------+---------+------+----------+-------------+
| id | select_type | table            | partitions                              | type | possible_keys                         | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------------+-----------------------------------------+------+---------------------------------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | t_toll_universal | p201107,p201201,p201202,p201203,p201204 | ALL  | idx_beginTime_agentID_fisrtNo,agentID | NULL | NULL    | NULL | 10725523 | Using where |
+----+-------------+------------------+-----------------------------------------+------+---------------------------------------+------+---------+------+----------+-------------+
1 row in set

Answer 5

MYSQL技术内幕INNODB引擎，这本书有介绍。

Answer 6

CREATE TABLE `f_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `beginTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`beginTime`),
  KEY `idx_beginTime_col1_col2` (`beginTime`,`col1`,`col2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
+----+---------------------+------+------+------+
| id | beginTime           | col1 | col2 | data |
+----+---------------------+------+------+------+
|  1 | 2012-04-17 10:49:10 |    1 |    2 | NULL |
|  2 | 2012-03-01 10:49:22 |    2 |    4 | NULL |
|  3 | 2012-02-01 10:49:37 |    5 |    6 | NULL |
+----+---------------------+------+------+------+EXPLAIN SELECT COUNT(*) FROM `f_test` WHERE beginTime >= '2012-02-01 10:49:22' AND beginTime <= '2012-03-01 10:49:22';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | f_test | index | idx_beginTime_col1_col2 | idx_beginTime_col1_col2 | 18      | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+--------------------------+不对啊，按你说的20%的话，上面这条sql查询出来的数据是总数的66%,这不是也用的索引了吗?

Answer 7

浪头说的是对的，数据占了数据量20%是会全表扫描的 
 mysql> EXPLAIN PARTITIONS SELECT count(invokeId) FROM `t_toll_universal` WHERE (managerId = '30092591') AND ((agentID <> '' or secondNo <> '')) AND (callDirection = 0) AND (IsTransferred = 0) AND (beginTime >= '2012-01-11 00:00:00') AND (beginTime < '2012-04-12 00:00:00');
+----+-------------+------------------+-----------------------------------------+------+---------------------------------------+------+---------+------+----------+-------------+
| id | select_type | table            | partitions                              | type | possible_keys                         | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------------+-----------------------------------------+------+---------------------------------------+------+---------+------+----------+-------------+
create index  idx_managerId_callDirection_IsTransferred_beginTime on t_toll_universal(managerId,callDirection,IsTransferred,beginTime)

Answer 8

你自己已经给出答案了
EXPLAIN SELECT COUNT(*) FROM `f_test` WHERE beginTime >= '2012-02-01 10:49:22' AND beginTime <= '2012-03-01 10:49:22';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | f_test | index | idx_beginTime_col1_col2 | idx_beginTime_col1_col2 | 18      | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+--------------------------+通过全索引扫描已经可以得到结果，为什么要回表呢，如果执行这条语句的话，EXPLAIN PARTITIONS SELECT count(invokeId) FROM `t_toll_universal` WHERE (managerId = '30092591') AND ((agentID <> '' or secondNo <> '')) AND (callDirection = 0) AND (IsTransferred = 0) AND (beginTime >= '2012-01-11 00:00:00') AND (beginTime < '2012-04-12 00:00:00');试试这条索引
create index idx_managerId_callDirection_IsTransferred_beginTime on t_toll_universal(managerId,callDirection,IsTransferred,beginTime)