PHP cURL库函数抓取页面内容

cURL 是一个利用URL语法规定来传输文件和数据的工具,支持很多协议和选项,如HTTP、FTP、TELNET等,能提供 URL 请求相关的各种细节信息。最爽的是,PHP 也支持 cURL 库

本文将介绍 cURL 的一些高级特性,以及在 PHP 中如何运用它。

1 为什么要用cURL?

是的,我们可以通过其他办法获取网页内容。大多数时候,我因为想偷懒,都直接用简单的 PHP 的 file_get_contents() 函数:

$content = file_get_contents("http://www.awaimai.com");
$lines = file("http://www.awaimai.com");
readfile(http://www.awaimai.com);

不过,这种做法缺乏灵活性和有效的错误处理。而且,你也不能用它完成一些高难度任务,比如处理:coockies验证表单提交文件上传等等。

2 启用cURL

首先,我们得先要确定 PHP 是否开启了这个库,你可以通过使用phpinfo()函数来得到这一信息。如果在网页上看到下面的输出,那么表示 cURL 库已开启。

phpinfo_curl

如果 curl 没有开启,那么就需要开启这个库。如果是在Windows平台下,那么非常简单,你需要改一改 php.ini 文件的设置,找到 php_curl.dll,并取消前面的分号注释就行了。如下所示:

# 取消下面的注释
extension=php_curl.dll

如果是 Linux 服务器,需要重新编译 PHP ,编译时在configure命令上加上--with-curl参数。

3 基本结构

在学习更为复杂的功能之前,先来看一下在 PHP 中建立 cURL 请求的基本步骤:

  1. 初始化
  2. 设置选项
  3. 执行并获取结果
  4. 释放cURL句柄

实现代码如下:

// 1. 初始化
$ch = curl_init();

// 2. 设置选项
curl_setopt($ch, CURLOPT_URL, "http://www.awaimai.com");  // 设置要抓取的页面地址
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);              // 抓取结果直接返回(如果为0,则直接输出内容到页面)
curl_setopt($ch, CURLOPT_HEADER, 0);                      // 不需要页面的HTTP头

// 3. 执行并获取HTML文档内容,可用echo输出内容
$output = curl_exec($ch);

// 4. 释放curl句柄
curl_close($ch);

第二步(也就是 curl_setopt() )最为重要,一切玄妙均在此。有一长串cURL参数可供设置,它们能指定 URL 请求的各个细节。要一次性全部看完并理解可能比较困难,所以今天我们只试一下那些更常用也更有用的选项。

4 检查错误

你可以在 cur_exec() 后加一段检查错误的语句(虽然这并不是必需的):

$output = curl_exec($ch);
if ($output === FALSE) {
    echo "cURL Error: " . curl_error($ch);
}

请注意,比较的时候我们用的是=== FALSE,而非== FALSE。因为我们得区分空输出和布尔值FALSE,后者才是真正的错误。

5 获取信息

利用curl_getinfo()能够在 cURL 执行后获取请求的有关信息,当然,这也是一个可选的设置项,:

curl_exec($ch);
$info = curl_getinfo($ch);
echo '获取'. $info['url'] . '耗时'. $info['total_time'] . '秒';

返回的数组中包括了以下信息:

  • url        //资源网络地址
  • content_type  //内容编码
  • http_code    //HTTP状态码
  • header_size   //header的大小
  • request_size  //请求的大小
  • filetime     //文件创建时间
  • ssl_verify_result  //SSL验证结果
  • redirect_count   //跳转技术
  • total_time       //总耗时
  • namelookup_time   //DNS查询耗时
  • connect_time     //等待连接耗时
  • pretransfer_time  //传输前准备耗时
  • size_upload     //上传数据的大小
  • size_download    //下载数据的大小
  • speed_download   //下载速度
  • speed_upload     //上传速度
  • download_content_length  //下载内容的长度
  • upload_content_length   //上传内容的长度
  • starttransfer_time      //开始传输的时间
  • redirect_time         //重定向耗时

6 基于浏览器的重定向

在第一个例子中,我们将提供一段用于侦测服务器是否有基于浏览器的重定向的代码。例如,有些网站会根据是否是手机浏览器甚至用户来自哪个国家来重定向网页。

我们利用 CURLOPT_HTTPHEADER选项来设定发送出的HTTP请求头信息(http headers),包括user agent信息和默认语言。然后看看这些特定网站是否会把我们重定向到不同的URL。

// 测试用的URL
$urls = array(
	"http://www.bbc.com",
	"http://www.baidu.com",
	"http://www.ubuntu.com"
);
// 测试用的浏览器信息
$browsers = array(
	"standard" => array (
		"user_agent" => "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.6) Gecko/20091201 Firefox/3.5.6 (.NET CLR 3.5.30729)",
		"language" => "en-us,en;q=0.5"
		),

	"iphone" => array (
		"user_agent" => "Mozilla/5.0 (iPhone; U; CPU like Mac OS X; en) AppleWebKit/420+ (KHTML, like Gecko) Version/3.0 Mobile/1A537a Safari/419.3",
		"language" => "en"
		),

	"french" => array (
		"user_agent" => "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; GTB6; .NET CLR 2.0.50727)",
		"language" => "fr,fr-FR;q=0.5"
		)
);

foreach ($urls as $url) {
	echo "URL: $url\n<br />";
	foreach ($browsers as $test_name => $browser) {
		$ch = curl_init();

		// 设置 url
		curl_setopt($ch, CURLOPT_URL, $url);

		// 设置浏览器的特定header
		curl_setopt($ch, CURLOPT_HTTPHEADER, array(
				"User-Agent: {$browser['user_agent']}",
				"Accept-Language: {$browser['language']}"
			));

		// 页面内容我们并不需要
		curl_setopt($ch, CURLOPT_NOBODY, 1);

		// 只需返回HTTP header
		curl_setopt($ch, CURLOPT_HEADER, 1);

		// 返回结果,而不是输出它
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

		$output = curl_exec($ch);
		curl_close($ch);

		// 有重定向的HTTP头信息吗?
		if (preg_match("!Location: (.*)!", $output, $matches)) {
			echo "$test_name: redirects to $matches[1]\n<br />";
		} else {
			echo "$test_name: no redirection\n<br />";
		}
	}
	echo "\n\n<br /><br />";
}

首先,我们建立一组需要测试的URL,接着指定一组需要测试的浏览器信息。最后通过循环测试各种URL和浏览器匹配可能产生的情况。

因为我们指定了CURLOPT_NOBODY选项,所以返回的输出内容则只包括HTTP头信息(被存放于 $output 中)。利用一个简单的正则,我们检查这个头信息中是否包含了Location:字样。

运行这段代码应该会返回如下结果:

URL: http://www.bbc.com 
standard: no redirection 
iphone: no redirection 
french: no redirection 

URL: http://www.baidu.com 
standard: redirects to https://www.baidu.com/ 
iphone: no redirection 
french: redirects to https://www.baidu.com/ 

URL: http://www.ubuntu.com 
standard: redirects to http://www.ubuntu.com/index_kylin 
iphone: redirects to http://www.ubuntu.com/index_kylin 
french: redirects to http://www.ubuntu.com/index_kylin

7 用POST方法发送数据

当发起 GET 请求时,数据可以通过“查询字串”(Query String)传递给一个URL。例如,在必应(鉴于 Google 需要翻墙,用 Bing 代替)中搜索时,搜索关键即为 URL 的查询字串的一部分:

http://www.bing.com?q=awaimai.com

这种情况下你可能并不需要 cURL 来模拟。把这个URL丢给 file_get_contents() 就能得到相同结果。

不过有一些HTML表单是用 POST 方法提交的。这种表单提交时,数据是通过 HTTP请求体(request body) 发送,而不是查询字串。例如,当使用ThinkPHP网站的搜索功能时,无论输入什么关键字,总是被 POST 到如下页面:

http://www.thinkphp.cn/Search/

你可以用 PHP 脚本来模拟这种 URL 请求。首先,新建一个可以接受并显示 POST 数据的文件,我们给它命名为 post_output.php,脚本内容为:

print_r($_POST);

接下来,写一段 PHP 脚本来执行 cURL 请求:

$url = "http://localhost/post_output.php";

$post_data = array (
	"foo" => "bar",
	"query" => "Nettuts",
	"action" => "Submit"
);

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
// 我们在POST数据哦!
curl_setopt($ch, CURLOPT_POST, 1);
// 加上POST变量
curl_setopt($ch, CURLOPT_POSTFIELDS, $post_data);

$output = curl_exec($ch);
curl_close($ch);

echo $output;

执行代码后应该会得到以下结果:

Array
(
    [foo] => bar
    [query] => Nettuts
    [action] => Submit
)

这段脚本发送一个 POST 请求给 post_output.php ,这个页面 $_POST 变量并返回,我们利用 cURL 捕捉了这个输出。

8 文件上传

上传文件和前面的 POST 十分相似。因为所有的文件上传表单都是通过POST方法提交的。首先新建一个接收文件的页面,命名为 upload_output.php,页面内容:

print_r($_FILES);

以下是真正执行文件上传任务的脚本,命名为 upload.php,内容:

$url = "http://localhost/upload_output.php";

$post_data = array (
	"foo" => "bar",
	// 要上传的本地文件地址
	"upload" => "@C:/wamp/www/test.zip"
);

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post_data);

$output = curl_exec($ch);
curl_close($ch);

echo $output;

如果你需要上传一个文件,只需要把文件路径赋给upload,作为 POST 变量传过去,不过记得在前面加上@符号。执行这段脚本应该会得到类似如下输出:

Array
(
    [upload] => Array
    (
        [name] => test.zip
        [type] => application/octet-stream
        [tmp_name] => C:\Windows\php1BB4.tmp
        [error] => 0
        [size] => 487235
    )

)

9 cURL批处理(multi cURL)

cURL还有一个高级特性:批处理句柄(handle)。这一特性允许你同时或异步地打开多个URL连接。下面是来自来自php.net的示例代码

// 创建两个cURL资源
$ch1 = curl_init();
$ch2 = curl_init();

// 指定URL和适当的参数
curl_setopt($ch1, CURLOPT_URL, "http://lxr.php.net/");
curl_setopt($ch1, CURLOPT_HEADER, 0);
curl_setopt($ch2, CURLOPT_URL, "http://www.php.net/");
curl_setopt($ch2, CURLOPT_HEADER, 0);

// 创建cURL批处理句柄
$mh = curl_multi_init();

// 加上前面两个资源句柄
curl_multi_add_handle($mh, $ch1);
curl_multi_add_handle($mh, $ch2);

// 预定义一个状态变量
$active = null;

// 执行批处理
do {
    $mrc = curl_multi_exec($mh, $active);
} while ($mrc == CURLM_CALL_MULTI_PERFORM);

while ($active && $mrc == CURLM_OK) {
    if (curl_multi_select($mh) != -1) {
        do {
            $mrc = curl_multi_exec($mh, $active);
        } while ($mrc == CURLM_CALL_MULTI_PERFORM);
    }
}

