DBIS打卡之数据库篇

DBIS的21天学习打卡系列活动，此篇将记录这期间学习的数据库知识。琦琦学长发钱了，冲啊！

4.29 分区分表

挑战第一天，正好明天数据库上机课要讲分区分表，提前备好课，学习一下相关知识。

为什么要分区分表

虽然在我们平时的课程和作业中表中的数据都不多，在对表进行查询和修改时甚至感受不到延迟。但是在真实的数据库开发过程中，却经常会出现存储记录数达到百万级乃至千万级的大表。这种情况下，数据库的查询和修改耗时会很长，性能受到极大影响，对于多个大表的联合查询更是无法想象。

那么是否可以多使用些索引来加快大表的查询呢？索引其实也不是万能的，对于静态的数据表，构造良好的索引的确可以提高查询效率，但更多情况下是有增删改需求的动态表，这时大表索引的维护会带来巨大的开销，甚至给总体的性能带来负面影响。

所以我们要用到分表和分区的技术，减少数据库的负担，提高数据库增删改查的效率。

分区

• 分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。
• 分区后，逻辑上还是一张表，但数据散列到多个位置了
• 用户读写的时候操作的还是大表名字，数据库自动去组织分区的数据
• 每个分区都需要独立地存储数据和索引等信息

根据分区的这些特点，可以看出它主要依靠数据库底层的存储机制，将一张表分为多个块来存储，经过数据库本身的封装，可以为用户屏蔽底层的实现细节，用户可以和处理一张表一样来操作表的多个分区。如果有必要，用户也可以操作某个具体的分区，这样使得批量删除等操作非常便捷。正因为它依赖于数据库底层的实现机制，所以在MySQL5.1之后才出现分区功能。这种底层分区的方法提高了数据库和磁盘的IO性能，使其能够支持更高的并发。

分表

• 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表
• 这些子表可以分布在同一块磁盘上，也可以在不同的机器上
• 读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它

与分区相比，分表是从逻辑上将一个表分成了多个子表，每个表都是独立的。也就是说，我们完全可以自己写代码来实现分表的操作，比如说一个用户表user可能不够存储，就可以将其分为user001、user002、...、user010十张表，将数据较为均匀地存储在这十张表内，可以有效地提高大表的性能。

MySQL的分表在MyISAM存储引擎中使用较多，主要是因为引擎的锁机制。在InnoDB中写操作使用的是行级锁，对并发性能影响不大。而在MyISAM中使用的是表级锁，对表中某一行数据的修改需要锁住整个表，其他对该表的操作均会阻塞，在表中数据量较大，操作较频繁时，会极大地影响性能。而分表使修改操作分散到各个子表，能有效缓解该问题。例如采用MyISAM引擎的Discuz中有较多分表。

分表方式

分表主要有垂直拆分和水平拆分两种方式。

垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 垂直分割一般用于拆分大字段和访问频率低的字段，分离冷热数据。比如说在使用爬虫爬取数据时，数据爬取和信息获取两步，爬取到的数据很乱很杂，但它对应着的如标题、时间等信息是我们所想要的。所以可以将较大不常用的原始数据作为冷数据垂直拆分出去，分成新表，可提高对重要字段操作的性能。

水平拆分是指数据表行的拆分，表的行数超过500万行或者单表容量超过10GB时，查询就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当，比较均匀。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。可以使用Hash函数来实现，可以解决对较近插入数据访问的倾斜问题。

4.30 续

数据存储的演进

在数据库最开始设计的时候通常是单库单表，这不是说一个数据库一张表，而是数据库中的每个表都只有一份。例如，有一张用户(user)表放在数据库DB中，所有的用户都可以在DB库中的user表中查到。

然后随着表中数据量的增加，单张表不足以满足存储和性能的要求，就需要单库多表。比如说将user表水平拆分成user001、user002等表。

当数据量增加到单台数据库服务器已无法支撑存储与计算时，需要用到多库多表，将数据库拆分到多台服务器上，实现分布式的存储和计算。

