Cat Paw Software

ユーザ用ツール

サイト用ツール

トレース:
mysql

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
次のリビジョン		前のリビジョン
mysql [2015/09/12 05:28] – [インデックスマージ] nullponmysql [2024/01/18 01:41] (現在) – [DDLクエリが終わらない] nullpon
行 3: 行 3:
   * [[http://dev.mysql.com/doc/refman/5.1/ja/index.html|MySQL 5.1リファレンスマニュアル]]   * [[http://dev.mysql.com/doc/refman/5.1/ja/index.html|MySQL 5.1リファレンスマニュアル]]
  
-ネットの情報は間違いも多い。鵜呑みにせず公式のマニュアルで確認すること。公式サイト、オライリーの書籍（実践ハイパフォーマンス等）はほぼ無条件で信用してもいいだろう。もちろんこのページの情報も鵜呑みにしないこと。きちんとマニュアルで調べる。すべての情報はマニュアルに掲載されている。+ネットの情報は間違いも多い。鵜呑みにせず公式のマニュアルで確認すること。もちろんこのページの情報も鵜呑みにしないこと。きちんとマニュアルで調べる。すべての情報はマニュアルに掲載されている。公式サイト以外でもオライリーの書籍（実践ハイパフォーマンス等）はほぼ無条件で信用してもいいだろう。 
 + 
 +===== DDLクエリが終わらない ===== 
 + 
 +alter tableがなかなか終わらない場合、プロセスリストを見てみよう 
 + 
 +<code> 
 +show processlist 
 + 
 +or  
 + 
 +show full processlist 
 +</code> 
 + 
 +''Waiting for table metadata lock''と出ている場合は、他のプロセスが該当のテーブルにトランザクションを開いているためDDLがロックされている状態。 
 + 
 +ありがちなのはGUIのデータベースクライアントツールがトランザクションを張っているケース（例えばDBeaverはテーブルを開くだけでトランザクションを作成する）。アプリを閉じましょう 
 + 
 +===== readonlyが効かない ===== 
 + 
 +<code> 
 +set global read_only = 1;  
 +</code> 
 + 
 +を実行しても書き込みできてしまう。 
 + 
 +super権限を持っているユーザにはread_onlyは効かない。セキュリティ上の観点からもアプリからはsuper権限を持たないユーザで接続するのをオススメする。 
 +===== rootログインできない！ ===== 
 + 
 +Ubuntu 18.08 + MySQL 5.7で発生。 
 + 
 +認証方法がauth_socketとなっており、OSのrootユーザのみMySQLのrootユーザでログイン可能な設定にされている。なので sudo すればOK 
 + 
 +    sudo mysql -u root 
 + 
 +認証方法を以前と同じ（mysql_native_password）に変更するには 
 + 
 +<code> 
 +$ sudo mysql -u root  
 +mysql> use mysql; 
 +mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'パスワード;     
 +</code> 
 + 
 +なお、認証方法がauth_socketの状態では ''sudo mysql_secure_installation'' を実行してrootパスワード設定しても反映されなかったので、上記手順で認証方法を変更する必要があった。 
 + 
 +==== パスワード要件 ==== 
 + 
 +5.7以降、デフォルトのパスワード要件がmedium policyとなっており、 
 + 
 +  * パスワードが最低 1 つの数値文字を含み、1 つの小文字および大文字を含み、1 つの特殊文字 (英数字以外) を含む 
 +  * 長さ8文字以上 
 + 
 +でないと設定できない。 
  
 ===== データ無いのに非常に遅い ===== ===== データ無いのに非常に遅い =====
行 37: 行 90:
 my.cnfの my.cnfの
   bind-address = 127.0.0.1   bind-address = 127.0.0.1
-をコメントアウトするか、アドレスを自分のinetアドレスに書き換える。+をホストのIPアドレスに置き換える
  
-bind-addressは、MySQLサーバを公開するIPアドレスである。MySQLが起動しているサーバマシンに複数のIPアドレスが割り当てられている場合、クライアントはbind-addressで指定したサーバのIPアドレスに対して接続した場合のみMySQLにへ接続できるようになる。例えば、サーバマシンに有線LANで192.168.1.4、無線LANで172.29.10.5 というIPアドレスが割り当てられていた場合、+bind addressはMySQL Serverがlistenしているネットワークソケットをバインドするアドレスを指定する。127.0.0.1は一般にループバックデバイスにマップされるため、ホスト内からのアクセスのみ受け付けるようになる。
  
-  bind-address = 192.168.1.4+ホストが複数のネットワークインターフェースを持っており、それぞれが社内と社外に接続している場合がある。社内LANに172.29.10.5で公開され、社外には192.168.1.4で公開していたとすると、 
 +  bind-address = 172.29.10.5  
 +と指定することで社内からのアクセスのみ受け付けるようになる。
  
-とすると無線LAN経由でMySQLに接続することはできなくなる。 
  
-127.0.0.1を指定した場合、127.0.0.1というIPアドレスに対して接続できるマシンは自分自身のみなので、ローカルのみの接続が許可される。+全てのIPv4インターフェースからのアクセスを許可 
 +  bind-address = 0.0.0.0
  
 +全てのIPv4/v6インターフェースからのアクセスを許可
 +  bind-address = ::
 +
 +IPv6が有効なら全てのv6、そうでなければ全てのv4インターフェースからのアクセスを許可（MySQL 5.6.6〜）
 +  bind-address = *
 +
 +v6v4両方でアクセスするなら両方指定する
 +  bind-address = ::
 +  bind-address = 0.0.0.0
 +  
 +===== mysql_upgrade =====
 +
 +例えば5.6→5.7へアップグレードするとshow variablesでエラー。
 +
 +<code>
 +mysql> show variables;
 +ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist
 +</code>
 +
 +他にも5.7→8.0へアップグレードするとshow databasesでエラーなど
 +
 +<code>
 +mysql> show databases;
 +ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
 +</code>
 +
 +これはバージョン間の互換性がなくなっているため
 +
 +<code>
 +$ mysql_upgrade -u root
 +</code>
 +
 +で、mysqldを再起動する（不要かも）。バージョンアップした時は場合はmysql_upgradeを実行！
 +
 +
 +===== 踏み台 =====
 +ローカルホストから直接mysql接続できない場合、踏み台サーバ経由の方法
 +
 +  * 踏み台サーバ、ホスト名 proxy.example.com、sshを22222でサーブ
 +  * MySQLサーバ、ホスト名 remote-mysql.example.com、mysqlを3306でサーブ
 +
 +sshトンネルの設定
 +
 +  ssh -f -N -L 33306:remote-mysql.example.com:3306 -p 22222 nullpon@proxy.example.com
 +
 +別のターミナルでリモートのMySQLに接続
 +
 +  mysql db_name -u mysql_user_name -p -P ３３３06
 +
 +sshをバックグラウンドで動かす（終了後SSHをkillし忘れないように）
 +
 +  ssh -f -N -L 33306:remote-mysql.example.com:3306 -p 22222 nullpon@proxy.example.com
 +  mysql db_name -u mysql_user_name -p -P ３３３06
 +
 +自動でkillする方法（sleep 10の場合、10秒以内に接続しないとsshが終了するので注意）
 +
 +  ssh -f -L 33306:remote-mysql.example.com:3306 -p 22222 nullpon@proxy.example.com sleep 10
 +  mysql db_name -u mysql_user_name -p -P ３３３06    
 ===== テーブルの種類（ストレージエンジン） ===== ===== テーブルの種類（ストレージエンジン） =====
   * MyISAM - テーブルロック、フルテキストインデックス、Rツリーインデックス、超爆速count、超爆速insert（ただし同時にselectされないこと）   * MyISAM - テーブルロック、フルテキストインデックス、Rツリーインデックス、超爆速count、超爆速insert（ただし同時にselectされないこと）
行 111: 行 224:
 以下の3種類のマージメソッドが存在する 以下の3種類のマージメソッドが存在する
  
- * インデックスマージ共通集合アクセスアルゴリズム +  * インデックスマージ共通集合アクセスアルゴリズム 
- * インデックスマージ和集合アクセスアルゴリズム +  * インデックスマージ和集合アクセスアルゴリズム 
- * インデックスマージソート和集合アクセスアルゴリズム +  * インデックスマージソート和集合アクセスアルゴリズム 
  
 +ただしwhere条件によっては一方のインデックスだけが使われるケースも多い。
  
 +例
 +
 +<code>
 +mysql> show columns from hoge;
 ++-------+---------+------+-----+---------+-------+
 +| Field | Type    | Null | Key | Default | Extra |
 ++-------+---------+------+-----+---------+-------+
 +| id    | int(11) | NO   | PRI | NULL    |       |
 +| a     | int(11) | NO   | MUL | NULL    |       |
 +| b     | int(11) | NO   | MUL | NULL    |       |
 +| c     | int(11) | NO   |     | NULL    |       |
 ++-------+---------+------+-----+---------+-------+
 +4 rows in set (0.00 sec)
 +
 +mysql> show index from hoge;
 ++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 +| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 ++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 +| hoge  |          0 | PRIMARY  |            1 | id          | A         |      100320 |     NULL | NULL   |      | BTREE      |         |               |
 +| hoge  |          1 | a        |            1 | a           | A         |         202 |     NULL | NULL   |      | BTREE      |         |               |
 +| hoge  |          1 | b        |            1 | b           | A         |         202 |     NULL | NULL   |      | BTREE      |         |               |
 ++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 +3 rows in set (0.00 sec)
 +</code>
 +
 +==== インデックスマージ共通集合アクセス ====
 +
 +<code>
 +mysql> explain select * from hoge where a = 1 and b = 2;
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+-----------------------------------+
 +| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                             |
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+-----------------------------------+
 +|  1 | SIMPLE      | hoge  | index_merge | a,b           | a,b  | 4,4     | NULL |    9 | Using intersect(a,b); Using where |
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+-----------------------------------+
 +1 row in set (0.00 sec)
 +</code>
 +
 +and条件で別のキーを指定した場合に使われる（ただしこの例ではaとbの複合インデックスの方が圧倒的に高速）
 +
 +<code>
 +mysql> explain select * from hoge where id < 5 and a = 1;
 ++----+-------------+-------+-------------+---------------+-----------+---------+------+------+-----------------------------------------+
 +| id | select_type | table | type        | possible_keys | key       | key_len | ref  | rows | Extra                                   |
 ++----+-------------+-------+-------------+---------------+-----------+---------+------+------+-----------------------------------------+
 +|  1 | SIMPLE      | hoge  | index_merge | PRIMARY,a     | a,PRIMARY | 8,4     | NULL |    1 | Using intersect(a,PRIMARY); Using where |
 ++----+-------------+-------+-------------+---------------+-----------+---------+------+------+-----------------------------------------+
 +1 row in set (0.00 sec)
 +</code>
 +
 +primary keyの範囲検索と他のキー指定でも発生
 +
 +==== インデックスマージ和集合アクセス ====
 +
 +<code>
 +mysql> explain select * from hoge where a = 1 or b = 2;
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
 +| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                         |
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
 +|  1 | SIMPLE      | hoge  | index_merge | a,b           | a,b  | 4,4     | NULL | 1957 | Using union(a,b); Using where |
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
 +1 row in set (0.00 sec)
 +</code>
 +
 +or条件で別々のカラムを指定した場合に発生する
 +
 +
 +==== インデックスマージソート和集合アクセス ====
 +
 +<code>
 +mysql> explain select * from hoge where a < 5 or b < 5;
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------+
 +| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                              |
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------+
 +|  1 | SIMPLE      | hoge  | index_merge | a,b           | a,b  | 4,4     | NULL | 9794 | Using sort_union(a,b); Using where |
 ++----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------+
 +1 row in set (0.00 sec)
 +</code>
 +
 +or条件で別々のカラムを範囲検索した場合に発生する。
  
  
行 283: 行 476:
  
 Fulltextインデックスは再構築にも時間がかかるしメンテナンスが死ぬほどめんどくさいのでオススメしない。他の全文検索ライブラリを併用するのがいいだろう。 Fulltextインデックスは再構築にも時間がかかるしメンテナンスが死ぬほどめんどくさいのでオススメしない。他の全文検索ライブラリを併用するのがいいだろう。
 +
 +
 +===== JSON型カラム =====
 +
 +JSONフィールドの値で検索する
 +
 +<code sql>
 +where colName->'$.id' = 1 
 +
 +where JSON_EXTRACT(colName, '$.id') = 1
 +</code>
 +
mysql.1442035720.txt.gz · 最終更新: 2015/09/12 05:28 by nullpon