MySQL

下载、安装MySQL

数据类型

主要分三类：数值类型、字符串类型、日期时间类型

类型	大小（bytes）	描述	有符号（signed）范围	无符号（unsigned）范围	备注
tinyint	1	小整数值	（-128，127）	（0，255）
smallint	2	大整数值
mediumint	3	大整数值
int	4	大整数值
bigint	8	极大整数值
float	4	单精度浮点数值			float(5, 2)：5表示整个数字长度，2表示小数位个数
double	8	双精度浮点数值			double(5, 2)：5表示整个数字长度，2表示小数位个数
decimal		小数值（精度更高）			decimal(5, 2)：5表示整个数字长度，2表示小数位个数。 在浮点数计算中以字符串处理小数，从而避免精度损失

类型	大小（bytes）	描述	备注
char	0-255	定长字符串	char(10)：最多只能存储10个字符，不足10个字符，占用字符空间 性能高，浪费空间 例如：phone char(11)
varchar	0-65535	储变长字符串	varchar(10)：最多只能存储10个字符，不足10个字符，按实际存， 性能低，更节省空间 例如：username varchar(55)
linyblob	0-255	不超过255个自字二进制数据
tinytext	0-255	短文本数据
blob		二进制形式的长文本数据
text		长文本的数据
mediumblob		二进制形式的中等长度文本数据
mediumtext		中等长度的文本数据
longblob		二进制形式的极大文本数据

类型	大小	范围	格式	描述	备注
date	3		YYYY-MM-DD	日期值	例如：birthday date
time	3		HH:MM:SS	时间值或持续时间
year	1		YYYY	年份值
datetime	8		YYYY-MM-DD HH:MM:SS	混合日期和时间值	例如：update_time datetime
timestamp	4		YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

约束条件

作用于表中字段上的规则，用于限制存储在表中的数据。

default 默认约束

保存数据时，如果未指定该字段值，则采用默认值

not null 非空约束

限制该字段值不能为空

unique 唯一约束

保证字段的所有数据是唯一，不能重复的；添加唯一约束时，数据库实际上会添加唯一索引。

primary key 主键约束

唯一标识，要求非空且唯一；主键字段，在建表时，会自动创建主键索引。

foreign key 外键约束

让两张表的数据建立连接

外键约束

# 物理外键
-- 创建表时指定
create table 表名(
	字段名 数据类型,
	...
	[ constraint ] [ 外键名称 ] foreign key(外键字段名) references 主表(字段名);
);
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

外键约束	物理外键	逻辑外键
概念	使用foreign key定义外键关联另外一张表	在业务层逻辑中，解决外键关联
优缺点	影响增、删、改的效率（需要检查外键关系）。 仅用于单节点数据库，不适用于分布式、集群场景。 容易引发数据库的死锁问题，消耗性能。	没有物理外键的缺点。

其他约束

关键字	描述
unsigned	无符号，数据类型为非负数，可增加数据长度
auto_increment	主键自增

聚合函数

Tip: null值不参与所有聚合函数运算

select
	# 聚合函数(字段列表) 
from
	# 表名

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

事务

事务是一组MySQL操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。默认mysql的事务是自动提交的。

事务控制

--开启事务
start transaction; / begin;
--提交事务
commit;
--回滚事务
rollback;

事务的四大特性

• 原子性：事务是不可分割的小单元，要么全部成功，要么全部失败。
• 一致性：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性：数据库系统提供的隔离机制，保证事务不受外部并发操作影响的独立环境下运行。
• 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

• 脏读：一个事务读到另一个事务还没有提交的数据。
• 不可重复度：一个事务先后读取同一条记录，但两次读取数据不同。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

解决方案：隔离级别

索引

可以高效获取数据的数据结构，提高查询效率。

优点	缺点
提高数据查询的效率，降低数据库的IO成本。 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引会占用存储空间。 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

存储引擎

索引底层数据结构

MySQL数据库支持的索引，如Hash索引、B+Tree索引、Full-Text索引，默认指B+Tree结构组织的索引。

二叉搜索树

红黑树

