環境設定

• Linuxサーバー：8GBメモリ
• MySQL 8.0.20

事前準備

テーブル設計

CREATE TABLE `orders` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主キー',
  `order_code` VARCHAR(255) COMMENT '注文番号',
  `item_code` VARCHAR(255) COMMENT '商品コード',
  `item_name` VARCHAR(255) COMMENT '商品名',
  `client_code` VARCHAR(255) COMMENT '顧客コード',
  `client_name` VARCHAR(255) COMMENT '顧客名',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '注文金額',
  `status` TINYINT NOT NULL COMMENT 'ステータス (0:未払,1:支払済み,2:発送済み,3:完了,4:取消)',
  `created_at` DATETIME NOT NULL COMMENT '登録日時',
  `updated_at` DATETIME COMMENT '更新日時',
  INDEX `idx_client_status_created` (`client_code`,`status`,`created_at`),
  INDEX `idx_order_code` (`order_code`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='注文テーブル';

テストデータ

INSERT INTO orders VALUES 
(1, 'ORD20240001', 'ITEM001', 'スマートウォッチ', 'CLT001', '山田太郎', 499.99, 2, NOW(), NOW()),
(2, 'ORD20240002', 'ITEM002', 'ワイヤレスイヤホン', 'CLT002', '佐藤花子', 299.99, 1, NOW(), NOW());

大量データ生成

DELIMITER $$
CREATE PROCEDURE generate_orders(IN total INT)
BEGIN
  DECLARE counter INT DEFAULT 1;
  START TRANSACTION;
  WHILE counter <= total DO
    INSERT INTO orders (
      order_code, item_code, item_name, 
      client_code, client_name, amount,
      status, created_at, updated_at
    ) VALUES (
      CONCAT('ORD', UNIX_TIMESTAMP(), counter),
      CONCAT('ITEM', LPAD(counter%1000,3,'0')),
      CONCAT('商品-', counter),
      CONCAT('CLT', LPAD(counter%500+1,4,'0')),
      CONCAT('顧客-', FLOOR(RAND()*500)+1),
      ROUND(100 + RAND()*900, 2),
      FLOOR(RAND()*5),
      NOW(),
      NOW()
    );
    SET counter = counter + 1;
  END WHILE;
  COMMIT;
END$$
DELIMITER ;

CALL generate_orders(10000000);

MySQL診断コマンド

-- 実行中クエリ表示
SHOW FULL PROCESSLIST;

-- 設定パラメータ確認
SHOW VARIABLES LIKE '%binlog%';

-- パフォーマンス統計
SHOW GLOBAL STATUS;

-- 実行計画分析
EXPLAIN SELECT * FROM orders WHERE client_code = 'CLT001';

パフォーマンス分析手法

スロークエリログ設定:

-- ログ有効化
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.001;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 設定確認
SHOW VARIABLES LIKE 'slow_query%';

パフォーマンススキーマ活用:

-- パフォーマンススキーマ有効化
SHOW VARIABLES LIKE 'performance_schema';

-- 統計テーブル確認
USE performance_schema;
SHOW TABLES;

クエリ最適化手法

EXPLAINによる実行計画分析

最適化ケーススタディ

1. 不要列の選択回避

-- 非効率
SELECT * FROM orders WHERE client_code = 'CLT001';

-- 最適化
SELECT order_code, amount FROM orders WHERE client_code = 'CLT001';

2. ページネーション改善

-- 非効率
SELECT * FROM orders LIMIT 1000000, 10;

-- 最適化
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

3. インデックス列での関数使用回避

-- 非効率 (インデックス未使用)
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 最適化 (範囲検索)
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

4. 複合インデックス活用

-- インデックス: (client_code, status, created_at)

-- 有効ケース (左端列使用)
SELECT * FROM orders 
WHERE client_code = 'CLT001' AND status = 1;

-- 無効ケース (左端列欠如)
SELECT * FROM orders WHERE status = 1;

5. ワイルドカード検索

-- 非効率 (前方一致)
SELECT * FROM orders WHERE client_code LIKE '%001';

-- 効率的 (後方一致)
SELECT * FROM orders WHERE client_code LIKE 'CLT%';

6. NULL値検索

-- インデックス使用可能
SELECT * FROM orders WHERE order_code IS NULL;

-- インデックス未使用
SELECT * FROM orders WHERE order_code IS NOT NULL;

7. データ型変換

-- 非効率 (暗黙的型変換)
SELECT * FROM orders WHERE order_code = 1001;

-- 最適化
SELECT * FROM orders WHERE order_code = '1001';