一般大型在线游戏的用户数据表通过多库多表的方式存取。玩过大型在线游戏就会知道，在进入游戏时通常需要选择服务器（像一刀999这样的页游可能取名比较简单，比如一服、二服，像英雄联盟这样高级一点的可能取些花里胡哨的名字迎合用户，刚入坑的新手还得选符合自己品味的服务器名称，于是小姐姐选了黑色玫瑰，老爷们选了祖安），这些服务器就可以简单地理解成不同的数据库，当然实际上还有其他的计算和管理模块。选择了某个服务器，就和某个数据库进行了连接。通过划分服务器，使用多库的方式，可以将数据和访问人数压力分摊到多个节点上，提高访问性能和用户的游戏体验（游戏还会显示各服务器的负载状态来引导用户均衡选择）。但多库的情况下，数据库之间的通信和数据一致性保障是对性能的重要限制（网络传输），所以一些游戏会独立地运行各个数据库，不进行数据的传输，也就无法跨服务器的登录和交易。当然理论上是可以实现的，但是代价比较大。不过对于一些实时性和一致性要求没那么高的功能还是可以支持的，比如说跨服聊天。

MySQL分区算法

• RANGE分区（常用）：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

分片

在分布式存储系统中，数据需要分散存储在多台设备上，数据分片（Sharding）就是用来确定数据在多台存储设备上分布的技术。数据分片要达到三个目的：

• 分布均匀，即每台设备上的数据量要尽可能相近；
• 负载均衡，即每台设备上的请求量要尽可能相近；
• 扩缩容时产生的数据迁移尽可能少。

分片的本质其实就是分表，将一张大表通过一定的分片规则分成多个分片表存储在不同的存储设备上。同样的也有水平拆分和垂直拆分的方法。

5.7 慢查询日志

MySQL有很多种日志，一般的查询日志记录用户所有的增删改查操作，在调试数据库时比较有用，但会带来大量额外IO开销。二进制日志主要记录修改数据或有可能引起数据改变的MySQL语句，可以进行数据库恢复。慢查询日志记录执行时间超过指定阈值的查询语句，可用于性能优化。错误日志存储服务器启动和关闭过程中的信息、服务器运行过程中的错误信息等。中继日志用于分布式数据库的主从复制，将主数据库服务器上的二进制文件同步到从服务器上的中继日志文件实现同步。

慢查询日志不仅记录查询超过阈值的select语句，还有update、delete、call等其他操作。但不记录如ALTER等数据库管理语句，默认情况下关闭。对于“慢”的定义，不同的业务场景是不同的，比如银行、零售业对数据库的实时性要求比较高，对慢查询容忍度小，而销售、库存等使用数据库查询处理大量数据，对时间要求不严格，对慢查询容忍度大。在默认设置下，执行时间超过10s的语句才会被记录。

常用参数：

• log-slow-queries(slow_query_log):是否开启慢查询日志
• log_output：日志存储方式，可设置为'FILE'、'TABLE'、'FILE, TABLE'或'NONE'
• long_query_time：慢查询阈值，默认为10s
• slow_query_log_file：使用文件存储慢查询日志时，指定日志存储的文件
• log_queries_not_using_indexes：如果运行的SQL语句没有使用到索引，是否也被当做慢查询语句记录
• log_throttle_queries_not_using_indexes：没有使用索引的查询语句每分钟记录到慢查询日志中的次数

MySQL自带了对慢查询日志的统计分析工具mysqldumpslow，可以从时间、查询次数等不同维度对慢查询进行排序、查找、统计。但mysqldumpslow只能作用于日志文件而不会统计slow_log表，对表进行操作直接使用SQL语句。

5.9 MySQL基础使用

安装MySQL可直接安装或安装XAMPP套件，之后可通过XAMPP控制台启动MySQL。另外可安装MySQL可视化管理工具Navicat。

• 登录MySQL可在命令行输入：

  mysql -h 主机名 -u 用户名 -p

• 常用管理MySQL命令：

  use 数据库名;
  show databases;
  show tables;
  show columns from 数据表;
  show index from 数据表;
  show table status [from db_name][like 'pattern']\G;

• 创建数据库并设置字符集：

  create database dbis default charset utf8 collate utf8_general_ci; 

• 删除数据库：

  drop database dbis

• 创建数据表：

  create table student(
      id int unsigned auto_increment,
      name varchar(40) NOT NULL,
      mentor varchar(40) NOT NULL,
      primary key(id)
  );

• 删除数据表：

  drop table student;

• 插入数据：

  insert into student (name, mentor) values ("cyn", "wyl");

  插入多行数据可以在values后面用逗号隔开，如果所有的列都添加数据可以不规定列，自增的列可写0或null。

• 查询数据：

  select column_name, column_name
  from table_name, table_name
  [where Clause]
  [limit n][ offset m];

• 更新数据：

  update table_name set field1 = new-value1, field2 = new-value2
  [where clause]

• 删除数据：

  delete from table_name [where clause]

  like模糊匹配，常与%，_，[]，[^]配合使用。

