跳至主要內容

mysql

HeChuangJun约 18938 字大约 63 分钟

1. 什么是MySQL？

  • 开源的关系型数据库管理系统，隶属Oracle

2. MySQL 的内连接、左连接、右连接、交叉连接、笛卡尔积有什么区别？

  • inner join内连接，取得两张表中满足连接条件的记录。只有当两个表中都有匹配的记录时，这些记录才会出现在查询结果中。如果某一方没有匹配的记录，则该记录不会出现在结果集中。相当于两个数据集的交集。
  • 外连接（outer join）：不只取得两张表中满足连接条件的记录，还包括某张表（或两张表）中不满足匹配关系的记录。分为左链接和右连接
  • left join返回左表（FROM子句中指定表）所有记录，以及右表满足连接条件的记录。如果右表中没有匹配的记录，则结果中右表的部分会以NULL填充
  • right join返回右表（FROM子句中指定表）所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则结果中左表的部分会以NULL填充
  • 交叉连接（crossjoin）：显示两张表所有记录一一对应，没有匹配关系进行筛选，它是笛卡尔积在SQL中的实现，如果A表有m行，B表有n行，那么A和B交叉连接的结果就有m*n行。
  • 笛卡尔积：例如集合A={a,b}，集合B={0,1,2}，那么A✖️B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

3. 数据库三大范式？

  • 第一范式(1NF)：字段值具有原子性,不能再分;例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段
  • 第二范式(2NF)：满足第一范式基础上，要求数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。例如订单表里，存储了商品信息（商品价格、商品类型），那就需要把商品 ID 和订单 ID 作为联合主键，才满足第二范式。
  • 第三范式(3NF)：在满足第二范式基础上，一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有冗余字段。订单表中包含订单编号和商品编号，存在冗余数据，比如说商品名称、单位、商品价格等，应该将其拆分为订单表、订单商品关联表、商品表。
  • 三大范式的作用是为了控制数据库的冗余，节省空间，实际上可以通过冗余一些数据，避免跨表跨库，利用空间换时间，提高性能,减少join的查询;例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;

4. varchar与char区别？

  • char表示定长字符串，长度固定

  • 如果插入数据的长度小于char的固定长度时，则用空格填充；CHAR会自动删除插入数据的尾部空格

  • 因为长度固定，所以存取速度要比varchar快，但可能会占据多余的空间，是空间换时间的做法

  • char最多能存放的字符个数为255，和编码无关

  • varchar(50)中50的涵义最多存放50个字符。varchar(50)和(200)存储所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)

  • varchar表示可变长字符串，长度不固定

  • 按照插入的数据长度来存储；VARCHAR不会删除尾部空格

  • 因为长度不固定，varchar比char存取慢，不占据多余的空间，是时间换空间的做法

  • varchar最多能存放的字符个数为65532

  • 对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

5. blob和text有什么区别？

  • 都在保存较大文本时使用
  • blob用于存储二进制数据，比如照片；而text用于存储大字符串。比如一篇文章或者日记
  • blob没有字符集，text有字符集，并且根据字符集的校对规则对值进行排序和比较

6. DATETIME和TIMESTAMP的异同？

  • 相同点
    • 存储时间的表现格式一致YYYY-MM-DD HH:MM:SS
    • 都包含「日期」和「时间」部分。
    • 都可以存储微秒的小数秒（秒后6位小数秒）
  • 区别
    • 日期范围：DATETIME的日期范围是1000-01-0100:00:00.000000到9999-12-31 23:59:59.999999；范围大。TIMESTAMP的时间范围是1970-01-0100:00:01.000000UTC到2038-01-0903:14:07.999999UTC，范围小。
    • 存储空间：DATETIME的存储空间为8字节；TIMESTAMP的存储空间为4字节
    • 时区相关：DATETIME存储时间与时区无关；TIMESTAMP存储时间与时区有关
    • 默认值：DATETIME的默认值为null；TIMESTAMP字段不为空(notnull)，默认值为当前时间(CURRENT_TIMESTAMP)

7. in和exists的区别？

  • in语句是把外表（in所在的子查询的表）和内表（from子句中的表）作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询
  • 如果查询的两个表大小相当，那么用in和exists差别不大。
  • 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
  • 如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

8. MySQL里记录货币用什么字段类型？ip呢？

  • 常用Decimal和Numric类型表示，例如salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。
  • DECIMAL和NUMERIC值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。
  • float和double是以二进制存储的，存在误差，不使用。
  • ip地址的存储 bigint insert into sessions (ipaddress)values (inet_aton('192.168.0.1')); select inet_ntoa(ipaddress) from sessions;
  • 范围查询IP select * from t where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<=inet_aton('192.168.1.20')

8.1. MySQL怎么存储emoji😊?

  • 使用字符串存储emoji。同时使用4字节的utf8mb4编码。
  • alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;

8.2. drop、delete 与 truncate 的区别？

  • 在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。
deletetruncatedrop
类型属于DML属于DDL属于DDL
回滚可回滚不可回滚不可回滚
删除内容表结构还在，删除表的全部或者一部分数据行不释放空间表结构还在，删除表中的所有数据释放空间删除数据后重新写数据会从1开始从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度速度慢，需要逐行删除速度快速度最快

9. UNION 与 UNION ALL 的区别？

  • Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
  • Union All：对两个结果集进行并集操作，包括重复行，不进行排序；速度较快

10. count(1)、count(*)、count(主键|列名)的区别？

  • MyISAM执行count(*)会直接返回存储在磁盘上的表的总行数，效率高；

  • InnoDB执行count(*)需要把数据一行一行地从引擎里面读出来，然后累积计数；因为innodb支持事务，每个事务查询结果有差异，故不存储表的总行数

  • count(*) 统计所有行（允许null）。为了减少扫描数据量，自动扫描索引树小的。不取值，按行累加

  • count(1)统计所有行（允许null），不取值，按行累加。用1代表代码行，速度比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作

  • count(主键id) 统计不为null的行。遍历整张表把每一行的id值都取出来返回给server层

  • count(列名)统计不为null的行。只包括列名那一列

  • 按照效率排序的话，count(字段)<count(主键id)<count(1)≈count()，建议尽量使用count()。

11. 一条SQL查询语句的执行顺序？√

  • FROM：对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积（Cartesianproduct），产生虚拟表VT1
  • ON：对虚拟表VT1应用ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2中
  • JOIN：如果指定了OUTERJOIN（如LEFTOUTERJOIN、RIGHTOUTERJOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止
  • WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才被插入虚拟表VT4中
  • GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5
  • ACG_FUNC聚合函数
  • CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生表VT6
  • HAVING条件过滤：对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才被插入虚拟表VT7中。
  • SELECT投影列：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中
  • DISTINCT：去除重复数据，产生虚拟表VT9
  • ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10。11）
  • LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户
  • 聚合函数在WHERE之后执行 在WHERE判断条件里加入聚合函数是做不到的

