sql

使用 partition by 查找并删除 MySQL 数据库中重复的行

在创建 MySQL 数据表的时候,经常会忘记给某个字段添加 unique 索引,但是等到想添加的时候又已经有了重复数据,这时候就需要删除重复数据。

准备数据

本文使用如下的数据作为演示:

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO contacts (first_name,last_name,email)
VALUES ("Carine ","Schmitt","[email protected]"),
       ("Jean","King","[email protected]"),
       ("Peter","Ferguson","[email protected]"),
       ("Janine ","Labrune","[email protected]"),
       ("Jonas ","Bergulfsen","[email protected]"),
       ("Janine ","Labrune","[email protected]"),
       ("Susan","Nelson","[email protected]"),
       ("Zbyszek ","Piestrzeniewicz","[email protected]"),
       ("Roland","Keitel","[email protected]"),
       ("Julie","Murphy","[email protected]"),
       ("Kwai","Lee","[email protected]"),
       ("Jean","King","[email protected]"),
       ("Susan","Nelson","[email protected]"),
       ("Roland","Keitel","[email protected]"),
       ("Roland","Keitel","[email protected]");

注意其中有一行重复了三次。输入完成后,数据如图所示:

file

查找重复的行

使用 group by 和 having

假设我们要通过 email 字段来查找重复值。通过使用 group by 和 having 子句可以查找到哪些行是重复的。

SELECT
    email,
    COUNT(email)
FROM
    contacts
GROUP BY email
HAVING COUNT(email) > 1;

file

Having 就类似于 Group by 之后的 where 子句。但是这个语句还是很难解决我们的问题,我们只知道发生重复的第一行了,而不知道哪些行是重复的。这时候可以使用 partition by 语句。

使用 partition by 找出所有的重复行

需要注意的是,partition by 只有在 MySQL 8.0 之后才支持,而现在常用的是 5.6 和 5.7 版本。

Partition 语句又叫做窗口聚合语句,也就是说他会把同一个值的行聚合成一个窗口,但是和 Group by 语句不同的是,窗口内的每一个行并没有被压缩成一行,具体说 Partition by 的语法是:

window_function_name(expression)
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

删除重复的行

删除的方法有很多种,这里介绍两种。

References

  1. https://www.mysqltutorial.org/mysql-window-functions/
    2.

MySQL 中的 wait_timeout 是做什么的?

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

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

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

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

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

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

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

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

有一种思路是在检测到和数据库链接断开的时候,自动重连,但是这样会破坏 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 连接数据库的工具。参考这里, 不过这种方法比较 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 收到后将事务先写入 redo log,然后重放事务,即在 slave 上重新执行一次事务,从而达到主从机事务一致的效果。 

MySQL 的三种日志

  • binlog
  • redo log
  • undo log

参考

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/

完全理解 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 排序

参考

  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 有更好的故障恢复机制。
  • InnoDB 实现了事务、外键和关系限制,MyISAM 没有.

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

聚簇索引

MyISAM 没有使用聚簇索引,InnoDB 使用了聚簇索引。

参考

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

  2. InnoDB versus MyISAM: no comparison

解决小内存机器 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

innodbbufferpool_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]

推荐使用 unicode 对应的编码模式:

create database blog DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;

查询当前 MySQL 中的表以及字段的信息

可以使用 information_schema 库中的表来查询。

SELECT column_name, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'xxx';
select table_name from information_schema.tables where table_schema = "xxx";

创建表

请注意,一定要是用 utf8mb4 对应的编码模式:

CREATE TABLE table_name (
    field_name type is_null default options,
    ...
    PRIMAR KEY (id),
    INDEX/KEY index_name (field_name),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
 
// 注意: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

创建 modifytime/updatetime 字段时使用自动更新时间

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 &#x60;tableName&#x60; MODIFY COLUMN &#x60;ColumnName&#x60; datatype(length);

比如说:

Alter TABLE &#x60;tbl_users&#x60; MODIFY COLUMN &#x60;dup&#x60; 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

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

组合索引

 
如果有一个组合索引 (cola,colb,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";

参考

  1. https://www.mysqltutorial.org/mysql-views/mysql-show-view/