What's New Qiita

20210302のMySQLに関する記事は7件です。

  • 投稿日：2021-03-02T23:24:35+09:00

そうだ、SQLの勉強をしよう23

はじめに

そろそろ、SQLについての基本的なことについては知ることができてきたと思います。
これからは手を動かして、自分の思い通りの結果を得られるように定着を図っていこうと思います。

本日は、あまり学習していないので、内容が薄いです…

自動的に連番を振る列に指定する方法

CREATE TABLE テーブル名(
列名(IDなどが適切) INTEGER PRIMARY KEY AUTO_INCREMENT
)

AUTO_INCREMENTの記述をすることで、データが追加されたタイミングで自動的に連番が振られます。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-03-02T22:38:25+09:00

【MySQL】データベースの基本操作

データベースの一覧

mysql> show databases;

データベース作成

mysql> CREATE DATABASE db_name;

SQL文は大文字でなくても動く。が、大文字の方が読みやすく一般的。

データベース削除

mysql> DROP DATABASE db_name;

DELETEではなく、DROPである。

データベースの使用

mysql> USE db_name;

現在使用中のデータベース

mysql> SELECT database();

何も使用してない場合はNULLとなる。

mysql> exit
Bye
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-03-02T21:23:40+09:00

MySQL インデックス戦略

カバリングインデックス

データが欲しい時に、より小さなコストでやろうと思ったら、
行全体を取得するよりも、インデックスのみで処理するようが良いに決まっている。

そこで、クエリを処理するのに必要なデータを全て含んでいるようなインデックスをカバリングインデックスと呼ぶ。
これは非常に有効な手段で、特にInnnoDBにおいては、セカンダリインデックスは、主キーの値をそのままリーフノードに格納するため、クエリをカバーするセカンダリインデックスによって、主キーでもう１つのインデックスルックアップを回避することができる。

カバリングインデックスが実行された時、EXPLAIN句のEXTRA列にUsing Indexが表示される。

それでは、これから、複合インデックスを用いてカバリングインデックスを活用することを考える。
Userのageとnameがの一覧が必要であれば、(age, name)の複合インデックスをUserテーブルにはる・
そして、

SELECT
  age,
  name
FROM
  users

これは、Using Indexとなる。

注意したいのが、MySQLにおけるクエリオプティマイザは、クエリを実行する前にインデックスを選択するため、
インデックスがWHERE条件をカバーしてもSELECTをカバーしないとカバリングインデックスと判断されないことだ。
そのため、下記のようなクエリは、チューニングを台無しにする。

SELECT
  *
FROM
  users
WHERE
  age >= 20
  AND name LIKE "riita%"

これはUsing Whereとなる。

SELECT * により、全ての列をカバーしているインデックスのみがカバリングインデックスであり、そのようなインデックスは存在しないため。

この場合は、カバリングインデックスとして、(id, age, name)を採用して、
(※ InnnoDBでは、セカンダリインデックスがリーフノードに主キー値を格納するため、事実上では、インデックス(id, name)は上記のクエリをカバーし、実際上記のクエリに対して、インデックスを使用する)

SELECT
  *
FROM
  users AS list
INNER JOIN (
  SELECT
    id
  FROM
    users
  WHERE
    age >= 20
    AND name LIKE "riita%") AS ind
ON
  ind.id = list.id

とすることで、カバリングインデックスとして、探索をすることができる。
ただし、WHERE句のヒット率が高すぎる場合、行を取得するコストを削減するよりも、
相関サブクエリを起動するコストがかかってしまいあまりパフォーマンスが改善されないような場合もある。

ソートのためのインデックススキャン

ORDER BY句をインデックスがカバーしていれば、そのインデックスを用いて並び替えを行うため、
クイックソートアルゴリズム$O(nlogn)$を使用する必要がなくなる。
クイックソートアルゴリズムを使用するとオプティマイザーが判断した時、Explain句のExtra列にUsing filesortと記述されることを確認すると良い。

ORDER BYがインデックスで順序付けをすることができるのは下記の場合である。
- まず、WHERE句とORDER BY句がどちらもインデックスにカバーされている必要がある。
- そして、インデックスの順序がORDER BY句と同じかつ、全ての列が同じ方向でソートされている場合
- 左端プレフィックスの原則を満たしている
- WHERE句によって定数を確保していれば問題ない

以下のような時には、順序付けにインデックスが使用されないので注意
- ORDER BY id DESC, age ASCとしているが、インデックスがASCで並んでいる
- WHEREまたはORDER BYをインデックスがカバーできていない
- カバーしていても左端プレフィックスの原則に準拠していない
- 最右端以外のインデックスが範囲指定をしてしまう(範囲指定すると残りのインデックスを使用できない)

冗長インデックス、重複インデックス

やめましょう

参考

実践ハイパフォーマンスMySQL 第3版
Baron Schwartz、Peter Zaitsev、Vadim Tkachenko　著、菊池 研自　監訳、株式会社クイープ　訳
2013年11月 発行
https://www.oreilly.co.jp/books/9784873116389/

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-03-02T21:23:40+09:00

