索引相关

索引相关

概述

    所有MySQL列类型都可以被索引，对相关的列使用索引是提高select操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎(如MyISAM,InnoDB,BDB,MEMORY等)，对每个表至少支持16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

    MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关，对于MyISAM存储引擎的表，索引的前缀长度可以达到1000字节长，而对InnoDB存储引擎的表，索引的前缀长度最长是767字节。请注意前缀的限制是以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。在为使用多字节字符集的列之情前缀长度时一定要加以考虑。

    MySQL中还支持全文本(FULLTEXT)索引，该索引可以用于全文索引。但是当前最新版本(5.0)中只有MyISAM存储引擎支持FULLTEXT索引，并且只限于CHAR,VERCHAR和TEXT列。索引总是针对整个列进行的，不支持局部(前缀)索引。

    默认情况下，MEMORY存储引擎使用HASH索引，但是也支持BTREE索引。

设计原则

    1.搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列；

    2.使用唯一索引。考虑某列中值得分布。索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只有"M"和"F"，则对此列进行索引没有多大用处，因为不管搜索那个值，都会得出大约一半的行；

    3.使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或者20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘IO较小，较短的值比较起来更快。更重要的是，对于较短的键值，索引高速缓存中能够容纳更多的键值，因此MySQL也可以在内存中容纳更多的值。这样就增加了找到索引行而不用读取索引中较多块的可能性；

    4.利用最左前缀。创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可以利用索引中最左边的列集来匹配行。这样的列集成为最左前缀。

    5.不要过度索引。不要以为索引"越多越好"，什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必定进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果一个索引很少使用或从不使用，那也会不必要的减缓表的修改速度。此外，MySQL在生成一个执行计划时，需要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。

    6.对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则会按照主键顺序保存。如果没有主键，但有唯一索引，那么就会按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列的顺序进行访问的速度是最快的，索引InnoDB表尽量自己指定主键，当表中同时有几列都是唯一的，都可以作为主键的时候，要选择最常最为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

BTREE索引与HASH索引

    MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引各有其不同的适用范围，HASH索引有一些重要的特征需要在使用的时候特别注意，如下：

1.只用于使用=或者<=>操作符的等式比较；

2.优化器不能使用HASH索引来加速ORDER BY操作；

3.MySQL不能确定在两个值之间大约有多少行。如果将MyISAM表给为HASH索引的MEMORY表，会影响一些查询的效率；

4.只能使用整个关键字来搜索一行。

    而对于BTREE索引，当使用>,<,>=,<=,BETWEEN,!=或者<>,或者LIKE 'pattern'(其中pattern不以通配符开始)操作时，都可以使用相关列上的索引。

概念
    索引是由用户创建，能够被修改和删除的，实际存储在数据库中的物理存在，它是某一个表中一列或者若干列值的集合和相应的指向表中物理标志这些值的数据页的逻辑指针清单。

分类

1.直接创建索引和间接创建索引
直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
2.普通索引和唯一性索引
普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
3.单个索引和复合索引
单个索引：即非复合索引
复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
CREATE INDEX name_index ON username(firstname,lastname)
4.聚簇索引和非聚簇索引(聚集索引，群集索引)
聚簇索引：物表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
非聚簇索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。 

在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。

CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

优点
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因；
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间； 
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大；
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

常见索引失效原因

1.使用了!=运算符及not in, not exist等，认为产生的结果集很大，往往导致引擎不使用索引而使用全局扫描；

2.对索引使用了函数，如where substr(name,1,3)='mark'，会导致索引无效；

3.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)；
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
4.对于多列索引，不是使用的第一部分，则不会使用索引；
5.like查询是以%开头；
6.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引。