分类 web后端 下的文章

100万条数据里面随机取10条

错误的语句

select id,name from news_6 order by rand() limit 10

100万条数据里面随机取10条 纯SQL语句实现

select id,name from news_6 where id > (

select ROUND(RAND() * ((SELECT MAX(id) as max FROM news_6)  -  (SELECT MIN(id) as min FROM news_6))) + (SELECT MIN(id) as min FROM news_6) - 10

) order by id asc limit 10;

上面语句保持在一个小数内10000条,where 语句 遇上带有rand()函数的子语句subquery会有问题。最后用join实现。

select t.* from (

select t1.id,t1.name from news_6 t1 join (

select ROUND(RAND() * ((SELECT MAX(id) as max FROM news_6)  -  (SELECT MIN(id) as min FROM news_6))) + (SELECT MIN(id) as min FROM news_6) - 30 as id

) t2 where t1.id>t2.id order by id asc limit 30

) t order by RAND() limit 10;

mysq分组内排序

#分组第一名
select * from birecord where platform='okex-swap' and concat(mark, addtime) in (select concat(mark, max(addtime)) from birecord where platform='okex-swap' group by mark)

#分组内前两名,查询速度慢
select * from birecord m where platform='okex-swap' and (select count(*) from birecord n where platform='okex-swap' and n.mark=m.mark and n.addtime>m.addtime)<2;

#分组内前两名,addtime值前两名,可能超过两名,mysql8.0后支持
select * from (select *,row_number() over(partition by mark order by addtime) as num from birecord where platform='okex-swap') n where num<3;
#分组内前两名,addtime排序前两名,mysql8.0后支持
select * from (select *,rank() over(partition by mark order by addtime) as num from birecord where platform='okex-swap') n where num<3;

#可以标记顺序但做不了分组排序
select *,(@row_num:=@row_num+1) as num from birecord,(select @row_num:=0) as t group by mark;

mysql根据逗号隔开的字段数据把一行数据拆分成多行数据

# 查找出被逗号分隔字段需要拆分的最大数量
select max((LENGTH(逗号分隔的字段)-LENGTH(REPLACE(逗号分隔的字段, ',', ''))+1)) from 处理表 where 条件;

# 创建一张临时表用于联合查询,方便把处理表单行记录分隔为多行
CREATE TEMPORARY TABLE incre_table (
    `id` int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);
insert into incre_table values (4);
insert into incre_table values (5);
insert into incre_table values (6);
insert into incre_table values (7);
insert into incre_table values (8);
insert into incre_table values (9);
insert into incre_table values (10);
# ... 大于 需要拆分的最大数量

# 关键在于连表查询 ON b.id <= 逗号分隔的数量
SELECT
    a.id,
    substring_index(
        substring_index(a.逗号分隔的字段, ',', b.id),
        ',', - 1
    )
FROM
    处理表 a
RIGHT JOIN incre_table b ON b.id <= (
    LENGTH(a.逗号分隔的字段) - LENGTH(REPLACE (a.逗号分隔的字段, ',', '')) + 1
)
WHERE
    a.条件;

distinct 和 group by的使用

公司同事有一个小项目的30w数据搜索用到like和排重查询比较慢,我对语句做了下优化。

mysql> desc shop;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| uid          | bigint(20) unsigned | NO   | MUL | 0       |                |
| sid          | bigint(20) unsigned | YES  | MUL | 0       |                |
| nick         | char(128)           | YES  | MUL |         |                |
| shop_type    | char(1)             | YES  |     | C       |                |
| shop_score   | int(10) unsigned    | YES  |     | 0       |                |
| shop_level   | int(10) unsigned    | YES  |     | 0       |                |
| category_id1 | int(10)             | YES  | MUL | 0       |                |
| category_id2 | int(10) unsigned    | NO   | MUL | 0       |                |
| shop_title   | varchar(128)        | YES  |     |         |                |
| shop_avatar  | varchar(255)        | YES  |     |         |                |
| sort         | int(6)              | NO   |     | 0       |                |
| locate       | varchar(45)         | NO   |     |         |                |
| disabled     | tinyint(1)          | NO   |     | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
14 rows in set (0.01 sec)

