概要 クラスタインデックスと非クラスタインデックスで処理速度がどのように変わるのか、mysqlを利用して調べてみた 検証環境 mysql 8.0.23 クラスタインデックスについて クラスタインデックスと、セカンダリインデックス テーブル内のデータは、クラスタインデックスのデータ行の値に基づいて並べ替えられ格納される テーブル上で PRIMARY KEY を定義すると、InnoDB ではそれがクラスタ化されたインデックスとして使用されます。 作成するテーブルごとに主キーを定義します。 クラスタ化されたインデックスでのクエリーの挙動 クラスタ化されたインデックスから行にアクセスすると、インデックス検索がすべての行データを持つページで直接実行されるため、高速になります。 セカンダリインデックスとクラスタ化されたインデックスとの関係 クラスタ化されたインデックス以外のインデックスは、すべてセカンダリインデックスと呼ばれます。 InnoDB では、セカンダリインデックス内の各レコードに、行の主キーカラム、およびセカンダリインデックスに指定されたカラムが含まれます。 InnoDB では、クラスタ化されたインデックス内で行を検索する際に、この主キー値が使用されます。 速度検証 テストデータの作成 速度検証を行うために、クラスタインデックスとして id（Primary key）と、非クラスタインデックスとして、number（indexのみ設定）を含んだテーブルを作成し、処理速度の比較を行ってみた /* dbおよび、テーブルの作成 */ mysql> create database cluster_index_test character set utf8mb4; mysql> create table users (id int auto_increment primary key, number int, index(number)); /* プロシージャを作成して、100万行のテストデータを挿入 */ mysql> delimiter // mysql> create procedure create_test_data(in i int) -> begin -> declare cnt int default 0; -> while cnt < i do -> set cnt = cnt + 1; -> insert into users(number) values(cnt); -> end while; -> end -> // mysql> delimiter ; mysql> call create_test_data(1000000); /* テストデータを確認 */ mysql> select count(*) from users; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.05 sec) /* id と numberに同じ値が入ったレコードが100万件存在している */ mysql> select * from users limit 10; +----+--------+ | id | number | +----+--------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | | 10 | 10 | +----+--------+ 10 rows in set (0.00 sec) mysql での実行速度検証方法 show profileを利用して、queryの実行時間を測定する。（現在はパフォーマンススキーマの利用が推薦されている） https://dev.mysql.com/doc/refman/8.0/ja/show-profile.html mysql> SET profiling = 1; クラスタインデックスの処理速度検証 クエリを複数回実行して、処理速度を取得 order by を id（クラスタインデックス） の昇順に設定 処理速度は0.04秒台 mysql> select * from users where id between 100000 AND 200000 order by id; mysql> show profiles; +----------+------------+--------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------------------+ | 1116865 | 0.04819600 | select * from users where id between 100000 AND 200000 order by id | | 1116866 | 0.04485100 | select * from users where id between 100000 AND 200000 order by id | | 1116867 | 0.04960100 | select * from users where id between 100000 AND 200000 order by id | | 1116868 | 0.04490800 | select * from users where id between 100000 AND 200000 order by id | | 1116869 | 0.04457800 | select * from users where id between 100000 AND 200000 order by id | | 1116870 | 0.04597700 | select * from users where id between 100000 AND 200000 order by id | | 1116871 | 0.04481600 | select * from users where id between 100000 AND 200000 order by id | | 1116872 | 0.04475000 | select * from users where id between 100000 AND 200000 order by id | | 1116873 | 0.04400700 | select * from users where id between 100000 AND 200000 order by id | | 1116874 | 0.04724000 | select * from users where id between 100000 AND 200000 order by id | | 1116875 | 0.04957500 | select * from users where id between 100000 AND 200000 order by id | | 1116876 | 0.04591700 | select * from users where id between 100000 AND 200000 order by id | | 1116877 | 0.04394200 | select * from users where id between 100000 AND 200000 order by id | | 1116878 | 0.04451400 | select * from users where id between 100000 AND 200000 order by id | | 1116879 | 0.05022800 | select * from users where id between 100000 AND 200000 order by id | +----------+------------+--------------------------------------------------------------------+ 15 rows in set, 1 warning (0.00 sec) SQLの実行計画を取得 mysql> explain select * from users where id between 100000 AND 200000 order by id; +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | users | NULL | range | PRIMARY,number | PRIMARY | 4 | NULL | 195732 | 100.00 | Using where | +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) key に PRIMARY が選択されていることから、PRIMARY KEY の index を利用してインデックススキャンを行なっている Extra に Using filesort がないので、特にソート処理が行われていない usersテーブルのデータは、クラスタインデックスの値の順番（ここでは id）の昇順で並んでいる、ソート処理が発生しない 降順に並び替えてみる 降順に並び替えを行うと、Extra に Backward index scan とあるので、元々のデータの並び順の逆順で処理が行われている模様 mysql> explain select * from users where id between 100000 AND 200000 order by id DESC; +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+----------------------------------+ | 1 | SIMPLE | users | NULL | range | PRIMARY,number | PRIMARY | 4 | NULL | 195732 | 100.00 | Using where; Backward index scan | +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) 非クラスタインデックスの処理速度検証 order by を number（非クラスタインデックス） の昇順に設定 id、number は同一レコードに同じ値が格納されている 処理速度は0.07秒台。PRIMARY KEY を利用した処理よりも処理時間がかかっている mysql> select * from users where id between 100000 AND 200000 order by number; mysql> SHOW PROFILES; +----------+------------+------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------------------+ | 1116885 | 0.07490500 | select * from users where id between 100000 AND 200000 order by number | | 1116886 | 0.07895600 | select * from users where id between 100000 AND 200000 order by number | | 1116887 | 0.07719300 | select * from users where id between 100000 AND 200000 order by number | | 1116888 | 0.07689800 | select * from users where id between 100000 AND 200000 order by number | | 1116889 | 0.07640900 | select * from users where id between 100000 AND 200000 order by number | | 1116890 | 0.07063900 | select * from users where id between 100000 AND 200000 order by number | | 1116891 | 0.07628000 | select * from users where id between 100000 AND 200000 order by number | | 1116892 | 0.07592100 | select * from users where id between 100000 AND 200000 order by number | | 1116893 | 0.07825900 | select * from users where id between 100000 AND 200000 order by number | | 1116894 | 0.07519200 | select * from users where id between 100000 AND 200000 order by number | | 1116895 | 0.07810800 | select * from users where id between 100000 AND 200000 order by number | | 1116896 | 0.07623300 | select * from users where id between 100000 AND 200000 order by number | | 1116897 | 0.07581800 | select * from users where id between 100000 AND 200000 order by number | | 1116898 | 0.07553000 | select * from users where id between 100000 AND 200000 order by number | | 1116899 | 0.07491900 | select * from users where id between 100000 AND 200000 order by number | +----------+------------+------------------------------------------------------------------------+ 15 rows in set, 1 warning (0.00 sec) 実行計画を取得してみる mysql> explain select * from users where id between 100000 AND 200000 order by number; +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | users | NULL | range | PRIMARY,number | PRIMARY | 4 | NULL | 195732 | 100.00 | Using where; Using filesort | +----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec) number の検索に対して、number に設定された index を利用していることを確認 Extra に Using filesort が存在しているので、ソート処理が行われていることが確認できる 参考 https://engineer.namake-mono.xyz/development/410/ https://yanor.net/wiki/?MySQL/%E3%83%81%E3%83%A5%E3%83%BC%E3%83%8B%E3%83%B3%E3%82%B0/SQL%E3%81%AE%E5%AE%9F%E8%A1%8C%E6%99%82%E9%96%93%E3%82%92%E8%AA%BF%E3%81%B9%E3%82%8B http://nippondanji.blogspot.com/2010/10/innodb.html http://nippondanji.blogspot.com/2009/03/using-filesort.html

