nodejs
阅读 336
MySQL性能优化之准备测试数据

很多时候,我们本地的代码运行的好好的,上线后,刚开始没问题,运行一段时间后就有性能问题了,这是为什么呢?

出现这种情况,往往是本地测试数据量不够,上线后,刚开始,数据量很少,也不会有问题,运行一段时间后,量慢慢起来了,性能问题就逐渐暴露出来。

这里让我们先准备下测试数据

创建logs表

字段 类型 含义
id bigint 主键
user_id int 用户id
error_code int 错误码
message text 日志内容
created_at datetime(3) 创建时间

创建表的代码如下
createLogTable.ts

import { Connection } from 'typeorm';

export const createLogTable = async (connection: Connection) => {
    const sql = `CREATE TABLE IF NOT EXISTS logs (
        id bigint NOT NULL AUTO_INCREMENT,
        user_id int NOT NULL,
        error_code int NOT NULL,
        message text,
        created_at datetime(3) NOT NULL,
        PRIMARY KEY (id),
        KEY user_id_created_at_idx (user_id, created_at),
        KEY error_code_created_at_idx (error_code, created_at)
    )`;
    connection.manager.query(sql);
}

插入测试数据

insertLogRecord.ts

import { Connection } from 'typeorm';

const allErrorCode = [ 0, 10000, 10001, 10002, 10003, 10004, 10005, 10006, 10007, 10008 ];
const allMessage = [
    {
        "userData": {
            "coin": "1000000011650500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000", 
            "heart": "20921836959105000000000000000000000", 
            "gems": "2531", 
            "research_point": "0", 
            "avaliable_gold_treasure_chest": 0, 
            "avaliable_silver_treasure_chest": 0, 
            "gacha_silver_treasure_chest": 51, 
            "gacha_gold_treasure_chest": 23, 
            "time_ticket_1": 1, 
            "time_ticket_6": 6, 
            "time_ticket_12": 3, 
            "heart_ticket_1": 0, 
            "heart_ticket_6": 0, 
            "heart_ticket_12": 0, 
            "quest_index": 269, 
            "quest_progress": 1, 
            "penguin_photo_count": 398, 
            "penguin_reward_coin": 0, 
            "penguin_reward_heart": 53502, 
            "language": "zh-CN", 
            "quality": 1, 
            "max_offline_hour": 364, 
            "submarine_reward_ready": false, 
            "habitats": {
                "level_habitat_1": 1605, 
                "evol_habitat_1": 32, 
                "level_habitat_2": 1590, 
                "evol_habitat_2": 27, 
                "level_habitat_3": 1556, 
                "evol_habitat_3": 14, 
                "level_habitat_4": 1506, 
                "evol_habitat_4": 11, 
                "level_habitat_5": 1406, 
                "evol_habitat_5": 8, 
                "level_habitat_6": 1306, 
                "evol_habitat_6": 9, 
                "level_habitat_7": 1206, 
                "evol_habitat_7": 7, 
                "level_habitat_8": 1106, 
                "evol_habitat_8": 6, 
                "level_habitat_9": 1006, 
                "evol_habitat_9": 5, 
                "level_habitat_10": 906, 
                "evol_habitat_10": 5, 
                "level_habitat_11": 806, 
                "evol_habitat_11": 5, 
                "level_habitat_12": 605, 
                "evol_habitat_12": 3, 
                "level_habitat_13": 406, 
                "evol_habitat_13": 2, 
                "level_habitat_14": 207, 
                "evol_habitat_14": 4, 
                "level_habitat_15": 1, 
                "evol_habitat_15": 0
            }, 
            "researches": {
                "researach_1": 1, 
                "researach_2": 1, 
                "researach_3": 1, 
                "researach_4": 1, 
                "researach_5": 1, 
                "researach_6": 1, 
                "researach_7": 1, 
                "researach_8": 1, 
                "researach_9": 1, 
                "researach_10": 1, 
                "researach_11": 1, 
                "researach_12": 1, 
                "researach_13": 1, 
                "researach_14": 1, 
                "researach_15": 1, 
                "researach_16": 1, 
                "researach_17": 2, 
                "researach_18": 1, 
                "researach_19": 1, 
                "researach_20": 1, 
                "researach_21": 1, 
                "researach_22": 1, 
                "researach_23": 2, 
                "researach_24": 1, 
                "researach_25": 1, 
                "researach_26": 3, 
                "researach_27": 1, 
                "researach_28": 3, 
                "researach_29": 3, 
                "researach_30": 1, 
                "researach_31": 1, 
                "researach_32": 3, 
                "researach_33": 1, 
                "researach_34": 3, 
                "researach_35": 3, 
                "researach_36": 1, 
                "researach_37": 1, 
                "researach_38": 3, 
                "researach_39": 1, 
                "researach_40": 3, 
                "researach_41": 4, 
                "researach_42": 1, 
                "researach_43": 1, 
                "researach_44": 3, 
                "researach_45": 1, 
                "researach_46": 3, 
                "researach_47": 4, 
                "researach_48": 1, 
                "researach_49": 1, 
                "researach_50": 3, 
                "researach_51": 1, 
                "researach_52": 3, 
                "researach_53": 5, 
                "researach_54": 1, 
                "researach_55": 1, 
                "researach_56": 3, 
                "researach_57": 1, 
                "researach_58": 3, 
                "researach_59": 5, 
                "researach_60": 1, 
                "researach_61": 1, 
                "researach_62": 3, 
                "researach_63": 1, 
                "researach_64": 3, 
                "researach_65": 5, 
                "researach_66": 1, 
                "researach_67": 1, 
                "researach_68": 3, 
                "researach_69": 1, 
                "researach_70": 3, 
                "researach_71": 5, 
                "researach_72": 1, 
                "researach_73": 1, 
                "researach_74": 1
            }, 
            "penguins": {
                "count_penguin_1": 20, 
                "active_penguin_1": 20, 
                "count_penguin_2": 20, 
                "active_penguin_2": 20, 
                "count_penguin_3": 20, 
                "active_penguin_3": 20, 
                "count_penguin_4": 20, 
                "active_penguin_4": 20, 
                "count_penguin_5": 20, 
                "active_penguin_5": 20, 
                "count_penguin_6": 20, 
                "active_penguin_6": 20, 
                "count_penguin_7": 20, 
                "active_penguin_7": 20, 
                "count_penguin_8": 20, 
                "active_penguin_8": 20, 
                "count_penguin_9": 2, 
                "active_penguin_9": 2
            }, 
            "penguin_combine": { }, 
            "creatures": {
                "count_item_16": 2, 
                "active_item_16": 1, 
                "piece_item_16": 9, 
                "count_item_17": 0, 
                "active_item_17": 0, 
                "piece_item_17": 7, 
                "count_item_18": 2, 
                "active_item_18": 1, 
                "piece_item_18": 16, 
                "count_item_19": 1, 
                "active_item_19": 1, 
                "piece_item_19": 14, 
                "count_item_20": 3, 
                "active_item_20": 1, 
                "piece_item_20": 2, 
                "count_item_21": 1, 
                "active_item_21": 1, 
                "piece_item_21": 5, 
                "count_item_22": 2, 
                "active_item_22": 1, 
                "piece_item_22": 16, 
                "count_item_23": 1, 
                "active_item_23": 1, 
                "piece_item_23": 9, 
                "count_item_24": 1, 
                "active_item_24": 1, 
                "piece_item_24": 14, 
                "count_item_26": 2, 
                "active_item_26": 1, 
                "piece_item_26": 25, 
                "count_item_27": 1, 
                "active_item_27": 1, 
                "piece_item_27": 2, 
                "count_item_1": 1, 
                "active_item_1": 1, 
                "piece_item_1": 2, 
                "count_item_2": 1, 
                "active_item_2": 1, 
                "piece_item_2": 9, 
                "count_item_3": 2, 
                "active_item_3": 1, 
                "piece_item_3": 10, 
                "count_item_4": 2, 
                "active_item_4": 1, 
                "piece_item_4": 0, 
                "count_item_5": 1, 
                "active_item_5": 1, 
                "piece_item_5": 7, 
                "count_item_6": 0, 
                "active_item_6": 0, 
                "piece_item_6": 12, 
                "count_item_7": 1, 
                "active_item_7": 1, 
                "piece_item_7": 7, 
                "count_item_8": 2, 
                "active_item_8": 1, 
                "piece_item_8": 26, 
                "count_item_9": 1, 
                "active_item_9": 1, 
                "piece_item_9": 14, 
                "count_item_10": 1, 
                "active_item_10": 1, 
                "piece_item_10": 37, 
                "count_item_11": 3, 
                "active_item_11": 1, 
                "piece_item_11": 2, 
                "count_item_12": 1, 
                "active_item_12": 1, 
                "piece_item_12": 24, 
                "count_item_13": 1, 
                "active_item_13": 1, 
                "piece_item_13": 12, 
                "count_item_14": 1, 
                "active_item_14": 1, 
                "piece_item_14": 5, 
                "count_item_15": 0, 
                "active_item_15": 0, 
                "piece_item_15": 27, 
                "count_item_28": 2, 
                "active_item_28": 1, 
                "piece_item_28": 7, 
                "count_item_29": 1, 
                "active_item_29": 1, 
                "piece_item_29": 15, 
                "count_item_31": 1, 
                "active_item_31": 1, 
                "piece_item_31": 24, 
                "count_item_32": 1, 
                "active_item_32": 1, 
                "piece_item_32": 10
            }, 
            "event_creatures": {
                "ec_C1": 1, 
                "ec_A1": 1, 
                "ec_C15": 1, 
                "ec_A15": 1, 
                "ec_C16": 1, 
                "ec_A16": 1, 
                "ec_C17": 1, 
                "ec_A17": 1
            }, 
            "bonuses": {
                "bonus_1_count": 759, 
                "bonus_2_count": 398, 
                "bonus_3_count": 146
            }, 
            "achievements": {
                "achievement_1_level": 32, 
                "achievement_3_level": 32, 
                "achievement_7_level": 15, 
                "achievement_9_level": 25, 
                "achievement_10_level": 31, 
                "achievement_13_level": 7, 
                "achievement_14_level": 12, 
                "achievement_15_level": 9
            }, 
            "time": {
                "time_habitat_1": "2020-02-23|11:24:40", 
                "time_habitat_2": "2020-02-23|11:24:40", 
                "time_habitat_3": "2020-02-23|11:24:40", 
                "time_habitat_4": "2020-02-23|11:24:40", 
                "time_habitat_5": "2020-02-23|11:24:40", 
                "time_habitat_6": "2020-02-23|11:24:40", 
                "time_habitat_7": "2020-02-23|11:24:40", 
                "time_habitat_8": "2020-02-23|11:24:40", 
                "time_habitat_9": "2020-02-23|11:24:40", 
                "time_habitat_10": "2020-02-23|11:24:40", 
                "time_habitat_11": "2020-02-23|11:24:40", 
                "time_habitat_12": "2020-02-23|11:24:40", 
                "time_habitat_13": "2020-02-23|11:24:40", 
                "time_habitat_14": "2020-02-23|11:24:40", 
                "time_habitat_15": "2020-02-23|11:24:43", 
                "bonus_1_endtime": "2020-02-11|13:45:43", 
                "bonus_2_endtime": "2020-02-16|03:25:06", 
                "bonus_3_endtime": "2020-02-11|13:50:16", 
                "shop_gacha_ad_startTime": "2020-02-11|13:37:53", 
                "shop_gem_30_startTime": "2020-02-11|13:10:00"
            }, 
            "action_count": {
                "skill_use_count": 187, 
                "time_ticket_count": 24, 
                "gacha_count": 74, 
                "ad_count": 3
            }, 
            "iap": { }, 
            "christmas_event": {
                "xmas_19_vc": "0", 
                "xmas_19_t_c": 4, 
                "xmas_19_t_time": "2020-01-19|03:10:31", 
                "xmas_19_m_re": "0001-01-01|00:00:00", 
                "xmas_19_m_tier": 1, 
                "xmas_19_g_c_1": 1, 
                "xmas_19_g_c_2": 1, 
                "xmas_19_g_c_3": 0, 
                "xmas_19_g_c_4": 0, 
                "xmas_19_g_c_5": 0, 
                "xmas_19_g_c_6": 0, 
                "xmas_19_g_c_7": 0, 
                "xmas_19_g_c_8": 0, 
                "xmas_19_g_c_9": 0, 
                "xmas_19_g_c_10": 0, 
                "xmas_19_g_c_11": 0, 
                "xmas_19_g_c_12": 0, 
                "xmas_19_g_c_13": 0, 
                "xmas_19_g_c_14": 0, 
                "xmas_19_g_c_15": 0, 
                "xmas_19_g_c_16": 4, 
                "xmas_19_g_c_17": 0
            }, 
            "luckybag_event": {
                "newyear_2020_free_count": 0, 
                "newyear_2020_round1": "1,14,15,16,6,3,12,5,2,11,4,13,10,9,8,7", 
                "newyear_2020_round2": "17,2,8,7,13,10,1,9,5,6,14,12,11,4,15,3", 
                "newyear_2020_reward1": "1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1", 
                "newyear_2020_reward2": "1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1", 
                "newyear_2020_time": "2020-01-29|00:00:00", 
                "newyear_2020_paid_count": 21
            }, 
            "options": {
                "notification": true, 
                "bgm": true, 
                "fx": true
            }, 
            "saved_time": "2020-02-23|11:24:43", 
            "total_penguin_count": 162
        }, 
        "clientData": {
            "appVersion": "1.16", 
            "osVersion": "Mac OS X 10.14.6", 
            "appLanguage": "zh-CN", 
            "systemLanguage": "English", 
            "appBundle": "com.fantome.penguinisle", 
            "deviceModel": "MacBookPro12,1"
        }, 
        "requestId": "854ce94d-54f1-4a95-9bd6-f68c9fd864b5", 
        "requestTimeUTC": "2/23/2020 11:24:44 AM", 
        "saveCount": 9817, 
        "deviceId": "63DCE36B-E95D-57BC-B11A-F5F56FD727D1", 
        "socialId": "Mingli", 
        "ip": "47.74.39.127", 
        "countryCode": "CN"
    },
    {
        "clientData": {
            "appVersion": "1.16", 
            "osVersion": "Mac OS X 10.14.6", 
            "appLanguage": "zh-CN", 
            "systemLanguage": "English", 
            "appBundle": "com.fantome.penguinisle", 
            "deviceModel": "MacBookPro12,1"
        }, 
        "deviceId": "63DCE36B-E95D-57BC-B11A-F5F56FD727D1", 
        "socialId": "Mingli", 
        "ip": "47.74.47.121", 
        "countryCode": "CN"
    }
].map(msg => JSON.stringify(msg));

