介绍一下MySQL索引?
索引是帮助MySQL高效获取数据的数据结构,能加快数据库的查询速度。 索引一般是存储在磁盘上的文件中。
索引的优势:
可以提高数据检索效率,降低数据库的IO成本。
加入索引的列会自动进行排序,降低数据排序的成本,降低了CPU的消耗。
索引的劣势:
占用磁盘空间
索引虽然会提高查询效率,但是会降低更新表的效率。每次对表进行增删改的操作时,MySQL不仅要保存数据,还要保存或更新对应的索引文件。
索引的分类:
按功能划分:
主键索引:一张表只能有一个主键索引,索引列的值必须唯一,不允许有空值
唯一索引:一张表可以有多个唯一索引,索引列的值必须唯一,但是允许为空值
普通索引:一张表允许创建多个普通索引,并允许数据重复和null
前缀索引:只适用于字符串类型的数据。
全文索引:只能在char、varchar、text类型字段上添加全文索引。
按列数划分:
单列索引:
组合索引:使用表中多个字段创建索引
覆盖索引即需要查询的字段正好是索引的字段,你们直接根据索引就可以查到数据了,而无需回表查询。
索引失效的情况:
情况一:在模糊匹配中以‘%’开头;
情况二:使用or时,有一边字段没有索引。
情况三:使用组合索引时,没有使用左侧的列查找。
情况四:在where中索引列参加了运算。
情况五:在where中索引列使用了函数。
什么时候不建议使用索引?
- 经常增删改查的列不要建立索引
- 有大量重复的列不建立索引
- 表记录太少不要建立索引
B+树和B树的区别,以及为什么不使用B树,Hash索引?
B树也称B-树,多路平衡查找树
- B 树的所有节点既存放键(key) 也存放 数据(data),而 B+树只有叶子节点存放 key 和 data,其他内节点只存放 key。
- B 树的叶子节点都是独立的。B+树的叶子节点有一条引用链指向与它相邻的叶子节点
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找,可能还没有到达叶子节点,检索就结束了。而 B+树的检索效率就很稳定了,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显。
MySQL没有使用Hash表作为索引结构是因为:Hash 索引不支持顺序和范围查询
不使用B树的原因:
- B+树的检索效率就很稳定
- B 树不管叶子节点还是非叶子节点,都会保存数据,所以每个结点能保存的索引值就很少,如果数据很大,只能增加树的高度,这样会降低性能。
聚簇索引和非聚簇索引的区别?以及各自的优缺点?
在InnoDB存储引擎中,可以将B+树索引分为聚簇索引和非聚簇索引。无论哪种索引,每个页的大小都为16KB,且不能更改。
聚簇索引是根据主键创建的一颗B+树,聚簇索引的叶子节点存放了表中的所有记录。InnoDB中的主键索引就是聚簇索引
聚簇索引的优点:
查询速度快,比非聚簇索引少了一次读取数据的IO操作
对排序查找和范围查找速度非常快
聚簇索引的缺点:
依赖于有序数据,
更新代价大,如果对索引列的数据被修改时,那么对应的索引也将会被修改,而且聚簇索引的叶子节点还存放着数据,修改代价肯定是较大的。
非聚簇索引是根据索引键创建的一颗B+树,与聚簇索引不同的是,其叶子节点仅存放索引键值,以及该索引键值指向的主键。也就是说,如果通过非聚簇索引来查找数据,那么当找到非聚簇索引的叶子节点后,很可能还需要根据主键值查找聚簇索引来得到数据,这种查找方式又被称为书签查找。 因为非聚簇索引不包含行记录的所有数据,这就意味着每页可以存放更多的键值,因此其高度一般都要小于聚簇索引。
非聚簇索引的优点:
更新代价比聚簇索引要小
非聚簇索引的缺点:
依赖于有序的数据
可能会二次查询(回表)
最左前缀匹配原则?
最左前缀匹配原则指的是,在使用联合索引时,MySQL 会根据联合索引中的字段顺序,从左到右依次到查询条件中去匹配,如果查询条件中存在与联合索引中最左侧字段相匹配的字段,则就会使用该字段过滤一批数据,直至联合索引中全部字段匹配完成,或者在执行过程中遇到范围查询,如 >、<、between和 以%开头的like查询等条件,才会停止匹配。
所以,我们在使用联合索引时,可以将区分度高的字段放在最左边,这也可以过滤更多数据。