MySQL进阶—全知全能

2022-03-23 / 数据库 MySQL 事务锁机制

概念

MySQL

原理

MySQL架构器各个模块功能

连接器：管理与安全验证：每个客户端都会建立一个与服务器连接的线程，服务器会有一个线程池来管理这些连接；如果客户端需要连接到 MySQL 还需要进行验证用户名、密码、主机信息等
解析器：主要是分析查询语句，最终生成解析树；首先解析器会对查询语句的语法进行分析，分析语法是否有问题。还有解析器会查询缓存，如果在缓存中有对应的语句，就返回查询结果不进行接下来的优化执行操作。前提是缓存中的数据没有被修改，当然如果被修改了也会被清出缓存
优化器：主要是对查询语句进行优化操作，包括选择合适的索引，数据的读取方式，包括获取查询的开销信息，统计信息等，这也是为什么图中会有优化器指向存储引擎的箭头。之前在别的文章没有看到优化器跟存储引擎之间的关系
执行器：包括执行查询语句，返回查询结果，生成执行计划包括与存储引擎的一些处理操作

Client层：
	发送SQL给服务器

Server层：
	连接器：用户与MySQL Server建立连接和维持以及权限(身份认证和权限相关)的获取，mysql -u root -p
		连接失败：账号或密码错误
		连接成功：连接器去查询权限表，查看当前用户权限

	缓存池: 执行查询语句的时候，会先查询缓存，但是每次更新都会清空缓存
		第一次select查询，会将SQL作为key，查询结果作为value形式存储在缓存
		第二次select查询，先查缓存

	分析器: 没有命中缓存，SQL语句就会经过分析器，分析器就是要先看SQL语句要干嘛，再检查你的SQL语句语法是否正确
		语法分析：select 字段 from 表 where
		词法分析：词是否写错，顺序是否乱

	优化器：按照 MySQL 认为最优的方案去执行
		优化：一个查询有多个查询条件，先执行哪个？表由很多索引，先使用哪个索引？

	执行器: 执行语句，然后从存储引擎API返回数据
		判断是否有select权限，没有报错，有就调用引擎接口进行查询
		查询过程：
			查询表第一条记录，判断条件是否符合，满足将记录放入结果集
			查询下一条，重复上一步
			返回结果集

存储引擎：主要负责数据的存储和提取

SELECT语句完整的执行顺序

SELECT DISTINCT select_list
FROM left_table
LEFT JOIN right_table ON join_condition
WHERE where_condition
GROUP BY group_by_list
HAVING having_condition
ORDER BY order_by_condition

FROM组装来自不同数据源的数据
ON -> JOIN
WHERE：基于指定的条件对记录行进行筛选
GROUP BY将数据划分为多个分组
聚集函数进行计算
HAVING子句筛选分组
计算所有的表达式
SELECT的字段
DISTINCT
ORDER BY对结果集排序

MySQL的记录如何写入到表

InnoDB存储数据的最基本单位是页SHOW GLOBAL STATUS LIKE 'innodb_page_size'，一页的大小为16KB

数据页的结构：MySQL官方文档：页的结构

Fil Header
Page Header
The Infimum and Supremum Records
User Records：行记录
Free Space
Page Directory：页目录
Fil Trailer

记录存储到数据页中的 User Records 中，多条记录在插入时就会根据主键升序排序

当记录过大，就会用到 Page Directory，Page Directory 存储了一页的 User Records 主键最小值

理解：你要找一本书的第666页，不会从第一页开始翻起吧，可以查找目录中最小靠近第666页的页码

注意：往已经装满数据的页插入记录，会根据记录的主键先找 Page Directory，找到后插入记录。因为这一页的数据已经满了，就要生成新页存储，页中主键值大于插入记录主键值的记录往后挪，数据量大的话会极大损耗性能

什么是临时表，何用到临时表，何时删除临时表？

临时表：执行SQL时会临时创建一些存储中间结果集的表，只对当前连接可见，在连接关闭后，临时表被删除并释放空间。临时表分为内存临时表和磁盘临时表，内存临时表使用的是MEMORY存储引擎，而临时表采用的是MyISAM存储引擎
使用到临时表场景：

FROM中的子查询
DISTINCT查询并加上ORDER BY
ORDER BY和GROUP BY的子句不一样时会产生临时表
使用UNION查询会产生临时表

语法

内、外连接及全连接的区别

内连接：总要求查出符合连接条件的数据，显示表之间有连接匹配的所有行
可获取两表的公共部分的记录：select * from A JOIN B ON A.Aid=B.Bid
外连接：可以允许查询出不符合查询条件的数据，分为左连接和右连接
- 左连接：保留左表所有记录，然后和右表做连接，对不满足条件的数据，右表会用NULL值和左表连接
  表A中的记录为主循环体，依次匹配表B中的记录，如果表A中Aid的值在表B中没有Bid值与之对应，则右侧以Bid代替select * from A Left JOIN B ON A.Aid=B.Bid
- 右连接：保留右表所有记录，然后和左表做连接，对不满足条件的数据，左表会用NULL值和右表连接
  表B中的记录为主循环体，依次匹配表A中的记录，如果表B中Bid的值在表A中没有Aid值与之对应，则左侧以null代替：select * from A Right JOIN B ON A.Aid=B.Bid
全连接：先以左表进行左外连接，再以右表进行右外连接select * from A FULL JOIN B ON A.Aid=B.Bid

VARCHAR(N)最多可存储多少数据

VARCHAR(N)类型的列最多可以定义65535个字节，N 代表该类型最多存储的字符数量，但在实际存储时并不能放这么多。MySQL 对一条记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节

INT(10)、CHAR(10)、VARCHAR(10)区别

int(10)中的10表示的是显示数据的长度，而char(10)和varchar(10)表示的是存储数据的大小

UNION和UNIONALL区别

IN和EXISTS区别

IN和EXISTS一般用于子查询

顺序：
使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件
使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据
索引：
in在内表查询或者外表查询过程中都会用到索引
exists仅在内表查询时会用到索引
效率：
当子查询的结果集比较大，外表较小使用exist效率更高
当子查询寻得结果集较小，外表较大时，使用in效率更高

select count(*)和select count(1)的区别

select count(*)：如果表中只有一个字段，count(*)最快
select count(1)：如果表中没有主键，那么count(1)比count(*)快

select * 和select 1的区别

select *：select * from ... 是返回所有行的所有列
select 1：selelct 常量 from ... 对应所有行，返回的永远只有一个值，即常量。所以正常只会用来判断是否有还是没有，比如exists子句

MySQL怎么创建存储过程？

MySQL存储过程是从 MySQL5.0 开始增加的新功能。存储过程的优点最主要的还是执行效率和SQL代码封装。特别是 SQL 代码封装功能，如果没有存储过程，在外部程序访问数据库时，要组织很多 SQL 语句。特别是业务逻辑复杂的时候，一大堆的 SQL 和条件夹杂在代码中，让人不寒而栗。现在有了 MySQL 存储过程，业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高

事务

事务特性-ACID

原子性（atomicity）：事务不可分割，事务中的操作要么都做，要么都不做
转账转到一半，系统出问题了，回滚事务，不会出现只跑一半的情况
一致性（consistency）：事务执行会使数据从一个状态切换到另一个状态，数据总量不变
能量守恒：钱转来转去，不会多也不会少
隔离性（isolation）：事务内部操作及使用数据对其他并发事务是隔离的，并发执行的事务之间不相干扰
转账过程中，要对账号金额进行汇总，由于转账事务未提交，汇总的事务是不可见的
持久性（durability）：事务一旦提交，它对数据库中数据的改变就应该是永久性

ACID是靠什么保证的？

原子性：undo log保证，记录需要回滚的日志，事务回滚时撤销已经执行成功的SQL

一致性：由其他三大特性保证，程序代码要保证业务上的一致性

隔离性：MVCC保证

持久性：redo log保证，MySQL修改数据的时候会在redo log中记录日志数据，就算数据没有保存成功，只要日志保存成功了，数据仍然不会丢失

隔离级别

多个事务是相互隔离，若多个事务要操作同一份数据可能引起问题，设置不同的隔离级别可以解决(安全级别越高，效率越低)

数据问题

脏读（Dirty Read）：一个事务读取到另一个事务没有提交的数据
不可重复读（Nonrepeatable Read：同一事务中，两次读到的数据不一样。事务 B 读取了两次数据资源，在这两次读取的过程中事务 A 修改了数据，导致事务 B 在这两次读取出来的数据不一致
幻读：事务 B 前后两次读取同一个范围的数据，在事务 B 两次读取的过程中事务 A 新增了记录，导致事务B 后一次读取到前一次查询没有看到的行

隔离级别

READ UNCOMMITED：未提交读。允许脏读。事务可以提交为提交的数据
READ COMMITED：已提交读。防止脏读。Oracle默认，一个事务从开始到提交前对数据所做的修改对其他事务是不可见的
REPEATABLE READ：可重复读。防止脏读和不可重复读。MySQL默认，一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据
SERIALIZABLE：可串行化。防止脏读、幻读，不可重复读。事务序列化执行，事务只能一个接着一个地执行，但不能并发执行

MVCC

数据库并发场景

读读：不存在任何问题，也不需要并发控制
读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
写写：有线程安全问题，可能存在更新丢失问题

解决问题：

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

实现原理：MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照如何支持事务

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲奕了，不同的事务 session 会看到自己特定版本的数据—版本链

MVCC只在已提交读（READ COMMITTED）和不可重复度（REPEATABLE READ）两个隔离级别下工作。其他两个隔离级别够和 MVCC 不兼容，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁

当前读：读取的是数据库最新版本，在读取时要保证其他事务不会修该当前记录，会对读取记录加锁

快照读：不加锁读取操作即为快照读，使用 MVCC 来读取快照中的数据，避免加锁带来的性能损耗
作用就是在不加锁的情况下，解决数据库读写冲突问题，并且解决脏读、幻读、不可重复读等问题，但是不能解决丢失修改问题

索引

索引概念

索引：一种数据结构，能够快速检索数据库数据。MySQL的 InnoDB 引擎，索引默认的数据结构是 B+ Tree

B Tree和B+ Tree区别

