count(*) 300W INNODB 数据表,根据型号 like '%?%' 还有优化可能吗

MySql 码拜 8年前 (2016-03-11) 1150次浏览
表结构
CREATE TABLE `b_common_product` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT “id”,
`version` int(11) DEFAULT NULL COMMENT “版本”,
`createByUserCode` varchar(50) DEFAULT NULL COMMENT “创建人编码”,
`createByUserName` varchar(50) DEFAULT NULL COMMENT “创建人名称”,
`createTime` datetime DEFAULT NULL COMMENT “创建时间”,
`lastModifyByUserCode` varchar(50) DEFAULT NULL COMMENT “最后修改人编码”,
`lastModifyByUserName` varchar(50) DEFAULT NULL COMMENT “最后修改人名称”,
`lastModifyTime` datetime DEFAULT NULL COMMENT “最后修改时间”,
`status` varchar(10) DEFAULT NULL COMMENT “状态”,
`datasheetsUrl` varchar(500) DEFAULT NULL COMMENT “产品PDF.Iczoom网址 或 厂家网址”,
`brandUrl` varchar(255) DEFAULT NULL COMMENT “品牌网址”,
`imageUrl` varchar(255) DEFAULT NULL COMMENT “图片网址”,
`productPackaging` varchar(200) DEFAULT NULL COMMENT “包装”,
`productApplication` varchar(200) DEFAULT NULL COMMENT “应用”,
`series` varchar(200) DEFAULT NULL COMMENT “系列”,
`pn` varchar(200) DEFAULT NULL COMMENT “型号”,
`brand` varchar(200) DEFAULT NULL COMMENT “品牌”,
`cat_id` varchar(18) DEFAULT NULL COMMENT “类别Id”,
`encapsulate` varchar(200) DEFAULT NULL COMMENT “封装”,
`description` varchar(200) DEFAULT NULL COMMENT “产品描述”,
`sourcetype` varchar(20) DEFAULT NULL COMMENT “产品来源的类型”,
`workingTemperature` varchar(200) DEFAULT NULL COMMENT “工作温度”,
`voltageSource` varchar(200) DEFAULT NULL COMMENT “电压电源”,
`encapsulateShell` varchar(200) DEFAULT NULL COMMENT “封装外壳”,
`refClassName` varchar(200) DEFAULT NULL COMMENT “数据来源Class全名”,
`refId` varchar(20) DEFAULT NULL COMMENT “数据来源Id”,
`manufacturer` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_cat_id` (`cat_id`) USING BTREE,
FULLTEXT KEY `idx_pn` (`pn`)
) ENGINE=InnoDB AUTO_INCREMENT=3557736 DEFAULT CHARSET=utf8;

SHOW INDEX FROM b_common_product

b_common_product 0 PRIMARY 1 id A 2896290 BTREE
b_common_product 1 idx_cat_id 1 cat_id A 304 YES BTREE
b_common_product 1 idx_pn 1 pn 2896290 YES FULLTEXT

EXPLAIN SELECT count(*) FROM b_common_product a WHERE INSTR(a.pn,"TLP109")

1 SIMPLE a ALL 2896290 Using where

SELECT count(*) FROM b_common_product a WHERE INSTR(a.pn,"TLP109")

[SQL]SELECT count(*) FROM b_common_product a WHERE INSTR(a.pn,”TLP109″)
受影响的行: 0
时间: 12.632s

解决方案

20

pn可以建立全文索引

30

LIKE %X% 这种无法直接优化,只能通过  全文搜索索引
详见MYSQL官网免费参考手册的。

引用

11.8. Full-Text Search Functions
MATCH (col1,col2,…) AGAINST (expr [search_modifier])
search_modifier:
{
IN BOOLEAN MODE
| IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| WITH QUERY EXPANSION
}
MySQL has support for full-text indexing and searching:
A full-text index in MySQL is an index of type FULLTEXT.
Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.
For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.
Full-text searching is performed using MATCH() … AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name. There are three types of full-text searches:
A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “some” or “then” are stopwords and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 11.8.2, “Boolean Full-Text Searches”.
A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given.
A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 11.8.3, “Full-Text Searches with Query Expansion”.
Constraints on full-text searching are listed in Section 11.8.5, “Full-Text Restrictions”.
The myisam_ftdump utility can be used to dump the contents of a full-text index. This may be helpful for debugging full-text queries. See Section 4.6.2, “myisam_ftdump — Display Full-Text Index information”.
11.8.1. Natural Language Full-Text Searches
By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.
mysql> CREATE TABLE articles (
->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
->   title VARCHAR(200),
->   body TEXT,
->   FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles (title,body) VALUES
-> (“MySQL Tutorial”,”DBMS stands for DataBase …”),
-> (“How To Use MySQL Well”,”After you went through a …”),
-> (“Optimizing MySQL”,”In this tutorial we will show …”),
-> (“1001 MySQL Tricks”,”1. Never run mysqld as root. 2. …”),
-> (“MySQL vs. YourSQL”,”In the following database comparison …”),
-> (“MySQL Security”,”When configured properly, MySQL …”);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body)
-> AGAINST (“database” IN NATURAL LANGUAGE MODE);
+–+–+–+
| id | title             | body                                     |
+–+–+–+
|  5 | MySQL vs. YourSQL | In the following database comparison … |
|  1 | MySQL Tutorial    | DBMS stands for DataBase …             |
+–+–+–+
2 rows in set (0.00 sec)
By default, the search is performed in case-insensitive fashion. However, you can perform a case-sensitive full-text search by using a binary collation for the indexed columns. For example, a column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.
When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明count(*) 300W INNODB 数据表,根据型号 like '%?%' 还有优化可能吗
喜欢 (0)
[1034331897@qq.com]
分享 (0)