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

AustinDatabases

发布于 2019-08-19 23:17:01

1.7K0

发布于 2019-08-19 23:17:01

文章被收录于专栏：AustinDatabases

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

我们创建一个表

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 上有区别，下边的第一个

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 查询优化器选择索引的可能性。