数据库服务存储引擎

①.数据库服务存储引擎介绍

1. 存储引擎官方网站资料:https://dev.mysql.com/doc/refman/8.4/en/innodb-storage-engine.html

复制代码

1.什么是存储引擎/存储引擎有什么作用

1. 利用存储引擎可以有效管理磁盘和内存硬件，实现数据库服务数据调取和数据存储
3. 1.客户端
4. 包含连接工具信息（命令/工具/程序）
6. 2.服务端
7. 包含连接层：负责实现客户端与服务端建立连接会话（连接线程-show processlist;）
8. 包含服务层：负责处理客户端发送SQL语句请求
9.         解析器：可以对SQL语句做语义和语法检查（权限验证），生成解析数信息（各种处理SQL语句方案）
10.         优化器：可以对解析树中的执行方案做出选择（资源消耗低的方案，作为最优方案选择）
11.         执行器：可以根据最优SQL语句方案进行执行，执行后会得到语句的存储信息
13. 引入数据库包含的引擎层
14. 包含引擎层：可以有效控制管理磁盘或内存，进行数据存储调取（CPU-内存-磁盘/内存）
15. 控制管理磁盘或内存完成数据的存储或调取？

复制代码

2.存储引擎的类型

1. show engines;                #                显示存储引擎的类型
2. mysql> show engines;
3. +--------------------+---------+------------------------------------------------------
4. | Engine             | Support | Comment                                             
5. +--------------------+---------+------------------------------------------------------
6. | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign                  
7. | MyISAM             | YES     | MyISAM storage engine     
8. ............
9. ............
12. InnoDB                默认的数据存储引擎（MySQL5.5之后的）
13. 应用场景：适用于读多写多，可以保证数据存储安全性和一致性，如：游戏 金融 银行
14. 应用特点：支持事务（保证数据存储安全性）
15.                 支持行级锁（提高并发处理能力）
16.                 支持MVCC机制（可以实现热备数据，并且备份期间不影响数据库正常存储业务）
17.                 支持外键功能
19. MyISAM                早期的数据存储引擎（MySQL5.5之前的）
20. 应用场景：适用于读多写少，数据完整性要求不高的场景，如博客，新闻网站等
21. 应用缺陷：不支持事务（无法保证数据存储安全性）
22.                 无法支持行级锁，但可以支持表级锁（改善数据库并发处理能力）
23.              不支持MVCC多版本并发控制机制（在数据备份时，可以实现不影响业务进行备份数据）
24.              不支持外键约束功能

复制代码

3.数据库存储引擎的管理操作（了解即可）

1. #        1.查看默认的存储引擎
2. mysql> select @@default_storage_engine;
3. +--------------------------+
4. | @@default_storage_engine |
5. +--------------------------+
6. | InnoDB                   |
7. +--------------------------+
10. #        2.修改默认的存储引擎
11. 永久修改存储引擎
12. vim /etc/my.cnf
13. [mysqld]
14. default_storage_engine=存储引擎名称
16. 全局临时修改存储引擎
17. set global default_storage_engine='myisam';
20. #        3.针对指定数据表设置存储引擎
21. 可以对数据库存储碎片进行整理（可以提高磁盘应用效率）
22. 数据库碎片整理参考资料：https://cloud.tencent.com/developer/article/1848499
23. create table 表名 （...） engine='存储引擎';
24. alter table 表名 engine='存储引擎';

复制代码

数据异常损坏修复方法

