Percona MySQL 5.6 WHERE 条件中 OR 的索引测试

亦凉 2024-04-18 01:43 29阅读 0赞

在测试表的两列上分别创建索引。  
mysql> select \* from test;  
\+------+-------+  
| id   | name  |  
\+------+-------+  
|   10 | neo   |  
|   20 | John  |  
|   30 | Lucy  |  
|   40 | Larry |  
|   50 | Lilly |  
\+------+-------+  
5 rows in set (0.00 sec)  
mysql> show keys from test;  
Empty set (0.01 sec)  
  
mysql> create index idx\_test\_id on test(id);  
Query OK, 0 rows affected (0.34 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
mysql> create index idx\_test\_name on test(name);  
Query OK, 0 rows affected (0.72 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> show keys from test;  
\+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table | Non\_unique | Key\_name      | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| test  |          1 | idx\_test\_id   |            1 | id          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |  
| test  |          1 | idx\_test\_name |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |  
\+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
2 rows in set (0.02 sec)

#### 在OR条件中分别涵盖这两列，会使用到这两个索引。 ####

mysql> explain select \* from test where id=10;  
\+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+  
| id | select\_type | table | type | possible\_keys | key         | key\_len | ref   | rows | Extra |  
\+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+  
|  1 | SIMPLE      | test  | ref  | idx\_test\_id   | idx\_test\_id | 5       | const |    1 | NULL  |  
\+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where name='Lucy';  
\+----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+  
| id | select\_type | table | type | possible\_keys | key           | key\_len | ref   | rows | Extra                 |  
\+----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+  
|  1 | SIMPLE      | test  | ref  | idx\_test\_name | idx\_test\_name | 18      | const |    1 | Using index condition |  
\+----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where id=10 or name='Lucy';  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys             | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | test  | ALL  | idx\_test\_id,idx\_test\_name | NULL | NULL    | NULL |    5 | Using where |  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where id=20 or name='Lucy';  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys             | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | test  | ALL  | idx\_test\_id,idx\_test\_name | NULL | NULL    | NULL |    5 | Using where |  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where id=50 or name='neo';  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys             | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | test  | ALL  | idx\_test\_id,idx\_test\_name | NULL | NULL    | NULL |    5 | Using where |  
\+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)

#### 删除这两个索引，重建一个索引，在OR条件中会使用到这一个索引。 ####

mysql> drop index idx\_test\_id;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1  
mysql> drop index idx\_test\_id on test;  
Query OK, 0 rows affected (0.33 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> drop index idx\_test\_name on test;  
Query OK, 0 rows affected (0.02 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> create index idx\_test\_id on test(id);  
Query OK, 0 rows affected (0.09 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> explain select \* from test where id=50 or name='neo';  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | test  | ALL  | idx\_test\_id   | NULL | NULL    | NULL |    5 | Using where |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where name='neo';  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where name='Lucy';  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
| id | select\_type | table | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |  
\+----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)

#### 删除表中的索引，创建一个包含这两列的联合索引，在OR条件中会使用到这个索引。发现在MySQL里面联合索引的应用规则和Oracle中不同，单独查询条件中只含一列的WHERE条件，如创建联合索引的字段顺位为A、B，在B上的查询也会使用联合索引。 ####

mysql> drop index idx\_test\_name on test;  
ERROR 1091 (42000): Can't DROP 'idx\_test\_name'; check that column/key exists  
mysql> drop index idx\_test\_id on test;  
Query OK, 0 rows affected (0.09 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> create index idx\_test\_id\_name on test(id,name);  
Query OK, 0 rows affected (0.21 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> show keys from test;  
\+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table | Non\_unique | Key\_name         | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| test  |          1 | idx\_test\_id\_name |            1 | id          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |  
| test  |          1 | idx\_test\_id\_name |            2 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |  
\+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
2 rows in set (0.00 sec)  
  
mysql> explain select \* from test where name='Lucy';  
\+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+  
| id | select\_type | table | type  | possible\_keys | key              | key\_len | ref  | rows | Extra                    |  
\+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+  
|  1 | SIMPLE      | test  | index | NULL          | idx\_test\_id\_name | 23      | NULL |    5 | Using where; Using index |  
\+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where name='neo';  
\+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+  
| id | select\_type | table | type  | possible\_keys | key              | key\_len | ref  | rows | Extra                    |  
\+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+  
|  1 | SIMPLE      | test  | index | NULL          | idx\_test\_id\_name | 23      | NULL |    5 | Using where; Using index |  
\+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where id=10;  
\+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+  
| id | select\_type | table | type | possible\_keys    | key              | key\_len | ref   | rows | Extra       |  
\+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+  
|  1 | SIMPLE      | test  | ref  | idx\_test\_id\_name | idx\_test\_id\_name | 5       | const |    1 | Using index |  
\+----+-------------+-------+------+------------------+------------------+---------+-------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> explain select \* from test where id=10 or name='Lucy';  
\+----+-------------+-------+-------+------------------+------------------+---------+------+------+--------------------------+  
| id | select\_type | table | type  | possible\_keys    | key              | key\_len | ref  | rows | Extra                    |  
\+----+-------------+-------+-------+------------------+------------------+---------+------+------+--------------------------+  
|  1 | SIMPLE      | test  | index | idx\_test\_id\_name | idx\_test\_id\_name | 23      | NULL |    5 | Using where; Using index |  
\+----+-------------+-------+-------+------------------+------------------+---------+------+------+--------------------------+  
1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2123721/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2123721/

发表评论取消回复

表情：

评论列表（有 0 条评论，29人围观）

还没有评论，来说两句吧...

相关阅读

相关 Percona MySQL 5.6 WHERE 条件中 OR 的索引测试

测试表的两列上分别创建索引。 mysql> select \* from test; \+------+-------+ | id | name | ...

亦凉/ 2024年04月18日 01:43/ 0 赞/ 30 阅读

相关 MYSQL中WHERE语句（条件查询）

0 写在前面最近写SQL语句的时候经常使用where去做判断。所以写一点关于WHERE的知识。 `WHERE`子句使用来过滤结果集中的行记录。如果使用select

今天药忘吃喽~/ 2024年04月01日 16:52/ 0 赞/ 51 阅读

相关 mysql where 条件取反_MySQL：where子句过滤

一、单一条件：select 列 from 表 where 条件 eg: select prod\_name,price from products where price>5

野性酷女/ 2023年01月12日 04:18/ 0 赞/ 188 阅读

相关 mysql5.6 索引条件推送_《高性能MySQL》中，在索引中使用where条件过滤不匹配的记录,在存储器层面完成的，怎么理解？...

在索引中使用where条件过滤不匹配的记录,在存储器层面完成的个人理解：这个应该是跟ICP有关,ICP就是 index condition pushdown 在说ICP之前

女爷i/ 2023年01月12日 04:12/ 0 赞/ 13 阅读

相关 mysql包含条件查询怎么写_mysql中where条件查询

\进阶2：条件查询 /\ 语法： SELECT 查询列表 FROM 表名 WHERE 筛选条件；分类：一、按条件表达式筛选条件运算符：> < = <

骑猪看日落/ 2023年01月10日 15:55/ 0 赞/ 172 阅读

相关 mybatis中where条件的查询

mybatis中的where语句的使用 <select id="findUserInfoByTrim" parameterType="Map"

比眉伴天荒/ 2022年12月25日 10:55/ 0 赞/ 221 阅读

相关 mysql 联合索引生效的条件、索引失效的条件

1.联合索引失效的条件联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中

- 日理万妓/ 2022年03月17日 08:21/ 0 赞/ 347 阅读

相关 [MySQL] 测试where group by order by的索引问题

1. select \ from test where a=xx group by b order by c 如何加索引 CREATE TABLE \`index\_t

傷城~/ 2021年12月14日 11:01/ 0 赞/ 217 阅读

相关 mysql 联合索引生效的条件、索引失效的条件

1.联合索引失效的条件联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的

迈不过友情╰/ 2021年11月11日 19:24/ 0 赞/ 532 阅读

相关 MySQL where 条件字句查询

where 条件字句搜索条件可由一个或多个逻辑表达式组成 , 结果一般为布尔值 > 逻辑运算符 <table> <thead> <tr> <t

ゝ一纸荒年。/ 2021年09月21日 11:26/ 0 赞/ 399 阅读