nginx反向代理服务器时 获取用户IP的几种方法

最近刚刚写好个人电商网站,然后忙于找工作,可是自己的网站我想看到访问人数与访问的ip地址。这样可以了解是否有人看到。

最开始的时候查了一下资料,也没有去想其他的问题,直接就开始写代码了,可是发现写好之后,我在本机进行测试,获取到的ip地址都是127.0.0.1,这是有点疑惑,为什么会出现这种问题。我的代码是这样的。

public String addOrUpdate(HttpServletRequest request){
        String ip = request.getRemoteAddr();
        return ip;
    }

网上查了之后说是我这里使用了代理软件,所以只能获取到本地localhost地址,只能是127.0.0.1

然后 网上给出了两种解决办法

  • 方法一
public String getRemortIP(HttpServletRequest request) {  
    if (request.getHeader("x-forwarded-for") == null) {  
        return request.getRemoteAddr();  
    }  
    return request.getHeader("x-forwarded-for");  
}  
  • 方法二
public String getIpAddr(HttpServletRequest request) {  
    String ip = request.getHeader("x-forwarded-for");  
    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {  
        ip = request.getHeader("Proxy-Client-IP");  
    }  
    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {  
        ip = request.getHeader("WL-Proxy-Client-IP");  
    }  
    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {  
        ip = request.getRemoteAddr();  
    }  
    return ip;  
}  

可是经过我的测试两种方法都不管用,不知道是出于什么原因,这时候我在想可能原因在于我使用的nginx设置上面,这个还真在网上找到了答案

  • nginx配置这里需要添加这样一条
location /{
           root   html;
           index  index.html index.htm;
           proxy_pass                  http://backend; 
           proxy_redirect              off;
           proxy_set_header            Host $host;
           proxy_set_header            X-real-ip $remote_addr;
           proxy_set_header            X-Forwarded-For $proxy_add_x_forwarded_for;
        }
  • 然后修改Java代码
String ip = request.getHeader("X-Real-IP");
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("X-Forwarded-For");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("Proxy-Client-IP");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("WL-Proxy-Client-IP");
        }
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getRemoteAddr();
        }
  • 这样就可以获取到真实的ip地址了

网上查到给出的解释是这样的

我在查阅资料时,有一本名叫《实战nginx》的书,作者张晏,这本书上有这么一段话“经过反向代理后,由于在客户端和web服务器之间增加了中间层,因此web服务器无法直接拿到客户端的ip,通过$remote_addr变量拿到的将是反向代理服务器的ip地址”。这句话的意思是说,当你使用了nginx反向服务器后,在web端使用request.getRemoteAddr()(本质上就是获取$remote_addr),取得的是nginx的地址,即$remote_addr变量中封装的是nginx的地址,当然是没法获得用户的真实ip的,但是,nginx是可以获得用户的真实ip的,也就是说nginx使用$remote_addr变量时获得的是用户的真实ip,如果我们想要在web端获得用户的真实ip,就必须在nginx这里作一个赋值操作,如下:

proxy_set_header X-real-ip $remote_addr;

其中这个X-real-ip是一个自定义的变量名,名字可以随意取,这样做完之后,用户的真实ip就被放在X-real-ip这个变量里了,然后,在web端可以这样获取:

request.getAttribute("X-real-ip")

这样就明白了吧。

使用Docker配置Nginx环境部署Nextcloud

相关介绍

  • NGINX
    Nginx是一款功能强大的反向代理服务器,支持HTTP、HTTPS、SMTP、IMAP、POP3等协议,它也可以作为负载均衡器、HTTP缓存或是Web服务器。

  • Docker
    Docker是一款轻量级虚拟机软件,他充分利用操作系统本身已有的机制和特性,实现远超传统虚拟机额度轻量级虚拟化。

  • Nextcloud
    Nextcloud是一款功能强大的PHP网盘程序,衍生自著名开源项目ownCloud,拥有美观的Web界面和强大的扩展能力,以及优秀的安全性能。可满足复杂条件下对私有云的需求。

前言

Nextcloud复杂的功能和高安全似乎决定了它的臃肿——但我们不能像对于WordPress一样责怪开发人员。但Nextcloud无后端设计使它在性能落后于Seafile这样的程序,我一直坚持Apache+PHP是Nextcloud最好的搭档,现在我依旧坚持这一点,看看那冗长的Nginx配置文件,而Apache几乎什么都不用做。尽管如此,我还是不得不嫉妒Nginx处理静态文件的优秀性能。

或许以上就是LNMPA诞生的原因吧。但我今天并不准备演示一遍如何搭建LNMPA环境,而是寻找另一种更简易的方法:Nginx+Docker。

其实很简单,不过是将Apache装入容器中而已:

未分类

由此图可以看出,这并没有什么复杂的地方,根本谈不上高深。

那么,将ApacheMySQL等服务放进容器有什么好处呢?

  • 易部署:所使用的都是现成的景象,随时启用,随时删除。
  • 高容错:操作出现任何问题也不会对宿主机有什么影响。
  • 模块化:附加的服务(ONLYOFFICE、Collabora Online、XMPP等)均运行于独立的容器中,互不干扰,增删方便。
  • 免于处理各种复杂的兼容问题。

但我今天就是要简单的问题复杂化,把每一部分都分析透彻。

Docker

Nextcloud在Docker Hub上有已经配置完成的镜像,使用Apache+PHP或是Nginx+FPM,但是不包含MySQL或MariaDB这样的数据库应用,也不直接支持HTTPS访问。

对于缺少的数据库应用,当然可以使用SQLite来应付这个问题,但是,显然不是最佳的解决方案。

最佳的解决方案也不是使用宿主机的数据库服务,而是使用Docker的一个关键功能——容器互联。

容器互联(linking)是一种让多个容器中的应用进行快速交互的方式。它会在源和接受容器中间创建连接关系,接受容器可以通过容器名快速访问到源容器而不用指出具体的IP地址。

举个例子,我们运行一个容器的命令一般是这样的:

dock run -d <container>

可以加上 –name 来为这个容器指定一个名字吗,比如“c1”

docker run -d --name c1 <container>

概念:Docker网桥(Net Bridge)

Docker在创建容器时会默认将容器连接于一个虚拟网桥(docker0)上,这实际上是一个Linux网桥,可以理解为是一个软件交换机(和家里的路由器有几分相像)。它会在挂载其上的接口进行转发,如图:

未分类

docker0可以理解为一个局域网,就像你家的网络与电信服务商之间隔了一个路由器,两个网络之间无法直接访问,除非映射端口。

未分类

(映射端口的操作使用 -p 宿主机端口:容器端口来完成)

如果你操作过路由器上的端口映射功能,这部分会很好理解。

对于docker0内部,每个容器都会分配到一个IP地址,同时,在每个容器内的hosts文件中会记下IP地址与容器的对应关系,这样,如果一个容器想要访问另一个容器,只需要知道容器的ID或者容器名,就像域名一样,而不必获知它的IP地址。

有了网桥,我们就可以将Apache和MySQL分别部署到两个容器中,通过容器名来访问。

数据的操作和持久化

无论是使用Docker,还是Virtualbox亦或是VMware这样的虚拟机软件,实现宿主机和虚拟机之间的文件互访一直是很重要的一件事。这儿我们要用到Docker的数据管理方式之一——数据卷。

  • 数据卷(Data Volumes):容器内数据直接映射到本地主机环境。

数据卷是一个可供容器使用的特殊目录,它会将主机操作系统目录直接映射至容器。

