注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

中吴南顾惟一笑

成功法则就是那19个字

 
 
 

日志

 
 

Using Index or NOT  

2010-11-01 09:18:12|  分类: dbms |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Wiley - Beginning MySQL

Despite the clear advantages of using indexes, there are some drawbacks. For example, indexes can require a great deal of storage. The larger the tables and greater the number of indexes, the more storage you need to hold those indexes. You must allow for file size and potential growth whenever you implement an index. Another disadvantage to indexes is that, while they speed up data retrieval, they can slow down data inserts and deletes, as well as updates to columns that are indexed. Any change made to rows in an indexed table must also be made to the index(unless the change is an update that doesn't affect the value in the index).

Despite these drawbacks, indexing provides the most beneficial tool for improving the performance of your SELECT statements. You should not, however, index every column in a table. The following list provides several guidelines that you can use in determining when to implement indexing:

Index columns that appear in search conditions. As a general rule, you should consider defining an index on any column that you commonly use in WHERE, GROUP BY, or HAVING clauses. Because these columns define the limitations of a query, they are good candidates for improving performance because they allow MySQL to identify quickly which rows should be included in a search and which should not.

Index columns that appear in join conditions. Index any columns that appears in a join condition. Because join conditions are often based on foreign key columns that reference primary key columns, MySQL creates the indexes automatically when you define the primary keys and foreign keys.

Do not index columns that appear only in the SELECT clause. If a column appears in the SELECT clause of a SELECT statement, but does not appear in WHERE, GROUP BY, or HAVING clauses, you usually shouldn't index these columns because indexing them provides no performance benefit but does require additional storage. Indexing columns in the SELECT clause provides no benefit because the SELECT clause is one of the last parts of a SELECT statement to be processed. MySQL conducts searches based on the other clauses. After MySQL identifies which rows to return, it then consults the SELECT clause to determine which columns from the identified rows to return.

Do not index columns that contain only a few different values. If a column contains many duplicated values, indexing that column provides little benefit. For example, suppose that your column is configured to accept only Y and N values. Because of the way in which MySQL accesses an index and uses that index to locate the rows in the tables, many duplicated values can actually cause the process to take longer than if no index is used. In fact, when MySQL finds that a value occurs in more than 30 percent of a table's rows, it usually doesn't use the index at all.

Specify prefixes for indexes on columns that contain large string values. If you're adding an index to a string column, consider defining a prefix on that index so that your index includes only part of the entire values, as they're stored in the table. For example, if your table includes a CHAR(150) column, you might consider indexing only the first 10 or 15 bytes, or whatever number provides enough unique values without having to store the entire values in the index.

Create only the indexes that you need. Never create more indexes than you need. If a column is rarely used in a search or join condition, don't index that column. You want to index only those columns that are frequently used to identity the rows being searched.
  评论这张
 
阅读(521)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017