使用索引的MYSQL数据库优化[英] MYSQL database optimization using indexing



我正在研究 LearnBiz Si​​mulations ,一家自筹资金的初创企业,为域名制造了模拟管理教育.

(a)我们的网站目前正在迎合约16,000人.当前,数据库的大小为30 MB,总计约90个表,每个表的尺寸可能在5至50列之间.我们在数据库中的所有表都反复具有新的插入,删除或更新.但是从未添加任何列.使用索引技术会造成任何问题吗?还有其他几个论坛和视频表明,即使插入或删除一行也会使索引无用!



(d)我们目前拥有的服务器是带有GoDaddy的VD(每年耗资约400美元).为了使我们的网站更快,我们转移到DDS(每年耗资约3000美元)或获得Tokudb之类的东西是有意义的吗?有没有办法判断当前系统的用户容量,即服务器 +数据库 +编码?




(b)在大多数情况下,尽管要谨慎地创建实际上有用的索引. MySQL文档具有整章如何做那个 (通常,您需要看看整个优化章节也






总结:阅读有关MySQL Server的基本配置,以便它可以有效地利用您的资源(默认值通常不够好),还可以查看手册中的优化章节.


关于索引问题,索引不仅是可取的,而且需要 "加快"事物.据我了解(用"外行"术语),索引的功能是加快表格中数据的搜索和恢复.


  1. 唯一地识别每个表上的每一行(毕竟,主要键是索引)
  2. 分类索引(即使数据不是)
  3. 加速搜索和过滤器:索引使数据的恢复更快,因为它"保留"数据将数据放置在表中(它"查明"您要恢复的数据).此外,数据库引擎更容易过滤数据(与 crambleed 数据相比,它总是更快,更简单地过滤排序数据)
  4. 在使用相关表时优化数据的恢复方式:必须索引每个外键,以加快涉及主要外国密钥关系的查询


  • 每个主键都是索引的(明显一个:主键必须是唯一的,而不是null)
  • 必须索引每个外国密钥(为了使主要外国密钥关系有效)
  • 必须索引我需要执行的每个数字或日期字段.也就是说,我尝试避免double(或任何其他浮点数字类型)字段,因为它们是用来存储值不得搜索的一般性.
  • 我需要执行搜索的每个char或varchar必须索引.尝试避免在text字段上索引,因为它们可以在其中保持非常大的价值.
  • 避免索引二进制(blob)字段...这是没有意义的
  • 不要属于索引一切的诱惑.花点时间确定必须索引哪些字段,哪些字段不得被索引.



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.


Regarding the indexes questions, an index is not only desirable, but it is needed to "speed up" things. As I understand it (in "layman" terms), the function of an index is to speed up searches and recoveries of data inside a table.

Reasons to use indexes:

  1. Identify uniquely each row on each table (the primary key is an index, after all)
  2. The indexes are sorted (even if the data is not)
  3. Speed up searches and filters: An index makes the recovery of data faster, since it "holds" the placing of the data in the table (it "pinpoints" the data you want to recover). Also it makes easier to the database engine to filter the data (it is always faster and simpler to filter sorted data than scrambled data)
  4. Optimize the way data is recovered when using related tables: Every foreign key must be indexed in order to speed up the queries that involve primary - foreign key relations

Some "thumb rules" I use to decide which fields need to be indexed:

  • Every primary key is indexed (the obvious one: a primary key must be unique and not null)
  • Every foreign key must be indexed (in order to make primary - foreign key relations efficient)
  • Every numeric or date field on which I need to perform searches must be indexed. That said, I try avoid double (or any other floating point numeric type) fields to be indexed, since they are generaly used to store values not meant to be searched.
  • Every char or varchar field on which I need to perform searches must be indexed. Try to avoid indexes on text fields, since they can hold very big values in them.
  • Avoid indexing binary (blob) fields... it makes no sense
  • Don't fall in the temptation of indexing everything. Take your time to decide which fields must be indexed and which fields must not be indexed.