Mysql慢查询优化笔记

##获取慢查询日志 mysql-slow.log

一般慢查询日志文件存放在/data/mysql/mysql-slow.log,可以通过以下命令来查看:

mysql> show variables like '%slow%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| log_slow_queries    | ON                         |
| slow_launch_time    | 2                          |
| slow_query_log      | ON                         |
| slow_query_log_file | /data/mysql/mysql-slow.log |
+---------------------+----------------------------+

部分云数据库厂商不提供直接登录数据库服务器功能,但是会提供额外的日志获取途径,具体请咨询相关厂商。

##查看日志中慢查询语句

# Time: 160315 15:02:52
# User@Host: ecourse[ecourse] @  [192.168.100.2]
# Query_time: 5.440974  Lock_time: 0.000126 Rows_sent: 13  Rows_examined: 7086046
SET timestamp=1458025372;
select subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ where 1=1 and (subject0_.ID in (select distinct classsubje1_.SUB_ID from CLASSES_SUBJECTS classsubje1_ where classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462')) order by subject0_.SORT_NUM ASC;

以上信息中,此条sql语句执行了5.44秒,检查数据行数7086046行。可能数据库表中一共也没这么多行,但是在连接查询、子查询的情况下,会导致m*n这种复杂度的检查。

##EXPLAIN 分析

mysql> EXPLAIN select subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ where 1=1 and (subject0_.ID in (select distinct classsubje1_.SUB_ID from CLASSES_SUBJECTS classsubje1_ where classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462')) order by subject0_.SORT_NUM ASC;
+----+--------------------+--------------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type        | table        | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+--------------------+--------------+------+---------------+------+---------+------+------+------------------------------+
|  1 | PRIMARY            | subject0_    | ALL  | NULL          | NULL | NULL    | NULL | 1226 | Using where; Using filesort  |
|  2 | DEPENDENT SUBQUERY | classsubje1_ | ALL  | NULL          | NULL | NULL    | NULL | 6197 | Using where; Using temporary |
+----+--------------------+--------------+------+---------------+------+---------+------+------+------------------------------+
2 rows in set (0.00 sec)

这个前面加了EXPLAIN的语句可以放心执行,他只是分析语句,不具体查库。
以上结果,我们首先要关心 typerows 这两列。type里面的ALL应该不是我们想看到的,表示全表扫描了。rows里面的两个数相乘,基本上是慢查询记录的检查行数的值(慢查询记录时间和我执行这个的时间不一样,有差距,另外这个值也是一个估值,不准)。再结合咱们的sql看看,基本上能确定算法复杂度不是我们想要的m*n。

##SQL修改

mysql> EXPLAIN select distinct subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ , CLASSES_SUBJECTS classsubje1_ where subject0_.ID = classsubje1_.SUB_ID and classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462'  order by subject0_.SORT_NUM ASC;
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                         | rows | Extra                                        |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | classsubje1_ | ALL    | NULL          | NULL    | NULL    | NULL                        | 6197 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | subject0_    | eq_ref | PRIMARY       | PRIMARY | 96      | ecourse.classsubje1_.SUB_ID |    1 |                                              |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------+------+----------------------------------------------+

去掉子查询使用join,复杂度降低了一个数量级。

##添加索引

我们看到classsubje1_.CONF_ID='402881f1536e732a01536e7c066e6d6e'这么一个条件,考虑可以在这个字段上建立索引:

mysql> CREATE INDEX index_cs_config_id ON `CLASSES_SUBJECTS` (CONF_ID);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次分析:

mysql> EXPLAIN select distinct subject0_.ID as ID1_29_, subject0_.CONF_ID as CONF_ID2_29_, subject0_.GROUP_NAME as GROUP_NA3_29_, subject0_.IS_OPT as IS_OPT4_29_, subject0_.SUB_NAME as SUB_NAME5_29_, subject0_.SORT_NUM as SORT_NUM6_29_, subject0_.SUB_TYPE as SUB_TYPE7_29_ from SUBJECTS subject0_ , CLASSES_SUBJECTS classsubje1_ where subject0_.ID = classsubje1_.SUB_ID and classsubje1_.CONF_ID='ff808181536deb2e01537099b04b2462'  order by subject0_.SORT_NUM ASC;
+----+-------------+--------------+--------+--------------------+--------------------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys      | key                | key_len | ref                         | rows | Extra                                        |
+----+-------------+--------------+--------+--------------------+--------------------+---------+-----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | classsubje1_ | ref    | index_cs_config_id | index_cs_config_id | 97      | const                       |  158 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | subject0_    | eq_ref | PRIMARY            | PRIMARY            | 96      | ecourse.classsubje1_.SUB_ID |    1 |                                              |
+----+-------------+--------------+--------+--------------------+--------------------+---------+-----------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

相同大小的K均值变化算法

###算法基本如下:
####初始化:
1. 计算所需要的簇大小。
2. 初始化质点,最好使用k-means++算法。
3. 点到离他最近的簇的距离,减去到离他最远的簇的距离(=最好和最坏分配的最大利益)。
4. 分配点到他们的首选簇,直到这个簇是完整的,然后分配其余的点而不再考虑已经完整的簇。

这个初始化方法不是最佳的,对于本教程还有改进的空间,特别是最后一个簇。但这将作为一个初始化方法。

附加数据结构:对于每一个簇,我们都要保留一个愿意离开集群的对象列表。这样将更容易得找到转移候选人。当有一个点,想要进入一个簇,想要离开他的顶部元素被认为是切换。(或许也可以考虑更多的顶部元素)

####迭代 1. 计算当前簇的方法 2. 对于每一个点,计算他到簇的距离 3. 根据当前分配任务和最佳替代分配排序元素 4. 对每个元素的优先级:
对于每个其他集群,由元素增益,除非已经移动:
1)如果有一个元素想要离开其他的簇和这个交换之后的收益率有改善,则交换2个元素
2)如果该元素可以移动而不违反大小限制,移动他
3)如果元素没有更改,添加到输出转移列表
4)如果没有更多的转移,或者达到最大迭代阈值,终止

