sol1: 파이썬 코드로 직접 쿼리 생성
준비
쿼리 생성기 - 1.7GB, 약 2천만건의 데이터
테이블 구조
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price INT NOT NULL,
brand VARCHAR(255) NOT NULL,
category VARCHAR(255) NOT NULL,
status ENUM(\'단종\', \'판매 예정\', \'판매중\') NOT NULL
);
Python
복사
실행
SELECT * from product where price > 800000 ORDER BY price, id ASC limit 10;
SQL
복사
-- 인덱스 생성
CREATE INDEX high_price ON product (price);
-- 인덱스 삭제
DROP INDEX high_price ON product;
-- 인덱스 조회
SHOW INDEX FROM product;
SQL
복사
결과
reference
sol2: sysbench 사용
준비 (in mac)
•
local terminal
local> brew install sysbench
local> mysql -u root -p
SQL
복사
•
mysql server
mysql> create database sysbench;
mysql> create user 'sysbench'@'localhost' identified by 'sysbench';
mysql> create user 'sysbench'@'127.0.0.1' identified by 'sysbench';
mysql> grant all on sysbench.* to 'sysbench'@'localhost';
mysql> grant all on sysbench.* to 'sysbench'@'127.0.0.1';
mysql> flush privileges;
SQL
복사
•
local terminal
local> brew list sysbench ✔ 37s 17:21:49
/opt/homebrew/Cellar/sysbench/1.0.20_6/bin/sysbench
/opt/homebrew/Cellar/sysbench/1.0.20_6/sbom.spdx.json
/opt/homebrew/Cellar/sysbench/1.0.20_6/share/doc/sysbench/manual.html
/opt/homebrew/Cellar/sysbench/1.0.20_6/share/sysbench/ (82 files)
local> sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=Sysbench@1234 --mysql-db=sysbench --table-size=5000000 --tables=1 /opt/homebrew/Cellar/sysbench/1.0.20_6/share/sysbench/oltp_read_write.lua prepare
SQL
복사
[root@master ~]# sysbench --mysql-host=[호스트명] --mysql-port=[mysql port] --mysql-user=[DB User] --mysql-password=[DB Pass] --mysql-db=[DB명] --table-size=[각 Table별 데이터 건 수] --tables=[table 갯수] /경로/share/sysbench/oltp_read_write.lua prepare
실행
sol1과 동일