12. SQL分类

SQL(Structure Query Language)结构化查询语言，关系型数据库应用语音

  • DDL(Data Definition Languages数据库定义语言) 定义不同的数据段、数据库、表、列、索引等数据库对象(create、alter、drop等)
  • DML(Data Manipulation Language数据库操作语言) 用于添加、删除、更新和查询数据库记录，并检查数据完整性(insert、delete、update)
  • DQL(Data Query Language数据库查询语言)查询数据库中表的记录(select)
  • DCL(Data Control Language数据库控制语言) 用于控制不同数据段直接的许可和访问级别的语句。定义了数据库、表、字段、用户的访问权限和安全级别(grant,revoke等)

12.1. DDL语句

# 创建数据库
mysql> CREATE DATABASE 数据库名 [charset 字符集] [collate 校对规则];

# 查看所有数据库
# information_schema：存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、分区信息等
# cluster：存储了系统的集群信息
# mysql：存储了系统的用户权限信息
mysql> SHOW DATABASES;

# 查看数据库详细说明(编码)
mysql> SHOW CREATE DATABASE 数据库名;

# 选择操作的数据库
mysql> USE 数据库名;

# 查看当前操作的数据库
mysql> SELECT DATABASE();

# 查看数据库的表
mysql> SHOW TABLES;

# 改变数据库的全局特性
mysql> ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];

# 删除数据库
mysql> DROP DATABASE 数据库名;

# 创建表
mysql> CREATE TABLE 表名(列名 数据类型(长度)[约束] (s) )[NGINE=存储引擎][CHARSET 字符集 collate 校对规则]

# 创建MEMORY表
mysql> CREATE TABLE 表名 ENGINE=MEMORY select子句

# 创建MERGE表
mysql> CREATE TABLE 表名(列名 类型(长度)[约束] (s)) engine=merge union=(表名(s)) [INSERT_METHOD=LAST|FIRST|NO]

# 查看表定义/建表sql
mysql> DES 表名;
mysql> show create table 表名;

# 删除表
mysql> DROP TABLE 表名;
mysql> truncate table 表名;删表重建，auto_increment重新记录,删除的数据找不回

# 增加表字段
mysql> ALTER TABLE 表名 ADD 列名 类型（长度）[约束][FIRST | AFTER 列名];

# 修改表字段类型(长度)[约束]
mysql> ALTER TABLE 表名 MODIFY 列名 类型(长度)[约束][FIRST | AFTER 列名]

# 改整个字段
mysql> ALTER TABLE 表名 CHANGE  旧列名 新列名 类型(长度)[约束] [FIRST | AFTER 列名]

# 删除字段
mysql> ALTER TABLE 表名 DROP 列名;

# 改表名
mysql> ALTER TABLE 旧表名 RENAME  新表名

# 改表字符集
mysql> ALTER TABLE 表名 character set 字符集;

# 修改存储引擎
mysql> ALTER TABLE 表名 engine = innodb;

# 增加外键
# RESTRICT和NO ACTION是指限制在子表有关联记录的情况下主表不能更新；CASCADE 表示主表在更新或者删除时，更新或者删除子表对应记录；SET NULL表示主表在更新或者删除的时候，子表的对应字段被 SET NULL
mysql> ALTER TABLE 子表 add [constraint 外键名(以_fk结尾)] foreign key (从表外键列名) references 主表 (主表主键)[ON DELETE/UPDATE RESTRICT/CASCADE/SET NULL/NO ACTION(s)];

# 添加主键约束
mysql> ALTER TABLE table_name ADD PRIMARY KEY (column_name);

# 删外键
mysql> alter table 从表 drop foreign key 外键名称;

# 查字符集对应的校对规则
mysql> show character set;

# 查看MySQL编码
mysql> SHOW VARIABLES LIKE 'char%';

# 修改mysql的隔离级别
mysql> set session transaction isolation level

# 创建索引
mysql> CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 [USING 索引类型] ON 表名 (列名)[(长度)(s)],...

# 删除索引
mysql> DROP INDEX 索引名称 ON 表名

12.2. DML语句

# 添加记录
mysql> INSERT INTO 表名 [(列名1，列名2，列名3...)] VALUES (值1，值2，值3)[,(值3, 值3, 值5)...]

# 修改(多表)记录
mysql> UPDATE 表名(s) SET 列名=值[,列名=值(s)] [where 条件];
mysql> UPDATE t1[,t2…tn] SET t1.field1=expr1[,tn.fieldn=exprn] [where 条件]

# 删除(多表)记录
mysql> DELETE FROM 表名 [where 条件] auto_increment记录不清空，同一个事务可以找回数据
mysql> DELETE t1,t2…tn FROM t1,t2…tn [where 条件]

# 开始事务
mysql> START TRANSACTION;
# 提交事务
mysql> COMMIT;
# 回滚事务
mysql> ROLLBACK;

12.3. DQL语句

# 查询记录
mysql> SELECT [distinct全部列,非第一列] */列名 [as `别名`](s) FROM 表名 [inner/left/right join 表名 on 条件] [where 条件] [group by 条件 [WITH ROLLUP 对分类聚合后的结果进行再汇总 和ORDER BY是互相排斥] [having 条件 对分类后的结果再进行条件的过滤]]  [order by 条件 asc/desc(s)多个字段时，相同的按第二个字段排，依次类推，默认升序] [limit offset_start[,row_count] offset_start=(第几页-1)*row_count row_count每页数量]

# 隐式内连接
mysql> select *from A,B where 条件
# 显示内连接
mysql> select * from A [inner] join B on 条件
# 左外连接: 包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
mysql> select * from A left outer join B on 条件
# 右外连接: 包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
mysql> select * from B right outer join A on 条件 左连接：
# 子查询(in、not in、=、!=、exists、not exists、ALL、ANY、SOME),表连接在很多情况下用于优化子查询
mysql> select * from 表名 where 列名=（select where 列名=值）;


- 表与表的关系
  - 一对一：可以创建成一张表
  - 一对多：部门与员工、客户和订单、分类和商品
  - 从表（多）创建字段并添加外键指向主表（一）的主键
  - 多对多：学生和课程、商品和订单、人和角色
  - 建立中间表，至少两字段。作为从表，指向各方（多）的主键（拆成两个一对多）
- 外键操作
  - 从表外键是对主表主键的引用、从表外键类型必须与主表主键类型一样
  - 从表不能添加(更新)，主表中不存在的数据。主表不能删除（更新），从表中已经使用的数据
  - 外键的目的是保证数据完整性
  - 删除主表中与从表有关联关系的数据
  - ①解除主从表的约束关系
  - ②先删除从表中与主表有关系的数据，再删除主表中的数据

12.4. DCL语句

