这个月11号应 蝎子 同学和 ThinkInLamp 的邀请，在上海光大会展中心参加了由 ThinkInLamp 社区组织的 第二届华东地区数据库大会，并做了一个题为“浅谈MySQL数据库优化”的主题分享交流。
考虑到参会人员大多是一线的技术工程师，而且还有很多是开发人员，所以内容更偏重于一些指导性的原则，会后将 PPT 格式稍作了调整，这里再发出来希望对感兴趣的朋友有所帮助。
浅谈 MySQL 性能调优
View more presentations from Sky Jian
这个月11号应 蝎子 同学和 ThinkInLamp 的邀请，在上海光大会展中心参加了由 ThinkInLamp 社区组织的 第二届华东地区数据库大会，并做了一个题为“浅谈MySQL数据库优化”的主题分享交流。
考虑到参会人员大多是一线的技术工程师，而且还有很多是开发人员，所以内容更偏重于一些指导性的原则，会后将 PPT 格式稍作了调整，这里再发出来希望对感兴趣的朋友有所帮助。
浅谈 MySQL 性能调优
View more presentations from Sky Jian
接着上一篇 MySQL 数据库性能优化之索引优化，这是 MySQL数据库性能优化专题 系列的第三篇文章：MySQL 数据库性能优化之SQL优化
有人反馈之前几篇文章过于理论缺少实际操作细节，这篇文章就多一些可操作性的内容吧。
注：这篇文章是以 MySQL 为背景，很多内容同时适用于其他关系型数据库，需要有一些索引知识为基础
优化目标
减少 IO 次数
IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。
降低 CPU 计算
除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标
优化方法
改变 SQL 执行计划
明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标
常见误区
count(1)和count(primary_key) 优于 [...]
接着上一篇 MySQL 数据库性能优化之表结构，这是 MySQL数据库性能优化专题 系列的第三篇文章：MySQL 数据库性能优化之索引优化
大家都知道索引对于数据访问的性能有非常关键的作用，都知道索引可以提高数据访问效率。
为什么索引能提高数据访问性能？他会不会有“副作用”？是不是索引创建越多，性能就越好？到底该如何设计索引，才能最大限度的发挥其效能？
这篇文章主要是带着上面这几个问题来做一个简要的分析，同时排除了业务场景所带来的特殊性，请不要纠结业务场景的影响。
索引为什么能提高数据访问性能？
很多人只知道索引能够提高数据库的性能，但并不是特别了解其原理，其实我们可以用一个生活中的示例来理解。
我们让一位不太懂计算机的朋友去图书馆确认一本叫做《MySQL性能调优与架构设计》的书是否在藏，这样对他说：“请帮我借一本计算机类的数据库书籍，是属于 MySQL 数据库范畴的，叫做《MySQL性能调优与架构设计》”。朋友会根据所属类别，前往存放“计算机”书籍区域的书架，然后再寻找“数据库”类存放位置，再找到一堆讲述“MySQL”的书籍，最后可能发现目标在藏（也可能已经借出不在书架上）。
在这个过程中： “计算机”->“数据库”->“MySQL”->“在藏”->《MySQL性能调优与架构设计》其实就是一个“根据索引查找数据”的典型案例，“计算机”->“数据库”->“MySQL”->“在藏” 就是朋友查找书籍的索引。
假设没有这个索引，那查找这本书的过程会变成怎样呢？朋友只能从图书馆入口一个书架一个书架的“遍历”，直到找到《MySQL性能调优与架构设计》这本书为止。如果幸运，可能在第一个书架就找到。但如果不幸呢，那就惨了，可能要将整个图书馆所有的书架都找一遍才能找到我们想要的这本书。
注：这个例子中的“索引”是记录在朋友大脑中的，实际上，每个图书馆都会有一个非常全的实际存在的索引系统（大多位于入口显眼处），由很多个贴上了明显标签的小抽屉构成。这个索引系统中存放这非常齐全详尽的索引数据，标识出我们需要查找的“目标”在某个区域的某个书架上。而且每当有新的书籍入库，旧的书籍销毁以及书记信息修改，都需要对索引系统进行及时的修正。
下面我们通过上面这个生活中的小示例，来分析一下索引，看看能的出哪些结论？
索引有哪些“副作用”？
图书的变更（增，删，改）都需要修订索引，索引存在额外的维护成本
查找翻阅索引系统需要消耗时间，索引存在额外的访问成本
这个索引系统需要一个地方来存放，索引存在额外的空间成本
索引是不是越多越好？
如果我们的这个图书馆只是一个进出中转站，里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”，那我们的索引就只会不断的修改，而很少会被用来查找图书
所以，对于类似于这样的存在非常大更新量的数据，索引的维护成本会非常高，如果其检索需求很少，而且对检索效率并没有非常高的要求的时候，我们并不建议创建索引，或者是尽量减少索引。
如果我们的书籍量少到只有几本或者就只有一个书架，索引并不会带来什么作用，甚至可能还会浪费一些查找索引所花费的时间。
所以，对于数据量极小到通过索引检索还不如直接遍历来得快的数据，也并不适合使用索引。
如果我们的图书馆只有一个10平方的面积，现在连放书架都已经非常拥挤，而且馆藏还在不断增加，我们还能考虑创建索引吗？
所以，当我们连存储基础数据的空间都捉襟见肘的时候，我们也应该尽量减少低效或者是去除索引。
索引该如何设计才高效？
如果我们仅仅只是这样告诉对方的：“帮我确认一本数据库类别的讲述 MySQL 的叫做《MySQL性能调优与架构设计》的书是否在藏”，结果又会如何呢？朋友只能一个大类区域一个大类区域的去寻找“数据库”类别，然后再找到 “MySQL”范畴，再看到我们所需是否在藏。由于我们少说了一个“计算机类”，朋友就必须到每一个大类去寻找。
所以，我们应该尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段。
如果我们是这样说的：“帮我确认一本讲述 MySQL 的数据库范畴的计算机丛书，叫做《MySQL性能调优与架构设计》，看是否在藏”。如果这位朋友并不知道计算机是一个大类，也不知道数据库属于计算机大类，那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中，然后从包含 “MySQL” 书籍中再看有哪些是“数据库”范畴的（有可能部分是讲述PHP或者其他开发语言的），然后再排除非计算机类的（虽然可能并没有必要），然后才能确认。
所以，字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前。
如果我们还有这样一个需求（虽然基本不可能）：“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找，每次都到索引柜找到计算机书籍所在的区域，然后从书架上搬下一格（假设只能以一格为单位从书架上取下，类比数据库中以block/page为单位读取），取出第一本，然后再从索引柜找到计算机图书所在区域，再搬下一格，取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢？他需要从地一个书架一直往后找，当找到计算机的书，搬下一格，取出所有计算机的书，再往后，直至所有书架全部看一遍。在这个过程中，如果计算机类书籍较多，通过索引来取所花费的时间很可能要大于直接遍历，因为不断往复的索引翻阅所消耗的时间会非常长。（延伸阅读：这里有一篇以前写的关于Oracle的文章，索引扫描还是全表扫描（Index Scan Or Full Table Scan））
所以，当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。
如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类，抑或者图书馆的索引系统中这两个类别属性并没有关联关系，又会怎样呢？也就是说，朋友得到的是2个独立的索引，一个是告知“计算机”这个大类所在的区域，一个是“数据库”这个小类所在的区域（很可能是多个区域），那么他只能二者选其一来搜索我的需求。即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找，那这样的效率实际过程中也会比较低下。
所以，在实际使用过程中，一次数据访问一般只能利用到1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。
看完这些分析，我想大家应该了解索引优化的一些基本思路了吧
这是之前发布于《程序员》杂志2011年08期的一篇文章，这里再在Blog上发布一下。
在当前这个信息量飞速增长的时代，一个企业，尤其是电子商务企业的成功已经越来越多地与其海量数据处理能力相关联。高效、迅速地从海量数据中挖掘出潜在价值并转化为决策依据的能力，将成为企业的核心竞争力。
数据的重要性毋庸置疑，但随着数据的产生速度越来越快，数据量越来越大，数据处理技术的挑战自然也越来越大。如何从海量数据中挖掘出价值所在，分析出深层含义，进而转化为可操作的信息，已经成为各互联网企业尤其是电子商务公司不得不研究的课题。本文将介绍国内箱包行业电子商务领军者麦包包如何利用海量数据的分析处理（个性化推荐引擎）来协助用户更好地完成购买体验。
图1 数据层基础架构
数据层基础架构
如图1所示，麦包包的数据层基础架构与其他很多互联网公司相比，可能会有一点儿差异，那就是有一个用于实时分析处理的在线分析数据层，用来处理一些对实时性要求较高的分析任务。
总的来说，麦包包的数据层分为下面三个部分。
在线交易数据层
用于存放网站对外访问数据，如交易相关、产品相关、用户相关等数据。
离线分析数据层
用于分析各种报表、数据挖掘，如购买行为、销售分析、浏览跟踪等。
在线分析数据层
用于处理一些对实时性要求较高的分析，如在线交易分析、用户浏览推荐等。在线交易数据层和离线分析数据层对于大家来说都已经比较熟悉了，二者的数据特点和访问特点都很清晰明确，架构方向也相对明确。只有在线分析系统比较特别，既有高并发的用户访问，同时又兼具了分析型复杂查询及海量的基础数据，构建起来相对要复杂一些。所以下面简单介绍一下麦包包如何构建在线分析系统的应用之一——“个性化推荐引擎”。
个性化推荐引擎
我们首先分析一下这个推荐引擎的需求。
关联个性化
根据用户的喜好倾向以及访问历史记录，不同用户浏览同一个产品时，将给出不同的关联推荐结果。
页面个性化
不同用户访问同一个页面，我们将会根据用户的以往购买历史及浏览行为而展示个性化的内容。
搜索个性化
随着用户的多次搜索及结果点击行为，我们会对搜索结果进行过滤重组，尽可能展示更符合用户需求的搜索结果。也就是说，在完全相同的基础数据中，不同用户在同一时间搜索同一个关键词，可能会给出不一样的结果；或者同一个用户重复多次搜索同一个关键词，也可能会有不一样的结果。
我们再来看一看推荐引擎的数据特点。
海量
超过500万会员，5位数的SKU，7位数的访问量。将这些数据与会员及SKU的各类属性相互关联，数据量之庞大可想而知。
多维度
从性能优化角度来说，数据量大并不可怕，只要访问方式简单，很容易通过索引等手段进行优化。可偏偏不幸的是，由于将用户和产品进行多维度关联，既需要根据用户去分析，又需要根据产品去关联，再辅以运行时的各类属性；既可能各个维度同时存在，也可能只有任何一个维度；多维度就多维度吧，可还有很多访问是分析型，比较难以优化扩展。
访问高并发
当然，数据量大也并不一定就可怕，如果并发访问较小，响应时间要求不是太高，那也容易解决，可以用Hadoop之类的分布式系统来分析计算。可恰恰不巧的就是这个系统面对的是网站上的访问客户，对并发及响应时间的要求和OLTP系统一样。
需求已经确定，数据特点也已了解，下一步就是根据数据的特点，设计一个切实可行的架构来实现这些应用需求了。
在如此海量数据中进行高并发的复杂分析查询，还要能够快速响应，看上去就像是一个不可能的任务。但仔细分析之后，我们不难发现，推荐引擎结果主要由以下几个因素决定。
用户固定属性：年龄、性别、职业类型、地域、价格承受范围、色彩喜好、品牌喜好等。
产品固定属性：品牌、类别、材质、价格、色系等。
用户以往行为：浏览历史、购买历史等。
用户当前行为：当前点击、浏览等。
以上四个因素实际上对应了四种数据，在分析每一种数据的特点之后，可以发现前面三个因素所对应的数据都是相对静态的，只有用户当前行为才是一个在不断变化的动态数据。也就是说，在海量数据中，只有少部分数据是动态的，其他大部分都是静态。
当然，用户属性中的各种喜好，也需要我们通过用户以往的历史购买以及浏览行为进行各种分析挖掘才能获得，但这都是由历史积淀数据分析得来，而不是由当前的运行时动态数据决定。价格承受范围以及地域特性也同样如此。
数据的这一特性对我们的架构设计起到了一个非常关键的作用，因为我们可以使用完全不同的方式来将静态数据和动态数据分开处理，再合并分析。静态数据的变化较小，实时性要求较低，我们将进行离线分析；动态数据相对较少，但实时性要求较高，我们在线实时处理。动、静数据在线合并分析。这样一来，我们就可以很轻松地绕过海量数据的高并发在线分析的问题，将这一动作交由离线分析系统定时作业批量完成，既不会有高并发问题，又不存在响应时间的压力。至于在线实时数据的处理，由于数据量的大幅缩减，以及访问方式的简化，比在线交易的OLTP系统复杂度高不了太多，自然也就容易优化了。
图2 推荐引擎基本架构
架构设计
简单来说，推荐引擎系统本身的基础架构就如图2所展现的一样，一部分数据进行离线计算，另一部分数据在线计算合并，最终通过推荐引擎API将数据处理后返回给前端应用。
看上去简单，但有几个问题并没有展现出来，那就是离线计算和在线计算这两部分具体是如何构建的？数据如何进入离线计算系统？又如何将离线运算结果回送至在线计算系统中？最终数据又如何交由前端应用使用？让我们再来看看图3。
离线分析层完全可以通过成熟的产品来构建，如Greenplum、Hadoop等，目前我们已经使用了Greeplum，后续很快还会引入Hadoop，通过HBase + Hive来对处理我们的用户与各SKU的关系数据，帮助进一步完善我们的协同过滤算法，进而优化推荐引擎。在线合并分析层我们选择MySQL数据库。可能有些人会问，为什么不使用当前如此流行的NoSQL产品呢？主要原因有以下两点。
MySQL更便于维护与备份等运维需求。
NoSQL不满足我们的一些分析型查询需求。
NoSQL产品虽然流行，但每种产品都还只适于某些特定的应用场景，很多听上去完美的理论目前暂时还仅仅只是听上去完美，实际用起来仍然存在各种各样的问题。所以我们选择了更适合于我们的MySQL作为在线合并分析层的数据库。
图3 推荐引擎整体架构
整个架构的数据流，如图3所示。
前端应用产生用户的浏览日志、购买日志、搜索日志以及用户及产品属性数据进入。
通过文件日志收集程序以及基于MySQL开放复制协议所定制的数据同步工具（注：在我的个人网站上有介绍：http://isky000.com/database/mysql-replication-extend）将数据同步至离线分析系统中。
通过离线任务的统计分析，得出会员的各种喜好属性，并将之与产品属性进行关联分析，得出一个用户产品倾向性关联结果，然后再通过应用程序定期从离线分析系统将上述分析结果写入在线合并分析数据库中。
推荐引擎根据前端应用（如Search）传入的用户当前运行时操作属性，与在线合并分析数据库中属性进行合并（Merge），再过滤（Filter）。
前端应用从推荐引擎处获取Merge与Filter之后的数据，再在前端页面上完成展现。
以上就是整个推荐引擎的数据流架构方案，乍一看也没有太多特别的内容，但在实际实施过程中，会遇到以下几个难点。
各种日志传输到离线分析系统，如何做到尽可能实时并不影响在线系统。
这个难点，我们首先在每一个页面部署监测点，通过请求一个gif图片来获得用户的各种浏览信息，并存入到MySQL数据库，交易相关的数据自然也会有在数据库中进行存储。然后使用通过扩展MySQL复制协议而实现的日志解析合并程序，实时解析 MySQL日志，再将其以我们需要的格式传输至离线分析系统中进行分析运算。
如何将用户的运行时操作属性与我们的离线分析结果进行Merge及Filte。
这个难点，实际上在6月刊的《程序员》杂志对麦包包首席架构师盛国军的采访稿中，已经有了相应的介绍。我们主要使用了基于用户（User）、商品（Item）、话题（Topic）以及曝光（Exposure）这四种协同过滤技术，来实现推荐算法。
总的来说，数据量的增长，以及分析需求的越来越复杂，将会对互联网公司的数据处理能力提出越来越高的要求、越来越大的挑战。但每一个场景都有其特性，充分分析其数据特性，将合适的软件用在合适的场景下，才能更好地解决实际问题。
接着上一篇 MySQL 数据库性能优化之缓存参数优化 ，这是 MySQL数据库性能优化专题 系列的第二篇文章：MySQL 数据库性能优化之表结构
很多人都将 数据库设计范式 作为数据库表结构设计“圣经”，认为只要按照这个范式需求设计，就能让设计出来的表结构足够优化，既能保证性能优异同时还能满足扩展性要求。殊不知，在N年前被奉为“圣经”的数据库设计3范式早就已经不完全适用了。这里我整理了一些比较常见的数据库表结构设计方面的优化技巧，希望对大家有用。
由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作 IO 的时候是以 page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。
数据类型选择
数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。
我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题：
数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。
字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。
时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
ENUM & SET：对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。
LOB类型：强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。
字符编码
字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。
纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间
如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费
MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率
适当拆分
有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。
当我们的表中存在类似于 TEXT [...]
