某次清洗项目笔记

发表于 | 分类于

mysql常用命令与查询语句

连接mysql:

mysql -u username -p -h 主机 -D db_name

断开连接：quit or Ctrl + C

所有命令末尾加上;或者\g

查看所有数据库：

show databases;

切换数据库：

use <数据库名>;

查看所有数据表：

show tables;

通配符：

%百分号通配符: 表示任何字符出现任意次数 (可以是0次).
_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.

获取表中行数：

select count(md5_id) from t_kaitinggonggaos;

查看表中字段数据类型：

show COLUMNS from table

获取庭审公开网的数据：

select * from t_kaitinggonggaos_0 where url like 'http://tingshen%';

获取前两行并以友好格式展示：

select * from t_kaitinggonggaos_0 where url like 'http://tingshen%' limit 0,2\G

查看索引:

show index from table

1
2
3
4
5
6
7
8
9
10
11
12
13
Table: t_kaitinggonggaos    # 表名
   Non_unique: 0            # 0：索引不能包括重复值；1：索引可以包括重复值
     Key_name: PRIMARY      # 索引名称
 Seq_in_index: 1            # 索引中的列序列号，从1开始
  Column_name: _id          # 列名称（索引加在哪列上）
    Collation: A            # 列以什么方式存储在索引中，'A'升序或NULL无分类
  Cardinality: 399070       # 索引中唯一值的数目的估计值 
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

查询结果中进行二次查询：

select * from (select * from table) temp condition

必须给第一次查询得结果集取个名字。

查询name字段的值出现的次数大于10的name值：

select name from table group by name having count(name) > 10

查询时多字段排序,DESC降序，ASC升序：

select * from table order by column1 DESC, column2 ASC

查询出现次数最多的字段值：

select count(name) number from 表名 group by name order by number desc limit 1

== > 二叉搜索树 ,每个节点最多有两个子树，左子树的值都小于父节点，右子树的值都大于父节点；

时间复杂度（O(logn)）,磁盘IO的次数由树的高度来决定，数据量大时树的深度过深。

== > 平衡二叉树 （每个节点左右子树高度差至多为1），插入过程要不断调整树的形状。

== > B-Tree（平衡树/B树）：m阶B-Tree每个节点最多拥有m个子树，可以大大减少树的深度。

查找次数 = 以树的分叉数为底，记录总数的对数

B树主要应用于文件系统以及部分数据库索引，如MongoDB，大部分关系型数据库索引则是使用B+树实现。

== > B+树：所有的非叶子节点只存储关键字信息，叶子结点中包含了全部元素的信息，叶子节点之间都有一个链指针。

操作系统从磁盘读取数据到内存是以磁盘块（block）为基本单位的；

1、B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。

 2、由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。

 3、由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。

建立索引可以提高查询的速度，但是会影响写入和修改的速度

当写入mongodb时，一般我们会采用update_one方法，当集合中数据量较大时，速度会明显降低，原因其实是因为update_one虽然是写入（或者说修改）操作，理论上讲不建立索引更快，但实际上update_one里面包含查询的动作，所以建立索引更快。

mongodb在插入数据时会自动建立一个索引，其对应的字段为_id，但实际上无法优于我们主动建立的索引。

查询语句带有like无法使用索引，查询变慢

鉴于此点，在读取数据库时，不写查询条件，遍历全库，然后在python脚本中进行判断；此外，还可以设置每次查询的limit,之后每次查询接着上一次的序号开始，这里查询的时候需要排序order by 索引列。

用到的语句：

1
2
3
4
5
6
# 第一次查询 （_id表示建立了索引的列）
select * from t_kaitinggonggaos order by _id limit 2000
# 第n次查询，n>=2
select * from t_kaitinggonggao where _id > 'xxx' order by _id limit 2000
# 上面的xxx是第n-1次查询结果集中最后一条记录的_id值
# 当某次返回的集合中的记录数小于limit的值，就表示读取完了

虽然以上方式，遍历完全库的速度和使用like做查询的速度相差不大，但是由于不使用like语句对数据库服务器造成的压力减少了很多，而且又设置了limit，这样每次读取到内存的数据也会减少，也减轻了跑程序的服务器的压力。

注意这里与我们常说的分页查询的区别，分页查询时使用limit可以提高单次查询的速度，改善用户体验；而当我们的需求本身就是查询全库时，使用limit我们的查询次数就会变多，每次的时间短；不使用limit查询次数少，但是查询时间长；但是这两种情况总的查询时间相差不大，使用limit的好处在于可以给我们的服务器减轻内存压力。

mongo游标超时问题