Tip： 不采用二叉搜索数，红黑树的原因是大数据量情况下，层级深，检索速度慢。
B+Tree（多路平衡搜索树）二分查找

B+Tree的特点：

• 每一个节点，可以存储多个key（有n个key，就有n个指针）。
• 所有数据都存储在叶子节点，非叶子节点仅用于索引数据。
• 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。

创建索引

create [unique] index 索引名 on 表名(字段名...)；

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

索引失效

MySQL常用语句

规定：
以英文的分号结尾
不区分字母大小写
注释：
 1.单行注释：-- 注释内容; # 注释内容;（MySQL特有）
 2.多行注释：/* 注释内容; */

开启mysql服务
Win+R输入services.msc

——数据定义语言——

用来定义数据库对象（数据库，表，字段）。

进入mysql

mysql -u root -p
Enter password: 1234

退出mysql

exit

查看所有数据库

show databases;

查看当前使用的是哪个数据库

select database();

使用数据库

use 数据库名;

创建数据库

create database [ if not exists ] 数据库名;

删除数据库

drop database [ if not exists ] 数据库名;

创建数据表

create table 表名( 字段1 字段数据类型 [ (数据长度) ] [ 约束 ] [ comment '字段1注释' ], 字段2 字段数据类型 [ (数据长度) ] [ 约束 ] [ comment '字段2注释' ] ) [ comment '表注释' ];

查看当前数据库下的数据表

show tables;

查看数据表的结构

desc 表名;

查看数据表的建表语句

show create table 表名;

添加字段

alter table 表名 add 字段名 数据类型 [ (数据长度) ] [ comment '注释' ] [ 约束 ];

修改字段类型

alter table 表名 modify 字段名 新数据类型 [ (数据长度) ];

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名  新数据类型 [ (数据长度) ] [ comment '注释' ] [ 约束 ];

删除字段

alter table 表名 drop column 字段名;

修改表名

rename table 表名 to 新表名;

删除数据表

drop table [ if exists ] 表名;

——数据操作语言——

用来对数据库表中的数据进行增删改。

添加数据(INSERT)

Tips:
 1.插入数据时，指定的字段顺序需要与值得顺序时一一对应。
 2.字符串和日期型数据应该包含在引号中。
 3.插入的数据大小，应该在字段的规定范围内。

指定字段添加数据

insert into 表名 ( 字段名1, 字段名2 ) values ( 值1, 值2 );

全部字段添加数据

insert into 表名 values ( 值1, 值2, ... );

批量添加数据（指定字段）

insert into 表名 ( 字段名1, 字段名2 ) values ( a值1, a值2, ... ), ( b值1, b值2, ... );

批量添加数据(全部字段)

insert into 表名 values ( a值1, a值2, ... ), ( b值1, b值2, ... );

修改数据(UPDATE)

Tips: 如果没有条件，则会修改整张表的所有数据

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [ where 条件 ];

删除数据(DELETE)

Tips:
 1.如果没有条件，则会删除整张表的所有数据。
 2.不能删除某个字段的值（如果有这一需求，可以使用UPDATE,将该字段的值设置为NULL）。

delete from 表名 [ where 条件 ];

——数据查询语言——

用来查询数据库表中的记录。

-- 查询语法
select
	# 字段列表
from
	# 表名列表
where
	# 条件列表
group by
	# 分组字段列表
having
	# 分组后条件列表
order by
	# 排序字段列表
limit
	# 分页参数

基本查询

查询多个字段

select 字段1, 字段2, 字段3 from 表名;

查询所有字段（通配符）

select * from 表名;

设置别名

select 字段1 [ as 别名1 ], 字段2 [ as 别名2 ], 字段3 [ as 别名3 ] from 表名 [ as 表别名 ];

去除重复记录

select distinct 字段列表 from 表名;

条件查询

select 字段列表 from 表名 where 条件列表;

条件构造

比较运算符	功能
between…and…	在某个范围之内（包含最小值、最大值）
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配（_表示匹配单个字符，%表示匹配任意个字符）
is null	是空值