const now: number = Date.now();
const duration: number = 7 * 24 * 60 * 60 * 1000; // 7天的时间,单位毫秒
let userID: number = 1;

/**
 * 模拟7天内,50万个活跃用户的一亿条日志
 */
export const query = async (connection: Connection, count) => {
    const sql = 'insert into logs (user_id, error_code, message, created_at) values ';
    const sqlArr = [];
    const dataArr = [];
    let curCount = 0;
    while (true) {
        sqlArr.push('(?, ?, ?, ?)');
        const index: number = Math.floor(Math.random() * allErrorCode.length);
        // 让创建时间随机分布在7天内
        const createdAt = now - Math.floor(Math.random() * duration)
        dataArr.push(userID, allErrorCode[index], allMessage[index % 2], new Date(createdAt));
        userID++;
        // 模拟50万个活跃用户
        if (userID >= 50 * 10000) {
            userID = 1;
        }
        curCount++;
        if (curCount >= count) {
            break;
        }
    }
    await connection.manager.query(sql + sqlArr.join(','), dataArr);
};

export const insertLogRecord = async (connection) => {
    const count = 10000; // 一次插入一万条
    const length = 10000; // 插入10000次,总共插入一亿条
    for (let i = 0; i < length; i++) {
        console.time('insert ' + i);
        await query(connection, count);
        console.timeEnd('insert ' + i);
    }
};

