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

投稿日：2019-07-07T22:01:03+09:00

my.cnfの場所にハマった

自分の環境でのmy.cnfの場所
mysql --help | grep my.cnf
 order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
てっきりmy.cnfは~/etc/my.cnfにあるんだと思って確認しようと下記を実行
cat /etc/my.cnf
すると
cat: /etc/my.cnf: No such file or directory
なんでだよ！！！

調べまくった結果
ここにあった
usr/local/etc/my.cnf

　 Tweet
Qiitaで続きを読む

投稿日：2019-07-07T20:37:31+09:00

MySQLが起動できない「drwxr-xr-x 31 _mysql _mysql 992 4 21 21:31 mysql」

MySQLをターミナルで起動しようとした際、なぜか起動できない問題が発生しました。
（すみません、、エラー文を撮り忘れたので、原因と対処方法をまとめておきます）

参考記事
https://qiita.com/gcyata/items/ebbb98602eafc149abb2

原因

色々と調べると「パーミッション」という権限の問題で、MySQLが起動できないご様子。

対処方法

MySQLが居るであろうディレクトリまで、cdコマンドで移動（ターミナル上）

cd usr/local/var

pwdコマンドで自分が今いる、ディレクトリを確認
/usr/local/var
OK！

次に下記のコマンドをターミナルで叩く

ls -ll
そうするとusr/local/var配下で起動中と思われるプロセスが表示
total 0
drwxrwxr-x   4 [ここは自分のユーザー名]  admin   128  4  3 20:58 homebrew
drwxr-xr-x   4 [ここは自分のユーザー名]  admin   128  4 21 08:45 log
drwxr-xr-x  31 _mysql               _mysql  992  4 21 21:31 mysql
drwx------  26 [ここは自分のユーザー名]  admin   832  7  7 08:13 postgres
drwxr-xr-x   2 [ここは自分のユーザー名]  admin    64  4 21 08:43 run
↑なんか１つだけ _mysql が管理者になってるプロセスが居るぞ〜！

これを下記のコマンドで管理者変更させる
$ sudo chown -R [ユーザー名] mysql

もう１度、ls -ll
total 0
drwxrwxr-x   4 [ここは自分のユーザー名]  admin   128  4  3 20:58 homebrew
drwxr-xr-x   4 [ここは自分のユーザー名]  admin   128  4 21 08:45 log
drwxr-xr-x  31 [ここは自分のユーザー名]  _mysql  992  4 21 21:31 mysql
drwx------  26 [ここは自分のユーザー名]  admin   832  7  7 08:13 postgres
drwxr-xr-x   2 [ここは自分のユーザー名]  admin    64  4 21 08:43 run
ｷﾀ━━━━(ﾟ∀ﾟ)━━━━!!

この状態で、mysql.server startすれば無事にMySQLが起動できますた。

　 Tweet
Qiitaで続きを読む

投稿日：2019-07-07T19:11:48+09:00

【MySQL】Mysql2::Error: Incorrect string value 【エラー】

　開発環境では問題なく行えていたseedファイルの読み込みですが、本番環境ではseedファイルを読み込む際に、MySQLエラーが発生しました。その対応について記述します。

1. エラー発生状況とエラー内容