B Tree：B Tree演示
- 非叶子节点和叶子节点均存放键和值
- 任何关键字出现且只出现在一个节点中
- 搜索有可能在非叶子节点结束
- 所有叶子节点都出现在同一层，且叶子节点不包含任何关键字信息
B+ Tree：B+ Tree演示
- 非叶子节点只有键没有值，数据记录都存放在叶子节点中
- 所有叶子节点之间都有一个链指针
- 多路平衡查询树，所以ta的节点是天然有序的（左子节点小于父节点、父节点小于右子节点）

InnoDB为什么使用B+ Tree而不是B Tree

空间利用率更高：因为B Tree每个节点要存储键和值，而B+ Tree的内部节点只存储键，这样B+ Tree的一个节点就可以存储更多的索引，从而使树的高度变低，减少I/O次数，使得数据检索速度更快
B+Tree 中，叶子结点通过指针连接在一起，支持范围查询，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动
B+ Tree的性能更加稳定，因为在B+ Tree中，每次查询都是从根节点到叶子节点，而在B Tree中，要查询的值可能不在叶子节点，在内部节点就已经找到

B+ Tree与红黑树区别，索引为啥不用红黑树

B+树的高度要比红黑树小，有效减少了磁盘的随机访问
B+树的数据节点相互临近，能够发挥磁盘顺序读取的优势（缓存）
B+树的数据全部存于叶子结点，而其他节点产生的浪费在经济负担上能够接收，红黑树存储浪费小

B Tree更适用于磁盘读取，红黑树更适用于内存读取

InnoDB一颗B+ Tree可以存放多少行数据

约2千万

叶子节点和非叶子节点上存储的数据格式略有差异），我们可以简单算一下

计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块

假设数据库中一条记录是 1KB，那么一个页就可以存 16 条数据（叶子结点）；对于非叶子结点存储的则是主键值+指针，在 InnoDB 中，一个指针的大小是 6 个字节，假设我们的主键是 bigint ，那么主键占 8 个字节，当然还有其他一些头信息也会占用字节我们这里就不考虑了，我们大概算一下，小伙伴们心里有数即可：
16*1024/(8+6)=1170
即一个非叶子结点可以指向 1170 个页，那么一个三层的 B+Tree 可以存储的数据量为：
1170*1170*16=21902400
可以存储 2100万条数据
在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了

索引原理

索引扫描的底层原理

MySQL 的 InnoDB 存储引擎，索引默认的数据结构是 B+ Tree，B+ Tree是一种多路平衡查询树，所以ta的节点是天然有序的（左子节点小于父节点、父节点小于右子节点）

查询先走索引（按照页地址快速锁定查找记录），如果查询数据列存在索引中，返回即可，否则需要回表，全表扫描（不走索引，从最左侧的叶子结点逐行查找记录）

回表：通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据

InnoDB如何支持范围查找能走的索引？

范围查找：比如 WHERE 条件是主键ID>6，走索引先找主键ID=6，返回后面数据即可

索引类型

FULLTEXT：全文索引，MyISAM 引擎支持，只有 CHAR、VARCHAR、TEXT 列可创建全文索引
Hsah：可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高
B Tree：将索引值按算法存入B Tree，每次查询都是从B Tree的入口Root开始，依次遍历Node，获取Leaf
B+ Tree：

索引结构：B+ Tree与Hash对比

Hash索引：
使用场景：Hash索引一般多用于精确的等值查找
排序：Hash索引不支持排序，因为Hash表是无序的
范围查找：Hash索引不支持范围查找，不支持模糊查询及多列索引的最左前缀匹配
性能：Hash索引的性能是不稳定（Hash表存在哈希冲突）
B+ Tree：
使用场景：数据库、文件系统等场景
特点：平衡多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的二节点间有指针相关连接
排序：叶子结点都是排好序的
性能：在B+ Tree上的常规检索，每次查询都是从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高

索引种类

主键索引：建立在主键上的索引。一张数据表只能有一个主键索引，索引列值不允许有空值
唯一索引：建立在 UNIQUE 字段上的索引。一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突
普通索引：建立在普通字段上的索引
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（多个单列索引组合搜索）

主键索引

使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

主键索引的叶子结点存储的是一行完整的数据
非主键索引的叶子结点存储的则是主键值

覆盖索引

SELECT 的数据列从索引中就能够取得，不必回表，即在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。实现覆盖索引：将被查询的字段，建立到联合索引里去

聚簇索引

日常所说的主键索引，其实就是聚簇索引（Clustered Index）
主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index）

聚簇和非聚簇索引区别

MySQL 的索引类型跟存储引擎是相关，InnoDB 存储引擎数据文件跟索引文件全部放在 ibd 文件中，而MyISAM 的数据文件放在 myd 文件中，索引放在 myi 文件中，其实区分聚簇索引和非聚簇索引非常简单，只要判断数据跟索引是否存储在一起就可以了
InnoDB 存储引擎在进行数据插入的时候，数据必须要跟索引放在一起，如果有主键就使用主键，没有主键就使用唯一键，没有唯一键就使用 6 字节的 rowid，因此跟数据绑定在一起的就是聚簇索引，而为了避免数据冗余存储，其他的索引的叶子节点中存储的都是聚簇索引的 key 值，因此 InnoDB 中既有聚簇索引也有非聚簇索引，而 MyISAM 中只有非聚簇索引

聚簇索引和非聚簇索引最主要的区别：数据和索引是否分开存储
聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行
非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址

非聚簇索引一定会回表查询？

非聚簇索引的叶子节点存储的是主键，也就是说要先通过非聚簇索引找到主键，再通过聚簇索引找到主键所对应数据，后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询，不一定会进行回表查询。如果查询的数据在辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name、age等字段。假设聚簇索引是以ID为键值构建的索引，非聚簇索引是以name为键值构建的索引，select id,name from user where name = 'zhangsan';这个查询便不需要进行回表查询因为，通过非聚簇索引已经能全部检索出数据，这就是索引覆盖的情况。如果查询语句是这样，select id,name,age from user where name = 'zhangsan';则需要进行回表查询，因为通过非聚簇索引不能检索出age的值。那应该如何解决那呢？只需要将索引覆盖即可，建立age和name的联合索引再使用select id,name,age from user where name = 'zhangsan';进行查询即可。所以通过索引覆盖能解决非聚簇索引回表查询的问题

组合索引

组合索引与多个单列索引的区别

设计原则

建立索引时会考虑哪些问题

适合索引的列是出现在 WHERE 条件字句中的字段（数值类>字符类）
- 索引列避免使用 NULL，可使用 0/-1
- 索引列避免使用 !、= 或 <>判断，转化为 < <= = > >= BETWEEN-AND
  select * from table_name where a != 1
- 索引列避免使用 OR 连接查询条件
  select * from table_name where a = 1 or b = 3
- 索引字段避免使用 IS NULL/IS NOT NULL判断
  select * from table_name where a is null
- 索引列避免使用 [NOT] IN，使用EXISTS代替
  select id from stu where id in(select id from dtu)
  select id from stu where exists(select 1 from dtu where id=stu.id)
- 索引列避免使用 LIKE
  select id from stu where name like '%abc' # 索引失效
  select id from stu where name like 'abc%' # 索引生效
- 索引列避免使用表达式或函数操作
  select * from admin where year(admin_time)>2014
  select * from admin where admin_time> '2014-01-01′
- 索引类型上避免进行数据类型的隐形转换
  例如字符串一定要加引号，select * from table_name where a = '1'会使用到索引
  如果写成select * from table_name where a = 1则会导致索引失效
在选择索引列的时候，越短越好，可以指定某些列的一部分，没必要用全部字段的值
定义有外键的数据列一定要创建索引
更新频繁的字段不要有索引
创建索引的列不要过多，可以创建组合索引，组合索引的列的个数不建议太多8、大文本、大对象不建索引
out_date为索引：select ... from product where to_days(out_date)-to_days(current_date)<=30 --> select...…… from product where out_date<=date_add(current_date, interval 30 day)
联合索引
如何选择索引列的顺序：经常会被使用到的列优先、选择性高的列优先、宽度小的列优先
一张表的索引数最好不超过6个

失效场景

不符合最左前缀原则（最左优先，在检索数据时从联合索引的最左边开始匹配）
字段进行隐式数据类型转化
索引字段是VARCHAR，查询的 WHERE 条件给的确实 INT 型，会不会影响索引的使用？
执行 SELECT * FROM table_name WHERE a='a'，但是会查询出表中存在字段 a 是 0 的记录
走索引没有全表扫描效率高
- 部分ORDER BY
- 部分范围查找：SELECT * 返回数据过大，走索引回表还不如全表扫描快

最左匹配原则

最左匹配原则是什么？

最左匹配原则：最左边为起点任何连续的索引都能匹配上，遇到范围查询（<、>、between、like）停止匹配

为什么要遵守最左前缀匹配原则才能用到索引？

比如表中存在索引(b, c, d)，WHERE条件b=XX and c=XX and d=XX，WHERE的顺序不会影响索引最左匹配原则，即对b、c、d的判断，无论谁先谁后都没有影响，也可以这样c=XX and d=XX and b=XX

怎样不会走索引：c=XX and d=XX，因为 b 不知道，要找的记录索引就是 *cd，就不会用得到页地址，相当于走全面扫描

走索引：b=XX and c=XX and d=XX、b=XX and d=XX、b=XX and d=XX

LIKE模糊查询导致索引失效

字段操作导致索引失效

SELECT查询的 WHERE 条件是对 INT 型字段进行运算where a+1=2

走索引没有回表快

ORDER BY导致索引失效

要比较走索引和全表扫描哪个更快：