这里要说到读取mysql数据库和mongodb的区别，在读取数据时，mysql默认在内存中缓存下所有行然后再处理，如果行太多导致内存溢出，你的程序就会被杀死；而mongo就更友好一些，通过batchSize(int size)设置每次网络请求返回的document条数，比如你需要查询50000条数据，mongodb不会一次性全部load并返回给client，而是每次返回batchSize条，遍历完之后再通过网络IO获取直到cursor耗尽。默认情况下，首次批量获取101个document或者1M的数据（当101个文档小于1M大小时，就返回101条文档；若101个文档超过1M大小，则返回1M大小的数据），此后每次4M;但是有一个默认时间10分钟，要求在10分钟之内处理完每一批次返回的数据，从而进行下一次IO操作，否则就会出现游标超时错误。

游标超时的根本原因是：十分钟内没有操作游标即游标长时间处于不活跃状态；

我们可以通过设置no_cursor_timeout=True来使游标不会超时，但是这种方式非常危险，除非你确保会事后一定会关闭连接，否则不要使用该方式。

此外我们可以自己设置batchSize的大小（大于1的整数），并且batchSize条文档数的大小不能超过16M限制,这是mongo的内部机制规定的每次返回的文档总大小不能超过16M,相应地这也就对应了另一个限制，mongo还要求单个document大小不能超过16M。

stats()方法可以返回某集合和文档信息，其中avgObjSize表示一条文档平均大小（单位：字节Byte）。利用该大小，我们就可以计算1M或者4M大概是多少条文档了。

使用例子：db.getCollection(collection_name).stats()

通过执行该方法，我的collection中平均一条文档大小为227B,则4M的大小大概有18476条

计算方式：

1
2
3
1M = 1024KB = (1024 * 1024) B
1024 * 1024 // 227 = 4619  # 1M的大小大概有4619条文档
4619 * 4 = 18476          # 4M的大小大概有18476条文档

所以，如果我的程序不能在10分钟之内处理完这18000多条数据，而使得游标长时间处于不活跃状态，那么报游标超时的错误就很正常。

上面只是推理过程，无法确认程序在执行过程中是否真的如此，下面开始证明：

查看pymongo官网文档,find_raw_batches()方法，返回原始二进制流；

我们调用该方法，对其返回的内容进行迭代，共迭代十七次,使用bson.decode_all()方法可以查看其每次都是返回一个列表，列表长度依次为101、17578、18482、18509、18526、18519、18527、18512、18526、18532、18533、18502、18499、18499、18495、18509、5695，总和共282544条，与数据库中文档总数相同。

观察上述每次返回的数据长度，首次101，接下来每一次与18476相差不大（因为文档与文档之间的大小有微小的差别），最后一次因为所剩的数据不够4M，所以返回剩下的全部。

以上就证明了mongo的取数据的工作机制,也搞清了为什么会出现游标超时错误。

当然如果我们不了解mongo底层的工作机制，也可以靠自己避免游标超时，我们在读取mongo全库时可以像mysql那样分段读取（limit），思想逻辑一样，代码实现如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
from bson import ObjectId

    def iter_whole_collection(schema, collection, query, order_by_mode="ASC", limit=1000):
        max_id = ""
        while 1:
            try:
                records = MongodbDAO.find(schema, collection, query, limit, sort_type=order_by_mode)
                iter_count = 0
                for record in records:
                    yield record
                    max_id = record["_id"]
                    iter_count += 1
                if not iter_count:
                    break
                query["_id"] = {"$gt": ObjectId(max_id)}
            except Exception as _e:
                logging.exception(_e)

MongoDB中条件操作符有：

  • (>) 大于 : $gt
  • (<) 小于 : $lt
  • (>=) 大于等于 :$gte
  • (<= ) 小于等于 -:$lte

还有个技巧，使用python的list可以直接将mongo的cursor对象转换为列表，不过会将游标对应的全部数据读取到内存中。

如何快速找出两个列表中的重复值

  1. 暴力枚举法：就是把两个list当中的每个元素都取出来进行两两比较，直到找到相同元素；

    设第一个数组的长度为n，第二个数组的长度为m，则时间复杂度为O(n*m)，空间复杂度为O（1）。

  2. 哈希表法：python的dict类型就是利用哈希结构，可以直接将其中一个列表的数据设置为字典的key值，然后判断另一个列表中的值是否在字典的key中；所以此种方式需要在内存中保存一组数据（一般选择元素较少的列表），时间复杂度为O(n),空间复杂度为O(m)。

    要注意两个列表内部之间各自有重复元素或两个列表之间有多个重复元素时的处理细节。

  3. 先将两个列表都排序，然后使用二分查找。