mysql简单查询优化

最近爬Boss直聘数据,因为没有使用scrapy这种框架,自建轮子写的整个框架,构造的url和数据存储都在阿里云国际的vps上(1G内存,CPU也是弱鸡),到了40多万时mysql查询就成了瓶颈。

研究了下,主要从数据库和查询语句两方面下手优化:

  1. innodb_buffer_pool_size = 256M (这个最重要,但是我内存很小,设置过高后容易把机器卡死,没办法)
  2. max_allowed_packet = 512M(这里对我没有意义,因为内存很小)
  3. 开启慢查询,针对性的优化了查询语句, 加索引 参考 https://www.mysqltutorial.org/mysql-index/mysql-create-index/ ,还有就是避免 like %s, NULL,所有查询都要加字段where避免select *:

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/tmp_slow.log
long_query_time = 10

(boss) [[email protected] boss]$ sudo mysqldumpslow -t 10  /var/lib/mysql/tmp_slow.log

Reading mysql slow query log from /var/lib/mysql/tmp_slow.log
Count: 3  Time=20.94s (62s)  Lock=0.00s (0s)  Rows=590255.0 (1770765), root[root]@[47.56.250.177]
  SELECT `job_url` FROM jobs

Count: 68  Time=16.41s (1116s)  Lock=0.00s (0s)  Rows=589155.1 (40062550), root[root]@25hosts
  SELECT `job_url`, `city_code` FROM jobs

Count: 11  Time=16.38s (180s)  Lock=0.00s (0s)  Rows=110793.7 (1218731), root[root]@10hosts
  select `main_url` from start_url_cp LIMIT N, N

Died at /bin/mysqldumpslow line 167, <> chunk 82.

因为脚本框架的原因,需要查询已经存在的url防止重复爬取,所以数据量大时进行全表查询确实费时,加上网络延迟,各个爬虫host都在谷歌GCE上,这个效果目前能接受:

mysql> select count(job_url) from jobs;
+—————-+
| count(job_url) |
+—————-+
| 592501 |
+—————-+
1 row in set (2.35 sec)

之前这个查询非常慢了,效率指数级下降,几十秒甚至十几分钟。参考:

https://blog.csdn.net/u011277123/article/details/72627011

查询历史命令: cat  ~/.mysql_history

自从前几天爬了boss直聘7000+职位信息后,一直停滞不前,遇到了几个坑,今天算是重新启动了,感谢亚马逊!
https://geoseis.cn/CoV_tab.html
自适应各种平台显示,新版脚本也格式化成了标签形式。
根据我的爬虫脚本实时监控数据可视化,美国新增病历继续高歌猛进,闭着眼睛都能想到美股或者原油又崩盘了。目前为止161872累计确诊病例,但是不得不说,这个数据应该经得住敲打。
今天我的boss直聘脚本已经突破了1000行,主要还是因为脚本考虑的太周全,超时处理,没有找到相关职位的空页面处理,遇到机器人反爬处理,各种代理设置,反爬策略,多种日志并进(sql和csv),不知不觉就几百上千行了。如果用scrapy这种框架会少很多,不用自己造轮子。

疫情监控脚本可视化
一小部分boss直聘数据

废寝忘食地搞了两天,终于搞明白了几个服务。最近天天想方设法寻找合适的ip代理了,现在发现脚本写的越来越长不仅不方便维护,效率也下来了,有时间得整改下,套用下成熟的框架试试。
昨天发现新大陆,昨天晚上连夜研究,今天终于把脚本写好了,利用亚马逊服务器可以虽然更换ip的功能,调用它的api,用python写了个脚本每个两分钟更换一次ip,其实挺简单,就是release, allocate, attach,核心就三步,扔掉原来ip,分配新ip,新ip分配给服务器。当然,其中还有从远程服务器删除旧ip等过程。
美国服务器的ip更新后自动存到远在日本的服务器(上面架设了redis服务),笔记本配置好redis客户端后python脚本连接日本的服务器redis,获取ip。
看似简单,实际操作中还是挺繁琐,很多坑要踩。当然,最重要的是过程,国外的服务器还是不适合,gfw的过滤明显减慢速度。
这两天还配置了squid服务器,但是不知怎么不太好使,可能跟阿布云代理有关。数据没采集多少,💰 没少花,时间更是大把浪费了。

SQL处理数据-字符串和重复数据处理

mysql重复数据和字符串处理

因为爬取的wikipedia数据不规范,写入MySQL数据库时没能定义好Primary Key和字符类型,后来发现主要是有几个小问题:

  • 地区人口一栏中有不少非数字(县级市比如保定市),城市一栏因为这个原因也是有重复值(后来删掉了县级市,不需要)
  • 香港澳门城镇人口一栏缺失值,城市一栏有中括号

NULL值更新成0

mysql> UPDATE cities_population
-> SET
-> urban_population=0
-> WHERE urban_population IS null;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

删除县级市