走索引：不需要排序 + 回表
全表扫描：额外排序(（内存） + 不要回表（导致存储引擎放弃索引进而全表扫描）

避免：

索引中列顺序（升序，降序）和Order by子句完全一致
Order by中的字段全部在关联表中的第一张表中

范围查找导致索引失效原理

回表：先通过索引扫描出数据所在的行，再通过行主键ID取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树

范围查找导致索引失效：SELECT * 返回数据过大，走索引回表还不如全表扫描快

MySQL数据类型转换

索引字段是VARCHAR，查询的 WHERE 条件给的确实 INT 型，会不会影响索引的使用？

SELECT 'a'=0      -- 1
SELECT 'a'=1      -- 0
SELECT '123'=123  -- 1

执行 SELECT * FROM table_name WHERE a='a'，但是会查询出表中存在字段 a 是 0 的记录

再详细分析MySQL的数据类型转换：

隐式转换：两值进行运算或比较，首先对比数据类型是否一致。如果数据类型不一致就会隐式类型转换

如：把字符串转成数字SELECT '1'+1; --2

显式转换：利用函数进行数据类型的转换

CAST(expr AS type)，Cast(col_name as date_type)
将任意类型的表达式expr转换成指定类型type的值

type：BINARY[N]—二进制字符串、CHAR[N]—字符串、DATE—日期、DATETIME—日期时间、TIME—时间、DECIMAL[M,N)]—浮点数、SIGNED[INTEGER]—有符号整数、UNSIGNED[INTEGER]——无符号整数
- 字符串转成数字
  
  转成 DECIMAL 时，从头扫描字符串直到第一个不为数字的字符为至。对截断的那一位进行四舍五入
  - 默认不限定M,N，转换为整数，按照小数点后第一位进行四舍五入：SELECT cast('28.82abc' AS DECIMAL); --29
  - 限定M不限定N，转换为整数：SELECT cast('28.82abc' AS DECIMAL(4)); --29，如果M的长度小于实际的数字位数时，会转换成设定位数的最大值。SELECT cast('28.82abc' AS DECIMAL(1)); --9转换中M为1，即显示不了29这个两位数，就会显示1位数中最大的9
  - M、N都限定，转换为整数：必须先满足小数点后的N位小数：先满足了1位小数，再取1位整数SELECT cast('28.82abc' AS DECIMAL(2,1)); --9.9；先满足2位小数，不取整数部分SELECT cast('28.82abc' AS DECIMAL(2,2)); --0.99
  转无符号整数：SELECT cast('28.82abc' AS UNSIGNED); --28
  
  转有分号整数：SELECT cast('-28.82abc' AS SIGNED); --(-28)
- 字符串转成日期
  
  字符串转DATETIME：SELECT cast('2007-12-25' AS DATETIME); -- 2007-12-25 00:00:00，日期格式必须是 YYYY-MM-DD
  
  字符串转DATE：SELECT cast('2007-12-25' AS DATE); -- 2007-12-25
  
  字符串转TIME：SELECT cast('20:20:20' AS TIME); -- 20:20:20
- 数字转字符串：SELECT cast(123 AS CHAR); --123
- 日期转字符串：SELECT cast(NOW() AS CHAR); --2021-02-16 04:18:21
- 字符串到二进制字符串：比较表达式中，binary影响后面所有字符串，并不会忽略字符串的尾部空格
  
  查看当前字符集和校对规则设置：SHOW VARIABLES LIKE 'collation_%';
  
  字符串大小写不影响，二进制字符串大小写有影响：SELECT 'a'='A', BINARY 'a'='A' -- 1 0
  
  字符串后的空格不影响，二进制字符串后的空格有影响：SELECT 'a'='a ', BINARY 'a'='a '-- 1 0
CONVERT(expr, type), CONVERT(expr USING sharset_name)

CONVERT 函数的作用和 CAST 函数几乎相同，但是ta可以把字符串从一种字符集转换成另一种字符集

SELECT convert('abc' USING utf8);、SELECT convert('你好' USING gbk);

如果目标字符集不能表示该字符，则返回乱码：SELECT convert('你好' USING latin1); --??
日期字符串转换
- 日期转字符串
  
  DATE_FORMAT(date, format)：将日期 date 按给定模式 format 转字符串。SELECT date_format('2009-10-04 22:23:00', '%W %M %Y'); --Sunday October 2009、SELECT date_format('2009-10-04 22:23:00', '%H:%i:%s'); --22:23:00。当然还有诸如 TIME_FORMAT(date, format)，最常用的还是 DATE_FORMAT(date, format)
- 字符串转日期
  
  STR_TO_DATE(str, format)：将字符串 str 按给定模式 format 转日期。SELECT STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s') --2019-01-20 16:01:45

