PHP重点知识复习 – 高并发之浏览器缓存和数据压缩

一、HTTP缓存机制

启用浏览器缓存

1.缓存分类

HTTP缓存模型中,如果请求成功会有三种情况

(1)200 from cache:直接从本地缓存中获取响应,最快速,最省流量,因为根本没有向服务器发送请求

(2)304 Not Modified:协商缓存,浏览器在本地没有命中的情况下请求头中发送一定的校验数据到服务端,如果服务端数据没有改变浏览器从本地缓存响应,返回304

快速,发送的数据很少,只返回一些基本的响应头信息,数据量很少,不发送实际响应体

(3)200 OK:以上两种缓存全部失败,服务器返回完整响应。没有用到缓存,相对最慢。

2.本地缓存

浏览器认为本地缓存可以使用,不会去请求服务端

相关Header

Pragma:HTTP1.0时代的遗留产物,该字段被设置为 no-cache 时,会告知浏览器禁用本地缓存,即每次都向服务器发送请求。

Expires:HTTP1.0时代用来启用本地缓存的字段,expires 值对应一个形如 Fri, 05 Jan 2018 06:10:41 GMT 的格林威治时间,告诉浏览器缓存实现的时刻,如果还没到该时刻,表明缓存有效,无需发送请求。

浏览器与服务器的时间无法保持一致,如果时间差距大,就会影响缓存结果。

Cache-Control:HTTP1.1针对Expires时间不一致的解决方案,运用Cache-Control告知浏览器缓存过期的时间间隔而不是时刻,即使具体时间不一致,也不影响缓存的管理。

no-store:禁止浏览器缓存响应

no-cache:不允许直接使用本地缓存,先发起请求和服务器协商

max-age=delta-seconds:告知浏览器该响应本地缓存有效的最长期间,以秒为单位

优先级 Pragma > Cache-Control > Expires

3.协商缓存

当浏览器没有命中本地缓存,如本地缓存过期或者响应中声明不允许直接使用本地缓存,那么浏览器肯定会发起服务端请求。

服务端会验证数据是否修改,如果没有通过浏览器使用本地缓存。

相关Header

Last-Modified:通知浏览器资源的最后修改时间,如 Last-Modified: Sat, 09 Dec 2017 02:48:31 GMT

If-Modified-Since:得到资源的最后修改时间后,会将这个信息通过If-Modifed-Since提交到服务器做检查,如果没有修改,返回304状态码

ETag:HTTP1.1推出,文件的指纹标识符,如果文件内容修改,指纹会改变

If-None-Match:本地缓存失效,会携带此值去请求服务端,服务端判断该资源是否改变,如果没有改变,直接使用本地缓存,返回304

二、缓存策略的选择

1.适合缓存的内容

不变的图像,如logo、图标等;js、css静态文件;可下载的内容,媒体文件。

2.建议使用协商缓存

HTML文件;经常替换的图片;经常修改的js、css文件。

js、css文件的加载可以加入文件的签名来拒绝缓存,如 index.css?签名  或者 index.签名.js

3.不建议缓存的内容

用户隐私等敏感数据

经常改变的api的数据接口

三、Nginx配置缓存策略

1.本地缓存配置

add_header指令:添加状态码为2XX和3XX的响应头信息

语法格式:add_header name value [always];

可以设置 Pragma/Expires/Cache-Control,可以继承

expries指令:通知浏览器过期时长

语法格式:expires time;

为负值时表示 Cache-Control: no-cache;

当为正或者0时,就表示Cache-Control: max-age=指定的时间;

在nginx的配置中可以添加如下代码,分别设置图片缓存 30天和js css缓存12小时。

location ~ .*\.(gif|jpg|jpeg|png|bmp|swf|PNG|JPG|JPEG|GIF)$
{
 expires 30d;
}

location ~ .*\.(js|css)?$
{
  expires  12h;
}

当expires设置为max时,会把Expires设置为 “Thu, 31 Dec 2037 23:55:55 GMT”,Cache-Control设置到 10 年;

2.协商缓存相关配置

Etag指令:指定签名

语法:etag on | off; 默认是on

如下nginx中设置etag关闭

location ~ .*\.(gif|jpg|jpeg|png|bmp|swf|PNG|JPG|JPEG|GIF)$
{
 etag off;
}

四、前端代码和资源的压缩

