应用分布式索引提高海量数据查询性能

摘要

In the field of telecommunications precision marketing and ad-hoc query, there are a lot of random queries scenarios on one or more wide-tables (which have more than 50 fields). In the traditional system (the queries are performed on the database directly), the query response time can be optimized less than a few seconds to tens of seconds when the database records size is under 10 million. When the data size reaches tens of millions, hundreds of millions or even more than one billion records, whatever optimization including changing indexing mechanism are unable to meet the second-level concurrency query requirements. In the new query system, we introduce the Solr distributed index layer to solve these problems. The layer will index the database records firstly and queries will access the Solr index layer and not perform on the database directly, therefore, the performance will be improved highly. After a comparison of the two processing patterns in same environment, for the data of 50 million, 20 per concurrent access query scenario, the traditional accessing queries all are timeout; while the other’s queries can be returned within 2 seconds and all are success.%在电信领域的精准化营销、即席查询业务中，存在着大量针对一张宽表或几张宽表(超过50字段)的随机查询场景。传统处理模式(直接查询数据库)在数据量不大(<1000万)时，查询响应时间可优化到几秒至数十秒级，而当数据量到达几千万、上亿甚至十亿记录以上时，此处理模式无论如何优化或更改索引机制，都无法满足秒级并发查询要求。新的处理模式通过引入分布式 Solr 索引层解决上述问题。索引层预先对数据库记录建立索引，查询不再作用于数据库而直接查询索引层，如此，可大幅提高查询性能。经过对两种处理模式的对比验证，在相同环境下，数据量到达5000万，每秒20并发访问的宽表查询场景，传统处理模式的查询全部超时失败，而使用分布式索引层的查询可以在2秒以内返回，查询全部成功。