MySql

一、MySQL基本命令

1、MySql安装

参考：https://www.runoob.com/mysql/mysql-install.html
注意：安装路径千万不要包含中文字符

2、管理数据库

启动mysql：net start mysql
关闭mysql：net stop mysql
连接数据库：mysql -u root -p
选择要操作的数据库：use <databasename>
显示所有的数据库：show databases
显示当前数据库的所有表：show tables
显示表的信息：show columns from <tablename>
创建数据库：create database <databasename>（列如CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 指定字符集）
删除数据：drop <databasename>

3、数据类型

参考：https://www.runoob.com/mysql/mysql-data-types.html

• char和varchar的区别：
  char和varchar都可以指定长度，比如char(4)，varchar(4)。需要注意的是这里的长度指的是有多少字符并不是占多少字节。
  <1>char(长度)：当字符串的长度不够指定的长度时，在末尾补上空格。字符串长度为[0, 255]。Innodb对于char如果占的字节数大于等于768则会将其转换为变长字符串，比如char(255)的编码是utf8mb4。
  <2>varchar(长度)：会在字符串的前面记录添加一个或者两个字节（如果字符串所占字节数小于等于255则用一个字节否则两个字节）来标志当前串的长度。字符串所占字节数的范围是[0, 65535]。字符串长度不够指定长度时不会在末尾补空格。
  
  <3>有空格时查询的区别：对于varchar不会自动加空格，因此取出数据时会包含空格。而对于char添加数据时会补上空格，没法分清是自己加的还是系统自动补全的空格，因此不会包含空格。

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

<4>char和varchar的优缺点
char：查询效率高（不需要计算机字段的长度），不会产生内存碎片。可能会浪费存储空间。
varchar：长度可变，查询效率低（需要先取字段的长度），会产生内存碎片（更新一个字段，字段所占字节数减少了），可能会导致页分裂。
https://dev.mysql.com/doc/refman/8.0/en/char.html

(5)null值：
1、NULL只支持IS NULL、IS NOT NULL、IFNULL()操作；
2、NULL对数学比较运算符（>, =, <=, <>）运算出的结果都是FALSE；
3、索引列是允许存在NULL的（所有的null值都对应着索引；
4、DISTINCT、GROUP BY、ORDER BY中认为所有的NULL值都是相等的；
5、ORDER BY认为NULL是最小的值；
6、MIN()、SUM()、COUNT()在运算时会忽略NULL值，但是COUNT(*)不会忽略；
7、TIMESTAMP类型的字段被插入NULL时，实际写入到表中的是当前时间；
8、AUTO_INCREMENT属性的字段被插入NULL时，实际写入到表中的是顺序的下一个自增值；
9、想要禁止某个字段被设置为NULL，则对此字段设置NOT NULL属性；
10、如非必要，不要使用NULL，会带来不可预料的麻烦。
原文链接：https://blog.csdn.net/ljl890705/article/details/97263432

4、创建表（create）

create table <tablename> (<columnname> columntype)

5、检索表（retrieve）

select [distinct] column_name1, column_name2,…,column_namen
from <tablename1>, <tablename2> …
where condition1 [and,or] condition2 [and,or] …
group by
having
order by column_name1 [asc,desc]
（1）执行顺序：from… where…group by… having… select … order by…
（2）在where中的判断语句中，除了不等号可以用<>或者!=之外其他都与c,c++,java中的相同。在where中查询字符串时不区分大小写，可以通过加入关键字binary来指定查找时区分大小写比如：binary name = ‘Zhang san’。NULL与任何值比较都返回null，需要用is null 或者is not null判断是否非空。使用<=>时，若两者相等或者两者都为NULL则返回true。
（3） like子句实现模糊匹配：%匹配任意字符，也可以使用正则表达式进行匹配。
（4） union,union all, union distinct：
select column_name1, column_name2
from table1
union [distinct,all]
select column_name1,column_name2
from table2
合并两张表的搜索结果，union和union distinct作用相同都会删除重复元素，union all会保留重复结果并且union all效率远高于union。
（5）group by子句
按照某个数学对数据分组，在gruop by column_name1后可加with rollup对所有的数据求和。不指定名字时，这列的名字为NULL，可用coalesce(a,b,c)来指定这列的名字，如果anull则选择b,如果bnull则选择c,如果a!=null则选择a。
（6）inner join, left join, right join
select column_name1,column_name2,…,column_namen
from talbe1 inner join [left join,right join] table2
on xxxx
inner join：两边的列满足具体的条件时才连接，on后关于两个表的筛选条件都会生效
left join：左边的列都会取到，右边没有满足条件的列值为空，on后只有关于右边表的筛选条件会生效
right join：右边的列都会取到，左边没有满足条件的列值为空，on后只有关于左边表的筛选条件会生效

select字段和select *的区别

• 二者的差别
  <1>说法一：Select *需要先统计识别所有字段再执行下一步，明确指定字段可以减少上述操作，效率有所提升。
  <2>说法二：二者效率差距不大
• 使用哪个：推荐使用select 字段
  <1>使用Select *或取出更多的字段，带来更多内存、磁盘、cpu的开销，并且随之而来会带来更大的网络开销。
  <2>使用Select字段有可能可以使用到索引覆盖，效率大大提高。
  <3>扩展性问题：当表结构变更时select *可能会导致项目无法运行（字段增加或者字段的顺序改变了）。
  https://mp.weixin.qq.com/s?__biz=Mzg2MjEwMjI1Mg==&mid=2247486963&idx=1&sn=deaaad47aa3a1c6b3167ab028e01b61b&source=41#wechat_redirect