优势:让资源文件更小,加快文件在网络中的传输,让网页更快的展现,降低带宽和流量开销

压缩方式:JS、CSS、图片、HTML代码的压缩;Gzip压缩

1.JavaScript代码压缩

js压缩的原理一般是去掉多余的空格和回车、替换长变量名、简化一些代码写法等。

常用压缩工具:UglifyJS、YUI Compressor、Closure、Compiler

2.CSS代码压缩

原理跟js压缩原理类似,同样是去除空白符、注释并且优化一些CSS语义规则等

常用压缩工具:YUI Compressor、CSS Compressor

3.HTML代码压缩

不建议使用代码压缩,有时会破坏代码结构,可以使用Gzip压缩。

4.图片压缩

除了代码的压缩外,有时对图片的压缩也是很必要的,一般情况下图片在web系统的比重都比较大。

压缩工具:tinypng、JpegMini、ImageOptiom

5.Gzip压缩

配置Nginx

gzip on | off;  #是否开启gzip

gzip_buffers 32 4k | 16 8k  #缓冲(在内存中缓冲几块?每块多大)

gzip_comp_level [1-9]  #推荐6 压缩级别(级别越高,压缩的越小,越浪费CPU计算资源)

gzip_disable  #正则匹配UA 什么样的Uri不进行gzip

gzip_min_length 200  #开始压缩的最小长度

gzip_http_version 1.0 | 1.1  #开始压缩的http协议版本

gzip_proxied   #设置请求者代理服务器,该如何缓存内容

gzip_types text/plain application/xml  #对哪些类型的文件用压缩 比如txt,xml,html,css

gzip_vary on | off  #是否传输gzip压缩标志

以下是Nginx配置gzip代码

gzip on;
gzip_min_length 1k;
gzip_buffers 4 16k;
gzip_http_version 1.1;
gzip_comp_level 2;
gzip_types text/plain application/x-javascript text/css application/xml text/javascript application/x-httpd-php image/jpeg image/gif image/png;
gzip_vary on;
gzip_proxied expired no-cache no-store private auth;
gzip_disable "MSIE [1-6]\.";

 

PHP重点知识复习 – 高并发之减少HTTP请求次数

一、为什么要减少HTTP请求

性能黄金法则

只有10%-20%的最终用户响应时间花在接收请求的HTML文档上,剩下的80%-90%时间花在HTML文档所引用的所有组件(图片、js、css、flash等等)进行的HTTP请求上。

如何改善

改善响应时间的最简单途径就是减少组件的数量,并由此减少HTTP请求的数量。

HTTP连接产生的开销

域名解析–TCP连接–发送请求–等待–下载资源–解析时间

HTTP1.1协议规定请求只能串行发送,也就是说一百个请求必须依次逐个发送,前面的一个请求完成才能开始下一个请求。

二、减少HTTP请求的方式

1.图片地图

图片地图允许你在一个图片上关联多个URL。目标URL的选择取决于用户单击了图片上的哪个位置。

我们可以通过使用五个分开的图片,然后每个图片对应一个超链接产生了5个HTTP请求,我们的目标是要减少HTTP请求

将五个图片合并成一张图片,然后以位置信息定位超链接。

把HTTP请求减少为一个,可以保证设计的完整性和功能的齐全性。

使用HTML的<map>标签

<map><area></area></map>

2.CSS Sprites

CSS Sprites中文翻译为CSS精灵,通过使用合并图片,通过指定css的backgroud-image和backgroud-position来显示元素。

backgroud-position:x y; 这里x和y可以写负值也可以写正值,我们可以想象图片的左上方为(0,0),以(0,0)坐标向右是为负数的x轴,以(0,0)坐标向下是为负数的y轴。

3.性能影响

图片地图与CSS精力的响应时间基本上相同,但比使用各自独立图片的方式要快50%以上。

4.合并js脚本和css样式表

使用外部的js和css文件引用的方式,因为这要比直接写在页面中性能要更好一点。

独立的一个js比用多个js文件组成的页面载入要快38%

把多个脚本合并为一个脚本,把多个样式表合并为一个样式表。

5.图片使用Base64编码减少页面请求数

采用Base64的编码方式将图片直接嵌入到网页中,而不是从外部载入

<img src=”data:image/gif;base64,/9j4AAQSKZJ……”>

PHP可以实现base64转码。

 

PHP重点知识复习 – 高并发之防盗链

一、什么是防盗链

