数据库

迷你派大星
103 阅读22分钟

MySQL：

Linux基本命令： s cd pwd路径查看 rm删除 mv移动 cp复制 tar解压 Ps看进程 kill杀进程 chmod改权限（分别表示User、Group、及Other的权限。 r=4，w=2，x=1 ） useradd加用户 userdel删 sudo ping ssh cp远程拷贝 ifconfig
cat 用途是连接文件或标准输入并打印
， grep 命令用于查找文件里符合条件的字符串，-a可以转成二进制查找，-c显示行数，grep test *file

怎么清除表数据：

单表删除： delete from 表名 where
多表删除： delete 别名1，别名2 from 表1 别名1，表2 别名2 where 连接条件 and 筛选条件;

  1. truncate不能加where条件，而delete可以加where条件
  2. truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始 delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
  3. truncate删除不能回滚，delete删除可以回滚

数据库的范式

  1. ：每一列都是不能分割的原子项，设计出来的表都是简单的二维表
  2. ：跟第一范式相比，确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关
  3. ：数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系； 比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话） 这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话) 这样的表结构，关系给剥离开，拆成两个表 （学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

NoSQL和SQL区别

而NoSQL则更加灵活和可扩展，存储方式可以省是JSON文档、哈希表或者其他方式，键值对。
SQL，关系型数据库，通常以数据库表形式存储数据，必须定义好表和字段结构后才能添加数据

Join

数据库中的两张或两张以上表进行连接操作。

  1. left join 是左联接 返回包括左面表中的所有记录和右面表中联结字段相等的记录 left join是以左面表的记录为基础的,右面表数据不足的地方用NULL填充
  2. Inner join等等，因为直接查询会出现笛卡尔积，只返回两个表中联结字段相等的行 ，只有在两个表中匹配的行才能在结果中出现

Where和Having

Where和Having都是进行条件判断
“Where”是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用“聚合函数”；
“Having”是一个过滤声明，，HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

select

在select 语句中可以使用group by 子句将行划分成较小的组，group by 子句可以将查询结果分组，并返回行的汇总信息，按照group by子句中指定的表达式的值分组查询结果

事务的基本要素

原子性，要不全部成功，要不全部撤销
一致性，数据库正确改变状态后，数据库的一致性约束没有被破坏
隔离性，实物之间相互独立，互不干扰
持久性，事务提交的结果将永久保存在数据中

事务的隔离级别

  1. Read uncommited读未提交，最低隔离级别，只能解决丢失更新问题，解决未提交读
  2. Read committed不可重复读，A事务提交后，B事务才能读取到，避免脏读，导致不可重复读取
  3. repeatble read可重复读，解决不重复读取问题，脏读，但是出现幻读
  4. serializable序列化，可以解决所有并发访问问题

并发出现的问题

脏读：A事务访问某数据，B事务访问并修改，但是未进行commit，A发现数据不一致，提交的数据叫脏数据
不重复读取：A事务访问数据，B事务修改数据，进行commit，A继续访问造成读取不一致
幻读：A进行范围查询，查询到了一行记录，B插入了一行数据并提交，A发现多了一行数据，A出现幻觉

MVCC

多并发版本控制用来实现不同事务隔离级别的读，保证隔离级别，MVCC是行级锁的一个变种，大部分属于非阻塞操作； MVCC的实现是通过保存某个时间点的快照实现的，根据储存引擎不同由乐观锁和悲观锁实现。

  1. InnoDB里是通过每行记录两个隐藏列，创建时间+过期时间，不是实际时间值，是版本号，每开始一个新的事务，版本号会递增；事务开始时会比对版本号。

  2. 行级锁 行级锁是一种排他锁，防止其他事务修改此行;

  3. 表级锁表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使 用的 MYISAM 与 INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁 (排他锁)。

  4. 页级锁MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。

事务提交

  1. 将所有的DML(insert、update、delete)语句操作历史记录和底层硬盘数据来一次同步
  2. 事务开启后，更新数据的话，数据库中的真实数据是不会有变化的，只是将记录更新到事务日志中。如果想要数据同步更新到数据库中，我们就需要进行事务提交操作
  3. commit

