mysql - 加速包含大表的 MySQL

Question

我们在 Amazon AWS 服务器上托管了一个 MySQL 表，有时查询速度非常非常慢。我正在考虑使用更好的索引和列数据类型来重构表。

这是创建表结构:

SHOW CREATE TABLE export_users;

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| exportusers | CREATE TABLE `exportusers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `UAccountID` varchar(50) CHARACTER SET utf8 NOT NULL,
  `ApplyURL` text,
  `CityName` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `ClassList` text,
  `UCompanyID` varchar(20) DEFAULT NULL,
  `UContactCompany` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `UContactEmail` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  `UContactFax` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `UContactName` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `UContactPhone` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CountryName` char(2) CHARACTER SET utf8 NOT NULL,
  `DateCreated` datetime NOT NULL,
  `DateModified` datetime NOT NULL,
  `DateSysCreated` datetime NOT NULL,
  `DateSysModified` datetime NOT NULL,
  `DegreeCode` varchar(200) DEFAULT NULL,
  `DegreeCodeDecoded` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  `DisplayCity` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `DisplayuserID` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `ExperienceCode` varchar(200) DEFAULT NULL,
  `ExperienceCodeDecoded` text,
  `ExternalKey` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `GeoUSZip5` varchar(5) DEFAULT NULL,
  `HostSite` char(2) CHARACTER SET utf8 NOT NULL,
  `IndustryCode` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `IndustryCodeDecoded` text,
  `IsBOFuser` tinyint(1) DEFAULT NULL,
  `IsDiversityuser` tinyint(1) DEFAULT NULL,
  `userID` varchar(20) NOT NULL,
  `userDesc` text,
  `userFunctionCode` text,
  `userFunctionCodeDecoded` text,
  `userReq` text,
  `userSkinDID` varchar(20) DEFAULT NULL,
  `userTitle` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `userType` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  `userTypeDesc` text,
  `userTypeCodeDecoded` text,
  `Latitude` decimal(10,5) DEFAULT NULL,
  `Longitude` decimal(10,5) DEFAULT NULL,
  `Location` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
  `ManagementCode` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `MaximumExp` int(11) DEFAULT NULL,
  `MinimunExp` int(11) DEFAULT NULL,
  `Onet` varchar(10) DEFAULT NULL,
  `OnetTitle` text,
  `BeginDate` datetime NOT NULL,
  `EndDate` datetime NOT NULL,
  `PayBaseH` decimal(10,2) DEFAULT NULL,
  `PayBaseL` decimal(10,2) DEFAULT NULL,
  `PayBonus` decimal(10,2) DEFAULT NULL,
  `PayComm` decimal(10,2) DEFAULT NULL,
  `PayOther` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `PayPer` varchar(8) DEFAULT NULL,
  `PayType` char(3) DEFAULT NULL,
  `PostalCode` varchar(10) DEFAULT NULL,
  `PostingPath` varchar(20) NOT NULL,
  `Relocate` tinyint(1) DEFAULT NULL,
  `RelocateOptions` varchar(5) DEFAULT NULL,
  `ScreenerID` varchar(20) DEFAULT NULL,
  `SiteID` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  `SliceList` text,
  `StateName` char(30) CHARACTER SET utf8 DEFAULT NULL,
  `Status` varchar(50) NOT NULL,
  `TextPay` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `TotalPay` decimal(10,2) DEFAULT NULL,
  `TownName` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `TravelCode` varchar(200) DEFAULT NULL,
  `TravelCodeDecoded` text,
  `UpgradeList` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_jobs_on_UAccountID` (`UAccountID`),
  KEY `index_jobs_on_CountryName` (`CountryName`),
  KEY `index_jobs_on_DateCreated` (`DateCreated`),
  KEY `index_jobs_on_DateModified` (`DateModified`),
  KEY `index_jobs_on_DateSysCreated` (`DateSysCreated`),
  KEY `index_jobs_on_DateSysModified` (`DateSysModified`),
  KEY `index_jobs_on_HostSite` (`HostSite`),
  KEY `index_jobs_on_userID` (`userID`),
  KEY `index_jobs_on_BeginDate` (`BeginDate`),
  KEY `index_jobs_on_EndDate` (`EndDate`),
  KEY `index_jobs_on_PostingPath` (`PostingPath`),
  KEY `index_jobs_on_Status` (`Status`)
) ENGINE=InnoDB AUTO_INCREMENT=7907436 DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.27 sec)

Answer 1

考虑 Normalization你的架构。将一个非常大的实体分解成更小的相关实体将对您的查询速度产生最大的影响。

Normalization involves decomposing a table into less redundant (and smaller) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.