数据库知识点总结归纳

数据库
Word count: 25.2k   |   Reading time≈ 94 min

本文主要对数据库基础知识进行梳理、回顾，把一些需要记住的概念原理，和容易混淆，晦涩的知识点进行归纳。

数据库常识

1. 什么是索引

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

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

索引的分类：

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

创建索引和删除索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 普通索引
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

1
2
-- 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)

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

15. MySql的事务隔离级别

  • 读未提交（Read uncommitted）

事物A和事物B，事物A未提交的数据，事物B可以读取到”脏数据”；隔离级别低，一般都高于该级别。

  • 读已提交（Read committed）

事物A和事物B，事物A提交的数据，事物B才能读取到;隔离级别高于读未提交，可以避免脏读，但是可能会导致不可重复读和幻读。（是Oracle默认隔离级别）

  • 可重复读（Repeatable read）

事务A和事务B，事务A提交之后的数据，事务B是可重复读取数据这时事务B读取不到；该隔离级别高于读已提交，可以避免不可重复读，但有可能导致幻读，MySQL（也是InnoDB）默认隔离级别。

  • 串行化（Serializable）

事务A和事务B，事务A在操作数据库时，事务B只能排队等待；这种隔离级别很少使用，吞吐量太低，用户体验差，这种级别可以避免脏读、重复读、幻读，每一次读取的都是数据库中真实存在数据，这时事务A与事务B串行，而不是并发。

四个隔离级别可能出现脏读、不可重复读、幻读问题

隔离级别 出现情况
脏读 不可重复读 幻读
读未提交 √ √ √
读已提交 × √ √
可重复读 × × √
串行化 × × ×

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

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

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

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

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

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

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

18. 共享锁与排他锁

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

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

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

1
SELECT * FROM table_name WHERE ... FOR UPDATE

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

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语句用不到索引，则不会使用行级锁的，会使用表级锁将整张表锁住。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 来源：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
2
3
4
5
6
7
8
9
10
-- 使用版本号时,可以再数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+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';

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

23. MySQL支持哪些存储引擎

MySQL的存储引擎的特点比较

(1) InnoDB存储引擎

MySQL的默认存储引擎，提供事务安全表，该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。用于事务处理应用程序，具有众多特性，包括ACID事务支持。相对于MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB主要特点：

  • InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎；
  • InnoDB有强大的CPU效率，能够处理巨大数据量；
  • InnoDB存储引擎完全与MySQL服务器整合，将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件，表可以是任何尺寸；
  • InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。

(2) MyISAM存储引擎

MyISAM存储引擎是管理非事务表，不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表，支持3种不同的存储格式，分别是：静态表（字段长度固定）、动态表（字段长度动态不固定）、压缩表（每个记录单独被压缩，访问开支小）。默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

MyISAM的主要特点：

  • 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持；
  • 当把删除和更新及插入操作混合使用的时候，动态大小的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成；
  • 每个MyISAM表最大索引数是64，这可以通过重新编译来改变，每个索引最大的列数是16；
  • NULL被允许在索引的列中，这个值占每个键的0~1个字节；
  • 可以把数据文件和索引文件放在不同目录；

(3) MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

(4) MERGE存储引擎

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，Merge表本身并没有数据，对Merge类型的表可以进行select、update、delete操作，这些操作实际上是对内部的MyISAM表进行的。允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

(5) Archive存储引擎

存储引擎被用来无索引的、非常小的覆盖存储大量数据。为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

(6) EXAMPLE存储引擎

EXAMPLE存储引擎是一个“存根”引擎，可以用这个引擎创建表，但没有数据被存储于其中或从其中检索，这个引擎的目的是服务，可为快速创建定制的插件式存储引擎提供帮助。

(7) NDB Cluster存储引擎

MySQL的簇式数据库引擎，是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

(8) FEDERATED存储引擎

FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

(9) CSV存储引擎

CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。

(10) BLACKHOLE存储引擎

BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。用于临时禁止对数据库的应用程序输入。

24. MyISAM和InnoDB的区别

MyISAM InnoDB
构成 表定义文件：.frm；数据文件：.MYD；索引文件：.MYI； 表大小只限制与操作系统文件的大熊啊，一般为2G
事务处理 性能快，但不支持事务和外键 支持事务，支持外键
增删改查 适合执行大量的SELECT操作 执行大量数据的INSERT或UPDATE；删除不会重新建表，而是一行行删除
AUTO_INCREMENT操作 MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列； 在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，仅被存储在主内存中，而不是存在磁盘上
表行数 执行select count(*) from table保存行数 执行select count(*) from table且要扫描整表去计算行数
锁 表级锁 行级锁
全文索引 支持全文索引 支持全文索引(>=5.6)
多版本并发控制 不支持MVCC 支持MVCC

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后面没有通配符，则跟=等号效果一致。
1
SELECT * FROM products WHERE products.prod_name like '1000';
  • %百分号通配符：表示任何字符出现任意次数（可以是0次），但是不能匹配NULL。
1
2
3
4
5
6
7
8
-- 匹配以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';
  • _下划线通配符：表示只能匹配单个字符，不能多也不能少，就一个字符。
1
2
3
4
5
-- 向前匹配一个字符
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+树（看完你就明白了）

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

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的内容，将所有数据恢复到最新的状态。

Mysql数据库（端口：3306）

1. 创建数据库

1
2
3
4
5
-- utf-8
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

-- gbk
CREATE DATABASE db_name DEFAULT CHARSET gbk COLLATE gbk_chinese_ci;

2. 用户管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建用户
create user '用户名'@'IP地址' identified by '密码';

-- 删除用户
drop user '用户名'@'IP地址';

-- 修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;

-- 修改密码
set password for '用户名'@'IP地址' = Password('新密码');
-- 或者用update更新user
update user set password=password('123') where user='root' and host='localhost';

-- 修改需要刷新才生效
flush privileges;

3. 授权管理

1
2
3
4
5
6
7
8
-- 查看权限
show grant for '用户名'@'IP地址';

-- 授权
grant 权限 on database1.table1 to '用户名'@'IP地址';

-- 取消权限
revoke 权限 on database1.table1 from '用户名'@'IP地址';

4. 修改表的列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 添加列
alter table table_name add column int;

-- 默认添加到最后一列
alter table t1 add age int default 0;

-- 添加到第一列
alter table t1 add addr char(12) first;

-- 添加到addr列后面
alter table t1 add phone after addr;

-- 删除列
alter table t1 drop phone;

-- 修改列，只修改类型
alter table t1 modify column phone INTEGER;

