基于抓取的千万数据,实战优化mysql数据库查询速度历程(基于索引的优化)

  sql, 数据库
30 views
起因是抓取的一些公共数据量太大了,达到了千万级的量,抓取的数据需要写接口给前端用。如下字段:

字段主要是企业的手机号邮箱以及所属的行业地区

数据库字段实例

给前端的接口是要能根据注册时间、地区、行业、注册资金以及根据关键字搜索到相关的企业数据,如下图:
查询参数

所有的查询条件都是可以随意组合,如下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索引,最后的索引如下图:
索引

总之索引对查询速度提升非常大,千万级的数据单表查询完全不用担心

LEAVE A COMMENT