教材内容
什么是封锁？基本的封锁类型有几种？试述它们的含义。
- 封锁：事务对数据操作前，向系统请求封锁，释放锁前，其他事务不能更新数据；
- 两种：排它锁（写锁，X锁）和共享锁（读锁，S锁）；
- 排它锁：T对A加X锁，只允许T读取修改A，其他事务不能对A加锁、不能读取修改A，直到T释放锁；
- 共享锁：T对A加S锁，T可以读A不能修改A，其他事务只能对A加S锁不能加X锁，直到T释放锁；
什么是活锁？试述活锁产生的原因和解决方法。
- T1封锁A；
- T2请求封锁A，等待；
- T3请求封锁A，T1释放锁后，先批准T3，T2等待；
- T4请求封锁A，T3释放后，批准T4，T2又等待，…可能一直等待；
- 原因：封锁不按先后顺序执行，导致有些事务无限期等待，产生活锁；
- 解决方法：采用先来先服务（FTFS）策略；
什么是死锁？试给出预防死锁的方法
- T1封锁R1，T2封锁R2，T1又请求封锁R2，T2又请求封锁R1;
- 出现T1等T2，T2等T1的局面，两事务永远不结束;
- 防止策略：（1一次封锁法：要求事务一次把所有要用的数据全部加锁2顺序封锁法：对数据对象规定封锁顺序，所有事务按这个顺序封锁）;
试给出检测死锁的一种方法。当发生死锁后，如何解除死锁？
- 超时法：事务等待时间超过规定时限，认为死锁，可能误判；
- 解除方法：选择代价最小的事务，将其撤销，释放它所有锁，让其他事务运行，对撤销事务所执行的数据修改操作进行恢复；
超键、候选键、主键、主属性、非主属性
- 超键：在关系中能唯一标识元组的属性集称为关系模式的超键；
- 候选键：不含有多余属性的超键称为候选键。也就是关系中的一个属性组，其值能唯一标识一个元组。若从属性组中去掉任何一个属性，它就不具有这一性质了，这样的属性组称作候选键；
- 主键：从关系的多个候选键中，选定一个作为主键；
- 主属性：在任何一个候选键中的属性称作主属性；
- 非主属性：不在任何候选键中的属性；
实体完整性和参照完整性
- 实体完整性：关系的主码唯一并且主码的各属性不能取空；
- 参照完整性：外码的值要么取空要么等于被参照关系某个元组的主码值；
为什么外码的属性值可以为空？什么情况下可以为空
- 为空表示该属性值尚未确定;
- 前提是该外码属性不是其所在关系的主属性。例如“学生表”，专业号是外码，不是“学生表”主属性，可以取空；在“选修表”中，课程号是外码，但同时是“选修表”的主属性，不能取空;
数据库如何保证一致性
一致性：数据库只包含成功事务提交的结果时，就处于一致性，否则不一致。
- 数据库层面：通过原子性、隔离性和持续性来保证一致性。即ACID特性中，一致性是目的，原子性、隔离性和持续性都是手段；
- 应用层面：通过代码判断数据库数据是否有效，然后决定回滚还是提交数据；
数据库
关系型数据库和非关系型数据库的区别，各自在什么情况下使用
关系型
采用关系模型来组织数据（即二维表格模型）。
优点
- 易于理解：二维表的结构贴近现实世界，易于理解；
- 使用方便：通用的sql语句使得操作关系型数据库非常方便；
- 易于维护：数据库的ACID属性，大大降低了数据冗余和数据不一致的概率；
缺点
- 海量数据的读写效率低：如果网站的并发量高达每秒上万次的请求，对于关系型数据库来说，硬盘I/O是一个很大的挑战；
- 扩展性差：关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难；
非关系型
对不同于传统的关系数据库的数据库管理系统的统称。
优点
- 高查询性能：基于键值对，数据之间无关系，不需要经过SQL层的解析，且往往存储在内存中，因此查询性能非常高；
- 易扩展：数据之间没有耦合性，所以非常容易水平扩展；
缺点
- 没有强大的事务关系，不能保证数据的完整性和安全性；
- 有一些非关系型数据库没有持久化机制；
选择
目前许多大型互联网都会选用MySql+NoSql的组合方案，因为SQL和NoSql都有各自的优缺点。
关系型数据库适合存储结构化数据，比如：用户的账号、地址：
（1）这些数据通常需要做结构化查询，比如说Join，这个时候，关系型数据库就要胜出一筹。
（2）这些数据的规模、增长的速度通常是可以预期的。
（3）事务性、一致性，适合存储比较复杂的数据。
NoSql适合存储非结构化数据，比如：文章、评论：
（1）这些数据通常用于模糊处理，例如全文搜索、机器学习，适合存储较为简单的数据。
（2）这些数据是海量的，并且增长的速度是难以预期的。
（3）按照key获取数据效率很高，但是对于join或其他结构化查询的支持就比较差。
关系型数据库在海量数据场景面临的挑战
事务：关系模型要求多个SQL操作满足ACID特性，所有的SQL操作要么全部成功，要么全部失败。在分布式系统中，如果多个操作属于不同的服务器，保证它们的原子性需要用到两阶段提交协议，而这个协议的性能很低，且不能容忍服务器故障，很难应用在海量数据场景。
联表：传统的数据库设计时需要满足范式要求，例如，第三范式要求在一个关系中不能出现在其他关系中已包含的非主键信息。假设存在一个部门信息表，其中每个部门有部门编号、部门名称、部门简介等信息，那么在员工信息表中列出部门编号后就不能加入部门名称、部门简介等部门有关的信息，否则就会有大量的数据冗余。而在海量数据的场景，为了避免数据库多表关联操作，往往会使用数据冗余等违反数据库范式的手段。实践表明，这些手段带来的收益远高于成本。
性能：关系数据库采用B+树存储引擎，更新操作性能不如LSM树这样的存储引擎。另外，如果只有基于主键的增、删、查、改操作，关系数据库的性能也不如专门定制的Key-Value存储系统。
数据库优化
数据库结构优化
- 可适当违反第三范式，减少join操作；
- 设计一些中间表；
- 对表进行垂直拆分：常用字段单独一个表，不常用字段单独一个表，大字段单独一个表；
- 对表进行水平拆分：将一个表中的记录拆分到多个结构相同的表中；
- 主键尽量自增；
参考链接.
SQL语句优化
- 查询SQL尽量不要使用select *，而是select具体字段；
- 如果知道查询结果只有一条记录，建议用limit 1，因为加上limit 1后，只要找到了对应的一条记录，就不会继续向下扫描了，效率将会大大提高；
- 避免索引失效；
- 使用explain分析SQL语句，看是不是走索引；
select语句书写顺序
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT
select语句执行顺序
FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT > LIMIT
解释一下什么是池化设计思想。什么是数据库连接池?为什么需要数据库连接池?
- 池化设计即初始预设资源，解决的问题就是每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好比去食堂打饭，打饭的大妈会先把饭盛好几份放那里，你来了就直接拿着饭盒加菜即可，不用再临时打饭，效率就高了。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等。这些特征可以直接映射到Java线程池和数据库连接池的成员属性中；
- 数据库连接池：数据库连接本质就是⼀个socket连接。数据库服务端还要维护⼀些缓存和用户权限信息等，因此占用了⼀些内存。为用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。有了数据库连接池，创建连接后，就将其放在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立⼀个新连接并将其添加到池中；
1/2/3NF
为了在数据库设计中更好地解决数据冗余和数据有效性检查，提高存储效率，提出了范式的概念。
1NF
概念：每一个分量必须是不可分的数据项（即不能以集合、序列等作为属性）；
举例
它就不满足1NF，因为{C1，C2，C3}和{C1，C4}是集合。
修改为符合1NF：
2NF
概念：在1NF基础上，消除非主属性对键的部分依赖；
举例，对于Student表：
（学生编号、课程编号）是键，对于“学生姓名”、“班级编号”、“院系”这三个非主属性来说，可以直接通过“学生编号”来确定，“课程编号”显得多余，即“学生姓名”、“班级编号”、“院系”对（学生编号、课程编号）部分函数依赖。把Student表进行拆分，可以消除部分依赖。
其中，学生表Student如下：
学生-课程表如下：
3NF
概念：在2NF基础上，消除非主属性对键的传递依赖；
根据上面对传递依赖的分析，对于Student表，学生编号可以唯一确定他所在的院系，但是注意到这中间存在传递过程，即学生编号唯一确定该学生所对应的班级编号，班级编号对应唯一的院系。我们称，院系对学生编号传递函数依赖。
把Student表继续进行拆分，可以消除传递依赖。
其中，学生表Student如下：
班级-院系表如下：
inner join、outer join 和 full join
- inner join（内连接）：返回两表中连接字段相等的记录；
- outer join（外连接）
- left join（左外连接）
左表的所有数据都显示出来，右表数据只显示共有的那部分，不共有的部分只能填空显示。 - right join（右外连接）
右表的所有数据都显示出来，左表数据只显示共有的那部分，不共有的部分只能填空显示。
- left join（左外连接）
- full join（全连接）：返回两表所有数据，但是去除两表的重复数据。
SQL注入攻击
攻击者在HTTP请求中注入恶意的SQL代码，服务器使用参数构建数据库SQL命令时，恶意SQL被一起构造，并在数据库中执行。例如用户登录：输入用户名 lisan，密码 ‘ or ‘1’=’1 ，如果此时使用参数构造的方式，就会出现select * from user where name = ‘lisan’ and password = ‘’ or ‘1’=‘1’，不管用户名和密码是什么内容，上述查询语句是永远可以查询出结果的。
如何防范SQL注入攻击使用预编译的PrepareStatement是必须的，但是一般我们会从两个方面同时入手：
- Web端：1 有效性检验； 2 限制字符串输入的长度；
- 服务端：1 不用拼接SQL字符串，使用预编译的PrepareStatement；2 有效性检验。(为什么服务端还要做有效性检验？第一准则，外部都是不可信的，防止攻击者绕过Web端请求)；3 过滤SQL需要的参数中的特殊字符。比如单引号、双引号；
MySQL
MySQL是如何执行一条SQL的？具体步骤有哪些？
服务层按顺序执行SQL的步骤为：
- 客户端请求；
- 连接器（验证用户身份，给予权限）；
- 查询缓存（存在缓存则直接返回，不存在则执行后续操作）；
- 分析器（对SQL进行词法分析和语法分析操作）；
- 优化器（选择它认为最优的执行方案）；
- 执行器（执行前先看用户是否有执行权限，如果没有返回错误信息，如果有就去调用存储引擎接口）；
- 去存储引擎层获取数据返回（如果开启查询缓存还会缓存查询结果）；
MySQL的内部构造一般分哪两个部分？
分服务层和存储引擎层。
- 服务层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一个通用的日志模块binlog日志模块；
- 存储引擎层：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB 引擎有自有的日志模块redo log模块。现在最常用的存储引擎是InnoDB，它从 MySQL 5.5 版本开始就被当作默认存储引擎了；
binlog、redo logo、undo log的区别
MySQL可划分为服务层和存储引擎层。binlog是在服务层实现的，redo log、undo log是在存储引擎层实现的，且是InnoDB引擎独有的；
- binlog：即归档日志，记录的是对整个MySQL数据库的操作内容，包括执行的DDL、DML，对所有的引擎都适用，用来进行主从复制和数据恢复。依靠binlog没有crash-safe能力(crash-safe能力即如果数据库发生崩溃重启，之前提交的记录都不会丢失)；
- redo log：即重做日志，记录的是数据修改之后的值，如果一条数据已提交成功，不会立即同步到磁盘，而是先记录到redo log中，等待合适的时机再刷盘。在数据库发生崩溃重启的时候，可以根据redo log日志进行崩溃恢复，也就达到了crash-safe；
- undo log：即回滚日志，记录的是当前操作中的相反操作，一条insert语句在undo log中会对应一条delete语句，update语句会在undo log中对应相反的update语句，在事务回滚时会用到undo log，实现事务的原子性；
redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑；
binlog可以作为数据恢复和主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用；
binlog 日志只用于归档，但仅仅依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB 特有的，且日志记录落盘(将数据写入到磁盘)后会被覆盖掉。因此需要 binlog 和 redo log 二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。
MySQL更新语句分析
UPDATE tb_student SET age='19'
WHERE name='张三';
更新语句和查询语句类似，只不过执行更新的时候要记录日志。MySQL自带的日志模块为binlog(归档日志），所有的存储引擎都可以使用，InnoDB引擎还自带了一个日志模块redo log(重做日志），下面以InnoDB模式来探讨语句的执行流程。流程如下：
- 查询到“张三”这一条数据，把age改为19，然后调用引擎API接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo log；
- 此时redo log进入prepare状态，然后告诉执行器，执行完成了，随时可以提交；
- 执行器收到通知后记录binlog，然后提交redo log为commit状态；
- 更新完成；
问：为什么要用两个日志模块，用一个日志模块不行吗?
这是因为最开始 MySQL 并没有 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致不具备 crash-safe 的能力(crash-safe即数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。
并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里用反证法来说明下为什么要这么做？
- 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
- 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。
如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：
- 如果redo log里面的事务是完整的，也就是说已经有了commit标识，则直接提交；
- 如果redo log里面的事务只有完整的prepare，则判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务；
这样就解决了数据一致性的问题。
MySQL中为什么要有事务回滚机制
在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再对数据库进行写入。当事务已经提交，就无法再回滚了；
回滚日志作用：
能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息。在系统发生崩溃或数据库进程直接被杀死后，当用户再次启动数据库进程时，能够通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因；
InnoDB和MyISAM的区别
- 都是数据库存储引擎，存储数据和提供读写接口，InnoDB是MySQL默认的存储引擎；
- 事务和崩溃后的安全恢复：InnoDB支持事务，具有事务、回滚和崩溃修复能力的事务安全型表；MyISAM不支持事务，崩溃后无法安全恢复；
- 是否支持MVCC：InnoDB支持，MyISAM不支持。应对高并发事务，MVCC比单纯的加锁更高效；
- 锁粒度：InnoDB既支持表锁，又支持行锁，默认是行锁；MyISAM只支持表锁；
- 索引结构：InnoDB聚簇索引，MyISAM非聚簇索引；
- 主键：InnoDB的表必须有主键，如果没有设定主键，会自动生成一个主键，MyISAM允许没有主键的表存在；
- 外键：InnoDB支持，MyISAM不支持；
数据库锁的粒度
三种：
| 锁类别 | 资源开销 | 加锁速度 | 是否会出现死锁 | 锁粒度 | 并发度 |
|---|---|---|---|---|---|
| 表级锁 | 小 | 快 | 不会 | 大 | 低 |
| 行级锁 | 大 | 慢 | 会 | 小 | 高 |
| 页面锁 | 一般 | 一般 | 不会 | 一般 | 一般 |
InnoDB默认采用行级锁，MyISAM默认采用表级锁。
MySQL中InnoDB引擎的行锁模式及其是怎么实现的？
行锁模式
在存在行锁和表锁的情况下，一事务想对某个表加X锁，需要先检查是否有其他事务对这个表加了锁或对这个表的某一行加了锁。对表的每一行都检测一次效率非常低，为了解决这个问题，实现多粒度锁机制，InnoDB引擎提供了两种内部使用的意向锁，这两种意向锁都是表锁。
- 意向共享锁：即IS锁，一事务给数据行加共享锁前必须先获得该表的IS锁；
- 意向排他锁：即IX锁，一事务给数据行加排他锁前必须先获得该表的IX锁；
有了意向锁，一事务想对某个表加X锁，只需检查是否有其他事务对这个表加了X/IX/S/IS锁即可。
锁的兼容性如下：
InnoDB行锁实现方式：InnoDB的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录进行加锁。
InnoDB行锁的特性：如果不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，实际产生的效果和表锁一样。
InnoDB行锁分三种情况：
- Record Lock：对索引项加锁；
- Grap Lock：对索引之间的“间隙”、第一条记录前的“间隙”或最后一条后的间隙加锁；
- Next-Key Lock：前两种方式的组合，对记录及间隙加锁；
MVCC解决幻读问题
在可重复读隔离级别下，使用MVCC+Next-Key Lock可以解决幻读问题。
MySQL字符集及校对规则
- 字符集指的是⼀种从二进制编码到某类字符符号的映射，校对规则是在字符集内用于比较字符的一套规则；
- MySQL采用类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，将会采用默认字符集）；
char和varchar
- char长度固定，当插入的字符超过长度时，严格模式下会拒绝插入并提示错误信息，一般模式下会截取后插入；当插入的字符小于长度时，用空格填充；varchar长度可变，根据传入的字符分配长度；char存取速度比varchar快得多；
- char的存储方式是：1个英文字符占1个字节，1个中文字符占2个字节；varchar的存储方式是：1个英文字符或1个中文字符都占2个字节；
- char最大容量为255个字节，varchar最大容量为65535字节；
- 当某个字段的数据长度为定长时，选择char，例如性别、生日等；当某个字段的数据长度不确定时，选择varchar，例如姓名、简介等；
delete、truncate和drop
- delete可以删除表中全部数据，也可以加where子句部分删除，再插入时自增id延续删除前；
- truncate删除表中全部数据，再插入时自增id又从1开始 ;
- drop直接删掉表;
- delete是DML，操作时原数据会被放到rollback segement中，可以回滚；truncate、drop是DDL，操作立即生效，原数据不会放到rollback segment中，不能回滚；
- 应用场景：想删除部分数据行时用delete；保留表而删除所有数据时用truncate；不再需要一张表时用drop；
UNION和UNION ALL
UNION和UINON ALL都是将两个结果集合并到一起。
- UNION会对结果去重并排序，UNION ALL直接返回合并后的结果，不去重也不进行排序；
- UINON ALL的性能比UNION要好；
临时表
MySQL在执行SQL语句时会创建一些存储中间结果集的表，这种表被称为临时表，临时表只对当前连接可见，在连接关闭后，临时表会被删除并释放空间。
临时表分内存临时表和磁盘临时表两种。MEMORY使用的是内存临时表，MyISAM和InnoDB使用的是磁盘临时表。
用到临时表的情况：
- FROM中的子查询；
- DISTINCT查询加上ORDER BY；
- ORDER BY和GROUP BY的子句不一样时会产生临时表；
- 使用UNION会产生临时表；
慢日志查询
慢日志查询一般用于记录执行时间超过某个临界值的SQL语句的日志。
相关参数：
- slow_query_log：是否开启慢日志查询，1表示开启，0表示关闭；
- slow_query_log_file：慢查询日志存储路径；
- long_query_time：慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上；
- log_queries_not_using_indexes：未使用索引的查询会被记录到慢查询日志中；
- log_output：日志存储方式。“FILE”表示将日志存入文件；“TABLE”表示将日志存入数据库；
如何对慢查询进行优化？
- 分析语句的执行计划，查看SQL语句的索引是否命中；
- 优化数据库结构，将字段很多的表分解成多个表，或者考虑建立中间表；
- 优化LIMIT分页；
数据库备份方式和备份策略
1. 备份方式
- 热备份：当数据库进行备份时，数据库的读写操作均不受影响；
- 温备份：当数据库进行备份时，数据库的读操作可以进行，但不能进行写操作；
- 冷备份：当数据库进行备份时，数据库不能进行读写操作；
2. 备份策略
-
1 直接复制数据库文件
针对数据量较小的场景。
-
2 mysqldump + binlog
针对数据量适中的场景。mysqldump 对数据库进行完全备份，定期备份 binlog 达到增量备份的效果。
mysqldump 实际上就是将表结构和数据存储在文本文件中，原理：先根据表结构生成 CREATE 语句，然后再将数据转换为 INSERT 语句。
-
3 ivm2 快照 + binlog
(注：binlog 即二进制日志，记录对数据发生或者潜在发生更改的 SQL 语句，以二进制形式保存在文件中。)
InnoDB为什么使用自增主键
- 如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，会自动开辟一个新的页；
- 如果使用非自增主键（如身份证号），由于每次插入主键的值过于随机，因此新记录很有可能被插到现有索引页的某个中间位置而移动数据，增加了很多开销，而且频繁的移动、分页操作造成了大量的碎片，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面；
事务
试述事务的概念及事务的4个特性
- 概念：数据库操作序列，要么全做要么全不做，不可分割的工作单位；
- 特性：原子性一致性隔离性持续性，简称ACID特性；
- 原子性：事务中各项操作要么全做要么全不做；
- 一致性：数据库只包含成功事务提交的结果时，就处于一致性，否则不一致；
- 隔离性：一个事务的执行不被其他事务干扰；
- 持续性：即永久性，事务一旦提交，对数据库数据的改变是永久性的；
并发操作可能会产生哪几类数据不一致
- 丢失修改：T1修改一数据，T2随后修改，T2覆盖T1，T1的修改丢失；
- 读“脏”数据：T1修改一数据，T2随后读取，T1因故撤销修改，T2读取的就是脏数据；
- 不可重复读：T1读取一数据，T2修改数据，T1再次读取这个数据，读取结果和第一次读取结果不同；
- 幻读：T1 读取某个范围的数据，T2 在这个范围内更新数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同；
不可重复读和幻读区别是什么？
不可重复读的重点是修改，幻读的重点在于新增或者删除。
- 不可重复读：同样的条件, 你读取过的数据, 再次读取出来发现值不一样；
- 幻读：同样的条件, 第1次和第2次读出来的记录数不一样；
事务隔离级别
-
数据库产生并发不一致问题主要是因为破坏了事务的隔离性 ，可以通过并发控制（如封锁机制）来保证隔离性，但并发度低代价太大，数据库管理系统提供了事务隔离级别（4种）来提高并发度，可根据不同的场景进行选择：
-
未提交读：事务中的修改，即使没有提交，对其它事务也是可见的；
-
提交读：一事务只能读取已提交的事务所做的修改；
-
可重复读：保证在同一事务中多次读取同样数据的结果是一样的；
-
可串行化：强制事务串行执行，加锁实现；
MySQL的默认隔离级别是可重复读。
事务并发访问引起的问题及使用哪种事务隔离级别避免
MVCC
MVCC（Multi-Version Concurrency Control，多版本并发控制）主要用来提高数据库的并发性。
MVCC的当前读和快照读
当前读：读取的是最新提交的数据，读取时要保证其他事务不会修改当前记录，所以会对读取的记录加锁；
快照读：读取的是小于等于此事务的数据，不加锁，可以避免加锁带来的性能损耗；
MVCC的实现原理
版本号
- 系统版本号：是一个自增ID，每开启一个事务，系统版本号都会递增；
- 事务版本号：即事务开始时的系统版本号，可以通过事务版本号的大小判断事务的时间顺序；
行记录隐藏的列
- DB_ROW_ID：所需空间6byte，隐含的自增ID，用来生成聚簇索引，如果数据表没有指定聚簇索引，InnoDB会利用这个隐藏ID创建聚簇索引；
- DB_TRX_ID：所需空间6byte，最近修改的事务ID，记录创建这条记录或最后一次修改这条记录的事务ID；
- DB_ROLL_PTR：所需空间7byte，回滚指针，指向这条记录的上一个版本；
undo日志
MVCC使用到的快照会存储在undo日志中，该日志通过回滚指针将一个一个数据行的所有快照连接起来。
举例说明
- 某条记录最开始的样子：
- 现在来了一个事务1对它的年龄字段进行了修改，变成了这样：
- 现在又来了一个事务2对它的性别进行了修改，又变成了这样：
从上面的分析可以看出，事务对同一记录的修改，记录的各个版本会在undo日志中连接成一个线性表，表头就是最新的旧纪录。
在可重复读的隔离级别下，InnoDB的工作流程：
- SELECT
作为查询的结果要满足两个条件：
- 当前事务所要查询的数据行快照的创建版本号必须是小于当前事务的版本号，这样做的目的是保证当前事务读取的数据行的快照要么是在当前事务开始前就已经存在的，要么就是当前事务自身插入或者修改过的；
- 当前事务所要读取的数据行快照的删除版本号必须是大于当前事务的版本号，如果是小于等于的话，表示该数据行快照已经被删除，不能读取；
- INSERT
将当前系统版本号作为数据行快照的创建版本号。
- DELETE
将当前系统版本号作为数据行快照的删除版本号。
- UPDATE
保存当前系统版本号为更新前的数据行快照的删除版本号，并保存当前系统版本号为更新后的数据行快照的创建版本号，即先删除再更新。
总结
MVCC的作用就是在避免加锁的情况下最大限度解决读写并发冲突的问题，它可以实现提交读和可重复读两个隔离级别。
MySQL 中事务隔离级别的实现
事务的隔离机制主要是依靠锁机制和MVCC（多版本并发控制）实现的，提交读和可重复读可以通过MVCC实现，串行化可以通过锁机制实现。
- 未提交读：总是读取最新的数据；
- 提交读：使用 MVCC的当前读，读取的是最新提交的数据，有可能会出现一个事务中两次读到了不同的结果；
- 可重复读：使用MVCC的快照读，读取的是小于等于此事务的数据，也就实现了可重复读；
- 可串行化：读加共享锁（S），写加排他锁（X），读写互斥；
表优化
大表优化
- 当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：1 限定数据的范围 2 读写分离 3 垂直拆分 4 水平拆分；
- 限定数据的范围：禁止不带任何限制数据范围条件的查询语句；
- 读写分离：经典的数据库拆分方案，主库负责写，从库负责读；
- 垂直拆分：指数据表列的拆分，把⼀张列比较多的表拆分为多张表。优点：简化表结构，易于维护，在查询时减少读取的Block数，减少I/O次数；缺点：主键会出现冗余，会引起Join操作，会让事务变得更复杂；
- 水平拆分：水平拆分是指数据表行的拆分，保持数据表结构不变，通过某种策略存储数据分片；每个数据分片分散到不同的表或者库中，达到了分布式的目的；
分库分表
为了解决由于数据量过大而导致数据库性能降低的问题，将单一数据库拆分成若干数据库，将单一数据表拆分成若干数据表，从而提升数据库性能；
分表
可以减少数据库的单表负担，将压力分散到不同表上，提高查询性能和并发度。
- 水平分表：把一个表的数据按照一定规则拆分到多个表中；
- 垂直分表：把一个表按照字段拆分成多个表。比如常用字段单独一个表，不常用字段单独一个表，大字段单独一个表；
分库
库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘IO、网络带宽等。因此还需要分库，将分好的数据库放在不同服务器上。
分库分表之后，id 主键如何处理
如果分成多个表之后，每个表的id都是从1开始累加，这是不对的，我们需要⼀个全局唯⼀的 id 来支持。生成全局 id 有下面这几种方式：
- UUID（通用唯一识别码）：不适合作为主键，因为太长了，并且无序不可读，查询效率低；
- 每台机器设置不同的初始值，且步长和机器数相等。缺点：1 系统水平扩展比较困难，比如定义好了机器台数和步长之后，如果要添加机器会比较困难；2 id没有了单调递增（1，2，3，4）的特性，只能趋势递增（1，100，2，3，101）；
- 利用 Redis 生成 id : 通过Redis的INCR/INCRBY自增原子操作命令，能保证生成的id肯定是唯一有序的。优点：性能比较好，灵活方便，不依赖于数据库；缺点：引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本；
- 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯⼀性、趋势递增、单调递增、信息安全；
分库分表带来的分布式困境及应对之策
- 数据迁移与扩容问题：先通过程序读出数据，再按照指定的分表策略将数据写入到各个分表中；
- 分页与排序问题：先在不同的分表中将数据排序并返回，再将不同分表返回的结果集进行汇总和再次排序，最后返回给用户；
索引
索引
- 索引是对数据库表中一列或多列的值进行排序的结构，好比一本书的目录，可以提高查询效率，但同时耗费了数据库存储空间，在插入和修改时也需花费更多的时间（因为索引也要随之变动）；索引使用的数据结构主要有B+树索引和HASH索引 。HASH索引的底层数据结构就是HASH表，因此在绝大多数需求为单条记录查询的时候，可以选择HASH索引，查询性能最好；其余大部分场景，建议选择B+树索引。
- 聚簇索引：索引项顺序与表记录的物理顺序一致，叶子结点存储索引字段值和真实的数据行；
- 非聚簇索引：索引项顺序与表记录的物理顺序不一致，叶子结点存储索引字段值和数据页数据行的地址。
- 唯一索引：不允许具有索引值相同的行，索引值可以为NULL但不能有多个NULL；
- 主键索引：唯一索引的特殊类型，主键作为索引(默认)；
- 组合索引：由多个列组成的索引；
- 全文索引：对文本内容进行搜索的索引；
索引的优缺点
优点：将随机I/O变成顺序I/O（因为B+树的叶子节点是连接在一起的），加快了数据检索的速度；
缺点：1 空间角度：建立索引需要占用物理空间；2 时间角度：创建和维护索引都需要花费时间（对数据进行增删改的时候都需要维护索引）；
主键索引（聚簇索引）、辅助索引（普通索引）、覆盖索引
- 主键索引（聚簇索引）：索引项顺序与表记录的物理顺序一致，叶子结点存储真实的数据行，不再有另外单独的数据页；
- 辅助索引（普通索引，二级索引）：非主键索引，叶子节点=键值+对应主键值。（问：辅助索引的叶子结点存放了主键ID，查询的时候一定要回表查询吗？不一定，当查询的字段刚好是索引的字段或者索引的一部分，就可以不用回表，这也是覆盖索引的原理）；
- 覆盖索引：查询的数据列只用从索引中就能够取得，即查询列被所使用的索引覆盖。通过建立联合索引可以很好地利用覆盖索引从而避免回表；
使用索引要注意什么？
- 对于中大型表建立索引非常有效，对于非常小的表，反而全表扫描更快一些；
- 对于超大型的表，建立和维护索引的代价会很高，可以考虑分区技术；
- 如果表的增删改非常多，而查询需求非常少的话，就没有必要建索引了，因为维护索引也需要付出代价；
- 多个字段经常被查询的话可以考虑联合索引；
- 字段多且字段值唯一的字段可以考虑建立唯一索引；
- 字段多且有重复时考虑建立普通索引；
创建索引要注意什么
- 选择经常被查询的字段：where后出现的列，而非select后出现的列；
- 字段值越小越好：字段越小查询时IO次数越少，效率越高；
- 选择离散程度高的字段：可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多，即字段的离散程度越高；
- 选择非NULL字段：在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL；
- 总之：经常被查询、字段值小、离散程度高、不为空的字段适合建索引；
MySQL中InnoDB和MyISAM使用索引的区别
MySQL的索引使用的数据结构默认是B+树，但对于InnoDB和MyISAM这两种存储引擎的实现方式是不同的：
- InnoDB：1 主键索引叶节点data域保存了完整的数据记录，即聚簇索引；其余索引作为辅助索引，辅助索引的data域存储相应记录主键的值。2 在根据主键索引查找时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，需要先取出主键的值，再回表走⼀遍主键索引；
- MyISAM：主键索引和辅助索引都是非聚簇索引，叶节点data域存放的是数据记录的地址。在根据索引查找时，找到key所在的节点，取出其data域的值，然后以data域的值作为地址读取相应的数据记录；
B树
- 一个结点存放多个键值对，键升序排列，左小于根小于右;
- 对M阶B树：根结点至少两个子结点，每个结点最多M个子结点，除根节点和叶子节点外其他节点至少有 M/2 个子结点，所有叶子节点都在同一层；
- B树的阶数一般比较大，即使存储大量数据，高度依然很小;
- 磁盘I/O代价主要花费在查找上，而查找的次数由树的高度决定，在大规模数据存储时，平衡二叉树往往由于树的高度过大而造成磁盘I/O过于频繁，导致效率低下，因此采用B树进行存储；
- 文件系统设计者将B树一个结点的大小设为等于一个页（1024字节或其整数倍），这样只需一次I/O就可以完全载入一个结点（磁盘预读原理）;
- 3层B树可以容纳102410241024差不多10亿个数据，假定操作系统一次读取一个结点，那么B树在10亿个数据中查找目标值，只需小于3次磁盘读取就可以找到，大大提高了I/O的效率；
B+树
- B树的一种变形;
- 不同点：（1）非叶子结点仅具有索引作用，只存储key，不存储value；（2）所有叶子结点构成一个按key排序的有序链表；
- 为什么数据库索引选择B+树而不是B树：（1）B+树高度更小：相较于B树B+树每个叶子结点存储的关键字数更多，所以树高更小查询更快；（2）B+树查询速度更稳定：B+树所有数据都存在叶子结点上，所有关键字查询的路径长度相同，因此查询速度更加稳定；（3）B+树天然具备排序功能：B+树所有叶子结点构成一个有序链表，因此区间查询效率高，而在数据库中基于区间的查询是非常频繁的；
HASH索引
- 底层数据结构是哈希表，采用哈希算法把键值换算成哈希值，以O(1)时间查找，速度快但失去了有序性；
- 在绝大多数需求为单条记录查询时，选择哈希索引查询性能最好，否则选择B+树索引；
- 不足：（1）无法用于排序和分组；（2）不支持范围查找；（3）不支持模糊查询及多列索引的最左匹配（3）存在哈希冲突，查询性能不稳定，而且如果遇到大量哈希冲突的情况查询效率会大大降低；
MySQL索引的类型有哪些？
MySQL主要的索引类型主要有FULLTEXT，HASH，BTREE，RTREE。
-
FULLTEXT
即全文索引，一般用于查找文本中的关键字，而不是直接比较是否相等，多在char、varchar、text等数据类型上创建全文索引。全文索引主要用来解决WHERE name LIKE "%zhang%"等针对文本的模糊查询效率低的问题。
-
HASH
即哈希索引，多用于等值查询，时间复杂度为O(1)查询效率高，但不支持排序、范围查询及模糊查询等。
-
BTREE
即B+树索引，InnoDB存储引擎默认的索引，支持排序、分组、范围查询、模糊查询等，并且性能稳定。
-
RTREE
即空间数据索引，多用于地理数据的存储，相比于其他索引，空间数据索引的优势在于范围查找。
如何创建/删除索引
创建索引
- 使用CREATE INDEX语句
CREATE INDEX index_name ON table_name(column_list);
- 在CREATE TABLE时创建
CREATE TABLE user (
id INT PRIMARY KEY,
information TEXT,
FULLTEXT (information)
);
- 使用ALTER TABLE创建
ALTER TABLE table_name ADD INDEX index_name (column_list);
删除索引
- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;
- 删除其他索引
ALTER TABLE table_name DROP INDEX index_name;
什么是前缀索引
定义：前缀索引是指对文本或字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。
使用场景：前缀的区分度比较高的情况。
建立方式：
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
prefix_length即前缀长度，可以使用以下方法来确定：
- 先计算全列的区分度
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
- 然后不断调整prefix_length，直到和全列的区分度相近：
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
最左匹配原则
定义：对于组合索引，从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。
例如建立索引(a,b,c)：
第一种
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM table_name WHERE b = 2 AND a = 1 AND c = 3;
上面两次查询过程中a、b、c都用到了索引，WHERE后面字段调换不会影响查询结果，因为MySQL中的优化器会自动优化查询顺序。
第二种
SELECT * FROM table_name WHERE a = 1;
SELECT * FROM table_name WHERE a = 1 AND b = 2;
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3;
三个查询语句都用到了索引，因为三个语句都是从最左开始匹配的。
第三种
SELECT * FROM table_name WHERE b = 2;
SELECT * FROM table_name WHERE b = 2 AND c = 3;
两个查询语句都没有用到索引，因为不是从最左边开始匹配的。
第四种
SELECT * FROM table_name WHERE a = 1 AND c = 3;
这个查询语句只有a列用到了索引，c列没有用到索引，因为中间跳过了b列，不是从最左开始连续匹配的。
第五种
SELECT * FROM table_name WHERE a = 1 AND b < 2 AND c < 3;
这个查询语句只有a列和b列用到了索引，c列没有用到索引，因为根据最左匹配原则，遇到范围查询会停止。
第六种
SELECT * FROM table_name WHERE a LIKE 'ab%';
SELECT * FROM table_name WHERE a LIKE '%ab';
SELECT * FROM table_name WHERE a LIKE '%ab%';
只有第一个语句使用到了索引。因为对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。
索引在什么情况下会失效
- 不符合最左匹配原则的组合索引会失效；
- 对索引字段使用函数或进行表达式操作；
- 对索引字段使用 != / not in / <> 等否定操作符；
- 对索引字段使用is null / is not null；
- 对索引字段使用or进行连接；
- 对索引字段使用like查询时以%开头；
1640
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
