相关知识点-数据库(Mysql)

事务 ACID

在数据库系统中，一个事务是指由一系列数据库操作组成的一个完整的逻辑过程。例如银行转帐，从原账户扣除金额，以及向目标账户添加金额，这两个数据库操作的总和构成一个完整的逻辑过程，不可拆分。这个过程被称为一个事务，具有ACID特性，ACID是指在数据库管理系统（DBMS）中事务所具有的四个特性：

1、A (Atomicity) 原子性
原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。

比如银行转账，从A账户转100元至B账户，分为两个步骤：1）从A账户取100元；2）存入100元至B账户。这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了100元。

2、C (Consistency) 一致性
一致性也比较容易理解，也就是说数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。

例如现有完整性约束a+b=10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。

3、I (Isolation) 隔离性/独立性
所谓的独立性是指并发的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。

比如现在有个交易是从A账户转100元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的。

4、D (Durability) 持久性
持久性是指一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。
MySql的持久性是通过一条记录事务过程中系统变化的二进制事务日志文件来实现的。如果遇到硬件损坏或者系统的异常关机，系统在下一次启动时，通过使用最后的备份和日志就可以恢复丢失数据。

事务的并发问题—隔离级别

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
　　
隔离级别

1.读未提交（read-uncommitted）
所有事务都可以看到其他未提交事务的执行结果，该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据。

2.读提交（read-committed）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。
这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

3.可重复读（repeatable-read）
这是MySQL的默认事务隔离级别。
它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。

4.串行化（serializable）
它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
在这个级别，可能导致大量的超时现象和锁竞争。

参考：https://www.cnblogs.com/snsdzjlz320/p/5761387.html

数据库三大范式

1.第一范式（1NF）（原子性）
数据库表中的所有字段值都是不可分割的原子值。

举例：
班级字段值：高二10班
原因：高三年1班含有年级和班级信息，可继续拆分，为非原子信息。

2.第二范式（2NF）（完全依赖主键）
属性完全依赖于主键。数据库表中的每一列都与主键完全相关，而不能只与主键的部分相关。

假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，主键为属性组(学号, 课程名称)，因为存在如下决定关系：
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式，因为存在如下决定关系：
(课程名称) → (学分)
(学号) → (姓名, 年龄)
即非主属性依赖于主键的一部分（如学分只依赖于课程名称，而不是学号和课程名称）。

3.第三范式（3NF）（直接依赖主键）
属性不依赖于其它非主属性。数据库表中的每一列和主键直接相关，而不是间接相关。也就是属性不能传递依赖于主属性。

假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，关键字为单一关键字"学号"，因为存在如下决定关系
(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)
这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系
(学号) → (所在学院) → (学院地点, 学院电话)

参考：https://blog.youkuaiyun.com/weixin_41886551/article/details/80540636

乐观锁和悲观锁

悲观锁：
先获取锁，再进行业务操作。假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
乐观锁：
先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

索引

索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

索引类型:
可以使用SHOW INDEX FROM table_name;查看索引详情：

1. 主键索引 PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。
2. 唯一索引 UNIQUE：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引
3. 普通索引 INDEX：这是最基本的索引，它没有任何限制。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引：
4. 组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引：
5. 全文索引 FULLTEXT：也称全文检索，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引：
6. 索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引。

索引设计的原则

1）适合索引的列是出现在where子句中的列，或者连接子句中指定的列；
2) 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的;
3) 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引;
4) 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生;
5) 多表关联时，要保证关联字段上一定有索引;
6）使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；
7）不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

回表：当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。

1）建立索引的列，不允许为null。

2）前导模糊查询不能命中索引。
EXPLAIN SELECT * FROM user WHERE name LIKE ‘%s%’;
非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询：
EXPLAIN SELECT * FROM user WHERE name LIKE ‘s%’;

3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
EXPLAIN SELECT * FROM user WHERE name=1;
EXPLAIN SELECT * FROM user WHERE name=‘1’;

4）复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则:查询条件中是否包含索引最左列字段），不会命中符合索引。
name,age,status列创建复合索引：
ALTER TABLE user ADD INDEX index_name (name,age,status);

5）union、in、or都能够命中索引，建议使用in。
查询的CPU消耗：or>in>union。

6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

7）负向条件查询不能使用索引，可以优化为in查询。
负向条件有：!=、<>、not in、not exists、not like等。

8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引：EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;

如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：
EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;

8）数据库执行计算不会命中索引。
EXPLAIN SELECT * FROM user WHERE age+1>24;

9）利用覆盖索引进行查询，避免回表。
EXPLAIN SELECT status FROM user where status=1;

索引优化：
https://blog.youkuaiyun.com/shang_xs/article/details/86681023

为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

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

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

数据库索引采用B+树而不是B树的主要原因：
B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

面试题:

1).InnoDB使用的B+ Tree的索引模型, 这和Hash索引比较起来有什么优缺点吗？

