数据库基础知识

7/5/2026

主键和唯一索引的区别

主键只有一个，唯一索引有多个
主键的字段不能有空值，唯一索引可以有空值
主键可以作为外键，唯一索引不行，因为它其实就是个索引

LSM树

将对数据的修改增量保持在内存中，达到指定的大小限制后将这些修改操作批量写入磁盘，不过读取的时候稍微麻烦，需要合并磁盘中历史数据和内存中最近修改操作
Log-Structured Merge Tree
Mongodb的wiredTiger引擎和Hbase使用这种结构

MySql中的联合索引

最左原则，B+树的非叶子中用联合索引的第一个key值，在叶子节点在按照第一个key，第二个key，value这样子排序
所以，如果查询的时候前面的条件是联合索引的第二个，索引不生效

B树和B+树

由于平衡二叉树太高，遍历的效率低，所以设计成矮肥的多叉树，多叉平衡树就是B树，每个既放了key也放了value。
为了让B树能放更多key，设计成非叶子节点只放key，叶子节点放key和value。而且父节点的key值都会出现在子节点中，为子节点中的最大或最小值，这样子所有的叶子节点就是所有的key。为了让范围查询更方便，设计了每个节点指向右边的相邻节点，这样子，当做范围查询时，只需要遍历这个链表就行了。由于非叶子节点不放value，可以放更多的key，所以一页磁盘存放的索引更多，可以减少IO次数，提高性能。这就是B+树。

离散度低的字段不适合建立索引

比如性别，只有2个值，如果有100万数据，取性别为男的1条数据，速度还是比较快的，如果要取性别为男的50万条数据，由于先要查到索引，要取50万条索引，再查50万条数据，相当于取了100万次，跟全表扫描差不多

InnoDB和MyIsam的区别

MyIsam性能好，有缓存表meta-data，所以做COUNT(*)时速度更快
MyIsam不支持事务，不支持外键
MyISAM支持压缩特性可以减少磁盘的空间占用

Mysql的默认连接数

是100，一般都改这个数

Mysql和Oracle的分页

Mysql用limit offset Oracle只能用三层嵌套了, 先查出来0 ~ 20行数据，再从结果里面取10-20行数据

三范式

第一范式：表中的每一列都不可再分
第二范式：每一条记录必须要有一个主键，其他字段必须完全依赖于这个主键
第三范式：任意的非主键字段，不能依赖于其他的非主键字段，必须要分成多张表

事务

ACID特性

原子性Actomic, 对外面来说是一步完成的，要么成功要么失败
一致性Consistence, 要么成功，失败了要回滚
隔离性Isolation, 一个事务不受其他事务的影响
持久性Duration, 事务成功后，数据要持久，不能消失

事务并发产生的问题：脏读dirty read：事务1读一条记录，还没commit的时候，另一个事务更新了这条记录，commit之后，查到的就是被修改了的记录，被弄脏了。

不可重复读non-repeatable read: 事务1对同一条记录查询了两次，查了第一次后，被另一个事务更新了这条记录，查第二次的时候，发现两次查询的结果不一样。

幻读phantom read: 事务1进行两次列表查询，这个时候，另一个事务插入或删除了一些符合条件的行，再查的时候，发现查询的记录数不一样，就像产生了幻觉一样。

丢失修改lost update: 事务1和事务2都对某条记录进行修改，事务1先提交，事务2异常回滚，把事务1的修改也回滚了，则事务1的修改就丢失了。

解决上面的问题，就有了五种隔离级别。 none 不使用事务 read uncommitted 允许脏读（可以在更新未提交之前读） read committed 防止脏读（只能在更新提交之后读），大部分数据库默认隔离级别 repeatable read 防止脏读和不可重复读（可以重复读） serializable 事务序列化，可防止前面的所有问题

写的时候加排他锁，这样就解决了丢失修改的问题。写的时候加排他锁，读的时候加共享锁，这样，读的时候，只能由读的事务进行操作，读完马上释放共享锁，不用等到事务提交，这样读出来的就不会是脏数据了写的时候加排他锁，读的时候也加排他锁，这样，读的时候一直到事务提交，不能有更新的事务c对某条记录进行操作，就不会有不可重复读的问题了。写的时候加排他锁，读的时候也加排他锁，列表查询的时候加范围锁，在查询的时候，规定某个区间内不能增加或删除记录，就不会出现幻读了。

事务日志用以保存数据库数据的变动，包括增删改等操作。

管理表空间

第一、字典管理表空间

将Oracle的区管理信息存放在表空间的字典中进行管理，所有区的分配与释放，都会使字典的记录的增减变动。也就是在字典的记录中会执行更新、插入、删 除操作，在执行上述操作时，都会生成重做日志，对字典的管理，将影响正常操作的效率，并且在区分配、回收的过程中，产生磁盘碎片，如果磁盘碎片增加到一定 的程度，会浪费空间，严重影响效率,同时，Oracle 在管理表空间的管理中，会产生递归SQL。

