本文共 11839 字，大约阅读时间需要 39 分钟。

一、大数据量统计重复问题

问题描述

上亿条数据（有重复），统计其中出现次数最多的前N个数

以上类型题目其本质其实还是属于统计类型的业务，比如：统计目前商品表中各个分类的商品数量，并排出前十的分类以及分类商品数量。

对于这种业务我们有多种不同的方案，以下是方案解释：

方案1

该类型的案例：统计目前商品表中各个分类的商品数量，并排出前十的分类以及分类商品数量。

涉及sql语句以及表此时数据量：

select count(*) as count,category_id from products group by category_id order by count desc limit 0,10;

为优化此sql语句，我们加了一个idx_category_id(category_id)的索引。但是这个索引的优化并不理想。所以我们需要另辟蹊跷。

查询效率比较低：多达45s

mysql> select count(*) as count,category_id from products group by category_id order by count desc limit 0,10;+--------+-------------+| count  | category_id |+--------+-------------+| 792262 |          44 || 792108 |          81 || 791920 |          39 || 791488 |          18 || 791354 |          95 || 791258 |          35 || 791208 |          51 || 791160 |          82 || 791095 |          29 || 791084 |          37 |+--------+-------------+10 rows in set (1 min 45.95 sec)

1. 在shell脚本中查询出需要的数据

#!/bin/bashHOME_NAME='192.168.29.1'DB_PORT='3306'DB_NAME='starsky'USER_NAME='starsky'PASSWORD='root'MYSQL_ETL="/usr/local/mysql/bin/mysql -h$HOME_NAME -P$DB_PORT $DB_NAME -s -e"HIVE_TABLE_SQL="select count(*) as count,category_id from products group by category_id order by count desc limit 0,10"HIVE_TABLE=$($MYSQL_ETL "${HIVE_TABLE_SQL}"|while read a b;do echo "$a:$b";done)DELETE_TABLE="truncate table count"$($MYSQL_ETL "${DELETE_TABLE}")for i in $HIVE_TABLEdo  count=`echo $i |cut -d: -f 1`  category_id=`echo $i |cut -d: -f 2`  INSERT_TABLE="insert into count (count,category_id)values($count,$category_id);commit;"  $($MYSQL_ETL "${INSERT_TABLE}")doneecho "已更新最新排名"

2. 将数据新增到一张统计表中
   在msql中我们新建了一张用户存储各个分类的商品数量以及排名前十的分类id