收敛性与K均值相同:任何一个转移都必须减少方差,也可能是K均值。

改进空间:寻找最佳转移的逻辑,如:通过求解三角形,对象1从A到B,对象2从B到C,对象3从C到A,仍然保留大小。

##自动生成代码 由于这是一个K均值变化,我们可以使用基类AbstractKMeans。生成标准方法,添加一个记录器,选择泛型属性。

asd…

MySQL快速备份与恢复

以下内容未实践,看群里这么讨论的,特此记录一下,备查。 首先确定目标库的参数值

mysql>show variables like 'max_allowed_packet';
mysql>show variables like 'net_buffer_length'; 根据参数值书写mysqldump命令,如:  

mysqldump -h127.0.0.1 -ulxyy_db -pkamuyopJKCIDjhkdsf -R -E -e -l -F  --max_allowed_packet=4194304 --net_buffer_length=16384 kamuy>lxyy_db_20130426.sql   还原:

mysql -uroot -pdsideal --default-character-set=utf8 --max_allowed_packet=4194304--net_buffer_length=16384 然后use lxyy_db进入需要还原的数据库,运行  

source c:\lxyy_db_20130426.sql 

这样导入将会非常快,之前数小时才能导入的sql现在几十秒就可以完成了。
谢谢阿航。

Scrapy学习笔记

##创建项目

使用命令scrapy startproject tutorial创建一个名为tutorial的项目结构:

tutorial/
    scrapy.cfg              #部署的配置文件
    tutorial/               #项目模块文件夹
        __init__.py         
        items.py            #项目items文件,定义爬取的数据
        pipelines.py        #项目pipelines文件
        settings.py         #项目设置文件
        spiders/            #用来放置爬虫的文件夹,定义爬取规则
            __init__.py

集成开发环境我选择PyCharm,用惯了jetbrains 的东西了。
直接用PyCharm新建一个项目,选择位置在上面命令创建的位置,库选择2.7的。

##写一个简单爬虫

items.py文件中定义item:

import scrapy

class DmozItem(scrapy.Item):
    title = scrapy.Field()
    link = scrapy.Field()
    desc = scrapy.Field()

在spiders文件夹中定义一个爬虫DmozSpider.py:(注意第一行的utf-8编码设置)

#coding=utf-8

import scrapy

class DmozSpider(scrapy.Spider):
    name = "dmoz"           #name属性很重要,不同spider不能使用相同的name
    allowed_domains = ["dmoz.org"]
    start_urls = [
        "http://www.dmoz.org/Computers/Programming/Languages/Python/Books/",
        "http://www.dmoz.org/Computers/Programming/Languages/Python/Resources/"
    ]
    def parse(self, response):
        filename = response.url.split("/")[-2] + '.html'
        with open(filename, 'wb') as f:
            f.write(response.body)