如果要用字典的方式管理表空间，可以在创建表空间时，使用： EXTENT MANAGEMENT DICTIONARY 选项。

第二、本地管理表空间

本地管理是以位图的方式，将区的分配信息保存在数据文件本身，所有区的分配等操作都只是位图的运算，位图中的每一位对应数据文件中的一个区或几个连续的区，这样在进行区管理时，生成的重做日志将非常少，并且运行的效率很高。并且产生磁盘碎片很少。

如果要用本地管理表空间，可以在创建表空间时，使用： EXTENT MANAGEMENT LOCAL 选项。

视图

视图除了进行查询记录外，也可以利用视图进行插入、更新、删除记录的操作，减少对基表中信息的直接操作，提高了数据的安全性。在视图上使用INSERT语句添加数据时，要符合以下规则。（1）使用INSERT语句向数据表中插入数据时，用户必须有插入数据的权利。（2）由于视图只引用表中的部分字段，所以通过视图插入数据时只能明确指定视图中引用的字段的取值。而那些表中并未引用的字段，必须知道在没有指定取值的情况下如何填充数据，因此视图中未引用的字段必须具备下列条件之一。该字段允许空值。该字段设有默认值。该字段是标识字段，可根据标识种子和标识增量自动填充数据。该字段的数据类型为timestamp或uniqueidentifier。（3）视图中不能包含多个字段值的组合，或者包含使用统计函数的结果。（4）视图中不能包含DISTINCT或GROUP BY子句。（5）如果视图中使用了WITH CHECK OPTION，那么该子句将检查插入的数据是否符合视图定义中SELECT语句所设置的条件。如果插入的数据不符合该条件，SQL Server会拒绝插入数据。（6）不能在一个语句中对多个基础表使用数据修改语句。因此，如果要向一个引用了多个数据表的视图添加数据时，必须使用多个INSERT语句进行添加。

存储过程和触发器

存储过程：就是一连串sql语句的打包，需要create procedure这样来创建，再在代码中调用。触发器：类似事件，定义做完一些事后，去执行一些sql语句

snipet

列出数据库： show databases;

列出表： show tables;

查看数据库和表的信息，用describe, DESC question; DESC great;

删除表，数据丢失： DROP TABLE question;

显示支持的存储类型： show engines;

查看建表语句： show create table question;

mysql是默认配置只允许本地连接的，所以要在/etc/mysql/my.cf里面把bind 127.0.0.1注释掉

innodb 支持事务，myisam不支持，所以innodb效率低些。还有memory存储引擎，

修改root密码

update user set password=password('abc') where user='root';
FLUSH PRIVILEGES;

查出表中的列名

select column_name from information_schema.columns
where table_schema = 'stock'
order by table_name, ordinal_position

查出表中列的个数

desc stocks; select FOUND_ROWS();

查看字符编码

show variables like '%char%';

关闭safe update mode

SET SQL_SAFE_UPDATES=0;

查看正在执行的线程

select * from information_schema.processlist;

比较两列

select * from (
	select portfolio_transaction_id, settle_type, case when amount=total_amount then 'true' else 'false' end  as same from hive.analysis.portfolio_transaction as portfolio_transaction where status = 'repaying' and product_category='smart'
) where same='false' and settle_type != 'partSettle' limit 10

时间操作

整数转时间戳：

from_unixtime(time / 1000))

得到时间戳：

to_unixtime(date('2017-10-18')) * 1000

mongoose

findOneAndUpdate可以upsert后又返回upsert后的文档

yield Model.actionLogTrade.findOneAndUpdate(query, newTrades, {upsert: true, 'new': true})

mongo shell

- 可以编辑一个临时文件

edit file.js

- 可以pretty输出

db.collection.find().pretty()

- 可以在replset的从库读写

rs.slaveOk()

查看操作的性能

db.answers.find().explain("executionStats")

查看索引

db.collection.getIndexes()

更新某些字段

db.collection.update(  { _id:...} , { $set: someObjectWithNewData }

新建索引

db.collection.createIndex({risk_accessment: 1}, {background: true})

慢查询分析

查看多少时间属于慢查询

db.getProfilingStatus()

查询慢查询

db.system.profile.find()

看今天的

db.system.profile.find({ts: {$gte: new Date('2016/11/07')}}).sort({$millis: -1}).limit(1).pretty()

杂项

.mongorc.js

EDITOR="vim"

DBQuery.prototype._prettyShell = true

用mongodump备份数据，mongorestore恢复数据

Failed to unlink socket file /tmp/mongodb-27017.sock, 解决方案，删掉这个文件

操作

mongoose在find的时候，option加上lean: true，会返回纯javascript对象，而不是mongoose document，所以速度会很快，内存占用也小

查询数组内元素时，如果元素有多个属性，不用$elemMatch，将默认是or，不是and

数组操作

删掉某个数组元素

db.getCollection('user_account').update({'config.bonus.ticket_id': '558270b209da1ca263a3cc10'},
{$pull: {'config.bonus': {'ticket_id': '558270b209da1ca263a3cc10'}}})

ticket_id不是唯一的，在本地可以，在服务器删除不了，只能用这种方法了

db.user_account.aggregate([
{$match: {'config.bonus': {$elemMatch: {'ticket_id': {$in: ['574f97d6104076d6254b9647']}}}}},
{$unwind: '$config.bonus'},
{$match: {'config.bonus.ticket_id': {$in: ['574f97d6104076d6254b9647']}}}
]).forEach(function (x) {
    print(x.config.bonus.id + '');
db.getCollection('user_account').update({'config.bonus.id': x.config.bonus.id},
{$pull: {'config.bonus': {id: x.config.bonus.id}}})    
})

更新数组里面的对象

db.user_account.find({user_id: '56ce758be7926c7f94e1dc71', 'portfolios.firstDepositTime': null}).forEach((it)=> {
      var i = 0;
      for (var i = 0; i < it.portfolios.length; ++i) {
          var p = it.portfolios[i];
          if (p.product_id === '549922452238c54e98b750bc' || !p.start_time) {
              continue;
          }       
          var time = p.start_time ? p.start_time.getTime() : null;
          print(time, p.asset_id)
          db.user_account.update({user_id: '56ce758be7926c7f94e1dc71', 'portfolios.asset_id': p.asset_id, 'portfolios.firstDepositTime': null},
            {$set: {'portfolios.$.firstDepositTime': time}})
      }         
});

查询数组元素大于等于2个的元素

db.order.find({"products.1": {$exists: true}}).sort({_id: -1})

基于docker搞一个mongodb replset

先生成一个密钥

openssl rand -base64 755 > keyfile
chmod 400 keyfile
chown 999 keyfile

再启动一个节点

mongodb:
  container_name: mongo-master
  image: mongo
  ports:
    - "57017:27017"
  volumes:
    - /mnt/disk100/db:/data/db
    - /home/monkey/docker/mongo-replset:/opt/keyfile
  hostname: master.secret

进入这个mongodb，创建用户

use admin;
db.createUser( {
 user: "siteUserAdmin",
 pwd: "password",
 roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
});
db.createUser( {
 user: "siteRootAdmin",
 pwd: "abc",
 roles: [ { role: "root", db: "admin" } ]
});

把docker-compose.yml改成：

mongodb:
  container_name: mongo-master
  image: mongo
  ports:
    - "57017:27017"
  volumes:
    - /mnt/disk100/master:/data/db
    - /home/monkey/docker/mongo-replset/master:/opt/keyfile
  hostname: master.secret
  extra_hosts:
    - master.secret:111.111.111.1
    - slave1.secret:111.111.111.1
    - slave2.secret:111.111.111.1
  command: mongod --replSet rs0 --keyFile /opt/keyfile/keyfile

启动，授权，配置

db.auth('siteRootAdmin', 'abc')
rs.initiate()
rs.conf()
rs.add('slave1.secret:57018')
rs.add('slave2.secret:57019')

两个从节点的docker-compose.yml差不多

formatter

function formatter(t) {
    var date = new Date(t);
    return date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
}

group整个文档

db.user_account_cashflow.aggregate([
{$match: {user_id: '56ce758be7926c7f94e1dc71'}},
{$project: {asset_id: '$asset_id', amount: '$amount', type: '$type'}},
{$group: {_id: '$type', flows: {$push: '$ROOT'}}}
])

db.order.aggregate([
      {$match : {uid : '123456', oType : 1, status : 1}},
      {$project: {amount: "$amount", time: {$subtract: ["$time", {$mod: ["$time", 1000 * 60 * 60 * 24]}]}}},
      {$group: {_id: "$time", amount: {$sum: "$amount"}}},
      {$sort: {amount: -1}},
      {$limit: 1}
    ])
跟大家分享一小段代码， from young
mongo 并没有提供对 timestamp 类型的日期处理，如果 time 字段是 timestamp 类型，在做分组统计的时候，以天为单位统计就会比较麻烦。 用这个方式: time: {$subtract: ["$time", {$mod: ["$time", 1000 * 60 * 60 * 24]}]} 可以将 time 转换为凌晨的时间，简单地实现对天的统计。

查询数组里面包含某个元素

db.timerateup.find({'phones': {$elemMatch: {$in: ['13228259968']}}}).sort({_id: -1})
db.timerateup.find({'phones': '13228259968'}).sort({_id: -1})