日期格式：

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh🇲🇲ss [AP]M)
%T 时间,24 小时(hh🇲🇲ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一

锁机制

锁机制：当数据库有并发事务的时候，保证数据访问顺序的机制

锁类型

按照锁粒度划可分：表级锁、行级锁

行级锁：InnoDB。上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高
表级锁：InnoDB、MyISAM。上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；特点：粒度大，加锁简单，容易冲突
页级锁：BDB。MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
记录锁：行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。精准条件命中，并且命中的条件字段是唯一索引。加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题
间隙锁：行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读）的事务级别中
触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样
临建锁：行锁的一种，并且是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住触发条件：范围查询并命中，查询命中了索引
结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入

按照锁级别划分：共享锁（读）、排它锁（写）

共享锁又称读锁（read lock）：读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁SELECT...FROM…LOCK IN SHARE MODE
排他锁又称写锁（writer lock）：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取，不能进行写操作，需等待其释放。排它锁是悲观锁的一种实现SELECT... FROM... FOR UPDATE

锁的使用：

乐观锁：系统假设数据的更新在大多数时候是不会产生冲突的，所以数据库只在更新操作提交的时候对数据检测冲突，如果存在冲突，则数据更新失败。实现方式：一般通过版本号和 CAS 算法实现
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁。实现方式：通过数据库的锁机制实现，对查询语句添加 for update

锁的状态:

意向共享锁：简称IS锁，当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁
意向排他锁：简称IX锁，当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁

MySQL什么时候加行锁，什么时候加表锁

数据库的锁与隔离级别的关系？

未提交读总是读取最新的数据，无需加锁
提交读读取数据时加共享锁，读取数据后释放共享锁
可重复读读取数据时加共享锁，事务结束后释放共享锁
串行化锁定整个范围的键，一直持有锁直到事务结束

死锁

死锁：两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁

如何查看死锁？
使用命令show engine innodb status查看最近的一次死锁
InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能

对待死锁常见的两种策略：

通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时
发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行

如何避免死锁？

如果不同的程序并发存取多个表，尽量以相同的顺序访问表
在程序以批量方式处理数据的时候，如果已经对数据排序，尽量保证每个线程按照固定的顺序来处理记录
在事务中，如果需要更新记录，应直接申请足够级别的排他锁，而不应该先申请共享锁，更新时在申请排他锁，因为在当前用户申请排他锁时，其他事务可能已经获得相同记录的共享锁，从而造成锁冲突或死锁
尽量使用较低的隔离级别
尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突的机会
合理选择事务的大小，小事务发生锁冲突的概率更低
不要申请超过实际需要的锁级别，查询时尽量不要显示加锁
对于一些特定的事务，可以表锁来提高处理速度或减少死锁的概率

MyISAM会出现死锁吗？

MyISAM是一次获得所需的全部锁，要么全部满足，要么等待，所以不会出现死锁
InnoDB存储引擎中，除了单个SQL组成的事务外，锁都是逐步获得的，所以存在死锁问题

存储引擎

MySQL有哪些存储引擎

MyISAM：在MySQL 5.1及之前版本，MyISAM是默认的存储引擎。但是MyISAM不支持事务和行级锁，使用表级锁，而且崩溃后无法安全恢复。同时MyISAM对整张表加锁，很容易因为表锁的问题导致典型的的性能问题。适用于读操作远远大于写操作，不需要使用事务的场景
InnoDB：数据按主键聚集存储，支持行级锁及MVCC。MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。被设计用来处理大量的短期（short-lived）事务，应该优先考虑lnnoDB引擎。适用于大多数OLTP场景
Memory 引擎：Memory表至少比MyISAM表要快一个数量级，所有字段长度固定，数据文件是存储在内存中。Memory表的结构在重启以后还会保留，但数据会丢失。不支持事务，读写速度非常快
Memroy表在很多场景可以发挥好的作用：
用于查找或者映射表，例如将邮编和州名映射的表（缓存字典映射表）
用于缓存周期性聚合数据（periodically aggregated data）的结果（缓存周期性分析数据）
用于保存数据分析中产生的中间数据
Archive引擎：不支持事务，只允许查询和新增，不允许修改的非事务型存储引擎，会缓存所有的写并利用zlib对插入的行（只允许在自增ID上建立索引）进行压缩，所以比MyISAM表的磁盘/O更少。但是每次SELECT查询都需要执行全表扫描。
适用于日志和数据采集类应用、数据归档存储
CSV引擎：数据以CSV格式存储（逗号分割值的文件）作为MySQL的表来处理，所有列都不能为NULL，但这种表不支持索引。适用于做为数据交换的中间表使用

MyISAM和InnoDB的区别

InnoDB支持事务，MyISAM不支持事务
InnoDB支持外键，MyISAM不支持
InnoDB是聚集索引，MyISAM是非聚集索引
InnoDB不保存表的具体行数，执行 select count（*）from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
InnoDB最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁
InnoDB读效率高，MyISAM写效率高

在MyISAM，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限

InnoDB如何实现事务

InnoDB 通过 Buffer Pool，LogBuffer，redo log，undo log 来实现事务

以一个 UPDATE 语句为例：

InnoDB 收到一个 UPDATE 语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中
执行 UPDATE 语句，修改Buffer Pool中的数据（内存中）
针对 UPDATE 语句生成一个redo log对象，并存入 LogBuffer 中
针对 UPDATE 语句生成 undolog 日志，用于事务回滚
如果事务提交，把redo log对象持久化，还有其他机制将Buffer Pool中所修改的数据页持久化磁盘
如果事务回滚，则利用 undo log 日志进行回滚

项目

设计

数据库范式

第一范式：数据库表中的所有字段都只具有单一属性，单一属性的列是由基本的数据类型所构成的
第二范式：要求一个表中只具有一个业务主键，符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系
第三范式：每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖

不要过分的反范式化为表建立太多的列；不要过分的范式化造成太多的表关联

将字段很多且部分字段不频繁使用，可以将这些字段分离出一张新表

分析经常联合查询的表的字段，使用这些字段建立中间表，将原来联合查询的表数据插入到中间表，使用中间表查询提高查询效率

设计数据库表考虑因素

命名：数据库、表、字段
合适的存储引擎：InnoDB、MyISAM
为字段选择合适的数据类型
- 当一个列可以选择多种数据类型时，数字 > 日期 > 字符
  - 字符类型
  - varchar：不定长，很多字符串列很少被更新
  - char：定长，适合存储经常更新的字符串列
  - 布尔类型：在MySQL里面对应的是 tinyint(1)

尽量避免使用NULL：NULL不利于索引，也不利于查询，在创建字段时候使用not null default "

数据库设计，字段如何选择

字段类型优先级：整型 > date、time > char、enum > varchar > blob、text
可以选整型就不选字符串：整型是定长的，没有国家/地区之分，没有字符集差异
尽量避免使用NULL：NULL不利于索引，也不利于查询，在创建字段时候使用not null default "

VARCHAR和CHAR的区别和使用场景

char：定长字符串，当输入小于指定的数目，char会在后面补空值。当你输入超过指定允许最大长度后，MySQL会报错
varchar：长度可变，并且是非Unicode的字符数据。n值是介于1-8000之间的数值。存储大小为实际大小

WHERE子句可对字段进行NULL值判断吗？

可以，比如select id from t where num is null。但是最好不要给数据库留NULL，尽可能的使用NOT NULL填充数据库。不要以为NULL不需要空间，如：char(100)型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用100个字符的空间，如果是varchar这样的变长字段，null不占用空间

字段为什么要设置成NOT NULL?

NULL和空值是不一样的，空值是不占用空间的，而NULL是占用空间的，所以字段设为NOT NULL后仍然可以插入空值
1.NULL值会影响一些函数的统计，如count，遇到NULL值，这条记录不会统计在内
2.B树不存储NULL，所以索引用不到NULL，会造成第一点中说的统计不到的问题
3.NOT IN子查询在有NULL值的情况下返回的结果都是空值
4.MySQL在进行比较的时候，NULL会参与字段的比较，NULL是一种比较特殊的数据类型，数据库在处理时需要进行特殊处理

使用外键有什么危害？

性能影响：数据库要保证有一个完整性，当进行更新操作时，需要进行相关的检查操作
热更新：数据库有外键时，可能导致热更新后不能正常使用，无法运行，只有重启服务才正常，从而达不到热更新目的
耦合度：外键的使用物理关联，导致耦合度增加
难以分库分表：在大型互联网项目中，如果要做分库，当存在很多外键关联，则难以实现分库分表

问题

MySQL数据丢失，怎么恢复，具体怎么操作

MySQL数据恢复的时候用了几个线程和几个日志

库存锁定的时候有没有遇到过死锁的状态，怎么解决

海量数据下，如何快速查找一条记录

使用布隆过滤器，快速过滤不存在的记录。使用Redis的bitmap结构来实现布隆过滤器
在Redis中建立数据缓存（缓存击穿、缓存过期）
查询优化

在什么情况下InnoDB无法在线修改表结构

InnoDB不支持在线修改表结构的场景
加全文索引：CREATE FULLTEXT INDEX name ON table（column）
加空间索引：ALTER TABLE geom ADD SPATIAL INDEX（g）
删除主键：ALTER TABLE tbl_name DROP PRIMARY KEY
增加自增列：alter table t add column id int auto_increment not null primary key
修改列类型：ALTER TABLE tbl_name CHANGE c1 c1 NEW_TYPE
改表字符集：ALTER TABLE tbl_name CHARACTER SET=charset_name
如何更安全的在线修改表结构
pt-online-schema-change [OPTIONS] DSN

如何修改大表的表结构

对表中的列的字段类型进行修改
改变字段的宽度时还是会锁表
无法解决主从数据库延迟的问题

pt-online-schema-change 
--alter="MODIFY c VARCHAR(50) NOT NULL DEFAULT ''"
--user=root --password=hefery D=数据库名,t=表名
--charset=utf8 
--execute

MySQL优化及异常处理

数据库服务器负载过大的问题

现象：QPS（低谷）、CPU占有率（峰值）
原因
服务器磁盘IO超负荷
存在大量阻塞线程
存在大量并发慢查询
存在其它占用CPU的服务
服务器硬件资源原因
解决

慢查询造成的磁盘IO爆表

原因
MySQL输出大量日志
MySQL正在进行大批量写
慢查询产生了大量的磁盘临时表
解决
优化慢查询，减少使用磁盘临时表
增加tmp_table_size和max heap_table_size参数的大小

主从数据库数据不一致

现象
主从数据库延迟为0
IO_THREAD和SQL_THREAD 状态为YES
相同查询在主从服务器中查询结果不同
原因
对从服务器进行了写操作
使用sql_slave_skip_counter或注入空事务的方式修复错误
使用了statement格式的复制
解决
设置read_only=ON
设置super_read_only=ON
使用row格式的复制

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几?

　　一般情况下，我们创建的表的类型是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启（文中提到的）MySQL的话，这条记录的ID是6。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。

但是，如果我们使用表的类型是MylSAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

注：如果在这7条记录里面删除的是中间的几个记录（比如删除的是3,4两条记录），重启MySQL数据库后，insert一条记录后，ID都是8。因为内存或者数据库文件存储都是自增主键最大ID

调优

MySQL性能判断

SQL查询速度

QPS：单位时间内所处理的SQL查询量
TPS：单位时间内所处理的事务量
并发量：同时处理的查询请求的量
大量的并发和超高的CPU使用率风险：
大量的并发：数据库连接数被占满（max connections默认100）
超高的CPU使用率：因CPU资源耗尽而出现宕机

MySQL优化方向

原则

减少系统的瓶颈
减少资源的占用
增加系统的反应速度

优化方面

找出系统的瓶颈，提高 MySQL 数据库整体的性能
需要合理的结构设计和参数调整，以提高用户操作响应的速度
尽可能节省系统资源，以便系统可以提供更大负荷的服务

服务器硬件及参数

硬件

内存：高并发：数据库连接数被占满
CPU：高CPU使用率：CPU资源耗尽而宕机（不支持多CPU对同一SQL并发处理）
磁盘IO：PCIe > SSD > Raid10 > 磁盘 > SAN
磁盘IO性能突然下降（使用更快的磁盘设备）
其它大量消耗磁盘性能的计划任务（调整计划任务，做好磁盘维护）
网卡IO：减少从服务器数量、分级缓存、避免使用“select *”、分离业务网络和服务器网络
减少从服务器的数量
进行分级缓存
避免使用" select * "进行查询
分离业务网络和服务器网络

硬件优化

配置较大的内存：内存的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I/O
配置高速磁盘系统，读盘的等待时间，提高响应速度
合理分布磁盘I/O：把磁盘I/O分散在多个设备上，减少资源竞争，提高并行操作能力
配置多处理器：MySQL是多线程数据库，多处理器可同时执行多个线程

参数优化

内存配置相关参数

确定可以使用的内存的上限
确定MySQL的每个连接使用的内存
确定需要为操作系统保留多少内存
如何为缓存池分配内存：
```
Innodb_buffer_pool_size：总内存-（每个线程所需要的内存*连接数）- 系统保留内存
key_buffer_size：
```

IO相关配置参数

Innodb_log_file_size：
Innodb_log_files_in_group：
事务日志总大小 = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size
Innodb_flush_log_at_trx_commit：
        0：每秒进行一次log写入 cache，并flush log到磁盘
        1：默认，在每次事务提交执行log写入cache，并flush log到磁盘
        2：建议，每次事务提交执行log数据写入到cache，每秒执行一次flush log到磁盘
Innodb_flush_method = O_DIRECT
Innodb_file_per table = 1
Innodb_doublewrite = 1
delay_key_write 
	OFF：每次写操作后刷新键缓冲中的脏块到磁盘
        ON：只对在键表时指定了 delay_ key_write选项的表使用延迟刷新
        ALL：对所有 MYISAM表都使用延迟键写入

安全相关配置参数

expire_ logs_days：指定自动清理 binlog 的天数
max_allowed_packet：控制 MySQL可以接收的包的大小
skip_name_resolve：禁用DNS查找
sysdate_is_now：确保 sysdate() 返回确定性日期
read_only：禁止非 super权限的用户写权限
skip_slave_start：禁用 Slave 自动恢复
sql_mode：设置 MySQL 所使用的SQL模式
	strict_trans_tables
        no_engine_subtitution
        no_zero_date
        no_zero_in_date
        only_full_group_by

其它常用配置参数

sync_binlog：控制MySQL如何向磁盘刷新 binlog
tmp_table_size 和 max_ heap_table_size：控制内存临时表大小
max_connections：控制允许的最大连接数
key_buffer_size：索引缓冲区大小，取决于内存大小，太大导致OS频繁换页，降低系统性能
table_cache：同时打开表的数量
query_cache_size：查询缓冲区大小
sort_buffer_size：排序缓冲区大小
read_buffer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区大小
read_rnd_buffer_size：为每个线程保留的缓冲区大小，主要用于存储按特定顺序读取的记录
Innodb_buffer_pool_size：InnoDB类型的表和索引最大缓存
Innodb_flush_log_at_trx_commit：何时将缓冲区的数据写入数据文件，并将日志文件写入磁盘
max_connections：控制允许的最大连接数
back_log：MySQL暂停回答新请求之前短时间内，多少个请求可以被存入堆栈
interactive_timeout：MySQL在关闭一个交互的连接之前所要等待的秒数
thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁
wait_timeout：MySQL关闭一个非交互的连接之前所要等待的秒数
tmp_table_size 和 max_ heap_table_size：控制内存临时表大小

操作系统设置

配置内核参数/etc/sysctl.conf
修改系统限制/etc/security/limits.conf

MySQL性能参数

# 连接MySQL服务器次数
SHOW STATUS LIKE '%CONNECTION%'
# 服务器上线时间
SHOW STATUS LIKE '%Uptime%'
# 慢查询次数
SHOW STATUS LIKE 'Slow_queries'
# 查询查询操作次数
SHOW STATUS LIKE 'Com_select'
# 查询修改操作次数
SHOW STATUS LIKE 'Com_update'
# 查询删除操作次数
SHOW STATUS LIKE 'Com_delete'

服务器配置参数
max_connections：设置MySQL允许访问的最大连接数量
interactive_timeout：设置交互连接的 timeout 时间
wait_timeout：设置非交互连接的 timeout 时间
max_allowed_packet：控制MySQL可以接收的数据包的大小
sort_buffer_size：设置每个会话使用的排序缓存区的大小
join_buffer_size：设置每个会话所使用的连接缓冲的大
read_buffer_size ：指定了当对一个MYISAM进行表扫描时所分配的读缓冲池大小
read_rnd_buffer_size：设置控制索引缓冲区的大小
binlog_cache_size 设置每个会话用于缓存未提交的事务缓存大小
存储引擎参数
innodb_flush_log_attrx_commit：
0：每秒进行一次重做日志的磁盘刷新操作。
1：每次事务提交都会刷新事务日志到磁盘中。
2：每次事务提交写入系统缓存每秒向磁盘刷新一次
innodb_buffer _pool_size：设置Innodb缓冲池的大小，应为系统可用内存的75%
innodb_buffer_pool_instances：Innodb缓冲池的实例个数，每个实例的大小为总缓冲池大小/实例个数
innodb_file_per_table：设置每个表独立使用一个表空间文件

SQL优化

MySQL的执行计划怎么看？

为什么要查看SQL的执行计划

怎么检查SQL有没有用到想要用的索引

通过执行计划对SQL使用索引的情况分析
通过慢查询日志获取有性能问题的SQL

explain select * from t_test：

id：执行SELECT语句的顺序。id值相同时，执行顺序由上至下；id值越大优先级越高，越先被执行
在有子查询是用得到，因为 MySQL 优化器可以对 SQL 的执行顺序进行编排。单查询往往是1
select_type：
SIMPLE：不包含子查询或是UNION操作的查询
PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY
SUBQUERY：SELECT列表中的子查询
DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖外部结果的子查询
UNION：Union操作的第二个或是之后的查询的值为union
DEPENDENT UNION：当UNION做为子查询时，第二或是第二个后的查询的select_type值
UNION RESULT：UNION产生的结果集
DERIVED：出现在FROM子句中的子查询
table：输出数据行所在的表的名称
<unionM,N> 由ID为M，N查询union产生的结果集
<derivedN>/<subqueryN> 由ID为N的查询产生的结果
partitions：匹配的分区
对于分区表，显示查询的分区ID
对于非分区表，显示为NULL
type：表的连接类型，至少达到range，all最差
ALL：Full Table Scan全表扫描，遍历全表以找到匹配的行，这是效率最差的联接方式
index：Full Index Scan全索引扫描，index与ALL区别为index类型只遍历索引树
range：索引范围扫描，常见于between、>、< 的查询条件，只检索给定范围的行，使用一个索引选择行
index_merge：使用了索引合并优化方法
ref_of_null：类似于ref类型的查询，但是附加了对NULL值列的查询
ref：非唯一索引查找，上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref：唯一索或主键引查找，对于每个索引键，表中只有一条记录与之匹配
const：表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
system：这是const联接类型的一个特例，当查询的表只有一行时使用
NULL：在优化过程中分解语句，执行时甚至不用访问表或索引
extra：MySQL解决查询的详细信息
Distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
Not exists：使用not exists来优化查询
Using filesort：使用额外操作进行排序，通常会出现在order by或group by查询中
Using index：使用了覆盖索引进行查询
Using temporary ：MySQL需要使用临时表来处理查询，常见于排序、子查询和分组查询
Using where：需要在MySQL服务器层使用WHERE条件来过滤数据
select tables optimized away：直接通过索引来获得数据，不用访问表
possible_keys：MySQL能使用那些索引来优化查询
查询列所涉及到的列上的索引都会被列出，但不一定会被使用
key：查询优化器优化查询实际所使用的索引
如果没有可用的索引，则显示为NULL，如查询使用了覆盖索引，则该索引仅出现在Key列中
key_len：索引中使用的字节数，可通过该列计算查询中使用的索引的长度
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：MySQL通过索引统计信息，估算所需读取的行数，rows值大小是个统计抽样结果，并不十分准确
filtered：返回结果的行数占需读取行数的百分比
Filtered列的值越大越好
Filtered列的值依赖说统计信息

无法展示存储过程，触发器，UDF对查询的影响
无法使用 explain 对存储过程进行分析
早期版本的 MySQL 只支持对 SELECT 语句进行分析

如何处理慢查询

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

开启慢查询日志，定位问题SQL
分析SQL语句，看看是否 load 了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写

分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可进行横向或纵向的分表

详细操作：

启动慢查询日志

set global slow_query_log_file = /sql_log/slow_log.log;
set global log_queries_not_using_indexes=on;   # 未使用索引的SQL记录日志
set global long_query time=0.001;		 # 抓取执行超过多少时间的SQL，单位：秒
set global low_query_log=on;

分析慢查询日志

mysqldumpslow

# 查看帮助信息
$ mysqldumpslow.pl --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

pt-query-digest

# 查看帮助信息
pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit    限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计
--until 截止时间，配合—since可以分析一段时间内的慢查询

# 案例解读
# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 343ms user time, 78ms system time, 0 rss, 0 vsz
# 工具执行时间
# Current date: Thu Mar 29 15:51:38 2018
# 运行分析工具的主机名
# Hostname: NB2015041602
# 被分析的文件名
# Files: /d/xampp/mysql/data/NB2015041602-slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 5 total, 3 unique, 0.00 QPS, 0.05x concurrency ________________
# 日志记录的时间范围
# Time range: 2018-03-28 14:02:06 to 14:22:10
# 属性               总计      最小    最大    平均    95%  标准    中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time            60s     10s     17s     12s     17s      3s     11s
# 锁占用时间
# Lock time            1ms       0   500us   200us   490us   240us       0
# 发送到客户端的行数
# Rows sent             50      10      10      10      10       0      10
# select语句扫描行数
# Rows examine     629.99k  45.43k 146.14k 126.00k 143.37k  39.57k 143.37k
# 查询的字符数
# Query size         2.81k     235   1.36k  575.40   1.33k  445.36  234.30
# String:
# Databases    database_base
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `database_base` LIKE 'table_list1'\G
#    SHOW CREATE TABLE `database_base`.`table_list1`\G
#    SHOW TABLE STATUS FROM `database_base` LIKE 'user_list'\G
#    SHOW CREATE TABLE `database_base`.`user_list`\G
# EXPLAIN /*!50100 PARTITIONS*/
select SQL_CALC_FOUND_ROWS al.*, ul.Alias as userName
        FROM table_list1 al
        LEFT JOIN user_list ul ON ul.ID = al.UserId
         WHERE TRUE  AND (al.SupportCountrys LIKE '%%')

         limit 80, 10\G


直接分析慢查询文件
pt-query-digest  slow.log > slow_report.log

分析最近12小时内的查询
pt-query-digest  --since=12h  slow.log > slow_report2.log

分析指定时间范围内的查询
pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log

分析含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

查询所有全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

把查询保存到query_review表
pt-query-digest --user=root –password=abc123 --review  h=localhost,D=test,t=query_review--create-review-table  slow.log

把查询保存到query_history表
pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0001
pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0002

通过tcpdump抓取的tcp协议数据，然后分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

分析biglog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

分析general log
pt-query-digest  --type=genlog  localhost.log > slow_report11.log

慢查询如何优化

检查是否走了索引，如果没有则优化SQL利用索引
检查所利用的索引，是否是最优索引
检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
检查表中数据是否过多，是否应该进行分库分表
检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

大表JOIN小表处理数据倾斜

select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10 如何优化?
select * from (select * from admin where admin_id>10) as T1 left join log on T1.admin_id=log.admin_id
使用JOIN时候，应该用小的结果驱动大的结果。LEFT JOIN左边表结果尽量小如果有条件应该放到左边先处理
尽量避免在列上做运算，这样导致索引失效

MySQL分页查询优化

如果有100万条数据不使用分页查询，直接插查询可能会有什么后果

优化查询 select * from user where age>20 limit 20000 10

如何提高INSERT性能

合并多条 INSERT 为一条：主要原因是多条 INSERT 合并后日志量（MySQL的binlog和innodb的事务日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并 SQL 语句，同时也能减少 SQL 语句解析的次数，减少网络传输的 IO
修改参数bulk_insert_buffer_size，调大批量插入的缓存
设置innodb_flush_log_attrx_commit=0：log buffer 中的数据将以每秒一次的频率写入到 log file 中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的 commit 并不会触发任何 log buffer 到 log file的刷新或者文件系统到磁盘的刷新操作
手动使用事务：因为 MySQL 事务默认是 autocommit 的，这样每插入一条数据，都会进行一次commit，为了减少创建事务的消耗，可用手工使用事务，即START TRANSACTION; INSERT...; INSERT...; COMMIT，执行多个 INSERT 语句，一起提交事务

优化插入记录的速度

MyISAM

禁用索引：插入前先禁用索引，插完再恢复索引
禁用唯一性检查：先禁用SET UNIQUE_CHECKS=0，再开启SET UNIQUE_CHECKS=1
使用批量插入：使用INSERT插入多条数据
InnoDB

禁用唯一性检查：先禁用SET UNIQUE_CHECKS=0，再开启SET UNIQUE_CHECKS=1
禁用外键检查：先禁用SET foreign_key_checks=0，再开启SET foreign_key_checks=1
禁用自动提交：先禁用SET autocommit=0，再开启SET autocommit=1

多表查询时，JOIN和子查询哪个效率高

Schema优化

范式化

数据库结构优化目的

减少数据冗余
尽量避免数据维护中出现更新，插入和删除异常

优化表结构

分析表：ANALYZE TABLE tbl_name

Table：库名.tbl_name
Op：analyze，进行分析操作
Msg_type：状态status、信息info、注意note、警告warning、错误error
Msg_text：OK，显示信息
检查表：CHECK TABLE tbl_name [QUICK|FAST|MEDIUM|EXTENDED|CHANGED]

Table：库名.tbl_name
Op：check，进行检查操作
Msg_type：状态status、信息info、注意note、警告warning、错误error
Msg_text：OK，显示信息
优化表：OPTIMIZE TABLE tbl_name

通过 OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁
修复表：REPAIR TABLE tbl_name

MySQL分库分表

大表：记录行数巨大，单表超过千万行或表数据文件巨大，表数据文件超过10G

影响数据库性能

慢查询：很难在一定的时间内过滤出所需要的数据
DDL：
建立索引需要很长的时间，建立索引会锁表(MySQL<5.5)、不会锁表但会引起主从延迟(MySQL>=5.5)
修改表结构需要长时间锁表，会造成长时间的主从延迟，影响正常的数据操作

处理大表

分库分表
难点：分表主键的选择；分表后跨分区数据的查询和统计
大表的历史数据归档
优点：减少对前后端业务的影响
难点：归档时间点的选择；如何进行归档操作

大事务：运行时间比较长，操作的数据比较多的事务

风险

锁定太多的数据，造成大量的阻塞和锁超时
回滚时所需时间比较长
执行时间长，容易造成主从延迟

处理大事务

避免一次处理太多的数据
移出不必要在事务中的 SELECT 操作

谈谈如何对MySQL进行分库分表？

分库：将原本存储于单个数据库上的数据拆分到多个数据库

分表：把原来存储在单张数据表的数据握分到多张数据表中，实现数据切分，从而提升数据库操作性能

分库分表的实现可以分为两种方式：垂直切分和水平切分

水平：将数据分散到多张表，涉及分区键，
分库：每个库结构一样，数据不一样，没有交集。库多了可以缓解 IO 和 CPU 压力
分表：每个表结构一样，数据不一样，没有交集。表数量减少可以提高 SQL 执行效率、减轻 CPU 压力
垂直：将字段拆分为多张表，需要一定的重构
分库：每个库结构、数据都不一样，所有库的并集为全量数据
分表：每个表结构、数据不一样，至少有一列交集，用于关联数据，所有表的并集为全量数据

垂直分片就是从业务角度将不同的表拆分到不同的库中，能够解决数据库数据文件过大的问题，但是不能从根本上解决查询问题

水平分片就是从数据角度将一个表中的数据拆分到不同的库或表中，这样可以从根本上解决数据量过大造成的查询效率低的问题。但是拓展比较麻烦

分库分表工具：MyCAT、ShardingSphere

问题：

事务一致性问题
跨节点关联查询
跨节点分页、排序函数
主键全局唯一

多大数据量需要进行分库分表？

阿里提供的开发手册当中建议：三年业务量，表的数据量超过500W或者数据文件超过2G，就要考虑分库分表

分片策略由哪些？

取余取模：优点均匀存放数据，缺点扩容非常麻烦
按照范围分片：比较好扩容，数据分布不够均匀
按照时间分片：比较容易将热点数据区分出来
按照枚举值分片：例如按地区分片
按照目标字段前缀指定进行分区：自定义业务规则分片

分库分表后，SQL语句的执行流程是怎样的？

解析SQL
查询优化
改写SQL
执行SQL
结果归并

分库分表解决什么问题？

解决负载均衡问题

高可用架构

读写分离

负载均衡

集群

MySQL日志

二进制日志binary-log

二进制日志：记录了所有对MySQL数据库成功执行的修改事件，包括增删改查事件和对表结构的修改事件

作用：基于时间点的备份和恢复；主从复制

二进制日志的格式

基于段的格式 binlog_format=STATEMENT
优点：日志记录量相对较小，节约磁盘及网络I/O
缺点：可能造成MySQL复制的主备服务器数据不一致
基于行的日志格式 binlog_format=ROW
优点：Row格式可以避免MySQL复制中出现的主从不一致问题,使MySQL主从复制更加安全；对每一行数据的修改比基于段的复制高效
缺点：记录日志量较大.
binlog_row_image=[FULL | MINIMAL | NOBLOB]
混合日志格式binlog_format=MIXED
特点：根据SQL语句由系统决在基于段和基于行的日志格式中选择；数据量的大小由所执行的SQL语句决定

开启：my.ini中MySQLld组设置是关于二进制日志的：log-bin

查看：

# SHOW BINARY LOGS
show variables like 'bonlog_format'
MYSQLBINLOG D:/mysql/log/binlog.001

设置：

set session binlog_format=STATEMENT | ROW
flush logs

log-bin [=base_name]
binlog_ format=[ROW | ISTATEMENT | IMIXED]  # 二进制日志格式
binlog_row_image=[FULLI | MINIMALI | NOBLOB]  # Row格式设置
binlog_rows query_log_events=[ON I OFF]
log_ slave_updates=[ON I OFF]
sync_binlog=[1 | 0]

删除：

RESET MASTER  # 删除所有二进制文件
PURGE MASTER LOGS TO "binlog.001"  # 删除指定二进制文件

暂停和恢复：

SET sql_log_bin = 0  # 暂停
SET sql_log_bin = 1  # 恢复

使用二进制日志恢复数据库：

MYSQLBINLOG --stop-date="2021-08-30 15:27:48" D:/mysql/log/binlog.001 | mysql --uuser --ppass

错误日志error-log

错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题

作用：分析排除MySQL运行错误；记录未经授权的访问

启动：my.ini中MySQLld组设置是关于错误日志的：log_error=$mysql/sql_log/mysql-error.log

查看：

SHOW VARIABLES LIKE "log_error"		# 查看存储路径及文件名

删除：

服务端：MYSQLADMIN -u root -P flush-logs
客户端：flush logs

通用查询general-log

通用查询日志：记录建立的客户端连接和执行的语句
启动：my.ini中MySQLld组设置是关于查询日志的：log
查看：D:\MySQL-5.7.10-win32\data目录下的myPC.log
删除：物理删除 .err 日志文件，执行 MYSQLADMIN -u root -P flush-logs

慢查询slow-query-log

慢查询：记承所有执行时间超过 long_query_time的所有查询或不使用索引的查询

启动

# 开启慢查询
set global slow_query_log=on;

配置

slow_query_log=[ON | OFF]:是否开启慢查询日志
slow_query_log_file =$mysql/sql_log/slowlog.log：慢查询日志存储路径
long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志
log_queries_not_using_indexes=[ON | OFF]：未使用索引的查询也被记录到慢查询日志中
log_slow_admin_statements=[ON | OFF]
log_slow_slave_statements=[ON | OFF]

查看

SHOW VARIABLES LIKE '%slow_query_log%'	# 查看存储路径及文件名

重做日志redo-log

作用

确保事务的持久性
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）

innodb_log_group_home_dir  指定日志文件组所在的路径
innodb_log_files_in_group  指定重做日志文件组中文件的数量
innodb_log_file_size       重做日志文件的大小
innodb_mirrored_log_groups 指定了日志镜像文件组的数量

回滚日志undo-log

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性。当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间

innodb_undo_directory = /data/undospace/   # undo独立表空间的存放目录
innodb_undo_logs = 128 			   # 回滚段为128KB
innodb_undo_tablespaces = 4 		   #  指定有4个undo log文件

中继日志relay-log

用于主从复制，临时存储从主库同步的二进制日志，用来给 slave 库恢复

relay_log=filename
relay_log_purge=[ON I OFF]

MySQL集群如何搭建，读写分离如何实现

MySQL 通过将主节点的 binlog 同步给从节点完成主从之间的数据同步

MySQL的主从集群只会将 binlog 从主节点同步到从节点，而不会反过来从从节点同步到主节点

读写分离：因为要保证主从之间的数据一致，写数据的操作只能在主节点完成，而读数据的操作，可以在主节点或者从节点上完成

MySQL主从同步过程及原理

主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件
主节点log dump线程，当 binlog 有变动时，log dump线程读取其内容并发送给从节点
从节点 IO 线程接收 binlog 内容，并将其写入到relay log文件中
从节点的 SQL 线程读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步

MySQL主从复制作用

实现在不同服务器上的数据分布
利用二进制日志增量进行
不需要太多的带宽，但是使用基于行的复制在进行大批量的更改时，会对带宽带来一定的压力，特别是跨IDC环境下进行复制，应该分批进行
实现数据读取的负载均衡
需要其它组件配合完成
利用DNS轮询的方式把程序的读连接到不同的备份数据库使用LVS，haproxy这样的代理方式非共享架构，同样的数据分布在多台服务器上
增强了数据安全性
利用备库的备份来减少主库负载
复制并不能代替备份
实现数据库高可用和故障切换
方便进行数据库高可用架构的部署，避免MySQL单点失败
实现数据库在线升级
MySQL复制功能提供分担读负载
为高可用、灾难恢复、备份提供更多的选择

MySQL复制基于二进制日志文件

二进制日志文件：记录了所有对MySQL数据库成功提交的修改事件，包括增删改查事件和对表结构的修改事件

二进制日志的格式

基于段的格式 binlog_format=STATEMENT
基于行的日志格式 binlog_format=ROW
混合日志格式binlog_format=MIXED

二进制日志格式对复制的影响

基于SQL语名的复制（SBR）
二进制日志格式使用的是 STATEMENT 格式
- 优点
  生成的日志量少，节约网络转输I/O
  并不强制要求主从数据库的表定义完全相同
  相比于基于行的复制方式更为灵活
- 缺点
  对于非确定性事件，无法保证主从复制数据的一致性
  对于存储过程，触法器，自定义函数进行的修改也可能造成数据不一致
  相比于基于行的复制方式在从上执行时需要更多的行锁
基于行的复制（RBR）
二进制日志格式使用的是基于行的日志格式
- 优点
  可以应用于任何SQL的复制包括非确定函数，存储过程等
  可以减少数据库锁的使用
- 缺点
  要求主从数据库的表结构相同，否则可能会中断复制
  无法在从上单独执行触法器
混合模式
根据实际内容在以上两者间切换

MySQL复制工作方式

主服务器将变更写入二进制日志（先开启二进制日志）
从服务器读取主服务器的二进制日志变更写入到 relay_log 中
在从服务器上重放 relay_log 中的日志
基于SQL段（statement）的日志是在从服务器上重新执行记录的SQL
基于行的日志（row）则是在从库上直接应用对数据库行的修改

MySQL复制拓扑

MySQL5.7之前，一个从库只能有一个主库

MySQL5.7之后支持一从多主架构

一主多从

优点

配置简单
可以用多个从库分担读负载

用途

为不同业务使用不同的从库
将一台从库放到远程IDC，用作灾备恢复
分担主库的读负载

双主模式

主主复制

产生数据冲突而造成复制链路的中断：耗费大量的时间；造成数据丢失
解决
- 两个主中所操作的表最好能够分开
- 使用参数控制自增ID的生成
  auto_increment_increment=2
  auto_increment_offset=1 | 2

主备模式

只有一台主服务器对外提供服务，一台服务器处于只读状态并且只作为热备使用
在对外提供服务的主库出现故障或是计划性的维护时才会进行切换
使原来的备库成为主库，而原来的主库会成为新的备库并处理只读或是下线状态，待维护完成后重新上线
确保两台服务器上的初始数据相同
确保两台服务器上已经启动 binlog 并且有不同的 server_id
在两台服务器上启用 log_slave_updates 参数
在初始的备库上启用 read_only

级联复制

MySQL复制性能优化

影响主从延迟的因素

主库写入二进制日志的时间 --> 控制主库的事务大小，分割大事务
二进制日志传输时间 --> 使用MIXED日志格，设置set binlog_row_image=minimal；
默认情况下从只有一个SQL线程，主上并发的修改在从上变成了串行 --> 使用多线程复制
```
stop slave
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave
```

MySQL复制问题处理

由于数据损坏或丢失所引起的主从复制错误

主库或从库意外宕机引起的错误
使用跳过二进制日志事件
注入空事务的方式先恢复中断的复制链路
再使用其它方法来对比主从服务器上的数据
主库上的二进制日志损坏
通过change master命令来重新指定
备库上的中继日志损坏
在从库上进行数据修改造成的主从复制错误
从库设置 ready_only 参数
不唯一的server_id或server_uuid
server_uuid是记录在数据目录中的auto.cnf文件中
max_allow_packet设置引起的主从复制错误

MySQL复制无法解决的问题

分担主数据库的写负载
分库分表
自动进行故障转移及主从切换
提供读写分离功能

主从复制用到了几个线程

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程
binlog线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中
I/O线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中
SQL线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放
1.Master在每个事务更新数据完成之前，将操作记录写入到binlog中
2.Slave从库连接Master主库，并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的
binlog发生变化时，binlog dump会通知所有的Slave，并将相应的binlog发送给Slave
3.I/O线程接收到binlog内容后，将其写入到中继日志（Relay log）中
4.SQL线程读取中继日志，并在从服务器中重放

如果要直接增加一台机器要怎么处理？

数据库同步延迟如何降低或者怎么解决，新版本InnoDB如何解决此类问题

主从复制延迟产生的原因
减小主从延迟的处理方法

如何解决数据库读/写负载大的问题？

如何解决读负载大的问题？读写分离
如何解决写负载大的问题？分库分表

MySQL高可用架构

高可用概念

高可用性H.A.（High Availability）指的是通过尽量缩短因日常维护操作（计划）和突发的系统崩溃（非计划）所导致的停机时间，以提高系统和应用的可用性

严重的主从延迟、主从复制中断、锁引起的大量阻塞都可能导致MySQL出现性能问题

如何实现高可用

避免导致系统不可用的因素（服务器磁盘空间耗尽；性能糟糕的SQL；表结构和索引没有优化；主从数据不一致；人为的操作失误），减少系统不可用的时间

建立完善的监控及报警系统
对备份数据进行恢复测试
正确配置数据库环境
对不需要的数据进行归档和清理

增加系统冗余，保证发生系统不可用时可以尽快恢复

避免存在单点故障
主从切换及故障转移

单点故障：在一个系统中提供相同功能的组件只有一个，如果这个组件失效了，就会影响整个系统功能的正常使用。组成应用系统的各个组件都有可能成为单点

如何避免MySQL单点故障？

利用SUN共享存储或DRDB磁盘复制解决MySQL单点故障
利用多写集群或NDB集群来解决MySQL单点故障
利用MySQL主从复制来解决MySQL单点故障

如何解决主服务器的单点问题

主服务器切换后如何通知应用新的主服务器的IP地址
如何检查MYSQL主服务器是否可用
如何处理从服务器和新主服务器之间的那种复制关系

读写分离

进行MySQL主从复制配置的一个主要目的：为了分担主库的读负载

目标：Master—写 Slave—读

解决：读压力大问题，如何在复制集群的不同角色上，去执行不同的SQL语句

实现

程序
- 优点
  由开发人员控制什么样查询在从库中执行，因此比较灵活
  由程序直接连接数据库，所以性能损耗比较少
- 缺点
  增加了开发的工作量，使程序代码更加复杂
  人为控制，容易出现错误
中间件：mysql-proxy、maxScale
- 优点
  由中间件根据查询语法分析，自动完成读写分离
  对程序透明，对于已有程序不用做任何调整
- 缺点
  由于增加了中间层，所以对查询效率有损耗
  对于延迟敏感业务无法自动在主库执行

负载均衡

解决：具有相同角色的数据库，如何共同分担相同的负载

实现

软件：LVS、Haproxy、MaxScale
硬件：F5

MMM架构

MMM，Multi-Master Replication Manager

MMM架构作用

监控和管理MySQL的主主复制拓扑，并在当前的主服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作

MMM架构优缺点

优点

使用Perl脚本语言开发及完全开源
提供了读写VIP（虚拟IP），使服务器角色的变更对前端应用透明
MMM提供了从服务器的延迟监控
MMM提供了主数据库故障转移后从服务器对新主的重新同步功能
很容易对发生故障的主数据库重新上线

缺点

发布时间比较早不支持MySQL新的复制功能
没有读负载均衡的功能
在进行主从切换时，容易造成数据丢失
MMM监控服务存在单点故障

MMM架构功能

MMM监控MySQL主从复制健康情况
在主库出现宕机时进行故障转移并自动配置其它从对新主的复制
如何找到从库对应的新的主库日志点的日志同步点
如果存在多个从库出现数据不一致的情况如何处理
提供了主，写虚拟IP，在主从服务器出现问题时可以自动迁移虚拟IP

MMM架构拓扑

MMM部署资源

资源名称	数量	说明
主DB服务器	2	用于主备模式的主主复制配置
从DB服务器	0-N	可以配置0台或多台从服务器，但不建议太多
监控服务器	1	用于监控MySQL复制集群
lP地址	2 *（N+1）	N为MySQL服务器的数量
监控用户	1	用户于监控数据库状态的MySQL用户（replication client）
代理用户	1	用户MMM代理的MySQL用户（super、replication client、process）
复制用户	1	用户配置MySQL复制的MySQL用户（replication slave）

MMM部署步骤

配置主主复制及主从同步集群
安装主从节点所需要的支持包
安装及配置MMM工具集
运行MMM监控服务

MHA架构

MHA，Master High Availability

MHA架构优缺点

优点

由Perl语言开发的开源工具
可以支持基于GTID的复制模式
MHA在进行故障转移时更不易产生数据丢失
同一个监控节点可以监控多个集群

缺点

需要编写脚本或利用第三方工具来实现 Vip 的配置
MHA启动后只会对主数据库进行监控
需要基于SSH免认证配置，存在一定的安全隐患
没有提供从服务器的读负载均衡功能

MHA架构功能

监控主数据库服务器是否可用
当主DB不可用时，从多个从服务器中选举出新的主数据库服务器
提供了主从切换和故障转移功能

MHA是如何进行主从切换的

MHA主从切换过程

尝试从出现故障的主数据库保存二进制日志
从多个备选从服务器中选举出新的备选主服务器
在备选主服务器和其它从服务器之间同步差异二进制数据
应用从原主DB服器上保存的二进制日志
提升备选主DB服务器为新的主DB服务器
迁移集群中的其它从DB作为新的主DB的从服务器

MHA架构拓扑

MHA部署步骤

配制集群内所有主机的SSH免认证登陆
安装 MHA-node 软件包和 MHA-manager 软件包
建立主从复制集群
配制MHA管理节点
使用 masterha_check_ssh 和 masterha_check_repl 对配置进行检验

数据库监控方向

性能指标
- QPS：数据库每秒钟处理的请求数量
- TPS：数据库每秒钟处理的事务数量
- 并发数：数据库实例当前并行处理的会话数量
- 连接数：连接到数据库会话的数量
- 缓存命中率：Innodb的缓存命中率
功能指标
- 可用性：数据库是否可正常对外提供服务
- 阻塞：当前是否有阻塞的会话
- 死锁：当前事务是否产生了死锁
- 主从延迟：数据库主从延迟时间
- 主从状态：数据库主从复制链路是否正常

数据库服务可用性

如何确认数据库是否可以通过网络连接

MySQL本地的SQL文件可以连接数据库服务器，不意味着可以通过网络TCP/IP协议连接MySQL，防火墙、端口开放都可能影响

mysqladmin -umonitor_user -p -h ping
telnet ip db_port
使用程序通过网络建立数据库连接（最好方式）

如何确认数据库是否可读写

检查数据库的 read_only 参数是否为 off
建立监控表并对表中数据进行更新
执行简单的查询 select @@version

如何监控数据库的连接数

show variables like'max_connections';
show global status like "Threads_connected"
设置 Threads_connected / max_connections > 0.8 发出报警

数据库性能监控

计算QPS和TPS

QPS = (Queries2 - Queries1) / (Uptime_since_flush_status2 - Uptime_since_flush_status1)
TPS=( (Com_insert2+Com_update2+Com_delete2) - (Com_insert1+Com_update1+Com_delete1)) / (Uptime_since_flush_status2-Uptime_since_flush_status1)

数据库的并发请求数量

数据库系统的性能会随着并发处理请求数量的增加而下降

show global status like 'Threads_running'

并发处理的数量通常会远小于同一时间连接到数据库的线程的数量

PS：数据库出现大量阻塞会导致数据库并发数量剧增

InnoDB的阻塞

SELECT b.trx_mysql_thread_id AS '被阻塞线程', 
	b.trx_query AS '被阻塞SQL', 
	c.trx_mysql_thread_id AS '阻塞线程',  
	c.trx_query AS '阻塞SQL'
	(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS '阻塞时间'
FROM 
	information_schema.innodb_lock_waits a 
JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id 
JOIN information_schema.innodb_trx c ON a.blocking_trx_id=c.trx_id 
WHERE (UNIX_TIMESTAMP - UNIX_TIMESTAMP(c.trx_started)) > 60

主从复制监控

如何监控主从复制链路的状态

主要监控从服务器（执行show slave status） Slave_IO_Running:Yes；Slave_SQL_Running:Yes 是否正常

如何监控主从复制延迟

主要监控从服务器（执行show slave status）Seconds_Behind_Master:0 是否正常

使用多线程的程序同时对于主从服务器的状态来进行检查

主服务器上的二进制日志文件名和偏移量：show master status \G
File:mysql-bin.001083；Position:302054375
从服务器上的二进制日志文件名和偏移量：show slave status
Master_Log_File:mysql-bin.001083；Read_Master_Log_Pos:301861230
已经传输完成的主上二进制日志的名字和偏移量
Exec_Master_Log_Pos:301861230；Relay_Log_Space:301861614

如何验证主从复制的数据是否一致

pt-table-checksum u=dba,p=hefery --databases mysql --replicate test.checksums

主库创建账号：GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO' dba'@' ip' IDENTIFIED BY "hefery";

监控是如何实现的？

QPS：数据库每秒钟处理的请求数量
show global status like 'com%'
Show global status like 'Queries'
QPS=（Queries2-Queries1）/时间间隔
TPS：数据库每秒钟处理的事务数量
show global status where Variable_ name in (com_ insert','com_delete','com_update');
Tcscom_insert+com_delete+com_update
TPS*(TC2-Tc1)/(time2-time1)
并发数：数据库实例当前并行处理的会话数量
SHOW GLOBAL STATUS LIKE "Threads_running'
连接数：连接到数据库会话的数量
SHOW GLOBAL STATUS LIKE 'Threads_ connected'
InnoDB缓存命中率
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests*100%
Innodb_buffer_pool_read_requests：从缓冲池中读取的次数
Innodb_buffer_pool_reads：表示从物理磁盘读取的次数
show global status like 'innodb_buffer_pool_read%';

报警阀值：Threads_connected/max_connections>0.8

可用性：数据库是否可正常对外提供服务
周期性连接数据库服务器并执行select@@version
mysqladmin-uxxxx-pxxxxx-hxxxxx ping

阻塞

SELECT 
	waiting_pid AS '被阻塞的线程'，
	waiting_query AS‘被阻塞的SQL'，
	blocking_pid AS '阻塞线程'，
	blocking_query AS '阻塞SQL'，
	wait age AS '阻塞时间',
	sql_kill_blocking_query AS“建议操作’
FROM 
	sys.innodb_lock_waits
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait started))_>30

监控慢查询
通过慢查询日志监控
通过 information_schema.PROCESSLIST 表实时监控
死锁
show engine innodb status
pt-deadlock-logger u=dba,p=xxxxx,h=127.0.0.1 --create-dest-table --dest u=dba,p=xxxxx,h=127.0.0.1,D=crn,t=deadlock
set global innodb_print_all_deadlocks=on;
主从延迟：数据库主从延迟时间
show slave status;（Seconds_Behind_Master）
pt-heartbeat --user=xx --password=xxx -h master --create -table --database xxx --update --daemonize --interval=1
pt-heartbeat --user=xx --password=xxx -h slave --database crn --monitor --daemonize --log/tmp/slave_lag.log
主从状态：数据库主从复制链路是否正常
show slave status;（Slave_IO_Running:YES；Slave_SQL_Running:YES）

Demo

公司场景

176.第二高的薪水

题述

获取 Employee 表中第二高的薪水（Salary）

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

上述 Employee 表，SQL应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

题解

ORDER BY DESC + DISTINCT + LIMIT

薪资降序排序，然后使用 LIMIT 子句获得第二高的薪资

去重（DISTINCT）
考虑第二高的薪资不存在的情况

# 临时表
SELECT
    (SELECT DISTINCT Salary
     FROM Employee
     ORDER BY Salary DESC
     LIMIT 1 OFFSET 1
    ) AS SecondHighestSalary

# IFNULL
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
       LIMIT 1 OFFSET 1), NULL
    ) AS SecondHighestSalary