-- 列名和类型同时修改
alter table t1 change column name myname varchar(20);

5. 键的操作

1
2
3
4
5
6
7
8
9
10
11
12
-- 添加主键
alter table t1 add primary key(id);

-- 删除主键
alter table t1 drop primary key(id);
alter table t1 modify id int, drop primary key;

-- 添加外键
alter table 从表 add constraint 从键名称 foreign_key 从表(外键字段)

-- 删除外键
alter table table_name drop foreign_key key_name

6. 设置默认值

1
2
3
4
5
-- 设置默认值
alter table t1 alter age set default 18;

-- 删除默认值
alter table t1 alter age drop default;

7. SQL语句基础

SQL基础知识

  • select语句

格式：select 字段 from 表名； 全部字段可以用 *

  • where 用于限制查询的结果

格式：where 字段='xxx'; 查询条件> < >= <= = !=

  • 与(AND)或(OR)

  • 在(IN)不在(NOT IN)

  • 空(NULL)非空(NOT NULL)

  • 全部(ALL) 任一(ANY)

  • 在[a,b]之间

格式：between a and b

  • 排重DISTINCT

格式：select DISTINCT 字段 from 表名;

  • insert语句
1
2
3
-- 插入数据
insert into 表名(列名,列名) values(值,值);
insert into tb1(id, name) values(1, 'luck');
  • update语句
1
2
-- 更新数据
update tb1 set name = 'zhangsan' where id > 1;
  • delete语句
1
2
-- 删除数据
delete from tb1 where id=1 and name = 'luck';

8. 排序

  • ORDER BY语句

格式：select 字段 from 表名 where 条件 ORDER BY 字段;

  • 升序(ASC)与降序(DESC)

格式：select 字段 from 表名 where 条件 ORDER BY 字段 ASC;

格式：select 字段 from 表名 where 条件 ORDER BY 字段 DESC;

  • 多项排序

格式：select 字段 from 表名 where 条件 ORDER BY 字段 ASC|DESC，字段ASC|DESC;

1
2
3
4
5
6
7
8
-- 升序
select * from 表 order by 列 asc;

-- 降序
select * from 表 order by 列 desc;

-- 多项排序
select * from 表 order by 列1 desc,列2 asc;

9. 聚合函数

把 select 语句的查询结果汇聚成一个结果，这样的函数叫聚合函数。

  • 最大值(MAX)

  • 最小值(MIN)

  • 平均值(SVG)

  • 求和(SUM)

  • 统计数量(COUNT)

10. 分组

  • GROUP BY

格式：select 组函数 from 表 where 条件 group by 字段;
特别的group by 必须在where之后,order by之前

  • HAVING 组判断条件，它的真假决定一组数据是否返回

格式：select 组函数 from 表 where 条件 group by 字段 having 组判断条件;

1
2
3
4
5
6
7
8
9
select num from tb1 group by num;

select num, id from tb1 group by num, id;

select num, id from tb1 where id > 5 group by num, id order by id desc;

select num, id count(*), sum(score), max(score), min(score) from tb1 group by num, id;

select num from tb1 group by num having max(id) > 10;

11. 关联查询

  • JOIN：如果表中有至少一个匹配，则返回行

格式：select * from a join b on a.id=b.id;

  • 内连接(INNER JOIN)：只返回两个表中联结字段相等的行

格式：select * from a inner join b on a.id=b.id; 其中inner可以省略，等同于JOIN的用法

  • 左外连接(LEFT JOIN 或 LEFT OUTER JOIN)：即使右表中没有匹配，也从左表返回所有的行

格式：select * from a left outer join b on a.id=b.aid; 其中outer可忽略

  • 右外连接(RIGHT JOIN 或 RIGHT OUTER JOIN)：即使左表中没有匹配，也从右表返回所有的行

格式：select * from a right outer join b on a.id=b.aid; 其中outer可忽略

  • 全连接(FULL JOIN 或 FULL OUTER JOIN): 只要其中一个表中存在匹配，就返回行。相当于左外连接+右外链接，注意mysql不支持全连接

格式：select * from a full outer join b on a.id=b.id; 其中outer可忽略

  • 自连接：自连接意思是把自身表当成另外一张表看待，互相关联查询，连接方式可以使用以上的内外连接，这种连接方式可以解决很多奇怪的问题。

格式：SELECT ab.* from a ab,a ac where ab.id>ac.id;

  • 自然连接：是一种特殊的等值连接，要求两个关系表中进行连接的必须是相同的属性列（名字相同），无需添加连接条件，并且在结果中消除重复的属性列。
1
select * from table1 natural join table;

12. 组合

1
2
3
4
5
-- 组合，自动处理重合
select username from A union select name from B;

-- 组合，不处理重合
select username from A union all select name from B;

13. LIMIT的用法

格式：select * from student limit 10; 查询前10条数据，显示1-10条数据

格式：select * from student limit 1,10; 查询从第2行开始，累加10条id记录，共显示id为2….11

格式：select * from student limit 5,10; 查询从第6行开始向前加10条数据，共显示id为6,7….15

格式：select * from student limit i,n; i: 为查询结果的索引值(默认从0开始),当i=0时可省略i; n: 为查询结果返回的数量

14. case when

CASE WHEN 及 SELECT CASE WHEN的用法
SQL Case when 的使用方法

  • 简单Case函数
1
2
3
4
5
CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
    ELSE '其他' 
END
  • Case搜索函数
1
2
3
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

实例分析：

(1). 已知数据按照另外一种方式进行分组，分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*根据这个国家人口数据，统计亚洲和北美洲的人口数量*/
SELECT  SUM(population),
CASE country  WHEN '中国' THEN '亚洲'
  WHEN '印度' THEN '亚洲'
  WHEN '日本' THEN '亚洲'
  WHEN '美国' THEN '北美洲'
  WHEN '加拿大'  THEN '北美洲'
  WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END
FROM    Table_A
GROUP BY
CASE country 
WHEN '中国' THEN '亚洲'
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲'
WHEN '美国' THEN '北美洲'
WHEN '加拿大'   THEN '北美洲'
WHEN '墨西哥'   THEN '北美洲'
ELSE '其他' END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*判断工资的等级，并统计每一等级的人数*/
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)  FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;

(2). 用一个SQL语句完成不同条件的分组

1
2
3
4
5
/*按照国家和性别进行分组*/
SELECT country, 
SUM( CASE WHEN sex = '1' THEN  population ELSE 0 END),  --男性人口
SUM( CASE WHEN sex = '2' THEN  population ELSE 0 END)   --女性人口
FROM  Table_A  GROUP BY country;

