第三步：根据非聚集索引查询
- mysql> select name ,gender from person4index where gender in ("male","female");
- +----------+--------+
- | name | gender |
- +----------+--------+
- | wenwen | female |
- | zhaoming | male |
- +----------+--------+
- 2 rows in set (0.00 sec)
|
查看器执行计划：
- mysql> explain select name ,gender from person4index where gender in ("male","female");
- +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+
- | 1 | SIMPLE | person4index | range | gender | gender | 12 | NULL | 2 | Using where |
- +----+-------------+--------------+-------+---------------+--------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
|
可以看出，走的也是范围索引。同主键查询，那么就看其消耗时间了
- mysql> show profiles;
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
- | 68 | 0.00106600 | select name ,gender from person4index where gender in ("male","female") |
- | 69 | 0.00092500 | explain select name ,gender from person4index where gender in ("male","female") |
- +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
- 15 rows in set (0.00 sec)
|
这个非主键索引消耗的时间为：0.00106600，可以看出略大于组件索引消耗的时间。
看其具体消耗的阶段：
- mysql> show profile for query 68 ;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.000059 |
- | checking query cache for query | 0.000111 |
- | Opening tables | 0.000085 |
- | System lock | 0.000023 |
- | Table lock | 0.000067 |
- | init | 0.000183 |
- | optimizing | 0.000031 |
- | statistics | 0.000139 |
- | preparing | 0.000035 |
- | executing | 0.000020 |
- | Sending data | 0.000148 |
- | end | 0.000024 |
- | query end | 0.000019 |
- | freeing items | 0.000043 |
- | storing result in query cache | 0.000042 |
- | logging slow query | 0.000017 |
- | cleaning up | 0.000020 |
- +--------------------------------+----------+
- 17 rows in set (0.00 sec)
|
看几个关键词的点；init,statistics,Sending data 这几个关键点上的消耗向比较主键的查询要大很多，特别是Sending data。因为若是走的非聚集索引，那么就需要回表进行再进行一次查询，多消耗一次IO。
第四步：根据覆盖索引查询数据
- mysql> select gender ,name from person4cindex where gender in ("male","female");
- +--------+----------+
- | gender | name |
- +--------+----------+
- | female | wenwen |
- | male | zhaoming |
- +--------+----------+
- 2 rows in set (0.01 sec)
|
这里需要注意的是，我的字段查询顺序变了，是gender,name而不在是前面的name,gender，这样是为了走覆盖索引。具体看效果吧
还是先看执行计划：
- mysql> explain select gender ,name from person4cindex where gender in ("male","female");
- +----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+
- | 1 | SIMPLE | person4cindex | index | NULL | name | 44 | NULL | 2 | Using where; Using index |
- +----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+
- 1 row in set (0.00 sec)
|
