admin管理员组文章数量:1599883
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)
–这个意思是查找时使用了索引,只从索引中就可得到数据,不用访问表数据
-只使用了索引,而没有读表,因为选择的列只是索引的一部分
版权声明:本文标题:Using index conditionUsing index 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/xitong/1728323562a1154143.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论