binlog和redolog的区别

  1. redolog是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层服务层产生的。 两种日志记录的内容形式不同
  2. MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志
  3. 两种日志与记录写入磁盘的时间点不同，binlog日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的
  4. binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。

触发器：

  1. 数据完整性的检测，日志记录，数据校验
  2. Create trigger，before/after，for each row
  3. 语法：Insert型（新增数据），Update型（old修改之前，new将要或已经修改的数据），Delete型（已经删除的数据）

存储引擎

  1. （基于表，不是基于数据库）：大部分选InnoDB，对一致性要求要求不高，读写速度比较快MyISAM
  2. InnoDB：默认的，事务安全，支持行锁，
  3. MyISAM：不支持数据库事务，不支持行锁和外键，只支持表锁，读取快，适合大量查询的应用

B+：

B树：m叉的多路平衡查找树，每个结点包含K个孩子，K就是树的阶，k与磁盘页大小有关

  1. B树的出现的原因：树的高度与磁盘的IO次数是一样的，这时候这要压缩树的高度，就能减少磁盘IO，这是B树矮胖的原因
  2. 所有的叶子节点都位于同一层；
  3. 每个节点中从小到大排列;
  4. 有值域的划分，查询速度还是Ologn，但是IO的次数比较少，至少多了几次内存交互

B+树

  1. 每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。
  2. 只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。 中间结点不存数据，只是当索引用，所有数据都保存在叶子结点中
  3. 在B+树上增加了顺序访问指针，类似于链表，查询速度很快

为什么会出现B+树：

  1. 首先是普通二叉树，由于可能由于插入的有序的数据会变成链表；然后 有了多叉数，m数，同样会出现退化现象；m叉平衡树，B树，这个时候节点放了key和value，为了使得每个结点多方key值，减少磁盘访问次数，将每个结点只放key值，value放在叶子结点，然后再在叶子结点value增加相邻指针，变成类B+树；
  2. 哈希索引没办法利用索引完成排序 哈希索引不支持多列联合索引的最左匹配规则 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

红黑树

属于平衡的二叉树，虽然牺牲了一定的平衡性，但是add、remove操作要由优于AVL树，Java中TreeSet，TreeMap的底层都是基于RedBlackTree红黑树的；
B+树主要用在文件系统以及数据库权做索引。比如磁盘存储、文件系统、MySQL数据库

MYSQL优化

SQL和索引优化 ---- 表结构优化 ----- 系统配置 ------- 硬件

检查工具

慢查询日志

（set global slow_query_log='ON';） 通过一些工具分析慢查询日志：

  1. MySQLDUmpSLow 语法-- mysqldumpslow -t(top表示条数)+ log文件
    慢查询日志(查询结束后才记录): 执行的时间，看看哪条执行比较慢
  2. pt-query-digest 语法 pt-query-digest - log文件
    针对以上查询的结果做分析，查询次数最多的，看看IO（row examine），看看索引命中次数（看row examine和row send对比）
Explain

explain + SQL（查看sql的执行计划）：连接类型，使用的索引，扫描的行数等信息。——主要指标

  1. id：操作表的顺序，id表示加载的优先级；
  2. Type：代表访问类型，**constant一次就找到，all全表遍历
  3. possible key（索引），key（可能没用索引）**， length

SQL优化

  1. 在使用order by时，会使用文件排序（就是返回时进行了排序操作）或索引排序，尽量使用Using index；如果不能用索引就优化orderby相关的缓冲参数
    双路索引
    两次扫描磁盘最终得到数据，读取行指针和ORDERBY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输
    单路索引：
    从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出；数据顺序地保存在内存里。
    什么情况下会导致单路排序失效呢？
    在sort_buffer中，单路排序要比双路排序占很多空间，因为单路排序把所有的字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能读取sort_buffer容量大小的数据，进行排序-------增大sort_buffer_size参数大小
  2. groupby 差不多
  3. 嵌套查询时候，要注意是否有一对多关系，有没有数据的重复
  4. Max（）执行前可以建立个覆盖索引（需要通过索引就可以返回查询所需要的数据，而不通过二级索引查到主键之后再去查询数据）

分页

Limit分页（SELECT ... FROM ... W... LIMIT 5-10... ）

  1. 直接分页代价比较大，因为每次操作要取出所有字段内容，再进行排序切割的步骤，所以效率很低 www.cnblogs.com/scotth/p/79…
  2. 优化1: 通过直接根据索引字段定位后，把LIMIT查询转换成某个位置的查询，减少分页翻页的压力。

索引优化（上）

首先要有主键，有主键的表MySQL会有聚簇索引（聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据）,主键和数据行在一行
creat index on，drop index table name
一般选择在where，orderby，groupby建立索引，要使用覆盖索引（覆盖了所有列）

  1. 对于离散度较高的列进行添加索引，离散度低的例如性别，不需要索引，以免造成额外的IO开销，进行联合索引（包含多个列）时候，把离散成都高的列放在前面，离散度大的可选择性越高效果越好，用count（distinct id）查看唯一值，唯一值越多说明离散程度越高 。
  2. 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询才停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  3. 查询特别多，使用覆盖索引（直接在索引中查询到数据），配合最左原则，减少一些索引的维护 如果索引走不上，可以考虑SQL有问题：
  4. 比如对索引字段进行类函数操作-----连接查询的时候两个表的编码不一样-----是不是索引统计信息有问题：analyze table统计索引信息（是个随机采样过程，未必是准确的）
  5. 不要在索引上进行运算，（假如select from where substring(3, 2) = ‘abc‘，这样索引失效 ）（不走索引）
  6. 字符串需要加双引号，否则索引失效 （不走索引）
  7. 语句中如果有or，索引会失效 （不走索引）
  8. 模糊匹配， %like加在前面，索引失效 （不走索引）
  9. In是可以走索引，not in索引失效，is not null 不走索引 !=、<> 不走索引

索引优化（中）

大数据环境下：

  1. 磁盘I/O是提升查询的关键。应当尽量把索引与数据分散到不同的磁盘上。逻辑上，数据表空间与索引表空间分开。
  2. 因为在建索引的时候要对表进行全表的扫描，可以调大初始化参数db_file_multiblock_read_count的值（在一次I/O时，可以读取多个数据块，从而用最小的I/O完成数据的读取）。 32
  3. 索引设计到的排序操作，可以调整排序缓冲区的大小，让排序编程顺序操作
  4. 如果不怕风险的话，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。

索引优化（下）

怎么去掉重复索引，有个工具pt-duplicate-key-checker

  1. 索引的意义： 索引减少了服务器需要扫描的数据量/帮助服务器避免排序和临时表/将随机IO变为顺序IO
  2. 唯一索引，因为会确保它的唯一，多了一次判断的过程，开销很小，一般使用普通索引。特别是在使用机械盘的场景下，尽量把changebuffer开大从而确保数据的写入速度

表优化

  1. 选择最合适的数据类型，类似能使用char就不用varchar；可以用int放时间（用函数Unix timestamp）
  2. 在表结构遵循第三范式设计，垂直拆分
  3. 水平拆分：时间、取模、哈希

系统配置优化

  1. 操作系统网络优化；增加tcp支持的数量ect/sysctl.conf/文件下 ，net.ipv4.tcp.max-syn-backlog 55433 ｜｜｜｜｜net.ipv4.tcp.fin.timeout//
  2. ect/securiy/limits.conf 下改CentOS最大文件描述符限制更改，修改soft nofile/har nofile 64333，这个更改打开文件数量限制
  3. 关闭防火墙iptables
  4. mysql系统配置文件优化： 数据库多长时间了变更刷新到磁盘 innodb-flush-log-at-trx-commit,可以用1/2/3 ，这个参数对IO影响比较大
    innodb-read-io-thread调整读写的线程数量
    配置缓冲池大小，innodb-buffer-pool-size
    配置缓冲池有多少份：innodb-buffer-pool-instance
    配置日志log缓冲，innodb log

索引离散度

  1. 索引是把一个或几个字段（组合索引）按规律排列起来，再附上该字段所在行数据的物理地址（位于表中）；比如字段是年龄，如果要选取某个年龄段的所有行，需要进行一次全表扫描。但如果以这个年龄段建个索引，那么索引中会按年龄值根据特定数据结构建一个排列，这样在索引中就能迅速定位。
  2. 访问索引需要付出额外的IO开销，从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销还是大
  3. 把性别字段设为表的聚集索引，那么就肯定能加快大约一半该字段的查询速度了。聚集索引指的是表本身数据按哪个字段的值来进行排序。因此，聚集索引只能有一个，而且使用聚集索引不会付出额外IO开销。当然你得能舍得把聚集索引这么宝贵资源用到性别字段上。
  4. 可以根据业务场景需要，将性别和其它字段建立联合索引，比如时间戳，但是建立索引记得把时间戳字段放在性别前面

数据库去重

www.cnblogs.com/insane-Mr-L…

游标

  1. 看成是结果集的一个指针，可以根据需要在结果集上面来回滚动
  2. 查询的结果是含一行或者是多行的数据集，如果我们要对查询的结果再进行查询，比如（查看结果的下一行、最后一行等操作）简单的通过select语句是无法完成的
  3. 声明游标（Declare cursorname Cursor）—>打开游标（Open Cursor）—>读取数据Fetch....From—>关闭游标—>删除游标

Redis相关

数据类型

String，set，list，hash，sorted（set）

hash的底层

（数据较少时使用ziplist，由于hash是具有key-value的当有多对要存入时，按顺序先存入第一对的键、值 第二对的键、值 以维持正确 ，当数据较多时使用hashtable，使用hash映射存储，讲了一下dict结构、rehash等）

Zset底层

skipList 跳跃表是一种有序的数据结构，是链表结构上修改的，每个节点中维持多个指向其他点的指针。查询效率很高；
查询是从顶层往下找，先从第顶层开始找，方式就是循环比较，如过顶层节点的下一个节点为空说明到达末尾，会跳到第二层，继续遍历，直到找到对应节点

zhuanlan.zhihu.com/p/33674267

缓存雪崩问题

缓存雪崩是指，缓存层出现了错误，不能正常工作了。于是所有的请求都会达到存储层，存储层的调用量会暴增，造成存储层也会挂掉的情况。

  1. 使用 Redis 高可用架构：
  2. 使用 Redis 集群来保证 Redis 服务不会挂掉
  3. 缓存时间不一致，给缓存的失效时间，加上一个随机值，避免集体失效
  4. 限流降级策略：有一定的备案，比如个性推荐服务不可用了，换成热点数据推荐服务

缓存穿透问题

用户想要查询一个数据，发现redis内存数据库没有，也就是缓存没有命中，于是向持久层数据库查询。

  1. 存null值（缓存击穿加锁）
  2. 布隆过滤器拦截：在Redis和数据之间添加，用过一个谷歌的过滤器
    将所有可能的查询key 先映射到布隆过滤器中，查询时先判断key是否存在布隆过滤器中，才继续向下执行，不存在，则直接返回。过滤器里面可以设置期望大小/误差率，（可以判定这个东西一定不在里面，但是判断不了一定在里面），布隆过滤器会进行多次哈希，哈希是个概率性的算法，会出现偏差率，

持久化机制

  1. RDB：快照形式是直接把内存中的数据保存到一个dump的文件中，定时保存，保存策略。 当Redis需要做持久化时，Redis会fork一个子进程，子进程将数据写到磁盘上一个临时RDB文件中。当子进程完成写临时文件后，将原来的RDB替换掉。
  2. AOF：把对Redis的服务器进行修改的命令都存到一个文件里，命令的集合。 使用AOF做持久化，每一个写命令都通过write函数追加到appendonly.aof中。aof的默认策略是每秒钟fsync一次，在这种配置下，就算发生故障停机，也最多丢失一秒钟的数据。 缺点是对于相同的数据集来说，AOF的文件体积通常要大于RDB文件的体积。根据所使用的fsync策略，AOF的速度可能会慢于RDB。
  3. Redis默认是快照RDB的持久化方式。对于主从同步来说，主从刚刚连接的时候，进行全量同步（RDB）；全同步结束后，进行增量同步(AOF)。

Redis并发竞争key的解决方案

  1. 分布式锁+时间戳
  2. 利用消息队列

实战运用

1、热点数据的缓存，做一些限时的功能

由于redis访问速度块，所以redis很适合用来存储热点数据，另外结合expire，我们可以设置过期时间然后再进行缓存更新操作，还做一些毒app那些抢购，用incrby命令可以实现原子性的递增，这样完成一些高并发的秒杀，分布式序列号的生成，具体业务还体现在比如限制一个手机号发多少条短信、一个接口一分钟限制多少请求、一个接口一天限制调用多少次等等

