mysql 的用户与权限管理
create user zhangsan identified by ‘123123’;
mysql的权限管理
每次更新权限后记得刷新权限
FLUSH PRIVILEGES;
GRANT
[权限]
ON [库.表]
TO [用户名]@[IP]
IDENTIFIED BY [密码]
# WITH GRANT OPTION;grant select,insert,delete,drop on atguigudb.* to li4@localhost;
#给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。grant all privileges on *.* to joe@'%' identified by '123';
#授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，密码设为123.查看自己的权限
show grants;
sql_mode
MySQL的sql_mode合理设置
sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。
show variables like 'sql_mode';
ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：
在严格模式下，不允许日期和月份为零
NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：
禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT：
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
ORACLE：
设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.**mysql 密码多少天过期策略 、密码强度策略（具体百度，没做笔记）
**
mysql 逻辑架构
mysql 查询缓存
show variables like ‘%query_cache_type’
Innodb和Myisam的区别
Innodb 插入 修改困难，浪费时间和空间。
Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
innodb支持事务.myisam不支持事务
innodb支持外键，myisam不支持外键
innodb是聚集索引（聚簇索引），myisam是非聚集索引（非聚簇索引）
innodb不支持FULLTEXT类型全文索引，myisam支持FULLTEXT类型全文索引
innodb支持的最小颗粒度是行锁，myisam支持的最小颗粒度是表锁ibd 表空间、段、区、页
因为磁盘物理地址不连续，区的概念 把连续的页存到区中。空间换时间
为了顺序io 查询效率快innodb 支持自适应hash索引。默认自动开启 （比如 二级索引进行hash 运算，返回数据页，就不用一个一个数据页去检索。）
页 文件头 文件尾
数据 数据头 数据尾
MySQL5.7默认使用的行格式就是DYNAMIC，溢出列的所有数据全部存储在「溢出页」中。
MySQL8.0 支持索引倒叙，隐藏索引。查看 mysql慢查询日志
show variables like '%slow_query_log'
show variables like '%slow_query_log_file'
查看 mysql慢查询时长
show variables like '%long_query_time';
查看慢查询次数
show status like '%slow_queries%'
select * from business_order bo limit 100000
派生表 是使用 另一个查询结果 作为新的表 命名派生表。
partitions 分区。 查询将匹配记录的分区。 对于非分区表，该值为 NULL。
type (表示访问表的方式)
system > const > eq_ref > ref > range > index > ALLeq_ref 被驱动表 唯一索引 或者主键索引关联
索引覆盖 是联合索引， 查询字段 和where 条件 正好符合 联合索引，不用回表。
possible_keys 可能用到的索引。
key 实际用到的索引。
key_len。表示执行计划所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择索引下推。指的是 二级索引 联合索引第二个字段失效，用不上索引，会表次数就会增多，下推 就是失效，还在二级索引进行过滤，减少回表次数
/* 开启索引下推优化，默认开启 */
SET optimizer_switch = 'index_condition_pushdown=on';
/* 关闭索引下推优化 */
SET optimizer_switch = 'index_condition_pushdown=off';
1. 索引下推功能是mysql 5.6推出优化回表的操作，只支持向上兼容，低版本是不支持的；
2. 索引下推优化的只是回表次数，扫描行数还是一样的。事务最重要的四个特性通常被称为 ACID 特性
A - Atomicity 原子性: 一个事务是一个不可分割的最小单位，事务中的所有操作要么全部成功，要么全部失败，没有中间状态。原子性主要是通过事务日志中的回滚日志(undo log)来实现的，当事务对数据库进行修改时，InnoDB 会根据操作生成相反操作的 undo log，比如说对 insert 操作，会生成 delete 记录，如果事务执行失败或者调用了 rollback，就会根据 undo log 的内容恢复到执行之前的状态。
C - Consistency 一致性: 事务执行之前和执行之后数据都是合法的一致性状态，即使发生了异常，也不会因为异常引而破坏数据库的完整性约束，比如唯一性约束等。
I - Isolation 隔离性: 每个事务是彼此独立的，不会受到其他事务的执行影响，事务在提交之前对其他事务不可见。隔离性通过事务的隔离级别来定义，并用锁机制来保证写操作的隔离性，用 MVCC 来保证读操作的隔离性，将在下文详细介绍。
D - Durability 持久性: 事务提交之后对数据的修改是持久性的，即使数据库宕机也不会丢失，通过事务日志中的重做日志(redo log)来保证。事务修改之前，会先把变更信息预写到 redo log 中，如果数据库宕机，恢复后会读取 redo log 中的记录来恢复数据脏读
脏读指一个事务访问到了另一个事务未提交的数据
不可重复读
不可重复读指一个事务多次读取同一数据的过程中，数据值 内容 发生了改变，导致没有办法读到相同的值，描述的是针对同一条数据 update/delete 的现象，
幻读
幻读指一个事务多次读取同一数据的过程中，全局数据（如数据行数）发生了改变，仿佛产生了幻觉，描述的是针对全表 insert/delete 的现象，如下过程查看隔离级别的命令为
SHOW VARIABLES LIKE 'transaction_isolation';
# 或者
SELECT @@global.tx_isolation, @@tx_isolation;读未提交 Read Uncommitted
允许读取未提交的内容，这种级别下的查询不会加锁，因此脏读、不可重复读、幻读都有可能发生。
读已提交 Read Committed
只允许读取已提交的内容，这种级别下的查询不会发生脏读，因为脏数据属于未提交的数据，所以不会被读取，但是依然有可能发生不可重复读和幻读。
可重复读 Repeatable Read （MySQL 的默认隔离级别）
使用行级锁来保证一个事务在相同查询条件下两次查询得到的数据结果一致，可以避免脏读和不可重复读，但是没有办法避免幻读。
需要特殊注意的是，Innodb 在 Repeatable Read 下通过 MVCC 提供了稳定的视图，因此 Innodb 的 RR 隔离级别下是不会出现上述幻读异常中的第一个场景的，但第二个场景还是会出现。
串行化 Serializable
使用表级锁来保证所有事务的串行化，可以防止所有的异常情况，但是牺牲了系统的并发性
二、日志
在使用 redo 日志与 undo 日志的情况下，UPDATE一条数据的大致过程为：
反向UPDATE记入undo log ⇒ \Rightarrow⇒ 更新数据 ⇒ \Rightarrow⇒ 记入redo log
1.- redo日志的特点
redo 日志是为了持久性，保证不管发生什么数据一定可以存入磁盘。
redo 日志是物理日志。
redo 日志不是事务提交才记录，而是随着事务的执行就记录（Write-Ahead Logging，在持久化一个数据页之前，先将内存中相应的日 志页持久化）
**- redo日志的刷盘**
redo 日志生成时是记录到 redo log buffer（重做日志缓存）中的，这一过程是实时的、随着操作就记录到 buffer 中的。
然后根据一定的策略，定时地转入操作系统的 page cache（文件系统缓存）中，最后才写入 redo log file（重做日志）中的。
redo 日志的三种刷盘策略（innodb_flush_log_at_trx_commit参数的值分别为0、1、2时）：
0 - 全不做： 提交事务时什么也不做，系统每隔1秒执行一次 redo log buffer ⇒ \Rightarrow⇒ page cache ⇒ \Rightarrow⇒ redo log file（MySQL 和操作系统谁挂了都不行）。
1 - 全做： 每次提交事务时就执行一次 redo log buffer ⇒ \Rightarrow⇒ page cache ⇒ \Rightarrow⇒ redo log file（MySQL 和操作系统谁挂了都没事）。
2 - 做一半： 每次提交事务时执行 redo log buffer ⇒ \Rightarrow⇒ page cache，操作系统自己决定什么时候执行 page cache ⇒ \Rightarrow⇒ redo log file（MySQL 挂了没事，操作系统挂了不行）。
2.Undo 日志
redo log 是事务持久性的保证，undo log 是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个 undo log。
.如何理解 undo 日志？
（1）事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：
情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误， 操作系统错误 ，甚至是突然断电导致的错误。
情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。
（2）以上情况出现，我们需要把数据改回原先的样子，这个过程称之为回滚，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合原子性要求。
（3）每当我们要对一条记录做改动时（这里的改动可以指 INSERT、DELETE、UPDATE，不包括 SELECT），都需要"留一手"——即把回滚时所需的东西记下来。比如：
3.Binlog日志
概念
Binary log(二进制日志)，简称Binlog。 Binlog是记录所以数据表结构变更以及表数据修改的二进制日志，不会记录select和show这类操作。Binlog是以事件形式记录，还包括语句所执行的消耗时间。Binlog是MySql Server自己的日志，但是Redo Log是基于InnoDB引擎所特有的日志。
开启Binlog日志的最重要使用场景：
主从复制
在主库开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
数据恢复
通过mysqlbinlog工具来恢复数据。
4. relay_log 日志
从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致
Redo Log和Binlog区别
Redo Log是属于InnoDB引擎功能，Binlog是属于MySql Server自带功能，并且是以二进制文件记录。
Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写，写完一个写下一个，不会覆盖使用。
Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。
mysql主从同步的三种模式
异步复制
半同步复制
全同步复制
记录模式
Statement Level模式
Row Level模式
Mixed模式（混合模式）你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录融掉就好了。(对于每个INSERT，InnoDB 存储引擎会完成一个DELETE）
你删除了一·条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。（对于每个DELETE，InnoDB 存储引擎会执行一个 INSERT）
你修改了一荼记绿，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。（对于每个UPDATE，InnoDB 存储引擎会执行一个相反的 UPDATE，将修改前的行放回去）
侨总：
按锁粒度从大到小分类：表锁，页锁和行锁；以及特殊场景下使用的全局锁
如果按锁级别分类则有：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁；
以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题，引入的Record Lock（行记录锁）、Gap Lock（间隙锁）、Next-key Lock（Record Lock + Gap Lock结合）等；
还有就是我们面向编程的两种锁思想：悲观锁、乐观锁。
面试官：袁芳你怎么看？
HR小姐姐：。。。
面试官：小侨啊，那你来谈一谈你对表锁、行锁的理解吧。
表锁
侨总：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，大大降低并发度。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
行锁
侨总：与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。
适用场景：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。
页锁
除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
使用页级锁定的主要是BerkeleyDB存储引擎。
面试官：那全局锁是什么时候用的呢？
全局锁
侨总：首先全局锁，是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。
MySQL提供加全局读锁的命令：Flush tables with read lock (FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。
风险：
如果在主库备份，在备份期间不能更新，业务停摆
如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟同步
还有一种锁全局的方式：set global readonly=true ，相当于将整个库设置成只读状态，但这种修改global配置量级较重，和全局锁不同的是：如果执行Flush tables with read lock 命令后，如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。但将库设置为readonly后，客户端发生异常断开，数据库依旧会保持readonly状态，会导致整个库长时间处于不可写状态，试想一下微信只能看，不能打字~~
HR小姐姐：那微信不就完蛋了？
侨总：是啊，抓紧找老实人背锅！
面试官：不错，你把这几种锁的侧重点都表述清楚了。那你再说一下你对不同级别的那几种锁的使用场景和理解吧？
侨总：MySQL基于锁级别又分为：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁
共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁
侨总：对于共享（读）锁、排他（写）锁，比如咱们住酒店，入住前顾客都是有权看房的，只看不住想白嫖都是可以的，前台小姐姐会把门给你打开。当然，也允许不同的顾客一起看（共享 读），比如和这位杀马特小伙子。
看房时房间相当于公共场所，小姐姐嘱咐不能乱涂乱画，也不能偷喝免费的矿泉水。。如果你觉得不错，偷偷跑到前台要定这间房，交钱后会给你这个房间的钥匙并将房间状态改为已入住，不再允许其他人看房（排他 写）。
对了，当办理入住时前台小姐姐也会通知看房的杀马特小伙子说这间房已经有人定了！！等看房的杀马特小伙儿骂骂咧咧出门后，看到满头大汗的你，鄙夷着咽了一口口水，咳tui！然后你锁上门哼着歌儿，开始干那些见不得人的事儿~~直到你退房前，其他人无法在看你的房。
可见，读锁是可以并发获取的（共享的），而写锁只能给一个事务处理（排他的）。当你想获取写锁时，需要等待之前的读锁都释放后方可加写锁；而当你想获取读锁时，只要数据没有被写锁锁住，你都可以获取到读锁，然后去看房。
另外还有意向读\写锁，严格来说他们并不是一种锁，而是存放表中所有行锁的信息。就像我们在酒店，当我们预定一个房间时，就对该行（房间）添加 意向写锁，但是同时会在酒店的前台对该行（房间）做一个信息登记（旅客姓名、男女、住多长时间、家里几头牛等）。大家可以把意向锁当成这个酒店前台，它并不是真正意义上的锁（钥匙），它维护表中每行的加锁信息，是共用的。后续的旅客通过酒店前台来看哪个房间是可选的，那么，如果没有意图锁，会出现什么情况呢？假设我要住房间，那么我每次都要到每一个房间看看这个房间有没有住人，显然这样做的效率是很低下的。杀马特小伙儿表示支持！
读写锁、意向锁的兼容性如下所示；
锁类型 读锁 写锁 意向读锁 意向写锁
读锁 兼容 冲突 兼容 冲突
写锁 冲突 冲突 冲突 冲突
意向读锁 兼容 冲突 兼容 兼容
意向写锁 冲突 冲突 兼容 兼容
侨总：再回到MySQL原理上讲
1 共享（读）锁（Share Lock）
共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。
如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。
SQL显示加锁写法:
SELECT … LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。
2 排他（写）锁（Exclusive Lock）
排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。
SQL显示加锁写法:
SELECT … FOR UPDATE;
在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。
3 意向锁（Intention Lock）
意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。
共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;
面试官：（这小子有两下子）嗯，袁芳你怎么看？
HR：通俗易懂，我听懂了~~
面试官：好，那最后一个问题，你上面提到了乐观锁和悲观锁，谈谈你对它的看法吧。
侨总：其实悲观锁和乐观锁，也并不是 MySQL 或者数据库中独有的概念，而是并发编程的基本概念。主要区别在于，操作共享数据时，“悲观锁”即认为数据出现冲突的可能性更大，而“乐观锁”则是认为大部分情况不会出现冲突，进而决定是否采取排他性措施。
反映到 MySQL 数据库应用开发中，悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。乐观锁则与 Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。
MySQL的多版本并发控制 （MVCC），其本质就可以看作是种乐观锁机制，而排他性的读写锁、两阶段锁等则是悲观锁的实现。
