系列总结04-数据库

1. 数据库常用语句

1.1创建数据库：create DATABASE [name]
1.2创建建表： create table [name] (id,name)
create table [name] (id int not null,
name varchar(10),
age int)
1.3给你一个表exam（name,course，grade） 请你查询所有成绩都及格的学生的
selct name from exam where name not in(select name from exam where grade<60)
1.4 筛选出成绩前三名的信息
select * from Grade order by grade desc limit 0,3
1.5 筛选出后三名
select * from Grade order by grade asc limit 0,3

2 数据库三大范式

第一范式： 每列的原子性 第二范式：表中每列都和主键相关 第三范式： 每一列都和主键列直接相关，而不是间接相关

3 数据库查询慢的原因

1.数据量太大
2.没有创建索引
3．索引失效
4.内存不足
5.网速较慢
6.锁或者死锁，使用show processlist 查看语句执行的状态，是否被锁住

4 mysql和其他数据库的区别

mysql
免费，功能中等， Mysql 环境里的其他相关组件数量中等，几乎能在所有主流平台运行，有开源版本，速度最快，最不安全。多个数据库多个用户形式。
sqlserver
中等贵，功能最少，Microsoft SQL Server 2014 环境里的其他相关组件最少，只能在Windows上运行，不开源，一般安全
oracle
最贵，功能最多，，Oracle 环境里的其他相关组件最多，几乎能在所有主流平台运行，不开源，速度最慢，最安全。它是数据库中多个用户的形式。Oracle 支持大并发，大访问量

5 where和having的区别

1 having是从前面筛选的字段再筛选，而where是从数据表中的数据直接筛选

6 存储引擎

MyISAM
data存的是数据地址。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

InnoDB
data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

7 MyISAM 和 InnoDB的区别

1. 【是否支持行锁】
    MyISAM 仅支持表锁， InnoDB支持表锁和行锁
    
2. 【是否支持事务】 
    MyISAM不支持事务，且不具备【崩溃恢复能力】。
    InnoDB 支持事务，提供回滚和崩溃恢复。

3. 【是否支持外键】
    仅InnoDB支持外键 
4. 【是否支持MVCC（多版本并发控制）】
    仅InnoDB支持MVCC

5. 【应用场景不同】
    MyISAM 适用 【Select】频繁，并发少
    InnoDB 适用 【Insert Delete Update】频繁，高并发

    因为 
    (1) MyISAM 仅缓存索引块，而InnoDB缓存 {索引+数据}
    (2) InnoDB 回表需要映射块，而MyISAM 直接映射数据地址
    (3) InnoDB 需要维护MVCC机制
    
6.  索引不同
    MyISAM 【索引】 叶子节点 = {key ， 行地址}
    InnoDB 【主键索引】 叶子节点 = {主键ID ， data数据}
    因此 InnoDB根据【主键】索引查询，不需要二次寻址，效率更高
    InnoDB 【二级索引】 叶子节点 = {key, 主键ID}
    如果过实现索引覆盖，即 要查询列值 全部命中索引，则直接返回 非主键索引的内容
    如果为实现索引覆盖，则需要根据【主键ID】做回表
7.  【存储结构不同】
    InnoDB 存储结构 = 索引和数据 集中存储
    MyISAM存储结构 = {表定义，MYD数据文件，MYI索引文件}

8.  不带WHERE条件时，【count(*)效率不同 】
    MyISAM 自带计数器存储保存【总行数】，可直接返回结果
    InnoDB 扫描全表计算总行数

7 为什么使用B+树，而不是 B树

8 为什么使用 B+树，而不是红黑树

B+树磁盘IO次数更少

1. 红黑树的深度往往较大，而磁盘IO次数是和树高度成正比。

