okinawa

勉強メモ

仮・SQL勉強メモ

書き途中。SQLServer用。

where 1 = 1

where はtrueかfalseかで判定している。

1 = 1なら常にtrue。

使い所はwhere句かand句かの分岐の時。

よく複数単語検索なんかで使う。

検索条件が増える度にAND句で追加したいんだけど、最初はWHERE句じゃないといけないのでめんどくさい。

そこで WHERE句を1=1にして、AND句の追加だけで良いようにする。

select * from
where 1= 1
and~ // ここに追加していく

・参考サイト
「WHERE 1=1」は条件付きSQL文が書きやすくなる魔法の言葉 | キノコログ

count(*)over()

window関数。

ページングの時にとても便利!

selectで総レコード数を取得しつつ、1ページ分のselect結果も欲しいときに使える。

secect count(*)over() , account_name
from account
limit 0 rows
fetch 100 rows

inner join と outer joinの違いはベン図で表すとわかりやすい

・参考記事
SQL Joinサンプル集 Joinで遅いSQLの原因を調べる方法 | ポテパンスタイル

SQLチューニング

実行計画の見方。

  • MySQLSQL文の頭に explain を付けるだけ。
  • SQLServerSQL文の頭に SET STATISTICS PROFILE ON を付ける。

GUIで見る方法もある↓

実際の実行プランの表示 - SQL Server | Microsoft Docs

チューニングについては過去記事も参照↓

GMOのDBチューニング課題やってみた - okinawa

EclipseでSQLServerと接続

まずは公式を参考にする

docs.microsoft.com

公式を参考にJDBCドライバーをダウンロードして WEB-INF < lib ディレクトリに入れる。

そして、クラスパスを通す。

クラスパス

コードも公式を参考に。

docs.microsoft.com

public class TestDao {
    public static void main(String[] args) {
        String connectionUrl =
                "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;"
                        + "database=DB名;"
                        + "username=ユーザー名;"
                        + "password=パスワード;"
                        + "encrypt=true;"
                        + "trustServerCertificate=true;";

        try (Connection connection = DriverManager.getConnection(connectionUrl);) {
            // Code here.
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

SQLServerのユーザー追加

Windows認証はダメだったので下記記事を参考にユーザー追加。

ただ、追加するだけじゃログインできなくてハマった。

DBにすんなり接続できたこと1度もない。

memo.itsysgroup.com

SQLServerサーバー認証を設定 & 再起動

ユーザー権限変更するもまだログインできない。

下記記事を参考にSQLServer自体にSQLServer認証を設定 & 再起動。

qiita.com

がっ!だめ!

ポートが開いてないエラー

ホスト localhost、名前付きインスタンス SQLEXPRESS への接続が失敗しました。 エラー: "java.net.SocketTimeoutException: Receive timed out"。サーバーとインスタンスの名前を調べ、ポート 1434 への UDP トラフィックファイアウォールにブロックされていないことを確認してください。 SQL Server 2005 以降では、SQL Server Browser サービスがホスト上で実行されていることを確認してください。

sqlserver ポート」で調べると下記の記事が出てきた。

sql55.com

TCP/IPを有効&ポートの設定

上記の記事を参考にTCP/IPを有効にしてポートを1433に設定する。

さらにSQLServerを再起動する。

やっと接続できた!!!

エラー対処メモ

Java

環境構築

warファイル等のサンプルがある場合はそれをインポートするのが一番早い。
ほんで中身を変えよう。

設定ファイル自体の文字コードに注意。文字化けしてなくても読み込んでくれないことも。

JavaScript

Cannot read properties of null (reading 'addEventListener')

要素取得したはずが、できてなくてnullになってた時のエラー。

JavaScriptの読み込みの位置が問題だった。
HTMLが読み込まれる前にJavaScriptを書いていたのが原因。

bodyタグの最後にJavaScriptを書いて解決。

GMOのDBチューニング課題やってみた

参考

下記サイトを参考にDBチューニングをやってみましたので、メモ書きを残しておきます。

recruit.gmo.jp

DBチューニングで大事なこと

インデックス化とバッファプールの2つ。

とりあえずこれだけ覚えておこう。

テストデータ作成でエラー

いきなりテストデータの作成でつまずく。

Insert時に下記のエラー。

Error Code: 2013. Lost connection to MySQL server during query

下記サイトを参考にエラー解決。ありがとうございます。

MySQL Workbench 「Error Code: 2013. Lost connection to MySQL server during query」が発生した場合の対処法 | mebee

どうやら、Insertに時間かかりすぎてタイムアウトしていた模様。

解答のクエリ

まずは、課題に対する解答のクエリを2つ考えました。

・クエリ1

select
sum(case when todo.STATUS = 1 then 1 else 0 end) / count(*) * 100 as'TODO',
sum(case when todo.STATUS = 2 then 1 else 0 end) / count(*) * 100 as 'Doing',
sum(case when todo.STATUS = 3 then 1 else 0 end) / count(*) * 100 as 'Done'
from user join todo
on user.USER_ID = todo.USER_ID
where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month);

結果1 19.906秒

Todo Doing Done
33.3137 33.3160 33.3704

・クエリ2

SELECT todo.STATUS,
count(*) / (select count(*) from user join todo on user.USER_ID = todo.USER_ID where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month)) * 100 as '割合'
from user join todo
on user.USER_ID = todo.USER_ID
where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month)
group by todo.STATUS;

