MySQL查询优化经验分享

记录工作中MYSQL查询优化

• 版本：5.7.27

select * from table_a where column like '%xxx%'

使用like '%xxx%'会导致全表扫描，如果表里数据较多，查询的列内容很长，就会影响查询效率。

1. 创建全文索引，使用全文索引查询

ALTER TABLE table_a ADD FULLTEXT INDEX ft_column(column); -- 添加全文索引

SELECT * FROM table_a WHERE MATCH(table_a) AGAINST('+keyword' IN BOOLEAN MODE); -- 检索

补充：'+keyword'中,keyword表示查询时要使用的关键字，+表示必须包含。
实际使用提升非常大，也存在一些限制。
例如：
有最小词长度限制（默认为4），太短的词可能不会被索引。SHOW VARIABLES LIKE 'ft_min_word_len';查看最小词长度，可以修改但需要重启mysql。
必须是明确的字符串。不能在连表查询时作为关联的条件使用，例如：

SELECT a.* FROM table_a a 
JOIN table_b b ON MATCH(a.column) AGAINST(b.column IN BOOLEAN MODE);--会报错

2. 使用前缀索引
   某些情况下要搜索的关键字正好是在开头，就可以使用。

ALTER TABLE 表名 ADD INDEX 索引名 (列名(前缀长度));
-- 或
CREATE INDEX 索引名 ON 表名 (列名(前缀长度));

在实际使用中，由于我的需要，没有采用这种方法
3. LOCATE(substr,str), LOCATE(substr,str,pos),POSITION('substr' IN field),INSTR(str,'substr')方法
这几个方法都大同小异，实际使用提升不如全文索引。

特别长的字段影响查询速度

当你需要查询的列中存在特别长的内容时（如longtext类型的），会很慢。参考https://zhuanlan.zhihu.com/p/358724108
检查列中的内容是否全部都是你需要的，尽可能将长长的内容拆成小的，分成别的列，或者别的表。

有复杂逻辑处理的sql

实际工作中发现，有些处理逻辑太复杂了，于是只从库中获取必要的数据，将处理逻辑放到程序中完成。