一个Web报表项目的性能分析和优化实践(四)：MySQL建立索引,唯一索引和组合索引

新风花雪月

　　先大致介绍下项目的数据库信息。
　　数据库A：主要存放的通用的表，如User、Project、Report等。
　　数据库B、C、D：一个项目对应一个数据库，而且这几个项目的表是完全一样的。
　　数据库表的特点
　　A中的表：数据量几乎都比较小，比如User表中用户数，顶多也就几百上千。
　　B中的表：X/Y/Z 3张表几乎是确定的，Data表中的数据量比较大，几千万到上亿。
　　周期性的会加入一大批数据，比如，每月末增加几百万条数据。
　　即一般情况下，B中的表只有查询操作，而且特别是Data查询频繁且数据量很大。
　　建立索引
　　1.为所有的表建立了唯一索引，索引字段是主键id。
　　2.考虑到数据库A中表的数据量很小，暂时没有建立组合索引。
　　如有可能，对频繁查询的表和字段，后期尝试加入组合索引。
　　3.对Data表建立组合索引。
　　频繁查询的一条SQL语句
　　select from Data where projectId=? and (inputVersion in (201)) and (sideId in (10001)) and (breakId in (?)) and (periodId in (?)) order by id desc;
　　建立组合索引的语句
ALTER TABLE Data ADD INDEX data_query_index (projectId,inputVersion,sideId,breakId,periodId);
　　建立索引之前，需要花费2.796秒。
　　建立索引之后，只需要0.136秒。
　　可以说是，大幅度提升了查询效率。
　　索引的弊端
　　随之而来的问题：如果已经建立了索引，那么批量增加数据的时候，会特别慢。
　　一种较快的方法是:批量插入数据之前，先删除索引，提高批量插入数据的效率。
然后，再重新建立索引，提高查询效率。
　　1000万条记录，重建5个字段的组合索引需要2到3秒。
　　重建索引的问题是，这个过程中，查询会比较慢。
　　应对之策：导入数据，重建索引 应该选择 晚上/凌晨等用户较少使用系统的时间段。
　　一个建议
用 explain sql;
可以分析sql语句的执行情况，进而对sql语句进行优化。
　　天下武功，唯勤不破
　　性能优化，以前只是看过一些书，没啥实践经验。
　　最近项目需要由我来进行优化，只好硬着头皮一点点去实践。
　　网上搜资料、请教同事、请教大牛。
　　周末再多看看书，认真复习和学习Linux、MySQL、Tomcat、Redis等一大堆，先侧重系统优化。

　　相关阅读
　　一个Web报表项目的性能分析和优化实践(一)：小试牛刀,统一显示SQL语句执行时间
　　一个Web报表项目的性能分析和优化实践(二)：MySQL数据库连接不够用(TooManyConnections)问题的一次分析和解决案例
　　一个Web报表项目的性能分析和优化实践(三) :提高Web应用服务器Tomcat的内存配置，并确认配置正确
　　
原文参见：http://FansUnion.cn/articles/3026