B+ Tree索引和Hash索引区别:
哈希索引适合等值查询，但是无法进行范围查询;
哈希索引没办法利用索引完成排序;
哈希索引不支持多列联合索引的最左匹配规则;
如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题.

2)B+ Tree的叶子节点都可以存哪些东西吗？

InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值。
InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

那么，聚簇索引和非聚簇索引，在查询数据的时候有区别吗？
聚簇索引更快.因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询。

刚刚你提到主键索引查询只会查一次，而非主键索引需要回表查询多次。（回表）是所有情况都是这样的吗？非主键索引一定会查询多次吗？
覆盖索引（covering index）
指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

3)那你知道在MySQL 5.6中，对索引做了哪些优化吗？
索引下推Index Condition Pushdown (ICP)
MySQL用索引去表里取数据的一种优化。如果禁用ICP，引擎层会穿过索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤。
ICP启用，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分下推到引擎层。存储引擎通过使用索引条目，然后推索引条件进行评估，使用这个索引把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。

并非全部where条件都可以用ICP筛选。如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。

4)面试官：那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？
查询优化器
一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下： 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个.

面试：
https://blog.youkuaiyun.com/hollis_chuang/article/details/95167242

MyISAM和InnoDB

主要区别:

• 事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。

• 索引：InnoDB含聚簇索（主键索引，既存储索引值,又在叶子中存储行的数据）引和非聚簇索引（辅助索引，通过这个主键值来回表查询到一条完整记录）。MyISAM索引文件和数据文件分离，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行，故能加载更多索引。

• 存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

• 可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

• 表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；-而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了where条件后，myisam和innodb处理的方式都一样。

• CURD操作：在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

• 表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

• 全文索引：MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

• 表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

• 外键：MyISAM不支持外键，而InnoDB支持外键。

应用场景：

1).MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2).InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
3).MYISAM:从平台角度来说，经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为我平台上最小的一个数据库实例的数据量基本都是几十G大小。
4).MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

MySQL中MyISAM和InnoDB的索引方式以及区别与选择：
https://blog.youkuaiyun.com/LJFPHP/article/details/80029968

实践中如何优化MySQL？

实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面。

1). SQL语句及索引的优化

SQL语句的优化主要包括三个问题，即如何发现有问题的SQL、如何分析SQL的执行计划以及如何优化SQL，下面将逐一解释。

a. 怎么发现有问题的SQL?（通过MySQL慢查询日志对有效率问题的SQL进行监控）
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。慢查询日志的相关参数如下所示：
通过MySQL的慢查询日志，我们可以查询出执行的次数多占用的时间长的SQL、可以通过pt_query_disgest(一种mysql慢日志分析工具)分析Rows examine(MySQL执行器需要检查的行数)项去找出IO大的SQL以及发现未命中索引的SQL，对于这些SQL，都是我们优化的对象。

b. 通过explain查询和分析SQL的执行计划
使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。
　　
c. SQL语句的优化

1）优化insert语句：一次插入多值；
2）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
3）应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
4）优化嵌套查询：子查询可以被更有效率的连接(Join)替代；
5）很多时候用 exists 代替 in 是一个好的选择。

索引优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形：

1）以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
2）OR语句前后没有同时使用索引；
3）数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
4）对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则 索引生效的情形包括col1或col1，col2或col1，col2，col3)。

2). 数据库表结构的优化

数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。

(1). 选择合适数据类型

• 使用较小的数据类型解决问题；
• 使用简单的数据类型(mysql处理int要比varchar容易)；
• 尽可能的使用not null 定义字段；
• 尽量避免使用text类型，非用不可时最好考虑分表；

(2). 表的范式的优化

• 一般情况下，表的设计应该遵循三大范式。

(3). 表的垂直拆分

把含有多个列的表拆分成多个表，解决表宽度问题，具体包括以下几种拆分手段：

• 把不常用的字段单独放在同一个表中；
• 把大字段独立放入一个表中；
• 把经常使用的字段放在一起；

这样做的好处是非常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。

3). 系统配置的优化

• 操作系统配置的优化：增加TCP支持的队列数
• mysql配置文件优化：Innodb缓存池设置(innodb_buffer_pool_size，推荐总内存的75%)和缓存池的个数（innodb_buffer_pool_instances）

4). 硬件的优化

• CPU：核心数多并且主频高的
• 内存：增大内存
• 磁盘配置和选择：磁盘性能

简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

• Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；
• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
• Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

COUNT

COUNT函数，主要用于统计表行数。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)。

因为COUNT()是SQL92定义的标准统计行数的语法，所以MySQL对他进行了很多优化，MyISAM中会直接把表的总行数单独记录下来供COUNT()查询，而InnoDB则会在扫表的时候选择最小的索引来降低成本。当然，这些优化的前提都是没有进行where和group的条件查询。

在InnoDB中COUNT(*)和COUNT(1)实现上没有区别，而且效率一样，但是COUNT(字段)需要进行字段的非NULL判断，所以效率会低一些。

什么是触发器？

触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。

本文参考文章：

1. https://blog.youkuaiyun.com/justloveyou_/article/details/78308460