mysql优化,导致查询不走索引的原因总结

最近公司让我做SQL优化的工作(MySql),用explain发了一些问题。常见的像OR ,IN,>= ,或者是嵌套等导致索引失效,导致查询性能降低的问题在这里就不做陈述了,网上的文章一搜一 大片。我只是写点个人工作中遇到的,网上不好搜索的,但是不保证所有的场景都试用,后续我还会更新。 1、order by 和 limit 结合使用,如果where 字段,order by字段都是索引,那么有limit索引会使用order by字段所在的索引。没有limit会使用where 条件的索引。遇到此类状况可以考虑用子查询将order by 和 limit 分开。这种情况主要发生在你用了多个索引,那么你需要注意了。它可能不执行你希望的走索引。(我觉得mysql会自动计算索引) 2、DATE_FORMAT()格式化时间,格式化后的时间再去比较,可能会导致索引失效。 3、子查询中order by的索引会失效,同时可能导致子查询中的where条件索引都不能用。 4、字符集的使用导致不走索引,有时你会发现用一个SQL 条件值不同可能会有天的差别(我之前遇到的 两个不同的ID号,一个查询80s,一个不到1s) 5、like语句 6、列类型为字符串类型,查询时没有用单引号引起来 7、在where查询语句中使用表达式 8、在where查询语句中对字段进行NULL值判断 9、在where查询中使用了or关键字, myisam表能用到索引, innodb不行;(用UNION替换OR,可以使用索引) 10、全表扫描快于索引扫描(数据量小时) 先说这几条.如果查看执行计划不理想的话,我建议在启动数据库时加上两个启动参数,会看的更清楚(每个表的执行次数和执行时间) --log-slow-queries     (查询日志) --log-queries-not-using-indexes   (查询未使用索引日志) 最后的优化方式就是测试,因为业务的不同优化理论不可能总是可以带来很高的效率,利用explain或desc查看,然后再真的某个查询或表做改进吧。

一个月的艰辛稳定服务过程

业务大了成本问题更加的凸显,最重要的之一就是带宽,为了更好的优化资源,我们决定将目前 BGP 全线的流量切换一部分到双线上去。说白了很简单,网络方面不需要动什么大手脚,全网 10G 的链路半年之前就全部升级完毕,剩下的就是加前端,10G 服务器,LVS(nat), Keepalived,Nginx。接下来就是小流量的上线测试,从 10M 到 50M 到 100M 再到 300M 前后大概有一周的观察时间,整体还是很稳定的,当然也遇到了一些小问题,比如 10G 网卡不规则的出现了一些 rx_crc_errors/rx_missed_errors,通过优化一些硬件设备基本得到了解决,虽然后期还会时不时的冒出一些,但是从各个服务以及业务的监控方面没有看到潜在的影响,暂时略过。 正所谓稳定压倒一切,从开始小流量切换到正式全量上线的这一个月,为了稳定,充满了坎坷,下面是这一段时间遇到的一些比较有挑战的事件, 「很不幸的是」,全部是人为原因。 4xx 错误飙升 一周之后的某个早上收到报警,4xx, 5xx 的错误上升了大概一倍的样子: 可以确认的是这段时间没有任何的变更,从到后端的请求来看,这段时间的异常并没有对其有很大的影响,但是本着负责任的态度还是把当时的 log 归档拿出来分析了下详情: 可以看到,这段时间的 return code 主要是由 499 以及 500 错误引起,并且 499 的错误占了大部分的,那就从 499 的分析开始。G 了一番,发现这个 code 并非是一个标准的 RFC,唯一可以确认的就是是客户端主动的关闭了链接,至于是什么原因让客户端主动关闭的,原因就太多了。除此之外,还有一部分的稳定的 408 错误,这个比较好理解,对于由于 Nginx 读取客户端发来的请求会造成超时而返回 408。 看了上面的解释,基本得不到很明确的答案。接下来,至少还可以从 debug log 以及 error log 入手,看看能否得到一些信息。 … Continue reading 一个月的艰辛稳定服务过程

nginx 负载均衡集群解决方案 healthcheck_nginx_upstreams (一)

