MySQL学习记录，持续更新

一、基础篇

1.MySQL概述

数据库：database

数据库管理系统：DBMS，操作和管理数据库

SQL：操作关系型数据库的编程语言

连接

• 使用MySQL提供的客户端命令行工具

• cmd mysql -u root -p

关系型数据库

建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

A. 使用表存储数据，格式统一，便于维护。

B. 使用SQL语言操作，标准统一，使用方便。

2.SQL

结构化查询语言。定义操作关系型数据库统一标准。

DDL数据定义语言

数据库结构

创建数据库：creat database if not exists 数据库名 default charset 字符集 collate 排序规则；

删除数据库：drop database if exists 数据库名；

查询数据库：show databases;

切换数据库：use 数据库名；

查询当前数据库：select database();

表结构：

创建：

create table tb_user(

id int comment '编号',

`name varchar(50) comment '姓名'

) comment '用户表';

展示表：show tables;

查看表结构：desc 表名；

查看建表语句：show create table 表名；

表操作：

添加：alter table 表名 add 字段名 类型 comment 注释 约束；

修改:数据类型：alter table 表名 modify 字段名 新类型；

字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型 注释 约束；

删除：alter table 表名 drop 字段名；

修改表名：alter table 表名 rename to新表名

表删除：drop table if exists 表名

DML数据操作语言

对数据库中表的数据记录进行增、删、改操作。

添加：insert into 表名 values (3,'3','韦一笑','男',58),(第二条记录),……;

更改：update 表名 set 修改项 = 修改内容 where 条件;

删除：delete from 表名 where 条件；

DQL数据查询语言

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

基本查询

查询字段：select 字段名，字段名 from 表名；

查询设置别名：select 字段名 别名 from 表名；

查询去重：select distinct 字段名 from 表名；

条件查询

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

多个条件用逻辑运算符连接。

常用< ,>,>=,<=,=,!=,between...and...,in(a,b,c)

like(模糊匹配，代表一个字符，%任意个字符)，is null,and,or,not;

聚合函数

某一列作为整体，纵向计算

select 聚合函数（字段列表）from 表名；

常用函数：count,max,min,avg,sum。注意，null不参与运算。

分组查询

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

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组

之后对结果进行过滤。

• 判断条件不同：where不能对聚合函数进行判断，而having可以。

排序查询

select 字段列表 from 表名 order by 字段1 排序方式，字段2，排序方式；

ASC升序，desc降序

分页查询

select 字段列表 from 表名 limit 查询页码-1（索引），每页显示数。

执行顺序以及查询语法总结

DCL数据控制语言

管理数据库用户、控制数据库的访问权限

用户管理

查询用户：select * from mysql.user

新建用户：create user '用户名'@'主机名' identified by '密码'；

改密码：alter user '用户名'@'主机名' identified with mysql_native_password by'新密码'；

删除用户：drop user '用户名'@'主机名'；

权限管理

显示权限：show grants for '用户名'@'主机名';

授予权限：grant 权限 on 数据库.表名 to '用户名'@'主机名';

撤销权限：revoke 权限 on 数据库.表名 from '用户名'@'主机名';

3.函数

字符串函数

concat(s1,s2...)拼接

lower(s)转小写

upper(s)转大写

lpad(s,n,t)左填充，到n位，填充t

rpad(s,n,t)右填充

trim去首尾空格

substring(s,begin,end)取子串

数值函数

ceil()上取整

floor()下取整

mod(x,y)

rand()0-1随机数

round(x,y)x四舍五入保留y位小数

日期函数

curdata()当前日期

curtime()当前时间

now()日期+时间

year(date)

mouth(date)

day(date)

data_add(date,interval x day(mouth,year))日期date增加x天/月/年

datediff(date1,date2) date1-date2

流程函数

if(value,t,f)如果value是true，返回t，否则返回f

ifnull(value1,value2)value1不为空就返回，否则返回value2

case when val1 then res1 ...else default end val1为真返回res1，否则返回default默认值

case expr when val1 then res1 ...else default end expr==val1返回res1否则default

4.约束

概述

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

目的：保证数据库中数据的正确、有效性和完整性。

约束演示

非空约束：not null

唯一约束：unique

主键约束：一行数据唯一标识，非空且唯一primary key

默认约束：default

检查约束：check

外键约束：foreign key

建表的时候，在字段类型之后加入。

datagrip中就是default项

外键约束

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

5.多表查询

内连接

查两个表的交集

隐式内连接：select 字段列表 form 表1，表2 where 条件；

显式内连接：select 字段列表 from 表1 join 表2 on 条件；

注意可以添加别名简化书写，添加别名之后字段列表和条件都只能使用别名；

区分字段列表用表名.字段名的方法；

外连接

左表/右表以及两个表的交集

select 字段列表 from 表1 left join 表2 on条件；

查询表1的所有数据，也包含表2中的交集信息

自连接

把自己这张表查询多次。这个注意要起别名。

select 字段列表 from 表 别名 join 表 别名 on 条件。

联合查询

多次查询的结果合并起来。字段列表要求一致，要去重的话删除all.

select 字段列表 from 表 where ...

union all

select 字段列表 from 表 where ...。

子查询

可以选择分步思考操作

1.标量子查询：子查询返回来的是一个值

select 字段列表 from 表名 where 条件(关系)select字段列表 from 表名 where 条件

2.列子查询：子查询返回的是一列。

常用操作，in在指定范围中。not in 不在指定范围中。

any,some满足任意一个条件。all满足所有条件

eg:

3.行子查询

eg

4.表子查询

常用操作符：in

eg：

6.事务

一组操作，一气呵成，不可分割

控制事务

• 提交方式设置为手动

select @@autocommit;
​
set @@autocommit = 0;
​
    这是事务
​
commit;  - 正确就提交
​
rollback; - 不正确就回滚

• bagin

start transaction 或者 begin;
​
这是事务
​
commit;
rollback;

四大特性

原子性

一致性：执行完成后，所有数据保持一致

隔离性：不受外界影响

持久性：对数据的改变是永久的

并发事务问题

脏读：读还没提交的数据

不可重复读：一个事务先后读取的同一记录数据不同

幻读：查询没有，但是插入时候发现又有了。

事务隔离级别

• 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

• 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

• 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

• 串行化（serializable）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

二、进阶篇

1.存储引擎

是mysql数据库的核心，是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表的。

逻辑存储结构：表空间--段--区（1M）--页（16K）--行

存储引擎对比：

总结：InnoDB:支持事务，行锁，外键：遵循ACID

2.索引

帮助MySQL高效获取数据的数据结构(有序)。

概述

优点：提高检索效率，降低数据库IO成本，通过索引对数据排序，降低数据排序成本

缺点：存储索引占用空间，降低更新表的速度(比如增删数据)

索引结构

一般默认的索引结构：B+树

二叉树：退化成单向链表。大数据量情况下，层级较深，检索速度慢。

B树：

B+树的非叶子节点不存放实际的记录数据，仅存放索引，所以检索会比较快。

B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。（内存中一页可以放更多的索引）

B+树增删更快：B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

叶子结点之间局部性比较好：B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

MySQL的B+树优化：

在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。（叶子结点前后可以互相访问）

哈希

通常一次检索，效率高

不支持范围查询

不能通过索引完成排序操作

分类

聚集索引：索引结构的叶子结点是行数据，必须有，只有一个(主键，unique,默认生成)

二级索引：索引结构的叶子结点存放的是对应的主键。(回表查询)

索引语法

创建索引：create [unique/fulltext] index 索引名 on 表名 (字段名)；

查看索引：show index from 表名；

删除索引：drop index 索引名 on 表名；

索引名一般为:idx_字段名

SQL性能分析

查看增删改查次数：show global status like 'com';

慢查询日志：定位哪些SQL语句执行效率比较低

Profile

设置profile打开：set profiling = 1;

查看SQL耗时情况：show profiles;

各个阶段耗时情况：show profile for query_id;

CPU使用情况：show profile cpu for query_id;

explan

获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序.

索引使用

建立索引之后，查询效率大大提高

最左前缀法则：联合索引最左边的字段要存在，索引才会生效，但是与其在where中位置无关。跳过某列，则联合索引部分生效。此外范围查询>,<查询右侧的索引失效，但是<=,>=不影响。

索引失效

• 在索引上运行

• 字符串不加引号

• 模糊查询%加在关键字之前

• 使用or连接，只有两个都有索引，索引才会生效

• 数据分布：MySQL评估全表扫描比索引快

• 联合索引不符合最左前缀

SQL提示

让MySQL使用固定的索引，使用/忽略/强制使用某索引。

explain select * from 表名 use/ignore/force index(索引名) where...;

使用规则

• 覆盖索引：减少select * 的使用，多使用覆盖索引。有时候就不需要回表。

• 前缀索引：抽取字符串的一部分前缀，建立索引。

  • create index 索引名 on 表名(数据项名(前缀个数))

  • 查看索引选择性：select count(distinct substring(数据项名，起始位，尾位))/count(*) from 表名

• 单列索引和联合索引：存在多个查询条件，可以使用联合索引，避免回表。

设计原则

• 数据量大，查询频繁

• where,order by,group by条件的字段

• 区分度高的列为索引

• 字符太长可以前缀索引

• 尽量使用联合索引

• 索引并不多多益善，维护不便

• 如果索引不存储NULL，创建时用NOT　NULL约束

３.SQL优化

插入优化

• 批量插入：insert into 表名 values()、()...

• 手动提交；start transaction; 批量插入 ； commit;

• 主键顺序插入

• 大批量使用load本地导入

  • 连接服务器：mysql --local-infile -u root -p

  • 设置参数：set global local_infile = 1;

  • 加载数据：load data local infile '本地位置' into table 表名 field terminated by ',' lines terminated by '\n';

主键优化

主键按序插入：本质就是为了防止插入删除记录时候出现的频繁页合并也就是B+树的调整

• 降低主键长度

• 顺序插入，可使用aoto_increment自增主键

• 业务修改避免修改主键

• 避免使用自然主键，比如身份证号

order by优化

排序方式：

• Using filesort:全表扫描/通过索引扫描,排序缓冲区排序，不能通过索引直接返回排序结果

• Using index:通过有序索引顺序扫描返回有序数据，不需要额外排序

解决：创建索引，可以指定索引升序降序的。默认升序的。

体会

• 根据排序字段建立合适的索引，多字段排序遵循最左前缀法则

• 尽量使用覆盖索引

• 按需要指定升序降序

• filesort,增大排序缓冲区的大小

group by

和order by有点类似的

• 也可以使用索引

• 分组操作，索引满足最左前缀法则

limit

进行limit分页查询，在查询时，越往后，分页查询效率越低

优化：子查询，覆盖索引

Count

count(字段) < count(主键 id) < count(1) ≈ count(※)，所以尽量使用 count(※)。

updata优化

加了索引就是行锁，不加就是表锁，并发性能降低。

4.视图、存储过程、触发器

视图

视图只保存了查询的SQL逻辑，不保存查询结果。

语法

• 创建：creare or replace view 视图名 as 查询语句

• 查询

  • 查看视图语句：show create view 视图名称；

  • 查询视图数据：show * from 视图名；

• 修改和创建一样

• 删除：drop view 视图名称；

检查选项

语法：创建视图之后加上 with cascaded/local check option;

cascaded要检查关联视图

local不检查关联视图

视图更新：一定要和原来的表中数据一对一

作用

• 简化操作，常用查询可以定义为视图、简化一些多对多关系的表

• 安全，一些用户只通过视图查询修改所见

• 数据，屏蔽真正表结构带来的影响

存储过程

介绍

把多条SQL语句封装在一起，减少网络交互，提升效率，还可复用

语法

• 创建

create procedure 存储过程名(参数列表)
​
begin 
​
   -- sql语句
​
end;

• 调用：call 名称(参数)

• 查看

变量

系统变量：全局变量(针对所有会话)、会话变量(针对单个会话)

用户自定义变量：不用声明，用的时候直接@变量名；set @变量名 ：= 变量值；用的时候select @变量名；

局部变量：声明：declare 变量名 类型;复制set 变量名 ：= 值；select 字段 into 变量名from 表名；

if

if 条件 then
​
    ...;
​
elseif 条件 then
​
    ...;
​
else
​
    ...;
​
end if;
​
​

参数

创建进程传递参数；in , out, inout; in/out/inout+参数名+参数类型

case

case
    when 条件 then
        ...;    
    when 条件 then
        ...;
    else
        ...;
    end case;

while

while 条件 do
    ...;
end while;

repeat

repeat
    ...;
    until 条件
end repeat;

游标

存储查询结果集

存储函数

有返回值的存储过程，参数只能是IN类型

create function 函数名(n int)
return int [特征]
begin
    ...;
    return ...;
end;
​
查询：select 函数名(参数)

触发器

在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。

//创建
create trigger trigger_name
before/after INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
    trigger_stmt ;//在日志表中插入数据
END;
​
//查看
show triggers;
//删除
drop trigger trigger_name;

5.锁

全局锁

数据库进行进行逻辑备份

加锁: flush tables with read lock; 数据备份: mysqldump -uroot -p1234 itcast > itcast.sql; 释放锁: unlock tables;

表级锁

表锁

• 读锁：可同时读，不可同时写

• 写锁：不可读写

元数据锁

某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的

意向锁

避免行锁与表锁的冲突，意向锁使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

加了行锁之后自动加意向锁

行级锁

• 行锁：对单个记录的锁，防止其他事务对此进行update和delete

  • 共享锁：允许一个事务读一行，阻止其他事务获取排他锁

  • 排他锁：允许排他锁事务更新数据，阻止其他事务获得共享锁、排他锁

• 间隙锁：索引记录之间的间隙不变，防止在间隙插入

• 临键锁：行锁和间隙锁的组合

6.InnoDB引擎

1.逻辑存储结构

表-段-区1M-页16KB-行

2.架构

内存机构

三、运维篇

1.日志

1.1 错误日志

当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志.

查看日志位置：show variables like ‘%log_error%’;

1.2 二进制日志

记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，不包括查询

作用：a.灾难时的数据恢复；b.MySQL的主从复制。

show variables like ‘%log_bin%’;

• log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。

• log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。

格式show variables like ‘%binlog_format%’;

• STATEMENT：对数据修改的SQL语句

• ROW：每一行的数据变更，默认

• MIXED：混合两种

查看

mysqlbinlog 参数选项 logfilename

参数：-d指定数据库，-o忽略前n行命令，-v行事件重构为SQL语句，-vv重构并输出注释信息

删除binlog

reset master删除全部binlog

purge master logs to 'binlog.*'删除✳之前的

purge master logs before 'yyyy-mm-dd hh24:mi:ss':删除日期之前的

1.3 查询日志

记录所有操作语句，默认关闭

开启：修改 /etc/my.cnf

# 开启
general_log = 1
​
# 设置日志文件名，若不设置，文件名：host_name.log
general_log_file = mysql_query.log

1.4 慢查询

所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小min_examined_row_limit 的所有的SQL语句的日志。

开启：在MySQL的配置文件：/etc/my.cnf

# 慢查询日志
slow_query_log=1
​
# 执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。如需使用,设置参数，重启生效。

# 记录执行较慢的管理语句
log_slow_admin_statements = 1
​
# 记录执行较慢的未使用索引的语句
log_queries_not_using_index = 1

2.主从复制

2.1 概述

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步

MySQL 复制的优点主要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务。

• 实现读写分离，降低主库的访问压力。

• 可以在从库中执行备份，以避免备份期间影响主库服务

2.2 原理

从上图来看，复制分成三步：

• Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

• 从库IOthread读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

• slaveThread重做中继日志中的事件，将改变反映它自己的数据

2.3 搭建

主库配置

修改配置文件/etc/my.cnf

# mysql服务ID，集群环境中唯一
sever-id = 1
# 只读
read-only = 0
#忽略的数据
binlog-ignore-db = mysql
#指定同步数据库
binlog-do-db = db01

重启MySQL服务器systemctl restart mysqld

登录MySQL，创建远程连接账号，赋予主从复制权限

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
​
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

从库配置

修改配置文件/etc/my.cnf

# mysql服务ID，集群环境中唯一
sever-id = 1
# 只读
read-only = 1

重启MySQL服务器systemctl restart mysqld

登录mysql，设置主库配置

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', SOURCE_USER='itcast',
SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000004',
SOURCE_LOG_POS=663

开启同步：start slave;

查看主从状态：show slave status;

3.分库分表

4.读写分离