結果2 40.453秒

STATUS 割合
1 33.3137
2 33.3160
3 33.3704

インデックスとは?

データベースにおけるインデックスとは、目的のレコードを効率よく取得するための「索引」のこと。

インデックス化するとソートされたテーブルがもう1枚できるようなイメージ。

カラムがソートされているので、検索は早い。

行追加時にはテーブル本体とインデックスの両方に書き込むので、追加は遅くなる。

・参考

インデックスとは?仕組みをわかりやすく解説

インデックス化しよう

インデックスの候補はwhere, order by. group by で指定されるカラムになるそうです。

候補

  • user テーブル: user_id, created_at
  • todo テーブル: user_id, status

user.user_id は PRIMARYのため、元々インデックスが効いており、インデックスを貼り直さなくてOK。

参考サイトと同じSQLです。

alter table user add index idx_created_at(created_at)
alter table todo add index idx_status(status)
alter table todo add index idx_user_id(user_id)

インデックス作成後に再びクエリ1・2を実行。

  • クエリ1 19.906秒→156.860秒
  • クエリ2 40.453秒→187.719秒

んん???

めっちゃ遅くなってますやん!なんで!?

なぜか遅くなったのでMySQLのパフォーマンスを見る

インデックス作ったのに遅くなったので、とりあえずパフォーマンスを見てみます。

Server→Dashboardで見れます。

performance dashboard
performance dashboard

画像取り忘れたのですが、InnoDB Buffer PoolのUsageが90%を超えていました。

なのでPoolのサイズを増やします。※やり方は後述。

バッファプール増加後にクエリ1・2を実行。

  • クエリ1 19.906秒→156.860秒→2.031秒
  • クエリ2 40.453秒→187.719秒→2.984秒

劇的高速化!!!

インデックス化により、バッファプールの使用量が増えたことで遅くなっていたようです。

Buffer Poolとは?

テーブルの本体データとインデックスの保存領域。

一度読み込んだデータをここに保存して、DBとのI/Oを減らし高速化する。

バッファプールの容量があまりにも大きすぎると、逆にバッファー検索に時間がかかってしまうこともある。

下記サイトがわかりやすい。

チューニング ~データベースチューニング~|PostgreSQLインサイド : 富士通

Amazon RDS for MySQL のパラメータ設定 パート 1: パフォーマンス関連のパラメータ | Amazon Web Services ブログ

・バッファプールサイズの確認

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'
Vriable_name Value
innodb_buffer_pool_size 8388608

約8MB。

・バッファプールの必要量確認