# 创建用户
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';
# 授权
mysql> GRANT ALL[PRIVILEGES]/SELECT/INSERT ON database_name.table_name TO 'username'@'localhost' IDENTIFIED BY 'ermazi'
# 回收
mysql> REVOKE ALL[PRIVILEGES]/SELECT/INSERT ON database_name.table_name FROM 'username'@'localhost'
# 删除用户
mysql> DROP USER 'username'@'host';

13. 介绍一下MySQL bin目录下的可执行文件

  • mysql：客户端程序，用于连接MySQL服务器
  • mysqldump：数据库备份工具，用于创建一个或多个MySQL数据库级别的SQL转储文件，包括数据库的表结构和数据。用于数据备份、迁移或恢复。
  • mysqladmin：管理工具，用来执行一些管理操作，比如说创建数据库、删除数据库、查看MySQL服务器的状态等。
  • mysqlcheck：命令行工具，用于检查、修复、分析和优化数据库表，对数据库的维护和性能优化非常有用。
  • mysqlimport：用于从文本文件中导入数据到数据库表中，非常适合用于批量导入数据。
  • mysqlshow：用于显示MySQL数据库服务器中的数据库、表、列等信息。
  • mysqlbinlog：用于查看MySQL二进制日志文件的内容，可以用于恢复数据、查看数据变更等。

14. 第 3-10 条记录怎么查

  • limit 语句用于限制查询结果的数量，偏移量表示从哪条记录开始，行数表示返回的记录数量。
  • SELECT * FROM table_name LIMIT 2, 8;

15. mysql函数

字符串函数

  • CANCAT(S1,S2,…Sn) 连接 S1,S2,…Sn 为一个字符串，任何字符串与NULL连接的结果都是NULL

  • LENGTH(): 返回字符串的长度。

  • SUBSTRING(str,x,y) 返回从字符串 str x 位置起 y 个字符长度的字串

  • REPLACE(str,a,b) 用字符串 b 替换字符串 str 中所有出现的字符串 a

  • LOWER(str)、UPPER(str) 将字符串 str 中所有字符变为小、大写

  • TRIM(str) 去掉字符串行尾和行头的空格

  • INSERT(str,x,y,instr) 将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr

  • LEFT(str ,x) 返回字符串 str 最左边的 x 个字符，如果第二个参数是 NULL，那么将不返回任何字符串

  • RIGHT(str,x) 返回字符串 str 最右边的 x 个字符，如果第二个参数是 NULL，那么将不返回任何字符串

  • LPAD(str,n,pad) 用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度

  • RPAD(str,n,pad) 用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度

  • REPEAT(str,x) 返回 str 重复 x 次的结果

  • STRCMP(s1,s2)函数：比较字符串 s1 和 s2 的 ASCII 码值的大小

数值函数

  • ABS(x) 返回x绝对值

  • CEIL(x) 返回大于或等于给定数值的最小整数。

  • FLOOR(x) 返回小于或等于给定数值的最大整数。

  • ROUND(x,y) 返回x四舍五入有y位小数的值。如果是整数，将会保留y位数量的0；如果不写y，则默认y为0，即将x四舍五入后取整。适合于将所有数字保留同样小数位的情况

  • MOD(x，y) 返回x/y的模

  • RAND() 返回0到1内随机值 产生0～100随机整数 ceil(100*rand())

  • TRUNCATE(x,y) 返回数字x截断为y位小数的结果，不进行四舍五入

  • BIT_OR()、BIT_AND()配合GROUP BY降低存储量，提高统计查询效率。统计数字字段的情况，比如使用十进制数字存储某个人购买过的商品，每一位的1表示购买了某种商品，0表示没购买，则可以使用BIT_OR表示客户购买过哪些商品，BIT_AND表示客户每次都来买的商品

日期和时间函数

  • NOW() 返回当前日期和时间，年月日时分秒

  • CURDATE() 返回当前日期，年月日

  • CURTIME() 返回当前时间，时分秒

  • DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB()返回一个日期或时间值加上、减去一个时间间隔的时间值

    • HOUR 小时 hh
    • MINUTE 分 mm
    • SECOND 秒 ss
    • YEAR 年 YY
    • MONTH 月 MM
    • DAY 日 DD
    • YEAR_MONTH 年和月 YY-MM
    • DAY_HOUR 日和小时 DD hh
    • DAY_MINUTE 日和分钟 DD hh:mm
    • DAY_ SECOND 日和秒 DD hh:mm:ss
    • HOUR_MINUTE 小时和分 hh:mm
    • HOUR_SECOND 小时和秒 hh:ss
    • MINUTE_SECOND 分钟和秒 mm:ss

  • DATEDIFF(expr,expr2) 返回起始时间 expr 和结束时间 expr2 之间的天数

  • UNIX_TIMESTAMP(date) 返回日期date的UNIX时间戳

  • FROM_UNIXTIME 返回 UNIX 时间戳的日期值

  • WEEK(date) 返回日期 date 为一年中的第几周

  • YEAR(date) 返回日期 date 的年份

  • HOUR(time) 返回 time 的小时值

  • MINUTE(time) 返回 time 的分钟值

  • DATE_FORMAT(date,fmt) 返回按字符串 fmt 格式化日期 date 值

    • %S,%s 两位数字形式的秒（00,01,...,59）
    • %i 两位数字形式的分（00,01,...,59）
    • %H 两位数字形式的小时，24 小时（00,01,...,23）
    • %h,%I 两位数字形式的小时，12 小时（01,02,...,12）
    • %k 数字形式的小时，24 小时（0,1,...,23）
    • %l 数字形式的小时，12 小时（1,2,...,12）
    • %T 24 小时的时间形式（hh:mm:ss）
    • %r 12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
    • %p AM 或 PM
    • %W 一周中每一天的名称（Sunday,Monday,...,Saturday）
    • %a 一周中每一天名称的缩写（Sun,Mon,...,Sat）
    • %d 两位数字表示月中的天数（00,01,...,31）
    • %e 数字形式表示月中的天数（1,2，...,31）
    • %D 英文后缀表示月中的天数（1st,2nd,3rd,...）
    • %w 以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
    • %j 以 3 位数字表示年中的天数（001,002,...,366）
    • %U 周（0,1,52），其中 Sunday 为周中的第一天
    • %u 周（0,1,52），其中 Monday 为周中的第一天
    • %M 月名（January,February,...,December）
    • %b 缩写的月名（January,February,...,December）
    • %m 两位数字表示的月份（01,02,...,12）
    • %c 数字表示的月份（1,2,...,12）
    • %Y 4 位数字表示的年份
    • %y 两位数字表示的年份
    • %% 直接值“%”

聚合函数

  • SUM(): 计算数值列的总和。
  • AVG(): 计算数值列的平均值。
  • COUNT(): 计算某列的行数。
  • MAX() 和 MIN(): 分别返回列中的最大值和最小值。
  • GROUP_CONCAT(): 将多个行值连接为一个字符串。

