mysql优化百万级数据量的过程

背景

   假设有某一个业务 数据量大体是500多万,经过sql测试发现20条数据用了2秒多,这速度太慢了!!

需要减少RT

解决过程 

 1.前置配置

   1.配置mysql的慢sql定位

        修改系统参数可以达到我们定位慢sql的目的,要求接口平均响应时间低于500毫秒,预估起码sql响应时间要低于1秒 为什么是1秒? 因为接口可以使用缓存降低DB压力, 关于压测和整体系统架构这里不做讨论

新建一个sql查询:

-- 打开慢sql记录
SET GLOBAL slow_query_log = on;
-- 记录大于1秒的sql
SET GLOBAL long_query_time = 1;
-- 设置慢sql文件位置
SET GLOBAL slow_query_log_file='/var/lib/mysql/slowSQL.sql';

  2.测试请求接口(略)

  3.查看慢sql

打开/var/lib/mysql/slowSQL.sql 文件可以看到慢sql  Query_time就是查询时间 下图是其中一条

  4.分析慢sql原因

使用EXPALN 分析sql发现已经走了索引,

经过剖析发现是sql连表查询产生的导致响应过慢

  5.优化sql

     知道了原因,根据业务接口排查 count统计数量不需要连表查询 只有实际数据sql需要连表查询

把连表sql部分去除 最后问题解决 

实际需要查询数据的sql同理.解决问题可能与不同业务不一样 思路大体一致

相关技术

 1.OPTIMIZE TABLE

  OPTIMIZE TABLE是MySQL中的一个命令，用于重新组织表和索引以提高性能。它适用于MyISAM和InnoDB存储引擎。

OPTIMIZE TABLE table_name;

2.索引失效常见

  (1)数据类型不匹配:数据类型不匹配会导致mysql隐式转换导致索引失效

  (2)模糊查询以%开头

  (3)索引列使用了函数或运算 

  (4)索引列包含空值 :mysql建立索引时候不存储NULL

  (5)查询条件中使用了OR关键字:涉及到不同索引,不支持多个索引来进行查询优化

  (6)联合索引违反了最左匹配原则

  (7) 全表扫描比使用索引更快的时候

3.B+树结构

   B+树 的非叶子节点是不保存数据的，只起到索引作用，它的叶子节点才保存数据。

4.读写分离

  由于索引的结构 SELECT快而INSERT、UPDATE和DELETE慢,这时候可以采用读写分离,将读操作与写操作在不同的库上实现

 5.使用数据缓存

  在高并发下特别是秒杀类的场景,应该尽量减少DB的请求,这时候可以使用缓存

参考:MySQL性能分析工具——如何快速定位SQL执行慢的原因？_mysql慢sql分析工具_warybee的博客-CSDN博客

 UI响应:3个重要的界限