// 关闭各个句柄
curl_multi_remove_handle($mh, $ch1);
curl_multi_remove_handle($mh, $ch2);
curl_multi_close($mh);

这里要做的就是打开多个 cURL 句柄并指派给一个批处理句柄。然后你就只需在一个while循环里等它执行完毕。

这个示例中有两个主要循环。第一个 do-while 循环重复调用 curl_multi_exec() 。这个函数是无隔断(non-blocking)的,但会尽可能少地执行。它返回一个状态值,只要这个值等于常量 CURLM_CALL_MULTI_PERFORM ,就代表还有一些刻不容缓的工作要做(例如,把对应URL的http头信息发送出去)。也就是说,我们需要不断调用该函数,直到返回值发生改变。

而接下来的 while 循环,只在 $active 变量为 true 时继续。这一变量之前作为第二个参数传给了 curl_multi_exec() ,代表只要批处理句柄中是否还有活动连接。接着,我们调用 curl_multi_select() ,在活动连接(例如接受服务器响应)出现之前,它都是被“屏蔽”的。这个函数成功执行后,我们又会进入另一个 do-while 循环,继续下一条URL。

还是来看一看怎么把这一功能用到实处吧:

9.1 WordPress 链接检查器

想象一下你有一个文章数目庞大的博客,这些文章中包含了大量外部网站链接。一段时间之后,因为这样那样的原因,这些链接中相当数量都失效了。要么是被和谐了,要么是整个站点都被功夫网了…

我们下面建立一个脚本,分析所有这些链接,找出打不开或者404的网站/网页,并生成一个报告。

请注意,以下并不是一个真正可用的WordPress插件,仅仅是一段独立功能的脚本而已,仅供演示,谢谢。

好,开始吧。首先,从数据库中读取所有这些链接:

// 配置 MySQL 数据库
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'wordpress';
$excluded_domains = array('localhost', 'www.mydomain.com');
$max_connections = 10;

// 初始化一些变量
$url_list = array();
$working_urls = array();
$dead_urls = array();
$not_found_urls = array();
$active = null;

// 连到 MySQL
if (!mysql_connect($db_host, $db_user, $db_pass)) {
	die('Could not connect: ' . mysql_error());
}

if (!mysql_select_db($db_name)) {
	die('Could not select db: ' . mysql_error());
}

// 找出所有含有链接的文章
$sql = "SELECT post_content FROM wp_posts
	WHERE post_content LIKE '%href=%'
	AND post_status = 'publish'
	AND post_type = 'post'";
$res = mysql_query($sql) or die(mysql_error());

while ($d = mysql_fetch_assoc($res)) {
	// 用正则匹配链接
	if (preg_match_all("!href=\"(.*?)\"!", $d['post_content'], $matches)) {

		foreach ($matches[1] as $url) {

			// 剔除排除的域名
			$tmp = parse_url($url);
			if (in_array($tmp['host'], $excluded_domains)) {
				continue;
			}

			// 保存 URL
			$url_list []= $url;
		}
	}
}

// 移除重复链接
$url_list = array_values(array_unique($url_list));

if (!$url_list) {
	die('No URL to check');
}

我们首先配置好数据库,一系列要排除的域名($excluded_domains),以及最大同时连接数量($max_connections)。然后,连接数据库,获取文章和包含的链接,把它们收集到一个数组中($url_list)。

下面的代码有点复杂了,因此我将一小步一小步地详细解释:

// 1. 批处理器
$mh = curl_multi_init();

// 2. 加入需批量处理的URL
for ($i = 0; $i < $max_connections; $i++) {
	add_url_to_multi_handle($mh, $url_list);
}

// 3. 初始处理
do {
	$mrc = curl_multi_exec($mh, $active);
} while ($mrc == CURLM_CALL_MULTI_PERFORM);

// 4. 主循环
while ($active && $mrc == CURLM_OK) {

	// 5. 有活动连接
	if (curl_multi_select($mh) != -1) {

		// 6. 干活
		do {
			$mrc = curl_multi_exec($mh, $active);
		} while ($mrc == CURLM_CALL_MULTI_PERFORM);

		// 7. 有信息否?
		if ($mhinfo = curl_multi_info_read($mh)) {
			// 意味着该连接正常结束

			// 8. 从curl句柄获取信息
			$chinfo = curl_getinfo($mhinfo['handle']);

			// 9. 死链么?
			if (!$chinfo['http_code']) {
				$dead_urls []= $chinfo['url'];

			// 10. 404了?
			} else if ($chinfo['http_code'] == 404) {
				$not_found_urls []= $chinfo['url'];

			// 11. 还能用
			} else {
				$working_urls []= $chinfo['url'];
			}

			// 12. 移除句柄
			curl_multi_remove_handle($mh, $mhinfo['handle']);
			curl_close($mhinfo['handle']);

			// 13. 加入新URL,干活
			if (add_url_to_multi_handle($mh, $url_list)) {
				do {
					$mrc = curl_multi_exec($mh, $active);
				} while ($mrc == CURLM_CALL_MULTI_PERFORM);
			}
		}
	}
}

// 14. 完了
curl_multi_close($mh);

echo "==Dead URLs==\n";
echo implode("\n",$dead_urls) . "\n\n";

echo "==404 URLs==\n";
echo implode("\n",$not_found_urls) . "\n\n";

echo "==Working URLs==\n";
echo implode("\n",$working_urls);

// 15. 向批处理器添加url
function add_url_to_multi_handle($mh, $url_list) {
	static $index = 0;

	// 如果还剩url没用
	if ($url_list[$index]) {

		// 新建curl句柄
		$ch = curl_init();

		// 配置url
		curl_setopt($ch, CURLOPT_URL, $url_list[$index]);

		// 不想输出返回的内容
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

		// 重定向到哪儿我们就去哪儿
		curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);

		// 不需要内容体,能够节约带宽和时间
		curl_setopt($ch, CURLOPT_NOBODY, 1);

		// 加入到批处理器中
		curl_multi_add_handle($mh, $ch);

		// 拨一下计数器,下次调用该函数就能添加下一个url了
		$index++;

		return true;
	} else {
		// 没有新的URL需要处理了
		return false;
	}
}

下面解释一下以上代码。列表的序号对应着代码注释中的顺序数字。

  1. 新建一个批处理句柄。
  2. 稍后我们将创建一个把URL加入批处理器的函数 add_url_to_multi_handle() 。每当这个函数被调用,就有一个新url被加入批处理器。一开始,我们给批处理器添加了10个URL(这一数字由 $max_connections 所决定)。
  3. 运行 curl_multi_exec() 进行初始化工作是必须的,只要它返回 CURLM_CALL_MULTI_PERFORM 就还有事情要做。这么做主要是为了创建连接,它不会等待完整的URL响应。
  4. 只要批处理中还有活动连接主循环就会一直持续。
  5. curl_multi_select() 会一直等待,直到某个URL查询产生活动连接。
  6. cURL的活儿又来了,主要是获取响应数据。
  7. 检查各种信息。当一个URL请求完成时,会返回一个数组。
  8. 在返回的数组中有一个 cURL 句柄。我们利用其获取单个 cURL 请求的相应信息。
  9. 如果这是一个死链或者请求超时,不会返回http状态码。
  10. 如果这个页面找不到了,会返回404状态码。
  11. 其他情况我们都认为这个链接是可用的(当然,你也可以再检查一下500错误之类…)。
  12. 从该批次移除这个 cURL 句柄,因为它已经没有利用价值了,关了它!
  13. 很好,现在可以另外加一个URL进来了。再一次地,初始化工作又开始进行…
  14. 嗯,该干的都干了。关闭批处理器,生成报告。
  15. 回过头来看给批处理器添加新 URL 的函数。这个函数每调用一次,静态变量 $index 就递增一次,这样我们才能知道还剩多少 URL 没处理。

我把这个脚本在我的博客上跑了一遍(测试需要,有一些错误链接是故意加上的),共检查约40个URL,只耗费两秒不到。当需要检查更加大量的URL时,其省心省力的效果可想而知!如果你同时打开10个连接,还能再快上10倍!另外,你还可以利用cURL批处理的无隔断特性,来处理大量URL请求,而不会阻塞你的Web脚本。

10 另一些有用的cURL 选项

10.1 HTTP 认证

如果某个URL请求需要基于 HTTP 的身份验证,你可以使用下面的代码:

$url = "http://www.somesite.com/members/";

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// 发送用户名和密码
curl_setopt($ch, CURLOPT_USERPWD, "myusername:mypassword");

// 你可以允许其重定向
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);

// 下面的选项让 cURL 在重定向后
// 也能发送用户名和密码
curl_setopt($ch, CURLOPT_UNRESTRICTED_AUTH, 1);

$output = curl_exec($ch);

curl_close($ch);

10.2 FTP 上传

PHP 自带有 FTP 类库, 但你也能用 cURL:

// 打开一个文件指针
$file = fopen("/path/to/file", "r");
$size = filesize("/path/to/file");

// url里包含了大部分所需信息
$url = "ftp://username:password@mydomain.com:21/path/to/new/file";

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// 上传相关的选项
curl_setopt($ch, CURLOPT_UPLOAD, 1);
curl_setopt($ch, CURLOPT_INFILE, $file);
curl_setopt($ch, CURLOPT_INFILESIZE, $size);

// 是否开启ASCII模式 (上传文本文件时有用)
curl_setopt($ch, CURLOPT_FTPASCII, 1);

$output = curl_exec($ch);
curl_close($ch);

10.3 代理/翻墙请求

你可以用代理发起 cURL 请求:

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, 'http://www.example.com');

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// 指定代理地址
curl_setopt($ch, CURLOPT_PROXY, '11.11.11.11:8080');

// 如果需要的话,提供用户名和密码
curl_setopt($ch, CURLOPT_PROXYUSERPWD, 'username:password');

$output = curl_exec($ch);
curl_close ($ch);

10.4 回调函数

可以在一个URL请求过程中,让 cURL 调用某指定的回调函数。例如,在内容或者响应下载的过程中,立刻开始利用数据,而不用等到完全下载完。

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, 'http://net.tutsplus.com');
curl_setopt($ch, CURLOPT_WRITEFUNCTION, "progress_function");

curl_exec($ch);
curl_close ($ch);

function progress_function($ch, $str) {
	echo $str;
	return strlen($str);
}

这个回调函数必须返回字串的长度,不然此功能将无法正常使用。在URL响应接收的过程中,只要收到一个数据包,这个函数就会被调用。

11 小结

今天我们一起学习了cURL库的强大功能和灵活的扩展性,希望你喜欢。下一次要发起URL请求时,考虑下cURL吧!谢谢!

Nginx开启和配置Gzip压缩

nginx 是一个高性能的 Web 服务器,合理配置nginx可以有效提高网站的响应速度。

本文介绍 nginx 的 gzip 和缓存开启配置。

gzip的压缩页面需要浏览器和服务器双方都支持,实际上就是服务器端压缩,传到浏览器后浏览器解压并解析。

1 开启gzip

