MySQL 索引优化总结

一、索引的类型：
普通索引：这是最基本的索引类型，没唯一性之类的限制。
唯一性索引：和普通索引基本相同，但所有的索引列只能出现一次，保持唯一性。
主键：主键是一种唯一索引，但必须指定为"PRIMARY KEY"。

全文索引：全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

二、查看索引使用效率，其长度一致会增加其效率: 其选择性越大则效率越高。

select count(distinct(concat(col_name, left(col_name, 4))))/count(*) as selectivity from table_name;  --查看联合索引的选择性.

show index from table_name;  --查看索引

三、索引的优点与缺点：

1、优点如下所示：

大大减少了服务器需要扫描的数据量；可以帮助服务器避免排序和临时表；可以将随机的 I/O 变为顺序的 I/O；提高查询、排序、分组的速度以及提供系统查询的性能；

2、缺点如下所示：

创建和维护索引要耗费时间，并随着数据量的增加而增加；占用了大量的物理空间，如创建聚簇索引去，其需要空间更大；对表的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；

四、索引的创建、修改以及删除的 sql 语句:
1、使用ALTER TABLE语句创建索引:
        alter table table_name add index index_name (column_list) ;
        alter table table_name add unique (column_list) ;
        alter table table_name add primary key (column_list) ;
2、使用CREATE INDEX语句对表增加索引:
        create index index_name on table_name (column_list) ;
        create unique index index_name on table_name (column_list) ;
3、删除索引:
        drop index index_name on table_name ;
        alter table table_name drop index index_name ;

        alter table table_name drop primary key ;

五、如何创建索引以及其使用的原则：

1、如何创建索引：

①、在经常进行连接的字段、在排序或分组的列上建立索引

②、如其字段是索引的话，想要使用其索引，则查询子段的数据类型一定要和创建表的数据类型保持一致；

③、在条件表达式中建立所因（where），情况有两种：

        不同值较多的列上使用索引；不同值较少的列上不适用索引；

④、待排序的列有多个，可以在这些列上建立复合索引

⑤、可以建立短索引，其 sql 语句如下所示：

       alter table table_name add index index_name(column_list(length));

⑥、join中出现的列需要建立索引；

2、索引的使用原则：

①、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配；

②、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col_name)/count(*);

③、索引列不能参与计算以及字段上也不可以使用任何函数；

④、尽量的扩展索引，使用联合索引，不要新建索引增加其维护成本以及物理空间的增大；

⑤、定义主键的数据列一定要建立索引；定义有外键的数据列一定要建立索引；对于经常查询的数据列最好建立索引

⑥、经常出现在关键字 where、order by、group by、distinct后面的字段，建立索引；

⑦、对于那些查询中很少涉及的列、重复值比较多的列、定义为text、image和bit的数据类型的列以及经常存取的列，不要建立索引；

⑧、限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个；索引虽说提高了访问速度，但太多索引会影响数据的更新、插入操作。

⑨、对复合索引，按照字段在查询条件中出现的频度建立索引；其应用频度高的字段放在首位，会使系统最大可能地使用此索引，发挥索引的作用。