时间戳

首页 > 解决方案 > 如何在 MySQL 和 PHP 中处理随时间增加的数据

php - 如何在 MySQL 和 PHP 中处理随时间增加的数据

问题描述

我的网站上有一个内容列表，它很快就会被记录填满，例如一个月后，里面有 30 万条记录。因此，随着时间的推移，列表页面的响应速度越来越慢。

此页面具有以下功能：

此表还左加入其他表，它增加了运行时间。

每次加载此页面时，我都会运行两个查询，一个用于获取 10 条有限记录，另一个用于获取所有记录的数量。

如何在不影响用户体验的情况下处理这么多数据？

编辑

这是我的查询：

SELECT *,
    `note`.`attached_file` AS `attached_file`,
    `note`.`description` AS `description`,
    `note`.`id` AS `id`,
    `note_type`.`title` AS `title`,
    `note_goal`.`title` AS `goal`
FROM `note`
    LEFT JOIN `contact` ON `note`.`id_contact`=`contact`.`id`
    LEFT JOIN `contact_activity_field` ON `contact_activity_field`.`id_contact`=`contact`.`id`
    LEFT JOIN `activity` ON `contact_activity_field`.`id_activity`=`activity`.`id`
    LEFT JOIN `note_type` ON `note`.`title`=`note_type`.`id`
    LEFT JOIN `note_goal` ON `note`.`goal`=`note_goal`.`id`
WHERE
    ( `note_type`.`title` LIKE '%$q%' OR
    `firstname_eng` LIKE '%$q%' OR
    `lastname_eng` LIKE '%$q%' OR
    `firstname_per` LIKE '%$q%' OR
    `lastname_per` LIKE '%$q%' OR
    `company_name` LIKE '%$q%' OR
    `company_name_per` LIKE '%$q%' OR
    `description` LIKE '%$q%' OR
    `note_goal`.`title` LIKE '%$q%' ) AND some other condition
    GROUP BY `note`.`id`
    ORDER BY `note`.`id` DESC
    LIMIT $start_from, 10

编辑 2

笔记表

CREATE TABLE `note` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `id_user` int(10) NOT NULL,
 `id_contact` int(10) NOT NULL,
 `title` int(10) DEFAULT NULL,
 `goal` int(10) DEFAULT NULL,
 `register_date` date DEFAULT NULL,
 `description` text COLLATE utf8_unicode_ci,
 `attached_file` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `meeting_place` text COLLATE utf8_unicode_ci,
 `start_time` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `finish_time` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3297 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

联系表

CREATE TABLE `contact` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_user` int(10) NOT NULL DEFAULT '0',
 `id_user_registered` int(10) DEFAULT NULL,
 `code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `contact_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT 'legal',
 `firstname_eng` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lastname_eng` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `firstname_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lastname_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `gender` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `id_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `national_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `birth_date` date DEFAULT NULL,
 `company_name` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `company_name_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `recommender_eng` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `recommender_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `company_type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `registration_type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `registration_date` date DEFAULT '1900-01-01',
 `registration_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `national_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `economic_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `website` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `postal_code` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_eng` text COLLATE utf8_unicode_ci,
 `address_per` text COLLATE utf8_unicode_ci,
 `phone_number` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `email` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `fax` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `activity_comment` text COLLATE utf8_unicode_ci,
 `level` varchar(50) COLLATE utf8_unicode_ci DEFAULT 'Basic',
 `guild` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `verify_comment` text COLLATE utf8_unicode_ci,
 `status` int(1) NOT NULL DEFAULT '0',
 `comment` text COLLATE utf8_unicode_ci,
 `submitted` int(1) NOT NULL DEFAULT '0',
 `assign_date` datetime DEFAULT NULL,
 `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57357 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

而其他表只包含一个简单的id和title记录。

这是结果EXPLAIN query： 在此处输入图像描述

这些结果在我的本地数据库中。

标签： phpmysql

解决方案

  • LIKE with a leading wild card is inefficient -- and will scan the entire table, hence the "slower as it gets bigger". Switch to FULLTEXT.

  • Pagination via OFFSET is inefficient because it must scan all the rows befoer the few desired. See http://mysql.rjweb.org/doc.php/pagination

  • Both of these have been discussed on stackoverflow repeatedly. Search for other discussions.

推荐阅读