Nginx的压缩输出有一组gzip压缩指令来实现。

相关指令位于http{…}两个大括号之间。

# 开启gzip
gzip on;

# 启用gzip压缩的最小文件,小于设置值的文件将不会压缩
gzip_min_length 1k;

# gzip 压缩级别,1-10,数字越大压缩的越好,也越占用CPU时间
gzip_comp_level 6;

# 进行压缩的文件类型。javascript有多种形式。
# 其中的值可以在 mime.types 文件中找到。
gzip_types text/plain application/javascript application/x-javascript text/css application/xml text/javascript application/x-httpd-php image/jpeg image/gif image/png;

# 是否在http header中添加Vary: Accept-Encoding,建议开启
gzip_vary on;

# 禁用IE 6 gzip
gzip_disable "MSIE [1-6]\.";

关于具体的参数说明可以参考 nginx 的文档

2 开启缓存

location ~* ^.+\.(ico|gif|jpg|jpeg|png)$ { 
    access_log off; 
    expires 30d;
}

location ~* ^.+\.(css|js|txt|xml|swf|wav)$ {
    access_log off;
    expires 24h;
}

location ~* ^.+\.(html|htm)$ {
     expires 1h;
}

其中的缓存时间可以自己根据需要修改。

PHP非阻塞实现方法

为让 PHP 在后端处理长时间任务时不阻塞,快速响应页面请求,可以有如下措施:

1 使用 fastcgi_finish_request()

如果 PHP 与 Web 服务器使用了 PHP-FPM(FastCGI 进程管理器),那通过 fastcgi_finish_request() 函数能马上结束会话,而 PHP 线程可以继续在后台运行。

echo "program start...";

file_put_contents('log.txt','start-time:'.date('Y-m-d H:i:s'), FILE_APPEND);
fastcgi_finish_request();

sleep(1);
echo 'debug...';
file_put_contents('log.txt', 'start-proceed:'.date('Y-m-d H:i:s'), FILE_APPEND);

sleep(10);
file_put_contents('log.txt', 'end-time:'.date('Y-m-d H:i:s'), FILE_APPEND);
从输出结果可看到,页面打印完program start...,输出第一行到 log.txt 后会话就返回了,所以后面的 debug... 不会在浏览器上显示,而 log.txt 文件能完整地接收到三个完成时间。

2 使用 fsockopen()

使用 fsockopen() 打开一个网络连接或者一个Unix套接字连接,再用 stream_set_blocking() 非阻塞模式请求:

$fp = fsockopen("www.example.com", 80, $errno, $errstr, 30);

if (!$fp) {
    die('error fsockopen');
}

// 转换到非阻塞模式
stream_set_blocking($fp, 0);

$http = "GET /save.php  / HTTP/1.1\r\n";
$http .= "Host: www.example.com\r\n";
$http .= "Connection: Close\r\n\r\n";

fwrite($fp, $http);
fclose($fp);

3 使用 cURL

利用cURL中的 curl_multi_* 函数发送异步请求

$cmh = curl_multi_init();
$ch1 = curl_init();
curl_setopt($ch1, CURLOPT_URL, "http://localhost/");
curl_multi_add_handle($cmh, $ch1);
curl_multi_exec($cmh, $active);
echo "End\n";

4 使用 Gearman/Swoole 扩展

 Gearman 是一个具有 php 扩展的分布式异步处理框架,能处理大批量异步任务。
Swoole 最近很火,有很多异步方法,使用简单。

5 使用缓存和队列

使用redis等缓存、队列,将数据写入缓存,使用后台计划任务实现数据异步处理。
这个方法在常见的大流量架构中应该很常见吧

6 调用系统命令

极端的情况下,可以调用系统命令,可以将数据传给后台任务执行,个人感觉不是很高效。

$cmd = 'nohup php ./processd.php $someVar >/dev/null  &';
`$cmd`

7 使用 pcntl_fork()

安装 pcntl 扩展,使用 pcntl_fork() 生成子进程异步执行任务,个人觉得是最方便的,但也容易出现僵尸进程。

$pid = pcntl_fork()
if ($pid == 0) {
    child_func();    //子进程函数,主进程运行
} else {
    father_func();   //主进程函数
}

echo "Process " . getmypid() . " get to the end.\n";
 
function father_func() {
    echo "Father pid is " . getmypid() . "\n";
}

function child_func() {
    sleep(6);
    echo "Child process exit pid is " . getmypid() . "\n";
    exit(0);
}

8 PHP 原生支持

外国佬的大招,没看懂
http://nikic.github.io/2012/12/22/Cooperative-multitasking-using-coroutines-in-PHP.html

网站大规模并发处理方案:电商秒杀与抢购

一、大规模并发带来的挑战

在过去的工作中,我曾经面对过5w每秒的高并发秒杀功能,在这个过程中,整个Web系统遇到了很多的问题和挑战。如果Web系统不做针对性的优化,会轻而易举地陷入到异常状态。我们现在一起来讨论下,优化的思路和方法哈。

1. 请求接口的合理设计

一个秒杀或者抢购页面,通常分为2个部分,一个是静态的HTML等内容,另一个就是参与秒杀的Web后台请求接口。

通常静态HTML等内容,是通过CDN的部署,一般压力不大,核心瓶颈实际上在后台请求接口上。这个后端接口,必须能够支持高并发请求,同时,非常重要的一点,必须尽可能“快”,在最短的时间里返回用户的请求结果。为了实现尽可能快这一点,接口的后端存储使用内存级别的操作会更好一点。仍然直接面向 MySQL之类的存储是不合适的,如果有这种复杂业务的需求,都建议采用异步写入。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

当然,也有一些秒杀和抢购采用“滞后反馈”,就是说秒杀当下不知道结果,一段时间后才可以从页面中看到用户是否秒杀成功。但是,这种属于“偷懒”行为,同时给用户的体验也不好,容易被用户认为是“暗箱操作”。

2. 高并发的挑战:一定要“快”

我们通常衡量一个Web系统的吞吐率的指标是QPS(Query Per Second,每秒处理请求数),解决每秒数万次的高并发场景,这个指标非常关键。举个例子,我们假设处理一个业务请求平均响应时间为100ms,同时, 系统内有20台Apache的Web服务器,配置MaxClients为500个(表示Apache的最大连接数目)。

那么,我们的Web系统的理论峰值QPS为(理想化的计算方式):

20*500/0.1 = 100000 (10万QPS)

咦?我们的系统似乎很强大,1秒钟可以处理完10万的请求,5w/s的秒杀似乎是“纸老虎”哈。实际情况,当然没有这么理想。在高并发的实际场景下,机器都处于高负载的状态,在这个时候平均响应时间会被大大增加。

就Web服务器而言,Apache打开了越多的连接进程,CPU需要处理的上下文切换也越多,额外增加了CPU的消耗,然后就直接导致平均响应时间 增加。因此上述的MaxClient数目,要根据CPU、内存等硬件因素综合考虑,绝对不是越多越好。可以通过Apache自带的abench来测试一 下,取一个合适的值。然后,我们选择内存操作级别的存储的Redis,在高并发的状态下,存储的响应时间至关重要。网络带宽虽然也是一个因素,不过,这种 请求数据包一般比较小,一般很少成为请求的瓶颈。负载均衡成为系统瓶颈的情况比较少,在这里不做讨论哈。

那么问题来了,假设我们的系统,在5w/s的高并发状态下,平均响应时间从100ms变为250ms(实际情况,甚至更多):

20*500/0.25 = 40000 (4万QPS)

于是,我们的系统剩下了4w的QPS,面对5w每秒的请求,中间相差了1w。

然后,这才是真正的恶梦开始。举个例子,高速路口,1秒钟来5部车,每秒通过5部车,高速路口运作正常。突然,这个路口1秒钟只能通过4部车,车流量仍然依旧,结果必定出现大塞车。(5条车道忽然变成4条车道的感觉)

同理,某一个秒内,20*500个可用连接进程都在满负荷工作中,却仍然有1万个新来请求,没有连接进程可用,系统陷入到异常状态也是预期之内。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

其实在正常的非高并发的业务场景中,也有类似的情况出现,某个业务请求接口出现问题,响应时间极慢,将整个Web请求响应时间拉得很长,逐渐将Web服务器的可用连接数占满,其他正常的业务请求,无连接进程可用。

更可怕的问题是,是用户的行为特点,系统越是不可用,用户的点击越频繁,恶性循环最终导致“雪崩”(其中一台Web机器挂了,导致流量分散到其他正常工作的机器上,再导致正常的机器也挂,然后恶性循环),将整个Web系统拖垮。

3. 重启与过载保护

如果系统发生“雪崩”,贸然重启服务,是无法解决问题的。最常见的现象是,启动起来后,立刻挂掉。这个时候,最好在入口层将流量拒绝,然后再将重启。如果是redis/memcache这种服务也挂了,重启的时候需要注意“预热”,并且很可能需要比较长的时间。

秒杀和抢购的场景,流量往往是超乎我们系统的准备和想象的。这个时候,过载保护是必要的。如果检测到系统满负载状态,拒绝请求也是一种保护措施。在前端设置过滤是最简单的方式,但是,这种做法是被用户“千夫所指”的行为。更合适一点的是,将过载保护设置在CGI入口层,快速将客户的直接请求返回。

 

二、作弊的手段:进攻与防守

秒杀和抢购收到了“海量”的请求,实际上里面的水分是很大的。不少用户,为了“抢“到商品,会使用“刷票工具”等类型的辅助工具,帮助他们发送尽可 能多的请求到服务器。还有一部分高级用户,制作强大的自动请求脚本。这种做法的理由也很简单,就是在参与秒杀和抢购的请求中,自己的请求数目占比越多,成功的概率越高。

这些都是属于“作弊的手段”,不过,有“进攻”就有“防守”,这是一场没有硝烟的战斗哈。

1. 同一个账号,一次性发出多个请求

部分用户通过浏览器的插件或者其他工具,在秒杀开始的时间里,以自己的账号,一次发送上百甚至更多的请求。实际上,这样的用户破坏了秒杀和抢购的公平性。

这种请求在某些没有做数据安全处理的系统里,也可能造成另外一种破坏,导致某些判断条件被绕过。例如一个简单的领取逻辑,先判断用户是否有参与记 录,如果没有则领取成功,最后写入到参与记录中。这是个非常简单的逻辑,但是,在高并发的场景下,存在深深的漏洞。多个并发请求通过负载均衡服务器,分配到内网的多台Web服务器,它们首先向存储发送查询请求,然后,在某个请求成功写入参与记录的时间差内,其他的请求获查询到的结果都是“没有参与记录”。 这里,就存在逻辑判断被绕过的风险。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

应对方案:

在程序入口处,一个账号只允许接受1个请求,其他请求过滤。不仅解决了同一个账号,发送N个请求的问题,还保证了后续的逻辑流程的安全。实现方案, 可以通过Redis这种内存缓存服务,写入一个标志位(只允许1个请求写成功,结合watch的乐观锁的特性),成功写入的则可以继续参加。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

