【数据库基础知识】常见的数据库基础知识点梳理

末蓝、 2023-02-24 11:25 142阅读 0赞

### 文章目录 ###

*   *  1. 什么是索引
     *  2. 索引的数据结构
     *  3. 聚簇索引与非聚簇索引
     *  4. 什么是索引覆盖
     *  5. 什么是回表查询
     *  6. 建立索引需要考虑的因素
     *  7. 联合索引
     *  8. 查看索引是否被使用到
     *  9. 为什么要尽量设定一个主键
     *  10. 主键使用自增ID还是UUID
     *  11. 字段为什么要求定义为NOT NULL
     *  12. 如果要存储用户的密码散列，应该使用什么字段进行存储
     *  13. 什么是事务
     *  14. 事务的特性
     *  16. 同时多个事务并发执行可能造成的问题
     *  17. 不可重复读和幻读的区别
     *  18. 共享锁与排他锁
     *  19. 表级锁、行级锁、页级锁
     *  20. 死锁
     *  21. 什么是悲观锁
     *  22. 什么是乐观锁
     *  25. 存储过程
     *  26. 什么是视图
     *  27. 什么是游标
     *  28. 触发器
     *  29. drop、truncate、delete区别
     *  30. 临时表
     *  31. 数据库范式
     *  32. 关系型数据库与非关系型数据库优势和区别
     *  33. SQL语言分类
     *  34. count(\*)、count(1)和count(column)的区别
     *  35. like、百分号和下划线的区别
     *  36. 最左前缀原则
     *  37. 超大分页处理
     *  38. 慢查询优化
     *  39. 横向分表和纵向分表
     *  40. B+树和B树的区别
     *  41. 查询语句中用到的关键词
     *  42. 使用explain优化SQL和索引
     *  43. 数据库组主从复制的方式
     *  44. 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）

## 1. 什么是索引 ##

> 索引其实就是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候,索引可以大大的加快查询的速度，这是因为使用索引之后不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据的屋里地址然后访问相应的数据。

> 索引的缺点：在创建索引和维护索引会耗费时间，随着数据的增加而增加.索引也会增加，所以如果是经常增删改的列创建索引会大大的增加项目的维护速度。

> 索引的分类：

*  普通索引
 *  主键索引
 *  唯一索引
 *  多列索引
 *  全文索引

> 创建索引和删除索引：

-- 普通索引
    create index index_name on tb_name
    alter table tb_name add index index_name
    
    drop index index_name on tb_name  -- 删除索引
    
    -- 主键索引
    alter table tb_name add primary key
    
    -- 删除主键索引：
    alter table tb_name drop primary key  -- 普通非自增主键
    
    alter table tb_name id modify id int;  -- 自增主键，取消自增列
    alter table tb_name drop primary key  -- 删除主键索引
    
    -- 唯一索引，与普通索引类似，可以有null值
    create unique index index_name on tb_name
    alter table tb_name add unique index index_name
    
    drop index index_name on tb_name  -- 删除索引
    
    -- 多列索引
    ALTER TABLE people ADD INDEX lname_fname_age (lame, fname,age);

## 2. 索引的数据结构 ##

> 索引的数据结构和具体存储引擎的实现有关，在MySql中常用的有Hash索引和B+树索引，而常用的`InnoDB`存储引擎的默认索引实现为：B+树索引。

*  Hash索引

> Hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获取实际数据。

*  B+树索引

> B+树索引底层实现是多路平衡查找树，所有节点遵循左节点小于父节点，右节点大于父节点；对于每一次查询都是从根节点出发，查找到叶子节点方可以获取所查询的键值，然后根据查询判断是否需要返回表查询数据。

> Hash索引与B+树所以的差异：

*  hash索引进行等值查询比较快，但是无法进行范围查询，而B+支持范围查询；
 *  hash索引不支持使用索引排序；
 *  hash索引不支持模糊查询以及多列索引的最左前缀匹配；
 *  hash索引避免不了回表查询数据，而B+树在（聚簇索引、覆盖索引）的时候可以只通过索引完成查询；
 *  hash索引不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率极差，而B+树查询效率比较稳定，所有查询都是从根节点到叶子节点，且树的高度较低。