SELECT 
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,  
    floor((data_length+index_length)/1024/1024) AS all_mb,  #総容量
    floor((data_length)/1024/1024) AS data_mb,  #データ容量
    floor((index_length)/1024/1024) AS idx_mb   #インデックス容量
FROM
    information_schema.tables  
WHERE
    table_schema=database()
ORDER BY
    (data_length+index_length) DESC;

data size
data size

all_mbの合計値が必要量なので、330MB。

全然足りないので増やそう。

・バッファプールサイズの変更方法

350MBに増量。

SET GLOBAL innodb_buffer_pool_size = 350000000;

ちゃんと増えているか確認↓

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'
Vriable_name Value
innodb_buffer_pool_size 352321536

OK!!!

Table Opened Cacheとは?

同時に開けるテーブル数のこと。

・参考
MySQLのテーブルキャッシュについて - Qiita
MariaDB - table_open_cache の最適化 - table_open_cacheは、パフォーマンスを改善するために調整するのに役立つ変数です。 同じテーブルにアクセスする並行セッションは、それぞれ独立してアク - 日本語

パフォーマンス画面では下記のように表示されていて気になったので調べました。Efficiency(効率)97%なので良さげ。

table open cache
table open cache

・Table Opened Cacheの確認

select @@table_open_cache;

show table open cache
show table open cache

・現在開いているテーブル数の確認

show global status like 'opened_tables';

opened table
opened table

キャッシュ領域2000に対して、開いているテーブル数は179なので問題なし。

複合インデックスとは?

複数列のインデックスのこと。

・参考

qiita.com

複合インデックス化しよう

複合インデックス対象は、user テーブルは created_at + user_id 、 todo テーブルは user_id + status 。順番も変えて全部登録する。

alter table user add index idx_created_at_user_id(created_at, user_id)
alter table user add index idx_user_id_created_at(user_id, created_at)
alter table todo add index idx_status_user_id(status,  user_id)
alter table todo add index idx_user_id_status( user_id, status)

クエリ1・2を実行すると。

  • クエリ1 2.031→0.453
  • クエリ2 2.984→0.797

1秒切った!

Visual Explainの変遷

SQL入力欄の上部の虫眼鏡アイコンで表示される。

赤色がFullTableScanで一番時間がかかり、青色がSingle row: constantで最も早い。

クエリ2

インデックス前 40.453秒

todoテーブルがフルスキャンになっている。Rowsは合計で約8M。

インデックス化&バッファプールサイズの増量後 2.984秒

todoテーブルが茶色のNon-Unique Key Lookupに改善、idx_user_idインデックスが使われている。userテーブルはIndexRangeScanに改悪?

Rows合計が875.55Kと大幅に改善。

複合インデックス化後 0.797秒

秒数以外はあんまり変わってない。todoテーブルで複合インデックスidx_user_id_statusが使われている。

参考サイトによると複合インデックス化後はテキストのExplainを見たほうが良いらしいが、ちょっとよくわからない。

とりあえずExtraの列にUsing Indexと表示されていれば良さそう。

text explain
text explain

クエリ1

インデックス前の画像撮り忘れ。

インデックス化後&バッファプール増量後 2.031秒

複合インデックス化後 0.453秒

Explainの見方

参考サイトに書いてないものの補足。曖昧な部分あり。

・nested loop

テーブル結合の処理をしている箇所っぽい。

userテーブルの1行に対して、マッチする行をtodoテーブルから1行ずつ検索している。

普通のExplainについては下記の参考サイトに書いてあるので割愛。


・VisualExplainについて↓
https://engineering.linecorp.com/ja/blog/mysql-workbench-index-behavior-with-visual-explain/

・普通のExplainについて↓
最低限押さえておきたい EXPLAIN の見かた - Qiita

まとめ

クエリ1

  • インデックス前:19.906秒
  • インックス後:156.860
  • バッファプール増量後:2.031
  • 複合インデックス後:0.453

クエリ2

  • インデックス前:40.453
  • インックス後:187.719
  • バッファプール増量後:2.984
  • 複合インデックス後:0.797

きっちり早くなってよかった。