MySQL中in和exist已经小表驱动大表

1、小表驱动大表
根据X表中的数据去另Y表中查找数据就是X表驱动Y表，为什么要小表驱动大表呢？类似于for循环，

for i in X
	search i in Y

对于内存循环，可以使用到数据库的索引查询速度快，对于外层循环则无法使用到索引，速度慢。
2、in和exsits的区别：in是子查询驱动外层查询，exist是外层查询驱动子查询。
<1>in：子查询为驱动表，外面的表为被驱动表（主要因为in一般是非关联子查询），因此内表小外表大。

select * from X where X.id in (select id from Y);
//等价于
for i = Y.id
	search i in X.id

<2>exists：外面的表为驱动表，子查询为被驱动，因此外表大，内标表小。

select * from X where exists (select * from Y where X.id = Y.id);
//等价于
for i in X.id
	search i in Y.id

https://www.cnblogs.com/developer_chan/p/9247185.html
https://blog.csdn.net/coollei2008/article/details/100376976?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166377026916782388052126%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=166377026916782388052126&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduend~default-1-100376976-null-null.142^v49control_1,201^v3add_ask&utm_term=in%E5%92%8Cexist%20&spm=1018.2226.3001.4187

6、更新表（update）

插入：insert into <tablename>(field1,field2,…,fieldn)
values
(value1,value2,…,valuen);
更新：update <tablename>
set field1 = value1, field2 = value2,…
where …
删除：delete from <tablename>
where …

7、删除表（delete）

drop table <tablename>

8、事务（transaction）

• 原子性（Atomicity）：一个事务中的操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
• 一致性（Consistency）：在事务开始之前和事物结束之后，数据库的完整性没有被破坏。
• 隔离型（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。 开启事务
• 事务的操作
  begin
  提交事务
  commit
  事务回滚
  rollback

9、事务的完整性

• 事务的完整性是指数据库中数据的正确性和相容性
• 数据库的三大完整性
  <1>实体完整性：主码唯一且不为空
  <2>参照完整性：外码为空或者在并参照的表的主键中
  <3>用户自定义完整性：具体应用的数据符合语义要求，比如年龄要小于100

10、修改表

alter table…
添加列
alter table 表名 add columnname 类型
删除列
alter table 表名 drop columnname
修改列的类型
alter table 表名 modify columnname 类型
修改列的列名
alter table 表名 change columnname 新名字 类型

11、创建索引

创建方式：
（1）create [unique] index indexname on tablename(columnname);
（2）alter table tablename add [unique] index indexname(columnname);
（3）create table tablename(
index indexname(columnname)
)

12、创建临时表

create temporary table …
除了需要添加temporary 关键字以外，其他与正常表一样。当断开与数据库的连接后，临时表会自动被销毁。

13、面试常问问题

<1>查询语句：select
<2>DML数据操纵语句：delete，insert，update
<3>DDL数据定义语句：create，drop，alter
<4>DCL数据控制语句：grant，revoke
<5>TCL事务控制语句：commit，rollback，savepoint
<6>聚合查询：max()，min()，avg()等
<7>关联查询：inner join（取两个表中满足条件的列），left join（左边的都取，右边的取满足条件的列），right join（右边都取，左边取满足条件的列），union （两个表的列的所有排列并且去重），union all（不去重）
<8>语句的执行顺序：

<9>in 和 exists的区别：in会先将子查询结果合并为一个集合，然后判断每一条记录的task_id是不是在这个集合里。exists先取出t1的一条记录，然后执行EXIST如果EXIST返回true（子查询结果不为空时返回true）则包含这条查询结果。
in适合于子查询结果集大而外表小的情况，exist适用于子查询结果集小而外表大的情况。

SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);

<10>drop：删除表，truncate：清空表中数据，delete：删除表中满足条件的数据。执行效率drop > truncate > delete
<11>limit：select xxx from xxx limit n：取前n条数据，select xxx from xxx limit start, n：从下标start开始向后取n条数据，第一个元素的下标为0，select xxx from xxx limit start, -1从下标start开始向后取所有数据。
<12>order by: select xxx from xxx order by column1, column2 desc：注意desc只能作用到column2。
<13>时间有关的函数：year(xxx)获取年份，month(xxx)获取月份，date_format(xxx, ‘%Y-%m’)将时间按照指定格式输出
<14>单引号（‘’)，双引号（“”)，反引号（``)的区别：单引号和双引号都可以表示字符串，单引号、双引号和引号都可表示别名（select * as ‘xxx’)，反引号可以表示列名或者表名。
<15>distinct是对查询的所有字段去重所以必须要位于查询字段的最前面，如果和聚合函数使用例如count(distinct xxx)则只作用于这个字段

二、三大范式及其优缺点

1、第一范式：

属性不可再分，比如student属性下有(id，name)，这就不满足第一范式

2、第二范式：

在第一范式的基础上消除了部分函数依赖（有主键，并且所有非主属性必须完全依赖与候选码），比如(姓名，年龄，比赛时间，比赛场地，得分)中主键为（姓名，比赛场地，比赛时间），年龄只依赖于姓名，不满足第二范式。
不满足第二范式的缺点：数据冗余，插入异常，删除异常，修改异常。