CREATE TABLE `count` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `count` bigint(255) DEFAULT NULL,  `level` int(255) DEFAULT NULL,  `category_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

id：主键索引 count:统计的数据量 level：等级 category_id:分类id

3. 定时执行shell脚本

crontab是Linux系统中的定时任务，我们可以借助他来完成延迟性的数据统计问题，比如每天的十二点进行统计。

crontab -e:进入编辑定时任务 进入按 i 键，退出保存先按ESC键，在打出冒号，输入wq保存并退出

编辑定时任务

[root@localhost bin]# crontab -e* * * * * /home/count.sh~:wq

使用crontab -l 进行查看定时任务

[root@localhost bin]# crontab -l

• • • • • /home/count.sh
          查询统计表数据
          mysql> select count() from products;
          ±---------+
          | count() |
          ±---------+
          | 87000000 |
          ±---------+
          1 row in set (28.89 sec)

mysql> select * from count;+----+--------+-------+-------------+| id | count  | level | category_id |+----+--------+-------+-------------+|  1 | 812327 |  NULL |          44 ||  2 | 812179 |  NULL |          81 ||  3 | 811903 |  NULL |          39 ||  4 | 811365 |  NULL |          35 ||  5 | 811364 |  NULL |          18 ||  6 | 811355 |  NULL |          51 ||  7 | 811334 |  NULL |          29 ||  8 | 811269 |  NULL |          95 ||  9 | 811002 |  NULL |          82 || 10 | 810996 |  NULL |          30 |+----+--------+-------+-------------+10 rows in set (0.00 sec)

注意：此时因为数据发生变化定时任务执行，使count表中的数据出现了变化，并不是与前面查询的不一致

缺点：数据不够实时。

方案2

第二种办法是使用redis消息队列来解决问题，原理是在每次新增商品，都往队列中插入一个任务，去按照分类统计商品数据量，排出前十的分类id。

数据一样也是可以新增到统计表中，也可以使用redis进行缓存。

步骤如下：

1. 新增一个商品到数据库中
2. 数据库完成新增之后程序调用队列，插入一个任务
3. 执行任务，统计数据，将结果写入统计表
4. 查询数据

二、促销商品超卖问题

秒杀或者活动商品的超卖问题是在商城项目都会存在的一个问题，这个问题也是属于高并发场景下产生的问题之一，主要是因为并发过大，导致商品出售超出库存。

mysql排它锁

   0));//最后是关闭自动提交  $pdo->setAttribute(PDO::ATTR_ERRMODE,  PDO::ERRMODE_EXCEPTION);//开启异常处理  try {    $pdo->beginTransaction();//开启事务处理    $product_id = 2;//要修改的商品id    $sql = "select * from product where id = {$product_id} for update";    $restful=$pdo->query($sql)->fetch(PDO::FETCH_ASSOC);    if (!$restful) {      $pdo->rollback();//事务回滚      echo "商品不存在：".$e->getMessage();      exit;    }else{      if ($restful['product_stock'] <= 0 ){        $pdo->rollback();//事务回滚        echo "修改库存失败,库存不足";        exit;      }else{        $version = $restful['version'];        $sql = "update product set product_stock=product_stock-1 where id = {$product_id}";        $restful=$pdo->exec($sql);        if ($restful) {          $pdo->commit();//事务提交          echo "修改库存成功";          exit;        }else{          echo "修改库存失败";          $pdo->rollback();//事务回滚          exit;        }      }    }  } catch (\Exception $e) {    $pdo->rollback();//事务回滚    echo $sql.$e->getMessage();    exit;  }} catch (\Exception $e) {  echo "数据库连接失败：".$e->getMessage();  exit;}?>

先进行商品的查询并加上一把排它锁，判断商品是否存在以及商品的库存是否是大于0的，因为在查询的时候加了排它锁，所以数据并不属于缓存查询的数据，而是磁盘中实际的，属于当前读。 如果此时其他事务进行查询，会进入锁等待，直到他得到锁为止。

三、join查询

MySQL中对于join的实现主要是通过Nest额的 Loop join算法处理的，其他数据库可能是使用hash join以及sort merge join。NL J实际上就是通过驱动表的结构及作为循环基础数据，然后讲该结果集中的数据作为过滤条件一条条第到下一个表中查询数据，最后合并结构。如果还有第三个表参与join，则把前面两个表的join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，以此往下推

优化的思路：尽可能减少 Join 语句中的 Nested Loop 的循环总次数； 如何减少 Nested Loop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是在本章第二节中的优化基本原则之一“永远用小结果集驱动大的结果集”。

优先优化 Nested Loop 的内层循环；

保证 Join 语句中被驱动表上 Join 条件字段已经被索引；

当无法保证被驱动表的 Join 条件字段被索引且内存资源充足的前提下，不要太吝惜 JoinBuffer 的设置；

1. Nested-Loop Join算法解释

Simple Nested-Loop Join

如下图，r为驱动表，s为匹配表，可以看到从r中分别取出r1、r2、…、rn去匹配s表的左右列，然后再合并数据，对s表进行了rn次访问，对数据库开销大

Index Nested-Loop Join（索引嵌套）：

这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。 在查询时，驱动表（r）会根据关联字段的索引进行查找，挡在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。 如果非驱动表（s）的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

Block Nested-Loop Join：

如果有索引，会选取第二种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

官方解释：https://www.docs4dev.com/docs/zh/mysql/5.7/reference/server-system-variables.html

用于普通索引扫描，范围索引扫描和不使用索引的连接的缓冲区的最小大小，从而执行全表扫描。通常，获得快速连接的最佳方法是添加索引。当无法添加索引时，增加 join_buffer_size 的值以获得更快的完全连接。为两个表之间的每个完整连接分配一个连接缓冲区。对于未使用索引的多个表之间的复杂连接，可能需要多个连接缓冲区。

2. join实例的优化

2.1 女性客户的数量与平均月薪 & 不同城市的客户数量与平均月薪

可以先通过explain进行分析：

explain select count(*),avg(s.monthsalary) from customers c,salary s where c.gender = 0 and c.id=s.id;+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+----------+-------------+| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows   | filtered | Extra |+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+----------+-------------+| 1  | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL         | 992340 | 10.00    | Using where || 1  | SIMPLE      | s     | eq_ref | PRIMARY       | PRIMARY | 60      | mysql12.c.id | 1      | 100.00   | NULL        |+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+----------+-------------+2 rows in set, 1 warning (0.00 sec)explain select count(*),avg(s.monthsalary) from customers c,salary s where c.id=s.id group by c.city \G;*************************** 1. row ***************************                            id: 1                   select_type: SIMPLE                         table: s                    partitions: NULL                          type: ALL                 possible_keys: PRIMARY                           key: NULL                       key_len: NULL                           ref: NULL                          rows: 980276                      filtered: 100.00                         Extra: Using temporary; Using filesort*************************** 2. row ***************************                            id: 1                   select_type: SIMPLE                         table: c                    partitions: NULL                          type: eq_ref                 possible_keys: PRIMARY                           key: PRIMARY                       key_len: 60                           ref: mysql12.s.id                          rows: 1                      filtered: 100.00                         Extra: NULL 2rows in set, 1 warning (0.00 sec)

通过如上的分析，第一个SQL使用的是customers作为了驱动表，而第二个则是使用的是salary作为驱动表。之所以选择主要是因为MySQL的优化器会先获取两个表的结构信息，根据表的大小优先选择小表然后再选择大表，但是在其中有条件的情况下会选择与根据条件刷选之后的数据表，相对来说数据会少一些 当然优化就比较简单分别对于salary与customers表建立对应的索引就可以(gender,city)和(monthsalary);

建立索引：

alter table customers add index idx_gender_city(gender,city);--建立一个性别与地区的索引alter table salary add index idx_monthsalary(monthsalary);--在薪资表中建立月薪相关索引--查看查询效率select count(*),avg(s.monthsalary) from customers c,salary s where c.gender = 0 and c.id=s.id;--3sselect count(*),avg(s.monthsalary) from customers c,salary s where c.id=s.id group by c.city;--33s

发现第二题的语句在加了索引后反而效率更低了，我们可以稍微调整下SQL语句：

结果时间 7.22s

select count(*),avg(salary.monthsalary) from customers left join salary where customers.id=salary.id group by customers.city;

2.2 列出没有手机号码，或者没有照片，或者没有年奖金的客户姓名

explain select `name` from customers,salary where customers.id=salary.id and (mobile = '0' or photo = '0' or yearbonus=0);--23s

现在改为之前的union all在前面提过的方法对于 一个表的优化方式：

select `name` from customers,salary where customers.photo = '0' and customers.id = salary.idunion allselect `name` from customers,salary where customers.mobile = '0' and customers.id = salary.idunion allselect `name` from customers,salary where salary.yearbonus = 0 and customers.id = salary.id

执行的效率不理想，可以适当调整SQL语句，如下：

select `name` from customers where photo = '0' and mobile='0'union allselect `name` from customers where id in (select id from salary where salary.yearbonus = 0);

那么这个时候我们就可以单独针对于如上的两条SQL进行优化实际上最好的优化方式是（建立的索引）

alter table customers add index idx_mobile_photo_name(mobile,photo,`name`);alter table salary add index idx_yearbonus(yearbonus);

对于idx_mobile_photo_name的解释，在where中我们对于mobile，photo，name定义了一个关联的索引对于MySQL来说；会查找where上的(所有)字段是否包含在了某一个索引中如果说存在就会生效

2.3 join查询优化思路

尽可能减少Join 语句中的Nested Loop 的循环总次数； 如何减少Nested Loop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小。 为什么？因为驱动结果集越大，意味着需要循环的次数越多，也就是说在被驱动结果集上面所需要执行的查询检索次数会越多。比如，当两个表（表A 和表B） Join 的时候，如果表A 通过WHERE 条件过滤后有10 条记录，而表B 有20 条记录。如果我们选择表A 作为驱动表，也就是被驱动表的结果集为20，那么我们通过Join 条件对被驱动表（表B）的比较过滤就会有10 次。反之，如果我们选择表B 作为驱动表，则需要有20 次对表A 的比较过滤。当然，此优化的前提条件是通过Join 条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要Join 语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化。

优先优化Nested Loop 的内层循环； 不仅仅是在数据库的Join 中应该做的，实际上在我们优化程序语言的时候也有类似的优化原则。内层循环是循环中执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源。

保证Join 语句中被驱动表上Join 条件字段已经被索引； 保证被驱动表上Join 条件字段已经被索引的目的，正是针对上面两点的考虑，只有让被驱动表的Join 条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是优化内层循环的实际优化方法。

当无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置： 当在某些特殊的环境中，我们的Join 必须是All，Index，range 或者是index_merge 类型的时候，Join Buffer 就会派上用场了。在这种情况下，Join Buffer 的大小将对整个Join 语句的消耗起到非常关键的作用。

四、 索引 icp

7.1 版本索引区别icp(Index Condition Pushdown) icp是 MySQL 中一个常用的优化，尤其是当 MySQL 需要从一张表里检索数据时。

ICP（index condition pushdown）是 MySQL 利用索引（二级索引）元组和筛字段在索引中的 WHERE 条件从表中提取数据记录的一种优化操作。

ICP 的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的 where 条件，如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。

ICP（优化器）尽可能的把 index condition 的处理从 server 层下推到存储引擎层。 注意：icp是在MySQL5.6才提出来的并且在之后的版本中一直使用的优化的方式

简单来说就是如下SQL， 使用的索引是：

idx_gender_city_name_monthsalary_yearbonus(gender,city,name,monthsalary,yearbonus)SELECT * FROM customers1s ignore index(idx_name_photo) WHERE gender = 0 AND city LIKE "长%" AND name LIKE "实%" LIMIT 0, 20;

也就是说使用了ICP就是MySQL会根据与索引上的信息对于数据进行过滤然后再获取数据返回出来，没有使用的话MySQL会根据gender = 0取出数据然后再去server层中对于后面的条件进行过滤 如下就是流程 没有使用ICP的情况如下图：

①：MySQL Server 发出读取数据的命令，调用存储引擎的索引读或全表表读。此处进行的是索引读。

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（红色的）从表记录中读出（步骤 ④，通常有 IO）。

⑤：从存储引擎返回标识的结果。 以上，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤 ③），还要进行进行步骤 ④，通常有 IO。

⑥：从存储引擎返回查找到的多条数据给 MySQL Server，MySQL Server 在 ⑦ 得到较多的元组。

⑦–⑧：依据 WHERE 子句条件进行过滤，得到满足条件的数据。 注意在 MySQL Server 层得到较多数据，然后才过滤，最终得到的是少量的、符合条件的数据。

使用icp流程

①：MySQL Server 发出读取数据的命令，过程同图一。

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足已经下推的条件的（红色的）从表记录中读出（步骤 ④，通常有 IO）；

⑤：从存储引擎返回标识的结果。 此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤 ③），还要在 ③ 这个阶段依据下推的条件进行进行判断，不满足条件的，不去读取表中的数据，直接在索引树上进行下一个索引项的判断，直到有满足条件的，才进行步骤 ④ ，这样，较没有 ICP 的方式，IO 量减少。

⑥：从存储引擎返回查找到的少量数据给 MySQL Server，MySQL Server 在 ⑦ 得到少量的数据。 因此比较图一无 ICP 的方式，返回给 MySQL Server 层的即是少量的、 符合条件的数据。

下一篇：暂未更新