数据库

mysql 基础知识(6) – Join

看到网上有篇文章用韦恩图来讲解了一下 SQL 的 join 操作,但是感觉举的例子似乎不太实际,遂自己写了一篇,图是从那篇文章里面盗的(逃

假设我们有下面两张表,上边的是表 user,下边的是 package,表示每个用户对应的包裹

id name
1 Luke
2 Leia
3 Anakin
4 Padem
id content user_id
1 droid 3
2 lightsaber 2
3 blaster 1
4 R2D2 5

创建这两个表的语句分别是:

create table user (id integer, name string);
create table package (id integer, content string, user_id integer);
insert into user (id, name) values (1, 'Luke');
insert into user (id, name) values (2, 'Leia');
insert into user (id, name) values (3, 'Anakin');
insert into user (id, name) values (4, 'Padme');
insert into package (id, content, user_id) values (1, 'droid', 3);
insert into package (id, content, user_id) values (2, 'lightsaber', 2);
insert into package (id, content, user_id) values (3, 'blaster', 1);
insert into package (id, content, user_id) values (4, 'R2D2', 5);

Veen diagram(韦恩图)是一种表示集合的图形语言。SQL 的 join 本质上也是从集合论里面来的,可以从集合论的角度来学习和记忆 Join 的语法。

Inner Join

如果我们要选出每个有包裹的人,以及对应的包裹,可以使用 inner join。内连接(inner join)计算的是两个表的交集,也就是 A ∩ B

select
user.id, user.name, package.id, package.content 
from
user inner join package
on user.id == package.user_id;

结果一共有3列,每个表中的第四列都因为在对方表中没有而没有出现在结果里。

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid

Full Outer Join

如果我们想要选出所有的任何包裹的对应关系,哪怕是对应得人或者包裹不存在的话,可以使用 full outer join。全连接计算的是两个表的并集,也就是 A ∪ B

select
user.id, user.name, package.id, package.content
from
user full outer join package
on user.id == package.user_id;

结果一共有6列,注意其中缺字段的地方被补上了 null。另外 SQLite 不支持 full outer join。感觉这个 Join 似乎用的不是太多,因为实际情况中,往往 package.user_id 是 user.id 的外键,所以不会出现 user_id 不存在的情况。

// 结果省略

Left Outer Join

如果我们要取出每个人的包裹情况,没有包裹的也写上 null,那么这用情况下应该使用 left outer join。

select
user.id, user.name, package.id, package.content
from
user left outer join package
on user.id == package.user_id;

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid
4           Padme       NULL        NULL

Cross Join

要获得A表和B表左右可能的交叉组合的话,可以使用 cross join,也就是笛卡尔乘积。

select
user.id, user.name, package.id, package.content
from
user cross join package;

结果如下

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        1           droid
1           Luke        2           lightsaber
1           Luke        3           blaster
1           Luke        4           R2D2
2           Leia        1           droid
2           Leia        2           lightsaber
2           Leia        3           blaster
2           Leia        4           R2D2
3           Anakin      1           droid
3           Anakin      2           lightsaber
3           Anakin      3           blaster
3           Anakin      4           R2D2
4           Padme       1           droid
4           Padme       2           lightsaber
4           Padme       3           blaster
4           Padme       4           R2D2

mysql 基础知识(5) – 聚合语句(group by)

Group by 用来按照某一列或者某几列的值聚合数据。group by x 按照 x 相同的值聚合,group by x, y 按照 x 和 y 都相同的值聚合。而查询的列要么是聚合的列,要么应该通过聚合函数来选取一列。而且所有的 null 会被聚合成一行

比如说下面的数据表中

-- How many countries are in each continent?
select
  continent
  , count(*)
from 
  countries
group by 
  continent

执行查询可以得到每个洲的国家的数量。

过滤

在 SQL 中,Where 子句是在 group 子句之前运行的,所以我们无法通过 where 来过滤 group 之后的结果,而应该使用 having 子句来过滤。

select
 continent
  , max(area)
from 
  countries
group by 
  1
having
  max(area) >= 1e7

隐式聚合

当你没有使用 group by,而使用了 max、min、count 等聚合函数的时候已经在聚合了

-- What is the largest and average country size in Europe?
select
  max(area) as largest_country
  , avg(area) as avg_country_area
from 
  countries
where 
  continent = 'Europe'

MySQL 的特殊处理

如果在查询中有没有聚合的列,那么 MySQL 就会随机选取一个列,比如下面就会随机选取一个州。

select 
  country
  , state
  , count(*)
from
  countries
group by 
  country

ref

这篇文章主要参考这里:https://www.periscopedata.com/blog/everything-about-group-by

完全理解 SQL 的内在逻辑

太多的程序员认为SQL像是洪水猛兽一样。它是少有的几种声明式的语言,和其他的命令似的面向对象的甚至函数使得语言大相径庭。
我每天都会写SQL而且在我的开源项目中大量的使用SQL,因此我非常地想要把SQL的美展现给你们这些还在挣扎着使用它的渣渣们。下面的教程适合

  1. 使用过SQL但是从来没有完全理解他的人
  2. 很了解SQL,但是从来没有思考过他的语法的人
  3. 想要把SQL交给其他人的人

这个教程将会这关注SELECT语句,其他的DML将会在另一篇文章中介绍

SQL是声明式的

首先要记住,声明式。唯一的一种范式就是你可以只是声明你想要的结果就得到了他。而不是告诉你的电脑怎样去把这个结果计算出来,不错吧?

Select first_name, last_name FROM employees WHERE salary > 100000

很简单,你不需要关心employee的记录是存在哪里的,你只想要知道那些薪水还不错的人。

如此简单,那么问题在哪里呢?问题在于我们大部分时候是在按照命令式的编程思维在思考,比如“机器,干这个,然后干那个,但是在这之前检查一下,如果是这样或者那样就不行”。这其中包括了存储临时结果在变量里,循环,迭代,调用函数等等。

忘掉那些东西,思考如何声明东西,而不是告诉机器怎样去计算。

SQL语法的顺序有些问题

常见的混乱的来源可能是SQL语法并不是按他们的执行顺序来排序的,词法(Lexical)排序是

  1. SELECT [DISTINCT]
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. UNION
  7. ORDER BY

简洁起见,并没有列出所有语句,而从逻辑上来说,真正的逻辑执行顺序是这样的:

  1. FROM。FROM后面的表标识了这条语句要查询的数据源。和一些子句如,(1-J1)笛卡尔积,(1-J2)ON过滤,(1-J3)添加外部列,所要应用的对象。FROM过程之后会生成一个虚拟表VT1。

    1. (1-J1)笛卡尔积 这个步骤会计算两个相关联表的笛卡尔积(CROSS JOIN) ,生成虚拟表VT1-J1。
    2. (1-J2)ON过滤 这个步骤基于虚拟表VT1-J1这一个虚拟表进行过滤,过滤出所有满足ON 谓词条件的列,生成虚拟表VT1-J2。
    3. (1-J3)添加外部行 如果使用了外连接,保留表中的不符合ON条件的列也会被加入到VT1-J2中,作为外部行,生成虚拟表VT1-J3。
  2. WHERE 对VT1过程中生成的临时表进行过滤,满足where子句的列被插入到VT2表中。

  3. GROUP BY 这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。

  4. HAVING 这个子句对VT3表中的不同的组进行过滤,满足HAVING条件的子句被加入到VT4表中。

  5. SELECT 这个子句对SELECT子句中的元素进行处理,生成VT5表。

    1. (5-1)计算表达式 计算SELECT 子句中的表达式,生成VT5-1
    2. (5-2)DISTINCT 寻找VT5-1中的重复列,并删掉,生成VT5-2
    3. (5-3)TOP 从ORDER BY子句定义的结果中,筛选出符合条件的列。生成VT5-3表
  6. ORDER BY 从VT5-3中的表中,根据 ORDER BY 子句的条件对结果进行排序,生成VC6表。

当然强大的 SQL 执行引擎在实际执行过程用会有各种优化,不一定严格按照这个顺序来。但是在写和看 SQL 的时候可以按照这个逻辑思考。

例子

可以思考一下下面这个语句的执行过程

SELECT C.customerid, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON C.customerid = O.customerid
WHERE C.city = 'Madrid'
GROUP BY C.customerid
HAVING COUNT(O.orderid) < 3
ORDER BY numorders

SQL是关于表的(而不是列)

因为词法排序和逻辑排序上的不同,很多的初学者认为列的值是SQL中的一等公民,实际上,不是。最重要的是表的引用。

比如说

FROM a,b

这个语句实际上是 a cross join b,也就是笛卡尔乘积。比如说,a 中有3列3行数据,b中有5列5行数据。上面的一句产生的结果是一个3+5=8列,3×5=15行的数据。

不过,尽量显式 join 的表,而不要使用逗号。

SQL 中衍生的表可以看做表的变量。

-- A derived table
FROM (SELECT * FROM author) a -- 后边这个变量是可选的
-- Get authors' first and last names, and their age in days
SELECT first_name, last_name, age
FROM (
  SELECT first_name, last_name, current_date - date_of_birth age
  FROM author
)
-- If the age is greater than 10000 days
WHERE age > 10000

在 MySQL 8.0 中还可以使用 with 语句

WITH a AS (
  SELECT first_name, last_name, current_date - date_of_birth age
  FROM author
)
SELECT *
FROM a
WHERE age > 10000

SQL 中的 Select 语句在关系代数中被称作投影(projection)。一旦你生成了表的引用,然后过滤,转换,接着你就可以把它投影成另一种形式。在 select 语句中,你终于可以按列操作生成的表了。也就是说其他的语句都是按表,或者说按照行操作的,只有到了 select 语句中你才可以操作列。

执行完了 select 语句之后,你就可以执行其他的集合排序等等操作了。

  • distinct 删除重复的行
  • union 把两个查询组合起来,并且删除重复的行
  • union all 把两个查询组合起来,并且不删除重复
  • except 做差集并且删除重复的行
  • intersect 求交集

ORDER BY 排序

Ref:

  1. https://web.archive.org/web/20150424213133/http://tech.pro:80/tutorial/1555/10-easy-steps-to-a-complete-understanding-of-sql
  2. http://www.cnblogs.com/myprogram/archive/2013/01/24/2874666.html

MyISAM vs InnoDB: mysql 的两种存储引擎的区别

  • InnoDB 有行级别的锁,而 MyISAM 只能锁定到表级别。
  • InnoDB 有更好的故障恢复机制.
  • MyISAM 有全文索引,InnoDB 直到5.6(Feb 2013)才拥有。
  • InnoDB 实现了事物、外键和关系限制, MyISAM 没有.

ref: https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam

总的来说,引用完整性和事物才是数据库的本质,所以说:“MyISAM is a file system that understands SQL. There’s no comparison. If you want a database engine with MySQL, use InnoDB.”

ref: https://jeremystein.com/journal/innodb-versus-myisam-no-comparison/

mysql 基础知识(4) – 用户和权限

创建用户

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

授权

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';

其中的 % 代表这个用户可以在任意主机登录.

SHOW GRANTS FOR newuser

用来显示一个用户的当前授权

set password for USERNAME = password('xxx')

更改用户密码

重置密码

sudo mysqld_safe --skip-grant-tables --skip-networking &
 
use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password"; # THIS LINE
flush privileges;
quit;

其他问题

EXPLAIN, slow-log

解决小内存机器 MySQL 总是 OOM 的问题

有一台512M内存的小机器总是报数据库错误, 查看了下日志是OOM了

解决方案:

一 Add swap file to cloud instance

http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html

  1. Run dd if=/dev/zero of=/swapfile bs=1M count=1024
  2. Run mkswap /swapfile
  3. Run swapon /swapfile
  4. Add this line /swapfile swap swap defaults 0 0 to /etc/fstab  

Some useful command related to SWAP space:

$ swapon -s   
$ free -k
$ swapoff -a
$ swapon  -a

二 limit mysql buffersize

innodb_buffer_pool_size = 8M

三 limit apache memory cosumption,editing /etc/apache2/mods-enabled/mpm_prefork.conf

<IfModule mpm_prefork_module>
    StartServers        3
    MinSpareServers     3
    MaxSpareServers     5
    MaxRequestWorkers   25
    MaxConnectionsPerChild  0
</IfModule>

mysql 基础知识(3) – 创建修改表和权限

创建数据库

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

创建表?

CREATE TABLE table_name (
    field_name type is_null default options,
    ...
    PRIMAR KEY (id),
    INDEX/KEY index_name (field_name),
) ENGINE=InnoDB;
 
// 注意:KEY is normally a synonym for INDEX

设定 auto_increment

注意 mysql 的关键字是 auto_increment, 而 sqlite 的是 autoincrement

CREATE TABLE(...) AUTO_INCREMENT=xxx;

更改已经存在的表

ALTER TABLE SET AUTO_INCREMENT=xxx;

 

数据类型

字符串

字符串分两种,定长和变长,MySQL处理定长数据比变长数据快得多。CHAR属于定长类型,VARCHAR和TEXT属于变长类型。

  • CHAR的长度为1-255,默认为1,使用CHAR(n)指定长度
  • VARCHAR为0-255,使用VARCHAR(n)指定长度
  • TEXT为65536,MEDIUMTEXT为16k,LONGTEXT为4GB

数字

注意数字后面跟的数字,例如INT(5),并不是限制数字的存储长度,而是限制数字的展示长度(显示时填充0)!可以使用UNSIGNED指定为非负值,默认为signed

日期

使用DATETIME,不要使用TIMESTAMP,防止2038年溢出

tips

创建 modify_time/update_time 字段时使用自动更新时间

`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

设计表要注意每个字段的正交性,不要出现一个字段表示“xx且xx”的状态。

 

更新表

rename table 'old_name' to 'new_name'

添加一列,其中的 COLUMN 关键字是 optional 的。

ALTER TABLE table_name ADD COLUMN field_name type;

更新某个字段的数据类型

Alter TABLE `tableName` MODIFY COLUMN `ColumnName` datatype(length);

比如说:

Alter TABLE `tbl_users` MODIFY COLUMN `dup` VARCHAR(120);

重命名一列

需要注意的是数据类型也需要带上

alter table `user` change `name` `first_name` varchar(128) default null;

添加不同类型的索引

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

ALTER TABLE table_name ADD PRIMARY KEY index_name (column_list)

需要注意的是 mysql 索引的最大长度是 255,也就是在 VARCHAR(255) 以上的列是不能添加索引的,一个改进方法就是另外添加一列存储这一列的 hash 值。

删除字段

删除索引

alter table TABLENAME drop index xxxx

——————之前笔记的分割线————————
 
 

组合索引

 
如果有一个组合索引(col_a,col_b,col_c)
 
下面的情况都会用到这个索引:

col_a = "some value";
col_a = "some value" and col_b = "some value";
col_a = "some value" and col_b = "some value" and col_c = "some value";
col_b = "some value" and col_a = "some value" and col_c = "some value";

对于最后一条语句,mysql会自动优化成第三条的样子
 
下面的情况就不会用到索引:

col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

mysql 基础知识(2) – 增删改查

我们还是使用上篇文章定义的例子来说明问题

插入数据

使用 INSERT 语句.

insert into students(**field_names) values(**VALUES), values(**VALUES);

批量插入

如果一次要插入所有数据的话, 可以直接省略前面的字段名. MySQL 可以一次插入多行数据或者一行数据, 但是这并不是SQL标准规定的. 使用批量插入可以大幅度提高性能。

在批量插入的语句中,如果有一行是错的,那么就会导致整个插入失败,可以使用 insert ignore 语句。

如果要在批量插入不同的表,可以一次执行多个语句,而不是只执行一个。

insert or update

如果要实现 insert or update 的功能,可以使用 insert on duplicate update 语句。

更新数据

使用 UPDATE 语句.

update students set math_score = 100 where first_name = 'luke';

删除数据

使用 DELETE 语句.

delete from students where name = 'luke';

参考

  1. http://www.mysqltutorial.org/mysql-insert-ignore/

RocksDB 基础操作教程

## 打开一个数据库

“`
#include
#include “rocksdb/db.h”

rocksdb::DB* db;
rocksdb::Options options;
options.create_if_missing = true;
options.error_if_exists = true;
rocksdb::Status status = rocksdb::DB::Open(options, “/tmp/testdb”, &db);
assert(status.ok());

“`

通过options制定一些属性, 然后用 `rocksdb::DB::Open`打开. RocksDB 会把使用的配置保存在 `OPTIONS-xxxx` 文件中.

注意上面返回的那个status变量, 在RocksDB中所有会遇到错误的函数都会返回这个变量, 可以用来检查有没有出错.

“`
rocksdb::Status s = …;
if (!s.ok()) cerr << s.ToString() << endl; ``` 关闭数据库, 只需要简单得把指针释放就可以了: `delete db`. ## 读写数据库 基本的Put, Get, Delete: ``` std::string value; rocksdb::Status s = db->Get(rocksdb::ReadOptions(), key1, &value);
if (s.ok()) s = db->Put(rocksdb::WriteOptions(), key2, value);
if (s.ok()) s = db->Delete(rocksdb::WriteOptions(), key1);
“`

注意其中每次都检查了操作是否成功.

每次 Get 操作都会导致至少一次的memcpy, 如果不想要这种浪费的话, 可以使用 PinnableSlice 操作.

“`
PinnableSlice pinnable_val;
rocksdb::Status s = db->Get(rocksdb::ReadOptions(), key1, &pinnable_val);
“`

## 原子操作

使用`WriteBatch`来构成一个原子性的操作. 什么是原子性操作总不用多说吧…原子操作不仅保证了原子性, 而且一般来说对性能也有帮助

“`
#include “rocksdb/write_batch.h”

std::string value;
rocksdb::Status s = db->Get(rocksdb::ReadOptions(), key1, &value);
if (s.ok()) {
rocksdb::WriteBatch batch;
batch.Delete(key1);
batch.Put(key2, value);
s = db->Write(rocksdb::WriteOptions(), &batch);
}
“`

## 同步与异步读写

这块没看明白…

默认的是异步读写, 如果使用了`sync`这个标志, 那么就是同步读写了

“`
rocksdb::WriteOptions write_options;
write_options.sync = true;
db->Put(write_options, …);
“`

异步读写经常会比同步读写快上1000倍, 但是当机器down掉的时候, 会丢失最后的几个写入. 不过通常来说, 可以认为异步读写安全性也是够的.

除了可以使用异步读写以外, 还可以使用 `WriteBatch` 来批量读写.

## 并发

一个数据库同时只能被一个进程读写. 但是一个db实例的`Get`操作都是线程安全的, 而`WriteBatch`等操作可能需要其他一些同步机制

## Merge 操作符

待续

## Iterators

遍历所有的key

“`
rocksdb::Iterator* it = db->NewIterator(rocksdb::ReadOptions());
for (it->SeekToFirst(); it->Valid(); it->Next()) {
cout << it->key().ToString() << ": " << it->value().ToString() << endl; } assert(it->status().ok()); // Check for any errors found during the scan
delete it;
“`

遍历[start, limit)之间的值

“`
for (it->Seek(start);
it->Valid() && it->key().ToString() < limit; it->Next()) {

}
assert(it->status().ok()); // Check for any errors found during the scan
“`

反向遍历

“`
for (it->SeekToLast(); it->Valid(); it->Prev()) {

}
assert(it->status().ok()); // Check for any errors found during the scan
“`

## Snapshot(快照)

Snapshot 提供了当前系统在某一点的一个只读的状态表示.

“`
rocksdb::ReadOptions options;
options.snapshot = db->GetSnapshot();
… apply some updates to db …
rocksdb::Iterator* iter = db->NewIterator(options);
… read using iter to view the state when the snapshot was created …
delete iter;
db->ReleaseSnapshot(options.snapshot);
“`

注意这里通过snapshot读到的都是在做snapshot那个时间点的数据库的值.

## Slice

上面提到的 `iter->key()` 和 `iter-value()` 的返回值都是 `rocksdb::Slice` 类型的. Slice 仅仅是一个包含了长度和指针的bytearray. 它本身并不储存值, 这样也就避免了拷贝.

Slice和string的互相转换:

“`
rocksdb::Slice s1 = “hello”;

std::string str(“world”);
rocksdb::Slice s2 = str;

std::string str = s1.ToString();
assert(str == std::string(“hello”));
“`

未完待续…

RocksDB 常见问题

## RocksDB 会抛出异常吗?

不会, RocksDB会返回一个Status表示成功或者失败, 但是RocksDB并没有捕获STL中的异常, 比如bad_alloc这种

## 基础操作是线程安全的吗?

是的

## 可以使用多个进程(process)同时读写 RocksDB 吗?

不可以, 当然只读模式随意