2. B树利用磁盘预读取特性。在建立节点时，申请【物理连续】的一页空间。
   一页空间 = 磁盘预读取的 多个磁盘块总和，从而 【实现一次磁盘IO读取一个节点】，减少磁盘IO次数

   红黑树上逻辑较近的节点，其物理距离可能较远。无法利用磁盘预读特性

9 mysql的索引分为哪几种：

1 普通索引
2 唯一索引：索引列的值必须唯一
3 主键索引：一种特殊的唯一索引，一个表只能有一个逐渐，不允许有空值
4 组合索引：在多个字段上创建索引，遵循最左前缀原则
5 覆盖索引：在非聚集索引的叶子节点中 所含的列值是要查询的列值，不用进行回表。直接返回即可

10 聚簇索引和非聚簇索引 【区别】

相关定义
数据行的物理顺序与列值的逻辑顺序相同，一个表只能拥有一个聚集索引
注意在mysql的InnoDB的存储引擎中主键索引就是聚集索引
当表中定义了主键索引时，他就默认是聚集索引，如果没有定义主键索引，就默认第一个第一个唯一非空索引为聚集索引，如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引；
但是在myisam存储引擎中主键索引不是聚集索引

聚簇索引 = 将索引结构和数据放在一起的索引
【优点】：查询效率高，定位到索引树叶节点，就直接找到数据
【缺点】： 索引列更新时，维护代价大
【主键索引】属于【聚簇索引】

非聚簇索引 = 索引结构和数据分离的索引
【优点】： 更新代价小
【缺点】： 需要二次查询，
例如 二级索引需要在主键索引中再次回表查询
【二级索引】属于【非聚簇索引】
比如mysql的聚集索引和非聚集索引

11 其他索引介绍

主键索引
主键索引 叶子节点 = {key,其他data数据}
InnoDB中如果没有指定表的主键，则检测表是否有唯一索引
如果有，则该字段默认 = 主键
如果没有， 则InnoDB自动创建6B的自增主键

二级索引

二级索引 叶子节点 = {key，主键ID}

二级索引查询【过程】
1. 【非聚集索引】中查询到主键ID
2. 【回表】 ： 根据主键ID在 【聚集索引】中查询整行记录

普通索引无法直接定位行记录，需要扫描两遍索引

12 回表

数据库根据索引找到指定的记录所在行之后，还要根据rowid（数据所在具体地址）再次回到数据块取数据（索引中未提供的数据）的操作。
怎么避免回表
将需要的字段放在索引中，查询的时候就可以避免回表，但是不能刻意避免回表，代价太大

13 为什么不对每个列创建索引

1. 数据CRUD时， 索引需要动态维护
2. 索引占据物理空间，尤其聚簇索引

14 索引应用场景

1 ALTER TABLE add index(字段名) 创建索引
1. WHERE
WHERE 筛选字段进行查询时，
如果 该字段建立索引，则查询效率更高

2 ORDER BY

使用order by将查询结果按照某个字段排序时，

如果该字段没有建立索引，
查询出的所有数据使用外部排序，涉及磁盘IO，则效率底下

ALTER TABLE table_name ADD INDEX index_name (column_list);
由于索引有序，所以按照索引顺序逐条读出数据

3 索引覆盖
【查询的字段】都建立过索引，
引擎会直接在索引表中查询而【不访问原始数据】
尽可能在select后只写必要的查询字段，以增加索引覆盖的几率

15 创建索引的原则

1. 字段 基数大
2. 字段 重复值较少
3. 字段 频繁作为【查询条件】
4. 字段 更新少
5. 最左匹配原则 ：
   MySQL对 索引中的列值 从左到右匹配，直到遇到范围查询就停止
6. 尽量扩展索引，而非创建新索引

16 MVCC

mvcc叫做多版本并发控制，是一种不利用锁机制实现的隔离级别，主要实现了在保证数据的一致性的前提下，实现了读写的并行。

17 什么是事务

事务是访问数据库的一个操作序列，要么完全执行要么完全不执行

18 事务特性

事务符合 ACID原则的一组操作
1. Atomicity（原子性）
事务是不可分割的最小单元，事务内所有操作要么全部执行成功，要么全部失败回滚

2 Isolation（隔离性）
事务所作出的修改，在提交前，对于其他事务而言不可见

3 Durability（持久性）
事务一旦提交，所作出的修改是永久的。

4 Consistency（一致性）
系统从一个正确状态–>另一个正确状态
AID是数据库的特征，通过AID手段保证C这个目的

   【数据库】约束列值 不能小于0，则事务执行失败
   【应用层】约束 余额数据不能小于0，则应用层通过事务回滚保证约束
   没有约束，则事务执行成功，且保持了一致性。因为没有破坏约束。

19 并发事务带来的问题

1 脏读
事务A 修改数据，但并未commit。而事务B读取该数据。
2 不可重复读
事务A 共读取两次数据，而在这两次读取之间。事务B修改了数据。则事务A两次读取到的数据不同。
3 幻读
事务A 读取【n行数据】,事务B在n行数据间添加数据。则事务A第二次读取时，读取到n+1行。

20 事务的隔离级别

1. Read-Uncommitted 未提交读
   无论事务是否提交，直接【读取主记录】
   即并发问题 =【脏读】+【不可重复读】+【幻影读】
2. Read-committed 提交读
   【读取最新版本的快照】，快照是事务提交后产生，但可能存在同一事务两次读取的快照不同。
   即并发问题 = 【不可重复读】+【幻影读】
3. Repeatable 可重复读
   【读取指定版本快照】，则事务多次读取某数据行的结果一致，但读取到的数据可能并非最新数据 。使用MVCC实现，详情请见MVCC
   即并发问题 = 【幻影读】
4. Serializable 可串行化
   上表锁，读写相互阻塞。效率低下。
   无并发问题
   InnoDB引擎默认实现 可重复读隔离级别

21 大表优化方式

1 explain 查看执行计划 ，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等

2 限定数据范围
查询语句 通过查询条件限定数据范围

3 数据库主从同步，读写分离
多个服务器上部署mysql，
一台服务器为【主库】，负责【写数据】
其他服务器为【从库】，负责【读数据】

4 主从同步实现
【主库A】数据库事件存储在 日志系统A中，传输日志文件
【从库】 接收文件并写入本地日志系统，然后将【数据库事件】在从库MySQL中完成

5 垂直分区

优点：一行记录 的数据量变少，在查询时IO次数减少

缺点：
(1) 主键冗余，
(2) 引起join操作

6 水平分区
将 【行数据】分组存储到不同【库 ||表】， 【最好分库】

【缺点】：
(1) 查询时，通常需要 【多个表名】 + 【UNION操作】
(2) 跨库 JOIN 性能很差 ,
(3) 【跨库】的【事务支持】，逻辑复杂度和性能代价 都很大

7 分表 分库后的问题
(1). 【跨节点】的 count，order by ， group by 等【聚合函数】逻辑复杂
(2). 【跨库】的join 性能代价大
(3). 【主键ID问题】 ：MySQL原先主键自增机制，跨库情况下，存在主键重复的问题
1) 若采用 UUID作为主键， 则UUID主键占用内存过大
2) 多个数据库采用 跨步自增
3) …
(4) 【事务支持】

8 主从分布，读写分离
作用 = { 负载均衡 + 数据备份 + 高可用}
流程 ={
1. 主库的【数据库事件】记录在二进制的binlog，binlog传输给从库
2. 从库读取binlog内容到本地日志系统的relaylog
3. 从库 执行relay log中的SQL语句，实现主从同步
}

22 SQL生命周期

1. 应用服务器与数据库服务器建立一个连接，应用服务器发送SQL请求

2. 数据库进程拿到【sql请求】，通过【连接器】验证SQL语句是否具有【权限】

3. 【分析器】进行 词法分析和语法分析。
   词法分析 提取SQL语句关键元素 ： SELECT 、 表名 、 查询条件等
   语法分析 判断SQL语句是否正确 ：判断关键词是否正确

4. 【优化器】生成SQL【执行计划】

5. 【执行器】按照执行计划， 将数据读取到内存并进行逻辑处理

6. 通过连接，将数据传输给应用服务器

7. 断开连接

23 MySQL锁

1. 表锁
2. 行锁
   1. Record Lock : 单个数据行的锁
   2. Gap Lock ： 锁定范围，不包括记录本身
   3. Next-key lock : record + gap 结合，锁定一个范围，并且锁定记本身

24 count* count1 count(col)区别

count(expression) = 聚合函数
逐行检测expression是否=null,
expression！=null则计数器加1

count(1)和count(*)作用相同:
count(1) 每行expression=1 ，NOT NULL，count(1)作用=统计总行数
count() 并非 统计每行所有列值是否为null，列值全为null的数据行不存在.
因此 count() = 统计总行数

count(1) count(*) 经过优化器指定的执行计划相同 ，两者效率相同
在没有二级索引情况下， 通过主键索引统计总行数
如果有二级索引， 则优先选择 【最窄】二级索引统计总行数
主键索引 叶节点 = {主键ID, 其他所有列值}
二级索引 叶节点 = {key+主键ID}

count(col) 逐行检测col是否=null
因此 count(col)统计 非null列的总行数
对col建立非聚集索引，可以优化查询效率

25. explain 返回的结果

type 【引擎查找表】的方式
all,index,range,ref,eq_ref，const
从左到右，它们的效率依次是增强的
    
all     全表扫描
index   全索引扫描
range   范围扫描 ：     【索引】+【范围查询】
ref     小范围扫描：    【可能重复】的【普通索引】+【表联结情况】
eq_ref  单条数据扫描 ： 【主键 || 唯一】索引+【表联结情况】
const   单条数据扫描 ： 【主键 || 唯一】索引
key
MySQL 使用的索引
rows
MYSQL 执行查询的行数，数值越大性能越差
extra
Using index 使用覆盖索引
Using Where 使用WHERE过滤
Using temporary ： 使用临时表 ，建议优化
Using filesort ： 对查询结果【外部排序】，而非根据索引有序读取。建议优化

26 SQL优化

1 WHERE的优化

全表扫描
WHERE,ORDER BY涉及的列上 建立索引

WHERE条件中避免使用的判断

{
    is null ,
    !=  ,
    OR  ,
    in  ,   //  慎重使用，非连续 in集合会放弃索引，全表扫描
}

这些判断，会使得引擎【放弃索引】，进行【全表扫描】

2 WHERE条件中 避免【运行时解析】的变量
原因： 执行计划在【编译期】制定，【运行时解析】的变量不能作为索引输入项
因此采用全表扫描

   {
        1. 参数 
             WHERE num = @num
        2. WHERE条件左侧 避免 函数调用+算术计算 
            WHERE num = 100+2
            WHERE substring(num,1,3) = 'abc'
    }

3 超大分页

超大分页 = Limit 10000，10
MySQL并非跳过= offset行，而是取offset+N行，然后返回放弃前offset行，返回N行
那当offset特别大的时候，效率低下
【优化方法】： 先快速定位 主键，然后再关联

4 日志优化

     慢查询日志 ，查看开销大的SQL
        set GLOBAL slow_query_log = on
   
   开销大原因 = {
        1.  
            【原因一】：SQL语句 是否加载额外的数据 
            【例子】： 未使用WHERE范围限制、 Limit分页过大、 使用{is NULL，!= ，OR} 触发全表扫描
            【优化】 ： 修改SQL语句
            
        2. 
            【原因二】： 未命中索引、使用的索引有待优化
            【例子】： SELECT A,B,C FROM t WHERE A=? AND B= ? 
                        或者 ORDER BY 、ON的列上是否建立索引 
            【优化】：explain查询执行计划，创建||选择其他索引，使得SQL语句尽可能地命中索引
            
        3. 
            【原因三】： SQL语句无法优化，【大表情况】
                (1) 分库分表
                (2) 主从同步，读写分离
    } 