使用命令运行爬虫scrapy crawl dmoz
运行后模块下会多出两个html文件来。

##命令行工具

###全局命令

  • startproject #scrapy startproject <project_name>创建项目
  • settings
  • runspider #scrapy runspider <spider_file.py>运行在一个Python文件中定义的独立爬虫
  • shell
  • fetch #scrapy fetch <url>下载url内容输出到标准输出
  • view #scrapy view <url>在浏览器中打开url
  • version

###项目命令 只能在项目目录执行

  • crawl #scrapy crawl <spider>使用一个爬虫开始爬
  • check
  • list #scrapy list列出该项目下所有爬虫
  • edit #scrapy edit <spider>调用setting中设置的编辑器进行编辑
  • parse
  • genspider #scrapy genspider [-t template] <name> <domain>在当前项目中生成一个爬虫
  • bench

##爬虫 一个爬虫就是一套规则,它定义了如何进行抓取,如何提取结构化数据等:
> 1. 生成请求抓取一个网址,并使用回调函数处理抓取回来的返回信息。默认使用的方法start_requests()parse()。 > 2. 在回调函数中,解析返回的网页信息,返回提取的数据、Item对象、Request对象。 > 3. 最后,返回的数据被存储到数据库或者文件系统。

###scrapy.Spider scrapy.spiders.Spider这是一个最简单的爬虫,其他爬虫必须继承这个。它提供一个默认的start_request()方法实现,该方法从start_urls属性开始爬取数据,然后调用parse方法解析返回的结果。

  1. name 必需,定义爬虫的名字
  2. allowed_domains 可选,包含了这个爬虫可以爬取的域。OffsiteMiddleware开启的时候,不是在这些域下的URL将不会被继续抓取。
  3. start_urls 没有特殊的URL被指定时,爬虫将从这些URL开始爬取。
  4. custom_settings 执行这个爬虫时加载的设置,可以覆盖项目的设置。
  5. crawler
  6. settings
  7. logger
  8. from_crawler
  9. start_requests()
  10. make_requests_from_url(url)
  11. parse(response)
  12. log(message[,level,component])
  13. closed(reason)

一个简单的爬虫例子:

import scrapy

class MySpider(scrapy.Spider):
    name = 'example.com' allowed_domains = ['example.com'] 
    start_urls = [
        'http://www.example.com/1.html',
        'http://www.example.com/2.html',
        'http://www.example.com/3.html',
    ]

    def parse(self, response):
        for h3 in response.xpath('//h3').extract():
            yield {"title": h3}
            
        for url in response.xpath('//a/@href').extract(): 
            yield scrapy.Request(url, callback=self.parse)

###Spider参数

爬虫可以接收一些参数,可以通过crawl命令使用-a:

scrapy crawl myspider -a category=electronics

爬虫在构造器中接收这些参数:

import scrapy

class MySpider(scrapy.Spider):
    name = 'myspider'

    def __init__(self, category=None, *args, **kwargs): 
        super(MySpider, self).__init__(*args, **kwargs)
        self.start_urls = ['http://www.example.com/categories/%s' % category] 
        # ...

###通用Spiders

有一些通用的Spiders,可以通过继承他们,通过定义子类的方式来使用他们中的功能。
定义一个TestItem在items模块中:

import scrapy

class TestItem(scrapy.Item):
    id = scrapy.Field()
    name = scrapy.Field() 
    description = scrapy.Field()

//TODO 待续

####CrawlSpider ####XMLFeedSpider ####CSVFeedSpider ####SitemapSpider

##选择器

###xpath(query) ###css(query) ###re(query) ###extract() ###register_namespace(prefix,uri) ###remove_namespaces() ###__nonzero__()

##Items

balablaba

Mac OS X EI Capitan安装Scrapy的坑

前段可以参考这里
有一个坑,在删除老版本的six包的时候,可能文件被系统保护了,不允许操作。
这是就需要先把这个SIP关掉,删掉低版本,装好高版本之后再打开。

  1. 重启 Mac,按住 Command+R 键直到 Apple logo 出现,进入 Recovery Mode
  2. 点击 Utilities > Terminal
  3. 在 Terminal 中输入 csrutil disable,之后回车
  4. 重启 Mac
  1. 删除包 sudo rm -rf /System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/six*
  2. sudo pip install six
  1. 重复1-4步,不过这次是用 csrutil enable 恢复SIP保护机制
  2. 重启 Mac