MYSQL 查询速度优化-CSDN博客

本文链接：https://blog.csdn.net/azheng_li/article/details/134535610

前提: 适用于只有一个mysql库的中小公司项目无任何集群主从读写分离等的单机项目

数据库目前有几亿条数据，有的表中有几千万长文本富文本等数据，并且需要联查很多张表；

1.创建索引，mysql目前最大索引能支持3072个字节，大概也就是小几百字。多了建立不了索引，

所以在需要当作联查条件的字段上务必能加索引的都加上索引！！

2.优化sql，一般来说全表扫描模糊查询 like什么的是避免不了的，但是可以对比一下你的sql 用不同方式搜索跑下来的速度比如对比 like 或者 REGEXP正则或者LOCATE函数或者POSITION函数或者 instr 或者find_in_set 或者REPLACE等各种函数相同条件下的搜索速度，这点很重要，一定挑一个用时短的sql再去优化！！ 可以在sql前面加上 explain 去分析你的sql用到了什么索引，检索了多少数据等信息。

还有 不要频繁的多次调用mysql。如果有一对多的情况，可以自己写sql写一对多的查询语句，这种方式会比你在代码里for循环调用查询速度快的多！！

3. 在数据量大的情况下，可能上述2中很多函数基本都要跑很长时间，特别是如果是要去判断筛选长文本 text等是否包含某个字段或者多个字段的时候，速度更慢！！！

但是不要怕，我们可以用mysql的全文检索功能，在一般的中小公司，可能连全文检索的工具es或者solr等都没有，我们这里就用mysql自带的检索实现速度不差于检索功能的查询！

在需要匹配关键词语的长文本上添加fullText 键 (字段需要carchar或者text) 然后修改sql语句为全文检索语句 MATCH (字段) AGAINST ('关键词' IN BOOLEAN MODE) 使用方法详见其他人链接:

MySQL 布尔全文搜索|极客笔记

修改完后你会发现你的搜索速度直线上升，但是目前搜索速度并不是最快的，接下看下面

4 .在使用完全文检索后，可以去检查一下你服务器的配置，提升服务器内核数和内存也能明显提升查询语句的速度。 SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; mysql搜索一下这个语句。可以看到你当前使用的mysql的内存是多大的，我们可以通过配置mysql的运行内存等来提升mysql的查询速度，

在linux服务器上。vim /etc/my.cnf 查看你的mysql innodb_buffer_pool_size 配置，如果没有，可以设置一个指定大小的内存比如: innodb_buffer_pool_size = 12G 参考linux 修改MySQL5.7 配置内存大小_mob649e815b5994的技术博客_51CTO博客

key_buffer_size = 2G 设置以后重启你的mysql

然后试试你原来几分钟都跑不下来的查询吧，你会发现，他被秒了

如果你的数据量达到了千万上亿级别，公司也不舍得花钱再拓展服务器，接下来还有几种方式

5: mysql 数据分区，数据分区其实和分表类似，区别在于:分区是将一张数据量很大的表，存储成多个文件，按照某个字段或者方式去查询时候使用分区，这样每次只用搜特定的分区数据，可以提高查询速度，分表是直接将数据分成多个表，也能够提升查询速度。分区参考链接:

实战演练 | 使用 Navicat 对 MySQL 表进行分区_navicate 分区-CSDN博客

注意使用了全文检索可能没法分区

6: 数据库分表: 分表按你的年份或者需要的业务逻辑分表，是最简单解决查询问题的一种方式,只不过代码业务啥的可能也要跟着更改。

7 ：再次回头排查sql, 经过一系列操作，你可以换一种思路去写sql。比如用in ，或者union去替换 where中包含 or的语句有or的一半大数据情况下会很慢，如果涉及到分页不好用union，可以试试把多个where条件拆分，找到最慢的那个条件，把他换一种方式去搜索。像我之前用的全文检索 sql :

       sELECT distinct a.pubmed_id as pubmedId,
        a.article_title,
        a.date_completed,
        0.2 * IFNULL(b.influence_factor, 0) + IF(POSITION('liver cancer' IN a.article_title), 20, 0) + IF(POSITION('liver cancer' IN c.abstract_content), 10, 0) AS score
        FROM pubmed_primary_table_2023 a
        LEFT JOIN pubmed_abstract_table_2023 c ON a.pubmed_id = c.pubmed_id
        LEFT JOIN pubmed_journal_table b ON a.journal_issn=b.journal_issn
        WHERE
        MATCH(c.abstract_content)
        AGAINST ('+stem +cells +extracellular +vesicles MRD' IN BOOLEAN MODE)
        or
        MATCH(a.article_title)
        AGAINST ('+stem +cells +extracellular +vesicles MRD' IN BOOLEAN MODE)
        ORDER BY score desc

之后修改成这两种 :

	sELECT distinct a.pubmed_id as pubmedId,
	a.article_title,
	a.date_completed,
	0.2 * IFNULL(b.influence_factor, 0) + IF(POSITION('ERBB2' IN a.article_title), 20, 0) + IF(POSITION('ERBB2' IN c.abstract_content), 10, 0) AS score
	FROM pubmed_primary_table_2023_11_21 a
	inner JOIN pubmed_abstract_table_2023_11_21 c ON a.pubmed_id = c.pubmed_id
	inner JOIN pubmed_journal_table b ON a.journal_issn=b.journal_issn
	WHERE
	MATCH(a.article_title)
	AGAINST ('+cancer' IN BOOLEAN MODE)
	union
	sELECT distinct a.pubmed_id as pubmedId,
	a.article_title,
	a.date_completed,
	0.2 * IFNULL(b.influence_factor, 0) + IF(POSITION('ERBB2' IN a.article_title), 20, 0) + IF(POSITION('ERBB2' IN c.abstract_content), 10, 0) AS score
	FROM pubmed_primary_table_2023_11_21 a
	left JOIN pubmed_abstract_table_2023_11_21 c ON a.pubmed_id = c.pubmed_id
	left JOIN pubmed_journal_table b ON a.journal_issn=b.journal_issn
	WHERE
	a.pubmed_id in (select distinct pubmed_id from pubmed_abstract_table_2023 where MATCH(abstract_content)
	AGAINST ('+cancer' IN BOOLEAN MODE))) aa
	order by aa.score desc

速度快了无数倍

或者

sELECT distinct a.pubmed_id as pubmedId,
		a.article_title,
		a.date_completed,
		0.2 * IFNULL(b.influence_factor, 0) + IF(POSITION('ERBB2' IN a.article_title), 20, 0) + IF(POSITION('ERBB2' IN c.abstract_content), 10, 0) AS score
		FROM pubmed_primary_table_2023_11_21 a
		inner JOIN pubmed_abstract_table_2023_11_21 c ON a.pubmed_id = c.pubmed_id
		inner JOIN pubmed_journal_table b ON a.journal_issn=b.journal_issn
		WHERE
		a.pubmed_id in ((select distinct pubmed_id from pubmed_abstract_table_2023_11_21 where MATCH(abstract_content)
		AGAINST (' +lung cancer' IN BOOLEAN MODE))union all select distinct pubmed_id from pubmed_primary_table_2023_11_21 where MATCH(article_title)
		AGAINST ('+lung cancer' IN BOOLEAN MODE))
order by score desc

速度都快了数十倍,

最后如果你的数据真的上亿并且多表联查都是上亿上千万数据；那么推荐你分表吧，这是最块最有效的办法了，但是可能plus的api就用不了，找一个规则去分表，按年或者按月或者按某个属性。

分完表手写sql记得要用注入 from ${表名称} 取值 #{}是取不到值的。