MySQL 性能优化

由于配置是运行过那么长时间，很稳定，基本上不考虑，所以本次主要是sql的优化，并且集中在开源中国的个人空间。下面是这次优化的数据库版本：...



信息安全公益宣传，信息安全知识启蒙。

加微信群回复公众号：微信群；QQ群：16004488加微信群或QQ群可免费索取：学习教程

由于配置是运行过那么长时间，很稳定，基本上不考虑，所以本次主要是sql的优化，并且集中在开源中国的个人空间。下面是这次优化的数据库版本：

案例一：粉丝查询优化

粉丝查询有2条sql

--查询所有粉丝

SELECT user FROM osc_friends f INNER JOIN osc_users u

ON u.id=f.user AND f.friend=? AND f.user? ORDER BY create_time DESC

--查询粉丝数量

SELECT COUNT(friend) FROM osc_friends f INNER JOIN osc_users u

ON u.id=f.user AND f.friend = ? AND f.user  ?

这两个查询在业务可以优化，inner join一个osc_users表目的是去掉osc_friends里面自带了自己的userid，偏偏osc_users表是比较大的表，为啥这样设计，可以看看早年红薯分享的OSChina 用户动态设计说明

优化思路

简化sql，自带的userid的逻辑放到代码层去处理

优化后

SELECT user FROM osc_friends f WHERE f.friend=? ORDER BY create_time DESC

SELECT COUNT(*) FROM osc_friends f WHERE f.friend = ?

sql简化了很多，大大提升了查询速度

小结

有时候业务处理放到代码层，能达到意想不到的效果

案例二：私信优化

SELECT MAX(id) AS id, COUNT(id) AS msgCount

FROM osc_msgs WHERE user = 12 GROUP BY friend ORDER BY id DESC

osc_msgs表存储着所有的私信纪录，随着时间推移，该表慢慢变大，一次查询成本变高，基本都要1秒多

优化思路

取私信表的最新的两个人的对话放入一个新建的osc_last_msgs表，每次发私信更新osc_last_msgs表，这个表只记录最新的私信，这样优化后的私信列表sql就不需要在msg表里面找数据，只需要去osc_last_msgs表寻找.

优化后

SELECT * FROM osc_last_msgs WHERE user=? ORDER BY msg_id DESC

小结

把数据量从大化小的典型案例

案例三 评论优化

SELECT

l1.id

FROM

osc_opt_logs l1,

osc_opt_logs l2

WHERE

l1.obj_type IN (101, 111, 113, 116, 119, 121)

AND l2.obj_type IN (

100,

110,

112,

114,

118,

120,

123,

124,

122,

125,

126,

127,

99

)

AND l1.parent_id = l2.id

AND l2. USER = 12

ORDER BY

l1.id DESC

LIMIT 20;

尝试建立联合索引进行优化，不过效果不佳，因为optlog表特别的大，因此联表查询效率极低，占用查询缓存空间极大。

优化思路

添加一个reply_user字段，将回复的动弹进行标记，这样子就可以简化整个联表查询操作

优化后

SELECT id FROM osc_opt_logs where reply_user = 12 ORDER BY id DESC limit 20;

小结

适当的冗余字段可以降低sql的复杂度

案例四 索引优化

索引优化主要还是依赖explain命令，关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一般很快。所以优化语句基本上都是在优化rows。

一般来说.

[list][*]rows

    关注 计算机与网络安全