• UNION操作符：

  select expr1, expr2, exprn
  from tables
  [where conditions]
  union [ALL | DISTINCT]
  select expr1, expr2, exprn
  from tables
  [where conditions]

  默认以及distinct会删除结果集中的重复数据

• MySQL排序：

  select ...
  order by field1 [ASC | DESC], [field2...] [ASC | DESC]

• MySQL group by 语句：

  select column_name, function(column_name)
  from table_name
  where column_name operator value
  group by column_name

  with rollup：在分组统计的基础上再进行相同的统计

• MySQL连接（inner join, left join, right join）

  select a.name, a.mentor, b.field from student a [inner | left | right] join teacher b on a.mentor = b.name;

  ## 5.10 续

• NULL值处理：关于NULL的条件运算均返回NULL，应使用 is null、is not null、<=>进行比较判断

• REGEXP正则表达式：^, $, ., [...], [^...], |, *, +, {n}, {n, m}

  select name from student where name regexp '^c';

• 事务使用begin，rollback，commit实现，savepoint identifier允许在事务中创建保存点，release savepoint identifier 删除保存点，rollback to identifier把事务回滚到保存点

• alter 命令修改数据表名或数据表字段

  alter table student drop mentor;
  alter table strdent add age int [first | after name];
  alter table student modify name char(30);
  alter table student change age ages smallint;
  alter table student rename to students;

• 索引

  # 普通索引
  create index indexName on table_name(column_name);
  alter table tableName add index indexName(column_name);
  create table mytable(
      username varchar(16) not null,
      index indexName (username(length))
  );
  drop index indexName on mytable;
  # 唯一索引
  create unique index indexName on mytable(username(length));
  # 显示索引信息
  show index from table_name;

• 临时表

  create temporary table tableName as (
      select * from oldTable
      limit 0, 10000
  );

• 复制表

  show create table tableName;
  复制粘贴sql语句
  或
  create table targetTable Like sourceTable;

• MySQL导出数据

  select * from teacher into outfile 'E:/mywork/teacher.txt';

  mysqldump -u root -p dbis teacher > teacher.txt

• MySQL导入数据

  # mysql命令导入
  mysql -u root -p  dbis < dbis.sql
  # source命令导入（进入数据库）
  source dbis.sql;
  # 使用 LOAD DATA 导入数据
  load data local infile 'teacher.txt' into table teacher;
  # 使用 mysqlimport（与 load data infile类似）
  mysqlimport -u root -p --local teacher teacher.txt

5.14 上机总结

白嫖一份杀哥的操作演示

• 启动数据库服务

  mysqld.exe --defaults-file="D:\ide\xampp\mysql\my.ini"

• 修改密码

  mysqladmin -uroot -p password
  set password for 'root'@'localhost' = password('****');