流程函数

  • IF(value,t f)如果value是真，返回t；否则返回f
  • IFNULL(value1,value2) 如果value1不为空返回value1，否则返回value2
  • CASE WHEN [value1] THEN[result1]…ELSE[default]END 如果value1是真，返回 result1，否则返回 default
  • CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 如果expr等于value1，返回result1，否则返回default
  • COALESCE(): 返回参数列表中的第一个非 NULL 值。

格式化函数

  • FORMAT(): 格式化数字为格式化的字符串，通常用于货币显示。SELECT FORMAT(1234567.8945, 2) AS formatted_number;

类型转换函数

  • CAST(): 将一个值转换为指定的数据类型。SELECT CAST('2024-01-01' AS DATE) AS casted_date;
  • CONVERT(): 类似于CAST()，用于类型转换。SELECT CONVERT('123', SIGNED INTEGER) AS converted_number;

16. SQL 的隐式数据类型转换？

  • 当不同数据类型的值进行运算或比较时，会发生隐式数据类型转换。

17. MySQL的基础架构

mysqlinfrastructure.png
mysqlinfrastructure.png
  • 客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等
  • Server层：所有跨存储引擎的功能都在这一层实现，比如函数、存储过程、触发器等。不同的存储引擎共用一个Server层
    • 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
    • 查询缓存(MySQL 8.0后移除，因为表更新后缓存随时失效) 如果你的查询能够直接在这个缓存中找到key(查询的语句)，那么这个value(查询结果)就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。
    • 分析器：先会做“词法分析”。识别出里面的字符串分别是什么，代表什么。然后做“语法分析”，判断你输入的这个SQL语句是否满足MySQL语法。
    • 优化器：在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
    • 执行器：执行语句，然后从存储引擎返回数据。
  • 存储引擎层：负责数据存储和提取。支持InnoDB(默认)、MyISAM、Memory等多个存储引擎。可在create table语句中使用engine = MyISAM指定引擎.Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

18. 一条SQL查询语句在MySQL中如何执行的？

  • 客户端发送 SQL 查询语句到 MySQL 服务器。
  • 连接器检查客户端对表有没有执行查询的权限，没有则返回没有权限的错误，
  • 查询缓存，如果有，直接返回给客户端；(MySQL8.0版本以前)。
  • 分析器进行语法分析，提取sql语句中select等关键元素，然后判断sql语句是否有语法错误，比如关键词是否正确等等。
  • 优化器对查询的语句进行优化，确定 SQL 语句的执行计划。确定使用的索引等
  • 执行器按照生成的执行计划调用数据库引擎接口，取这个表的每一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中，直到取到这个表的最后一行，返回执行结果。

19. MySQL有哪些常见存储引擎？

  • mysql5.5后默认存储引擎改为innodb，5.6InnoDB支持全文索引。
  • InnoDB支持事务、全文索引、B+树索引、哈希索引（自适应，不能人为干预）、外键
  • MyISAM支持全文索引、B+树索引
  • MEMORY支持B+树索引、哈希索引

20. 存储引擎应该怎么选择？

  • 首选InnoDB：需要支持事务，并发控制(在并发条件下要求数据的一致性)，外键，崩溃恢复，数据操作除了插入和查询以外，还包括很多的更新、删除操作。适合类似计费系统或者财务系统等对数据准确性要求比较高的系统
  • 选择MyISAM：如果数据表主要用来插入和查询记录，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
  • 选择MEMORY：如果只是临时存放数据，需要快速访问，数据量不大，内容变化不频繁(代码表)并且不需要较高的数据安全性，可以作为临时表，存放查询的中间结果。需要保证数据库异常后能恢复。且表大小有限制
  • MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可有效地改善MERGE表的访问效率。主要用于诸如数据仓储等VLDB环境
  • 使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

21. InnoDB和MylSAM主要区别？

  • 存储结构
    • MyISAM：用三种格式的文件来存储，.frm 文件存储表的定义；.MYD 存储数据；.MYI 存储索引。
    • InnoDB：用两种格式的文件来存储，.frm 文件存储表的定义；.ibd 存储数据和索引。
  • 事务支持：MyISAM不支持事务；InnoDB支持事务
  • 最小锁粒度：MyISAM只支持表级锁，高并发中写操作存在性能瓶颈；InnoDB支持行级锁。并发写入性能高（命中索引时）
  • 索引类型：
    • MyISAM的索引为非聚簇索引，数据结构是B树，索引和数据分开存储，索引保存的是数据文件的指针；
    • InnoDB的索引是聚簇索引，索引和数据不分开。数据结构是B+树。
  • 主键必需：MyISAM表可以没有主键；InnoDB如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
  • 表的具体行数：MyISAM保存了表的总行数;InnoDB没有保存表的总行数
  • 外键支持：MyISAM不支持外键；InnoDB支持外键。外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，不建议在实际生产项目中使用外键，在业务代码中进行约束即可！
  • MyISAM 不支持数据库异常崩溃后的安全恢复，InnoDB支持。依赖于 redo log
  • MyISAM 不支持MVCC，InnoDB支持MVCC，MVCC可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。

22. MySQL日志文件及作用？

  • 错误日志（errorlog）：记录了当mysql启动、停止、运行过程中发生任何严重错误时的信息

  • 慢查询日志（slowquerylog）：记录执行时间超过long_query_time值的查询语句

  • 一般查询日志（generallog）记录了所有对MySQL数据库请求的信息，对于访问频繁的系统，不建议开启

  • 二进制日志（binlog）：记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），用于数据恢复

  • 重做日志（redolog）：记录对于InnoDB表的每个写操作，是物理级别的，主要用于崩溃恢复(InnoDB独有)

  • 回滚日志（undolog）：记录数据被修改前的值，用于事务的回滚(InnoDB独有)

  • binlog 是一种物理日志，会在磁盘上记录下数据库的所有修改操作，以便进行数据恢复和主从复制。

    • 当发生数据丢失时，binlog 可以将数据库恢复到特定的时间点。
    • 主服务器（master）上的二进制日志可以被从服务器（slave）读取，从而实现数据同步。

  • binlog 包括两类文件：二进制索引文件（.index）、二进制日志文件（.00000*）

  • binlog 默认不启用。需要在配置文件（my.cnf 或 my.ini）中设置 log_bin 参数。show variables like '%log_bin%'; 查看 binlog 是否开启。

log_bin = mysql-bin #开启binlog MySQL数据目录生成 db-bin.000001、db-bin.000002 等日志文件。

#mysql-bin.*日志文件最大字节（单位：字节）
#设置最大100MB 当 binlog 文件达到这个大小时，MySQL 会关闭当前文件并创建一个新的 binlog 文件。
max_binlog_size=104857600

#设置了只保留7天BINLOG（单位：天）过期的 binlog 文件将被自动删除。防止长时间累积的binlog文件占用过多存储空间
expire_logs_days = 7

#binlog日志只记录指定库的更新
#binlog-do-db=db_name