或者,自己实现一个服务,将同一个账号的请求放入一个队列中,处理完一个,再处理下一个。

2. 多个账号,一次性发送多个请求

很多公司的账号注册功能,在发展早期几乎是没有限制的,很容易就可以注册很多个账号。因此,也导致了出现了一些特殊的工作室,通过编写自动注册脚 本,积累了一大批“僵尸账号”,数量庞大,几万甚至几十万的账号不等,专门做各种刷的行为(这就是微博中的“僵尸粉“的来源)。举个例子,例如微博中有转 发抽奖的活动,如果我们使用几万个“僵尸号”去混进去转发,这样就可以大大提升我们中奖的概率。

这种账号,使用在秒杀和抢购里,也是同一个道理。例如,iPhone官网的抢购,火车票黄牛党。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

应对方案:

这种场景,可以通过检测指定机器IP请求频率就可以解决,如果发现某个IP请求频率很高,可以给它弹出一个验证码或者直接禁止它的请求:

  1. 弹出验证码,最核心的追求,就是分辨出真实用户。因此,大家可能经常发现,网站弹出的验证码,有些是“鬼神乱舞”的样子, 有时让我们根本无法看清。他们这样做的原因,其实也是为了让验证码的图片不被轻易识别,因为强大的“自动脚本”可以通过图片识别里面的字符,然后让脚本自 动填写验证码。实际上,有一些非常创新的验证码,效果会比较好,例如给你一个简单问题让你回答,或者让你完成某些简单操作(例如百度贴吧的验证码)。
  2. 直接禁止IP,实际上是有些粗暴的,因为有些真实用户的网络场景恰好是同一出口IP的,可能会有“误伤“。但是这一个做法简单高效,根据实际场景使用可以获得很好的效果。

3. 多个账号,不同IP发送不同请求

所谓道高一尺,魔高一丈。有进攻,就会有防守,永不休止。这些“工作室”,发现你对单机IP请求频率有控制之后,他们也针对这种场景,想出了他们的“新进攻方案”,就是不断改变IP。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

有同学会好奇,这些随机IP服务怎么来的。有一些是某些机构自己占据一批独立IP,然后做成一个随机代理IP的服务,有偿提供给这些“工作 室”使用。还有一些更为黑暗一点的,就是通过木马黑掉普通用户的电脑,这个木马也不破坏用户电脑的正常运作,只做一件事情,就是转发IP包,普通用户的电 脑被变成了IP代理出口。通过这种做法,黑客就拿到了大量的独立IP,然后搭建为随机IP服务,就是为了挣钱。

应对方案:

说实话,这种场景下的请求,和真实用户的行为,已经基本相同了,想做分辨很困难。再做进一步的限制很容易“误伤“真实用户,这个时候,通常只能通过设置业务门槛高来限制这种请求了,或者通过账号行为的”数据挖掘“来提前清理掉它们。

僵尸账号也还是有一些共同特征的,例如账号很可能属于同一个号码段甚至是连号的,活跃度不高,等级低,资料不全等等。根据这些特点,适当设置参与门槛,例如限制参与秒杀的账号等级。通过这些业务手段,也是可以过滤掉一些僵尸号。

4. 火车票的抢购

看到这里,同学们是否明白你为什么抢不到火车票?如果你只是老老实实地去抢票,真的很难。通过多账号的方式,火车票的黄牛将很多车票的名额占据,部分强大的黄牛,在处理验证码方面,更是“技高一筹“。

高级的黄牛刷票时,在识别验证码的时候使用真实的人,中间搭建一个展示验证码图片的中转软件服务,真人浏览图片并填写下真实验证码,返回给中转软件。对于这种方式,验证码的保护限制作用被废除了,目前也没有很好的解决方案。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

因为火车票是根据身份证实名制的,这里还有一个火车票的转让操作方式。大致的操作方式,是先用买家的身份证开启一个抢票工具,持续发送请 求,黄牛账号选择退票,然后黄牛买家成功通过自己的身份证购票成功。当一列车厢没有票了的时候,是没有很多人盯着看的,况且黄牛们的抢票工具也很强大,即 使让我们看见有退票,我们也不一定能抢得过他们哈。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

最终,黄牛顺利将火车票转移到买家的身份证下。

解决方案:

并没有很好的解决方案,唯一可以动心思的也许是对账号数据进行“数据挖掘”,这些黄牛账号也是有一些共同特征的,例如经常抢票和退票,节假日异常活跃等等。将它们分析出来,再做进一步处理和甄别。

 

三、高并发下的数据安全

我们知道在多线程写入同一个文件的时候,会存现“线程安全”的问题(多个线程同时运行同一段代码,如果每次运行结果和单线程运行的结果是一 样的,结果和预期相同,就是线程安全的)。如果是MySQL数据库,可以使用它自带的锁机制很好的解决问题,但是,在大规模并发的场景中,是不推荐使用 MySQL的。秒杀和抢购的场景中,还有另外一个问题,就是“超发”,如果在这方面控制不慎,会产生发送过多的情况。我们也曾经听说过,某些电商搞抢购活动,买家成功拍下后,商家却不承认订单有效,拒绝发货。这里的问题,也许并不一定是商家奸诈,而是系统技术层面存在超发风险导致的。

1. 超发的原因

假设某个抢购场景中,我们一共只有100个商品,在最后一刻,我们已经消耗了99个商品,仅剩最后一个。这个时候,系统发来多个并发请求,这批请求读取到的商品余量都是99个,然后都通过了这一个余量判断,最终导致超发。(同文章前面说的场景)

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

在上面的这个图中,就导致了并发用户B也“抢购成功”,多让一个人获得了商品。这种场景,在高并发的情况下非常容易出现。

2. 悲观锁思路

解决线程安全的思路很多,可以从“悲观锁”的方向开始讨论。

悲观锁,也就是在修改数据的时候,采用锁定状态,排斥外部请求的修改。遇到加锁的状态,就必须等待。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

虽然上述的方案的确解决了线程安全的问题,但是,别忘记,我们的场景是“高并发”。也就是说,会很多这样的修改请求,每个请求都需要等待 “锁”,某些线程可能永远都没有机会抢到这个“锁”,这种请求就会死在那里。同时,这种请求会很多,瞬间增大系统的平均响应时间,结果是可用连接数被耗 尽,系统陷入异常。

3. FIFO队列思路

那好,那么我们稍微修改一下上面的场景,我们直接将请求放入队列中的,采用FIFO(First Input First Output,先进先出),这样的话,我们就不会导致某些请求永远获取不到锁。看到这里,是不是有点强行将多线程变成单线程的感觉哈。

然后,我们现在解决了锁的问题,全部请求采用“先进先出”的队列方式来处理。那么新的问题来了,高并发的场景下,因为请求很多,很可能一瞬 间将队列内存“撑爆”,然后系统又陷入到了异常状态。或者设计一个极大的内存队列,也是一种方案,但是,系统处理完一个队列内请求的速度根本无法和疯狂涌 入队列中的数目相比。也就是说,队列内的请求会越积累越多,最终Web系统平均响应时候还是会大幅下降,系统还是陷入异常。

4. 乐观锁思路

这个时候,我们就可以讨论一下“乐观锁”的思路了。乐观锁,是相对于“悲观锁”采用更为宽松的加锁机制,大都是采用带版本号 (Version)更新。实现就是,这个数据所有请求都有资格去修改,但会获得一个该数据的版本号,只有版本号符合的才能更新成功,其他的返回抢购失败。 这样的话,我们就不需要考虑队列的问题,不过,它会增大CPU的计算开销。但是,综合来说,这是一个比较好的解决方案。

Web系统大规模并发——电商秒杀与抢购 - 徐汉彬Hansion - 技术行者

有很多软件和服务都“乐观锁”功能的支持,例如Redis中的watch就是其中之一。通过这个实现,我们保证了数据的安全。

 

四、小结

互联网正在高速发展,使用互联网服务的用户越多,高并发的场景也变得越来越多。电商秒杀和抢购,是两个比较典型的互联网高并发场景。虽然我们解决问题的具体技术方案可能千差万别,但是遇到的挑战却是相似的,因此解决问题的思路也异曲同工。

生成每秒100万级别HTTP请求的Web负载工具

Tsung

http://tsung.erlang-projects.org/

Tsung是一个开源的多协议分布式的负载测试工具。Tsung可以对HTTP,WebDAV,SOAP和PostgreSQL,MySQL,LDAP和Jabber/ XMPP服务器进行压力测试,是一款免费软件。

Httperf

https://github.com/httperf/httperf

Httperf是一个负载压力测试工具,用于在一定约束条件下测试系统所能承受的并发用户量、运行时间、数据量,以确定系统所能承受的最大负载压力。用于压力,性能,负责等测试,对测试站点进行分析。

Apache Jmeter

http://jmeter.apache.org/

Apache JMeter是Apache组织开发的基于Java的压力测试工具。用于对软件做压力测试,它最初被设计用于Web应用测试但后来扩展到其他测试领域。 它可以用于测试静态和动态资源例如静态文件、Java 小服务程序、CGI 脚本、Java 对象、数据库, FTP 服务器, 等等。JMeter 可以用于对服务器、网络或对象模拟巨大的负载,来自不同压力类别下测试它们的强度和分析整体性能。另外,JMeter能够对应用程序做功能/回归测试,通过创建带有断言的脚本来验证你的程序返回了你期望的结果。为了最大限度的灵活性,JMeter允许使用正则表达式创建断言。
Apache jmeter 可以用于对静态的和动态的资源(文件,Servlet,Perl脚本,java 对象,数据库和查询,FTP服务器等等)的性能进行测试。它可以用于对服务器,网络 或对象模拟繁重的负载来测试它们的强度或分析不同压力类型下的整体性能。你可以使用它做性能的图形分析或在大并发负载测试你的服务器/脚本/对象。

Autobench

http://www.xenoclast.org/autobench/

autobench是一个Perl脚本,它能是httperf得以充分利用——它会连续测试和自动增长请求频率,直到服务器变得饱和。autobenchd的一个有趣功能是它能够产生一个.tsv报告,可以使用各种应用程序打开并产生图形。

OpenWebLoad

http://sourceforge.net/projects/openwebload/

OpenWebLoad是一个自由的开源系统,它适用于Linux和Windows平台,最早开发于2000年,可在服务器上抛出大量请求,看能够正确处理多少,它只是使用数量可变的连接,发送尽可能多的请求,每一秒发送报告。

26条MySQL性能优化的最佳经验

今天,数据库操作越来越成为应用的性能瓶颈。

对于Web应用尤其明显。

关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情

当我们去设计数据库表结构、查询表数据时,都应该注意性能问题。

这里,我们不会讲过多的SQL语句的优化,这里我们主要针对MySQL数据库的设计优化

1 使用查询缓存

大多数的MySQL服务器都开启了查询缓存(QueryCache)。

这是提高性最有效的方法之一,查询缓存由MySQL数据库引擎自动处理。

当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,

这样,后续的相同的查询就不用操作表,而直接访问缓存结果了

