©2020-2024 ioDraw All rights reserved
索引简介
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合与相应的指向表中数据的指针组成清单。索引的作用相当于书的目录，可以根据目录中的页码快速定位到所需的内容。索引是一种使记录有序化的技术，它从逻辑上对记录进行排序而不影响物理存储的数据。
优点：
加速数据检索
创建唯一可以索引保证每一行数据的唯一性
加速连接查询、排序、分组操作
缺点：
需要额外的物理空间进行存储
数据增删改的时候需要动态维护索引，耗费时间
增加优化器在选择索引时的计算代价
注意：要合理选择必要的字段建立索引！
索引类型
普通索引：这是最基本的索引，它没有任何限制。
唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
组合索引与单列索引：单列索引是针对一个字段创建的索引，组合索引是针对多个字段创建一个索引，但是如果字段顺序不同，那么代表索引也是不同的。
聚集索引与非聚集索引：聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。
索引结构
Hash索引
将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中。
优点：通过哈希运算得到的hash值一次定位，适合精准查询。
缺点：索引中保存的是hash值，hash值的大小关系同原值的大小关系不能保证一致，因此不能用于“like”等范围查询和排序，此外还不能利用组合索引的部分查询特性。由于还需通过行指针访问实际的表数据，因此不能避免对表的扫描。
B-tree索引
B-Tree(平衡多路查找树)的特性：一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)
。
由于索引本身也很大，不可能全部存储在内存中，因此索引往往也是以索引文件的形式存储的磁盘上，树状结构可以大大减少磁盘IO次数已提高查询速度。根据局部性原理和磁盘预读特性，数据库系统将一个节点的大小设为磁盘一个页的大小，这样每个节点的数据只需一次IO就能载入。实际应用中B树的度往往大于100即百万级数据也只需3次IO。
Mysql使用的Innodb 存储引擎中 B-Tree 索引使用的存储结构实际上是
B+Tree，它是B-Tree的变种，相比B-Tree，B+Tree的内部节点不保存关键字对应的指针，因此每个节点可以容纳更多的关键字，相对的可以减少磁盘IO次数。另外每个叶子节点会保存相邻的后一个叶子节点的指针，可以加速多个相邻叶子节点的检索。
创建索引
可视化工具创建
sql语句
CREATE INDEX [IDXNAME] ON [TABLE]([FIELD]([LENGTH]))
ALTER TABLE [TABLE] ADD INDEX [IDXNAME]([FIELD]([LENGTH]))
索引使用情景(重要)
适合创建索引条件
出现在on 条件或where条件中的列
经常用于排序或分组的列
经常使用聚集函数(如COUNT/MIN/MAX/SUM)的列
不适合创建索引的条件
频繁新增、修改删除的表
数据量比较小的表
选择性不高的列(例如性别)
使用索引注意事项
索引应该建在选择性高的字段上(键值唯一的记录数/总记录条数)，选择性越高索引的效果越好、价值越大，唯一索引的选择性最高
组合索引中字段的顺序，选择性越高的字段排在最前面
不要重复创建彼此有包含关系的索引，如index1(a,b,c) 、index2(a,b)、index3(a) (最左前缀原理)
索引值不应过大，组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索
数据量较少的时候，全表扫描比使用索引快,则不使用索引
索引列不能包含NULL，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的
使用短索引
导致索引失效的情况
对索引列进行运算 错误： select * from test where id-1=9;
使用or操作符(对所有列设置单列索引，union)
使用not in ,not exist，<>
%开头的like
组合索引的索引列以不同顺序排序或先后顺序不同
条件中不含组合索引的中间字段
无条件检索全部记录