#binlog日志不记录指定库的更新
#binlog-ignore-db=db_name

#写缓冲多少次，刷一次磁盘，默认0 表示 MySQL 不会主动触发同步操作，而是依赖操作系统的磁盘缓存策略。即当执行写操作时，数据会先写入操作系统的缓存，当缓存区满了再由操作系统将数据写入磁盘。设置为 1 每次 binlog 写操作后都会同步到磁盘，这可以提高数据安全性，但可能会对性能产生影响。

sync_binlog=0

23. binlog和redolog有什么区别？

  • binlog会记录所有与数据库有关的日志记录，包括InnoDB、MyISAM等存储引擎的日志，而redolog只记InnoDB存储引擎的日志
  • 记录的内容不同，binlog记录事务的具体操作内容，是逻辑日志。redolog记录每个页（Page）的更改的物理情况
  • 写入的时间不同，binlog仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有redoertry被写入redolog中
  • 写入的方式也不相同，binlog是追加写入，不会覆盖已经写的文件。redolog是循环写入和擦除，

24. 一条更新语句怎么执行的了解吗？

mysqlexecuteupdate.jpg
mysqlexecuteupdate.jpg
  • 执行器先找引擎获取ID=2这一行。ID是主键，存储引擎检索数据，找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  • 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redolog里面，此时redolog处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
  • 执行器生成这个操作的binlog，并把binlog写入磁盘。
  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的redolog改成提交（commit）状态，更新完成。
  • MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redolog的写入。
  • 在对redolog写入时有两个阶段的提交，一是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入

25. 为什么要两阶段提交呢？

  • 假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入redolog，后写入binlog；要么先写入binlog，后写入redolog。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。
  • 假设ID=2的行字段c的值是0，再假设执行update T set c=c+1 where ID=2;语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？
    • 先写redo log后写binlog。假设在redo log写完，MySQL进程异常重启。redo log已写，崩溃恢复以后事务生效，这一行c的值是1。而用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，恢复出来的这一行c的值就是0，与数据库的值不同
    • 先写binlog后写redo log。假设binlog写完，MySQL进程异常重启。redo log未写，崩溃恢复以后事务无效，这一行c的值是0。而用binlog来恢复的时候就多了“把c从0改成1”这个日志，恢复出来的这一行c的值就是1，与数据库的值不一样
  • 简单说，redolog和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

26. redo log怎么刷入磁盘的知道吗？

  • redo log是固定大小的。redo log可以看作是一个逻辑上的loggroup，由一定数量的redologblock组成。块的大小是固定的512字节。它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。 其中有两个标记位置：writepos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。write pos和checkpoint(cp)之间的是空闲部分，可以用来记录新的操作。当write_pos追上checkpoint时，表示redolog日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。
  • redo log的写入机制：事务执行过程中，先把日志写到redo log buffer。 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（fsync）
  • 在如下的一些情况中，redo log buffer的数据会刷入磁盘：
  • log buffer空间不足时：如果当前写入redo log buffer的日志量超过logbuffer总容量innodb_log_buffer_size的一半，后台线程会主动写盘fwrite，保存在文件系统的page cache，但没有调用fsync
  • 并行的事务提交时，顺带将这个事务的redo log buffer持久化到磁盘。当innodb_flush_log_at_trx_commit=1，假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候会带上事务A在redo log buffer里的日志一起持久化到磁盘。
  • 后台线程输入：innodb_flush_log_at_trx_commit=2时，有一个后台线程，大约每秒都会刷新一次redo log buffer中的redo log到磁盘。
  • 正常关闭服务器时
  • 触发checkpoint规则：

31. 索引的分类

  • 功能分类
  • 主键索引: 表中每行数据唯一标识的索引，强调列值的唯一性和非空性。一张数据表有只能有一个主键
  • 唯一索引: 保证数据列中每行数据的唯一性，允许有空值。
  • 普通索引: 基本的索引类型，用于加速查询。允许数据重复和NULL
  • 全文索引：用于检索大文本数据中的关键字的信息，而不用使用 like ‘%…%’
  • 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
  • 从数据结构上分类
    • B+树索引：一种将索引值按照一定的算法，存入一个树形的数据结构中（二叉树），每次查询都从树的根节点开始，一次遍历叶子节点，找到对应的值。查询效率是O(logN)。
    • Hash索引：基于哈希表的索引，查询效率可以达到O(1)，但是只适合=和in查询，不适合范围查询和排序。当发生哈希冲突的时候也是通过拉链法来解决。InnoDB并不提供直接创建哈希索引的选项。但InnoDB使用了一种名为“自适应哈希索引”（Adaptive Hash Index, AHI）的技术，可通过SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';查看
  • 从存储位置上分类：
    • 聚簇索引：聚簇索引的叶子节点保存了一行记录的所有列信息。聚簇索引的叶子节点中，包含了一个完整的记录行。
    • 非聚簇索引：叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。
  • InnoDB存储引擎的主键使用的是聚簇索引，MyISAM存储引擎不管是主键索引，还是二级索引使用的都是非聚簇索引。

37. 为什么 InnoDB 要使用 B+树作为索引？

  • 磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小。如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

  • 查询性能更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。这种特性使得所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

  • 支持高效的范围查询：B+树的叶子节点存储了所有数据记录的指针，并且形成了一个有序链表，这使得范围查询变得非常高效。数据库经常需要查询某个范围内的数据，B+树的结构能够很好地满足这一需求。

  • 减少回表操作：B+树的内部节点只存储索引信息，不存储数据记录的具体信息。当查询条件能够命中索引时，可以先通过索引找到数据记录的指针，然后再根据指针到数据页中查找具体的数据记录。这种方式可以减少回表操作，提高查询效率。

  • 支持并发访问：InnoDB 存储引擎支持行级锁定，而 B+树的结构可以很方便地支持这种锁定方式。当对某个数据记录进行锁定时，只需要锁定该数据记录所在的叶子节点即可，这样可以减少锁定的范围，提高并发访问的性能。

  • 在InnoDB存储引擎中，默认的页大小是16KB。可以通过showvariableslike'innodb_page_size';查看。

  • B树是一种自平衡的多路查找树，和红黑树、二叉平衡树不同，B树的每个节点可以有m个子节点，而红黑树和二叉平衡树都只有2个

  • 内存和磁盘在进行IO读写的时候，有一个最小的逻辑单元，叫做页（Page），页的大小一般是4KB

  • 为了提高读写效率，从磁盘往内存中读数据的时候，一次会读取至少一页的数据，比如说读取2KB的数据，实际上会读取4KB的数据；读取5KB的数据，实际上会读取8KB的数据。我们要尽量减少读写的次数。因为读的次数越多，效率就越低。

  • 树越高，意味着查找数据时就需要更多的磁盘IO，因为每一层都可能需要从磁盘加载新的节点。B树的节点大小通常与页的大小对齐，这样每次从磁盘加载一个节点时，可以正好是一个页的大小。因为B树的节点可以有多个子节点，可以填充更多的信息以达到一页的大小。

  • B树的一个节点通常包括三个部分：键值：即表中的主键；指针：存储子节点的信息；数据：表记录中除主键外的数据。正是因为B树的每个节点上都存了数据，就导致每个节点能存储的键值和指针变少了，因为每一页的大小是固定的，于是B+树就来了，B+树的非叶子节点只存储键值，不存储数据，而叶子节点存储了所有的数据，并且构成了一个有序链表。这样做的好处是，非叶子节点上由于没有存储数据，就可以存储更多的键值对，树就变得更加矮。由此一来，查找数据进行的磁盘IO就更少了，查询的效率也就更高了。

  • 再加上叶子节点构成了一个有序链表，范围查询时就可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。