1.盗链概念

盗链是指在自己的页面上展示一些并不在自己服务器的内容

获取他人服务器上的资源地址,绕过别人的资源展示页面,直接在自己的页面上向最终用户提供此内容。

常见的是小站盗用大站的图片、音乐、视频、软件等资源

通过盗链的方法可以减轻自己服务器的负担,因为真实的空间和流量均是来自别人的服务器

2.防盗链概念

防止别人通过一些技术手段绕过本站的资源展示页面,盗用本站的资源,让绕开本站资源展示页面的资源链接失效。

可以大大减轻服务器及带宽的压力

二、防盗链的工作原理

工作原理

通过Referer或签名,网站可以检测目标网页访问的来源网页,如果是资源文件,则可以跟踪到显示它的网页地址。

一旦检测到来源不是本站即进行阻止或者返回指定的页面

通过计算签名的方式,判断请求是否合法,如果合法则显示,否则返回错误信息。

Referer

Nginx 模块 ngx_http_referer_module 用于阻拦来源非法的域名请求

Nginx指令 valid_referers,全局变量$invalid_referer

valid_referers none | blocked | server_names | string …;

none: “Referer”来源头部为空的情况

blocked: “Referer”来源头部不为空,但是里面的值被代理或者防火墙删除了,这些值都不以http://或者https//开头

server_names: “Referer”来源头部包含当前的server_names

以下是nginx的配置实例

location ~ .*\.(gif|jpg|jpeg|png|bmp|swf|PNG|JPG|JPEG|GIF)$
{
  valid_referers none blocked yanxisheji.com *.yanxisheji.com;
  if ($invalid_referer)
  {
   return 403;
  }
}

伪造Referer

可以使用加密签名解决

 

 

PHP重点知识复习 – 高并发和大流量解决方案概述

一、高并发框架相关概念

1.高并发是什么?

高并发、并发,通常是指并发访问。也就是在某个时间点,有多少个访问同时到来。

通常如果一个系统的日PV在千万以上,有可能是一个高并发的系统。

2.高并发的问题,我们具体该关心什么?

QPS:每秒请求或者查询的数量,在互联网领域,指每秒响应请求数(指HTTP请求);

吞吐量:单位时间内处理的请求数量(通常由QPS与并发数决定)

响应时间:从请求发出到收到响应花费的时间。例如系统处理一个HTTP请求需要100ms,这个100ms就是系统的响应时间

PV:综合浏览量(Page View),即页面浏览量或者点击量,一个访客在24小时内访问的页面数量。同一个人浏览网站同一个页面,只记住一次PV。

UV:独立访客(Unique Visitor),即一定时间范围内相同访客多次访问网站,只计算为1个独立访客

带宽:计算带宽大小需关注两个指标,峰值流量和页面的平均大小

日网站带宽 = PV/统计时间(换算到秒) * 平均页面大小(单位KB) * 8

峰值一般是平均值的倍数,根据实际情况来定

QPS 不等于 并发连接数

QPS是每秒HTTP请求数量,并发连接数是系统同时处理的请求数量。

一个并发连接数,可能包含多个HTTP请求。比如一个网页打开时,会同时加载内容、图片、js等等资源,每个加载项都是一次HTTP请求。

(总PV数 * 80%) / (6小时秒数 * 20%) = 峰值每秒请求数(QPS)

80%的访问量集中在 20%的时间

3.压力测试

(1)压力测试

测试能承受的最大并发,测试最大承受的QPS值

常用性能测试工具

ab、wrk、http_load、Web Bench、Siege、Apache JMeter

ab 全称是apache benchmark,是apache官方推出的工具

创建多个并发访问线程,模拟多个访问者同时对某一URL地址进行访问。它的测试目标是基于URL的,因此,它既可以用来测试apache的负载压力,也可以测试nginx、lighthttp、tomcat、IIS等其他Web服务器的压力。

ab的使用

模拟并发请求100次,总共请求5000次

ab -c 100 -n 5000 待测试网站

注意事项

测试机器与被测试机器分开

不要对线上服务做压力测试

观测测试工具ab所在机器,以及被测试的前端机的CPU、内存、网络等都不超过最高限度的75%

一般apache自带ab工具,如果没有,可以通过yum安装

yum -y install httpd-tools

(2)QPS达到极限

随着QPS的增长,每个阶段需要根据实际情况来进行优化,优化的方案也与硬件条件、网络带宽息息相关。

