八股文五：数据库

最新推荐文章于 2023-06-17 23:06:37 发布

Dark_Flame_Master?

最新推荐文章于 2023-06-17 23:06:37 发布

阅读量676

点赞数

分类专栏： java面试文章标签： java mysql 数据库后端

原文链接：https://zhuanlan.zhihu.com/p/215878135

版权

java面试专栏收录该内容

9 篇文章 2 订阅

订阅专栏

五、MySQL （Inno DB）

1、聚簇索引与非聚簇索引

聚簇索引和非聚簇索引的定义：

聚簇索引：表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，聚簇索引的叶子节点就是数据节点，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
非聚簇索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
总结一下：聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

都使用 B+ 树作为数据结构
聚簇索引中数据存在主键索引的叶子结点中，得到 key 即得到 data ；非聚簇索引的数据存在单独的空间。
聚簇索引中辅助索引的叶子结点存的是主键；非聚簇索引中叶子结点存的是数据的地址；
聚簇索引的优势是找到主键就找到数据，只需一次磁盘 IO ；当 B+ 树的结点发生变化时，地址也会发生变化，这时非聚簇索引需要更新所有的地址，增加开销。

2、为何使用 B 树做索引而不是红黑树？

索引很大，通常作为文件存储在磁盘上面，每次检索索引都需要把索引文件加载进内存，所以磁盘 IO 的次数是衡量索引数据结构好坏的重要指标。应用程序在从磁盘读取数据时，不只是读取需要的数据，还会连同其他数据以页的形式做预读来减少磁盘 IO 的次数。数据库的设计者将每个节点的大小设置为一页的大小，同时每次新建节点时都重新申请一个页，这样检索一个节点只需要一次 IO，根据索引定位到数据只需要 h- 1（h 为 B 树高度，根节点常驻内存）次 IO，而 d (度，可以理解为宽度)与 h 称反比，即 d 越大，高度就越小，所以树越扁，磁盘 IO 次数越少，即渐进复杂度为 logdN ，这也是为什么不选择红黑树做索引的原因。前面可以得出结论，d 越大，索引的性能越好。节点由 key 和 data 组成，页的大小一定，key 和 data 越小，d 越大。B + 树去掉了节点内的 data 域，所以有更大的 d , 性能更好。

B树&B+树&红黑树：

详见此链接

B树(Balance Tree)是一种多路平衡查找树(多路:每个节点的子节点可以多于两个),一个m阶的B树具有如下几个特征(阶:树中所有孩子结点个数的最大值称为该树的阶)：

1.根结点至少有两个子女。

2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4.所有的叶子结点都位于同一层。

5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

B+树(B+ Tree)是B树的plus版本,在数据库应用中有着比B树更高的查询性能,他俩有许多共同点,但是B+树在B树基础上具备一些新的特征,一个m阶的B+树具有如下几个特征：

1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

红黑树(Red&Black Tree)除了符合二叉查找树（BST）的特性外,其的节点会分出一个存储位来标记其颜色(标颜色是为了更好地通过颜色相关的规则来检验树的平衡性),比BST多出如下的附加特性:

1.节点是红色或黑色。

2.根节点是黑色。

3.每个叶子节点都是黑色的空节点（NIL节点）。

4.每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)

5.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

3、最左前缀原则

在 MySQL 中，可以指定多个列为索引，即联合索引。比如 index(name，age，sex，phonenumber) （这就是sql语句的写法！），最左前缀原则是指查询时精确匹配到从最左边开始的一列或几列（name；name&age），就可以命中索引。如果所有列都用到了，顺序不同，查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

4、什么情况下可以用到 B 树索引

(1) 定义有主键的列一定要建立索引。因为主键可以加速定位到表中的某行

(2) 定义有外键的列一定要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接

(3) 对于经常查询的数据列最好建立索引。

① 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间

② 经常用在 where 子句中的数据列，将索引建立在 where 子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间。

5、事务隔离级别

Read uncommitted 读未提交，可能出现脏读，不可重复读，幻读。
Read committed 读提交，可能出现不可重复读，幻读。
Repeatable read 可重复读，可能出现脏读。
Serializable 可串行化，同一数据读写都加锁，避免脏读，性能不忍直视。

Inno DB 默认隔离级别为可重复读级别，分为快照度和当前读，并且通过行锁和间隙锁解决了幻读问题。

6、MVCC （多版本并发控制）

实现细节（这不就是乐观锁思想吗，CAS）
- 每行数据都存在一个版本，每次数据更新时都更新该版本。
- 修改时 Copy 出当前版本随意修改，各个事务之间互不干扰。
- 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃 copy（rollback）。
Inno DB 实现

在 InnoDB 中为每行增加两个隐藏的字段，分别是该行数据创建时的版本号和删除时的版本号，这里的版本号是系统版本号（可以简单理解为事务的 ID），每开始一个新的事务，系统版本号就自动递增，作为事务的 ID 。通常这两个版本号分别叫做创建时间和删除时间。（时间得考虑到时间的细度，就是更新的时间间隔，不然太长的话会显示出版本号相同）

7、MYSQL数据库的一些基础操作

