我在下面有一个连接 4 个表的查询。我添加了索引，使用索引后解释输出看起来不错(见下文)。

我可以进一步优化查询吗？

modelXml 在某些记录上相当大。对于一个大项目，我返回 22 条记录，每条记录大约有 2.5 - 3MB 的 modelXml 数据，查询时间很长(返回的数据总数为 69MB)。 我怀疑这是问题所在，但不确定如何处理。 我正在阅读调整内部 mysql 变量，例如 key_buffer_size 和 table_cache。这有什么帮助吗？

key_buffer_size 当前设置为 8384512 (~8MB) 和 table_cache 为 64 我应该把它增加到什么程度？ 我还应该关注哪些其他变量来设法加快此类大数据的返回速度？

欢迎任何其他建议。我是 mysql 的新手，但真的想变得更好。

SELECT `m`.`modelId`, `m`.`modelTypeId`, `m`.`modelXml`, `m`.`xmlSize`, `m`.`createdById`, `m`.`creationDate`, `m`.`modifiedDate`, `u`.`firstName`, `u`.`lastName` FROM `models_1` AS `m` 
INNER JOIN `modelFolderAssociations_1` AS `mfa` ON m.modelId = mfa.modelIOId 
INNER JOIN `modelFolders_1` AS `mf` ON mfa.folderId = mf.folderId 
INNER JOIN `users_1` AS `u` ON m.createdById = u.userId 
WHERE (m.projectId = 2) AND (mfa.folderId = 5) AND (mfa.modelIOType = 2) AND (m.modelTypeId = 2)

CREATE TABLE `models` (
 `modelId` int(11) NOT NULL auto_increment,
 `customerId` int(11) NOT NULL,
 `groupId` int(11) NOT NULL,
 `projectId` int(11) NOT NULL,
 `createdById` int(11) NOT NULL,
 `modelTypeId` int(11) NOT NULL,
 `modelXml` longtext,
 `modelSpecXml` longtext NOT NULL,
 `xmlSize` bigint(20) NOT NULL default '0',
 `creationDate` datetime NOT NULL,
 `modifiedDate` datetime NOT NULL,
 PRIMARY KEY  (`modelId`,`customerId`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8 


CREATE TABLE `modelFolders` (
     `folderId` int(11) NOT NULL auto_increment,
     `customerId` int(11) NOT NULL,
     `groupId` int(11) NOT NULL,
     `projectId` int(11) NOT NULL,
     `parentId` int(11) NOT NULL,
     `folderName` varchar(64) NOT NULL,
     `folderType` int(11) NOT NULL,
     `editable` tinyint(1) NOT NULL default '1',
     `nextDefaultNameNumber` int(11) NOT NULL default '1',
     `creationDate` datetime NOT NULL,
     `modifiedDate` datetime NOT NULL,
     PRIMARY KEY  (`folderId`,`customerId`),
     KEY `parentId` (`parentId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

CREATE TABLE `modelFolderAssociations` (
 `associationId` int(11) NOT NULL auto_increment,
 `customerId` int(11) NOT NULL,
 `folderId` int(11) NOT NULL,
 `projectId` int(11) NOT NULL,
 `modelIOId` int(11) NOT NULL,
 `modelIOType` tinyint(1) NOT NULL default '1',
 `creationDate` datetime NOT NULL,
 `modifiedDate` datetime NOT NULL,
 PRIMARY KEY  (`associationId`,`customerId`),
 KEY `folderId` (`folderId`,`modelIOType`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8

CREATE TABLE `users` (
 `userId` int(11) NOT NULL auto_increment,
 `customerId` int(11) NOT NULL,
 `userName` varchar(50) NOT NULL,
 `password` varchar(256) NOT NULL,
 `firstName` varchar(50) default NULL,
 `lastName` varchar(50) default NULL,
 `creationDate` datetime NOT NULL,
 `modifiedDate` datetime NOT NULL,
 PRIMARY KEY  (`userId`,`customerId`),
 UNIQUE KEY `userName` (`userName`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8

解释输出

+----+-------------+-------------------------+--------+---------------+----------+---------+---------------------------------------------------+------+-------------+
| id | select_type | table                   | type   | possible_keys | key      | key_len | ref                                               | rows | Extra       |
+----+-------------+-------------------------+--------+---------------+----------+---------+---------------------------------------------------+------+-------------+
|  1 | SIMPLE      | modelFolders            | const  | PRIMARY       | PRIMARY  | 8       | const,const                                       |    1 | Using index |
|  1 | SIMPLE      | modelFolderAssociations | ref    | folderId      | folderId | 5       | const,const                                       |   22 | Using where |
|  1 | SIMPLE      | models                  | eq_ref | PRIMARY       | PRIMARY  | 8       | xa_system.modelFolderAssociations.modelIOId,const |    1 | Using where |
|  1 | SIMPLE      | users                   | eq_ref | PRIMARY       | PRIMARY  | 8       | xa_system.models.createdById,const                |    1 |             |
+----+-------------+-------------------------+--------+---------------+----------+---------+---------------------------------------------------+------+-------------+

最佳答案

无论索引的结构如何，拥有较大的文本列(如我认为用于存储 XML 的列)都会影响性能。

在这些情况下，将文本列移动到一个单独的表中可能会更好，该表由您已经存储的字符串长度和 CRC32 索引。

CREATE TABLE MODEL_XML (
 xmlId INT(11) unsigned NOT NULL auto_increment,
 xmlSize BIGINT(20) NOT NULL default '0',
 crc32 INT(11) unsigned NOT NULL,
 xmlData LONGTEXT,
 PRIMARY KEY (xmlId),
 UNIQUE KEY (xmlSize, crc32)
)

然后表格中包含重要索引的列的宽度变为常量。

例如

modelXmlId INT(11) unsigned NOT NULL
specXmlId INT(11) unsigned NOT NULL

它还有一个好处是，对于冗余文本(空字符串等)来说，空间效率更高，因为它们将共享一个 xmlId，因此在数据库中共享一行。

关于php - 进一步优化这个查询？ - 索引但仍然很慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/5507394/