QPS达到50

可以称之为小型网站,一般的服务器就可以应付

QPS 达到 100 

假设关系型数据库的请求在0.01秒完成

假设单页面只有一个SQL查询,那么100QPS意味着1秒完成100次请求,但是此时我们并不能保证数据库查询都完成100次

方案:数据库缓存层、数据库的负载均衡

QPS 达到 800

假设我们使用百兆带宽,意味着网站出口的实际带宽是8M左右

假设每个页面10K,在这个并发条件下,百兆带宽已经吃完

方案:CDN加速、负载均衡

QPS 达到 1000

假设使用Memcache 缓存数据库查询数据,每个页面对 Memcache 的请求远大于直接对 DB 的请求。

Memcache 的悲观并发数在2w左右,但有可能在之前内网带宽已经吃光,表现出不稳定

方案:静态HTML缓存

QPS 达到 2000

这个级别下,文件系统访问锁都成为了灾难

方案:做业务分离,分布式存储

二、高并发解决方案案例

流量优化

防盗链处理

前端优化

减少HTTP请求

添加异步请求

启用浏览器缓存和文件压缩

CDN加速

建立独立图片服务器

服务端优化

页面静态化

并发处理

队列处理

数据库优化

数据库缓存

分库分表、分区操作

读写分离

负载均衡

Web服务器优化

负载均衡

PHP重点知识复习 – 常见算法(待完善)

一、算法的概念

基本概念

解决特定问题求解步骤的描述,在计算机中表现为指令的有限序列,并且每条指令表示一个或多个操作。

一个问题可以有很多中算法,每种算法都有不同的效率。

一个算法具有五个特征:有穷性、确切性、输入项、输出项、可行性

二、时间复杂度和空间复杂度的概念

算法评定

算法分析的目的在于选择合适算法和改进算法。

一个算法的评价主要从时间复杂度和空间复杂度来考虑。

1.时间复杂度

执行算法所需要的计算工作量。一般来说,计算机算法是问题规模 n 的函数 f(n),算法的时间复杂度也因此记做 T(n)=O(f(n))

问题的规模n越大,算法执行的时间的增长率与 f(n) 的增长率正相关,称作渐进时间复杂度(Asymptotic Time Complexity)

(1)时间复杂度计算方式

得出算法的计算次数公式

用常数1来取代所有时间中的所有加法常数

在修改后的运行次数函数中,只保留最高阶项

如果最高阶存在且不是1,则去除与这个项相乘的常数

(2)举例

常见时间复杂度:常数阶 O(1)、线性阶 O(n)、平方阶 O(n^2)、立方阶 O(n^3)、对数阶 O(log2n)、nlog2n阶、指数阶

复杂度大小:O(1) > O(log2n) > O(n) > O(nlog2n) > O(n^2) > O(n^3) > O(2^n) > O(n!) > O(n^n)

(3)时间复杂度其他概念

最坏情况:最坏情况时的运行时间,一种保证,如果没有特别说明,说的时间复杂度即为最坏情况的时间复杂度

平均情况:期望的运行时间

2.空间复杂度

算法需要消耗的内存空间,记住 S(n) = O(f(n))

包括程序代码所占用的空间,输入数据所占用的空间和辅助变量所占用的空间这三个方面

计算和表示方法和时间复杂度类似,一般用复杂度的渐近性来表示

空间复杂度计算方式

有时用空间换取时间

冒泡排序的元素交换,空间复杂度 O(1)

三、常见算法

1.排序算法

冒泡排序、直接插入排序、希尔排序、选择排序、快速排序、堆排序、归并排序

① 冒泡排序

原理:两两相邻的数进行比较,如果反序就交换,否则不交换

时间复杂度:最坏(O(N^2)),平均(O(n^2))

空间复杂度:O(1)

② 直接插入排序

原理:每次从无序表中取出第一个元素,把它插入到有序表的合适位置,使有序表仍然有序

时间复杂度:最坏(O(n^2)),平均(O(n^2))

空间复杂度:O(1)

③ 希尔排序

原理:将待排序的数据根据增量分成几个子序列,对子序列进行插入排序,直到增量为1,直接进行插入排序;增量的排序,一般是数组的长度的一半,再变成原来增量的一半,直到增量为1

时间复杂度:最差(O(n^2)),平均(O(n*log2n))

空间复杂度:O(1)

