mysql的where查询语句后有多个“or”的SQL语句执行分析!

mysql的where查询语句后有多个“or”的SQL语句执行分析

看到一篇文章里面提到where查询语句后有多个“or”的SQL语句执行分析,原来没有碰到这样的情况,做个实验测试下,详细过程如下:

一个数据表person有3个字段,都有索引。

mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| person |          0 | PRIMARY  |            1 | id          | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| person |          1 | name     |            1 | name        | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
| person |          1 | descs    |            1 | descs       | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)


一个字段的情况,用到了索引,是正常的。
mysql> explain select * from person  where id = 3;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | person | const | PRIMARY       | PRIMARY | 2       | const |    1 |       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from person  where name = 'chF';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | person | ref  | name          | name | 181     | const |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from person  where descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | person | ref  | descs         | descs | 63      | const |    1 | Using where |
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)


2个字段的情况,用到了索引,正常。
mysql> explain select * from person  where id = 3 or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| id | select_type | table  | type        | possible_keys | key           | key_len | ref  | rows | Extra                                   |
+----+-------------+--------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
|  1 | SIMPLE      | person | index_merge | PRIMARY,descs | PRIMARY,descs | 2,63    | NULL |    2 | Using union(PRIMARY,descs); Using where |
+----+-------------+--------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from person  where name = 'syy' or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+-------------+---------------+------------+---------+------+------+--------------------------------------+
| id | select_type | table  | type        | possible_keys | key        | key_len | ref  | rows | Extra                                |
+----+-------------+--------+-------------+---------------+------------+---------+------+------+--------------------------------------+
|  1 | SIMPLE      | person | index_merge | name,descs    | name,descs | 181,63  | NULL |    2 | Using union(name,descs); Using where |
+----+-------------+--------+-------------+---------------+------------+---------+------+------+--------------------------------------+
1 row in set (0.00 sec)


3个字段的情况,没有用到索引,异常。
mysql> explain select * from person  where id = 3 or name = 'syy' or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | person | ALL  | PRIMARY,name,descs | NULL | NULL    | NULL |    9 | Using where |
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


强制使用其中2个字段,没有用到索引,异常。
mysql> explain select * from person  force index(primary, name) where id = 3 or name = 'syy' or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | person | ALL  | PRIMARY,name  | NULL | NULL    | NULL |    9 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)



综述所上:
只要是两个索引,都可以走index_merge,换成三个就不行了。
即使是强行指定用某两个索引也不行,索引虽然都能够找到,但优化器不使用任何一个。

原因如下:
即使强制使用了两个索引,那么会有剩下一个条件不会走索引,那么对于该条件的过滤还是要通过表查询,这样,对于 mysql来说就相当于要两个索引的index_mereg后再读表,而且仍然要做一次全表扫描,那还不如就作一次表扫描,Mysql最终还是选择一次表扫描,这样是可以理解的。
在Mysql官方文档上,在提示了mysql用某一个索引后,也就相当于告诉了mysql不要用其他的相关的一些索引。估计 Mysql也并没有去实现三个索引的index_merge,实际上想想就算是实现了,通过读三个索引然后做merge再去取表的记录,其消耗可能也并不会太小,对于Mysql的这个选择也无可厚非。
<script>window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"16"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];</script>
阅读(183) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值