程序员很容易忽略这个功能,不经意间就会写了一些用不上缓存的SQL语句。

如下示例:

// 查询缓存不被启用
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 
// 查询缓存被启用
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。

所以,像 NOW() RAND() 或是其它的诸如此类的SQL函数,

都不会开启查询缓存,因为这些函数的返回是变数。

2 为查询加上 LIMIT

即使确定查询表只会有1条结果,加上 LIMIT 1 可以增加性能。

因为这样加上LIMIT后, MySQL数据库引擎在找到一条数据后就会停止搜索,而不是继续往查找。

如下示例,我们要查找是否有“中国”的用户。

// 没有效率的:
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
    // ...
}

// 有效率的:
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

很明显,后面的会比前面的更有效率。

请注意,第一条中是select *,第二条是select 1

3 使用索引

索引是提高数据库查询性能最常用的方法。

索引可以令数据库查询快得多。

尤其是在查询语句当中包含有MAX()MIN()ORDER BY这些命令的时候,性能提高更为明显。

那该对哪些字段建立索引呢?

一般说来,索引应建立在那些将用于JOINWHERE判断和ORDER BY排序的字段上。

也就是说明,如果某个字段经常用来做搜索,那么,请为其建立索引吧。

注意,尽量不要对数据库中某个含有大量重复的值的字段建立索引。

对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况,

例如 :客户表中的province(省份)字段,在这样的字段上建立索引将不会有什么帮助。

相反,还有可能降低数据库的性能。

我们可以在创建表的时候同时创建合适的索引,也可以在之后使用ALTER TABLECREATE INDEX创建索引。

如下是LIKE条件查询有无索引的性能比较:

从上图你可以看到,搜索字串是:last_name LIKE 'a%',创建索引的性能高处4倍左右。

另外,从MySQL5.6开始,INNODB和MyISAM存储引擎都支持全文索引和搜索。

全文索引在MySQL 中是一个FULLTEXT类型索引,可以根据实际情况使用。

需要注意的是,索引会使得INSERTUPDATE的变慢,

所以,在需要经常更改的字段,请考虑精简索引或者分表处理。

4 EXPLAIN 查询语句

使用 EXPLAIN 关键字可以通过了解SQL语句的执行过程,来分析性能瓶颈的缘由。

EXPLAIN 的查询结果还会展示索引主键如何被利用、数据表如何被搜索和排序……等等。

挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。

然后,我们会得到一张表格。

如下示例,没有为 group_id加索引的情况:

group_id 字段加上索引后的情况:

可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 16 行。

查看rows列可以让我们找到潜在的性能问题。

5 使用JOIN来代替子查询

MySQL从4.1开始支持SQL的子查询。

这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。

例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询,

先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,

如下所示:

DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

使用子查询可以一次性的完成很多操作,这些操作逻辑上需要多个SQL步骤才能完成。

同时也可以避免事务或者表锁死,并且写起来也很容易。

但是,有些情况下,子查询可以被更有效率的连接JOIN替代。

例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接JOIN来完成这个查询工作,速度将会快很多。

尤其是当 salesinfo 表中对 CustomerID 建有索引的话,性能将会更好。

查询如下:

SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL

连接JOIN之所以更有效率一些,是因为 JOIN 不需要在内存中创建临时表

6 JOIN列同类型,并索引

如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的

这样,MySQL内部会为你优化Join的SQL语句的机制。

而且,这些被用来Join的字段,应该是相同的数据类型的。

例如:如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。

对于那些STRING类型,还需要有相同的字符集才行。

特别是两个表,它们的字符集有可能不一样。

// 在state中查找company
// 两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集。
$r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");

7 使用UNION

MySQL 从 4.0 的版本开始支持 UNION 查询。

UNION查询可以把两条或更多的 SELECT 查询合并的一个查询中,这在以前是要手动创建临时表来完成的。

在客户端的会话开始和结束的时候,临时表会自动创建和删除,从而保证数据库整齐、高效。

使用 UNION 来创建查询的时候,我们只需要用 UNION 作为关键字把多个 SELECT 语句连接起来就可以了。

要注意的是,所有 SELECT 语句中的字段数目要相同

下面的例子就演示了一个使用 UNION的查询。

SELECT name,phone FROM client UNION SELECT name,birthdate FROM author
UNION
SELECT name,supplier FROM product

8 使用事务

尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,

但不是所有的数据库操作都这么简单,只用一条或少数几条SQL语句就可以完成。

更多的时候是,需要用到一系列的语句来完成某种工作。

在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。

设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:

第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成。

这样,就会造成数据的不完整,甚至会破坏数据库中的数据。

要避免这种情况,就应该使用事务。

它的作用是:要么语句块中每条语句都操作成功,要么都失败。

换句话说,就是可以保持数据库中数据的一致性完整性

事务以BEGIN 关键字开始,COMMIT关键字结束。

执行完成前,只要有一条SQL操作失败,ROLLBACK命令就可以把数据库恢复到 BEGIN 开始之前的状态。

BEGIN;
    INSERT INTO salesinfo SET CustomerID=14;
    UPDATE inventory SET Quantity=11 WHERE item='book';
COMMIT;

事务的另一个重要作用是,当多个用户同时使用相同的数据源时,

它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,

这样可以保证用户的操作不被其它的用户所干扰。

9 锁定表

尽管事务是维护数据库完整性的一个非常好的方法,

但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。

由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。

如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大的问题。

但假设有成千上万的用户同时访问一个数据库系统,

例如,访问一个电子商务网站,就会产生比较严重的响应延迟。

其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。

下面的例子就用锁定表的方法,来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE
    SELECT Quantity FROM inventory WHERE Item='book';
    ...
    UPDATE inventory SET Quantity=11 WHERE Item='book';
UNLOCK TABLES

这里,我们用一个 SELECT 语句取出初始数据,

通过一些计算,用 UPDATE 语句将新值更新到表中。

包含有 WRITE 关键字的 LOCK TABLE 语句,保证在 UNLOCK TABLES 命令被执行之前,

不会有其它的访问来对表 inventory 进行INSERTUPDATE或者DELETE的操作。

10 使用外键

锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。

如果要保证数据的关联性,我们就可以使用外键。

例如,外键可以保证每一条销售记录都指向某一个存在的客户。

在这里,外键可以把 customerinfo 表中的 CustomerID 映射到 salesinfo 表中 CustomerID

任何一条没有合法 CustomerID 的记录都不会被更新或插入到 salesinfo 中。

CREATE TABLE customerinfo
(
    CustomerID INT NOT NULL ,
    PRIMARY KEY ( CustomerID )
) TYPE = INNODB;

CREATE TABLE salesinfo
(
    SalesID INT NOT NULL,
    CustomerID INT NOT NULL,
    PRIMARY KEY(CustomerID, SalesID),
    FOREIGN KEY (CustomerID) REFERENCES customerinfo
    (CustomerID) ON DELETECASCADE
) TYPE = INNODB;

注意例子中的参数ON DELETE CASCADE

该参数保证:customerinfo 表中的一条客户记录被删除的时候,salesinfo 表中所有与该客户相关的记录也会被自动删除。

如果要在 MySQL 中使用外键,一定要记住在创建表的时候,将表的类型定义为事务安全表 InnoDB类型

该类型不是 MySQL 表的默认类型。

定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB,如例中所示。

11 千万不要 ORDER BY RAND()

想打乱返回的数据行?随机挑一个数据?

真不知道谁发明了这种用法,但很多新手很喜欢这样用。

但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。

这样使用只让你的数据库的性能呈指数级的下降。

这里的问题是:MySQL会不得不去执行RAND()函数(很耗CPU时间),

而且这是为了每一行记录去记行,然后再对其排序。

就算是你用了Limit 1也无济于事(因为要排序)。

下面的示例是随机挑一条记录:

// 千万不要这样做:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

// 这要会更好:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0, $d[0] - 1);
 
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

12 避免 SELECT *

从数据库里读出越多的数据,那么查询就会变得越慢。

并且,如果数据库服务器和WEB服务器是独立的服务器的话,这还会增加网络传输的负载。

所以,应该养成需要什么就取什么的好的习惯。

// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
 
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

13 永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键

而且最好的是INT型的(推荐使用UNSIGNED INT),并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫 email的字段,你也别让它成为主键。

使用 VARCHAR 类型来当主键会使用得性能下降。

另外,在你的程序中,你应该使用表的ID来构造你的数据结构。

而且,在MySQL数据引擎下,还有一些操作需要使用主键,

在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……

在这里,只有一个情况是例外,那就是“关联表”的“外键”,

也就是说,这个表的主键,通过若干个别的表的主键构成,我们把这个情况叫做“外键”。

比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,

那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,

在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。

14 使用 TINYINT 而不是 VARCHAR

TINYINT类型是最大值是127,非常小巧快捷。

这样一来,用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,

你知道这些字段的取值是有限而且固定的,

那么,你应该使用 TINYINT 而不是 VARCHAR

 

15 别用 NULL

除非你有一个很特别的原因使用 NULL 值,不然应该总是让字段保持 NOT NULL

这看起来好像有点争议,请往下看。

首先,问问你自己,“Empty”和“NULL”有多大的区别(如果是INT,那就是0NULL)?

如果你觉得它们之间没有什么区别,那么你就不要使用NULL

(你知道吗?在 Oracle 里,NULLEmpty 的字符串是一样的!)

不要以为 NULL 不需要空间,其需要额外的空间。

并且,在进行比较的时候,NULL会让程序会更复杂。

当然,这里并不是说你就不能使用NULL了。

现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。

下面摘自MySQL自己的文档:

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

16 Prepared Statements

Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合。

我们可以从使用 Prepared statements 获得很多好处,无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击

当然,你也可以手动地检查你的这些变量,然而,手动的检查容易出问题,而且很经常会被程序员忘了。

当我们使用一些framework或是ORM的时候,这样的问题会好一些。

在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。

你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次。

最新版本的MySQL在传输Prepared Statements是使用二进制形势,所以这会使得网络传输非常有效率。

在PHP中要使用 Prepared statements,你可以查看其使用手册:mysqli 扩展 或是PDO

// 创建 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { 

    // 绑定参数
    $stmt->bind_param("s", $state);
 
    // 执行
    $stmt->execute();
 
    // 绑定结果
    $stmt->bind_result($username);
 
    // 移动游标
    $stmt->fetch();

    printf("%s is from %s\n", $username, $state);
    $stmt->close();
}

17 无缓冲的查询

正常的情况下,当脚本中执行一个SQL语句的时候,程序会停在那里,

直到SQL语句执行完成,然程序才继续往下执行。

我们可以使用无缓冲查询来改变这个行为。

关于这个事情,在PHP的文档中有一个非常不错的说明,

mysql_unbuffered_query() 函数(新的应用应该使用PDO):

“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”

上面那句话翻译过来是说,mysql_unbuffered_query() 发送一个SQL语句到MySQL。

不像mysql_query()mysql_unbuffered_query() 不去自动fetch和缓存结果。

这会相当节约内存,尤其是那些会产生大量结果的查询语句。

