跳至主要內容

MySQL

HeChuangJun约 9833 字大约 33 分钟

.1. 数据库三大范式？varchar与char、blob和text区别？DATETIME和TIMESTAMP异同？存储货币、ip、emoji😊？

第一范式1NF：字段值具有原子性，不能再分。姓名字段如果区分姓和名则分为两个字段
第二范式2NF：满足第一范式基础上，表中每一列都和全部主键相关，而不能只与一部分主键相关（联合主键）。订单表的商品价格只与商品ID相关，与订单ID无关
第三范式3NF：满足第二范式基础上，表中每一列都和主键直接相关，而不能间接相关。订单表的订单编号和商品编号，商品名称与商品ID相关再与订单相关，应拆分为订单表、订单商品关联表、商品表
控制数据冗余，节省空间，可冗余数据，空间换时间，避免跨表跨库查询，提高性能

char长度固定；varchar长度不固定
char插入数据的长度小于固定长度时用空格填充，会删除插入数据的尾部空格；VARCHAR按照插入的数据长度来存储，不会删除尾部空格
因为长度固定，char存取速度要比varchar快，可能浪费空间，空间换时间；因为长度不固定，varchar比char存取慢，不浪费空间，时间换空间
char最多存255个字符与字符集无关，字节数与字符集有关，因为不同字符集对字符的编码字节数不同；varchar最多存65532个字节，包括小于等于255个字符用1个、否则2个字节存储字符串的长度信息，字符数与字符集有关
varchar(50)最多存50个字符。varchar(50)和(200)在相同字符数时字节数一样，但VARCHAR(200)在长度信息部分会多1个字节，排序时消耗更多内存
对于长度相对固定的字符串使用char，对于长度不确定使用varchar

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

同：存储时间的表现格式一致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字节；4字节
DATETIME存储时间与时区无关；有关
DATETIME默认值为null；TIMESTAMP不为空，默认当前时间(CURRENT_TIMESTAMP)
范默大时

Decimal和Numric，字符串存储保存小数精度。如DECIMAL(9,2)，9代表将总的小数位数，2代表小数点后的位数。存储范围从-9999999.99到9999999.99，float和double以二进制存储存在误差
bigint

INSERT INTO sessions (ipaddress) VALUES (inet_aton('192.168.0.1'));
SELECT inet_ntoa(ipaddress) FROM sessions;
SELECT * from t WHERE inet_aton(ip) >= inet_aton('192.168.1.3') 
AND inet_aton(ip) <= inet_aton('192.168.1.20')
字符串，使用4字节的utf8mb4编码
ALTER TABLE blogs modify content text CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

.2. SQL分类、查第3-10条、SQL执行顺序？√内连接、左连接、右连接、交叉连接、笛卡尔积区别？in/exists，delete、truncate/drop，UNION/UNION ALL，count(1)、count(*)、count(主键|列名)区别？函数,SQL的隐式数据类型转换？

Structure Query Language结构化查询语言
Data Definition Languages定义数据段、数据库、表、列、索引等数据库对象create、alter、drop
Data Manipulation操纵 Language 添加、删除、更新和查询数据库记录insert、delete、update
Data Query Language查询数据表的记录select
Data Control Language 定义数据库、表、字段、用户的访问权限和安全级别grant、revoke

## 创建数据库
CREATE DATABASE 数据库名 [CHARACTER SET 字符集] [COLLATE 校对规则];

## 查看所有数据库/表/数据库详细说明(编码)
SHOW DATABASES;
SHOW TABLES;
SHOW CREATE DATABASE 数据库名;

## 选择/查看操作的数据库
USE 数据库名;
SELECT DATABASE();

## 改变数据库的全局特性
ALTER DATABASE 数据库名 [CHARACTER SET 字符集] [COLLATE 校对规则];

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

## 创建表/MEMORY表/MERGE表
CREATE TABLE 表名(列名 数据类型(长度)[约束]...)
[ENGINE=存储引擎][CHARACTER SET 字符集] [COLLATE 校对规则]
CREATE TABLE 表名 ENGINE=MEMORY select子句
CREATE TABLE 表名(列名 类型(长度)[约束]...) 
engine=merge union=(表名...) [INSERT_METHOD=LAST|FIRST|NO]

## 查看表定义/建表sql
DES 表名;
SHOW CREATE TABLE 表名;