(3). 在Check中使用Case函数

1
2
3
4
5
6
/*公司A，这个公司有个规定，女职员的工资必须高于1000块*/
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
THEN CASE WHEN salary > 1000
THEN 1 ELSE 0 END
ELSE 1 END = 1 )

(4). 根据条件有选择的UPDATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/* 1.工资5000以上的职员，工资减少10% */
UPDATE Personnel  SET salary = salary * 0.9  WHERE salary >= 5000;

/* 2.工资在2000到4600之间的职员，工资增加15% */
UPDATE Personnel  SET salary = salary * 1.15 WHERE salary >= 2000 AND salary < 4600;

/* 3.如果满足条件1和条件2，顺序执行则会出现问题。
假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。
接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内，需增加15%，
最后这个人的工资结果是5175,不但没有减少，反而增加了。
如果要是反过来执行，那么工资4600的人相反会变成减少工资。 
这里用到case when 进行不同条件的更新*/
UPDATE Personnel
SET salary =
CASE WHEN salary >= 5000  　                THEN salary * 0.9
     WHEN salary >= 2000 AND salary < 4600  THEN salary * 1.15
ELSE salary END;  -- 这行else必须写，不写会导致不符合这两个条件的工资会变成NULL
1
2
3
4
5
/* 把主键a和b相互交换 */
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'  THEN 'b'
WHEN p_key = 'b'  THEN 'a'  ELSE p_key END
WHERE p_key IN ('a', 'b');
  • 检查两个表数据是否一致

Case函数不同于DECODE函数。在Case函数中，可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS，可以进行子查询，从而 实现更多的功能。

1
2
3
4
5
6
7
8
9
10
--使用IN的时候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )  THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;

--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B  WHERE tbl_A.keyCol = tbl_B.keyCol )  THEN 'Matched'  ELSE 'Unmatched' END Label
FROM tbl_A;
  • 在Case函数中使用合计函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/* Studentclass
+------+----------+--------------+-----------------+
|std_id| class_id |  class_name  |  main_class_flg |
+------+----------+--------------+-----------------+
| 100  |    1     |     经济学    |        Y        |
| 100  |    2     |     历史学    |        N        |
| 200  |    2     |     历史学    |        N        |
| 200  |    3     |     考古学    |        Y        |
| 200  |    4     |     计算机    |        N        |
| 300  |    4     |     计算机    |        N        |
| 400  |    5     |      化学     |        N        |
| 500  |    6     |      数学     |        N        |
+------+----------+---------------+----------------+
*/

--条件1：只选择了一门课程的学生, 返回那门课程的ID
SELECT std_id, MAX(class_id) AS main_class  FROM Studentclass  GROUP BY std_id  HAVING COUNT(*) = 1;

--条件2：选择多门课程的学生, 返回所选的主课程ID
SELECT std_id, class_id AS main_class  FROM Studentclass  WHERE main_class_flg = 'Y';

--使用Case函数,满足以上两个条件
SELECT  std_id, 
CASE
WHEN COUNT(*) = 1                   THEN MAX(class_id)
ELSE
MAX(CASE WHEN main_class_flg = 'Y'  THEN class_id  ELSE NULL END)
END AS main_class
FROM Studentclass  GROUP BY std_id;
  • select case when

select 与 case结合使用最大的好处有两点，一是在显示查询结果时可以灵活的组织格式，二是有效避免了多次对同一个表或几个表的访问。

1
2
3
4
5
6
7
8
9
10
11
/* 例如表 students(id, name ,birthday, sex, grade)，要求按每个年级统计男生和女生的数量各是多少，统计结果的表头为，年级，男生数量，女生数量。如果不用select case when，为了将男女数量并列显示，统计起来非常麻烦，先确定年级信息，再根据年级取男生数和女生数，而且很容易出错。*/

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1
                            ELSE NULL
                        END) 男生数,
               COUNT (CASE WHEN sex = 2 THEN 1
                            ELSE NULL
                       END) 女生数
FROM students

GROUP BY grade;

15. mysql 常用数据类型

  • 字符串类型

    • CHAR：0-255 bytes, 存定长字符串
    • VARCHAR：0-65535 bytes, 存不定长字符串
    • TINYBLOB: 0-255 bytes, 不超过 255 个字符的二进制字符串
    • TINYTEXT: 0-255 bytes, 短文本字符串
    • BLOB: 0-65535 bytes, 二进制形式的长文本数据
    • TEXT: 0-65535 bytes, 长文本数据
    • MEDIUMBLOB: 0-16777215 bytes, 二进制形式的中等长度文本数据
    • MEDIUMTEXT: 0-16777215 bytes, 中等长度文本数据
    • LONGBLOB: 0-4294967295 bytes, 二进制形式的极大文本数据
    • LONGTEXT: 0-4294967295 bytes, 极大文本数据

  • 数值类型

    • TINYINT: 1 bytes, 有符号范围(-128，127)、无符号范围(0，255)
    • SMALLINT: 2 bytes, 有符号范围(-32768，32767)、无符号范围(0，65535)
    • MEDIUMINT: 3 bytes, 有符号范围(-8388608，8388607)、无符号范围(0，16777215)
    • INT/INTEGER: 4 bytes, 有符号范围(-2147483648，2147483647)、无符号范围(0，4294967295)
    • BIGINT: 8 bytes, 有符号范围(-9223372036854775808，9223372036854775807)、无符号范围(0，18446744073709551615)
    • FLOAT: 4 bytes, 有符号范围[(-3.402823466E+38，-1.175494351 E-38)，0，(1.175494351E-38，3.402823466351E+38)]、无符号范围[0，(1.175494351E-38，3.402823466E+38)]
    • DOUBLE: 8 bytes, 有符号范围[(-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.2250738585072014E-308，1.797 6931348623157E+308)]、无符号范围[0，(2.2250738585072014 E-308，1.7976931348623157E+308)]
    • DECIMAL: DECIMAL(M, D), 若M>D，这为大小为M+2，否则为D+2

  • 日期类型

    • DATE: 3 bytes, YYYY-MM-DD（1000-01-01/9999-12-31）
    • TIME: 3 bytes, HH:MM:SS（'-838:59:59'/'838:59:59'）
    • YEAR: 1 bytes, YYYY（1901/2155）
    • DATETIME: 8 bytes, YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
    • TIMESTAMP: 4 bytes, YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

16. MySQL读取数据的基本单位

在 MySQL 中数据读取的基本单位都是页，InnoDB 中页的默认大小是 16KB。

