Using index condition Using index

Using index condition

Tables are read by accessing index tuples and testing them first to determine whether to read full table

rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is

necessary.

通过索引访问表，看是否需要全表扫描，这时索引的信息推迟使用，必要时读全表。

 Using index

The column information is retrieved from the table using only information in the index tree without having

to do an additional seek to read the actual row. This strategy can be used when the query uses only

columns that are part of a single index.

If the Extra column also says Using where, it means the index is being used to perform lookups of 

key values. Without Using where, the optimizer may be reading the index to avoid reading data rows

but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may

scan it without using it for lookups.

当查询的只是索引的一部分时，只查看索引的信息就可以得到所需的列，不用访问表。 

如果也有Using where时，意思是查找值时使用了索引。 没有using where，优化器可能读索引而没有读数据行，但是没有查找。

例：

mysql> create table t2 ( table_schema varchar(64), table_name varchar(64),table_rows bigint(21) unsigned);

Query OK, 0 rows affected (0.33 sec)

mysql> insert into t2 select  table_schema,table_name,table_rows from information_schema.tables;

Query OK, 153 rows affected (0.09 sec)

Records: 153  Duplicates: 0  Warnings: 0

mysql> create index idx_t2_table_name_rows on t2(table_name,table_rows);

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t2;

+---------+---------+----------+----------+

| Table   | Op      | Msg_type | Msg_text |

+---------+---------+----------+----------+

| test.t2 | analyze | status   | OK       |

+---------+---------+----------+----------+

1 row in set (0.01 sec)

mysql> explain select * from t2 where table_name='t2' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: ref

possible_keys: idx_t2_table_name_rows

          key: idx_t2_table_name_rows

      key_len: 259

          ref: const

         rows: 1

        Extra: Using index condition        --因为select * 还是要读到表数据的，但是使用了索引

1 row in set (0.00 sec)

mysql> explain select * from t2 where table_name='t2' and table_rows=0\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: ref

possible_keys: idx_t2_table_name_rows

          key: idx_t2_table_name_rows

      key_len: 268

          ref: const,const

         rows: 1

        Extra: Using index condition        --因为select * 还是要读到表数据的，但是使用了索引

1 row in set (0.00 sec)

mysql> explain select id from t2 where table_name='t2' and table_rows=0\G

ERROR 1054 (42S22): Unknown column 'id' in 'field list'

mysql> explain select table_name from t2 where table_name='t2' and table_rows=0\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: ref

possible_keys: idx_t2_table_name_rows

          key: idx_t2_table_name_rows

      key_len: 268

          ref: const,const

         rows: 1

        Extra: Using where; Using index         --这个意思是查找时使用了索引，只从索引中就可得到数据，不用访问表数据

1 row in set (0.00 sec)

mysql> explain select table_name from t2 where table_name='t2'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: ref

possible_keys: idx_t2_table_name_rows

          key: idx_t2_table_name_rows

      key_len: 259

          ref: const

         rows: 1

        Extra: Using where; Using index         --这个意思是查找时使用了索引，只从索引中就可得到数据，不用访问表数据

1 row in set (0.00 sec)

mysql> explain  select table_name from t2  \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: index

possible_keys: NULL

          key: idx_t2_table_name_rows

      key_len: 268

          ref: NULL

         rows: 153

        Extra: Using index           --只使用了索引，而没有读表,因为选择的列只是索引的一部分

1 row in set (0.00 sec)

mysql> explain  select table_rows  from t2  \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: index

possible_keys: NULL

          key: idx_t2_table_name_rows

      key_len: 268

          ref: NULL

         rows: 153

        Extra: Using index         --只使用了索引，而没有读表,因为选择的列只是索引的一部分

1 row in set (0.00 sec)

mysql> explain  select table_name,table_rows from t2  \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: index

possible_keys: NULL

          key: idx_t2_table_name_rows

      key_len: 268

          ref: NULL

         rows: 153

        Extra: Using index          --只使用了索引，而没有读表，列都能在索引中得到

1 row in set (0.00 sec)

转载请注明源出处

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-1630731/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-1630731/