MySQL之索引
在MySQL中存储大量的数据，那么数据如何组织能进行高效的数据库操作就显得尤为重要，那么索引就是帮助数据库高效获取数据的结构
索引的优缺点
优点：
- 索引大大减少了数据库服务器需要扫描的数据，优化了数据查询，大大提高了效率
- 提高数据检索的效率，通过对索引进行排序，将随机IO变为了顺序IO，降低数据库的IO成本，
- 提前将数据进行组织，避免数据库临时建表排序
缺点：
- 创建索引和维护索引都需要消耗时间，这种时间上的占用随着数据量的不断变大而变大
- 索引是一种数据结构，需要占用物理空间
- 索引大大调高了查询的效率，但查询的高效也牺牲了表更新的效率，需要动态的维护索引
- 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
- 对于非常小的表，大部分情况下简单的全表扫描更高效
索引的分类
根据物理存储形式划分
聚簇索引
聚簇索引可以理解为一种数据存储形式（依靠B+树实现），索引与数据存放在一起，找到索引就找到了数据；且一个表中只能有一个，如果表中存在主键那么主键索引就是聚簇索引，如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引；其余情况InnoDB会自动生成一个row_id作为隐藏的聚集索引。
优点：
1.查找速度快， 因为数据和索引存放在同一个B+树中，找到索引就找到了数据，省去了回表操作
缺点：
1.插入速度严重依赖插入的顺序，按照主键的顺序插入是最快的，否则就会导致页分裂
2.更新主键的代价大，因此一般主键设为不可更新（因为可能设计多表关联）
3.二级索引访问需要两次索引查找，第一次查找主键，第二次查找行数据
非聚簇索引（二级索引）
聚簇索引和非聚簇索引的对比
1.聚簇索引存储的就是数据记录，非聚簇索引存储的数据位置，非聚簇索引不会影响数据表物理存储的顺序
2.一个表只能有一个聚簇索引，因为只能有一种物理存储的方式;可以有多个非聚簇索引，也就是多个索引目录提供检索
3.聚簇索引的查询效率优秀但更新效率低
根据逻辑功能划分
普通索引
唯一索引
主键索引
单列索引
多列索引（联合索引）
多列索引是在指索引以两个或两个以上字段进行组织，可以通过多个字段进行索引查询但是必须遵循最左前缀原则（即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效）
全文索引
空间索引
使用SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，可以提高系统获取空间数据类型的效率。（目前只有MyISAM支持空间索引）
索引的数据结构
B-TREE
B-树特征：
B+TREE
B+树特征：
- 所有的数据都存储在叶子节点的数据页，数据页中的数据以单向链表组织
- MySQL对B+树索引进行了优化，数据页之间通过双向链表组织，增强了区间访问的性能
- 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层
HASH
Hash索引仅仅能满足”=”,“IN”和”<=>”查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
B+树相比于B树，在每个节点上取消了存储的数据可以释放更多的空间来存储索引，这样构造的树结果就更矮更扁查询效率高，降低了进行IO的次数；
1.根页面的位置不动，在生成B+树的过程中 经过数据的添加 –>数据页的复制–>目录页–>页分裂 但根页面的位置不改变 这样的话InnoDB引擎用到这个索引的话从固定的地方取出根节点的页号来访问这个索引
2.内节点目录页的唯一性，二级索引的目录项如果不添加上主键的话有可能出现不唯一的情况，如果存在太多的重复索引，那么使用索引查询的效率也会大大降低
不同存储引擎索引的实现
InnoDB
InnoDB使用B+TREE存储数据，除了主键索引为聚簇索引，其它索引均为非聚簇索引。
一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。
InnoDB中主键不宜定义太大，因为辅助索引也会包含主键列，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。
InnoDB中尽量不使用非单调字段作主键（不使用多列），因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择
MyISAM
两者索引使用的区别
1.在InnoDB中我们需要主键查找时使用聚簇索引直接就可以查找到想要的数据而MyISM只能查找到数据的地址必须进行额外的一次回表操作才能查找到数据
2.InnoDB中数据文件本身就是索引文件 而MyISAM中数据文件和索引文件是分离的
3.InnoDB中非聚簇索引的data域存储的是主键的值，而MyISAM存储的是地址值
5.InnoDB中一定要有主键而MyISAM不要求
索引的设计原则
什么情况下适合创建索引
- 字段数值具有唯一约束性；DISTINCT字段
- 频繁作为where条件的字段；经常有GROUP BY和ORDER BY的字段
- 使用列的字段类型小的作为索引
- 使用字符串作为索引时使用字符串前缀作为索引（避免索引过大，通过文本区分度来确定前缀）（前缀索引）
- 区分度高（散列度高）的字段适合作为索引
- 使用最频繁的列放在联合索引的左侧
- 在多个字段需要创建索引时，一般选择联合索引
- 单表的索引一般不超过6个
什么情况下不适合创建索引
索引优化
覆盖索引
优点：
1.避免进行回表操作（InnoDB是以聚集索引存储的那么在通过二级索引进行查询数据的时，如果查询的数据包含在了索引内那么不需要进行回表）
缺点：
索引字段的维护是需要代价的，因此在建立冗余索引字段时需要权衡
索引下推（ICP）
在没有使用ICP的情况下，MySQL的查询：
使用ICP的情况下，查询过程：
- 存储引擎读取索引记录（不是完整的行记录）；
- 判断
WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录； - 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
- 存储引擎把记录交给
Server层，Server层检测该记录是否满足WHERE条件的其余部分。
ICP使用条件
- 如果表的访问类型是range,ref,ref_or_null,eq_ref可以使用ICP、
- 在innodb和myisam中
- 需要有回表操作的索引查询（二级索引）
- 相关子条件查询时不能使用
- 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数
索引失效
mysql 会一直向右匹配直到遇到索引搜索键使用
>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询，那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列，因此如果查询条件中有两个>、<范围列则无法全用到索引。
不等于索引失效情况
普通索引使用
!=索引失效，主键索引没影响。
where语句中索引列使用了负向查询，可能会导致索引失效。
负向查询包括：NOT、!=、<>、NOT IN、NOT LIKE等。
or 条件索引问题
or 的条件列除了同时是主键的时候，索引才会生效。其他情况下的，无论条件列是什么，索引都失效。
原文地址:https://blog.csdn.net/qq_62592925/article/details/134715910
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_16127.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
