对mysql的总结与反思_mysql数据库性能调优

每一篇文章的开始照旧是一点废话，今天应该是来北京刚好满一年吧，端午和实验室的大佬们吹吹逼，已经感觉到自己被深深吊打了，知耻而后勇吧，反正不去写算法我挺开心的，深度瞎学，人工智障。最近也反思了一下我最开始找工作的时候没有理想的算法offer，大概是诗和远方总是在撩拨我那不安躁动的心，整个人都显得很浮躁，没有去认真的去钻研算法原理，也没有总结出一个好一点的方法论。那从现在立志去做一个高级的curdboy，为时不晚，哈哈。

本来这周是打算把hashmap的升级版currenthashmap写完的，顺便引入一下多线程，讲一讲各种锁，但是技术菜鸟的我，上周写了3个sql，3个都报了慢sql，是真的菜，甚至有报5s，被主动查杀的。感到有点羞愧，所以先补一补sql基础吧。

回归主题，本期主题是mysql性能调优。在正式开始之前我们先思考几个问题：

1。你的sql问题的表现形式是什么。响应时间很慢？偶然出现的mysql，死锁？

2。你想优化的目标是什么。响应时间，sql稳定性。

在分析sql之前先分析结果

当然我们今天想说的性能调优指的是降低sql的响应时间。要想降低响应时间，首先我们就需要理解服务器执行sql花费在了哪里。

值得优化的查询：

解决最主要的矛盾

优化的方向：

1.扫描最少的行

2.返回最少的列。

从实战出发

//先大概介绍一下业务背景，业务系统每天都会有50w条数据写到我们的数据库，我们加工处理完之后再传给下游
//pt是时间版本号，fileverison是文件版本号（会存在逻辑错误，或者各种修复数据，导致需要重刷业务数据）
//下游每次会使用10个线程，每次读取5000行
select
    id
    from xx
    where is_delete = 0
      and pt = #{pt}
      and fileversion = #{version}
    limit #{pageNo},#{pageSize}
场景的特点，查询的字段重复性高 limit到后面，扫描的数量多

版本最开始上线：盲目加索引。

出现问题：数据库慢查询，数据量900w左右，某一天的数据重跑了多次，数据量大概在400w左右。

问题定位：

新建的索引为非聚簇索引，每一次查询需要先查询辅助索引，再查询主键索引。

在limit 395000，5000 的时候整个过程需要访问 800w次，失败的尝试

Explain

select_type:表示查询的类型。 type:表示表的连接类型 key:表示实际使用的索引 rows:扫描出的行数(估算的行数) Extra:执行情况

type:ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

Extra:using index using where

第二次尝试：添加pt和version联合索引：

联合索引，相当于B+树的叶子节点，再加了一层叶子节点。效果依旧不理想。

在limit 395000，5000 的时候整个过程需要访问 100w次，失败的尝试。

提到了联合索引，再提一个sql的优化。

第三次尝试：覆盖索引

索引原则：

一星：索引将相关的原则放在一起

二星：索引中的顺序和查找中的顺序放在一起

三星：如果索引中的列包含了查询中的所有列

覆盖索引

在limit 395000，5000 的时候整个过程需要访问 50w次，失败的尝试

SELECT
    t1.*
    FROM
    ershou_ledger_receive_all t1
    JOIN (select
    id
    from ershou_ledger_receive_all
    where is_delete = 0 
      and pt = #{pt}
      and fileversion = #{version}
    limit #{pageNo},#{pageSize}
    )t2 ON t1.id = t2.id

第四次尝试

在limit 395000，5000 的时候整个过程需要访问 5000次.查询次数缩小200倍，性能稳定，并且与数据量无关

select
    fileversion
    from ershou_ledger_receive_all
    where pt = #{pt} and fileversion=#{fileversion}
    order by id limit 1
SELECT
    t1.*
    FROM
    ershou_ledger_receive_all t1
    JOIN (select
    id
    from ershou_ledger_receive_all
    where id >#{id}
    limit 5000
    )t2 ON t1.id = t2.id