Percona MySQL 5.6 WHERE 條件中 OR 的索引測試

feelpurple發表於2016-08-19

在測試表的兩列上分別建立索引。
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

MySQL索引條件下推的簡單測試
2017-07-03
MySql索引
mysql where條件中字串右邊的空格會忽略
2015-12-07
MySql字串
SQL中on條件與where條件的區別[轉]
2011-11-09
SQL
MySQL核心技術之“WHERE條件”
2017-10-26
MySql
switch拼接where條件
2021-05-14
Mysql索引的使用-組合索引+跳躍條件
2010-03-03
MySql索引
Percona MySQL 5.6 HINT介紹
2016-08-22
MySql
mysql，where條件查詢等學習筆記
2018-07-25
MySql筆記
Oracle 連線條件中帶有OR的測試
2016-09-28
Oracle
【原創】MySQL 模擬條件索引
2021-09-09
MySql索引
MYSQL學習筆記6: DQL條件查詢（where）
2024-03-08
MySql筆記
在 Python 中測試競爭條件
2015-04-15
Python
替代SQL語句WHERE條件中OR關鍵詞
2010-07-01
SQL
Mysql索引的使用 - 組合索引 + 範圍條件的處理
2010-06-01
MySql索引
Oracle中where子句中條件的物理執行順序
2015-09-11
Oracle
MySQL索引效能測試
2015-10-30
MySql索引
【原創】MySQL 模擬條件索引薦
2014-04-02
MySql索引
MySQL中WHERE後跟著N多個OR條件會是你期望的結果嗎？
2023-02-28
MySql
JUnit5的條件測試、巢狀測試、重複測試
2021-07-14
巢狀
mysql5.6的online ddl功能測試
2015-10-30
MySql
關於在SQL語句中ON和WHERE中條件使用的差異
2008-08-31
SQL
避免在WHERE條件中，在索引列上進行計算或使用函式，因為這將導致索引不被使用
2014-01-15
索引函式
儲存過程WHERE條件不生效
2020-06-12
儲存過程
關於外連線和where條件
2018-10-11
LINQ系列：LINQ to SQL Where條件
2014-10-24
SQL
帶你讀 MySQL 原始碼：where 條件怎麼過濾記錄？
2023-04-25
MySql原始碼
Percona MySQL 5.6 RPM包方式安裝流程
2016-08-08
MySql
MySQL 聯合索引測試3
2015-04-15
MySql索引
MySQL 5.6建索引的正確姿勢
2018-06-06
MySql索引
當long欄位在where條件裡的解決方案
2009-05-21
《shell條件測試語句，字串測試apache是否開啟》
2014-06-01
字串Apache
(11)mysql 中的條件定義、處理
2017-01-19
MySql
SQL中的where條件,在資料庫中提取與應用淺析
2014-02-13
SQL資料庫
【索引】反向索引--條件範圍查詢
2010-09-07
索引
MySQL製作具有千萬條測試資料的測試庫
2020-11-29
MySql
MySQL 5.6快速定位不合理索引
2016-03-04
MySql索引
基於Solr的HBase多條件查詢測試
2014-10-15
Solr
MongoDB之資料查詢（where條件過濾）
2017-08-29
MongoDB