入口文件

index.ts

import 'reflect-metadata';
import { createConnection } from 'typeorm';
import { createLogTable } from './sql/createLogTable';
import { insertLogRecord } from './sql/insertLogRecord';

createConnection().then(async connection => {
    console.time('duration: ');
    await createLogTable(connection);
    await insertLogRecord(connection);
    console.timeEnd('duration: ');
    console.log('done');
}).catch(error => console.log(error));

依赖的模块

package.json

{
   "name": "TestMySQL",
   "version": "0.0.1",
   "description": "Awesome project developed with TypeORM.",
   "devDependencies": {
      "ts-node": "3.3.0",
      "@types/node": "^8.0.29",
      "typescript": "3.3.3333"
   },
   "dependencies": {
      "typeorm": "0.2.22",
      "reflect-metadata": "^0.1.10",
      "mysql": "^2.14.1"
   },
   "scripts": {
      "start": "ts-node src/index.ts"
   }
}

ts配置

tsconfig.json

{
   "compilerOptions": {
      "lib": [
         "es5",
         "es6"
      ],
      "target": "es5",
      "module": "commonjs",
      "moduleResolution": "node",
      "outDir": "./build",
      "emitDecoratorMetadata": true,
      "experimentalDecorators": true,
      "sourceMap": true
   }
}