## 3. 聚簇索引与非聚簇索引 ##

> 在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。以`InnoDB`作为存储引擎的数据表中，只有主键索引才是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引；如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

> 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用在进行回表查询。

> 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据还需要根据主键再去聚集索引中进行查找，也就是回表查询。非聚簇索引也不一定会回表查询，因为这涉及到查询语句所要求的字段是否全部命中了索引，如果是，那么就不用再进行回表查询了。

## 4. 什么是索引覆盖 ##

> 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

> 可以利用索引覆盖来优化SQL的场景：

*  全表count查询优化
 *  列查询回表优化
 *  分页查询

## 5. 什么是回表查询 ##

> 先定位主键值，再定位行记录，它的性能比扫一遍索引树更低。

## 6. 建立索引需要考虑的因素 ##

*  考虑字段的使用频率，经常作为条件进行查询的字段；
 *  经常作为表连接的字段考虑建索引，可以加快连接的速度；
 *  在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度；
 *  经常在order by、group by之后的字段考虑建索引；
 *  考虑联合索引中的顺序，否则无法命中索引
 *  对非空字段`(NOT NULL)`创建索引，Mysql很难对控制做查询优化
 *  索引适合区分度高、离散程度大的字段，有大量重复值的字段不适合建索引；
 *  索引的长度不能太长，耗时；
 *  对于那些定义为text, image和bit数据类型的列不应该增加索引；

## 7. 联合索引 ##

> MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

> 因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

## 8. 查看索引是否被使用到 ##

> MySQL提供了explain命令来查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，如`possilbe_key, key,key_len`等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

> Mysql索引未被使用到的情况：

*  列参与了数学运算或者函数；
 *  在字符串like时，左边是通配符，如’%aaa’;
 *  当Mysql分析全表扫描比使用索引快的时候不用索引；
 *  当使用联合索引，前面一个条件为范围查询，后面的即使符合最左前缀原则，也无法使用索引；

## 9. 为什么要尽量设定一个主键 ##

> 主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

## 10. 主键使用自增ID还是UUID ##

> 推荐使用自增ID，不要使用UUID（通用唯一识别码，Universally Unique Identifier）。因为在`InnoDB`存储引擎中，主键索引是作为聚簇索引存在的，也就是说主键索引的B+树叶子节点上存储了主键索引以及全部的数据，如果主键索引是自增ID,那么只需要不断向后排序即可；如果用UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入、数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

## 11. 字段为什么要求定义为NOT NULL ##

-- MySQL官网这样介绍：
    NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

> NULL值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

## 12. 如果要存储用户的密码散列，应该使用什么字段进行存储 ##

> 密码散列、盐、用户身份证号等固定长度的字符串应该使用`char`而不是`varchar`来存储,这样可以节省空间且提高检索效率.

## 13. 什么是事务 ##

> 在MySQL中只有使用了`Innodb`数据库引擎的数据库或表才支持事务；事务是数据库并发控制的基本单位；事务可以用来管理`INSERT/UPDATE/DELETE`操作语句；事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行（回滚）。

## 14. 事务的特性 ##

*  原子性(Atomicity)

> 事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

*  一致性(Consistency)

> 几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

*  隔离性(Isolation)

> 事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。

*  持久性(Durability)

> 对于任意已交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现异常。

## 16. 同时多个事务并发执行可能造成的问题 ##

*  脏读：就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问这个数据，然后使用了这个数据。
 *  不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。
 *  幻读：事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测获取到的数据如同鬼影一般。
 *  丢失修改：并发写入造成其中一些修改丢失。

## 17. 不可重复读和幻读的区别 ##

*  不可重复读是读取了其他事务更改的数据，针对update操作

> 解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

*  幻读是读取了其他事务新增的数据，针对insert与delete操作

> 解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

## 18. 共享锁与排他锁 ##

> 共享锁(shared lock)，又叫读锁，读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，而不会造成阻塞。当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

> 排他锁(exclusive lock)，又叫写锁，写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，它和其他的排他锁、共享锁都相斥。

SELECT * FROM table_name WHERE ... FOR UPDATE

## 19. 表级锁、行级锁、页级锁 ##

**[MySQL锁详解][MySQL]**

> 表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

*  使用表级锁的主要是MyISAM、MEMORY、CSV等一些非事务性存储引擎。

> 行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。  
> 虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

*  使用行级锁的主要是InnoDB存储引擎。

> 页级锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

*  使用页级锁的主要是BerkeleyDB存储引擎。

**MySQL中这三种锁的总结**

> 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

> 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

> 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

> 适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

## 20. 死锁 ##

> 在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

> 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。实际上在InnoDB发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。

> 避免死锁的常用方法：

*  在应用中，如果不同的程序会并发存储多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的概率。
 *  在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
 *  在事务中，如果要更新记录，应该直接申请足够级别的锁，及排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得相同记录的共享锁，从而造成锁冲突，甚至死锁。
 *  在可重复读隔离级别下，如果两个线程同时对相同条件记录用`select ... for update`加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么，就会出现死锁。这种情况下，将隔离级别该为读已提交，就可以避免问题。
 *  当隔离级别为读已提交时，如果两个线程都先执行`select ... for update`，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现等待，当第一个线程提交后，第二个线程会因主键重复出错，但虽然这个线程出错了，却获得一个排他锁。这时，如果有第三个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后在捕获主键异常，或在遇到主键重错误时，总是执行Rollback释放获得的排他锁。
 *  查出线程杀死kill：`SELECT try_MySQL_thread_id FROM information_schema.INNODB_TRX;`
 *  设置锁超时时间：`set innodb_lock_wait_timeout=1000;`

## 21. 什么是悲观锁 ##

> 悲观锁(Pessimistic Lock)，顾名思义就是很悲观。每次去操作数据的时候，都会认为别人会修改。所以为了防止别人修改，就在操作的时候上锁。这样别人来访问的时候就会阻塞在那里直到锁被是释放。传统的关系型数据库里面就用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。

> 遵循逻辑为一锁二查三更新(select for update)会把数据给锁住，其中MySQL默认InnoDB引擎，默认是行级锁，基于索引的，若SQL语句用不到索引，则不会使用行级锁的，会使用表级锁将整张表锁住。

-- 来源：https://www.jianshu.com/p/083bbe5f5534
    -- encoding:utf-8
    
    -- 场景：商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单的时候必须确保该商品status为1。假设商品id为1。
    
    -- 如果不使用锁,那么操作方法如下:
    -- 1.查询出商品信息
    select status from t_goods where id = 1;
    
    -- 2.根据商品信息生成订单
    insert into t_orders(id,goods_d) values(null,1);
    -- 3.修改商品status为2
    update t_goods set status=2;
    
    -- 上面这种场景在高并发访问的情况下很可能会出现问题。
    -- 前面已经提到，只有上status为1的时候，才能对该商品下单，上面的第一步操作中查询出来的上面品status为1，但是当我们执行第二步的时候，很有可能有的人先一步执行了步骤2和步骤3，此时订单已经被下过了，然后我们又下了一次。很有可能就出现一个订单被下单了两次的情况。
    
    
    -- 解决这种问题的方法，我们使用悲观锁来实现。
    
    -- 在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程。使用悲观锁的原理就是,当我们查询goods的信息时，就把当前的数据锁住，直到我们修改完毕后再解锁。那么在这个过程中，因为goods被锁定了，就不会出现有第三者来对其进行修改了。
    
    -- 注意：如果要使用悲观锁，我们必须将mysql数据库的自动提交功能关闭。因为mysql默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySql会立即将结果进行提交。
    
    
    -- 我们可以使用命令设置MySQL为非autocommit的模式:
    set autocommit=0;
    -- 设置完autocommit后,我们就可以执行我们的正常业务了。具体如下：
    
    -- 0.事务开始
    begin;/begin work;/start trasaction;  -- 三者选一即可
    -- 1.查询出商品信息
    select status from t_goods where id=1 for update;
    -- 2.根据商品信息生成订单
    insert into t_orders (id ,goods_id) values (null,1);
    -- 3.修改商品status为2
    update t_goods set status=2;
    -- 4.提交事务
    commit;/ commint work;
    
    -- 上面的第一步我们执行了一次查询操作:
    select status from t_goods from t_good where id=1 for update;
    
    -- 与普通查询不一样的是，我们使用了select... for update的方式，这样就通过数据库实现了悲观锁。此时t_goods表中，id为1的那条数据就被我们锁定了，其他的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其他事务修改。

> 悲观锁的优缺点：悲观锁的策略是先取锁在访问，保证了数据的安全性，但是效率方面，处理锁机制会让数据库产生额外开销，还有可能增加产生死锁的可能，所以一般只有在写操作冲突很多的时候才使用悲观锁，而读操作不需要加锁。

## 22. 什么是乐观锁 ##

> 乐观锁(Optimistic Lock)，顾名思义就是很乐观，每次去拿数据的时候都认为比人不会修改，所以不会上锁。但是在更新数据的时候，会判断一下在更新数据的这段时间内，数据有没有被修改；如果被修改了，就取消这次操作；如果没有被修改，则使得这次操作生效。一般使用版本号或者时间戳的方式来实现。

> 遵循先修改，更新时发现数据已经变了就回滚(check and set)，乐观锁一般通过数据库版本号或时间戳实现。在数据库增加一个字段version，当我们操作数据的时候，将版本号一同取出来，当我们更新数据，在提交更新的时候，会再去数据查询下当前的版本号，跟我们之前取出来的版本号，是否一致。如果一致，就使得这次更新生效。并且在每次更新数据的时候，都使得版本号加1。

-- 使用版本号时,可以再数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断一下当前的版本号是不是该数据的最新的版本号。
    
    -- 1. 查询出商品信息
    select (status, version) from t_goods where id = 'id';
    
    -- 2.根据商品信息生成订单
    
    -- 3.修改商品status为2
    update t_goods set status=2, version=version+1
    where id='id' and version='version';

> 乐观锁的优缺点：乐观并发控制相信事务之间的数据竞争的概率是比较小的，会先进行操作。再提交的时候，再进行验证这次提交是否可行，因此不会有任何的死锁和锁。但是这样做还是有问题的，例如如果某两个事务对同一行的数据同时进行了修改，经过修改之后，同时写进了数据库，这时就会出现问题。所以乐观锁，一般用在读数据比较多的地方。而对于写数据比较多的地方，我们最好使用悲观锁来解决。

## 25. 存储过程 ##

> 存储过程是一些预编译的SQL语句，执行效率比较高，一个存储过程代替大量的T-SQL语句，可以降低网络通信量，提高通信速率，可以一定程度上确保数据安全。

> 通俗的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能，然后再给这个代码块取一个别名，调用时直接用别名调用。

> 存储过程的优缺点

*  优点：
    
     *  存储过程是预编译过的，执行效率高；
     *  存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯；
     *  安全性高，执行存储过程需要一定权限的用户；
     *  存储过程可以重复使用，可减少数据库开发人员的工作量；
 *  缺点：移植性差

## 26. 什么是视图 ##

> 视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增、改、查操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

> 视图的优缺点

*  优点：
    
     *  对数据库的访问，因为视图可以有选择性的选取数据库里的一部分；
     *  用户通过简单的查询可以从复杂查询中得到结果；
     *  维护数据的独立性，视图可从多个表检索数据；
     *  对于相同的数据可产生不同的视图；
 *  缺点：
    
     *  查询视图时，必须把视图的查询转化成基本表的查询，如果这个视图是由一个复杂的夺标查询所定义的，那么就无法更改数据。