该文章来源于互联网,目前找不到原作者,放在这里的目的是记录healthcheck_nginx_upstreams 的安装过程和相关配置,在起初安装成功后不能够正常运行healthcheck_nginx_upstreams,后通过阅读源码和调试,能够正常运行。 不过信息如下: [html] view plain copy *26 no live upstreams while connecting to upstream   Nginx是一个免费的,开源的,高性能的服务器和反向代理服务器软件,同时它也可以为IMAP和POP3服务器代理,以其高性能,稳定性,丰富的功能,结构简单,低资源消耗的特性换来广大运维者所喜爱。 Nginx与传统的服务器不同,不依赖线程来处理请求。相反,它使用一个更可扩展事件驱动架构(异步)。这种结构资源消耗较小,但更重要的是,可以承受较大的请求负荷。即使你不希望处理成千上万的请求,你仍然可以受益于Nginx的高性能和小的内存占用,以及其丰富的功能。 Nginx的反向代理: 反向代理指以代理服务器来接受Internet上的连接请求,然后将请求转发给内部网络上的服务器,并将从服务器上得到的结果返回给Internet上请求连接到客户端,此时代理服务器对外就表现为一个服务器,而此种工作模式类似于LVS-NET模型。 反向代理也可以理解为web服务器加速,它是一种通过在繁忙的web服务器和外部网络之间增加的 一个高速web缓冲服务器,用来降低实际的web服务器的负载的一种技术。反向代理是针对web服务器提高加速功能,所有外部网络要访问服务器时的所有请求都要通过它,这样反向代理服务器负责接收客户端的请求,然后到源服务器上获取内容,把内容返回给用户,并把内容保存在本地,以便日后再收到同样的信息请求时,它会将本地缓存里的内容直接发给用户,已减少后端web服务器的压力,提高响应速度。因此Nginx还具有缓存功能。 反向代理的工作流程: 1)用户通过域名发出访问请求,该域名被解析为反向代理服务器的IP地址; 2)反向代理服务器接收用户的请求; 3)反向代理服务器在本地缓存查找是否存在当前用户所请求的内容,找到则直接把内容返回给用户; 4)如果本地没有用户请求的内容,反向代理服务器会以自己的身份去后端服务器请求同样的信息内容,并把信息内容发给用户,如果信息内容是可以被缓存的,则会将该内容缓存在代理服务器的本地缓存中。 反向代理的好处: 1)解决了网站服务器对外可见的问题,提高了网站服务器的安全性; 2)节约了有限的IP地址资源,后端服务器均可使用私有IP地址与代理服务器进行通信; 3)加速了网站的访问速度,减轻了真是web服务器的负荷。 (一)、调度算法 Nginx的upstream指令用于指定proxy_pass和fastcgi_pass所使用的后端服务器,即nginx的反向代理功能,因此可以将两者结合起来使用以达到负载均衡的目的,而Nginx也支持多种调度算法: 1、轮询(默认) 每个请求按时间顺序逐一分配到不同的后端服务器,如果后端服务器down掉,则会跳过该服务器分配至下一个监控的服务器。并且它无需记录当前所有连接的状态,所以它是一种无状态调度。 2、weight 指定在轮询的基础上加上权重,weight和访问比率成正比,即用于表明后端服务器的性能好坏,若后端服务器性能较好则可将大部分请求分配给它,已实现其力所能及。 例如: 我后端服务器172.23.136.148配置:E5520*2 CPU,8G内存 后端服务器172.23.136.148配置:Xeon(TM)2.80GHz * 2,4G内存 我希望在有30个请求到达前端时,其中20个请求交给172.23.136.148处理,剩余10个请求交给172.23.136.149处理,就可做如下配置 upstream web_poll { server 172.23.136.148 weight=10; server 172.23.136.149 weight=5; } 3、ip_hash 每个请求按访问ip的hash结果分配,当新的请求到达时,先将其客户端IP通过哈希算法进行哈希出一个值,在随后的请求客户端IP的哈希值只要相同,就会被分配至同一个后端服务器,该调度算法可以解决session的问题,但有时会导致分配不均即无法保证负载均衡。 例如: upstream web_pool { ip_hash; server 172.23.136.148:80; server 172.23.136.149:80; … Continue reading nginx 负载均衡集群解决方案 healthcheck_nginx_upstreams (一)

记一次压测引起的nginx负载均衡性能调优

这边有个性能要求极高的api要上线,这个服务端是golang http模块实现的。在上线之前我们理所当然的要做压力测试。起初是 “小白同学” 起头进行压力测试,但当我看到那压力测试的结果时,我也是逗乐了。   现象是,直接访问Golang http api是每秒可以到3.5W的访问,  为了理论承受更强的QPS,多开了几个go http api进程端口,又在这前面加了层nginx负载均衡,结果往nginx压测的结果是每秒才可以解决1.5w的访问量。 这结果让高级黑 “小白” 把nginx又给鄙视了。 该文章写的有些乱,欢迎来喷 ! 另外文章后续不断更新中,请到原文地址查看更新. http://xiaorui.cc/?p=3495   虽然哥平时开发任务很饱和,又因为带几个新人的原因,有点心累。 但哥还是抽出宝贵的时间来解决nginx在压力测试下性能上不去的问题。 哈哈,这里肯定有人要打我了。  说实话,做运维虽然能时常碰一些负载均衡调度器,但由于很多时候配置都标准化了,新开一个业务线,把配置一scp,然后选择性的修改域名及location就可以了,还真是没遇到过这次的问题。 我们在寻找性能瓶颈的是时候,会频繁的使用后面的工具进行监控,推荐大家使用tmux或者screen开启多个终端监控,用top可以看到nginx及go api的cpu占用率,load值,run数,各个cpu核心的百分比,处理网络的中断。用dstat可以看到流量及上下文切换的测试。  ss + netstat 查看连接数。 首先是压力测试的方法问题 以前做运维的时候,我们一般不会用简单的ab来进行压测,这样会造成压力源过热的情况,正常的针对服务端测试的方法是,分布式压力测试,一个主机压测的结果很是不准,当然前提是 服务端的性能够高,别尼玛整个python django就用分布式压测,随便找个webbench,ab , boom这类的http压测就可以了。 关于客户端压测过热的情况有几个元素,最主要的元素是端口占用情况。首先我们需要明确几个点, 作为服务端只是消耗fd而已,但是客户端是需要占用端口来发起请求。 如果你自己同时作为服务端和客户端,会被受限于65535-1024的限制,1024内一般是常规的系统保留端口。   如果你按照65535-1024计算的话,你可以占用64511端口数,但如果你是自己压力测试nginx,然后nginx又反向代理几个golang http api。  那么这端口被严重的缩水了。   当你压测的数目才6w以上,很明显报错,不想报错,那么只能进行排队阻塞,好让客户端完成该请求。 另外一点是nginx 配置问题。 这一点很重要,也是最基本的要求,如果nginx worker连接数过少的化,你的请求连接就算没有被阻塞到backlog队列外,nginx worker也会因为过载保护不会处理新的请求。nginx的最大连接数是worker num *worker_connections, 默认worker_connections是1024, 直接干到10w就可以了。 在我们配置调整之后,访问的速度有明显的提升,但还是没有达到我们的预期。 接着通过lsof追了下进程,发现nginx 跟 … Continue reading 记一次压测引起的nginx负载均衡性能调优

线上nginx的一次“no live upstreams while connecting to upstream ”分析

先描述一下环境,前段的负载均衡转发给nginx,nginx再转发给后端的应用服务器。 nginx配置文件如下: upstream ads {         server ap1:8888 max_fails=1 fail_timeout=60s;         server ap2:8888 max_fails=1 fail_timeout=60s; } 出现的现象是: 日志里面每隔一两分钟就会记录一条类似 *379803415 no live upstreams while connecting to upstream  的日志, 此外,还有大量的“upstream prematurely closed connection while reading response header from upstream”的日志。 我们先看“no live upstreams”的问题。 看字面意思是nginx发现没有存活的后端了,但是很奇怪的事情是,这段时间一直访问都正常,并且用wireshark看到的也是有进来的,也有返回的。 现在只能从nginx源码的角度来看了。 因为是upstream有关的报错,所以在ngx_http_upstream.c中查找“no live upstreams”的关键字,可以找到如下代码(其实,你会发现,如果在nginx全局代码中找的话,也只有这个文件里面有这个关键字):  在这里可以看出,当rc等于NGX_BUSY的时候,就会记录“no live upstreams”的错误。 往上看1328行,可以发现rc的值又是ngx_event_connect_peer这个函数返回的。 … Continue reading 线上nginx的一次“no live upstreams while connecting to upstream ”分析

MySQL错误: could not retrieve transation read-only status server

问题描述: java代码在开始事务后,先做了一个查询,再insert,此时会报:         java.sql.SQLException: could not retrieve transation read-only status server 解决过程: 查看mysql的事物隔离级别 SHOW VARIABLES LIKE '%iso%'; 返回结果: REPEATABLE-READ 把这个改成:READ-COMMITTED 就好了: SET GLOBAL tx_isolation='READ-COMMITTED'; (记得java重启应用,要永久生效的就改my.ini配置文件) 问题分析: 当数据库隔离级别为REPEATABLE-READ时,查询一个select语句也算是事物的开始,而且在hibernate里会把以select语句开头的事务标记为只读事务,此时在这个事务里再执行insert、update、delete等DML语句就会报错。 http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tx_read_only

rsyslog 日志服务器接收日志权限问题

rsyslog 权限; 默认 [root@dr-mysql01 zjzc_log]# ls -ltr zj-frontend0*-access*27 -rw------- 1 root root 322 Sep 27 07:55 zj-frontend02-access.2016-09-27 600 $FileOwner elk $FileGroup elk $FileCreateMode 0755 $DirCreateMode 0755 $Umask 0022 修改后: [root@dr-mysql01 zjzc_log]# ls -ltr zj-frontend0*-access*27 -rwxr-xr-x 1 elk elk 10558 Sep 27 07:58 zj-frontend02-access.2016-09-27 http://blog.csdn.net/yurunsheng/article/details/8135629

Linux 之 rsyslog 系统日志转发

一、rsyslog 介绍 ryslog 是一个快速处理收集系统日志的程序,提供了高性能、安全功能和模块化设计。rsyslog 是syslog 的升级版,它将多种来源输入输出转换结果到目的地,据官网介绍,现在可以处理100万条信息。 特性:1.多线程 2.支持加密协议:ssl,tls,relp 3.mysql、oracle、postgreSQL 4.等等..   二、实践部署() 2.1 环境图 2.2.rsyslog server上部署操作 安装rsyslog 程序(rsyslog默认已经在各发行版安装,如果系统中没有的话,可以用yum 进行安装,如下:) [root@opm ~]# yum install rsyslog -y 编辑rsyslog配置文件,路径 /etc/rsyslog.conf,修改前最好先备份一份,修改后的文件内容如下 [root@opm log]# grep -v "^#" /etc/rsyslog.conf | grep -v "^$" $ModLoad imuxsock # provides support for local system logging (e.g. via logger command) $ModLoad imjournal # provides access … Continue reading Linux 之 rsyslog 系统日志转发

使用 Artifactory 搭建 Maven 私服

❤️ 使用 Docker 部署 Artifactory 1、获取 artifactory-oss 镜像 $ docker pull docker.bintray.io/jfrog/artifactory-oss 2、创建数据卷 例如在 ~/docker/volume/artifactory 路径下执行 $ docker volume create data_artifactory 3、启动容器 $ docker run --name any-artifactory -d \ -v data_artifactory:/var/opt/jfrog/artifactory \ -p 8081:8081 docker.bintray.io/jfrog/artifactory-pro 部署 artifactory ❤️ Maven 私服配置 1、访问 http://localhost:8081/ 进入 Artifactory 首页后,配置好密码,并选择 Maven 插件。 后台首页 2、获取加密密码并保存,供后续流程使用。 获取加密密码 1 获取加密密码 2 ❤️ 本机 … Continue reading 使用 Artifactory 搭建 Maven 私服

Maven 安装和配置

Maven 安装 Maven 安装 官网:http://maven.apache.org/ 官网下载:http://maven.apache.org/download.cgi 历史版本下载:https://archive.apache.org/dist/maven/binaries/ 此时(20160502) Maven 最新版本为:3.3.9 Maven 3.3 的 JDK 最低要求是 JDK 7 我个人习惯 /opt 目录下创建一个目录 setups 用来存放各种软件安装包;在 /usr 目录下创建一个 program 用来存放各种解压后的软件包,下面的讲解也都是基于此习惯 我个人已经使用了第三方源:EPEL、RepoForge,如果你出现 yum install XXXXX 安装不成功的话,很有可能就是你没有相关源,请查看我对源设置的文章 下载压缩包:wget http://mirrors.cnnic.cn/apache/maven/maven-3/3.3.9/binaries/apache-maven-3.3.9-bin.tar.gz 解压:tar zxvf apache-maven-3.3.9-bin.tar.gz 修改目录名,默认的太长了:mv apache-maven-3.3.9/ maven3.3.9/ 移到我个人习惯的安装目录下:mv maven3.3.9/ /usr/program 环境变量设置:vim /etc/profile 在文件最尾巴添加下面内容: # Maven MAVEN_HOME=/usr/program/maven3.3.9 PATH=$PATH:$MAVEN_HOME/bin MAVEN_OPTS="-Xms256m -Xmx356m" export MAVEN_HOME export PATH … Continue reading Maven 安装和配置