3、第三范式：

在第二范式的基础上消除了传递函数依赖（所有非主属性都依赖于候选码并且非主属性不能依赖于其他非主属性）。比如（球员编号，球队编号，教练），球队编号依赖于球员编号，教练又依赖于球队编号）。
不满足第三范式的缺点：数据冗余，插入异常，删除异常，修改异常

三、数据库的事务：数据库的不可分割的操作序列

1、事务的四大特性

<1>原子性：事务是最小的执行单位，要么全做、要么全不做。
保证：undo log（回滚日志）
<2>一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态，也就是说事务要保证执行前后数据库中的数据都是正确的，执行前后数据库的完整性都没有遭到破坏。
保证：原子性、隔离性、持久性都是为了保证一致性，undo log + redo log
<3>隔离性：并发情况下，事务与事务的执行不能相互干扰，这是一致性的重要保障。
保证：锁机制和MVCC
<4>持久性：事务提交后对数据库的更改是持久的，即使数据库发生故障也不能影响。
保证：redo log（重做日志），Mysql将更新的数据先写到Buffer Pool缓存中，再定期从缓存刷到磁盘来提高执行效率。为了解决Mysql宕机造成的数据丢失，Mysql先将写操作写入日志文件中，再将更新数据写到Buffer Poll中。

2、事务的并发带来的问题：

脏读、幻读、不可重复读，丢失更新

• 脏读：B事务修改了数据但未提交，A读取B的修改，B发生回滚。
• 幻读：A对数据库的范围数据进行查询（比如id > 10)，B在此时又插入一条数据（多行也可以，删除也可以）并且也在A的查询范围之内，A再次查询发现多了一条数据好像出现了幻影一样。脏读针对的是一条数据进行修改，幻读是插入或者删除数据。
• 不可重复读：A事务读取表中的一条数据，B对这条数据进行修改，A又读这条数据，导致A两次读取的数据不一样。
• 丢失更新：
  提交：A，B同时尝试修改数据，A，B同时将数据读入，A修改，提交，B又修改提交覆盖了A的数据
  回滚：A，B同时尝试修改数据，A，B同时将数据读入，A修改，提交，B又修改后回滚

3、事务的隔离序列

<1>读未提交：上面的三个问题都没有解决
<2>读已提交：解决了脏读
<3>可重复读：在读已提交的基础上解决了不可重复读
<4>可串行化：在可重复读的基础上解决了幻读

四、数据库的锁

1、按粒度分类：

全局锁、表级锁、行级锁

• 全局锁：锁住整个数据库，整个数据库都处于只读装态，DML、DCL语句都将被阻塞。
  <1>全局锁的使用情景：数据库的全局逻辑备份，如果不锁住整个数据库，将出现数据不一致性
  <2>数据备份的相关命令

#加全局锁，这会锁住所有的数据库
mysql> flush tables with read lock;
#释放锁
mysql> unlock tables;
#逻辑备份，注意mysqldump不是mysql语句而是mysql提供的一个命令
[root@localhost ~]# mysqldump -uroot –p1234 itcast > itcast.sql

• 表级锁
  每次都锁住表级锁，锁的粒度大。
  <1>表锁
  1、读锁（read lock），添加了读锁之后所有的添加写锁和写操作（包括添加读锁的用户）都将被阻塞，但添加读锁和读操作不会被阻塞。

#加读锁
lock tables stu read;
#解锁
unlock tables;

2、写锁（write lock），添加写锁之后，除了添加锁的用户之外所有的读写操作和添加锁操作都会被阻塞。

#加读锁
lock tables stu write;
#解锁
unlock tables;

<2>元数据锁：
1、元数据可以简单理解为表的结构。表锁锁的是表中的数据，元数据锁锁的是表的结构。引入元数据锁是为了防止DML与DDL语句冲突，比执行select，insert，update，delete的过程中，如果表的结构发生了改变将导致严重的错误。元数据锁的添加与删除都是系统自动控制，无需个人干预。
2、元数据锁和表锁的操纵对象不同，但表的数据是由表的结构决定的，因此在执行DML语句的时候也会触发对元素据对象加锁。几种常见的元素据锁、触发条件还有兼容性关系如下表。

3、元数据锁的类型总结：
其实可以将元数据锁分为两类排他锁（EXCLUSIVE）和共享锁（SHARED），共享锁之间不会发生冲突，排他锁和共享锁之间会发生冲突。DML语句的执行会触发加共享锁，DDL语句的执行会触发加排他锁。其实也很好理解，对表中数据进行操作的时候不能修改表的结构，修改表的结构时候不能操纵表中数据。这里需要注意的是mysql的DDL语句是非事务的，即使为DDL语句添加了事务，DDL语句还是会自动提交，不会等到commit。如果执行了DDL语句后再执行DML语句则不会阻塞DML语句，因为DDL语句已经提交结果。
<3>意向锁
在加表锁之前除了判断这个表有没有加表锁之外还需要判断这个表有没有添加行锁，一行一行的判读有没有添加行锁太过耗时，于是就引入了意向锁。当加了行锁之后，就会对这个表加上意向锁。
1、意向共享锁(IS): 由语句select … lock in share mode添加 。与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
2、意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