④ 选择排序

原理:每次从待排序的数据元素中选出最小(或最大)的一个元素,存放在序列的起始位置,直到全部待排序的数据元素排完

时间复杂度:最坏(O(n^2)),平均(O(n^2))

空间复杂度:O(1)

⑤ 快速排序

原理:通过一趟排序将要排序的数据分割成独立的两部分,其中一部分的所有数据都比另外一部分的所有数据都要小,然后再按照此方法对这两部分数据分别进行快速排序,整个排序过程可以递归完成

时间复杂度:最差(O(n^2)),平均(O(nlog2n))

空间复杂度:最差(O(n)),平均(O(log2n))

⑥ 堆排序

原理:把待排序的元素按照大小在二叉树位置上排序,排序好的元素要满足:父节点的元素要大于等于子节点;这个过程叫做堆化过程,如果根节点存放的是最大的数,则叫做大根堆,如果是最小,就叫小根堆,可以把根节点拿出来,然后再堆化,循环到最后一个节点。

时间复杂度:最差(O(nlog2n)),平均(O(nlog2n))

空间复杂度:O(1)

⑦ 归并排序

原理:将两个(或两个以上)有序表合并成一个新的有序表,即把待排序序列分为若干个有序的子序列,再把有序的子序列合并为整体有序序列

时间复杂度:最差(O(nlog2n)),平均(O(nlog2n))

空间复杂度:O(n)

总结:快速排序、归并排序的理想时间复杂度都是O(nlog2n),但是快速排序的时间复杂度并不稳定,最坏情况下复杂度为O(n^2),所以最理想的算法还是归并排序

2.查找算法

① 二分查找

原理:从数组的中间元素开始,如果中间元素正好是要查找的元素,搜索结束,如果某一个特定元素大于或者小于中间元素,则在数组大于或者小于中间元素的那一半中查找,而且跟开始一样从中间开始比较,如果某一步骤数组为空,代表找不到。

时间复杂度:最差(O(log2n)),平均(O(log2n))

空间复杂度:迭代(O(1))、递归(O(log2n))

② 顺序查找

原理:按一定的顺序检查数组中每一个元素,直到找到所要寻找的特定值为止。

时间复杂度:最差(O(n)),平均(O(n))

空间复杂度:O(1)

总结:二分查找算法的时间复杂度最差是O(log2n),所以二分查找法更快,但是递归情况下,二分查找要消耗内存,时间复杂度为 O(log2n)

解题方法

此类考点非常重要也较为复杂,需要考生充分理解各种排序算法和查找算法的原理以及实现方式,另外还需要理解时间复杂度和空间复杂度的计算方式和概念,此类靠差点是考察考生的逻辑思维能力,因此需要仔细研究各种算法的实现方式。

 

备注:需要对其他常见的算法进行练习

PHP重点知识复习 – MVC框架

一、MVC框架的基本原理:M(model 数据操作层)、C(controller 业务处理层)、V(view 视图层)

二、单一入口的工作原理

1.工作原理

用一个处理程序文件处理所有的HTTP请求,根据请求时的参数的不同区分不同模块和操作的请求

2.优势

可以进行统一的安全性检查

集中处理程序

3.劣势

URL不美观(URL重写)

处理效率会稍低

三、模板引擎的理解

常见模板引擎

PHP是一种HTML内嵌式的在服务端执行的脚本语言,但是PHP有很多可以使PHP代码和HTML代码分开的模板引擎,例如:Smarty、Twig、Haml、Liquid等

工作原理

模板引擎就是庞大的完善的正则表达式替换库

四、PHP框架的差异和优缺点

Yii2框架

优点:

Yii2是一款非常优秀的通用Web后端框架,结构简单优雅、实用功能丰富、扩展性强、性能高是它最突出的优点。

缺点:

学习成本较高,重量级框架

 

PHP重点知识复习 – MySQL的安全性

一、SQL查询的安全方案

1.使用预处理语句防SQL注入,一般使用PDO处理

2.写入数据库的数据要进行特殊字符的转义

3.查询错误信息不要返回给用户,将错误记录到日志

注意:PHP端尽量使用PDO对数据库进行相关操作,PDO拥有对预处理语句很好的支持的方法,MySQLi也有,但是可扩展性不如PDO,效率略高于PDO,MySQL函数在新版本中已经趋向于淘汰,所以不建议使用,而且它没有很好的支持预处理的方法。

