mysql的where查询语句后有多个“or”的SQL语句执行分析!
来源:互联网 发布:php 扩展reflection 编辑:程序博客网 时间:2024/05/02 02:28
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的这个选择也无可厚非。
看到一篇文章里面提到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的这个选择也无可厚非。
0
上一篇:gedit 快捷键
下一篇:网站开发 百度地图的添加 (自定义地址)
相关热门文章
- A sample .exrc file for vi e...
- IBM System p5 服务器 HACMP ...
- 游标的特征
- busybox的httpd使用CGI脚本(Bu...
- Solaris PowerTOP 1.0 发布
- linux dhcp peizhi roc
- 关于Unix文件的软链接
- 求教这个命令什么意思,我是新...
- sed -e "/grep/d" 是什么意思...
- 谁能够帮我解决LINUX 2.6 10...
给主人留下些什么吧!~~
评论热议
0 0
- mysql的where查询语句后有多个“or”的SQL语句执行分析!
- mysql where 查询在某个时间段内的sql语句
- oracle where语句中and,or,not的执行顺序
- MYSQL 配置查询日志[包含所有执行的SQL语句]
- MySQL循环遍历执行查询SQL语句结果的方法
- MySQL循环遍历执行查询SQL语句结果的方法
- 如何查询mysql中执行效率低的sql语句
- 10 CI where数据库操作 以及查询最后一次执行的sql语句
- SQL查询语句中select from where group by having order by的执行顺序
- Sql查询--sql语句的执行顺序
- .mysql的SQL语句执行计划分析:EXPLAIN
- mysql 分析查找执行效率慢的SQL语句
- mysql 分析查找执行效率慢的SQL语句
- mysql 分析查找执行效率慢的SQL语句
- 转载------mysql 分析查找执行效率慢的SQL语句
- mysql explain分析sql语句的执行计划
- mysql 分析查找执行效率慢的SQL语句
- mysql的SQL语句执行计划分析:EXPLAIN
- shell文件操作汇总
- 配置CentOS 6.2 第三方yum源
- Firefox 键盘快捷键
- Python抽象工厂模式
- gedit 快捷键
- mysql的where查询语句后有多个“or”的SQL语句执行分析!
- 网站开发 百度地图的添加 (自定义地址)
- ecmall 页面空白解决方案汇总 (仅供参考)
- 用wget做站点镜像
- 安装系统简明教程(windows)
- 嘉缘人才系统_BUG修复 出生日期总提示需大于12岁
- C#/获取本机IP的代码
- ecmall 支付宝无法安装
- Mysql 事务(一)
原创粉丝点击
热门IT博客
热门问题
老师的惩罚
人脸识别
我在镇武司摸鱼那些年
重生之率土为王
我在大康的咸鱼生活
盘龙之生命进化
天生仙种
凡人之先天五行
春回大明朝
姑娘不必设防,我是瞎子
韩春雨事件调查结果
写春雨的诗句
春雨里洗过的太阳
描写春雨的句子
春雨面膜用完要洗吗
模特熊春雨高清大图
描写春雨的古诗
杏花春雨江南
描写春雨古诗
春雨面膜真假鉴别
春雨丫头们伺候老爷
春雨面膜孕妇可以用吗
春雨面膜含荧光剂事件
春雨好像什么流进麦田
陈春雨刘洁全
肉裔by春雨沙拉
春雨医生网页版
春雨医生医生版
春雨是什么颜色的
春雨植物图片
春雨什么成语
春雨什么什么
春雨医生下载
春雨诗句大全
春雨古诗杜甫
关于春雨的诗
春雨医生医生端
对对子春雨对什么
春雨医生好不好
春雨医生软件开发
怎样成为春雨医生
免费下载春雨医生
春雨掌上医生怎么样
春雨掌上医生app
春雨医生产品经理
如果我是春雨 我将
春雨育儿医生app
春雨医生如何
春雨会计公司
春雨医生免费吗
春雨医生电话