177.第N高的薪水

题述

获取 Employee 表中第n高的薪水（Salary）

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

上述 Employee 表，n = 2时，返回第二高的薪水 200。如果不存在第n高的薪水，那么查询应返回 null

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

题解

ORDER BY DESC + DISTINCT + LIMIT

薪资降序排序，然后使用 LIMIT 子句获得第二高的薪资

去重（DISTINCT）
考虑第二高的薪资不存在的情况
LIMIT里面不能做运算，所以要处理下N的值

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET n = N-1;
  RETURN (
      SELECT (
          IFNULL(
              (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT n,1), NULL
          )
      )
  );
END

181. 超过经理收入的员工

题述

Employee 表包含所有员工，他们经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工

+----------+
| Employee |
+----------+
| Joe      |
+----------+

题解

自查询

# WHERE
SELECT
    a.name AS Employee 
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id AND a.Salary > b.Salary

# JOIN，更常用也更有效
SELECT a.name AS Employee 
FROM Employee AS a
JOIN Employee AS b
ON a.ManagerId = b.Id AND a.Salary > b.Salary

184. 部门工资最高的员工

题述

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写SQL，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+
PS：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高

题解

使用 JOIN 和 IN 语句

SELECT 
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
JOIN Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )

185.部门工资前三高的所有员工