逻辑运算符	功能
and 或 &&	并且（多个条件同时成立）
or 或 | |	或者（多个条件任意一个成立）
not 或 !	非，不是

分组查询

Tip:
 1.分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
 2.执行顺序：where > 聚合函数 > having

select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];

where与having区别

 1.执行时机不同：where分组之前过滤
 2.判断条件不同：having可以对聚合函数进行判断

排序查询

select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 order by 字段1 排序方式1, 字段2 排序方式2 ...;

排序方式

ASC：升序（默认）
DESC：降序

Tips： 如果要多个字段排序，只有当第一个字段字相同时，才会根据第二个字段进行排序，以此类推。

分页查询

select 字段列表 from 表名 limit 起始索引, 查询记录数;

Tips：
 1.起始索引指的是要从0开始往后查询每一条记录。
  起始索引 = （页码 - 1）* 每页展示记录数
 2.查询记录数是这一页我需要展示多少条记录。

多表查询

指从多张表中查询数据，在多表查询时，需要消除无效的笛卡尔积，笛卡尔积：笛卡尔乘积是指两个集合（A集合和B集合）的所有集合情况。

——连接查询——

内连接

相当于查询A、B交集部分数据

--隐式内连接
select 字段列表 from 表1, 表2 where 条件...;
--显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

外连接

左外连接

查询左表所有数据（包含两张表交集部分数据）

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

右外连接

查询右表所有数据（包含两张表交集部分数据）

select 字段列表 from 表1 right [outer] join 表2 连接条件...;

——子查询——

在查询当中又嵌套查询，子查询外部的语句可以是insert/update/delete/select，最常见的是select。

select * from t1 where column = (select column1 from t2...);

标量子查询

子查询返回的结果为单个值（数字、字符串、日期等）
常用的操作符：

操作符	说明
=	等于
<>	不等于

列子查询

子查询返回的结果为一列（可以是多列）
常用的操作符：in、not in

行子查询

子查询返回的结果为一行

表子查询

子查询返回的结果为多行多列，常作为临时表

——MySQL优化——

如何去定位慢查询

慢查询出现的情况：

• 聚合查询 新增临时表
• 多表查询 优化sql语句结构
• 表数据量过大查询 添加索引
• 深度分页查询

表象：页面加载过慢，接口压测响应时间过长（超过1s）。

开源工具进行监听和调试

调试工具：Arthas
运维工具：Prometheus

慢日志查询（调试阶段）

Tips：开启会损耗mysql性能

# 进入mysql
mysql -u root -p
Enter passsword: 1234
mysql>	show variables like 'slow_query%';	# 查看是否开启慢日志查询
mysql>	show variables like '%data%'; # 查看data存放路径，一般my.ini文件在data文件的上一级

需要在MySQL的配置文件中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒，SQL语句执行时间操过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2

service mysqld restart # 重启mysql服务

SQL执行计划

可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息。

-- 直接在select语句之前加入关键字explain/desc
explain select 字段列表 from 表名 where 条件;

possible_key 当前索引可能使用的索引

key 当前sql实际命中索引

key_len 索引占用的大小

通过key和key_len可以检查是否命中了索引

Extra 额外的优化建议

Extra	含义
Using where; Using Index	查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
Using Index condition	查找使用了索引，但需要回表查询数据

type sql的连接类型

性能由好到差为NULL > system > const > eq_ref > ref > range > index > all

type	含义
system	查询系统中的表
const	根据主键查询
eq_ref	主键索引查询或唯一索引查询，只能返回一条数据
ref	索引查询
range	走的是索引，但范围查询
index	检索索引树扫描
all	不走索引，全盘扫描

索引

• 索引是帮助MySQL高效获取数据的数据结构（有序）。
• 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的底层数据结构

MySQL的innoDB引擎采用B+树的数据结构来存储索引

• 阶数更多，路径更短
• 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
• B+树便于扫库和区间查询，叶子节点是一个双向链表

数据结构对比

B树与B+树对比

1. 磁盘读写代价B+树更低
2. 查询效率B+树更加稳定
3. B+树便于扫库和区间查询