17. 为什么mysql事务回滚后，自增ID依然自增

因为innodb的auto_increament的计数器记录的当前值是保存在存内 存中的，并不是存在于磁盘上，当mysql server处于运行的时候，这个计数值只会随着insert改增长，不会随着delete而减少。

当mysql server启动时，当我们需要去查询auto_increment计数值时，mysql便会自动执行：SELECT MAX(id) FROM 表名 FOR UPDATE;语句来获得当前auto_increment列的最大值，然后将这个值放到auto_increment计数器中。所以就算Rollback MySQL的auto_increament计数器也不会作负运算。

18. mysql的主从复制的实现过程

MySQL主从复制的实现过程

  • 主从复制的作用

    • 主数据库出现问题，可以切换到从数据库
    • 可以进行数据库层面的读写分离
    • 可以在从数据库上进行日常备份

  • 主从复制的过程

    • (1). master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中.
    • (2). salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志（Realy log）中。
    • (3). SQL Thread会读取中继日志（Realy log），并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

  • 具体步骤

    • 准备两台服务器，安装mysql

      主库是：192.168.x.x master
      从库是：192.168.x.xx slave

    • 主库操作：

      • （1）开启binlog功能，修改vim /etc/my.cnf配置
      1
      2
      3
      4
      5
      6
      7
      8
      [mysqld]
      <!-- 指明主库的身份id为1 -->
      server-id=1
      <!-- 指明binlog的日志名 -->
      log-bin=/var/log/mysql/mysql-bin
      <!-- 为了在使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性 -->
      innodb_flush_log_at_trx_commit = 1
      sync_binlog = 1
      • （2）创建日志目录并赋予权限，重启mysql，使得binlog生效
      1
      2
      3
      4
      5
      6
      <!-- 创建日志目录 -->
      mkdir /var/log/mysql
      <!-- 赋予权限 -->
      chown mysql.mysql /varlog/mysql
      <!-- mysqld重启服务 -->
      systemctl restart mysqld
      • （3）登录mysql，检查主库的状态
      1
      show master status;
      • （4）创建一个用户，用于进行主从同步
      1
      create user 'database_backup'@'%' identified by 'database_backup_666';
      • （5）授予账号权限，授予一个从库的身份权限
      1
      2
      grant replication slave on . to 'database_backup'@'%';
      flush privileges;
      • （6）锁定mysql的表，防止数据写入
      1
      2
      flush table with read lock;
      flush privileges;
      • （7）这一步需要等下面到处数据完成后，然后解除锁表
      1
      2
      unlock tables;
      flush privileges;

    • 导出数据

      • 导出当前的数据，用于slave机器导入数据，保证在一个起始点
      1
      mysqldump -u root -p --all-databases > /data/db.dump;
      • 将此db.dump文件远程传输给 slave机器，用于导入
      1
      scp /data/db.dump root@192.168.x.x:/tmp/

    • 从库操作：

      • （1）修改vim /etc/my.cnf配置
      1
      2
      [mysqld]
      server-id=2
      • （2）重启mysql，使得binlog生效
      1
      2
      <!-- mysqld重启服务 -->
      systemctl restart mysqld
      • （3）登录slave从库，导入主库的数据信息
      1
      source /tmp/db.dump;
      • （4）从数据库上进行复制行为的相关配置
      1
      2
      3
      4
      5
      change master to master_host='192.168.x.xx',
      master_user='database_backup',
      master_password='database_backup_666',
      master_log_file='mysql-bin.000001',  -- 根据show master status给出的状态
      master_log_pos=666;  -- 根据show master status给出的状态
      • （5）启动slave线程
      1
      slave start;

      -（6）检查slave状态，检查两条参数，如果都是yes，即主从ok

      1
      show slave status;

19. mysql高并发环境解决方案

  • 水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力；
  • 集群方案，解决DB宕机带来的单点DB不能访问问题；
  • 读写分离策略，极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力；

Redis数据库（端口：6379）

1. 什么是Redis

Redis 是完全开源免费的，遵守BSD协议，是一个高性能的key-value数据库。

Redis 与其他 key - value 缓存产品有以下三个特点：

  • Redis支持数据的持久化，可以将内存中的数据保存在磁盘中，重启的时候可以再次加载进行使用。
  • Redis不仅仅支持简单的key-value类型的数据，同时还提供list，set，zset，hash等数据结构的存储。
  • Redis支持数据的备份，即master-slave模式的数据备份。

Redis的优势：

  • 性能极高：Redis能读的速度是110000次/s,写的速度是81000次/s 。
  • 丰富的数据类型：Redis支持二进制案例的 Strings, Lists, Hashes, Sets 及 Ordered Sets 数据类型操作。
  • 原子性：Redis的所有操作都是原子性的，意思就是要么成功执行要么失败完全不执行。单个操作是原子性的。多个操作也支持事务，即原子性，通过MULTI和EXEC指令包起来。
  • 丰富的特性：Redis还支持 publish/subscribe, 通知, key 过期等等特性。

Redis与其他key-value存储有什么不同

  • Redis有着更为复杂的数据结构并且提供对他们的原子性操作，这是一个不同于其他数据库的进化路径。Redis的数据类型都是基于基本数据结构的同时对程序员透明，无需进行额外的抽象。

  • Redis运行在内存中但是可以持久化到磁盘，所以在对不同数据集进行高速读写时需要权衡内存，因为数据量不能大于硬件内存。在内存数据库方面的另一个优点是，相比在磁盘上相同的复杂的数据结构，在内存中操作起来非常简单，这样Redis可以做很多内部复杂性很强的事情。同时，在磁盘格式方面他们是紧凑的以追加的方式产生的，因为他们并不需要进行随机访问。

以上来源于《菜鸟教程–Redis简介》

2. redis的数据类型

  • 字符串(String)

string类型是二进制安全的，可以包含任何数据，比如JPG图片或者序列化对象。string类型是Redis最基本的数据类型，一个键最大能存储512MB。

1
2
3
4
# 设置字符串类型
127.0.0.1:6379> set key value
# 读取字符串类型
127.0.0.1:6379> get key value
  • 哈希(Hash)

hash存的是字符串和字符串值之间的映射，是一个键值(key=>value)对集合，比如用户存储姓名、年龄等信息，非常适合存储对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 建立哈希，并赋值
127.0.0.1:6379> hmset name key1 value1 key2 value2 ...
127.0.0.1:6379> hmset user:001 username liming pwd 123456 age 18

# 列出哈希的内容
127.0.0.1:6379> hgetall user:001
# 输出结果
"username"
"liming"
"pwd"
"123456"
"age"
"18"

