ONLY_FULL_GROUP_BY GROUP BY の出てくるクエリは、select_listは全て GROUP BY か集約関数に含まれ るべき continent と indepyear は1対1で紐づかないので、そんなものを許すと（本来）何が返ってく るかわからない ‐ mysql> SELECT continent, COUNT(*), indepyear FROM country GROUP BY continent; ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains n onaggregated column 'world.country.IndepYear' which is not functionally dependent on colu mns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 12/63
ONLY_FULL_GROUP_BY ついでに、集約した行とANY_VALUEの行の一貫性もない mysql> SELECT continent, ANY_VALUE(name), MAX(indepyear) FROM country GROUP BY continent; +---------------+-----------------+----------------+ | continent | ANY_VALUE(name) | MAX(indepyear) | +---------------+-----------------+----------------+ | North America | Aruba | 1983 | <-- Arubaのindepyearは1983ではない | Asia | Afghanistan | 1991 | | Africa | Angola | 1993 | | Europe | Albania | 1993 | | South America | Argentina | 1975 | | Oceania | American Samoa | 1994 | | Antarctica | Antarctica | NULL | +---------------+-----------------+----------------+ 7 rows in set (0.00 sec) mysql> SELECT continent, name, indepyear FROM country WHERE name = 'Aruba'; +---------------+-------+-----------+ | continent | name | indepyear | +---------------+-------+-----------+ | North America | Aruba | NULL | +---------------+-------+-----------+ 1 row in set (0.00 sec) 16/63
暗黙の型変換 3バイト範囲(t2)から4バイト範囲(t1)へのキャストは無損失だけど逆がそうではな いので、 WHERE にt1を絞り込める条件があるけれどJOINに使えなくなるので使わ ない mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.val LIKE 'a%'; +----+-------------+-------+------------+-------+---------------+------+---------+-----------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+--------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | val | val | 99 | NULL | 996424 | 100.00 | Using where; Using index | | 1 | SIMPLE | t1 | NULL | ref | val | val | 131 | d1.t2.val | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+--------+----------+--------------------------+ 2 rows in set, 2 warnings (0.02 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'val' due to type or collation conversion on field 'val' | | Note | 1003 | /* select#1 */ select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val`,`d1`.`t2`.`num` AS `num`,`d1`.`t2`.`val` AS `val` from `d1`.`t1` join `d1`.`t2` where ((`d1`.`t1`.`val` li ke 'a%') and (`d1`.`t1`.`val` = `d1`.`t2`.`val`)) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------+ 2 rows in set (0.00 sec) 25/63
ORDER BY .. LIMIT .. OFFSET Oracleだと OFFSET m ROWS FETCH FIRST n ROWS ONLY ? MySQLの場合は LIMIT n OFFSET m とか LIMIT m, n Oracleは知らないけどMySQLのOFFSETは0オリジン ‐ ユニークキーで ORDER BY していない限り、 LIMIT .. OFFSET .. で返ってくる行 は「不定」 WHEREとかとの兼ね合いで選ばれたインデックスで「見つかった順」にしか返さないので、使う インデックスが変わると戻ってくる行が変わる ‐ ユニークでない限り、 ORDER BY c1 ASC, c2 ASC でc1もc2も同じレコード同士がどういう順番 で返ってくるかはわからない ‐ 42/63
ORDER BY .. LIMIT .. OFFSET ORDER BY狙いのキーを使わない場合の処理 WHERE , HAVING でフィルタ後の件数が多ければ多いほどレスポンスが悪化していく 逆を言うとソートは常に最後までやってしまうので、OFFSETがいくら深くても性能にはそれほど寄与しないことが多 い（ sort_buffer_size をあふれるくらいまでいくと強く影響する） ‐ LIMIT .. OFFSET .. は「結果セットの転送に使うネットワーク使用量」くらいにしか寄与しな い ‐ 45/63
ORDER BY .. LIMIT .. OFFSET ORDER BY狙いのキーを使った場合の処理の順番 LIMIT n OFFSET m の場合、カーソルを合わせたインデックスレコードの先頭から n+(m+1)行 ぶん読み出して、先頭 m+1 行を捨てる ‐ OFFSET が深くなれば深くなるほど線形に近くレスポンスタイムが劣化していく 特にどうもこれselect_listを一緒に処理してから捨てているらしく、カバリングインデックスを使ったサブクエリ と組み合わせると速くなることが多い ‐ 46/63
yoku0825
finengine
kenta_hi
gonkun
nakasho
hirosatogamo
soracom
grimoh
keio_smilab
konifar
oracle4engineer
ytaisei
hankehly
shpigford
mojombo
marcelosomers
philhawksworth
danielanewman
bermonpainter
colly
morganepeng
moore
chrisshort
addyosmani
zakiwarfel