【MySQL】一次200万数据的优化过程

数据库结构

 CREATE TABLE `customers1` (
      -- 身份证
      `id` char(20) NOT NULL,
      -- 姓名
      `name` varchar(20) NOT NULL,
      -- 城市名
      `city` varchar(10) NOT NULL,
      -- 性别：1（男），0（女）
      `gender` tinyint(4) NOT NULL,
      -- 出生日期
      `birthdate` date NOT NULL,
      -- 手机号
      `mobile` char(11) DEFAULT NULL,
      -- 照片
      `photo` varchar(20) DEFAULT NULL,
      -- 月薪
      `monthsalary` decimal(10,2) NOT NULL,
      -- 年奖金额
      `yearbonus` decimal(10,0) DEFAULT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

批量添加测试数据

随便扔那个框架里边执行一下就可以

    public function bulkData()
    {
        $sql = "INSERT INTO customers1(`name`,`city`,`gender`,`birthdate`,`monthsalary`) VALUES ";

        for ( $i = 1;$i < 2000000; $i++ ){
            $name = $this->getChar(3);
            $city = mt_rand(10,100);
            $gender = rand(0,1);
            $birthdate = rand(1000,2000).'-'.rand(1,12).'-'.rand(01,10);
            $monthsalary = rand(4000,5000);
            $sql.="('".$name."','".$city."','".$gender."','".$birthdate."','".$monthsalary."'),";
        }
        $sql=substr($sql,0, strlen($sql)-1 );
        DB::insert($sql);
    }

    public function getChar($num)  // $num为生成汉字的数量
    {
        $b = '';
        for ($i=0; $i<$num; $i++) {
            // 使用chr()函数拼接双字节汉字，前一个chr()为高位字节，后一个为低位字节
            $a = chr(mt_rand(0xB0,0xD0)).chr(mt_rand(0xA1, 0xF0));
            // 转码
            $b .= iconv('GB2312', 'UTF-8', $a);
        }
        return $b;
    }

需求：写出女性客户数量跟平均月薪

第一步

我们先写出完整的语句

select COUNT(*),avg(monthsalary) from customers1 where gender = 0;

执行查询：：记录一下第一次是0.68秒

使用explain分析语句

可以看到没使用到索引，进行了全表扫描

第二步拆分语句执行并分析语句

拆分后的语句都没有使用索引并且都进行了全表扫描

尝试添加索引

索引应该添加在搜索、排序、归组等操作所涉及的数据列上，那么我们先加到gender上

alter table customers1 add index gender(gender);

再次进行拆分执行
结果：count(*)这条语句查询时间明显减少了，从0.59s到0.15s
但是平均的这条语句就有点问题了，执行了大概9秒

可以看出来是avg(monthsalary)引起的，也就是说我们只需要把 select avg(monthsalary) from customers1 where gender = 0; 这个优化好了那么就可以了其实avg与count一样在MySQL操作的时候也会自动的匹配一个合适的索引，而count的默认匹配索引是主键，但是在我们上面的操作环节中因为给customers1创建了一个索引gender 这个时候count(*)在操作的时候就会以gender作为辅助索引使用。

而在上面的语句中仅仅只是根据 where gender = 0 过滤了查找的内容，但是在进行数据avg的时候这个时候就是需要去进行IO获取数据具体的数据，MySQL在辅助索引操作的时候如果无法从辅助索引中获取数据这个时候就会再去查询一级索引主键根据主键获取数据再做计算；所以为了与更好的进行monthsalary 的avg操作我们应该要给monthsalary建立一个索引

alter table customers1 add index monthsalary(monthsalary);

查看我们建立的所有索引

show indexes from customers1;

然后在执行avg(monthsalary)的这条语句

发现时间还越来越久了
还是使用了gender索引 (⊙_⊙)? 其实这就是MySQL对于一个表的索引的选择的关系，MySQL只会选择与最合适的哪一个索引而不是使用所有的索引，在上面的查询中最为合适的就是 gender

在执行总条语句

这闹心了，怎么了怎么从0.68s到18s了

删除之前所有建立的索引

alter table customers1 drop index gender;

alter table customers1 drop index monthsalary;

之前做的所有操作

在之前没有进行任何优化的时候我们的sql执行时间大概是0.68s
添加了gender索引后，count语句执行使用0.15s，avg花了9s，问题出现在了avg上
在给avg（）语句加上索引后，数据库根据最适合的索引选择了gender索引
然后进行一次索引添加时查询时间变到了18s

建立联合索引

alter table customers1 add index gender_monthsalary(gender, monthsalary);

然后查询

终于从0.68s到0.23了