数据库

MySQL 性能小技巧和在 Django 中的应用

对于 delete update insert 等语句一定要使用 limit 子句限制影响的行数,避免一次更改特别多的行,造成数据库假死

while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了,退出!
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

2. 垂直分割

把不会用作索引的,或者是过长的字段可以考虑使用其他存储引擎,比如 rocksdb 等。

3. IPv4 地址可以存为 uint32

使用 uint32 存储 IP 地址不光可以节省空间,而且可以按区间查询。

4. 避免 select *

从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和应用服务器是两台独立的服务器的话,这还会增加网络传输的负载。

所以,你应该养成一个需要什么就取什么的好的习惯。

不要使用:

SELECT * FROM user WHERE user_id = 1

使用:

SELECT username FROM user WHERE user_id = 1

在 django 中,可以使用 only

books = Book.objects.filter(author="Jim").only('book_name')

5. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。

在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)

SELECT * FROM user WHERE country = 'China'
SELECT 1 FROM user WHERE country = 'China' LIMIT 1

在 Django 中可以使用 [:1] 来添加 limit 1

6. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

7. 尽量让查询能 fit 进内存中

参考:

  1. https://coolshell.cn/articles/1846.html

MySQL 中的 wait_timeout 是做什么的?

Mysql 中默认的 wait_timeout 和 interactive_timeout 的值是八小时,也就是一个连接(交互式和非交互式的)只有在 8 小时没有活动之后才会被关闭掉。对于互联网公司来说,这个值实在太大了,一个库可能被很多脚本和服务访问,可能只是一个简短的查询就不需要数据库了,如果每个查询都占据了8小时的时间,那么 mysql 很快连接数就会满了,报出 too many connections 错误。

mysql 默认的连接数可以修改 max_connections 参数,但是一个服务器能支撑的连接数显然是由硬件决定的。

设置 wait_timeout 过短可能会造成一些问题,如果在 django 中两次查询的之间时间大于 wait_timeout,就会报 (2006, ‘MySQL server has gone away’)。django 官方给的建议是:

  1. 当你的脚本不需要使用数据库的时候,主动关闭连接,比如在 django 中使用 from django.db import connection; connection.close()
  2. 增大 wait_timeout 的值

不过django默认 CONN_MAX_AGE 是 0,也就是在查询数据库之后会立即关闭链接,理论上应该不会报这个错误。但是这样不能复用链接,会造成对数据压力很大。

CONN_MAX_AGE 应该小于数据库本身的最大连接时间wait_timeout,否则应用程序可能会获取到连接超时的数据库连接,这时会出现MySQL server has gone away的报错。

可以在 settings.py 中动态地获取并填充这个值,然后写到 CONN_MAX_AGE 中

理论上这样就不会再报错了,但是难免数据库重启或者什么时候会报错,总是使用 close_old_connections 还是很烦。

有一种思路是在检测到和数据库链接断开的时候,自动重连,但是这样会破坏 django.db.atomic,但是可以实现一种不同的backend。可以参考这两个:

  1. https://github.com/django/django/pull/2740/commits/38f58aa4d751ad83f1dc76d5b945a1036239584f

  2. https://github.com/django/django/pull/2454/commits/36b8bf870cab183b7ad63c0d8e7e8c02e314a053#diff-f8a587a973ef4c3a94d7550a5b85342c

还有一种解决思路是使用 connection pooling,我们可以使用 sqlalchemy 的 连接池作为django连接数据库的工具。参考这里:http://menendez.com/blog/mysql-connection-pooling-django-and-sqlalchemy/, 不过这种方法比较 hack。

参考

  1. https://code.djangoproject.com/ticket/21597#no2
  2. https://github.com/django/django/commit/2ee21d9f0d9eaed0494f3b9cd4b5bc9beffffae5
  3. https://stackoverflow.com/questions/1125504/django-persistent-database-connection
  4. django 优化
  5. https://docs.djangoproject.com/en/2.1/ref/databases/#persistent-connections
  6. 如何设置 max_age

mysql 基础知识(8) – 主从复制

mysql 有三种主从复制方式

