业务层面优化
这是一次值得纪念的优化，值得回忆的内容非常丰富，虽然这个SQL本身并不复杂，几乎是一个相对规范式的SQL，所以，这次优化的重点并不是SQL的改写，而更多的是业务需求、物理模型的优化。在长达3个月，历经5个版本的优化过程中，也不泛优化与开发、功能与性能、测试与开发间关系的微妙变化，其间各方的博弈也耐人寻味。
事出有因
系统存在一个功能，“编辑日志查询”，顾名思义就是查询被修改的历史记录（这个功能的存在性有待商榷）。功能刚上线的时候，由于数据量少，相安无事，使用甚欢。由于基本上都是大批量的编辑，导致了日志数据量急剧增长，每天的增量大概在100万左右，两个月后，数据量无情的增长到了6000万，性能隐患也日益凸显，终于换来了一封来自业务用户的邮件，于是我也收到了一个需要优化的SQL，如下：
大胆假设，小心求证
我做SQL优化有个习惯，拿到SQL的时候，并不急于去看执行计划，而是先要快速浏览一遍SQL，一看结构，二看内容，因为我坚信80%的性能问题是由于SQL写法不当导致的，比如冗余的对象访问、冗余的关联条件、冗余的过滤条件、无意义的DISTINCT\UNION\GROUP\ORDER、自定义函数等等这些常见的问题。这个SQL也不例外，很明显TimeZone_Date_Translator这个自定义函数可能会是性能瓶颈。
为了验证自己的判断，我将函数去掉后，再执行，果然性能得到了质的提升。因为符合了性能指标，我也没有深入的分析原因。
问题严重化
逝者如斯，RP_PLAN_LOG_T表的数据量日复一日的增加，一个月后，又收到了一封来自一线业务用户的邮件，这次的邮件内容措辞相对上一封，要严厉了很多，大意是：该功能的性能问题已经严重影响到一线业务效率，查询数据居然要等待30s之久，更有甚者直接超时报错（120s），因此强烈要求该功能的性能要在5s内。
这封邮件犹如一颗巨石，在平静的水面炸开了锅。
SQL还是那个SQL，我在PL SQL里面执行，平均耗时在10秒内，也没有邮件中说的30s之久呀。难道是执行计划的走偏导致的？因为这是动态拼凑的SQL，SQLID变化无常，所以分析当时执行计划是否走偏的难度很高。
办案讲究的是犯罪现场，而现在“犯罪现场”肯定是不存在的了，那能否可以重现“犯罪现场”呢？虽然此种方案也并不能支撑“执行计划走偏”的原因分析，但是至少可以为我们拓展思维：会不会是查询条件变化？会不会是网络原因？
于是，根据邮件里面零碎的信息，我们在生产环境的功能界面上重现了“现场”，但是结果并没有“犯罪”，也就是说并没有出现邮件中说的达到30s之久。因为是根据只言片语拼凑的“现场”，所以可能存在模拟失真的可能性。
为了模拟的真实性，我们联系上了“案发”当事人，在询问了“案发”条件后，才得知：原来用户是在选定某个“项目编码”下查询条件下检索了近一年的日志数据。而由于该日志功能才启用了不到4个月，也就是说是查询了某个项目下所有的日志数据。根据用户提供的信息，我们在PL SQL中执行了SQL，确实达到了30s之久，结果数据集的量也达到了500万+。
至此，我们可以得出这样的结论：本次查询的性能问题的原因归结于数据量，基表的数据量（近一个亿）及结果集数据量（500万+）。
那么，如何解决呢？一方面是如洪水般迅猛增涨的基表数据，另一方面是超大的结果集返回。针对这两个问题，我给出了如下的解决方案：
-
引入表分区技术，即将基表RP_PLAN_LOG_T表按照operate_time字段按月分区，以实现数据的分区命中
-
为实现数据的分区命中，在查询界面将operate_time作为必选条件，而且尽量做到不跨月
-
为配合1、2两点，创建project_number和operate_time的联合索引
事情往往是从扯皮开始的
现在，问题来了，这些事情谁来落实呢？先说第二点吧，这是改需求呀，需要与BA协商，找到了BA，BA说自己也做不了主呀，还得要跟业务用户去确认，这一来二往的，开发人员性子急，就不耐烦了：还是不改了吧，太麻烦了。
再说第一点，数据分区的责任定位也不明确，开发人员说这需要DBA来做，DBA又说这属于应用范畴，理应开发人员写脚本，他们负责执行就好了。扯来扯去，最后又把BA扯出来了：这个事情需要时间来做，BA应该下个需求单，有了需求单，就能评估人天，这样有人天了，自然就有人来做了。
而BA也在为自己辩护：这属于纯技术范畴，与业务需求无关，说白了是当初在设计模型的时候就该考虑分区技术，因此这个需求单不能下。
最后，开发、BA、DBA、用户及我达成协议：分区由DBA来实施，不过需要在下个版本实施；用户确认可以将operate_time作为必选条件，并且尽量做到压缩查询周期；开发人员在project_number和operate_time字段上创建联合索引。
自查的勇气
为了避免用户由“怨责”转变成“投诉”，项目组对该功能的性能也重视起来，要求性能测试人员严格把关，如果性能超过5S就不放行。这样，开发人员就开始对该功能的性能自检自查，测试人员也在积极的准备数据做性能验证。我的责任还是对SQL进行分析并优化。
第一次是粗略的过了一遍SQL，发现了TimeZone_Date_Translator自定义函数；第二次直接是优化了对象模型；这一次才是真正的正面又深入的打量这个SQL，其中一段代码引起了我的兴趣：
这段代码是获取字段subtitlename值的标量子查询，从代码看，该值的获取逻辑如下：
以operate_type为“其他”为例，在展开之前，我们先看看相关的模型结构。
RP_PLAN_LOG_T的模型如下：
该模型中有个BUSINESS_ID的字段，这个字段存放业务ID：属性类型（即OPERATE_TYPE=1）对应的是RP_PLAN_EXTENSION_T.PLAN_EXTENSION_ID,其他类型（即operate_type in(2,3,4,5,7,8,9)）对应的是RP_TASK_T.TASK_ID,Site Owner(即operate_type = 6)为-100，所以，在获取字段subtitlename值的时候需要根据operate_type的值分别到不同的表中获取对应的name值。
我们再看看RP_TASK_T和RP_PLAN_EXTENSION_T的模型结构
先看RP_TASK_T表模型：
模型中TASK_ID是主键，但是如果你按照常规理解TASK_ID与TASK_NAME存在一对一的关系的话，那你就错了，这也是玄机所在。在RP_TASK_T表中，TASK_NAME与TASK_ID是一对多的关系，即同一个TASK_NAME对应多个TASK_ID。
事实上，TASK_NAME作为一个实体，也是存在一个独立的模型，即SDS_ACTIVITY_T，其结构如下：
在这个模型里，ACTVITY_NAME就是对应RP_TASK_T中的TASK_NAME，并且该模型里面的ACTVITY_ID与ACTVITY_NAME在同一个project_number下是一一对应的。
看到这里就清晰了，原来，为了获取subtitlename字段值，我们还可以从SDS_ACTIVITY_T表中拿ACTVITY_NAME字段，如果在RP_PLAN_LOG_T表中存放了ACTVITY_ID字段值的话。
也就是说，目前operate_type in(2,3,4,5,7,8,9)的情况下，有两种途径可以获取到subtitlename字段值。而这两种途径的优劣在哪里呢？我们对比下rp_task_t和SDS_ACTIVITY_T表的数据量就知道了：
| 表 | 存量 | 增量 |
| RP_TASK_T | 2千万+ | 大 |
| RP_TASK_HIS_T | 420万+ | 大 |
| SDS_ACTIVITY_T | 11万+ | 小 |
由此可见，两种途径孰优孰劣显而易见。
无独有偶，operate_type = 6的情况与此同出一辙，也是存在另一种通过小数据量的表获取subtitlename的途径。
正当其时，测试人员提了一个性能BUG单，内容是：当选择了subtitlename查询条件时，查询响应非常慢，达到了20s之久。这也印证了我的分析：当前获取subtitlename字段值是一个潜在的性能瓶颈。
说是潜在的，原因是如果该字段不作为查询条件，则不会触发，因为该SQL的结果集是分页的，每次只返回15条数据，而作为标量子查询，也就是执行15次而已；但是，一旦作为了查询条件，则执行的次数则是巨大的，而标量子查询中的表都是千万级的大表。所以就成为了严重的性能瓶颈。
至此，我以BUG单为契机，适时的提出了优化方案：将business_id拆分成两个字段，分别存储ACTVITY_ID和ATTRIBUTE_ID。
看热闹的不嫌事大
当我提交这个方案时，开发人员甚为激动，倒不是因为有了方案而激动，而是因为这个方案于他们而言有点不太靠谱，他给出了如下理由：
-
增加字段，这是伤筋动骨之举，只能在万不得已的情况下才能实施；
-
该表的数据并非一个来源，表结构改动后，会涉及到多个来源的代码同步修改；
-
目前RP_PLAN_LOG_T表的数据量已经上亿了，增加字段，就意味着需要对历史数据进行初始化，动作太大；
-
现在已接近版本上线日，如此大动作在短时间内完成，风险太大。
其实，说白了，就是这个版本的工作计划无法承受该方案，所以站在他们的立场，目前正在如火如荼进行版本的功能开发，这是优先保障的，而我的方案被当成了：看热闹不嫌事大；但是如果是在原有模型的基础上，我实难完成优化目标。
时间一天一天过去了，待到上线前一天，这个BUG单依然open着，而按照上线变更条例，如果有BUG单没有close掉，是不能上线的。最后关头，在测试人员的紧逼之下，开发的SE动用了“特权”：将该BUG单移至到下个版本。尽管测试人员强烈反对，但是功能优先性能的大条件不容挑衅。
断腕的决心
测试人员上个版本吃了哑巴亏，在版本上线后，第一时间盯着开发人员优化该功能。毕竟头上悬着业务用户这把利剑，开发人员也不敢马虎，也投入了人力优化。方案很简单，但是对于整个功能代码而言，涉及到的内容就远比在表上增加两个字段复杂得多，从如下邮件截图可窥一斑：
总结
该功能的性能优化在经历了自定义函数、分区、索引、业务方案、模型方案后，性能总算是稳定了下来，但是仍然留给了我很多疑问：
-
模型设计初期是否能考虑全面？是否能做到一步到位？
-
日志查询的意义何在？一次性查询十万百万的数据意义何在？
-
能否有一套成熟的方案来应对查询条件的动态化？查询条件是动态组合的，显然索引不可能动态组合。
数据库层面优化
-
数据库优化这个课题较大，可分为四大类：
-
主机性能
内存使用性能
网络传输性能
SQL语句执行性能
-
my.cnf文件参数优化
-
Copy
[mysqld] port = 3306 serverid = 1 socket = /tmp/mysql.sock skip-locking #避免MySQL的外部锁定，减少出错几率增强稳定性。 skip-name-resolve #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ back_log = 384 #back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。 key_buffer_size = 256M #key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！ max_allowed_packet = 4M thread_stack = 256K table_cache = 128K sort_buffer_size = 6M #查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。 read_buffer_size = 4M #读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 myisam_sort_buffer_size = 64M table_cache = 512 thread_cache_size = 64 query_cache_size = 64M # 指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的 情况；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓 冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。 tmp_table_size = 256M max_connections = 768 #指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。 max_connect_errors = 10000000 wait_timeout = 10 #指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。 thread_concurrency = 8 #该参数取值为服务器逻辑CPU数量*2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4*2=8 skip-networking #开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！ table_cache=1024 #物理内存越大,设置就越大.默认为2402,调到512-1024最佳 innodb_additional_mem_pool_size=4M #默认为2M innodb_flush_log_at_trx_commit=1 #设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1 innodb_log_buffer_size=2M #默认为1M innodb_thread_concurrency=8 #你的服务器CPU有几个就设置为几,建议用默认一般为8 key_buffer_size=256M #默认为218，调到128最佳 tmp_table_size=64M #默认为16M，调到64-256最挂 read_buffer_size=4M #默认为64K read_rnd_buffer_size=16M #默认为256K sort_buffer_size=32M #默认为256K thread_cache_size=120 #默认为60 query_cache_size=32MCopy
当order by 和 group by无法使用索引时，增大max_length_for_sort_data参数设置和增大sort_buffer_size参数的设置
SQL语句拆分简单sql
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20
可以这样查询： select id from t where num=10 union all select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描： select id from t where name like ‘%abc%’ 若要提高效率，可以考虑全文检索。
7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num
可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num .应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100
应改为: select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where substring(name,1,3)=’abc’–name以abc开头的id select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id
应改为: select id from t where name like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会 被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(…)
13.很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b)
用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有 字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免大事务操作，提高系统并发能力。
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
1764
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
