Symfony 4.4 getResult消火栓慢速-PHP8.1

Question

我将以前的Symfony 4.3应用程序更新为4.4，并使用PHP8.1和mySQL8将应用程序移动到新服务器上。在我使用PHP7.4的服务器之前。

现在我注意到我的应用程序在新设置上非常慢。有些路由速度慢1秒，还有4-5 seconds.And，这是一个非常大的命令，在旧系统上执行需要7分钟，现在需要多个小时。

我的代码并不完美，但这样的增加是不正常的。

例如，我发现了一个简单的数据库请求：

$queryBuilder = $this->em->createQueryBuilder()
        ->select("k")
        ->from(KVP::class, "k")
        ->andWhere("k.deleted = false")
        ->andWhere("(k.accept = 1 or k.deniedFrom is NOT NULL) and (k.done is NOT NULL or k.until is NULL)")
        ->addOrderBy('k.createdAt', 'DESC');
$queryBuilder->getQuery()->getResult();

当我将getResult更改为getArrayResult时，路由加载速度要快500 to。在旧的设置中，getResult和getArrayResult之间的执行时间没有差别。

我是需要为PHP8做一些特定的配置，还是需要修改代码？

下面是示例中KVP实体的摘录：

 namespace App\Entity\KVP;

     use App\Annotation\Acl;
     use App\Entity\Auth\User;
     use App\Entity\Employee\Department;
     use App\Traits\TableExtensionTrait;
     use Doctrine\ORM\Mapping as ORM;
     use Gedmo\Mapping\Annotation as Gedmo;
     use Symfony\Component\Validator\Constraints as Assert;

/**
 * KVP entity
 *
 * @ORM\Entity(repositoryClass="App\Repository\KVP\KVPRepository")
 * @ORM\Table(name="t_kvp")
 *
 *
 * @Gedmo\Loggable
 * @Acl
 */
class KVP
{
    // Includes create/update information
    use TableExtensionTrait;

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    protected $id;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\ToDos\ToDos", mappedBy="kvp")
     */
    protected $todos;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\News\News", mappedBy="kvp")
     */
    protected $newsKVP;

    /**
     * @ORM\OneToOne(targetEntity="App\Entity\Chat\ChatGroup")
     */
    private $chatGroup;

    /**
     * @var User
     * @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvp")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=true)
     */
    private $user;

    /**
     * @var User
     * @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvpResponsible")
     * @ORM\JoinColumn(name="userResponsible_id", referencedColumnName="id", nullable=true)
     */
    private $userResponsible;

    /**
     * @var Department
     * @ORM\ManyToOne(targetEntity="App\Entity\Employee\Department", inversedBy="kvpDepartment")
     * @ORM\JoinColumn(name="department_id", referencedColumnName="id", nullable=true)
     */
    private $department;

    /**
     * @var User
     * @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvpAcceptedFrom")
     * @ORM\JoinColumn(name="acceptedFrom_id", referencedColumnName="id", nullable=true)
     */
    private $acceptedFrom;

    /**
     * @var User
     * @ORM\ManyToOne(targetEntity="App\Entity\Auth\User", inversedBy="kvpDeniedFrom")
     * @ORM\JoinColumn(name="deniedFrom_id", referencedColumnName="id", nullable=true)
     */
    private $deniedFrom;