1. 数据异常损坏，修复数据方法： 利用表空间文件修复数据（相同版本数据库之间 引擎使用innodb）
2. 修复数据常见方法--利用备份数据修复（备份文件 日志文件 利用表空间文件修复）
3. 步骤一：创建两个数据库实例
4. 实例01 模拟早期使用数据库服务
5. 实例02 模拟数据库服务故障后，重新建立的数据库服务
7. 步骤二：在实例01中模拟创建业务数据
8. mysql -p666666 -S /tmp/mysql.sock <./t100w_oldboy.sql
9. mysql> show databases;
10. +--------------------+
11. | Database           |
12. +--------------------+
13. | oldboy
15. mysql> show tables;
16. +------------------+
17. | Tables_in_oldboy |
18. +------------------+
19. | t100w            |
22. 步骤三：保存备份数据表结构信息   
23. mysql> SHOW CREATE TABLE t100w;
25. CREATE TABLE `t100w` (
26.   `id` int DEFAULT NULL,
27.   `num` int DEFAULT NULL,
28.   `k1` char(2) DEFAULT NULL,
29.   `k2` char(4) DEFAULT NULL,
30.   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
33. 步骤四：模拟数据库服务故障
34. mv ibdata1 ibdata11
36. 步骤五：需要进行数据库数据修复
37. create database oldboy;
38. -- 在新的实例02中，创建好oldboy数据库
39. CREATE TABLE `t100w` (
40.   `id` int DEFAULT NULL,
41.   `num` int DEFAULT NULL,
42.   `k1` char(2) DEFAULT NULL,
43.   `k2` char(4) DEFAULT NULL,
44.   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
45. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
46. -- 在新的实例02中，创建新的表信息
48. alter table oldboy.t100w discard tablespace;
49. -- 将指定表空间文件，从物理层面进行删除，但数据库逻辑层面依然存在表信息   
51. cp -a /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy
53. alter table oldboy.t100w import tablespace;
55. 步骤六：查看验证数据信息是否恢复成功
56. mysql> select count(*) from t100w;
57. +----------+
58. | count(*) |
59. +----------+
60. |  1000000 |
61. +----------+
62. 1 row in set (0.12 sec)数据异常损坏，修复数据方法： 利用表空间文件修复数据（相同版本数据库之间 引擎使用innodb）
63. 修复数据常见方法--利用备份数据修复（备份文件 日志文件 利用表空间文件修复）
64. 步骤一：创建两个数据库实例
65. 实例01 模拟早期使用数据库服务
66. 实例02 模拟数据库服务故障后，重新建立的数据库服务
68. 步骤二：在实例01中模拟创建业务数据
69. mysql -p123456 -S /tmp/mysql80.sock <./t100w_oldboy.sql
70. mysql> show databases;
71. +--------------------+
72. | Database           |
73. +--------------------+
74. | oldboy
76. mysql> show tables;
77. +------------------+
78. | Tables_in_oldboy |
79. +------------------+
80. | t100w            |
83. 步骤三：保存备份数据表结构信息   
84. CREATE TABLE `t100w` (
85.   `id` int DEFAULT NULL,
86.   `num` int DEFAULT NULL,
87.   `k1` char(2) DEFAULT NULL,
88.   `k2` char(4) DEFAULT NULL,
89.   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
90. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
92. 步骤四：模拟数据库服务故障
93. mv ibdata1 ibdata11
95. 步骤五：需要进行数据库数据修复
96. create database oldboy;
97. -- 在新的实例02中，创建好oldboy数据库
98. CREATE TABLE `t100w` (
99.   `id` int DEFAULT NULL,
100.   `num` int DEFAULT NULL,
101.   `k1` char(2) DEFAULT NULL,
102.   `k2` char(4) DEFAULT NULL,
103.   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
104. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
105. -- 在新的实例02中，创建新的表信息
107. alter table oldboy.t100w discard tablespace;
108. -- 将指定表空间文件，从物理层面进行删除，但数据库逻辑层面依然存在表信息   
110. cp -a /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy
112. alter table oldboy.t100w import tablespace;
114. 步骤六：查看验证数据信息是否恢复成功
115. mysql> select count(*) from t100w;
116. +----------+
117. | count(*) |
118. +----------+
119. |  1000000 |
120. +----------+
121. 1 row in set (0.12 sec)

复制代码

②.数据库服务索引功能应用

1.什么是数据库索引功能

1. 数据库索引功能类似书籍的目录，可以实现快速检索数据（可以减少磁盘IO资源消耗）

复制代码

2.如何设置创建索引信息

方法一：创建设置主键索引——PRI

1. 使用场景：针对主键约束会创建主键索引，可以实现快速查询整行数据信息
3. 创建索引
4. create table table_name (....,primary key (column));
5. ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
7. 查看索引
8. desc 表名  
9. -- 查看输出中key信息，确认是否有PRI标识信息即可
10. show index from stu02\G
11. -- 可以详细查看表中索引信息
12. 需要关注的输出信息：
13. Non_unique: 0/1
14.                0 表示创建的可能是唯一或主键索引  
15.                1 表示创建的是普通或联合和索引
16. Key_name:   索引名称信息，主键索引名只能是 PRIMARY 其他索引名称可以自定义
17.             区分索引信息应用情况  根据索引名称可以正确删除索引
18. Cardinality: 索引选择度参考数值，数值越大的列越优选设置为索引信息（列的重复值越少）
19. Index_type:  索引应用结构方式 利用索引检索数据算法应用  B+Tree -- 拼音  B-Tree -- 笔画
21. 删除索引：
22. alter table stu01 drop primary key;

复制代码

方法二：创建设置唯一索引——UNI

1. 创建设置唯一索引        UNI
3. 使用场景：
4. 类似与主键索引功能，但不同于主键索引，唯一索引列可以存储空值
5. 利用唯一索引功能，只能调取对应主键列信息，无法直接调取所有行数据信息
7. 创建索引：
8. create table table_name (......,unique index index_name(column));
9. create unique index 索引名称 on 表名(列名);
10. ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
12. 查看索引：
13. desc stu05;                                        #        查看输出中key信息，确认是否有UNI标识信息
14. show index from 表名;                   #        可以详细查看表中索引信息
16. 删除索引
17. alter table 表名 drop index 索引名;
18. drop index 索引名 on 数据表名;

复制代码

方法三：创建设置普通索引

1. 使用场景：
2. 当某个列经常作为条件查询数据时，可以设置为普通索引（列数据可以重复/可以为空）
3. 利用普通索引查询信息时，只能直接获取对应主键信息，不能直接获取整行数据信息
5. 创建索引：
6. create table table_name (......,index index_name(column));
7. create index 索引名称 on 表名(列名);
8. ALTER TABLE 表名 ADD 索引名 (列名);
10. 查看索引：
11. desc stu05;                                        #        查看输出中key信息，确认是否有MUL标识信息
12. show index from 表名;                   #        可以详细查看表中索引信息
14. 删除索引：
15. alter table 表名 drop index 索引名;
16. drop index 索引名 on 数据表名;

复制代码

方法四：创建设置联合索引

1. 联合索引应用：当进行数据查询时，需要利用多列信息作为条件，查询所需数据，可以多列组合创建索引
2. select * from 表名 where
4. 联合索引创建和应用原则：最左原则
5. 1）创建索引：将需要创建索引的多列信息中，重复值少的列放在联合索引创建的最左边
6. 2）应用索引：在利用联合索引列查询数据时，最左列一定要作为条件进行应用
7. --- 可以提高数据库数据查询效率，减少磁盘的IO资源消耗
9. 创建索引：
10. create table table_name (....,index  index_name(name,gender,age))
11. create index 索引名称 on 表名(列名) ;
12. ALTER TABLE 表名 ADD 索引名 (列名);   
14. 查看索引：
15. show index from 表名
16. -- 可以详细查看表中索引信息
18. 删除索引：
19. alter table 表名 drop index 索引名;
20. drop index 索引名 on 数据表名;

复制代码

③数据库性能压力测试

1. 3）数据库性能压力测试
2. mysqlslap --defaults-file=/data/3307/my80.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -S /tmp/mysql87.sock -verbose
5. --defaults-file  -- 表示加载配置文件信息
6. --concurrency    -- 表示模拟并发连接数量
7. --create-schema  -- 表示压力测试哪个库中数据
8. --query          -- 表示指定查询数据请求语句
9. --number-of-queries  -- 表示总的访问数据库请求处理的语句次数  100 -- 每个连接向数据库发送20次SQL查询请求
10. --iterations     -- 表示以上压力测试过程反复进行几回

复制代码

④数据库索引结构介绍

1. 索引算法结构：
2. https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
3. B(Blance)-TREE
4. 主要包含3层结构：
5. 最上层结构：根节点结构 只有有一个数据页作为根节点
6. 中间层结构：支节点结构（非页节点结构） 可以有多层 可以包含多个数据页
7. 最低层结构：叶节点结构 只有一层 可以有多个数据页
8. 利用结构存储数据情况：
9. 每个页节点中，都会存储索引列数据信息 会存储索引列之外数据列信息
10. id  name   age  gender
11. 01  xiaoA  18   男      -- page 01 xiaoA 18 男  page
12. 在根节点数据页中，还会存储关联叶节点支节点数据页指针信息
13. 在支节点数据页中，还会存储关联叶节点支节点数据页指针信息
14. 在页节点数据页中，没有指针信息存储
15. -- 以上索引结构存在数据查询缺陷
16.    1）查询不同数据信息，消耗IO资源不均衡
17.    2）更适合做等值数据查询，不太适合做范围数据查询   
19. B(Blance)+TREE
20. 主要包含3层结构：
21. 最上层结构：根节点结构 只有有一个数据页作为根节点
22. 中间层结构：支节点结构（非页节点结构） 可以有多层 可以包含多个数据页
23. 最低层结构：叶节点结构 只有一层 可以有多个数据页
24. 利用结构存储数据情况：
25. 每个页节点中，都会存储索引列数据信息 不会所有数据页都存储数据列信息
26. id  name   age  gender
27. 01  xiaoA  18   男      -- page 01 xiaoA 18 男  page
28. 在根节点数据页中，只会存储索引列信息，以及关联下层数据页节点的指针信息
29. 在支节点数据页中，只会存储索引列信息，以及关联下层数据页节点的指针信息
30. 在页节点数据页中，会存储索引列信息和对应索引列的数据信息  以及横向关联的数据页指针信息
31. -- 以上存储方式的改进，可以保证查询任何数据信息，消耗IO资源是相同的
32. -- 利用横向指针可以提高数据范围查询效率
33. -- 在索引结构中，每个方块代表一个page（数据页-4个block=16k 一个数据页输入输出过程称为1个IO消耗）
34. -- 有了以上索引算法结构，都是为了在查询数据信息时，可以有效定位数据，减少不必要的IO资源损耗
36. 索引应用结构
37. 聚簇索引结构：
38. - 数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引；
39. - 数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；
40. - 数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列作为聚簇索引；
42. 辅助索引结构：
43. - 辅助索引主要是：主要用于辅助聚簇索引查询的索引，一般按照业务查找条件，建立合理的索引信息，也可以称之为一般索引；
44. - 辅助索引作用是：主要是将需要查询的列信息可以和聚合索引信息建立有效的关联，从而使数据查询过程更高效，节省IO和CPU消耗
45. - 辅助索引的存储：调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的数据页中；
48. 课程知识总结：
49. 01 数据库引擎知识介绍
50.    1）数据库引擎功能作用  基于数据库体系结构，利用引擎可以管理内存和磁盘进行数据读取或存储
51.    2）数据库常用引擎区别  innodb/myisam
52.    3）数据库引擎功能设置  设置默认引擎 修改指定表的引擎
53.    4）实现数据信息修复    独立表空间迁移修复数据操作
54. 02 数据库索引知识介绍
55.    1）数据库索引功能作用  加快数据存储或读取效率 减少磁盘IO消耗
56.    2）数据库索引应用方法  主键索引/唯一索引/普通索引/联合索引（创建/查看/删除）
57.    3）数据库压力测试方法  验证索引创建是否有效 （了解）
58.    4）数据库索引结构层次  算法结构（B-TREE、B+TREE） 应用结构（聚簇索引/辅助索引）

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！