mysql索引介绍

1,什么是索引

 

    索引是一种用于数据库中对一列或者多列进行排序的一种结构，有利于数据的快速查找或者排序。

    就像字典，索引就是字典的目录，我们根据目录（按字母，按偏旁）可以快速找到所需要的查找的字。

 

2，索引的类型

 

    1，普通索引

    2，唯一索引

    3，主键索引

    4，全局索引

    5，组合索引

 

    2.1 普通索引

        任何字段都可以创建，没有限制，是最基本的索引

        1,直接创建：

　　

create index index_name on table_name (col_name)

 

        2,修改表方法

        

alter table add index index_name on (col_name)

 

       

　　3，创建表时直接加上

 

create table tablename (
                        id is not null AUTO_INCREMENT ,
                        name varchar(200) is not null ,
                        age int(11)
                        primary key (id),
                        index name_index (name(200))
                        )

　　

    2.2 唯一索引

        

        和普通索引类似，唯一不同的的是，值必须唯一，但可以为空（只有一个）,如果是组合索引，则组合必须唯一

 

         1,直接创建：

 

create unique index index_name on table_name (col_name)

 

        2,修改表方法

 

alter table add unique index_name on (col_name)

　　

 

        3，创建表时直接加上

create table tablename (
                        id is not null AUTO_INCREMENT ,
                        name varchar(200) is not null ,
                        age int(11)
                        primary key (id),
                        unique name_index (name(200))
                        )

　　

    2.3  主键索引

 

        一种特殊的唯一索引，值必须唯一且不为空，每个表有且只能有一个，如果存在重复，创建失败

        一般在创建表时直接加上

　　

create table tablename (
                        id is not null AUTO_INCREMENT ,
                        name varchar(200) is not null ,
                        age int(11)
                        primary key (id),
                         
                        )
 

　　

    2.4 组合索引

 

            多个字段作为一个索引，且遵循最左前缀集合。

 

alter table add index index_name on (col_name2, col_name2)

 

    2.5 全局索引

 

        我们在做模糊查找时，常用的时like ，但是效率很低，尤其时在'%n%'等不走索引的情况下，严重影响性能。对于数量量很大的表来说，这将是个灾难，所以mysql5.6之后，innodb引入了全局索引，通过FULLTEXT进行创建，它与其他索引不同，不是与索引的值进行比较，而是查找文本中的关键字，同时sql中不是用在where之后，需要结合FullTEXT match against 进行查找，目前只有char，varchar，text类型字段可以创建全局索引。

 

    1， 直接创建

create fulltext index dex_index on tablename(description)

 

    2,   修改创建

alter table tablename add fulltext fulltext_name(col_name)

　　

    3,   表创建

create table tablename(
                        id int(11) not null auto_increment,
                        name char(10) not null,
                        age int(4) not null,
                        description text not null
                        primary key(id),
                        fulltext(description)
                       )
 

　　

3，索引的原理

 

        索引的数据以文件的形式存储在硬盘中。索引原理简单概述就是用空间换取时间，我们知道如果没有索引，数据库查找需要轮询的方式全表进行查找，再将结果放到结果集中。而建立索引，会将数据的key值放到n叉树上（bTree),我们知道BTree很适合磁盘中的动态查询，我们通过查询的key在树中定位到行数据，进行数据查询，后面单独介绍索引的底层原理。

 

4，索引的使用场景

 

    对于数据量大，查询效率低可以选择合理的索引进行优化。

    

5，索引的优缺点

    

    优点：

        1，可以加快排序，分组等

        2，某些索引可以保证数据的唯一性。

        3，提高查询效率。

        4，提高表关联的查询效率。

 

    缺点：

        1，占用空间。

        2， 影响增删改的效率。

        3， 增加维护成本。

 

 

6，合理利用索引

 

    介绍几种不能利用索引的情况。

1，模糊查询：

        like查询，检索的值k ，k在前%号在后，'%k%不能利用索引，'k%'可以利用索引（同_,[],[^])

2， 排序

    order by 多个字段，1，只有一个索引字段，索引字段需要放在左边第一个，2，组合索引，排序方式需要一致（desc，asc)

3,  运算（>,<,=)

    同样最左前缀原则，有索引的字段尽量放到最左。否则不能走索引。

4，in 导致索引失败

5. 对于order by、group by 、 union、 distinc 中的字段出现在where条件中时，才会利用索引！

6，or 的使用

    必须要or条件的所有字段都是索引，否则不能走索引。

 

7，总结：

 

    索引是优化数据库查询效率的最好方式，大量数据查询，选择合适的索引，以及利用explain分析索引的使用情况，是否正确使用可以 很好的结果。对于加了索引优化后依然不能解决问题，我们需要进一步优化sql。