概要

• MySQL に文字セット utf8mb4 に設定したテーブルを作成して、Python 3 からデータ操作(参照・追加・更新・削除)を試す
• 環境: GMOデジロックのレンタルサーバー「コアサーバー」 + MySQL 5.7 + Python 3.6 + mysqlclient (MySQLdb)

MySQL のバージョンを確認

$ mysqld --version
mysqld  Ver 5.7.27 for Linux on x86_64 (MySQL Community Server (GPL))

MySQL の文字セットと照合順序を確認

起動している MySQL デーモンの文字セットと照合順序の設定を MySQL monitor にて確認。

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

参考:

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.4 接続文字セットおよび照合順序
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.14.1 Unicode 文字セット

データベースを作成

MySQL monitor にてデータベースを作成。

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.01 sec)

mysql> use test_db;
Database changed

参考:

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.10 CREATE DATABASE 構文

テーブルを作成

テーブル作成用の SQL を書いてファイルに保存。

create-table.sql

CREATE TABLE IF NOT EXISTS test_table (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE=InnoDB
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

mysql コマンドでテーブルを作成。

$ mysql -h localhost -u alice -p test_db < create-table.sql

MySQL monitor にてテーブルを確認。

mysql> show tables from test_db;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_table        |
+-------------------+
1 row in set (0.00 sec)

mysql> show create table test_db.test_table;
+------------+-----------------------------------------------
| Table      | Create Table                                  
+------------+-----------------------------------------------
| test_table | CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+------------+-----------------------------------------------
1 row in set (0.00 sec)

参考:

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.5 バッチモードでの MySQL の使用

Python のバージョンを確認

$ python3 --version
Python 3.6.8

Python のパッケージ mysqlclient をインストール

mysqlclient は Python 2 で使えるパッケージ MySQL-python (MySQLdb1) から派生して Python 3 に対応したパッケージ。

今回は mysqlclient のバージョン 1.3.4 を使う。

$ python3 -m pip install mysqlclient==1.3.4

今回の動作確認環境であるGMOデジロックのレンタルサーバー「コアサーバー」では該当バージョンがすでにインストール済みだった。

$ python3 -m pip list | grep mysqlclient
mysqlclient            1.3.4  

参考:

• Python モジュールのインストール — Python 3.6.10rc1 ドキュメント
• GitHub - PyMySQL/mysqlclient-python: MySQL database connector for Python (with Python 3 support)
• GitHub - farcepest/MySQLdb1: MySQL database connector for Python (legacy version)

Python + mysqlclient でデータ操作

データを追加・取得・更新・削除する Python のソースコードを用意。

crud.py

# mysqlclient を使う
import MySQLdb

# connect db
connection = MySQLdb.connect(host="localhost",
                             user="alice",
                             passwd="your-password",
                             db="test_db",
                             charset="utf8mb4")
cursor = connection.cursor(MySQLdb.cursors.DictCursor)

# insert
insert_sql = "INSERT INTO test_table (name) VALUES (%s)"
cursor.execute(insert_sql, ("寿司ビール??????文字化けしないで",))
print(f"insert count={cursor.rowcount}")
connection.commit()

# select
select_sql = "SELECT id, name FROM test_table"
cursor.execute(select_sql)
print(f"select count={cursor.rowcount}")
for row in cursor:
  print(f"{row['id']}: {row['name']}")
  target_id = row["id"]

# update
update_sql = "UPDATE test_table set name=%s WHERE id=%s"
cursor.execute(update_sql, ("Alice", target_id))
print(f"update count={cursor.rowcount}")
connection.commit()

# delete
delete_sql = "DELETE FROM test_table WHERE id=%s"
cursor.execute(delete_sql, (target_id,))
print(f"delete count={cursor.rowcount}")
connection.commit()

connection.close()

実行結果。
寿司ビール絵文字などが文字化けせずに追加・取得・表示できている。

$ python3 crud.py 
insert count=1
select count=1
1: 寿司ビール??????文字化けしないで
update count=1
delete count=1

参考:

• Welcome to MySQLdb’s documentation! — MySQLdb 1.2.4b4 documentation
• 【Python3】MySQL 操作をひと通りマスター！導入方法とCRUDサンプルコード集 | ITエンジニアラボ
• Python3でMySQLを使う – 基本操作からエラー処理までサンプルコード付 | Crane & to.

テーブルとデータベースを削除

MySQL monitor から使い終わったテーブルとデータベースを削除。

mysql> drop table test_db.test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test_db;
Query OK, 0 rows affected (0.00 sec)

アカウント作成

アカウントを作成します。無料ですが、クレジットカードの登録が必要です。ただし、クレジットカードの情報を登録しても良いことに勝手に課金されることはありません。つまり、有料プランへの切り替えを明示的にしない限り、課金されません。どうやら本人認証のために登録するだけのようです。

手順

• Microsoft Account 作成
• Microsoft Azure アカウント作成

参考サイト　https://azure.microsoft.com/ja-jp/free/

Web App 作成

Web App を作成します。その際、フリープラン「F1」を選ぶことで半永久的に無料で、サーバーが使えることになります。この Web App 無料プランは、HTTPサーバーでもあり、アプリサーバーでもあり、さらに簡易的なデータベースもついています。
ランタイムは、PHPを選択します。プランは安いものでOK。

手順

• Web App Windows 版作成
• 発行：　コード
• OS： Windows
• 課金：　フリープラン（F1）

ちなみに、OSをWindows にするのは、MySQL in App を使いたいため、です。
Linuxだとこのとおり、使えません

Web App とパソコンの同期方法

Dropbox、Onedrive、Git、GitHubなどで、ソースコードを、AzureにDeployできるようにします
設定はデプロイセンターです。

おすすめの方法いくつか。

チーム開発を行う場合

　＞　GitHubなどのリポジトリと直接つなぐ

チーム開発を行い、かつ本番業務を行う

　＞　Azure DevOpsで、CI/CDを構築する

一人で開発する場合

　＞　FTPや、Dropboxなどで十分
　たぶん一番簡単です。Dropboxの「アプリ」というフォルダに「Azure」フォルダが自動で作成されます。この下にソースコードを置けばOK

Web App のうえに、MySQLを作成

Web Appsができあがったら、ダッシュボードからMySQLをたちあげて、PHPMyAdminを開きます

この画面の、「Manage」というボタンは地味ですがこれを押すと、PHPMyAdminが立ち上がります

ですがたまにエラーになることがあります。。

理由は、Web Appsがフリープランのため、プロセスがおちてしまっているというやつです。

参照：　MySQL In AppでphpMyAdminに入れない→解決
https://www.ryuzoji.com/archives/2079

普通にアクセスすれば普通に画面が開きます。便利です。

DBやテーブルを作って接続確認してみましょう。

User と、Password　は、Web Appsのコンソールから、設定テキストファイルを開くとわかります

estie Advent Calendar 2019　14日目の記事になります。
是非他の記事もご覧くださいね

はじめに

こんにちは、estie.incでエンジニアやってます、marushoです。
estieでは「テクノロジーの力で、世界を自由に、楽しく。」を合言葉に、不動産分野の「めんどくさい」を解消するため

• オフィス探しサービス estie
• 不動産データの可視化サービス estiepro

を運営しており、日々更新される不動産データを分析し、新たな価値の創出にチャレンジしています。

データ分析や分析結果の反映をスピーディに行うためには、セキュリティ構成を担保しつつ気軽にDBへアクセスする必要があります。
弊社ではpandas<->DBのやりとりが頻繁に行われるのですが、一度csvファイルに変換したり踏み台サーバに入ったりするのは何かと時間を消費してしまいます。

ということで、
今回は踏み台経由でDBのデータを、pandasで基本的なCRUD操作を直接やってみます。

環境

DBはprivateなサブネットにいて踏み台サーバを経由しないとアクセスできない、というよくある環境を前提とします。
今回はAWS上のEC2/RDS(MySQL5.7)で動作させています。

ちなみにローカル環境は

• MacOS Mojave
• Python 3.6.8
• Pandas 0.24.1

必要なパッケージをインストール

PythonでDB情報を扱うので、定番ORMのSQLAlchemyを使います。
また、MySQLのドライバと踏み台にSSHを張るためのSSHtunnelもインストールします

$ pip install SQLAlchemy PyMySQL sshtunnel

SSH config

普段sshに接続するために、.ssh/configにHostを登録しておくことが多いと思います。
今回もsshtunnelでconfigに書かれたHost情報を利用するので、以下のように踏み台の接続情報を書き込んでおきます。

~/.ssh/config

Host rds_bastion
    Hostname [踏み台IP]
    Port 22
    User [UserName]
    IdentityFile ~/.ssh/[KeyName]

RDSに接続

まずはmoduleのimportと、DBの接続に必要な情報を書いておきます

import pandas as pd
import sqlalchemy as sa
from sshtunnel import SSHTunnelForwarder

DB_USER = 'test_user' # DBのユーザー名
DB_PASS =  'db_passward' # DBのパスワード
ENDPOINT = 'hogehoge.fugafuga.ap-northeast-1.rds.amazonaws.com' # RDSエンドポイント
PORT = 3306 # ポート
DB_NAME = 'test_db' # DB名
CHARSET = 'utf8' # 文字コード

次にSSHポートフォワードを使って、踏み台越しのDBに接続します。

server = SSHTunnelForwarder(ssh_address_or_host = 'rds_bastion',
                            ssh_config_file = '~/.ssh/config',
                            remote_bind_address=(ENDPOINT,PORT))
server.start()

接続を終了するときはcloseしましょう

server.close()

sshを接続した状態で、SQLAlqhemyのエンジンを取得します。

#SQLAlchemyの接続URLを生成
URL = f"mysql+pymysql://{DB_USER}:{DB_PASS}@127.0.0.1:{server.local_bind_port}/{DB_NAME}?charset={CHARSET}"

#engineの取得
engine = sa.create_engine(URL)

このengineを使ってPandasでのデータ操作をやっていきます

Pandasでごにょごにょする

さて、本題です。
pandasでcreate,read,update,delete操作ができるか試してみましょう。

サンプルとして、DB名test_dbにmembersテーブルを作成しておきます

MySQL [test_db]> SELECT * FROM members;
+----+------------------+-----+
| id | name             | age |
+----+------------------+-----+
|  1 | 雪村 あおい       | 15  |
|  2 | 倉上 ひなた       | 15  |
|  3 | 斎藤 楓          | 16  |
|  4 | 青羽 ここな       | 13  |
+----+------------------+-----+

Read:読み込み

まずはpandas.read_sqlを使ってmembersテーブルをDataFrameとして読み込んでみましょう

テーブル全てのデータを読み込む場合は、テーブル名を指定します

df = pd.read_sql('members', engine)

	id	name	age
0	1	雪村 あおい	15
1	2	倉上 ひなた	15
2	3	斎藤 楓	16
3	4	青羽 ここな	13

綺麗に読み込めてますね

indexカラムの指定や、取得したいカラム名をリスト指定することもできます。

df= pd.read_sql('members', engine, index_col='id', columns=['name'])

id	name
1	雪村 あおい
2	倉上 ひなた
3	斎藤 楓
4	青羽 ここな

もちろんSQLクエリでレコード指定することも可能です。

df= pd.read_sql('SELECT * FROM members WHERE id = 2', engine)

	id	name	age
1	2	倉上 ひなた	15

Create:テーブル作成

to_sqlを使ってDataFrameのデータから新しいテーブルを作成できます。
(DataFarameの)indexの有無や、どれをindexとして取り込むかの指定もできます。

df = pd.read_sql('SELECT * FROM members WHERE age < 14', engine)
df.to_sql('jc_members', engine, index=False, index_label='id')

MySQL [test_db]> select * from jc_members;
+------+------------------+------+
| id   | name             | age  |
+------+------------------+------+
|    4 | 青羽 ここな      | 13   |
+------+------------------+------+

Update:レコードの挿入/更新

こちらもto_sqlで実行できますが、
if_existオプションで挙動が異なるので注意が必要です。

if_exist=appendとすると、新しいレコードとして追加し、同じレコードがあった場合はエラーになります。

insert_df = pd.DataFrame({'id':['5'],'name' : ['黒崎 ほのか'],'age':['14']})
insert_df.to_sql('members', engine, index=False, index_label='id', if_exists='append')

id	name	age
1	雪村 あおい	15
2	倉上 ひなた	15
3	斎藤 楓	16
4	青羽 ここな	13
5	黒崎 ほのか	14

INSERTとおなじ挙動ですね。ちゃんと追加されています。

しかしif_exist=replaceとすると、指定テーブルのデータをすぺてdeleteして、DataFrameを追加します。

insert_df = pd.DataFrame({'id':['5'],'name' : ['黒崎 ほのか'],'age':['14']})
insert_df.to_sql('members', engine, index=False, index_label='id', if_exists='replace')

id	name	age
5	黒崎 ほのか	14

UPDATEでもUPSERTでもなく、はたまたREPLACEとも異なる挙動なので注意が必要です！

特定レコードだけ更新する、などの操作はまだto_sqlに実装されいないようです。
今回は割愛しますが、SQLAlchemyのupsertを使う方法や、to_sqlのmethodオプションでSQLの挙動を変更するやり方があるようなので、試してみようと思います。

Delete:レコード/テーブルの削除

read_sqlでdrop/delete操作をするとreturnが無くエラーになるのですが、
実はDB側には削除操作が実行されてしまいます。

pd.read_sql('DROP TABLE members', engine)

MySQL [test_db]> SELECT * FROM members;
ERROR 1146 (42S02): Table 'test_db.members' doesn't exist

これは本来の用途ではないので、delete操作を行うときは素直にsqlalchemyでのクエリ実行をお勧めします

engine.execute('DROP TABLE members')

おわりに

離れたDBの情報を手軽にDataFrameにできるのは魅力ですね。
更新系のメソッドはかゆいところに手が届いてない感じなので、今後のpandasの発展を注視したいと思います。

---

estieではWebエンジニアを募集しています！
Wantedly
お気軽にオフィスに遊びに来てくださいね！

mysql 新規データベース作成

mysql> create user ユーザー名 IDENTIFIED BY 'パスワード';
mysql> grant all privileges on プロジェクト名.* to 'ユーザー名';

データベースの中身をみるコマンド

mysql> show databases;

テーブル一覧表示

mysql> show tables from プロジェクト名;

テーブルの確認

mysql> describe テーブル名;

テーブルの削除

mysql> drop table 削除したいテーブル名;

テーブルにある見えてるレコードを全消去(以前消したデータの表面しか消せてないため、idは残る)

mysql> delete table cart_items;

テーブルのレコード全てを全消去する(表面上消せていたものも含め)

mysql> truncate table cart_items;

指定したテーブルの全レコードの確認

mysql> select * from テーブル名;

データベースの特定レコードのみ抽出

mysql> select name, email from users;

データベースの特定レコードにWhere句で条件を付けて抽出

mysql> select * from users where id <= 1;

//=を使えば当然idが1のレコードを抽出
mysql> select * from users where id = 1;

複合条件andを使ってidが3以上で、なおかつ、nameがhogehogeのものを抽出

select * from user where id >= 3 and name = ‘hogehoge’;

複合条件orを使ってidが3以上、または、nameがhogehogeのものを抽出

select * from user where id >= 3 or name = ‘hogehoge’;

あいまい検索（like, %, _）idが3以上で、nameの頭文字が「ho」の人」を取得したいとき

select * from user where id >= 3 and username  like ‘ho%’;
//「%」はho に続く任意の0以上の文字列をあらわし、「_」はho に続く任意の1文字をあらわします。

ORDER BYを使って商品の価格が高い順に並び替え(itemsテーブルのamountカラムを使っています)

select * from items order by amount desc;
//ascは昇順でデフォルトのため入力しなくてもかまわない
//descは降順、入力必須

環境

Windows 10 pro
Ubuntu 18.04 LTS
ruby 2.5.1p57 (2018-03-29 revision 63029) [x86_64-linux]
Rails 5.2.4
gem 2.7.6
Bundler version 2.0.2

mysqlの前に

github上で

$ rails _5.2.4_ new example -d mysql

をして新しい環境を作った後、
Gemfileを

gem 'mysql2', '0.5.2'

と編集されたものを、
git pullした後に、
WSL上でbundle installしようとしたが、

$ bundle install

  * * *

Fetching mysql2 0.5.2
Installing mysql2 0.5.2 with native extensions
Gem::Ext::BuildError: ERROR: Failed to build gem native extension.

  * * *

-----
mysql client is missing. You may need to 'apt-get install libmysqlclient-dev' or 'yum install mysql-devel', and try again.
-----
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.  Check the mkmf.log file for more details.  You may
need configuration options.

  * * *

An error occurred while installing mysql2 (0.5.2), and Bundler cannot continue.
Make sure that `gem install mysql2 -v '0.5.2' --source 'https://rubygems.org/'` succeeds before bundling.

In Gemfile:
  mysql2

とエラーが出てしまった（汗）

-----
mysql client is missing. You may need to 'apt-get install libmysqlclient-dev' or 'yum install mysql-devel', and try again.
-----

この箇所を参考に

$ sudo apt-get install libmysqlclient-dev

した後、

Make sure that `gem install mysql2 -v '0.5.2' --source 'https://rubygems.org/'` succeeds before bundling.

この箇所を参考に

$ gem install mysql2 -v '0.5.2' --source 'https://rubygems.org/'

Successfully installed mysql2-0.5.2
Parsing documentation for mysql2-0.5.2
Installing ri documentation for mysql2-0.5.2
Done installing documentation for mysql2 after 0 seconds
1 gem installed

こうすると、

$ bundle install
$ bundle update

がそれぞれ実行できて、gemの環境が整った(^^)/

mysqlを使えるようにする

試しにmysqlの状態を確認すると、

$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

mysqlをstartさせようと試みた後、

$ sudo service mysql start
 * Starting MySQL database server mysqld                                                                                                                           
No directory, logging in with HOME=/

もう一度、mysqlの状態を確認すると、

$ mysql
ERROR 1045 (28000): Access denied for user 'example'@'localhost' (using password: NO)

こちらのサイトによると、
パスワードを設定しなおさなければならないらしい。

$ sudo mysqld_safe --skip-grant-tables &

セーフモードで実行したのち、

$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
// データベースの状態を確認したよ

mysql>  update user set authentication_string=password("パスワード") where user='root';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
// パスワードは英数字と文字を組み合わせないとだめですよ

mysql> update user set authentication_string=password("英数字と文字をいれたパスワード") where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

$ sudo service mysql start
 * Starting MySQL database server mysqld     

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

こういう状態になりました(^▽^)/

素人がやっているので説明不足等あるとは思いますが、
ご指摘の程、よろしくお願いします！！

rails 新規アプリ作成時に、Mysqlにてbundle exec rake db:createが上手くいかない解決記録（自分用）

①　bundle exec rails new　アプリ名 . -B -d mysql --skip-test --skip-coffee

②　bundle exec rake db:create

error文、
rake aborted!
No Rakefile found (looking for: rakefile, Rakefile, rakefile.rb, Rakefile.rb)

色々試し、不要なコマンドは実行したと思われるが、次回はここから行う。

$ ①　mysql.server start 

　②　$ bundle exec rake db:create RAILE_ENV=development

＊rake aborted!

No Rakefile found (looking for: rakefile, Rakefile, rakefile.rb, Rakefile.rb)　　が出る場合は、rakefileがあるディレクトリにcdで移動後、再度$ bundle exec rake db:create RAILE_ENV=developmentで

無事にCreated database となった。

クエリに関する戦略

SELECT * を避ける

一つだけ言えることは、そのクエリですべてのカラムが必要になることは、ほとんどないということです。
特に、複数のテーブルを結合した際に取得する行の数は計り知れません。

-- 3つのテーブルすべての行から取得される!
SELECT * FROM a
LEFT JOIN b 
ON a.id = b.a_id
LEFT JOIN c
ON a.id = c.a_id

取得する列はすべて明示的に選択してあげるべきでしょう。

この罠に陥りがちなのが、あなたがORMフレームワークを利用しているときです。

デフォルトのクエリは、たいていすべての行を取得しようとするはずです。

//これは先程のクエリと同じ問題を抱えている
$a_table = TableRegistry::get('a');
$a_table->find('all')
    ->contain(['b', 'c']);

確かにデメリットも存在します。
第一に、もし100の行が必要になるときは、コードがスマートではなくなることです。

$a_table = TableRegistry::get('a');
$a_table->find('all')
    ->select('hoge', 'foo', 'bar' //....これが後100回続く
    ->contain(['b', 'c']);

表示されている表に新たなカラムが必要になるかもしれません。
その際には、表示する側とクエリを発行する側の双方で、確実に修正をする必要があります。

多くのコードの記述を省く*の使用か、パフォーマンスの選択になることでしょう。
この場合なら、パフォーマンスを取るほうが優勢に思えます。

WHEREでかける条件をHAVINGで書かない

次のSQL文は、富山県の60歳以上の人数を取得するためのものです。

SELECT name, age
FROM people
WHERE pref = '富山'
GROUP BY name
HAVING age >= 60

あなたは違和感に気づくことになるでしょう。わざわざage >= 60という条件を、HAVINGで書く理由はどこにも見当たりません。

このように書き直されたクエリは、全く同じ結果を返すことになります。

SELECT name, age
FROM people
WHERE pref = '富山'
AND age >= 60
GROUP BY name

見出しにあるように、後者のwhereで書かれたクエリのほうがパフォーマンスは優れていることでしょう。

ではこの2つのクエリの違いはどこにあるのでしょうか?
単純なSQLの実行順序を見ていきましょう。

簡単なイメージです。左の数字は、取得された件数を表しています。
WHEREで絞り込まれた場合には、早い段階で取得件数は30件まで絞り込まれています。
その後の作業もスムーズに進行することでしょう。

HAVINGは結果セットに対して絞り込まれます。
HAVINGは全行をスキャンしてソートしてから絞り込みを実行するでしょう。
HAVINGは基本的にや集約された結果に対して使用されるものです。

ソートはコストが高い

ソートは思っているよりもコストの高い操作です。
ORDER BYで順序を明示的に指定するのはもちろんですが、
以下の操作においても、暗黙のソートが使われています。

• GROUP BY
• DISTINCT
• 集約関数(SUM、COUNT、AVG、MAX、MIN)
• 集合演算子（UNION、INTERSECT、EXCEPT）
• ウィンドウ関数

この話を聞いて不安になる必要はありません。インデックスがソートに関する問題を解決してくれます。
最も利用されることの多いB木インデックスは、既にデータをソート順に格納しています。
逆に言えば、インデックスを用いていない列に対してはソートを利用するのは、良い考えではないでしょう。

また、下記にあげる列に対してはインデックスは使用されません。

• COUNT(*)や、price * 1.08など、計算された列
• 結合テーブルに対するソート

しかし、投稿に関するお気に入りの総数の多い順で並び替えたい欲求は自然と生まれてくるものです。
そのようなソートを実現させる方法は、設計に関する戦略をご覧ください。

アプリケーションコードとも向き合う

SQLの最適化は、クエリのみにとどまる話ではありません。

例えば、あなたはORMが発行するクエリを見たことはありますか？
一度も見たことがないというなら、仕事中にこの文章を眺めている場合ではありません。
確かにORM魔法のようにクエリを発行してくれますが、現実ともしっかり向き合うべきなのです。

代表的な話題としては、「N + 1問題」などがあります。
あなたの使用しているフレームワークは、Eager loadingをサポートしていますか？

また、全く必要のないクエリをアプリケーションコード内で発行していることに気づくきっかけになるかもしれません。

データベースとアプリケーションコードでは、それぞれ苦手なことと得意なことがあります。
例えば、データベースは複雑な文字列の処理は苦手なので、アプリケーションコードに任せるべきでしょう。
反対に、行をカウントするなら、データベースのほうが適切です。

インデックスに関する戦略

インデックスを知る

パフォーマンスの最適化について、インデックスの話題は欠かせません。
インデックスについて、「特定の単語について調べたいとき、本を全ページ見開いて探すのでなく索引を使って探す」なんて例えがよく使われます。

場合によっては、データ行を参照せずに値を取得することも可能です。（カバリングインデックス)

MySQLにおいて一般的なのは、B木インデックスです。

インデックスをむやみやたらに貼らない

インデックスが使われたら早くなるからといって、すべてのカラムにペタペタ貼るべきではありません。
そもそも、基本的には一つのクエリでは一つのインデックスしか使用できませんし、インデックスが多すぎる場合には、挿入、更新、削除といった動作のコストが高くつきます。

テーブルの設計にあった、最適なインデックスを選択しましょう。
に主キーとUNIQUEキーを設定している時点で自動でインデックスので、わざわざさらにインデックスを指定する必要はありません。
また、重複インデックスに対しても無力です。全く同じインデックスが作成されたとしても、警告を出したりしてくれることはないでしょう。

カーディナリティの高いものを選ぶ

最適なインデックスを選択する指標となるものが、カーディナリティです。

カーディナリティとは、そのカラムの持つデータの種類の度合いです。

例えば、性別を表す列なら、男女の2種類しかないので、カーディナリティは低いです。
対して、誕生日はカーディナリティの高い列だと言えます。

インデックスは、カーディナリティの高い列に対して効果的です。
カーディナリティの低い列にインデックスを利用した場合には、かえって逆効果になる可能性もあります。

複合インデックスを理解する

複合インデックスは、列の順序が重要です。
次のようなインデックスがあったとします。
`ADD INDEX idx(sex, birth_date)'

次のクエリはいずれもインデックスが使用されません。

SELECT first_name, last_name
FROM users
WHERE birth_date = '1970-11-11'

SELECT first_name, last_name
FROM users
WHERE sex = '男' or birth_data = '1970-11-11'

このインデックスにはまだ問題があります。
通常は、WHEREでは、カーディナリティの高い選択から初めたほうが、パフォーマンスはよくなります。
100→50→2の順序で数が絞られていくよりも、
100→4→2の順序で進めたほうがよいのは直感的に明らかです。

インデックスの使用されない方法

あなたがもしインデックスを使用したくないのなら、以下の方法を検討してみるべきです。

列を加工する

SELECT age
FROM user
WHERE age + 10 > 60

インデックスを利用する列は単純にするべきです。もとの列を加工してやったら、MySQLはそれを理解することができません。

暗黙の型変換

SELECT age 
FROM user
WHERE age > '60'

WHEREの右辺の値が今度は文字列になっています。
このような場合には、ageを文字列に変換してから比較を行います。
このような場合にも、インデックスの利用は難しいでしょう。

中間一致または後方一致

SELECT name
FROM user
WHERE name LIKE '%山%'

このようなクエリは非常に便利ですが、インデックスは使われてくれません。

前方一致の場合はインデックスを利用してくれます。

SELECT name
FROM user
WHERE name LIKE '%山'

or条件

SELECT name
FROM user
WHERE name '山田' or '山中'

このクエリはこのように書き換えることが可能です。

SELECT name
FROM user
WHERE name = '山田'
UNION ALL
SELECT name
FROM user
WHERE name = '山中'

否定条件

SELECT name
FROM user
WHERE name <> '山田'

否定条件は、返却する行数が多すぎて、オプティマイザがシーケンシャルスキャンのほうが有利だと判断する可能性があります。

設計に関する戦略

最適なデータ型を選択する

データ型の選択に際して、最も大切で当たり前なことは、小さなデータ型を選択することです。
text型より`varchar'型、のほうがパフォーマンスに与える影響が良いことは言うまでもありません。

またもや当たり前のことを述べますが、一般的に正しいデータ型を選択するべきです。
あなたのデータベースは数字や日付を格納するはずのデータをvarchar型になっていたりしませんか？

一般に文字列を比較するよりも整数を比較するほうがコストがかかりません。

あえて正規化を崩す

私達は幼い頃から、データベースは適切に正規化するように母親によく諭されたものです。

よくある正規化されたデータベースの例
booksテーブル

title	author_id	publish date	ISBN
吾輩は猫である	1	1905-10-06	0000000000000
伊豆の踊り子	２	1926-01-01	0000000000001
雪国	2	1927-06-12	0000000000002
ノルウェイの森	3	1987-09-04	0000000000003

authorsテーブル

author_id	name	sex	died
1	夏目漱石	男	1916-12-09
2	川端康成	男	1972-04-16
3	村上春樹	男	9999-99-99

テーブルを正規化するのは、良い設計です。
この例では、booksテーブルから、冗長な著者の情報はありません。
データの更新は正規化されていないものと比べて、きっと早くなることでしょう。

しかし、正規化されたデータベースには短所もあります。
青空文庫に掲載されている本を探すクエリを考えてみましょう。

SELECT books.title, authors.name
FROM books
INNER JOIN authors
ON books.author_id = authors.id
WHERE auhtors.died >= DATE_ADD(CURRENT_DATE(), INTERVAL 50 YEAR)
ORDER BY authors.name 

まず、テーブルを結合すると、多少なりともコストが掛かることは頭の片隅にいれておきましょう。
今回の例は単純な結合すみましたが、複雑な結合が介入するとその分コストは大きくなることでしょう。
さらに、インデックスは期待できるものではもうありません。

このクエリを最適化する方法が、非正規化です。

title	publish date	ISBN	name	sex	died
吾輩は猫である	1905-10-06	0000000000000	夏目漱石	男	1916-12-09
伊豆の踊り子	1926-01-01	0000000000001	川端康成	男	1972-04-16
雪国	1927-06-12	0000000000002	川端康成	男	1972-04-16
ノルウェイの森	1987-09-04	0000000000003	村上春樹	男	９９９９−９9-99

クエリは単純になります。

SELECT title, name
FROM books
WHERE died >= DATE_ADD(CURRENT_DATE(), INTERVAL 50 YEAR)
ORDER BY name 

パフォーマンス上の問題は解決できましたが、非正規化の欠点は正規化の長所がすべて失われることです

もし著者の情報を更新や削除をしようとするなら、すべての更新を確実に行われることを達成しなければなりません。

正規化と非正規化は重大なトレードインです。
本当にその非正規がが必要なのか、正規化を崩すことによりデメリットを上回るメリットがあるのか、もう一度慎重に考えてみましょう。

キャシュされた結果をもたせる

先ほどと同じテーブルを使用して、出版した本が多い順著者を並べ替えます。

SELECT count(*) AS book_cnt authors.name
FROM books
LEFT JOIN authors
ON books.author_id = authors.id
GROUP BY books.author_id
ORDER BY book_cnt

ORDER BYに指定したbook_cntには、インデックスは使用されません。
そのため、ファイルソートは覚悟しておく必要はあるでしょう。
また、この例では比較的簡単にすみましたが、集計を表示するために複雑なサブクエリが必要になるかもしれません。

そんなときは、authorsテーブルに予めbook_cnt列をもたせておきます。
booksテーブルに新たな書籍が追加されたタイミングで、同時にbook_cnt列も更新して置く方法が一般的です。

booksテーブルを更新する際のコストはかかる、冗長構成になるなどのデメリットは存在します。

リアルタイム性があまり重要でない数をカウントする必要があるなら、キャッシュテーブルを使用するてもあります。

例えば、コミケの参加者を得るために、入場者がカウントされるたびに他のテーブルを更新するのは現実出来ではありません。

そのような時には、1時間おきくらいにバッチ処理などで集計した結果を格納するキャシュテーブルを生成するのが有効です。

最後に

パフォーマンスを改善するヒントを提供してきましたが、それらが実際に有効かどうかは実装に依るところです。
パフォーマンスを改善したいのなら、必ずプロファイリングや測定が重要になってきます。

情報を鵜呑みにするのではなく。いかに活用するかを考えるべきでしょう。

参考書籍

実践ハイパフォーマンスMySQL
SQLアンチパターン
プログラマのためのSQL
達人に学ぶSQL徹底指南書

困ったら公式