この記事の続きです。 2. GitHubの設定 次に、先ほど書いたコードをGitHubにアップします。 なお、本記事ではローカル環境へのGitインストール、GitHubアカウント作成等のステップは省略します。 なお、heroku Gitを使う場合はこの項目はスキップしてください。 2-1. リポジトリ作成 いつものページから新たに作成。 今回は既存のフォルダからそのままコミットするので空のリポジトリを作ります。 2-2. 1で作ったコードのpush [heroku_pj]の作業ディレクトリで以下のコマンドを叩きます。 zsh % git init % git remote add origin [リポジトリのSSHリンク] % git add . % git commit -m "Initial commit" % git push -u origin main GitHubでやることは以上です。 3. heroku設定 続いてはherokuの設定です。 3-1. 初期設定 herokuを使ったことがあり、heroku CLIのインストールが済んでいる人は飛ばしてください。 3-1-1. herokuアカウントの作成 herokuのSign upページ herokuアカウントにクレジットカードを未登録の場合は、必ず登録してください(MySQLの利用に必要です。PostgreSQLでいい場合は不要)。 3-1-2. heroku CLIのインストール ローカルにheroku CLIをインストールします。 zsh % brew tap heroku/brew && brew install heroku 3-2. herokuアプリケーションの作成 今回のサイトを作成します。 zsh % heroku login # ここでブラウザが開き、ログインする % heroku create [heroku-pj] 3-3. ClearDBの設定 herokuでMySQLを利用するには、ClearDBと呼ばれるサービスを用いるのが便利です。 3-3-1. ClearDBをherokuアプリケーションに追加 herokuアプリケーションにClearDBを追加します。今回はIgniteという無料プランを適用します。 zsh % heroku addons:create cleardb:ignite 3-3-2. DB情報を環境変数に設定 続いて、DBの情報をherokuアプリケーションの環境変数に登録します。 ここで登録した値はコード上のos.environ['key']で取得できます。 まず、必要な情報を取得します。heroku configで確認できるCLEARDB_DATABASE_URLの値を確認してください。 zsh % heroku config === [heroku-pj] Config Vars CLEARDB_DATABASE_URL: mysql://[ユーザー名]:[パスワード]@[ホスト名]/[データベース名]?reconnect=true 上記の値を以下のように設定します。 zsh % heroku config:add DB_CONNECTION='mysql' % heroku config:add DB_NAME='[データベース名]' % heroku config:add DB_USERNAME='[ユーザー名]' % heroku config:add DB_PASSWORD='[パスワード]' % heroku config:add DB_HOSTNAME='[ホスト名]' % heroku config:add DB_PORT='3306' % heroku config:add CLEARDB_DATABASE_URL='mysql://[ユーザー名]:[パスワード]@[ホスト名]/[データベース名]' 3-4. その他の環境変数の設定 zsh % heroku config:add SECRET_KEY='[settings.pyに書かれていたSECRET_KEY]' % heroku config:add DJANGO_SETTINGS_MODULE='heroku_pj.settings.production' DJANGO_SETTINGS_MODULEの設定は、heroku環境でpython manage.pyコマンドを使用するのに必要になります。 3-5. GitHubとの連携設定 heroku Gitを使わずに、herokuアプリケーションをGitHubに連携する方法です。 この方法により、GitHubのmainブランチにpushされるたびに自動デプロイを走らせることができます。 ブラウザでheroku.comにアクセスし、ログイン ダッシュボードから今回作成したアプリケーションを開き、Deployタブを選択 「Connect to GitHub」をクリックし、GitHubの認証にログイン 認証したら以下のテキストボックスにリポジトリ名を入力し、「Search」をクリック 表示されたリポジトリを「Connect」する 「Automatic deploys」の項目で「Enable Automatic deploys」をクリック これで自動デプロイの設定が完了です。 今回は既にリモートにpushしているので、手動でデプロイします。 やり方はManual Deployから「Deploy Branch」をクリックするだけです。 最後に これで一通りのデプロイは完了となります。 気が向いたらトラブルシューティングについても書きたいと思います。