TypeORM配置

ormconfig.json

{
   "type": "mysql",
   "host": "localhost",
   "port": 3306,
   "username": "root",
   "password": "111111",
   "database": "my_test",
   "synchronize": false,
   "logging": "error",
   "maxQueryExecutionTime": 3600000,
   "entities": [
      "src/entity/**/*.ts"
   ],
   "migrations": [
      "src/migration/**/*.ts"
   ],
   "subscribers": [
      "src/subscriber/**/*.ts"
   ],
   "cli": {
      "entitiesDir": "src/entity",
      "migrationsDir": "src/migration",
      "subscribersDir": "src/subscriber"
   }
}

vscode运行配置

.vscode/launch.json

{
    // 使用 IntelliSense 了解相关属性。 
    // 悬停以查看现有属性的描述。
    // 欲了解更多信息,请访问: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "type": "node",
            "request": "launch",
            "name": "Launch Program",
            "runtimeArgs": [
                "--nolazy",
                "-r",
                "ts-node/register",
            ],
            "args": [
                "${workspaceFolder}/src/index.ts"
            ],
            "env": {
                "NODE_ENV": "development"
            },
            "sourceMaps": true,
            "cwd": "${workspaceFolder}",
            "protocol": "inspector",
            "console": "integratedTerminal",
            "internalConsoleOptions": "neverOpen"
        }
    ]
}
关注下面的标签,发现更多相似文章
评论
相关推荐
测试

