MySQL indexのメモ

indexサイズの算出

テーブルに対するindexサイズ

SELECT TABLE_NAME, TABLE_ROWS, ROUND(DATA_LENGTH/1024/1024) Data_MB, ROUND(INDEX_LENGTH/1024/1024) Index_MB
  FROM information_schema.tables WHERE TABLE_NAME = 'テーブル名';

index別のサイズ

SELECT index_name, ROUND((SUM(stat_value) * @@innodb_page_size) / 1024 / 1024) MB
  FROM mysql.innodb_index_stats
WHERE table_name = 'テーブル名' AND stat_name = 'size'
GROUP BY index_name

オプティマイザの実行計画

SET optimizer_trace='enabled=on';
SELECT * FROM information_schema.optimizer_trace\G
SET optimizer_trace='enabled=off';

検証

テーブル定義

CREATE TABLE `sample` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `warehouse_base_id` int unsigned NOT NULL,
  `type_str` varchar(12) NOT NULL,
  `type_int` int NOT NULL,
  `type_tinyint` tinyint NOT NULL,
  `type_float` float NOT NULL,
  `type_decimal` decimal(6,2) NOT NULL,
  `name` varchar(255) NOT NULL,
  `data` text,
  `created_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

データ投入

// 会社(1000) * 倉庫(10) * 日付(365) * 種別(4) = 14,600,000件
$id = 1;
for ($companyId = 1; $companyId <= 1000; $companyId++) {
    $dbh->beginTransaction();
    for ($wbId = 1; $wbId <= 10; $wbId++) {
        $dt = new \DateTime('2020-01-01');
        for ($day = 1; $day <= 365; $day++) {
            for ($type = 1; $type <= 4; $type++) {
                $stmt = $dbh->prepare($sql);
                $stmt->bindValue(':company_id', $companyId);
                $stmt->bindValue(':warehouse_base_id', $wbId);
                $stmt->bindValue(':type_str', 'type'.$type);
                $stmt->bindValue(':type_int', $type);
                $stmt->bindValue(':type_tinyint', $type);
                $stmt->bindValue(':type_float', $type.'.05');
                $stmt->bindValue(':type_decimal', $type.'.05');
                $stmt->bindValue(':name', str_repeat('HOGE', 50).$id);
                $stmt->bindValue(':data', str_repeat('DATA', 200).$id);
                $stmt->bindValue(':created_at', $dt->format('Y-m-d H:i:s'));
                if (($id % 100) === 0) {
                    $stmt->bindValue(':deleted_at', $dt->format('Y-m-d H:i:s'));
                } else {
                    $stmt->bindValue(':deleted_at', null);
                }
                $stmt->execute();
                $id++;
            }
            $dt->modify('+1 day');
        }
    }
    $dbh->commit();
}

基本的な事

超基本的なことから改めて説明

単体のindexは複数は使われない

複合indexは順序が大事

varcharのindexは前方一致

ユニークキーの長さは制限がある

CREATE UNIQUE INDEX uniqe_name ON sample (name)
CREATE TABLE db_name.tbl_name
  (col_name data_type UNIQUE, ...)

左辺値を関数にするとindexを使用しない

floatよりDecimal

floatだと「SELECT * FROM a WHERE f=0.9」が一致しない

オプティマイザ

オプティマイザを意識した戦略

カーディナリティを意識

カーディナリティの高いキーを左側に持ってくる

レンジを左にすると安定しない

日付みたいなデータを左に持ってくると、

日付集計用indexを個別に貼った方が良いかも

日付を個別に貼ると良い