并且,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了。

不过需要注意的是,要么你把所有行都读走,要么下一次的查询前调用 mysql_free_result() 清除结果,

不然 mysql_num_rows()mysql_data_seek() 将无法使用。

所以,是否使用无缓冲的查询,你需要仔细考虑。

18 把IP存成 UNSIGNED INT

很多程序员会创建一个 VARCHAR(15) 字段来存放字符串形式的IP,而不是整型的IP。

如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。

而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:where IP between ip1 and ip2

我们必需要使用UNSIGNED INT,因为IP地址会使用整个32位的无符号整型。

而SQL查询中可以使用 INET_ATON()来把一个字符串IP转成一个整型,使用 INET_NTOA() 把一个整形转成一个字符串IP。

在PHP中,也有这样的函数: ip2long()long2ip()

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

19 固定长度的表会更快

如果表中的所有字段都是“固定长度”的,整个表会被认为是 static” 或 “fixed-length

例如,表中没有如下类型的字段: VARCHARTEXTBLOB

只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,

这样,MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能,MySQL搜寻得会更快一些,

因为这些固定的长度很容易计算下一个数据的偏移量,所以读取的自然也会很快。

而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

并且,固定长度的表也更容易被缓存和重建。

不过,唯一的副作用是,固定长度的字段会浪费一些空间,

因为无论你用不用,定长的字段都要分配那么多的空间。

使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。

20 垂直分割

“垂直分割”是一种把数据库中的表,按列变成几张表的方法。

这样可以降低表的复杂度和字段的数目,从而达到优化的目的。

示例一:

Users表中有一个字段是address,它是可选字段,并且不需要经常读取或是修改。

那么,就可以把它放到另外一张表中,这样会让表有更好的性能。

很多情况下,Users表只有用户ID、用户名、口令、户角色等会被经常使用。

小一点的表总是会有好的性能。

示例二:

有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。

但是,每次更新时会导致该表的查询缓存被清空。

所以,你可以把这个字段放到另一个表中。

这样就不会影响你对用户ID、用户名、用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。

另外需要注意的是,这些被分出去的字段所形成的表,不要经常性地去Join他们。

不然的话,这样的性能会比不分割时还要差。

而且,会是极数级的下降。

21 拆分大的 DELETE 或 INSERT 语句

如果在一个在线的网站上执行大的 DELETEINSERT ,需要非常小心。

要避免你的操作让整个网站停止响应。

因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

Apache/Nginx 会有很多的子进程或线程,它们在同一时间可能会有很多请求到MySQL。

而我们的服务器也不希望有太多的子进程、线程和数据库链接。

这是极大的占服务器资源的事情,尤其是内存。

如果你把你的表锁上一段时间,比如30秒钟,

那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程、线程、数据库链接、打开的文件数,

可能不仅仅会让你的WEB服务Crash,还可能会让你的整台服务器马上掛了。

所以,如果你有一个大的处理,你定你一定把其拆分,使用 LIMIT 条件是一个好的方法。

下面是一个示例:

while (1) {

    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");

    if (mysql_affected_rows() == 0) {
        // 没得可删了,退出!
        break;
    }

    // 每次都要休息一会儿
    usleep(50000);
}

22 越小的列会越快

对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。

所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。

参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

如果一个表只会有几行罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,

使用 MEDIUMINTSMALLINT 或是更小的 TINYINT 会更经济一些。

如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。

再比如,在定义邮政编码字段时,CHAR(6)就可以很好的完成任务,

如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR类型也是多余的。

当然,你也需要留够足够的扩展空间。

不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),

一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。

23 避免数据类型自动转换

绝大多数情况下,使用索引可以提高查询的速度。

但如果SQL语句使用不恰当的话,索引将无法发挥它应有的作用。

我们应该注意的几个方面。

首先,最好是在相同类型的字段间进行比较的操作。

在MySQL 3.23版之前,这甚至是一个必须的条件。

例如,不能将一个建有索引的INT字段和BIGINT字段进行比较;

但是作为特殊的情况,在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候,可以将它们进行比较。

其次,在建有索引的字段上尽量不要使用函数进行操作

例如,在一个DATE类型的字段上使用YEAR()函数时,将会使索引不能发挥应有的作用。

所以,下面的两个查询虽然返回的结果一样,但后者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";

同样的情形也会发生在对数值型字段进行计算的时候:

SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;

上面的两个查询也是返回相同的结果,但后面的查询将比前面的一个快很多。

第三,在搜索字符型字段时,我们应该少用 LIKE 关键字和通配符

因为LIKE这种做法虽然简单,但却也是以牺牲系统性能为代价的。

例如下面的查询将会比较表中的每一条记录。

SELECT * FROM books WHERE name like "MySQL%";

但是如果换用下面的查询,返回的结果一样,但速度就要快上很多:

SELECT * FROM books WHERE name>="MySQL"and name<"MySQM";

在实际应用中,以上例子中的查询字段应使用具体的字段名列表,而不是直接用* 号,以提高查询速度。

最后,应该注意避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用。

24 选择正确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。

甚至你只是需要update一个字段,整个表都会被锁起来,

而别的进程,就算是读进程都无法操作直到读操作完成。

另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。

但是InnoDB 支持“行锁” ,于是在写操作比较多的时候,会更优秀。

并且,他还支持更多的高级应用,比如:事务

下面是MySQL的手册

25 使用一个对象关系映射器

使用 ORM (Object Relational Mapper),你能够获得可靠的性能增涨。

一个ORM可以做的所有事情,也能被手动的编写出来。

但是,这需要一个高级专家。

ORM 的最重要的是“Lazy Loading”。

也就是说,只有在需要的去取值的时候才会去真正的去做。

但你也需要小心这种机制的副作用,因为这很有可能会创建很多很小的查询,这反而会降低性能。

ORM 还可以把你的SQL语句打包成一个事务,这会比单独执行他们快得多得多。

26 小心“永久链接”

“永久链接”的目的是用来减少重新创建MySQL链接的次数。

当一个链接被创建了,它会永远处在连接的状态,即使数据库操作已经结束。

这可能源于Apache/Nginx可以重用子进程的缘故。

也就是说,下一次的HTTP请求会重用WEB服务器子进程,并重用相同的 MySQL 链接。

在理论上来说,这听起来非常的不错。

但是从经验上来说,这个功能制造出来的麻烦事更多。

因为,你只有有限的链接数、内存问题、文件句柄数,等等。

而且,WEB服务器运行在极端并行的环境中,会创建很多很多的子进程。

这就是为什么这种“永久链接”的机制工作地不好的原因。

在你决定要使用“永久链接”之前,你需要好好地考虑一下你的整个系统的架构。

30条MySQL查询的优化方法

以下是广泛使用的30个SQL查询语句优化方法:

1、应尽量避免在 where 子句中使用!=<>操作符,否则将引擎放弃使用索引而进行全表扫描

2、对查询进行优化,首先应考虑在 whereorder by 涉及的列上建立索引,避免全表扫描

3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引,而进行全表扫描,如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引,而进行全表扫描,如:

select id from t where num=10 or num=20

可以用union来查询:

select id from t where num=10
union all
select id from t where num=20

5、前置百分号的查询也将导致全表扫描

select id from t where name like '%abc%'

若要提高效率,可以考虑全文检索。

6、innot in 也要慎用,否则会导致全表扫描,如:

select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数,也会导致全表扫描

因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时。

它必须在编译时进行选择。

然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。

如下面语句将进行全表扫描:

select id from t where num=@num

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作

这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2=100

应改为:

select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作

这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3)='abc'name                            -- 以abc开头的id
select id from t where datediff(day, createdate, '2005-11-30')=0'2005-11-30'    -- 生成的id

应改为:

select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10、不要在 where 子句中的等式判断=左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引

11、在复合索引中,在使用索引字段作为条件时,

必须使用到该索引中的第一个字段作为条件时,才能保证系统使用该索引,否则该索引将不会被使 用,

并且应尽可能的让字段顺序与索引顺序相一致

12、不要写一些没有意义的查询,如需要生成一个空表结构:

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(…)

13、很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引对查询都有效。

SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引

如一表中有字段 sexmalefemale几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好。

索引固然可以提高相应的 select 的效率,但同时也降低了 insertupdate 的效率。

因为 insertupdate 时有可能会重建索引,

所以怎样建索引需要慎重考虑,视具体情况而定。

一个表的索引数最好不要超过6个。

若太多,则应考虑一些不常使用到的列上建的索引是否有 必要。

16、应尽可能的避免更新 clustered 索引数据列。

因为 clustered 索引数据列的顺序就是表记录的物理存储顺序。

一旦该列值改变,将导致整个表记录的顺序的调整,会耗费相当大的资源。

若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段。

若只含数值信息的字段,尽量不要设计为字符型。

否则会降低查询和连接的性能,并会增加存储开销。

这是因为,引擎在处理查询和连接时,会逐个比较字符串中每一个字符。

而对于数字型而言只需要比较一次就够了。

例如,PHP可以使用ip2long()long2ip()函数把IP地址转成整型后,再存放进数据库。

18、尽可能的使用 varchar/nvarchar 代替 char/nchar

首先,变长字段存储空间小,可以节省存储空间。

其次,对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。

如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21、避免频繁创建和删除临时表,以减少系统表资源的消耗。

22、临时表并不是不可使用,适当地使用它们可以使某些例程更有效。

例如,当需要重复引用大型表或常用表中的某个数据集时。

但是,对于一次性事件,最好使用导出表。

23、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table

这样可以避免造成大量 log ,提高速度。

如果数据量不大,为了缓和系统表的资源,应先create table,然后insert

24、如果使用到了临时表,在存储过程的最后,务必将所有的临时表显式删除

truncate table ,然后 drop table

这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标。

因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26、基于集的方法通常更有效。

使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题。

27、与临时表一样,游标并不是不可使用。

对小型数据集使用 FAST_FORWARD 游标,通常要优于其他逐行处理方法。

尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括“合计”的例程,通常要比使用游标执行的速度快。

如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF

无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量。

若数据量过大,应该考虑相应需求是否合理。

30、尽量避免大事务操作,提高系统并发能力。

48条高效率的PHP优化写法

1 字符串

1.1 少用正则表达式

能用PHP内部字符串操作函数的情况下,尽量用他们,不要用正则表达式, 因为其效率高于正则。

没得说,正则最耗性能。

str_replace函数要比preg_replace快得多,strtr函数又比str_replace来得快。

有没有你漏掉的好用的函数?

例如:strpbrk()strncasecmp()strpos()strrpos()stripos()strripos()

1.2 字符替换

如果需要转换的全是单个字符,用字符串作为 strtr() 函数完成替换,而不是数组:

$addr = strtr($addr, "abcd", "efgh");       // 建议
$addr = strtr($addr, array('a' => 'e', ));  // 不建议

效率提升:10 倍。

str_replace字符替换比正则替换preg_replace快,但strtrstr_replace又快1/4

另外,不要做无谓的替换,即使没有替换,str_replace也会为其参数分配内存。很慢!