# 更改哈希中的某一个值
127.0.0.1:6379> hgetall user:001 pwd aaaaaa
# 查看更改后的哈希的内容
127.0.0.1:6379> hgetall user:001
# 输出结果
"username"
"liming"
"pwd"
"aaaaaa"
"age"
"18"
  • 列表(List)

Redis 列表是简单的字符串列表，按照插入顺序排序，底层实现是链表，对于一个具有几百上千万个元素的list来说，在头部和尾部插入一个新元素，其时间复杂度是常数级别的，插入速度也是不变的。弊端就是链表型的list比数组型的list定位更慢。list主要应用在消息队列上，可以确保先后顺序，还可以利用lrange实现分页功能，博客系统中评论也可以存入一个单独的list中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 头部插入元素
127.0.0.1:6379> lpush mylist "1"

# 尾部插入元素
127.0.0.1:6379> rpush mylist "2"

# 读取list中的元素
127.0.0.1:6379> lrange mylist index1 index2 ...
# 读取list中编号0到倒数第一个元素
127.0.0.1:6379> lrange mylist 0 -1

# 删除元素
127.0.0.1:6379> lrem mylist index

# 获取list长度
127.0.0.1:6379> llen mylist
  • 集合(Set)

Redis的Set是string类型的无序且元素不重复的集合，集合是通过哈希表实现的，所以添加，删除，查找的复杂度都是O(1)，同时可以进行集合运算：取交集、取并集、取差集等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 添加元素
127.0.0.1:6379> sadd myset "1"
# 读取元素
127.0.0.1:6379> smembers myset
# 删除元素
127.0.0.1:6379> srem myset "1"
# 判断元素是否存在，存在返回1，否则返回0
127.0.0.1:6379> sismember myset "1"
# 取交集
127.0.0.1:6379> sinter set1 set2
# 取并集
127.0.0.1:6379> sunion set1 set2
# 取差集
127.0.0.1:6379> sdiff set1 set2
  • 有序集合(sorted set)

有序集合中，每个元素都会关联一个double类型的分数(score)，redis正是通过分数来为集合中的元素进行从小到大的排序。有序集合中的元素是唯一的，但分数(score)可以重复。

1
2
3
4
5
6
7
8
9
10
# 添加元素，赋予序号1
127.0.0.1:6379> zadd myzset 1 aa
# 添加元素，赋予序号2
127.0.0.1:6379> zadd myzset 2 bb
# 添加元素，赋予序号3
127.0.0.1:6379> zadd myzset 3 cc
# 读取元素，并输出序号（即score）
127.0.0.1:6379> zrange myzset 0 -1 with scores
# 得出排名
127.0.0.1:6379> zrange myzset cc

3. 两种持久化方式

持久化就是把内存的数据写到磁盘中去，防止服务宕机了内存数据丢失。

  • RDB(Redis DataBase)

RDB是Redis默认的方式，就是把某一时刻的数据以快照的方式存储到磁盘等介质上。Redis在进行持久化过程中，会调用rdbSave()函数，把数据写到临时文件中，等持久化过程都结束了，才调用rdbLoad()函数加载上次持久化好的文件，并用新的临时文件进行覆盖，快照文件总是完整的。

Redis会单独创建一个子进程进行持久化，而主进程是不会进行任何IO操作，确保了Redis极高的性能。

如果需要进行大规模数据的恢复，且对于数据恢复的完整性不是非常敏感，那RDB方式要比AOF方式更加的高效。但是如果对数据的完整性非常敏感，RDB方式安全性低一些，可能在故障时丢失数据。

  • AOF(Append Only File)

Append Only File意思是只允许最佳不允许改写的文件，AOF的方式是将执行过程的所有写指令记录下来，在数据回复时按照冲钱到后顺序在将指令执行一遍。通过配置redis.conf中的appendonly yes就可以打开AOF功能，每当服务器执行定时任务或者函数时，都会调用flushAppendOnlyFile()函数，根据条件将aof_buf中的缓存写到AOF文件中，而后调用fsync()或 fdatasync()函数将AOF保存到磁盘中。其中AOF默认持久化的策略是每秒钟fsync一次，所以对数据安全更加有保障。

因为采用了追加的方式，不做处理的话，AOF文件将会越来越大，所以要使用AOF文件重写机制，当AOF文件大小超过说设定的阈值时，Redis就会启动AOF文件的内容压缩，只保留可以恢复数据的最小指令集。如100条INCR指令可以合并层一条SET指令。

在进行AOF重写时，仍然先写入临时文件，全部完成后再进行替换，因此断电、磁盘满等问题都不会影响AOF文件的可用性。

在同样的数据规模下，AOF文件比RDB文件的体积更大，且AOF方式恢复数据的速度也比RDB方式慢。

4. RDB与AOF比较

  • AOF文件比RDB更新更加频繁，因此AOF文件更大
  • AOF方式比RDB方式数据安全性更高
  • RDB性能比AOF更好
  • 优先使用AOF还原数据

5. 如果AOF文件出现被写坏的情况，该怎么处理

出现AOF文件被写坏的情况，Redis并不会冒然加载这个有问题的AOF文件，而是报错退出，这时可以用过以下步骤来修复出错文件：

  • 备份被写坏的AOF文件
  • 运行redis-check-aof -fix进行修复
  • 用diff -u比两个文件的差异，定位问题点并修复
  • 重启redis，加载修复后的AOF文件

6. redis 和 memcached 的主要区别

redis和memcached的区别详解

  • Redis支持服务器端的数据操作：相比于memcached，redis拥有更多是数据结构，所以支持更多的数据操作，而在Memcached里，你需要将数据拿到客户端来进行类似的修改再set回去。
  • 内存使用效率对比：使用简单的key-value存储的话，Memcached的内存利用率更高，而如果Redis采用hash结构来做key-value存储，由于其组合式的压缩，其内存利用率会高于Memcached。
  • 性能对比：redis只支持单核，memcached可以使用多核，所以平均每一个核上 redis 在存储小数据时比 memcached 性能更高，但是在大数据存储上的处理memcached性能更高。
  • redis内部使用的是文件事件处理器file event handler，是单线程模型，处理效率高。文件事件处理器的结构包含多个socket、IO多路复用程序（非阻塞）、文件事件分派器、事件处理器四个部分。
  • 存储数据安全：Redis拥有持久化机制，而memcached没有。
  • 应用场景：Redis除了作为NoSQL数据库使用外，还能做消息队列、数据堆栈和数据缓存等；而Memcached适用于缓存SQL语句、数据集、用户临时性数据、延迟查询数据和session等。