    /**
     * @var string
     * @ORM\Column(name="title", type="text", nullable=true)
     * @Assert\NotBlank(message="field.not.blank")
     */
    private $title;

这是我的composer.json：

{
    "type": "project",
    "license": "proprietary",
    "require": {
        "php": "^8.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "beberlei/doctrineextensions": "^1.2",
        "cboden/ratchet": "^0.4.2",
        "doctrine/annotations": "^1.13",
        "doctrine/doctrine-bundle": "^2.7.0",
        "doctrine/doctrine-migrations-bundle": "^3.2.2",
        "doctrine/orm": "^2.13",
        "friendsofsymfony/user-bundle": "~2.2",
        "gedmo/doctrine-extensions": "^3.9",
        "knplabs/knp-paginator-bundle": "^5.0",
        "knplabs/knp-snappy-bundle": "^1.7",
        "phpdocumentor/reflection-docblock": "^5.3.0",
        "sensio/framework-extra-bundle": "^5.1",
        "stof/doctrine-extensions-bundle": "^1.3",
        "symfony/asset": "4.4.*",
        "symfony/console": "4.4.*",
        "symfony/dotenv": "4.4.*",
        "symfony/expression-language": "4.4.*",
        "symfony/flex": "^1.3.1",
        "symfony/form": "4.4.*",
        "symfony/framework-bundle": "4.4.*",
        "symfony/http-client": "4.4.*",
        "symfony/intl": "4.4.*",
        "symfony/monolog-bundle": "^3.1",
        "symfony/process": "4.4.*",
        "symfony/property-access": "4.4.*",
        "symfony/property-info": "4.4.*",
        "symfony/proxy-manager-bridge": "4.4.*",
        "symfony/security-bundle": "4.4.*",
        "symfony/serializer": "4.4.*",
        "symfony/swiftmailer-bundle": "^3.1",
        "symfony/translation": "4.4.*",
        "symfony/twig-bundle": "4.4.*",
        "symfony/validator": "4.4.*",
        "symfony/web-link": "4.4.*",
        "symfony/webpack-encore-bundle": "^1.7",
        "symfony/yaml": "4.4.*",
        "twig/extensions": "^1.5",
        "umulmrum/holiday": "^0.6.0",
        "michaeldegroot/doctrine-encrypt-bundle": "5.1.*"
    },
    "require-dev": {
        "phpunit/phpunit": "^9.2",
        "symfony/browser-kit": "4.4.*",
        "symfony/css-selector": "4.4.*",
        "symfony/debug-bundle": "4.4.*",
        "symfony/maker-bundle": "^1.0",
        "symfony/phpunit-bridge": "4.4.*",
        "symfony/stopwatch": "4.4.*",
        "symfony/var-dumper": "4.4.*",
        "symfony/web-profiler-bundle": "4.4.*",
        "symfony/web-server-bundle": "4.4.*"
    },
    "config": {
        "preferred-install": {
            "*": "dist"
        },
        "sort-packages": true,
        "allow-plugins": {
            "symfony/flex": true
        }
    },
    "autoload": {
        "psr-4": {
            "App\\": "src/"
        }
    },
    "autoload-dev": {
        "psr-4": {
            "App\\Tests\\": "tests/"
        }
    },
    "replace": {
        "paragonie/random_compat": "2.*",
        "symfony/polyfill-ctype": "*",
        "symfony/polyfill-iconv": "*",
        "symfony/polyfill-php71": "*",
        "symfony/polyfill-php70": "*",
        "symfony/polyfill-php56": "*"
    },
    "scripts": {
        "auto-scripts": {
            "cache:clear": "symfony-cmd",
            "assets:install %PUBLIC_DIR%": "symfony-cmd"
        },
        "post-install-cmd": [
            "@auto-scripts"
        ],
        "post-update-cmd": [
            "@auto-scripts"
        ]
    },
    "conflict": {
        "symfony/symfony": "*"
    },
    "extra": {
        "symfony": {
            "allow-contrib": false,
            "require": "4.4.*"
        }
    }
}

有没有人有过同样的经历或者知道我在这里错过了什么？

编辑

我想我可以把它缩小到理论水化。当我使用与其他实体有多个连接的enitites创建getResult时，就会遇到性能问题。在旧的设置/版本上一切都很好。

这是来自上述请求的SQL请求：

SELECT t0_.id AS id_0, t0_.title AS title_1, t0_.problemState AS problemState_2, t0_.solution AS solution_3, t0_.accept AS accept_4, t0_.until AS until_5, t0_.inWork AS inWork_6, t0_.accepted AS accepted_7, t0_.comment AS comment_8, t0_.commentInWork AS commentInWork_9, t0_.deleted AS deleted_10, t0_.greenKvp AS greenKvp_11, t0_.done AS done_12, t0_.reminderSupervised AS reminderSupervised_13, t0_.lastReminderSupervised AS lastReminderSupervised_14, t0_.reminderAdmins AS reminderAdmins_15, t0_.lastReminderAdmins AS lastReminderAdmins_16, t0_.reminderSuperAdmins AS reminderSuperAdmins_17, t0_.lastReminderSuperAdmins AS lastReminderSuperAdmins_18, t0_.reminderOpen AS reminderOpen_19, t0_.lastReminderOpen AS lastReminderOpen_20, t0_.created_at AS created_at_21, t0_.updated_at AS updated_at_22, t0_.update_version AS update_version_23, t0_.chat_group_id AS chat_group_id_24, t0_.user_id AS user_id_25, t0_.userResponsible_id AS userResponsible_id_26, t0_.department_id AS department_id_27, t0_.acceptedFrom_id AS acceptedFrom_id_28, t0_.deniedFrom_id AS deniedFrom_id_29, t0_.created_by AS created_by_30, t0_.updated_by AS updated_by_31 
FROM t_kvp t0_ 
WHERE t0_.deleted = 0 
ORDER BY t0_.created_at DESC

这个节目创建了一个表格：

CREATE TABLE `t_kvp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `chat_group_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `title` longtext COLLATE utf8mb4_unicode_ci,
  `problemState` longtext COLLATE utf8mb4_unicode_ci,
  `solution` longtext COLLATE utf8mb4_unicode_ci,
  `accept` int DEFAULT NULL,
  `until` datetime DEFAULT NULL,
  `inWork` datetime DEFAULT NULL,
  `accepted` datetime DEFAULT NULL,
  `comment` longtext COLLATE utf8mb4_unicode_ci,
  `commentInWork` longtext COLLATE utf8mb4_unicode_ci,
  `deleted` tinyint(1) NOT NULL,
  `greenKvp` tinyint(1) NOT NULL,
  `done` datetime DEFAULT NULL,
  `reminderSupervised` int DEFAULT NULL,
  `lastReminderSupervised` date DEFAULT NULL,
  `reminderAdmins` int DEFAULT NULL,
  `lastReminderAdmins` date DEFAULT NULL,
  `reminderSuperAdmins` int DEFAULT NULL,
  `lastReminderSuperAdmins` date DEFAULT NULL,
  `reminderOpen` int DEFAULT NULL,
  `lastReminderOpen` date DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `update_version` int NOT NULL DEFAULT '1',
  `userResponsible_id` int DEFAULT NULL,
  `acceptedFrom_id` int DEFAULT NULL,
  `deniedFrom_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_34FC256BCA760E77` (`chat_group_id`),
  KEY `IDX_34FC256BA76ED395` (`user_id`),
  KEY `IDX_34FC256BEC9046B3` (`userResponsible_id`),
  KEY `IDX_34FC256BAE80F5DF` (`department_id`),
  KEY `IDX_34FC256BB6A8B8C4` (`acceptedFrom_id`),
  KEY `IDX_34FC256B36D52A95` (`deniedFrom_id`),
  KEY `IDX_34FC256BDE12AB56` (`created_by`),
  KEY `IDX_34FC256B16FE72E1` (`updated_by`),
  CONSTRAINT `FK_34FC256B16FE72E1` FOREIGN KEY (`updated_by`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `FK_34FC256B36D52A95` FOREIGN KEY (`deniedFrom_id`) REFERENCES `t_user` (`id`),
  CONSTRAINT `FK_34FC256BA76ED395` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`),
  CONSTRAINT `FK_34FC256BAE80F5DF` FOREIGN KEY (`department_id`) REFERENCES `t_department` (`id`),
  CONSTRAINT `FK_34FC256BB6A8B8C4` FOREIGN KEY (`acceptedFrom_id`) REFERENCES `t_user` (`id`),
  CONSTRAINT `FK_34FC256BCA760E77` FOREIGN KEY (`chat_group_id`) REFERENCES `t_chat_group` (`id`),
  CONSTRAINT `FK_34FC256BDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `FK_34FC256BEC9046B3` FOREIGN KEY (`userResponsible_id`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

当我只选择几个与其他实体没有连接的字段时，服务器响应就会很好。

这只是一个例子。问题是，在任何地方，我都可以获取与多个实体有连接的数据。(对错误的解释表示歉意:)

编辑2

我看过这两种设置的Symfony性能结果，这里的问题似乎不是Doctrine/Database。我的接缝毕竟是PHP，还是我解释错了屏幕截图？

Symfony 4.4 PHP 8.1

​

Symfony 4.3 PHP 7.4

​

Answer 1

    ->andWhere("k.deleted = false")
    ->andWhere("(k.accept = 1 or k.deniedFrom is NOT NULL) and (k.done is NOT NULL or k.until is NULL)")

那个凌乱的WHERE闻起来就像你在每个SELECT上钉的东西。对，是这样?把它归结为一个单一的标志，也许是is_valid。(如果你需要的话，也保留这些细节。)我们不能优化那些东西。

向我们展示生成的SQL和SHOW CREATE TABLE。有了这些，我们就能帮上忙了。

More

看起来整张桌子都会被扫描。桌子有多大？很多行都被“删除”了吗？