strpos 先查找(非常快),看是否需要替换,如果需要,再替换。

如果需要替换,效率几乎相等,差别在 0.1% 左右。

如果不需要替换:用 strpos200%

1.3 压缩大的字符串

使用 gzcompress() 和 gzuncompress() 对容量大的字符串进行压缩和解压,再存入和取出数据库

这种内置的函数使用gzip算法,能压缩字符串90%

1.4 echo 输出

echo 字符串用逗号代替点连接符更快些。

虽然,echo是一种语言结构,不是真正的函数。

但是,它可以把逗号隔开的多个字符串当作“函数”参数传入,所以速度会更快。

echo $str1, $str2;       // 速度快
echo $str1 . $str2;      // 速度稍慢

1.5 尽量用单引号

PHP 引擎允许使用单引号和双引号来封装字符串变量,但是它们的速度是有很大的差别的!

使用双引号的字符串会告诉 PHP 引擎,首先去读取字符串内容,查找其中的变量,并改为变量对应的值。

一般来说字符串是没有变量的,使用双引号会导致性能不佳。

最好使用字符串连接,而不是双引号字符串。

$output = "This is a plain string";  // 不好的实践
$output = 'This is a plain string';  // 好的实践

$type = "mixed";                     // 不好的实践
$output = "This is a $type string";

$type = 'mixed';                     // 好的实践
$output = 'This is a ' . $type . ' string';

1.6 使用isset代替strlen

在检验字符串长度时,我们第一想法会使用 strlen() 函数。

此函数执行起来相当快,因为它不做任何计算,只返回在zval结构(C的内置数据结构,用于存储PHP变量)中存储的已知字符串长度。

但是,由于strlen()是函数,多多少少会有些慢,因为函数调用会经过诸多步骤,如字母小写化、哈希查找,会跟随被调用的函数一起执行。

在某些情况下,你可以使用 isset() 技巧加速执行你的代码。例如:

if (strlen($foo) < 5) {
    echo "Foo is too short";
}

// 使用isset()
if (!isset($foo{5})) {
    echo "Foo is too short";
}

1.7 用split分割字符串

在分割字符串时,split()要比explode()快。

split()
0.001813 - 0.002271 seconds (avg 0.002042 seconds)
explode()
0.001678 - 0.003626 seconds (avg 0.002652 seconds)

1.8 echo效率高于print

因为echo没有返回值,print返回一个整型。

注意:echo输出大字符串的时候,如果没有调整就会严重影响性能。

打开Apache的mod_deflate进行压缩,或者打开ob_start将内容放进缓冲区,可以改善性能问题。

2 语句

2.1 最好不用@

@掩盖错误会降低脚本运行速度,并且在后台有很多额外操作。

@比起不用,效率差距 3 倍。特别不要在循环中使用@

在 5 次循环的测试中,即使是先用error_reporting(0)关掉错误,循环完成后再打开,都比用@快。

2.2 避免使用魔术方法

对于__开头的函数就命名为魔术函数,它们都在特定的条件下触发。

这些魔术函数包括:__construct()__get()__call()__autoload()等等。

__autoload() 为例,如果不能将类名与实际的磁盘文件对应起来,将不得不做大量的文件存在判断。

而判断文件存在需要磁盘I/O操作,众所周知,磁盘I/O操作的效率很低,因此这才是使得autoload机制效率降低的原因。

因此,在系统设计时,需要定义一套清晰的、将类名与实际磁盘文件映射的机制。

这个规则越简单越明确,__autoload()机制的效率就越高。

autoload机制并不是天然的效率低下,只有滥用autoload、设计不好的自动装载函数,才会导致其效率的降低.

所以说,尽量避免使用__autoload等魔术方法,有待商榷。

2.3 别在循环里用函数

例如:

for($x=0; $x < count($array); $x++) {
}

这种写法在每次循环的时候都会调用 count() 函数,效率大大降低,建议这样:

$len = count($array);
for($x=0; $x < $len; $x++) {
}

让函数在循环外面一次获得循环次数。

2.4 使用三元运算符

在简单的判断语句中,三元运算符?:更简洁高效。

2.5 使用选择分支语句

switchcase好于使用多个ifelse if语句,并且代码更加容易阅读和维护。

2.6 屏蔽敏感信息

使用 error_reporting() 函数来预防潜在的敏感信息显示给用户。

理想的错误报告应该被完全禁用在php.ini文件里。

如果用的是共享虚拟主机,php.ini不能修改,最好添加 error_reporting() 函数。

放在每个脚本文件的第一行,或者用require_once()来加载,能有效的保护敏感的SQL查询和路径,在出错时不被显示。

2.7 不实用段标签<?

不要使用开始标志的缩写形式,你正在使用这样的符号吗<?,应该用完整的<?php开始标签。

当然,如果是输出变量,用<?= $value ?>这种方式是鼓励的,可以是代码更加简洁。

2.8 纯PHP代码不加结束标记

如果文件内容是纯 PHP 代码,最好在文件末尾删除 PHP 结束标记?>

这可以避免在 PHP 结束标记之后万一意外加入了空格或者换行符,会导致 PHP 开始输出这些空白,而脚本中此时并无输出的意图。

2.9 永远不要使用register_globalsmagic quotes

这是两个很古老的功能,在当时(十年前)也许是一个好方法,但现在看来并非如此。

老版本的PHP在安装时会默认打开这两个功能,这会引起安全漏洞、编程错误及其他的问题。

如只有用户输入了数据时才会创建变量等。

PHP5.4.0开始这两个功能都被舍弃了,所以每个程序员都应该避免使用。

如果你过去的程序有使用这两项功能,那就尽快将其剔除吧。

3 函数

3.1 尽量使用PHP内部函数

内置函数使用C语言实现,并且经过PHP官方优化,效率更高。

3.2 使用绝对路径

includerequire中尽量使用绝对路径。

如果包含相对路径,PHP会在include_path里面遍历查找文件。

用绝对路径就会避免此类问题,解析路径所需的时间会更少。

3.3 包含文件

尽量不要用require_onceinclude_once包含文件,它们多一个判断文件是否被引用的过程,能不用尽量不用。

而使用requireinclude方法代替。

鸟哥在其博客中就多次声明,尽量不要用require_onceinclude_once

3.4 函数快于类方法

调用只有一个参数、并且函数体为空的函数,花费的时间等于7-8$localvar++运算。

而同一功能的类方法大约为15次$localvar++运算。

3.5 用子类方法

基类里面只放能重用的方法,其他功能尽量放在子类中实现,子类里方法的性能优于在基类中。

3.6 类的性能和其方法数量没有关系

新添加10个或多个方法到测试的类后,性能没什么差异。

3.7 读取文件内容

在可以用file_get_contents()替代file()fopen()feof()fgets()等系列方法的情况下,尽量用file_get_contents()

因为他的效率高得多!

3.8  引用传递参数

通过参数地址引用的方式,实现函数多个返回值,这比按值传递效率高。

方法是在参数变量前加个 &

3.9 方法不要细分得过多

仔细想想你真正打算重用的是哪些代码?

3.10 尽量静态化

如果一个方法能被静态,那就声明它为静态的,速度可提高1/4,甚至我测试的时候,这个提高了近三倍。

当然了,这个测试方法需要在十万级以上次执行,效果才明显。

其实,静态方法和非静态方法的效率主要区别在内存

静态方法在程序开始时生成内存,实例方法(非静态方法)在程序运行中生成内存。

所以,静态方法可以直接调用,实例方法要先成生实例再调用,静态速度很快,但是多了会占内存。

任何语言都是对内存和磁盘的操作,至于是否面向对象,只是软件层的问题,底层都是一样的,只是实现方法不同。

静态内存是连续的,因为是在程序开始时就生成了,而实例方法申请的是离散的空间,所以当然没有静态方法快。

静态方法始终调用同一块内存,其缺点就是不能自动进行销毁,而实例化可以销毁。

3.11 用C扩展方式实现

如果在代码中存在大量耗时的函数,可以考虑用C扩展的方式实现它们。

4 变量

4.1 及时销毁变量

数组对象GLOBAL变量在 PHP 中特别占内存的,这个由于 PHP 的底层的zend引擎引起的。

一般来说,PHP数组的内存利用率只有 1/10

也就是说,一个在C语言里面100M 内存的数组,在PHP里面就要1G。

特别是,在PHP作为后台服务器的系统中,经常会出现内存耗费太大的问题。

4.2 使用$_SERVER变量

如果你需要得到脚本执行的时间,$_SERVER['REQUSET_TIME']优于time()

一个是现成就可以直接用,一个还需要函数得出的结果。

4.3 方法里建立局部变量

在类的方法里建立局部变量速度最快,几乎和在方法里调用局部变量一样快。

4.4 局部变量比全局变量快

由于局部变量是存在栈中的。

当一个函数占用的栈空间不是很大的时候,这部分内存很有可能全部命中cache,CPU访问的效率是很高的。

相反,如果一个函数同时使用全局变量和局部变量,当这两段地址相差较大时,cpu cache需要来回切换,效率会下降。

4.5 局部变量而不是对象属性

建立一个对象属性(类里面的变量,例如:$this->prop++)比局部变量要慢3倍。

4.6 提前声明局部变量

建立一个未声明的局部变量,要比建立一个已经定义过的局部变量慢9-10倍。

4.7 谨慎声明全局变量

声明一个未被任何一个函数使用过的全局变量,也会使性能降低。

这和声明相同数量的局部变量一样,PHP可能去检查这个全局变量是否存在。

4.8 使用++$i递增

当执行变量$i的递增或递减时,$i++会比++$i慢一些。

这种差异是PHP特有的,并不适用于其他语言,所以请不要修改你的C或Java代码,并指望它们能立即变快,没用的。

++$i更快是因为它只需要3条指令(opcodes),$i++则需要4条指令。

后置递增实际上会产生一个临时变量,这个临时变量随后被递增。

而前置递增直接在原值上递增。

这是最优化处理的一种,正如Zend的PHP优化器所作的那样。

牢记,这个优化处理不失为一个好主意,因为不是所有的指令优化器都会做同样的优化处理。

4.9 不要随便复制变量

有时候为了使 PHP 代码更加整洁,一些 PHP 新手(包括我)会把预定义好的变量,复制到一个名字更简短的变量中。

其实这样做的结果是增加了一倍的内存消耗,只会使程序更加慢。

试想一下,在下面的例子中,如果用户恶意插入 512KB 字节的文字,就会导致 1MB 的内存被消耗!

// 不好的实践
$description = $_POST['description'];
echo $description;

// 好的实践
 echo $_POST['description'];

4.10 循环内部不要声明变量

尤其是大变量,这好像不只是PHP里面要注意的问题吧?

4.11 一定要对变量进行初始化

这里的“初始化”指的是“声明”。

当需要没有初始化的变量,PHP解释器会自动创建一个变量,但依靠这个特性来编程并不是一个好主意。

这会造成程序的粗糙,或者使代码变得另人迷惑。

因为你需要探寻这个变量是从哪里开始被创建的。

