MySQL_DB索引&B+树&SQL优化

rookieLin8848

已于 2024-05-09 00:37:54 修改

阅读量273

点赞数 1

文章标签：数据库 b树数据结构

于 2024-04-22 17:37:52 首次发布

本文链接：https://blog.csdn.net/rookieLin8848/article/details/138084051

版权

在这里插入图片描述

数据库的索引就像一本书的目录，查数据快人一步，快速定位，精准打击！

什么是数据库的索引？

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引有哪几种？

主键索引
- 概述：表中创建的索引
- 特点：默认自动创建，只能有一个
- 关键字：PRIMARY
唯一索引
- 概述：避免同一表中数据列的值重复
- 特点：可以有多个
- 关键字：UNIQUE
常规索引
- 概述：快速定位特定数据
- 特点：可以有多个
- 关键字：
全文索引
- 概述：查找文本中的关键词，而不是比较索引中的值
- 特点：可以有多个
- 关键字：FULLTEXT

索引失效了解吗？如何解决？

在这里插入图片描述

索引的数据结构—树

什么是二叉树？

父节点下有两个叶子结点，比父节点小的放在左边，比父节点大的放在右边。
痛点：如果元素顺序插入的话二叉树会变成单向链表。
在这里插入图片描述

什么是自平衡二叉树？

痛点：如果元素顺序插入的话二叉树会变成单向链表。

为了解决这个痛点，引入了平衡二叉树。也称为红黑树。它会自动平衡父节点下叶子结点左右的均匀分布。

什么是B树？

什么是B+树？

元素过多裂变的话，中间的元素向上裂变。B+树底下含有所有上面出现过的元素，且底下的元素是单向链表链接。

SQL优化

`不要使用SELECT ` 。为啥select 会影响性能？

选择所有元素造成查询数据过多，而每次读取数据的过程都是磁盘的IO操作，对磁盘的读写操作负荷增大，对数据库的性能会有影响。使用了select * 查询时不会走索引，所以性能比较弱。

`不要使用子查询`。什么是子查询？为什么不使用子查询？

子查询就是嵌套查询。
子查询会创建一个临时表，会影响性能。查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

`不使用关联查询`。什么是关联查询？为什么不使用关联查询？

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL5.5以后的版本中，MySQL 通过引入BNLJ算法来优化嵌套执行。
因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。

硬是要用join怎么办？
大小表优化，减少驱动表的数据量

`不要使用in和not in`。使用exists和not exiexis。为什么不要使用in和not in？

因为这两个都是全表搜索，用不到索引和主键，所以导致搜索缓慢。

什么是事务？事务基本特性ACID？

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。在这里插入图片描述

A原子性(atomicity) 指的是一个事务中的操作要么全部成功，要么全部失败。
C一致性(consistency) 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
I隔离性(isolation) 指的是一个事务的修改在最终提交前，对其他事务是不可见的。
D持久性(durability) 指的是一旦事务提交，所做的修改就会永久保存到数据库中。

ACID靠什么保证的呢？

A原子性(atomicity) 由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性(consistency) 一般由代码层面来保证
I隔离性(isolation) 由MVCC来保证
D持久性(durability) 由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

能说下myisam 和 innodb的区别吗？

myisam引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。innodb是基于B+Tree索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

说下MySQL的索引有哪些吧？

索引在什么层面？首先，索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。有哪些？

B+Tree 索引
是大多数 MySQL 存储引擎的默认索引类型。
哈希索引
哈希索引能以 O(1) 时间进行查找，但是失去了有序性；InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
全文索引
MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
空间数据索引
MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询

什么是B+树？为什么B+树成为主要的SQL数据库的索引实现？

什么是B+Tree?
B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

在这里插入图片描述

为什么是B+Tree?
- 为了减少磁盘读取次数，决定了树的高度不能高，所以必须是先B-Tree；
- 以页为单位读取使得一次 I/O 就能完全载入一个节点，且相邻的节点也能够被预先载入；所以数据放在叶子节点，本质上是一个Page页；
- 为了支持范围查询以及关联关系，页中数据需要有序，且页的尾部节点指向下个页的头部；

那你知道什么是覆盖索引和回表吗？

MySQL 锁的类型有哪些呢？

说两个维度：

共享锁(简称S锁)和排他锁(简称X锁)
- 读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
- 写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。
表锁和行锁
- 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。
- 行锁又可以分为乐观锁和悲观锁
  - 悲观锁可以通过for update实现
  - 乐观锁则通过版本号实现。