数据库索引简述

概述

索引的目的在于提高查询效率

缺点

 

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在要给大表上建了多种组合索引，索引文件会膨胀很宽

      索引只是提高效率的一个方式，如果mysql有大数据量的表，就要花时间研究建立最优的索引，或优化查询语句。

代码

ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
//普通索引
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) ;

CREATE INDEX index_name ON table_name(username(length)); 
//create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

使用技巧及注意事项

1.索引不会包含有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 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

    7.索引要建立在经常进行select操作的字段上。

       这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

    8.索引要建立在值比较唯一的字段上。

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

    10.在where和join中出现的列需要建立索引。

    11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

    12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

    13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

索引的结构及种类

数据库索引从物理结构上分为聚集索引和非聚集索引

1. 聚集索引即索引项在物理结构上是连续的，依据索引规则紧密排序，mysql的主键索引就是聚集索引，优点是查找快，缺点是增删耗费的时间慢，因为如果以乱序插入就会不断破坏原有数据的结构，尤其像mysql索引以b+树结构存储，如以uuid作为主键将耗费大量时间
2. 非聚集索引即为索引项在物理结构上不连续

数据库索引从逻辑结构上分为几种，主键索引，唯一索引，联合索引，位图索引

1. 主键索引及表在建立是以主键作为索引项建立的索引，默认为聚集索引
2. 唯一索引，能确定某个数据列将只包含彼此各不相同的值，这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
3. 联合索引可以覆盖多个数据列不过遵循最左前缀原则
4. 位图索引，用于重复值较高的字段例如性别等，1是0非查询时利用&运算快速求解

最左前缀

 1.索引左前缀性的第一层意思：必须用到索引的第一个字段。select * from AAA where b=:xxx and c=sysdate;则不会用到索引，因为必须有a出现在where 语句中才会使用到该索引。
   2. 索引前缀性的第二层意思：对于索引的第一个字段，用like时左边必须是固定值，通配符只能出现在右边。select * from AAA where a like '1%';会用到索引;而select * from AAA where a like '%1';不会用到索引。
    3.索引前缀性的第三层意思：如果在字段前加了函数，则索引会被抑制，例如：select * from aaa where trim(a)=1,则不会用到索引。
           在字段前嵌入了表达式，索引也将被抑制。假设a是date格式的，那么where a+7<sysdate将不会用到索引，而where a<sysdate-7会用到索引。
　还有两个特殊声明：
         1).select * from AAA where a=:xxx and c=sysdate与 select * from AAA where c=sysdate and a=:xxx;都会用到索引，即与where语句中字段出现的顺序无关；

         2).select * from AAA where a=:xxx and b=1;会使用索引，此时A出现，即使其他字段不是索引字段也会使用到索引。