主にACL用のSQLにINDEX追加
主にACL関連のSQLでINDEX追加できる物が無いかを調査してみました。
参考
MySQLとオープンソースに捧げる毎日:MySQLチューニング - livedoor Blog（ブログ）
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.8.2 EXPLAIN 構文
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.15 ORDER BY の最適化
MySQL逆引きクイックリファレンス―MySQL 4.0/4.1/5.0対応
- 作者: 山田祥寛
- 出版社/メーカー: 毎日コミュニケーションズ
- 発売日: 2006/12
- メディア: 単行本
- この商品を含むブログ (3件) を見る
結果、追加したINDEX
- CREATE INDEX model_foriegnkey ON aros (model, foreign_key);
- CREATE INDEX alias ON acos (alias);
- CREATE INDEX lft_rght ON acos (lft, rght);
- CREATE INDEX aro_aco ON aros_acos (aro_id, aco_id);
以下、検証作業ログ。
準備。
mysql> ANALYZE TABLE aros, acos, aros_acos; +----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | cake_frame.aros | analyze | status | OK | | cake_frame.acos | analyze | status | OK | | cake_frame.aros_acos | analyze | status | OK | +----------------------+---------+----------+----------+ 3 rows in set (0.00 sec)
Super Administratorのホームを表示して、デバッグ表示に出力されたSQLをEXPLAINで調査。
本当はスロークエリー調査してINDEX作るのですが、とりあえずsessionとACL関連は間違いなく頻繁に呼ばれるSQLなので。
cake_sessions。問題なし。
というかプライマリキーにセッションID入れてるんですね。この発想はなかった。
mysql> EXPLAIN (SELECT `cake_sessions`.`data` FROM `cake_sessions` WHERE `cake_sessions`.`id` = 'bk8sgg15thfvr20fpfa7arsam4')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cake_sessions type: const possible_keys: PRIMARY key: PRIMARY key_len: 767 ref: const rows: 1 Extra: 1 row in set (0.00 sec)
これは先の記事でも取り上げた、INDEXの効かないSQLなので保留。
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;
controllers/Usersのアクセス制限関連。
SELECT `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias` FROM `acos` AS `Aco` LEFT JOIN `acos` AS `Aco0` ON (`Aco0`.`alias` = 'controllers') LEFT JOIN `acos` AS `Aco1` ON (`Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco1`.`alias` = 'Users') WHERE ( (`Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght`) OR (`Aco`.`lft` <= `Aco1`.`lft` AND `Aco`.`rght` >= `Aco1`.`rght`) ) ORDER BY `Aco`.`lft` DESC;
Using temporary; Using filesortの高負荷コンボですが、こちらはINDEXの余地がありそうです。
mysql> EXPLAIN(SELECT `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias` FROM `acos` AS `Aco` LEFT JOIN `acos` AS `Aco0` ON (`Aco0`.`alias` = 'controllers') LEFT JOIN `acos` AS `Aco1` ON (`Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco1`.`alias` = 'Users') WHERE ((`Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght`) OR (`Aco`.`lft` <= `Aco1`.`lft` AND `Aco`.`rght` >= `Aco1`.`rght`)) ORDER BY `Aco`.`lft` DESC)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Aco type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Aco0 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: Aco1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7 Extra: Using where 3 rows in set (0.01 sec)
まず、acosのaliasにINDEX追加。
mysql> CREATE INDEX alias ON acos (alias); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> EXPLAIN(SELECT `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias` FROM `acos` AS `Aco` LEFT JOIN `acos` AS `Aco0` ON (`Aco0`.`alias` = 'controllers') LEFT JOIN `acos` AS `Aco1` ON (`Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco1`.`alias` = 'Users') WHERE ((`Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght`) OR (`Aco`.`lft` <= `Aco1`.`lft` AND `Aco`.`rght` >= `Aco1`.`rght`)) ORDER BY `Aco`.`lft` DESC)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Aco type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7 Extra: Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Aco0 type: ref possible_keys: alias key: alias key_len: 768 ref: const rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: Aco1 type: ref possible_keys: alias key: alias key_len: 768 ref: const rows: 1 Extra: Using where 3 rows in set (0.00 sec)
しかる後にlft, rghtにINDEX作成で、Using filesortも解消！
mysql> CREATE INDEX lft_rght ON acos (lft, rght); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> EXPLAIN(SELECT `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias` FROM `acos` AS `Aco` LEFT JOIN `acos` AS `Aco0` ON (`Aco0`.`alias` = 'controllers') LEFT JOIN `acos` AS `Aco1` ON (`Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco1`.`alias` = 'Users') WHERE ((`Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght`) OR (`Aco`.`lft` <= `Aco1`.`lft` AND `Aco`.`rght` >= `Aco1`.`rght`)) ORDER BY `Aco`.`lft` DESC)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Aco type: index possible_keys: lft_rght key: lft_rght key_len: 10 ref: NULL rows: 7 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Aco0 type: ref possible_keys: alias key: alias key_len: 768 ref: const rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: Aco1 type: ref possible_keys: alias,lft_rght key: alias key_len: 768 ref: const rows: 1 Extra: Using where 3 rows in set (0.00 sec)
次。
mysql> SELECT `Permission`.`id`, `Permission`.`aro_id`, `Permission`.`aco_id`, `Permission`.`_create`, `Permission`.`_read`, `Permission`.`_update`, `Permission`.`_delete`, `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias`, `Aro`.`lft`, `Aro`.`rght`, `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias`, `Aco`.`lft`, `Aco`.`rght` FROM `aros_acos` AS `Permission` LEFT JOIN `aros` AS `Aro` ON (`Permission`.`aro_id` = `Aro`.`id`) LEFT JOIN `acos` AS `Aco` ON (`Permission`.`aco_id` = `Aco`.`id`) WHERE `Permission`.`aro_id` = 9 AND `Permission`.`aco_id` IN (2, 1) ORDER BY `Aco`.`lft` desc;
Empty set (0.00 sec)
お約束のようにUsing where; Using temporary; Using filesort
こちらもWHERE とORDER BYが異なるパターン。
mysql> EXPLAIN(SELECT `Permission`.`id`, `Permission`.`aro_id`, `Permission`.`aco_id`, `Permission`.`_create`, `Permission`.`_read`, `Permission`.`_update`, `Permission`.`_delete`, `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias`, `Aro`.`lft`, `Aro`.`rght`, `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias`, `Aco`.`lft`, `Aco`.`rght` FROM `aros_acos` AS `Permission` LEFT JOIN `aros` AS `Aro` ON (`Permission`.`aro_id` = `Aro`.`id`) LEFT JOIN `acos` AS `Aco` ON (`Permission`.`aco_id` = `Aco`.`id`) WHERE `Permission`.`aro_id` = 9 AND `Permission`.`aco_id` IN (2, 1) ORDER BY `Aco`.`lft` desc)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Permission type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 20 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Aro type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: Aco type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: cake_frame.Permission.aco_id rows: 1 Extra: 3 rows in set (0.00 sec)
INDEXを貼りましたが、完全解決にはなっていません・・・
mysql> CREATE INDEX aro_aco ON aros_acos (aro_id, aco_id); Query OK, 20 rows affected (0.00 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> EXPLAIN(SELECT `Permission`.`id`, `Permission`.`aro_id`, `Permission`.`aco_id`, `Permission`.`_create`, `Permission`.`_read`, `Permission`.`_update`, `Permission`.`_delete`, `Aro`.`id`, `Aro`.`parent_id`, `Aro`.`model`, `Aro`.`foreign_key`, `Aro`.`alias`, `Aro`.`lft`, `Aro`.`rght`, `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias`, `Aco`.`lft`, `Aco`.`rght` FROM `aros_acos` AS `Permission` LEFT JOIN `aros` AS `Aro` ON (`Permission`.`aro_id` = `Aro`.`id`) LEFT JOIN `acos` AS `Aco` ON (`Permission`.`aco_id` = `Aco`.`id`) WHERE `Permission`.`aro_id` = 9 AND `Permission`.`aco_id` IN (2, 1) ORDER BY `Aco`.`lft` desc)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Permission type: range possible_keys: aro_aco key: aro_aco key_len: 8 ref: NULL rows: 2 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Aro type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: Aco type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: cake_frame.Permission.aco_id rows: 1 Extra: 3 rows in set (0.00 sec)
変数が変わっただけで同上のSQLは飛ばして、次。
全然問題なし。
mysql> EXPLAIN(SELECT COUNT(*) AS `count` FROM `users` AS `User` WHERE `User`.`id` = 1 )\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: User type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 1 row in set (0.00 sec)
先の記事の検証で追加したINDEXが別の箇所で効いていた件。
mysql> SELECT `Aro`.`id` FROM `aros` AS `Aro` WHERE `model` = 'User' AND `foreign_key` = 1 LIMIT 1; +----+ | id | +----+ | 9 | +----+ 1 row in set (0.00 sec) mysql> EXPLAIN(SELECT `Aro`.`id` FROM `aros` AS `Aro` WHERE `model` = 'User' AND `foreign_key` = 1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Aro type: ref possible_keys: model_foreign_key key: model_foreign_key key_len: 773 ref: const,const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)
以下、問題ない件をさくさくと。
mysql> EXPLAIN(SELECT COUNT(*) AS `count` FROM `aros` AS `Aro` WHERE `Aro`.`id` = 9)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Aro type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 1 row in set (0.00 sec) mysql> EXPLAIN(SELECT `User`.`id`, `User`.`group_id`, `User`.`name`, `User`.`username`, `User`.`password`, `User`.`pcmail`, `User`.`mobile_mail`, `User`.`useragent`, `User`.`host`, `User`.`created`, `User`.`modified`, `Group`.`id`, `Group`.`name`, `Group`.`parent_id` FROM `users` AS `User` LEFT JOIN `groups` AS `Group` ON (`User`.`group_id` = `Group`.`id`) WHERE `User`.`id` = 1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: User type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Group type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 2 rows in set (0.00 sec)
で、終了。
・・・・で、冒頭の追加INDEXまとめに繋がります。
