简单的索引问题

zhengdl126

页面搜索条件为：用户id，上传类型，是否审核，关键字

索引：Index_U_Id:  `u_id`, `type`, `ischecked`

mysql> explain SELECT `seeds`.* FROM `seeds` WHERE (u_id=14 AND type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+-------------------

----------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref               | rows | Extra               

        |
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+-------------------

----------+
|  1 | SIMPLE      | ios_seeds | ref  | Index_U_Id    | Index_U_Id | 10      | const,const,const |    1 | Using where; Using

filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+-------------------

----------+
1 row in set (0.00 sec)

下面的sql语句都没有用到索引，该如何解决？需要再新建索引吗？


mysql> explain SELECT `seeds`.* FROM `seeds` WHERE (1=1 AND type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | ios_seeds | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)


mysql> explain SELECT `seeds`.* FROM `seeds` WHERE (type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | ios_seeds | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

cenalulu

下面的两句之所以没有用到索引是因为，mysql中只有复合索引中的最左索引子集可以使用。
因此（`u_id`, `type`, `ischecked`）这样的索引，可能被使用的索引组合是以下3种
（`u_id`, `type`, `ischecked`）
（`u_id`, `type`）
（`u_id`）
如果你想让下面两个语句也用上索引的话，应该调整下索引里列的顺序：
Index_U_Id:  `type`, `ischecked`, `u_id`
这样理论上就会被mysql使用。
具体执行计划怎样，就还要考虑你的语句和mysql内部的优化了

zhengdl126

"如果你想让下面两个语句也用上索引的话，应该调整下索引里列的顺序：
Index_U_Id:  `type`, `ischecked`, `u_id`"


如果这样修改，那么第一条SQL可能就不会用到索引了吧，我试试再建个索引
Index_Type:  `type`, `ischecked`


效果如下：


Index_U_Id:  `u_id`, `type`, `ischecked`
Index_Type:  `type`, `ischecked`



mysql> explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+-----------------------+------------+---------+-------------------+------+-----------

------------------+
| id | select_type | table     | type | possible_keys         | key        | key_len | ref               | rows | Extra      

                |
+----+-------------+-----------+------+-----------------------+------------+---------+-------------------+------+-----------

------------------+
|  1 | SIMPLE      | ios_seeds | ref  | Index_U_Id,Index_Type | Index_U_Id | 10      | const,const,const |    1 | Using

where; Using filesort |
+----+-------------+-----------+------+-----------------------+------------+---------+-------------------+------+-----------

------------------+
1 row in set (0.01 sec)



mysql> explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (1=1 AND type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
| id | select_type | table     | type | possible_keys | key        | key_len | ref         | rows | Extra                     

  |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
|  1 | SIMPLE      | ios_seeds | ref  | Index_Type    | Index_Type | 6       | const,const |    1 | Using where; Using

filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
1 row in set (0.00 sec)



mysql> explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref         | rows | Extra                       |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | ios_seeds | ref  | Index_Type    | Index_Type | 6       | const,const |    1 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-----------------------------+
1 row in set (0.00 sec)




都能调用索引，这样做可以吗？

[ 本帖最后由 zhengdl126 于 2009-8-10 18:08 编辑 ]

cenalulu

为什么会认为修改顺序后，第一条语句会用不到索引？不试怎么知道呢

虽然用2个索引确实解决了语句的需求。但是过多的索引会降低表数据插入和修改的性能。
既然能用最左索引解决问题，就尽量使用一个索引。

zhengdl126

按照Index_U_Id:  `type`, `ischecked`, `u_id`，三条SQL都可以用到索引，但是

mysql中只有复合索引中的最左索引子集可以使用
这样的索引理论上可能被使用的索引组合是以下3种
（`type`, `u_id`, `ischecked`）
（`type`,`u_id` ）
（`type`）

但是为什么以下第一个和第二个SQL可以用到索引，不是很明白哦？可以详细说说原理吗？


mysql> explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+-------------------

----------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref               | rows | Extra               

        |
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+-------------------

----------+
|  1 | SIMPLE      | ios_seeds | ref  | Index_U_Id    | Index_U_Id | 10      | const,const,const |    1 | Using where; Using

filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+-------------------

----------+
1 row in set (0.00 sec)







mysql> explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (1=1 AND type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
| id | select_type | table     | type | possible_keys | key        | key_len | ref         | rows | Extra                     

  |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
|  1 | SIMPLE      | ios_seeds | ref  | Index_U_Id    | Index_U_Id | 6       | const,const |    1 | Using where; Using

filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
1 row in set (0.00 sec)





mysql> explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (type=01 AND ischecked =1) ORDER BY `id` ASC ;
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
| id | select_type | table     | type | possible_keys | key        | key_len | ref         | rows | Extra                     

  |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
|  1 | SIMPLE      | ios_seeds | ref  | Index_U_Id    | Index_U_Id | 6       | const,const |    1 | Using where; Using

filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------------+------+-------------------------

----+
1 row in set (0.00 sec)

[ 本帖最后由 zhengdl126 于 2009-8-11 09:44 编辑 ]

cenalulu

你的索引顺序是  Index_U_Id:  `type`, `ischecked`, `u_id`，

最左子集是这个？
（`type`, `u_id`, `ischecked`）
（`type`,`u_id` ）
（`type`）

笔误了吧

zhengdl126

写错了，应该是

（`type`, `ischecked`, `u_id`）
（`type`, `ischecked` ）
（`type`）

能解释一下为什么第一个和第二个SQL可以用到索引吗？

cenalulu

因为第一句有u_id=14 AND type=01 AND ischecked =1
第二句有type=01 AND ischecked =1
这些条件的组合跟索引的组合是一样的，所以能够用到索引。

and连接的条件的顺序并不影响索引使用的判断。
索引列的顺序会影响索引使用的判断

zhengdl126

四、最左前缀

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

firstname，lastname，age

firstname，lastname

firstname

从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。 下面这些查询都能够使用这个fname_lname_age索引：

SELECT peopleid FROM people

WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';

SELECT peopleid FROM people WHERE firstname ='Mike' AND lastname='Sullivan';

SELECT peopleid FROM people WHERE firstname ='Mike';

The following queries cannot use the index at all:

SELECT peopleid FROM people WHERE lastname= 'Sullivan';

SELECT peopleid FROM people WHERE age='17';

SELECT peopleid FROM people WHERE lastname ='Sullivan' AND age='17';  

似乎和你说的   “and连接的条件的顺序并不影响索引使用的判断”  有冲突哦

[ 本帖最后由 zhengdl126 于 2009-8-11 18:11 编辑 ]

cenalulu

我所说的and条件的顺序是指：
type=01 AND ischecked =1
ischecked =1 AND type=01
这样的先后顺序不影响索引的使用。

你再理解下