• 行级锁
  MySQL中行级锁是基于索引的，也就是锁住的是索引上的索引项，锁住的并不是数据，因此如果没有建立索引就会升级为表级锁。
  <1>行锁：锁住单个记录，分为共享锁（S）和排他锁（X）
  

SQL	行锁类型
Insert，Update，Delete，Select … for update	排他锁
Select	不加锁
Select … lock in share mode	共享锁

<2>间隙锁：
锁住记录间隙（不包括该记录）确保记录的间隙不变，防止其他事务向这个间隙插入数据产生幻读。
产生间隙锁的情况：索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。
比如，对键为33的数据加锁，它会锁住[39,44]之间的数据。当向这个范围插入数据时就会阻塞插入操作。

<3>临键锁（Next-Key Lock）：行锁和间隙锁组合，锁住这个键值，并且锁住这个键值的后一键值的前面的间隙。
产生临键锁的情况：
1、对非唯一索引的某个键加锁。
比如，对18进行加锁，它或锁住[18,29)。这是因为索引是非唯一索引，仅仅锁住18是不够的，有可能在(18,29)之间再插入18。

2、对于范围查询（唯一索引和非唯一索引），锁住当前键及其后面所有的键以及无穷大。
比如id>=18，他会锁住[18,无穷)这区间的所有数据。

2、按使用性质分类：

<1> 共享锁（读锁，S锁）：一个事务加共享锁后，其他事务也可加共享锁，但不可加排他锁，在事务执行过程中可以释放共享锁。
<2>排他锁（写锁，X锁）：一个事务加排他锁后，其他事务不能加任何锁，事务执行完后才可释放锁。

• 从主观上分：
  <1>乐观锁：不加锁，在修改某个数据时，先保存副本，在执行修改后，再读取这个数据看它和之前的值是否相同，相同则写回，不同则重复上面的步骤。
  <2> 悲观锁：每次读写都加锁。

3、隔离级别和锁的关系

<1>读未提交：不加锁
<2>读已提交：读的时候加共享锁，读完后释放锁
<3>可重复读：加共享锁，事务执行完后才释放锁
<4>可串行化：读数据时添加表级共享锁，执行完后才释放

4、当前读和快照读

• 快照读：不加锁，读的是快照数据，读的可能是历史数据。
  <1>不加锁的select就是快照读。
• 当前读：加锁，读的是最新数据，一个事务读数据时需要防止其他事务修改数据，快照读有如下几种情况：
  <1>select … in share mode
  <2>select … for update
  <3>update，insert，delete

5、MVCC实现事务的隔离级别（这里指的实现快照读的可重复读和读已提交）

• redolog
  redolog：redolog由redolog buffer（内存中），redolog file（磁盘中）两部分组成。
  <1>为什么要redolog：当执行DML语句后，innodb不会直接修改数据库中的内容，而是修改Buffer Pool中缓存的内容，然后在适当的时候将脏页保存到磁盘中。innodb不会每提交一次事务就将Buufer Pool中的内容就将数据写回磁盘，因为用户执行DML语句时操纵的数据是随机的，磁盘的随机io是非常耗时的。但这也就导致了磁盘中的数据和Buffer Pool中数据的不一致性，在mysql宕机后就会导致数据丢失。
  <2>redolog：当用户执行DML语句但还没有提交事务时，先将数据页的变化保存在redolog buffer中，在提交事务时再将redolog buffer中的内容保存到磁盘中的redolog file中，当MySQL宕机时就可通过redolog file恢复数据。这里需要注意的是，将redolog buffer中内容写入redolog file时是磁盘的顺序io。
  
• undolog：
  <1>记录数据被修改前的状态，提供两个作用：事务回滚和多版本并发控制。
  <2>销毁：undo log在事务提交时并不会立马销毁，因为这些数据要用于MVCC。
  <3>存储：undo log采用段的方式进行管理，也就是回滚段。
• MVCC（Multi-Version Concurrency Control）：MVCC也就是多版本并发控制。通过维持一个数据的多个版本，在不加锁的情况下实现读-写（一个事务程读，其他事务写）安全，由于不加锁，这里的读指的是快照读。注意MVCC只是一个概念，而不是具体的算法。
  <1>MVCC在MySQL中实现的基本原理：依赖于表中的三个隐式字段、undo log版本链和read view实现的。
  <2>表中三个隐式字段：DB_TRX_ID，DB_ROLL_PTR，DB_ROW_ID，由innodb自动创建。
  <3>undo log的版本链：记录了事务对这条记录的操作的历史版本，每一个历史版本的回滚指针指向上一次事务操作的版本。
  
  <4>read view：一张表，记录了事务的id

m-ids	生成当前read view时活跃的事务id集合，也就是那些还没有提交的事务
min-trx-id	m-ids中的最小值
max-trx-id	分配给下一个事务的id，该事务还没有被创建或执行
creator-id	创建当前视图的事务的id

• MVCC实现事务的隔离级别

