실습 스키마

CREATE TABLE Employees ( 
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(100), 
    Department VARCHAR(50),  
    Salary DECIMAL(10, 2) 
);

-- 대량의 데이터를 삽입하는 반복 스크립트
DELIMITER $$

CREATE PROCEDURE InsertEmployees()
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 반복문으로 데이터 100만개 삽입
    WHILE i <= 1000 DO
        INSERT INTO Employees (Name, Department, Salary)
        SELECT 
            CONCAT('Employee', FLOOR(RAND() * 1000000)),  -- 랜덤한 이름
            CASE FLOOR(RAND() * 5) + 1                     -- 랜덤 부서
                WHEN 1 THEN 'HR'
                WHEN 2 THEN 'Engineering'
                WHEN 3 THEN 'Sales'
                WHEN 4 THEN 'Marketing'
                WHEN 5 THEN 'Support'
            END,
            FLOOR(RAND() * 90000 + 30000)                 -- 랜덤 연봉 (30000 ~ 120000)
        FROM 
            dual
        LIMIT 1000;

        -- 배치를 처리하고 다음으로 넘어감
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

-- 프로시저 삭제
DROP PROCEDURE InsertEmployees;

-- 프로시저 실행
CALL InsertEmployees();

SELECT * FROM Employees WHERE Name = 'Employee12345';
EXPLAIN SELECT * FROM Employees WHERE Name = 'Employee12345';
CREATE INDEX idx_name ON Employees (Name);
CREATE INDEX idx_department ON Employees (Department);
CREATE INDEX idx_name_department ON Employees (Name, Department);
drop index idx_name on Employees;
drop index idx_department on Employees;
drop index idx_name_department on Employees;