Information Technology Tips

Windows側のMySQL Workbenchから、WSL2上のUbuntuにインストールしたMySQLへ接続する方法をまとめました。 Ubuntu側 (WSL2) MySQLのインストール 普通にUbuntuへMySQLをインストールする手順に従う。 https://takake-blog.com/wsl2-ubuntu2004-mysql/ を参考に進めた。 MySQLの起動コマンドは sudo service mysql start MySQLの設定修正 ■ MySQLのユーザを作成 外部のホストからログインできるようにするために、ホスト部分は%指定 。ホストを固定したい場合はホスト名やIPアドレスを指定。 CREATE USER 'user' @ '%' IDENTIFIED BY 'password' ; FLUSH PRIVILEGES ; CREATE SCHEMA some_schema CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' ; GRANT ALL PRIVILEGES ON ` some_schema ` .* TO 'user' @ '%' ; ■ Listenするアドレスを0.0.0.0に修正 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf #bind-address = 127.0.0.1 bind-address = 0.0.0.0 sudo service mysql restart を実行してMySQL再起動 ■ ifconfigコマンドを実行して接続先のIPアドレスを調べる user@host:~$ ifconfig Command 'ifconfig' not found, but can be installed with: sudo apt install net-tools 自分の環境では、上記のメッセージが出て、ifconfigがインストールされていなかったので、 sudo apt install ne...

JetBrainsのIDE内のツールを使ってデータベース(MySQL)に接続しようとしたところ、下記のエラーが出て接続に失敗しました。 Failed Cancel Copy Search Error Troubleshooting [08S01] Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate). 少し調べたところ、JetBrainsのIDE内のJavaを使っている部分のエラーであることがわかりました。根本の原因は、 こちらの記事 に書かれていました。 「接続先ホストからJavaが対応していないTLS(いわゆるSSL)のプロトコルバージョンを要求されたときに出る」ものとのことです。 サーバー側のTLSの設定を変えられない場合、Javaのクライアント側で対応していないTLSのプロトコルバージョンを許可することでこのエラーは回避できます（セキュリティレベルの低いプロトコルを許可することになるので、そのリスクを理解したうえで作業を実施してください）。 C:\Program Files\JetBrains\{JetBrainsのIDE}\jbr\conf\security\java.security 内の下記の記述の部分からTLSv1, TLSv1.1を削除します。 # Note: This property is currently used by the JDK Reference implementation. # It is not guaranteed to be examined and used by other implementations. # # Example: # jdk.tls.disabledAlgorithms=MD5, SSLv3...

