mysql之查询优化探索

来源：互联网发布：linux系统工程师培训编辑：程序博客网时间：2024/06/05 03:52

今天遇到一个实际的mysql查询性能问题，试验了半天，终于摸出点门道，业务背景如下：

有如下数据库表

CREATE TABLE `tb_feature` (
`id` int(11) NOT NULL,
`feature_desc` char(255) NOT NULL,
`type_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_featuretype` (`type_id`),
KEY `feature_desc` (`feature_desc`),
CONSTRAINT `fk_featuretype` FOREIGN KEY (`type_id`) REFERENCES `tb_feature_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中 type_id 是另一张表的外键。

表中有数据1846058条，数据示例如下：

| 2015041889 | tgw__busi__zy__业务(蜀山传奇)__appid(1)__abc               |      22 |
| 2015046243 | tgw__busi__zy__业务(侠义无双)__appid(2)__abc               |      22 |
| 2015048614 | tgw__busi__zy__业务(大笑西游)__appid(3)__abc               |      22 |
| 2015052785 | tgw__busi__zy__业务(幻世仙征)__appid(6)__abc               |      22 |
| 2015079514 | tgw__busi__zy__业务(海岛大亨)__appid(7)__abc               |      22 |
| 2015086117 | tgw__busi__zy__业务(千智风声)__appid(9)__def               |      23 |
| 2015087409 | tgw__busi__zy__业务(红色火线)__appid(10)__def             |      23 |
| 2015096361 | tgw__busi__zy__业务(寻侠)__appid(11)__def                    |      23 |
| 2015096613 | tgw__busi__zy__业务(JayLi25428)__appid(13)__def         |      23 |
| 2015100282 | tgw__busi__zy__业务(北欧传说)__appid(15)__def             |      23 |

其中feature_desc组成如下： tgw__busi__zy__业务($appname)__appid($业务id)_$type

mysql> select count(*) from tb_feature;
+----------+
| count(*) |
+----------+
| 1846058 |
+----------+

查询需求场景：

type_id=22 ，且 appid固定为某一批特定值，appname未知， type=abc;

type_id=23 ，且 appid固定为某一批特定值，appname未知， type=def;

由于appname 未知，只能使用like查询，固有如下语句：

select id as ft_id, feature_desc as ft_desc from tb_feature where type_id  IN (22,23) and  feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__def' or feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__abc';

查询结果为1条数据，但是耗时 (4.04 sec)接着测试更多的批量查询， like后面跟10个匹配条件，耗时 15.23秒。

对于一个实际的业务需求来讲，这个查询速度显然不能满足需要。

使用explain 语句来跟踪这条语句：

参考百度文库explain的解释：Mysql Explain 语法详细解析

可以看出2点：

1. 没有使用索引查询，全亮数据匹配；

2.1918565 大于表总数据量。

按照设想，先根据type_id过滤一次，总数据约4261条，然后从这4261条数据中使用like匹配查询，应该是不至于需要4秒的。

将sql语句修改一下，仅仅加入一个()，

select id as ft_id, feature_desc as ft_desc from tb_feature where type_id  IN (22,23) and  (feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__def' or feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__abc');

查询耗时0..01秒，继续explain，看看其中的区别：

rows显示仅有4261行数据需要进一步使用where条件查询，说明这一次type_id起了作用，那是否意味着使用了fk_featuretype 索引呢，。

接下来继续修改查询语句：

select id as ft_id, feature_desc as ft_desc from tb_feature where type_id  IN (22,23) and  (feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__def') or (feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__abc');

查询耗时4.04秒

select id as ft_id, feature_desc as ft_desc from tb_feature where (type_id  IN (22,23)) and  ((feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__def') or (feature_desc like 'tgw__busi__zy__业务(%)__appid(1)__abc'));

查询耗时0.01秒

对于like后面跟10个匹配条件的查询，用()括起来之后耗时也仅为0.26秒。

可见，将后面的 feature_desc like 用()整体括起来，索引方能起作用。

为什么会有如此大的区别呢？条件的顺序或者一个() 到底改变了什么？

0 0