Percona MySQL 5.6 WHERE条件中OR的索引测试该怎么运行
发布时间：2022-01-12 12:11:43 所属栏目：MySql教程 来源：互联网
导读：本篇文章给大家分享的是有关Percona MySQL 5.6 WHERE条件中OR的索引测试该怎么进行，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 在测试表的两列上分别创建索引。 mysql select * from
|
本篇文章给大家分享的是有关Percona MySQL 5.6 WHERE条件中OR的索引测试该怎么进行，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 在测试表的两列上分别创建索引。 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) 以上就是Percona MySQL 5.6 WHERE条件中OR的索引测试该怎么进行，小编相信有部分知识点可能是我们日常工作会见到或用到的。 （编辑：92站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