7. 事务处理

Redis 事务可以一次执行多个命令，并且带有以下三个重要的保证：

  • 批量操作在发送 EXEC 命令前被放入队列缓存
  • 收到 EXEC 命令后进入事务执行，事务中任意命令执行失败，其余的命令依然被执行
  • 在事务执行过程，其他客户端提交的命令请求不会插入到事务执行命令序列中

一个事务从开始到执行会经历开始事务、命令入队、执行事务三个阶段。

redis 通过 MULTI、EXEC、DISCARD、WATCH四个指令实现事务处理

  • MULTI用来组装一个事务
  • EXEC用来执行一个事务
  • DISCARD用来取消一个事务
  • WATCH用来监视一些key，一旦这些key在事务执行之前被改变，则取消事务的执行

8. Redis常见的架构模式

(1) 单机模式

  • 内存容量有限
  • 处理能力有限
  • 无法高可用

(2) 主从复制模式

MySQL一样，redis是支持主从同步的，而且也支持一主多从以及多级从结构。一是为了纯粹的冗余备份，二是为了提升读性能。

通常我们会设置一个主节点，N个从节点，默认情况下，主节点负责处理使用者的IO操作，而从节点则会对主节点的数据进行备份，并且也会对外提供读操作的处理。

主从架构中，可以考虑关闭主服务器的数据持久化功能，只让从服务器进行持久化，这样可以提高主服务器的处理性能。

Redis的主从同步是异步进行的，不会阻塞主节点，主节点和从节点是读写分离的。

全量同步：Redis全量复制一般发生在slave初始化阶段，这时，需要将Master上所有数据都复制一份。具体步骤如下：

  • 从服务器连接主服务器，发送SYNC命令
  • 主服务器接收到SYNC命令后，开始执行BGSAVE命令生成RDB文件并使用缓冲区记录此后执行的所有写命令
  • 主服务器BGSAVE执行完后，向所有从服务器发送快照文件，并在发送期间继续记录被执行的写命令
  • 从服务器收到快照文件后丢弃所有旧数据，载入收到的快照
  • 主服务器快照发送完毕后开始向从服务器发送缓冲区中的写命令
  • 从服务器完成对快照的载入，开始接受命令请求 ，并执行来自主服务器缓冲区的写命令

增量同步：通常情况下，Master每执行一个写命令就回向Slave发送相同的命令，然后slave接收并执行。只有slave第一次连接是全量同步，断线重连有可能是全量同步，也有可能是增量同步；除此之外都是增量同步。

Redis主从复制模式的优点：

  • 解决数据备份问题
  • 实现读写分离，降低读压力，提高服务器性能

Redis主从复制模式的缺点：

  • 无法实现高可用，一旦出现故障需要人工进行故障转移
  • 无法实现动态扩容，受单击控制

(3) 哨兵模式

sentinel是基于主从模式进行优化，能够为Redis提供高可用性。在实际生产中，服务器难免遇到服务器宕机、停电、硬件损坏等突发状况，哨兵模式可以一定程度上帮助规避这些意外情况导致的灾难性的后果。核心还是主从复制，增加了由一个或多个Sentinel实例组成的Sentinel系统，监视任意多个主服务器以及这些主服务器属下的所有从服务器，当主服务器宕机导致不可写或者下线状态时，自动将主服务器下的某个服务器升级为新的主服务器。这样，就保证了Redis的高可用，规避数据丢失风险。

sentinel的特点：

  • 监控（Monitoring）：它会监听不断检查主服务器和从服务器之间是否在正常工作。
  • 通知（Notification）：当监控的某个Redis服务器出现问题时，Sentinel可以通过API向管理管或者其他应用程序发送通知。
  • 故障自动转移（Automatic failover）：当一个主服务器出现故障不能正常工作时，Sentinel会在所有从服务器中选择一个作为新的主服务器，实现故障自动转移。
  • 提供主服务器地址：能够向当前使用者提供当前主节点的地址，特别是在故障自动转移后，使用者不用做任何修改就可以知道当前的主节点地址。
  • sentinel也可以集群，部署多个哨兵，sentinel可以通过发布和订阅(pub/sub)来自动发现Redis集群上的其他Sentinel。sentinel在发现其它sentinel进程后，会将其放入一个列表中，这个列表存储了所有已被发现的sentinel。

Redis哨兵模式的优点：

  • 保证高可用
  • 监控各个节点
  • 自动故障迁移

Redis哨兵模式的缺点：

  • 切换需要时间，可能存在丢失数据风险
  • 从节点下线，故障不能自动转移
  • 无法实现动态扩容

(4) 集群模式

Redis 集群

Redis 集群是一个提供在多个Redis间节点间共享数据的程序集。Redis集群并不支持处理多个keys的命令，因为这需要在不同的节点间移动数据，从而达不到像Redis那样的性能，在高负载的情况下可能会导致不可预料的错误。

Redis 集群通过分区来提供一定程度的可用性，在实际环境中当某个节点宕机或者不可达的情况下继续处理命令。

Redis 集群没有使用一致性hash, 而是引入了 哈希槽的概念。Redis 集群有16384个哈希槽,每个key通过CRC16校验后对16384取模来决定放置哪个槽。集群的每个节点负责一部分hash槽，比如当前集群有3个节点，那么节点 A 包含 0 到 5500号哈希槽；节点 B 包含5501 到 11000 号哈希槽；节点 C 包含11001 到 16384号哈希槽；这种结构很容易添加或者删除节点，比如如果我想新添加个节点D，我需要从节点 A, B, C中得部分槽到D上。如果我想移除节点A，需要将A中的槽移到B和C节点上，然后将没有任何槽的A节点从集群中移除即可。由于从一个节点将哈希槽移动到另一个节点并不会停止服务，所以无论添加删除或者改变某个节点的哈希槽的数量都不会造成集群不可用的状态。

一致哈希算法根据数据的key值计算映射位置时和所使用的节点数量有非常大的关系。一致哈希分区的实现思路是为系统中每个节点分配一个token，范围一般在0~2^32，这些token构成一个哈希环，数据读写执行节点查找操作时，先根据key计算hash值，然后顺时针找到第一个大于等于该hash值的token节点，需要操作的数据就保存在该节点上。

Redis 一致性保证 Redis 并不能保证数据的强一致性。这意味这在实际中集群在特定的条件下可能会丢失写操作。

