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;

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

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

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

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

Using index condition Using index

相關文章