单行的SQL视图(带有where子句)

Question

我正在开发一个管理课程的系统，有多个课程，有多个课程，有多个消费者。随着系统的增长，需要更多的数据，所以对于一些性能问题，我决定使用SQL视图。我们正在使用MySQL。

因此，我已经替换了对DB的旧调用(例如，对于单个课程)。

select * from `courses_classes_lessons` where `courses_classes_lessons`.`deleted_at` is null limit 1;
select count(consumer_id) as consumers_count from `courses_classes_lessons_consumers` where `lesson_id` = '448' limit 1;
select `max_consumers` from `courses_classes` where `id` = '65' limit 1;
select `id` from `courses_classes_lessons` left join `courses_classes_lessons_consumers` on `courses_classes_lessons_consumers`.`lesson_id` = `courses_classes_lessons`.`id` where `id` = '448' group by `courses_classes_lessons`.`id` having count(courses_classes_lessons_consumers.consumer_id) < '4' limit 1;
select courses_classes.max_consumers - LEAST(count(courses_classes_lessons_consumers.consumer_id), courses_classes.max_consumers) as available_spaces from `courses_classes_lessons` left join `courses_classes_lessons_consumers` on `courses_classes_lessons_consumers`.`lesson_id` = `courses_classes_lessons`.`id` left join `courses_classes` on `courses_classes_lessons`.`class_id` = `courses_classes`.`id` where `courses_classes_lessons`.`id` = '448' group by `courses_classes`.`id` limit 1;

以上所述时间约为4-5毫秒。

使用SQL视图，如下所示：

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
SELECT
    courses_classes_lessons.id AS lesson_id,
    (SELECT
            max_consumers
        FROM
            courses_classes
        WHERE
            id = courses_classes_lessons.class_id
        LIMIT 1) AS class_max_consumers,
    (SELECT
        count(consumer_id)
    FROM
        courses_classes_lessons_consumers
    WHERE
        lesson_id = courses_classes_lessons.id) AS consumers_count, 
    (SELECT
        CASE WHEN consumers_count >= class_max_consumers THEN
            TRUE
        ELSE
            FALSE
        END AS is_full) AS is_full, 
    (CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
        'completed'
    WHEN courses_classes_lessons.cancelled_at > NOW() THEN
        'cancelled'
    WHEN courses_classes_lessons.starts_at > NOW() THEN
        'upcoming'
    ELSE
        'incomplete'
    END) AS status, 
    (SELECT
        class_max_consumers - LEAST(consumers_count, class_max_consumers)) AS available_spaces
    FROM
        courses_classes_lessons

我的问题是，这并不重要，如果我是加载整个视图或从它的一行-它总是需要6-9的加载！但是，当我使用WHERE子句尝试使用相同的查询时，它需要大约500个μs。我对SQL很陌生，并且很困惑--为什么没有索引/主键可以用来快速加载单个行？我做错了什么吗？

解释结果

INSERT INTO `courses_classes` (`id`, `select_type`, `table`, `partitions`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `filtered`, `Extra`) VALUES
(1, 'PRIMARY', 'courses_classes_lessons', NULL, 'ALL', NULL, NULL, NULL, NULL, 478832, 100.00, NULL),
(3, 'DEPENDENT SUBQUERY', 'courses_classes_lessons_consumers', NULL, 'ref', 'PRIMARY,courses_classes_lessons_consumers_lesson_id_index', 'courses_classes_lessons_consumers_lesson_id_index', '4', 'api.courses_classes_lessons.id', 3, 100.00, 'Using index'),
(2, 'DEPENDENT SUBQUERY', 'courses_classes', NULL, 'eq_ref', 'PRIMARY,courses_classes_id_parent_id_index', 'PRIMARY', '4', 'api.courses_classes_lessons.class_id', 1, 100.00, NULL);

表结构

课程

