基于抓取的千万数据，实战优化mysql数据库查询速度历程（基于索引的优化）

  sql, 数据库
515 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