起因是抓取的一些公共数据量太大了,达到了千万级的量,抓取的数据需要写接口给前端用。如下字段:
字段主要是企业的手机号和邮箱以及所属的行业和地区
给前端的接口是要能根据注册时间、地区、行业、注册资金以及根据关键字搜索到相关的企业数据,如下图:
所有的查询条件都是可以随意组合,如下sql语句(公司名称模糊查询):
SELECT * FROM company WHERE name LIKE '%字节跳动%' LIMIT 10;
执行了40多秒,这还是数据量在600万的时候。
[SQL]SELECT * FROM company WHERE name LIKE '%字节跳动%' LIMIT 10;
受影响的行: 0
时间: 44.781s
再看加了索引的效果,后面说做法:
[SQL]SELECT * FROM company WHERE MATCH(`name`) AGAINST ('+字节跳动' IN BOOLEAN MODE) LIMIT 10;
受影响的行: 0
时间: 0.120s
可以看到,模糊查询的速度非常慢,而加了索引后的速度简直飞起来,像个梦一样
其他的语句:
SELECT * FROM company WHERE shi = '成都' AND xian = '武侯区' AND trade_id = '721' LIMIT 10;
执行结果和时间:
[SQL]SELECT * FROM company WHERE shi = '成都' AND xian = '武侯区' AND trade_id = '721' LIMIT 10;
受影响的行: 0
时间: 15.232s
再看加了索引后的效果:
[SQL]SELECT * FROM company WHERE shi = '成都' AND xian = '武侯区' AND trade_id = '721' LIMIT 10;
受影响的行: 0
时间: 0.279s
效果依然显著,平均提高速度150倍左右,简直奇迹
—
下面开始怎么做的:
本次的数据库表没有涉及到多表联查,只是一个表的查询,首先是公司名称的模糊查询,这个开始用的是sql语句的like关键字模糊查询,速度非常的慢。
解决办法:刚开始想着数据都千万条了,是不是只能分表来解决,然后看了mycat这个数据库中间件,安装了下,发现是挺不错的。
本来的打算是先加下全文索引后提升下速度,再分下表了,然而结果出乎我的意料,mysql数据库真的强,千万条数据根本用不着分表(这里没有多表联合查询),不多废话,开始看看怎么给公司名加的全文索引来替代like关键字的模糊查询和普通字段索引:
1. 首先 MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
2. 配置mysql,在my.ini文件里[mysqld]下面添加,这是设置全文索引的分词字段长度范围,搜索的关键字不在范围内则不会匹配,支持myisam和innodb数据引擎,设置后重启数据库
innodb_ft_min_token_size = 1
ft_min_word_len = 1
ngram_token_size = 1
数据库命令窗口里执行:show variables like '%ft%';
能查看到相关信息:
这里最小分词长度设置为1,最大默认是84,够用了
3. 执行下面的命令,用mysql自带的分词器给company表的name字段设置全文索引,名称随意设置(注意:mariadb目前还没有自带分词器,用它的话会报找不到ngram函数的错误,这就需要自己分词并加一个字段存取分词,然后建立索引了)
ALTER TABLE company ADD FULLTEXT INDEX name_index (`name`) WITH PARSER ngram
稍等10几分钟就建好索引了,这时候查询语句要按照全局索引的查询方法,具体使用网上有很多,这里不详说了。
4. 普通字段索引就非常好添加了,用的btree索引,最后的索引如下图:
总之索引对查询速度提升非常大,千万级的数据单表查询完全不用担心