业务优化不是一步到位的

15/02/2017 00:50:37MySQL39,1844字数 11503阅读38分20秒阅读模式

准备

在项目开始的时候为了能快速的迭代开发，基本上字段都是添加在一个表上。下面我们以一个商品表为例来说明业务的变化和数据库的优化。

CREATE TABLE goods(
    goods_id INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '商品名称',
    price DECIMAL(9, 2) NOT NULL DEFAULT 0.00 COMMENT '商品价格',
    inventory INT NOT NULL DEFAULT 0 COMMENT '商品库存',
    sales_num INT NOT NULL DEFAULT 0 COMMENT '销量',
    sales_amount DECIMAL(13, 2) NOT NULL DEFAULT 0 COMMENT '销售额',
    PRIMARY KEY(goods_id)
) COMMENT = '商品表';

当然对于真正的需求商品表的字段是不可能那么少的，这边只列出一些代表性的字段进行说明/实验。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

造一些模拟数据(这些数据有些不准确，但不影响):文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

INSERT INTO goods VALUES
(NULL, 'goods_name_1', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_2', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_3', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_4', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_5', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_6', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_7', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_8', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_9', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_10', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_11', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_12', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_13', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_14', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_15', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_16', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_17', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_18', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_19', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_20', RAND()*1000, 100000, 1000, 10000000),
(NULL, 'goods_name_21', RAND()*1000, 100000, 1000, 10000000);

简单数据查询和展示

这时候一般都会有展示商品列表，并且需要进行分页的需求。这对于开发来说可谓是相当简单就一个 LIMIT 基本就能搞定。
如下显示(这边就以数据库命令行简陋的输出来代替前台页面的展示):文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

SELECT goods_id,
    name,
    price,
    inventory,
    sales_num,
    sales_amount
FROM goods LIMIT 0, 10;
+----------+---------------+--------+-----------+-----------+--------------+
| goods_id | name          | price  | inventory | sales_num | sales_amount |
+----------+---------------+--------+-----------+-----------+--------------+
|        1 | goods_name_1  | 798.75 |    100000 |      1000 |     10000000 |
|        2 | goods_name_2  | 877.35 |    100000 |      1000 |     10000000 |
|        3 | goods_name_3  | 990.49 |    100000 |      1000 |     10000000 |
|        4 | goods_name_4  | 320.39 |    100000 |      1000 |     10000000 |
|        5 | goods_name_5  | 630.51 |    100000 |      1000 |     10000000 |
|        6 | goods_name_6  | 191.38 |    100000 |      1000 |     10000000 |
|        7 | goods_name_7  |  65.36 |    100000 |      1000 |     10000000 |
|        8 | goods_name_8  | 752.65 |    100000 |      1000 |     10000000 |
|        9 | goods_name_9  | 567.18 |    100000 |      1000 |     10000000 |
|       10 | goods_name_10 | 577.96 |    100000 |      1000 |     10000000 |
+----------+---------------+--------+-----------+-----------+--------------+

表字段分类

上面的商品数据可以分为两类文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

第一类：基本属于静态的。如：name、price。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

第二类：数据进场发生改变并带有一些统计意义的。如：`inventory`、`sales_num`、`sales_amount`。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

注意：其实 inventory 这个字段应该属于另外一类。这边就不计较那么多。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

引入缓存

由于业务发展，架构的改变开始引入了缓存(redis/memcache)，一般情况下会把商家的基本信息存放在缓存中。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

这时候如果将商品的所有信息都放入缓存，由于那些实时统计的字段会影响到缓存的使用效率。这时候就应该把字段(`inventory`、`sales_num`、`sales_amount`)拆分到另外一张表中
当然将这种频繁更新的字段和静态字段分库不仅仅是为了提高缓存的利用率，也是为了将操作 MySQL 表的资源经常分散，从而降低某一张表资源挣用过热现象。
这时候就增加了一张统计表：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

CREATE TABLE goods_stat(
    goods_stat_id INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    goods_id INT NOT NULL COMMENT '商品ID',
    inventory INT NOT NULL DEFAULT 0 COMMENT '商品库存',
    sales_num INT NOT NULL DEFAULT 0 COMMENT '销量',
    sales_amount DECIMAL(13, 2) NOT NULL DEFAULT 0 COMMENT '销售额',
    PRIMARY KEY(goods_stat_id),
    INDEX idx$goods_id(goods_id)
) COMMENT = '商品统计表';

-- 将商品表(goods)中的统计数据迁移到商品统计表(goods_stat)中
INSERT INTO goods_stat
SELECT NULL,
    goods_id,
    inventory,
    sales_num,
    sales_amount
FROM goods;

-- 删除商品表统计字段(这需要和开发确认，看是否需要删除，因为这涉及到他们代码相关映射使用问题)
ALTER TABLE goods
    DROP inventory,
    DROP sales_num,
    DROP sales_amount;

修改代买满足商品列表业务文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

这时候开发只需要做一个表连接 + LIMIT 一般就可以满足展现商品列表并且分页的需求。
SQL如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

SELECT g.goods_id,
    g.name,
    g.price,
    gs.inventory,
    gs.sales_num,
    gs.sales_amount
FROM goods AS g
INNER JOIN goods_stat AS gs USING(goods_id)
LIMIT 0, 10;
+----------+---------------+--------+-----------+-----------+--------------+
| goods_id | name          | price  | inventory | sales_num | sales_amount |
+----------+---------------+--------+-----------+-----------+--------------+
|        1 | goods_name_1  | 798.75 |    100000 |      1000 |  10000000.00 |
|        2 | goods_name_2  | 877.35 |    100000 |      1000 |  10000000.00 |
|        3 | goods_name_3  | 990.49 |    100000 |      1000 |  10000000.00 |
|        4 | goods_name_4  | 320.39 |    100000 |      1000 |  10000000.00 |
|        5 | goods_name_5  | 630.51 |    100000 |      1000 |  10000000.00 |
|        6 | goods_name_6  | 191.38 |    100000 |      1000 |  10000000.00 |
|        7 | goods_name_7  |  65.36 |    100000 |      1000 |  10000000.00 |
|        8 | goods_name_8  | 752.65 |    100000 |      1000 |  10000000.00 |
|        9 | goods_name_9  | 567.18 |    100000 |      1000 |  10000000.00 |
|       10 | goods_name_10 | 577.96 |    100000 |      1000 |  10000000.00 |
+----------+---------------+--------+-----------+-----------+--------------+

可以看到完成这个列表展示也是十分的简单。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

小插曲:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

上面的商品字段的划分有经验的人一眼其实就能判断出是否需要进行分表。有时候也会碰到一些不起眼的字段其实修改的也是很频繁的。这时候就不能凭靠自己的感觉来做事了，这时候就需要用数据说话了。需要计算出表中的那些字段修改的比较多。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

那要如何计算出那些字段修改的频繁呢，这边有两个工具可以帮到你：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

第一个是wubx大神写的：https://github.com/wubx/mysql-binlog-statistic文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

第二个是笔者写的：https://github.com/daiguadaidai/mysql_binlog_stat文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

具体如何使用都有说明。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

业务又进一步发展了

由于业务的发展，一些基本的功能不能满足。为了然产品不单调，开始有了各种统计/报表。这时候什么实时计算，离线统计等等架构都慢慢的搭建了起来。
这时候开始区分一般型业务和统计型业务。为了迎合业务的在数据库设计方面也会分成统计库和基本库。
这时候的 `goods`、`goods_stat`这两个表就不在同一个库，或不在同一个实例上了。这时候对于开发来说就不能使用JOIN了。这时候就需要根据需求进行分开查询了。这也是开发很不愿意的地方，因为需要增加比较多的工作量。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

异步请求的引入

使用前台Ajax或其他异步请求技术来完成对数据进行多次查询。(下面我以Ajax技术来说明)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-business-optimize-not-one-step-ok/

第一步：查询出基本数据并且完成分页。语句如下：

SELECT goods_id,
    name,
    price
FROM goods LIMIT 0, 10;
+----------+---------------+--------+
| goods_id | name          | price  |
+----------+---------------+--------+
|        1 | goods_name_1  | 798.75 |
|        2 | goods_name_2  | 877.35 |
|        3 | goods_name_3  | 990.49 |
|        4 | goods_name_4  | 320.39 |
|        5 | goods_name_5  | 630.51 |
|        6 | goods_name_6  | 191.38 |
|        7 | goods_name_7  |  65.36 |
|        8 | goods_name_8  | 752.65 |
|        9 | goods_name_9  | 567.18 |
|       10 | goods_name_10 | 577.96 |
+----------+---------------+--------+

第二步：在前台页面先展示的就只有这些基本的数据。这时候需要通过`Jquery`技术获取每一个`goods_id`的值并且将数据异步发送到后端获取相关商品的统计数据。

最终获取的数据的SQL如下(这边以`goods_id=1`为例)：

SELECT inventory,
    sales_num,
    sales_amount
FROM goods_stat
WHERE goods_id = 1;
+-----------+-----------+--------------+
| inventory | sales_num | sales_amount |
+-----------+-----------+--------------+
|    100000 |      1000 |  10000000.00 |
+-----------+-----------+--------------+

第三步：获取到某一个商品的统计数据并且传输到了前端，之后前端通过JQuery技术就能将统计数据和基本数据拼凑在一起。

如下展示(发起一个异步请求拼凑情况):

+----------+---------------+--------+-----------+-----------+--------------+
| goods_id | name          | price  | inventory | sales_num | sales_amount |
+----------+---------------+--------+-----------+-----------+--------------+
|        1 | goods_name_1  | 798.75 |    100000 |      1000 |  10000000.00 |
|        2 | goods_name_2  | 877.35 |
|        3 | goods_name_3  | 990.49 |
|        4 | goods_name_4  | 320.39 |
|        5 | goods_name_5  | 630.51 |
|        6 | goods_name_6  | 191.38 |
|        7 | goods_name_7  |  65.36 |
|        8 | goods_name_8  | 752.65 |
|        9 | goods_name_9  | 567.18 |
|       10 | goods_name_10 | 577.96 |
+----------+---------------+--------+

所以前台如果需要完整的展现出说有的商品统计信息就需要发起 10 个异步请求，并获取数据在前台展现。
最终拼凑出完整数据如下显示：

+----------+---------------+--------+-----------+-----------+--------------+
| goods_id | name          | price  | inventory | sales_num | sales_amount |
+----------+---------------+--------+-----------+-----------+--------------+
|        1 | goods_name_1  | 798.75 |    100000 |      1000 |  10000000.00 |
|        2 | goods_name_2  | 877.35 |    100000 |      1000 |  10000000.00 |
|        3 | goods_name_3  | 990.49 |    100000 |      1000 |  10000000.00 |
|        4 | goods_name_4  | 320.39 |    100000 |      1000 |  10000000.00 |
|        5 | goods_name_5  | 630.51 |    100000 |      1000 |  10000000.00 |
|        6 | goods_name_6  | 191.38 |    100000 |      1000 |  10000000.00 |
|        7 | goods_name_7  |  65.36 |    100000 |      1000 |  10000000.00 |
|        8 | goods_name_8  | 752.65 |    100000 |      1000 |  10000000.00 |
|        9 | goods_name_9  | 567.18 |    100000 |      1000 |  10000000.00 |
|       10 | goods_name_10 | 577.96 |    100000 |      1000 |  10000000.00 |
+----------+---------------+--------+-----------+-----------+--------------+

从上面可以看到我们为了获取完整的商品列表数据总共需要花`11个`对应用服务器或数据库的请求`(1基本信息 + 10统计信息)`。
如果我们的请求并发极限在`2000`左右那么现在只能有 `182` 用户同时访问商品列表`(2000 / 11)`。
这时候就需要考虑如何增加用户并数。

合并请求，提高用户并发数

第一步(和之前一样)：查询出基本数据并且完成分页。语句如下：

SELECT goods_id,
    name,
    price
FROM goods LIMIT 0, 10;
+----------+---------------+--------+
| goods_id | name          | price  |
+----------+---------------+--------+
|        1 | goods_name_1  | 798.75 |
|        2 | goods_name_2  | 877.35 |
|        3 | goods_name_3  | 990.49 |
|        4 | goods_name_4  | 320.39 |
|        5 | goods_name_5  | 630.51 |
|        6 | goods_name_6  | 191.38 |
|        7 | goods_name_7  |  65.36 |
|        8 | goods_name_8  | 752.65 |
|        9 | goods_name_9  | 567.18 |
|       10 | goods_name_10 | 577.96 |
+----------+---------------+--------+

第二步：使用`JQuery`技术收集所有`goods_id`，并且一次性将这些`goods_id`传入到后台获取商品统计数据

最终获取的数据的SQL如下：

SELECT goods_id,
    inventory,
    sales_num,
    sales_amount
FROM goods_stat
WHERE goods_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+----------+-----------+-----------+--------------+
| goods_id | inventory | sales_num | sales_amount |
+----------+-----------+-----------+--------------+
|        1 |    100000 |      1000 |  10000000.00 |
|        2 |    100000 |      1000 |  10000000.00 |
|        3 |    100000 |      1000 |  10000000.00 |
|        4 |    100000 |      1000 |  10000000.00 |
|        5 |    100000 |      1000 |  10000000.00 |
|        6 |    100000 |      1000 |  10000000.00 |
|        7 |    100000 |      1000 |  10000000.00 |
|        8 |    100000 |      1000 |  10000000.00 |
|        9 |    100000 |      1000 |  10000000.00 |
|       10 |    100000 |      1000 |  10000000.00 |
+----------+-----------+-----------+--------------+

第三步：在前端通过JQuery技术将获得的数据进行循环拼凑。最终获得如下效果：

+----------+---------------+--------+-----------+-----------+--------------+
| goods_id | name          | price  | inventory | sales_num | sales_amount |
+----------+---------------+--------+-----------+-----------+--------------+
|        1 | goods_name_1  | 798.75 |    100000 |      1000 |  10000000.00 |
|        2 | goods_name_2  | 877.35 |    100000 |      1000 |  10000000.00 |
|        3 | goods_name_3  | 990.49 |    100000 |      1000 |  10000000.00 |
|        4 | goods_name_4  | 320.39 |    100000 |      1000 |  10000000.00 |
|        5 | goods_name_5  | 630.51 |    100000 |      1000 |  10000000.00 |
|        6 | goods_name_6  | 191.38 |    100000 |      1000 |  10000000.00 |
|        7 | goods_name_7  |  65.36 |    100000 |      1000 |  10000000.00 |
|        8 | goods_name_8  | 752.65 |    100000 |      1000 |  10000000.00 |
|        9 | goods_name_9  | 567.18 |    100000 |      1000 |  10000000.00 |
|       10 | goods_name_10 | 577.96 |    100000 |      1000 |  10000000.00 |
+----------+---------------+--------+-----------+-----------+--------------+

通过上面的将请求合并我们发现原来的 `11个`请求就变为了 `2个`请求这时候我们的用户并发就变成了 `1000` 比原来提高了`5倍`多。

注意:在第二步上我多获取了一个 goods_id 主要是为了在前端能找到相关的拼凑元素的位置(会一点开发的一般都明白)。

需求还没有完

这时候需求又增加了，商品列表需要有排序功能。可以通过 `inventory`、`sales_num`、`sales_amount`分别排序。

这样对原来获取数据的步骤就不一样了(这边以`inventory`排序为例)：

第一步：获取商品统计表信息并且对`inventory`进行排序。

SQL如下：

SELECT goods_id,
    inventory,
    sales_num,
    sales_amount
FROM goods_stat
ORDER BY inventory
LIMIT 0, 10;
+----------+-----------+-----------+--------------+
| goods_id | inventory | sales_num | sales_amount |
+----------+-----------+-----------+--------------+
|        1 |    100000 |      1000 |  10000000.00 |
|        2 |    100000 |      1000 |  10000000.00 |
|        3 |    100000 |      1000 |  10000000.00 |
|        4 |    100000 |      1000 |  10000000.00 |
|        5 |    100000 |      1000 |  10000000.00 |
|        6 |    100000 |      1000 |  10000000.00 |
|        7 |    100000 |      1000 |  10000000.00 |
|        8 |    100000 |      1000 |  10000000.00 |
|        9 |    100000 |      1000 |  10000000.00 |
|       10 |    100000 |      1000 |  10000000.00 |
+----------+-----------+-----------+--------------+

第二步：使用`JQuery`技术收集所有`goods_id`，并且一次性将这些`goods_id`传入到后台获取商品基本信息。

最终获取的数据的SQL如下：

SELECT goods_id,
    name,
    price
FROM goods
WHERE goods_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+----------+---------------+--------+
| goods_id | name          | price  |
+----------+---------------+--------+
|        1 | goods_name_1  | 798.75 |
|        2 | goods_name_2  | 877.35 |
|        3 | goods_name_3  | 990.49 |
|        4 | goods_name_4  | 320.39 |
|        5 | goods_name_5  | 630.51 |
|        6 | goods_name_6  | 191.38 |
|        7 | goods_name_7  |  65.36 |
|        8 | goods_name_8  | 752.65 |
|        9 | goods_name_9  | 567.18 |
|       10 | goods_name_10 | 577.96 |
+----------+---------------+--------+

第三步：在前端通过`JQuery`技术将获得的数据进行循环拼凑。最终获得如下效果：

+----------+---------------+--------+-----------+-----------+--------------+
| goods_id | name          | price  | inventory | sales_num | sales_amount |
+----------+---------------+--------+-----------+-----------+--------------+
|        1 | goods_name_1  | 798.75 |    100000 |      1000 |  10000000.00 |
|        2 | goods_name_2  | 877.35 |    100000 |      1000 |  10000000.00 |
|        3 | goods_name_3  | 990.49 |    100000 |      1000 |  10000000.00 |
|        4 | goods_name_4  | 320.39 |    100000 |      1000 |  10000000.00 |
|        5 | goods_name_5  | 630.51 |    100000 |      1000 |  10000000.00 |
|        6 | goods_name_6  | 191.38 |    100000 |      1000 |  10000000.00 |
|        7 | goods_name_7  |  65.36 |    100000 |      1000 |  10000000.00 |
|        8 | goods_name_8  | 752.65 |    100000 |      1000 |  10000000.00 |
|        9 | goods_name_9  | 567.18 |    100000 |      1000 |  10000000.00 |
|       10 | goods_name_10 | 577.96 |    100000 |      1000 |  10000000.00 |
+----------+---------------+--------+-----------+-----------+--------------+

如果需要对`sales_num`、`sales_amount`进行排序展现，方法是一样的。

需求是不断的

这时候业务方又要求说需要按 `inventory`、`sales_num`、`sales_amount`。这时候我生气了。用户确实有这样的需求吗？这样的需求多吗？一般只要单个查询就能满足`80%`的人的要求了吧。吧啦吧啦开始对撕了。

我的微信

微信公众号

扫一扫关注运维生存时间公众号，获取最新技术文章~

业务优化不是一步到位的

准备

简单数据查询和展示

表字段分类

引入缓存

业务又进一步发展了

异步请求的引入

合并请求，提高用户并发数

需求还没有完

需求是不断的

xtrabackup原理及实施

会用JOIN，却不懂编程的“程序员”（4）

DBA蹩脚编程能力-0007-通过拓扑批量创建主从(视频)

MySQL行转列