MySQL的一个坑

PS:其实也不是坑吧,主要是之前没有关注这个东西

首先看一下坑的由来:

1. 昨天运营那边反馈过来一个问题,就是手机端商品存在重复.然后开始排查,手机端的商品总数和PC上是一至的,但是手机端存在重复的商品,就意味着有的商品没有显示.已开始以为是缓存问题,在更新select的时侯,阿润老师的排序crond更新了排名,导致问题,但是后来取消了缓存,发现问题还在.

2. 最后问题定为在数据库上,我执行了如下的sql语句(也是常用的分页代码):

   SELECT * FROM `a`.`b` WHERE `ab` <= 11 AND `ac` >= 12 AND `status` = 0 AND
       `showType` IN (0, 2) ORDER BY `s` DESC LIMIT 100,20;
   SELECT * FROM `a`.`b` WHERE `ab` <= 11 AND `ac` >= 12 AND `status` = 0 AND
       `showType` IN (0, 2) ORDER BY `s` DESC LIMIT 120,20;

出现了如下的结果:

1. 注意第18行

   

2. 注意第2行

   

不出意外的出现了重复的商品.

同时我select了全表,发现第二次搜索出的第一个结果,并不是在应该在的位置(应该是第120行的,结果是118).多次重试之后,确认了问题就出在SQL上,遂临时处理.

初步怀疑走了不同的索引,遂explain了一下,发现确是同一个索引’ab’,然后就没折了.

然后在stackoverflow上提出了问题:点这

得到的结果就是,如果排序字段存在相同,则MySQL不保证这个排序值中的顺序.特奇葩了.

于是,我尝试在本地重复这个问题,按照如下的方式建表:

CREATE TABLE `mytest`.`orderByTable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sort` INT UNSIGNED NULL,
  `content` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_sort` (`sort` ASC));

按照如下方式插入:

for (int i = 0; i < 500; i++) {
    statement.execute("insert into orderByTable(`sort`, `content`) "
                       + " values (" + i % 40 + ", '" + i + "');");
}

按照如下方式模拟查寻:

select * from orderByTable where content not like '1%' and id > 10
    order by sort desc limit 30,11;
select * from orderByTable where content not like '1%' and id > 10
    order by sort desc limit 41,11;

发现,跟本没有问题嘛….遂具体原因成为了一个悬案(需要查原码了),但是,跟本原因还是在于排序字段不唯一,所以解决办法也很简单,多加一个排序字段即刻

比如上面就直接写成如下即可:

SELECT * FROM `a`.`b` WHERE `ab` <= 11 AND `ac` >= 12 AND `status` = 0
        AND `showType` IN (0, 2) ORDER BY `s` DESC, `id` ASC LIMIT 100,20;