ADADADADAD

Percona MySQL 5.6 WHERE条件中OR的索引测试该怎么进行[ mysql数据库 ]

mysql数据库时间：2024-11-26 22:17:40

作者：文/会员上传

如何提高mysql查询效率

简介：

在测试表的两列上分别创建索引。mysql> select * from test;+------+-------+| id | name |+------+-------+| 10 | neo || 20 | John || 30 | Lucy || 40 | Larry || 50 |

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

在测试表的两列上分别创建索引。
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的索引测试该怎么进行.docx

将本文的Word文档下载到电脑

热门标签: mysqlperconawhere

精品

申请https需要什么条件

热门推荐

hadoop分布式数据库怎样管理

2024-12-03

ajax和数据库交互的方法是什么

2024-12-03

hadoop archive适合哪些应用场景

2024-12-06

hbase metadata能否备份恢复

2024-12-25

如何使用Kafka构建可扩展的日志收集系统

2024-12-03

大家都在看