MYSQL数据库优化使用索引 [英] MYSQL database optimization using indexing
问题描述
我正在致力于 LearnBiz模拟，这是一家自费资助的初创公司，可以模拟管理教育。
（a）我们的网站目前约有16k人。目前，数据库的大小是30mb，总共大约90个表，并且每个表可以具有在5到50列之间的任何地方。我们在数据库中的所有表，反复有新的插入，删除或更新行。但不会添加任何列。它使用索引技术创建任何问题吗？有几个其他论坛和视频，表明即使插入或删除一行将使索引无用！
（b）我们的网站上的最大工作是mysql查询其中一些处理代码行在200-800线之间。我们几乎95％的查询都有多个相等的where子句，用于指定所需的行。如果我理解正确，那么使用索引将帮助我们加快这个过程多倍快？
（c）有一些模拟，每分钟处理许多参与者的数据。对于这样的模拟，目前我们不能一次满足甚至50个参与者。 （d）我们目前的服务器是一个带有GoDaddy的VDS（每年花费大约400美元）。为了使我们的网站更快，我们有必要转向DDS（每年花费大约3000美元）或者获得类似TokuDB的东西吗？有没有办法判断当前系统的用户容量，即服务器+数据库+编码？
视频是毫无价值的，毫无意义地重复半真值（是的，使用索引时，有一个开销，这只是表现的增益通常胜过它很多次）。
（b）在大多数情况下，尽管要小心创建实际有用的索引。 MySQL文档有一个关于如何操作的整章 （通常您需要查看整个优化
（c）确保你的基准测试没有模拟过多的流量，例如50个真实用户每秒不会生成50个连接，你的性能应该在实现索引和优化你的查询后增加
（d）如果你的数据库服务器没有正确配置，没有更好的资源会有帮助？你允许MySQL使用足够的内存来保存内存中的表等等）
总结：阅读你的MySQL服务器的基本配置，有效利用您的资源（默认值通常不够好），并且还要看看手册中的优化章节。
I am working on LearnBiz Simulations, a self-funded start-up which makes simulations for the domain of management education.
(a) Our website is currently catering to about 16k people. Currently, size of database is 30 mb with a total of about 90 tables and each table may have anywhere between 5 to 50 columns. All our tables in the database, repeatedly have new rows of insertions, deletions or updations. But no columns are ever added. Does it create any issues with employing indexing techniques? There were a couple of other forums and videos which suggests that even inserting or deleting a row will render indexing useless!
(b) The maximum work on our website is mysql queries with some processing of lines of code ranging between 200-800 lines. Almost 95%+ of our queries have multiple equality where clause, to pin-point the row required. If I understand correctly, then employing indexing will help us speed up the process multiple times faster?
(c) There are some simulations which process data of many participants every minute. For such simulations, currently we are not able to cater even 50 participants at a time. Would employing indexing help such simulations bear many more users?
(d) The server we have currently is a VDS with GoDaddy (costs around $400 annually). To make our website faster, would it make sense for us to shift to a DDS (costing around $3000 annually) or get something like TokuDB? Is there a way to judge user capacity of current system, i.e. server + database + coding?
(a) Those forums and videos are worthless, mindlessly repeating half-truths (yes, there is an overhead when using indexes, it's just that the performance gain usually outweighs it many times)
(b) In majority of cases, although be careful to create indexes that are actually useful. MySQL documentation has a whole chapter on how to do that (in general you will want to have a look at the entire Optimization chapter too
(c) Make sure your benchmark is not simulating too heavy traffic. 50 real users at a time will not generate 50 connections each second for example. Again, you performance should increase after implementing indexes and optimizing your queries
(d) No amount of better resources will help if your database server is not configured properly (do you use query cache? do you allow MySQL to use enough memory to keep tables in memory? etc.)
To sum up: read about basic configuration of your MySQL server, so that it can utilize your resources effectively (defaults are usually not good enough), and also have a look at the Optimization chapter in the manual.
这篇关于MYSQL数据库优化使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