<1>实现读已提交（RC)
读数据时，从版本链的最新记录和所有历史版本中从上到下一找到第一个DB_TRX_ID满足以下条件的版本的数据：
DB_TRX_ID = creator-id 或 DB_TRX_ID < min-trx-id 或 min-trx-id= DB_TRX_ID < max-trx-id并且DB_TRX_ID不在m-ids中。
<2>原理：原理很简单，读未提交事务修改的数据造成了读未提交，只要我们不去读未提交事务修改的数据就可以了，上面的几个条件其实也就是过滤掉了未提交事务修改的数据。但是这并没有解决不可重复读，因为每次读操作生成的read view都是不同的。
<3>实现可重复读（RR）
在<1>的基础上，限制事务只生成一个read view，在第一读之后，后续的读操作都取第一次的read view。但是这并没有解决幻读，因为这里限制的都是对同一条数据的读写。
<4>可串行化
在<3>的基础上添加间隙锁，锁住读操作需要操作的范围，禁止在读的时候向这个范围内添加数据。

五、存储引擎与存储结构

1、MySQL的基本结构

• 连接层：完成连接处理、授权认证等工作
• 服务层：缓存查询，SQL分析和优化，部分内置函数的执行
• 引擎层：负责数据的存储和提取，索引是在引擎层实现的
• 存储层：数据的存储

2、常见的存储引擎

• MyISAM与Innodb的区别
  <1>MyISAM：不支持事务、不支持外键、主键索引是非聚集索引、保存了表的总行数、只支持表锁、可以没有唯一索引。适用以插入为主的数据库。
  <2>Innodb：支持事务，支持外键、主键索引是聚集索引，不保存表的总行数、支持行锁和表锁、有唯一索引
• 2、为什么Innodb推荐使用自增主键
  插入时自增主键总是在右侧，可以节点频繁的合并和分裂。

3、Innodb存储引擎

• Innodb存储引擎的结构：
  
  <1>表空间：一张表对应一个表空间
  <2>段：分为数据段、索引段、回滚段。索引的叶子节点和非叶子节点属于不同的段。
  <3>区：一个区大小为1MB
  <4>页：一个页默认大小16KB，一个区含有64个页
  <5>行：表中的一行数据，包含两个默认字段Trx_id事务id，和Roll_pointer回滚指针

4、日志文件

• redolog（Innodb存储引擎自己特有）
  redolog：redolog由redolog buffer（内存中），redolog file（磁盘中）两部分组成。
  <1>为什么要redolog：当执行DML语句后，innodb不会直接修改数据库中的内容，而是修改Buffer Pool中缓存的内容，然后在适当的时候将脏页保存到磁盘中。innodb不会每提交一次事务就将Buufer Pool中的内容就将数据写回磁盘，因为用户执行DML语句时操纵的数据是随机的，磁盘的随机io是非常耗时的。但这也就导致了磁盘中的数据和Buffer Pool中数据的不一致性，在mysql宕机后就会导致数据丢失。
  <2>redolog：当用户执行DML语句但还没有提交事务时，先将数据页的变化保存在redolog buffer中，在提交事务时再将redolog buffer中的内容保存到磁盘中的redolog file中，当MySQL宕机时就可通过redolog file恢复数据。这里需要注意的是，将redolog buffer中内容写入redolog file时是磁盘的顺序io。
• undolog：
  <1>记录数据被修改前的状态，提供两个作用：事务回滚和多版本并发控制。
  <2>销毁：undo log在事务提交时并不会立马销毁，因为这些数据要用于MVCC。
  <3>存储：undo log采用段的方式进行管理，也就是回滚段。
• Binlog日志
  <1>Binlog
  Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。
  <2>Binlog使用场景
  开启Binlog日志有以下两个最重要的使用场景。
  主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
  数据恢复：通过mysqlbinlog工具来恢复数据。
  <3>Binlog的三种记录模式
  Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。
  ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
  优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
  缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
  STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
  优点：日志量小，减少磁盘IO，提升存储和恢复速度
  缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。
  MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
  <4>Binlog文件结构
  MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。
  
  <5>Binlog写入机制
  Binlog写入机制根据记录模式和操作触发event事件生成log event（事件触发执行机制）将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在
  binlog文件中是连续的，中间不会插入其他事务的log event。
  <>Binlog文件操作：
  1、查看Binlog的状态，是否开启等

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

2、开启Binlog，修改my.cnf或my.ini配置文件，在[mysqld]下面增加如下命令

#开启log-bin
log-bin=ON
#binglog文件名序列号前面的值
log-bin-basename=mysqlbinlog
#Binlog记录模式
binlog-format=ROW

或者使用下面语句

binlog-format=ROW
#开启并执行binglog文件名序列号前面的值
log-bin=mysqlbinlog

3、使用mysql语句显示binlog的相关信息

#显示有哪些binlog文件
mysql>show binary logs; //等价于show master logs;
#显示binlog文件的状态
mysql>show master status;
#显示binlog文件中的事件
mysql>show binlog events;
#显示特定binlog文件中的事件
mysql>show binlog events in 'mysqlbinlog.000001';

4、通过mysqlbinlog命令查询binlog文件中的内容，注意mysqlbinlog命令与mysql命令同级，需要放在命令行中执行。

#查看文件内容，以字符显示（不能直接查看二进制文件）
mysqlbinlog --no-defaults "文件名"
#将显示的内容存储为文件
mysqlbinlog --no-defaults "文件名" > "test.sql"

5、使用mysqlbinlog Binlog恢复数据

//按指定时间恢复
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
//按事件位置号恢复
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

6、删除Binlog文件

purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
reset master; //清除所有文件

7、Redo Log和Binlog区别
Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制
文件记录。
Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不
会覆盖使用。
Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢
复使用。Binlog没有自动crash-safe能力。

