Using index condition Using index

psufnxk2000發表於2015-05-09
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章