数据库知识点总结

实习/秋招时按自己需求总结的知识点，内容并不十分详细，建议选择性阅读。

MySQL默认端口号3306
命令行连接数据库：

mysql -h127.0.0.1 -uroot -proot -P3306
show databases;
use dbname;
show tables;

mysqldump -hhost -uuser -ppwd -P3315 dbname tablename \
--where='true limit 1000' \ # 按条件导出
--skip_lock_tables \        # 去除表的锁定（锁定了导不出去）
--complete_insert \         # 显示完成的插入
--skip_extended_insert \   # 一条一条插入
-d \# 只导出表结构，不导出数据
out.sql

范式

• 第一范式，列不可分，即有的列还能继续划分但是没有
• 第二范式，有主键，需完全依赖主键
• 第三范式，无传递依赖，即非主键A依赖非主键B，非主键依赖主键C的情况
• BC范式，任何非主属性不能对主键子集依赖，是第三范式的扩充

数据库的完整性

• 域完整性，取值约束，不能为非法值
• 实体完整性，主键不能为空
• 参照完整性，外键要么为空，要么为已存在的值

索引

在一列或多列字段上建立索引，已达到快速查询的目的
使用范围：

• 经常做查询的字段
• group by，order by，distinct后连接的字段
• 用作表连接的字段

创建索引的较佳实践：

• 在非空字段上创建，因为B+树遇到空值时会更难以处理，且难以优化
• 选取值比较离散的字段
• 索引字段越小越好。数据库的数据存储以页为单位，一页存储的数据越多，一次IO操作获取的数据也越大，效率就越高

会使索引失效的情况：

• where子句中用 !=, <> 或判断是否为null的语句
• 模糊查询中，like语句时用了 %
• 数据类型出现隐式转换

索引类型

https://www.cnblogs.com/gaoquanquan/p/11030999.html

• 唯一索引，字段的值为唯一
• 普通索引
• 全文索引

按组织方式分：

• 聚集索引，InnoDB的存储引擎表是索引组织表，表中的数据是根据主键按顺序存放的，聚集索引根据主键来构造B+树，叶子结点存放的是该主键所在行的完整数据，每个表中只能有一个
• 非聚集索引，又称辅助索引，二级索引，每个表可以有多个非聚集索引。之所以引入非聚集索引，是因为其构造的B+树存放的是主键，而不是完整数据，因此占用的空间较少，通常使用非聚集索引时，先查到主键，再根据主键到聚集索引中找到完整的行

聚集索引：主键
非聚集索引：唯一索引，普通索引，前缀索引

在数据量很大同时某个字段取值范围很小的情况下，B树的深度会很浅，每个结点包含的数据量很大，此时难以体现B树的优势，应使用位图索引，位图索引在Oracle中实现
位图索引

• 适用于取值范围很小的字段，如性别，婚姻状况
• 对于这一字段的取值，若符合某一条件，则为1，否则为0，通过这一设置，可以通过位操作很方便的取得想要的数据
• 更新位图索引时，所有数据都会被锁定，因此不适合OLTP，不适合频繁修改的环境，只适合查询系统

概念区分
1.主键，唯一索引，且不为空
2.自增主键，自动增加的主键，字段类型为数字
3.主键索引，主键是一种特殊的索引
4.唯一索引，索引列的值必须唯一，但可以为空。主键是唯一索引
5.主键和聚集索引。InnoDB在创建主键时，默认将主键设置为聚集索引。创建表时，若未设定主键，则会将唯一非空的字段设为主键；若未找到，则会创建一个隐藏的聚集索引主键

索引的优点

• 加快查询
• 加速表与表之间的连接
• 减少分组和排序的时间
• 创建唯一性索引保证字段的唯一性

索引的缺点

• 索引需要花费时间动态维护
• 索引需要额外的物理空间来存储

B-树

一种平衡多路查找m叉树，AVL或红黑树在数据量非常大的时候，较深的树结构会导致查询效率变低，m叉树每个结点保存多个值，使得其保持较低的高度，因而效率仍较高

一棵m叉树的定义为：

• 每个结点至多有m个子节点
• 除根节点外，每个结点至少有m/2（向上取整）个子节点
• 根节点至少有2个子节点
  通常，m>=3

每个结点包含的值是个有序表，可表示为：

（n,A0,K1,A1,K2,A2,….,Kn,An）

