在關(guān)系型數(shù)據(jù)庫中，使用like模糊查詢，如果是 'xxx%'頭匹配方式，字段索引不會失效，性能能接受；但如果是 '%xxx%'全文匹配方式，索引會失效，在數(shù)據(jù)量大的情況下，通過此種方式查詢的效率極低。此時可通過全文索引（Full-Text Search）來進(jìn)行。
Mysql在5.6之后提供全文索引，本文主要記錄測試全文索引效率。
1、Mysql在5.6之后基于MyISAM引擎提供全文索引，在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引
2、Mysql5.7開始 innoDb開始支持全文索引，且從MySQL 5.7.6開始，MySQL內(nèi)置了ngram全文解析器，用來支持中文、日文、韓文分詞。
1、為了對比，本次測試用了10萬、100萬、1000萬數(shù)量級做對比測試。
2、測試數(shù)據(jù)庫在服務(wù)器（ctrix虛擬機2vcpu，8G內(nèi)存），本次使用版本5.7.13；客戶端在PC上用navicat工具；網(wǎng)絡(luò)千M網(wǎng)。
3、測試是基于我使用的硬件情況，因此反映性能的具體指標(biāo)供參考。
4、預(yù)先準(zhǔn)備好數(shù)據(jù)，我是采用了《簡愛》全本中英文，采用隨機截斷不同長度文本方式分別從里面收取10萬、100萬、1000萬數(shù)據(jù)。
5、my.cnf配置（配置完了別忘了重啟mysql）如下：
[mysqld]
#英文分詞長度默認(rèn)為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 ;
由于數(shù)據(jù)量大，將數(shù)據(jù)加載到?jīng)]有FULLTEXT索引的表中然后創(chuàng)建索引要比將數(shù)據(jù)加載到具有現(xiàn)有FULLTEXT索引的表中快得多。本次均是在數(shù)據(jù)導(dǎo)入后再建索引。
需要自己寫程序?qū)?。本人用python完成，見如下代碼。
主程序：
# -*- coding:utf-8 -*-
import random
import string
from dbsql import DbSql
cn_en_src=[]
def getTenantId():
'隨機生成6位數(shù)字的字符串'
return "%06d" % random.randint(100000, 119999)
def getCodeStr():
# 從a-zA-Z0-9生成指定數(shù)量的隨機字符：
# 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()
#最后的數(shù)據(jù)插入
if(len(rowData)>=1):
dbSql.batchInsert(insertHead,rowData)
def makeLoginData(rows):
'''生成10000條三張表的數(shù)據(jù)
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=[] #測試數(shù)據(jù)文件
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文件代碼：
'''數(shù)據(jù)庫執(zhí)行'''
import pymysql
class DbSql:
def __init__(self):
# 打開數(shù)據(jù)庫連接
self.con = pymysql.connect(host="192.168.1.231",
port=3306,
user="root",
password="pwd",
database="test_bg",
cursorclass=pymysql.cursors.DictCursor)
# 使用 cursor() 方法創(chuàng)建一個游標(biāo)對象 cursor
self.cursor = self.con.cursor()
def __del__(self):
# 關(guān)閉數(shù)據(jù)庫連接
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形式返回數(shù)據(jù)'''
# 使用 execute() 方法執(zhí)行 SQL 查詢
self.cursor.execute(sql)
datas = self.cursor.fetchall()
return datas
def execSql(self,sql,autoTransaction=True):
'''執(zhí)行sql語句'''
self.con.autocommit(autoTransaction)
self.cursor.execute(sql)
def batchInsert(self,insertSqlHead,valueList):
'''執(zhí)行sql語句
參數(shù)：insertSqlHead格式為'insert into table_name(col1,col2,...） values “
valueList：每條數(shù)據(jù)一行數(shù)據(jù)（同insertSqlHead格式為對應(yīng)），每行格式為”(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和磁盤速度又很大關(guān)系
2、表空間大?。?br> 10萬：約70M
100萬：約600M
1000萬：約5.7G
2、單字段索引
3、多字段索引
1、 全文索引相比于like非頭匹配模式，性能是指數(shù)級的提升。
2、 單字段全文索引比多字段全文索引效率更高，當(dāng)然空間也占用更大。
本次測試中，還發(fā)現(xiàn)了Mysql全文索引的很多注意事項，見：Mysql全文索引使用注意事項
