数据库-索引
索引的分类
普通索引
普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
唯一索引
- 唯一索引不允许被索引的数据列包含重复的值。
- 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
主索引
主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。
外键索引
如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
复合索引
索引可以覆盖多个数据列，如像INDEX（colA，colB）索引。
索引的优缺点
优点
- 创建唯一性索引，保证数据库表中每一行数据的唯一性。
- 加快数据的检索速度。
- 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能。
缺点
- 索引文件本身要消耗存储空间。
- 对表中的数据进行增加、删除和修改的时候，索引也需要维护。
一般两种情况不考虑索引
-
表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引。
- 个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。
-
索引的选择性较低。
- 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
- 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。
索引常见的存储数据结构
B-Tree
- 首先定义一条数据记录：[key, data]
- key为记录的键值，对于不同数据记录，key是互不相同的；
- data为数据记录除key外的数据。
- 每个节点由n-1个key和n个指针组成。
- 一个节点中的key从左往右非递减排列。
- 节点中的指针类似于二叉搜索树。
B-Tree检索数据的算法：
step1：首先从根节点进行二分查找，如果找到则返回对应节点的data
step2：否则对相应区域的指针指向的节点进行递归查找，直到找到节点或null
B-Tree的检索效率：
一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。
B+Tree
- 非叶子节点不存储data，只存储key。
- 每个叶子节点增加一个指向相邻叶子节点的指针，提高了区域访问的性能。例如：查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
B-Tree和B+Tree性能分析
前言：
-
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程就要产生磁盘IO消耗。
所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘IO操作次数的渐进复杂度。即：索引要尽量减少查找过程中磁盘IO的存取次数。 -
磁盘存取原理
- 磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间；
- 然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。
-
局部性原理与磁盘预读
- （根据局部性原理）磁盘读取数据时每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
- 预读的长度一般为页（page）的整倍数。主存和磁盘以页为单位交换数据。
B-Tree的性能分析：
- 根据B-Tree的定义，可知检索一次最多需要访问h个节点。（h为树高）
- B-Tree每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
- B-Tree中一次检索最多需要 h-1 次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。
综上：用B-Tree作为索引结构效率是非常高的。
B+Tree的性能分析：
- B+Tree更适合外存索引，原因和内节点出度d有关。d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：（其中floor表示向下取整。）
- dmax=floor(pagesize/(keysize+datasize+pointsize))
- 由于B+Tree非叶子节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。
MySQL索引实现
MyISAM索引实现
- MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
- 非聚簇索引。
主索引存储示意图
叶子节点的data域只保存数据记录的地址。
辅助索引存储示意图
在MyISAM中，主索引和辅助索引(Secondary Key)在结构上没有任何区别，叶子节点的data域存放的仍然是数据记录的地址，只是主索引要求Key唯一，而辅助索引的Key可以重复。
InnoDB索引实现
- InnoDB引擎也使用B+Tree作为索引结构，但叶子节点data域存放完整的数据记录。这个索引的Key就是数据表的主键。
- 聚簇索引。
- 因此InnoDB表数据文件本身就是主索引。
- InnoDB数据表不支持全文索引。
主索引存储示意图
叶子节点的data域就是数据记录本身。
辅助索引存储示意图
辅助索引的叶子节点的data域存放的是主键key，不是地址。
因此辅助索引搜索需要检索两遍索引，第一遍搜索主键key，第二遍根据主键key到主索引中检索数据记录。
联合索引详解
联合索引存储方式
联合索引（b，c，d）也会生成一个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在行的主键值（上图叶子节点紫色背景部分）。
1 1 5 12 13....他是单调递增的；如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引树，上图中的b列都等于1时，则根据c排序，此时c列也相等则按d列排序，如：1 1 4 ，1 1 5，c=4在c=5前面，以及13 12 4,13 16 1,13 16 5就可以说明这种情况。联合索引查询方式
针对于这样的查询语句：
select * from T1 where b = 12 and c = 14 and d = 3;
最左前缀匹配原则
首先我们创建的index_bcd(b,c,d)索引，相当于创建了(b)、（b、c）（b、c、d）三个索引，看完下面你就知道为什么相当于创建了三个索引。
我们看，联合索引是首先使用多列索引的第一列构建的索引树，用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建，当b列值相等时再以c列排序，若c列的值也相等则以d列排序。
索引的第一列也就是b列可以说是从左到右单调递增的，但我们看c列和d列并没有这个特性，它们只能在b列值相等的情况下这个小范围内递增，如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。 由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如（c,d）、(c）、(d)是无法应用缓存的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引。
select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到 select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引 select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引 select * from T1 where b = 12 and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化 select * from T1 where b = 12 and d = 3;-- 应用到一列索引 因为不能跨列使用索引 没有c列 连不上 select * from T1 where c = 14 and d = 3;-- 无法应用索引，违背最左匹配原则
tips：
- 覆盖索引的情况下，where语句中不等于会走索引。
- 多重范围的第一个范围会走索引，而后面的多重范围则不会走索引。
浙公网安备 33010602011771号