Mysql全文索引性能测试对比

在关系型数据库中，使用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全文索引使用注意事项