189 8069 5689
关于mysql处理百万级以上的数据时如何提高其查询速度的方法
创新互联公司主营岷县网站建设的网络公司,主营网站建设方案,app开发定制,岷县h5小程序设计搭建,岷县网站营销推广欢迎岷县等地区企业咨询
最近一段时间由于工作需要，开始关注针对Mysql数据库的select查询语句的相关优化方法。
由于在参与的实际项目中发现当mysql表的数据量达到百万级时，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。曾经测试对一个包含400多万条记录（有索引）的表执行一条条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。以下是网上流传比较广泛的30种SQL查询语句优化方法：
1、应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。
2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order 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
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5、下面的查询也将导致全表扫描：(不能前置百分号)
select id from t where name like ‘%c%’
若要提高效率，可以考虑全文检索。
6、in 和 not 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查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
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、尽量避免大事务操作，提高系统并发能力。
php安全篇值过滤用户输入的人参数
规则 1：绝不要信任外部数据或输入
关于Web应用程序安全性，必须认识到的第一件事是不应该信任外部数据。外部数据(outside data) 包括不是由程序员在PHP代码中直接输入的任何数据。在采取措施确保安全之前，来自任何其他来源(比如 GET 变量、表单 POST、数据库、配置文件、会话变量或 cookie)的任何数据都是不可信任的。
例如，下面的数据元素可以被认为是安全的，因为它们是在PHP中设置的。
复制代码 代码如下:
?php
$myUsername = 'tmyer';
$arrayUsers = array('tmyer', 'tom', 'tommy');define(”GREETING”, 'hello there' . $myUsername);?
但是，下面的数据元素都是有瑕疵的。
清单 2. 不安全、有瑕疵的代码
复制代码 代码如下:
?php
$myUsername = $_POST['username']; //tainted!
$arrayUsers = array($myUsername, 'tom', 'tommy'); //tainted!
define(”GREETING”, 'hello there' . $myUsername); //tainted!
?
为 什么第一个变量 $myUsername 是有瑕疵的？因为它直接来自表单 POST。用户可以在这个输入域中输入任何字符串，包括用来清除文件或运行以前上传的文件的恶意命令。您可能会问，“难道不能使用只接受字母 A-Z 的客户端（Javascrīpt）表单检验脚本来避免这种危险吗？”是的，这总是一个有好处的步骤，但是正如在后面会看到的，任何人都可以将任何表单下载 到自己的机器上，修改它，然后重新提交他们需要的任何内容。
解决方案很简单：必须对 $_POST['username'] 运行清理代码。如果不这么做，那么在使用 $myUsername 的任何其他时候（比如在数组或常量中），就可能污染这些对象。
对用户输入进行清理的一个简单方法是，使用正则表达式来处理它。在这个示例中，只希望接受字母。将字符串限制为特定数量的字符，或者要求所有字母都是小写的，这可能也是个好主意。
清单 3. 使用户输入变得安全
复制代码 代码如下:
?php
$myUsername = cleanInput($_POST['username']); //clean!
$arrayUsers = array($myUsername, 'tom', 'tommy'); //clean!
define(”GREETING”, 'hello there' . $myUsername); //clean!
function cleanInput($input){
$clean = strtolower($input);
$clean = preg_replace(”/[^a-z]/”, “”, $clean);$clean = substr($clean,0,12);
return $clean;
}
?
规则 2：禁用那些使安全性难以实施的 PHP 设置已经知道了不能信任用户输入，还氏碰渣应该知道不应该歼悄信任机器上配置 PHP 的方式。例如，要吵晌确保禁用 register_globals。如果启用了 register_globals，就可能做一些粗心的事情，比如使用 $variable 替换同名的 GET 或 POST 字符串。通过禁用这个设置，PHP 强迫您在正确的名称空间中引用正确的变量。要使用来自表单 POST 的变量，应该引用 $_POST['variable']。这样就不会将这个特定变量误会成 cookie、会话或 GET 变量。
规则 3：如果不能理解它，就不能保护它
一些开发人员使用奇怪的语法，或者将语句组织得很紧凑，形成简短但是含义模糊的代码。这种方式可能效率高，但是如果您不理解代码正在做什么，那么就无法决定如何保护它。
例如，您喜欢下面两段代码中的哪一段？
清单 4. 使代码容易得到保护
复制代码 代码如下:
?php
//obfuscated code
$input = (isset($_POST['username']) ? $_POST['username']:”);//unobfuscated code
$input = ”;
if (isset($_POST['username'])){
$input = $_POST['username'];
}else{
$input = ”;
}
?
在第二个比较清晰的代码段中，很容易看出 $input 是有瑕疵的，需要进行清理，然后才能安全地处理。
规则 4：“纵深防御” 是新的法宝
本教程将用示例来说明如何保护在线表单，同时在处理表单的 PHP 代码中采用必要的措施。同样，即使使用 PHP regex 来确保 GET 变量完全是数字的，仍然可以采取措施确保 SQL 查询使用转义的用户输入。
纵深防御不只是一种好思想，它可以确保您不会陷入严重的麻烦。
既然已经讨论了基本规则，现在就来研究第一种威胁：SQL 注入攻击。
防止 SQL 注入攻击
在 SQL 注入攻击 中，用户通过操纵表单或 GET 查询字符串，将信息添加到数据库查询中。例如，假设有一个简单的登录数据库。这个数据库中的每个记录都有一个用户名字段和一个密码字段。构建一个登录表单，让用户能够登录。
清单 5. 简单的登录表单
复制代码 代码如下:
html
head
titleLogin/title
/head
body
form action=”verify.php” method=”post”
plabel for='user'Username/label
input type='text' name='user' id='user'/
/p
plabel for='pw'Password/label
input type='password' name='pw' id='pw'/
/p
pinput type='submit' value='login'//p
/form
/body
/html
这个表单接受用户输入的用户名和密码，并将用户输入提交给名为 verify.php 的文件。在这个文件中，PHP 处理来自登录表单的数据，如下所示：
清单 6. 不安全的 PHP 表单处理代码
复制代码 代码如下:
?php
$okay = 0;
$username = $_POST['user'];
$pw = $_POST['pw'];
$sql = “select count(*) as ctr from users where username='”.$username.”' and password='”. $pw.”' limit 1″;$result = mysql_query($sql);
while ($data = mysql_fetch_object($result)){if ($data-ctr == 1){
//they're okay to enter the application!
$okay = 1;
}
}
if ($okay){
$_SESSION['loginokay'] = true;
header(”index.php”);
}else{
header(”login.php”);
}
?
这 段代码看起来没问题，对吗？世界各地成百（甚至成千）的 PHP/MySQL 站点都在使用这样的代码。它错在哪里？好，记住 “不能信任用户输入”。这里没有对来自用户的任何信息进行转义，因此使应用程序容易受到攻击。具体来说，可能会出现任何类型的 SQL 注入攻击。
例如，如果用户输入 foo 作为用户名，输入 ' or '1′='1 作为密码，那么实际上会将以下字符串传递给 PHP，然后将查询传递给 MySQL：
复制代码 代码如下:
?php
$sql = “select count(*) as ctr from users where username='foo' and password=” or '1′='1′ limit 1″;?
这个查询总是返回计数值 1，因此 PHP 会允许进行访问。通过在密码字符串的末尾注入某些恶意 SQL，黑客就能装扮成合法的用户。
解 决这个问题的办法是，将 PHP 的内置 mysql_real_escape_string() 函数用作任何用户输入的包装器。这个函数对字符串中的字符进行转义，使字符串不可能传递撇号等特殊字符并让 MySQL 根据特殊字符进行操作。清单 7 展示了带转义处理的代码。
清单 7. 安全的 PHP 表单处理代码
复制代码 代码如下:
?php
$okay = 0;
$username = $_POST['user'];
$pw = $_POST['pw'];
$sql = “select count(*) as ctr from users where username='”.mysql_real_escape_string($username).”' and password='”. mysql_real_escape_string($pw).”' limit 1″;$result = mysql_query($sql);
while ($data = mysql_fetch_object($result)){if ($data-ctr == 1){
//they're okay to enter the application!
$okay = 1;
}
}
if ($okay){
$_SESSION['loginokay'] = true;
header(”index.php”);
}else{
header(”login.php”);
}
?
使用 mysql_real_escape_string() 作为用户输入的包装器，就可以避免用户输入中的任何恶意 SQL 注入。如果用户尝试通过 SQL 注入传递畸形的密码，那么会将以下查询传递给数据库：
select count(*) as ctr from users where username='foo' and password='\' or \'1\'=\'1′ limit 1″数据库中没有任何东西与这样的密码匹配。仅仅采用一个简单的步骤，就堵住了 Web 应用程序中的一个大漏洞。这里得出的经验是，总是应该对 SQL 查询的用户输入进行转义。
但是，还有几个安全漏洞需要堵住。下一项是操纵 GET 变量。
防止用户操纵 GET 变量
在前一节中，防止了用户使用畸形的密码进行登录。如果您很聪明，应该应用您学到的方法，确保对 SQL 语句的所有用户输入进行转义。
但 是，用户现在已经安全地登录了。用户拥有有效的密码，并不意味着他将按照规则行事 —— 他有很多机会能够造成损害。例如，应用程序可能允许用户查看特殊的内容。所有链接指向 template.php?pid=33 或 template.php?pid=321 这样的位置。URL 中问号后面的部分称为查询字符串。因为查询字符串直接放在 URL 中，所以也称为 GET 查询字符串。
在 PHP 中，如果禁用了 register_globals，那么可以用 $_GET['pid'] 访问这个字符串。在 template.php 页面中，可能会执行与清单 8 相似的操作。
清单 8. 示例 template.php
复制代码 代码如下:
?php
$pid = $_GET['pid'];
//we create an object of a fictional class Page$obj = new Page;
$content = $obj-fetchPage($pid);
//and now we have a bunch of PHP that displays the page?
这 里有什么错吗？首先，这里隐含地相信来自浏览器的 GET 变量 pid 是安全的。这会怎么样呢？大多数用户没那么聪明，无法构造出语义攻击。但是，如果他们注意到浏览器的 URL 位置域中的 pid=33，就可能开始捣乱。如果他们输入另一个数字，那么可能没问题；但是如果输入别的东西，比如输入 SQL 命令或某个文件的名称（比如 /etc/passwd），或者搞别的恶作剧，比如输入长达 3,000 个字符的数值，那么会发生什么呢？
在这种情况下，要记住基本规则，不要信任用户输入。应用程序开发人员知道 template.php 接受的个人标识符（PID）应该是数字，所以可以使用 PHP 的 is_numeric()函数确保不接受非数字的 PID，如下所示：
清单 9. 使用 is_numeric() 来限制 GET 变量复制代码 代码如下:
?php
$pid = $_GET['pid'];
if (is_numeric($pid)){
//we create an object of a fictional class Page$obj = new Page;
$content = $obj-fetchPage($pid);
//and now we have a bunch of PHP that displays the page}else{
//didn't pass the is_numeric() test, do something else!
}
?
这个方法似乎是有效的，但是以下这些输入都能够轻松地通过 is_numeric() 的检查：
100 （有效）
100.1 （不应该有小数位）
+0123.45e6 （科学计数法 —— 不好）
0xff33669f （十六进制 —— 危险！危险！）那么，有安全意识的 PHP 开发人员应该怎么做呢？多年的经验表明，最好的做法是使用正则表达式来确保整个 GET 变量由数字组成，如下所示：
清单 10. 使用正则表达式限制 GET 变量
复制代码 代码如下:
?php
$pid = $_GET['pid'];
if (strlen($pid)){
if (!ereg(”^[0-9]+$”,$pid)){
//do something appropriate, like maybe logging them out or sending them back to home page}
}else{
//empty $pid, so send them back to the home page}
//we create an object of a fictional class Page, which is now//moderately protected from evil user input$obj = new Page;
$content = $obj-fetchPage($pid);
//and now we have a bunch of PHP that displays the page?
需 要做的只是使用 strlen() 检查变量的长度是否非零；如果是，就使用一个全数字正则表达式来确保数据元素是有效的。如果 PID 包含字母、斜线、点号或任何与十六进制相似的内容，那么这个例程捕获它并将页面从用户活动中屏蔽。如果看一下 Page 类幕后的情况，就会看到有安全意识的 PHP 开发人员已经对用户输入 $pid 进行了转义，从而保护了 fetchPage() 方法，如下所示：
清单 11. 对 fetchPage() 方法进行转义
复制代码 代码如下:
?php
class Page{
function fetchPage($pid){
$sql = “select pid,title,desc,kw,content,status from page where pid='”.mysql_real_escape_string($pid).”'”;}
}
?
您可能会问，“既然已经确保 PID 是数字，那么为什么还要进行转义？” 因为不知道在多少不同的上下文和情况中会使用 fetchPage() 方法。必须在调用这个方法的所有地方进行保护，而方法中的转义体现了纵深防御的意义。
如 果用户尝试输入非常长的数值，比如长达 1000 个字符，试图发起缓冲区溢出攻击，那么会发生什么呢？下一节更详细地讨论这个问题，但是目前可以添加另一个检查，确保输入的 PID 具有正确的长度。您知道数据库的 pid 字段的最大长度是 5 位，所以可以添加下面的检查。
清单 12. 使用正则表达式和长度检查来限制 GET 变量复制代码 代码如下:
?php
$pid = $_GET['pid'];
if (strlen($pid)){
if (!ereg(”^[0-9]+$”,$pid) strlen($pid) 5){//do something appropriate, like maybe logging them out or sending them back to home page}
} else {
//empty $pid, so send them back to the home page}
//we create an object of a fictional class Page, which is now//even more protected from evil user input$obj = new Page;
$content = $obj-fetchPage($pid);
//and now we have a bunch of PHP that displays the page?
现在，任何人都无法在数据库应用程序中塞进一个 5,000 位的数值 —— 至少在涉及 GET 字符串的地方不会有这种情况。想像一下黑客在试图突破您的应用程序而遭到挫折时咬牙切齿的样子吧！而且因为关闭了错误报告，黑客更难进行侦察。
缓冲区溢出攻击
缓冲区溢出攻击 试图使 PHP 应用程序中（或者更精确地说，在 Apache 或底层操作系统中）的内存分配缓冲区发生溢出。请记住，您可能是使用 PHP 这样的高级语言来编写 Web 应用程序，但是最终还是要调用 C（在 Apache 的情况下）。与大多数低级语言一样，C 对于内存分配有严格的规则。
缓冲区溢出攻击向缓冲区发送大量数据，使部分数据溢出到相邻的内存缓冲区，从而破坏缓冲区或者重写逻辑。这样就能够造成拒绝服务、破坏数据或者在远程服务器上执行恶意代码。
防止缓冲区溢出攻击的惟一方法是检查所有用户输入的长度。例如，如果有一个表单元素要求输入用户的名字，那么在这个域上添加值为 40 的 maxlength 属性，并在后端使用 substr() 进行检查。清单 13 给出表单和 PHP 代码的简短示例。
大数据的话可以进行以下操作：宴尘圆
减少对数据库的读取，也就是减少调用数据库，
进行数据缓存，
利用数据库的自身兄团优化技术，如索引等
精确查询条件，晌塌有利于提高查找速度