MYSQL性能调优

原文出处：http://www.zwjoy.com/blog/?p=3

 

1. 商业需求
说明：
不合理的需求导致资源的投入与产出成反比，开发人员有必要对产品提出的需求进行评估，对不影响大局或者可有可无的功能与产品进行协商，商讨是否可以放弃该功能或者适当修改功能。

 

实例：
产品提出论坛帖子总数实时更新，当前论坛情况是用户量庞大，同时帖子更新频繁。如果要做到实时，必须每次发帖后同时更新统计表数据(假设统计数据全部存储在统计表里面)。如果一秒内帖子产生很多，由于并发问题导致统计数据并不正确，由于如果有锁资源争用，造成性能下降。

 

解决方案：
跟产品讨论数据其实可以不那么实时，没有哪个无聊的人会发完帖子，盯着论坛的帖子数研究总数是否增加，即时不准确也不会影响用户的实际操作。把实时功能去 掉后，可以做一个定时任务(SHELL或者用户触发皆可)，系统每5分钟跑一次，更新掉论坛帖子数即可，这样最直接的结果是减少了大量由于更新产生的 query语句。
事实像类似DZ这样的开源项目都是不做实时的，甚至帖子的回复数都不是实时准确的，它是每发表一个回复，插入统计缓存，系统定时批量执行这些要更新的数据。

 

思考：
大家曾见过淘宝的分页，它并没有列出总共多少页，而是用户点击的附近几页，这样其实节省了select count(*) 的操作(innodb引擎做这个 跟myisam引擎做这个不在同一个概念，所以对于总数据庞大分页的东西其实可以修改分页方式，比如搜索结果)

 

PS:
无用功能堆积导致系统复杂
开发中经常会遇到做了的一些功能不需要啦，导致出现无用的代码和无用的数据表，应该在确认之后，及时对这些功能下线，可以备份一次数据即可

 

2. 系统架构
说明：
包括主从、读写分离、分布式散列存储、事务处理、引擎选用，存放数据、是否使用存储过程、视图、临时表、触发器等等

 

3. 数据表(schema)设计
Schema设计实质反映了一个项目的实际需求，是项目中的存储数据的一个体现，基本上我们看了数据表，大体知道这个项目在做什么。但在设计的同时需要考虑到基本业务逻辑所需要的QUERY操作以及由此产生的性能问题。

 

1) 适度冗余，减少JOIN操作
说明：
这个其实是反范式的操作，schema设计时不能完全遵照范式，范式的目的是减少冗余。 但适当的冗余对性能是有不少的提升。

实例：
记录论坛版块最后的发帖人以及发帖时间，这个可以通过在帖子表里面进行查询(select * from posts where pid = ‘’ order by dateline desc) , 但如果在版块表设计两个字段last_post_member last_post_time, 则可以减少查询次数，一次取出数据。虽然更新（对于频繁的更新，我们同样使用定时任务操作）要导致数据表的写操作，但相对更新，查询显然更加频繁。

 

2) 大表水平拆分
说明：
跟适当冗余相反，大表水平拆分则是根据表里面数据读取的频繁度将一张表分成多张。

实例：
大家见过许多开源项目，基本上用户数据存储在两张或者更多的表中，比如bbs_member (主表) bbs_memberfields(从表)，主表存储读写比较多的字段(一般都是定长字段，比如username、password、groups)，而 从表存储的是读写相对少的字段(比如qq msn 以及存数容量比较大的数据,比如text类型的数据)

 

3) 合适的数据类型
说明：更小的数据类型让数据库以更小的空间存储相同的数据量，这样直接较少IO的消耗。 特别是对于要进行比较或者排序的字段应该选用更为迅速的字段类型，从而节省CPU的消耗。

 

4) 创建合适的索引(单独做一节介绍)

总结：
所以我们在表的设计的时候，需要考虑到我们有多大的用户量(包括一个较为长远的考虑)，考虑哪些query执行得更加频繁，从而给出相应的优化方案。

 

4. 索引(index)设计

1）索引类型：
B-tree ()
Myisam 主键就是聚集索引
聚集索引（数据表的物理存储顺序和表的逻辑存储顺序一致）
非聚集索引：。。。
InnoDB表会包含一个聚集索引
一般是按照下面的规则来设定聚集索引的：
1，假如表包含PRIMARY KEY，InnoDB使用它作为聚集索引
2，假如表没有定义PRIMARY KEY，InnoDB将第一个只包含NOT NULL属性列的UNIQUE index作为主键并且将它设置为聚集索引
3，前两者都不满足的时候，mysql就增加一个隐藏的autocreament

HASH （仅仅memory和 ndb引擎支持)
full-text （仅仅myisam支持，并且只支持 char varchar text三种数据类型)
r-tree

 

2）索引用途：
# 提高数据表检索效率
# 降低数据排序成本（排序主要消耗cpu和内存， 对于分组操作同样是先排序后分组）

2）如何判定 是否建立索引：
# 只有在操作频繁的字段建立索引，绝不建不必要或者想当然的索引，这个在设计表的时候要能大致估计SQL要怎么写。

# 唯一性太差的字段不建立索引，基本上当一条QUERY返回的数据占15%以上就不适合建立索引(通常的像像性别这样的字段绝对不建立索引)

# 更新频繁的字段不建立索引。更新表数据的时候同时要更新索引数据，导致IO访问增大以及影响整个存储系统的消耗。(如果查询更新都较多的情况下，则要比较查询与更新的比例，当比例较大的时候，更新附带的成本是可以接受的)

 

3) 单键索引和联合索引
# 当where语句的过滤条件比较多的时候，考虑几个字段同时出现的频繁度，对频繁度出现较高的字段集建立联合索引。