mysql> DELETE FROM cities_population WHERE class='县级市';
Query OK, 772 rows affected (0.01 sec)

删除个别城市名称后的一个字符

mysql> UPDATE cities_population
-> SET city=CONCAT(LEFT(city, CHAR_LENGTH(city) -1), '')
-> WHERE city NOT LIKE '%市' AND city NOT LIKE '香港%' AND city NOT LIKE '澳门%';
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0

可以看到香港澳门城市名称里的中括号

mysql> SELECT * FROM cities_population WHERE city LIKE '%[%';
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
| index | province | city | class | total_population | admin_population | urban_population | report_time |
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
| 378 | 香港 | 香港 [2] | 特别行政区 | 7071576 | 7071576 | NULL | 2011-06-30 |
| 379 | 澳门 | 澳门 [3] | 特别行政区 | 552503 | 552503 | NULL | 2011-08-12 |
| 468 | 香港 | 香港 [5] | 特别行政区 | 6708389 | 6708389 | NULL | 2001-03-14 |
| 469 | 澳门 | 澳门 [6] | 特别行政区 | 435235 | 435235 | NULL | 2001-08-23 |
+-------+----------+-----------+------------+------------------+------------------+------------------+-------------+
4 rows in set (0.00 sec)

去掉中括号:

mysql> UPDATE cities_population
-> SET
-> city=province
-> WHERE city LIKE '%[%';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

检查城市名称重复次数:

mysql> SELECT COUNT(*) as repetitions,city
-> FROM cities_population
-> GROUP BY city
-> HAVING repetitions > 1;

mysql> SELECT * FROM cities_population
-> WHERE 城市 LIKE '%香港%';
+------+-----------+------------+-----------------+-------------------+-----------------+------------+
| 省市 | 城市 | 层级 | 地区 人口 [1] | 市辖区 人口 [1] | 城镇 人口 [1] | 普查年月日 |
+------+-----------+------------+-----------------+-------------------+-----------------+------------+
| 香港 | 香港 [2] | 特别行政区 | 7071576 | 7071576 | NULL | 2011-06-30 |
+------+-----------+------------+-----------------+-------------------+-----------------+------------+

可以看到县级市:

mysql> SELECT * FROM cities_population LIMIT 10;
+------+----------+--------+-----------------+-------------------+-----------------+------------+
| 省市 | 城市 | 层级 | 地区 人口 [1] | 市辖区 人口 [1] | 城镇 人口 [1] | 普查年月日 |
+------+----------+--------+-----------------+-------------------+-----------------+------------+
| 北京 | 北京市 | 直辖市 | 19612368 | 18827262 | 16446857 | 2010-11-01 |
| 天津 | 天津市 | 直辖市 | 12938693 | 11090783 | 9562255 | 2010-11-01 |
| 河北 | 石家庄市 | 地级市 | 10163788 | 2834942 | 2770344 | 2010-11-01 |
| 河北 | 唐山市 | 地级市 | 7577289 | 3187171 | 2128191 | 2010-11-01 |
| 河北 | 秦皇岛市 | 地级市 | 2987605 | 1029670 | 967877 | 2010-11-01 |
| 河北 | 邯郸市 | 地级市 | 9174683 | 1445338 | 1316674 | 2010-11-01 |
| 河北 | 邢台市 | 地级市 | 7104103 | 670154 | 668765 | 2010-11-01 |
| 河北 | 保定市 | 地级市 | 11194382 | 1138521 | 1038195 | 2010-11-01 |
| 河北 | 定州市 | 县级市 | 保定市 | 1165182 | 482121 | 2010-11-01 |
| 河北 | 张家口市 | 地级市 | 4345485 | 1060605 | 924628 | 2010-11-01 |
+------+----------+--------+-----------------+-------------------+-----------------+------------+

可以看到数据类型问题,即使命令行先创建也不行(可能是因为脚本里写入SQL我用了replace)

mysql> DESC cities_population;
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| province | text | YES | | NULL | |
| city | text | YES | | NULL | |
| class | text | YES | | NULL | |
| total_population | text | YES | | NULL | |
| admin_population | bigint(20) | YES | | NULL | |
| urban_population | text | YES | | NULL | |
| report_time | text | YES | | NULL | |
+------------------+------------+------+-----+---------+-------+

处理香港澳门城市名称问题:

mysql> UPDATE cities_population
-> SET city=CONCAT(LEFT(city, CHAR_LENGTH(city) -1), '')
-> WHERE city NOT LIKE '%市' AND city NOT LIKE '香港%' AND city NOT LIKE '澳门%';
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0

修改字符类型:

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  | MUL | NULL    |       |
| province         | text        | YES  |     | NULL    |       |
| city             | varchar(20) | NO   |     | NULL    |       |
| class            | text        | YES  |     | NULL    |       |
| total_population | text        | YES  |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | text        | YES  |     | NULL    |       |
| report_time      | text        | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> ALTER TABLE cities_population
    -> MODIFY province VARCHAR(20) NOT NULL,
    -> MODIFY class VARCHAR(20) NOT NULL,
    -> MODIFY total_population FLOAT NOT NULL,
    -> MODIFY urban_population FLOAT NOT NULL,
    -> MODIFY report_time DATE;