六、视图、存储过程和触发器

• 视图：一张虚拟的表，creat view as select …
  <1>视图的特点：通过视图也可以对表进行更新操作，但必须与表中的行一一对应，不能出现sum()等函数或者having子句
  <2>视图的作用：简化用户对数据的操作，更加安全（可以限制用户对表的操作)，屏蔽表的变化对用户的影响。
• 存储过程：预编译的一段SQL语言
  CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
  BEGIN
  – SQL语句
  END ;
• 触发器：可以协助应用在数据库端确保 数据的完整性,、日志记录 、数据校验 等操作
  CREATE TRIGGER trigger_name
  BEFORE / AFTER INSERT / UPDATE / DELETE
  ON tbl_name FOR EACH ROW – 行级触发器
  BEGIN
  trigger_stmt ;
  END ;

七、SQL优化

1、SQL性能分析

• 查询SQL执行频率
  执行show [session|global] status可以查看服务器状态信息

#查看select语句的执行次数
show global status like 'Com_select';
#查看insert语句的执行次数
show global status like 'Com_insert';
....
#查看所有命令的执行次数
show global status like 'Com_%';

• 慢查询日志
  <1>查看慢查询日志状态：show variables like slow_query_log。默认未开启。
  <2>开启慢查询日志
  编辑/etc/my.cnf文件，添加如下两条配置

#开启慢查询日志
slow_query_log=1;
#设置查询时间，只有超过阈值的查询语句才会被记录在慢查询日志文件中
long_query_time=2;

<3>重启MySQL

systemctl restart mysqld

<4>查看慢查询日志的内容

/var/lib/mysql/localhost-slow.log

• 使用profile查看执行时间
  <1>使用select @@have_profiling;查看是否执行profile
  <2>使用set profiling=1;开启profile
  <3>查看语句执行时间

#查看每条语句及其id
show profiles;
#根据id查看具体的一条语句的执行时间
show profile for query query_id;
#查看cpu使用情况
show profile cpu for query query_id;

• 使用explain查看命令执行计划
  <1>在select语句前加explain，比如explain select * from tb_user;
  <2>执行计划中各个字段的含义

字段	含义
id	查询序列号，表示查询语句执行的顺序（一个查询可能包含子查询等），如果序号相同则从上到下执行，如果不同序列号大的查询语句先执行
select_type	select语句的类型，一个select语句中可能包含子查询等多个查询语句。常见类型有SIMPLE（不包含只查询，只有一个查询语句），PRIMARY（主查询即最外层查询语句），UNION（union中的第二个查询语句），SUBQUERY（子查询）
type	连接类型，即扫描方式。常见的有（从慢到快），system（系统表，少量数据，往往不需要磁盘IO），constant（常量连接，命中主键或者唯一索引，并且where的判断条件中的值为常量），eq_ref（主键索引或者唯一索引的等值查询，不要求查询条件为常量），ref（非主键，非唯一索引等值查询），range（索引上的范围查询），index（索引的全部扫描，不需要回表），all（全表扫描）
possible_key	可能使用到的索引
key	实际使用到的索引
key_len	使用到的索引的长度（索引所在column的长度的和）
rows	MySQL认为查询必须要执行查询的行数，是个估计值
filtered	返回结果的行数占读取的行数的百分比
Extra	Using filesort：没有使用索引进行排序，会使用quicksort进行排序，如果待排序数据大于sort_buffer_size，就会将数据分块并存储在文件中，然后再合并这些分块，一般用于order by、group by，Using temporary：创建临时表对数据进行排序（可能使用内存，也可能使用磁盘,一般用于group by），Using index：使用了覆盖索引，无需回表，Using where使用了where过滤（在使用存储引擎得到数据后需要在server层在对where条件进行过滤），Using index condition：使用索引，但仅靠索引无法得出全部数据，需要进行回表查询，Using where; Using index：如果同时出现 Using where; Using index 也是依赖索引、不需要回表查询，但由于是范围查询，需要进行过滤，null：上面的几种情况（不仅限于上面的几种情况）都没有使用到，但null不一定就代表性能不好，比如使用了索引但存在回表查询就是null

https://www.cnblogs.com/benbenhan/articles/13212861.html
https://blog.csdn.net/sz85850597/article/details/91907988
https://blog.csdn.net/belalds/article/details/80728354

2、通过索引优化查询

• 分类
  <1>根据物理结构分类
  聚簇索引：索引的键值序列与表中相应行的物理序列一致，不过需要注意的是一张表只能有一个聚簇索引。
  非聚簇索引：索引的键值序列与表中相应行的物理序列不一致。
  
  <2>从应用上划分
  普通索引：定义了索引的列可以为空或者重复
  唯一索引：定义了索引的列可以为空但不能重复
  主键索引：定义了索引的列不能为空且不能重复，由myql自动创建
  组合索引：定义在多个列上的索引
  全文索引：MyISAM引擎上才能使用
• 索引的使用场景：
  对于经常使用order by，group by，union，distinct的字段添加索引
  对于经常进行查询的字段添加索引
  尽量在数据量少的字段上添加索引