mysql> explain select * from shop,(select distinct sid from shop where disabled = 0 and shop_title like '%迪卡侬%' limit 10) shopx where shop.sid=shopx.sid group by shop.sid;
+----+-------------+------------+------+---------------+---------+---------+-----------+--------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key     | key_len | ref       | rows   | Extra                                        |
+----+-------------+------------+------+---------------+---------+---------+-----------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL    | NULL    | NULL      |     10 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | shop       | ref  | INX_sid       | INX_sid | 9       | shopx.sid |      1 | NULL                                         |
|  2 | DERIVED     | shop       | ALL  | INX_sid       | NULL    | NULL    | NULL      | 269483 | Using where; Using temporary                 |
+----+-------------+------------+------+---------------+---------+---------+-----------+--------+----------------------------------------------+
3 rows in set (0.01 sec)

mysql> explain select  * from shop where disabled = 0 and shop_title like '%迪卡侬%' group by sid limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | shop  | index | INX_sid       | INX_sid | 9       | NULL |   10 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> select SQL_NO_CACHE distinct sid from shop where disabled = 0 and shop_title like '%迪卡侬%' limit 10;
9 rows in set (0.45 sec)

mysql> select SQL_NO_CACHE * from shop,(select distinct sid from shop where disabled = 0 and shop_title like '%迪卡侬%' limit 10) shopx where shop.sid=shopx.sid group by shop.sid;
...
9 rows in set (0.46 sec)

mysql> select SQL_NO_CACHE  * from shop where disabled = 0 and shop_title like '%迪卡侬%' group by sid limit 10;
...
9 rows in set (4.96 sec)

DNS学习

https://github.com/forthxu/mydns

mydns

DNS查询器,主要用来学习dns协议和c#软件开发。

每当我们在浏览器上敲入任何一个域名访问某个网站的时候,我们都要使用Dns协议进行一次”域名:IP”的查询;作为命令行使用者,与dns有关用的最多的就是Nslookup 命令吧;作为程序员,以c#程序员为例,要得到一个域名的ip大概也是这么一行“System.Net.Dns.GetHostByName(string UriHostName)”。

在这简单使用的背面,很少人会真了解其协议的规则,这也许就是高度封装给程序员带来的一点麻烦吧。下面来了解一下dns协议的内容。

DNS结构:

整个dns分为5个部分,分别为Header、Question、Answer、Authority、Additional。

dns协议头

其中头部的大小是固定的为12字节。这5个部分不是全部都是必须的,在向服务器发送查询请求的时候,只需要前2个。回复的时候也不一定包含5个(按查询的内容和返回的信息而定)。

Header 部分:

header头部分是必须的,无论发送查询或者返回结果都需要该部分,且长度一定,为12字节。结果如下图

dns协议头部

ID:长度为16位,是一个用户发送查询的时候定义的随机数,当服务器返回结果的时候,返回包的ID与用户发送的一致。

QR:长度1位,值0是请求,1是应答。

Opcode:长度4位,值0是标准查询,1是反向查询,2是服务器状态查询。

AA:长度1位,授权应答(Authoritative Answer) - 这个比特位在应答的时候才有意义,指出给出应答的服务器是查询域名的授权解析服务器。

TC:长度1位,截断(TrunCation) - 用来指出报文比允许的长度还要长,导致被截断。

RD:长度1位,期望递归(Recursion Desired) - 这个比特位被请求设置,应答的时候使用的相同的值返回。如果设置了RD,就建议域名服务器进行递归解析,递归查询的支持是可选的。

RA:长度1位,支持递归(Recursion Available) - 这个比特位在应答中设置或取消,用来代表服务器是否支持递归查询。

Z:长度3位,保留值,值为0.

RCode:长度4位,应答码,类似http的stateCode一样,值0没有错误、1格式错误、2服务器错误、3名字错误、4服务器不支持、5拒绝。

QDCount:长度16位,报文请求段中的问题记录数。

ANCount:长度16位,报文回答段中的回答记录数。

NSCOUNT :长度16位,报文授权段中的授权记录数。

ARCOUNT :长度16位,报文附加段中的附加记录数。