## 删除表
DROP TABLE 表名;
TRUNCATE TABLE 表名;

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

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

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

## 改表名/字符集
ALTER TABLE 旧表名 RENAME 新表名;
ALTER TABLE 表名 character set 字符集;

## 增加外键/删外键
## `RESTRICT`和`NO ACTION`限制在子表有关联记录的情况下主表不能更新；
## `CASCADE`主表在更新或者删除时，更新或者删除子表对应记录；
## `SET NULL`表示主表在更新或者删除的时候，子表的对应字段被 `SET NULL`

ALTER TABLE 子表 add constraint 外键名以_fk结尾 foreign key (子表外键列名) 
references 主表(主键) [ON DELETE/UPDATE RESTRICT/CASCADE/SET NULL/NO ACTION];
ALTER TABLE 子表 drop foreign key 外键名;

## 添加主键约束
ALTER TABLE table_name ADD PRIMARY KEY (列名);

## 查字符集对应的校对规则/编码
show character set;
SHOW VARIABLES LIKE 'char%';

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

## 创建/删除索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 [USING 索引类型] ON 表名 (列名)[(长度)(s)],
DROP INDEX 索引名 ON 表名

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

## 修改(多表)记录
UPDATE 表1[,表2] SET 列名=值[,列名=值(s)] [where 条件];

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

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

## 查询记录
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每页数量]

## 子查询[not] in、=、!=、[not] exists、ALL、ANY、SOME，表连接用于优化子查询
select * from 表名 where 列名=（select where 列名=值）;

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

## 创建用户
CREATE USER 'uname'@'host' IDENTIFIED BY 'pwd';
## 授权
GRANT ALL[PRIVILEGES]/SELECT/INSERT ON 库名.表名 TO 'uname'@'localhost' IDENTIFIED BY 'pwd'
## 回收
REVOKE ALL[PRIVILEGES]/SELECT/INSERT ON 库名.表名 FROM 'uname'@'localhost'
## 删除用户
DROP USER 'uname'@'host';

SELECT * FROM table_name LIMIT 2, 8;

FROM，ON，JOIN，WHERE，GROUP BY，ACG_FUNC，CUBE|ROLLUP，HAVING，SELECT，DISTINCT，ORDER BY，LIMIT
聚合函数在WHERE之后执行 故WHERE不能用聚合函数

inner join内连接，取得两张表中满足连接条件的记录。相当于两个数据集的交集
outer join外连接：取得两张表中满足连接条件的记录，和某张表中不满足匹配关系的记录。分为
left join返回左表（FROM子句中的表）所有记录，以及右表满足连接条件的记录。如果右表中没有匹配的记录，则结果中右表的部分会以NULL填充，right join右连接相反
crossjoin交叉连接：显示两张表所有记录，笛卡尔积在SQL的实现，如果A表有m行，B表有n行，那么A和B交叉连接的结果就有m*n行。
笛卡尔积：集合A={a,b}，集合B={0,1,2}，AxB={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>}

in语句把外表（from子句中的表）和内表（in子查询的表）作hash连接(先内后外)。exists语句对外表作loop循环，每次loop循环再对内表进行查询(先外后内)
子查询表大用exists，小用in。”小表驱动大表“思想：exist驱动表是外表，in是内表
not exists比not in快，not in内外表都全表扫描；而not extsts的子查询能用索引

deletetruncatedrop
类型DMLDDLDDL
回滚可回滚不可回滚不可回滚
删除内容删除部分数据，不释放空间删除所有数据,释放空间删除数据后重新写数据会从1开始删除表结构，索引和权限也会被删除
删除速度慢，逐行删除快最快

都是对两个结果集进行并集操作，Union去重并排序(按默认规则)，Union All不去重不排序；速度快

MyISAM执行count(*)会返回存储在磁盘上的表总行数，效率高
InnoDB执行count(*)需要实时计数；因为innodb每个事务查询结果有差异，故不存储表总行数

count(*)允许null。不取值，自动扫描索引树小的。减少扫描数据量
count(1)允许null，不取值，1代表代码行，速度比count(主键id)快。因为返回id会涉及到解析以及拷贝字段值操作
count(主键id/列名)不允许null。取id/列名值
效率排序：count(字段)<count(主键id)<count(1)≈count()，建议使用count()