投票过程是集群中所有master参与，如果半数以上master节点与master节点通信超时(cluster-node-timeout)，认为当前master节点挂掉。这时如果当前master没有slave，集群就进入fail状态；当集群超过半数以上master挂掉，无论是否有slave，集群进入fail状态。

一般集群建议搭建三主三从架构，三主提供服务，三从提供备份功能。

Redis集群模式的优点：

  • 有效解决了Redis在分布式方面的需求
  • 遇到单击内存，并发和流量瓶颈问题时，可采用Cluster方案达到负载均衡的目的
  • 可实现动态扩容，可线性扩展到1000个节点，节点可以动态的添加或删除
  • P2P模式，无中心化
  • 自动故障转移，节点间通过Gossip协议同步节点信息，用投票机制完成slave到maser的角色提升
  • 数据按照Slot存储分布在多个节点，节点间数据共享，可动态调整数据分布。
  • 高可用性，部分节点不可用时，集群仍然可用，通过增加slave做备份数据副本

Redis集群模式的缺点：

  • 架构比较新，最佳时间比较少
  • 为了性能提升，客户端需要缓存路由表信息
  • 节点发现、reshard操作不够自动化

9. Redis如何实现分布式锁

使用 setnx 实现枷锁，可以同时通过 expire 添加超时时间

1
2
3
4
5
6
7
8
9
# 当key不存在时，为key设置一个值，否则在一定时间内进行尝试
def acquire_lock(conn, lockname, acquire_timeout=10):
    identifier = str(uuid.uuid4())
    end = time.time() + acquire_timeout
    while time.time() < end:
        if conn.setnx('lock:' + lockname, identifier):
            return identifier
        time.sleep(0.001)
    return False

锁的 value 值可以使用一个随机的 uuid 或者特定的命名

释放锁的时候，通过 uuid 判断是否是该锁，是则执行 delete 释放锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def release_lock(conn, lockname,identifier):
    pipe = conn.pipeline(True)
    lockname = 'lock:' + lockname
    while True:
        try:
            pipe.watch(lockname)
            if pipe.get(lockname) == identifier:
                pipe.multi()
                pipe.delete(lockname)
                pipe.execute()
                return True
            pipe.unwatch()
            break
        except redis.exceptions.WatcjError:
            pass
    return False

10. 缓存使用场景

缓解关系数据库并发访问的压力： 热点数据

减少响应时间：内存IO速度比磁盘快

提升吞吐量：redis等内存数据库单机就可以支撑很大并发

11. 常用的缓存使用模式

  • Cache-Aside

Cache-Aside可能是项目中最常见的一种模式。它是一种控制逻辑都实现在应用程序中的模式。缓存不和数据库直接进行交互，而是由应用程序来同时和缓存以及数据库打交道，即同时更新缓存和数据库。

应用场景：应用于缓存不支持Read-Through/Write-Through的系统。

优点：缓存仅仅保存被请求的数据，属于懒加载模式，避免了任何数据都被写入缓存造成缓存频繁的更新。

缺点：当发生缓存未命中的情况时，则会比较慢，因为要经过三个步骤，查询缓存、从数据库读取、写入缓存；复杂的逻辑都在应用程序中，如果实现微服务，多个微服务中会有重复的逻辑代码。

  • Read-Through / Write Through

这种模式中，应用程序将缓存作为主要的数据源，而数据库对于应用程序是透明的，更新数据库和从数据库的读取的任务都交给缓存来代理了，所以对于应用程序来说，简单很多。先更新缓存，缓存负责同步更新数据库。

应用场景：写入之后经常被读取的应用。

优点：缓存不存在脏数据；相比较Cache-Aside懒加载模式，读取速度更高，因为较少因为缓存未命中而从数据库中查找；应用程序的逻辑相对简单。

缺点：对于总是写入却很少被读取的应用，那么Write-Through会非常浪费性能，因为数据可能更改了很多次，却没有被读取，白白的每次都写入缓存造成写入延迟。

  • Write-Back

又叫做Write-Behind，和Write-Through写入的时机不同，Write-Back将缓存作为可靠的数据源，每次都只写入缓存，而写入数据库则采用异步的方式，比如当数据要被移除出缓存的时候再存储到数据库或者一段时间之后批量更新数据库。即先更新缓存，缓存定期异步更新数据库。

应用场景：读写效率都非常好，写的时候因为异步存储到数据库，提升了写的效率，适用于读写密集的应用。

优点：写入和读取数据都非常的快，因为都是从缓存中直接读取和写入；对于数据库不可用的情况有一定的容忍度，即使数据库暂时不可用，系统也整体可用，当数据库之后恢复的时候，再将数据写入数据库。

缺点：有数据丢失的风险，如果缓存挂掉而数据没有及时写到数据库中，那么缓存中的有些数据将永久的丢失了。

  • Write-Around

和Write-Through不同，更新的时候只写入数据库，不写入缓存，结合Read-Through或者Cache-Aside使用，只在缓存未命中的情况下写缓存。

应用场景：适合于只写入一次而很少被读取的应用。

优点：相比较Write-Through写入的时候的效率较高，如果数据写入后很少被读取，缓存也不会被没用到的数据占满。

缺点：如果数据会写入多次，那么可能存在缓存和数据库不一致

12. 什么是缓存穿透

一般的缓存系统，都是按照key去缓存查询，如果不存在对应的value，就应该去后端系统查找（比如DB）。一些恶意的请求会故意查询不存在的key,请求量很大，就会对后端系统造成很大的压力。这就叫做缓存穿透。

解决：对查询结果为空的情况也进行缓存，缓存时间设置短一点，或者该key对应的数据insert了之后清理缓存；对一定不存在的key进行过滤，可以把所有的可能存在的key放到一个大的Bitmap中，查询时通过该bitmap过滤。

13. 什么是缓存击穿

缓存击穿是指缓存中没有但数据库中有的数据（一般是缓存时间到期，热点数据key失效），这时由于并发用户特别多，同时读缓存没读到数据，又同时去数据库去取数据，引起数据库压力瞬间增大，造成过大压力。

解决：设置热点数据永远不过期；分布式锁；异步后台更新，后台任务针对过期的key自动刷新。

14. 什么是缓存雪崩

当缓存服务器重启或者大量缓存集中在某一个时间段失效，这样在失效的时候，会给后端系统带来很大压力，导致系统崩溃。和缓存击穿不同的是，缓存击穿指并发查同一条数据，缓存雪崩是不同数据都过期了，很多数据都查不到从而查数据库。