## 27. 什么是游标 ##

> 游标是对查询出来的结果作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或者多行。可以对结果集当前行做修改。一般不用游标，主要用在逐条处理数据时。

## 28. 触发器 ##

> 触发器是一种特殊的存储过程，主要是通过实践来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算，如某表上的触发器包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

## 29. drop、truncate、delete区别 ##

*  drop直接删除表
 *  truncate删除表中的数据，再插入时自增长id又从1开始
 *  delete删除表中的数据，可以加where语句

## 30. 临时表 ##

> MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。

## 31. 数据库范式 ##

> 第一范式：最基本范式，数据库表中所有字段都是不可分解的原子值。

> 第二范式：在一个数据表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据表中。主要针对联合主键而言，确保数据表中的每一列都和主键相关，而不能只与主键的某一部分相关。

> 第三范式：确保数据表中的每一列数据都和主键直接相关，而不是间接相关。

> **一般，一个数据库设计符合3NF或BCNF就可以了**

> 第四范式：要求把统一表内的多对多关系删除。

> 第五范式：从最终结构重新建立原始结构。

## 32. 关系型数据库与非关系型数据库优势和区别 ##

*  关系型数据库
    
     *  复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
     *  事务支持：使得对安全性能很高的数据访问要求得以实现。
 *  非关系型数据库
    
     *  性能：NoSQL基于k-v键值对，不需要经过SQL层的解析，性能非常高。
     *  可扩展性：因为基于键值对，数据之间没有耦合性，所以水平扩展非常容易。
 *  趋势
    
     *  NoSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能，如MongoDB。
     *  对于事务的支持，也可以用一些系统级的原子操作（乐观锁之类的方法）来解决，比如redis set nx

## 33. SQL语言分类 ##

*  数据查询语言DQL：主要是`SELECT`、`FROM`、`WHERE`语句
 *  数据操纵语句DML：主要是`INSERT`、`UPDATE`、`DELETE`
 *  数据定义语言DDL：创建数据库中的各种对象（表、视图、索引、同义词）`CREATE TABLE/VIEW/INDEX/SYN/CLUSTER`
 *  数据控制语言DCL：授权（`GRANT`）、回滚上次最后提交的状态（`ROLLBACK`）、回退到某个点（`ROLLBACK WORK TO [SAVEPOINT`）、提交（`COMMIT WORK`）。

## 34. count(\*)、count(1)和count(column)的区别 ##

*  `count(*)`：对行的数目进行计算，包含NULL，跟`count(非主键)`一样，扫描整个表；
 *  `count(1)`：与`count(主键)`是一样的，只扫描主键；
 *  `count(column)`：对特定的类的值具体的函数进行计算，不包含NULL值；

> 特别注意：

*  任何情况下`SELECT COUNT(*) FROM tablename`是最优选择；
 *  尽量减少`SELECT COUNT(*) FROM tablename WHERE COL = 'value'`这种查询；
 *  杜绝`SELECT COUNT(COL) FROM tablename WHERE COL1 = 'value'`的出现；
 *  如果表没有主键，那么`count(1)`比`count(*)`快；若果有主键，那么`count(主键，联合主键)`比`count(*)`快；若过表只有一个字段，`count(*)`最快。

## 35. like、百分号和下划线的区别 ##

*  like操作符：LIKE作用是指示MySQL后面的搜索模式是利用通配符而不是直接相等匹配进行比较；若like后面没有通配符，则跟=等号效果一致。

SELECT * FROM products WHERE products.prod_name like '1000';

*  %百分号通配符：表示任何字符出现任意次数（可以是0次），但是不能匹配NULL。

