Joonas

Joonas 查看完整档案

北京编辑  |  填写毕业院校  |  填写所在公司/组织填写个人主网站
编辑

八小时工作八小时睡眠,剩下的八小时决定你是否与众不同,而住哪里某种程度上决定剩下八小时

个人动态

Joonas 赞了文章 · 2020-09-15

地理位置geo处理之mysql函数

目前越来越多的业务都会基于LBS,附近的人,外卖位置,附近商家等等,现就讨论离我最近这一业务场景的解决方案。

目前已知解决方案有:

  • mysql 自定义函数计算
  • mysql geo索引
  • mongodb geo索引
  • postgresql PostGis索引
  • redis geo
  • ElasticSearch

本文测试下mysql 函数运算的性能

准备工作

创建数据表

CREATE TABLE `driver` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lng` float DEFAULT NULL,
  `lat` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建测试数据

在创建数据之前先了解下基本的地理知识:

  • 全球经纬度的取值范围为: 纬度-90~90,经度-180~180
  • 中国的经纬度范围大约为: 纬度3.86~53.55,经度73.66~135.05
  • 北京行政中心的纬度为39.92,经度为116.46
  • 越北面的地方纬度数值越大,越东面的地方经度数值越大
  • 度分转换: 将度分单位数据转换为度单位数据,公式:度=度+分/60
  • 分秒转换: 将度分秒单位数据转换为度单位数据,公式:度 = 度 + 分 / 60 + 秒 / 60 / 60

在纬度相等的情况下:

  • 经度每隔0.00001度,距离相差约1米

在经度相等的情况下:

  • 纬度每隔0.00001度,距离相差约1.1米

mysql函数计算

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `getDistance`(
    `lng1` float(10,7) 
    ,
    `lat1` float(10,7)
    ,
    `lng2` float(10,7) 
    ,
    `lat2` float(10,7)

) RETURNS double
    COMMENT '计算2坐标点距离'
BEGIN
    declare d double;
    declare radius int;
    set radius = 6371000; #假设地球为正球形,直径为6371000米
    set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2)   
        *SIN((lat1-lat2)*PI()/180/2)+   
        COS(lat2*PI()/180)*COS(lat1*PI()/180)   
        *SIN((lng1-lng2)*PI()/180/2)   
        *SIN((lng1-lng2)*PI()/180/2)),   
        SQRT(1-SIN((lat1-lat2)*PI()/180/2)   
        *SIN((lat1-lat2)*PI()/180/2)   
        +COS(lat2*PI()/180)*COS(lat1*PI()/180)   
        *SIN((lng1-lng2)*PI()/180/2)   
        *SIN((lng1-lng2)*PI()/180/2))))*radius;
    return d;
END//
DELIMITER ;

创建数据python脚本

# coding=utf-8
from orator import DatabaseManager, Model
import logging
import random
import threading

""" 中国的经纬度范围 纬度3.86~53.55,经度73.66~135.05。大概0.00001度差距1米 """

# 创建 日志 对象
logger = logging.getLogger()
handler = logging.StreamHandler()
formatter = logging.Formatter(
    '%(asctime)s %(name)-12s %(levelname)-8s %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)
logger.setLevel(logging.DEBUG)

# Connect to the database

config = {
    'mysql': {
        'driver': 'mysql',
        'host': 'localhost',
        'database': 'dbtest',
        'user': 'root',
        'password': '',
        'prefix': ''
    }
}

db = DatabaseManager(config)
Model.set_connection_resolver(db)


class Driver(Model):
    __table__ = 'driver'
    __timestamps__ = False
    pass


def ins_driver(thread_name,nums):
    logger.info('开启线程%s' % thread_name)
    for _ in range(nums):
        lng = '%.5f' % random.uniform(73.66, 135.05)
        lat = '%.5f' % random.uniform(3.86, 53.55)

        driver = Driver()
        driver.lng = lng
        driver.lat = lat
        driver.save()

thread_nums = 10
for i in range(thread_nums):
    t = threading.Thread(target=ins_driver, args=(i, 400000))
    t.start()

image.png

以上脚本创建10个线程,10个线程插入4万条数据。耗费150.18s执行完,总共插入40万条数据

测试

  • 测试环境

系统:mac os

内存:16G

cpu: intel core i5

硬盘: 500g 固态硬盘

测试下查找距离(134.38753,18.56734)这个坐标点最近的10个司机

select *,`getDistance`(134.38753,18.56734,`lng`,`lat`) as dis from driver ORDER BY dis limit 10
  • 耗时:18.0s
  • explain:全表扫描

我测试了从1万到10万间隔1万和从10万到90万每间隔10万测试的结果变化

image.png

结论

  • 此方案在数据量达到3万条查询耗时就会超过1秒
  • 大约每增加1万条就会增加0.4秒的耗时
查看原文

赞 1 收藏 0 评论 0

Joonas 回答了问题 · 2020-08-11

去掉iterm2中多余提示

你换个主题不就行了吗

关注 3 回答 2

Joonas 关注了用户 · 2020-06-03

就想叫yoko @notokoy

关注 305

Joonas 关注了专栏 · 2020-06-03

TIGERB的技术博客

关于web开发的基础回顾与学习

关注 569

Joonas 关注了标签 · 2020-05-12

golang

Go语言是谷歌2009发布的第二款开源编程语言。Go语言专门针对多处理器系统应用程序的编程进行了优化,使用Go编译的程序可以媲美C或C++代码的速度,而且更加安全、支持并行进程。
Go语言是谷歌推出的一种全新的编程语言,可以在不损失应用程序性能的情况下降低代码的复杂性。谷歌首席软件工程师罗布派克(Rob Pike)说:我们之所以开发Go,是因为过去10多年间软件开发的难度令人沮丧。Go是谷歌2009发布的第二款编程语言。

七牛云存储CEO许式伟出版《Go语言编程
go语言翻译项目 http://code.google.com/p/gola...
《go编程导读》 http://code.google.com/p/ac-m...
golang的官方文档 http://golang.org/doc/docs.html
golang windows上安装 http://code.google.com/p/gomi...

关注 26185

Joonas 关注了专栏 · 2020-04-26

周梦康

金三银四 还不上车补课?学了就是赚了 https://segmentfault.com/ls/1650000011318558

关注 4461

Joonas 收藏了文章 · 2020-04-13

Go 爬虫之 colly 从入门到不放弃指南

最近发现知乎上感兴趣的问题越来越少,于是准备聚合下其他平台技术问答,比如 segmentfault、stackoverflow 等。

要完成这个工作,肯定是离不开爬虫的。我就顺便抽时间研究了 Go 的一款爬虫框架 colly。

概要介绍

colly 是 Go 实现的比较有名的一款爬虫框架,而且 Go 在高并发和分布式场景的优势也正是爬虫技术所需要的。它的主要特点是轻量、快速,设计非常优雅,并且分布式的支持也非常简单,易于扩展。

如何学习

爬虫最有名的框架应该就是 Python 的 scrapy,很多人最早接触的爬虫框架就是它,我也不例外。它的文档非常齐全,扩展组件也很丰富。当我们要设计一款爬虫框架时,常会参考它的设计。之前看到一些文章介绍 Go 中也有类似 scrapy 的实现。

相比而言,colly 的学习资料就少的可怜了。刚看到它的时候,我总会情不自禁想借鉴我的 scrapy 使用经验,但结果发现这种生搬硬套并不可行。

到此,我们自然地想到去找些文章阅读,但结果是 colly 相关文章确实有点少,能找到的基本都是官方提供的,而且看起来似乎不是那么完善。没办法,慢慢啃吧!官方的学习资料通常都会有三处,分别是文档、案例和源码。

今天,暂时先从官方文档角度吧!正文开始。

官方文档

官方文档介绍着重使用方法,如果是有爬虫经验的朋友,扫完一遍文档很快。我花了点时间将官网文档的按自己的思路整理了一版。

主体内容不多,涉及安装快速开始如何配置调试分布式爬虫存储运用多收集器配置优化扩展

其中的每篇文档都很短小,甚至是少的基本都不用翻页滚动。

如何安装

colly 的安装和其他的 Go 库安装一样简单。如下:

go get -u github.com/gocolly/colly

一行命令搞定。So easy!

快速开始

我们来通过一个 hello word 案例快速体验下 colly 的使用。步骤如下:

第一步,导入 colly。

import "github.com/gocolly/colly"

第二步,创建 collector。

c := colly.NewCollector()

第三步,事件监听,通过 callback 执行事件处理。

// Find and visit all links
c.OnHTML("a[href]", func(e *colly.HTMLElement) {
    link := e.Attr("href")
    // Print link
    fmt.Printf("Link found: %q -> %s\n", e.Text, link)
    // Visit link found on page
    // Only those links are visited which are in AllowedDomains
    c.Visit(e.Request.AbsoluteURL(link))
})

c.OnRequest(func(r *colly.Request) {
    fmt.Println("Visiting", r.URL)
})

我们顺便列举一下 colly 支持的事件类型,如下:

  • OnRequest 请求执行之前调用
  • OnResponse 响应返回之后调用
  • OnHTML 监听执行 selector
  • OnXML 监听执行 selector
  • OnHTMLDetach,取消监听,参数为 selector 字符串
  • OnXMLDetach,取消监听,参数为 selector 字符串
  • OnScraped,完成抓取后执行,完成所有工作后执行
  • OnError,错误回调

最后一步,c.Visit() 正式启动网页访问。

c.Visit("http://go-colly.org/")

案例的完成代码在 colly 源码的 _example 目录下 basic 中提供。

如何配置

colly 是一款配置灵活的框架,提供了大量的可供开发人员配置的选项。默认情况下,每个选项都提供了较优的默认值。

如下是采用默认创建的 collector。

c := colly.NewCollector()

配置创建的 collector,比如设置 useragent 和允许重复访问。代码如下:

c2 := colly.NewCollector(
    colly.UserAgent("xy"),
    colly.AllowURLRevisit(),
)

我们也可以创建后再改变配置。

c2 := colly.NewCollector()
c2.UserAgent = "xy"
c2.AllowURLRevisit = true

collector 的配置可以在爬虫执行到任何阶段改变。一个经典的例子,通过随机改变 user-agent,可以帮助我们实现简单的反爬。

const letterBytes = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

func RandomString() string {
    b := make([]byte, rand.Intn(10)+10)
    for i := range b {
        b[i] = letterBytes[rand.Intn(len(letterBytes))]
    }
    return string(b)
}

c := colly.NewCollector()

c.OnRequest(func(r *colly.Request) {
    r.Headers.Set("User-Agent", RandomString())
})

前面说过,collector 默认已经为我们选择了较优的配置,其实它们也可以通过环境变量改变。这样,我们就可以不用为了改变配置,每次都得重新编译了。环境变量配置是在 collector 初始化时生效,正式启动后,配置是可以被覆盖的。

支持的配置项,如下:

ALLOWED_DOMAINS (字符串切片),允许的域名,比如 []string{"segmentfault.com", "zhihu.com"}
CACHE_DIR (string) 缓存目录
DETECT_CHARSET (y/n) 是否检测响应编码
DISABLE_COOKIES (y/n) 禁止 cookies
DISALLOWED_DOMAINS (字符串切片),禁止的域名,同 ALLOWED_DOMAINS 类型
IGNORE_ROBOTSTXT (y/n) 是否忽略 ROBOTS 协议
MAX_BODY_SIZE (int) 响应最大
MAX_DEPTH (int - 0 means infinite) 访问深度
PARSE_HTTP_ERROR_RESPONSE (y/n) 解析 HTTP 响应错误
USER_AGENT (string)

它们都是些非常容易理解的选项。

我们再来看看 HTTP 的配置,都是些常用的配置,比如代理、各种超时时间等。

c := colly.NewCollector()
c.WithTransport(&http.Transport{
    Proxy: http.ProxyFromEnvironment,
    DialContext: (&net.Dialer{
        Timeout:   30 * time.Second,          // 超时时间
        KeepAlive: 30 * time.Second,          // keepAlive 超时时间
        DualStack: true,
    }).DialContext,
    MaxIdleConns:          100,               // 最大空闲连接数
    IdleConnTimeout:       90 * time.Second,  // 空闲连接超时
    TLSHandshakeTimeout:   10 * time.Second,  // TLS 握手超时
    ExpectContinueTimeout: 1 * time.Second,  
}

调试

在用 scrapy 的时候,它提供了非常好用的 shell 帮助我们非常方便地实现 debug。但非常可惜 colly 中并没有类似功能,这里的 debugger 主要是指运行时的信息收集。

debugger 是一个接口,我们只要实现它其中的两个方法,就可完成运行时信息的收集。

type Debugger interface {
    // Init initializes the backend
    Init() error
    // Event receives a new collector event.
    Event(e *Event)
}

源码中有个典型的案例,LogDebugger。我们只需提供相应的 io.Writer 类型变量,具体如何使用呢?

一个案例,如下:

package main

import (
    "log"
    "os"

    "github.com/gocolly/colly"
    "github.com/gocolly/colly/debug"
)

func main() {
    writer, err := os.OpenFile("collector.log", os.O_RDWR|os.O_CREATE, 0666)
    if err != nil {
        panic(err)
    }

    c := colly.NewCollector(colly.Debugger(&debug.LogDebugger{Output: writer}), colly.MaxDepth(2))
    c.OnHTML("a[href]", func(e *colly.HTMLElement) {
        if err := e.Request.Visit(e.Attr("href")); err != nil {
            log.Printf("visit err: %v", err)
        }
    })

    if err := c.Visit("http://go-colly.org/"); err != nil {
        panic(err)
    }
}

运行完成,打开 collector.log 即可查看输出内容。

分布式

分布式爬虫,可以从几个层面考虑,分别是代理层面、执行层面和存储层面。

代理层面

通过设置代理池,我们可以将下载任务分配给不同节点执行,有助于提供爬虫的网页下载速度。同时,这样还能有效降低因爬取速度太快而导致IP 被禁的可能性。

colly 实现代理 IP 的代码如下:

package main

import (
    "github.com/gocolly/colly"
    "github.com/gocolly/colly/proxy"
)

func main() {
    c := colly.NewCollector()

    if p, err := proxy.RoundRobinProxySwitcher(
        "socks5://127.0.0.1:1337",
        "socks5://127.0.0.1:1338",
        "http://127.0.0.1:8080",
    ); err == nil {
        c.SetProxyFunc(p)
    }
    // ...
}

proxy.RoundRobinProxySwitcher 是 colly 内置的通过轮询方式实现代理切换的函数。当然,我们也可以完全自定义。

比如,一个代理随机切换的案例,如下:

var proxies []*url.URL = []*url.URL{
    &url.URL{Host: "127.0.0.1:8080"},
    &url.URL{Host: "127.0.0.1:8081"},
}

func randomProxySwitcher(_ *http.Request) (*url.URL, error) {
    return proxies[random.Intn(len(proxies))], nil
}

// ...
c.SetProxyFunc(randomProxySwitcher)

不过需要注意,此时的爬虫仍然是中心化的,任务只在一个节点上执行。

执行层面

这种方式通过将任务分配给不同的节点执行,实现真正意义的分布式。

如果实现分布式执行,首先需要面对一个问题,如何将任务分配给不同的节点,实现不同任务节点之间的协同工作呢?

首先,我们选择合适的通信方案。常见的通信协议有 HTTP、TCP,一种无状态的文本协议、一个是面向连接的协议。除此之外,还可选择的有种类丰富的 RPC 协议,比如 Jsonrpc、facebook 的 thrift、google 的 grpc 等。

文档提供了一个 HTTP 服务示例代码,负责接收请求与任务执行。如下:

package main

import (
    "encoding/json"
    "log"
    "net/http"

    "github.com/gocolly/colly"
)

type pageInfo struct {
    StatusCode int
    Links      map[string]int
}

func handler(w http.ResponseWriter, r *http.Request) {
    URL := r.URL.Query().Get("url")
    if URL == "" {
        log.Println("missing URL argument")
        return
    }
    log.Println("visiting", URL)

    c := colly.NewCollector()

    p := &pageInfo{Links: make(map[string]int)}

    // count links
    c.OnHTML("a[href]", func(e *colly.HTMLElement) {
        link := e.Request.AbsoluteURL(e.Attr("href"))
        if link != "" {
            p.Links[link]++
        }
    })

    // extract status code
    c.OnResponse(func(r *colly.Response) {
        log.Println("response received", r.StatusCode)
        p.StatusCode = r.StatusCode
    })
    c.OnError(func(r *colly.Response, err error) {
        log.Println("error:", r.StatusCode, err)
        p.StatusCode = r.StatusCode
    })

    c.Visit(URL)

    // dump results
    b, err := json.Marshal(p)
    if err != nil {
        log.Println("failed to serialize response:", err)
        return
    }
    w.Header().Add("Content-Type", "application/json")
    w.Write(b)
}

func main() {
    // example usage: curl -s 'http://127.0.0.1:7171/?url=http://go-colly.org/'
    addr := ":7171"

    http.HandleFunc("/", handler)

    log.Println("listening on", addr)
    log.Fatal(http.ListenAndServe(addr, nil))
}

这里并没有提供调度器的代码,不过实现不算复杂。任务完成后,服务会将相应的链接返回给调度器,调度器负责将新的任务发送给工作节点继续执行。

如果需要根据节点负载情况决定任务执行节点,还需要服务提供监控 API 获取节点性能数据帮助调度器决策。

存储层面

我们已经通过将任务分配到不同节点执行实现了分布式。但部分数据,比如 cookies、访问的 url 记录等,在节点之间需要共享。默认情况下,这些数据是保存内存中的,只能是每个 collector 独享一份数据。

我们可以通过将数据保存至 redis、mongo 等存储中,实现节点间的数据共享。colly 支持在任何存储间切换,只要相应存储实现 colly/storage.Storage 接口中的方法。

其实,colly 已经内置了部分 storage 的实现,查看 storage。下一节也会谈到这个话题。

存储

前面刚提过这个话题,我们具体看看 colly 已经支持的 storage 有哪些吧。

InMemoryStorage,即内存,colly 的默认存储,我们可以通过 collector.SetStorage() 替换。

RedisStorage,或许是因为 redis 在分布式场景下使用更多,官方提供了使用案例

其他还有 Sqlite3StorageMongoStorage

多收集器

我们前面演示的爬虫都是比较简单的,处理逻辑都很类似。如果是一个复杂的爬虫,我们可以通过创建不同的 collector 负责不同任务的处理。

如何理解这段话呢?举个例子吧。

如果大家写过一段时间爬虫,肯定遇到过父子页面抓取的问题,通常父页面的处理逻辑与子页面是不同的,并且通常父子页面间还有数据共享的需求。用过 scrapy 应该知道,scrapy 通过在 request 绑定回调函数实现不同页面的逻辑处理,而数据共享是通过在 request 上绑定数据实现将父页面数据传递给子页面。

研究之后,我们发现 scrapy 的这种方式 colly 并不支持。那该怎么做?这就是我们要解决的问题。

对于不同页面的处理逻辑,我们可以定义创建多个收集器,即 collector,不同 collector 负责处理不同的页面逻辑。

c := colly.NewCollector(
    colly.UserAgent("myUserAgent"),
    colly.AllowedDomains("foo.com", "bar.com"),
)
// Custom User-Agent and allowed domains are cloned to c2
c2 := c.Clone()

通常情况下,父子页面的 collector 是相同的。上面的示例中,子页面的 collector c2 通过 clone,将父级 collector 的配置也都复制了下来。

而父子页面之间的数据传递,可以通过 Context 实现在不同 collector 之间传递。注意这个 Context 只是 colly 实现的数据共享的结构,并非 Go 标准库中的 Context。

c.OnResponse(func(r *colly.Response) {
    r.Ctx.Put("Custom-header", r.Headers.Get("Custom-Header"))
    c2.Request("GET", "https://foo.com/", nil, r.Ctx, nil)
})

如此一来,我们在子页面中就可以通过 r.Ctx 获取到父级传入的数据了。关于这个场景,我们可以查看官方提供的案例 coursera_courses

配置优化

colly 的默认配置针对是少量站点的优化配置。如果你是针对大量站点的抓取,还需要一些改进。

持久化存储

默认情况下,colly 中的 cookies 和 url 是保存在内存中,我们要换成可持久化的存储。前面介绍过,colly 已经实现一些常用的可持久化的存储组件。

启用异步加快任务执行

colly 默认会阻塞等待请求执行完成,这将会导致等待执行任务数越来越大。我们可以通过设置 collector 的 Async 选项为 true 实现异步处理,从而避免这个问题。如果采用这种方式,记住增加 c.Wait(),否则程序会立刻退出。

禁止或限制 KeepAlive 连接

colly 默认开启 KeepAlive 增加爬虫的抓取速度。但是,这对打开的文件描述符有要求,对于长时间运行的任务,进程非常容易就能达到最大描述符的限制。

禁止 HTTP 的 KeepAlive 的示例代码,如下。

c := colly.NewCollector()
c.WithTransport(&http.Transport{
    DisableKeepAlives: true,
})

扩展

colly 提供了一些扩展,主要与爬虫相关的常用功能,如 referer、random_user_agent、url_length_filter 等。源码路径在 colly/extensions/ 下。

通过一个示例了解它们的使用方法,如下:

import (
    "log"

    "github.com/gocolly/colly"
    "github.com/gocolly/colly/extensions"
)

func main() {
    c := colly.NewCollector()
    visited := false

    extensions.RandomUserAgent(c)
    extensions.Referrer(c)

    c.OnResponse(func(r *colly.Response) {
        log.Println(string(r.Body))
        if !visited {
            visited = true
            r.Request.Visit("/get?q=2")
        }
    })

    c.Visit("http://httpbin.org/get")
}

只需将 collector 传入扩展函数中即可。这么简单就搞定了啊。

那么,我们能不能自己实现一个扩展呢?

在使用 scrapy 的时候,我们如果要实现一个扩展需要提前了解不少概念,仔细阅读它的文档。但 colly 在文档中压根也并没有相关说明啊。肿么办呢?看样子只能看源码了。

我们打开 referer 插件的源码,如下:

package extensions

import (
    "github.com/gocolly/colly"
)

// Referer sets valid Referer HTTP header to requests.
// Warning: this extension works only if you use Request.Visit
// from callbacks instead of Collector.Visit.
func Referer(c *colly.Collector) {
    c.OnResponse(func(r *colly.Response) {
        r.Ctx.Put("_referer", r.Request.URL.String())
    })
    c.OnRequest(func(r *colly.Request) {
        if ref := r.Ctx.Get("_referer"); ref != "" {
            r.Headers.Set("Referer", ref)
        }
    })
}

在 collector 上增加一些事件回调就实现一个扩展。这么简单的源码,完全不用文档说明就可以实现一个自己的扩展了。 当然,如果仔细观察,我们会发现,其实它的思路和 scrapy 是类似的,都是通过扩展 request 和 response 的回调实现,而 colly 之所以如此简洁主要得益于它优雅的设计和 Go 简单的语法。

总结

读完 colly 的官方文档会发现,虽然它的文档简陋无比,但应该介绍的内容基本上都涉及到了。如果有部分未涉及的内容,我也在本文之中做了相关的补充。之前在使用 Go 的 elastic 包时,同样也是文档少的可怜,但简单读下源码,就能立刻明白了该如何去使用它。

或许这就是 Go 的大道至简吧。

最后,如果大家在使用 colly 时遇到什么问题,官方的 example 绝对是最佳实践,建议可以抽时间一读。

查看原文

Joonas 赞了文章 · 2020-04-13

Go 爬虫之 colly 从入门到不放弃指南

最近发现知乎上感兴趣的问题越来越少,于是准备聚合下其他平台技术问答,比如 segmentfault、stackoverflow 等。

要完成这个工作,肯定是离不开爬虫的。我就顺便抽时间研究了 Go 的一款爬虫框架 colly。

概要介绍

colly 是 Go 实现的比较有名的一款爬虫框架,而且 Go 在高并发和分布式场景的优势也正是爬虫技术所需要的。它的主要特点是轻量、快速,设计非常优雅,并且分布式的支持也非常简单,易于扩展。

如何学习

爬虫最有名的框架应该就是 Python 的 scrapy,很多人最早接触的爬虫框架就是它,我也不例外。它的文档非常齐全,扩展组件也很丰富。当我们要设计一款爬虫框架时,常会参考它的设计。之前看到一些文章介绍 Go 中也有类似 scrapy 的实现。

相比而言,colly 的学习资料就少的可怜了。刚看到它的时候,我总会情不自禁想借鉴我的 scrapy 使用经验,但结果发现这种生搬硬套并不可行。

到此,我们自然地想到去找些文章阅读,但结果是 colly 相关文章确实有点少,能找到的基本都是官方提供的,而且看起来似乎不是那么完善。没办法,慢慢啃吧!官方的学习资料通常都会有三处,分别是文档、案例和源码。

今天,暂时先从官方文档角度吧!正文开始。

官方文档

官方文档介绍着重使用方法,如果是有爬虫经验的朋友,扫完一遍文档很快。我花了点时间将官网文档的按自己的思路整理了一版。

主体内容不多,涉及安装快速开始如何配置调试分布式爬虫存储运用多收集器配置优化扩展

其中的每篇文档都很短小,甚至是少的基本都不用翻页滚动。

如何安装

colly 的安装和其他的 Go 库安装一样简单。如下:

go get -u github.com/gocolly/colly

一行命令搞定。So easy!

快速开始

我们来通过一个 hello word 案例快速体验下 colly 的使用。步骤如下:

第一步,导入 colly。

import "github.com/gocolly/colly"

第二步,创建 collector。

c := colly.NewCollector()

第三步,事件监听,通过 callback 执行事件处理。

// Find and visit all links
c.OnHTML("a[href]", func(e *colly.HTMLElement) {
    link := e.Attr("href")
    // Print link
    fmt.Printf("Link found: %q -> %s\n", e.Text, link)
    // Visit link found on page
    // Only those links are visited which are in AllowedDomains
    c.Visit(e.Request.AbsoluteURL(link))
})

c.OnRequest(func(r *colly.Request) {
    fmt.Println("Visiting", r.URL)
})

我们顺便列举一下 colly 支持的事件类型,如下:

  • OnRequest 请求执行之前调用
  • OnResponse 响应返回之后调用
  • OnHTML 监听执行 selector
  • OnXML 监听执行 selector
  • OnHTMLDetach,取消监听,参数为 selector 字符串
  • OnXMLDetach,取消监听,参数为 selector 字符串
  • OnScraped,完成抓取后执行,完成所有工作后执行
  • OnError,错误回调

最后一步,c.Visit() 正式启动网页访问。

c.Visit("http://go-colly.org/")

案例的完成代码在 colly 源码的 _example 目录下 basic 中提供。

如何配置

colly 是一款配置灵活的框架,提供了大量的可供开发人员配置的选项。默认情况下,每个选项都提供了较优的默认值。

如下是采用默认创建的 collector。

c := colly.NewCollector()

配置创建的 collector,比如设置 useragent 和允许重复访问。代码如下:

c2 := colly.NewCollector(
    colly.UserAgent("xy"),
    colly.AllowURLRevisit(),
)

我们也可以创建后再改变配置。

c2 := colly.NewCollector()
c2.UserAgent = "xy"
c2.AllowURLRevisit = true

collector 的配置可以在爬虫执行到任何阶段改变。一个经典的例子,通过随机改变 user-agent,可以帮助我们实现简单的反爬。

const letterBytes = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

func RandomString() string {
    b := make([]byte, rand.Intn(10)+10)
    for i := range b {
        b[i] = letterBytes[rand.Intn(len(letterBytes))]
    }
    return string(b)
}

c := colly.NewCollector()

c.OnRequest(func(r *colly.Request) {
    r.Headers.Set("User-Agent", RandomString())
})

前面说过,collector 默认已经为我们选择了较优的配置,其实它们也可以通过环境变量改变。这样,我们就可以不用为了改变配置,每次都得重新编译了。环境变量配置是在 collector 初始化时生效,正式启动后,配置是可以被覆盖的。

支持的配置项,如下:

ALLOWED_DOMAINS (字符串切片),允许的域名,比如 []string{"segmentfault.com", "zhihu.com"}
CACHE_DIR (string) 缓存目录
DETECT_CHARSET (y/n) 是否检测响应编码
DISABLE_COOKIES (y/n) 禁止 cookies
DISALLOWED_DOMAINS (字符串切片),禁止的域名,同 ALLOWED_DOMAINS 类型
IGNORE_ROBOTSTXT (y/n) 是否忽略 ROBOTS 协议
MAX_BODY_SIZE (int) 响应最大
MAX_DEPTH (int - 0 means infinite) 访问深度
PARSE_HTTP_ERROR_RESPONSE (y/n) 解析 HTTP 响应错误
USER_AGENT (string)

它们都是些非常容易理解的选项。

我们再来看看 HTTP 的配置,都是些常用的配置,比如代理、各种超时时间等。

c := colly.NewCollector()
c.WithTransport(&http.Transport{
    Proxy: http.ProxyFromEnvironment,
    DialContext: (&net.Dialer{
        Timeout:   30 * time.Second,          // 超时时间
        KeepAlive: 30 * time.Second,          // keepAlive 超时时间
        DualStack: true,
    }).DialContext,
    MaxIdleConns:          100,               // 最大空闲连接数
    IdleConnTimeout:       90 * time.Second,  // 空闲连接超时
    TLSHandshakeTimeout:   10 * time.Second,  // TLS 握手超时
    ExpectContinueTimeout: 1 * time.Second,  
}

调试

在用 scrapy 的时候,它提供了非常好用的 shell 帮助我们非常方便地实现 debug。但非常可惜 colly 中并没有类似功能,这里的 debugger 主要是指运行时的信息收集。

debugger 是一个接口,我们只要实现它其中的两个方法,就可完成运行时信息的收集。

type Debugger interface {
    // Init initializes the backend
    Init() error
    // Event receives a new collector event.
    Event(e *Event)
}

源码中有个典型的案例,LogDebugger。我们只需提供相应的 io.Writer 类型变量,具体如何使用呢?

一个案例,如下:

package main

import (
    "log"
    "os"

    "github.com/gocolly/colly"
    "github.com/gocolly/colly/debug"
)

func main() {
    writer, err := os.OpenFile("collector.log", os.O_RDWR|os.O_CREATE, 0666)
    if err != nil {
        panic(err)
    }

    c := colly.NewCollector(colly.Debugger(&debug.LogDebugger{Output: writer}), colly.MaxDepth(2))
    c.OnHTML("a[href]", func(e *colly.HTMLElement) {
        if err := e.Request.Visit(e.Attr("href")); err != nil {
            log.Printf("visit err: %v", err)
        }
    })

    if err := c.Visit("http://go-colly.org/"); err != nil {
        panic(err)
    }
}

运行完成,打开 collector.log 即可查看输出内容。

分布式

分布式爬虫,可以从几个层面考虑,分别是代理层面、执行层面和存储层面。

代理层面

通过设置代理池,我们可以将下载任务分配给不同节点执行,有助于提供爬虫的网页下载速度。同时,这样还能有效降低因爬取速度太快而导致IP 被禁的可能性。

colly 实现代理 IP 的代码如下:

package main

import (
    "github.com/gocolly/colly"
    "github.com/gocolly/colly/proxy"
)

func main() {
    c := colly.NewCollector()

    if p, err := proxy.RoundRobinProxySwitcher(
        "socks5://127.0.0.1:1337",
        "socks5://127.0.0.1:1338",
        "http://127.0.0.1:8080",
    ); err == nil {
        c.SetProxyFunc(p)
    }
    // ...
}

proxy.RoundRobinProxySwitcher 是 colly 内置的通过轮询方式实现代理切换的函数。当然,我们也可以完全自定义。

比如,一个代理随机切换的案例,如下:

var proxies []*url.URL = []*url.URL{
    &url.URL{Host: "127.0.0.1:8080"},
    &url.URL{Host: "127.0.0.1:8081"},
}

func randomProxySwitcher(_ *http.Request) (*url.URL, error) {
    return proxies[random.Intn(len(proxies))], nil
}

// ...
c.SetProxyFunc(randomProxySwitcher)

不过需要注意,此时的爬虫仍然是中心化的,任务只在一个节点上执行。

执行层面

这种方式通过将任务分配给不同的节点执行,实现真正意义的分布式。

如果实现分布式执行,首先需要面对一个问题,如何将任务分配给不同的节点,实现不同任务节点之间的协同工作呢?

首先,我们选择合适的通信方案。常见的通信协议有 HTTP、TCP,一种无状态的文本协议、一个是面向连接的协议。除此之外,还可选择的有种类丰富的 RPC 协议,比如 Jsonrpc、facebook 的 thrift、google 的 grpc 等。

文档提供了一个 HTTP 服务示例代码,负责接收请求与任务执行。如下:

package main

import (
    "encoding/json"
    "log"
    "net/http"

    "github.com/gocolly/colly"
)

type pageInfo struct {
    StatusCode int
    Links      map[string]int
}

func handler(w http.ResponseWriter, r *http.Request) {
    URL := r.URL.Query().Get("url")
    if URL == "" {
        log.Println("missing URL argument")
        return
    }
    log.Println("visiting", URL)

    c := colly.NewCollector()

    p := &pageInfo{Links: make(map[string]int)}

    // count links
    c.OnHTML("a[href]", func(e *colly.HTMLElement) {
        link := e.Request.AbsoluteURL(e.Attr("href"))
        if link != "" {
            p.Links[link]++
        }
    })

    // extract status code
    c.OnResponse(func(r *colly.Response) {
        log.Println("response received", r.StatusCode)
        p.StatusCode = r.StatusCode
    })
    c.OnError(func(r *colly.Response, err error) {
        log.Println("error:", r.StatusCode, err)
        p.StatusCode = r.StatusCode
    })

    c.Visit(URL)

    // dump results
    b, err := json.Marshal(p)
    if err != nil {
        log.Println("failed to serialize response:", err)
        return
    }
    w.Header().Add("Content-Type", "application/json")
    w.Write(b)
}

func main() {
    // example usage: curl -s 'http://127.0.0.1:7171/?url=http://go-colly.org/'
    addr := ":7171"

    http.HandleFunc("/", handler)

    log.Println("listening on", addr)
    log.Fatal(http.ListenAndServe(addr, nil))
}

这里并没有提供调度器的代码,不过实现不算复杂。任务完成后,服务会将相应的链接返回给调度器,调度器负责将新的任务发送给工作节点继续执行。

如果需要根据节点负载情况决定任务执行节点,还需要服务提供监控 API 获取节点性能数据帮助调度器决策。

存储层面

我们已经通过将任务分配到不同节点执行实现了分布式。但部分数据,比如 cookies、访问的 url 记录等,在节点之间需要共享。默认情况下,这些数据是保存内存中的,只能是每个 collector 独享一份数据。

我们可以通过将数据保存至 redis、mongo 等存储中,实现节点间的数据共享。colly 支持在任何存储间切换,只要相应存储实现 colly/storage.Storage 接口中的方法。

其实,colly 已经内置了部分 storage 的实现,查看 storage。下一节也会谈到这个话题。

存储

前面刚提过这个话题,我们具体看看 colly 已经支持的 storage 有哪些吧。

InMemoryStorage,即内存,colly 的默认存储,我们可以通过 collector.SetStorage() 替换。

RedisStorage,或许是因为 redis 在分布式场景下使用更多,官方提供了使用案例

其他还有 Sqlite3StorageMongoStorage

多收集器

我们前面演示的爬虫都是比较简单的,处理逻辑都很类似。如果是一个复杂的爬虫,我们可以通过创建不同的 collector 负责不同任务的处理。

如何理解这段话呢?举个例子吧。

如果大家写过一段时间爬虫,肯定遇到过父子页面抓取的问题,通常父页面的处理逻辑与子页面是不同的,并且通常父子页面间还有数据共享的需求。用过 scrapy 应该知道,scrapy 通过在 request 绑定回调函数实现不同页面的逻辑处理,而数据共享是通过在 request 上绑定数据实现将父页面数据传递给子页面。

研究之后,我们发现 scrapy 的这种方式 colly 并不支持。那该怎么做?这就是我们要解决的问题。

对于不同页面的处理逻辑,我们可以定义创建多个收集器,即 collector,不同 collector 负责处理不同的页面逻辑。

c := colly.NewCollector(
    colly.UserAgent("myUserAgent"),
    colly.AllowedDomains("foo.com", "bar.com"),
)
// Custom User-Agent and allowed domains are cloned to c2
c2 := c.Clone()

通常情况下,父子页面的 collector 是相同的。上面的示例中,子页面的 collector c2 通过 clone,将父级 collector 的配置也都复制了下来。

而父子页面之间的数据传递,可以通过 Context 实现在不同 collector 之间传递。注意这个 Context 只是 colly 实现的数据共享的结构,并非 Go 标准库中的 Context。

c.OnResponse(func(r *colly.Response) {
    r.Ctx.Put("Custom-header", r.Headers.Get("Custom-Header"))
    c2.Request("GET", "https://foo.com/", nil, r.Ctx, nil)
})

如此一来,我们在子页面中就可以通过 r.Ctx 获取到父级传入的数据了。关于这个场景,我们可以查看官方提供的案例 coursera_courses

配置优化

colly 的默认配置针对是少量站点的优化配置。如果你是针对大量站点的抓取,还需要一些改进。

持久化存储

默认情况下,colly 中的 cookies 和 url 是保存在内存中,我们要换成可持久化的存储。前面介绍过,colly 已经实现一些常用的可持久化的存储组件。

启用异步加快任务执行

colly 默认会阻塞等待请求执行完成,这将会导致等待执行任务数越来越大。我们可以通过设置 collector 的 Async 选项为 true 实现异步处理,从而避免这个问题。如果采用这种方式,记住增加 c.Wait(),否则程序会立刻退出。

禁止或限制 KeepAlive 连接

colly 默认开启 KeepAlive 增加爬虫的抓取速度。但是,这对打开的文件描述符有要求,对于长时间运行的任务,进程非常容易就能达到最大描述符的限制。

禁止 HTTP 的 KeepAlive 的示例代码,如下。

c := colly.NewCollector()
c.WithTransport(&http.Transport{
    DisableKeepAlives: true,
})

扩展

colly 提供了一些扩展,主要与爬虫相关的常用功能,如 referer、random_user_agent、url_length_filter 等。源码路径在 colly/extensions/ 下。

通过一个示例了解它们的使用方法,如下:

import (
    "log"

    "github.com/gocolly/colly"
    "github.com/gocolly/colly/extensions"
)

func main() {
    c := colly.NewCollector()
    visited := false

    extensions.RandomUserAgent(c)
    extensions.Referrer(c)

    c.OnResponse(func(r *colly.Response) {
        log.Println(string(r.Body))
        if !visited {
            visited = true
            r.Request.Visit("/get?q=2")
        }
    })

    c.Visit("http://httpbin.org/get")
}

只需将 collector 传入扩展函数中即可。这么简单就搞定了啊。

那么,我们能不能自己实现一个扩展呢?

在使用 scrapy 的时候,我们如果要实现一个扩展需要提前了解不少概念,仔细阅读它的文档。但 colly 在文档中压根也并没有相关说明啊。肿么办呢?看样子只能看源码了。

我们打开 referer 插件的源码,如下:

package extensions

import (
    "github.com/gocolly/colly"
)

// Referer sets valid Referer HTTP header to requests.
// Warning: this extension works only if you use Request.Visit
// from callbacks instead of Collector.Visit.
func Referer(c *colly.Collector) {
    c.OnResponse(func(r *colly.Response) {
        r.Ctx.Put("_referer", r.Request.URL.String())
    })
    c.OnRequest(func(r *colly.Request) {
        if ref := r.Ctx.Get("_referer"); ref != "" {
            r.Headers.Set("Referer", ref)
        }
    })
}

在 collector 上增加一些事件回调就实现一个扩展。这么简单的源码,完全不用文档说明就可以实现一个自己的扩展了。 当然,如果仔细观察,我们会发现,其实它的思路和 scrapy 是类似的,都是通过扩展 request 和 response 的回调实现,而 colly 之所以如此简洁主要得益于它优雅的设计和 Go 简单的语法。

总结

读完 colly 的官方文档会发现,虽然它的文档简陋无比,但应该介绍的内容基本上都涉及到了。如果有部分未涉及的内容,我也在本文之中做了相关的补充。之前在使用 Go 的 elastic 包时,同样也是文档少的可怜,但简单读下源码,就能立刻明白了该如何去使用它。

或许这就是 Go 的大道至简吧。

最后,如果大家在使用 colly 时遇到什么问题,官方的 example 绝对是最佳实践,建议可以抽时间一读。

查看原文

赞 8 收藏 6 评论 0

Joonas 收藏了文章 · 2020-03-31

PHPWord中文手册整理

修正

中文支持的问题,使用前如果发现乱码,需要进行一些修正:

  • 解决编码问题,PHPword 会对输入的文字进行utf8_encode编码转化,如果你使用GBK、GB2312或者utf8编码的话就会出现乱码,如果你用utf8编码,就查找类库中所有方法中的 utf8_encode 转码将其删除,如果你采用GBK或者GB2312编码,使用iconv进行编码转换。
  • 解决中文字体支持,在writer/word2007/base.php中 312行添加 $objWriter->writeAttribute('w:eastAsia',$font)
  • 启动php zip支持,windows环境下在php配置文件php.ini中,将extension=php_zip.dll前面的分号“;”去除;(如果没有,请添加extension=php_zip.dll此行并确保php_zip.dll文件存在相应的目录),然后同样在php.ini文件中,将 zlib.output_compression = Off 改为zlib.output_compression = On ; 

计量单位:缇(twips)

PHPWord最基本的计量单位:“缇”(twips),我们常常在文件中看到或使用计量单位“缇”,它是开源办公软件中最基本的计量单位,“缇”是"TWentieth of an Inch Point"的简写,意思 1/20磅,与其他常用剂量单位的换算是1缇=1/1,440英寸
1缇=1/567厘米
1缇=1/15像素

字体设置

文档默认字体是Arial,字号10号,我们可以通过以下方法设置默认字体和字号:
注,该库存在中文字体支持问题,解决方法:见文档开头

$PHPWord->setDefaultFontName('Tahoma');
$PHPWord->setDefaultFontSize(12);

文档属性设置

我们可以设置下列文档属性

名称类型描述
CreatorString创建者
CompanyString公司
TitleString标题
DescriptionString描述
CategoryString分类
Last modified byString最后修改者
CreatedDatetime创建时间
ModifiedDatetime修改时间
SubjectString主题
KeywordsString关键词

我们可以通过以下方法设置文档属性

$properties = $PHPWord->getProperties();
$properties->setCreator('My name'); 
$properties->setCompany('My factory');
$properties->setTitle('My title');
$properties->setDescription('My description'); 
$properties->setCategory('My category');
$properties->setLastModifiedBy('My name');
$properties->setCreated( mktime(0, 0, 0, 3, 12, 2010) );
$properties->setModified( mktime(0, 0, 0, 3, 14, 2010) );
$properties->setSubject('My subject'); 
$properties->setKeywords('my, key, word');

新建文档

添加页面

添加默认页面(默认页面方向和页边距):

$section = $PHPWord->createSection();

页面样式

调整页面样式和布局有两种方法:
1.创建样式数组:

$sectionStyle = array('orientation' => null,
       'marginLeft' => 900,
       'marginRight' => 900,
       'marginTop' => 900,
       'marginBottom' => 900);
$section = $PHPWord->createSection($sectionStyle);

2.直接调用样式属性设置方法进行设置:

$section = $PHPWord->createSection();
$sectionStyle = $section->getSettings();
$sectionStyle->setLandscape();
$sectionStyle->setPortrait();
$sectionStyle->setMarginLeft(900);
$sectionStyle->setMarginRight(900);
$sectionStyle->setMarginTop(900);
$sectionStyle->setMarginBottom(900);

页面样式属性

注意:所有的属性对大小写敏感 !

属性描述
orientation页面方向:默认竖向:null 横向:landscape
marginTop上边距,单位:twips.
marginLeft左边距,单位:twips.
marginRight右边距,单位:twips.
marginBottom下边距,单位:twips..
borderTopSize上边框尺寸,单位:twips.
borderTopColor上边框颜色
borderLeftSize左边框尺寸,单位 :twips.
borderLeftColor左边框颜色
borderRightSize右边框尺寸,单位:twips.
borderRightColor右边框颜色
borderBottomSize底边框尺寸,单位:twips.
borderBottomColor底边框颜色

页面高度和宽度是自动设置的,你可以通过以下两个属性来修改,但不推荐进行修改。

属性描述
pageSizeW页面宽度,单位: twips.
pageSizeH页面高度,单位:twips.

文本

添加文本

向文档添加文本使用方法函数: addText.(注意PHPword 会对输入的文字进行utf8_encode编码转化,如果你使用GBK、GB2312或者utf8编码的话就会出现乱码,如果你用utf8编码,就查找类库中所有方法中的 utf8_encode 转码将其删除,如果你采用GBK或者GB2312编码,使用iconv进行编码转换。)

$section->addText( $text, [$fontStyle], [$paragraphStyle] );
addText()
参数类型描述
$textString文本内容.
$fontStyleString / Array字体样式.
$paragraphStyleString / Array段落样式

添加文本资源

文本资源可以包含文本链接,可以统一赋予段落样式,添加文本资源使用函数方法createTextrun.

createTextRun()
参数类型描述
$paragraphStyleString / Array文本样式.

添加文本资源后,就可以添加具有独特样式的文本或链接了。

$textrun = $section->createTextRun();
$textrun->addText('I am bold', array('bold'=>true)); 
$textrun->addText('I am italic, array('italic'=>true));
$textrun->addText('I am colored, array('color'=>'AACC00'));

当然也可以继承使用段落或文字样式

文本样式

设置文本样式有两种方法:
1.内嵌样式:

$fontStyle = array('color'=>'006699', 'size'=>18, 'bold'=>true);
$section->addText('helloWorld', $fontStyle);

$text = $section->addText('helloWorld');
$style = $text->getStyle();
$style->setColor('006699');
$style->setSize(18);
$style->setBold();

2.或者定义一个样式定义设置文本样式,定义一种样式后,必须把第二个参数设置为样式名称,使用方法函数addFontStyle:

$PHPWord->addFontStyle( $styleName, $fontStyle);
addFontStyle()
参数类型描述
$styleNameString样式名称
$fontStyleArray样式风格.

示例:

$fontStyle = array('color'=>'006699', 'size'=>18, 'bold'=>true);
$PHPWord->addFontStyle('myOwnStyle', $fontStyle);
$text = $section->addText('helloWorld', 'myOwnStyle');

添加段落样式,使用方法函数addParagraphStyle:

addParagraphStyle()
参数类型描述
$styleNameString段落样式名称.
$paragraphStyleArray段落样式.

样式属性列表

属性大小写敏感 !

Font Style

名称描述
size字号.
name字体
bold粗体
italic斜体
superScript上标
subScript下标
underline下划线,使用常量: PHPWord_Style_Font::UNDERLINE_...
Color字体颜色
fgColor前景色. 只能使用预定义常量:PHPWord_Style_Font::FGCOLOR_...

Paragraph Style

名称描述
align水平对齐:leftrightcenterboth / justify
spaceBefore段前间距,单位: twips.
spaceAfter段后间距,单位:twips
spacing行间距,单位: twips.

换行符

添加换行符,使用方法函数 addTextBreak:

$section->addTextBreak();

添加多个换行符:

$section->addTextBreak(15);

添加分页符

添加分页符,使用方法函数:addPageBreak:

$section->addPageBreak();

列表

添加列表

添加列表使用方法函数: addListItem:

$section->addListItem( $text, [$depth], [$styleText], [$styleList], [$styleParagraph] );
addListItem()
参数类型描述
$textString文本内容.
$depthInteger编号
$styleTextString / Array文本样式.
$styleListArray列表样式.
$styleParagraphString / Array段落样式

列表样式

示例:

$listStyle = array('listType' => PHPWord_Style_ListItem::TYPE_NUMBER);
$section->addListItem('Listitem 1', 0, null, $listStyle);

列表样式属性列表

属性大小写敏感!

名称描述
listType列表符号样式.使用常量 PHPWord_Style_ListItem::TYPE_...

超链接

添加超链接

添加超链接,使用方法函数: addLink:

$section->addLink( $linkSrc, [$linkName], [$styleFont], [$styleParagraph]);
addListItem()
参数类型描述
$linkSrcString链接地址
$linkNameString链接名称.
$styleFontString / Array文本样式
$styleParagraphString / Array段落样式

注意在添加链接地址时最好trim一下前后有空格很可有可能导致文档打不开

超链接样式

定义超链接风格的两种方法

  1. 内嵌样式:
$linkStyle = array('color'=>'0000FF',
    'underline'=>PHPWord_Style_Font::UNDERLINE_SINGLE);
$section->addLink('http://www.google.de', null, $linkStyle);
  1. 或者定义一个样式定义设置超链接样式,定义一种样式后,必须把第三个参数设置为样式名称
$linkStyle = array('color'=>'0000FF',
    'underline'=>PHPWord_Style_Font::UNDERLINE_SINGLE);
$PHPWord->addLinkStyle('myHyperlinkStyle', $linkStyle);
$section->addLink('http://www.google.de', null, 'myHyperlinkStyle');
addLinkStyle()
参数类型描述
$styleNameString超链接样式名称.
$stylesArray链接样式.可以使用各种字体样式属性

图片

添加图片

添加图片的函数方法: addImage:

$section->addImage( $src, [$style] );
addImage()
参数类型描述
$srcString图像的服务器路径,支持相对和绝对路径
$styleArray图片样式.

注意在添加图片路径时最好trim一下前后有空格很可有可能导致文档打不开

图片样式

添加图片样式只能使用数组方式 :

$imageStyle = array('width'=>350, 'height'=>350, 'align'=>'center');
$section->addImage('EARTH.jpg', $imageStyle);

图片样式属性

大小写敏感!

名称描述
width图像宽度,单位像素
height图像高度,单位像素
align图像对齐方式leftrightcenter

如果没有指定图片高或宽的属性,系统将使用PHP原生函数”getimagesize”来获取相关属性。
PHPWord 支持的图片格式: gif, jpeg, png, bmp, tiff.

添加GD生成图片

你也可以添加由GD库生成的图片,使用函数方法:addMemoryImage:

$section->addMemoryImage( $link, [$style] );
addMemoryImage()
参数类型描述
$linkString生成图片的php文件的路径. 注意: 应设置文件的绝对路径(就像你在浏览器中调用php文件),否则会发生错误。
$styleArray图像样式.

示例:

$section->addMemoryImage('http://localhost/image.php');

你GD图片样式的设置和本地图片一样.

PHPWord 支持的 GD 图片类型: png, jpeg, gif.

添加水印

添加水印的页面需要一个头部引用,添加水印方法函数:addWatermark

addWatermark()
参数类型描述
$srcString水印图片的文件地址
$styleArray水印图片样式

水印图片是在页面是绝对定位的,所以水印图片至少需要两个样式属性

名称描述
marginLeft左边距,单位像素
marginTop上边距,单位像素

注:图片样式并没有提供图像并排,文字环绕等功能,可以通过与表格想结合进行解决。

添加对象

我们可以使用方法函数 addObject,添加对象和链接

$section->addObject( $src, [$style] );
addObject()
参数类型描述
$srcString文件的服务器,支持相对和绝对路径.
$styleArray对象样式.

对象属性是有一个样式:
属性区分大小写!

名称描述
align对齐方式leftrightcenter

PHPWord 支持的对象类型: XLS, DOC, PPT.

添加标题

我们可以使用标题来为结构化文档或为文档建立目录,添加标题使用方法函数addTitleStyle 和 addTitle:

$PHPWord->addTitleStyle( $titleCount, [$fontStyle] );
addTitleStyle()
参数类型描述
$srcInteger标题级别,最多支持9级标题
$fontStyleArray标题字体样式

需要添给标题添加一个样式,否则文档不会将其作为一个真正的标题来处理。

定义标题样式后,定义标题就很简单了,可以使用函数方法:addTitle;

$section->addTitle( $text, [$depth] );
addTitle()
参数类型描述
$textString标题文本内容
$depthInteger标题级别编号,通过该参数调用addTtileStyle()设置的标题样式

添加目录

添加目录使用方法函数: addTOC:

$styleTOC = array('tabLeader'=>PHPWord_Style_TOC::TABLEADER_DOT);
$styleFont = array('spaceAfter'=>60, 'name'=>'Tahoma', 'size'=>12);
$section->addTOC($styleFont, $styleTOC);
addTOC()
参数类型描述
$styleFontArray目录字体样式
$styleTOCArray目录样式

目录样式属性列表:

样式属性区分大小写 !

名称描述
tabLeader标题的类型和对应页码.默认使用系统常量 PHPWord_Style_TOC::TABLEADER_...
tabPos标题与页码的位置,单位: twips.
Indent标题缩进,单位: twips.

表格

添加表格

添加表格使用函数方法:addTable:

$table = $section->addTable( [$tableStyle] );

参数 $tableStyle 是可选的. 表格样式这章有关于表格样式的详细说明。为addTable建立一个本地对象,我们需要使用这个对象来调用相关函数方法。

  • 添加行
$table->addRow( [$height] );

行的高度可以通过$height参数来设置,单位:twips.

  • 添加单元格

单元格添加前必须先添加行,添加单元格的函数方法为: addCell

$cell = $table->addCell(h, [$cellStyle] );
addCell()
参数类型描述
$widthInteger单元格宽度: twips.
$cellStyleArray单元格样式

为addcell创建一个本地对象,需要使用该对象来 调用以下函数

名称描述
addText添加文本
addTextBreak添加换行符
addLink添加链接
addImage添加图片
addMemoryImage添加水印
addListItem添加列表
addObject添加对象
addPreserveText添加页码,只对页眉和页脚有效

示例1:

$table = $section->addTable();
$table->addRow();
$cell = $table->addCell(2000);
$cell->addText('Cell 1');
$cell = $table->addCell(2000);
$cell->addText('Cell 2');
$cell = $table->addCell(2000);
$cell->addText('Cell 3');

示例2:

$table = $section->addTable();
$table->addRow(400);
$table->addCell(2000)->addText('Cell 1');
$table->addCell(2000)->addText('Cell 2');
$table->addCell(2000)->addText('Cell 3');

$table->addRow(1000);
$table->addCell(2000)->addText('Cell 4');
$table->addCell(2000)->addText('Cell 5');
$table->addCell(2000)->addText('Cell 6');

单元格样式

使用addCell的第二个参数来给单元格设置样式
示例:

$cellStyle = array('textDirection'=>PHPWord_Style_Cell::TEXT_DIR_BTLR,     'bgColor'=>'C0C0C0');

$table = $section->addTable();
$table->addRow(1000);
$table->addCell(2000, $cellStyle)->addText('Cell 1');
$table->addCell(2000, $cellStyle)->addText('Cell 2');
$table->addCell(2000, $cellStyle)->addText('Cell 3');
$table->addRow();
$table->addCell(2000)->addText('Cell 4');
$table->addCell(2000)->addText('Cell 5');
$table->addCell(2000)->addText('Cell 6');

单元格样式属性列表:

属性大小写敏感 !

名称描述
valign单元格内容对齐方式: left, right, center
textDirection文本方向. 使用预定常量 PHPWord_Style_Cell:: TEXT_DIR_...
bgColor单元格背景色
borderTopSize单元格上边框尺寸,单位 twips.
borderTopColor单元格上边框 颜色
borderLeftSize单元格左边框尺寸,单位twips
borderLeftColor单元格左边框颜色
borderRightSize单元格右边框尺寸,单位twips
borderRightColor单元格右边框颜色
borderBottomSize单元格下边框尺寸 ,单位twips
borderBottomColor单元格下边框颜色

表格样式

我们可以设置整个表格的样式,通过创建表格函数addTable的参数$tableStyle,表格具有如下样式属性
属性名称大小写敏感!

名称描述
cellMarginTop单元格上边距,单位: twips.
cellMarginLeft单元格左边距,单位: twips.
cellMarginRight单元格右边距,单位: twips.
cellMarginBottom单元格下边距,单位: twips.

示例:

$tableStyle = array('cellMarginTop'=>80,
     'cellMarginLeft'=>80,
     'cellMarginRight'=>80,
     'cellMarginBottom'=>80);
$table = $section->addTable($tableStyle);

我们可以使用函数方法: addTableStyle,为表格定义一个完整的样式。

$PHPWord->addTableStyle($styleName,  $styleTable, [$styleFirstRow] );
addTableStyle()
参数类型描述
$styleNameString表样式名称
$styleTableArray这个表的样式
$styleFirstRowArray表头样式(第一行)

示例:

$styleTable = array('borderColor'=>'006699',
     'borderSize'=>6,
     'cellMargin'=>50);
$styleFirstRow = array('bgColor'=>'66BBFF');
$PHPWord->addTableStyle('myTable', $styleTable, $styleFirstRow);

$table = $section->addTable('myTable');
$table->addRow(400);
$table->addCell(2000)->addText('Cell 1');
$table->addCell(2000)->addText('Cell 2');
$table->addCell(2000)->addText('Cell 3');
$table->addRow(1000);
$table->addCell(2000)->addText('Cell 4');
$table->addCell(2000)->addText('Cell 5');
$table->addCell(2000)->addText('Cell 6');

表格样式属性,注意属性名称大小写敏感!

名称描述
cellMarginTop单元格上边距,单位:twips.
cellMarginLeft单元格左边距,单位:twips.
cellMarginRight单元格右边距,单位:twips.
cellMarginBottom单元格下边距,单位:twips.
cellMargin单元格间距,单位:twips.
bgColor表格背景色
borderTopSize表格上边框尺寸,单位:twips.
borderTopColor表格上边框颜色
borderLeftSize表格左边框尺寸,单位:twips.
borderLeftColor表格左边框颜色
borderRightSize表格右边框尺寸,单位:twips.
borderRightColor表格右边框颜色
borderBottomSize表格下边框尺寸,单位:twips..
borderBottomColor表格下边框颜色
borderInsideHSize表格内水平网格尺寸,单位: twips.
borderInsideHColor表格内水平网格颜色
borderInsideVSize表格内垂直网格尺寸,单位: twips.
borderInsideVColor表格内垂直网格颜色
borderSize表格边框尺寸,单位:twips.
borderColor表格边框颜色

注意:表格在word布局中的功能可以进行体现,例如进行图片,对象等的布局可以考虑与表格结合进行处理

页脚

添加文档页脚使用函数方法: createFooter:

$footer = $section->createFooter();

确保在本地对象中保存页脚,并使用下列函数

名称描述
addText添加文本
addTextBreak添加换行符
addImage添加图像
addMemoryImage添加GD生成图像
addListItem添加列表
addPreserveText添加页码,只能在页眉或页脚使用
addTable添加表格
createTextrun添加文本资源

向页脚(页眉)添加页码使用函数方法:addPreserveText:

addPreserveText( $text, [$style] );
addPreserveText()
参数类型描述
$textString页脚(页眉)的文本内容
$styleArray文字样式.

示例:

$footer->addPreserveText('Page {PAGE} of {NUMPAGES}.');

页眉

添加页眉,使用函数方法: createHeader:

$header = $section->createHeader();

确保页眉是建立在一个文档中,页眉和页脚使用相同的属性和函数,详见页脚章节 。
注意:只用添加了页眉的页面,才能添加和使用图片水印

模版

我们可以利用搜索替换功能创建一个docx格式的模版文档,来替换文档中你想替换的文本. 但是要注意,只有文本或链接可以被替换。加载模版文档使用函数方法:loadTemplate function.

loadTemplate()
参数方法描述
$strFilenameString模版文件路径和名称

加载完成模版文档后,你可以使用函数方法: setValue 来搜索替换相关内容

setValue()
参数TypeDescription
$searchMixed搜索的值
$replaceMixed替换的值
$template = $PHPWord->loadTemplate('Template.docx');
$template->setValue('Name', 'Somebody someone');
$template->setValue('Street', 'Coming-Undone-Street 32');

被搜索替换的标签格式为: ${YOUR_SEARCH_PATTERN}
不能添加新的PHPWORD元素到加载的模版文档中
模版使用的几个注意事项:

  • 从模板生成word文档,支持在word模板文档里写替换标签,标签格式为${xxx},不过一定要注意,不要直接在word里编辑这些标签,一定要在文本文档里先写好标签,直接拷贝粘贴上去,千万不要编辑,否则无法替换,原因也很简单,把word文档另存为xml,然后看xml里标签的位置,会发现标签中间被插入了很多没用的xml节点,还有中文字体的节点。。。
  • 模版文档要一次性完成在保存,否则会出现替换失败问题。
  • 中文乱码问题,这个一定是存在的,如果php环境已经是utf8了,要找到关键地方,转utf8的代码,去掉,否则就是转两次编码了,典型的是phpwordtemplate.php文件,把这行注释掉:$replace = utf8_encode($replace);
  • linux下报“Could not close zip file.”错误,这个你永远想不到,要将模板文件所在目录权限改为可写,因为要在那个目录下生成临时文件
  • 引用模板文件和另存文件路径什么的,最好用绝对路径

声明:本文内容来自网络文件,部分未测试,后面有机会整个实例出来,目前整理成一份适合在网页阅读的文档,供参考。
官方实例:https://github.com/PHPOffice/...
官网:https://phpword.readthedocs.i...

查看原文

Joonas 赞了文章 · 2020-02-14

MySQL 性能优化神器 Explain 使用分析

简介

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.
EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了, 例如:

EXPLAIN SELECT * from user_info WHERE  id < 300;

准备

为了接下来方便演示 EXPLAIN 的使用, 首先我们需要建立两个测试用的表, 并添加相应的数据:

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);
CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

EXPLAIN 输出格式

EXPLAIN 命令的输出内容大致如下:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列的含义如下:

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

  • select_type: SELECT 查询的类型.

  • table: 查询的是哪个表

  • partitions: 匹配的分区

  • type: join 类型

  • possible_keys: 此次查询中可能选用的索引

  • key: 此次查询中确切使用到的索引.

  • ref: 哪个字段或常数与 key 一起被使用

  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

  • filtered: 表示此查询条件所过滤的数据的百分比

  • extra: 额外的信息

接下来我们来重点看一下比较重要的几个字段.

select_type

select_type 表示了查询的类型, 它的常用取值有:

  • SIMPLE, 表示此查询不包含 UNION 查询或子查询

  • PRIMARY, 表示此查询是最外层的查询

  • UNION, 表示此查询是 UNION 的第二或随后的查询

  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询

  • UNION RESULT, UNION 的结果

  • SUBQUERY, 子查询中的第一个 SELECT

  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

最常见的查询类别应该是 SIMPLE 了, 比如当我们的查询没有子查询, 也没有 UNION 查询时, 那么通常就是 SIMPLE 类型, 例如:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

如果我们使用了 UNION 查询, 那么 EXPLAIN 输出 的结果类似如下:

mysql> EXPLAIN (SELECT * FROM user_info  WHERE id IN (1, 2, 3))
    -> UNION
    -> (SELECT * FROM user_info WHERE id IN (3, 4, 5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | user_info  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
|  2 | UNION        | user_info  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

table

表示查询涉及的表或衍生表

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.

type 常用类型

type 常用的取值有:

  • system: 表中只有一条数据. 这个类型是特殊的 const 类型.

  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
    例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如:

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 314
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.order_info.user_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)
  • ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
    例如下面这个例子中, 就使用到了 ref 类型的查询:

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.01 sec)
  • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
    typerange 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

例如下面的例子就是一个范围查询:

mysql> EXPLAIN SELECT *
    ->         FROM user_info
    ->         WHERE id BETWEEN 2 AND 8 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
  • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
    index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.

例如:

mysql> EXPLAIN SELECT name FROM  user_info \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: name_index
      key_len: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

上面的例子中, 我们查询的 name 字段恰好是一个索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据. 因此这样的情况下, type 的值是 index, 并且 Extra 的值是 Using index.

  • ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
    下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

mysql> EXPLAIN SELECT age FROM  user_info WHERE age = 20 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

possible_keys

possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

  • 字符串

    • char(n): n 字节长度

    • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

  • 数值类型:

    • TINYINT: 1字节

    • SMALLINT: 2字节

    • MEDIUMINT: 3字节

    • INT: 4字节

    • BIGINT: 8字节

  • 时间类型

    • DATE: 3字节

    • TIMESTAMP: 4字节

    • DATETIME: 8字节

  • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

我们来举两个简单的栗子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: range
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: NULL
         rows: 5
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引:

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据 最左前缀匹配 原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id, 因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0', 则 key_length 应该是8.

上面因为 最左前缀匹配 原则, 我们的查询仅仅使用到了联合索引的 user_id 字段, 因此效率不算高.

接下来我们来看一下下一个例子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 161
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

  • Using filesort
    当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

例如下面的例子:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

我们的索引是

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort.
如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了. 例如:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
  • Using index
    "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

  • Using temporary
    查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

查看原文

赞 283 收藏 297 评论 20

认证与成就

  • 获得 21 次点赞
  • 获得 14 枚徽章 获得 0 枚金徽章, 获得 1 枚银徽章, 获得 13 枚铜徽章

擅长技能
编辑

开源项目 & 著作
编辑

(゚∀゚ )
暂时没有

注册于 2016-07-23
个人主页被 1.1k 人浏览