INFORMATION_SCHEMA.COLUMNS表优化

🌞 建议反馈 产品需求
1

需求反馈
请清晰准确地描述问题场景、需求行为及背景信息，更有利于产品同学及时跟进需求
【需求涉及的问题场景】
从v6.5升级到v7.5.1版本后发现INFORMATION_SCHEMA.COLUMNS表查询性能急剧下降，导致业务影响，使用方式：通过jdbc获取表结构信息
DatabaseMetaData metaData = conn.getMetaData();
tableFieldResultSet = metaData.getColumns(catalog, schema, tableName, “%”);

image
image941×626 49.6 KB

【期望的需求行为】
针对INFORMATION_SCHEMA.COLUMNS查询做优化，使用方式为
DatabaseMetaData metaData = conn.getMetaData();
tableFieldResultSet = metaData.getColumns(catalog, schema, tableName, “%”);
【需求可替代方案】

【背景信息】
如哪些用户将从中获益，以及一些使用场景，任何API设计，模型或者图标都会更有帮助。

2

点击发一下具体的 SQL 吧。感觉可能做了转换才慢的。

3 
SELECT
TABLE_SCHEMA,
NULL,
TABLE_NAME,
COLUMN_NAME,
CASE
WHEN UPPER( DATA_TYPE )= 'DECIMAL' THEN
	3 
	WHEN UPPER( DATA_TYPE )= 'DECIMAL UNSIGNED' THEN
	3 
	WHEN UPPER( DATA_TYPE )= 'TINYINT' THEN