Query OK, 878 rows affected, 2 warnings (0.03 sec)
Records: 878  Duplicates: 0  Warnings: 2

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  | MUL | NULL    |       |
| province         | varchar(20) | NO   |     | NULL    |       |
| city             | varchar(20) | NO   |     | NULL    |       |
| class            | varchar(20) | NO   |     | NULL    |       |
| total_population | float       | NO   |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | float       | NO   |     | NULL    |       |
| report_time      | date        | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> CREATE TABLE tmp SELECT DISTINCT *  FROM  cities_population;
Query OK, 691 rows affected (0.02 sec)
Records: 691  Duplicates: 0  Warnings: 0

mysql> DROP TABLE cities_population;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE tmp RENAME TO  cities_population;
Query OK, 0 rows affected (0.01 sec)
这样就删除了重复数据:
mysql> ALTER TABLE cities_population
    -> ADD PRIMARY KEY(city,report_time);
Query OK, 691 rows affected (0.02 sec)
Records: 691  Duplicates: 0  Warnings: 0

mysql> DESC cities_population;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| index            | bigint(20)  | YES  |     | NULL    |       |
| province         | varchar(20) | NO   |     | NULL    |       |
| city             | varchar(20) | NO   | PRI | NULL    |       |
| class            | varchar(20) | NO   |     | NULL    |       |
| total_population | float       | NO   |     | NULL    |       |
| admin_population | bigint(20)  | YES  |     | NULL    |       |
| urban_population | float       | NO   |     | NULL    |       |
| report_time      | date        | NO   | PRI | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

肺炎疫情-用数据分析的角度来看我们的医疗

领英也分享过。

能用数据说话咱就不BB!
通过这次数据分析,能清晰地看到我们平时的有些认知和事实是有差距的。三甲医院总数辽宁95,广东89,湖北88,山东82,江苏74,黑龙江70,河南北京60,天津55。按照人均医院,天津稳居第一,北京上海辽宁排名也很靠前,青海这个黑马(西藏因为人少也很有优势)我没去核实数据。
从数据上看,天津北上辽是养老居家圣地,辽宁作为老工业基地的底子现在看来仍旧不可小觑。数据上看东北养老还是不错的,冬天就去海南嘛。如果不差钱,广州应该是个养老的好地方,医疗水平高,环境也不错,要知道老人都是怕冷的。所以说,好好赚钱,争取在广东养老才是正道。这次疫情的焦点湖北省医疗资源一点不差,昨晚(其实昨晚没睡)没来得及做完城市数据可视化,武汉的三甲医院(46家)也是第一排的,所以不是武汉撑不住,任何一个正常的医疗系统都不可能承担这么严重的疫情。最牛的天津也是23万人才有一家三甲医院好嘛!

全国三甲医院数据分析-pyecharts动态图表可视化

1200+三甲医院地理坐标(百度地图API),数据处理和分析,pyecharts数据可视化

https://www.linkedin.com/feed/update/urn:li:activity:6642160429646868480

https://lnkd.in/f78_A3T
Within one or days, I did ETL on 1200+ top hospitals in China. 30000hospitals of which 3000 were tier 3. Only 1200+ tier 3 Top Class.
Data crawling was done a few weeks ago. Recently I put the data into mysql. Then I did data cleaning and processing with MySQL and python. Finally data visualization was done with Pyecharts.
Feel free to review it on my website. It’s in html tabs and easy to view. Very interactive Charts!
To your surprise, Wuhan or Hubei has one of the best medical resources in China. Virus like this is beyond capability of any emergency systems in any countries.
嗯,天快亮了。花了一两天时间把全国1200+三甲医院的数据(全国3万家左右医院,提取出不到3000三级,三甲其实才1200+)提取出来(在我这个链接里你可以看到每家三甲医院的地理位置和电话),处理分析了下,一共12张动态图,做成了html,欢迎大家打开链接观赏下,不看你会后悔的,开心一笑!
大家一定要打开网页看下,手机调成横屏。一共12个标签页,因为这样更方便看图。有时间我再出个简单的分析报告和处理流程,放在自己的网站。你的点击能助力那些想学习数据分析而又找不到敲门的朋友找到我的网站[Shake]如果有朋友懂得运营,欢迎指点迷津,有志于做个高质量的技术博客。
这些漂亮的动态图之后是几十个小时的数据处理,这两天代码敲的都快折了!一共700-800行代码,包括爬虫,导入数据库(mysql里也做了些简单的分析处理),数据清洗,数据处理,数据分析和可视化。用到的工具主要是python,pyecharts,json,mysql。
睡个觉,该起来吃早饭了,忙的一天只吃了一顿饭。