前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MYSQL 查询条件的函数不要乱用, 与随机函数怎么走索引

MYSQL 查询条件的函数不要乱用, 与随机函数怎么走索引

作者头像
AustinDatabases
发布2019-08-19 23:17:01
1.7K0
发布2019-08-19 23:17:01
举报
文章被收录于专栏:AustinDatabases

偶然想起一事,具体的人和场景就不提了,事情是一条语句,明明是很简单的一句话,有索引,验证也是很快了,但只要在程序里面就慢的要死。后来发现是在语句后面使用了某函数,造成了问题。OK 我们来做一个测试,稍微的还原一下场景。

我们创建一个表

代码语言:javascript
复制
CREATE TABLE `rand_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg_code` varchar(20) DEFAULT NULL,
  `insert_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
创建一个存储过程来进行数据的填充
create PROCEDURE insert_data()
   begin 
 declare i int;
    declare msg int;
    declare msg_ch varchar(20);
    set i = 1;
    set msg = 1;
      while i < 10000000 do
      set msg=  floor(rand()*10000);
      set msg_ch=convert(msg,CHAR);
      insert into rand_table (msg_code,insert_date) values (msg_ch,now());
      set i=i+1;
   end while;
  end    

然后我们给这个“白开水的表”创建索引,并且查询,OK 一定是走索引的。

下面的语句目的是随机的选择一个数来匹配rand_table 中的一个字段,

select * from rand_table where msg_code = floor(rand()*1000);

结果是可以出来的,没有问题,但反过来在看看执行计划, 80几万的数据要全表扫描,这怎么看上去都不美好。

写这个语句的人,怀疑是数据的分布有问题,经过验证不是,并且都后面的filtered 看也不可能是这个问题,下图可以看到,缺失不是因为数据分布造成的问题。

那到底是怎么产生这个问题的,MYSQL 的在查询中,由于后面的函数rand() 是一个随机的函数,他反馈的也是一个随机的值,相关的对比不是获得了值后进行查询而是每一行都需要和随机值对比,虽然随机值在对比的时候应该是一致的。

我觉得我说道这里,已经有人抱着怀疑的心态,想着这人是不是在 胡说八道的心情在看这段文字了,OK 我们来验证一下。下面是两个自建的函数,就是要证明我上边说的不是胡说八道,注意两个函数没有大的区别,仅仅在

DETERMINISTIC 上有区别,下边的第一个

代码语言:javascript
复制
DELIMITER $$
create function pick_up_rand() returns int
   DETERMINISTIC
     BEGIN
     RETURN floor(rand()*1000);
     end
     $$
DELIMITER $$
create function pick_up_rand_n() returns int
        NOT  DETERMINISTIC
          BEGIN
          RETURN floor(rand()*1000);
          end
     $$

从下图看,1 证明我的观点是正确的,的确不确定的数值在MYSQL 中是要进行全表扫描的, 2 类似这样的问题,可以采用在写一个函数,并且将其确定化来满足这样的需求,同时也满足MYSQL 查询优化器选择索引的可能性。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-08-13,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 AustinDatabases 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档