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

feelpurple發表於2016-08-19

原文網址 : http://blog.itpub.net/26506993/viewspace-2123721/

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

相關文章

switch拼接where條件
2021-05-14
MySQL的where條件字串區分大小寫的問題
2024-10-29
MySql字串
mysql，where條件查詢等學習筆記
2018-07-25
MySql筆記
【原創】MySQL 模擬條件索引
2021-09-09
MySql索引
MYSQL學習筆記6: DQL條件查詢（where）
2024-03-08
MySql筆記
MySQL中WHERE後跟著N多個OR條件會是你期望的結果嗎？
2023-02-28
MySql
MySQL 5.6建索引的正確姿勢
2018-06-06
MySql索引
MySQL 索引優化 Using where, Using filesort
2020-10-26
MySql索引優化
帶你讀 MySQL 原始碼：where 條件怎麼過濾記錄？
2023-04-25
MySql原始碼
關於外連線和where條件
2018-10-11
儲存過程WHERE條件不生效
2020-06-12
儲存過程
JUnit5的條件測試、巢狀測試、重複測試
2021-07-14
巢狀
基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6
2019-11-04
MySql
T-SQL——關於Join on的的連線條件和where的篩選條件的區分
2024-09-13
SQL
MySQL 5.6 innodb_io_capacity引數效能測試
2018-09-21
MySql
ORACLE sql merge into update where條件位置與效能消耗
2018-03-19
OracleSQL
mysql 左連結 left join 條件寫在where 後面與 on後面的區別
2021-08-27
MySql
【Mysql】資料庫索引，百萬資料測試索引效果
2021-05-25
MySql資料庫索引
MySQL製作具有千萬條測試資料的測試庫
2020-11-29
MySql
OGG雙向條件複製的部署與測試
2018-03-28
mysql條件查詢
2024-04-06
MySql
[20190502]查詢條件不等於測試.txt
2019-05-03
詳解MySQL中WHERE子句的用法
2021-09-09
MySql
Junit5系列-Junit5中DisabledCondition條件測試執行
2019-01-10
Android學習 —— 測試init.rc中的條件觸發的處理順序
2024-03-10
Android
drools中的條件 when
2022-05-24
MySQL 針對 like 條件的優化
2019-11-08
MySql優化
【Mysql】MySQL 5.6中如何定位DDL被阻塞的問題
2018-08-27
MySql
Mysql生成100w條測試資料
2018-06-06
MySql
MySQL中的索引詳講
2018-09-22
MySql索引
mysql中BTree索引的理解
2021-09-11
MySql索引
oracle中的條件語句
2018-05-07
Oracle
【SQL】SQL中if條件的使用
2024-06-24
SQL
測試平臺系列(84) 支援複製其他前置條件
2021-11-29
測試平臺系列(83) 前置條件支援Redis語句
2021-11-27
Redis
六條幹貨幫你的MySQL索引起飛
2022-11-17
MySql索引
Oracle 條件索引 case when 報錯解決方案
2022-02-21
Oracle索引
MySQL 資料庫生成 10000 條測試資料
2020-05-27
MySql資料庫
資料庫聚簇索引——not null條件對唯一鍵索引成為聚簇索引的影響
2024-05-27
資料庫索引Null