CASE
		
		WHEN LOCATE( '(1)', COLUMN_TYPE ) != 0 THEN
		- 7 ELSE - 6 
	END 
		WHEN UPPER( DATA_TYPE )= 'TINYINT UNSIGNED' THEN
	CASE
			
			WHEN LOCATE( '(1)', COLUMN_TYPE ) != 0 THEN
			- 7 ELSE - 6 
		END 
			WHEN UPPER( DATA_TYPE )= 'BOOLEAN' THEN
			16 
			WHEN UPPER( DATA_TYPE )= 'SMALLINT' THEN
			5 
			WHEN UPPER( DATA_TYPE )= 'SMALLINT UNSIGNED' THEN
			5 
			WHEN UPPER( DATA_TYPE )= 'INT' THEN
			4 
			WHEN UPPER( DATA_TYPE )= 'INT UNSIGNED' THEN
			4 
			WHEN UPPER( DATA_TYPE )= 'FLOAT' THEN
			7 
			WHEN UPPER( DATA_TYPE )= 'FLOAT UNSIGNED' THEN
			7 
			WHEN UPPER( DATA_TYPE )= 'DOUBLE' THEN
			8 
			WHEN UPPER( DATA_TYPE )= 'DOUBLE UNSIGNED' THEN
			8 
			WHEN UPPER( DATA_TYPE )= 'NULL' THEN
			0 
			WHEN UPPER( DATA_TYPE )= 'TIMESTAMP' THEN
			93 
			WHEN UPPER( DATA_TYPE )= 'BIGINT' THEN
			- 5 
			WHEN UPPER( DATA_TYPE )= 'BIGINT UNSIGNED' THEN
			- 5 
			WHEN UPPER( DATA_TYPE )= 'MEDIUMINT' THEN
			4 
			WHEN UPPER( DATA_TYPE )= 'MEDIUMINT UNSIGNED' THEN
			4 
			WHEN UPPER( DATA_TYPE )= 'DATE' THEN
			91 
			WHEN UPPER( DATA_TYPE )= 'TIME' THEN
			92 
			WHEN UPPER( DATA_TYPE )= 'DATETIME' THEN
			93 
			WHEN UPPER( DATA_TYPE )= 'YEAR' THEN
			91 
			WHEN UPPER( DATA_TYPE )= 'VARCHAR' THEN
			12 
			WHEN UPPER( DATA_TYPE )= 'VARBINARY' THEN
			- 3 
			WHEN UPPER( DATA_TYPE )= 'BIT' THEN
			- 7 
			WHEN UPPER( DATA_TYPE )= 'JSON' THEN
			- 1 
			WHEN UPPER( DATA_TYPE )= 'ENUM' THEN
			1 
			WHEN UPPER( DATA_TYPE )= 'SET' THEN
			1 
			WHEN UPPER( DATA_TYPE )= 'TINYBLOB' THEN
			- 3 
			WHEN UPPER( DATA_TYPE )= 'TINYTEXT' THEN
			12 
			WHEN UPPER( DATA_TYPE )= 'MEDIUMBLOB' THEN
			- 4 
			WHEN UPPER( DATA_TYPE )= 'MEDIUMTEXT' THEN
			- 1 
			WHEN UPPER( DATA_TYPE )= 'LONGBLOB' THEN
			- 4 
			WHEN UPPER( DATA_TYPE )= 'LONGTEXT' THEN
			- 1 
			WHEN UPPER( DATA_TYPE )= 'BLOB' THEN
			- 4 
			WHEN UPPER( DATA_TYPE )= 'TEXT' THEN
			- 1 
			WHEN UPPER( DATA_TYPE )= 'CHAR' THEN
			1 
			WHEN UPPER( DATA_TYPE )= 'BINARY' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'GEOMETRY' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'UNKNOWN' THEN
			1111 
			WHEN UPPER( DATA_TYPE )= 'POINT' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'LINESTRING' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'POLYGON' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'MULTIPOINT' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'MULTILINESTRING' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'MULTIPOLYGON' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'GEOMETRYCOLLECTION' THEN
			- 2 
			WHEN UPPER( DATA_TYPE )= 'GEOMCOLLECTION' THEN
			- 2 ELSE 1111 
		END AS DATA_TYPE,
		UPPER(
		CASE
				
				WHEN LOCATE(
					'UNSIGNED',
				UPPER( COLUMN_TYPE )) != 0 
				AND LOCATE(
					'UNSIGNED',
				UPPER( DATA_TYPE )) = 0 
				AND LOCATE(
					'SET',
				UPPER( DATA_TYPE )) <> 1 
				AND LOCATE(
					'ENUM',
					UPPER( DATA_TYPE )) <> 1 THEN
					CONCAT( DATA_TYPE, ' UNSIGNED' ) 
					WHEN UPPER( DATA_TYPE )= 'TINYINT' THEN
				CASE
						
						WHEN LOCATE( '(1)', COLUMN_TYPE ) != 0 THEN
						'BIT' 
						WHEN LOCATE(
							'UNSIGNED',
						UPPER( COLUMN_TYPE )) != 0 
						AND LOCATE(
							'UNSIGNED',
							UPPER( DATA_TYPE )) = 0 THEN
							'TINYINT UNSIGNED' ELSE DATA_TYPE 
						END 
							WHEN UPPER( DATA_TYPE )= 'POINT' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'LINESTRING' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'POLYGON' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'MULTIPOINT' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'MULTILINESTRING' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'MULTIPOLYGON' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'GEOMETRYCOLLECTION' THEN
							'GEOMETRY' 
							WHEN UPPER( DATA_TYPE )= 'GEOMCOLLECTION' THEN
							'GEOMETRY' ELSE UPPER( DATA_TYPE ) 
						END 
						) AS TYPE_NAME,
						UPPER(
						CASE
								
								WHEN UPPER( DATA_TYPE )= 'DATE' THEN
								10 
								WHEN UPPER( DATA_TYPE )= 'TIME' THEN
								8+ ( CASE WHEN DATETIME_PRECISION > 0 THEN DATETIME_PRECISION + 1 ELSE DATETIME_PRECISION END ) 
								WHEN UPPER( DATA_TYPE )= 'DATETIME' 
								OR UPPER( DATA_TYPE )= 'TIMESTAMP' THEN
									19+ ( CASE WHEN DATETIME_PRECISION > 0 THEN DATETIME_PRECISION + 1 ELSE DATETIME_PRECISION END ) 
									WHEN UPPER( DATA_TYPE )= 'YEAR' THEN
									4 
									WHEN UPPER( DATA_TYPE )= 'TINYINT' 
									AND LOCATE( '(1)', COLUMN_TYPE ) != 0 THEN
										1 
										WHEN UPPER( DATA_TYPE )= 'MEDIUMINT' 
										AND LOCATE(
											'UNSIGNED',
											UPPER( COLUMN_TYPE )) != 0 THEN
											8 
											WHEN UPPER( DATA_TYPE )= 'JSON' THEN
											1073741824 
											WHEN UPPER( DATA_TYPE )= 'GEOMETRY' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'POINT' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'LINESTRING' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'POLYGON' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'MULTIPOINT' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'MULTILINESTRING' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'MULTIPOLYGON' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'GEOMETRYCOLLECTION' THEN
											65535 
											WHEN UPPER( DATA_TYPE )= 'GEOMCOLLECTION' THEN
											65535 
											WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN
											NUMERIC_PRECISION 
											WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN
											2147483647 ELSE CHARACTER_MAXIMUM_LENGTH 
										END 
										) AS COLUMN_SIZE,
										65535 AS BUFFER_LENGTH,
										UPPER(
										CASE
												
												WHEN UPPER( DATA_TYPE )= 'DECIMAL' THEN
												NUMERIC_SCALE 
												WHEN UPPER( DATA_TYPE )= 'FLOAT' 
												OR UPPER( DATA_TYPE )= 'DOUBLE' THEN
												CASE
														
														WHEN NUMERIC_SCALE IS NULL THEN
														0 ELSE NUMERIC_SCALE 
												END ELSE NULL 
												END 
												) AS DECIMAL_DIGITS,
												10 AS NUM_PREC_RADIX,
											CASE
													
													WHEN IS_NULLABLE = 'NO' THEN
													0 ELSE
												CASE
														
														WHEN IS_NULLABLE = 'YES' THEN
														1 ELSE 2 
													END 
													END AS NULLABLE,
													COLUMN_COMMENT AS REMARKS,
													COLUMN_DEFAULT AS COLUMN_DEF,
													0 AS SQL_DATA_TYPE,
													0 AS SQL_DATETIME_SUB,
												CASE
														
														WHEN CHARACTER_OCTET_LENGTH > 2147483647 THEN
														2147483647 ELSE CHARACTER_OCTET_LENGTH 
													END AS CHAR_OCTET_LENGTH,
													ORDINAL_POSITION,
													IS_NULLABLE,
													NULL AS SCOPE_CATALOG,
													NULL AS SCOPE_SCHEMA,
													NULL AS SCOPE_TABLE,
													NULL AS SOURCE_DATA_TYPE,
												IF
													( EXTRA LIKE '%auto_increment%', 'YES', 'NO' ) AS IS_AUTOINCREMENT,
												IF
													( EXTRA LIKE '%GENERATED%', 'YES', 'NO' ) AS IS_GENERATEDCOLUMN 
												FROM
													INFORMATION_SCHEMA.COLUMNS 
												WHERE
													TABLE_SCHEMA = 'JHDL' 
													AND TABLE_NAME LIKE 'JHDL_29_SQLUSER.DHC_TARSUBCATE' 
													AND COLUMN_NAME LIKE '%' 
												ORDER BY
													TABLE_SCHEMA,
												TABLE_NAME,
ORDINAL_POSITION

以上就是通过jdbc获取表结构实际查询的sql

4

并且查询多的话，响应会更慢