题述

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
PS：IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二

题解

使用 JOIN 和子查询

# JOIN + 子查询
SELECT
    d.Name AS 'Department', 
    e1.Name AS 'Employee', 
    e1.Salary
FROM
    Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT COUNT(DISTINCT e2.Salary)
         FROM Employee e2
         WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId )

# 子查询
SELECT
	Department.NAME AS Department,
	e1.NAME AS Employee,
	e1.Salary AS Salary 
FROM
	Employee AS e1, 
        Department
WHERE
	e1.DepartmentId = Department.Id 
	AND 3 > (SELECT count( DISTINCT e2.Salary ) 
		 FROM Employee AS e2 
		 WHERE	e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId) 
ORDER BY Department.NAME,Salary DESC;

# dense_rank函数，找到每个部门最高，然后取dense_rank<=3的结果
SELECT 
    B.name AS Department,
    A.Employee,
    A.Salary 
FROM 
    (
        SELECT 
            DepartmentId,
            name AS employee,
            salary,
            dense_rank() over (partition by departmentid order by salary desc) as rk
        FROM employee
    ) AS A
LEFT JOIN department B ON A.departmentid = B.id
WHERE A.rk <= 3

183.从不订购的客户

题述

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

题解

使用子查询和 NOT IN 子句

SELECT c.name AS Customers 
FROM Customers AS c
WHERE c.id NOT IN
    (
        SELECT CustomerId FROM Orders 
    )