测试...

测一测

优点:前端耗时少。因为后端拼接完了html,浏览器只需要直接渲染出来。有利于SEO(搜索引擎优化)。因为在后端有完整的html页面,所以爬虫更容易爬取获得信息,更有利于seo。无需占用客户端资源。即解...

测试

安装 socialiteproviderssocialiteproviders 为 Laravel Socialite 提供了更多的第三方登录方式,基本上你需要的,都能在这里找到。这个组件方便我们完成...

(毕设项目)Vue+Go前端后端一体化 企业级微服务网关项目完整无密云盘分享

(毕设项目)Vue+Go前端后端一体化 企业级微服务网关项目 【点击下载】不管是基于Java、Python、PHP,还是基于Go的网站,网站流量越高,对网关性能要求越高,尤其是亿级流量网站中,网关更是...

2020最新慕课网全站课程分享实战就业班全部都有云盘链接分享

*2020 慕课网所有课程全部都有,百分百高清原画,所有课程全部包含课件源码,完整无密,百度网盘链接分享,官方同步永久包更新! *官方品质,信誉保障!所有课程全部支持试看任何章节! *【点击下载】 *...

Node.js+Koa2+MySQL打造前后端分离精品项目《旧岛》完整无密 云盘分享

Node.js+Koa2+MySQL打造前后端分离精品项目《旧岛》完整无密 【点击下载】Nodejs逐渐成为前端同学进阶的必修技术之一,本课程将带大家从0到1开发一个前后端分离的精品项目《旧岛》,手把...

Learn Go in Y Minutes

转自:https://learnxinyminutes.com/docs/zh-cn/go-cn/ 发明 Go 语言是出于更好地完成工作的需要。Go 不是计算机科学的最新发展潮流,但它却提供了解决现实...

实战企业级项目 践行App重构之路

实战企业级项目 践行App重构之路 完整无密 随着企业级App功能不断累加强大,App代码质量下降、设计缺陷、难以维护、迭代困难等问题越来越突出,App的重构迭代已经成为Android工程师急需解决的...

你好,米粒

米粒小米粒小小米粒其实有时候很孤单...

大学计算机必修课新讲--编译原理+操作系统+图形学

大学计算机必修课新讲--编译原理+操作系统+图形学 无密 [【点击下载】](http://www.97yrbl.com/t-310.html)编译原理,操作系统,图形学被称为程序员的三大浪漫,不仅因为...

2020最新慕课网全站课程完整无密网盘分享

*2020 慕课网所有课程全部都有,百分百高清原画,所有课程全部包含课件源码,完整无密,百度网盘链接分享,官方同步永久包更新!*官方品质,信誉保障!所有课程全部支持试看任何章节!*下载地址:&nbsp...

asdfasdfasdf

asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf ~asdfasdfasdfasdfsadfasdfffffffffffffff...

asdfasdfasdf

tttttt...

ERP订单流程

ERP订单流程...

Nest 从入门到放弃

最近开始学 Nest 啊,据说是能跟 JAVA Spring 有的一拼的框架...

将mongo的查询结果写到文件里

数据库的连接的命令如下 mongo my-host:27017/mydb --ssl --sslCAFile my-ca-bundle.pem --username user --password p...

mark

sdsdsdsd...

1231213213112313

123123...

优秀的Node.js第三方库推荐

axiosAxios 是一个基于 Promise 的 HTTP 库,可以用在浏览器和 node.js 中 cheeriocheerio是一个nodejs实现的类似jquery核心功能的一个模块,用它可...

测试呀

广泛大使馆管道施工幅度顺丰大概岁的法国当时法国对```` 范德萨手动阀是的 范德萨士大夫是的****...