一个典型的例子:我创建了一个带有HTTP服务器的容器,在宿主机上使用Nginx反向代理指向它的请求,为了提高性能,需要分离客户端的动态请求和静态请求。此时,我就可以将容器内的文件映射出来,对于动态请求,Nginx会与容器进行通信,而对于静态请求,Nginx可以直接从本地获得静态文件,提高速度。

提前说一下,上面的例子并不适用于Nextcloud,或者说,我还没找到正确的途径。

1. 在容器内创建一个数据卷

在用Docker run命令的时候,使用 -v 标记可以在容器内创建一个数据卷。标记可重复使用。示例:

docker run -d -P --name web -v /webapp training/webapp python app.py

2. 挂载一个主机目录作为数据卷

格式和映射端口相同, -v 本地目录:容器内目录 (本地目录必须为绝对路径)。

如果想更集中地去管理容器的数据的话,可以使用数据卷容器,不再赘述。

Nginx

Nginx在这里的身份是反向代理服务器,之前我一直将Nginx用作HTTP服务器,现在才正式接触Nginx一直所标榜的功能。

反向代理的配置可以十分简单,直接在server{}中加入:

location / 
{
proxy_pass http://代理地址:端口;
}

以上就是一个反向代理配置,但是这还不够,并且在接下来的时间里就会发现这远远不够。