MySQL カバリングインデックス

カバリングインデックス

データが欲しい時に、より小さなコストでやろうと思ったら、
行全体を取得するよりも、インデックスのみで処理するようが良いに決まっている。

そこで、クエリを処理するのに必要なデータを全て含んでいるようなインデックスをカバリングインデックスと呼ぶ。
これは非常に有効な手段で、特にInnnoDBにおいては、セカンダリインデックスは、主キーの値をそのままリーフノードに格納するため、クエリをカバーするセカンダリインデックスによって、主キーでもう１つのインデックスルックアップを回避することができる。

カバリングインデックスが実行された時、EXPLAIN句のEXTRA列にUsing Indexが表示される。

それでは、これから、複合インデックスを用いてカバリングインデックスを活用することを考える。
Userのageとnameがの一覧が必要であれば、(age, name)の複合インデックスをUserテーブルにはる・
そして、

SELECT
  age,
  name
FROM
  users

これは、Using Indexとなる。

注意したいのが、MySQLにおけるクエリオプティマイザは、クエリを実行する前にインデックスを選択するため、
インデックスがWHERE条件をカバーしてもSELECTをカバーしないとカバリングインデックスと判断されないことだ。
そのため、下記のようなクエリは、チューニングを台無しにする。

SELECT
  *
FROM
  users
WHERE
  age >= 20
  AND name LIKE "riita%"

これはUsing Whereとなる。

SELECT * により、全ての列をカバーしているインデックスのみがカバリングインデックスであり、そのようなインデックスは存在しないため。

この場合は、カバリングインデックスとして、(id, age, name)を採用して、
(※ InnnoDBでは、セカンダリインデックスがリーフノードに主キー値を格納するため、事実上では、インデックス(id, name)は上記のクエリをカバーし、実際上記のクエリに対して、インデックスを使用する)

SELECT
  *
FROM
  users AS list
INNER JOIN (
  SELECT
    id
  FROM
    users
  WHERE
    age >= 20
    AND name LIKE "riita%") AS ind
ON
  ind.id = list.id

とすることで、カバリングインデックスとして、探索をすることができる。
ただし、WHERE句のヒット率が高すぎる場合、行を取得するコストを削減するよりも、
相関サブクエリを起動するコストがかかってしまいあまりパフォーマンスが改善されないような場合もある。

ソートのためのインデックススキャン

ORDER BY句をインデックスがカバーしていれば、そのインデックスを用いて並び替えを行うため、
クイックソートアルゴリズム$O(nlogn)$を使用する必要がなくなる。
クイックソートアルゴリズムを使用するとオプティマイザーが判断した時、Explain句のExtra列にUsing filesortと記述されることを確認すると良い。

ORDER BYがインデックスで順序付けをすることができるのは下記の場合である。

  • まず、WHERE句とORDER BY句がどちらもインデックスにカバーされている必要がある。
  • そして、インデックスの順序がORDER BY句と同じかつ、全ての列が同じ方向でソートされている場合
  • 左端プレフィックスの原則を満たしている
    • WHERE句によって定数を確保していれば問題ない

以下のような時には、順序付けにインデックスが使用されないので注意
- ORDER BY id DESC, age ASCとしているが、インデックスがASCで並んでいる
- WHEREまたはORDER BYをインデックスがカバーできていない
- カバーしていても左端プレフィックスの原則に準拠していない
- 最右端以外のインデックスが範囲指定をしてしまう(範囲指定すると残りのインデックスを使用できない)

冗長インデックス、重複インデックス

やめましょう

参考

実践ハイパフォーマンスMySQL 第3版
Baron Schwartz、Peter Zaitsev、Vadim Tkachenko　著、菊池 研自　監訳、株式会社クイープ　訳
2013年11月 発行
https://www.oreilly.co.jp/books/9784873116389/

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-03-02T15:50:17+09:00

Dockerでphpmyadmin+Wordpress+MySQLの環境構築[Windows 10 home]

※自分も初心者なので責任は一切負いません。参考程度にしてください。

使用するもの

  • Docker
  • Docker-compose
  • Ubuntu
  • WSL2
    ※各自インストールするなりしてください

手順

1:任意の場所にディレクトリ（フォルダ）を作成

どこに作ってもいいですがCドライブから遠すぎるとめんどくなりがちです。
名前も自由です。
今回は例として「Test」という名前にします。
更に「Test」フォルダに入って「wp-content」フォルダを作り、更にその中に入って「wp-themes」フォルダと「wp-plugins」フォルダを作りましょう。

2：docker-compose.ymlを作成

先程作成した「Test」フォルダの中に作ってください。開いたら中身を編集します。
中身はこう書いてください↓

version: '3.1'

