SQLCheck ツールで SQL のアンチパターンを検出する
アンチパターンとは？
アンチパターンとは開発の現場においてよくある失敗を整理し、その解決策をパターン化したものです。つまりこうやってしまうと失敗するという典型例や、セキュリティー問題等を引き起こす悪い作法を纏めたもので、それらの一般的な解決策を提示してくれるものです。
SQL のアンチパターンをチェックするには よくニュースでもシステムがハッキングされた事件や情報漏洩等、様々な事件が発生していますが、それらの中には SQL が正しく書かれていないために問題となったケースもありますので昨今の開発ではセキュリティーチェックは必須となっています。
またそれ以外にもデータベースのパフォーマンスがとても悪い SQL や読みにくい SQL の書き方等、様々な SQL の問題を事前にチェック出来れば問題を回避することが可能です。
そこで SQLCheck というツールが役に立ちます。このツールはまさに SQL のアンチパターン(一般的にやってはいけない)を基に SQL の問題点をチェックしてアドバイスしてくれるものです。
では早速ツールを導入していきましょう。
SQLCheck をインストール
まず SQLCheck ツールを導入する方法を各 OS 毎に説明します。[Windows] 1) 下記 URL をブラウザに貼り付けてダウンロードします。 https://github.com/jarulraj/sqlcheck/releases/download/v1.2/sqlcheck.exe 2) ダウンロードした sqlcheck.exe を任意の場所に移動します。 3) コマンドプロンプトを開き、sqlcheck.exe を実行します。
[Ubuntu x86_64] $ wget https://github.com/jarulraj/sqlcheck/releases/download/v1.2/sqlcheck-x86_64.deb $ sudo dpkg -i sqlcheck-x86_64.deb
[CentOS x86_64] $ wget https://github.com/jarulraj/sqlcheck/releases/download/v1.2/sqlcheck-x86_64.rpm $ yum --nogpgcheck localinstall sqlcheck-x86_64.rpm
[Mac HomeBrew] brew tap gwerbin/tap brew cask install gwerbin/tap/sqlcheck
テスト用の SQL を用意
SQLCheck ツールを試すために、以下のような SQL を test.sql として保存しました。なお SQLCheck の検証は Ubuntu で行います。$ cat test.sql SELECT * FROM colors WHERE name LIKE '%re%' OR name LIKE '%w%';
この SQL を実行すると次のような結果が返ってきます。LIKE 条件を２つ使ったとてもシンプルな SQL です。
=> SELECT * FROM colors WHERE name LIKE '%re%' OR name LIKE '%w%'; +----+--------+ | id | name | +----+--------+ | 1 | red | | 3 | yellow | | 5 | white | +----+--------+ (3 rows)
SQLCheck ツールで SQL をチェックする
それでは早速 SQLCheck ツールを実行してみます。ここでは "-c" オプションを付けて結果に色付けを行うようにしています。また "-f" オプションに続けてチェックしたい SQL が記載されたファイル名を指定します。SQLCheck の実行結果は以下の通りです。
$ sqlcheck -c -f test.sql +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ALL ANTI-PATTERNS > SQL FILE NAME :: test.sql > COLOR MODE :: ENABLED > VERBOSE MODE :: DISABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement: select * from colors where name like '%re%' or name like '%w%'; [test.sql]: (HIGH RISK) SELECT * [Matching Expression: select *] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 1 > Medium Risk :: 0 > Low Risk :: 0 > Hints :: 0
上記結果の中で "All Anti-Patterns and Hints :: 1" という部分があり、この SQL にはハイリスクな問題が１つ発見されたと言っています。
その詳細は "Results" に記載されており、"(HIGH RISK) SELECT " ということで "SELECT *" は良くないということが分かります。
実際の開発現場においても "SELECT *" を使ってしまうと、テーブルに列を追加した場合にアプリケーション側で取得している列の順番が変わってしまうため問題となってしまいますので避けるべきです。
では指摘された SQL を修正して再度 SQLCheck ツールを実行してみます。
まず SQL を "SELECT *" を使わずに以下のように書き換えます。
[修正前] $ cat test.sql SELECT * FROM colors WHERE name LIKE '%re%' OR name LIKE '%w%'; [修正後] $ cat test.sql SELECT id, name FROM colors WHERE name LIKE '%re%' OR name LIKE '%w%';
上記 SQL の修正を行い、再度 SQLCheck ツールを動かしてみます。
$ sqlcheck -c -f test.sql +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ALL ANTI-PATTERNS > SQL FILE NAME :: test.sql > COLOR MODE :: ENABLED > VERBOSE MODE :: DISABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== No issues found.
今度は "No issues found." ということで、問題は検出されなかったということでエラーが無くなりました。
SQLCheck ツールで更なる詳細情報を得る
しかし先の "SELECT *" に問題があることは分かっても、何故それが問題なのかは分からない場合があります。そこで今度は SQLCheck ツールの冗長出力モードを使ってチェックしてみましょう。冗長出力モードを有効にするためには "-v" オプションを付けて SQLCheck ツールを実行します。
以下に実際に先の SQL を冗長出力モードで実行した結果を記載します。今度はかなり多くの情報が出力されました。
もし必要であれば Google 翻訳等を使って翻訳してみても良いかと思いますが、ここでは英文の説明を解説していきます。
$ sqlcheck -c -v -f test.sql +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ALL ANTI-PATTERNS > SQL FILE NAME :: test.sql > COLOR MODE :: ENABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement: select * from colors where name like '%re%' or name like '%w%'; [test.sql]: (HIGH RISK) SELECT * ● Inefficiency in moving data to the consumer: When you SELECT *, you're often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well as taking more time to travel across the network. This is especially true when someone adds new columns to underlying tables that didn't exist and weren't needed when the original consumers coded their data access. ● Indexing issues: Consider a scenario where you want to tune a query to a high level of performance. If you were to use *, and it returned more columns than you actually needed, the server would often have to perform more expensive methods to retrieve your data than it otherwise might. For example, you wouldn't be able to create an index which simply covered the columns in your SELECT list, and even if you did (including all columns [shudder]), the next guy who came around and added a column to the underlying table would cause the optimizer to ignore your optimized covering index, and you'd likely find that the performance of your query would drop substantially for no readily apparent reason. ● Binding Problems: When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names. [Matching Expression: select *] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 1 > Medium Risk :: 0 > Low Risk :: 0 > Hints :: 0
上記結果の中で、最初に指摘されている "(HIGH RISK) SELECT *" の "● Inefficiency in moving data to the consumer:" について見てみます。
[test.sql]: (HIGH RISK) SELECT *
● SQL 実行結果を利用者に移動させる際の非効率性:
"SELECT *" を実行するとき、あなたはしばしばアプリケーションが本当に必要としている列数以上のデータを返します。
これはデータベースサーバーからクライアントへの必要以上の結果データの移動を引き起こし、またあなたのマシンを遅くして負荷を高めるとともに、ネットワーク経由で更に時間が掛かってしまいます。
これは特に誰かがテーブルにこれまで存在しなかった新しい列を追加し、またその列がアプリケーション側で必要ではない場合に問題となり得ます。
つまり "SELECT *" を実行することによって、必要以上のデータが取得されてしまいデータベース・サーバー及びクライアント側、さらには余計なデータによりネットワーク経由でのデータのやり取りが遅延するという問題点を指摘してくれています。とても詳しくアドバイスしてくれています。
２つ目のアドバイスである "● Indexing issues:" は、"SELECT *" を実行することによって、高レベルなパフォーマンスを要求される場合において不必要な列データを取得することは問題となり、またデータベースサーバーでの最適な処理を実行する際に問題となる、ということをアドバイスしてくれています。
そして３つ目のアドバイスとしては、"SELECT *" を実行することによって JOIN を使うことによって異なる複数のテーブルから同じ列名のデータを取得することが可能で、その場合に結果を受け取った側で問題となる、ということをアドバイスしています。
これらのアドバイスは非常に的確かつ大変有用なものです。SQL を作成したら SQLCheck ツールを実行し、指摘された問題点やアドバイスを見ることで SQL の技術力が向上していきます。
なお SQLCheck ツールの詳細は下記本家 URL で確認することが出来ます。
https://github.com/jarulraj/sqlcheck