• n为该结点值的个数；
• Ki（i=1,2,…,n）为关键码，且Ki < K(i+1)，
• Ai 为指向子树根结点的指针(i=0,1,…,n)，且指针A(i-1) 所指子树中所有结点的关键码均小于Ki (i=1,2,…,n)，An 所指子树中所有结点的关键码均大于Kn.

B+树

B-树的变种，是InnoDB存储引擎的实现

• 非叶子结点存储的是指针
• 非叶子结点的子树指针个数为m；
• 所有的关键码都存放在叶子结点中，以有序链表存放；
• 有一个指向叶子结点的指针，用于遍历该有序链表

1.由于关键码存放在叶子结点中，因此查找时必经历一条从根到叶结点的完整路径
2.由于有一个指向叶子结点的指针，因此遍历所有关键码更为方便，而B-树则需要中序遍历，效率更低

B*树

在B+树的基础上增加了非叶子结点指向兄弟结点的指针，空间利用率比B+树高

存储过程

预先编译好的一组SQL语句，方便执行一组操作，使用时调用即可

• 提高执行效率，存储过程只在创建时编译一次，以后调用即可，而执行SQL语句每执行一次就要编译一次，因此存储过程缩短了执行时间
• 减少网络传输，在客户端调用存储过程的开销比执行一串SQL传输的语句更小
• 提高安全性，用户可以在不接触数据的情况下通过存储过程访问数据库，提升了安全性

为什么禁用存储过程

阿里的开发手册禁用存储过程，查了一下，原因如下：

• 难以调试
• 减少的网络传输其实并没有那么多
• 存储过程只编译一次，在大业务量环境下反而成为劣势，随着数据增大或变动，需要重编译或修改存储过程

https://blog.csdn.net/HoneyYHQ9988/article/details/96773210

drop，truncate，delete的区别

• drop，删除表
• truncate，清空表中的数据
• delete，删除表中的部分或全部数据
  删除表中的部分数据，用delete
  清空表中的数据，truncate效率比delete高
  删除表，用drop

内连接，外连接和全表连接

• 内连接
  至少返回一个匹配结果，若不存在，则不返回
• 外连接
  分为左外连接和右外连接，若不存在，则某字段为空
  left join时结果可能会大于实际的数据，原因在于部分数据值相同（可以用group by解决？）
• 全表连接
  将两个表连接

select a.email from person a join person b on a.email=b.emial and a.id != b.id

SQL语句

show databases;
use databasename; use tablename;

• insert into on duplicate key update：在表中插入数据，若已存在，则更新

insert into table(id, name) values(2, 'larry') on duplicate key update name='lairay'
如果不想更新，就写成：
insert into table(id, name) values(2, 'larry') on duplicate key update name=name;

• insert into select from：从表1中复制数据插入到表2

insert into table1(id, name) select tid as id, tname as name from table2;

• GROUP_CONCAT函数：从表中查询多个数据，若同名则以逗号隔开

https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc

select GROUP_CONCAT(name separator ','), score from students group by score; # 查询所有学生的分数，按分数高低排序，分数相同用逗号隔开

select t1.id, t1.status, group_concat(t1.name separator ','), t2.id, t2.name  from test t1 left join test1  t2 on t1.name=t2.name group by t1.id;

• 查询所有分数及格和不及格的学生，按班级分组

select class, sum(case when grade >= 60 then 1 else 0 end), sum(case when grade < 60 then 1 else 0 end) from A group by class;

group by详解

https://www.jianshu.com/p/8b135d373df1

语法：
select name1, aggregate_func(name2) from table where name3 operator value group by name2;

• 使用having子句筛选group by的结果

replace函数

https://blog.csdn.net/lanxingbudui/article/details/83854735
TRIM函数

https://www.1keydata.com/cn/sql/sql-trim.php

优化

SQL语句优化

• where子句中避免用!= <>以及null判断
• 子句嵌套查询尽量改为连接查询
• 尽量使用exists代替in
• insert时一次插入多条数据
• 使用union代替or

SQL表优化

• 选择合适的数据类型
• 垂直拆分，将列多的表拆为多个表，拆分思路为：经常使用的，不经常使用的，经常查询的列
• 水平拆分，将数据量大的表按行拆为多个表，可以提高查询速度，即分库，这种设计适合于需要保存历史订单的情况。例如，某个平台的订单不断增加，而用户实际使用的只是近期订单，因此可以考虑把历史订单水平拆分，提升查询速度

慢查询日志

记录了超过long_query_time的SQL语句
慢查询日志有mysqldump, pt_query_digest
可以查询的有：