MySQL传统的高可用解决方案是通过binlog复制来搭建主从或一主多从的数据库集群。主从之间的复制模式支持异步模式(async replication)和半同步模式(semi-sync replication)。无论哪种模式下,都是主库master提供读写事务的能力,而slave只能提供只读事务的能力。在master上执行的更新事务通过binlog复制的方式传送给slave,slave收到后将事务先写入relay log,然后重放事务,即在slave上重新执行一次事务,从而达到主从机事务一致的效果。 

其他文档:

http://mysql.taobao.org/monthly/2017/08/01/
http://blog.csdn.net/d6619309/article/details/53691352
http://blog.51cto.com/wangwei007/1893703
https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04
http://blog.csdn.net/d6619309/article/details/53691352
http://mysql.taobao.org/monthly/2017/08/01/

mysql 基础知识(7) – JSON 字段

在前公司的时候,大家习惯在每个表加一个 extra 字段来表示一些额外的字段,然后在 ORM 中使用的时候再解析出来,方便了扩展字段,但是缺点也很明显,extra 字段只能读取而无法进行查询。MySQL 5.7 终于支持了 json 字段,相当于加入了一些 NoSQL 的特性,这样就可以很方便得查询了。

json 字段的使用

建表:

CREATE TABLE `book` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

直接使用 json 类型就可以了。注意 json 字段不可以作为主键,不可以作为外键,不过既然是 json 字段了,谁会这么做呢。。

插入:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

使用一个 json 字符串作为值插入即可。或者你也可以使用 json 相关的函数来表示json。

json 相关函数

json path

-- returns "SitePoint":
SELECT JSON_EXTRACT(
  '{"id": 1, "website": "SitePoint"}', 
  '$.website'
);

json path 的语法,用 $ 开头,然后跟着下面的选择器:

  • . 点后面跟着跟着一个字典里的名字, 比如 $.website
  • [N] 表示数组里的第 N 个元素
  • .[*] 表示选择字典里的所有元素
  • [*] 表示选择数组里的所有元素
  • prefix**suffix 表示以 prefix 开头,suffix 结尾的所有路径

举个栗子

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}
the following paths:

$.a returns 1
$.c returns [3, 4]
$.c[1] returns 4
$.d.e returns 5
$**.e returns [5]

构造、修改 json 的函数

函数都比较简单,看注释就明白了。

-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

-- returns ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

-- returns 1:
SELECT JSON_VALID('[1, 2, "abc"]');

还有其他一些函数,可以查看文档:

  • JSON_SET(doc, path, val[, path, val]…) —
    inserts or updates data in the document
  • JSON_INSERT(doc, path, val[, path, val]…) —
    inserts data into the document
  • JSON_REPLACE(doc, path, val[, path, val]…) —
    replaces data in the document
  • JSON_MERGE(doc, doc[, doc]…) —
    merges two or more documents
  • JSON_ARRAY_APPEND(doc, path, val[, path, val]…) —
    appends values to the end of an array
  • JSON_ARRAY_INSERT(doc, path, val[, path, val]…) —
    inserts an array within the document
  • JSON_REMOVE(doc, path[, path]…) —
    removes data from the document.

查询 json 函数

用于 where 子句中的函数

json_contains 用于选取数组中包含某个元素的行

-- all books with the 'JavaScript' tag:
SELECT * FROM `book` 
WHERE JSON_CONTAINS(tags, '["JavaScript"]');

json_search 用于选取字典中包含某个值的行

-- all books with tags starting 'Java':
SELECT * FROM `book` 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

用于 select 子句中的 json 函数

可以使用 json path 语法从得到的 json 文档中抽取出某个值。

select 语句

要想在select语句中使用 json path 抽取元素可以使用下面的语法,也就是 column->path

SELECT
  name,
  tags->"$[0]" AS `tag1`
FROM `book`;

一个更复杂一点的例子:

id name profile
1 Craig {“twitter”: “@craigbuckler”,“facebook”: “craigbuckler”,“googleplus”: “craigbuckler”}
2 SitePoint {“twitter”: “@sitepointdotcom”}
SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;
SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

REF:

  1. https://www.sitepoint.com/use-json-data-fields-mysql-databases/

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/