38. 那一棵B+树能存储多少条数据呢？

  • InnoDB 存储引擎的最小存储单元是页，默认大小是 16k。假设主键ID是bigint类型，长度为8个字节。指针大小为6字节，这样一共14字节。所以非叶子节点(一页)可以存储16384/14=1170个这样的单元(键值+指针)。
  • 一个指针指向一个存放记录的页，一页可以放16条数据，树深度为2的时候，可以存放1170*16=18720条数据。
  • 树深度为3的时候，可以存储的数据为1170117016=21902400条记录。
  • 理论上，在InnoDB存储引擎中，B+树的高度一般为2-4层，就可以满足千万级数据的存储。查找数据的时候，一次页的查找代表一次IO，当我们通过主键索引查询的时候，最多只需要2-4次IO就可以了。

39. 为什么要用B+树，而不用普通二叉树？

  • 为什么不用普通二叉树？普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

  • 为什么不用平衡二叉树呢？读取数据的时候，是从磁盘读到内存。如果树这种数据结构作为索引，那每查找一次数据就需要从磁盘中读取一个节点，也就是一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B+树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快。

40. 为什么用B+树而不用B树呢？√

  • 更高的查询效率：B+树的所有值（数据记录或指向数据记录的指针）都存在于叶子节点，并且叶子节点之间通过指针连接，形成一个有序链表。使得 B+树非常适合进行范围查询。一旦到达了范围的开始位置，接下来的元素可以通过遍历叶子节点的链表顺序访问，而不需要回到树的上层。而 B 树的数据分布在整个树中，进行范围查询时可能需要遍历树的多个层级。
  • 更高的空间利用率：在 B+树中，非叶子节点不存储数据，只存储键值，这意味着非叶子节点可以拥有更多的键，从而有更多的分叉。这导致树的高度更低，进一步降低了查询时磁盘 I/O 的次数，因为每一次从一个节点到另一个节点的跳转都可能涉及到磁盘 I/O 操作。
  • B+树的磁盘读写代价更低：根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，IO次数更少。
  • 排序能力更强：因为叶子节点上有下一个数据区的指针，数据形成了链表。
  • 查询效率更稳定：B+树中所有叶子节点深度相同，所有数据查询路径长度相等，保证了每次搜索的性能稳定性。而在 B 树中，数据可以存储在内部节点，不同的查询可能需要不同深度的搜索。

41. Hash索引和B+树索引区别是什么？

  • B+树可以进行范围查询，Hash索引不能。
  • B+树支持联合索引的最左侧原则，Hash索引不支持。
  • B+树支持orderby排序，Hash索引不支持。
  • Hash索引在等值查询上比B+树效率更高。
  • B+树使用like进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

42. 聚簇索引与非聚簇索引的区别？

  • 在聚簇索引中，索引结构和数据一起存放。主键索引属于聚集索引。表的.ibd文件就包含了该表的索引和数据，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
  • 在非聚簇索引中，索引结构和数据分开存放的索引。非聚簇索引的叶子节点不直接包含数据记录，而是包含了指向数据行的指针
  • 在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键ID，所以当我们使用非聚簇索引进行查询时，首先会得到一个主键ID，然后再使用主键ID去聚簇索引上找到真正的行数据，我们把这个过程称之为回表查询。
  • MyISAM采用的是非聚簇索引，InnoDB采用的是聚簇索引。
  • 聚簇索引直接将数据存储在B+树的叶子节点中，而非聚簇索引的叶子节点存储的是指向数据行的指针。
  • 一个表只能有一个聚簇索引，但可以有多个非聚簇索引。
  • 聚簇索引改善了顺序访问的性能，但更新主键的成本较高；非聚簇索引适合快速插入和更新操作，但检索数据可能需要更多的磁盘I/O。

43. 回表了解吗？

  • 在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

44. 覆盖索引了解吗？

  • 查询时已经在索引树上获得要返回的数据，无需回表的查询，可以通过建立联合索引支持覆盖索引，牺牲空间

45. 什么是最左前缀原则/最左匹配原则？

  • 在InnoDB的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。根据最左匹配原则，最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。例如：用（name，age）这个联合索引，where name = '张三'或者where name like '张%'，

46. 什么是索引下推优化？

  • 索引条件下推优化（Index Condition Pushdown(ICP)）是MySQL5.6添加的，用于优化数据查询。
  • 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQLServer，MySQLServer进行过滤条件的判断。
  • 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQLServer将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQLServer传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器
  • 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

47. MySQL中有哪几种锁

  • 按锁粒度划分
    • 表锁：开销小，加锁快；锁定粒度大，发生锁冲突概率高，并发度最低;不会出现死锁。
    • 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
    • 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
  • 按照兼容性
    • 共享锁（SLock）,也叫读锁（readlock），相互不阻塞。
    • 排他锁（XLock），也叫写锁（writelock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。
  • 按加锁机制
    • 乐观锁：假设冲突在系统中出现的频率较低，因此在数据库事务执行过程中，不会频繁地去锁定资源。相反，它在提交更新的时候才检查是否有其他事务已经修改了数据。可以通过在数据表中使用版本号（Version）或时间戳（Timestamp）来实现，每次读取记录时，同时获取版本号或时间戳，更新时检查版本号或时间戳是否发生变化。如果没有变化，则执行更新并增加版本号或更新时间戳；如果检测到冲突（即版本号或时间戳与之前读取的不同），则拒绝更新。
    • 悲观锁：假设冲突是常见的，因此在数据处理过程中，它会主动锁定数据，防止其他事务进行修改。可以直接使用数据库的锁机制，如行锁或表锁，来锁定被访问的数据。常见的实现是 SELECT FOR UPDATE 语句，它在读取数据时就加上了锁，直到当前事务提交或回滚后才释放。

48. 如何解决库存超卖问题？

  • 按照乐观锁的方式：
UPDATE inventory SET count = count - 1, version = version + 1 WHERE product_id = 1 AND version = current_version;
  • 按照悲观锁的方式：