2、弄个销售排行榜

借助redis的SortedSet进行热点数据的排序。

我们需要展示各个部门的点赞排行榜， 所以我针对每个部门像，球鞋/服装/美妆，做了一个SortedSet,然后以用户的id作username,以用户的购买销量作为上面的score, 然后针对每个用户做一个hash,通过zrangebyscore就可以按销量获取排行榜，然后再根据username获取用户的hash信息，这个当时在实际运用中性能体验也蛮不错的。

集群模式

  1. 主从复制：根据一个 Redis 服务器来创建任意多个该服务器的复制品，其中被复制的服务器为主服务器（master），复制品则为从服务器（slave）。 只要主从服务器之间的网络连接正常，主从服务器两者会具有相同的数据，主服务器就会一直将发生在自己身上的数据更新同步给从服务器，保证主从服务器的数据相同。 保证不了高可用/没解决master压力
  2. 哨兵：哨兵是一个独立的进程，作为进程，它会独立运行。就是哨兵通过发送命令，等待Redis服务器响应，从而监控运行的多个Redis实例。三个特性：监控（Monitoring/ 提醒（Notification/自动故障迁移（Automatic failover）： 当一个主服务器不能正常工作时， Sentinel 会开始一次自动故障迁移操作。

ZooKeeper

选举机制

目前有5台服务器：

  1. 服务器1启动，给自己投票，然后发投票信息，属于Looking。
  2. 服务器2启动，给自己投票，与之前的服务器1交换结果，由于服务器2的编号大所以服务器2胜出，但此时投票数没有大于半数，所以两个服务器的状态依然是LOOKING。
  3. 服务器3启动，给自己投票，同时与之前启动的服务器1,2交换信息，由于服务器3的编号最大所以服务器3胜出，此时投票数正好大于半数，所以服务器3成为leader，服务器1,2成为follower。
  4. 服务器4启动，给自己投票，同时与之前启动的服务器1,2,3交换信息，尽管服务器4的编号大，但之前服务器3已经胜出，所以服务器4只能成为follower。
  5. 服务器5启动，后面的逻辑同服务器4成为follower。

CAP定理：

一个分布式系统不可能同时满足以下三种,一致性（C:Consistency）,可用性（A:Available）,分区容错性（P:Partition Tolerance）.在此ZooKeeper保证的是CP，ZooKeeper不能保证每次服务请求的可用性，

ZAB协议：

  1. ZAB协议包括两种基本的模式：崩溃恢复和消息广播。
  2. 当整个 Zookeeper 集群刚刚启动或者Leader服务器宕机、重启或者网络故障，所有服务器进入崩溃恢复模式，首先选举产生新的 Leader 服务器，然后集群中 Follower 服务器开始与新的 Leader 服务器进行数据同步。
  3. 当集群中超过半数机器与该 Leader服务器完成数据同步之后，退出恢复模式进入消息广播模式，Leader 服务器开始接收客户端的事务请求生成事物提案来进行事务请求处理。

Niginx

加权轮询算法

一般的算法可能是：

1、轮训所有节点，找到一个最大权重节点； 2、选中的节点权重-1；
3、直到减到0，恢复该节点原始权重，继续轮询；
这样的算法看起来简单，这可能造成权重大的服务器造成过大压力的同时，小权重服务器还很闲。

加权轮询

每个节点有三个权重变量，分别是：
 1. weight: 约定权重，即在配置文件或初始化时约定好的每个节点的权重。
 2. effectiveWeight: 有效权重，初始化为weight。
     在通讯过程中发现节点异常，则-1；
     之后再次选取本节点，调用成功一次则+1，直达恢复到weight；
     此变量的作用主要是节点异常，降低其权重。
 3. currentWeight: 节点当前权重，初始化为0

加权轮询算法要生成一个服务器序列，该序列中包含n个服务器。n是所有服务器的权重之和。在该序列中，每个服务器的出现的次数，等于其权重值。并且，生成的序列中，服务器的分布应该尽可能的均匀

avatar
文章
阅读
粉丝