二、MySQL的其他安全设置

1.定期做数据备份

2.不给查询用户root权限,合理分配权限

3.关闭远程访问数据库权限

4.修改root口令,不用默认口令,使用较复杂的口令

5.删除多余的用户

6.改变root用户的名称

7.限制一般用户浏览其他库

8.限制用户对数据库文件的访问权限

解题办法:

通常情况下,SQL安全的考点都在防SQL注入的问题,因此只要遇到此类考点,优先考虑SQL注入的防护手段。

 

PHP重点知识复习 – MySQL的高扩展和高可用(待完善)

一、分区表的原理

1.工作原理

创建表时使用 partition by 子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询只需要查询所需数据在的分区即可。

分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性删除整个分区的数据也很方便。

2.适用场景

(1)表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据

(2)分区表的数据更易维护,可以对独立的分区进行独立的操作

(3)分区表的数据可以分布在不同的机器上,从而高效使用资源

(4)可以使用分区表来避免某些特殊的瓶颈

(5)可以备份和恢复独立的分区

3.限制

(1)一个表最多只能有1024个分区

(2)5.1版本中,分区表表达式必须是整数,5.5可以使用列分区

(3)分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来

(4)分区表中无法使用外键约束

(5)需要对现有表的结构进行修改

(6)所有分区都必须使用相同的存储引擎

(7)分区函数中可以使用的函数和表达式会有一些限制

(8)某些存储引擎不支持分区

(9)对于MyISAM的分区表,不能使用 load index into cache

(10)对于MyISAM表,使用分区表时需要打开更多的文件描述符

二、分库分表

1.工作原理

通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分

2.适用场景

(1)单表记录条数达到百万到千万级别时

(2)解决表锁的问题

3.分表方式

水平分割

表很大,分割后可以降低在查询时需要读的数据和牵引的页数,同时也降低了索引的层数,提高查询速度

使用场景

(1)表中的数据本身就有独立性,例如表中分别记录各个地区的数据或者不同时期的数据,特别时有些数据常用,有些不常用

(2)需要把数据存放在多个介质上

水平分表缺点:

(1)给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需要UNION操作

(2)在许多数据库应用中,这种复杂性会超过它带来的有点,查询时会增加读一个索引层的磁盘次数

垂直分表

把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中

使用场景

(1)如果一个表中某些列常用,而另外一些列不常用

(2)可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数

垂直分表缺点

管理冗余列,查询所有数据需要JOIN操作

3.分表缺点

有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差

对于应用层来说,逻辑算法无疑增加开发成本

三、主从热备和负载均衡

 

PHP重点知识复习 – MySQL的查询优化(待完善)

一、查找分析查询速度慢的原因

1.分析SQL查询慢的方法

记录慢查询日志

分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和精力,可以使用pt-query-digest工具进行分析

2.使用 show profile

set profiling = 1; 开启服务器上执行的所有语句检测消耗的时间,存到临时表中

show profiles

show profile for query 临时表ID

3.使用 show status

show status 会返回一些计数器,show global status 查看服务器级别的所有计数

有时根据这些计数,可以猜测出哪些操作代价较高或者消耗时间多

4.使用 show processlist

观察是否右大量线程处于不正常的状态或特征

5.使用 explain 别名 desc

分析单条SQL语句

二、优化查询过程中的数据访问

1.访问数据太多导致查询性能下降

2.确定应用程序是否在检索大量超过需要的数据,可能是太多行或列

3.确认MySQL服务器是否在分析大量不必要的数据行

4.避免使用如下SQL语句

查询不需要的记录,使用 limit 解决

多表关联返回全部列,指定 A.id,A.name,B.age

总是取出全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化

重复查询相同的数据,可以缓存数据,下次直接读取缓存

5.是否在扫描额外的记录

使用 explain 来进行分析,如果发现查询需要扫描大量的数据但只返回少数的行,可以通过如下技巧去优化:

使用索引覆盖扫描,把所用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果

改变数据库和表的结构,修改数据表范式

重写SQL语句,让优化器可以以更优的方式执行查询

三、优化长难的查询语句

一个复杂查询还是多个简单查询更好?

MySQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多

使用尽可能少得查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的

1.切分查询

将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据要比一次删除1万,暂停一会的方案更加损耗服务器开销

2.分解关联查询

可以将一条关联语句分解成多条SQL来执行

让缓存的效率更高

