关于index的一点遐思
下面来简单的介绍一下这三者的区别
using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
ok，开始测试一番
#建立测试表
mysql> create table test(n1 int,n2 int,n3 time);
Query OK, 0 rows affected (0.02 sec)
#插入测试数据
mysql> insert into test values(1,1,now()),(2,2,now());
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#确认数据
mysql> select * from test;
+------+------+----------+
| n1 | n2 | n3 |
+------+------+----------+
| 1 | 1 | 15:30:53 |
| 2 | 2 | 15:30:53 |
+------+------+----------+
2 rows in set (0.01 sec)
#创建index
mysql> create index idx on test(n1,n2);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#全表扫描，肯定不会用到索引
mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
#查询n1 字段，应该会用到索引 哈哈
mysql> explain select n1 from test;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | NULL | idx | 10 | NULL | 2 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
#查询n12字段，应该会用到索引 哈哈
mysql> explain select n2 from test;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | NULL | idx | 10 | NULL | 2 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
#查询n3字段，应该不会用到索引 哈哈
mysql> explain select n3 from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
#根据n2字段取出所有值。由于n2字段有索引，那么根据n2字段可以界定到index，但是查询的是 所有值 包括 n1 n2 n3，因此需要一个回表操作。
mysql> explain select * from test where n2=2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
#根据n2字段取出n1的值。由于n2字段有索引，那么根据n2字段可以界定到index，且查询的是 n1 ，因此单独从index 中就可以取到数据。
mysql> explain select n1 from test where n2=2;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | index | NULL | idx | 10 | NULL | 2 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
#根据n2字段取出n3的值。由于n2字段有索引，那么根据n2字段可以界定到index，但是查询的是 n3，因此需要一个回表操作。
mysql> explain select n3 from test where n2=2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
#n3字段无索引。哈哈哈 故 null
mysql> explain select n3 from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> explain select n3 from test where n2=1 and n2=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select n3 from test where n2=1 and n1=1;
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------+
| 1 | SIMPLE | test | ref | idx | idx | 10 | const,const | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------+
1 row in set (0.00 sec)
mysql> explain select n3 from test where n1=1 and n2=1;
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------+
| 1 | SIMPLE | test | ref | idx | idx | 10 | const,const | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------+
创建时间：2021-12-22 10:53
넶浏览量：0