另外,对一个没有初始化的变量进行递增操作要比初始化过的来得慢。

所以对变量进行初始化会是个不错的主意。

5 数组

5.1 用字符串而不是数组作为参数

如果一个函数既能接受数组,又能接受简单字符做为参数,那么尽量用字符作为参数。

例如,字符替换函数,参数列表并不是太长,就可以考虑额外写一段替换代码。

使得每次传递参数都是一个字符,而不是接受数组做为查找和替换参数。

5.2 数组元素加引号

$row['id']$row[id]速度快7倍。

如果不带引号,例如$a[name],那么PHP会首先检查有没有define定义的name常量。

如果有,就用这个常量值作为数组键值。如果没有,再查找键值为字符串'name'的数组元素。

多了一个查找判断的过程,所以建议养成数组键名加引号的习惯。

正如上面字符串部分所述,用'又比用"速度更快。

5.3 多维数组操作

多维数组尽量不要循环嵌套赋值。

5.4 循环用foreach

尽量用foreach代替whilefor循环,效率更高。

6 架构

6.1 压缩输出

在php.ini中开启gzip压缩:

zlib.output_compression = On
zlib.output_compression_level = (level)

level可能是1-9之间的数字,你可以设置不同的数字。

几乎所有的浏览器都支持Gzip的压缩方式,gzip可以降低80%的输出.

付出的代价是,大概增加了10%的cpu计算量。

但是还是会赚到了,因为带宽减少了,页面加载会变得很快。

如果你使用apache,也可以激活mod_gzip模块。

6.2 静态化页面

Apache/Nginx解析一个PHP脚本的时间,要比解析一个静态HTML页面慢210倍。

所以尽量使页面静态化,或使用静态HTML页面。

6.3 将PHP升级到最新版

提高性能的最简单的方式是不断升级、更新PHP版本。

6.4 利用PHP的扩展

一直以来,大家都在抱怨PHP内容太过繁杂。

最近几年来,开发人员作出了相应的努力,移除了项目中的一些冗余特征。

即便如此,可用库以及其它扩展的数量还是很可观。

甚至一些开发人员开始考虑实施自己的扩展方案。

6.5 PHP缓存

一般情况下,PHP脚本被PHP引擎编译后执行,会被转换成机器语言,也称为操作码。

如果PHP脚本反复编译得到相同的结果,为什么不完全跳过编译过程呢?

PHP加速器缓存了编译后的机器码,允许代码根据要求立即执行,而不经过繁琐的编译过程。

对PHP开发人员而言,目前提供了两种可用的缓存方案。

一种是APC(Alternative PHP Cache,可选PHP缓存),它是一个可以通过PEAR安装的开源加速器。

另一种流行的方案是OPCode,也就是操作码缓存技术。

6.6 使用NoSQL缓存

Memchached或者Redis都可以。

这些是高性能的分布式内存对象缓存系统,能提高动态网络应用程序性能,减轻数据库的负担。

这对运算码 (OPcode)的缓存也很有用,使得脚本不必为每个请求重新编译。

MySQL存储引擎MyISAM与InnoDB的区别比较

使用MySQL当然会接触到MySQL的存储引擎,在新建数据库和新建数据表的时候都会看到。

MySQL默认的存储引擎是MyISAM,其他常用的就是InnoDB了。

至于到底用哪种存储引擎比较好?这个问题是没有定论的,需要根据你的需求和环境来衡量。所以对这两种引擎的概念、原理、异同和各自的优劣点有了详细的了解之后,再根据自己的情况选择起来就容易多了。

MyISAM InnoDB
存储结构 每张表被存放在三个文件

  1. frm – 表格定义
  2. MYD(MYData) – 数据文件
  3. MYI(MYIndex) – 索引文件
所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
存储空间 MyISAM可被压缩,存储空间较小 InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池,用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
事务安全 不支持,每次查询具有原子性 支持 具有事务、回滚和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
AUTO_INCREMENT MyISAM表可以和其他字段一起建立联合索引 InnoDB中必须包含只有该字段的索引
SELECT MyISAM更优
INSERT InnoDB更优
UPDATE InnoDB更优
DELETE InnoDB更优 它不会重新建立表,而是一行一行的删除
COUNT without WHERE MyISAM更优。因为MyISAM保存了表的具体行数 InnoDB没有保存表的具体行数,需要逐行扫描统计,就很慢了
COUNT with WHERE 一样 一样,InnoDB也会锁表
只支持表锁 支持表锁、行锁。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
外键 不支持 支持
FULLTEXT全文索引 支持 5.6+支持 可以通过使用Sphinx从InnoDB中获得全文索引,会慢一点

总的来说,MyISAM和InnoDB各有优劣,各有各的使用环境。

但是,InnoDB的设计目标是处理大容量数据库系统,它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。

我觉得使用InnoDB可以应对更为复杂的情况,特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询,从而提高整体性能。

MySQL索引入门简述

概述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MYSQL必须从第一条记录开始读完整个表,直到找出相关的行。表越大,花费的时间越多。但也不全是这样。本文讨论索引是什么,如何使用索引来改善性能,以及索引可能降低性能的情况。

索引的本质

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织)。所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引的存储分类

索引是在MYSQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引。

  • B-Tree 索引:最常见的索引类型,大部分引擎都支持B树索引。
  • HASH 索引:只有Memory引擎支持,使用场景简单。
  • R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
  • Full-Text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。

MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引,例如标题title字段,可以只取title的前10个字符进行索引,这个特性可以大大缩小索引文件的大小,但前缀索引也有缺点,在排序Order By和分组Group By操作的时候无法使用。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
语法:create index idx_title on film (title(10))

MyISAM、InnoDB、Memory三个常用引擎对索引的支持比较:

索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-Tree 索引 支持 不支持 不支持
Full-Text 索引 支持 支持(5.6+) 不支持

B-TREE索引类型

  • 普通索引
    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
    (1)创建索引: CREATE INDEX 索引名 ON 表名(列名1, 列名2, ...);
    (2)修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1, 列名2, ...);
    (3)创建表时指定索引:CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1, 列名2, ...) );
  • UNIQUE索引
    表示唯一的,不允许重复的索引,如果该字段信息保证不会重复,例如身份证号,可设置为unique:
    (1)创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列名1, 列名2, ...);
    (2)修改表:ALTER TABLE 表名 ADD UNIQUE 索引名 (列名1, 列名2, ...);
    (3)创建表时指定索引:CREATE TABLE 表名( [...], UNIQUE 索引名 (列名1, 列名2, ...) );
  • 主键索引(PRIMARY KEY)
    主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。
    (1)主键一般在创建表的时候指定:CREATE TABLE 表名( [...], PRIMARY KEY (列名1, 列名2, ...) );
    (2)也可以通过修改表的方式加入主键:ALTER TABLE 表名 ADD PRIMARY KEY (列名1, 列名2, ...);
    每个表只能有一个主键。 (主键相当于聚合索引,是查找最快的索引)
    注:不能用CREATE INDEX语句创建PRIMARY KEY索引

索引的设置语法

设置索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEXALTER TABLE来为表增加索引。

1.ALTER TABLE 来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);    # 创建通索引
ALTER TABLE table_name ADD UNIQUE (column_list);              # 创建UNIQUE唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list);         # 创建PRIMARY KEY主键索引

2.CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list);          # 普通索引
CREATE UNIQUE INDEX index_name ON table_name (column_list);   # UNIQUE唯一索引

删除索引

可利用ALTER TABLEDROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

查看索引

mysql> show index from tblname;
mysql> show keys from tblname;
  • Table:表的名称
  • Non_unique:如果索引不能包括重复词,则为0。如果可以,则为1
  • Key_name:索引的名称
  • Seq_in_index:索引中的列序列号,从1开始
  • Column_name:列名称
  • Collation:列以什么方式存储在索引中。在MySQL中,有值A(升序)或NULL(无分类)。
  • Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLEmyisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
  • Sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL
  • Packed:指示关键字如何被压缩。如果没有被压缩,则为NULL
  • Null:如果列含有NULL,则含有YES。如果没有,则该列含有NO
  • Index_type:索引方法(BTREE, FULLTEXT, HASH, RTREE)。
  • Comment:更多评注。

索引选择性

索引选择原则

  1. 较频繁的作为查询条件的字段应该创建索引
  2. 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
  3. 更新非常频繁的字段不适合创建索引

    当然,并不是存在更新的字段就适合创建索引,从判定策略的用语上也可以看出,是”非常频繁”的字段。到底什么样的更新频率应该算是”非常频繁”呢?每秒?每分钟?还是每小时呢?说实话,还真难定义。很多时候是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断的,如果通过该字段的查询并不是很多,可能几个小时或是更长才会执行一次,更新反而比查询更频繁,那这样的字段肯定不适合创建索引。反之,如果我们通过该字段的查询比较频繁,但更新并不是特别多,比如查询几十次或更多才可能会产生一次更新,那我个人觉得更新所带来的附加成本也是可以接受的。

  4. 不会出现在 WHERE 子句中的字段不该创建索引

索引选择原则细述

  • 性能优化过程中,选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有两种类型的列:在where子句中出现的列,在join子句中出现的列,而不是在SELECT关键字后选择列表的列
  • 索引列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同的值,很容易区分行,而用来记录性别的列,只有”M”和”F”,则对此进行索引没有多大用处,因此不管搜索哪个值,都会得出大约一半的行,(见索引选择性注意事项对选择性解释;)
  • 使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度;

    例如,有一个CHAR(200)列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO较少,较短的值比较起来更快。更为重要的是,对于较短的键值,所以高速缓存中的快能容纳更多的键值,因此,MYSQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多快的可能性。

  • 利用最左前缀

索引选择注意事项

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。

一般两种情况下不建议建索引:

  1. 表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了;
    至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。
  2. 索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:
     Index Selectivity = Cardinality / #T

    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,上文用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.0000 |
    +-------------+
    

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

  3. MySQL只对一下操作符才使用索引<<==>>=betweenin, 以及某些时候的like(不以通配符%_开头的情形)。
  4. 不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

索引的弊端

索引的益处已经清楚了,但是我们不能只看到这些益处,并认为索引是解决查询优化的圣经,只要发现 查询运行不够快就将 WHERE 子句中的条件全部放在索引中。

确实,索引能够极大地提高数据检索效率,也能够改善排序分组操作的性能,但有不能忽略的一个问题就是,索引是完全独立于基础数据之外的一部分数据。假设在表 news 中的列 content 创建了索引 idx_news_content,那么任何更新列 content 的操作,都须要更新列 content 的索引数据,调整因为更新带来键值变化的索引信息。而如果没有对列 content 进行索引,MySQL要做的仅仅是更新表中列 content 的信息。这样,最明显的资源消耗就是增加了更新所带来的 IO 量,以及调整索引所致的计算量。此外,列 content 的索引 idx_news_content 须要占用存储空间,而且随着表 content 数据量的增加,idx_news_content 所占用的空间也会不断增加,所以索引还会带来存储空间资源消耗的增加。