182.查找重复的电子邮箱

题述

查找 Person 表中所有重复的电子邮箱

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
PS：所有电子邮箱都是小写字母

题解

# GROUP BY + HAVING
SELECT Email
FROM Person
GROUP BY Email HAVING COUNT(Email) > 1

# 子查询
SELECT DISTINCT a.Email
FROM Person a, Person b
WHERE a.Email=b.Email AND a.Id!=b.Id

196.删除重复的电子邮箱

题述

删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留Id最小的那个

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

题解

DELETE p1 
FROM Person p1, Person p2
WHERE p1.Email=p2.Email AND p1.Id>p2.Id

学校场景

至少连续出现3次的字段

-- user_info(userId, username)  连续出现3次的username
SELECT DISTINCT 
	user_info
FROM 
	table_name a
WHERE
	a.username = (SELECT username FROM a.userId = userId-1) 
	AND 
	a.username = (SELECT username FROM a.userId = userId-2)

连续主键ID中不连续的ID值

-- t_user(user_id...)
SELECT 
	user_id+1
FROM 
	t_user a
WHERE
	NOT EXISTS (SELECT * FROM t_user b WHERE b.user_id=a.user_id+1) 
	AND a.user_id < (SELECT MAX(t.user_id) FROM t_user t)

项目

