这篇博客详细介绍了数据库的基础知识，包括数据库类型、SQL语言、数据类型、表操作、索引和事务等内容。重点讲解了MySQL数据库的使用，如连接数据库、创建与删除数据库、表的增删改查操作，以及SQL语句的分类和注意事项。此外，还涉及到了数据约束、索引的工作原理和事务管理，是学习MySQL数据库的好资料。
数据库概述
- 数据库：存储和管理数据的仓库
- 常见的数据库分为：
关系型数据库：Oracle、mysql、sqlserver、access
非关系型数据库：MongoDB、Redis、Solr、ElasticSearch、Hive、HBase - 关系型数据库
底层以二维表的形式保存数据的库就是关系型数据库
- 非关系型数据库
非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档、键值对或图形等。
- 扩展内容（了解即可）
常见的关系型数据库
·MySQL：Oracle公司产品，08年被Sun公司收购，09年Sun被Oracle收购，开源产品，为了担心MySQL闭源MySQL创始人离开Oracle创办了MariaDB，市占率第一
·Oracle：Oracle公司产品，闭源产品，性能最高价格最贵，市占率第二
·SQLServer：微软公司产品，闭源产品，市占率第三；网站整套解决方案：编程语言+web服务软件+操作系统+数据库软件
·DB2：IBM公司产品，主要应用在IBM整套解决方案中，主要服务于对安全性要求极高的场景 比如银行
·SQLite：轻量级数据，安装包只有几十kb
数据库相关概念
- 数据库服务器
数据库服务器是一个软件，将数据库软件安装在电脑上，当前电脑就是一个数据库服务器。就可以对外提供存取数据的服务
在一个数据库服务器中可以创建多个数据库，每一个数据库都是一个单独的仓库。 - 什么是数据库
数据库就是存储和管理数据的仓库 - 表
一个数据库中可以创建多张表，每张表用于存储一类信息 - 什么是表记录
一张表中可以包含多行表记录，每一行表记录用于存储某一个具体数据
连接mysql服务器
1.连接mysql服务器：
mysql -uroot -p
-u：用户名
-p（小写）：密码
2. 连接mysql服务器并指定ip和端口
mysql -uroot -proot -h127.0.0.1 -P3306
-h:服务器主机名或ip地址，默认连接本机
-P（大写）：端口3306，默认连接3306
3. 快捷打开方式
– windows: 开始菜单中找到MySQL/MariaDB 里面的 MySQL Client 并点击, 输入自己的密码后回车
– linux/苹果系统: 桌面空白右键 打开终端 输入 mysql -uroot -p 回车 输入密码再回车
4.退出客户端命令
quit exit \q
5.FAQ：常见问题：
解决方法：复制mysql安装目录下的bin目录的路径，将bin目录的路径添加到path环境变量中!!
可以在cmd中通过 echo %path% 检查path环境变量的值。
sql语句
什么是sql语言
Structured Query Language 结构化查询语言
SQL是一门用于操作关系型数据库的通用的语言（使用SQL可以操作所有的关系型数据库）
使用SQL可以操作数据库、表、表记录
(1)创建数据库、删除数据库、修改数据库、查询数据库
(2)创建表、删除表、修改表、查询表
(3)新增表记录、删除表记录、修改表记录、查询表记录
使用SQL也可以操作存储过程/视图/索引等。
提示：SQL是一个标准通用的操作关系型数据库的语言（普通话），每个数据库厂商为了增强自己数据库的功能，都提供了支持自己数据库的语言，称之为数据库的方言。方言不通用！
ssql语句的分类
SQL规范非常成熟，分为下面的部分：
- DDL: 数据定义语言, 包括数据库和表相关的SQL语言
- DML:数据操作语言, 包括数据的增删改相关SQL
- DQL:数据查询语言, 只包括查询SQL
- DCL: 数据控制语言, 包括权限分配相关SQL
- TCL: 事务控制语言, 包括事务相关的SQL
最常见的是CRUD相关的SQL语句：创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete），每个程序员必须掌握，也是面试必考内容。
sql语句注意事项
- 以分号结尾
- 对大小写不敏感。推荐关键字使用大写，自定义的名称（库名，表名，列名等）使用小写。mysql习惯全小写.
- 在自定义名称时，针对多个单词不要使用驼峰命名，而是使用下划线连接。(例如：tab_name，而不是 tabName )
- 可以有多个换行和空格
导入*.sql批处理文件
-
下载emp.zip解压得到emp.sql 把文件移动到某个盘的根目录,其他系统放到桌面
-
在终端中执行以下命令:
source d:/emp.sql; -
测试是否成功:
show tables; 会显示2个表 emp和dept select * from emp ; 如果出现乱码 执行 set names gbk; 再次查询
mysql的数据类型
数值类型
整型
MySQL中支持多种整型，其实很大程度上是相同的，只是存储值的大小范围不同而已。
tinyint：占用1个字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
浮点型
浮点类型即：float和double类型
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double
float/double(m,d)：m是总个数，d是小数位
定点型
decimal： 定点数，浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
字符串类型
1、char(n) 定长字符串，最长255个字符。n表示字符数，例如：
– 创建user表，指定用户名为char类型，字符长度不超过10
create table user(
username char(10),
...
);
所谓的定长，是当插入的数据的长度小于指定的长度时，剩余的空间会用空格填充。（这样会浪费空间）
char类型在插入数据时，比varchar类型效率要高！
char类型往往用于存储长度固定的数据。
2、varchar(n) 变长字符串，最长不超过65535个字节，n表示字符数，一般超过255个字符，会使用text类型，例如：
iso8859-1码表：一个字符占用1个字节，1*n < 65535， n最多等于 65535
utf8码表：一个中文汉字占用3个字节，3*n < 65535，n最多等于 65535/3
GBK码表：一个中文汉字占用2个字节，2*n < 65535，n最多等于 65535/2
– 创建user表，指定用户名为varchar类型，长度不超过10个字符
create table user(
username varchar(10)
);
所谓的不定长，是当插入的数据的长度小于指定的长度时，剩余的空间可以留给别的数据使用。（节省空间）
总结：长度固定的数据，用char类型，这样既不会浪费空间，效率也比较高
如果长度不固定，使用varchar类型，这样不会浪费空间。
3、大文本（长文本）类型
最长65535个字节，一般超过255个字符列的会使用text。
– 创建user表：
create table user(
resume text
);
另，text也分多种，其中bigtext存储数据的长度约为4GB。
扩展内容3：(面试题)char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：
(1)char(n)在保存数据时，如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。
因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！
(2)varchar(n)保存数据时，按数据的真实长度存储，剩余的空间可以留给别的数据用，因此varchar不会浪费空间。
因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。
(3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。
日期类型
date：年月日
time：时分秒
datetime：年月日 时分秒, 默认是null, 最大9999-12-31
timestamp：时间戳，年月日 时分秒,默认当前系统时间, 最大2038-1-19
图片
blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。
数据库操作(DDL)
查询所有数据库
- 格式: show databases;
创建数据库
-
默认字符集格式: create database 数据库名;
-
指定字符集格式: create database 数据库名 character set utf8/gbk;
需要注意的是，mysql中不支持横杠（-），所以utf-8要写成utf8；
-
举例:
create database db1; create database db2 character set utf8; create database db3 character set gbk;show databases; 检查是否创建成功
查询数据库详情
-
格式: show create database 数据库名;
-
举例:
show create database db1; show create database db2;
删除数据库
-
格式: drop database 数据库名;
-
举例:
drop database db3;
使用数据库
-
格式: use 数据库名;
-
进入数据库后，才能操作库中的表和表记录
-
举例:
use db1;
表操作(DDL)
查询所有表
- 格式: show tables;
创建表
– 创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：
CREATE TABLE 表名(
列名 数据类型,
列名 数据类型,
...
列名 数据类型
);
SQL语句:
use mydb1;
create table stu(
id int,
name varchar(20), -- 20表示该列中最多存20个字符
gender varchar(10),
birthday date,
score double
);
查看表详情
-
格式: show create table 表名;
-
举例:
show create table stu;
查看表结构
– 查看stu学生表结构
– 语法：desc 表名
desc stu;
修改表：
1.修改表名
alter table 表名 remame to 新的表名;
2.添加一列
alter table 表名 add 列名 数据类型;
3.修改数据类型
alter table 表名 modify 列名 数据类型;
4.修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;
where中不能使用列别名
关于为什么where中不能使用列别名但是可以使用表别名
因为表别名是声明在from中，from先于where执行，先声明再使用没问题，但是列别名是声明在select中，where先于select执行，如果先使用列别名再声明，这样执行会报错！！！
5.删除列
alter table 表名 drop 列名；
DML操作数据
添加数据
1.给指定列添加数据
insert into 表名（列名1，列名2，...）values(值1，值2，...);
2.给全部列添加数据
insert into 表名 values（值1，值2，...）;
3.批量添加数据
insert into 表名（列名1，列名2，...）values(值1，值2，...),(值1，值2，...),(值1，值2，...);
insert into 表名 values(值1，值2，...),(值1，值2，...),(值1，值2，...);
4.删除数据
delete from 表名[ where 条件];
修改数据
1.修改表数据
update 表名 set 列名1=值1，列名2=值2，[where 条件]；
查询数据(DQL)
1.基础查询
（1）查询多个字段
select 字段列表 from 表名
select * from 表名
（2）去除重复数据
select distinct 字段列表 from 表名
（3）起别名
AS：
2.条件查询
select 字段列表 from 表名 where 条件列表
3.排序查询
select 字段列表 from 表名 order by 排序字段名[ 排序方式]
asc 升序 desc 降序
4.分组查询
聚合函数
select 聚合函数名（列名）from 表
count（列名） 统计数量
max （列名） 最大值
min （列名）最小值
sum （列名）求和
avg （列名）平均值
count(*)、count(1)、count(字段名)**
相同点：都是用来统计表的数据条数的。
不同点：count() 与 count(1)统计出的结果一样，都不会忽略null值，但是count(*)的效率没有count(1)高；
count(字段名)在统计的时候，如果字段值为null的话，则不会计入总数。
分组查询语法
select 字段列表 from 表名[ where 分组前条件限定] group by 分组字段名 [ having 分组后条件过滤]
执行顺序 where>聚合函数>having
例子：
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个
select sex,avg(math),count() from stu where math>70 group by sex having count()>2
5.分页查询
select 字段列表 from 表名 limit 起始索引，查询条目数
起始索引=（当前页码-1）*每页显示的条数
tips：
分页查询limit是mysql方言
Oracle 分页查询使用rownumber
sql server分页查询使用top
having
having关键字后面写聚合函数条件,要和group by结合使用,写在group by的后面
-
where和having子句的区别:
相同点: 都是对查询的结果进行筛选过滤
不同点:
(1)where是在分组之前对数据进行筛选过滤; 而having是在分组之后对数据进行筛选过滤
(2)where子句中不能使用聚合函数, 而having中可以使用聚合函数(3)在mysql中，having可以使用列别名，where不可以。
查询每个部门的平均工资，要求平均工资大于2000
select deptno,avg(sal) a from emp
group by deptno having a>2000;
各个关键字的顺序
SQL语句的书写顺序:
select * | 列名 -- 确定要查询的列有哪些
from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount -- 指定返回第几页记录以及每页显示多少条
SQL语句的执行顺序:
from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
select * | 列名 列别名 -- 确定要查询的列有哪些
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount
mysql的字段约束(constraints)
字段约束/列约束 --> 约束: 限制
什么是约束: 创建表时 给表字段添加的限制条件
主键约束primary key
主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且非空。
主键的作用: 作为一个唯一标识，唯一的表示一条表记录（作用类似于人的身份证号，可以唯一的表示一个人一样。）
添加主键约束，例如将id设置为主键：
create table t1(id int primary key,name varchar(20));
insert into t1 values(1,'aaa');
insert into t1 values(1,'bbb'); //报错:Duplicate entry '1' for key 'PRIMARY' 主键数据重复
insert into t1 values(null,'ccc'); //报错:Column 'id' cannot be null
主键是唯一的索引，通常会用GUID做为主键，但是并不是每个表都存在ID字段，例如学生表（姓名，生日，性别，班级），这里面每一个值都可能重复，无法使用单一字段作为主键，这时我们可以将多个字段设置为复合主键，由复合主键标识唯一性。只要不是复合主键每个值都重复，就不算重复。
create table student (
name varchar(16),
birth date,
sex bool,
class int,
primary key (name,birth,sex,class)
)
自增属性auto_increment
如果主键是数值类型，为了方便插入主键（并且保证插入数据时，主键不会因为重复而报错），可以设置一个主键自增。
create table t2(id int primary key auto_increment,name varchar(20));
insert into t2 values(null,'aaa'); 1
insert into t2 values(null,'bbb'); 2
insert into t2 values(10,'ccc'); 10
insert into t2 values(null,'ddd'); 11
delete from t2 where id>=10;
insert into t2 values(null,'eee'); 12
设置了自增的主键，可以在插入记录时，不给id赋值，只需要设置一个null值，数据库会自动为id分配一个值（AUTO_INCREMENT变量，默认从1开始，后面依次+1），这样既可以保证id是唯一的，也省去了设置id的麻烦。
drop、delete和truncate之间的区别？
drop和truncate是DDL语句，delete是DML语句。
drop删除库或者表，数据和结构定义
delete和truncate只是删除表的数据
delete可以指定where条件，删除满足条件的记录，truncate删除所有记录
delete是逐条删除，truncate是一次性删除
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数
非空约束 not null
非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
添加非空约束，例如为password添加非空约束：
create table t3(
password varchar(50) not null,
...
);
唯一约束 unique
唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
添加唯一约束，例如为username添加唯一约束及非空约束：
create table t4(
username varchar(50) unique not null,
...
);
默认约束 default
设置默认值
create table t5(
sex CHAR(2) DEFAULT '男', #默认值
...
);
检查约束 check
很少使用，了解即可
create table t6(
age INT CHECK (age>0 AND age<=200),
...
);
录入age小于0或者大于200将报错
外键约束 foreign key
外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。
这样数据库就会帮我们维护两张表中数据之间的关系。
(1) 创建表的同时添加外键
create table emp(
id int,
name varchar(50),
dept_id int,
foreign key(dept_id) references dept(id)
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fGFgj821-1676602577711)(mysql讲义.assets/6b56c23ba5cf6a63d9f9e8a8b5fd8467.png)]
（1）如果是要表示两张表的数据之间存在对应关系，只需要在其中的一张表中添加一个列，保存另外一张表的主键，就可以保存两张表数据之间的关系。
但是添加的这个列（dept_id）对于数据库来说就是一个普通列，数据库不会知道两张表存在任何关系，因此数据库也不会帮我们维护这层关系。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3dQ2ABWE-1676602577712)(mysql讲义.assets/image-20200530160646152.png)]
（2）如果将dept_id列设置为外键，等同于通知数据库，部门表和员工表之间存在对应关系，dept_id列中的数据要参考部门的主键，数据库一旦知道部门和员工表之间存在关系，就会帮我们维护这层关系。
语法
- 添加约束
creat table 表名（
列名 数据类型,
[constraint] [ 外键名称] foreign key（外键列名）references 主表（主表列名）
）；
或
alter table 表名 add constraint 外键名称 foreign key （外键字段名称）references 主表名称（主表列名称）；
2.删除约束
alter table 表名 drop foreign key 外键名称
数据库设计
表关系
1.一对一
一个人对应一个身份证号，一个身份证号对应一个人
实现方式： 在任意一方加入外键，关联另一方主键，并且设置外键唯一
2.一对多（多对一）
一个部门对应多个员工，一个员工对应一个部门
实现方式： 在多的一方建立外键，指向一的一方的主键
3.多对多
一个商品对应多个订单，一个订单包括多个商品
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键
多表查询
- 连接查询
内连接：相当于查询AB交集数据
外连接：
- 左外连接：相当于查询A表所有数据和交集部分数据
- 右外连接：相当于查询B表所有数据和交集部分数据
- 子查询
查询中嵌套查询
- 单行单列：作为条件值，使用= != > <等进行条件判断
select 字段列表 from 表 where 字段名 = （子查询）
- 多行单列：作为条件值，使用in等关键字进行条件判断
select 字段列表 from 表 where 字段名 in （子查询）
- 多行多列：作为虚拟表
select 字段列表 from （子查询）where 条件
合并查询结果集
使用union/union all 关键字
前提条件：
1.多个结果集的列数必须一致
2.列名、顺序也必须一致
语法： select...union/union all select
union和union all的区别：
union会自动筛选掉多个结果集合中的重复结果，而且会按照字段的顺序进行排序，union all 则将所有结果全部显示出来，不会进行排序。从效率上说，union all要比union快很多，所以如果可以确认合并的两个结果集中不包含重复数据并且不需要排序时的化，可以使用union all
select * from emp where empno=100
union
selecct *from emp where empno=200
索引
定义
索引是对数据库表中一列或多列的值进行排序的一种结构，好比是一本书前面的目录，使用索引可以帮助数据库高效进行数据的检索。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
分类
根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。
- 单值索引：一个索引只包括一个列，一个表可以有多个列
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：一个索引同时包括多列
创建索引
- 主键会自动创建索引
SHOW INDEX FROM empext
- 创建唯一索引
alter table empext add unique(phone)
alter table empext add unique(cardno)
- 创建复合索引
alter table empext add index idx_phone_cardno(phone,cardno)
- 删除索引
alter table empext drop index cardno
检查索引
INSERT INTO empext
VALUES(200,'废都西安','13572801415','610113'
)
## system > const > eq_ref > ref > range > index > ALL
EXPLAIN SELECT * FROM empext
EXPLAIN SELECT * FROM empext WHERE empno=200
EXPLAIN SELECT * FROM empext WHERE phone='13572801415'
EXPLAIN SELECT * FROM empext WHERE cardno='610113'
索引扫描类型
type：
-
ALL 全表扫描，没有优化，最慢的方式
-
index 索引全扫描，其次慢的方式
-
range 索引范围扫描，常用语<，<=，>=，between等操作
-
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
-
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
-
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
-
null MySQL不访问任何表或索引，直接返回结果
从最好到最差的类型：
system > const > eq_ref > ref > range > index > ALL
possible_keys：显示可能应用在这张表中的索引
key：真正使用的索引方式
最左特性
EXPLAIN
SELECT * FROM empext WHERE phone='13572801415'
EXPLAIN
SELECT * FROM empext WHERE cardno='610113'
EXPLAIN
SELECT * FROM empext WHERE phone='13572801415' AND cardno='610113'
EXPLAIN
SELECT * FROM empext WHERE cardno='610113' AND phone='13572801415'
当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。
为何索引快？
明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。
其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。
- 排序，tree结构，类似二分查找
- 索引表小
- 3层的BTree可以表示上百万的数据，如果上百万条数据，查找只需要三次IO，性能提高将是巨大的，如果没有索引每次查找都要发生一次IO，那么总共就需要百万次的IO,显然成本是非常高的。
小结
优点:
- 索引是数据库优化
- 表的主键会默认自动创建索引
- 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
- 索引事先对数据进行了排序，降低查询是数据排序的成本，降低CPU的消耗
缺点:
- 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
- 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
- 虽然索引大大提高了查询的速度，但反向影响了增、删、改操作的效率。如表中数据变化之后，会造成索引内容不正确，需要更新索引表信息，如果数据量非常巨大，重新创建索引的时间就大大增加
- 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引
怎么判断是否创建索引？
创建索引：
- 主键约束默认建立唯一索引
- 在经常需要搜索的列上，可以加快搜索的速度
- 频繁出现在where查询条件的字段，可以加快条件的判断速度。
- 多表查询中与其它表进行on关联的字段，这些列主要是外键，可以加快连接的速度
- 查询中经常用来排序的字段，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 查询中经常用来统计或者分组字段
不创建索引：
- 频繁更新的字段: 每次更新都会影响索引树
- where条件查询中用不到的字段
- 表记录太少
- 对于那些只有很少数据值或者重复值多的列也不应该增加索引。
- 经常增删改的表: 更新了表，索引也得更新才行
拓展：索引工作原理
BTree+索引
- 浅蓝色: 磁盘块
- 深蓝色: 数据项
- 黄色: 数据的指针
真实的数据仅在叶子节点中: 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90,99
查找过程: 假如要找29
- 从树根开始，即先把磁盘块1中内容读到内存中，发生一次IO
- 确定29在(17,35)之间，锁定磁盘块1中的P2指针
- 根据P2指针，找到磁盘块3，读取到内存中，发生二次IO
- 29在(26,30)之间，锁定磁盘块3的P2指针
- 通过磁盘3的P2指针，将磁盘块8的内容读取到内存中，发生第三次IO
- 最终找到数据29，查询结束，总共发生三次IO
为什么Mysql用B+树做索引而不用B-树或红黑树
- B+树只有叶节点存放数据，其余节点用来索引
- B-树是每个索引节点都会有Data域
从Mysql（InnoDB）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。
那么Mysql如何衡量查询效率呢？磁盘IO次数。B-树/B+树的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。
另一个优点是：B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。
B树相对于红黑树的区别：
平衡二叉树AVL 和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。
同时，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
========
事务
- 简介：一种机制、一个操作序列，包含了一组数据库操作指令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据命令要么同时成功要么同时失败。事务是一个不可分割的工作逻辑单元
- 开启事务
begin或start transaction
- 回滚事务
rollback
- 提交事务
commit
- 事务的四大特征
- 原子性：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
- 一致性：事务完成时，必须使所有的数据都保持一致状态
- 隔离性：多个事务之间，操作的可见性
- 持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的
- mysql事务默认自动提交
&& 查看事务的默认提交方式
- mysql事务默认自动提交
select @@autocommit
&& 1自动提交 0手动提交
&& 修改事务提交方式
set @@autocommit=0
扫一扫
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
