MySQL：：JSON列的添加使SELECT查询非性能(当需要临时表时)

发布于11月27日

我在MySQL中的一个表中添加了一个JSON列，该表为每行保存少量数据(大约7Kb).该表大约有135K行.在此添加之前，我在该表上的所有查询执行时间都不到1秒.然而，现在当explain个注释需要一个临时表来获取结果时，查询大约需要7秒.有人知道为什么JSON专栏会导致这样的性能问题吗？如果我将列更改为TEXT，则相同的查询运行时间不到1秒.JSON专栏的大小自然会增加.我应该把这一列拉到另一个1:1的表中吗？如果我们最终开始按JSON列中的值进行查询，这会是一个问题吗？

值得注意的是:

我没有查询JSON列，也没有在结果中返回它.
用户可以通过各种方式搜索这个表，因此 for each 可能的场景编写索引来消除临时表的使用是不切实际的.
大多数查询都是通过应用程序Hibernate ORM代码动态创建的

例如，使用JSON字段完成下面的查询需要7秒，但作为文本字段不到1秒.

SELECT 
    xaction.XactionId, xaction.PropStreet, xaction.PropNum, xaction.PropStreetUnit
FROM
    Xaction xaction
        INNER JOIN
    Team team ON xaction.TeamId = team.TeamId
        LEFT OUTER JOIN
    AppUser appuser_primaryAgent ON xaction.AppUserIdPrimaryAgent = appuser_primaryAgent.AppUserId
        LEFT OUTER JOIN
    AppUser appuser_coAgent ON xaction.AppUserIdCoAgent = appuser_coAgent.AppUserId
        LEFT OUTER JOIN
    AppUser appuser_assistant1 ON xaction.AppUserIdAssistant1 = appuser_assistant1.AppUserId
        LEFT OUTER JOIN
    AppUser appuser_assistant2 ON xaction.AppUserIdAssistant2 = appuser_assistant2.AppUserId
WHERE
    team.TeamId = 1
        AND (
            appuser_primaryAgent.AppUserId = 1 
            or appuser_coAgent.AppUserId = 1 
            or appuser_assistant1.AppUserId = 1 
            or appuser_assistant2.AppUserId = 1
        )
GROUP BY xaction.XactionId
ORDER BY PropStreet , PropNum , PropStreetUnit

解释一下

+----+-------------+----------------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table                | partitions | type   | possible_keys                                                                                                                                                                                            | key     | key_len | ref                                       | rows  | filtered | Extra                                        |
+----+-------------+----------------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | team                 | NULL       | const  | PRIMARY                                                                                                                                                                                                  | PRIMARY | 8       | const                                     |     1 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | xaction              | NULL       | index  | PRIMARY,IDX_PropNum,IDX_PropStreet,AppUserIdPrimaryAgent,AppUserIdCoAgent,XactionPropTypeId,XactionSourceId,XactionStatusId,IDX_PropNum_PropStreet_PropStreetNum,AppUserIdAssistant1,AppUserIdAssistant2 | PRIMARY | 8       | NULL                                      | 49515 |    10.00 | Using where                                  |
|  1 | SIMPLE      | appuser_primaryAgent | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdPrimaryAgent |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | appuser_coAgent      | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdCoAgent      |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | appuser_assistant1   | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdAssistant1   |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | appuser_assistant2   | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdAssistant2   |     1 |   100.00 | Using where; Using index                     |
+----+-------------+----------------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

作为参考，表定义如下:

CREATE TABLE `xaction` (
  `XactionId` bigint NOT NULL AUTO_INCREMENT,
  `PropNum` varchar(20) NOT NULL,
  `PropStreetDir` varchar(10) DEFAULT NULL,
  `PropStreet` varchar(100) NOT NULL,
  `PropStreetUnit` varchar(20) DEFAULT NULL,
  `City` varchar(100) DEFAULT NULL,
  `State` varchar(4) DEFAULT NULL,
  `Zip` varchar(10) DEFAULT NULL,
  `County` varchar(100) DEFAULT NULL,
  `MlsId` varchar(50) DEFAULT NULL,
  `TaxId` varchar(50) DEFAULT NULL,
  `GfNum` varchar(50) DEFAULT NULL,
  `ListPrice` decimal(13,4) DEFAULT NULL,
  `ListPriceOriginal` decimal(13,4) DEFAULT NULL,
  `SqFt` int DEFAULT NULL,
  `SqFtSource` varchar(50) DEFAULT NULL,
  `Beds` smallint DEFAULT NULL,
  `Baths` decimal(4,2) DEFAULT NULL,
  `YearBuilt` smallint DEFAULT NULL,
  `LotSize` varchar(100) DEFAULT NULL,
  `Schools` varchar(255) DEFAULT NULL,
  `Subdivision` varchar(255) DEFAULT NULL,
  `LockBoxId` varchar(20) DEFAULT NULL,
  `LockBox` varchar(255) DEFAULT NULL,
  `SecurityCode` varchar(255) DEFAULT NULL,
  `HoaFee` varchar(100) DEFAULT NULL,
  `HoaFrequency` varchar(100) DEFAULT NULL,
  `Occupancy` varchar(50) DEFAULT NULL,
  `Remarks` mediumtext,
  `Instructions` mediumtext,
  `ListOtherInfo` mediumtext,
  `ContractPrice` decimal(13,4) DEFAULT NULL,
  `OtherParty` varchar(500) DEFAULT NULL,
  `EarnestMoney` varchar(255) DEFAULT NULL,
  `DueDiligenceFee` varchar(255) DEFAULT NULL,
  `Concessions` varchar(255) DEFAULT NULL,
  `Financing` varchar(50) DEFAULT NULL,
  `SpecialProvisions` mediumtext,
  `ContractOtherInfo` mediumtext,
  `Possession` mediumtext,
  `EffectiveDate` date DEFAULT NULL,
  `ClosingDate` date DEFAULT NULL,
  `ListDate` date DEFAULT NULL,
  `ExpireDate` date DEFAULT NULL,
  `ClosedDate` date DEFAULT NULL,
  `XactionPropTypeId` bigint DEFAULT NULL,
  `XactionSourceId` bigint DEFAULT NULL,
  `XactionSide` varchar(10) NOT NULL,
  `XactionStatusId` bigint NOT NULL,
  `PercentageCommission` decimal(6,5) DEFAULT NULL,
  `CommissionNote` varchar(255) DEFAULT NULL,
  `SplitTeamLead` decimal(6,5) DEFAULT NULL,
  `SplitPrimaryAgent` decimal(6,5) DEFAULT NULL,
  `SplitCoAgent` decimal(6,5) DEFAULT NULL,
  `SplitAssistant1` decimal(6,5) DEFAULT NULL,
  `SplitAssistant2` decimal(6,5) DEFAULT NULL,
  `PayoutEstimated` decimal(13,4) DEFAULT NULL,
  `PayoutActual` decimal(13,4) DEFAULT NULL,
  `PayoutReferral` decimal(13,4) DEFAULT NULL,
  `PayoutBroker` decimal(13,4) DEFAULT NULL,
  `PayoutTeamLead` decimal(13,4) DEFAULT NULL,
  `PayoutPrimaryAgent` decimal(13,4) DEFAULT NULL,
  `PayoutCoAgent` decimal(13,4) DEFAULT NULL,
  `PayoutAssistant1` decimal(13,4) DEFAULT NULL,
  `PayoutAssistant2` decimal(13,4) DEFAULT NULL,
  `ExpensesBroker` decimal(13,4) DEFAULT NULL,
  `ExpensesTeamLead` decimal(13,4) DEFAULT NULL,
  `GeoLatitude` decimal(9,7) DEFAULT NULL,
  `GeoLongitude` decimal(10,7) DEFAULT NULL,
  `GeoLocatorStatus` varchar(10) DEFAULT NULL,
  `TimeZone` varchar(60) NOT NULL,
  `FieldDataJson` text,
  `CreateDateTime` datetime NOT NULL,
  `EditDateTime` datetime NOT NULL,
  `TeamId` bigint NOT NULL,
  `AppUserIdPrimaryAgent` bigint NOT NULL,
  `AppUserIdCoAgent` bigint DEFAULT NULL,
  `AppUserIdAssistant1` bigint DEFAULT NULL,
  `AppUserIdAssistant2` bigint DEFAULT NULL,
  PRIMARY KEY (`XactionId`),
  KEY `IDX_PropNum` (`PropNum`),
  KEY `IDX_PropStreet` (`PropStreet`),
  KEY `AppUserIdPrimaryAgent` (`AppUserIdPrimaryAgent`),
  KEY `AppUserIdCoAgent` (`AppUserIdCoAgent`),
  KEY `XactionPropTypeId` (`XactionPropTypeId`),
  KEY `XactionSourceId` (`XactionSourceId`),
  KEY `XactionStatusId` (`XactionStatusId`),
  KEY `IDX_PropNum_PropStreet_PropStreetNum` (`PropNum`,`PropStreet`,`PropStreetUnit`),
  KEY `AppUserIdAssistant1` (`AppUserIdAssistant1`),
  KEY `AppUserIdAssistant2` (`AppUserIdAssistant2`),
  CONSTRAINT `FK_Xaction_AppUser_Assistant1` FOREIGN KEY (`AppUserIdAssistant1`) REFERENCES `appuser` (`AppUserId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_AppUser_Assistant2` FOREIGN KEY (`AppUserIdAssistant2`) REFERENCES `appuser` (`AppUserId`),
  CONSTRAINT `FK_Xaction_AppUser_CoAgent` FOREIGN KEY (`AppUserIdCoAgent`) REFERENCES `appuser` (`AppUserId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_AppUser_PrimaryAgent` FOREIGN KEY (`AppUserIdPrimaryAgent`) REFERENCES `appuser` (`AppUserId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_XactionPropTypeLookup` FOREIGN KEY (`XactionPropTypeId`) REFERENCES `xactionproptypelookup` (`XactionPropTypeId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_XactionSourceLookup` FOREIGN KEY (`XactionSourceId`) REFERENCES `xactionsourcelookup` (`XactionSourceId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_XactionStatusLookup` FOREIGN KEY (`XactionStatusId`) REFERENCES `xactionstatuslookup` (`XactionStatusId`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB;

MySQL：：JSON列的添加使SELECT查询非性能(当需要临时表时)

推荐答案

Mysql相关问答推荐

SQL按另一个表中的字段进入顺序对子查询进行排序

MySQL工作台的编码问题

「已解决」MySQL 连接在 vb6 上出现运行时错误 -2147467259 (80004005)，但在 VBA Excel 上工作

优化解析 5000 万行 MySQL 表的请求

基于关系的每个实体有一个真值和多个假值

如何在 mysql 的 group by 子句中 Select 最后创建的记录？

检索按键列值分组的最新日期 (MySql)

谁能帮我优化低性能的mysql查询

从 SQL 中的左连接和内连接中减go 计数

减少mysql中的值但不是负数

MySQL：使用来自查询的信息创建一个新表

Facebook user_id：big_int、int 还是 string？

MySQL：按字段排序，将空单元格放在末尾

PHP MySQLI 防止 SQL 注入

外键可以引用非唯一索引吗？

WHERE 子句中的条件顺序会影响 MySQL 性能吗？

将行插入 MySQL 数据库的最有效方法

应该使用什么列类型将序列化数据存储在 mysql 数据库中？

在 Ubuntu 上安装 mysql gem 的困难

PHP MYSQL - 插入不使用列名但使用自动增量字段