字符串函数

  • CANCAT(S1,S2,…Sn) 连接 S1,S2,…Sn 为一个字符串，与NULL连接的结果是NULL
  • LENGTH():
  • SUBSTRING(str,x,y) 返回从x位置起y个字符长度的子串
  • REPLACE(str,a,b)
  • LOWER(str)、UPPER(str) 将所有字符变为小、大写
  • TRIM(str) 去掉头尾的空格

数值函数

  • ABS(x) 返回x绝对值
  • CEIL(x)、FLOOR(x) 返回大于/小于或等于给定数值的最小/大整数。
  • ROUND(x,y) 返回x四舍五入有y位小数的值。如果是整数，将会保留y位数量的0；y不写默认0
  • MOD(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()返回一个日期或时间值加上、减去一个时间间隔的时间值
  • DATEDIFF(expr,expr2) 返回两个时间之间的天数

聚合函数

  • SUM()、AVG()、COUNT()、MAX()、MIN()、GROUP_CONCAT()将多个行值连接为一个字符串

流程函数

  • IF(value,t f)如果value是true，返回t；否则返回f
  • IFNULL(value1,value2) 如果value1不为空返回value1，否则返回value2
  • CASE WHEN [value1] THEN[result1]…ELSE[default]END 如果value1是true，返回 result1，否则返回 default
  • CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 如果expr等于value1，返回result1，否则返回default
  • COALESCE()：返回第一个非NULL值

格式化函数

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

类型转换函数

  • CAST(): CAST('2024-01-01' AS DATE) AS casted_date;
  • CONVERT()：CONVERT('123', SIGNED INTEGER) AS converted_number;

当不同数据类型的值进行运算或比较时，会发生隐式数据类型转换。可以通过显式转换来规避

.3. 基础架构？SQL查询语句如何执行？bin目录下的可执行文件？数据存储形式

mysqlinfrastructure.png
mysqlinfrastructure.png

客户端：用于连接处理、授权认证等
Server层：实现所有跨存储引擎的功能，比如函数、存储过程、触发器等。不同的存储引擎共用Server层
存储引擎层：负责数据存储和提取。在create table时用engine = MyISAM指定。Server层通过接口与存储引擎进行通信。屏蔽了不同存储引擎的差异

客户端发送SQL查询语句到MySQL服务器。
连接器与客户端建立连接，检查客户端对表有没有执行查询的权限
查询缓存，(8.0后移除，因为表更新后缓存随时失效) 如果在缓存中找到key查询的语句，那么value将查询结果返回给客户端。如果找不到，则继续执行。执行完成后，执行结果会被存入查询缓存中
分析器做语法分析，提取sql语句中select等关键字、判断是否有语法错误
优化器优化查询语句，确定SQL执行计划、使用的索引及各个表的连接顺序
执行器按照执行计划调用数据库引擎接口，取这个表的每一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中，直到取到这个表的最后一行，返回执行结果。

mysql：MySQL客户端
mysqldump：数据库备份工具，用于数据备份、迁移或恢复。
mysqladmin：管理工具，用来创建数据库、删除数据库、查看MySQL服务器的状态
mysqlcheck：命令行工具，用于检查、修复、分析和优化数据库表
mysqlimport：用于从文本文件中导入数据到数据库表中
mysqlshow：用于显示MySQL数据库服务器中的数据库、表、列等信息
mysqlbinlog：用于查看MySQL二进制日志文件的内容、恢复数据、查看数据变更等

mysqlrow.png
段Segment：表空间由多个段组成，创建索引时会创建数据段和索引段，数据段用来存储叶子阶段中的数据；索引段用来存储非叶子节点的数据。回滚段包含了事务执行过程中用于数据回滚的旧数据。
区Extent：段由多个区组成，区是一组连续的页，通常包含64个连续的页，1M的数据。使用区进行数据分配可以优化磁盘操作，减少磁盘IO次数
页Page：InnoDB存储数据的基本单元，大小为16KB，索引树上的一个节点就是一个页。数据库每次读写都是以16KB为单位的
行Row：InnoDB采用行存储方式，数据按照行进行组织和管理，行数据可能有多个格式，比如说COMPACT、REDUNDANT、DYNAMIC等
MySQL8默认的行格式是DYNAMIC，如果超过了页内联存储的限制，则会被存储在溢出页中。show table status like '%article%'查看行格式

.4. 常见存储引擎？选择？InnoDB和MylSAM区别？

InnoDB支持事务、外键、全文索引、B+树索引、哈希索引， 事外全b哈
MyISAM支持全文、B+树索引；MEMORY支持B+树、哈希索引

InnoDB：对事务，并发条件下数据一致性要求高，外键，崩溃恢复，数据操作包括增删改查操作
MyISAM：~要求不高，插入和查询记录操作多，更新和删除操作少，如Web数据仓储
MEMORY：对访问速度要求高，数据量小，内容固定(代码表)、安全性要求低，如临时表，存放查询的中间结果。需要保证数据库异常后能恢复
MERGE：一个对象将MyISAM表以逻辑方式组合在一起。突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，提高访问效率。如数据仓储等VLDB环境
根据业务灵活选择，因为不同表能用不同的存储引擎

MVCC主存锁事外，恢复索行
MyISAM表可以没有主键；InnoDB不设定主键或者非空唯一索引时自动生成一个6字节的隐藏主键
都用.frm文件存储表的定义，MyISAM用.MYD存储数据；.MYI存储索引。InnoDB用.ibd 存储数据和索引
MyISAM只支持表级锁；InnoDB支持行级锁
MyISAM不支持MVCC、事务、外键、数据库异常崩溃后的安全恢复；InnoDB支持
MyISAM的索引为非聚簇索引，数据结构是B树，索引和数据分开存储。InnoDB的索引是聚簇索引，数据结构是B+树，索引和数据不分开存储
MyISAM保存了表的总行数；InnoDB没有

.5. 日志文件及作用？更新语句执行流程？为什么要两阶段提交？binlog和redolog区别？redo log怎么刷入磁盘的？

be sugr
错误日志errorlog：记录运行过程中发生错误时的信息
慢查询日志slowquerylog：记录执行时间超过long_query_time的查询语句
一般查询日志generallog：记录所有对MySQL数据库请求的信息，不建议开启
重做日志redolog：记录对于InnoDB表的每个写操作，用于崩溃恢复(InnoDB独有)
回滚日志undolog：记录数据被修改前的值，用于事务的回滚(InnoDB独有)
二进制日志binlog包括二进制索引文件（.index）、二进制日志文件（.00000*）

# 配置文件my.cnf/.ini
# 默认关闭。开启binlog
# show variables like '%log_bin%'; 查看binlog是否开启
log_bin = mysql-bin

# 单个日志文件最大字节
max_binlog_size=104857600

# 只保留7天的binlog。防止binlog占用过多存储空间
expire_logs_days = 7

# 写操作时数据会先写入操作系统的缓存，再将数据写入磁盘。
# 默认0表示依赖操作系统的磁盘缓存策略
# 1表示每次写操作后都同步到磁盘，数据安全性高，但性能差
sync_binlog=0

范内方时
记录范围：binlog记录所有存储引擎的日志，用于主从复制、恢复和备份。redolog记录InnoDB存储引擎的日志，用于崩溃恢复和保证事务持久性
记录内容：binlog记录DDL和DML语句（除了数据查询语句），是逻辑日志。redolog记录物理数据页的修改操作，是物理日志。
写入方式：binlog是追加写入，所有日志会顺序追加到文件末尾，直到达到文件大小限制后新建一个文件继续记录。redolog是循环写入的，日志空间固定大小，写到文件末尾时会回到文件开头，覆盖旧日志数据
写入时间：binlog在事务提交时写入，只写磁盘一次。而redolog在事务进行的过程中不断写入

mysqlexecuteupdate.jpg
执行器先找引擎获取ID=2这一行。如果这行所在的数据页在内存中，则直接返回给执行器；否则，需要先从磁盘读入内存并返回
执行器拿到引擎给的行数据N，得到新数据N+1，再调用引擎接口写入新数据。引擎将数据更新到内存中，同时将更新操作记录到redolog，此时redolog处于prepare状态。然后通知执行器执行提交事务
执行器生成更新操作的binlog，并把binlog写入磁盘
执行器调用引擎的提交事务接口，引擎把redolog改成提交commit状态，更新完成
redolog写入时有两个阶段的提交，binlog写入之前prepare状态的写入和binlog写入之后commit状态的写入

如果采用“单阶段”提交，会导致原来数据库的状态和被恢复后的数据库的状态不一致
假设ID=2的行c=0，执行update T set c=c+1 where ID=2
先写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，与数据库的值不同

redo log由一定数量的redo log block组成。~
写入机制：事务执行过程中先把日志写到redo log buffer。 然后写到redo log的文件系统缓存page cache(fwrite)，然后再同步到磁盘文件（fsync）

redo log buffer空间不足时：relog buffer日志量超过总容量innodb_log_buffer_size的一半，后台线程会主动写盘fwrite保存在文件系统，但没有调用fsync
innodb_flush_log_at_trx_commit=1时，并行事务提交时顺便将本事务的redo log buffer持久化到磁盘。假设事务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机制：redolog用writepos表示当前记录的位置，checkpoint表示当前要擦除的位置，都是往后移动并且循环的，write pos和checkpoint(cp)之间用来写日志。当write_pos追上checkpoint时，表示redolog日志已经写满需要将buffer中日志页都刷到磁盘释放空间

.6. 索引分类？原理√为什么InnoDB要使用B+树作为索引？不用普通、平衡二叉树、红黑树、B树√？B+树能存储多少条数据呢？Hash、B+树索引，聚簇索引与非聚簇索引区别？回表？覆盖索引？索引下推优化？最左匹配原则？

  • 按功能分
    • 主键索引：表中每行数据唯一的非空索引。一张数据表有只能有一个主键
    • 唯一索引：保证数据列中每行数据的唯一性，允许有空值。
    • 普通索引：基本的索引类型，用于加速查询。允许数据重复和NULL
    • 全文索引：用于检索大文本数据中的信息，避免使用like %…%
    • 外键索引：保证数据的一致性和实现级联操作，但性能较差，生产中建议通过业务代码约束
  • 按数据结构分
    • B+树索引：查询效率O(logN)
    • Hash索引：基于哈希表的索引，查询效率O(1)，只适合=和in查询，不适合范围查询和排序。哈希冲突时通过拉链法来解决。InnoDB使用了自适应哈希索引Adaptive Hash Index, AHI技术，SHOW VARIABLES LIKE 'innodb_adaptive_hash_index'查看
  • 按存储位置分：聚簇索引、非聚簇索引

根据索引特点
索引是一种数据结构，用来加速数据查询操作。通过索引可以快速定位表中的数据行，避免全表扫描
MySQL默认存储引擎为InnoDB，使用B+树作为默认的索引结构。B+树是多路平衡查找树。具有以下特点
查询速度快
B+树实际数据都存储在叶子节点，叶子节点通过指针相互连接形成有序链表。提高了范围查询和排序效率，因为只需顺序访问链表
B+树非叶子节点只存储索引键和子节点的指针。降低了树的高度，减少了磁盘IO次数
B+树节点的大小通常与磁盘页的大小一致，能够在一次磁盘 I/O 操作中读取一个完整的节点。在树的高度较低的情况下，减少磁盘I/O次数，提高了查询速度。
查询性能稳定：B+树所有叶子节点都在同一层，保证了从根节点到任意叶子节点的路径长度是相同的。时间复杂度稳定为O(log n)

如果插入的数据是有序的，那么二叉树会退化成链表，查询复杂度增至O(n)

红黑树/平衡树的节点只能包含2个节点，随着数据增多可能导致树的高度过高，查询性能下降

B树的数据分散在非叶子和叶子节点上，导致范围查询和排序可能需要遍历多个层级和节点，效率不如B+树以及不同查询可能需要通过不同的层级得到数据，导致查询路径不一致，查询稳定性不如B+树

假设主键ID是bigint类型，长度8字节。指针6字节，一共14字节。非叶子节点(一页)可存储16384/14=1170个单元(键值+指针)
一个指针指向一页，一页可以放16条数据，树深度为2时可以存放1170x16=18720条数据
树深度为3的时候，可以存储的数据为1170x1170x16=21902400条记录。
理论B+树的高度一般为2-4层，满足千万级数据的存储。查找数据时一次页的查找代表一次IO，通过主键索引查询只需要2-4次IO

B+树支持范围查询、排序、最左匹配原则，Hash索引不支持。
Hash索引在等值查询上比B+树效率高

聚簇索引的索引结构和数据一起存放。非聚簇索引中索引结构和数据分开存放。
聚簇索引的叶子节点存储索引和索引对应的数据。非聚簇索引的叶子节点存储指向数据行的指针(主键ID)
InnoDB主键使用的是聚簇索引，MyISAM不管是主键索引，还是二级索引使用的都是非聚簇索引。
一个表只能有一个聚簇索引，但可以有多个非聚簇索引。

使用非聚簇索引进行查询时，先找到主键ID再去聚簇索引上找到真正的行数据的过程

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

Index Condition Pushdown ICP在多个索引查询条件下，索引扫描时减少回表，通过过滤不必要的数据的过程，提高查询效率
假设有组合索引A,B,查询语句where A=? and B=?,如果没有索引下推，则每次都会根据A=?回表判断B=?，有索引下推则会根据A=?和B=?索引组合判断减少回表

在使用联合索引时，查询会优先使用索引中从左到右排列的字段，直到遇到无法匹配的字段为止。可以是联合索引的最左N个字段，字符串索引的最左M个字符。例如：用name，age联合索引，name = '张三'或者where name like '张%'

.7. 锁分类？如何解决库存超卖问题？行锁实现？意向锁？

  • 按锁粒度分
    • 表锁：开销小，加锁快；锁定粒度大，发生锁冲突概率高，并发度低，不会死锁
    • 行锁：开销大，加锁慢；锁定粒度小，发生锁冲突概率低，并发度高，会死锁
    • 页锁：开销和加锁速度介于表锁和行锁之间；锁定粒度介于表锁和行锁之间，并发度一般，会死锁
  • 按兼容性分
    • 共享锁（SLock）/读锁（readlock），相互不阻塞。
    • 排他锁（XLock）/写锁（writelock），排它锁是阻塞的
  • 按加锁机制分：乐观锁、悲观锁
# 乐观锁
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;

RecordLock记录锁：唯一索引和主键索引等值查询时锁定匹配的记录
GapLock间隙锁：等值或者范围查询没有匹配到记录时锁定两个记录之间间隙，左开右开区间
Next-keyLock临键锁：记录锁+间隙锁，范围查询并且匹配到记录时同时锁住记录和索引之间的间隙，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。当唯一性索引等值查询匹配到一条记录时临键锁会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁
间隙锁和临键锁都是用来解决幻读问题的，在读已提交隔离级别下都失效

Insert Intention Lock插入意向锁。用于提升并发插入的性能
允许多个事务并发插入记录到不冲突的索引范围中
当一个事务尝试向一个表中插入一条记录时，InnoDB会在插入的位置上设置插入意向锁，表示这个事务打算在某个范围内插入数据。两个事务如果在同一个表的不同索引区间插入数据，它们的插入意向锁可以共存。当插入操作发生时会在插入行上加行锁。如果事务之间在同一位置插入数据，行锁才会引发冲突和等待

意向锁是表级锁，用于支持表锁和行锁的并发操作，避免全表级锁与行锁发生冲突
分意向共享锁（IS）和意向排他锁（IX）。
如果一个事务需要在某个表的行上加行锁时会先申请一个意向排他锁IX，而另一个事务想对同一个表加表锁，如果检查到有意向锁存在，则被阻塞到意向锁释放

.8. MySQL事务特性？怎么保证？并发事务问题？事务隔离级别？默认？实现？MVCC实现原理？会加锁吗？

原子性Atomicity：一个事务的所有操作要么全部提交成功，要么全部失败回滚。使用undo log~
一致性Consistency：一个事务前后数据的完整性必须保持一致。比如银行转账事务中转账前后两个账户的总金额应保持不变。假如A账户100块给B账户10块转10块，不管成功与否，A、B的总金额都是110块.通过原子性、隔离性、持久性和业务代码
隔离性Isolation：一个事务的执行不能被其他事务干扰。使用MVCC(多版本并发控制)机制来处理并发事务实现多种隔离级别
持久性Durability：一个事务提交后对数据的更改就是永久性的，系统崩溃也不会丢失。InnoDB使用redo log记录数据页的物理更改。崩溃时通过redo log恢复未写入的数据，确保数据不丢失

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

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

读未提交：读事务不加锁，写事务加排他锁
读已提交：读事务加共享锁，读取后立即释放（造成不可重复读原因）写事务加排他锁，事务提交才释放
可重复读：读事务加共享锁直到事务结束。写事务加排他锁和间隙锁防止其他事务在范围内插入或删除数据
串行化：读写事务都加排他锁，或者使用MVCC

Multi Version Concurrency Control多版本并发控制，用与解决并发事务问题，提高并发性能（读操作基于快照，不加锁。写操作UPDATE、DELETE、INSERT、SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE会加锁以避免写冲突），保证隔离性。通过undolog版本链和ReadView机制实现
版本链：InnoDB每一行记录都有两个隐藏列
DB_TRX_ID，保存创建这个版本的事务ID
DB_ROLL_PTR，指向undo日志记录的指针，用于事务回滚恢复数据。当一个事务对数据进行修改时，InnoDB会将修改前的数据记录到Undo log中并用undolog指针指向undolog日志记录，当一条记录被多个事务依次修改时，InnoDB会通过undo log记录指针将旧版本数据连接成链表，形成一个“版本链”。这个链表从最新版本指向之前的旧版本。如果事务提交，undolog会在某个时间点被清除。如果事务回滚，InnoDB会根据undolog回滚数据
ReadView 用于确定事务中哪些版本的行记录是可见的。包含以下信息
m_ids：活跃事务的事务id列表。
min_trx_id：活跃事务中最小的事务id，m_ids中的最小值
max_trx_id：分配给下一个事务的事务id
creator_trx_id：表示生成ReadView事务的事务id
访问某条记录时，根据以下规则判断某个版本是否可见
如果被访问版本的DB_TRX_ID属性值
等于creator_trx_id，表明生成该版本的事务是自己修改的，所以该版本可以被访问
小于min_trx_id，~已经提交，~可以~
大于max_trx_id，~还未开启，~不可以~
介于min_trx_id和max_trx_id且m_ids列表中，~还活跃，~不可以~；如果不在，~已经提交，~可以~
如果某个版本的数据不可以被访问，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么查询结果就不包含该记录。
READCOMMITTED每次读取数据前生成一个ReadView
REPEATABLEREAD第一次读取数据时生成一个ReadView

.9. 数据库读写分离？分配怎么实现？主从复制原理了解吗？主从同步延迟原因？处理？百万级别以上的数据如何删除？添加字段？自增id用完怎么办？

读写分离基本原理是将数据库读写操作分散到不同的节点上。基本实现是:
数据库服务器搭建主从集群，一主一从或一主多从。
主机负责写操作，从机只负责读操作。
主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。

程序代码/中间层封装：在代码中抽象一个数据访问层，实现读写操作分离和数据库服务器连接的管理。例如淘宝的TDDL（Taobao Distributed Data Layer）
中间件封装：使用中间件实现读写操作分离和数据库服务器连接的管理。对于业务服务器来说，访问中间件和访问数据库没有区别。

主从复制Master-Slave Replication将数据从主数据库复制到从数据库，用于数据备份和恢复

主服务器上，binlog记录了所有修改数据的语句（如 INSERT、UPDATE、DELETE）
主服务器上的一个线程（二进制日志转储线程）负责读取binlog日志的内容并发送给从服务器
从服务器接收到二进制日志数据后，将数据写入中继日志Relay Log（从服务器上的一个本地存储）
从服务器上有一个SQL线程会读取中继日志并执行，从而将更改应用到从数据库中，完成同步
mysqlslave.jpg

网络延迟、主库写入压力大、从库SQL线程执行效率低（只有一个线程）

写操作后的读操作发给主服务器。注册账号完成后登录的读操作发给主服务器。影响业务代码
读从机失败后再读一次主机。只需对底层数据库访问的API进行封装即可，不影响业务代码，实现代价较小，但如果有很多二次读取，将增加主机的读操作压力。黑客暴力破解账号会导致大量的二次读取操作
关键业务读写操作发给主服务器，非关键业务采用读写分离。对于用户注册+登录的业务读写操作全部访问主机，用户的介绍、爰好、等级等业务采用读写分离

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

通过中间表转换，创建一个临时的新表复制旧表结构，添加字段，再把复制旧表数据，删除旧表，新表改名为旧表，数据可能丢失
用pt-online-schema-change工具在线修改表结构，原理通过中间表
如果一张表数据量大且是热表（读写频繁），先在从库添加再主从切换，再将其他几个节点上添加字段

主键自增值达到上限后不变。继续插入数据时主键冲突
无主键自增row_id 6字节(0到2^48-1)达到上限后会归0重新递增。row_id重复则覆盖数据。InnoDB维护了全局的dict_sys.row_id，无主键的表插入数据是都用dict_sys.row_id值作为row_id，然后把值加1