解决：在缓存失效后，通过加锁或者队列来控制读数据库写缓存的线程数量，比如对某个key只允许一个线程查询数据和写缓存，其他线程等待；做二级缓存，A1为原始缓存，A2为拷贝缓存，A1失效时，可以访问A2，A1缓存失效时间设置为短期，A2设置为长期；不同的key，设置不同的过期时间，让缓存失效的时间点尽量均匀。

架构层面解决： 提升系统可用性， 监控、报警完善

15. 连接Redis

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# StrictRedis方式
from redis import StrictRedis
redis = StrictRedis(host='localhost', prot=6379, db='test', password='123456')

# 设置一个字符串值
redis.set('name', 'Bob')
print(redis.get('name'))
# 结果
'''
b'Bob'
'''

# ConnectionPool连接方式
from redis import StrictRedis, ConnectionPool
pool = ConnectionPool(host='localhost', port=63799, db='test', password='123456')
redis = StrictRedis(connection_pool=pool)

# ConnectionPool支持通过URL来构建
from redis import StrictRedis, ConnectionPool
url = 'redis://:123456@localhost:6379/test'
pool = ConnectionPool.from_url(url)
redis = StrictRedis(connect_pool=pool)

16. Redis异步队列

一般使用list结构作为队列，rpush生产消息，lpop消费消息，当lpop没有消息的时候，要适当sleep一会再重试。

缺点：在消费者下线的情况，生产者的消息会丢失，得使用专业的消息队列如Rabbitmq。

17. 生产一次消费多次

使用pub/sub主题订阅者模式，可以实现1：N的消息队列。

18. Redis分区

分区是分割数据到多个Redis实例的处理过程，因此每个实例只保存key的一个子集。

分区的优势：

  • 通过利用多台计算机内存的和值，允许我们构造更大的数据库。
  • 通过多核和多台计算机，允许我们扩展计算能力；通过多台计算机和网络适配器，允许我们扩展网络带宽。

分区的不足：

  • 涉及多个key的操作通常是不被支持的，如当两个set映射到不同的redis实例上时，你就不能对这两个set执行交集操作。
  • 涉及多个key的redis事务不能使用。
  • 当使用分区时，数据处理较为复杂，比如你需要处理多个rdb/aof文件，并且从多个实例和主机备份持久化文件。
  • 增加或删除容量也比较复杂。redis集群大多数支持在运行时增加、删除节点的透明数据平衡的能力，但是类似于客户端分区、代理等其他系统则不支持这项特性。

分区类型：

  • 范围分区：最简单的分区方式是按范围分区，就是映射一定范围的对象到特定的Redis实例。
  • 哈希分区：另外一种分区方法是hash分区。这对任何key都适用，也无需是object_name: 这种形式。

18. mySQL里有2000w数据，redis中只存20w的数据，如何保证redis中的数据都是热点数据

相关知识：redis 内存数据集大小上升到一定大小的时候，就会施行数据淘汰策略（回收策略）。redis 提供 6种数据淘汰策略：
volatile-lru：从已设置过期时间的数据集（server.db[i].expires）中挑选最近最少使用的数据淘汰
volatile-ttl：从已设置过期时间的数据集（server.db[i].expires）中挑选将要过期的数据淘汰
volatile-random：从已设置过期时间的数据集（server.db[i].expires）中任意选择数据淘汰
allkeys-lru：从数据集（server.db[i].dict）中挑选最近最少使用的数据淘汰
allkeys-random：从数据集（server.db[i].dict）中任意选择数据淘汰
no-enviction（驱逐）：禁止驱逐数据

MongoDB数据库（端口：27017）

1. MongoDB常用的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
import pymongo
client = pymongo.MongoClient(host='localhost', port=27017)

# 指定数据库test
db = client.test

# 指定集合
collection = db.students

# 插入数据
student1 = {
    'id': '20180101',
    'name': 'zhangsan',
    'age': 20,
    'gender': 'male'
}
student2 = {
    'id': '20180102',
    'name': 'lisi',
    'age': 20,
    'gender': 'male'
}
result = collection.insert([student1, student2])
result = collection.insert_one(student1)
result = collection.insert_many([student1, student2])

# 查询
result = collection.find_one({'name': 'zhangsan'})
results = collection.find({'age': 20})
for res in results:
    print(res)

# 年龄小于20的数据
results = collection.find({'age': {'$lt': 20}})
# 年龄小于等于20的数据
results = collection.find({'age': {'$lte': 20}})
# 年龄大于20的数据
results = collection.find({'age': {'$gt': 20}})
# 年龄大于等于20的数据
results = collection.find({'age': {'$gte': 20}})
# 年龄不等于20的数据
results = collection.find({'age': {'$ne': 20}})
# 年龄在[18, 25]范围内的数据
results = collection.find({'age': {'$in': [18,23]}})
# 年龄不在[18, 25]范围内的数据
results = collection.find({'age': {'$nin': [18,23]}})

# 正则查询，匹配以M开头的数据
results = collection.find({'name': {'$regex': '^M.*'}})

# 计数
num = collection.find({'age': 20}).count()

# 根据字段，升序ASCENDING/降序DESCENDING
results = collection.find().sort('name', pymongo.ASCENDING)
print(result['name'] for result in results)

# 偏移位置，skip(n)忽略前n个元素
results = collection.find().sort('name', pymongo.ASCENDING).skip(2)
print(result['name'] for result in results)

# 限制返回结果个数limit(n)，从左到右
results = collection.find().sort('name', pymongo.ASCENDING).limit(2)
print(result['name'] for result in results)

# 更新update()
condition = {'name': 'wangwu'}
student = collection.find_one(condition)
student['age'] = 25
result = collection.update(condition, student)

# $set操作符只更新student字典内存在的字段，其他字段不更新也不删除
result = collection.update(condition, {'$set': student})

# update_one()
result = collection.update_one(condition, {'$set': student})
# 查看获得匹配的数据条数和影响的数据条数
print(result.matched_count, result.modified_count)

# update_many()
condition = {'age': {'$gt': 20}}
result = collection.update_many(condition, {'$inc': {'age': 18})
# 查看获得匹配的数据条数和影响的数据条数
print(result.matched_count, result.modified_count)

# 删除
result = collection.remove({'name': 'zhangsan'})
result = collection.delete_one({'name': 'zhangsan'})
result = collection.delete_many({'age': {'$lt': 20})

Elasticsearch数据库

1. 索引如何存储

2. 介绍下Elastisearch原理和用途

3. 分布式实时日志使用ELK

Hive数据库

Hbase数据库

Hadoop生态

  • Copyright： Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.
  • Copyrights © 2019-2020 holysll
  • Visitors: | Views: