如何正确合理的建立MYSQL数据库索引

如何正确合理的建立MYSQL数据库索引
原文地址： http://lichuhui.javaeye.com/blog/298254

普通MySQL运行，数据量和访问量不大的话，是足够快的，但是当数据量和访问量剧增的时候，那么就会明显发现MySQL很慢，甚至down掉，那么就要考虑优化我们的MySQL了。其中优化MYSQL的一个重要环节就是为数据库建立正确合理的索引。

如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。也就是说索引可以大大减少数据库管理系统查找数据的时间。
索引有哪些优点？

1、  通过创建唯一性索引，可以保证数据库表中每一 行数据的唯一性。

2、  可以大大加快数据的检索速度，这也是创建索引 的最主要原因。

3、  可以加速表和表之间的连接，这在实现数据的参 考完整性方面特别有意义。

4、  在使用分组和排序子句进行数据检索时，同样可 以显著减少查询中分组和排序的时间。
索引有哪些缺点？

1、  创建索引和维护索引要耗费时间，这种时间随着 数据量的增加而增加。

2、  除了数据表占数据空间之外，每一个索引还要占 一定的物理空间，如果要建立聚簇索引，需要的空间就会更大。

3、  当对表中的数据进行增加、删除和修改的时候， 索引也要动态的维护，这样就降低了数据的维护速度。
索引有哪些类型？

1、 普通索引

这是最基本的索引类 型，而且它没有唯一性之类的限制。

2、 唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

3、主键

它是一种特殊 的唯一索引，不允许有空值。

4、全文索引

MySQL从3.23.23版开始支持全 文索引和全文检索。
单列索引和组合索引：

单列索引就是 把索引单独建立在一个字段上。

组合索引复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。
建立和使用索引有哪些注意事项：

1、           索引要建立在经常进行select操作的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2、           索引要建立在值比较唯一的 字段上。这样做才是发挥索引的最大效果。，比如主键的id字段，唯 一的名字name字段等等。如果索引建立在唯一值比较少的字段，比如性别gender字段，寥寥无几的类别字段等，刚索引几乎没有任何意义。

3、           对于那些定义为text、 image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

4、           当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

5、           在WHERE和JOIN中出现的列需要建立索引。

6、           在以通配符 % 和 _ 开头作查询时，MySQL索引是无效的。但是这样索引是有效的：select * from tbl1 where name like 'xxx%'，所以谨慎的写你的SQL是很重要的。



用一个例子详细说明单列索引与组合索引的区别及索引使用中的一些细节

建一个表：
    CREATE TABLE myIndex (

i_testID INT NOT NULL AUTO_INCREMENT,

vc_Name VARCHAR(50) NOT NULL,

vc_City VARCHAR(50) NOT NULL,

i_Age INT NOT NULL,

i_SchoolID INT NOT NULL,

PRIMARY KEY (i_testID)

);

   在这10000条记录里面7上8下地分布了5条vc_Name="erquan"的记录，只不过city,age,school的组合各不相同。
  来看这条T-SQL：
    SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑 州' AND i_Age=25;

    首先考虑建单列索引：
    在vc_Name列上建立了索引。执行T-SQL时，MYSQL很快将目标锁定在了vc_Name=erquan的5条记录上，取出来放到一中间 结果集。在这个结果集里，先排除掉vc_City不等于"郑州"的记录，再排除i_Age不等于25的记录，最后筛选出唯一的符合条件的记录。

    虽然在vc_Name上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在vc_City和i_Age分别建立的单列索引的效率相似。

    为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将vc_Name,vc_City,i_Age建到一个索引里：
    ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--注意了，建表时，vc_Name长度为50，这里为什么用10呢？因为一般情 况下名字的长 度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

    执行T-SQL时，MySQL无须扫描任何记录就到找到唯一的记录！！

    肯定有人要问了，如果分别在vc_Name,vc_City,i_Age上建立单列索引，让该表有3个单列索引，查询 时和上述的组合索引效率一样 吧？嘿嘿，大不一样，远远低于 我们的组合索引~~虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

    建立这样的组合索引，其实是相当于分别建立了
        vc_Name,vc_City,i_Age
        vc_Name,vc_City
        vc_Name
    这样的三个组合索引！为什么没有vc_City,i_Age等这样的组合索引呢？这是因为mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个T-SQL会用到：
    SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"
    SELECT * FROM myIndex WHREE vc_Name="erquan"
而下面几个则不会 用到：
    SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"
    SELECT * FROM myIndex WHREE vc_City="郑州"