Question 部分:

这部分的内容是你要查询的内容。也是必须的。

dns协议question部分

QName:是你要查询的域名,属于不定长字段。他的格式是可变长度数据格式,一般为“长度(1字节)+N字节内容(N由前面的长度定义,不超过63,下面可变数据格式有说明)+~~~+长度0。以一个长度单位N为开始,然后连续的N字节为其内容,然后又是一个N2长度的一字节,然后后面又是N2个字节内容,直到遇到长度为0的长度标记。

假设QName字段的内容是 forth.xu ,则字节内容为:

05 66 6f 72 74 68 02 78 75 0

第一个字节是长度:5,那么接下来的5个字节都是内容66 6f 72 74 68 ,ascii码转过来是“forth”。然后又是长度2,后面2个字节的内容78 75 字母为xu,然后是长度0,表示结束了。最后还要把两段文字组合起来中间加点号成forth.xu。

QType:长度16位,表示查询类型。取值大概如下:

enum QueryType //查询的资源记录类型。

{

A=0x01, //指定计算机 IP 地址。

NS=0x02, //指定用于命名区域的 DNS 名称服务器。

MD=0x03, //指定邮件接收站(此类型已经过时了,使用MX代替)

MF=0x04, //指定邮件中转站(此类型已经过时了,使用MX代替)

CNAME=0x05, //指定用于别名的规范名称。

SOA=0x06, //指定用于 DNS 区域的“起始授权机构”。

MB=0x07, //指定邮箱域名。

MG=0x08, //指定邮件组成员。

MR=0x09, //指定邮件重命名域名。

NULL=0x0A, //指定空的资源记。

WKS=0x0B, //描述已知服务。

PTR=0x0C, //如果查询是 IP 地址,则指定计算机名;否则指定指向其它信息的指针。

HINFO=0x0D, //指定计算机 CPU 以及操作系统类型。

MINFO=0x0E, //指定邮箱或邮件列表信息。

MX=0x0F, //指定邮件交换器。

TXT=0x10, //指定文本信息。

UINFO=0x64, //指定用户信息。

UID=0x65, //指定用户标识符。

GID=0x66, //指定组名的组标识符。

ANY=0xFF //指定所有数据类型。

};

QClass:长度为16位,表示分类。

enum QueryClass //指定信息的协议组。

{

IN=0x01, //指定 Internet 类别。

CSNET=0x02, //指定 CSNET 类别。(已过时)

CHAOS=0x03, //指定 Chaos 类别。

HESIOD=0x04,//指定 MIT Athena Hesiod 类别。

ANY=0xFF //指定任何以前列出的通配符。

};

Answer、Authority、Additional:

接下来的3个结构,格式可以说相同。都是如下图的结构和字段。

dns协议资源结构

Name:回复查询的域名,不定长。 这里的名字和Question结构的名字是一样的,但是为了节省资源,在question结构是这样,在之后的结构中,如果name字段的内容前面有出现了,那么他就不会再浪费空间去重复记录,而是指向某个前面出现了name的位置。如:

在question结构中的name字段的内容为forth.xu,即“05 66 6f 72 74 68 02 78 75 0”。然后在第3个结构中的answer中,第一个字段name的内容也是forth.xu,那么他会指向question中的name地址,让我们去那个地址读name内容。所以此时answer结构的name字段的内容为:

C0 0C

C0:这时不是表示接下来的内容有多长,而是接下来的内容在偏移量中,

0C:十进制是12的意思,就是偏移12个字节。从头开始12位,因为Header结构是固定的12字节,所以偏移0C就是到了Question的Name字段,即上面的“05 66 6f 72 74 68 02 78 75 0”。

Type:同上QType。

Class:同上QClass。

TTL:生存时间。4字节,指示RDATA中的资源记录在缓存的生存时间。

RDLength:资源的长度。

RDdata:资源的内容。

可变长度数据格式 说明:

可变长度有两种内容格式:

长度方式:

1字节长度N + N字节内容 [+ 1字节长度N + N字节内容] + 0x00

N最多不超过63也就是2^6,因为最前面两位用来表示地址方式的偏移量。

