Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL チューニングのど本命!索引チューニング

MySQLのチューニングで一番効果があるものは、パラメータのチューニングでもなく、はたまた、マシンのスペックでもなく、

索引のチューニング

です。私自身、サーバをスケールアウトしてみたり、パラメータをいじってみたりしてみましたが大した効果はないんですよね。やはり、目で見て効果が高いのは、やはり索引を適切に設定することでした。

f:id:hit10231023:20180309104332j:plain

今回、MySQLオプティマイザがぁ。。とか、オンメモリーにするためにはーとかの話は抜きで、何を勘所にして、どのように索引チューニングしていくかというのを中心に書いていきます

続きを読む

超便利!MySQL テーブル定義のデフォルト値で関数をつかっちゃおう

いままでなんで、きずかなかったのだろう。。

例えば、こんなテーブルがあったとして

DROP TABLE IF EXISTS test_tab1 ;

CREATE TABLE `test_tab1` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Numeric_only_date_time` varchar(8) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

idはオートインクリメント値

numeric_only_date_timeは、現在の日付を、数字のみに表したもの(20180511みたいな)

という形式でデータを格納したい場合、みなさまはどうしますか?

f:id:hit10231023:20180309104332j:plain

自分でしたら

INSERT INTO test_tab1
  (numeric_only_date_time) VALUES (DATE_FORMAT(CURRENT_TIMESTAMP,'%Y%m%d')) ;

で、まずINSERTすると思います。

結果は以下のような感じになります

SELECT * FROM test_tab1 ;
+----+---------------------+------------------------+
| Id | CreatedAt           | Numeric_only_date_time |
+----+---------------------+------------------------+
|  1 | 2018-05-11 12:50:16 | 20180511               |
+----+---------------------+------------------------+

今回シンプルなデータなので、あまりメリットは感じないかもしれませんが、仮に、numeric_only_date_timeのデフォルト(現在日付)にしたい場合で、もっとシンプルにINSERT文を発行したい時はどうしましょう?実はそれが簡単にできてしまうのです!!

続きを読む

MySQL ストアドプロシージャのHANDLERの順番には注意

ストアドプロシージャにて、トランザクションをかける場合、HANDLERの順番には注意しましょう!という話です。

Logicool ロジクール Bluetoothマウス M558

Logicool ロジクール Bluetoothマウス M558

HANDLERだけ書くと、別途MySQLがもっているHANDLER構文も入ってしまいますが、例外処理(DECLARE 〜 HANDLER)の話を今回はします。

f:id:hit10231023:20180309104332j:plain

先に正しい順番を書きますね。

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      SELECT _sqlstate, _errno, _text;
      ROLLBACK;
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      GET DIAGNOSTICS CONDITION 1 _sqlstate = RETURNED_SQLSTATE, _errno = MYSQL_ERRNO, _text = MESSAGE_TEXT;
    END;

続きを読む

MySQL ストアドプロシージャで処理を途中で抜け出す方法って知ってる?

私が知らなかっただけかもしれせんが、処理を途中で抜けることってストアドプロシージャって出来ないとばかり思っていたのですが、できるんですね汗

今まで、ストアドでこんなIF文のネスティングで書いてました。可読性悪いし、バグを誘発するしで何か良い手はないのかな?って思ってました

今までのダメな書き方

CREATE PROCEDURE `sp_test_proc`(
  IN _mode tinyint, -- (0: Normal 1: force delete 2: Batch )
  INOUT _value varchar(32) ,
  OUT _result_code int(11)
)
BEGIN

    BEGIN

       IF _mode = 1 OR _mode = 2 OR _mode = 3 THEN
          IF _value='test' THEN
              SET _value = CONCAT(_value, '-TEST') ;  
          ELSE            
                SET _result_code = 8888 ;
          END IF ;
       ELSE
           SET _result_code = 9999 ;
       END IF ;

〜以降略
    END ;
END $$

あ。。処理何やっているの?とか、コードの書き方が変とかの指摘はなしで。。なんて、IF のネスティングを延々と書いてました。理由は前述の通り、処理を途中で抜ける方法を知らなかったからです。。

よくよく考えたら、おかしいなって調べていたら当たり前のように、そういうやり方がありました。いやあ、はじめて、BEGIN - END の意味を知った感じでお恥ずかしいです。。

f:id:hit10231023:20180309104332j:plain

続きを読む

たまにしか使わないから忘れるMySQLコマンド集メモ

たまーにしか使わないのに、その必要が急に出てきて、調べる時間が勿体無いので、ざっくりここにMySQLDDLおよびSQLをメモしていきます。

f:id:hit10231023:20180309104332j:plain

  • テーブル名変更
  • プライマリーキー
  • プライマリーキーの削除
  • 索引
  • キー削除
  • カラムの追加
  • カラム名変更
  • カラム定義(属性)変更
  • カラム削除
  • Jsonの要素を仮想カラム化する(MySQL 5.7 - )
  • enum型の定義
  • enum型への値追加の(ALTER TABLE)
  • テーブルの名前変更
  • テーブル定義をコピーする
    • 定義は完全にコピーされるがデータはコピーされてない
    • データを含めて、コピーする
  • 定義のデータも完全にコピーしたい
  • INSERT SELECT JOIN
  • INSERT SELECT JOINで差分のみINSERTする(DIFF INSERT)
  • JOIN UPDATE
  • 集計結果を横倒しする
  • ストアドプロシージャ、ストアドファンクションの雛形
  • mysqlのバックアップ(mysqldump)
  • mysql障害時のリカバリ方法(ロールフォワード回復)
  • レコードの更新日を自動的にセットする
  • グループ毎に連番を振る
続きを読む

MySQL SQLでshellファイルを作成してしまう技!

とあるテーブルの内容を元にして、SQLで、shellファイルをつくって実行しちゃおうってのを無理やりつくってみました。

テーブル定義

CREATE TABLE `files` (
  `id` int(11) NOT NULL DEFAULT '0',
  `file_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