执行单个查询可以减少锁的竞争

在应用层左关联可以更容易对数据库进行拆分

3.优化 conut() 查询

count(*)中的*会忽略所有的列,直接统计所有行数,因此不要使用count(列名)

4.优化关联查询

确定ON或者USING子句的列上有索引

确保 GROUP BY 和 ORDER BY 中只有一个表中的列,这样MySQL才有可能使用索引

5.优化子查询

尽可能使用关联查询来替代

6.优化 GROUP BY 和 DISTINCT

这两种查询均可使用索引来优化,是最有效的优化方法

关联查询中,使用标识列(主键列或auto_increment列)进行分组的效率会更高

如果不需要 ORDER BY,进行GROUP BY 时使用ORDER BY NULL,MySQL不会再进行文件排序

WITH ROLLUP超级聚合,尽量不使用,可以挪到应用程序处理

7.优化LIMIT 分页

LIMIT偏移量大的时候,查询效率较低

可以记录上次查询的最大ID,下次查询时直接根据该ID来查询

8.优化UNION查询

UNION ALL的效率高于UNION

解题方法

对于此类考题,先说明如何定位低效SQL语句,然后根据SQL语句可能低效的原因做排查,先从索引着手,如果索引没有问题,考虑以上几个方面,数据访问的问题,长难查询句的问题还是一些特定类型优化的问题,逐一回答。

 

PHP重点知识复习 – MySQL的SQL语句编写

一、MySQL的关联更新UPDATE语句

1.关联更新

真题测试:有A(id,sex,par,c1,c2),B(id,age,c1,c2)两张表,其中A.id与B.id关联,现在要求写出一条SQL语句,将B中age>50的记录的c1,c2更新到A表中统一记录中的c1,c2字段中

解答:

UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id AND B.age > 50;

UPDATE A INNER JOIN B ON A.id = B.id SET A.c1 = B.c1, A.c2 = B.c2 WHERE B.age > 50;

二、MySQL的关联查询语句

六种关联查询

交叉连接(CROSS JOIN),内连接(INNER JOIN),外连接(LEFT JOIN/RIGHT JOIN),联合查询(UNION与UNION ALL),全连接(FULL JOIN)

1.交叉连接

SELECT * FROM A,B(,C); 或者

SELECT * FROM A CROSS JOIN B (CROSS JOIN C);

没有任何关联条件,结果是笛卡尔积,结果集会很大,没有意义,很少使用

2.内连接

SELECT * FROM A,B WHERE A.id = B.id; 或者

SELECT * FROM A INNER JOIN B ON A.id = B.id;

多表中同时符合某种条件的数据记录的集合

内连接分为三类

等值连接:ON A.id = B.id

不等值连接:ON A.id > B.id

自连接:SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid;

3.外连接

左外连接:LEFT OUTER JOIN,以左表为主,先查询出左表,按照ON后的关联条件匹配右表,没有匹配到的用NULL填充,可以简写成 LEFT JOIN

右外连接:RIGHT OUTER JOIN,以右表为主,先查询出右表,按照ON后的关联条件匹配左表,没有匹配到的用NULL填充,可以简写成 RIGHT JOIN

4.联合查询

SELECT * FROM A UNION SELECT * FROM B UNION……

就是把多个结果集集中在一起,UNION前的结果为基准,需要注意的是联合查询的列数要相等,相同的记录行会合并

5.嵌套查询

用一条SQL语句的结果作为另外一条SQL语句的条件

SELECT * FROM A WHERE id IN (SELECT id FROM B);

解题方法

根据考题要搞清楚表的结构和多表之间的关系,根据想要的结果思考使用哪种关联方式,通常把要查询的列先写出来,然后分析这些列都属于哪些表,才考虑使用关联查询。

真题测试

为了记录足球比赛的结果,设计表如下

其中,match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联,查询2002-6-1到2006-7-1之间举行的所有比赛,并且用以下形式列出:拜仁 2:0 不莱梅 2006-6-21

解答:

SELECT t1.teamName, m.matchResult, t2.teamName, m.matchTime FROM match as m LEFT JOIN team as t1 ON m.hostTeamID = t1.teamID, LEFT JOIN team as t2  ON m.guestTeamID = t2.teamID  WHERE m.matchTime BETTWEN “2006-6-1” AND “2006-7-1”;

 

此部分内容参考教程 《SQL语句设计》