[email protected]_monitor>create table test(id int not null primary key,fid int default null,tid int default null); Query OK, 0 rows affected (0.02 sec) [email protected]_monitor>alter table test add index index_fid_tid(fid,tid); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: [email protected]_monitor>explain select * from test where tid=4; +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+ | 1 | SIMPLE | test | index | NULL | index_fid_tid | 10 | NULL | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)[email protected]_monitor>show index from test; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | test | 1 | index_fid_tid | 1 | fid | A | 0 | NULL | NULL | YES | BTREE | | | test | 1 | index_fid_tid | 2 | tid | A | 0 | NULL | NULL | YES | BTREE | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
[email protected]_monitor>explain select * from test where id!=2; +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+ | 1 | SIMPLE | test | index | PRIMARY | index_fid_tid | 10 | NULL | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
您的存储引擎是innodb,使用的是聚集索引,其他索引(如index_fid_tid) 本身就包含了主键值,检索tid=2的时候,覆盖索引可以提供所有的字段值(3个),所以优化器就直接读取索引index_fid_tid就可以了extra列含义 Using where 使用mysql过滤innodb提供的数据 Using index 使用覆盖索引
有道理,我后来又扩展了一下,假如我增加一个name列的,再使用explain就不适用索引了,因为索引fid_tid中不能包含所有的列了;但是改为explain select id,fid,tid from test where tid=2,这样又使用到索引了。现在还在困惑中,总感觉这样有点牵强,那位牛人看下源码看是咋弄的。另外当数据量很大的时候,比如说几百万的时候也不使用索引,困惑啊
看mysql源码?????quite a tough task
另外当数据量很大的时候,比如说几百万的时候也不使用索引,困惑啊!mysql优化器非常复杂,但大体是依据预测开销来生成执行计划(选择开销最小的执行计划,但由于各种原 因,优化器有可能不会评估所有的执行计划,所以有可能不会使用最优的执行计划)如果优化器认为表扫描比索引扫描开销小,他也会使用表扫描您可以执行sql后,执行show status like 'last_query_cost';可以察看开销的大小 另外再执行一个强制使用索引 force index(index_name)的查询,再执行show status like 'last_query_cost'; 就可以比较开销的大小了
select出来的数据,全部是按照fid升序排列的,而去掉索引,数据就是根据id(pk)的顺序。至于原因,我估计,explain查找possible keys的时候,纯粹根据index的字段顺序是否可以用。而实际执行的时候,觉得扫描索引比扫描表快,所以,还是去用索引了
Query OK, 0 rows affected (0.02 sec)
[email protected]_monitor>alter table test add index index_fid_tid(fid,tid);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: [email protected]_monitor>explain select * from test where tid=4;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | index | NULL | index_fid_tid | 10 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)[email protected]_monitor>show index from test;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
| test | 1 | index_fid_tid | 1 | fid | A | 0 | NULL | NULL | YES | BTREE | |
| test | 1 | index_fid_tid | 2 | tid | A | 0 | NULL | NULL | YES | BTREE | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | index | PRIMARY | index_fid_tid | 10 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Using where 使用mysql过滤innodb提供的数据
Using index 使用覆盖索引
有道理,我后来又扩展了一下,假如我增加一个name列的,再使用explain就不适用索引了,因为索引fid_tid中不能包含所有的列了;但是改为explain select id,fid,tid from test where tid=2,这样又使用到索引了。现在还在困惑中,总感觉这样有点牵强,那位牛人看下源码看是咋弄的。另外当数据量很大的时候,比如说几百万的时候也不使用索引,困惑啊
看mysql源码?????quite a tough task
因,优化器有可能不会评估所有的执行计划,所以有可能不会使用最优的执行计划)如果优化器认为表扫描比索引扫描开销小,他也会使用表扫描您可以执行sql后,执行show status like 'last_query_cost';可以察看开销的大小
另外再执行一个强制使用索引 force index(index_name)的查询,再执行show status like 'last_query_cost'; 就可以比较开销的大小了