ターミナル(本番環境）

# seedファイルの読み込みコマンド
$ rails db:seed RAILS_ENV=production
# 発生したエラー
 ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect string value: '\xE5\x8C\x97\xE6\xB5\xB7...' for column 'name' at row 1: INSERT INTO 'areas ('name') VALUES ('北海道')

2. 開発環境と本番環境の文字コードを確認

　まずは、ローカル環境を確認する

ローカル環境

-- mysqlにログイン後、データベース一覧を出力
mysql> SHOW databases;
-- 該当のdatabaseを確認後、databaseを選択
mysql> use applicable_database --[該当のデータベース名]
-- 文字コードを確認
mysql> show variables like '%char%';
+--------------------------+------------------------------+
| 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     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mysql56/charsets/ |
+--------------------------+------------------------------+

　同様に、本番環境を確認すると

本番環境

mysql> show variables like '%char%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8                         |
| character_set_connection | utf8                         |
| character_set_database   | latin1                       |
| character_set_filesystem | binary                       |
| character_set_results    | utf8                         |
| character_set_server     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mysql56/charsets/ |
+--------------------------+------------------------------+

　上記を比べると、character_set_databaseのValueが
　ローカル環境では、utf8
　本番環境では、latin1
　となっています。

　latin-1では日本語対応していないため、MySQLエラーが出ていたみたいですね。
　ためしに、本番環境のdatabaseを削除して、utf-8を指定して作り直してみました。

本番環境

-- mysqlにログイン後、データベース一覧を出力します
mysql> SHOW databases;
-- 該当のdatabaseを確認後、削除コマンドによりデータベースを削除します
mysql> drop database applicable_database --[該当のデータベース名]
-- 同様の名前でdatabaseを作成、ただしオプションで文字コードを指定する
mysql> create database book_reviews_production default character set utf8;
-- databaseの文字コードを確認する
mysql> show variables like '%char%';
+--------------------------+------------------------------+
| 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     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mysql56/charsets/ |
+--------------------------+------------------------------+

　文字コードの変更できました。
　この後のmigrationとseedの読み込みコマンドは問題なく行えました。

3. 原因

　database.ymlを確認してみると、charsetの指定が抜けていました
　追記することで、Railsコマンドでdatabaseを作成した際にも、文字コードが変更されました。

database.yml

default: &default
  adapter: mysql2
  # ↓これ
  charset: utf8
  encoding: utf8
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

4. あとがき

　調べてみると、databaseをdropすることなく文字コードの変更も行えるみたいです。
　重要なレコードがある場合は、そちらをご活用ください。

　 Tweet
Qiitaで続きを読む

投稿日：2019-07-07T15:59:18+09:00

PHP+phalconでWEBアプリケーションを作成する

業務で、PHPの新規案件がありそうなので、簡単に実装できるWEB Frameworkを探していたら、phalconを見つけました。phalconを使ってcrudを実装するまでを投稿します。

【環境】

カテゴリ値

os windows 10 home 64bit

php PHP 7.3.5( ZTS MSVC15 (Visual C++ 2017) x64 )

1. phalcon

PHPのWEBフレームワーク「phalcon」をつかってみます。

1-(1). ダウンロード

https://phalconphp.com/ja/download/windows

1-(2). インストール

ダウンロードしたファイルを解凍し、「php_phalcon.dll」を「c:\php\ext」にコピーする。

1-(3). php.iniを編集

php.iniを開いて、extensionに以下を追加する。
extension=php_phalcon.dll
2. phalcon-devtools

Phalconの開発ツールです。Phalconで開発する場合は、この開発ツールを使用します。

2-(1). ダウンロード

phalcon-devtools

2-(2). パスを通す

ダウンロードしたphalcon-devtoolsのディレクトリを、環境変数のPathに追加します。
C:\data\php\phalcon\phalcon-devtools
2-(3). プロジェクトを作成する。

phalconコマンドを使ってプロジェクトを作成します。
phalcon project --name testprj1 --enable-webtools
cd testprj1
2-(4). 動かしてみる。

ビルトインウェブサーバーでプロジェクトを起動してブラウザで確認する。
php -S localhost:8000 -t public .htrouter.php
または、以下のコマンドでも起動できます。
phalcon serve
2-(5). 停止

「Ctrl-C」

3. INVO

PHPのWebFramework「Phalcon」のプロジェクトのテンプレートです。dev-toolsのprojectコマンドでプロジェクトを作成してもいいのですが、これは認証などの機能があらかじめ装備されているので、このテンプレートを使うほうがより効率的です。こちらもインストールします。

3-(1). インストール

3-(1)-1.ダウンロード

https://github.com/phalcon/invo

3-(1)-2. インストール

ダウンロードしたファイルを適当なフォルダに解凍するだけです。

3-(1)-3. DBセットアップ

3-(1)-3-1. データベースを作成する

プロジェクトが使用するデーターベースを作成します。以下は、MySQLを使う場合。
MySQLに接続し、データベースを作成します。データベース名は「invodb」とします。
create database invodb
3-(1)-3-2. プロジェクトをデータベースに接続できるようにする。

app\config\config.iniを開いて、databaseの接続情報を編集します。
[database]
adapter  = Mysql
host     = localhost
username = testuser
password = ************************
dbname   = invodb
charset  = utf8
3-(1)-3-3. INVOのテーブルやデータを登録する。

コマンドプロンプトから、以下のコマンドを実行してテーブルとデータを登録します。
mysql -u testuser -p ***************** -D invodb < C:\data\php\phalcon\invo-master\schemas\invo.sql
3-(1)-3-4. 動作確認

PHPのビルトインサーバを起動し、INVOを動かしてみます。
php -S localhost:8000 -t public .htrouter.php
4.INVOにCRUDを追加する。

phalcon devtoolsには、Ruby on RailsやelixirのPhoenixと同じように、crudを自動的に生成するコマンドが装備されています。INVOにCRUDを追加してみます。

4-1.データベースにテーブルを追加します。
CREATE TABLE `department` (
  `deptcode` varchar(6) NOT NULL,
  `deptname` varchar(255) NOT NULL,
  PRIMARY KEY (`deptcode`)
)
4-2.「scaffold」を実行する。

INVOのルートフォルダに移動して、以下のコマンドを実行します。
phalcon scaffold department --template-engine=volt
4-3.プロジェクトのフォルダ／ファイル構成

以下のようなフォルダ／ファイルが追加されます。
└─app
・・・・・
    ├─controllers
    │      DepartmentController.php
・・・・・
    ├─models
    │      Department.php
・・・・・
    └─views
        ├─department
        │      edit.volt
        │      index.volt
        │      new.volt
        │      search.volt
・・・・・
        └─layouts
                department.volt
・・・・・
5.追加したCRUDをINVOから呼び出す。

修正するファイルですが、以下のファイルになります。

5-(1). app\library\Elements.php

navibarに「department」のcrudを追加します。
class Elements extends Component
{
    private $_headerMenu = [
        'navbar-left' => [
// <-- 追加
            'department' => [
                'caption' => 'Department',
                'action' => 'index'
            ],
// --> ここまで
5-(2). app\plugins\SecurityPlugin.php

セキュリティレベルを設定します。
・・・
class SecurityPlugin extends Plugin
{
//・・・
public function getAcl()
{
// このif文はコメントしないと動かない。対応する}もコメントしてください。
// if (!isset($this->persistent->acl)) {

//・・・
$privateResources = [
// <-- 追加
    'department'    => ['index', 'search', 'new', 'edit', 'save', 'create', 'delete'],
// --> ここまで
//・・・
5-(3). 確認

WEBサーバを起動してブラウザで確認してみましょう。
phalcon serve
HTMLは日本語化などの微調整が必要ですが、テーブルさえ登録すればCRUDが簡単に実装できます。

PHPでの開発なら、Phalconはなかなかいい感じです。

参考サイト

Webアプリ開発に最適！2019年おすすめPHPフレームワーク10選
 Phalcon Devtoolsを使ったMVCプロジェクト作成からHelloWorldまで

カテゴリ	値
os	windows 10 home 64bit
php	PHP 7.3.5( ZTS MSVC15 (Visual C++ 2017) x64 )

　 Tweet
Qiitaで続きを読む

投稿日：2019-07-07T14:33:33+09:00

チョコチョコMySQLを勉強する「２回目」ログによりデータ復旧

はじめに

「MySQLは過去の特定の時刻までの状態に復旧までできる」という話をDBAからよく聞いていますが、「凄いなぁ」と思いながら、MySQLは一体どんな仕組でリストアやリカバりをできているのか。

UPDATE文から

問題の答えを探すため、まずUPDATE文から行きましょう。
仮に下記のようなテーブルがあります。
mysql> create table T(ID int primary key, c int);
mysql> insert into T (ID,c) VALUES (1,2),(2,3);
mysql> select * from T;
+----+------+
| ID | c    |
+----+------+
|  1 |    2 |
|  2 |    3 |
+----+------+
2行目レコードに１を増やす場合、下記のSQL文を使います。
 mysql> update T set c=c+1 where ID=2;
前回にSELECT文の実行流れからMySQLのアーキテクチャーを覗きましたが、UPDATE文の流れは図のように基本的同じです。

前の図をそのまま持ってきましたが、SELECTクエリとの違いはクエリキャッシュにある。
UPDATEクエリはクエリキャッシュにヒットする必要はないが、前回に言った通りこのSQL文を実行すれば、テーブル「T」に関するクエリキャッシュは全て無効になりますので是非ご注意ください。これはクエリキャッシュが廃棄される重要な理由でもあります。
クエリキャッシュ以外、もう一つとても重要な違うところがあります。あれは今回の主役です。UPDATEクエリは二つログに関わっている----InnoDB（REDO）ログとバイナリログです。
図書館問題

この二つログは何物かを紹介する前に、分かりやすく説明するため図書館によくある問題から見てみましょう。
仮にある図書館に管理員は一人しかいない、お客様が本を返却にくる時、管理員は大体二つ選択がある。
・本を置くべき本棚に着いてから本を戻すからお客さんの返却を記録する
・一旦本をカートに入れてお客さんの返却を記録して、手が空いてる時カートに置ける本を本棚に戻す
お客さんがいっぱいある時、管理員は間違いなく後者にします。だって前者は時間がかかるのでお客さんたちを待たせないといけない。

ログ先行書き込み(WAL)

MySQLには同じ問題を持っている。もし更新ごとにデータファイルまで書き込むとディスクI/Oコストは非常に高くなってしまい、とても非効率になる。この問題を避けるため、MySQLのデザインナーは「本棚とカートの組み合わせ」に似たような方法を使いました。その方法はWAL(Write Ahead Logging,ログ先行書き込み)という技術と呼ばれる。
簡単にいうと、データファイルへの変更はログに記録され、つまり、変更内容を記述したログレコードが永続格納領域に書き出され、その後にのみ(テーブルやインデックスがある)データファイルに書き出されなければならないということです。
WAL技術のメリットは効率向上に限らないが、今回の主役InnoDB（REDO）ログと共に紹介しましょう。

InnoDB（REDO）ログ

主役登場！InnoDBログ（REDO）はWAL技術をInnoDBに実装されるもの。つまり、InnoDBのテーブルに対して行われた更新は、いったんInnoDB（REDO）ログに書き込まれてデータをメモリ（InnoDBバッファー）に保存します。データファイルへの書き込みは後回します。ディスクへの書き込み回数を減りながら、InnoDBログ（REDO）はデータの一貫性及びリカバリにもサポートします。ログファイルに書く以上、MySQLに障害があった時ログからデータを復旧することも可能です。データの一貫性については後で説明するので一旦飛ばしても大丈夫です。InnoDBにはREDOログ以外、UNDOログというログも存在します。UNDOログはトランザクションをロールバックするためのものですので、今回は展開せず、次回に説明します。

「図書館のカート」と同じメリットを持った同時に、InnoDB（REDO）ログはカートと同じ制限がある。サイズは限られています。InnoDB（REDO）ログの大きさはinnodb_log_files_in_groupとinnodb_log_file_size二つパラメタで指定されています。デフォルトで前者は2、後者は 5MBに設定されています。意味としては、InnoDB（REDO）ログを２個に分けて、それぞれの大きさは５MBにする。この設定でInnoDB（REDO）ログのファイル名はib_logfile0とib_logfile1です。

InnoDB（REDO）ログは図のようにサークルの形で動いている。write_posはREDOログを記録する位置を示して、REDOログを貯まるごとに進んでいます。check_pointはディスクへ反映されたREDOログの位置を示して、REDOログに関するメモリをデスクへフラッシュしづつ進んでいます。write_posからcheck_pointまでは利用できる空間です。ということで、もしwrite_posはcheck_pointを追いかけると「カートはいっぱいになってる」状態と同然、check_pointからログをディスクに反映しないと更新操作は仕方なくてブロックされる。図書館のケースで「お客さんを待たせないといけない」状態。この時更新のパフォーマンスは悪くなってしまうのでご注意ください。これもMySQLをチューニングする時確認ポイントの一つです。

バイナリログ

そろそろもう一つ主役ーバイナリログを紹介しましょう。バイナリログを知っている人は多いと思います。知っている通り、バイナリログには、テーブルやデータの更新操作が格納される。リカバリ。やることから見るとREDOログと似ているが、疑問①：REDOログとの違いところは何か、疑問②：二つログを存在する意味は何かを持ってるからこれから答えみましょう。

疑問①：InnoDB（REDO）ログとバイナリログの違いところは

１．InnoDB（REDO）ログはInnoDB、つまり、ストレージエンジン層なログです、InnoDBストレージエンジンではない（MYISAM、MEMORYなど）と、InnoDB（REDO）ログはありません。バイナリログはストレージエンジンにかかわらず、サーバ層のログです。
2．InnoDB（REDO）ログに書くのは「あるデータページに何を更新した」という物理的な内容です。バイナリログは「クエリ(statementフォーマット)あるいはレコード(rowフォーマット)」という論理的な内容です。
3．InnoDB（REDO）ログはサークルのかたちて空間が限られてうわ書きや再利用こともあります。バイナリログはずっとアペンドで書き込みます。

疑問②：InnoDB（REDO）ログとバイナリログ同時に存在する意味は

結論から言うと、２点があります。
1．二つログ（複雑なトラザンクションがある場合InnoDB UNDOログも含めて三つログ）に合わせてMySQLに「クラッシュセーフ」という能力をつけてデータの一貫性を保つこと。
わかり難いと思いますが、まずさっきのUPDATEクエリロジックの流れのをもっと具体的に見てみましょう。

図から見ると⑥InnoDB（REDO）ログへの書き込む完了する時点でコミットがまだ完成していなくて、⑦バイナリログへの書き込む完了次第UPDATEクエリがコミット完了。これは２フェイズこっミットと呼ばれる機能です。
リカバリする時、REDOログとバイナリログ両方に一致したことしか復旧しません。これでデータの一貫性を保証する。MYISAM、MEMORYなどREDOログを持ってないストレージエンジンはクラッシュセーフではない。

・もしバイナリログがなければ、REDOログの大きさが限られて空間が再利用されてるので、MySQLはクラッシュした直前の状態しか復旧しない、「特定の時刻までの状態に復旧」というリストアは不可能です。例としては誤操作であるデータをDELETEされた時救うてはなくなる。
・もしREDOログがなければ、データの一貫性を保証できません。上のフローチャートの例を見てください。もし⑥データ更新が成功して、⑦バイナリログへの書き込む前にクラッシュが発生すると、MySQLサーバはストレージエンジン側の変更を知らず、操作を無視した。例のUPDATEクレリにする場合、cはバイナリログの記録より1を大きくなってしまう。例としてはMySQLクラッシュで再起動によりデータの正確性はなくなる。

2. データの一貫性を保つ上、データの復旧は可能です。ついに最初の問題を答えるようになりました。実は仕組を理解した上で、リストア手順の理解はそんないに難しくない。リストアするため、まず定期的にダンプ（全データベースのバックアップ）しないといけない。頻度は週に一回や毎日など業務により違います。特定の時刻までの状態に復旧するため、

ステップ１：特定の時刻前に一番近いのダンプを見つけだす。
ステップ２：ダンプの時点から特定の時刻までのバイナリログを取得。
ステップ３：バイナリログを実行する。
ざっくりで操作手順ではないですが、やってみたければこの記事を参考すれば大丈夫だと思います。

まとめ

今回はリカバリやリストアの問題を踏まえてInnoDB（REDO）ログとバイナリログを紹介しました。
InnoDB（REDO）ログ物理的なログでMySQLにクラッシュセーフ能力を提供します。innodb_flush_log_at_trx_commitというパラメタでREDOログをディスクへ書き込み存続します。おすすめの設定は1で、意味としてはトラザンクションごとにディスクへ書き込む。これで障害によりデータを失な割れない。
バイナリログは論理的なログでMySQLにリストア能力を提供します。ダンプファイルと合わせて過去の特定の時刻までの状態に復旧までできます。

　 Tweet
Qiitaで続きを読む

投稿日：2019-07-07T13:53:46+09:00

MySQLでパフォーマンスの良いSQLを書くコツ

「コツ」と題しましたが、パフォーマンスの良いSQLって本当に難しい事で、ちょっとしたコツでなんとかなる問題ではないことは重々承知しています。
しかし最近、SQLは知っているもののパフォーマンスなんて何も意識した事がない方が結構いるのだなぁと実感することがあり、そういう方にパフォーマンス設計のさわりだけでも知っていて頂ければと思い、記事にしました。

この記事の対象者

SQLはよく書くものの、パフォーマンスを意識してSQLを書いたことがない方

環境

MySQL5.5〜5.7
エンジンはinnoDB

まずは自分が作ったSQLが速いか見てみる

SQLを実行して素早く返ってくるか？だけで判断せず、実行計画（explain）も見るようにしましょう。
実行計画とは、MySQLがどのような手順でSQLを解析、実行するかの計画を示してくれるものです。
SQLの頭にexplainとつけて実行するだけで見ることができます。
explain
select 
  * 
from 
  t1 inner join t2 on t1.t2_id = t2.id 
where 
  t2.name = 'aaa'
以下のような結果が取れます。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE t1 NULL ALL index_t1_on_t2_id NULL NULL NULL 341 10.00 Using where

1 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 db.t1.t2_id 1 100.00 NULL

実行計画の見方ですが、とりあえずtable、type、rows、Extraを見ておきましょう。

カラム説明

table この順でテーブルを解析するよ、ということ

type テーブルの検索方法。上から順に総なめしていくとか、インデックスを使用して高速に検索するとかいうことがわかる

rows 検索対象とする行数で、ここが多いとそれだけパフォーマンスが悪くなる

Extra もう少し詳細な解析内容が書かれる

以下のような結果は要注意！

table：絞り込みが効きにくい順序になっていないか

type：ALLが出ている

rows：何十万件にもなっている

Extra：Using filesortやUsing temporaryが出ている

逆にすばらしい実行計画は以下のような感じです

「PRIMARY」と出ている（主キー検索）

「eq_ref」と出ている（インデックス検索）

遅いSQLがアプリケーションの中にどれくらいあるのだろう？

slow logを出力するようにMySQLを設定しましょう。応答に○秒以上かかったSQLだけを収集してくれるログです。１秒とか１分とか３０分とか設定できます。

インデックス

インデックスとは辞書の索引のようなものです。テーブルごとにalter tableで追加していくことができます。主キーや外部キーには最初からインデックスが作成されます。
MySQLはインデックスのないカラムで検索すると、一番上から順に総なめして検索します。これがtype:ALLの状態です。件数が多いと非常に遅くなります。
インデックスはうまく使えば劇的に速くなります。

インデックスの作成

インデックスを検討するときは以下の観点で行いましょう。

対象テーブル：データ件数が多く、検索されることも多いテーブル

対象カラム：where・join・sortに頻繁に使用されるカラムで、かつデータのばらつきが多いカラム

ひとつのテーブルに複数作成することができますが、１回のSQLで１テーブル１つしか使用されません。たくさん貼っても意味がない場合もあるので注意して下さい。
データのばらつきですが、例えばフラグのような２値しか持たないカラムに対してインデックスを作成してもあまり効果が得られないということです。IDや作成日時など、ばらばらのデータには非常に効果的です。
また、インデックスを作成することで以下のようなデメリットもあります。

insertやupdateは遅くなる（同時にインデックスも作成するため）

インデックス自体もディスク容量を消費する

インデックスが効いてるか確認する方法

インデックスを貼ったあと、実行計画のpossible_keysとkeysを見てみましょう。

カラム説明

possible_keys 使用できるインデックスの候補

keys 実際に使用するインデックス

インデックス貼ったのに効かない！インデックスの効きを阻害する要因

関数

DATEDIFFとかSUBSTRINGとか、私はよく使ってしまうのですが、関数を使用したカラムはインデックスが全く効かなくなるので要注意です。

LIKE %text%

LIKE検索は前方一致のみインデックスが効きます。
text%→効く
%text%→効かない

他のインデックスが採用されている

複数のインデックスを貼っている時、どのインデックスを使用するかはMySQLが判断しますが、時に思いもよらぬインデックスを使用してしまうことがあります。
そういう場合は以下のような対策があります。

analize tableコマンド実行（インデックスの再編成）

USINGという構文を使用して使用するインデックスをMySQLに指示する

SQLがあまりにも複雑すぎる

副問合せしてCASE構文使ってJOINも多すぎてUNIONもしちゃって更にGROUP BY HAVING……こんなことになると、MySQLも限界です。
個人的には、SQLはシンプルイズベストにして、プログラムの方でできることはプログラムにやらせる、としています。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	index_t1_on_t2_id	NULL	NULL	NULL	341	10.00	Using where
1	SIMPLE	t2	NULL	eq_ref	PRIMARY	PRIMARY	4	db.t1.t2_id	1	100.00	NULL

カラム	説明
table	この順でテーブルを解析するよ、ということ
type	テーブルの検索方法。上から順に総なめしていくとか、インデックスを使用して高速に検索するとかいうことがわかる
rows	検索対象とする行数で、ここが多いとそれだけパフォーマンスが悪くなる
Extra	もう少し詳細な解析内容が書かれる

カラム	説明
possible_keys	使用できるインデックスの候補
keys	実際に使用するインデックス

　 Tweet
Qiitaで続きを読む

投稿日：2019-07-07T13:33:52+09:00

【PDO】DELETE文でデータを削除する方法

はじめに

DELETE文でデータを削除する方法について本やネットの情報から調べて理解したことをまとめました。
もし、書いていることに何か間違いがある場合はご指摘いただけると嬉しいです。

データを削除するSQL文

データを削除するにはDELETE文を使います。

DELETE文の書き方

DELETE FROM テーブル名 WHERE 条件;

DELETE文でデータを削除するコード例

簡易掲示板を実装することを仮定して解説します。

開発環境

MAC
MAMP

ファイル

index.php
delete.php

画面

投稿一覧
削除画面

以下のようなデータベースとテーブル、レコード登録されていると仮定して解説します。

データベース名： test_db6
テーブル名： users

id	name	message
1	waka3	おはようございます
1	waka	こんばんわ

index.php（投稿一覧）

index.php

<?php

try {

    $user = "ここにユーザー名が入ります";
    $password = "ここにパスワードが入ります";

    $dbh = new PDO("mysql:host=localhost; dbname=test_db6; charset=utf8", "$user", "$password");       

    $stmt = $dbh->query('SELECT * FROM users');

    $result = 0;

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch (Exception $e) {
          echo 'エラーが発生しました。:' . $e->getMessage();
}

?>
  <!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>簡易掲示板</title>
  </head>
  <body> 
      <h2>簡易掲示板</h2>
        <?php
            echo "<table>\n";
              echo "<tr>\n";
              echo "<th>お名前</th><th>メッセージ</th>\n";
              echo "</tr>\n";
              foreach ($result as $user) {
                echo "<tr>\n";
                echo "<td>" . $user["name"] . "</td>\n";
                echo "<td>" . $user["message"] . "</td>\n";
                echo "<td>\n";
                echo "<a href=delete.php?id=" . $user["id"] . ">削除</a>\n";
                echo "</td>\n";
                echo "</tr>\n";
              }
            echo "</table>\n";
        ?>
  </body>
</html>

delete.php（削除画面）

waka こんばんわと表示されているデータを削除してみます。
削除ボタンを選択すると、delete.phpに遷移します。

delete.php

<?php

try {

    $user = "ここにユーザー名が入ります";
    $password = "ここにパスワードが入ります";

    $dbh = new PDO("mysql:host=localhost; dbname=test_db6; charset=utf8", "$user", "$password");

    $stmt = $dbh->prepare('DELETE FROM users WHERE id = :id');

    $stmt->execute(array(':id' => $_GET["id"]));

    echo "削除しました。";

} catch (Exception $e) {
          echo 'エラーが発生しました。:' . $e->getMessage();
}

?>

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>削除完了</title>
  </head>
  <body>          
  <p>
      <a href="index.php">投稿一覧へ</a>
  </p> 
  </body>
</html>

index.php（投稿一覧）

データが削除されました。

データベースの中身も削除されました。

　 Tweet
Qiitaで続きを読む

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

my.cnfの場所にハマった

自分の環境でのmy.cnfの場所

なんでだよ！！！

MySQLが起動できない「drwxr-xr-x 31 _mysql _mysql 992 4 21 21:31 mysql」

原因

対処方法

【MySQL】Mysql2::Error: Incorrect string value 【エラー】

1. エラー発生状況とエラー内容

2. 開発環境と本番環境の文字コードを確認

3. 原因

4. あとがき

PHP+phalconでWEBアプリケーションを作成する

1. phalcon

1-(1). ダウンロード

1-(2). インストール

1-(3). php.iniを編集

2. phalcon-devtools

2-(1). ダウンロード

2-(2). パスを通す

2-(3). プロジェクトを作成する。

2-(4). 動かしてみる。

2-(5). 停止

3. INVO

3-(1). インストール

3-(1)-1.ダウンロード

3-(1)-2. インストール

3-(1)-3. DBセットアップ

3-(1)-3-1. データベースを作成する

3-(1)-3-2. プロジェクトをデータベースに接続できるようにする。

3-(1)-3-3. INVOのテーブルやデータを登録する。

3-(1)-3-4. 動作確認

4.INVOにCRUDを追加する。

4-1.データベースにテーブルを追加します。

4-2.「scaffold」を実行する。

4-3.プロジェクトのフォルダ／ファイル構成

5.追加したCRUDをINVOから呼び出す。

5-(1). app\library\Elements.php

5-(2). app\plugins\SecurityPlugin.php

5-(3). 確認

チョコチョコMySQLを勉強する「２回目」ログによりデータ復旧

はじめに

UPDATE文から

図書館問題

ログ先行書き込み(WAL)

InnoDB（REDO）ログ

バイナリログ

疑問①：InnoDB（REDO）ログとバイナリログの違いところは

疑問②：InnoDB（REDO）ログとバイナリログ同時に存在する意味は

まとめ

MySQLでパフォーマンスの良いSQLを書くコツ

この記事の対象者

環境

まずは自分が作ったSQLが速いか見てみる

遅いSQLがアプリケーションの中にどれくらいあるのだろう？

インデックス

インデックスの作成

インデックスが効いてるか確認する方法

インデックス貼ったのに効かない！インデックスの効きを阻害する要因

関数

LIKE %text%

他のインデックスが採用されている

SQLがあまりにも複雑すぎる

【PDO】DELETE文でデータを削除する方法

はじめに

データを削除するSQL文

DELETE文の書き方

DELETE文でデータを削除するコード例

開発環境

ファイル

画面

index.php（投稿一覧）

delete.php（削除画面）

index.php（投稿一覧）

日付別

タグ一覧