START TRANSACTION;
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;
UPDATE inventory SET count = count - 1 WHERE product_id = 1;
COMMIT;

49. 说说InnoDB里的行锁实现?

  • RecordLock记录锁：记录锁就是直接锁定某行记录。当使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id = 6 for update;就会将id=6的记录锁定

  • GapLock间隙锁：间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。间隙锁就是锁定某些间隙区间的。当使用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id = 3 for update;或者select * from t where id>1 and id<6 for update;就会将(1,6)区间锁定。

  • Next-keyLock临键锁：间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。临键锁就是记录锁(RecordLocks)和间隙锁(GapLocks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select*fromtwhereid>5andid<=7forupdate;会锁住(4,7]、(7,+∞)。mysql默认行锁类型就是临键锁(Next-KeyLocks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-KeyLocks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

  • 间隙锁(GapLocks)和临键锁(Next-KeyLocks)都是用来解决幻读问题的，在已提交读（READCOMMITTED）隔离级别下，间隙锁(GapLocks)和临键锁(Next-KeyLocks)都会失效！

  • Insert Intention Lock插入意向锁

  • 一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁，如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。这种类型的锁命名为Insert Intention Locks，也就是插入意向锁。

  • 假如我们有个T1事务，给(1,6)区间加上了意向锁，现在有个T2事务，要插入一个数据，id为4，它会获取一个（1,6）区间的插入意向锁，又有有个T3事务，想要插入一个数据，id为3，它也会获取一个（1,6）区间的插入意向锁，但是，这两个插入意向锁锁不会互斥。

50. 意向锁是什么知道吗？

  • 意向锁是一个表级锁，不要和插入意向锁搞混。
  • 意向锁的出现是为了支持InnoDB的多粒度锁，它解决的是表锁和行锁共存的问题。
  • 当给一个表加表锁的时候，需要根据表中有没有数据行被锁定，以确定是否能加成功。
  • 假如没有意向锁，那么遍历表中所有数据行来判断有没有行锁；
  • 有了意向锁这个表级锁之后，则我们直接判断一次就知道表中是否有数据行被锁定了。
  • 有了意向锁之后，要执行的事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的互斥锁时就会失败，因为表上有意向排他锁之后事务B申请表的互斥锁时会被阻塞。

51. MySQL的乐观锁和悲观锁了解吗？

  • 悲观锁（PessimisticConcurrencyControl）：认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。行锁，表锁，读锁，写锁均为悲观锁。
  • 乐观锁（OptimisticConcurrencyControl）认为数据的变动不会太频繁。通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现，其中，版本最为常用。
  • 事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将其更新到表中时，会将之前取出的版本v1与数据中最新的版本v2相对比，如果v1=v2，那么说明在数据变动期间，没有其他事务对数据进行修改，此时，就允许事务对表中的数据进行修改，并且修改时version会加1，以此来表明数据已被变动。如果，v1不等于v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新到表中，一般的处理办法是通知用户让其重新操作。不同于悲观锁，乐观锁通常是由开发者实现的。

53. MySQL事务特性？

  • 原子性：事务的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务中的操作不能只执行其中一部分。
  • 一致性：一个事务中，事务前后数据的完整性必须保持一致。一致性确保事务从一个一致的状态转换到另一个一致的状态。比如在银行转账事务中，无论发生什么，转账前后两个账户的总金额应保持不变。假如A账户（100块）给B账户（10块）转了10块钱，不管成功与否，A和B的总金额都是110块。
  • 隔离性：多个并发事务之间需要相互隔离，即一个事务的执行不能被其他事务干扰。主要是为了解决事务并发执行时可能出现的问题，如脏读、不可重复读、幻读等。数据库系统通过事务隔离级别（如读未提交、读已提交、可重复读、串行化）来实现事务的隔离性。
  • 持久性：事务一旦提交，它对数据库所做的更改就是永久性的，即使发生系统崩溃，修改的数据也不会丢失

54. ACID怎么保证？

  • 原子性（Atomicity），undolog记录了事务发生之前的数据，如果事务失败，InnoDB会根据undolog回滚数据。当事务开始修改数据时，InnoDB首先会在undolog中记录旧值（即修改前的值）。如果事务顺利进行并最终提交，undolog会在某个时间点被清除。如果事务中的某个操作失败或者事务被明确地回滚，InnoDB会使用undolog中的信息来撤销所有更改，确保数据的原子性

  • 一致性（Consistency），只要保证原子性、隔离性、持久性，自然也就保证了数据的一致性。还有业务代码

  • 隔离性(Isolation)，MySQL使用多种隔离级别来控制事务如何与其他并发事务隔离。InnoDB存储引擎使用MVCC(多版本并发控制)机制来处理并发事务，确保每个事务都有自己的数据版本，每次更新记录时，都会生成记录的一个新版本，而不是覆盖老版本。每个版本都会有两个额外的属性：一个表示版本的创建时间（或事务ID），另一个表示版本的过期时间（或下一个版本的事务ID）。当事务尝试读取记录时，它会看到该事务开始时有效的那个版本。不同的事务会看到不同版本的数据行，这取决于事务的开始时间和它的隔离级别。对于如"读未提交"（READUNCOMMITTED）这样的较低隔离级别，事务可能会看到其他未提交事务所做的更改。但在更高的隔离级别，如"可重复读"（REPEATABLEREAD）或"串行化"（SERIALIZABLE），事务不会看到其他事务所做的更改，直到它们被提交。

  • 持久性(Durability)，存储引擎（如InnoDB）通过写入磁盘来确保。即使在系统崩溃之后，已提交事务的更改也不会丢失

  • InnoDB使用“redolog”来记录数据的更改，在系统崩溃后，redolog可用于恢复数据。保证数据永不丢失

  • redolog是一种物理日志，记录了对数据页的物理更改。当事务进行写操作时，InnoDB首先会写入redolog，并不会立即修改数据文件。这种写入方式被称为“write-aheadlogging”（先写日志）。

  • 当redolog填满或在某些其他情况下，InnoDB会异步将这些更改刷新到数据文件中。

  • 系统崩溃时，由于数据可能还没有被真正写入数据文件，但已经在redolog中，因此系统可以在启动时使用这些日志来重新执行或“重做”这些更改，确保数据的持久性。

  • 即使数据库在事务提交后立即崩溃，由于事务的更改已经记录在redolog中，这些更改在数据库恢复时仍然是安全的。

55. 事务的隔离级别？默认隔离级别是什么？

  • 读未提交（ReadUncommitted）读取尚未提交的数据 ：哪个问题都不能解决
  • 读已提交（ReadCommitted）读取已经提交的数据 ：可解决脏读（oracle默认）
  • 可重复读（RepeatableRead）可重复读：指一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的.除非是自己修改的数据。可解决脏读和不可重复读，（mysql默认）
  • 串行化（Serializable）对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。可解决 脏读 不可重复读 和 幻读
  • 性能read uncommitted>read committed>repeatable read>serialazable
  • 安全性read uncommitted<read committed<repeatable read<serialazable

56. 什么是幻读，脏读，不可重复读呢？

  • 脏读（dirty read） : 一个事务读取到了另一个事务尚未提交的数据
  • 不可重复读（non-repeatable read）: 一个事务读到了另一个事务已经提交的update数据 两次读取的数据的内容不一致
  • 幻读/虚读（phantom read） : 一个事务读到了另一个事务已经提交的insert、delete数据 两次读取的数据的数量不一致

57. 事务的各个隔离级别都是如何实现的？

  • 读未提交
    • 读不加锁。不阻塞其他事务的读和写
    • 事务写阻塞其他事务写，但不阻塞其他事务读；
  • 读取已提交&可重复读
    • 利用了ReadView和MVCC，也就是每个事务只能读取它能看到的版本（ReadView）。
    • READCOMMITTED：每次读取数据前都生成一个ReadView
    • REPEATABLEREAD：在第一次读取数据时生成一个ReadView
  • 串行化：对于同一行事务，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

58. MVCC实现原理？

  • MVCC(MultiVersionConcurrencyControl)，多版本并发控制，主要用来解决数据库并发问题。MVCC是通过版本链和 ReadView 机制来实现的
  • 版本链：对于InnoDB存储引擎，每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR
    • DB_TRX_ID，保存创建这个版本的事务 ID。
    • DB_ROLL_PTR，指向 undo 日志记录的指针，这个记录包含了该行的前一个版本的信息。通过这个指针，可以访问到该行数据的历史版本。
    • 每次变动都会先把undo日志记录下来，并用DB_ROLL_PTR指向undo日志地址。因此可以认为，对该条记录的修改日志串联起来就形成了一个版本链，版本链的头节点就是当前记录最新的值。
  • ReadView：对于ReadCommitted和RepeatableRead隔离级别来说，都需要读取已经提交的事务所修改的记录，也就是说如果版本链中某个版本的修改没有提交，那么该版本的记录时不能被读取的。所以需要确定在ReadCommitted和RepeatableRead隔离级别下，版本链中哪个版本是能被当前事务读取的。于是就引入了ReadView这个概念来解决这个问题。
  • ReadView就是事务执行快照读时，产生的读视图，相当于某时刻表记录的一个快照，通过这个快照，我们可以获取：
    • m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
    • min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
    • max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
    • creator_trx_id：表示生成该ReadView的事务的事务id
  • 有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：
    • 如果被访问版本的DB_TRX_ID属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
    • 如果被访问版本的DB_TRX_ID属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
    • 如果被访问版本的DB_TRX_ID属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
    • 如果被访问版本的DB_TRX_ID属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
    • 如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。
  • READCOMMITTED是每次读取数据前都生成一个ReadView，这样就能保证自己每次都能读到其它事务提交的数据；
  • REPEATABLEREAD是在第一次读取数据时生成一个ReadView，这样就能保证后续读取的结果完全一致。

59. 数据库读写分离了解吗？

读写分离的基本原理是将数据库读写操作分散到不同的节点上。读写分离的基本实现是:

  • 数据库服务器搭建主从集群，一主一从、一主多从都可以。
  • 数据库主机负责写操作，从机只负责读操作。
  • 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。

60. 读写分离的分配怎么实现呢？

  • 程序代码封装：在代码中抽象一个数据访问层（所以有的文章也称这种方式为"中间层封装"），实现读写操作分离和数据库服务器连接的管理。例如，基于Hibernate进行简单封装，就可以实现读写分离：淘宝的 TDDL （Taobao Distributed Data Layer
  • 中间件封装：独立一套系统出来，实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供SQL兼容的协议，业务服务器无须自己进行读写分离。对于业务服务器来说，访问中间件和访问数据库没有区别，事实上在业务服务器看来，中间件就是一个数据库服务器。

61. 主从复制原理了解吗？

  • MySQL 的主从复制（Master-Slave Replication）是一种数据同步机制，用于将数据从一个主数据库（master）复制到一个或多个从数据库（slave）。用于数据备份、灾难恢复和数据分析等场景。
  • 在主服务器上，所有修改数据的语句（如 INSERT、UPDATE、DELETE）会被记录到binlog日志中。
  • 主服务器上的一个线程（二进制日志转储线程）负责读取binlog日志的内容并发送给从服务器。
  • 从服务器接收到二进制日志数据后，会将这些数据写入自己的中继日志（Relay Log）。中继日志是从服务器上的一个本地存储
  • 从服务器上有一个 SQL 线程会读取中继日志，并在本地数据库上执行，从而将更改应用到从数据库中，完成同步。
    mysqlslave.jpg

62. 主从同步延迟怎么处理？

  • 主从同步延迟的原因:一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作,但是从服务器的里面读取binlog的线程仅有一个，当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

  • 写操作后的读操作指定发给数据库主服务器.例如，注册账号完成后，登录时读取账号的读操作也发给数据库主服务器。这种方式和业务强绑定，对业务的侵入和影响较大，如果哪个新来的程序员不知道这样写代码，就会导致一个bug。

  • 读从机失败后再读一次主机。即"二次读取"，二次读取和业务无绑定，只需要对底层数据库访问的API进行封装即可，实现代价较小，不足之处在于如果有很多二次读取，将大大增加主机的读操作压力。例如，黑客暴力破解账号，会导致大量的二次读取操作，主机可能顶不住读操作的压力从而崩溃。

  • 关键业务读写操作全部指向主机，非关键业务采用读写分离。例如，对于一个用户管理系统来说，注册+登录的业务读写操作全部访问主机，用户的介绍、爰好、等级等业务，可以采用读写分离，因为即使用户改了自己的自我介绍，在查询时却看到了自我介绍还是旧的，业务影响与不能登录相比就小很多，还可以忍受。

63. 百万级别以上的数据如何删除？

  • 由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。在删除数据库百万级别数据的时候删除数据的速度和创建的索引数量是成正比的。
  • 删除索引
  • 删除其中无用数据
  • 重新创建索引

64. 百万千万级大表如何添加字段？

  • 通过中间表转换过去，创建一个临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，删除旧表，新表命名为旧表的名称，这种方式可能回丢掉一些数据。
  • 用pt-online-schema-change是percona公司开发的一个工具，它可以在线修改表结构，它的原理也是通过中间表。
  • 先在从库添加再进行主从切换：如果一张表数据量大且是热表（读写特别频繁），则可以考虑先在从库添加，再进行主从切换，切换后再将其他几个节点上添加字段。

66. mysql自增id用完怎么办?

  • 表定义的自增值达到上限后，再申请时值保持不变。导致继续插入数据时报主键冲突的错误
  • 无主键自增row_id 6字节(0到2^48-1)达到上限后会归0重新递增。如果出现相同的row_id则覆盖原有的数据。InnoDB维护了一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值加1