27 数据库的锁

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

28 怎么实现索引，怎么使用索引查询语法

CREATE INDEX indexName ON mytable(username(length));
ALTER table tableName ADD INDEX indexName(columnName)

19 视图的作用
简化了操作，把经常使用的数据定义为视图。
安全性，用户只能查询和修改能看到的数据
逻辑上的独立性，屏蔽了真实表的结构带来的影响。

29 LSM-tree原理

LSM-tree 最大的特点就是写入速度快，主要利用了磁盘的顺序写

写入流程：一个 put（k，v） 操作来了，首先追加到写前日志（Write Ahead Log，也就是真正写入之前记录的日志）中，接下来加到 C0 层。当 C0 层的数据达到一定大小，就把 C0 层 和 C1 层合并，类似归并排序，这个过程就是Compaction（合并）。合并出来的新的 new-C1 会顺序写磁盘，替换掉原来的 old-C1。当 C1 层达到一定大小，会继续和下层合并。合并之后所有旧文件都可以删掉，留下新的。

**查询流程：**在写入流程中可以看到，最新的数据在 C0 层，最老的数据在 Ck 层，所以查询也是先查 C0 层，如果没有要查的 k，再查 C1，逐层查。
一次查询可能需要多次单点查询，稍微慢一些。所以 LSM-tree 主要针对的场景是写密集、少量查询的场景。

30 为什么redis比mysql查询快

1.Redis是基于内存存储的，MySQL是基于磁盘存储的
2.Redis存储的是k-v格式的数据。时间复杂度是O(1),常数阶，而MySQL引擎的底层实现是B+Tree，时间复杂度是O(logn)，对数阶。Redis会比MySQL快一点点。
3.MySQL数据存储是存储在表中，查找数据时要先对表进行全局扫描或者根据索引查找，这涉及到磁盘的查找，磁盘查找如果是按条点查找可能会快点，但是顺序查找就比较慢；而Redis不用这么麻烦，本身就是存储在内存中，会根据数据在内存的位置直接取出。
4.Redis是单线程的多路复用IO，单线程避免了线程切换的开销，而多路复用IO避免了IO等待的开销，在多核处理器下提高处理器的使用效率可以对数据进行分区，然后每个处理器处理不同的数据

31 redis为什么速度快

1. Redis将所有的数据都存放在内存中，正常工作中是不需要从磁盘读取数据的
2. 单线程简化了算法的实现，并发数据结构实现不但困难，且测试也麻烦；单线程避免了线程切换以及加锁释放锁带来的消耗
3. redis利用了I/O多路复用，使用epoll函数作为具体实现，实现对多个FD读写的监控，提高了性能

32 redis数据类型特点及应用场景

1. String 动态字符串。类似于ArrayList 访问量统计
2. hash（字典） 相当于HashMap，适合存储，读取对象的属性，比如姓名
3. List（底层快速链表） ，适合用于消息队列
4. Set，类似于java的HashSet，内部键值唯一，底层是字典，所有的value值为空，提供了交集并集功能，可以用来查找共同好友
5. SortedSet，内部value唯一，且可以给value添加score表示排序权重，插入数据时，会自然排序，可以用于排行榜

33 Redis的缺点

1、由于 Redis 是内存数据库，所以，单台机器，存储的数据量，跟机器本身的内存大小。虽然 Redis 本身有 Key 过期策略，但是还是需要提前预估和节约内存。如果内存增长过快，需要定期删除数据。
2、redis是单线程的，单台服务器无法充分利用多核服务器的CPU