MySQL |
您所在的位置：网站首页 › 索引页是什么东西 › MySQL |
MySQL
|
按数据结构分类可分为：B+tree 索引、 Hash 索引、 Full-text 索引。按物理存储分类可分为：聚簇索引(主键索引)、二级索引（辅助索引）。按字段特性分类可分为：主键索引、普通索引、前缀索引。按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。
1、常见的索引模型
Hash 表： 适用于只有等值查询的场景，比如Memcached；有序数组：只看查询效率，有序数组是最好的数据结构，只适用于静态存储引擎；二叉搜索树： 时间复杂度0(log(N))。
2、索引的优缺点？
答： 创建索引可以大大提高系统的性能。 2.1、优点 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；可以大大加快数据的检索速度，这也是创建索引的最主要的原因；可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 2.2、缺点 创建和维护索引需要耗费时间：这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。索引需要占物理空间：除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。 3、InnoDB的索引模型？以及为什么要选择B+树？答： B+树（B+树的高度通常是 1-3）。B树在提高了IO性能的但是并没有解决元素遍历的效率低下的问题，正是为了解决这个问题，B+树应运而生。B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。 3.1、B+树的性质：1、非叶子节点的子树指针与关键字个数相同； 2、非叶子节点的子树指针p[i]，指向关键字值属于[k[i],k[i+1])的子树。(B树是开区间，也就是说 B 树不允许关键字重复， B+ 树允许重复)； 3、为所有叶子节点增加一个链指针； 4、所有关键字都在叶子节点出现(稠密索引)。 (且链表中的关键字恰好是有序的)； 5、非叶子节点相当于是叶子节点的索引(稀疏索引)，叶子节点相当于是存储(关键字)数据的数据层； 6、更适合于文件系统；
答： 约2千万行。 3.2.2、为什么索引结构默认使用B+树，而不是Hash，二叉树，红黑树，B-树? Hash哈希，只适合等值查询，不适合范围查询。一般二叉树，可能会特殊化为一个链表，相当于全表扫描。红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。 3.3、B树和B+树的区别？ B-树内部节点是保存数据的；而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据；B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是；查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束；B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。 3.4、B+树是如何减少IO性能的？ 按B树 和B+树来说，B+树的索引页中全部是都是索引，这样一个数据页中能查询到很多索引降低了下一次去磁盘再拿索引页的可能性, 这样就降低了磁盘的IO了。B树在非叶子节点存储数据了，这样我一个索引页上上有数据有索引，肯定效率低了。 这个B树就是一个多叉树而已了。
答：由于 Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash** 索引的查询效率要远高于 ****B-Tree**索引。 既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些： Hash 索引仅仅能满足 "=" , "IN" 和 "" 查询，不能使用范围查询： 由于 Hash 索引比较的是进行 Hash运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。Hash 索引无法被用来避免数据的排序操作： 由于 Hash 索引中存放的是经过 Hash计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；Hash 索引不能利用部分索引键查询： 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。Hash 索引在任何时候都不能避免表扫描： 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B-Tree 索引高。对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。 5、主键索引和非主键索引？答： 非主键索引的叶子节点存放的是主键的值，主键索引的叶子节点存放的是整行数据。其中，非主键索引又称为二级索引，主键索引又称为聚簇索引。 6、自增主键和随机主键的索引有什么区别？ 自增主键： 在进行数据插入时，位置相对固定（B+树中的右下角）增加数据插入效率，减少插入的磁盘 IO 消耗，每页的空间在填满的情况下再去申请下一个空间，底层物理连续性更好，能更好的支持区间查找；UUID ： 由于UUID是随机生成的，插入时位置具有一定的不确定性，无序插入，会存在许多内存碎片，内存空间的占用量也会比自增主键大，区间查找也没自增主键性能优。 7、SQL查询语句确定创建哪种类型的索引？如何优化查询？ 7.1、MySQL索引类型normal，unique，full text的区别是什么？ normal ： 表示普通索引，最常见；unique ： 表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique；full text ： 表示全文搜索的索引。 FULL TEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX也可以。 7.2、实际操作过程中，应该选取表中哪些字段作为索引？ 选择唯一性索引为经常需要排序、分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数据量少的索引尽量使用前缀来索引删除不再使用或者很少使用的索引 7.3、MySQL索引创建方式普通索引：create index on Tablename(列的列表) alter table TableName add index (列的列表) create table TableName([...], index [IndexName] (列的列表)唯一性索引：create unique index alter ... add unique // 主键：一种唯一性索引，必须指定为primary key全文索引：从3.23.23版开始支持全文索引和全文检索，FULL TEXT 可以在char、varchar或text类型的列上创建。单列索引、多列索引 多个单列索引与单个多列索引的查询效果不同，因为： 执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。最左前缀(Leftmost Prefixing)：多列索引，例如：fname_lname_age索引，以下的搜索条件MySQL都将使用 fname_lname_age索引：firstname,lastname,age;firstname,lastname;firstname，其他情况将不使用。 8、范围查询会用到索引吗？答： =, |
【本文地址】