MySQLコマンドやSQL文に関する各種メモ書き:タグ「高速化」での検索

検索したリストの件数カウントを高速化する

よく 検索結果などに『◯◯件 ヒットしました』と表示するWEBアプリがあります。

その時に,

件数を取得するSQL文は

SELECT * FROM data_table;
SELECT count(*) AS count FROM data_table;

とすることが多いと思いますが、SELECT時に下記のように「SQL_CALC_FOUND_ROWS」を追加しておいて、その後 FOUND_ROWS() を記述するとかなり高速に取得できます。

SELECT SQL_CALC_FOUND_ROWS * FROM data_table;
SELECT FOUND_ROWS() AS count

がSQL文が複雑な場合は逆に遅くなることもあるようです。

http://ma-bank.com/item/998

No.768
10/07 17:57

edit

高速化

mysqlのスローログの取得し /var/log/mysql_slow.log に保存する

MySQLのスローログ(実行に時間がかかったSQLクエリ)を取得するには

● 1. /etc/my.cnf に以下の文を追加

# 1秒以上かかったクエリを mysql_slow.log に保存する
slow_query_log=ON
long_query_time=1
slow_query_log_file=/var/log/mysql_slow.log

# インデックスを使わない検索を スローログに追加する
# log-queries-not-using-indexes

● 2. 次にログファイルを作成し、権限・グループをmysqlに変更

touch  /var/log/mysql_slow.log
chown  mysql  /var/log/mysql_slow.log
chgrp  mysql  /var/log/mysql_slow.log

● 3. mysqlの再起動

service mysqld stop
service mysqld start

● 4. スローログの確認方法

# mysqladmin コマンドで確認する
mysqladmin -uroot -p status
# スローログを表示する
cat /var/log/mysql_slow.log
No.643
03/28 17:12

edit

高速化

MySQL メモリチューニング

現在のMySQLの設定をみるには MySQLから

show variables;

MySQLの設定は( /etc/my.cnf )を書き換える。

設定例は

my-huge.cnf(1G〜2Gバイトのメモリを持つMySQL専用サーバー向け)

key_buffer = 384M
sort_buffer_size = 2M
read_buffer_size = 2M

my-large.cnf(512Mバイト程度のメモリを持ち,MySQL専用となる機械向け)

key_buffer = 256M
sort_buffer_size = 1M
read_buffer_size = 1M

my-medium.cnf(32M〜64Mバイトのメモリを持つMySQL専用サーバーか,128Mバイトのメモリを持つサーバー向け)

key_buffer = 16M
sort_buffer_size = 512K
read_buffer_size = 0

my-small.cnf(64Mバイト以下のメモリの小規模サーバー向け)

key_buffer = 16K
sort_buffer_size = 64K
read_buffer_size = 0

このあたりを参考に必ず設定する。

またMySQL ABでは,「key_buffer」の値はマシンに搭載しているメモリーの1/4を推奨値としている。

http://vine-linux.ddo.jp/linux/sql/mycnf.php

http://www.819410.com/FreeBSD6/shop/-145.html


No.638
10/06 15:13

edit

高速化

MySQLクエリ結果のキャッシュ

MySQLにはクエリキャッシュの機能があり、これをONにするとクエリ結果をキャッシュから読み出すことが出来て高速な動作が期待できる。

1.まず現在クエリキャッシュが有効かどうかを調べる

show variables like 'query_cache_%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 | 
| query_cache_min_res_unit     | 4096    | 
| query_cache_size             | 0       | 
| query_cache_type             | ON      | 
| query_cache_wlock_invalidate | OFF     | 
+------------------------------+---------+
5 rows in set (0.00 sec)

【query_cache_size】が【0】なのでクエリキャッシュが有効ではないことがわかる

2.クエリキャッシュを有効にするには

「query_cache_type」をONに、「query_cache_size」を0より大きな適切な値に設定する必要がある。

/etc/my.cnf の [mysqld] の項目にクエリキャッシュに関する記述を追加する

[mysqld]
# (query_cache_limit)これより大きい結果はキャッシュしない
# (query_cache_min_res_unit) 4K が推奨値
# (query_cache_size)クエリキャッシュに割り当てるメモリ(Bytes)
# (query_cache_type) 0:OFF 1:ON 2:DEMAND
query_cache_limit=1M
query_cache_min_res_unit=4k
query_cache_size=24M
query_cache_type=1

3.mysqlをリスタート

/etc/init.d/mysqld restart
(または)service mysqld restart

4. キャッシュ状態を確認する。

<pre>SHOW STATUS LIKE 'Qcache%';</pre>

でクエリキャッシュの状態を確認する。

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2        | 空きブロック数
| Qcache_free_memory      | 24213616 | 空きメモリのサイズ (バイト)
| Qcache_hits             | 868      | クエリのヒット数 
| Qcache_inserts          | 900      | キャッシュに入れられたクエリの数
| Qcache_lowmem_prunes    | 0        | メモリが足りないために削除された数
| Qcache_not_cached       | 21       | キャッシュに入れられなかったクエリの数
| Qcache_queries_in_cache | 846      | キャッシュ内のクエリの数 
| Qcache_total_blocks     | 1711     | ブロックの領域の合計
+-------------------------+----------+
8 rows in set (0.00 sec)

【Qcache_lowmem_prunes】を注意深く見ればいいことがわかります。

5. キャッシュ

キャッシュは SELECT文にのみ適用される。

MySQL4.1以前を使用している場合 SQL文 の 'SELECT' の前に スペースが入っているとクエリキャッシュが有効にならないので注意。

(MySQL は先頭3文字のみを見て SEL であった場合のみクエリキャッシュに格納します。)


No.465
03/07 19:14

edit

高速化

MySQLの検索を高速にするインデックスの作成

● テーブルに貼られているインデックスを表示するには

show index from 【テーブル名】;

● インデックスを作成するには

alter table 【テーブル名】 add index 【インデックス名】 (【フィールド名】);

● インデックスを削除するには

DROP INDEX 【インデックス名】 ON 【テーブル名】;

● EXPLAIN 構文を使ってクエリの実行方法を調査する

EXPLAIN 【調査したいselect文】

表示されるデータの見方

【row】:テーブル読まれた行の数 (少ない方が良い。インデックス作成で激減する)
【type】:次の順番で早い順となる
・system, const(結果が単一行)
	・eq_ref(UNIQUE or PRIMARY index使用)
	・ref (index使用の単一パス検索)
	・range ( indexの範囲検索)
	・index(index全体をスキャン)
	・ALL (全DBデータを検索)

● mysqlクエリーを少しでも早くするには

  • 
    
  • 適切なインデックスをはる(どう貼っていいかわからない時は、全てのカラムにインデックスを貼って EXPLAIN する手もあります。)
  • 文字列後方一致検索をやめる
  • テンポラリテーブルを使ったほうが早いか検討する。
No.176
05/12 11:30

edit

高速化