• 视图、触发器

  CREATE VIEW `name` AS
  SELECT
  test_table.`name` AS `name`
  FROM
  test_table
  CREATE TABLE `test_trigger` (
  `name` varchar(40) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  DELIMITER ||
  CREATE TRIGGER `test_trigger`
  AFTER INSERT
  ON `test_table` FOR EACH ROW
  BEGIN
  INSERT INTO test_trigger VALUES (NEW.`name`);
  END||
  DELIMITER ;

• 查询显示

  SELECT * FROM user;
  SELECT * FROM user\g
  SELECT * FROM user\G

• MySQL安装Debug：杀哥yyds!

5.25 Redis

以下内容参考 JavaGuide

1.简单介绍
Redis是一个使用C语言开发的数据库，其数据存储在内存中，读写速度非常快，被广泛应用于缓存方向，也经常用来做分布式锁，甚至是消息队列。Redis提供了多种不同的数据类型来支持不同的业务场景。Redis还支持事务、持久化、Lua脚本、多种集群方案

2.Redis常用数据结构及应用场景
string：简单的key-value类型，可以保存文本数据和二进制数据，一般常用在需要计数的场景。
list：双向链表，易于数据元素的插入和删除，随机访问困难，可以应用于发布与订阅或者消息队列、慢查询。
hash：类似于HashMap，使用数组加链表实现。适合系统中对象数据的存储。
set：类似于Java中的HashSet，是一个不存在重复数据的无序集合。适用于存放数据不能重复以及需要获取多个数据源交集和并集的场景。
sorted set：在set上增加了一个权重参数score，使得集合中的元素能够按score进行有序排列，还可以通过score的范围来获取元素的列表。适用于需要对数据根据某个权重进行排序的场景。
bitmap：存储连续的二进制数字。适合需要保存状态信息并需要进一步对这些信息进行分析的场景。

3.Redis单线程模型
Redis基于Reactor模式设计开发了自己的一套高效的事件处理模型。通过IO多路复用程序来监听来自客户端的大量连接。Redis服务器是一个事件驱动程序，需要处理文件事件和时间事件，文件事件处理器主要由多个socket、IO多路复用程序、文件事件分派器、事件处理器四部分组成。

4.Redis为什么不使用多线程？
实际上Redis在4.0之后的版本中加入了堆多线程的支持，但大体来说Redis 6.0之前主要还是单线程处理。主要是因为：单线程编程更容易维护；Redis性能瓶颈不再是CPU，主要在内存和网络；多线程存在死锁、上下文切换等问题，甚至会影响性能。

5.Redis6.0后为何引入了多线程？
主要是为了提高网络IO读写性能，用于网络读写数据这类耗时的操作上。

5.26 Redis续

6.Redis给数据设置过期时间有什么用？
内存是有限的，如果缓存中的所有数据一直保存的话，很容易Out of memory。同时在很多业务场景下，比如短信验证码、用户登录的token等，只需要在某一时间段存在，设置过期时间很合适。

7.Redis如何判断数据是否过期？
Redis有一个过期字典来保存数据过期的时间，它的键指向Redis数据库中的某个key，值是一个long long类型的整数，保存了所指向的数据库键的过期时间。

8.过期数据的删除策略了解么？
惰性删除：只有在取出Key的时候才会对数据进行过期检查，CPU友好
定期删除：每隔一段时间抽取一批key执行删除过期key操作，Redis底层会限制删除操作执行的时间的时长和频率，内存友好

9.Redis内存淘汰机制了解么？（如何保证Redis中的数据都是热点数据？）
Redis提供6种数据淘汰策略：
volatile-lru：从已设置过期时间的数据集中挑选最近最少使用的数据淘汰
volatile-ttl：从已设置过期时间的数据集中挑选将要过期的数据淘汰
volatile-random：从已设置过期时间的数据集中任意选择数据淘汰
allkeys-lru：在键空间中移除最近最少使用的key（最常使用的淘汰策略）
allkeys-random：从数据集中任意选择数据淘汰
no-eviction：禁止驱逐数据，也就是内存不足时，新写入操作会报错
4.0版本后增加以下两种：
volatile-lfu：从已设置过期时间的数据集中挑选最不经常使用的数据淘汰
allkeys-lfu：在键空间中移除最不常用的key

10.Redis持久化机制？（怎么保证Redis挂掉之后重启数据可以进行恢复？）
Redis有快照（RDB）和只追加文件（AOF）两种持久化操作。快照存储内存中的数据在某个时间点上的副本，AOF在每执行一条会更改Redis中数据的命令，就会将该命令写入硬盘中的AOF文件。

11.Redis事务？
Redis通过MULTI（输入多个命令进队列）、EXEC（执行事务）、DISCARD（取消事务）和WATCH（监听指定的键，如果该键被修改，整个事务都不会执行）命令来实现事务功能。Redis事务不支持roll back，不满足原子性和持久性。

12.什么是缓存穿透？解决方法有哪些？
大量请求的key不存在与缓存中，导致请求直接到了数据库上，没有经过缓存这一层。解决方法有：缓存无效key，但无效key的过期时间得设置的短一点。使用布隆过滤器来判断请求值是否存在。

13.什么是缓存雪崩？解决方法有哪些？
缓存雪崩指缓存在同一时间大面积的失效，后面的请求都直接落到了数据库上，导致数据库短时间内承载大量请求，可能是Redis服务不可用或者热点缓存失效。针对前者可以使用Redis集群，或者限流避免同时处理大量请求；针对后者可以设置不同的失效时间或者缓存永不失效。

14.如何保证缓存和数据库数据的一致性？
可以使用Cache Aside Pattern（旁路缓存模式），遇到写请求时直接更新数据库，然后删除cache。

6.1 MySQL查询知识点

• 简单的单表查询

  SELECT XX, XX FROM table_name WHERE XX

• 计数count(*)、count(XX)
• 排序order by XX asc|desc，取前n个limit(n)
• 分组group by，聚集函数sum()、max()、min()
• 近似查询like "%XX%"
• 分组的过滤group by XX having XX
• 存在exist的使用
• 复杂的多表查询，左连接left join ... on的使用
• 条件分支函数case XX when XX then XX else XX
• 从子查询的表中进一步查询

  SELECT XX
  FROM (SELECT XX
        FROM XX
        WHERE XX)
  AS table_name
  WHERE XX