何をインデックス化してどのように検索すると早くなるのか、はもっと学習が必要そう。

デザインパターン

Factoryパターン

インスタンスの生成をFactoryクラスに集約することで、変更に強くなる。

生産拠点を一つの工場に集中するっちゅうことですね。

Factory methodパターンとはいちおう別物。

・複数クラスが直接クラスBのコンストラクタを呼ぶ場合

    コンストラクタ呼び出し
クラスA →→→→→→→→→→→→ クラスB
クラスC →→→→→→→→→→→→ クラスB
クラスD →→→→→→→→→→→→ クラスB
// これだとクラスBのコンストラクタを変更するとACDも変更必要。

・ファクトリがコンストラクタを呼ぶ場合

     createClassB()     コンストラクタ呼び出し
クラスA →→→→→→→→→→→→ ファクトリ →→→→→→→→→→→→ クラスB
クラスC →→→→→→→→→→→→ ファクトリ →→→→→→→→→→→→ クラスB
クラスD →→→→→→→→→→→→ ファクトリ  →→→→→→→→→→→→ クラスB
// ファクトリクラスのcreateClassB()メソッドだけ変更すればOK

↓こんな感じで直でCクラスのコンストラクタ呼び出すと、もしコンストラクタに変更あったときに、呼び出し側のA・Bも変更が必要になる。

public class A{
     public static void main(String []args){
        C c = newC("山田");
     }
}

public class B{
     public static void main(String []args){
        C c = new C("山田");
     }
}

public class C{
    String name;
    public C(String s){
        this.name = s;
    }
}

↓Factoryクラスを仲介するパターン

public class A{
     public static void main(String []args){
        Factory f = new Factory;
        f.createClassB();
     }
}

public class B{
     public static void main(String []args){
        Factory f = new Factory;
        f.createClassB();
     }
}

public class Factory extends C {
  public void createClassC() {
    C c = new C("山田");
  }
}

public class C{
    String name;
    public C(String s){
        this.name = s;
    }
}

Adapterパターン

継承による方法と委譲による方法がある。今回は継承の方だけ。

Adapterはその名の通り、変換アダプターみたいなもの。

HDMI変換アダプター
HDMI変換アダプター

メリット

既存のクラスを変更せずに済むので、テストが楽になる。

例えばもしバグが発生した場合、既存のクラスが十分にテストされているなら、追加したAdapterとAdapteeのクラスだけをチェックすれば良い。

・DVI→HDMIに変換するコード例↓

既存クラス+Adaptee+Adapter+実行クラスの4つ。

package adapter;
// 既存のクラス
public class Hdmi {

    String maker;
    String originalType;

    Hdmi(String maker, String originalType) {
        this.maker = maker;
        this.originalType = originalType;
    }

    void printMaker() {
        System.out.println(maker);
    }
    
    void printOriginalType() {
        System.out.println(originalType + "からHDMIに変換");
    }
}
package adapter;
// Adapteeクラス
public interface Dvi {
    public void shovMaker();
    public void showOriginalType();

}
package adapter;
// Adapterクラス
public class DviToHdmiAdapter extends Hdmi implements Dvi{
    
    DviToHdmiAdapter(String maker, String originalType) {
        super(maker, originalType);
    }


    @Override
    public void shovMaker() {
        printMaker();
    }


    @Override
    public void showOriginalType() {
        printOriginalType();        
    }
}
package adapter;
// 実行クラス
public class Main {

    public static void main(String[] args) {
        Dvi dvi = new DviToHdmiAdapter("サンワ", "DVI");
        dvi.shovMaker();
        dvi.showOriginalType();
    }
}

運用方法

他にも変換したいケーブルがあれば、AdapterとAdapteeを追加していく。

・追加例

  • USB→HDMI:Adapteeクラス「Usb」Adapterクラス「UsbToHdmiAdapter」
  • VGA:Adapteeクラス「Vga」Adapterクラス「VgaToHdmiAdapter」

参考

Java言語で学ぶデザインパターン入門

デザインパターン ~Adapter~ - Qiita

ネットワークメモ

サブネットマスク

ネットワークアドレスの範囲を指定する。

サブネットマスクの1の所がネットワークアドレス部、0の所がホスト部

00001010.00000001.00000010.00000011 // IPアドレス(2進数)
11111111.11111111.00000000.00000000 //サブネットマスク(2進数)
↓
↓サブネットマスクを1桁増やす
00001010.00000001.00000010.00000011 // IPアドレス(2進数)
11111111.11111111.10000000.00000000 //サブネットマスク(2進数)

1桁増やすと、ホスト部に割り当てられるIPアドレスの数が1/2になる。2進数が1桁減るので。

ホスト部が減るというのは繋げられるPCの台数が減るということで。

サブネットマスクの分割は、一般的に以下のような場合に利用されます。

  • 会社の部署間でデータの参照を制限する場合
  • パソコンやサーバの台数が多く分割して管理する場合
  • 開発環境と本番環境を分けるなど、環境を切り離す場合

引用元:サブネットマスク(subnet mask)とは?

・参考

www.cman.jp

低レイヤ勉強メモ

固定小数点・浮動小数

浮動小数点のメリットはメモリの節約。

0.0000001は8桁必要。

1 * 10^-6 なら「1」と「10」と「-」と「6」の5桁に節約できる。

float型、double型が浮動小数点。

・固定小数点の例

1.5555

314.555

3.141592

浮動小数点の例

0.0000001 * -10

0.000001* 10^-2

1 * 10^-7

100 * 10^-9

floatもdoubleもなぜ誤差が出るの?

・参考

qiita.com

10進数→2進数に変換するときに誤差が出てしまうらしい。

・例 0.3

  1. 10進数「0.3」→2進数「0.010011001100...」という無限小数になる。
  2. floatもdoubleも桁数に上限があるので丸められる。
  3. 誤差が発生。

じゃあなんでBigDecimalは誤差でないん?

小数部を整数として扱っているから。

例えば0.3は

3 × 10^-1 という風に扱う。

3は2進数で「0011」と表せるから誤差は出ない。

nullとは?

nullはメモリに何のデータも入っていないこと。

空文字とは違うのです。

基本だけどたまにわからなくなってしまうので。

// null
String ss = null;

// 空文字
String s = "";

環境変数とは?

OS内のすべてのプロセスが参照できる変数という認識で良さそう。

OSのグローバル変数といったイメージ。

グローバル変数なので、どんなに深い階層からも参照可能。

参照だけでなく変更も可能。

コマンドプロンプト環境変数を試してみよう

f:id:dodosu:20220406210911p:plain
コマンドプロンプト

↑こんな感じでどのディレクトリからでも「cd %環境変数%」で一発で移動できる。

・参考

環境変数って何?初心者向けに簡単に解説 | IT職種コラム

自動型変換の法則

四則演算の時は、基本的にはより精度の高い方に変換される。

12 - 5
# 7
 
12.0 - 5.0
# 7.0

12 - 5.0
# 7.0

ただし、割り算の場合は言語によって結果が異なる

12 / 5
# Java, C++ : 2
# JavaScript, PHP, Python : 2.4
 
12.0 / 2.0
# 6.0

基本型(プリミティブ型)と参照型

※以下はJavaの場合

・基本型

int/char/double/float/byteなど

・参照型

Integer/Stirng/Double/Floatなど

  • 基本型は値が直接入る。
  • 参照型はメモリアドレス(参照値)が入る。

基本型は宣言時に型に応じたメモリ領域が確保される。

参照型はどのくらいメモリ使うのかわからないので、実体はヒープ領域に入る。

スタック領域とヒープ領域と静的領域

  • スタック領域は後入れ先出し。
  • ヒープ領域は必要の都度、割り当てられる。
  • 静的領域はアプリケーション開始時に確保される。

スタック領域には基本型の値、参照型のメモリアドレスが入る。

参照型の実体はヒープ領域に入る。

ちなみにstaticメンバやグローバル変数は静的領域に入る。