mysql笔记

1、初识

mysql版本：5.7.37版本，select属于DML语句
DCL(Data Control Language) 数据控制语言
grant 授权
revoke 撤销 取消权限
DDL(Data Definition Language) 数据定义语言
create、drop、alter
DML(Data Manipulation Language) 数据操纵语言
insert、update、delete、(select)
DQL(Data Query Language) 数据查询语言
select

DCL
grant 权限 on 库.表 to ‘用户名’@‘允许登录的主机’ ;
revoke 权限 on 库.表 from ‘用户名’@‘允许登录的主机’ ;

DDL
create database 库 [default character set 字符集];
create table 表(字段 数据类型 选项) [engine=存储引擎 default charset=字符集];
drop database/table 库/表;
alter table 表 modify 字段 新数据类型;
alter table 表 change 旧的字段名 新的字段名 (新)数据类型;

DML
insert into 表(字段) values(值);
update 表 set 字段名=新值 where 条件;alter table 表 add 字段 数据类型;
delete from 表 where 条件;
select 字段 from 表 (where 条件);

2、主从复制的原理

文字表述

1、首先在master上开启二进制日志
2、master上数据发生变化，进行DML操作时，会产生二进制日志（Binary log）
3、master上的dump线程会通知slave上的IO线程来拿二进制日志，IO线程拿到二进制日志后会写入到slave上的中继日志（Relay log），然后SQL线程会去读取新产生的中继日志，重演二进制日志里的操作，从而达到slave和master上的数据一模一样，实现数据的一致性

3、执行sql语句很慢的解决思路

优化思路？
1、加硬件：cpu、内存、磁盘、网络带宽（最难的是cpu，cpu加不了的话就加机器、搞集群）
2、软件层面：
2.1优化Linux操作系统的内核参数、 /etc/sysctl.conf文件
2.2关闭不需要的服务和进程、
2.3mysql的配置参数的优化，在/etc/my.cnf文件里，如buffer_pool
2.4sql语句的优化：子查询，连接查询，创建索引
2.5分表分库：分表 --》将一个大表拆分成多个小表
分库 --》将多个表拆分到
分区 --》使用分区技术
2.6使用中间件缓存 --》redis
2.7使用集群
总结：优化的思想
1、加硬件
2、优化软件 --》使用最多
3、加机器
4、加缓存

4、SQL语句背后发生了什么

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行SQL：执行 SQL 共有三个阶段：
预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

5、事务 四个问题、ACID特性、隔离级别

四个问题

1、脏读（dirty read）
一个事务读到了另一个事务未提交的数据
也就是读正在改变的数据

2、不可重复读（nonrepeatable read）
在同一个事务中，同样的条件，你读取过的数据再次读取出来时发现值不一样了
背后是有人修改了数据，update

3、幻读（phantom read）
在同一个事务中，同样的条件，第1次和第2次读出来的记录数不一样
背后是有人增加了或者删除了记录， insert或delete

4、丢失更新（lost update）
一个事务的修改覆盖了另一个事务所做的修改

ACID特性

原子性(atomicity)：一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做
一致性(consistency)：事务必须是使数据库从一个一致性状态到另一个一致性状态，一致性与原子性是密切相关的
隔离性(isolation)：一个事务的执行不能被其他事务干扰
持久性(durability)：一个事务一旦提交，它对数据库中数据的改变就应该是永久的

隔离级别

读未提交 READ UNCOMMITTED
允许出现：脏读 不可重复读 幻读 丢失更新

读已提交READ COMMITTED
不允许出现：脏读
允许出现：不可重复读 幻读 丢失更新

可重复读REPEATABLE READ：默认级别
只允许出现：丢失更新
不允许出现：脏读 不可重复读 幻读

可串行化SERIALIZABLE
都不允许出现

6、mysql参数调优

wait_timeout
当MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout值为8个小时。
设置这个值是非常有意义的，比如你的网站有大量的MySQL连接请求（每个MySQL连接都是要内存资源开销的），
由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源，或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。
在设置之前你可以查看一下你的MySQL的状态（可用show processlist)，如果经常发现MySQL中有大量的Sleep进程，则需要修改wait-timeout值了。