• Innodb与MyISAM引擎为什么使用B+树索引
  （1）为什么不使用Hash索引：不能进行模糊查询，范围查询，不能避免回表
  （2）为什么不使用平衡二叉树：当数据量过多时，二叉树会过高，查找效率会下降。并且进行范围查询时会变得十分麻烦。
  （3）为什么不使用B树：B树的数据存放在节点上，不适合进行范围查询。
  （4）为什么使用B+树：B+树的值存放在叶子节点上，并且叶子节点用双向链表连接，非常适合进行范围查询。
• Innodb与MyISAM上索引的区别
  假设表的结构如下，id是主键

id	name
12	b
13	a
16	g
17	f

<1>主键索引，二者都采用了B+树，Innodb是聚簇索引，MyISAM是非聚簇索引。Innodb叶节点直接存储行，MyISAM存放行的地址。（注意下面的两幅图叶子节点与非叶子节点的数字都是表的key)

<2>用户创建的索引。MyISAM的结构与主键索引一致，而对于Ioondb，它的叶子节点的值存放的是主键值。在查询到主键值后，还需要去主键索引上根据id值查找到对于的表中的行，这一步叫做回表。而MyISAM不需要回表。

• 最左匹配原则：只是对于组合索引来说。假设有组合索引（id，age，name)，它会先去匹配id，匹配成功后会匹配age、匹配不成功不向后匹配，若age匹配成功再向后匹配name。需要注意的是，这里的匹配成功指的是只匹配了一个值如果匹配的是多个值则不会向后面匹配。在Mysql5.6之后，即使当前列匹配失败，当前列仍然会使用索引因为当前列是有序的，单当前列后面的列不会再使用索引。
• 索引下推
  
  Mysql的服务层解析语句，调用引擎层执行查询，引擎层再将查询结果返回给服务层。
  在前面说到，如果where中的判断条件（这里的查询条件的列都出现在索引中）出现了范围查询，like的时候，后面的规则都不会被匹配到。在进行回表查询时，取出的结果只会满足部分条件，结果还要交给server层在进行过滤。这浪费了后面的匹配规则，因此Mysql5.6以后就将server层的过滤交给引擎层处理（也就是为什么叫索引下推），再进行索引查询时判断是否满足后面的匹配规则。这样也减少了回表的次数，因为一部分的数据已经被过滤掉。
  前面是网上的解释，在这里我有点不同的意见，索引下推最主要的作用应该是减少回表的次数，因为在索引覆盖的情况下mysql并不会进行索引下推而是使用where进行过滤。如下，在(profession, age)上建立索引，如果只查询id,会索引覆盖，并没有使用索引下推；而如果查询所有数据，需要回表，就使用了索引下推
  
  参考链接：https://blog.csdn.net/sinat_40770656/article/details/120199122
• 最左匹配原则产生的原因：
  这与B+树的实现有关，对于多个列上建立的索引，比如(id，age，name)，会先根据id排序，如果id相同再根据age排序，如果age相同再根据name排序。由于是先根据id排序的，所以必须要id匹配成功才能去匹配age。而对于范围查询，匹配的id有多个，这多个id下面的age可能是无序的。而对于name，由于是字符串，也会对字符串进行字典排序。
  如下图，我们必须先根据id去找，id相等后再根据age找，age相等后根据name找。并且发现不同age下的name是没有顺序的。
  
• 索引失效的原因
  最主要的原因是不满足最左匹配原则，MySQL有时会进行优化，如果MySQL认为全表扫描更快则不会使用索引。
• 索引失效的几种情况（针对一个索引来说）
  <1>出现范围查询(>=,=<,between and等)，范围查询右侧的列索引失效。
  在(profession,age,status)上建立非唯一索引，索引的长度为54。执行如下语句我们发现这三条语句都使用了全部索引，但实际上age>=0这个条件被分开执行了，age = 30和age>30，对于age=30用到了全部索引而age>30却并没有用到。
  
  <2>如果在索引列上进行运算，当前列及后面的列会失效，因为在将数据库中的记录取出来前运算的结果都是未知的。
  <3>字符串不加引号当前列及后面的列会失效，因为索引是根据字符串建立了而传入的却是一个数字，无法使用索引。
  <4>模糊查询：如果头部存在模糊匹配则会失效，尾部存在模糊匹配到头部不存在模糊匹配则不会失效。需要注意的时，对于字符串，mysql是从前往后按照字典序建立索引的。例如'%name会失效，这与<3>类似在取出所有数据前都不知道是否满足模糊匹配条件，但'name%不会失效。这里的失效指的是当前列是否会失效，无论哪种情况后面的列都会失效，这与<1>类似。
  <5>or连接：只有or两边的条件都有索引、并且都是索引的第一个列才会走索引。会分别使用两边的索引，然后将取得的结果取一个交集。
  <6>使用索引比全表扫描更加耗时不会使用索引。
  <7>is null 、is not null属于<6>，有时会走索引有时不会走索引
• 覆盖索引与索引覆盖
  覆盖索引：查询数据只需要从索引中获取就行了，也就查询列需要被所建的索引覆盖。Innodb中的主键索引就是覆盖索引，而MyISAM就不是覆盖索引。
  索引覆盖：就是select后面的列都从索引中取并且查询条件中的判断语句都符合最左匹配原则。索引覆盖能避免回表查询，因为需要取的值都在索引中。例如，对(profession,gae,status)的三个字段建立索引，查询所有字段时需要回表查询，而只查询id时不需要回表查询就发生了索引覆盖。
  