# 联合索引的缺点是多个字段同时存在，更新可能性更高，索引存储长度也越大。但就查询角度来讲这个因为它过滤掉更多的数据，所以效率更高。同时比在多个字段 都建立单键索引效果好（因为mysql query optimizer需要将多个索引 index_merge 成本更高，有时候还会放弃其他索引）

# 联合索引左前缀原则
使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中 最左边的列集合都可以用于匹配数据行。这种列集合被称为”最左前缀”（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。

假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地 按照 state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个 索引。因此，这个索引可以被用于搜索如下所示的数据列组合：
state, city, zip
state, city
state

MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city 或zip来搜索，就不会使用到这个索引。如果你搜索给定的 state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范 围。

# 前缀索引(只使用某个字段前面部分内容作为索引键索引该字段)

4) MYSQL索引限制
# 索引键长度总和不超过1000字节
# BLOB TEXT类型列只创建前缀索引
# 不支持函数索引
# 使用(!= <> )的时候, mysql不支持索引
# 过滤字段使用了函数运算(如abs() ) 等，不支持索引
# join语句中JOIN字段类型不一致，不支持索引
# LIKE语句中以(‘%abc’)开始，不支持索引
# 非等值查询时，不支持HASH索引

 

5) JOIN语句优化
# 永远用小结果集驱动大结果集（资源消耗存在较大区别外），比如A B联查，
A过滤后 10rows
B过滤后 20rows
A作为驱动表，JOIN过滤则为10次
B作为驱动表，JOIN过滤则为20次
所以选择结果集小的作驱动表

# join字段优化，保证每次查询节省资源

 

6）ORDER BY、 GROUP BY、 DISTINCT优化
原理都需要进行排序，除对字段索引外，需要去掉不必要的返回字段，节省内存(排序的原则)

 

5. QUERY语句优化
优化10原则：
1）优化更需要的优化
说明：执行对系统影响更大的QUERY，一般指的是高并发，执行更加频繁的SQL）

2）定位优化对象性能瓶颈
3）明确优化目标
4）explain
5）profule
6）小结果集驱动大结果集
7）尽可能在索引中完成排序
8）只取自己需要的字段
9）仅仅使用最有效的过滤条件
10）尽可能避免复杂的JOIN和子查询

 

6. 实例分析
Demo1：过度弱化query造成性能消耗
Table1: users表(user_id, user_name, last_feed_time)
Table2: feeds表(feed_id, user_id, feed_data, dateline)
显然users表与feeds表是一对多的关系，现在要查询最近有动作的10个用户，同时在列表页要显示该用户最近24小时的动作。

解决方法1：
// 得到10个最近有动作的用户
$sql = ‘SELECT user_id, user_name
FROM users ORDER BY last_feed_time DESC limit 10’;

// PHP获得10条数据 $rs

// 循环query查询对应ID的最近三个动作
foreach($rs as $k=>$v) {
$sql = “SELECT feed_data
FROM feeds WHERE user_id = ‘{$v[‘user_id’]}’ AND dateline <’’ ORDER BY dateline DESC “;
}

解决方法2：
// 同第一步

// 查询10个用户最近24小时动作
$sql = “SELECT user_id, feed_data
FROM feeds WHERE user_id IN () AND dateline < ‘’ ORDER BY dateline DESC ”:

// 数组组装

总结：我们在开发中经常碰到一些二级栏目的列表页，但数据来自不同的数据表，通常的做法是循环里面执行query，殊不知这样增加了QUERY的次 数，而每次QUERY都需要MYSQL进行解析，在这种情况下，宁愿QUERY复杂点或者在程序端复杂点，保证性能。而从另一个理论来讲，这种情况属于弱 化了QUERY造成性能问题。

Demo2：过度依赖query造成性能消耗
Table1: users表(user_id, user_name)
Table2: user_profile表(user_id, profile_data)
Table3: users_group表(id, group_id, user_id, level)
现在要查询群组ID为1的群组成员信息以及群主的详细信息。
解决方法1：
// 一次查询所有信息
SELECT u.user_name, up.profile_data
FROM users_group ug LEFT JOIN users u ON ug.user_id = u.user_id LEFT JOIN users_profile up ON u.user_id = up.user_id
WHERE ug.group_id = ‘1’

解决方法2：
// 先查询群组下用户的基本信息
SELECT u.user_id, u.user_name,
FROM users_group ug LEFT JOIN users u ON ug.user_id = u.user_id
WHERE ug.group_id = ‘1’
// 查群主信息
SELECT *
FROM users_profile WHERE user_id = ‘’;

总结：与上例相反，这个DEMO操作是增加一个query减少不必要的访问(只需要群主的详细信息，而profile存储的是数据类型比较大的数据，这样操作减少的是IO的访问)

Demo3：小结果集驱动大结果集
Table1: users表(user_id, user_name, sex)
Table3: users_group表(id, group_id, user_id, level, join_time)
现在要查询某个群组下面(id = 1)用户的名称和性别，按加入时间倒序取100-120条的记录

解决方法1：
SELECT u.user_id, u.username, u.sex
FROM users_group ug LEFT JOIN users u ON ug.user_id = u.user_id
WHERE ug.group_id = ‘1’ ORDER BY ug.join_time DESC LIMIT 100,120;

解决方法2：
SELECT u.user_id, u.username, u.sex
FROM （
SELECT user_id
FROM user_group
WHERE user_group.group_id = 1
ORDER BY join_time DESC
LIMIT 100,20) ug, user
WHERE ug.user_id = user.user_id;

总结：方法1参与join操作的是全部user_group中group_id = 1的数据、而方法2 参与join操作的数据仅仅是过滤过的20条数据。所以SQL优化永远记住小结果集驱动大结果集，节省的是CPU和IO的消耗。