primary keyはidで、urlは、例えば、"abcdefg.jpg"ってのが入っていたとします。内容的にはこんな感じです

mysql> SELECT * FROM files LIMIT 3;
+----+-----------+
| id | file_name |
+----+-----------+
|  1 | a.jpg     |
|  2 | b.jpg     |
|  3 | c.jpg     |
+----+-----------+

今回、このfile_nameの中にあるファイル名を利用して、

#!/bin/bash
wget http://test.test.local/download/a.jpg
・
・
exit 0

というようなshellファイルを作成してみたいと思います

f:id:hit10231023:20180309104332j:plain f:id:hit10231023:20180308234003p:plain

続きを読む

Linux 特定の文字を含む行を削除したい場合、sedを使おう!

bashの処理で、ある特定の文字列が含まれる行をまるっと削除したい時ってありますよね?よね? sedコマンドでこんなに簡単にできます。

f:id:hit10231023:20180308234003p:plain

続きを読む

MySQL 意外と簡単!グループ連番を検索結果に付番する方法!

特に連番がないテーブルに意図的に連番を振りたいってこと、日々の運用で結構あったりませんか?以外と簡単にSQLでそれを実現できます!!!

f:id:hit10231023:20180309104332j:plain

サンプルデータについて

例えば以下のようなテーブルがあり、これを題材にやってみたいと思います

mysql> DESC login ;
+---------+------------+------+-----+-------------------+-------+
| Field   | Type       | Null | Key | Default           | Extra |
+---------+------------+------+-----+-------------------+-------+
| userId  | bigint(20) | NO   | PRI | NULL              |       |
| loginAt | datetime   | NO   | PRI | CURRENT_TIMESTAMP |       |
+---------+------------+------+-----+-------------------+-------+
続きを読む

スゴ技! MySQLでレコードの異なる日時の差(引き算)を求めるSQL

やってみたら、かなり私には、高度すぎて死にかけました。

やりたいことは、

MySQLで同列の異なる行と行の引き算をSQLで書くとこうなるってことです。

やってみたいことを文書で表すと、ユーザーのログインは前回のアクセスからどれくらいの感覚でログインしているのかを算出する場合、SQLでそれをどのように表現するかって感じになるのかなというのを試して見ました

f:id:hit10231023:20180309104332j:plain

サンプルデータについて

例えば以下のようなテーブルがあり、これを題材にやってみたいと思います

mysql> desc login ;
+---------+------------+------+-----+-------------------+-------+
| Field   | Type       | Null | Key | Default           | Extra |
+---------+------------+------+-----+-------------------+-------+
| userId  | bigint(20) | NO   | PRI | NULL              |       |
| loginAt | datetime   | NO   | PRI | CURRENT_TIMESTAMP |       |
+---------+------------+------+-----+-------------------+-------+

で、以下のようなデータが入っているものとします。ユーザーIDは、1,2,3とあり、それぞれログインの間隔が異なるものとします。 loginAtはログインした日付がはいっており、useridと、loginAtのセットで、primaryキーだとします。 つまり、同じユーザーidで複数、ログイン日付が入っていると想像してください

続きを読む

なければINSERT、あればUPDATE(ON DUPLICATE KEY UPDATE)

レコードがなければINSERT、あればUPDATEなどどいう超便利なSQL構文があります

ON DUPLICATE KEY UPDATE

オプションがそれなのですが、

結構便利です、今回 MariaDBで試しておりますが、MySQLでもおそらく同様にいけると思います。

f:id:hit10231023:20180309104332j:plain

続きを読む

MySQL 集計結果を横倒しで出すSQL

かなり無理やりだけど、集計の結果を横倒しで表示するサンプルSQLです。 かなり、無理やりですが笑

f:id:hit10231023:20180309104332j:plain

こんなデータを。。。

