blocks|key|1896688|text|你的场景是什么--+OLAP还是OLTP+(“搜索引擎”听起来查询比写新数据更频繁……)？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1896689|我遇到过类似的情况，我在DTA建议的基础上添加了大量索引，结果发现由于写入速度减慢，我的ETL进程变慢到了停顿状态。除了尝试不同的东西并找到最适合我情况的平衡之外，我没有任何经验可以遵循。|1896690|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

What's your scenario - OLAP or OLTP ("Search engine" sounds like querying is more frequent than writing new data...)? 

I had a similar situation where I added a load of indexes on the basis of DTA recommendations only to find that my ETL processes slowed down to a standstill, due to slow downs on writes. There wasn't any rule-of-thumb I could follow other than trying different things out and finding the balance that best fit my situation.

blocks|key|593940|text|总是针对查询进行优化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|593941|即使是“密集写入”也不会超过15%25的写入(我在某处读到的)。例如：|593942|593943|UPDATE..WHERE是select/search，因为带有唯一约束的WHERE|unordered-list-item|593944|INSERT需要检查duplicates|593945|DELETE的约束要求检查所有子表FK列|593946|593947|我对我们的联机事务处理系统的估计是最低95%25的阅读率(每天系统5次million%2B插入)到超过98%25的其他系统|593948|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|V|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

Always optimising for querying.

Even "write intensive" is no more then 15% write (I read somewhere). For example:

<ul>
<li>UPDATE..WHERE is a select/search because of the WHERE</li>
<li>INSERT with a unique constraint needs a check of the constrainst for duplicates</li>
<li>DELETE requires a check of all child tables FK columns</li>
</ul>

My back of the envelope estimates for our OLTP systems is minimum 95% read (5 million+ inserts per day system) to over 98% for others

blocks|key|655753|text|一种常见的策略，并不是在所有情况下都适用/实用，是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|655754|“输入网关”方法|offset|length|style|BOLD|655755|使用这种方法，所有的实时插入都是在一个表(或几个表)中完成的，而为搜索应用程序提供服务的是另一组表(具有许多索引和其他面向搜索的优化)。以固定(或可变/基于加载)的时间间隔，将输入表中的行传送到应用程序表，并从输入表中删除，以便保持该输入网关精简，这意味着不需要太多索引。|655756|这种方法的主要缺点当然是在实时更新方面，应用程序数据的滞后。这种情况可以通过几种方式解决，通常是通过增加传输频率，或者让应用程序运行两次搜索/联合类型的搜索(导入“堆”中的搜索通常足够快，即使没有/只有很少的索引，因为它的大小更小)|655757|entityMap^0|0|0|8|0|0|R|2H|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

A common strategy, not applicable/practical in all cases, is the 
"input gateway" approach 
With this approach, all the [real-time] inserts are done into one table (or a few tables), and the serving the [search] application is supported from another set of tables (with many indexes and other search-oriented optimizations). At fixed (or variable / based on load) intervals, rows from the input table(s) are transferred to the application tables, and deleted from the input table(s), as so to keep this input gateway lean and mean an without much need for indexes.

The main drawback of this approach is of course that the application data lags behind in terms of real-time updates. This situation can be addressed in several ways, typically by either increasing the frequency of transfers, or by having the application run two searches / UNION-type searches (the search in the import "heaps" is typically fast enough, even with no / few indexes, owing to the smaller size)

I'm working on a personal project (Search engine) and have a bit of a dilemma.
At the moment it is optimized for writing data to the search index and significantly slow for search queries.

The DTA (Database Engine Tuning Adviser) recommends adding a couple of Indexed views inorder to speed up search queries. But this is to the detriment of writing new data to the DB.

It seems I can't have one without the other!

This is obviously not a new problem.
What is a good strategy for this issue?

SQL Database dilemma : Optimize for Querying or Writing?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在做一个个人项目(搜索引擎)，有点进退两难。目前，它针对向搜索索引写入数据进行了优化，而对于搜索查询，它的速度明显变慢。DTA (数据库引擎优化顾问)建议添加几个索引视图，以加快搜索查询速度。但这不利于向DB写入新数据。看起来我不能没有另一个！这显然不是一个新问题。解决这个问题的好策略是什么？

问SQL数据库困境:针对查询还是写入进行优化？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL数据库困境:针对查询还是写入进行优化？EN