• 前缀索引：
  对于一非常长的字符串类型的字段，如果在这个字段上建立索引，将会导致索引文件非常大并且建立索引时间非常长，可以针对这个字符串的前面部分子串建立索引。
  <1>基本语法

create index idx_xxxx on table_name(column(n)) ;

<2>确定长度（n的值）：使用如下语句查看区分度，选择合适的区分度

select count(distinct substring(email, 1, 2))/count(*) from tb_user;

https://www.cnblogs.com/gered/p/12210055.html#_label1_0

• 哪些情况下应该建立索引
  针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 指定使用具体的索引，use建议使用，ignore忽略，force强制使用

explain select * from tb_user use/ignore/force/ index(idx_user_pro) where profession = '软件工程';

3、SQL语句优化

• 插入语句优化
  对于一次性要插入多条数据，可以使用如下优化方案。

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....

<1>批量插入

insert into tb_test values (1,'tom'),(2,'cat'),(3,'jerry');

<2>手动控制事务，将多条插入语句作为一个事务提交

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

<3>主键按顺序插入，避免乱序插入。
<4>大批量插入数据使用load命令，而非insert

#连接数据库时添加--local-infile参数
mysql --local-infile -u root -p
#开启从本地文件加载数据
set global local-infile = 1;
#加载数据,fields terminated by ','表示以','分割字段，lines terminated by '/n'表示以换行分割记录
load data local-infile '文件路径' into table 表名 fields terminated by ',' lines terminated by '/n';

• 主键优化
  mysql中索引的叶子节点和非叶子节点是分开存储的，下面看插入数据时叶子节点的变化过程。
  <1>顺序插入
  当前页没满，插入当前页
  
  当前页已满，申请一个新页
  
  <2>乱序插入
  如果插入的位置所在页已经容纳不下待插入记录，这时就会进行页分裂
  
  申请一个新页，将第一页数据的后半部分放入第三页，将带插入数据放入第三页，然后将第三页插入第一页和第二页之间
  
  <3>页合并
  当删除的记录的数量超过上限时（默认为页大小的50%），InnoDB会查看两边的页，看是否可以合并以优化空间。
  
  
  <4>索引设计原则：
  尽量降低索引长度，有序插入主键（比如AUTO_INCREMENT），避免修改主键
• order by：对于order by使用索引可以避免使用文件排序（Using filesort）
  <1>创建索引

create index idx_user_age_phone_aa on tb_user(age,phone);

<2>如果order by的两个字段一个升序一个降序就会使用文件排序，而两个同时为升序或者两个同时为降序就会使用索引。这时因为取出的数据都是先按照第一个字段升序排序然后按照第二个字段升序排序，如果order by中两个字段全为升序则直接使用取出的数据即可，如果两个字段全为降序则反向读取叶子节点中的数据即可，如果一个升序一个降序无论正向读取还是反向读取都无法满足条件，只能重新进行排序。

在上面的例子中，我们都强制使用索引，如果不强制使用索引innodb会使用使用全表扫描。因为上面的例子都需要回表，innodb认为全表扫描然后进行排序效率更高。如果索引覆盖，则不会发生这种情况。

<3>如果一个升序，一个降序则创建索引时就应该指定升序还是降序。

<4> 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

• group by：对于group by的字段建立索引，这样可以使用索引避免全表扫描
  
• limit：对于一条limit语句，如果索引覆盖则会直接走索引，否则会进行全表扫描。
  
  如果要进行分页查询，可以使用索引覆盖+子查询的方式实现。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
#10 rows in set (5.38 sec)
select * from tb_sku limit 9000000, 10;
#10 rows in set (8.24 sec)

mysql不支持如下方式

• count优化
  count有count()，count(主键)，count(1)，count(字段)。对于count()，如果没有加distinct它会统计重复的内容，不进行去重操作，实际上统计的也就是有多少非空(null)元素。
  <1>count()，count(主键)，count(1)
  count()是一个函数，这三种方式传入的数据都不可能为空，因此统计的都是表中的列数。
  1、对于count(*)它统计有多少列，表中不可能存在空列，因为主键不为空。
  2、对于count(主键)它也统计有多少列，主键不为空。
  3、对于count(1)它将每一行直接放入1，这也不会为空。
  这三种方式都统计的是列数，无需进行非空判断。innodb会去寻找最小的索引，然后统计这个索引的叶子节点的个数，主键索引的叶子节点存储了列，因此一般不会使用主键索引。下面通过例子说明，表结构如下：
  

#创建索引
create index idx_tb_sku_name on tb_sku(name);

查看三种方式的执行计划，发现它们都没有使用主键索引

查看使用主键索引和普通索引的执行时间，发现走主键索引会远远慢于普通索引

<2>count(字段)，如果这个字段设置了索引则会走索引，如果没有设置索引则不会走索引。如果这个字段设置了not null，在server层不会进行判断是否为空的操作，如果没有设置not null，查询结果返回给server层后会进行判空操作。
<3>几种方式的执行速度：
$$count(字段)<count(主键)<count(1)\approx count(\ast )$$
<4>MyISAM中的count()
MyISAM将表的列数存放在了磁盘上，在执行count()时直接去取即可。但对于innodb，支持事务，在并发的情况下会出现表的列数不一致的情况。
https://www.jianshu.com/p/5143b4d99376

• update的优化
  对于update，如果字段没有创建索引，行锁会升级为表锁。