高并发秒杀API之业务分析与DAO

作者: 意浅离殇 | 来源:发表于2017-10-15 20:51 被阅读0次

1.秒杀业务的分析

一般的秒杀系统会存在商家，库存，用户三个实体，商家添加调整库存，库存用于发货和核账，库存用户秒杀或者预售，用户的付款，退货也会影响到库存集体如下图：

这里写图片描述

也就是秒杀业务的核心就是库存的处理。
库存业务分析：首先用户秒杀成功要相应的减去库存已经记录购买的明细，这两项操作组成了一个完整的事务。如下图：

这里写图片描述

2.难点分析的分析

主要的难点问题就是竞争多个用户同时秒杀一种商品。对于mysql 来说竞争反应到背后的技术就是事务和行级锁。
1.事务工作机制
首先是开启事务 start transaction
update 库存数量（竞争出现的地方）
insert 购买明细
commit 事务提交
2 行级锁

当一个用户执行减库存的操作时，其他用户执行该项操作时为等待状态如下图

这里写图片描述
秒杀的难点在于如何高效的处理竞争具体的解决方法会在单写一遍博客进行解释。接下来通过一个项目主要实现一下如下的秒杀功能。

这里写图片描述

3.设计数据库

因为主要只实现秒杀相关的功能这里只设置两张表。
1.秒杀库存表下面给出建表语句。


