![gihyo.jp](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="282" height="54"><rect fill-opacity="0"/></svg>){kind=small}
MySQL道普請便り
第112回 知っておくと便利になるかもしれない小技
今回はMySQLを利用するうえで，
STRAIGHT_JOINの位置
第97回 JOIN_
みなさんはこのSTRAIGHT_
mysql> EXPLAIN SELECT Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code; +----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+-------+ | 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 100.00 | NULL | | 1 | SIMPLE | City | NULL | ref | CountryCode | CountryCode | 3 | world.Country.Code | 18 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
このクエリのINNER JOIN部分をSTRAIGHT_
mysql> EXPLAIN SELECT Country.Name as CountryName, City.Name AS City FROM City STRAIGHT_JOIN Country ON City.CountryCode=Country.Code;
2つ目はSELECTのすぐ後にSTRAIGHT_
mysql> EXPLAIN SELECT STRAIGHT_JOIN Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code;
どちらのクエリも駆動表が固定されて実行計画が出力されています。ただし，
mysql> SELECT STRAIGHT_JON Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.Cou' at line 1 mysql> SELECT Country.Name as CountryName, City.Name AS City FROM City STRAIGHT_JO Country ON City.CountryCode=Country.Code; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Country ON City.CountryCode=Country.Code' at line 1
3つ目のやり方はコメント構文に入れるやり方です。
EXPLAIN SELECT /*! STRAIGHT_JOIN */ Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code;
なお，
mysql> EXPLAIN SELECT /*! TRAIGHT_JOIN */ Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.Cou' at line 1
MySQLのバージョンによってSQLを切り分ける
第54回 SQLコメントを使ってみようにて，
これを利用して，
$ mysql -sse "/*!80000 SELECT distinct from_user, from_host FROM mysql.role_edges */" -bash: !80000: event not found
Bash側のエラーでevent not foundと表示されてしまいました。これはBashのヒストリ展開が!80000に反応して，
もしBashのスクリプトの中などで利用する場合は，
$ set +H $ mysql -sse "/*!80000 SELECT distinct from_user, from_host FROM mysql.role_edges */
MySQLのbyte単位のファンクション
MySQL 8.
たとえば，
mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 536870912 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT format_bytes(@@innodb_buffer_pool_size); +-----------------------------------------+ | format_bytes(@@innodb_buffer_pool_size) | +-----------------------------------------+ | 512.00 MiB | +-----------------------------------------+ 1 row in set (0.00 sec)
SET GLOBALなどで設定するときは乗算が利用できるので，
mysql> SET GLOBAL innodb_buffer_pool_size = 640 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> SELECT format_bytes(@@innodb_buffer_pool_size); +-----------------------------------------+ | format_bytes(@@innodb_buffer_pool_size) | +-----------------------------------------+ | 640.00 MiB | +-----------------------------------------+ 1 row in set (0.00 sec)
gtid_purgedに(+)を利用して設定する
gtid_
これだと多くのトランザクションをスキップする場合に手間がかかるため，
MySQL 8.+を用いることで，1000000-1000010のgtidを設定したい場合，
mysql> show variables like '%gtid_purged'; +----------------------------------+-----------------------------------------------+ | Variable_name | Value | +----------------------------------+-----------------------------------------------+ | gtid_purged | 7d2772a5-8987-11e8-a6a6-0201858272ab:1-344976 | +----------------------------------+-----------------------------------------------+ 1 rows in set (0.03 sec) mysql> SET GLOBAL gtid_purged = '+7d2772a5-8987-11e8-a6a6-0201858272ab:1000000-1000010'; Query OK, 0 rows affected (0.01 sec) mysql> show variables like '%gtid_purged'; +----------------------------------+---------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+---------------------------------------------------------------+ | gtid_purged | 7d2772a5-8987-11e8-a6a6-0201858272ab:1-344976:1000000-1000010 | +----------------------------------+---------------------------------------------------------------+ 1 rows in set (0.00 sec)
ただし，
mysql> SET GLOBAL gtid_purged = '+7d2772a5-8987-11e8-a6a6-0201858272ab:100000-100010'; ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
とはいえ，
より詳細な使い方については公式ドキュメントの17.
COMMIT，ROLLBACKのCHAINとRELEASE
COMMIT, ROLLBACKを実行する際に，AND CHAIN , RELEASEを付与することができるのはご存知でしょうか？ AND CHAINの場合は現在のトランザクションが終了したときに，
RELEASEの場合はトランザクションが終了したときにクライアントセッションを切断します。この挙動はcompletion_
より詳細な挙動については，
テーブル作成時のデータ型マッピング
MySQLにテーブルを作成するときに，int8とするとbigint型が作成されます。 他にも，
mysql> create table t1 (id int8);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
その他のマッピングについては，
まとめ
今回はMySQLを利用するときに役に立つかもしれない小技を少しだけご紹介しました。
みなさんはすべて把握済みだったでしょうか？