• 超过long_query_time的SQL语句
• 超时的次数
• 总的超时时间
• 发给客户端的行数
• 在总时间的占比

事务

事务是指向数据库提交一组操作，是一个不可分割的操作序列，使数据库从一个一致性状态转移到另一种一致性状态

事务的特性ACID

• Atomicity原子性，指事务的操作要么全做，要么全不做
• Consistency一致性，事务的操作会使数据库从一个一致性状态转移到另一个一致性状态
• Isolation隔离性，事务的操作不会对别的事务造成影响，也不会被的事务影响
• Durability持久性，事务一旦提交，对数据库的影响是永久性的

并发事务的问题

• 脏读
  事务可能会读取到另一个事务未提交的数据
• 不可重复读
  事务对同一数据的两次读的结果不同，原因在于期间被别的事务修改
• 幻读
  重点在于新增或删除，使得读出的记录数不一样

要解决脏读和不可重复读问题，需要利用MVCC，即多版本并发控制
每个事务会有版本号，在写操作时更新版本快照，读操作时只到旧版本快照读，以此解决读到了别人未提交的数据（脏读）以及两次读的结果不同（不可重复读）的问题
而幻读则需要next-keys算法

• record-locks
  锁的是记录上的索引，而不锁数据本身
• gap locks
  锁一个索引范围
• next-key locks
  record+gap lock，既锁记录上的索引，也锁索引范围

事务的隔离级别

• READ UNCOMMITTED
  最低级别，允许事务读取另一个事务未提交的数据，会导致脏读问题

• READ COMMITTED
  事务只能读取其他事务已提交的数据，但无法避免不可重复读的问题

• REPEATABLE READ
  MYSQL的默认隔离级别，在一个事务中，其他事务对该数据库的修改对本事务不可见，避免了不可重复读的问题，但是无法避免幻读

• SERIALIZABLE
  最高级别，只允许事务串行执行

mysql中的事务

https://www.qttc.net/175-mysql-autocommit.html

set autommit=0/off，该设置使得要执行的mysql语句一直处于事务中，直到执行commit或rollback语句才会开始一条新的事务
如：

% 以下语句开启一个事务
set autocommit=0;
% or
set autocommit=off;

insert ...; % 插入一条语句
rollback;   (如果insert失败)
update ...;
rollback;   (如果update失败)
commit; （提交事务，该事务结束）
commit and chain; (链式事务。提交事务，并开启下一个事务)
commit work;    (若completion_type=0，则该命令效果与commit一致；若completion_type=1，则变为链式事务。completion_type默认为0)
commit and release; （提交事务并断开mysql服务器连接）

分布式事务

https://juejin.im/post/6844903573667446797

防sql注入

/*

 * 检测字符串里是否含有特殊字符，防sql注入

 */

bool db_escape_string(const string &from)

{
    bool flag = true;
    size_t size = from.length();
    const char* data = from.c_str();
    for (size_t i = 0; i < size; ++i)
    {
        switch (*data) {
        case 0:             /* Must be escaped for 'mysql' */
        case '\n':              /* Must be escaped for logs */
        case '\r':
        case '\\':
        case '\'':
        case '"':               /* Better safe than sorry */
        case '\032':            /* This gives problems on Win32 */
        case ';':
        case '%':
            flag = false;
            break;
        }
        data++;
    }
    return flag;
}

锁

• 按粒度，表级锁，行级锁，页级锁
• 按级别，共享锁，排它锁，意向锁
• 按加锁方式，自动锁和显示锁
• 按使用方式，乐观锁，悲观锁

按粒度的锁

• 行级锁，粒度最细，并发最高，开销最大，发生锁冲突概率最低
• 表级锁，粒度最粗，并发最低，开销最小，加锁速度最快，锁冲突概率最高
• 页级锁，在行级和表级之间，一次锁定一组相邻记录作为“页”

级别

共享锁Shared

若事务加上共享锁，则其他事务也能加共享锁

排它锁eXclusive

若事务加上排它锁，则其他事务不能加任何锁

意向锁

若事务A对某行加了行级共享锁，则其他事务可以共享读；但是如果有事务B对整个表加了排它写锁，那理论上就可以对该行进行写，因此会产生冲突
意向锁的作用在于：先检测该表有没有其他事务使用行级共享锁，如果有，则被阻塞

乐观锁