地址方式:

第一字节大于等于0xc0开头,表示指针偏移量,所以偏移量的计算其实并不是指c0后面跟着的一个字节,不然一个字节的偏移量最多只有256个位置。

比如偏移量超过255,是300 ,他的小端格式为0x012c,那么他在内存中的表示应该是这样的:0xc12c。

这个300偏移量应该是这样计算的:

300 = 0x012c(小头) = 00000001 00101100(小头) = 00101100 00000001(大头) = 0x2c01(大头)

0xc0 & 0x2c01(大头) = 11000000 & 00101100 00000001 = 00101100 11000001(大头) = 11000001 00101100(小头) = 0xC12C

也就是:

0xc0 & (((0x012c << 8) & 0xff) & (0x012c >> 8)) = 0xC12C

同理反向计算:

((0xC12C & ~0xc0) & 0xff) << 8 & (0xC12C & ~0xc0) >> 8 = 0x012c = 300

需要注意是可变长度的格式有3中结尾方式:

  1. 长度+内容+~+长度0
  2. 偏移标识+偏移量
  3. 长度+内容+~+偏移标识+偏移量

现在来说说这个程序了

我按dns协议的结构把项目分成 MyDnsHeader.cs、MyDnsQuestion.cs、MyDnsRecord.cs 这样的3个大结构。

发送dns请求时只需要构造MyDnsHeader和MyDnsQuestion结构,然后通过GetBytes()函数得到构造好的字节数组,然后通过udp发送出去。然后接受来自服务器的响应,将接收到的字节数组通过Parse(byte[] recvData)方法让3个结构去解析,最后通过这些结构的属性字段获取相应的查询信息。

其中的资源记录,目前能分析 A记录、SOA记录、TXT记录、CNAME记录、MX记录、NS记录。

界面截图:

dns界面截图:

设计和实施 DNS 服务器和客户端服务时可能用到的RFC相关规范
RFC 标题
RFC1034 域名 - 概念和工具
RFC1035 域名 - 实现和规范
RFC1123 Internet 主机 - 应用和支持的要求
RFC1886 支持 IP 版本 6 的 DNS 扩展名
RFC1995 DNS 中的增量区域传输
RFC1996 提示通知区域更改的机制 (DNS NOTIFY)
RFC2136 域名系统中的动态更新 (DNS UPDATE)
RFC2181 对 DNS 规范的说明
RFC2308 DNS 查询的负缓存 (DNS NCACHE)
RFC2535 域名系统安全扩展 (DNSSEC)
RFC2671 DNS 的扩展机制 (EDNS0)
RFC2782 指定服务位置的 DNS RR (DNS SRV)
RFC2930 DNS 的密钥建立 (TKEY RR)
RFC3645 DNS (GSS-TSIG) 密钥事务身分验证的通用安全服务算法
RFC3646 IPv6 (DHCPv6) 动态主机配置协议的 DNS 配置选项

MySQL数据库名、表名、列名、别名、字段值大小写规则

MySQL在Window下数据库名、表名、列名、别名大小写规则不敏感。

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:
  1、数据库名与表名是严格区分大小写的;
  2、表的别名是严格区分大小写的;
  3、列名与列的别名在所有的情况下均是忽略大小写的;
  4、字段值默认字符集情况下是大小写不敏感的。

同时MySQL中数据库名和表名的大小写敏感受参数lower_case_table_names影响,为0时不做处理敏感的地方区分大小写,为1时,表示将转化为小写后存储,查询会做转化,因此不区分大小写,此配置的操作对Window系统的MySQL同样有效。

Linux之所以会有架构敏感,全因为Linux的文件存储系统。数据库名和表名在系统的存储形式分别是文件夹和文件,因此会敏感。

字段值的大小写由mysql的校对规则来控制。提到校对规则,就不得不说字符集。字符集是一套符号和编码,校对规则是在字符集内用于比较字符的一套规则。
一般而言,校对规则以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束 。比如 utf8字符集,utf8_general_ci,表示不区分大小写,这个是utf8字符集默认的校对规则;utf8_general_cs表示区分大小写,utf8_bin表示二进制比较,同样也区分大小写 。