-- 创建秒杀库存表
CREATE TABLE seckill(
    `seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '商品库存id',
    `name` VARCHAR(120) NOT NULL COMMENT '商品名称',
    `number` INT NOT NULL COMMENT '库存数量',
    `start_time` TIMESTAMP NOT NULL COMMENT '秒杀开始时间',
    `end_time` TIMESTAMP NOT NULL COMMENT '秒杀结束时间',
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '秒杀创建时间',
    PRIMARY KEY (`seckill_id`),
    /*创建时间索引是为了以后时间查询的业务提供方便*/
    KEY `idx_start_time` (`start_time`),
    KEY `idx_end_time` (`end_time`),
    KEY `idx_create_time` (`create_time`)
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='秒杀库存表'

-- 初始化数据
INSERT INTO 
    seckill(name, number, start_time, end_time)
VALUES
    ('1000元秒杀iphone6', 100, '2015-11-01 00:00:00', '2018-11-02 00:00:00'),
    ('500元秒杀ipad2', 200, '2015-11-01 00:00:00', '2018-11-02 00:00:00'),
    ('300元秒杀小米4', 300, '2015-11-01 00:00:00', '2018-11-02 00:00:00'),
    ('200元秒杀红米note', 400, '2015-11-01 00:00:00', '2018-11-02 00:00:00')

秒杀成功明细表下面给出建表语句


-- 秒杀成功明细表
-- 用户登录认证相关的信息
CREATE TABLE success_killed(
    `seckill_id` BIGINT NOT NULL COMMENT '商品库存id',
    `user_phone` BIGINT NOT NULL COMMENT '用户手机号',
    `state` TINYINT NOT NULL DEFAULT -1 COMMENT '状态信息：-1无效，0成功，1已付款，2已发货',
    `create_time` TIMESTAMP NOT NULL COMMENT '创建时间',
    PRIMARY KEY (`seckill_id`, `user_phone`),/*联合主键*/
    KEY `idx_create_time` (`create_time`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='秒杀库存表'

4.DAO编码

1.创建工程
首先创建一个maven工程seckill工程目录如下

这里写图片描述

2.添加依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.wen</groupId>
  <artifactId>seckill</artifactId>
  <version>0.0.1-SNAPSHOT</version>
   <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.2.RELEASE</version>
        <relativePath/>
    </parent>
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
        <thymeleaf.version> 3.0.2.RELEASE </thymeleaf.version>
        <thymeleaf-layout-dialect.version> 2.1.1 </thymeleaf-layout-dialect.version>
        <tomcat.version>7.0.69</tomcat.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- log4j2 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <!-- web组件支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency> 
        <!-- thymeleaf模板支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!-- mybatis支持 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <!--pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!-- mysql连接池 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>
        <!-- Apache公共类库 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>
        <!-- google guava公共类库 -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>21.0</version>
        </dependency>
        <!--热部署-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- 测试依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!--添加切面支持-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.31</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <!--开发中使用devtools 打包忽略-->
                    <excludeDevtools>false</excludeDevtools>
                    <fork>true</fork>
                </configuration>
            </plugin>
        
          </plugins>
        <finalName>seckill</finalName>
    </build>
</project>

3工程配置

#数据库连接配置
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/seckill
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
  thymeleaf:
    mode: HTML5
  #字符集和json格式工具
  http:
    encoding:
      charset: utf-8
    converters:
      preferred-json-mapper: fastjson
    multipart:
      max-file-size: 10MB
  application:
    name: seckill
#mynatis配置
mybatis:
  type-aliases-package: com.wen.seckill.model
  #mapper加载路径
  mapper-locations: classpath:mapper/*.xml
  #myatbis配置文件
  config-location: classpath:mybatis-conf.xml
  
#加载log4j2
logging:
  config: classpath:log4j2.xml
  level: debug
  file:
server:
  session-timeout : 3600
  port: 80

日志配置文件

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <properties>
        <!-- 文件输出格式 -->
        <property name="PATTERN">%d{yyyy-MM-dd HH:mm:ss.SSS} |-%-5level [%thread] %c [%L] -| %msg%n</property>
    </properties>

    <appenders>
        <Console name="Console" target="system_out">
            <PatternLayout pattern="${PATTERN}" />
        </Console>
    </appenders>
    <!--配置mybatis日志-->
    <loggers>

        <logger name="log4j.logger.org.mybatis" level="debug" additivity="false">
            <appender-ref ref="Console"/>
        </logger>
        <logger name="log4j.logger.java.sql" level="debug" additivity="false">
            <appender-ref ref="Console"/>
        </logger>
        <logger name="com.wen.seckill.dao" level="debug" />
        <root level="info">
            <appenderref ref="Console" />
        </root>
    </loggers>

</configuration>

mybatis 一些功能的配置文件

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--设置mybatis日志类型-->
    <settings>
        <setting name="logImpl" value="LOG4J2"/>
        <!--配置的缓存的全局开关。-->
        <setting name="cacheEnabled" value="true"/>
        <!--延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。 特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态。-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--当没有为参数提供特定的 JDBC 类型时，为空值指定 JDBC 类型。 某些驱动需要指定列的 JDBC 类型，多数情况直接用一般类型即可，比如 NULL、VARCHAR 或 OTHER。-->
        <setting name="jdbcTypeForNull" value="NULL"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="useColumnLabel" value="true"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>    
</configuration>

4 dao层实体编写
根据表结构创建实体
库存表

import java.util.Date;
/**
 * 秒杀库存实体
 */
public class Seckill {

    private long seckillId;

    private String name;

    private int number;

    private Date startTime;

    private Date endTime;

    private Date createTime;

    public long getSeckillId() {
        return seckillId;
    }

    public void setSeckillId(long seckillId) {
        this.seckillId = seckillId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "Seckill [seckillId=" + seckillId + ", name=" + name + ", number=" + number + ", startTime=" + startTime
                + ", endTime=" + endTime + ", createTime=" + createTime + "]";
    }

}

秒杀记录表

import java.util.Date;
/**
 * 成功秒杀实体
 * 
 */
public class SuccessKilled {

    private long seckillId;

    private long userPhone;

    private short state;

    private Date creteTime;

    // 多对一的复合属性
    private Seckill seckill;

    public long getSeckillId() {
        return seckillId;
    }

    public void setSeckillId(long seckillId) {
        this.seckillId = seckillId;
    }

    public long getUserPhone() {
        return userPhone;
    }

    public void setUserPhone(long userPhone) {
        this.userPhone = userPhone;
    }

    public short getState() {
        return state;
    }

    public void setState(short state) {
        this.state = state;
    }

    public Date getCreteTime() {
        return creteTime;
    }

    public void setCreteTime(Date creteTime) {
        this.creteTime = creteTime;
    }

    public Seckill getSeckill() {
        return seckill;
    }

    public void setSeckill(Seckill seckill) {
        this.seckill = seckill;
    }

    @Override
    public String toString() {
        return "SuccessKilled [seckillId=" + seckillId + ", userPhone=" + userPhone + ", state=" + state
                + ", creteTime=" + creteTime + "]";
    }

}

4 dao层借口编写
实体类接口
主要需要的功能有减库存，秒杀列表，根据id 检索商品信息


import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import com.wen.seckill.model.Seckill;
@Mapper
public interface SeckillDao {
    /**
     * 减库存
     * @param seckillId
     * @param killTime
     * @return 如果影响的行数大于1 则表示更新库存成功
     */
    int reduceNumber(@Param("seckillId")long seckillId,@Param("killTime")Date killTime);
    /**
     * 根据id  查询秒杀对象
     * @param seckillId
     * @return 
     */
    Seckill queryById(@Param("seckillId")long seckillId);
    /**
     * 获取秒杀列表
     */
    List<Seckill> queryAll(); 
}

为接口编写相应的xml 代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wen.seckill.dao.SeckillDao">
    <!-- 减少库存操作 -->
    <update id="reduceNumber">
        update 
            seckill 
        set number=number-1
        where seckill_id=#{seckillId}
        AND start_time  &lt;=#{killTime}
        and end_time>=#{killTime}
        and number>0
    </update>
    <!-- 根据id  查询 -->
    <select id="queryById" resultType="Seckill" parameterType="long">
        select seckill_id,name,number,start_time,end_time,create_time from seckill
        where seckill_id=#{seckillId}
    </select>
        <!-- 根据id  查询 -->
    <select id="queryAll" resultType="Seckill" >
        select seckill_id,name,number,start_time,end_time,create_time from seckill
    </select>
</mapper>

秒杀接口主要需要两个功能 1插入秒杀记录 2秒杀记录检索

import java.util.Date;
/**
 * 成功秒杀实体
 * 
 */
public class SuccessKilled {

    private long seckillId;

    private long userPhone;

    private short state;

    private Date creteTime;

    // 多对一的复合属性
    private Seckill seckill;

    public long getSeckillId() {
        return seckillId;
    }

    public void setSeckillId(long seckillId) {
        this.seckillId = seckillId;
    }

    public long getUserPhone() {
        return userPhone;
    }

    public void setUserPhone(long userPhone) {
        this.userPhone = userPhone;
    }

    public short getState() {
        return state;
    }

    public void setState(short state) {
        this.state = state;
    }

    public Date getCreteTime() {
        return creteTime;
    }

    public void setCreteTime(Date creteTime) {
        this.creteTime = creteTime;
    }

    public Seckill getSeckill() {
        return seckill;
    }

    public void setSeckill(Seckill seckill) {
        this.seckill = seckill;
    }

    @Override
    public String toString() {
        return "SuccessKilled [seckillId=" + seckillId + ", userPhone=" + userPhone + ", state=" + state
                + ", creteTime=" + creteTime + "]";
    }

}

为接口编写相应的xml 代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wen.seckill.dao.SuccessKilledDao">
    <!-- 秒杀成功插入 -->
    <insert id="insertSuccessKilled">
        <!-- 主键冲突报错 -->
        insert ignore into success_killed(seckill_id,user_phone) values(#{seckillId},#{userPhone})
    </insert>
    <select id="queryByIdWithSeckill" resultType="SuccessKilled">
    <!-- 根据id  查询  successkidded 并携带Seckill  实体 -->
    <!-- 根据 mybatis  将结果映射到SuccessKilled 同时映射 seckill  属性-->
    <!-- 可以自由控制sql  -->
        select 
            sk.seckill_id,
            sk.user_phone,
            sk.create_time,
            sk.state,
            s.seckill_id "seckill.seckill_id",
            s.name "seckill.name",
            s.number "seckill.number",
            s.start_time "seckill.start_time",
            s.end_time "seckill.end_time",
            s.create_time "seckill.create_time"
            from success_killed as sk 
            inner join seckill as  s on sk.seckill_id=s.seckill_id 
            where sk.seckill_id=#{seckillId} and sk.user_phone=#{userPhone}
    </select>
</mapper>

5.单元测试

编写完相应的代码后自然要编写单元测试，测试相应的代码的正确性。
首先编写一个公用的单元测试类引入相应的测试注解配置

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes=App.class)
@WebAppConfiguration
public class BaseTest {

}

编写秒杀库存dao的单元测试给出测试数据测试秒杀库存dao中的三个方法。


public class SeckillDaoTest extends BaseTest {

    //注入Dao实现类依赖
    @Resource
    private SeckillDao seckillDao;
    
    @Test
    public void testQueryById()  {
        long id = 1000;
        try {
            Seckill seckill = seckillDao.queryById(id);
            System.out.println(seckill.getName());
            System.out.println(seckill);
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    @Test
    public void testReduceNumber() throws Exception {
        Date killTime = new Date();
        int updateCount = seckillDao.reduceNumber(1000L, killTime);
        System.out.println("updateCount=" + updateCount);
    }
    @Test
    public void testQueryAll() throws Exception  {
        List<Seckill> seckills = seckillDao.queryAll();
        for (Seckill seckill : seckills) {
            System.out.println(seckill);
        }
    }

    

}

启动junit 查看测试结果。
编写秒杀记录dao的单元测试给出测试数据测试秒杀记录dao中的二个方法。


public class SuccessKilledDaoTest extends BaseTest {

    @Resource
    private SuccessKilledDao successKilledDao;

    @Test
    public void testInsertSuccessKilled() throws Exception {
        long id = 1001;
        long phone = 13631231234L;
        int insertCount = successKilledDao.insertSuccessKilled(id, phone);
        System.out.println("insertCount=" + insertCount);
    }

    @Test
    public void testQueryByIdWithSeckill() throws Exception {
        long id = 1001;
        long phone = 13631231234L;
        SuccessKilled successKilled = successKilledDao.queryByIdWithSeckill(id, phone);
        System.out.println(successKilled);
        System.out.println(successKilled.getSeckill());
    }

}

启动junit 查看测试结果。到此dao 层就算完成了下一遍将接受service 层实现以及测试。
源码地址：https://github.com/haha174/seckill.git
文章地址： http://www.haha174.top/article/details/256198
教程地址：http://www.imooc.com/learn/587

高并发秒杀API之业务分析与DAO

1.秒杀业务的分析

2.难点分析的分析

3.设计数据库

4.DAO编码

5.单元测试

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读