索引

1.概念

    索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含对数据表里所有记录的引用指针。数据库索引就像一本书前面的目录，能加快数据库的查询速度。

    索引分为聚簇索引和非聚簇索引，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样；聚簇索引能提高多行检查的速度，而非聚簇索引对单行的检查很快。

2.普通索引

    没有任何限制，MyIASM中默认的索引是BTREE类型，也是我们大多数情况下用到的索引。

    如果是char、varchar类型，length可以小于实际字段长度；如果是blob和text类型，必须指定length

    （1）直接创建索引

            create index index_name on table (column(length))

    （2）修改表结构的方式添加索引

            alter table table_name add index index_name  (column(length))

    （3）创建表的时候同时创建索引

            create table ’table’(

                ‘id' int(11) auto_increment,

                ‘title' char(255),

                index index_name (title(length))

            );

    （4）删除索引

            drop index index_name on table;

    （5）查看索引

            show index from table;

3.唯一索引

    索引列的值必须唯一，但允许有空值。如果是组合索引，则列值得组合必须唯一。

    （1）创建唯一索引

            create unique index index_name on table (column(length))

    （2）修改表结构的方式添加索引

            alter table table_name add unique index_name  (column(length))

    （3）创建表的时候同时创建索引

            create table ’table’(

                ‘id' int(11) auto_increment,

                ‘title' char(255),

                unique index_name (title(length))

            );

4.组合索引

    create table mytable(

        id int not null,

        name varchar(16) not null,

        city varchar(50) not null,

        age int not null

    );

    （1）创建组合索引

            alter table mytable add index name_city_age (name(10),city,age);

    （2）建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

    （3）如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

    （4）建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。

5.索引的不足

    上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

（1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

（2）建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

6.注意事项

    （1）索引不会包含有NULL值的列

        只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    （2）使用短索引

        对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    （3）索引列排序

        MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

    （4）like

        一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    （5）不要在列上进行运算

    （6）不使用NOT IN和<>操作