CREATE TABLE `courses_classes_lessons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `franchisee_id` int(10) unsigned NOT NULL,
  `class_id` int(10) unsigned NOT NULL,
  `instructor_id` int(10) unsigned NOT NULL,
  `instructor_rate` int(10) unsigned NOT NULL DEFAULT '0',
  `instructor_total` int(10) unsigned NOT NULL DEFAULT '0',
  `instructor_paid` tinyint(1) NOT NULL DEFAULT '0',
  `starts_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancelled_reason` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cancelled_reason_extra` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `courses_classes_lessons_franchisee_id_foreign` (`franchisee_id`),
  KEY `courses_classes_lessons_class_id_foreign` (`class_id`),
  KEY `courses_classes_lessons_instructor_id_foreign` (`instructor_id`),
  KEY `courses_classes_lessons_starts_at_ends_at_index` (`starts_at`,`ends_at`),
  KEY `courses_classes_lessons_completed_at_index` (`completed_at`),
  KEY `courses_classes_lessons_cancelled_at_index` (`cancelled_at`),
  KEY `courses_classes_lessons_class_id_deleted_at_index` (`class_id`,`deleted_at`),
  KEY `courses_classes_lessons_deleted_at_index` (`deleted_at`),
  KEY `class_ownership_index` (`class_id`,`starts_at`,`cancelled_at`,`deleted_at`),
  CONSTRAINT `courses_classes_lessons_class_id_foreign` FOREIGN KEY (`class_id`) REFERENCES `courses_classes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_franchisee_id_foreign` FOREIGN KEY (`franchisee_id`) REFERENCES `franchisees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_instructor_id_foreign` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=487853 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

课程消费者

