[mysql] mysql 查询性能问题

问题描述

查询一组数据，耗时大约10s，客户希望的是300ms，整体差距还是很大，想看下能否优化到用户想要的性能。

• 查询语句

select sid, attr from hide_trace_sig where segment like "1c6b%";

• segment是32位hash字符串，有索引

提前说下结论，当前的需求下，无法300ms，但是问题查询过程还是值得记录。

问题定位

explain

explain select sid, attr from hide_trace_sig where segment like "1c6b%";

结果完全命中索引，所以索引没有问题

profile

SET profiling = 1;
select sid, attr from hide_trace_sig where segment like "1c6b%";
show PROFILES;

查看对应的查询语句ID，比如4这个

image.png

SHOW PROFILE ALL FOR QUERY 4;

image.png

得到结论是sending-data耗时，是因为查询和搜索耗时

网上搜索该问题，类似的有
https://www.cnblogs.com/yaoxing92/p/11058420.html

这类问题的常用优化方式：增加buffer-size
https://www.cnblogs.com/wanbin/p/9530833.html

• 1 show variables like 'innodb_buffer_pool%';
  机器空间有4G，应该足够

• 2 show status like 'innodb_buffer_pool_read%';
  看到Innodb_buffer_pool_reads < Innodb_buffer_pool_read_requests ，使用率不到100%，应该足够
  所以读缓存空间不需要resize优化。

因为用户需要用like的方式查询，所以这个功能前提下，无法优化

索引优化

• 如果不要求用like方式查询，可以优化索引，
  因为mysql索引查询的时间复杂度是logm(N)，m是树的高度，m越小，查询越快，m的高度取决于索引内容的大小，所以短一点的内容和非like的方式可以提高性能。
  只是这样不符合用户需求了。
  高度计算方式
  https://www.cnblogs.com/songpingyi/p/10730156.html

• 字符串做索引的其他优化
  • 字符串倒置
  • 前缀索引优化存储
    解决这个问题都没有用上。