-- 匹配以aaa开头的记录，包括aaa
    SELECT FROM products WHERE products.prod_name like 'aaa%';
    
    -- 匹配包含aaa开头的记录，包括aaa
    SELECT FROM products WHERE products.prod_name like '%aaa%';
    
    -- 匹配以aaa结尾的记录，仅仅包括aaa（后面不能有空格）
    SELECT FROM products WHERE products.prod_name like '%aaa';

*  \_下划线通配符：表示只能匹配单个字符，不能多也不能少，就一个字符。

-- 向前匹配一个字符
    SELECT FROM products WHERE products.prod_name like '_aaa';
    
    -- 向后匹配一个字符
    SELECT FROM products WHERE products.prod_name like 'aaa_';

*  特别注意
    
     *  MySQL的通配符很有用，但这种通配符搜索的处理一般比较慢。
     *  不要过度使用通配符,如果其他操作符能达到相同的目的，应该使用其他操作符;
     *  在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处，因为会很慢；
     *  仔细注意通配符的位置，如果放错地方，可能不会返回想要的结果。

## 36. 最左前缀原则 ##

> 最左前缀原则：顾名思义，就是最左优先。在mysql中执行查询时，只能使用一个索引；在多列分别建索引，执行查询时，只用使用一个索引，mysql会选择一个最严格的的索引（获得结果集记录数最少的）。

## 37. 超大分页处理 ##

*  数据库层面：核心思想是减少load数据，适当利用所应覆盖
 *  靠缓存机制解决，可预测性地提前查到所需的内存，缓存到redis等k-v数据库中，直接返回即可。

## 38. 慢查询优化 ##

*  首先分析语句，查看是否load了额外的数据，可能是查询了多余的行并抛弃了，可能是加载了许多结果中并不需要的列，对语句进行分析并重写；
 *  分析语句的执行计划，然后获得其索引情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引；
 *  如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或纵向的分表；
 *  `slow_query_log_file` 查看慢查询日志，调整数据修改索引

## 39. 横向分表和纵向分表 ##

> 横向分表是按行分表，假设我们有一张用户表，主键是自增ID且同时是用户的ID。数据量较大，有1亿多条，那么此时放在一张表里的查询效果就不太理想。我们可以根据主键ID进行分表，无论是按尾号分，或者按ID的区间分都是可以的。假设按照尾号0-99分为100个表，那么每张表中的数据就仅有100w。这时的查询效率无疑是可以满足要求的。

> 纵向分表是按列分表，假设我们现在有一张文章表。包含字段id-摘要-内容。而系统中的展示形式是刷新出一个列表，列表中仅包含标题和摘要，当用户点击某篇文章进入详情时才需要正文内容。此时，如果数据量大，将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度。我们可以将上面的表分为两张。id-摘要，id-内容，当用户点击详情，那主键再来取一次内容即可，而增加的存储量只是很小的主键字段，代价很小。

## 40. B+树和B树的区别 ##

**[MySQL索引-B+树（看完你就明白了）][MySQL_-B]**

**[漫画叙述B+树和B-树][B_B-]**

> (1). B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

> (2). B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

> (3). B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。

> (4). B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。在`InnoDB`中，数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据；在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

> (5). B+树的磁盘读写代价更低，IO 次数少，数据查询的效率也会更快；B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路，路径长度相同，所以每一个数据的查询效率相当。

## 41. 查询语句中用到的关键词 ##

> 查询中用到的关键词主要包含六个，且执行顺序为`select-->from-->where-->group by-->having--order by`

> select和from是必须的，其他可选；

> where: 过滤表中数据的条件；

> group by：如何将上面过滤出的数据分组；

> having：对上面已经分组的数据进行过滤的条件；

> order by：按照什么样的顺序来查看返回的数据；

> **from后面的表关联，是自右向左解析，而where条件的解析顺序是自下而上的：** 在写SQL语句的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在where语句的最左边（用小表去匹配大表）。

## 42. 使用explain优化SQL和索引 ##

> 对于复杂、效率低的sql语句，可以使用`explain + sql`的格式来分析sql语句，可以打印出语句执行的过程，以便分析与优化。