如何删除重复数据

删除评论表中对同一订单同一商品的重复评论，只保留最早的一条

查看是否存在对同一订单同一商品的重复评论

SELECT order_id,product_id,count(*) 
FROM product_comment
GROUP BY order_id,product_id HAVING count(*)>1

备份商品评论product_comment表

# 创建备份表结构
CREATE TABLE bak_product_comment＿20220107
AS
SELECT * FROM　product_comment
# create table a as select * from b 可以创建一个与b表结构一样的表，但是在实际应用中最好不要这么创建表。原因是这样只创建表的结构，而不会将原表的默认值一起创建。不能将原表中的default value也一同迁移过来

CREATE TABLE bak_product_comment＿20220107
LIKE
product_comment

# 导入备份表数据
IINSERT INTO bak_product_comment＿20220107
SELECT * FROM　product_comment

删除同一订单的重复评论

DELETE a FROM product_comment a
JOIN (
    SELECT order_id, product_id, MIN(comment_id) AS comment_id
    FROM product_comment
    GROUP BY order_id, product_id
    HAVING COUNT(*)>=2
) b ON a.order_id=b.order_id AND a.product_id=b.product_id
AND a.comment_id > b.comment_id

如何进行分区间统计

统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数

SELECT 
    COUNT(CASE WHEN IFNULL(total_money,0) >= 1000 THEN a.customer_id END) AS '大于1000',
    COUNT(CASE WHEN IFNULL(total_money,0) >= 800 AND IFNULL(total_money,0) < 1000 THEN a.customer_id END) AS '800~1000',
    COUNT(CASE WHEN IFNULL(total_money,0) < 500 THEN a.customer_id END) AS '小于500'
FROM
    customer_user AS a
LEFT JOIN (
    SELECT customer_id, SUM(order_monery) AS total_money
    FROM order_master 
    GROUP BY customer_id
) AS b ON a.customer_id=b.customer_id

标题：MySQL进阶—全知全能
作者：Hefery
地址：http://hefery.icu/articles/2022/02/26/1645811324135.html