本文详尽探讨了MySQL事务的ACID特性、并发操作导致的数据不一致问题，如幻读、不可重复读等，以及事务隔离级别的原理与应用。深入解析了InnoDB引擎的MVCC、锁机制及undolog，指导如何避免幻读现象。此外，还对比了InnoDB与MyISAM，讨论了char与varchar的使用场景，并阐述了数据库优化策略与分库分表实践。
目录
- 事务
- 索引
- 数据库
- MySQL
- 数据库优化
- 教材内容
事务
事务的概念及事务4个特性
- 概念：数据库操作序列，要么全做要么全不做，不可分割的工作单位；
- 特性：原子性一致性隔离性持续性，简称ACID特性；
- 原子性：事务中各项操作要么全做要么全不做；
- 一致性：要求事务必须使数据库从一个一致性状态转变到另一个一致性状态。即事务开始前和结束后，数据库的完整性约束（主键约束、外键约束、检查约束等）没有被破坏；以及事务在完成后，数据间关系和数据准确性要得到保证，如银行转账事务，要求两账户的余额变化必须一致，一个账户减少的金额必须等于另一个账户增加的金额；
- 隔离性：确保并发执行的事务不会互相干扰，防止了多个事务同时访问相同数据时可能出现的问题，如丢失修改、脏读、不可重复读和幻读等；
- 持续性：即永久性，事务一旦提交，对数据库数据的更改是永久性的。即使在发生系统故障的情况下，这些更改也不会丢失；
数据库并发操作可能会导致的几类数据不一致现象
- 丢失修改：T1修改一数据，T2随后修改，T2覆盖T1，T1的修改丢失；
- 脏读：T1修改一数据，T2随后读取，T1因故撤销修改，T2读取的就是脏数据；
- 不可重复读：T1读取一数据，T2修改数据，T1再次读取这个数据，读取结果和第一次读取结果不同；
- 幻读：T1 读取某范围数据，T2 在此范围内更新数据，T1 再次读取此范围数据，此时读取结果和第一次读取结果不同；
*不可重复读和幻读的区别
不可重复读关注修改，幻读关注新增或者删除。
- 不可重复读：同样的条件, 读取过的数据, 再次读取出来发现值不一样；
- 幻读：同样的条件, 读取过的数据, 再次读取出来发现记录数不一样；
事务隔离级别及其对应的并发不一致问题避免
- 数据库并发不一致问题主要因为破坏了事务的隔离性 ，可通过并发控制（如封锁机制）来保证隔离性，但并发度低代价太大，DBMS提供了事务隔离级别（4种）来提高并发度，可根据不同场景进行选择：
- 未提交读：事务中的修改，即使没有提交，对其它事务也是可见的。存在3个常见问题（脏读、不可重复读、幻读）；
- 已提交读：一事务只能读取已提交的事务所做的修改。避免脏读，存在2个问题（不可重复读、幻读）
- 可重复读：保证在同一事务中多次读取同样数据的结果是一样的。避免脏读和不可重复读，存在1个问题（幻读）
- 串行化：强制事务串行执行，加锁实现。避免脏读、不可重复读、幻读；
MySQL 事务隔离级别
MySQL事务隔离机制的提交读和可重复读通过MVCC实现，串行化通过锁机制实现。
- 未提交读(read uncommitted)：读取最新的数据。会有脏读、不可重复读、幻读；
- 已提交读(read committed)：读取最新提交的数据。避免脏读，会有不可重复读、幻读；
- 可重复读(repeatable read)：，读取某历史版本的数据。避免脏读、不可重复读、幻读；
- 串行化(serializable)：强制事务串行执行，加锁实现。避免脏读、不可重复读、幻读；
MySQL的默认隔离级别是可重复读。
如何查看、设置MySQL 事务隔离级别
- 查看系统隔离级别：select @@global.tx_isolation;
- 查看会话隔离级别：select @@transaction_isolation;
- 设置系统隔离级别：set global transaction isolation level repeatable read;
- 设置会话隔离级别：set session transaction isolation level repeatable read;
如何选择事务隔离级别
- 上松下严：数据库相对严格一些，上游业务尽量适配下游数据库事务隔离级别；
- 当Spring开启了事务并设置了传播机制，就会覆盖Mysql已有的事务隔离级别。当Mysql不支持该隔离级别，Spring事务也就不会生效；
- 已提交读和可重复读是常态；
MySQL的当前读和快照读
- 当前读：select … for update，读取最新版本的数据，对读取的记录加锁阻塞，读写互斥；
- 快照读：select，不加锁的非阻塞读，串行级别下快照读退化成当前读；
快照读就是MySQL为我们实现MVCC的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现。
MySQL如何避免幻读现象
针对快照读（普通 select 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是可重复读级别下通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
InnoDB行锁分三种情况：
- Record Lock：对索引项加锁；
- Gap Lock：对索引之间的“间隙”或第一条索引前的“间隙”或最后一条索引后的“间隙”加锁，但不包括索引记录本身。只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象；
- Next-Key Lock：前两种方式的组合，对记录及间隙加锁，目的是为了解决可重复读隔离级别下幻读的现象；
MVCC
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能同时还可以解决脏读、不可重复读、幻读等事务隔离问题，但不能解决丢失修改问题。
InnoDB中MVCC的实现依赖四个条件: 隐藏字段、undo log、读视图(read view)、可见性算法。
隐藏字段
- DB_ROW_ID：6byte，隐藏主键，如果数据表没有指定主键，InnoDB会利用这个隐藏ID创建聚簇索引；
- DB_TRX_ID：6byte，记录创建这条记录或最后一次修改这条记录的事务ID；
- DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本；
undo log
回滚日志，InnoDB存储引擎层的日志，记录的是当前操作中的相反操作，一条insert语句在undo log中会对应一条delete语句，update语句会在undo log中对应相反的update语句，可用于回滚；
不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，即版本链表。
读视图(Read View)
Read View就是事务进行快照读操作时生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大)。
ReadView中主要包含4个比较重要的内容，分别是：
- creator_trx_id ，创建这个Read View 的事务ID，即创建者的事务ID；
- m_ids ：表示创建Read View时当前系统中活跃的事务的ID集合 (“活跃"指的就是，启动了但还没提交)；
- min_trx_id ：表示创建ReadView时m_ids中最小的事务ID；
- max_trx_id：表示创建ReadView时系统中应该分配给下一个事务的id值，当前最大事务ID+1；
可见性算法
Read View决定当前事务能读到哪个版本的数据，从表记录的Undo Log历史数据的版本链，依次匹配，满足哪个版本的匹配规则，就能读到哪个版本的数据，一旦匹配成功就不再往下匹配。
- DB_TRX_ID = creator_trx_id：表明数据记录的最后一次操作的事务就是当前事务，该版本的记录对当前事务可见；
- DB_TRX_ID < min_trx_id：表明这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见；
- DB_TRX_ID >= max_trx_id：表明这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见；
- min_trx_id <= DB_TRX_ID < max_trx_id：需判断DB_TRX_ID是否在m_ids里面。如果在m_ids中，则代表Read View生成时这个事务还在活跃，没有Commit，版本记录对当前事务不可见；如果不在m_ids中，则说明，这个事务在Read View生成之前就已经Commit了，版本记录对当前事务可见；
已提交读事务在每次查询时都重新生成Read View，所以查询最新提交的记录；
可重复读事务只第一次查询时生成Read View，之后查询复用这个Read View，所以查询的记录版本不变；
MySQL的锁
按锁粒度
- 行级锁：对当前操作的行进行加锁，锁粒度最小，发生锁冲突的概率最低，并发度最高；加锁的开销最大，加锁慢；会出现死锁。
- 表级锁：对当前操作的整张表加锁，锁粒度较大，发生锁冲突的概率较高，并发度较低；加锁的开销较小，加锁快；不会出现死锁；
lock tables tablename write、lock tables tablename read - 全局锁：对整个数据库加锁，锁粒度最大，发生锁冲突的概率最高，并发度最低；加锁的开销最小，加锁快；不会出现死锁；
flush tables with read lock指令执行完，整个数据库就处于只读状态了；
InnoDB默认采用行级锁，MyISAM默认采用表级锁。
按类别
- 排他锁: 写锁、X锁，T对A加X锁，只允许T读取修改A，其他事务不能对A加锁、不能修改A，直到T释放锁；（其他事务不能通过for update和for share锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。)
select ... for update;
- 共享锁: 读锁、S锁，T对A加S锁，T可以读A不能修改A，其他事务只能对A加S锁不能加X锁，直到T释放锁；
select ... for share;
按思想
-
悲观锁：1 指采用悲观的态度，假设外界对数据的操作必然会产生冲突 2 所以在数据处理的整个过程中都采用加锁的状态，保证同一时间，只有一个线程可以访问到数据，实现数据的排他性；3 数据库的悲观锁利用数据库本身提供的锁机制去实现，其悲观并发控制可以解决读-写冲突和写-写冲突；
-
乐观锁：1 指采用乐观的态度，假设外界对数据的操作一般不会造成冲突，所以不会去加锁(乐观锁不是一把锁)，而是在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回冲突信息，让用户决定如何去做下一步，比如说重试，直至成功为止；2 数据库的乐观并发控制要解决的是数据库并发场景下的写-写冲突，用无锁的方式去解决；3 使用数据版本（version）或时间戳(timestamp)实现；
MySQL中InnoDB引擎的行锁模式及其是怎么实现的？
行锁模式
在存在行锁和表锁的情况下，一事务想对某个表加X锁，需要先检查是否有其他事务对这个表加了锁或对这个表的某一行加了锁。对表的每一行都检测一次效率非常低，为了解决这个问题，实现多粒度锁机制，InnoDB引擎提供了两种内部使用的意向锁，这两种意向锁都是表锁。
- 意向共享锁：即IS锁，表示事务打算在表中的各个行上设置共享锁；
- 意向排他锁：即IX锁，表示事务打算在表中的各个行上设置排他锁；
意向锁是InnoDB自动加上的，加锁时遵从下面两个协议：
- 事务在获取表中行的共享锁之前，会自动获取表上的IS锁。
- 事务在获取表中行的排他锁之前，会自动获取表上的IX锁。
锁的兼容性如下：
InnoDB行锁实现方式：InnoDB的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录进行加锁。这意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁.
InnoDB行锁分三种情况：
- Record Lock：对索引项加锁；
- Gap Lock：对索引之间的“间隙”或第一条索引前的“间隙”或最后一条索引后的“间隙”加锁，但不包括索引记录本身。只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象；
- Next-Key Lock：前两种方式的组合，对记录及间隙加锁，目的是为了解决可重复读隔离级别下幻读的现象；
索引
*索引
- . 索引是对数据库表中一列或多列的值进行排序的结构，好比一本书的目录，可以提高查询效率，但同时耗费了数据库存储空间，在插入和修改时也需花费更多的时间（因为索引也要随之变动）；
- 索引使用的数据结构主要有B+树索引和HASH索引 。HASH索引的底层数据结构是HASH表，在绝大多数需求为单条记录查询的时候，可以选择HASH索引，查询性能最好；其余大部分场景建议B+树索引。
优点：将随机I/O变成顺序I/O（B+树的叶子节点连接在一起），加快了数据检索的速度；
缺点：1 空间角度：建立索引需要占用物理空间；2 时间角度：维护索引需要花费时间（对数据进行增删改时索引随之变动）；
*索引的种类
- 聚簇索引：索引决定了数据的物理存储顺序，表中数据按照聚簇索引的键值进行排序，并以此顺序存储在磁盘上。索引叶子结点中包含了实际的数据行，不再有另外单独的数据页；
- 非聚簇索引：索引不影响数据的物理存储顺序，索引的存储与数据的存储是分离的。索引叶子结点中包含指向数据行的指针，通过指针找到实际的数据行。
- 唯一索引：不允许具有索引值相同的行，索引值可以为NULL且允许多个NULL值存在，最大作用是确保写入数据库的该索引列数据唯一性；
- 主键索引：主键作为索引，聚簇索引、唯一索引，且不允许有NULL值；
- 辅助索引（普通索引，二级索引）：非主键索引，叶子节点存索引值+对应主键值。（问：辅助索引的叶子结点存放了主键ID，查询的时候一定要回表查询吗？不一定，当查询的字段刚好是索引的字段或者索引的一部分，就可以不用回表，这也是覆盖索引的原理）；
- 覆盖索引：查询的数据列只用从索引中就能够取得，即查询列被所使用的索引覆盖。通过建立联合索引可以很好地利用覆盖索引从而避免回表；
*索引字段选择与注意事项
- 选择经常被查询的字段：where后出现的列，而非select后出现的列；
- 经常与其他表进行连接的字段；
- 字段值越小越好：字段越小查询时IO次数越少，效率越高；
- 选择离散程度高的字段：可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多，即字段的离散程度越高；
- 选择非NULL字段：在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL；
- 存储空间固定的字段：例如char比text更适合；
- 多个字段经常被查询的话可以考虑联合索引；
- 如果表的增删改非常多，而查询需求非常少的话，就没有必要建索引了，因为维护索引也需要付出代价；
- 对于中大型表建立索引非常有效，对于非常小的表（小于300行），反而全表扫描更快一些；
- 对于超大型的表，建立和维护索引的代价会很高，可以考虑分区技术；
*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+树索引和Hash索引
B+树索引
- B树的一种变形;
- 不同点：（1）非叶子结点仅具有索引作用，只存储key，不存储value；（2）所有叶子结点构成一个按key排序的有序链表；
- 为什么数据库索引选择B+树而不是B树：（1）B+树高度更小：相较于B树B+树每个非叶子结点存储的键更多，所以树高更小查询更快；（2）B+树查询速度更稳定：B+树所有数据都存在叶子结点上，所有key查询的路径长度相同，因此查询速度更加稳定；（3）B+树天然具备排序功能：B+树所有叶子结点按key构成一个有序链表，因此区间查询效率高，而在数据库中基于区间的查询是非常频繁的；
HASH索引
- 底层数据结构是哈希表，采用哈希算法把键值换算成哈希值，以O(1)时间查找，速度快但失去了有序性；
- 在绝大多数需求为单条记录查询时，选择哈希索引查询性能最好，否则选择B+树索引；
- 不足：（1）无法用于排序和分组；（2）不支持范围查找；（3）不支持模糊查询（3）存在哈希冲突，查询性能不稳定，而且如果遇到大量哈希冲突的情况查询效率会大大降低；
*前缀索引
定义：前缀索引是指对字符串或文本的前几个字符建立索引，这样索引的长度更短，查询速度更快。
使用场景：前缀的区分度比较高的情况。
建立方式：
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)：
有些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，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列没有用到索引，因为根据最左匹配原则，遇到范围查询会停止。
由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描。但是在符合 a > 1 条件的索引记录的范围里，b 字段的值是无序的，c也是无序，因此只有a列用到了索引。
第六种
SELECT * FROM table_name WHERE a >= 1 AND b = 2;
虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的。
于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围（b 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。
所以，Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
第七种
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2;
由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值，所以类似于 第六种 查询语句，因此这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
第八种
SELECT * FROM t_user WHERE name like 'j%' and age = 22;
由于联合索引（二级索引）是先按照 name 字段的值排序的，所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的， 于是在进行索引扫描的时候，可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的 name 前缀不为 ‘j’ 为止。
所以 a 字段可以在联合索引的 B+Tree 中进行索引查询，形成的扫描区间是[‘j’,‘k’)。注意， j 是闭区间。如下图：
虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里，age 字段的值是「无序」的，但是对于符合 name = j 的二级索引记录的范围里，age字段的值是「有序」的（因为对于联合索引，是先按照 name 字段的值排序，然后在 name 字段的值相同的情况下，再按照 age 字段的值进行排序）。
于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 name 字段值为 ‘j’ 时，可以通过 age = 22 条件减少需要扫描的二级索引记录范围（age 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 name = ‘j’ and age = 22 条件的第一条记录时开始扫描，而不需要从第一个 name 为 j 的记录开始扫描。如下图的右边：
所以，这条查询语句 name 和 age 字段都用到了联合索引进行索引查询。
第九种
SELECT * FROM table_name WHERE a LIKE 'ab%';
SELECT * FROM table_name WHERE a LIKE '%ab';
SELECT * FROM table_name WHERE a LIKE '%ab%';
只有第一个语句使用到了索引。因为对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。
索引在什么情况下会失效
-
不符合最左匹配原则的组合索引会失效；
-
对索引字段使用函数，例如select * from table_name where abs(a) = 1。因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了；
-
对索引字段使用表达式操作，例如select * from table_name where a + 1 = 2。因为索引保存的是索引字段的原始值，而不是 a + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式;
-
对索引隐式类型转换。例如phone是varchar类型，select * from t_user where phone = 1300000001将走全表扫描。但如果phone是long类型，select * from t_user where phone = ‘1300000001’将走索引。因为MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较，于是select * from t_user where phone = 1300000001等同于select * from t_user where CAST(phone AS signed int) = 1300000001，也就是对索引使用了函数，导致索引失效；
-
对索引字段使用or进行连接，例如select * from table_name where a = 1 or b = 3。假如a建立索引而b没有，则索引失效全表扫描。这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。而如果a和b都建立了索引，则走索引。通过explain SQL语句，可以看到 type=index merge， index merge 的意思就是对a和b分别进行了扫描，然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描；
-
对索引字段使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
-
对索引字段使用 != / not in / <> 等否定操作符，例如select * from table_name where a != 1；
-
对索引字段使用is null / is not null，例如select * from table_name where a is null；
-
对索引字段使用like查询时以%开头；
MySQL中InnoDB和MyISAM使用索引的区别
MySQL的索引使用的数据结构默认是B+树，但对于InnoDB和MyISAM这两种存储引擎的实现方式是不同的：
- InnoDB：1 主键索引叶节点data域保存了完整的数据记录，即聚簇索引；其余索引作为辅助索引，辅助索引的data域存储相应记录主键的值。2 在根据主键索引查找时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，需要先取出主键的值，再回表走⼀遍主键索引；
- MyISAM：主键索引和辅助索引都是非聚簇索引，叶节点data域存放的是数据记录的地址。在根据索引查找时，找到key所在的节点，取出其data域的值，然后以data域的值作为地址读取相应的数据记录；
创建/删除索引
创建索引
- 使用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;
数据库
关系型数据库和非关系型数据库的区别，各自在什么情况下使用
关系型
采用关系模型来组织数据（即二维表格模型）。
优点
- 易于理解：二维表的结构贴近现实世界，易于理解；
- 使用方便：通用的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存储系统。
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：对SQL语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表V1（虚表就是视图，数据会来自多张表的执行结果）；
- ON：对FROM连接的结果进行ON过滤，创建新的虚表V2；
- JOIN：将ON过滤后的左表添加进来，创建虚表V3；
- WHERE：对虚拟表V3进行WHERE筛选，创建虚表V4；
- GROUP BY：对V4中的记录进行分组操作，创建虚表V5；
- HAVING：对V5进行过滤，创建虚表V6；
- SELECT：将V6中的结果按照SELECT进行筛选，创建虚表V8，如果使用了GROUP BY子句则无需使用DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都是不同的；
- ORDER BY：对V8表中的结果进行排序；
解释一下什么是池化设计思想。什么是数据库连接池?为什么需要数据库连接池?
- 池化设计即初始预设资源，解决的问题就是每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好比去食堂打饭，打饭的大妈会先把饭盛好几份放那里，你来了就直接拿着饭盒加菜即可，不用再临时打饭，效率就高了。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等。这些特征可以直接映射到Java线程池和数据库连接池的成员属性中；
- 数据库连接池：数据库连接本质就是⼀个socket连接。数据库服务端还要维护⼀些缓存和用户权限信息等，因此占用了⼀些内存。为用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。有了数据库连接池，创建连接后，就将其放在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立⼀个新连接并将其添加到池中；
1/2/3NF
（1NF每个分量事不可分数据项（不能以集合作属性），2NF消除非主属性对键的部分依赖，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两表连接字段相等记录，left join左表展示所有记录右表展示共有部分不共有部分填空，right 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注入攻击：
- Web端：有效性检验，使用正则表达式过滤SQL需要的参数中的特殊字符（如单引号、双引号）和限制输入长度；
- 服务端：1 SQL语句预编译，如 Java的PrepareStatement，由于SQL语句已经被预编译过，其SQL意图将无法通过非法的参数内容实现更改，其参数中的内容，无法变为SQL命令的一部分；2 有效性检验，使用正则表达式过滤SQL需要的参数中的特殊字符（如单引号、双引号）和限制输入长度；
预编译：可以视为将SQL语句模板化或者参数化，即将SQL语句先交由数据库预处理，构建语法树，再传入真正的字段值多次执行，省却了重复解析和优化相同语法树的时间，提升了SQL执行的效率。
MyBatis中 #{} 和 ${} 区别
（#{} 占位符代替参数将SQL语句预编译再将参数内容替换，${} 将形参和SQL语句直接拼接有SQL注入安全隐患，推荐#{}）
#{}: 解析为SQL时，会将形参变量的值取出，并自动给其添加引号，例如：当实参username="Amy"时，传入Mapper映射文件后，SQL将解析为：SELECT * FROM user WHERE username="Amy"${}: 解析为SQL时，将形参变量的值直接取出，直接拼接显示在SQL中，例如：当实参username="Amy"时，传入Mapper映射文件后，SQL将解析为：SELECT * FROM user WHERE username=Amy，故需要在mapper映射文件中的${value}前后手动添加引号：
<select id="findByName" parameterType="String" resultMap="studentResultMap">
SELECT * FROM user WHERE username='${value}'
</select>
${}方式是将形参和SQL语句直接拼接形成完整的SQL命令后，再进行编译，所以可以通过精心设计的形参变量的值，来改变原SQL语句的使用意图从而产生安全隐患，即为SQL注入攻击；- #{}方式则是先用占位符代替参数将SQL语句先进行预编译，然后再将参数中的内容替换进来。由于SQL语句已经被预编译过，其SQL意图将无法通过非法的参数内容实现更改，其参数中的内容，无法变为SQL命令的一部分。因此#{}可以防止SQL注入而${}却不行；
- 由于SQL注入的原因，${}和#{}在都可以使用的场景下，推荐使用#{}。
- 除非将字段名作为参数，如order by 字段名之类，必须用${}，基本不会这样使用；
MySQL
MySQL是如何执行一条SQL的？具体步骤有哪些？
服务层按顺序执行SQL的步骤为：
- 客户端请求；
- 连接器（TCP三次握手，验证用户身份，给予权限）；
- 查询缓存（存在缓存（看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。）则直接返回，不存在则执行后续操作）；
- 分析器（对SQL进行词法分析和语法分析操作）；
- 优化器（选择它认为最优的执行方案，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引）；
- 执行器（执行前先看用户是否有执行权限，如果没有返回错误信息，如果有就去调用存储引擎接口）；
- 去存储引擎层获取数据返回（如果开启查询缓存还会缓存查询结果）；
MySQL的内部构造一般分哪两个部分？
分服务层和存储引擎层。
- 服务层：主要包括连接器、查询缓存、分析器、优化器、执行器等。另外，所有的内置函数（日期、时间、数学函数）和所有跨存储引擎的功能（存储过程、触发器、视图等）都在这一层实现，还有一个通用的日志模块binlog日志模块；
- 存储引擎层：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory等多个存储引擎，都有undo log日志。默认InnoDB，其中InnoDB 引擎还有自有的日志模块redo log模块。索引结构也是由存储引擎层实现的；
binlog、redo logo、undo log的区别
（binlog归档日志记录所有操作内容DDL DML用于主从复制和数据恢复，redo log重做日志数据提交成功不立刻同步磁盘先记录到redo log合适时机再刷盘崩溃重启恢复，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 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 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不支持；
MySQL字符集及校对规则
- 字符集指的是⼀种从二进制编码到某类字符符号的映射，校对规则是在字符集内用于比较字符的一套规则；
- MySQL采用类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，将会采用默认字符集）；
char和varchar
（1 char长度固定varchar长度不固定，char存取速度比varchar快；2 char1英文一字符1字节2字节，varchar1英文/中文2字节；3 char性别等varchar简介等）
- char长度固定，当插入的字符超过长度时，严格模式下会拒绝插入并提示错误信息，一般模式下会截取后插入；当插入的字符小于长度时，用空格填充；varchar长度可变，根据传入的字符分配长度；char存取速度比varchar快得多；
- char的存储方式是：1个英文字符占1个字节，1个中文字符占2个字节；varchar的存储方式是：1个英文字符或1个中文字符都占2个字节；
- char最大容量为255个字节，varchar最大容量为65535字节；
- 当某个字段的数据长度为定长时，选择char，例如性别、生日等；当某个字段的数据长度不确定时，选择varchar，例如姓名、简介等；
varchar(n)中的n怎么设置比较合理？
- 估计列中存储的最大字符串长度，并设置n为该值的最大值。通常建议设置足够大的值，以便能够存储所有可能的字符串。但是，设置过大的值会浪费存储空间，影响查询性能。
- 如果存储的是固定长度的字符串，可以考虑选择 CHAR(n) 类型，这样可以避免额外的空间和时间消耗;
- varchar(n)中的n表示该字段可以存储的字符数的最大值，将n设置为2的幂并不是必须的；
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要好；
如何优化UNION查询
如果不需要对结果集进行去重或者排序建议使用UNION ALL，性能好些。
临时表
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 语句。
(注：binlog 即二进制日志，记录对数据发生或者潜在发生更改的 SQL 语句，以二进制形式保存在文件中。)
InnoDB为什么使用自增主键
（非自增主键值过于随机，新纪录被查到索引页某中间位置，频繁移动分页大量碎片和开销）
- 如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，会自动开辟一个新的页；
- 如果使用非自增主键（如身份证号），由于每次插入主键的值过于随机，因此新记录很有可能被插到现有索引页的某个中间位置而移动数据，增加了很多开销，而且频繁的移动、分页操作造成了大量的碎片，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面；
字段为什么要设置成NOT NULL
（1 影响一些函数统计如COUNT 2 NOT IN子查询在有NULL值的情况下返回结果都是空值 3 NULL参与字段比较时需特殊处理复杂）
首先，NULL和空值不一样，空值不占用空间，而NULL占用空间，所以字段设为NOT NULL后仍然可以插入空值。
- NULL会影响一些函数的统计，如COUNT，遇到NULL值，这条记录不会统计在内；
- B+树不存储NULL，因此索引用不到NULL，会造成第一点中说的统计不到的问题；
- NOT IN子查询在有NULL值的情况下返回的结果都是空值；
例如user表如下：
- MySQL在进行比较的时候，NULL会参与字段的比较，因为NULL是一种比较特殊的数据类型，数据库在处理时需要进行特殊处理，增加了数据库处理记录的复杂性；
SQL语句执行很慢的原因
- 如果SQL语句只是偶尔执行很慢，可能是执行的时候遇到了锁，也可能是redo log日志写满了，要将redo log中的数据同步到磁盘中去；
- 如果SQL语句一直都很慢，可能是字段上没建索引或者字段建了索引但是没用上索引；
MySQL主从复制
大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式，主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份。整体场景总结如下：
- 读写分离：从库提供查询，减少主库压力，提升性能；
- 高可用：故障时可切换从库，保证服务高可用；
- 数据备份：数据备份到从库，防止服务器宕机导致数据丢失。
主从复制的原理
主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步地完成。详细流程如下：
- 主库写 binlog：主库的更新 SQL(update、insert、delete) 被写到 binlog；
- 主库发送 binlog：主库创建一个 log dump 线程来发送 binlog 给从库；
- 从库写 relay log：从库在连接到主节点时会创建一个 IO 线程，以请求主库更新的 binlog，并且把接收到的 binlog 信息写入一个叫做 relay log 的日志文件；
- 从库回放：从库还会创建一个 SQL 线程读取 relay log 中的内容，并且在从库中做回放，最终实现主从的一致性；
主从延迟问题：完成主从复制会花费一定时间，这段时间会导致从库查询的数据和主库的不一致。对于一些核心的场景，比如库存，支付订单等，直接查询主库，其它非核心场景最终一致性即可，查从库。
数据库优化
（数据库结构优化：1适当违反第三范式减少join 2 设计一些中间表 3 对表垂直拆分 4 对表水平拆分；SQL语句优化：1 避免索引失效 2 尽量不要select * 3 确信查询结果只有一条建议limit 1）
数据库结构优化
- 可适当违反第三范式，减少join操作；
- 设计一些中间表；
- 对表进行垂直拆分：常用字段单独一个表，不常用字段单独一个表，大字段单独一个表；
- 对表进行水平拆分：将一个表中的记录拆分到多个结构相同的表中；
- 主键尽量自增；
参考链接.
SQL语句优化
- 查询SQL尽量不要使用select *，而是select具体字段；
- 如果知道查询结果只有一条记录，建议用limit 1，因为加上limit 1后，只要找到了对应的一条记录，就不会继续向下扫描了，效率将会大大提高；
- 避免索引失效（可用explain语句）；
大表优化
- 当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：1 限定数据的范围 2 读写分离 3 垂直拆分 4 水平拆分；
- 限定数据的范围：禁止不带任何限制数据范围条件的查询语句；
- 读写分离：经典的数据库拆分方案，主库负责写，从库负责读；
- 垂直拆分：指数据表列的拆分，把⼀张列比较多的表拆分为多张表。优点：简化表结构，易于维护，在查询时减少读取的Block数，减少I/O次数；缺点：主键会出现冗余，会引起Join操作，会让事务变得更复杂；
- 水平拆分：水平拆分是指数据表行的拆分，保持数据表结构不变，通过某种策略存储数据分片；每个数据分片分散到不同的表或者库中，达到了分布式的目的；
分库分表
（垂直分：常用字段一个表不常用字段一个表大字段一个表，减少锁表概率，减少IO，提升查询效率；水平分：解决单表数据量过大减少锁表概率，减少IO）
垂直分表、垂直分库
垂直分表
将一个表按照字段分成多个表，每个表存储其中一部分字段。比如常用字段单独一个表，不常用字段单独一个表，大字段单独一个表。
优势：
- 避免IO竞争减少锁表的概率。因为字段越大效率越低，一是数据量大需要的读取时间就长；二是大字段占用的空间更大，单页内存储的行数变少，使IO操作增多；
- 可以更好地提升热门数据的查询效率；
垂直分库
按照业务对表进行分类，部署到不同的数据库上面，不同的数据库可以放到不同的服务器上。
优势：
- 降低业务中的耦合，方便对不同的业务进行分级管理；
- 可以提升IO、数据库连接数、解决单机硬件资源的瓶颈问题；
垂直拆分（分库、分表）的缺点：
- 主键出现冗余，需要管理冗余列；
- 事务的处理变得复杂；
水平分表、水平分库
水平分表
在同一数据库内，把同一个表的数据按照一定规则拆分到多个表中。
优势：
- 解决了单表数据量过大的问题；
- 避免IO竞争并减少锁表的概率；
水平分库
把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。
优势：
- 解决了单库大数据量的瓶颈问题；
- IO冲突减少、锁的竞争减少、某个数据库出现问题不影响其他数据库，提高了系统的稳定性和可用性；
水平拆分（分库、分表）的缺点：
- 分片事务一致性难以解决；
- 跨节点JOIN性能差，逻辑会变得复杂；
- 数据扩展难度大，不易维护；
分库分表之后，ID主键如何处理
见分布式ID
教材内容
什么是封锁？基本的封锁类型有几种？试述它们的含义。
- 封锁：事务对数据操作前，向系统请求封锁，释放锁前，其他事务不能更新数据；
- 两种：排它锁（写锁，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顺序封锁法：对数据对象规定封锁顺序，所有事务按这个顺序封锁）;
试给出检测死锁的一种方法。当发生死锁后，如何解除死锁？
- 超时法：事务等待时间超过规定时限，认为死锁，可能误判；
- 解除方法：选择代价最小的事务，将其撤销，释放它所有锁，让其他事务运行，对撤销事务所执行的数据修改操作进行恢复；
超键、候选键、主键、主属性、非主属性
- 超键：在关系中能唯一标识元组的属性集称为关系模式的超键；
- 候选键：不含有多余属性的超键称为候选键。也就是关系中的一个属性组，其值能唯一标识一个元组。若从属性组中去掉任何一个属性，它就不具有这一性质了，这样的属性组称作候选键；
- 主键：从关系的多个候选键中，选定一个作为主键；
- 主属性：在任何一个候选键中的属性称作主属性；
- 非主属性：不在任何候选键中的属性；
为什么要设置主键
主键是唯一区别表中每一行的标识，如果没有主键，更新或者删除表中特定的行会很困难，因为不能唯一准确地标识某一行。
主键用自增ID还是UUID
自增ID
优点：
- 字段长度较UUID小得多；
- 数据库自动编号，按顺序存放，利于检索；
缺点：
- 由于是自增，在某些业务场景下，容易被其他人查到业务量；
- 发生数据迁移或表合并时会比较麻烦；
- 在高并发场景下，竞争自增锁会降低数据库的吞吐能力；
UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。
优点：
- 不用担心业务量泄漏的问题；
- 唯一标识，不会考虑重复问题，在数据拆分、合并时也能达到全局唯一性；
- 可以在应用层生成，提高数据库的吞吐能力；
缺点：
- 由于UUID是随机生成的，所以会导致随机I/O，影响插入速度，并且会造成硬盘的利用率较低；
- UUID占用空间较大，建立的索引越多，造成的影响越大；
- UUID之间比较大小较自增ID慢不少，影响查询速度；
结论：一般情况下建议使用自增ID，因为B+树的叶子节点按照顺序存储了主键值和数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。
实体完整性和参照完整性
- 实体完整性：关系的主码唯一并且主码的各属性不能取空；
- 参照完整性：外码的值要么取空要么等于被参照关系某个元组的主码值；
为什么外码的属性值可以为空？什么情况下可以为空
- 为空表示该属性值尚未确定;
- 前提是该外码属性不是其所在关系的主属性。例如“学生表”，专业号是外码，不是“学生表”主属性，可以取空；在“选修表”中，课程号是外码，但同时是“选修表”的主属性，不能取空;
数据库如何保证一致性
一致性：数据库只包含成功事务提交的结果时，就处于一致性，否则不一致。
- 数据库层面：通过原子性、隔离性和持续性来保证一致性。即ACID特性中，一致性是目的，原子性、隔离性和持续性都是手段；
- 应用层面：通过代码判断数据库数据是否有效，然后决定回滚还是提交数据；
扫一扫
1133
到【灌水乐园】发言
