Форум пользователей MySQL

RSol · 07.06.2010 09:38:20

Добрый день!

Подскажите, есть таблица прав доступа:

CREATE TABLE `tkt_permission_category_access` (

  `access_id` int(10) unsigned NOT NULL auto_increment,

  `access_group_id` int(10) unsigned NOT NULL,

  `access_user_id` int(10) unsigned NOT NULL,

  `access_theme_id` int(10) unsigned NOT NULL,

  `access_permission_category_id` int(10) unsigned NOT NULL,

  PRIMARY KEY  (`access_id`),

  KEY `access_goup_id` (`access_group_id`,`access_user_id`),

  KEY `access_permission_category_id` (`access_permission_category_id`),

  KEY `access_user_id` (`access_user_id`),

  KEY `access_theme_id` (`access_theme_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `tkt_permission_category_access`

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_4` FOREIGN KEY (`access_theme_id`) REFERENCES `tkt_theme` (`theme_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_1` FOREIGN KEY (`access_permission_category_id`) REFERENCES `tkt_permission_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_2` FOREIGN KEY (`access_user_id`) REFERENCES `tkt_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_3` FOREIGN KEY (`access_group_id`) REFERENCES `tkt_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

В ней будут храниться права доступа для групп (`access_group_id`=XXX,`access_user_id`=0) и конкретных пользователей (`access_group_id`=XXX,`access_user_id`=YYY). Если права указаны для конкретных пользователей - они "покрывают" права группы.

Таким образом чтобы найти права доступа конкретного пользователя (`access_group_id`=1,`access_user_id`=1) нужен запрос:

SELECT * FROM tkt_permission_category_access WHERE `access_group_id`=1 AND 

            (`access_user_id`=1 OR `access_user_id`=0) AND 

            `access_permission_category_id` NOT IN 

                        (SELECT `access_permission_category_id` FROM tkt_permission_category_access 

                                WHERE `access_group_id`=1 AND `access_user_id`=1)

UNION

SELECT * FROM tkt_permission_category_access WHERE `access_group_id`=1 AND `access_user_id`=1

Собственно вопросы:
1. Можно ли придумать более эффективный запрос
2. Правильно ли расставлены индексы?

Спасибо

Shopen · 07.06.2010 21:55:44

а. Что показывает EXPLAIN?
б. Навскидку, в вашем запросе нужен только один составной индекс (не считая примари) - `access_group_id`, `access_user_id` или наоборот, в зависимости от реальных данных (т.е. чего больше разброс значений групп или пользователей), может быть к нему в хвост можно добавить `access_permission_category_id`, но по моему он испльзоваться не будет. Можно поэкспериментировать поглядывая на explain. Индексы вообще всегда зависят сильно от данных, если у вас пользователей 10 - то индексы и вовсе не нужны

RSol · 07.06.2010 22:15:21

Спасибо

Вот данные EXPLAIN

+----+--------------------+--------------------------------+------+-------------------------------------------------------------+----------------+---------+-------------+------+-------------+

| id | select_type        | table                          | type | possible_keys                                               | key            | key_len | ref         | rows | Extra       |

+----+--------------------+--------------------------------+------+-------------------------------------------------------------+----------------+---------+-------------+------+-------------+

|  1 | PRIMARY            | tkt_permission_category_access | ref  | access_goup_id,access_user_id                               | access_goup_id | 4       | const       |    1 | Using where |

|  2 | DEPENDENT SUBQUERY | tkt_permission_category_access | ref  | access_goup_id,access_permission_category_id,access_user_id | access_goup_id | 8       | const,const |    1 | Using where |

|  3 | UNION              | tkt_permission_category_access | ref  | access_goup_id,access_user_id                               | access_goup_id | 8       | const,const |    1 |             |

|NULL | UNION RESULT      | <union1,3>                     | ALL  | NULL                                                        | NULL           | NULL    | NULL        | NULL |             |

+----+--------------------+--------------------------------+------+-------------------------------------------------------------+----------------+---------+-------------+------+-------------+

4 rows in set (0.06 sec)

Пока данных мало, но ........ Ваше утверждение верно на 100%.

Shopen · 07.06.2010 23:22:35

RSol написал:
Ваше утверждение верно на 100%.

которое?

судя по explain - у вас все хорошо, но я бы все равно сделал один составной индекс

RSol · 08.06.2010 09:17:39

Shopen написал:
RSol написал:
Ваше утверждение верно на 100%.
которое?

судя по explain - у вас все хорошо, но я бы все равно сделал один составной индекс

Доработал индекс:

CREATE TABLE `tkt_permission_category_access` (

  `access_id` int(10) unsigned NOT NULL auto_increment,

  `access_group_id` int(10) unsigned NOT NULL,

  `access_user_id` int(10) unsigned NOT NULL,

  `access_theme_id` int(10) unsigned NOT NULL,

  `access_permission_category_id` int(10) unsigned NOT NULL,

  PRIMARY KEY  (`access_id`),

  KEY `access_goup_id` (`access_group_id`,`access_user_id`,`access_theme_id`),

  KEY `access_permission_category_id` (`access_permission_category_id`),

  KEY `access_user_id` (`access_user_id`),

  KEY `access_theme_id` (`access_theme_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `tkt_permission_category_access`

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_4` FOREIGN KEY (`access_theme_id`) REFERENCES `tkt_theme` (`theme_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_1` FOREIGN KEY (`access_permission_category_id`) REFERENCES `tkt_permission_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_2` FOREIGN KEY (`access_user_id`) REFERENCES `tkt_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `tkt_permission_category_access_ibfk_3` FOREIGN KEY (`access_group_id`) REFERENCES `tkt_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

т.е. уточнил составной индекс, а остальные для целостности БД.

Да, была ошибка в запросе. Он будет таким:

SELECT * FROM tkt_permission_category_access WHERE `access_group_id`=1 AND

            (`access_user_id`=1 OR `access_user_id`=0) AND

            `access_theme_id` NOT IN

                        (SELECT `access_theme_id` FROM tkt_permission_category_access

                                WHERE `access_group_id`=1 AND `access_user_id`=1)

UNION

SELECT * FROM tkt_permission_category_access WHERE `access_group_id`=1 AND `access_user_id`=1

Подскажите, вообще стоит увлекаться индексам для сохранения целостности БД или это лужче переложить на плечи приложения?

paulus · 08.06.2010 20:51:24

Всё зависит от того, какие требования у Вас к приложению. Любые ограничения
замедляют работу (т.к. ограничения нужно проверять). Обычно приложение написано
таким образом, что не позволяет вводить плохие данные (Вы просто не сможете напи-
сать разумное приложение, которое вводит плохие ). В mission-critical приложениях,
тем не менее, разумно оставлять ограничения — просто на случай сбоя (или ошибки)
приложения.

Форум пользователей MySQL

#1 07.06.2010 09:38:20

Запрос и настройка индексов

#2 07.06.2010 21:55:44

Re: Запрос и настройка индексов

#3 07.06.2010 22:15:21

Re: Запрос и настройка индексов

#4 07.06.2010 23:22:35

Re: Запрос и настройка индексов

RSol написал:

#5 08.06.2010 09:17:39

Re: Запрос и настройка индексов

Shopen написал:

RSol написал:

#6 08.06.2010 20:51:24

Re: Запрос и настройка индексов

Board footer