如何优化MySQL

MySQL 提供了内建的耗时查询日志。使用前，打开 my.cnf 文件，将slow_query_log 设置为”On”。 同时将 long_query_time 设置为一个对一次查询来说比较慢的时间（秒数），比如 0.2。slow_query_log_file 设置为你想保存日志文件的路径。然后运行代码，执行时间超过设定上限的查询将会被记录到日志中。

一旦定位这些烦人的查询，就可以探究慢的原因。MySQL提供了EXPLAIN关键字。可以与SELECT、DELETE、INSERT、REPLACE、UPDATE语句一起使用。只需要如下一样添加到查询开始处：

1

2

3

EXPLAIN SELECT picture.id, picture.title


FROM picture


LEFT JOIN album ON picture.album_id = album.id
WHERE album.user_id = 1;

结果是对数据访问过程的解析。每行代表一个查询相关的数据表：

重点是表名、使用的key、查询执行时所扫描的行数。

这个查询扫描了picture表2百万行，对每个picture记录扫描了album表2万行。意味着，该查询实际扫描了album表400亿行。这个过程可以更高效。

索引

使用索引，可以极大提升性能。比如地址簿中的名字。找一个名字，你可以从头翻到尾，也可以找到相应的字符标签页来快速定位。

使用索引可以避免无必要的表扫描。比如在picture.album_id上添加索引：

1

ALTER TABLE picture ADD INDEX(album_id);

现在执行查询，就不会扫描整个picture列表了。首先，所有的album表会被扫描以找到属于该用户的album，然后，使用album_id列索引快速定位对应的picture。扫描的行数减少到了20万，比原始查询快了317倍。

按照以下方式添加索引可以确保两张表都能使用对应键：

1

ALTER TABLE album ADD INDEX(user_id);

这次，album表不会被全部扫描，通过user_id键就可以精确定位相应的album。然后这100个album被扫描后，通过album_id键相关的picture也可以快速找到。每张表都用到了一个键进行查询性能优化，结果比原始查询快了380倍。

但是并不意味着可以随意添加索引，因为索引会导致写数据库用时增长。所以使用索引导致读用时缩短，写用时增长。因此，如果能提升读性能时再添加索引。使用EXPLAIN来确认索引是否被查询用到，没有就删除。

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

案例一：粉丝查询优化

粉丝查询有2条sql

1

2

3

4

5

6

--查询所有粉丝

SELECTuserFROMosc_friendsfINNERJOINosc_usersu

ONu.id=f.userANDf.friend=?ANDf.user<>?ORDERBYcreate_timeDESC

--查询粉丝数量

SELECTCOUNT(friend)FROMosc_friendsfINNERJOINosc_usersu

ONu.id=f.userANDf.friend=?ANDf.user<>?

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

优化思路

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

优化后

1

2

3

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

 

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

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

小结

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

案例二：私信优化

1

2

SELECTMAX(id)ASid,COUNT(id)ASmsgCount

FROMosc_msgsWHEREuser=12GROUPBYfriendORDERBYidDESC

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

优化思路

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

优化后

1

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

小结

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

案例三 评论优化

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

SELECT

l1.id

FROM

osc_opt_logsl1,

osc_opt_logsl2

WHERE

l1.obj_typeIN(101,111,113,116,119,121)

ANDl2.obj_typeIN(

100,

110,

112,

114,

118,

120,

123,

124,

122,

125,

126,

127,

99

)

ANDl1.parent_id=l2.id

ANDl2.USER=12

ORDERBY

l1.idDESC

LIMIT20;

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

优化思路

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

优化后

1

SELECTidFROMosc_opt_logswherereply_user=12ORDERBYidDESClimit20;

小结

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

案例四 索引优化

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

一般来说.

• rows<1000，是在可接受的范围内的。
• rows在1000~1w之间，在密集访问时可能导致性能问题，但如果不是太频繁的访问(频率低于1分钟一次)，又难再优化的话，可以接受，但需要注意观察
• rows大于1万时，应慎重考虑SQL的设计，优化SQL

这个没有绝对值可参考，一般来说越小越好，，如果100万数据量的数据库，rows是70万，通过这个可以判断sql的查询性能很差，如果100万条数据量的数据库，rows是1万，从我个人的角度，还是能接受的。

另外就是extra的信息，该列包含MySQL解决查询的详细信息 ，重点关注出现关键字：

Using filesort：当Query 中包含order by 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。

Using temporary：在某些操作中必须使用临时表时，在 Extra 信息中就会出现Using temporary ,主要常见于 GROUP BY 和 ORDER BY 等操作中

当执行计划Extra 出现Using filesort 、Using temporary 时，可以考虑是否需要进行sql优化和调整索引，最后再调整my.cnf 中与排序或者临时表相关的参数，如sort_buffer_size或者tmp_table_size.

比如下面这个：

原因是mysql查询只使用一个索引，如果where字句用了索引的话，那么order by 中的列是不会使用索引的。所以order by的条件也需要添加到索引里面组成联合索引，优化后

还有一个需要注意的点是，索引有个最左前缀的原则：联合索引（a,b,c）可以匹配(a)、（a,b）、（a,b,c）但不可以匹配(b,c)

小结

explain SQL语句应该是日常开发中的习惯动作，有时explain出来的结果，可能会出于偏离设计的意料之外

案例五 关注数据库状态

当满满期待优化后有大幅度性能提升的时候，现实总是跟你开玩笑，经过测试检查，发现不是sql的问题，有可能是跟服务器有关，用top命令看了发现mysql进程的cpu占用率一直在100%左右，这就奇怪了，用show processlist看了一下mysql的进程，发现一个可疑的sql一直在执行，kill 掉后cpu占用率马上下来了

小结

当你发现数据库cpu或者io有异常现象时候，用show processlist看看数据库在忙什么

写在最后

经过这次优化，个人空间打开速度提升了，总结几条心得：

1.不要指望所有SQL语句都能通过SQL优化，业务上的调整带来意想不到的效果；

2.所有的性能优化都是空间换时间，通过冗余来提高性能，大体思路都是大化小，分而治之

3.explain是sql优化的入门

4.索引利大于弊，多用，善用之

转载自：http://blog.jobbole.com/111129/