こんにちは！ この前呪術廻戦で俺が学ぶMySQLという題名で 2つ記事を書きましたが、そもそもMySQLって何やねんと思い調べてきましたので 今日はそれについてアウトプットしていきたいと思います！ データベースとは データベースとは決まった形式で整理されたデータの集まりのことです。例えば、タンスの中身を「Tシャツ」や「ソックス」、「パンツ」の項目ごとに整理したものと考えればわかりやすいでしょう。 大量にあるアイテムを項目ごとに整理して集めることであるデータを検索しやすくします。このデータベースを管理するシステムのことをDBMS(DataBaseManagementSystem)データベース管理システムと呼びます。 データベースの種類 データベースには以下の３種類が存在します。 階層型 ネットワーク型 リレーショナル型 中でも幅広く使われているのがリレーショナル型です。 リレーショナルデータベース（RDB）では、データを表形式で表します。表ではエクセルのように行と列で表されます。この表のことをテーブルと呼びます。また、列にはそれぞれユーザーに分かりやすいような列名をつけます。リレーショナルデータベースでは、テーブル同士が連携して動く仕組みを持っています。 このようなシステムをリレーショナルデータベース管理システム（RDBMS）とよびます。 RDBMSにはMySQL、PostgreSQL、SQLiteなどがあります。 　SQLとは SQLとは, Structured Query Languageの略で、リレーショナルデータベース管理システム(RDBMS)と対話するための言語です。 RDBMSに（Query）問い合わせることでデータベースのあらゆる操作を行うことができます。 具体的に、データベースやテーブルの作成、更新、削除、データの作成、更新、削除、検索、また、データを特定するための条件を指定することができます。 SQLの基本構文 呪術廻戦で俺が学ぶMySQLという記事を書いたときにもやりましたけど、もう一度復習復習。 テーブルの作成 CREATE TABLE `magicians` (id INT, name TEXT, magic TEXT); 既存のテーブルの定義を変更した時はALTER文を使います。 今回は列の追加 ALTER TABLE `magicians` ADD [COLUMN] magic TEXT ; これであってるよな？？ テーブルを削除 DROP TABLE `magicians`; データを新たに登録 INSERT INTO `magicians` SET id = id = 1, name = '虎杖悠仁', magic = '逕庭拳'; データの更新 UPDATE `magicians` SET magic = "黒閃" WHERE name = "虎杖悠仁"; 今回は悠仁だけ呪術を変更したかったのでWHERE句を使用しました。 データを調べる SELECT * FROM `magicians` WHERE id = 1; データの削除 DELETE * FROM `magicians` WHERE id = 1;