ã¯ããã« ããã«ã¡ã¯ãKINTOãã¯ãããžãŒãºã®FACTORY ECéçºã°ã«ãŒãã§ããã¯ãšã³ããšã³ãžãã¢ããã£ãŠãããããã¯ã( @penpen_77777 )ã§ãã ä»åã¯WebãµãŒãã¹ã決ããããã¬ã®ã¥ã¬ãŒã·ã§ã³ã®äžã§éçãŸã§é«éåãå³ããã¥ãŒãã³ã°ããã«ãISUCONãã§åŸãç¥èãæŽ»çšããŠãFACTORYã§ãã¹ã¿ããŒã¿åæ ã«1æé30åããã£ãŠããåŠçããã£ã5åã§çµããããããã«ããæ¹æ³ã«ã€ããŠã玹ä»ããŸãã ãISUCONãã¯ãLINEã€ããŒæ ªåŒäŒç€Ÿã®åæšãŸãã¯ç»é²åæšã§ãã ISUCON is a trademark or registered trademark of LY Corporation. https://isucon.net ä»åã®èª²é¡ FACTORYã§ã¯ååãè»çš®ãªã©ã®ãã¹ã¿ããŒã¿ãExcelãã¡ã€ã«ã«åããŸãšãã ãã®Excelãã¡ã€ã«ãããšã«æ¬çªç°å¢ã®DBã«ããŒã¿ãåæ ããŠããŸã(=ãã¹ã¿åæ )ã ãã®ãã¹ã¿åæ ã«90åããã£ãŠããããã¹ã¿éçšäœæ¥ã®ããã«ããã¯ã«ãªã£ãŠããŸããã äŸãã°æ¬çªç°å¢ãžã®åæ ã®åã«æ€èšŒç°å¢ã§ãã¹ã¿ããŒã¿ã«åé¡ãªããã確èªããŠããã®ã§ããã ããŒã¿ã®èª€ãã«æ°ã¥ããŠä¿®æ£ããŠããã¹ã¿åæ ã«90åããããããããŒã¿ãæ£ããçŽãããã©ããããã«ç¢ºèªã§ããªãç¶æ³ã§ããã ããã§ããã¹ã¿åæ ãé«éåããããšã§éçšäœæ¥ã®å¹çåãå³ãããšã«ããŸããã ãã¹ã¿ããŒã¿åæ ãã¹ã¿åæ ã¯ãExcelã§ç®¡çãããŠãããã¹ã¿ããŒã¿ãå
ã«ãæçµçã«ãã¹ã¿åæ ã³ã³ãããDBã«æžã蟌ããšããæµãã«ãªã£ãŠããŸãã äžèšã®æµããå³ã«ç€ºããŸãã å³äžã§ã¯ä»¥äžã®ãããªæµãã§ãã¹ã¿åæ ãé²ã¿ãŸãã ãã¹ã¿éå¶æ
åœè
ãã忬ãšãªãExcelãã¡ã€ã«ã«è»çš®ãååæ
å ±ãå
¥åãã åºæ¥äžãã£ãExcelãã¡ã€ã«ããã¹ã¿ç®¡çããŒã«ã«ã¢ããããŒããã ãã¹ã¿ç®¡çããŒã«ãããªããŒã·ã§ã³ããããåé¡ãããã°æ
åœè
ã«éç¥ãã Excelãã¢ããããŒãããããšè£ã§Lambda颿°ãå®è¡ãããExcelãã¡ã€ã«ããCSVãã¡ã€ã«ã«å€æããã DBã«åæ ãããæ®µéã§ããã¹ã¿ããŒã¿ãFACTORYæ¬äœã«é£æºãããããCSVãã¬ããªã±ãŒã·ã§ã³ãã±ããã«ä¿åãã ã¬ããªã±ãŒã·ã§ã³ãã±ããã«ãã¡ã€ã«ãä¿åããããšFACTORYæ¬äœã§ã¹ããŒããã·ã³ãèµ·åãããã¹ã¿åæ ã³ã³ãããèµ·åãã ãã¹ã¿åæ ã³ã³ãããCSVãèªã¿åã£ãŠSQLãçµã¿ç«ãŠãDBã®åããŒãã«ã«ã¬ã³ãŒããèªã¿æžããã ä»åé«éåã®å¯Ÿè±¡ãšããã®ã¯ã7ã®ãã¹ã¿åæ ã³ã³ããã®åŠçã§ãã ããã©ãŒãã³ã¹ãã¥ãŒãã³ã°ãã©ã®ããã«é²ããã远äœéšãã ä»åã®ãã¹ã¿åæ ã«é¢ããããã©ãŒãã³ã¹åé¡ã«ã€ããŠã©ã®ããã«è§£æ±ºããããµã³ãã«ã³ãŒãã§èŠãŠãããŸãããã å®éã®ãã¹ã¿åæ åŠçã¯Kotlinã§èšè¿°ãããŠããŸããããµã³ãã«ã³ãŒãã®æ¹ã§ã¯çè
ãæ
£ããŠããGoã䜿ããŸãã ãŸãã䜿çšãããã¹ã¿ããŒã¿ã¯FACTORYã®å®éã«äœ¿ãããŠããããŒã¿ã§ã¯ãããŸããã ã§ãããäŒŒãæ§é ã®ãã¹ã¿ããŒã¿ã䜿ãã®ã§ãå®éã«çè
ãè¡ã£ãããã©ãŒãã³ã¹ãã¥ãŒãã³ã°ãšåãæ¹æ³ã§é«éåã§ããŸãã ãããããããã°çãããæãåãããªãã詊ããŠã¿ãŠãã ããã å
¥å ECãµã€ãã§ç®¡çããŠããååããŒã¿ãåæ ããããšèããŠã¿ãŸãããã 衚ã§ã¯çç¥ããŠããŸãããå
šéšã§50äžä»¶çšåºŠã®ããŒã¿ãšãªããŸã product_code ååãäžæã«èå¥ããã³ãŒã product_name ååã®è¡šç€ºå category_code ååãå±ããã«ããŽãªã®ã³ãŒã supplier_code ä»å
¥å
ã³ãŒã status_code ååã®è²©å£²ç¶æ
unit_price å䟡ïŒåïŒ P1001 ããŒã«ãã³ é» CAT01 SUP01 active 150 P1002 ããŒã«ãã³ èµ€ CAT01 SUP01 active 150 P1003 ã·ã£ãŒããã³ã·ã« CAT01 SUP02 discontinued 300 P2001 A4ã³ããŒçšçŽ 500æ CAT02 SUP03 active 450 P2002 A3ã³ããŒçšçŽ 500æ CAT02 SUP03 active 780 人éã«ãšã£ãŠåãããããããã«è¡šã§ç€ºããŸããããã·ã¹ãã ã«ã¯csvã®åœ¢ã§å
¥åãããŸãã product_code,product_name,category_code,supplier_code,status_code,unit_price P1001,ããŒã«ãã³ é»,CAT01,SUP01,active,150 P1002,ããŒã«ãã³ èµ€,CAT01,SUP01,active,150 P1003,ã·ã£ãŒããã³ã·ã«,CAT01,SUP02,discontinued,300 P2001,A4ã³ããŒçšçŽ 500æ,CAT02,SUP03,active,450 P2002,A3ã³ããŒçšçŽ 500æ,CAT02,SUP03,active,780 åºå å
¥åãããããŒã¿ã以äžã®ããã« product ããŒãã«ã«å
¥ããããšã«ããŸãã category_codeãsupplier_codeãstatus_codeã¯å€éšããŒãã«ã§ä¿æãããå€ãšãªããããidã«å€æããäžã§ä¿åãããŸãã å€éšããŒãã«ã«ã¯ãã§ã«ã¬ã³ãŒããåæ ãããŠãããšããŸãã product_id product_code product_name category_id supplier_id status_id unit_price 1 P1001 ããŒã«ãã³ é» 1 1 1 150 2 P1002 ããŒã«ãã³ èµ€ 1 1 1 150 3 P1003 ã·ã£ãŒããã³ã·ã« 1 2 2 300 4 P2001 A4ã³ããŒçšçŽ 500æ 2 3 1 450 5 P2002 A3ã³ããŒçšçŽ 500æ 2 3 1 780 erDiagram Product { string product_id PK "ååID" string product_code UK "ååã³ãŒã" string product_name "ååå" string category_id FK "ã«ããŽãªID" string supplier_id FK "ä»å
¥å
ID" string status_id FK "ã¹ããŒã¿ã¹ID" int unit_price "å䟡ïŒåïŒ" } Category { string category_id PK "ã«ããŽãªID" string category_code UK "ã«ããŽãªã³ãŒã" string category_name "ã«ããŽãªå" } Supplier { string supplier_id PK "ä»å
¥å
ID" string supplier_code UK "ä»å
¥å
ã³ãŒã" string supplier_name "ä»å
¥å
å" } Status { string status_id PK "ã¹ããŒã¿ã¹ID" string status_code UK "ã¹ããŒã¿ã¹ã³ãŒã" string status_name "ã¹ããŒã¿ã¹å" } Category ||--o{ Product : "has" Supplier ||--o{ Product : "supplies" Status ||--o{ Product : "applies" æ¹ååã®ã³ãŒã ãµã³ãã«ã³ãŒãã®å
šäœæ§æã以äžã®å³ã«ç€ºããŸãã ãã³ãºãªã³ããµã¯ããšã§ããããã«ãã¹ãããŒã¿ã®æºåçã®å¿
èŠãªäœæ¥ãè¡ã£ãã®ã¡ãæ¬é¡ã®ãã¹ã¿åæ ãå®è¡ãããããã«ãªã£ãŠããŸããtestcontainersã§MySQLã³ã³ãããèµ·åããã¹ãçšã®CSVãçæããåŸãmain.goããã®CSVãèªã¿åã£ãŠDBã«ãã¹ã¿åæ ãè¡ããŸãã ä»å䜿çšãããµã³ãã«ã³ãŒãã以äžã«ç€ºããŸãã以äžã®4ã€ã®ã³ãŒããåããã£ã¬ã¯ããªã«é
眮ããŠãã ããã :::details main.go (æ¹å察象ã®ã³ãŒã) package main import ( "context" "fmt" "log" "os" "time" _ "github.com/go-sql-driver/mysql" "github.com/gocarina/gocsv" "github.com/jmoiron/sqlx" ) func main() { ctx := context.Background() // MySQLã³ã³ãããèµ·å connStr, cleanup, err := startMySQLContainer(ctx) if err != nil { log.Fatal(err) } defer cleanup() db, err := sqlx.Open("mysql", connStr) if err != nil { log.Fatal(err) } defer db.Close() // ããŒãã«ã»ãã¹ã¿ãŒããŒã¿ãäœæ if err := setupTables(db); err != nil { log.Fatal(err) } // ãµã³ãã«CSVãçæïŒ50äžè¡ïŒ csvFilename := "data.csv" if err := generateSampleCSV(csvFilename, 500000); err != nil { log.Fatal(err) } // 1. CSVãèªã¿åã file, err := os.Open(csvFilename) if err != nil { log.Fatal(err) } defer file.Close() var products []Product if err := gocsv.UnmarshalFile(file, &products); err != nil { log.Fatal(err) } fmt.Printf("CSVèªã¿èŸŒã¿å®äº: %d è¡\n", len(products)) importStart := time.Now() for i, product := range products { // 2. èªãã§ãªãè¡ãããã°1è¡èªã¿åãããªããã°çµäº lineNum := i + 2 // 3. category_codeãcategory_idã«å€æ var category Category if err := db.Get( &category, `SELECT * FROM categories WHERE code = ?`, product.CategoryCode, ); err != nil { log.Fatalf("è¡ %d: category_code %q ã®æ€çŽ¢ã«å€±æ: %v", lineNum, product.CategoryCode, err) } // 4. supplier_codeãsupplier_idã«å€æ var supplier Supplier if err := db.Get( &supplier, `SELECT * FROM suppliers WHERE code = ?`, product.SupplierCode, ); err != nil { log.Fatalf("è¡ %d: supplier_code %q ã®æ€çŽ¢ã«å€±æ: %v", lineNum, product.SupplierCode, err) } // 5. status_codeãstatus_idã«å€æ var status Status if err := db.Get( &status, `SELECT * FROM statuses WHERE code = ?`, product.StatusCode, ); err != nil { log.Fatalf("è¡ %d: status_code %q ã®æ€çŽ¢ã«å€±æ: %v", lineNum, product.StatusCode, err) } // 6. ProductRowã«å€æ row := ProductRow{ ProductCode: product.ProductCode, ProductName: product.ProductName, CategoryID: category.ID, SupplierID: supplier.ID, StatusID: status.ID, UnitPrice: product.UnitPrice, } // 7. UPDATEæãå®è¡ãã result, err := db.NamedExec(` UPDATE products SET product_name = :product_name, category_id = :category_id, supplier_id = :supplier_id, status_id = :status_id, unit_price = :unit_price WHERE product_code = :product_code`, row, ) if err != nil { log.Fatalf("è¡ %d: productsã®æŽæ°ã«å€±æ: %v", lineNum, err) } rowsAffected, err := result.RowsAffected() if err != nil { log.Fatalf("è¡ %d: æŽæ°ä»¶æ°ã®ååŸã«å€±æ: %v", lineNum, err) } // 8. UPDATE察象ããªããã°INSERTãã if rowsAffected == 0 { _, err = db.NamedExec(` INSERT INTO products (product_code, product_name, category_id, supplier_id, status_id, unit_price) VALUES (:product_code, :product_name, :category_id, :supplier_id, :status_id, :unit_price)`, row, ) if err != nil { log.Fatalf("è¡ %d: productsã®ç»é²ã«å€±æ: %v", lineNum, err) } } if (lineNum-1)%1000 == 0 { rate := float64(lineNum-1) / time.Since(importStart).Seconds() fmt.Printf("鲿: %d / %d è¡ (%.0f è¡/ç§)\n", lineNum-1, len(products), rate) } // 9. 2ã«æ»ã } fmt.Printf("å®äº: %d è¡ (æèŠæé: %v)\n", len(products), time.Since(importStart)) } ::: :::details models.go (csv, dbãæäœããã®ã«å¿
èŠãªæ§é äœãå®çŸ©) package main type Product struct { ProductCode string `csv:"product_code"` ProductName string `csv:"product_name"` CategoryCode string `csv:"category_code"` SupplierCode string `csv:"supplier_code"` StatusCode string `csv:"status_code"` UnitPrice int `csv:"unit_price"` } type Category struct { ID int `db:"id"` Code string `db:"code"` Name string `db:"name"` } type Supplier struct { ID int `db:"id"` Code string `db:"code"` Name string `db:"name"` } type Status struct { ID int `db:"id"` Code string `db:"code"` Name string `db:"name"` } type ProductRow struct { ProductCode string `db:"product_code"` ProductName string `db:"product_name"` CategoryID int `db:"category_id"` SupplierID int `db:"supplier_id"` StatusID int `db:"status_id"` UnitPrice int `db:"unit_price"` } ::: :::details setup.goïŒDBåæåã»CSVçæïŒ package main import ( "context" "encoding/csv" "fmt" "math/rand" "os" "strconv" "time" "github.com/jmoiron/sqlx" "github.com/testcontainers/testcontainers-go" "github.com/testcontainers/testcontainers-go/modules/mysql" "github.com/testcontainers/testcontainers-go/wait" ) func startMySQLContainer(ctx context.Context) (connStr string, cleanup func(), err error) { mysqlContainer, err := mysql.Run(ctx, "mysql:8.0", mysql.WithDatabase("testdb"), mysql.WithUsername("user"), mysql.WithPassword("password"), testcontainers.WithWaitStrategyAndDeadline(3*time.Minute, wait.ForListeningPort("3306/tcp"). WithStartupTimeout(3*time.Minute), ), ) if err != nil { return "", nil, err } connStr, err = mysqlContainer.ConnectionString(ctx) if err != nil { _ = mysqlContainer.Terminate(ctx) return "", nil, err } cleanup = func() { _ = mysqlContainer.Terminate(ctx) } return connStr, cleanup, nil } func generateSampleCSV(filename string, rows int) error { file, err := os.Create(filename) if err != nil { return err } defer file.Close() writer := csv.NewWriter(file) defer writer.Flush() if err := writer.Write([]string{"product_code", "product_name", "category_code", "supplier_code", "status_code", "unit_price"}); err != nil { return err } categoryCodes := []string{"CAT01", "CAT02", "CAT03"} supplierCodes := []string{"SUP01", "SUP02", "SUP03"} statusCodes := []string{"active", "discontinued", "pending"} for i := 0; i < rows; i++ { record := []string{ fmt.Sprintf("P%d", 1000+i+1), fmt.Sprintf("åå_%d", i+1), categoryCodes[rand.Intn(len(categoryCodes))], supplierCodes[rand.Intn(len(supplierCodes))], statusCodes[rand.Intn(len(statusCodes))], strconv.Itoa(rand.Intn(10000) + 100), } if err := writer.Write(record); err != nil { return err } } return nil } func setupTables(db *sqlx.DB) error { tables := []string{ `CREATE TABLE IF NOT EXISTS categories ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(10) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL )`, `CREATE TABLE IF NOT EXISTS suppliers ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(10) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL )`, `CREATE TABLE IF NOT EXISTS statuses ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL )`, `CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, product_code VARCHAR(50) UNIQUE NOT NULL, product_name VARCHAR(255) NOT NULL, category_id INT NOT NULL, supplier_id INT NOT NULL, status_id INT NOT NULL, unit_price INT NOT NULL, FOREIGN KEY (category_id) REFERENCES categories(id), FOREIGN KEY (supplier_id) REFERENCES suppliers(id), FOREIGN KEY (status_id) REFERENCES statuses(id) )`, } for _, table := range tables { if _, err := db.Exec(table); err != nil { return err } } masterData := []string{ `INSERT IGNORE INTO categories (code, name) VALUES ('CAT01', 'ææ¿å
·'), ('CAT02', 'é£å'), ('CAT03', 'é»å補å')`, `INSERT IGNORE INTO suppliers (code, name) VALUES ('SUP01', 'æ ªåŒäŒç€ŸAåäº'), ('SUP02', 'æ ªåŒäŒç€ŸBç£æ¥'), ('SUP03', 'æ ªåŒäŒç€ŸCç©ç£')`, `INSERT IGNORE INTO statuses (code, name) VALUES ('active', '販売äž'), ('discontinued', '販売çµäº'), ('pending', '販売æºåäž')`, } for _, data := range masterData { if _, err := db.Exec(data); err != nil { return err } } return nil } ::: :::details go.mod module csv-import-example go 1.24.5 require ( github.com/go-sql-driver/mysql v1.9.3 github.com/gocarina/gocsv v0.0.0-20240520201108-78e41c74b4b1 github.com/jmoiron/sqlx v1.4.0 github.com/testcontainers/testcontainers-go v0.40.0 github.com/testcontainers/testcontainers-go/modules/mysql v0.40.0 ) require ( dario.cat/mergo v1.0.2 // indirect filippo.io/edwards25519 v1.1.0 // indirect github.com/Azure/go-ansiterm v0.0.0-20210617225240-d185dfc1b5a1 // indirect github.com/Microsoft/go-winio v0.6.2 // indirect github.com/cenkalti/backoff/v4 v4.3.0 // indirect github.com/containerd/errdefs v1.0.0 // indirect github.com/containerd/errdefs/pkg v0.3.0 // indirect github.com/containerd/log v0.1.0 // indirect github.com/containerd/platforms v0.2.1 // indirect github.com/cpuguy83/dockercfg v0.3.2 // indirect github.com/davecgh/go-spew v1.1.1 // indirect github.com/distribution/reference v0.6.0 // indirect github.com/docker/docker v28.5.1+incompatible // indirect github.com/docker/go-connections v0.6.0 // indirect github.com/docker/go-units v0.5.0 // indirect github.com/ebitengine/purego v0.8.4 // indirect github.com/felixge/httpsnoop v1.0.4 // indirect github.com/go-logr/logr v1.4.3 // indirect github.com/go-logr/stdr v1.2.2 // indirect github.com/go-ole/go-ole v1.2.6 // indirect github.com/google/uuid v1.6.0 // indirect github.com/grpc-ecosystem/grpc-gateway/v2 v2.27.7 // indirect github.com/klauspost/compress v1.18.0 // indirect github.com/lufia/plan9stats v0.0.0-20211012122336-39d0f177ccd0 // indirect github.com/magiconair/properties v1.8.10 // indirect github.com/moby/docker-image-spec v1.3.1 // indirect github.com/moby/go-archive v0.1.0 // indirect github.com/moby/patternmatcher v0.6.0 // indirect github.com/moby/sys/sequential v0.6.0 // indirect github.com/moby/sys/user v0.4.0 // indirect github.com/moby/sys/userns v0.1.0 // indirect github.com/moby/term v0.5.0 // indirect github.com/morikuni/aec v1.0.0 // indirect github.com/opencontainers/go-digest v1.0.0 // indirect github.com/opencontainers/image-spec v1.1.1 // indirect github.com/pkg/errors v0.9.1 // indirect github.com/pmezard/go-difflib v1.0.0 // indirect github.com/power-devops/perfstat v0.0.0-20210106213030-5aafc221ea8c // indirect github.com/shirou/gopsutil/v4 v4.25.6 // indirect github.com/sirupsen/logrus v1.9.3 // indirect github.com/stretchr/testify v1.11.1 // indirect github.com/tklauser/go-sysconf v0.3.12 // indirect github.com/tklauser/numcpus v0.6.1 // indirect github.com/yusufpapurcu/wmi v1.2.4 // indirect go.opentelemetry.io/auto/sdk v1.2.1 // indirect go.opentelemetry.io/contrib/instrumentation/net/http/otelhttp v0.49.0 // indirect go.opentelemetry.io/otel v1.38.0 // indirect go.opentelemetry.io/otel/metric v1.38.0 // indirect go.opentelemetry.io/otel/sdk v1.38.0 // indirect go.opentelemetry.io/otel/trace v1.38.0 // indirect golang.org/x/crypto v0.43.0 // indirect golang.org/x/sys v0.38.0 // indirect google.golang.org/grpc v1.78.0 // indirect google.golang.org/protobuf v1.36.11 // indirect gopkg.in/yaml.v3 v3.0.1 // indirect ) ::: é«éåããããã«main.goãæ¹åããŠãããŸãã main.goã®åŠçã®æµãããŸãšãããšä»¥äžã®éãã§ãã csvãèªã¿åã product_code,product_name,category_code,supplier_code,status_code,unit_price P1001,ããŒã«ãã³ é»,CAT01,SUP01,active,150 P1002,ããŒã«ãã³ èµ€,CAT01,SUP01,active,150 ... èªãã§ãªãè¡ãããã°1è¡èªã¿åãããªããã°çµäº P1001,ããŒã«ãã³ é»,CAT01,SUP01,active,150 category_codeãcategory_idã«å€æ SELECT * FROM categories WHERE code = 'CAT01' -- => id=1, code='CAT01', name='ææ¿å
·' supplier_codeãsupplier_idã«å€æ SELECT * FROM suppliers WHERE code = 'SUP01' -- => id=1, code='SUP01', name='æ ªåŒäŒç€ŸAåäº' status_codeãstatus_idã«å€æ SELECT * FROM statuses WHERE code = 'active' -- => id=1, code='active', name='販売äž' ProductRowã«å€æ UPDATEæãå®è¡ãã UPDATE products SET product_name = 'ããŒã«ãã³ é»', category_id = 1, supplier_id = 1, status_id = 1, unit_price = 150 WHERE product_code = 'P1001' UPDATE察象ããªããã°INSERTãã INSERT INTO products (product_code, product_name, category_id, supplier_id, status_id, unit_price) VALUES ('P1001', 'ããŒã«ãã³ é»', 1, 1, 1, 150) 2ã«æ»ã å®è¡ããŠã¿ã ãŸãã¯çŸç¶ãææ¡ããããåæ ã«ã©ããããæéãããããã¿ãŠã¿ãŸãããã testcontainersã§MySQLã³ã³ãããèµ·åãããããäºåã«Docker Desktopãèµ·åããŠãããŠãã ããã ãŸããäŸåããã±ãŒãžãååŸããããã« go mod tidy ãå®è¡ããŠãã go run . ãå®è¡ããŸãã go mod tidy go run . ãã®ã³ãŒããå®è¡ããŠã¿ããšä»¥äžã®ãããªå®è¡çµæãåŸãããŸãã ãªããšDBãžã®åæ ã«47åããã£ãŠããŸããŸããã $ go run . CSVèªã¿èŸŒã¿å®äº: 500000 è¡ é²æ: 1000 / 500000 è¡ (338 è¡/ç§) 鲿: 2000 / 500000 è¡ (329 è¡/ç§) 鲿: 3000 / 500000 è¡ (320 è¡/ç§) 鲿: 4000 / 500000 è¡ (326 è¡/ç§) 鲿: 5000 / 500000 è¡ (328 è¡/ç§) 鲿: 6000 / 500000 è¡ (328 è¡/ç§) 鲿: 7000 / 500000 è¡ (329 è¡/ç§) 鲿: 8000 / 500000 è¡ (328 è¡/ç§) 鲿: 9000 / 500000 è¡ (319 è¡/ç§) ... 鲿: 500000 / 500000 è¡ (176 è¡/ç§) å®äº: æå 500000 è¡, ãšã©ãŒ 0 è¡ (æèŠæé: 47m23.503716s) å®éã®FACTORYã®ãã¹ã¿åæ ã®è² è·ç¶æ³ å®éã®FACTORYã§ã®æ¬çªç°å¢ãžã®åæ ã§ã¯90åãã®æéãããã£ãŠããŸããã FACTORYæ¬çªã®RDSã§ã®è² è·ãèšæž¬ããããã以äžã«Database Insightsã®çµæã瀺ããŸãã å³ã§ã¯ã¯ãšãªå¥ã«AAS(å¹³åã¢ã¯ãã£ãã»ãã·ã§ã³)ã瀺ãããAASãé«ãé ã«äžŠãã§ããŸãã AASãé«ãã»ã©DBã«è² è·ãããã£ãŠãããäœãã»ã©DBã«è² è·ãããã£ãŠããªããšããããã«è§£éããã°okã§ãã èµ€æ ããã¹ã¿åæ æã«å®è¡ãããŠããSQLã«ãªããŸããã ç¹å®ã®ããŒãã«ã«å¯ŸããSELECTã®å®è¡åæ°ãå€ã(1ç§ãããã«200åçšåºŠå®è¡ãããŠãã) SELECTãããè² è·ã¯å°ãããã®ã®ãUPDATEãåçšåºŠã®é »åºŠã§å®è¡ãããŠãã ãã®ããã«èšæž¬ã®çµæããã¹ã¿åæ æã«å©ãããSQLãç¹ã«SELECTãåå ã ãªãšããããã«èŠåœãã€ããæ¹åãé²ããŠãããŸããã åå ãæ¢ã ããã ãã®æéããããåå ãæ¢ã£ãŠã¿ãŸãããã ããã§ã¯ã³ãŒãäžã§å®è¡ãããã¯ãšãªã«çç®ããŠã¿ãŸãã å®è¡ãããŠããã¯ãšãªã¯ä»¥äžã®éãã§ãã # ã¯ãšãª ã«ãŒãäž(å) åèš(å) 1 SELECT * FROM categories WHERE code = ? 1 à 50äžã«ãŒã = 50äž 50äž 2 SELECT * FROM suppliers WHERE code = ? 1 à 50äžã«ãŒã = 50äž 50äž 3 SELECT * FROM statuses WHERE code = ? 1 à 50äžã«ãŒã = 50äž 50äž 4 UPDATE products SET ... WHERE product_code = ? 1 à 50äžã«ãŒã = 50äž 50äž 5 INSERT INTO products (...) VALUES (...) æå€§1 à 50äžã«ãŒã = æå€§50äž æå€§50äž åèš æå€§250äž æå€§250äž 1ã«ãŒããããã®å®è¡åæ°ã¯å°ãªãã§ãããä»åã¯CSVã50äžè¡ããããšãã50äžã«ãŒãå®è¡ãããæå€§ã§åèš250äžã¯ãšãªå®è¡ãããããšã«ãªããŸãã å®è¡ãããã¯ãšãªãå€ããšãã€ã³ããã¯ã¹ã貌ã£ãŠåäœã®ã¯ãšãªãé«éã«ãããšããŠããã¡ãã€ãã§é
ããªã£ãŠããŸããŸãã ç¹ã«DBã¯å¥ãµãŒãã«åé¢ãããããšãå€ãããããã¯ãŒã¯ã®é信垯åã®åœ±é¿ãåããŠããŸããŸãã ãªã®ã§é«éåã®æ¹éãšããŠã¯å®è¡ãããã¯ãšãªãããã«åæžããããšããããšãèããã°è¯ãããã§ãã å®è¡ãããã¯ãšãªãåæžããããã«ã¯ïŒ SELECTç·š å®è¡ãããã¯ãšãªãåæžããã«ã¯ããã€ãã®ææ®µããããŸããããŸãã¯ãªã³ã¡ã¢ãªãã£ãã·ã¥ãåãäžããŠã¿ãããšæããŸãã ãªã³ã¡ã¢ãªãã£ãã·ã¥ã¯ãæéã®ãããåŠçã®å®è¡çµæããããããã¡ã¢ãªäžã«ä¹ã£ããŠããŸããçµæãæ¬²ããæã«ã¯ã¡ã¢ãªäžã®ããŒã¿ããåŒã£åŒµãåºãããšã§é«éåããææ³ã§ããISUCONã§ã¯åžžå¥ææ®µãšãã£ãŠãè¯ãã»ã©å
žåçãªãã¿ãŒã³ã§ãã ä»åã§ãããšæéã®ãããåŠçãšã¯DBãžã®åãåããã«ããããŸãã ãªã³ã¡ã¢ãªã§ãã£ãã·ã¥ããã«ã¯ããã£ãã·ã¥å¯Ÿè±¡ã®ããŒã¿ãããã£ãã·ã¥äžã«æžãæããããªãã»ããå®è£
ããããã§ãã ãã£ãã·ã¥äžã«å®ããŒã¿ã«æžã蟌ã¿ãããå Žåããã£ãã·ã¥ãæžã蟌ã¿ã«è¿œéãããããããŒã¿ã®æŽæ°ãå¿
èŠã«ãªããŸããæä»å¶åŸ¡ãèæ
®ããå¿
èŠããããå®è£
ãå°é£ã«ãªããŸãã productsããŒãã«ãæŽæ°ããéã«ã¯categories, suppliers, statusesããŒãã«ã¯ãã§ã«æŽæ°ãå®äºããŠãããæžã蟌ã¿ã¯ãããŸããããªã®ã§productsããŒãã«ãæŽæ°ããåã«ãã£ãã·ã¥ããŠããã°åé¡ãªãããã§ãã ãšããããšã§å
ã»ã©ã®ã³ãŒãã«ãã£ãã·ã¥åŠçãå ããŸãã CSVèªã¿åãçŽåŸã«SELECTãè¡ãå
šä»¶ãã¡ã¢ãªäžã«èŒããŸãã codeâIDãžé«éã«ããŒã¿ãåŒãããã®ã§ãã¹ã©ã€ã¹ã§ã¯ãªãããã§ã¯ map[string]int ã«èŒããŠãããŸããmapåã¯ããŒã«ã²ãã¥ãããŒã¿ã®ååŸã§$O(1)$ã®èšç®éã§é«éã«ããŒã¿ãåŒãããšãã§ããŸãã fmt.Printf("CSVèªã¿èŸŒã¿å®äº: %d è¡\n", len(products)) + // ãã¹ã¿ãŒããŒã¿ãmapã«èªã¿èŸŒã¿ïŒcode â idïŒ + var categories []Category + if err := db.Select(&categories, "SELECT * FROM categories"); err != nil { + log.Fatal(err) + } + categoryMap := make(map[string]int, len(categories)) + for _, c := range categories { + categoryMap[c.Code] = c.ID + } codeâIDãæ¬²ããã¿ã€ãã³ã°ã§ãå
ã»ã©å®çŸ©ããmapåã®å€æ°ã䜿ãããã«æžãæããŸã // 3. category_codeãcategory_idã«å€æ - var category Category - if err := db.Get(&category, "SELECT * FROM categories WHERE code = ?", product.CategoryCode); err != nil { - log.Printf("è¡ %d: category倿ãšã©ãŒ: %v", i+2, err) + categoryID, ok := categoryMap[product.CategoryCode] + if !ok { + log.Printf("è¡ %d: category倿ãšã©ãŒ: code %q ãèŠã€ãããŸãã", i+2, product.CategoryCode) errorCount++ continue } ä»ã®ä¿®æ£ãå ãããšä»¥äžã®ãããªå·®åã«ãªããŸãã :::details ãªã³ã¡ã¢ãªãã£ãã·ã¥åã®å
šäœå·®å diff --git a/main.go b/main.go index c3705d8..c3c16cf 100644 --- a/main.go +++ b/main.go @@ -52,6 +52,34 @@ func main() { } fmt.Printf("CSVèªã¿èŸŒã¿å®äº: %d è¡\n", len(products)) + // ãã¹ã¿ãŒããŒã¿ãmapã«èªã¿èŸŒã¿ïŒcode â idïŒ + var categories []Category + if err := db.Select(&categories, "SELECT * FROM categories"); err != nil { + log.Fatal(err) + } + categoryMap := make(map[string]int, len(categories)) + for _, c := range categories { + categoryMap[c.Code] = c.ID + } + + var suppliers []Supplier + if err := db.Select(&suppliers, "SELECT * FROM suppliers"); err != nil { + log.Fatal(err) + } + supplierMap := make(map[string]int, len(suppliers)) + for _, s := range suppliers { + supplierMap[s.Code] = s.ID + } + + var statuses []Status + if err := db.Select(&statuses, "SELECT * FROM statuses"); err != nil { + log.Fatal(err) + } + statusMap := make(map[string]int, len(statuses)) + for _, s := range statuses { + statusMap[s.Code] = s.ID + } + importStart := time.Now() for i, product := range products { @@ -59,41 +87,29 @@ func main() { lineNum := i + 2 // 3. category_codeãcategory_idã«å€æ - var category Category - if err := db.Get( - &category, - `SELECT * FROM categories WHERE code = ?`, - product.CategoryCode, - ); err != nil { - log.Fatalf("è¡ %d: category_code %q ã®æ€çŽ¢ã«å€±æ: %v", lineNum, product.CategoryCode, err) + categoryID, ok := categoryMap[product.CategoryCode] + if !ok { + log.Fatalf("è¡ %d: category_code %q ã®æ€çŽ¢ã«å€±æ", lineNum, product.CategoryCode) } // 4. supplier_codeãsupplier_idã«å€æ - var supplier Supplier - if err := db.Get( - &supplier, - `SELECT * FROM suppliers WHERE code = ?`, - product.SupplierCode, - ); err != nil { - log.Fatalf("è¡ %d: supplier_code %q ã®æ€çŽ¢ã«å€±æ: %v", lineNum, product.SupplierCode, err) + supplierID, ok := supplierMap[product.SupplierCode] + if !ok { + log.Fatalf("è¡ %d: supplier_code %q ã®æ€çŽ¢ã«å€±æ", lineNum, product.SupplierCode) } // 5. status_codeãstatus_idã«å€æ - var status Status - if err := db.Get( - &status, - `SELECT * FROM statuses WHERE code = ?`, - product.StatusCode, - ); err != nil { - log.Fatalf("è¡ %d: status_code %q ã®æ€çŽ¢ã«å€±æ: %v", lineNum, product.StatusCode, err) + statusID, ok := statusMap[product.StatusCode] + if !ok { + log.Fatalf("è¡ %d: status_code %q ã®æ€çŽ¢ã«å€±æ", lineNum, product.StatusCode) } row := ProductRow{ ProductCode: product.ProductCode, ProductName: product.ProductName, - CategoryID: category.ID, - SupplierID: supplier.ID, - StatusID: status.ID, + CategoryID: categoryID, + SupplierID: supplierID, + StatusID: statusID, UnitPrice: product.UnitPrice, } ::: DBã«åãåããã代ããã«ã¡ã¢ãªäžã®ãã£ãã·ã¥ã«ããŒã¿ãåãåãããããã SELECTã®150äžååããªããªããæ®ãã®UPDATE/INSERTã®æå€§100äžåã«ãŸã§åæžã§ããŸããã # ã¯ãšãª ã«ãŒãå(å) ã«ãŒãäž(å) åèš(å) 1 SELECT * FROM categories 1 0 1 2 SELECT * FROM suppliers 1 0 1 3 SELECT * FROM statuses 1 0 1 4 UPDATE products SET ... WHERE product_code = ? 0 1 à 50äžã«ãŒã = 50äž 50äž 5 INSERT INTO products (...) VALUES (...) 0 æå€§1 à 50äžã«ãŒã = æå€§50äž æå€§50äž åèš 3 æå€§100äž æå€§100äž3 ããã§ã©ããããé«éåã§ãããèŠãŠã¿ãŸãããã CSVèªã¿èŸŒã¿å®äº: 500000 è¡ é²æ: 1000 / 500000 è¡ (282 è¡/ç§) 鲿: 2000 / 500000 è¡ (302 è¡/ç§) 鲿: 3000 / 500000 è¡ (330 è¡/ç§) 鲿: 4000 / 500000 è¡ (360 è¡/ç§) 鲿: 5000 / 500000 è¡ (378 è¡/ç§) (ç¥) 鲿: 496000 / 500000 è¡ (409 è¡/ç§) 鲿: 497000 / 500000 è¡ (409 è¡/ç§) 鲿: 498000 / 500000 è¡ (409 è¡/ç§) 鲿: 499000 / 500000 è¡ (407 è¡/ç§) 鲿: 500000 / 500000 è¡ (405 è¡/ç§) å®äº: æå 500000 è¡, ãšã©ãŒ 0 è¡ (æèŠæé: 20m35.34731075s) 以äžã®ããã«æéãåæžãããããšãã§ããŸããã INSERT/UPDATEç·š SELECTã®å®è¡åæ°ã¯åæžã§ããŸãããããŸã 100äžåãã®SQLãå®è¡ãããŠããŸãã æ®ãã®INSERT/UPDATEã®é«éåã«ãã£ã¬ã³ãžããŠã¿ãŸãã INSERT/UPDATEã®å®è¡åæ°ãåæžããææ®µãšããŠã¯upsertã«å€æŽããããšãæããããŸãã UPSERTãšã¯ UPSERTãšã¯INSERTãšUPDATEãçµã¿åãããåèªã§ãINSERTæã«å¯Ÿè±¡ã¬ã³ãŒããååšããªãå Žåã¯INSERTãšããã§ã«ååšããå Žåã¯UPDATEããããåŠçã§ãã MySQLã§ã¯INSERT ON DUPLICATE KEY UPDATEãšREPLACEæ§æã䜿ããŸãããä»åã¯åè
ã®æ§æã䜿ã£ãŠã¿ãŸãã ä»åã§ãããšä»¥äžã®UPDATEæãå®è¡ãã UPDATE products SET product_name = ?, category_id = ?, supplier_id = ?, status_id = ?, unit_price = ? WHERE product_code = ? UPDATE察象ãååšããªããã°INSERTãè¡ã£ãŠããŸãã INSERT INTO products ( product_code, product_name, category_id, supplier_id, status_id, unit_price ) VALUES (?, ?, ?, ?, ?, ?) INSERT ON DUPLICATE KEY UPDATEã䜿çšãããš2ã€ã®ã¯ãšãªã1ã€ã«ãŸãšããããšãã§ããŸãã INSERT INTO products ( product_code, product_name, category_id, supplier_id, status_id, unit_price ) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE product_name = VALUES(product_name), category_id = VALUES(category_id), supplier_id = VALUES(supplier_id), status_id = VALUES(status_id), unit_price = VALUES(unit_price) ããã ãã§100äžåâ50äžåãŸã§ã¯ãšãªã®å®è¡åæ°ãåæžã§ããŸãã # ã¯ãšãª ã«ãŒãå(å) ã«ãŒãäž(å) åèš(å) 1 SELECT * FROM categories 1 0 1 2 SELECT * FROM suppliers 1 0 1 3 SELECT * FROM statuses 1 0 1 4 INSERT INTO products (...) ON DUPLICATE KEY UPDATE ... 0 1 à 50äžã«ãŒã = 50äž 50äž åèš 3 50äž 50äž3 ã³ãŒãã§ã¯ä»¥äžã®ããã«ä¿®æ£ããŠããŸã :::details UPSERTåã®å·®å diff --git a/main.go b/main.go index c3c16cf..0da4db0 100644 --- a/main.go +++ b/main.go @@ -113,36 +113,23 @@ func main() { UnitPrice: product.UnitPrice, } - // 7. UPDATEæãå®è¡ãã - result, err := db.NamedExec(` - UPDATE products - SET product_name = :product_name, - category_id = :category_id, - supplier_id = :supplier_id, - status_id = :status_id, - unit_price = :unit_price - WHERE product_code = :product_code`, + // 7. UPSERTïŒINSERT or UPDATEïŒãå®è¡ãã + _, err := db.NamedExec(` + INSERT INTO products ( + product_code, product_name, category_id, supplier_id, status_id, unit_price + ) VALUES ( + :product_code, :product_name, :category_id, :supplier_id, :status_id, :unit_price + ) + ON DUPLICATE KEY UPDATE + product_name = VALUES(product_name), + category_id = VALUES(category_id), + supplier_id = VALUES(supplier_id), + status_id = VALUES(status_id), + unit_price = VALUES(unit_price)`, row, ) if err != nil { - log.Fatalf("è¡ %d: productsã®æŽæ°ã«å€±æ: %v", lineNum, err) - } - - rowsAffected, err := result.RowsAffected() - if err != nil { - log.Fatalf("è¡ %d: æŽæ°ä»¶æ°ã®ååŸã«å€±æ: %v", lineNum, err) - } - - // 8. UPDATE察象ããªããã°INSERTãã - if rowsAffected == 0 { - _, err = db.NamedExec(` - INSERT INTO products (product_code, product_name, category_id, supplier_id, status_id, unit_price) - VALUES (:product_code, :product_name, :category_id, :supplier_id, :status_id, :unit_price)`, - row, - ) - if err != nil { - log.Fatalf("è¡ %d: productsã®ç»é²ã«å€±æ: %v", lineNum, err) - } + log.Fatalf("è¡ %d: productsã®UPSERTã«å€±æ: %v", lineNum, err) } if (lineNum-1)%1000 == 0 { ::: å®è¡ããŠã¿ãŸãããã CSVèªã¿èŸŒã¿å®äº: 500000 è¡ é²æ: 1000 / 500000 è¡ (636 è¡/ç§) 鲿: 2000 / 500000 è¡ (642 è¡/ç§) 鲿: 3000 / 500000 è¡ (658 è¡/ç§) 鲿: 4000 / 500000 è¡ (661 è¡/ç§) 鲿: 5000 / 500000 è¡ (652 è¡/ç§) (ç¥) 鲿: 497000 / 500000 è¡ (650 è¡/ç§) 鲿: 498000 / 500000 è¡ (650 è¡/ç§) 鲿: 499000 / 500000 è¡ (650 è¡/ç§) 鲿: 500000 / 500000 è¡ (650 è¡/ç§) å®äº: æå 500000 è¡, ãšã©ãŒ 0 è¡ (æèŠæé: 12m48.924974166s) ãã®ä¿®æ£ã ãã§10åçšåºŠãŸã§æ©ãããããšãã§ããŸããã bulkåãã upsertã«å€æŽããŠ50äžåãŸã§SQLã®å®è¡åæ°ãåæžã§ããŸããã ããã«SQLã®å®è¡åæ°ãåæžããããã«SQLãbulkåããŠã¿ãŸãã bulkåãšã¯DBã«å¯ŸããŠè€æ°ã®ã¬ã³ãŒãã«å¯Ÿããæäœã1ã€ã®SQLã«ãŸãšããŠå®è¡ããããšãèšããŸãã 以äžã®UPSERTåããSQLã¯ããŸã 50äžåå©ãããŠããŸãã INSERT INTO products ( product_code, product_name, category_id, supplier_id, status_id, unit_price ) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE product_name = VALUES(product_name), category_id = VALUES(category_id), supplier_id = VALUES(supplier_id), status_id = VALUES(status_id), unit_price = VALUES(unit_price) ãã®SQLã1è¡ãã€å
¥ããŠããã®ã§ã¯ãªããããçšåºŠã®ã¬ã³ãŒãæ°ã§åºããŠããéãããšã§ SQLã®å®è¡åæ°ãæžãããããã§ãã ä»åã¯1000ã¬ã³ãŒãåãã€SQLããŸãšããŠéãããšã«ããŠã¿ãŸãããã ãããš500000/1000=500åãŸã§SQLã®å®è¡åæ°ãåæžã§ããŸãã # ã¯ãšãª ã«ãŒãå(å) ã«ãŒãäž(å) åèš(å) 1 SELECT * FROM categories 1 0 1 2 SELECT * FROM suppliers 1 0 1 3 SELECT * FROM statuses 1 0 1 4 INSERT INTO products (...) VALUES (...), (...), ... ON DUPLICATE KEY UPDATE ... 0 50äžã«ãŒã / 1000 = 500 500 åèš 3 500 503 ã©ããããåºããããè¡šãæ°å€ãããããµã€ãºãšåŒã³ãŸããããã®å Žåããããµã€ãºã¯1000ãšãªããŸãã :::details ãã«ã¯UPSERTåã®å·®å diff --git a/main.go b/main.go index 0da4db0..daf2689 100644 --- a/main.go +++ b/main.go @@ -80,8 +80,8 @@ func main() { statusMap[s.Code] = s.ID } - importStart := time.Now() - + // code â id 倿ããŠProductRowã¹ã©ã€ã¹ãæ§ç¯ + var rows []ProductRow for i, product := range products { // 2. èªãã§ãªãè¡ãããã°1è¡èªã¿åãããªããã°çµäº lineNum := i + 2 @@ -104,16 +104,29 @@ func main() { log.Fatalf("è¡ %d: status_code %q ã®æ€çŽ¢ã«å€±æ", lineNum, product.StatusCode) } - row := ProductRow{ + // 6. ProductRowã«å€æ + rows = append(rows, ProductRow{ ProductCode: product.ProductCode, ProductName: product.ProductName, CategoryID: categoryID, SupplierID: supplierID, StatusID: statusID, UnitPrice: product.UnitPrice, + }) + } + fmt.Printf("倿å®äº: %d è¡\n", len(rows)) + + // ãã«ã¯UPSERTïŒ1000è¡ãã€ïŒ + const batchSize = 1000 + importStart := time.Now() + + for i := 0; i < len(rows); i += batchSize { + end := i + batchSize + if end > len(rows) { + end = len(rows) } + batch := rows[i:end] - // 6. UPSERTïŒINSERT or UPDATEïŒãå®è¡ãã _, err := db.NamedExec(` INSERT INTO products ( product_code, product_name, category_id, supplier_id, status_id, unit_price @@ -126,17 +139,16 @@ func main() { supplier_id = VALUES(supplier_id), status_id = VALUES(status_id), unit_price = VALUES(unit_price)`, - row, + batch, ) if err != nil { - log.Fatalf("è¡ %d: productsã®UPSERTã«å€±æ: %v", lineNum, err) + log.Fatalf("ããã %d-%d: UPSERTã«å€±æ: %v", i+1, end, err) } - if (lineNum-1)%1000 == 0 { - rate := float64(lineNum-1) / time.Since(importStart).Seconds() - fmt.Printf("鲿: %d / %d è¡ (%.0f è¡/ç§)\n", lineNum-1, len(products), rate) + if end%10000 == 0 || end == len(rows) { + rate := float64(end) / time.Since(importStart).Seconds() + fmt.Printf("鲿: %d / %d è¡ (%.0f è¡/ç§)\n", end, len(rows), rate) } - // 8. 2ã«æ»ã } fmt.Printf("å®äº: %d è¡ (æèŠæé: %v)\n", len(products), time.Since(importStart)) ::: ã§ã¯å®è¡ããŠã¿ãŸãããã CSVèªã¿èŸŒã¿å®äº: 500000 è¡ å€æå®äº: 500000 è¡ (ãšã©ãŒ 0 è¡) 鲿: 10000 / 500000 è¡ (56843 è¡/ç§) 鲿: 20000 / 500000 è¡ (72234 è¡/ç§) 鲿: 30000 / 500000 è¡ (78721 è¡/ç§) 鲿: 40000 / 500000 è¡ (73047 è¡/ç§) 鲿: 50000 / 500000 è¡ (76230 è¡/ç§) 鲿: 60000 / 500000 è¡ (78932 è¡/ç§) 鲿: 70000 / 500000 è¡ (81193 è¡/ç§) (ç¥) 鲿: 460000 / 500000 è¡ (83997 è¡/ç§) 鲿: 470000 / 500000 è¡ (83998 è¡/ç§) 鲿: 480000 / 500000 è¡ (84197 è¡/ç§) 鲿: 490000 / 500000 è¡ (83433 è¡/ç§) 鲿: 500000 / 500000 è¡ (83642 è¡/ç§) å®äº: æå 500000 è¡, ãšã©ãŒ 0 è¡ (æèŠæé: 5.977838667s) ããã6ç§çšåºŠã§å®äºããããã«ãªããŸããïŒ å
ã
50åããã£ãŠããåŠçã ãšèãããšãããªãé«éåãããã®ã§ã¯ãªãããšæããŸãã æ¹ååŸã®å®éã®FACTORYã§ã®DBã®è² è·ç¶æ³ æ¹åã®çµæãå
è¿°ã®Database Insightsã®AASã§ç¢ºèªããŠã¿ãŸãããã èµ€æ ããã¹ã¿åæ æã«å®è¡ãããŠããSQLã«ãªããŸããã æ¹ååã«è² è·ãããã£ãŠããSQLãšããŠæããããŠããSELECTããªããªã£ãŠãããã«ããã¯ãè§£æ¶ãã INSERTã¯ãŸã ãããå®è¡åæ°ãæžããAASãæžã£ã ãã®ããã«å®éã®FACTORYã®DBã®èšæž¬ãããè² è·ãæžã£ãããšãããããŸãã ãã®æ¹åã®çµæã5åçšåºŠã§åæ ãçµããããã«ãªããŸããïŒ æ¹ååã¯90åããã£ãŠãããšèãããšãã¡ããã¡ãé«éåã§ããŸããïŒ ãŸãšã ä»åã®æ¹åã®å€é·ããŸãšãããšä»¥äžã®éãã§ãã ã¹ããã æœç æèŠæé SQLå®è¡åæ°(æå€§) æ¹åå - 47å 250äžå 1. ãªã³ã¡ã¢ãªãã£ãã·ã¥ SELECTãã¡ã¢ãªåç
§ã«çœ®æ 20å 100äžå 2. UPSERTå UPDATE+INSERTã1ã¯ãšãªã«çµ±å 13å 50äžå 3. ãã«ã¯UPSERTå 1000è¡ãã€ãŸãšããŠå®è¡ 6ç§ 500å ããã©ãŒãã³ã¹ãã¥ãŒãã³ã°ã§ãšã£ãæ¹æ³ã¯ã©ããISUCONã§ã¯ããåºãŠããå
žåçãªå¯Ÿå¿çã§ãã ãŸããISUCONã§å¹ã£ãç¥èã䜿ã£ãŠæ¥åã§ããã»ã©ãŸã§ã®çµæãåºãããšã¯æããããŸããã§ããã ISUCONã¯æ¥åã§ã圹ã«ç«ã¡ãŸãã ãããããISUCONã§è
ã磚ãã€ã€ãæ¥åã§ã®ããã«ããã¯ãæ¹åããŠãããããšèããŠããŸãã