ACL関連SQLの一部にINDEXがつけられない
ACLを導入すると、アクセスのたびにACL関連テーブルにアクセスが発生するので、INDEX入れた方が良い箇所とかないかな？と思いSQL眺めてみましたが、
一部の高負荷SQLが、根本的にINDEXの効かない作りになっています。
SQL自体を変更すればINDEXが使えるようにもなるんですが・・・・
完全に本線変更になるので、どうしたものかとdb_acl.php眺めて考え中です*1
以下、チューニングは「何とかできる」レベルなので、もっと良いSQLがあるかもしれません*2
SELECT `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias` FROM `aros` AS `Aro` LEFT JOIN `aros` AS `Aro0` ON (`Aro`.`lft` <= `Aro0`.`lft` AND `Aro`.`rght` >= `Aro0`.`rght`) WHERE `Aro0`.`model` = 'User' AND `Aro0`.`foreign_key` = 1 ORDER BY `Aro`.`lft` DESC
「MySQLでINDEXが効かないケース：whereで使用するインデックスとorder byで使用するインデックスが異なる場合」に見事に該当しています。
そしてEXPLAINで調査すると、これがUsing temporary; Using filesort。高負荷の典型例です。
mysql> EXPLAIN(SELECT `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias` FROM `aros` AS `Aro` LEFT JOIN `aros` AS `Aro0` ON (`Aro`.`lft` <= `Aro0`.`lft` AND `Aro`.`rght` >= `Aro0`.`rght`) WHERE `Aro0`.`model` = 'User' AND `Aro0`.`foreign_key` = 1 ORDER BY `Aro`.`lft` DESC)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Aro type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Aro0 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using where 2 rows in set (0.00 sec)
例えばせめて、こういうSQLだったらINDEXが使えるようになるのですが。
SELECT `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias` FROM (SELECT * FROM `aros` ORDER BY `lft` DESC) AS `Aro` LEFT JOIN `aros` AS `Aro0` ON (`Aro`.`lft` <= `Aro0`.`lft` AND `Aro`.`rght` >= `Aro0`.`rght`) WHERE `Aro0`.`model` = 'User' AND `Aro0`.`foreign_key` = 1;
EXPLAIN。
mysql> EXPLAIN(SELECT `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias` FROM (SELECT * FROM `aros` ORDER BY `lft` DESC) AS `Aro` LEFT JOIN `aros` AS `Aro0` ON (`Aro`.`lft` <= `Aro0`.`lft` AND `Aro`.`rght` >= `Aro0`.`rght`) WHERE `Aro0`.`model` = 'User' AND `Aro0`.`foreign_key` = 1)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 29 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: Aro0 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using where *************************** 3. row *************************** id: 2 select_type: DERIVED table: aros type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using filesort 3 rows in set (0.00 sec)
これだけでも、Using temporaryが消えます。
そしてlftと、model&foreign_keyにINDEX追加で、1. rowと3.rowが改善されました。
mysql> CREATE INDEX lft ON aros (lft); Query OK, 29 rows affected (0.00 sec) Records: 29 Duplicates: 0 Warnings: 0 mysql> CREATE INDEX model_foreign_key ON aros (model, foreign_key); Query OK, 29 rows affected (0.01 sec) Records: 29 Duplicates: 0 Warnings: 0 mysql> EXPLAIN(SELECT `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias` FROM (SELECT * FROM `aros` ORDER BY `lft` DESC) AS `Aro` LEFT JOIN `aros` AS `Aro0` ON (`Aro`.`lft` <= `Aro0`.`lft` AND `Aro`.`rght` >= `Aro0`.`rght`) WHERE `Aro0`.`model` = 'User' AND `Aro0`.`foreign_key` = 1)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: Aro0 type: ref possible_keys: model_foreign_key,lft key: model_foreign_key key_len: 773 ref: const,const rows: 1 Extra: Using where *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using where *************************** 3. row *************************** id: 2 select_type: DERIVED table: aros type: index possible_keys: NULL key: lft key_len: 5 ref: NULL rows: 29 Extra: 3 rows in set (0.01 sec)
SQLを発行しているcake/libs/model/db_acl.php を見ると、必ず ORDER BY の指定を入れてます。
必要に応じて付ける、とかのオプションはなし。
どうしたものか・・・・