/*websites  表名   NAME alexa url country  字段*/
SELECT * FROM websites;                      /* 查询表所有数据 */
SELECT NAME FROM websites;                   /* 查询表字段数据 */
SELECT * FROM websites where name = "广西";   /* 查询表字段下条件数据 */
SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */
SELECT * FROM websites where id BETWEEN "1" AND "5";    /* 查询表下字段范围数据 */
SELECT * FROM websites WHERE name in ("广西","百度");    /* 查询表字段下固定条件数据 */
SELECT DISTINCT country FROM Websites;                  /* 查询去重值 */
SELECT * FROM Websites WHERE country = "CN" AND alexa > 50;  /*查询表下范围条件数据*/
SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */
SELECT * FROM Websites ORDER BY alexa;                      /* 查询表下值排序结果 */
SELECT * FROM Websites ORDER BY alexa DESC;                 /* 查询表下排序结果降序 */
SELECT * FROM Websites LIMIT 2;      /* 查询表下范围数据 */
SELECT name as zzz from websites;    /*别名查询表下数据*/

关联查询：select 字段 from 表1 left join 表2 on 条件 (一般为表1与表2的关联条件)
左关联: left join ... on ...；右关联: right join... on ...

单表查询
---------------------
mysql> select * from user;
+----+----------+------------+------+----------+
| id | username | birthday   | sex  | address  |
+----+----------+------------+------+----------+
|  1 | 王五     | 2017-11-25 | 3    | 南阳     |
| 10 | 张三     | 2014-07-10 | 1    | 北京市   |
| 16 | 张小明   | NULL       | 1    | 河南郑州 |
| 22 | 陈小明   | NULL       | 1    | 河南郑州 |
| 24 | 张三丰   | NULL       | 1    | 河南郑州 |
| 25 | 陈小明   | NULL       | 1    | 河南郑州 |
| 26 | 王五     | NULL       | NULL | NULL     |
| 29 | 小黑     | 2017-11-26 | NULL | NULL     |
| 30 | 抖森     | 2017-11-25 | 1    | 山村     |
+----+----------+------------+------+----------+
9 rows in set (0.03 sec)

mysql> select * from orders;
+----+---------+---------+---------------------+------+
| id | user_id | number  | createtime          | note |
+----+---------+---------+---------------------+------+
|  3 |       1 | 1000010 | 2015-02-04 13:22:35 | NULL |
|  4 |       1 | 1000011 | 2015-02-03 13:22:41 | NULL |
|  5 |      10 | 1000012 | 2015-02-12 16:13:23 | NULL |
+----+---------+---------+---------------------+------+
3 rows in set (0.03 sec)
--------------------------
关联查询
-------------------------
左关联
------------------------
mysql> select user.* ,orders.number from user  left join orders on user.id = orders.user_id;
+----+----------+------------+------+----------+---------+
| id | username | birthday   | sex  | address  | number  |
+----+----------+------------+------+----------+---------+
|  1 | 王五     | 2017-11-25 | 3    | 南阳     | 1000010 |
|  1 | 王五     | 2017-11-25 | 3    | 南阳     | 1000011 |
| 10 | 张三     | 2014-07-10 | 1    | 北京市   | 1000012 |
| 16 | 张小明   | NULL       | 1    | 河南郑州 | NULL    |
| 22 | 陈小明   | NULL       | 1    | 河南郑州 | NULL    |
| 24 | 张三丰   | NULL       | 1    | 河南郑州 | NULL    |
| 25 | 陈小明   | NULL       | 1    | 河南郑州 | NULL    |
| 26 | 王五     | NULL       | NULL | NULL     | NULL    |
| 29 | 小黑     | 2017-11-26 | NULL | NULL     | NULL    |
| 30 | 抖森     | 2017-11-25 | 1    | 山村     | NULL    |
+----+----------+------------+------+----------+---------+
10 rows in set (0.04 sec)
-----------------------------
右关联
-----------------------------
mysql> select user.* ,orders.number from user right join orders on user.id = orders.user_id;
+----+----------+------------+-----+---------+---------+
| id | username | birthday   | sex | address | number  |
+----+----------+------------+-----+---------+---------+
|  1 | 王五     | 2017-11-25 | 3   | 南阳    | 1000010 |
|  1 | 王五     | 2017-11-25 | 3   | 南阳    | 1000011 |
| 10 | 张三     | 2014-07-10 | 1   | 北京市  | 1000012 |
+----+----------+------------+-----+---------+---------+
3 rows in set (0.05 sec)

GROUP BY 语法：在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)

Dark_Flame_Master?

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
八股文五：数据库

五、MySQL （Inno DB）1、聚簇索引与非聚簇索引都使用 B+ 树作为数据结构聚簇索引中数据存在主键索引的叶子结点中，得到 key 即得到 data ；非聚簇索引的数据存在单独的空间。聚簇索引中辅助索引的叶子结点存的是主键；非聚簇索引中叶子结点存的是数据的地址；聚簇索引的优势是找到主键就找到数据，只需一次磁盘 IO ；当 B+ 树的结点发生变化时，地址也会发生变化，这时非聚簇索引需要更新所有的地址，增加开销。2、为何使用 B 树做索引而不是红黑树？索引很大，..
复制链接

扫一扫