先修改数据库，在提交时再判断数据库是否被修改，若被修改过，则失败，否则成功，判断是否被修改的方式是在要锁的数据中添加一个版本号或时间戳

注：
这种做法被称为多版本并发控制MVCC，例如，在InnoDB的实现中，事务在修改一行后，会增加两个隐藏列，分别保存系统版本号和过期时间，用于比对其是否被修改过

MySQL中MVCC的正确打开方式
https://blog.csdn.net/Waves___/article/details/105295060#4%E3%80%81%E5%BD%93%E5%89%8D%E8%AF%BB%E5%92%8C%E5%BF%AB%E7%85%A7%E8%AF%BB

悲观锁

在修改数据库之前，先获取该数据上的锁，若获取成功，则修改，否则等待
Oracle中可以添加no wait选项，使得在没有获取锁时立即报错

乐观锁和悲观锁适用范围

读多写少乐观锁，读少写多悲观锁

存储引擎

MyISAM

MySQL Indexed Sequential Access Method

• MySQL5.5之前的默认数据库引擎，速度快，效率高，但是不支持事务，不支持并发，不支持外键
  先有MyISAM后有InnoDB
• MyISAM只支持表级锁，InnoDB支持行级锁

CAP理论

• Consistency 一致性
• Availablity可用性
• Partition tolerance 分区容错性
  实际最多只能满足其中一项

数据库的主从复制

一台作为主服务器，其余多台用于读
优点：

• 用于热备份，在数据损坏时可用于恢复；
• 提升IO性能，采用一台数据库时在面临大数据量访问的时候会产生瓶颈，从数据库达到分流的目的，从而提升IO性能
• 读写分离，通常主库写，从库读，主从之间做同步

实现过程

• 主数据库的更新操作写入binlog；
• 从数据库连接到主数据库；
• 主数据库开辟一个线程，将binlog更新的内容发送至从数据库；
• 从数据库开辟一个IO线程，读取来自binlog的数据并写入到relay log；
• 从数据库创建一个线程从relay log中读取内容并更新从数据库。注意
  relay log中有一个Exec_Master_Log_Pos用于记录当前更新到了哪个位置，每次从这个pos开始做同步

主服务器宕机的解决方案

通常，若主服务器宕机，则需要将某一台从服务器设为主服务器，过程为：

• 确保所有relay log更新完毕，命令查看：show processlist；
• 登陆从数据库，查看master.info，对比最大的Exec_Master_Log_Pos作为主数据库；
• stop slave，删除master.info和relay.log，配置my.cnf，开启binlog，reset master；

主从数据库复制延迟的解决方案

MySQL默认的复制是异步的，即主数据库写数据后会立即返回给客户端，此时若主机宕机，可能导致数据丢失

半同步复制

MySQL5.5之后以插件形式支持

主库写完后，保证至少写入到一个从库的relay log才返回给客户端，优点是避免了数据丢失问题，提升了安全性，缺点是有一定的延迟，至少是一个TCP往返时间，因此会有一定的性能降低

数据库中间件

读写操作都经过中间件

• 写操作
  将写操作记录到中间件中，设置一个同步的经验时间，例如500ms，然后修改主数据库并进行同步
• 读操作
  先访问中间件，若在同步时间内，说明主库被修改，路由到从主库读；
  若没有数据，说明没有被修改，路由到从库读

数据库恢复

需确保开启binlog功能

https://www.cnblogs.com/aqiao1819/p/10681688.html

https://blog.csdn.net/mengzuchao/article/details/81674460

# 导出表
mysqldump -h10.56.44.111 -uncs -pncs -P3307 rc_conf t_oss_sid  >  t_oss_sid.sql
mysqldump -hhost -uuser -ppwd -P3315 dbname tablename \
--where='true limit 1000' \ # 按条件导出
--skip_lock_tables \        # 去除表的锁定（锁定了导不出去）
--complete_insert \         # 显示完成的插入
--skip_extended_insert \   # 一条一条插入
-d # 只导出表结构，不导出数据
> out.sql 
# 导入表到另一个数据库
mysql -h10.56.44.111 -uncs -pncs -P3315 -A rc_conf < t_oss_sid.sql

全文检索

可以通过索引来模糊查询：

select * from blog where content like 'MYSQL%';

只会查询MYSQL开头的数据，但是如果想要查文章中包含MYSQL的数据就做不到了，这时候就需要能全文检索，这是MySQL的InnoDB引擎1.2版本以后实现的功能
全文检索采拥倒排索引实现