blocks|key|37291|text|很难同时获得快速读取和写入。要获得快速读取，您需要添加索引。要获得快速写入，您需要使用较少的索引。而且，为了让两者都更快，它们不能相互锁定。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|37292|根据您的需要，一种解决方案是拥有两个数据库。将新数据写入您的实时数据库，并且偶尔当它处于安静状态时，您可以将数据同步到另一个数据库，在那里您可以执行查询。这种方法的缺点是您读取的数据会有点旧。这可能是问题，也可能不是问题，这取决于你需要做什么。|37293|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|$]]

It is difficult to get both fast reads and writes simultaneously. To get fast reads you need to add indexes. To get fast writes you need to have few indexes. And to get both to be fast they must not lock each other.

Depending on your needs, one solution is to have two databases. Write new data to your live database and every so often when it is quiet you can synchronize the data to another database where you can perform queries. The disadvantage of this approach is that data you read will be a little old. This may or may not be a problem depending on what it is you need to do.

blocks|key|3702663|text|每秒大约500次插入确实不是小菜一碟。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3702664|对于灵活的解决方案，您可能希望实现某种类型的分片。可能最简单的解决方案是预先将学校分成组，并将不同学校组的数据存储在不同的服务器上。例如，学校1-10的数据存储在服务器A上，学校11-20存储在服务器B上，等等。这几乎是无限可扩展的，假设来自不同学校的数据之间几乎没有关系。|3702665|此外，您可以尝试在问题上投入更多的马力，并投资于SSD驱动器的RAID，假设您有足够的处理能力，您应该可以。当然，如果它是一个巨大的数据库，SSD驱动器的容量可能不够。|3702666|最后，看看是否可以减少插入的次数，例如，通过非规范化数据库。比方说，不是将每个学生的出勤率存储在单独的行中，而是将整个班级的出勤率作为向量放在一行中。当然，这样的更改将严重限制您的查询能力。|3702667|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

~500 inserts per second is nothing to sneeze at indeed. 

For a flexible solution, you may want to implement some sort of sharding. Probably the easiest solution is to separate schools into groups upfront and store data for different groups of schools on different servers. E.g., data for schools 1-10 is stored on server A, schools 11-20 on server B, etc. This is almost infinitely scalable, assuming that there are few relationships between data from different schools.

Also you could just try throwing more horsepower at the problem and invest into a RAID of SSD drives and, assuming that you have enough processing power, you should be OK. Of course, if it's a huge database, the capacity of SSD drives may not be enough.

Finally, see if you can cut down on the number of insertions, for example by denormalizing the database. Say, instead of storing attendance for each student in a separate row put attendance of the entire class as a vector in a single row. Of course, such changes will heavily limit your querying capabilities.

blocks|key|2117184|text|我的建议是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2117185|2117186|构建您的应用程序轻量级。不要使用像活动记录这样的高级数据库抽象层。关于mysql+permformance.|unordered-list-item|2117187|Learn，关于mysql+replication.|2117188|Learn，关于load+balancing.|2117189|Learn，关于内存缓存，|2117190|都学到了很多。(memcached)|2117191|Hire管理员(具备良好的mysql知识)或web应用程序性能guru/consultant.|2117192|2117193|具体的策略取决于您的应用程序以及如何使用它。Mysql复制，可能适合也可能不合适(同样适用于前面提到的分片策略)。但这是实现一定程度缩放的一种相当简单的方法，因为它不会对应用程序设计产生太大影响。在内存缓存中，可以从数据库中保留一些负载，但它们需要一些工作来应用和一些权衡。最后，您需要全面了解如何在高负载下处理数据库驱动的应用程序。如果你有一个紧迫的最后期限，增加外部人力，因为如果没有经验，你不会在6周内做好这件事。|2117194|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|Z|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|10|8|@]|9|@]|A|$]]|$1|N|3|O|5|E|7|11|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

My laid back advice is:

<ul>
<li>Build you application lightweight. Don't use an high level database abstraction layer like Active Record. They suck at scaling.</li>
<li>Learn a lot about mysql permformance.</li>
<li>Learn about mysql replication.</li>
<li>Learn about load balancing.</li>
<li>Learn about in memory caches. (memcached)</li>
<li>Hire an administrator (with decent mysql knowledge) or web app performance guru/consultant.</li>
</ul>

The concrete strategy depends on your application and how it is used. Mysql replication, may or may not be appropriate (same applies for the mentioned sharding strategy). But it's a rather simple way to achive some scaling, because it doesn't impact your application design too much. In memory caches can keep away some load from your databases, but they need some work to apply and some trade offs. In the end you need a good overall understanding how to handle a database driven application under heavy load. If you have a tight deadline, add external manpower, because you won't do this right within 6 weeks without experience.

Hi Friends
i am using MySQL DB for one of my Product, about 250 schools are singed for it now, its about 1500000 insertion per hour and about 12000000 insertion per day, i think my current setup like just a single server may crash with in hours, and the read is also same as write, how can i make it crash free DB server, the main problem i am facing now is the slow of both writing and reading data how can i over come that,it is very difficult for me to get a solution.guys please help me..which is the good model for doing the solution?

how to do fast read data and write data in mysql?

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

嗨，朋友们，我正在使用MySQL DB为我的一个产品，大约250所学校现在是烧焦了它，它大约每小时1500000插入和大约每天12000000插入，我认为我目前的设置就像一个单一的服务器可能在几个小时内崩溃，读也是一样的写，我怎么才能使它崩溃无崩溃的DB服务器，我现在面临的主要问题是写入和读取数据的速度都很慢我如何克服...

问如何在mysql中快速读写数据？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在mysql中快速读写数据？EN