services:

  wordpress:
    image: wordpress
    restart: always
    ports:
      - 8080:80 #ポート番号は自由に設定していいがこだわりがないならこの番号でいい
    environment:
      WORDPRESS_DB_HOST: db 
      WORDPRESS_DB_USER:hogehoge # (任意) mysqlへログインする際のユーザー名と合わせる
      WORDPRESS_DB_PASSWORD:hogehoge # (任意) mysqlへログインする際のパスワード と合わせる
      WORDPRESS_DB_NAME: wordpress_db  
    volumes:
      - wordpress:/var/www/html
      - ./wp-content/themes:/var/www/html/wp-content/themes
      - ./wp-content/plugins:/var/www/html/wp-content/plugins

  db:
    image: mysql:5.7
    restart: always
    environment:
      MYSQL_DATABASE: wordpress_db 
      MYSQL_USER:hogehoge # (任意) mysqlへログインする際のユーザー名
      MYSQL_PASSWORD:hogehoge # (任意) mysqlへログインする際のパスワード
      MYSQL_RANDOM_ROOT_PASSWORD: '1'
    volumes:
      - db:/var/lib/mysql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin:latest 
    restart: always 
    depends_on: 
      - db 
    ports: 
      - "8888:80" #ポート番号は自由に設定していいがこだわりがないならこの番号でいい
volumes:
  wordpress:
  db:

※最後の

volumes:
  wordpress:
  db:

を書き忘れたがためにデータの永続化ができず５時間ほど無駄にしました！気をつけてください。

3:docker-compose up -d　を実行

Ubuntuで「docker-compose up -d」を実行しましょう。
（失敗したらちゃんと～/Test$という表示になってるか確認！）
image.png
こうなったら成功です。

4:成功したか確認

http://localhost:8080/
と
http://localhost:8888/
にアクセス。
Wordpressの画面とphpmyadminの画面が出るはず。
「docker-compose.yml」に書いたユーザー名とパスワードでログイン。

ここで、Volume一覧を表示して見ましょう。

docker volume ls

とコマンド実行。
おそらく
Test_db
Test_wordpress
が入っているはずです。

データの永続化ができてるかを確認するため、適当に新規投稿をしてみてから一回dockerのコンテナを削除します（docker-compose down）。永続化できていない場合はこの時点でデータがリセットされるので新規投稿データも消えます。
逆に永続化に成功していた場合はデータがまだ残っているはずです。

コンテナ削除後もう一度「docker volume ls」を実行。まだTest_dbとTest_wordpressが残っていたら多分問題ありません。
再度「docker-compose up -d」を実行し、phpmyadminやWordpress編集画面でコンテナ削除前の新規投稿が残っているか確認しましょう。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-03-02T14:24:30+09:00

MySQLWorkbenchはBig Surだと動かない？

MySQLWorkbench8.0.23がBigSur11.1で動かない

MySQLWorkbenchは、MySQLの公式サイトにてMySQL Serverと共に配布されている、データベースの設計・開発・管理を単一の開発環境に統合するビジュアルデータベース設計ツールである。MySQLをGUIでわかりやすく操作でき、大変便利なツールであるのだが、Big Sur11.1では、MySQLWorkbenchの一部のVerが動かないバグが存在するようだ。
以下のリンクにその報告が記載してある。

その中で筆者が確認出来たのは以下の二つである。

BigSur11.1において
MySQLWorkbench8.0.23　(記事執筆当時latest)　 起動しない
MySQLWorkbench8.0.21 　起動する

※MySQLWorkbench8.0.2２に関しては未確認です。

MacOSをアップデートする際はご注意ください。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-03-02T10:51:40+09:00

INSERT SELECTでWITHを利用したい

TL;DR

INSERT SELECTでWITHを利用したい場合、以下のように書けばよいです。

INSERT INTO a (
  WITH c AS (
    SELECT * FROM b
  )
  SELECT * FROM c
);

WITH RECURSIVE (再帰SQL) も利用することができます。

INSERT INTO e (
  WITH RECURSIVE r AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM r WHERE seq < 5
  )
  SELECT * FROM r
);

環境

以下の2環境で動作確認を行いました。WITHがサポートされているDBMS・バージョンであれば動くとは思います。

  • mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
  • PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

稼働確認用DDL

WITH

CREATE TABLE a (
  col text
);
CREATE TABLE b (
  col text
);

INSERT INTO b VALUES ('col1');
INSERT INTO b VALUES ('col2');
INSERT INTO b VALUES ('col3');
INSERT INTO b VALUES ('col4');
INSERT INTO b VALUES ('col5');

INSERT INTO a (
  WITH c AS (
    SELECT * FROM b
  )
  SELECT * FROM c
);

SELECT * FROM a;

このSQLが以下のような結果セットを返すことを確認する。

col
col1
col2
col3
col4
col5

WITH RECURSIVE

CREATE TABLE e (
  seq INT
);

INSERT INTO e (
  WITH RECURSIVE r AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM r WHERE seq < 5
  )
  SELECT * FROM r
);

SELECT * FROM e;

このSQLが以下のような結果セットを返すことを確認する。

seq
1
2
3
4
5
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