CREATE TABLE `courses_classes_lessons_consumers` (
  `lesson_id` int(10) unsigned NOT NULL,
  `consumer_id` int(10) unsigned NOT NULL,
  `present` tinyint(1) DEFAULT NULL,
  `plan_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`lesson_id`,`consumer_id`),
  KEY `courses_classes_lessons_consumers_consumer_id_foreign` (`consumer_id`),
  KEY `courses_classes_lessons_consumers_plan_id_foreign` (`plan_id`),
  KEY `courses_classes_lessons_consumers_lesson_id_index` (`lesson_id`),
  KEY `courses_classes_lessons_consumers_present_index` (`present`),
  CONSTRAINT `courses_classes_lessons_consumers_consumer_id_foreign` FOREIGN KEY (`consumer_id`) REFERENCES `customers_consumers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_consumers_lesson_id_foreign` FOREIGN KEY (`lesson_id`) REFERENCES `courses_classes_lessons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_consumers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `customers_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

在类中，它只使用max_consumers int(10)无符号非空默认值'0'，

更新1

我已将SQL视图更改为以下内容：

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
SELECT
    courses_classes_lessons.id AS lesson_id,
    courses_classes.max_consumers AS class_max_consumers,
    lessons_consumers.consumers_count AS consumers_count,
    (
        SELECT
            CASE WHEN consumers_count >= class_max_consumers THEN
                TRUE
            ELSE
                FALSE
            END AS is_full) AS is_full,
        (
            CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
                'completed'
            WHEN courses_classes_lessons.cancelled_at > NOW() THEN
                'cancelled'
            WHEN courses_classes_lessons.starts_at > NOW() THEN
                'upcoming'
            ELSE
                'incomplete'
            END) AS status,
        (
            SELECT
                class_max_consumers - LEAST(consumers_count, class_max_consumers)) AS available_spaces
        FROM
            courses_classes_lessons
            JOIN courses_classes ON courses_classes.id = courses_classes_lessons.class_id
            JOIN (
                SELECT
                    lesson_id,
                    count(*) AS consumers_count
                FROM
                    courses_classes_lessons_consumers
                GROUP BY
                    courses_classes_lessons_consumers.lesson_id) AS lessons_consumers ON lessons_consumers.lesson_id = courses_classes_lessons.id;

尽管SELECT查询本身似乎比前面的查询慢得多，但是作为View，它的性能似乎要好得多。它仍然没有我希望的那么快，但这是向前迈出的一步。

总体改进从6-7上升到大约800 is，这里的目标是在500μs-1毫秒的范围内。有什么可以改进我的SQL视图的吗？

更新2

好了，我找到瓶颈了！同样，它与最后一个类似(SELECT查询对单个行快速工作，但是SQL视图每次都试图同时访问整个表。

我的新经验SQL视图：

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
                SELECT
                courses_classes_lessons.id AS lesson_id,
                courses_classes.max_consumers AS class_max_consumers,
                IFNULL(lessons_consumers.consumers_count,0) AS consumers_count,
                (
                    SELECT
                        CASE WHEN consumers_count >= class_max_consumers THEN
                            TRUE
                        ELSE
                            FALSE
                        END AS is_full) AS is_full,
                    (
                        CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
                            'completed'
                        WHEN courses_classes_lessons.cancelled_at > NOW() THEN
                            'cancelled'
                        WHEN courses_classes_lessons.starts_at > NOW() THEN
                            'upcoming'
                        ELSE
                            'incomplete'
                        END) AS status,
                    (
                        SELECT
                            IFNULL(class_max_consumers, 0) - LEAST(IFNULL(consumers_count,0), class_max_consumers)) AS available_spaces
                    FROM
                        courses_classes_lessons
                        JOIN courses_classes ON courses_classes.id = courses_classes_lessons.class_id
                        LEFT JOIN courses_classes_lessons_consumers_view AS lessons_consumers ON lessons_consumers.lesson_id = courses_classes_lessons.id;

另一个SQL视图--这一次是针对消费者的：

CREATE OR REPLACE VIEW `courses_classes_lessons_consumers_view` AS
                SELECT
                    lesson_id,
                    IFNULL(count(
                        consumer_id),0) AS consumers_count
                FROM
                    courses_classes_lessons_consumers
                GROUP BY
                    courses_classes_lessons_consumers.lesson_id;

看上去这是个麻烦制造者！使用者表在上面，下面是上面选择查询的解释：

INSERT INTO `courses_classes_lessons_consumers` (`id`, `select_type`, `table`, `partitions`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `filtered`, `Extra`)
        VALUES(1, 'SIMPLE', 'courses_classes_lessons_consumers', NULL, 'index', 'PRIMARY,courses_classes_lessons_consumers_consumer_id_foreign,courses_classes_lessons_consumers_plan_id_foreign,courses_classes_lessons_consumers_lesson_id_index,courses_classes_lessons_consumers_present_index', 'courses_classes_lessons_consumers_lesson_id_index', '4', NULL, 1330649, 100.00, 'Using index');

你知道怎么把这个数目摊开吗？

Answer 1

经过多次尝试，看起来过程方式是最好的方法，我不会在SQL视图上花费更多时间。

我写的程序如下：

CREATE PROCEDURE `LessonData`(
                IN lessonId INT(10)
                )
            BEGIN
                SELECT
                courses_classes_lessons.id AS lesson_id,
                courses_classes.max_consumers AS class_max_consumers,
                IFNULL((SELECT
                    count(consumer_id) as consumers_count
                FROM
                    courses_classes_lessons_consumers
                WHERE
                    lesson_id = courses_classes_lessons.id
                GROUP BY
                    courses_classes_lessons_consumers.lesson_id), 0) AS consumers_count,
                (
                    SELECT
                        CASE WHEN consumers_count >= class_max_consumers THEN
                            TRUE
                        ELSE
                            FALSE
                        END) AS is_full,
                    (
                        CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
                            'completed'
                        WHEN courses_classes_lessons.cancelled_at > NOW() THEN
                            'cancelled'
                        WHEN courses_classes_lessons.starts_at > NOW() THEN
                            'upcoming'
                        ELSE
                            'incomplete'
                        END) AS status,
                    (
                        SELECT
                            class_max_consumers - LEAST(consumers_count, class_max_consumers)) AS available_spaces
                    FROM
                        courses_classes_lessons
                        JOIN courses_classes ON courses_classes.id = courses_classes_lessons.class_id
                    WHERE courses_classes_lessons.id = lessonId;    
            END

它的执行时间约为500μs-1ms。

谢谢大家的帮助！

Answer 2

考虑编写一个存储过程；它可能会使448得到更好的优化。

如果你知道只有一行(比如做LIMIT 1.

• Unless的时候)，跳过COUNT(consumer_id).

• A consumer_id可能是NULL，使用COUNT(*)代替没有ORDER BY的LIMIT就会产生随机行。H 115如果d16是一个多到多的映射表，在我看到5<代码>d20的SHOW CREATE TABLE.
• Which >是最慢的？H 221f之后，我可能会有一些索引建议。