Percona MySQL 5.6 WHERE條件中OR的索引測(cè)試該怎么進(jìn)行

本篇文章給大家分享的是有關(guān)Percona MySQL 5.6 WHERE條件中OR的索引測(cè)試該怎么進(jìn)行，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

在測(cè)試表的兩列上分別創(chuàng)建索引。
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條件中分別涵蓋這兩列，會(huì)使用到這兩個(gè)索引。

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)

刪除這兩個(gè)索引，重建一個(gè)索引，在OR條件中會(huì)使用到這一個(gè)索引。

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)

刪除表中的索引，創(chuàng)建一個(gè)包含這兩列的聯(lián)合索引，在OR條件中會(huì)使用到這個(gè)索引。發(fā)現(xiàn)在MySQL里面聯(lián)合索引的應(yīng)用規(guī)則和Oracle中不同，單獨(dú)查詢條件中只含一列的WHERE條件，如創(chuàng)建聯(lián)合索引的字段順位為A、B，在B上的查詢也會(huì)使用聯(lián)合索引。

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)

以上就是Percona MySQL 5.6 WHERE條件中OR的索引測(cè)試該怎么進(jìn)行，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。