所谓反向代理,对真实服务器来说无感知,也就是说它并不知道有Nginx这一个的存在。因为对服务端来说,它一直只被一个永户访问,就是反向代理服务器Nginx,而且一直是使用一个URL访问(http://代理地址:端口)。所幸的是,这些状况都是由上方的那简单的配置而导致的,通过添加一些配置信息,就可以解决这个问题:

location / {
         proxy_pass http://代理地址:端口;
         proxy_set_header Host $http_host;
         proxy_set_header X-Forwarded-Proto $scheme;
         proxy_set_header X-Real-IP $remote_addr;
         }

看看,都添加了哪些东西。

  • proxy_set_header Host $http_host; 传递了客户端(相对于Nginx)的URL地址,使得服务端得知访问它所用的URL是外部客户端所访问的真实URL。
  • proxy_set_header X-Real-IP $remote_addr; 获得客户端的真实IP,而不是Nginx的127.0.0.1
  • proxy_set_header X-Forwarded-Proto $scheme; 使服务端能正确识别客户端所用的是HTTP协议还是HTTPS协议

WebSocket代理

现在还有一个棘手的问题,很多Web应用都使用了WebSocket技术,以实现ajax难以实现的一些功能。但在前文中的配置下,Nginx并不会去代理WebSocket请求,Websocket协议是这样的: ws://服务器地址/ 或 wss://服务器地址/。

既然我这儿都把问题说出来了,那肯定就有解决方法咯。

在前文的配置中再加入以下内容:

proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection $connection_upgrade;

这样,我们就得到了一个靠谱的反向代理配置:

location / {
         proxy_pass http://代理地址:端口;
         proxy_set_header Host $http_host;
         proxy_set_header X-Forwarded-Proto $scheme;
         proxy_set_header X-Real-IP $remote_addr;
         proxy_set_header Upgrade $http_upgrade; 
         proxy_set_header Connection $connection_upgrade;
         }

它应该能应付大多数应用了。

等下……

欸?为什么反向代理就高效了?

明明没有啊?

的确没有。

在目前的配置下,我们只是在客户端和服务端之间安排了一个中间人,而且既符合常理也符合实际地说,它变慢了(虽然感觉上微乎其微)。就好比给网站做CDN,做了的全站CDN却没有缓存,所有请求由CDN服务器转发给源服务器,再由源服务器将所有回应全部转发给CDN服务器,实际上增加了中间过程,对响应速度没有丝毫改善。而如果做了CDN缓存,CDN服务器会承担相当一部分请求,回源的请求会大幅减少甚至为0。

同样的,Nginx也是如此,我们得想办法让Nginx自己去根据静态请求返回静态文件,让服务端少为它不擅长的静态文件传输浪费功夫,全心全意地去处理静态请求(Tomcat是个典型的例子,所以我们经常看到把Nginx和Tomcat结合起来用)。

这个解决办法就叫动静分离。

只需要对客户端发来的请求过滤一下,分出其中哪些是动态请求,哪些是静态请求。至于分离方法,可以使用强大正则表达式来匹配URL:

.*.(gif|jpg|png|htm|html|css|js|flv|ico|swf)(.*)

应用到Nginx中:

location / {
         proxy_pass http://代理地址:端口;
         proxy_set_header Host $http_host;
         proxy_set_header X-Forwarded-Proto $scheme;
         proxy_set_header X-Real-IP $remote_addr;
         proxy_set_header Upgrade $http_upgrade; 
         proxy_set_header Connection $connection_upgrade;
         }
location ~ .*.(gif|jpg|png|htm|html|css|js|flv|ico|swf)(.*)
         {
         root /var/www/html/static; #静态文件存放位置
         expires 30d; #缓存30天
         }

这就将动静态请求分离了出来,凡是带有gif、jpg、png……的请求统统从本地 /var/www/html/static目录中获取。而动态请求则发送至服务端。

为什么之前说不适用于Nextcloud呢?

因为Nextcloud的一些静态文件实则是由动态语言实时生成的,比如这个:

https://cloud.orgleaf.com/index.php/css/files/92221bb11c10969dc0aad6345517ad93-merged.css

对于这种请求,Nginx仍然会傻乎乎地到/var/www/html/static里去找,当然找不到。

这种问题也许能用更复制的正则表达式来解决,但有没有更简单的办法呢?

直接设置缓存所有静态请求,这样第二次访问的时候就自动从自身的缓存中获取,缓存中没有的内容就找服务端获取,然后再缓存下来:

location ~ .*.(gif|jpg|png|htm|html|css|js|flv|ico|swf)(.*) {
         proxy_pass http://代理地址:端口;
         proxy_redirect off;         
         proxy_set_header Host $host;
         proxy_cache cache_one;
         proxy_cache_valid 200 302 1h;      
         proxy_cache_valid 301 1d;
         proxy_cache_valid any 1m;
         expires 30d;
        }

另外还要在http{}中加入以下内容:

proxy_temp_path /app/nextcloud/temp_dir;  #临时文件夹
proxy_cache_path /app/nextcloud/cache levels=1:2 keys_zone=cache_one:200m inactive=1d max_size=30g;
                 #     ⇑缓存位置          目录深度⇑                                         ⇑最大体积

好了,现在Nginx的反向代理真的有了它的积极作用。

还有一点——post最大体积

使用Docker和Nginx搭建Nextcloud完成后,我在上传一个约70MB的文件时出现错误,Nextcloud本身没什么动静。看了下console,发现服务器返回错误码413:请求实体太大。

POST的最大体积需要在http{}中的 client_max_body_size 设置,例如:

client_max_body_size 100m;

完成后就可以上传小于100MB的文件了。

Nextcloud

或许我应该把这部分放到Docker里说。

从hub.docker.com获得的官方Nextcloud镜像是不包含数据库服务的,而镜像也阉割了很多常用命令。这样我们就不得不用容器互联来用上数据库了。

如前所述,如果我添加了两个容器,一个运行Nextcloud,另一个运行MySQL,这两个容器默认是在同一网桥上,而我就可以把它们连接起来。

首先运行MySQL容器,我给它起名为db1,然后在MYSQL_ROOT_PASSWORD后面指定root密码为my-secret-pw

docker run --name db1 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

然后运行Nextcloud容器,用 –link my-mysql:mysql 把它和MySQL容器连接起来

docker run -d -p 8080:80  --link db1:mysql nextcloud 

官方提供了一个docker-compose文件,可以看出他这儿使用MariaDB作为数据库,命名为db,并在nextcloud服务中连接

version: '2'

volumes:
  nextcloud:
  db:

services:
  db:
    image: mariadb
    restart: always
    volumes:
      - db:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_PASSWORD=
      - MYSQL_DATABASE=nextcloud
      - MYSQL_USER=nextcloud

  app:  
    image: nextcloud
    ports:
      - 8080:80
    links:
      - db
    volumes:
      - nextcloud:/var/www/html
    restart: always

启动容器后访问Nextcloud容器,填入MySQL/MariaDB的服务器地址时直接填“db”就可以了,因为“db”就是MySQL容器的主机名,hosts文件中已经指明了“db”对应的容器。

数据卷

把数据卷映射至本地,这样我们如果要更改Nextcloud的文件,直接在宿主机上就可以操作了,即前文中所说的方法。

我们一共要映射这三个目录:

nextcloud:/var/www/html          #Nextcloud的程序
data:/var/www/html/data          #Nextcloud的数据目录
config:/var/www/html/config      #Nextcloud的配置文件所在目录
apps:/var/www/html/apps          #Nextcloud应用的目录

具体命令就是这样:

docker run -d nextcloud 
-v nextcloud:/var/www/html 
-v apps:/var/www/html/custom_apps 
-v config:/var/www/html/config 
-v data:/var/www/html/data

注意:冒号前面的nextcloud、apps、config、data要全部替换为本地的绝对路径

OCC命令

使用docker exec命令以在宿主机上为容器执行OCC命令:

docker exec --user www-data CONTAINER_ID php occ

CONTAINER_ID就是运行的Nextcloud容器的ID,如果你给容器命了名,那么也可以是这个容器的名字。

本文到此结束,我也要暂时的疏远Nextcloud/ownCloud了,更广阔的世界还等着我去探索。

Nginx配置文件注释说明

nginx配置文件主要分为六个区域:

  • main 控制子进程的所属用户/用户组、派生子进程数、错误日志位置/级别、pid位置、子进程优先级、进程对应cpu、进程能够打开的文件描述符数目等

  • events 控制nginx处理连接的方式

  • http

  • sever

  • location

  • upstream

实例:

# 运行用户
user www-data;

# 启动进程数,通常设置成和cpu的数量相等
worker_processes 1;

# 全局错误日志
error_log /var/log/nginx/error.log;

# PID文件
pid /var/run/nginx.pid;

events {
  # 使用epoll多路复用模式
  use epoll;   

  # 单个后台worker process进程的最大并发链接数
  worker_connections  1024;
  # multi_accept on; 
}

http {
  # 设定mime类型,类型由mime.type文件定义
  include       /etc/nginx/mime.types;

  # 1 octet = 8 bit
  default_type  application/octet-stream;

  # 设定访问日志
  access_log    /var/log/nginx/access.log;

  # sendfile指令指定nginx是否调用sendfile函数(zero copy方式)来输出文件,对于普通应用,必须设为on,如果用来进行下载等应用磁盘IO重负载应用,可设置为off,以平衡磁盘与网络I/O处理速度,降低系统的uptime.
  sendfile        on;

  # 在一个数据包里发送所有头文件,而不一个接一个的发送
  #tcp_nopush     on;

  # 连接超时时间
  keepalive_timeout  65;

  # 作用于socket参数TCP_NODELAY,禁用nagle算法,也即不缓存数据
  tcp_nodelay        on;

  # 开启gzip压缩
  gzip  on;
  gzip_disable "MSIE [1-6].(?!.*SV1)";

  # 设定请求缓冲
  client_header_buffer_size    1k;
  large_client_header_buffers  44k;

  include /etc/nginx/conf.d/*.conf;
  include /etc/nginx/sites-enabled/*;

  # 设定负载均衡的服务器列表
  upstream mysvr {
    # weigth参数表示权值,权值越高被分配到的几率越大
    # 本机上的Squid开启3128端口
    server 192.168.8.1:3128 weight=5;
    server 192.168.8.2:80 weight=1;
    server 192.168.8.3:80 weight=6;
  }

server {
  # 侦听80端口
  listen 80;

  # 定义使用www.xx.com访问
  server_name  www.xx.com;

  # 设定本虚拟主机的访问日志
  access_log  logs/www.xx.com.access.log  main;

  # 默认请求
  location / {
    # 定义服务器的默认网站根目录位置
    root   /root;       

    # 定义首页索引文件的名称
    index index.php index.html index.htm;  

    fastcgi_pass  localhost:9000;
      fastcgi_param  SCRIPT_FILENAME  $document_root/$fastcgi_script_name; 
      include /etc/nginx/fastcgi_params;  
    }

    # 定义错误提示页面
    error_page   500 502 503 504 /50x.html;  
      location = /50x.html {
      root   /root;
    }

    # 静态文件,nginx自己处理
    location ~ ^/(images|javascript|js|css|flash|media|static)/ {
      root /var/www/virtual/htdocs;

      # 过期时间30天
      expires 30d;
    }

    # PHP脚本请求全部转发到FastCGI处理,使用FastCGI默认配置
    location ~ .php$ {
      root /root;
      fastcgi_pass 127.0.0.1:9000;
      fastcgi_index index.php;
      fastcgi_param SCRIPT_FILENAME /home/www/www$fastcgi_script_name;
      include fastcgi_params;
    }

    # 设定查看Nginx状态的地址
    location /NginxStatus {
      stub_status on;
      access_log on;
      auth_basic "NginxStatus";
      auth_basic_user_file conf/htpasswd;
    }

    # 禁止访问 .htxxx 文件
    location ~ /.ht {
      deny all;
    }
  }
}

mysql查看连接数命令show processlist详解

经常会遇到一些连接数超标或者mysql链接出现异常(比如表被锁等)导致的报错,此时直接重启不仅影响线上的业务而且指标不治本。

我们处理方式一般是不重启服务的前提下处理异常的表列,同时排查是服务器问题、mysql参数配置问题还是程序、或表本身设计的问题。

比如这里连接数:

[root@21yunwei /]# mysql   -uroot  -p123456  -e  "use 21yunwei;show processlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----------+----------+---------+------+----------+------------------+
| Id | User | Host      | db       | Command | Time | State    | Info             |
+----+------+-----------+----------+---------+------+----------+------------------+
| 27 | root | localhost | 21yunwei | Query   |    0 | starting | show processlist |
+----+------+-----------+----------+---------+------+----------+------------------+

show processlist查看方式:

mysqladmin  -uroot -p123456  processlist;
mysql   -uroot  -p123456  -e  "show processlist;"

说明:

  • 如果是以root用户登录,可以查看所有的进程信息。
  • 如果是以普通用户登录,只可以查看自己对应的数据库的信息。

如下信息取于酷喃博客:

  • 先简单说一下各列的含义和用途。
  • id列,不用说了吧,一个标识,你要kill一个语句的时候很有用。
  • user列,显示单前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。
  • host列,显示这个语句是从哪个ip的哪个端口上发出的。呵呵,可以用来追踪出问题语句的用户。
  • db列,显示这个进程目前连接的是哪个数据库。
  • command列,显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。
  • time列,此这个状态持续的时间,单位是秒。
  • state列,显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个sql语句,已查询为例,可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成。
  • info列,显示具体执行的sql语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列,mysql列出的状态主要有以下几种:

  • Checking table
    正在检查数据表(这是自动的)。
     
  • Closing tables
    正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
     
  • Connect Out
    复制从服务器正在连接主服务器。
     
  • Copying to tmp table on disk
    由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。
     
  • Creating tmp table
    正在创建临时表以存放部分查询结果。
     
  • deleting from main table
    服务器正在执行多表删除中的第一部分,刚删除第一个表。
     
  • deleting from reference tables
    服务器正在执行多表删除中的第二部分,正在删除其他表的记录。
     
  • Flushing tables
    正在执行FLUSH TABLES,等待其他线程关闭数据表。
     
  • Killed
    发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。
     
  • Locked
    被其他查询锁住了。
     
  • Sending data
    正在处理SELECT查询的记录,同时正在把结果发送给客户端。
     
  • Sorting for group
    正在为GROUP BY做排序。
     
  • Sorting for order
    正在为ORDER BY做排序。
     
    Opening tables
    这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。
     
  • Removing duplicates
    正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。
     
  • Reopen table
    获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。
     
  • Repair by sorting
    修复指令正在排序以创建索引。
     
  • Repair with keycache
    修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
     
  • Searching rows for update
    正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
     
  • Sleeping
    正在等待客户端发送新请求.
     
  • System lock
    正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
     
  • Upgrading lock
    INSERT DELAYED正在尝试取得一个锁表以插入新记录。
     
  • Updating
    正在搜索匹配的记录,并且修改它们。
     
  • User Lock
    正在等待GET_LOCK()。
     
  • Waiting for tables
    该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
     
  • waiting for handler insert
    INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。

参考资料:

mysql手册所有状态说明,链接如下:http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

mysql show processlist详解 http://coolnull.com/1230.html

实例分析MySQL下的四种事务隔离级别

数据库事务有四种隔离级别:

  • 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据。
  • 提交读(Read Committed):只能读取到已经提交的数据,Oracle等多数数据库默认都是该级别。
  • 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻读。
  • 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。

上面这样的教科书式定义第一次接触事务隔离概念的朋友看了可能会一脸懵逼,下面我们就通过具体的实例来解释四个隔离级别。

首先我们创建一个user表:

CREATE TABLE user (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

读未提交隔离级别

我们先将事务的隔离级别设置为read committed:

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

在下面我们开了两个终端分别用来模拟事务一和事务二,p.s: 操作一和操作二的意思是按照时间顺序来执行的。

事务1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user(name) values('ziwenxie'); # 操作3
Query OK, 1 row affected (0.05 sec)

事务2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user; # 操作4
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

从上面的执行结果可以和清晰的看出来,在read uncommited级别下面我们在事务一中可能会读取到事务二中没有commit的数据,这就是脏读。

读提交隔离级别

通过设置隔离级别为committed可以解决上面的脏读问题。

mysql> set session transaction isolation level read committed;

事务一

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)


mysql> select * from user; # 操作三
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user; # 操作五,操作四的修改并没有影响到事务一
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user; # 操作七

+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

事务二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)

mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

虽然脏读的问题解决了,但是注意在事务一的操作七中,事务二在操作六commit后会造成事务一在同一个transaction中两次读取到的数据不同,这就是不可重复读问题,使用第三个事务隔离级别repeatable read可以解决这个问题。

可重复读隔离级别

MySQL的Innodb存储引擎默认的事务隔离级别就是可重复读隔离级别,所以我们不用进行多余的设置。

事务一

mysql> start tansactoin; # 操作一

mysql> select * from user; # 操作五
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

事务二

mysql> start tansactoin; # 操作二

mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit; # 操作四

在事务一的操作五中我们并没有读取到事务二在操作三中的update,只有在commit之后才能读到更新后的数据。

Innodb解决了幻读么

实际上RR级别是可能产生幻读,InnoDB引擎官方称中利用MVCC多版本并发控制解决了这个问题,下面我们验证一下Innodb真的解决了幻读了么?

为了方便展示,我修改了一下上面的user表:

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)

mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)

mysql> select * from user;
+----+----------+----------+
| id | name     | salary   |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

事务一

mysql> start transaction;  # 操作一
Query OK, 0 rows affected (0.00 sec)

mysql> update user set salary='4444'; # 操作六,竟然影响了两行,不是说解决了幻读么?
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user; # 操作七, Innodb并没有完全解决幻读
+----+----------+--------+
| id | name     | salary |
+----+----------+--------+
| 10 | ziwenxie |   4444 |
| 11 | zhangsan |   4444 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

事务二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)

mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

从上面的例子可以看出,Innodb并没有如官方所说解决幻读,不过上面这样的场景中也不是很常见不用过多的担心。

串行化隔离级别

所有事务串行执行,最高隔离级别,不会出现幻读性能会很差,实际开发中很少使用到。

配置Vsftpd MySQL虚拟用户验证登录

一、安装所需要包和包组:

在数据库服务器上安装包:

yum –y install mariadb-server mariadb-devel 
systemctl start mariadb.service 
systemctl enable mariadb

在FTP服务器上安装包pam_mysql

centos7:无对应rpm包,需手动编译安装

yum -y groupinstall “Development Tools” 
yum -y install mariadb-devel pam-devel vsftpd 

下载pam_mysql-0.7RC1.tar.gz

ftp://172.16.0.1/pub/Sources/sources/pam/ 
tar xvfpam_mysql-0.7RC1.tar.gz 
cd pam_mysql-0.7RC1/ 
./configure –with-mysql=/usr –with-pam=/usr –with-pam-mods-dir=/lib64/security 
make 
make install

二、在数据库服务器上创建虚拟用户账号

1、建立存储虚拟用户的数据库

示例创建为vsftpd数据库

mysql> CREATE DATABASE vsftpd; 
mysql> SHOW DATABASES; 

ftp服务和mysql不在同一主机:

mysql> GRANT SELECT ON vsftpd.* TO 
vsftpd@’172.16.%.%’ IDENTIFIED BY ‘magedu’; 

ftp服务和mysql在同一主机:

mysql> GRANT SELECT ON vsftpd.* TO 
vsftpd@localhost IDENTIFIED BY ‘magedu’; 
mysql> GRANT SELECT ON vsftpd.* TO 
vsftpd@’127.0.0.1′ IDENTIFIED BY ‘magedu’; 
mysql> FLUSH PRIVILEGES;

2、准备相关表

mysql> USE vsftpd; 
Mysql> SHOW TABLES; 
mysql> CREATE TABLE users ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,name CHAR(50) BINARY NOT NULL,password CHAR(48) BINARY NOT NULL); 
mysql>DESC users; 

mysql -uvsftpd -h 172.16.200.200 -pmagedu 
mysql> SHOW DATABASES;

3、添加测试的虚拟用户

根据需要添加所需要的用户,为了安全应该使用PASSWORD函数加密其密码后存储

mysql>DESC users; 
mysql> INSERT INTO users(name,password) values(‘wang’,password(‘magedu’)); 
mysql> INSERT INTO users(name,password) values(‘mage’,password(‘magedu’)); 
mysql> SELECT * FROM users;

三、在FTP服务器上配置vsftpd服务

1、在FTP服务器上建立pam认证所需文件

vi /etc/pam.d/vsftpd.mysql 

添加如下两行

auth required pam_mysql.so user=vsftpd passwd=magedu host=mysqlserver db=vsftpd table=users usercolumn=name passwdcolumn=password crypt=2 
account required pam_mysql.so user=vsftpd passwd=magedu host=mysqlserver db=vsftpd table=users usercolumn=name passwdcolumn=password crypt=2 

注意:参考README文档,选择正确的加密方式crypt是加密方式,0表示不加密,1表示crypt(3)加密,2表示使用mysql password()函数加密,3表示md5加密,4表示sha1加密

配置字段说明

  • auth 表示认证
  • account 验证账号密码正常使用
  • required 表示认证要通过
  • pam_mysql.so模块是默认的相对路径,是相对/lib64/security/路径而言,也可以写绝对路径;后面为给此模块传递的参数
  • user=vsftpd为登录mysql的用户
  • passwd=mageedu 登录mysql的的密码
  • host=localhost 在mysql中定义的允许连接的主机名或ip地址
  • db=vsftpd 连接msyql的哪一个库
  • table=users 连接库里的哪一个表
  • usercolumn=name 当做用户名的字段
  • passwdcolumn=password 当做用户名字段的密码
  • crypt=2 密码的加密方式为mysql password()函数加密

2、建立相应用户和修改vsftpd配置文件

使其适应mysql认证

建立虚拟用户映射的系统用户及对应的目录

useradd -s /sbin/nologin -d /var/ftproot vuser 
chmod 555 /var/ftproot centos7 需除去ftp根目录的写权限 
mkdir /var/ftproot/{upload,pub} 
setfacl –m u:vuser:rwx /var/ftproot/upload 

确保/etc/vsftpd.conf中已经启用了以下选项

anonymous_enable=YES 

添加下面两项

guest_enable=YES 
guest_username=vuser 

修改下面一项,原系统用户无法登录

pam_service_name=vsftpd.mysql

四、启动vsftpd服务

service vsftpd start;systemctl start vsftpd 
chkconfig vsftpd on;systemctl enable vsftpd 

查看端口开启情况

netstat -tnlp |grep :21

五、Selinux相关设置:在FTP服务器上执行

restorecon -R /lib64/security 
setsebool -P ftpd_connect_db 1 
setsebool -P ftp_home_dir 1 
chcon -R -t public_content_rw_t /var/ftproot/

六、测试:利用FTP客户端工具,以虚拟用户登录验证结果

tail /var/log/secure

七、在FTP服务器上配置虚拟用户具有不同的访问权限

vsftpd可以在配置文件目录中为每个用户提供单独的配置文件以定义其ftp服务访问权限,每个虚拟用户的配置文件名同虚拟用户的用户名。配置文件目录可以是任意未使用目录,只需要在vsftpd.conf指定其路径及名称即可

1、配置vsftpd为虚拟用户使用配置文件目录

vim /etc/vsftpd/vsftpd.conf 

添加如下选项

user_config_dir=/etc/vsftpd/vusers_config

2、创建所需要目录

并为虚拟用户提供配置文件

mkdir /etc/vsftpd/vusers_config/ 
cd /etc/vsftpd/vusers_config/ 
touch wangmage

3、配置虚拟用户的访问权限

虚拟用户对vsftpd服务的访问权限是通过匿名用户的相关指令进行的。如果需要让用户wang具有上传文件的权限,可以修改/etc/vsftpd/vusers_config/wang文件,在里面添加如下选项并设置为YES即可,只读则设为NO

注意:需确保对应的映射用户对于文件系统有写权限

anon_upload_enable={YES|NO} 
anon_mkdir_write_enable={YES|NO} 
anon_other_write_enable={YES|NO}

MySQL调优/优化的100 个建议

MySQL是一个强大的开源数据库。随着MySQL上的应用越来越多,MySQL逐渐遇到了瓶颈。这里提供 101 条优化 MySQL 的建议。有些技巧适合特定的安装环境,但是思路是相通的。我已经将它们分成了几类以帮助你理解。

MySQL监控MySQL服务器硬件和OS(操作系统)调优

1、有足够的物理内存,能将整个InnoDB文件加载到内存里 —— 如果访问的文件在内存里,而不是在磁盘上,InnoDB会快很多。

2、全力避免 Swap 操作 — 交换(swapping)是从磁盘读取数据,所以会很慢。

3、使用电池供电的RAM(Battery-Backed RAM)。

4、使用一个高级磁盘阵列 — 最好是 RAID10 或者更高。

5、避免使用RAID5 — 和校验需要确保完整性,开销很高。

6、将你的操作系统和数据分开,不仅仅是逻辑上要分开,物理上也要分开 — 操作系统的读写开销会影响数据库的性能。

7、将临时文件和复制日志与数据文件分开 — 后台的写操作影响数据库从磁盘文件的读写操作。

8、更多的磁盘空间等于更高的速度。

9、磁盘速度越快越好。

10、SAS优于SATA。

11、小磁盘的速度比大磁盘的更快,尤其是在 RAID 中。

12、使用电池供电的缓存 RAID(Battery-Backed Cache RAID)控制器。

13、避免使用软磁盘阵列。

14、考虑使用固态IO卡(不是磁盘)来作为数据分区 — 几乎对所有量级数据,这种卡能够支持 2 GBps 的写操作。

15、在 Linux 系统上,设置 swappiness 的值为0 — 没有理由在数据库服务器上缓存文件,这种方式在Web服务器或桌面应用中用的更多。

16、尽可能使用 noatime 和 nodirtime 来挂载文件系统 — 没有必要为每次访问来更新文件的修改时间。

17、使用 XFS 文件系统 — 一个比ext3更快的、更小的文件系统,拥有更多的日志选项,同时,MySQL在ext3上存在双缓冲区的问题。

18、优化你的 XFS 文件系统日志和缓冲区参数 – -为了获取最大的性能基准。

19、在Linux系统中,使用 NOOP 或 DEADLINE IO 调度器 — CFQ 和 ANTICIPATORY 调度器已经被证明比 NOOP 和 DEADLINE 慢。

20、使用 64 位操作系统 — 有更多的内存能用于寻址和 MySQL 使用。

21、将不用的包和后台程序从服务器上删除 — 减少资源占用。

22、将使用 MySQL 的 host 和 MySQL自身的 host 都配置在一个 host 文件中 — 这样没有 DNS 查找。

23、永远不要强制杀死一个MySQL进程 — 你将损坏数据库,并运行备份。

24、让你的服务器只服务于MySQL — 后台处理程序和其他服务会占用数据库的 CPU 时间。

MySQL 配置

25、使用 innodb_flush_method=O_DIRECT 来避免写的时候出现双缓冲区。

26、避免使用 O_DIRECT 和 EXT3 文件系统 — 这会把所有写入的东西序列化。

27、分配足够 innodb_buffer_pool_size ,来将整个InnoDB 文件加载到内存 — 减少从磁盘上读。

28、不要让 innodb_log_file_size 太大,这样能够更快,也有更多的磁盘空间 — 经常刷新有利降低发生故障时的恢复时间。

29、不要同时使用 innodb_thread_concurrency 和 thread_concurrency 变量 — 这两个值不能兼容。

30、为 max_connections 指定一个小的值 — 太多的连接将耗尽你的RAM,导致整个MySQL服务器被锁定。

31、保持 thread_cache 在一个相对较高的数值,大约是 16 — 防止打开连接时候速度下降。

32、使用 skip-name-resolve — 移除 DNS 查找。

33、如果你的查询重复率比较高,并且你的数据不是经常改变,请使用查询缓存 — 但是,在经常改变的数据上使用查询缓存会对性能有负面影响。

34、增加 temp_table_size — 防止磁盘写。

35、增加 max_heap_table_size — 防止磁盘写。

36、不要将 sort_buffer_size 的值设置的太高 — 可能导致连接很快耗尽所有内存。

37、监控 key_read_requests 和 key_reads,以便确定 key_buffer 的值 — key 的读需求应该比 key_reads 的值更高,否则使用 key_buffer 就没有效率了。

38、设置 innodb_flush_log_at_trx_commit = 0 可以提高性能,但是保持默认值(1)的话,能保证数据的完整性,也能保证复制不会滞后。

39、有一个测试环境,便于测试你的配置,可以经常重启,不会影响生产环境。

MySQL Schema 优化:

40、保证你的数据库的整洁性。

41、归档老数据 — 删除查询中检索或返回的多余的行

42、在数据上加上索引。

43、不要过度使用索引,评估你的查询。

44、压缩 text 和 blob 数据类型 — 为了节省空间,减少从磁盘读数据。

45、UTF 8 和 UTF16 比 latin1 慢。

46、有节制的使用触发器。

47、保持数据最小量的冗余 — 不要复制没必要的数据.

48、使用链接表,而不是扩展行。

49、注意你的数据类型,尽可能的使用最小的。

50、如果其他数据需要经常需要查询,而 blob/text 不需要,则将 blob/text 数据域其他数据分离。

51、经常检查和优化表。

52、经常做重写 InnoDB 表的优化。

53、有时,增加列时,先删除索引,之后在加上索引会更快。

54、为不同的需求选择不同的存储引擎。

55、日志表或审计表使用ARCHIVE存储引擎 — 写的效率更高。

56、将 session 数据存储在 memcache 中,而不是 MySQL 中 — memcache 可以设置自动过期,防止MySQL对临时数据高成本的读写操作。

57、如果字符串的长度是可变的,则使用VARCHAR代替CHAR — 节约空间,因为CHAR是固定长度,而VARCHAR不是(utf8 不受这个影响)。

58、逐步对 schema 做修改 — 一个小的变化将产生的巨大的影响。

59、在开发环境测试所有 schema 变动,而不是在生产环境的镜像上去做。

60、不要随意改变你的配置文件,这可能产生非常大的影响。

61、有时候,少量的配置会更好。

62、质疑使用通用的MySQL配置文件。

查询优化

63、使用慢查询日志,找出执行慢的查询。

64、使用 EXPLAIN 来决定查询功能是否合适。

65、经常测试你的查询,看是否需要做性能优化 — 性能可能会随着时间的变化而变化。

66、避免在整个表上使用count(*) ,它可能会将整个表锁住。

67、保持查询一致,这样后续类似的查询就能使用查询缓存了。

68、如果合适,用 GROUP BY 代替 DISTINCT。

69、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。

70、保证索引简单,不要在同一列上加多个索引。

71、有时,MySQL 会选择错误的索引,这种情况使用 USE INDEX。

72、使用 SQL_MODE=STRICT 来检查问题。

73、索引字段少于5个时,UNION 操作用 LIMIT,而不是 OR。

74、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 来代替 UPDATE,避免 UPDATE 前需要先 SELECT。

75、使用索引字段和 ORDER BY 来代替 MAX。

76、避免使用 ORDER BY RAND()。

77、LIMIT M,N 在特定场景下会降低查询效率,有节制使用。

78、使用 UNION 来代替 WHERE 子句中的子查询。

79、对 UPDATE 来说,使用 SHARE MODE 来防止排他锁。

80、重启 MySQL 时,记得预热数据库,确保将数据加载到内存,提高查询效率。

81、使用 DROP TABLE ,然后再 CREATE TABLE ,而不是 DELETE FROM ,以删除表中所有数据。

82、最小化你要查询的数据,只获取你需要的数据,通常来说不要使用 *。

83、考虑持久连接,而不是多次建立连接,已减少资源的消耗。

84、基准查询,包括服务器的负载,有时一个简单的查询会影响其他的查询。

85、当服务器的负载增加时,使用SHOW PROCESSLIST来查看慢的/有问题的查询。

86、在存有生产环境数据副本的开发环境中,测试所有可疑的查询。

MySQL备份过程

87、在二级复制服务器上进行备份。

88、备份过程中停止数据的复制,以防止出现数据依赖和外键约束的不一致。

89、彻底停止MySQL之后,再从数据文件进行备份。

90、如果使用MySQL dump进行备份,请同时备份二进制日志 — 确保复制过程不被中断。

91、不要信任 LVM 快照的备份 — 可能会创建不一致的数据,将来会因此产生问题。

92、为每个表做一个备份,这样更容易实现单表的恢复 — 如果数据与其他表是相互独立的。

93、使用 mysqldump 时,指定 -opt 参数。

94、备份前检测和优化表。

95、临时禁用外键约束,来提高导入的速度。

96、临时禁用唯一性检查,来提高导入的速度。

97、每次备份完后,计算数据库/表数据和索引的大小,监控其增长。

98、使用定时任务(cron)脚本,来监控从库复制的错误和延迟。

99、定期备份数据。

100、定期测试备份的数据。

MySQL主键 数据类型及索引的优化

一、安装

这是同事分享给我的。安装数据库也是一门学问,千万不要被安装的简单性而忽略一些细节。针对于 Win os 服务器而言,MySQL 的安装版可以选则三种不同的服务器类型:

  • Developer Machine(开发机器)
    为 MySQL 分配最少分系统资源

  • Server Machine(服务器)
    为 MySQL 分配一定比例的系统资源

  • Dedicated MySQL Server Machine(专用MySQL服务器)
    为 MySQL 分配所有的系统资源

如果你跟我一样不幸,不仅选择了 Win os 做服务器系统,还选择了 Developer Machine(开发机器),兄弟抱一个,不要哭,重装。发生这些上述不幸的原因已经不重要,需要做的是必须切换成 Dedicated MySQL Server Machine(专用MySQL服务器)。

重装切换之后,你会发现,之前安装的一定是假的 MySQL。

二、主键

主键或者唯一键可以用作某条记录的唯一标志符。主键生成有两种方式:

  • 自增
  • UUID

1、自增

自增方式有个重要因素是“步长”,也就是则增的幅度,在单机模式下,一般步长为1。若是在分布式数据库系统下,步长设为节点的数量,这样一来,就可以避免主键重复的情况。建议预估好节点的数量,步长不可小于节点数。

2、UUID

UUID 可以更有效地避免自增主键带来的烦恼,但是它也有不足之处:

  • UUID 过长,增大数据库总容量,降低性能
  • UUID 无序,插入数据时根据主键寻址费时

针对上述 UUID 的缺点,推特开发了“雪花算法”,并开源。其中心思想是利用时间戳、数据中心码、机器码、序列号组成有规则的 UUID,使其有序降低性能消耗。

我在项目中使用了“自增+步长2”,因为使用了主从,虽然不是分布式,但是双数据源也是两个节点,采用这种方式保险一些。

更多内容请移步我的其他博文:数据库自增长主键与-UUID (http://zhoupq.com/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%87%AA%E5%A2%9E%E9%95%BF%E4%B8%BB%E9%94%AE%E4%B8%8E-UUID/)
  
推荐 MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程(从百万到千万表记录测试) (http://blog.csdn.net/mchdba/article/details/52336203)

三、数据类型

1、长度

我一直秉承杀鸡就用杀鸡刀,宰牛采用宰牛刀的原则。一个“tab_NAME” 的数据类型非要整一个 “VARCHAR(500)”,这是浪费,过多的长度分配会造成空间占用太多,最终造成性能下降。有同事说我杞人忧天,一个库才二十几张表,即使每个字段都设成500,也不过如此嘛。从短期的结果上来看,结果没有受到明显影响。但是别忘了,我们是来解决问题的,如果因为我们的操作违反了约定,造成严重后果,那么我们将背负罪过。“量身定做”的好处不言而喻,列的长度亦是如此。

2、NULL

尽量不将列设为 NULL,从业务角度上看,NULL 是错误的,试想,既然是 NULL,哪有何必存在这个列呢?反之,既然存在这个列,那么 NULL 便失去了意义。让我设计表的时候,我都会给列设置一个初始值,“tab_UPDATETIME” 就设置为 “CURRENT_TIMESTAMP”,“tab_STATUS” 就设置为 “1”或者“0”。

从开发维护的角度看,如果不确定列是否为 NULL,那么在 SQL 中,就必须加上 “AND tab_NAME != NULL AND tab_NAME != ‘’”,很容易被忽略,代码越多,出错的概率就越大。

四、索引

好的索引是一颗仙丹,可以让迟缓的查询得到质的提升,否则,就是一碗毒药。

索引我做了三点优化:

  • 勿滥用索引
  • 最左前缀索引
  • 前缀索引

1、勿滥用索引

索引不是越多越好,因为生成索引需要时间,而且索引占表物理空间。表一大,查询速度多少会受影响。我亲眼看到同事建好表时候,无微不至地为每一个字段都建了索引,或者为每一个条件都建了索引,形如条件“a=1 and b =2 and c=3 and d=4”,为其建了“a”、“b”、“c”、“d”四个索引。浪费!低效!这种情况,应当使用“最左前缀索引”。

2、最左前缀索引

在生成联合索引时会碰到最左前缀索引,什么是最左前缀索引呢?就是在联合索引中,从最左边的索引开始匹配,直到遇上“like”、“>”、“>=”、“<”、“<=”等范围匹配时停止,即使后面有“=”都不再匹配。

简单举例:现有字段“a”、“b”、“c”、“d”组成的联合索引“abcd”,SQL 条件部分为:

a=1 and b =2 and c=3 and d=4
a=1 and b =2 and d=4 and c=3
a=1 and b =2 and c>=3 and d=4

  
1 用到索引为“abcd”,2 用到的索引为“abd”, (2 同 1)3 用到的索引为“abc”。条件的顺序很重要。跟自拍一样,脸大的站后面。
  
抱歉,上述第二点同第一点,同样用到的索引为“abcd”。
  
利用 EXPLAIN 工具分析:

// 建表语句略,已知创建了组合索引 (abcd) 
mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.c = 'e' AND t.d = 'f';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set
mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.d = 'f' AND t.c = 'e';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set

3、前缀索引

前缀索引是针对某个字段而言,我们知道 MySQL 中,有一个全文索引,“BOLD”、“TEXT” 是不能建全文索引。想想也能理解,这么长的全文索引得占多少空间,这显然是不现实的。最好的办法是为其创建“左前缀索引”,只取字符串的前面一小段作为索引,具体取多少,决定于多长的字符可以尽可能多的确定唯一记录。“varchar”同样受用。

更多内容请移步我的其他博文:MySQL 高性能索引之前缀索引 (http://zhoupq.com/MySQL-%E9%AB%98%E6%80%A7%E8%83%BD%E7%B4%A2%E5%BC%95%E4%B9%8B%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95/)

五、多表联合查询

不管是 Heibernate 在代码中拼 SQL,还是 MyBatis 在 Mapper.xml 中写 SQL,由于数据库范式的规范,导致为完成某项查询,必须联合多表查询。一个 LEFT JOIN 很常见,三四个 LEFT JOIN 呢?

多个 LEFT JOIN 肯定不行,即使有索引,也很容易造成全表扫描,为了减少该情况发生的概率,我一般会采取两种方法:

  • 反范式
  • 临时表

1、反范式

衡量一个 DBA 的水平有多高,得看他反范式能力有多强。
—— 知乎

比如我要根据 A表 的日期,关联 B表,统计出每个日期下某个属性的数量。我可以在A表中添加一列,用来存储“数量”,虽然违反了范式,但是性能上得到了提升。我觉得这是一笔划算的买卖。

规范化是为了技术服务,而技术是为业务服务。规范化也就是套路,能保证不出错,但是并不能解决特殊问题,特殊问题还需要特殊处理。

2、临时表

当需要联合三张表以上时,轻微的反范式已经不适用了,推荐用临时表,或者物化视图,但是 MySQL 的物化视图实现起来比较困难。事实上,我用的就是临时表,将四张表的部分数据抽离出来,保存在一张临时表中,制定一个“计划”,每天凌晨会自动更新。

好处

  • 加快查询速度

缺点

  • 会在某一时刻(凌晨)数据库IO过高
  • 可能会出现异常,做好事务管理,让其回滚,重新执行,再有问题,就需要人工干预
  • 数据准确性会延迟一天,适合非敏感业务

以上是仅针对数据库做的优化,至于缓存(一级缓存、二级缓存),那属于持久层框架的职责,不在此文记录范围之内。

Docker安装部署MongoDB的两种方法

方法一、通过 Dockerfile 构建

创建Dockerfile

首先,创建目录mongo,用于存放后面的相关东西。

runoob@runoob:~$ mkdir -p ~/mongo  ~/mongo/db

db目录将映射为mongo容器配置的/data/db目录,作为mongo数据的存储目录

进入创建的mongo目录,创建Dockerfile

FROM debian:wheezy

# add our user and group first to make sure their IDs get assigned consistently, regardless of whatever dependencies get added
RUN groupadd -r mongodb && useradd -r -g mongodb mongodb

RUN apt-get update 
    && apt-get install -y --no-install-recommends 
        numactl 
    && rm -rf /var/lib/apt/lists/*

# grab gosu for easy step-down from root
ENV GOSU_VERSION 1.7
RUN set -x 
    && apt-get update && apt-get install -y --no-install-recommends ca-certificates wget && rm -rf /var/lib/apt/lists/* 
    && wget -O /usr/local/bin/gosu "https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$(dpkg --print-architecture)" 
    && wget -O /usr/local/bin/gosu.asc "https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$(dpkg --print-architecture).asc" 
    && export GNUPGHOME="$(mktemp -d)" 
    && gpg --keyserver ha.pool.sks-keyservers.net --recv-keys B42F6819007F00F88E364FD4036A9C25BF357DD4 
    && gpg --batch --verify /usr/local/bin/gosu.asc /usr/local/bin/gosu 
    && rm -r "$GNUPGHOME" /usr/local/bin/gosu.asc 
    && chmod +x /usr/local/bin/gosu 
    && gosu nobody true 
    && apt-get purge -y --auto-remove ca-certificates wget

# gpg: key 7F0CEB10: public key "Richard Kreuter <[email protected]>" imported
RUN apt-key adv --keyserver ha.pool.sks-keyservers.net --recv-keys 492EAFE8CD016A07919F1D2B9ECBEC467F0CEB10

ENV MONGO_MAJOR 3.0
ENV MONGO_VERSION 3.0.12

RUN echo "deb http://repo.mongodb.org/apt/debian wheezy/mongodb-org/$MONGO_MAJOR main" > /etc/apt/sources.list.d/mongodb-org.list

RUN set -x 
    && apt-get update 
    && apt-get install -y 
        mongodb-org=$MONGO_VERSION 
        mongodb-org-server=$MONGO_VERSION 
        mongodb-org-shell=$MONGO_VERSION 
        mongodb-org-mongos=$MONGO_VERSION 
        mongodb-org-tools=$MONGO_VERSION 
    && rm -rf /var/lib/apt/lists/* 
    && rm -rf /var/lib/mongodb 
    && mv /etc/mongod.conf /etc/mongod.conf.orig

RUN mkdir -p /data/db /data/configdb 
    && chown -R mongodb:mongodb /data/db /data/configdb
VOLUME /data/db /data/configdb

COPY docker-entrypoint.sh /entrypoint.sh
ENTRYPOINT ["/entrypoint.sh"]

EXPOSE 27017
CMD ["mongod"]

通过Dockerfile创建一个镜像,替换成你自己的名字

runoob@runoob:~/mongo$ docker build -t mongo:3.2 .

创建完成后,我们可以在本地的镜像列表里查找到刚刚创建的镜像

runoob@runoob:~/mongo$ docker images  mongo:3.2
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mongo               3.2                 282fd552add6        9 days ago          336.1 MB

方法二、docker pull mongo:3.2

查找Docker Hub上的mongo镜像

runoob@runoob:~/mongo$ docker search mongo
NAME                              DESCRIPTION                      STARS     OFFICIAL   AUTOMATED
mongo                             MongoDB document databases ...   1989      [OK]       
mongo-express                     Web-based MongoDB admin int...   22        [OK]       
mvertes/alpine-mongo              light MongoDB container          19                   [OK]
mongooseim/mongooseim-docker      MongooseIM server the lates...   9                    [OK]
torusware/speedus-mongo           Always updated official Mon...   9                    [OK]
jacksoncage/mongo                 Instant MongoDB sharded cluster  6                    [OK]
mongoclient/mongoclient           Official docker image for M...   4                    [OK]
jadsonlourenco/mongo-rocks        Percona Mongodb with Rocksd...   4                    [OK]
asteris/apache-php-mongo          Apache2.4 + PHP + Mongo + m...   2                    [OK]
19hz/mongo-container              Mongodb replicaset for coreos    1                    [OK]
nitra/mongo                       Mongo3 centos7                   1                    [OK]
ackee/mongo                       MongoDB with fixed Bluemix p...  1                    [OK]
kobotoolbox/mongo                 https://github.com/kobotoolb...  1                    [OK]
valtlfelipe/mongo                 Docker Image based on the la...  1                    [OK]

这里我们拉取官方的镜像,标签为3.2

runoob@runoob:~/mongo$ docker pull mongo:3.2

等待下载完成后,我们就可以在本地镜像列表里查到REPOSITORY为mongo,标签为3.2的镜像。

使用mongo镜像

运行容器

runoob@runoob:~/mongo$ docker run -p 27017:27017 -v $PWD/db:/data/db -d mongo:3.2
cda8830cad5fe35e9c4aed037bbd5434b69b19bf2075c8626911e6ebb08cad51
runoob@runoob:~/mongo$

命令说明:

-p 27017:27017 :将容器的27017 端口映射到主机的27017 端口

-v $PWD/db:/data/db :将主机中当前目录下的db挂载到容器的/data/db,作为mongo数据存储目录

查看容器启动情况

runoob@runoob:~/mongo$ docker ps 
CONTAINER ID   IMAGE        COMMAND                   ...    PORTS                      NAMES
cda8830cad5f   mongo:3.2    "/entrypoint.sh mongo"    ...    0.0.0.0:27017->27017/tcp   suspicious_goodall

使用mongo镜像执行mongo 命令连接到刚启动的容器,主机IP为172.17.0.1

runoob@runoob:~/mongo$ docker run -it mongo:3.2 mongo --host 172.17.0.1
MongoDB shell version: 3.2.7
connecting to: 172.17.0.1:27017/test
Welcome to the MongoDB shell.
For interactive help, type "help".
For more comprehensive documentation, see
  http://docs.mongodb.org/
Questions? Try the support group
  http://groups.google.com/group/mongodb-user
>

CentOS 6.5使用yum安装MongoDB数据库

mongodb是一个基于分布式文件存储的数据库。由 C++ 语言编写。旨在为 WEB 应用提供可扩展的高性能数据存储解决方案。
MongoDB 是一个介于关系数据库和非关系数据库之间的产品,是非关系数据库当中功能最丰富,最像关系数据库的。

安装MongoDB

1、创建repo

vi /etc/yum.repos.d/mongodb-org-3.2.repo
[mongodb-org-3.2]  
name=MongoDB Repository  
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/3.2/x86_64/  
gpgcheck=0  
enabled=1  

2、安装MongoDB和相关工具

sudo yum install -y mongodb-org

3、启动MongoDB

4、验证MongoDB是否启动成功

cat /var/log/mongodb/mongod.log

查看是否有一句:[initandlisten] waiting for connections on port
其中是在/etc/mongod.conf中配置的,默认情况下是27017端口。

还有另一种方式:

# sudo service mongod status  
Redirecting to /bin/systemctl status  mongod.service  
● mongod.service - High-performance, schema-free document-oriented database  
   Loaded: loaded (/usr/lib/systemd/system/mongod.service; disabled; vendor preset: disabled)  
   Active: active (running) since 一 2016-09-12 09:50:16 CST; 15s ago  
  Process: 8787 ExecStart=/usr/bin/mongod $OPTIONS run (code=exited, status=0/SUCCESS)  
 Main PID: 8842 (mongod)  
   CGroup: /system.slice/mongod.service  
           └─8842 /usr/bin/mongod --quiet -f /etc/mongod.conf run  

9月 12 09:50:13 192.168.1.155 systemd[1]: Starting High-performance, schema-free document-oriented database...  
9月 12 09:50:14 192.168.1.155 mongod[8787]: about to fork child process, waiting until server is ready for connections.  
9月 12 09:50:14 192.168.1.155 mongod[8787]: forked process: 8842  
9月 12 09:50:16 192.168.1.155 mongod[8787]: child process started successfully, parent exiting  
9月 12 09:50:16 192.168.1.155 systemd[1]: Started High-performance, schema-free document-oriented database. 

会看到“Active: active (running)”,说明正在运行。

没有运行的情况:

# sudo service mongod status  
Redirecting to /bin/systemctl status  mongod.service  
● mongod.service - High-performance, schema-free document-oriented database  
   Loaded: loaded (/usr/lib/systemd/system/mongod.service; disabled; vendor preset: disabled)  
   Active: inactive (dead)  

Active: inactive (dead)

5、使MongoDB开机自动启动

sudo chkconfig mongod on

6、停止MongoDB

sudo service mongod stop

7、重启MongoDB

sudo service mongod restart

进入Mongo命令行

# mongo  
MongoDB shell version: 2.6.12  
connecting to: test  
Welcome to the MongoDB shell.  
For interactive help, type "help".  
For more comprehensive documentation, see  
    http://docs.mongodb.org/  
Questions? Try the support group  
    http://groups.google.com/group/mongodb-user  
Server has startup warnings:   
2016-09-12T09:50:14.195+0800 [initandlisten]   
2016-09-12T09:50:14.195+0800 [initandlisten] ** WARNING: Readahead for /var/lib/mongodb is set to 4096KB  
2016-09-12T09:50:14.195+0800 [initandlisten] **          We suggest setting it to 256KB (512 sectors) or less  
2016-09-12T09:50:14.195+0800 [initandlisten] **          http://dochub.mongodb.org/core/readahead  
>   

Linux下安装PHP的MongoDB扩展

因为是手动安装的MongoDB,所以也需要编译安装MongoDB扩展,步骤如下:

1、下载最新的PHP mongodb扩展源码,源码可以在http://pecl.php.net/package/mongo下载.

2、解压,进入安装目录

wget http://pecl.php.net/get/mongo-1.4.0.tgz 
tar -zxvf mongo-1.4.0.tgz
cd mongo-1.4.0   

3、进入文件夹后,首先运行phpize来编译扩展的环境

/usr/bin/phpize
PHP Api Version: 20121113
Zend Module Api No: 20121212
Zend Extension Api No: 220121212

4、运行后,我们运行./configure脚本来进行配置

./configure --with-php-config=/usr/local/php/bin/php-config && make && make install

–with-php-config 这个参数是告诉配置脚本 php-config 这个程序的路径

5、完成后,请编辑你php.ini文件增加一行extension=mongo.so`

一般默认的编译php的ini文件/usr/local/php/etc/php.ini重启Apache/Nginx[或者/etc/init.d/php-fpm restart] 打开 phpinfo看到mongo模块,证明mongodb的php扩展安装成功。

OK ,至此你可以使用php来操作 MongoDB 了。