MySQLマルチカラムインデックスの適用

複数カラムインデックス
「WHERE条件の列にインデックスを付ける」という話をよく耳にしますが、実はこの提案は非常に間違っています.複数のカラムに個別のインデックスを作成しても、MySQLのクエリのパフォーマンスはほとんど向上しません.MySQLは5.0以降、指定したローを特定するためにテーブル上の複数の単一カラムインデックスを使用する「インデックスマージ」(index merge)というポリシーを導入しました.しかし、サーバが複数のインデックスを共同で操作する場合、アルゴリズムのキャッシュ、ソート、およびマージ操作に多くのCPUおよびメモリリソースを費やす必要があります.特に、インデックスの選択性が高くなく、大量のデータをマージしてスキャンする必要がある場合があります.この場合、複数のカラムインデックスが必要です.
ケース
テスト・データベースとデータ・テーブルを作成します.

CREATE DATABASE IF NOT EXISTS db_test default charset utf8 COLLATE utf8_general_ci; 
use db_test;
CREATE TABLE payment (  
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,  
    staff_id  INT UNSIGNED NOT NULL,  
    customer_id INT UNSIGNED NOT NULL,  
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ストレージ・プロシージャを使用して1000 w行のランダム・データを挿入します(テーブル・エンジンはMyISAMに設定してからInnoDBに変更できます):

DROP PROCEDURE IF EXISTS add_payment;  
DELIMITER //
    create PROCEDURE add_payment(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        SET @exesql = 'INSERT INTO payment(staff_id, customer_id) values (?, ?)';
        WHILE rowid < num DO
            SET @staff_id = (1 + FLOOR(5000*RAND()) ); 
            SET @customer_id = (1 + FLOOR(500000*RAND()));
            SET rowid = rowid + 1;
            prepare stmt FROM @exesql;
            EXECUTE stmt USING @staff_id, @customer_id;            
        END WHILE;
    END //
DELIMITER ;

あるいは、私のテストデータを直接ダウンロードして使用することもできます(上記のストレージプロセスを利用していますが、私は後でデータを調整しました):テストデータ
2つの単列インデックスを追加します(実行には時間がかかりますが、1つの文で実行することをお勧めします).

ALTER TABLE `payment` ADD INDEX idx_customer_id(`customer_id`);
ALTER TABLE `payment` ADD INDEX idx_staff_id(`staff_id`);

2つのカラムのインデックスを使用して、1つのデータをクエリーします.

select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112;

実行計画の表示:

mysql> explain select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112;
+----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                | key                          | key_len | ref  | rows  | Extra                                                                   |
+----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+
|  1 | SIMPLE      | payment | index_merge | idx_customer_id,idx_staff_id | idx_staff_id,idx_customer_id | 4,4     | NULL | 11711 | Using intersect(idx_staff_id,idx_customer_id); Using where; Using index |
+----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

typeがindex_であることがわかりますmerge,Extra中提示Using intersec(idx_staff_id,idx_customer_id);これは、2つのインデックスを使用してインデックスを結合し、2つの結果(交差または並列セットまたは両方)をマージしたクエリーの結果です.

mysql> select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112 ;
+----------+
| count(*) |
+----------+
|   178770 |
+----------+
1 row in set (0.12 sec)

次に、上記のインデックスを削除し、複数のカラムインデックスを追加します.

ALTER TABLE payment DROP INDEX idx_customer_id;
ALTER TABLE payment DROP INDEX idx_staff_id;
ALTER TABLE `payment` ADD INDEX idx_customer_id_staff_id(`customer_id`, `staff_id`);

複数のカラムインデックスは、インデックスカラムの順序(customer_idの選択性が大きいため、前に置く)に注目しています.

mysql> select count(*)  from payment  where staff_id =  2205  AND customer_id =  93112;
+----------+
| count(*) |
+----------+
|   178770 |
+----------+
1 row in set (0.05 sec)

複数のカラム・インデックスが高速化されたクエリーを検出します(ここではデータ量が小さく、より大きい場合はより顕著です).
に注意
複数のカラムインデックスのカラム順序が重要です.インデックスのカラム順序を選択するには、最も選択性の高いカラムをインデックスの最前列に配置する経験があります(絶対的ではありません).経験則は、特定のクエリではなく、グローバルベースと選択性を考慮します.

mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_selectivity, count(DISTINCT customer_id) / count(*) AS customer_id_selectivity, count(*) from payment\G;
*************************** 1. row ***************************
   staff_id_selectivity: 0.0005
customer_id_selectivity: 0.0500
               count(*): 10000000
1 row in set (6.29 sec)

customer_idの選択性が高いので、インデックス列の1番目とします.複数のカラム・インデックスは、左の接頭辞にのみ一致します.つまり、次のようになります.

select * from payment  where staff_id =  2205  AND customer_id =  93112 ;
select count(*)  from payment  where  customer_id =  93112 ;

インデックスは利用できますが、

select * from payment  where staff_id =  2205 ;

インデックスは使用できません.