数据库之索引

　　数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

• 索引的特点

1. 可以加快数据库的检索速度
2. 降低数据库插入、删除、修改等维护的速度
3. 只能创建在表上，不能创建在视图上
4. 既可以直接创建，也可以间接创建
5. 可以在优化隐藏中使用索引
6. 使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

 

• 索引的优点

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

 

• 索引的缺点

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

 

• 索引的分类

1. 直接创建索引和间接创建索引
2. 普通索引和唯一性索引
3. 单个索引和复合索引
4. 聚簇索引和非聚簇索引

 

• 索引失效的情况

1. 如果条件中有or，及时其中有条件带索引也不会使用
2. like查询以“%”开头
3. 如果列类型是字符串，那一定要在条件中使用引号来引用，否则不会使用索引
4. 如果数据库估计全表扫描比使用索引快，则不使用索引
5. 对于多列索引，不是使用的第一部分，则不会使用索引

 

• 在什么情况下适合建立索引

1. 为经常出现关键字order by、group by、distinct后面的字段，建立索引
2. 在union等集合操作的结果集字段上，建立索引。
3. 为经常用作查询选择的字段，建立索引
4. 为经常用作表连接的属性上，建立索引
5. 考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改为索引的扫描

 

• 索引优化策略

1. 最左前缀匹配原则
2. 为较长的字符串使用前缀索引
3. 对where、on、group by、order by中出现的列使用索引
4. 尽量选择区分度高的列作为索引，区分度的公式为count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。
5. 对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。
6. 尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
7. 对于like查询，”%”不要放在前面。 
8. 查询where条件数据类型不匹配也无法使用索引 

 

• 什么是前缀索引

       前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的代销和维护开销。

       一般来说以下情况可以使用前缀索引：

      (1) 字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’

      (2) 字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。

      (3) 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。