+------------+------+
| wdate      | cnt  |
+------------+------+
| 2014-01-21 |    1 |
| 2014-01-22 |   20 |
| 2014-01-23 |   10 |
| 2014-01-24 |    4 |
| 2014-01-25 |   15 |
| 2014-01-26 |   18 |
| 2014-01-27 |    5 |
| 2014-01-28 |    2 |

このような結果にしたい

+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| YYYY | MM   | day1 | day2 | day3 | day4 | day5 | day6 | day7 | day8 | day9 | day10 | day11 | day12 | day13 | day14 | day15 | day16 | day17 | day18 | day19 | day20 | day21 | day22 | day23 | day24 | day25 | day26 | day27 | day28 | day29 | day30 | day31 |
+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| 2014 |    2 | 1715 | 2540 | 2710 | 1577 | 1461 |  912 |  680 | 1033 |  631 |   629 |   552 |   364 |   309 |  1309 |  2509 |  1567 |   880 |   622 |   576 |    51 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |
+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+

そんなSQLを書いております

続きを読む

python3 のrequest.args.getでUnicodeEncodeErrorが出た時の対処方法

ここで書いていることって、現役エンジニアの人にしてみればちゃんちゃらおかしい話なんだろうな、なんてことをいつも思いつつも、今日もメモします。

python3でのgetでこんなエラーが出てしまいハマりました。

問題のgetはこちら

word = request.args.get('word')

とにかく、日本語が通らないのでなんだこりゃって感じで苦戦すること2時間。。

UnicodeEncodeError: 'ascii' codec can't encode characters in position 12-13: ordinal not in range(128)

とか

AttributeError: 'str' object has no attribute 'decode'

とかです。日本語がなんかダメなんですよね。。。というところまではわかったのですが、これの対策の苦戦した話のメモになります

f:id:hit10231023:20180302202040p:plain

続きを読む

python3 ヒアドキュメント で一部変数展開をする

python3でのヒアドキュメントで、一部変数が入っていた場合の置き換え方法をメモ あ、ヒアドキュメントを利用して、かつ、変数展開をするっていう、題名通りのメモです汗

ソースはこんな感じになりました。

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

def main():

  _text = '''
text1 : {_text1}
text2 : {_text2}
    '''.format(_text1 = 'テキスト1',_text2 = 'テキスト2').strip()

  print(_text)


if __name__ == '__main__':
    main()

f:id:hit10231023:20180302202040p:plain

続きを読む

知ってた!?MySQL5.6以上から日本語全文検索ができるようになったってこと・・

MySQL5.6~ で日本語全文検索ができるぞ!!!

f:id:hit10231023:20180309104332j:plain

最近MariaDBばかりやっていて、MySQLの存在を忘れていたのですが、今まで英語しか利用できなかったのに、いつの間にか標準で日本語全文検索ができるようになっていたので、久しぶりに触って見ようと思います。これがまた、意外に簡単!だったので、その設定/実装方法をできるだけ簡単に紹介したいと思います

全文検索とは

あれですね。通常テーブルに設定するカラムの索引ではなく、行全体に対して、インデクシングをする設定といえばいいのかしら

ウィキペディアで見るとこんな感じで書かれております

複数の文書(ファイル)から特定の文字列を検索すること。「ファイル名検索」や「単一ファイル内の文字列検索」と異なり、「複数文書にまたがって、文書に含まれる全文を対象とした検索」という意味

データベースでいうところの、複数カラムにまたがったカラムの中の全文を対象にした検索って言い方になると思います。僕的には、「索引を設定できない大きなサイズのカラムも全文検索索引でさくさく検索できる」と言った意味合いで利用しております。(ざっくりすぎですみません・・)

過去のMySQL、いや、Mariaもそうなのですが、昔から全文検索自体の機能はついていたのですが、こと日本語の全文検索になると。Mroonga等、サードパーティーのエンジンを入れる必要がありました。入れるまでは良いのですが、その後の運用保守が結構めんどくさい ・・いや、、Mroongaが使いづらいという話ではなくて、私自身が、複雑な構成をとっていたため、RDBMS本体のバージョンアップ等が大きな壁になっていたんですね。

今回は、日本語全文検索が標準で実装される!!これは大きいと思います。

索引文字列の抽出方法について

MySQLの抽出のデフォルトは、N-Gramになります、N-Gramは、検索対象を単語単位ではなく文字単位で分解し、後続の N-1 文字を含めた状態で出現頻度を求める方法です。インデクシングの速度は速いですが、サイズがでかくなることと検索のノイズ、そして検索速度が遅いというデメリットがあります。しかしながら、単語辞書が必要ないという利点があり、比較的導入しやすいと思います。

実装

前置きはさておき、実装を検討してみます。

続きを読む

MySQL 日時表記を数字のみで表す

現在日時を、YYYY-MM-DD の形式で表示されることがもっとも普通なのですが笑、時と場合によって、日付を8桁の数字のみで表したいとかありますよね。そんな時は、DATE_FORMATでやっちゃえばいいんです。

f:id:hit10231023:20180309104332j:plain f:id:hit10231023:20180309103851j:plain

続きを読む