<table> 
 <thead> 
  <tr> 
   <th align="center">关键字</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><code>table</code></td> 
   <td align="left">显示这一行的数据是关于哪张表的</td> 
  </tr> 
  <tr> 
   <td align="center"><code>type</code></td> 
   <td align="left">这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL</td> 
  </tr> 
  <tr> 
   <td align="center"><code>all</code></td> 
   <td align="left"><code>full table scan</code> 即MySQL将遍历全表以找到匹配的行</td> 
  </tr> 
  <tr> 
   <td align="center"><code>index</code></td> 
   <td align="left"><code>index scan</code> index 和 all的区别在于index类型只遍历索引</td> 
  </tr> 
  <tr> 
   <td align="center"><code>range</code></td> 
   <td align="left">索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between，等查询</td> 
  </tr> 
  <tr> 
   <td align="center"><code>ref</code></td> 
   <td align="left">非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找</td> 
  </tr> 
  <tr> 
   <td align="center"><code>eq_ref</code></td> 
   <td align="left">唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描</td> 
  </tr> 
  <tr> 
   <td align="center"><code>const, system</code></td> 
   <td align="left">当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量</td> 
  </tr> 
  <tr> 
   <td align="center"><code>possible_keys</code></td> 
   <td align="left">显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句</td> 
  </tr> 
  <tr> 
   <td align="center"><code>key</code></td> 
   <td align="left">实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用<code>USE INDEX（indexname）</code>来强制使用一个索引或者用<code>IGNORE INDEX（indexname）</code>来强制MySQL忽略索引</td> 
  </tr> 
  <tr> 
   <td align="center"><code>key_len</code></td> 
   <td align="left">使用的索引的长度。在不损失精确性的情况下，长度越短越好</td> 
  </tr> 
  <tr> 
   <td align="center"><code>rows</code></td> 
   <td align="left">MySQL认为必须检查的用来返回请求数据的行数</td> 
  </tr> 
  <tr> 
   <td align="center"><code>Extra</code></td> 
   <td align="left">关于MySQL如何解析查询的额外信息</td> 
  </tr> 
 </tbody> 
</table>

## 43. 数据库组主从复制的方式 ##

*  同步复制

> 所谓同步复制，意思是master的变化，必须等待`slave-1, slave-2, ..., slave-n`完成后才能返回。这显然不可取，也不是MySQL复制的默认设置。比如在Web全段页面上，用户增加了一条记录，需要等待很长时间。

*  异步复制

> 异步也就同ajax请求一样，master秩序完成自己的数据操作即可，至于slaves是否接收到二进制日志、是否完成操作，不需要关心，这是MySQL的默认设置。

*  半同步复制

> master只保证slaves中的一个操作成功就返回，其他的不管。这个是由goole为MySQL引入的。

## 44. 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志） ##

*  Undo Log

> 为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog），然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

> 为了保证事务持久性，必须将数据在事务提交前写到磁盘,只要事务成功提交，数据必然已经持久化。Undo log必须先于数据持久化到磁盘，如果在G,H之间系统崩溃，undo log是完整的， 可以用来回滚事务；如果在A-F之间系统崩溃，因为数据没有持久化到磁盘，所以磁盘上的数据还是保持在事务开始前的状态。

> 缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。  
> 如果能够将数据缓存一段时间，就能减少IO提高性能，但这样就会丧失事务的持久性。

*  Redo Log

> 原理和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化，系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

**持续跟新，更多数据库基础知识，[详见个人博客归纳][Link 1]**。

**欢迎留言补充，批评指正！**

[MySQL]: https://www.cnblogs.com/luyucheng/p/6297752.html
[MySQL_-B]: https://www.cnblogs.com/zhuyeshen/p/12082839.html
[B_B-]: https://blog.csdn.net/qq_35571554/article/details/82759668
[Link 1]: https://holysll.github.io/2020/05/08/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9F%A5%E8%AF%86/