在关系型数据库中，使用like模糊查询，如果是 'xxx%'头匹配方式，字段索引不会失效，性能能接受；但如果是 '%xxx%'全文匹配方式，索引会失效，在数据量大的情况下，通过此种方式查询的效率极低。此时可通过全文索引（Full-Text Search）来进行。
Mysql在5.6之后提供全文索引，本文主要记录测试全文索引效率。
Mysql全文索引基本情况
1、Mysql在5.6之后基于MyISAM引擎提供全文索引，在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引
2、Mysql5.7开始 innoDb开始支持全文索引，且从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。
测试全文索引效率
测试前说明
1、为了对比，本次测试用了10万、100万、1000万数量级做对比测试。
2、测试数据库在服务器（ctrix虚拟机2vcpu，8G内存），本次使用版本5.7.13；客户端在PC上用navicat工具；网络千M网。
3、测试是基于我使用的硬件情况，因此反映性能的具体指标供参考。
4、预先准备好数据，我是采用了《简爱》全本中英文，采用随机截断不同长度文本方式分别从里面收取10万、100万、1000万数据。
5、my.cnf配置（配置完了别忘了重启mysql）如下：
[mysqld]
#英文分词长度默认为4，<4通常不建索引；英文单词长度<4的通常查询不到
ft_min_word_len = 4
#中文分词长度位2，每个字都可以查出
ngram_token_size=2
测试步骤
建表（见如下语句）
CREATE TABLE ft_bigdata10 (
id int(11) NOT NULL AUTO_INCREMENT,
tenant_id char(6) NOT NULL ,
code_list Text DEFAULT NULL, -- 非英文单词的字符串，用','隔开，每个串为单词+','隔开，每个单词16个字符随机组成
en_cnword TEXT DEFAULT NULL, -- 中英文文段落（从简爱 中英文对照版随机抽取）
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
CREATE TABLE ft_bigdata100 (
id int(11) NOT NULL AUTO_INCREMENT,
tenant_id char(6) NOT NULL ,
code_list Text DEFAULT NULL, -- 非英文单词的字符串，用','隔开，每个串为单词+','隔开，每个单词16个字符随机组成
en_cnword TEXT DEFAULT NULL, -- 中英文文段落（从简爱 中英文对照版随机抽取）
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
CREATE TABLE ft_bigdata1000 (
id int(11) NOT NULL AUTO_INCREMENT,
tenant_id char(6) NOT NULL ,
code_list Text DEFAULT NULL, -- 非英文单词的字符串，用','隔开，每个串为单词+','隔开，每个单词16个字符随机组成
en_cnword TEXT DEFAULT NULL, -- 中英文文段落（从简爱 中英文对照版随机抽取）
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
导入数据
由于数据量大，将数据加载到没有FULLTEXT索引的表中然后创建索引要比将数据加载到具有现有FULLTEXT索引的表中快得多。本次均是在数据导入后再建索引。
需要自己写程序导入。本人用python完成，见如下代码。
主程序：
# -*- coding:utf-8 -*-
import random
import string
from dbsql import DbSql
cn_en_src=[]
def getTenantId():
'随机生成6位数字的字符串'
return "%06d" % random.randint(100000, 119999)
def getCodeStr():
# 从a-zA-Z0-9生成指定数量的随机字符：
# ran_str = ''.join(random.sample(string.ascii_letters + string.digits, 16))
ran_str=''
for i in range(1,random.randint(1, 50)):
ran_str+=','+''.join(random.sample(string.ascii_letters + string.digits, 16))
# print(ran_str)
return ran_str
def getEnCnStr():
global cn_en_src
if len(cn_en_src)<1:
#读取英文或中文源
with open('jianai.txt', 'r', encoding='utf-8') as f:
flines=f.readlines()
for line in flines: #去掉短的行
line=str(line)
if(len(line)>=5):
cn_en_src.append(line.replace('\'','')) #去掉 '
# print(len(cn_en_src))
currLine=random.randint(0,len(cn_en_src)-1)
colBegin=random.randint(1,int(len(cn_en_src[currLine])/2)-1)
colEnd=random.randint(1,len(cn_en_src[currLine])-colBegin)
enCnStr=str(cn_en_src[currLine][colBegin:(colBegin+colEnd)])
return enCnStr
def makeRow(tableName,rows):
rowData=[]
dbSql=DbSql()
insertHead='insert into '+tableName+' (tenant_id,code_list,en_cnword) values'
for row in range(1,rows+1):
print('生成行：'+str(row))
rd="('"+getTenantId()+"','"+getCodeStr()+"','"+getEnCnStr()+"')"
rowData.append(rd)
if(len(rowData)>=10000):
dbSql.batchInsert(insertHead,rowData)
rowData.clear()
#最后的数据插入
if(len(rowData)>=1):
dbSql.batchInsert(insertHead,rowData)
def makeLoginData(rows):
'''生成10000条三张表的数据
INSERT INTO gxycsy_resource.multi_tenant_account (account_code, status, tenant_id) VALUES ('r_accountno%', '1', 'r_tenantid%');
INSERT INTO gxycsy.mtsm_user (id, email, name, phone, sex, status, user_code, username, tenant_id, source, paper_no, photo, agent_id, create_user_id) VALUES ('r_userid%', 'r_accountno%@qq.com', '用户r_accountno%', NULL, '1', '1', NULL, 'r_accountno%', 'r_tenantid%', '0', NULL, NULL, NULL, NULL);
INSERT INTO gxycsy.mtsm_account (id, account_code, code_account_type, credentials_non_expired, enabled, non_expired, non_locked, password, user_id, tenant_id, del_status, change_time) VALUES ('r_accountId', 'r_accountno%', '1', '', '', '', '', '$2a$04$bIRQFnqrz9wL19NfrtP9j.oao380cpxGIguN2zgQtIzLz6Uow5e.6', 'r_userid%', 'r_tenantid%', '\0', NULL);
'''
sqlMulti="INSERT INTO gxycsy_resource.multi_tenant_account (account_code, status, tenant_id) VALUES "
sqlMultiPattern=" ('r_accountno%', '1', 'r_tenantid%')"
sqlUser="INSERT INTO gxycsy.mtsm_user (id, email, name, phone, sex, status, user_code, username, tenant_id, source, paper_no, photo, agent_id, create_user_id) VALUES "
sqlUsePattern=" ('r_userid%', 'r_accountno%@qq.com', '用户r_accountno%', NULL, '1', '1', NULL, 'r_accountno%', 'r_tenantid%', '0', NULL, NULL, NULL, NULL)"
sqlAccount="INSERT INTO gxycsy.mtsm_account (id, account_code, code_account_type, credentials_non_expired, enabled, non_expired, non_locked, password, user_id, tenant_id, del_status, change_time) VALUES "
sqlAccountPattern=" ('r_accountId%', 'r_accountno%', '1', 1, 1, 1, 1, '$2a$04$bIRQFnqrz9wL19NfrtP9j.oao380cpxGIguN2zgQtIzLz6Uow5e.6', 'r_userid%', 'r_tenantid%', 0, NULL)"
gapChar=""
testCsv=[] #测试数据文件
for i in range(1,rows+1):
tenantId="%06d" % random.randint(100000, 106666)
accountno="138%06d" % random.randint(10000000, 99999999)
userId="Testuse%06d" % i
accountId="testaccount%06d" % i
sqlMulti+=gapChar+sqlMultiPattern.replace("r_accountno%",accountno).replace("r_tenantid%",tenantId)
sqlUser+=gapChar+sqlUsePattern.replace("r_accountno%",accountno).replace("r_tenantid%",tenantId).replace("r_userid%",userId)
sqlAccount+=gapChar+sqlAccountPattern.replace("r_accountno%",accountno).replace("r_tenantid%",tenantId).replace("r_userid%",userId).replace("r_accountId%",accountId)
testCsv.append(accountno+","+str(i))
if(gapChar==""):
gapChar = ","
# print(sqlMulti+";")
# print(sqlUser+";")
# print(sqlAccount+";")
with open('testDataInsert.sql','w') as f:
f.write(sqlMulti+";\n")
f.write(sqlUser + ";\n")
f.write(sqlAccount + ";\n")
with open('testDataOfJmeter.csv','w') as f:
f.write("account,rowno\n")
for tc in testCsv:
f.write(tc+"\n")
if __name__ == '__main__':
# getCodeStr()
# print(getTenantId())
# for i in range(1,10):ss
# print(getEnCnStr())
# makeRow('ft_bigdata1000',10000000)
makeLoginData(10000)
sqldb.py文件代码：
'''数据库执行'''
import pymysql
class DbSql:
def __init__(self):
# 打开数据库连接
self.con = pymysql.connect(host="192.168.1.231",
port=3306,
user="root",
password="pwd",
database="test_bg",
cursorclass=pymysql.cursors.DictCursor)
# 使用 cursor() 方法创建一个游标对象 cursor
self.cursor = self.con.cursor()
def __del__(self):
# 关闭数据库连接
self.con.close()
def startTransaction(self):
self.con.begin()
def commit(self):
self.con.commit()
def rollback(self):
self.con.commit()
def query(self,sql):
'''sql查询，以json形式返回数据'''
# 使用 execute() 方法执行 SQL 查询
self.cursor.execute(sql)
datas = self.cursor.fetchall()
return datas
def execSql(self,sql,autoTransaction=True):
'''执行sql语句'''
self.con.autocommit(autoTransaction)
self.cursor.execute(sql)
def batchInsert(self,insertSqlHead,valueList):
'''执行sql语句
参数：insertSqlHead格式为'insert into table_name(col1,col2,...） values “
valueList：每条数据一行数据（同insertSqlHead格式为对应），每行格式为”(co1value,col2value,...)
'''
if(len(valueList)<1):
return
sql=insertSqlHead+' '
gapSignal=''
# print(len(valueList))
for value in valueList:
sql += gapSignal+value
if(gapSignal == ''): #第一次后，用','隔开
gapSignal=','
sql+=';'
# print(sql)
self.con.autocommit(True)
self.cursor.execute(sql)
if __name__ == '__main__':
dbSql=DbSql()
data=dbSql.query("select * from member where id=-1")
print("datalen="+str(len(data)))
print(data)
测试
1、建立全文索引
1.1 单字段索引（中英文字段分开）：
建索引语句：
alter table ft_bigdata10 add fulltext index idx_ft10_cl(code_list);
alter table ft_bigdata10 add fulltext index idx_ft10_en(en_cnword);
alter table ft_bigdata10 add fulltext index idx_ft10_cn(en_cnword) with parser ngram;
10万：116.96s
100万：5966.29s
1000万：-
1.2 双字段索引：
建索引语句：alter table ft_bigdata10 add fulltext index idx_ft10(code_list,en_cnword)
10万： 53.94s
100万：752s
1000万：6960s
注：建索引快慢同CPU和磁盘速度又很大关系
2、表空间大小：
10万：约70M
100万：约600M
1000万：约5.7G
2、单字段索引
3、多字段索引
结论
1、 全文索引相比于like非头匹配模式，性能是指数级的提升。
2、 单字段全文索引比多字段全文索引效率更高，当然空间也占用更大。
本次测试中，还发现了Mysql全文索引的很多注意事项，见：Mysql全文索引使用注意事项
462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