PHPでMySQLのinsertとupdateで使えるPDOのprepared statementを生成するプログラムを書いてみました。使用頻度の高そうなパターンのSQLを生成することを目標にしてプログラムを書きました。 SQLBuilderクラス メインとなるクラスです。SQLBuilderクラスの内部で使っているColumnインタフェースとColumnインタフェースを実装したBindableColumn、FixedValueクラスは、SQLBuilderクラスの内部のみで使用することを想定しているので、SQLBuilder使用時に意識する必要はありません。 <?php class SQLBuilder { private string $table; /** * @var Column[] */ private array $columns = []; public function __construct($table) { $this->table = $table; } public function add($column, $type): self { $this->columns[] = new BindableColumn($column, $type); return $this; } public function addFixedValue($column, $value): self { $this->columns[] = new FixedValue($column, $value); return $this; } public function buildStatementForInsert() { $targetColumns = implode(', ', array_map(fn(Column $column) => $column->getName(),$this->columns)); ...

MySQLのON DUPLICATE KEY UPDATEで複数行に対して、キーが存在する行は挿入、キーが重複する行は新しいデータで更新するクエリは、下記のように、VALUESキーワードを使うことでシンプルに書くことができます。 INSERT INTO table(id, score) VALUES (1, 87), (2, 75) ON DUPLICATE KEY UPDATE score = VALUES(score);

簡易的な方法として「指定した文字列からランダムに1文字選ぶ」を必要な文字の長さ分concat関数でつなげれば実現できます。 1文字ずつ文字を選ぶので、あまり性能もよくない上、セキュリティ的な観点からのランダム性も担保されていないので、あくまで開発中に必要になった時に使う程度が無難だと思います。 下記に英数字大文字小文字を含んだランダムな3文字の文字列を生成するクエリを示します。 # RAND関数で指定した文字列からランダムに1文字選択。 # 下記の例の62の部分はa~z、A~Z、1~9の文字数の合計値を入れた結果 SELECT CONCAT( SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', FLOOR(RAND() * 62 + 1), 1), SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', FLOOR(RAND() * 62 + 1), 1), SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', FLOOR(RAND() * 62 + 1), 1) ) AS random_string;

MySQLでテーブルごとのデータサイズは、information_schemaから確認できます。 下記は、テーブルごとのデータサイズとインデックスサイズの合計を取得するためのクエリ例です。 SELECT table_schema, table_name, ROUND((data_length + index_length) / 1024 / 1024), 2) AS `MB` FROM information_schema.Tables ORDER BY (data_length + index_length) DESC;

MySQLでGROUP_CONCATしたフィールドに対して疑似的にLIMITを実現するには、GROUP_CONCATで生成された文字列に対して、SUBSTRING_INDEXを使って文字列を切り出す方法が簡単です。 # 下記はid, codeをカラムに持つテーブルで、codeカラムでGROUP BYして、codeごとにidをlimitで取得する例です。 SELECT code ,SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', :limit) # :limitの部分に取得したい件数を指定。 FROM table GROUP BY code 長所は、下記のように条件を指定して、LIMIT句で取得件数を指定したクエリを何度も発行する必要がないところです。 特に、一回あたりクエリの発行コストが高い場合は、GROUP_CONCATを使って一度に取得したほうが最終的な実行時間をかなり節約することができます。 SELECT id FROM table WHERE code = 'A' ORDER BY id DESC LIMIT :limit; ただし、短所も多いので、使用する際は、これらの短所について十分に考慮したうえで使ってください。 GROUP_CONCATで生成された文字列に対して、SUBSTRING_INDEXを使って文字列を切り出すという文字列処理なので、無駄が多い。 特にGROUP_CONCATで生成された元の文字列が長い場合。 GROUP_CONCATの区切り文字が、GROUP_CONCATされる元の文字列に含まれていると正しくLIMITされない。 例:GROUP_CONCATされる元の文字列にカンマが含まれているのに、カンマを区切り文字で指定している場合。 GROUP_CONCATの最大文字数制限を超えた場合は、機能しない。 MySQLのGROUP_CONCATの最大文字数制限は、「SHOW VARIABLES LIKE '%group_concat%';」で調べられます。デフォルト値は1024のようです。 ...

サブクエリを用いたクエリの最適化 巨大なテーブルをFull Table Scanして件数を集計する場合に、JOINの少ないサブクエリで先に集計して後から他のテーブルをJOINすると、クエリの実行を高速化できる場合があります。 特にJOINするテーブルが多い時には実行時間に大きな差が出ます。 もちろん、データやINDEXの張り方にも依存するで、クエリを書き換える前にEXPLAINで必ずRows_examinedなどが減ることを確認することは必須です。 この記事では、huge_rows_tableに大量の行があり、他のテーブルの行数はそれほどない場合を想定したクエリ例を示します。 想定するテーブル CREATE TABLE `huge_rows_table` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `group_id_1` bigint(20) NOT NULL, `group_id_2` bigint(20) NOT NULL, `check_flg` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `IDX_group_id_1` (`group_id_1`), KEY `IDX_group_id_2` (`group_id_2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE `group_table_1` ( `id` bigint(20) NOT NULL, `name` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE `group_table_2` ( `id` bigint(20) NOT NULL, `name` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; クエリ # 元クエリ SELECT gt1.name AS g...

別サーバーにまたがる複数のDBへのTransactrionを管理するには、2 Phase Commitが用いられることがあります。 今回のコードは、できるだけ流れがわかるように可能な限り単純にしてあります。エラー処理などは厳密に実施していませんので注意してください。 その代わり1コマンドごとにログを書くようにして、エラーが起きたときにどこで落ちたか、可能な限り終えるようにしてあります。 このコードをProductionで使う場合(あまり推奨しません。。。)は、十分に検証してから使ってください。 XA Transactionのコマンドの流れ server1とserver2の2つのDBサーバーに対してXA transactionコマンドを実行する流れは、下記のようになります。 // XA trsanction start server1: XA start 'test' server2: XA start 'test' //-------------------------------- // server1とserver2のDBのデータ更新 //-------------------------------- // XA trsanction commit server1: XA END 'test' server2: XA END 'test' server1: XA PREPARE 'test' server2: XA PREPARE 'test' server1: XA COMMIT 'test' server2: XA COMMIT 'test' // 更新中にエラーが起きた場合のrollback server1: XA END 'test' server2: XA END 'test' server1: XA PREPARE 'test' server2: XA PREPARE 'test' server1: XA ROLLBACK 'test' server2: XA ROLLBACK 'test...

mysqldumpなどで生成されたzip圧縮された巨大なsqlを解凍しながら、mysqlへ実行(リストア)する方法です。 パイプ(|)とリダイレクト(<)を駆使します。 gunzip < test.sql.gz | mysql -u USER -pPASSWORD -D DBNAME ちなみにmysqldumpの出力をzip圧縮する方法は下記になります。 mysqldump -u USER -pPASSWORD DBNAME | gzip > dump.sql.gz

MySQLのユーザーの権限は「show grants for user@host」のようにコマンドを実行すれば取得できます。 すべてのユーザーを取得する場合は、mysql.userテーブルからユーザーとホストを取得し、concat関数を使って、全ユーザー分のshow grantsコマンドをsqlとして取得すると楽です。 下記に例を示します。 # 全ユーザー分のshow grants実行コマンドをsqlファイルに出力 mysql -N -e 'SELECT CONCAT("SHOW GRANTS FOR `", user, "`@`", host, "`;") FROM mysql.user order by user, host' > /tmp/show_grants_for_all_users.sql; # 生成したsqlファイルをmysqlで実行させて、あとはlinuxのコマンドを駆使して加工 # 下記は、取得したGRANT文をREVOKEに変換し、権限を削除するためのsqlを生成する例になります。 cat /tmp/show_grants_for_all_users.sql | mysql -N |egrep 'what you would like to extract' | grep -v USAGE | sed -e "s/GRANT/REVOKE/g" -e "s/$/;/g" -e "s/TO/FROM/g" > /tmp/mysql_revoke_example.sql

下記のようなコマンドで実行できます。{}で囲った部分は実際の運用に応じて値をあてこんでください。 mysql -u{user} -p{password} -e "SELECT CONCAT('kill ', pl.id, ';') FROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS trx ON trx.trx_mysql_thread_id = pl.id WHERE pl.time > {time_to_kill};" |sed -e '1,1d' | mysql -u{user} -p{pass} 上記のコマンドの簡単な解説です。 information_schemaのprocesslistテーブルとトランザクションを管理しているinnodb_trxテーブルをjoinして、{time_to_kill}秒以上実行されているプロセスを特定し、特定したプロセスをkillするsqlコマンドを生成 mysql -u{user} -p{password} -e "SELECT CONCAT('kill ', pl.id, ';') FROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS trx ON trx.trx_mysql_thread_id = pl.id WHERE pl.time > {time_to_kill};" 生成された行の1行目はヘッダーなので削除(sedコマンドを使って、1行目から1行目までを削除)。 sed -e '1,1d' 最後の部分で、生成されたsqlをmysqlに流し込んで実行させる。

MySQLのSQL_CALC_FOUND_ROWSを使用する際の無駄なメモリ消費に注意 ページング機能の実装などのために、ヒットした全件数を取得する必要のある場合があるかと思います。 その場合、下記のようにSQL_CALC_FOUND_ROWSを使うと、検索結果に加えて、そのクエリの全ヒット件数が取得できます。 SELECT SQL_CALC_FOUND_ROWS table.column1, table.column2 ...(途中省略)... FROM table WHERE (条件) しかし、SQL_CALC_FOUND_ROWSを使うと、「絞り込んだ結果にヒットする全べての行の結果のセットを作成する」という大きな欠点があります。 これは、LIMIT, OFFSETを指定していても実行されてしまうので、 SELECTで指定するカラム数やデータ量が多い SELECTの結果返ってくる行数が多い 場合、無駄に領域(≒メモリ)を消費してしまうので注意が必要です。 例えば、100万行検索対象としてヒットするクエリの場合、仮にLIMIT 20と指定して最初の20行を取得するようにクエリを書いても、その100万行分の結果セットが作成されてしまうということになります。 対応策 根本的な対応策としては、SQL_CALC_FOUND_ROWSを使わない(結果行数の取得はCOUNTを用いた別クエリで取得する、結果行数をあらかじめサマリーテーブルに保持しておく)ことですが、 SQL_CALC_FOUND_ROWSをどうしても使う必要がある場合は、 SELECT SQL_CALC_FOUND_ROWS primary_id のように最低限のカラムを指定して結果行セットを取得 (LIMIT OFFSET指定前提) 取得したprimary_idを使って必要なデータを取得 して、SQL_CALC_FOUND_ROWSで使用する領域をできるだけ減らすことで、対応するしかないと思います。 世の中ではLIMIT, OFFSETは使わない方がよいとよく書かれていますが、 SQL_CALC_FOUND_ROWSは、書いてしまえばどんなときも検索にヒットする全結果行セットを作成するので、同じくらい使用する際には注意が必要です。

SELECTの結果をUNIONして ORDER BY する際には下記の点に注意する必要があります。 無駄なメモリ消費 ソートにINDEXが利かない (≒CPU負荷増大) 対応策 可能であればPush-down Condition (各サブクエリ内でORDER BY, LIMIT, OFFSETを適用してからUNION, ORDER BYを実行する)を利用することで、 パフォーマンスを改善できる場合があります。 下記に例を示します。 もともとのクエリ SELECT tmp.* FROM ( SELECT tableA.column1, tableA.column2 FROM tableA WHERE (条件) UNION ALL SELECT tableB.column1, tableB.column2 FROM tableB WHERE (条件) ) AS tmp ORDER BY tmp.column1, tmp.column2 LIMIT 100, 20 Push-down Conditionを用いて書き直したクエリ SELECT tmp.* FROM ( SELECT tableA.column1, tableA.column2 FROM tableA WHERE (条件) ORDER BY tableA.column1, tableA.column2 LIMIT 30 # ただしこのPush-down Conditionの手法も下記の場合は、効果が半減しますので注意が必要です。 OFFSETの値が大きい場合は、結局全結果セットUNIONと変わらない サブクエリ内のソートで、INDEXが効かない場合

今回は、筆者が気をつけている、MySQLのクエリチューニングの基本的な考え方について、紹介しようと思います。 基本的な指針としては、下記を守ることが重要です。 MySQLがクエリの実行で使用する領域(≒メモリ使用量)を減らす。 クエリ対象の行を減らす (INDEX, サマリーテーブルを駆使)。 上記の基本的な指針に基づいて、クエリを組み立てる際は以下の点を考慮するとよいと思います。 SELECTで指定する行は少なくする。 JOINするテーブルの数は減らす。 TEXTや大きいサイズのVARACHARでのORDER BYは極力避ける。SUBSTRなどを使ってカラムの一部データのみを使うことも考慮。 EXPLAINを実行した際に Extra: Using where; Using temporary; Using filesort と表示される場合は要チェック。 確認部分: 巨大なTemporaryテーブルが作成されないように注意。 対策: 「ソート部分でINDEXを使えないか」「サマリーテーブルを使って余計なJOINを減らしソート対象の行数を減らせないか」を検討。 INDEXを効かせる。 テーブル設計段階でどのカラムにINDEXを張るかをしっかり検討 (当たり前ですが。) 特にソートの必要がある場合は、できるだけINDEXやPRIMARY KEYを利用できるようにクエリやテーブルを設計。 基本MySQLのクエリオプティマイザに従うべきですが、あえて特定のINDEXを効かせる/効かせない場合は FORCE INDEX STRAIGHT_JOIN の利用も検討。ただし、クエリの実行プランはデータによっても変わるので、基本、FORCE INDEXやSTRAIGHT_JOINは使うべきではないと筆者は考えています。 想定される状況が限定的 解決しようとしているクエリの問題が明確 性能改善度合いが大きい ときのみ、FORCE INDEXやSTRAIGHT_JOINは使用すべきかと思います。また強制したクエリの実行プランが最悪の実行プランになった場合も想定することをお勧めします。 複雑なネストクエリは避ける。 確認部分: Temporaryテーブル(INDEXが効かない！)が作成される可能性が高い上、メモリも大量に...

SQL_CALC_FOUND_ROWS Performance Issue If you are working on MySQL (<=5.5) for a long time, you have an experience with performance issue on SQL_CALC_FOUND_ROWS with LIMIT OFFSET. The core cause is MySQL generates full result set as a temporary table even if user only requested first 20 rows in the query. This kind of situation often happens when you implement paging functionality. Please see following example. SELECT SQL_CALC_FOUND_ROWS tbl_a.a_id, tbl_b.*, tbl_c.*, FROM tbale_a AS tbl_a INNER JOIN table_b AS tbl_b ON tbl_b.a_id = tbl_a.a_id INNER JOIN table_c AS tbl_c ON tbl_c.b_id = tbl_b.b_id LEFT JOIN table_d AS tbl_d ON tbl_d.a_id = tbl_a.a_id WHERE tbl_b.field1 = "ABC" LIMIT 100, 20; In this example, a temporary table which has full result set is generated. If you configure SQL_CACHE, the second time query might be quite faster. However it is not a good choice because the query performance depends on status of tables, and not stable. Assume if tbl_b is fr...

In this post, I will explain some performance tuning tips for MySQL query. 1) Don't use wildcard (e.g. *) in SELECT (=Pick only required column on SELECT). 2) Comare performance between with and without 'JOIN'. Sometimes JOIN helps MySQL engine will reduce examined number of rows. But in my exeprience, most of the case, removing LEFT JOIN makes performance improvement. 3) Comare performance between with and without 'ORDER BY'. 4) Comare performance between with and without 'SQL_CALC_FOUND_ROWS'. 5) Check INDEX is properly used for JOIN, GROUP BY, ORDER BY. 6) Compare performance between following 2 strategy. a) pick up all required columns (sometimes with ORDER BY, DISTINCT, LIMIT etc.) by single query b) pick only required id column, and then pull all required columns using JOIN or execute 2nd query based on id columns picked up from the 1st query. Okay the last 6) might need example. 6) says " comapre performance following a)...

In this post, I will show you SQL query for calculateing dense rank for MySQL . You know Oracle and SQL Server have the function called DENSE_RANK, which is for returning rank of the value. Unfortunately MySQL doesn't have the built-in function. We can calculate dense rank by nested select for same table with count function. However this strategy is toooo slow because calculating rank by counting number of rows above the target row for every rows. Anyway here is the query for calculating dense rank for MySQL. SELECT id, @rnk:=IF(@preval score, @rnk, @row + 1) AS dns_rnk, @row:= @row+1 AS rnk, @preval:=score as score FROM table # be careful for NULL handling. # if all the values of score column are null, then dns_rank will zero. # please set proper initial value for @preval based on your data. JOIN (SELECT @rnk := 0, @preval :=null, @row := 0) r ORDER BY score DESC The result should be something like this. id dns_rnk rnk score 1 1 1 100 4 2 2 81 ...

DB SQLite MySQL MongoDB couchDB HadoopDB Apache Cassandra Google Cloud SQL Information and Explanation DBonline DB Tools Toad MySQL Workbench : Ultra useful tools... phpMyAdmin