<?php
session_start();
error_reporting(0);
header("Content-type: text/html; charset=utf-8");
require "config.php";
@date_default_timezone_set(PRC);
set_time_limit(0);
@ob_end_clean();
ob_implicit_flush(true);
switch($_GET['act']){
case "database":
if(empty($_SESSION['member'])){
exit('var database=new Array("login");');
}
$connect_db = mysql_connect($dbnhost, $dbnuser, $dbnpass);
$select_db = mysql_select_db($dbname, $connect_db);
mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET CHARACTER_SET_RESULTS=UTF8");
$rs = mysql_query("SHOW TABLES FROM $dbname");
$tables = array();
while ($row = mysql_fetch_row($rs)) {
$tables[] = $row[0];
}
mysql_free_result($rs);
$array_tj=count($tables);
$count=1;
$text="";
foreach($tables as $key=>$tableName){
if($key==count($tables)-1){
$dian="";
}else{
$dian=",";
}
$text=$text.'"'.$tableName.'"'.$dian;
$count++;
}
echo "var database = new Array($text);";
break;
case "select":
if(empty($_SESSION['member'])){
echo "cnrv_msg(\"请登录\");addRow(\"登录后查询\",\"登录后查询\",\"登录后查询\",\"登录后查询\");";
exit;
}
$select_act=(int)addslashes(trim($_POST['select_act']));
$match_act=(int)addslashes(trim($_POST['match_act']));
$key=addslashes(trim($_POST['key']));
$table=addslashes(trim($_POST['table']));
if(empty($key) || $key==''){exit("请输入查询内容");}
if(strlen($key)<4){exit("key length!!!");}
$key = str_replace("_","\_",$key);
$key = str_replace("%","\%",$key);
switch($match_act){
case 2:$key = '=\''.$key.'\'';break;
case 1:$key = ' like \''.$key.'%\'';break;
default:exit("fuck you!");
}
switch($select_act){//查询方式
case 1:$limits="username".$key;break;
case 2:$limits="email".$key;break;
case 3:$limits="username".$key."or email".$key;break;
default:exit("fuck you!");
}
$connect_db = mysql_connect($dbnhost, $dbnuser, $dbnpass);
$select_db = mysql_select_db($dbname, $connect_db);
mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET CHARACTER_SET_RESULTS=UTF8");
$sql="select $Field from `$table` where $limits LIMIT 20";
require "database.php";
$databasename=database($table);
if($result=mysql_query($sql)){
while($rows=mysql_fetch_assoc($result)){
$username= mysql_real_escape_string($rows['username']);
$email= mysql_real_escape_string($rows['email']);
$password= mysql_real_escape_string($rows['password']);
echo "addRow(\"$username\",\"$email\",\"$password\",\"$databasename\");";
}// end while
}
break;
default:print_r("fuck you!");
}
索引FULLTEXT建立了。还是慢。几千万就用了30~配置双核2g内存ssd
session_start();
error_reporting(0);
header("Content-type: text/html; charset=utf-8");
require "config.php";
@date_default_timezone_set(PRC);
set_time_limit(0);
@ob_end_clean();
ob_implicit_flush(true);
switch($_GET['act']){
case "database":
if(empty($_SESSION['member'])){
exit('var database=new Array("login");');
}
$connect_db = mysql_connect($dbnhost, $dbnuser, $dbnpass);
$select_db = mysql_select_db($dbname, $connect_db);
mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET CHARACTER_SET_RESULTS=UTF8");
$rs = mysql_query("SHOW TABLES FROM $dbname");
$tables = array();
while ($row = mysql_fetch_row($rs)) {
$tables[] = $row[0];
}
mysql_free_result($rs);
$array_tj=count($tables);
$count=1;
$text="";
foreach($tables as $key=>$tableName){
if($key==count($tables)-1){
$dian="";
}else{
$dian=",";
}
$text=$text.'"'.$tableName.'"'.$dian;
$count++;
}
echo "var database = new Array($text);";
break;
case "select":
if(empty($_SESSION['member'])){
echo "cnrv_msg(\"请登录\");addRow(\"登录后查询\",\"登录后查询\",\"登录后查询\",\"登录后查询\");";
exit;
}
$select_act=(int)addslashes(trim($_POST['select_act']));
$match_act=(int)addslashes(trim($_POST['match_act']));
$key=addslashes(trim($_POST['key']));
$table=addslashes(trim($_POST['table']));
if(empty($key) || $key==''){exit("请输入查询内容");}
if(strlen($key)<4){exit("key length!!!");}
$key = str_replace("_","\_",$key);
$key = str_replace("%","\%",$key);
switch($match_act){
case 2:$key = '=\''.$key.'\'';break;
case 1:$key = ' like \''.$key.'%\'';break;
default:exit("fuck you!");
}
switch($select_act){//查询方式
case 1:$limits="username".$key;break;
case 2:$limits="email".$key;break;
case 3:$limits="username".$key."or email".$key;break;
default:exit("fuck you!");
}
$connect_db = mysql_connect($dbnhost, $dbnuser, $dbnpass);
$select_db = mysql_select_db($dbname, $connect_db);
mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET CHARACTER_SET_RESULTS=UTF8");
$sql="select $Field from `$table` where $limits LIMIT 20";
require "database.php";
$databasename=database($table);
if($result=mysql_query($sql)){
while($rows=mysql_fetch_assoc($result)){
$username= mysql_real_escape_string($rows['username']);
$email= mysql_real_escape_string($rows['email']);
$password= mysql_real_escape_string($rows['password']);
echo "addRow(\"$username\",\"$email\",\"$password\",\"$databasename\");";
}// end while
}
break;
default:print_r("fuck you!");
}
索引FULLTEXT建立了。还是慢。几千万就用了30~配置双核2g内存ssd
error_reporting(0);
header("Content-type: text/html; charset=utf-8");
require "config.php";
require "database.php";
$connect_db = mysql_connect($dbnhost, $dbnuser, $dbnpass);
$select_db = mysql_select_db($dbname, $connect_db);
mysql_query("SET NAMES 'UTF8'"); set_time_limit(0); if($_GET['act']=="database"){
if (empty($_SESSION['member'])) {
exit('var database=new Array("login");');
}
$rs = mysql_query("SHOW TABLES FROM $dbname");
$tables = array();
while ($row = mysql_fetch_row($rs)) {
$tables[] ="'". $row[0]."'";
}
$text= join(",", $tables);
echo "var database = new Array($text);";
}
if($_GET['act']=='select'){
if (empty($_SESSION['member'])) {
echo "cnrv_msg(\"请登录\");addRow(\"登录后查询\",\"登录后查询\",\"登录后查询\",\"登录后查询\");";
exit;
}
$select_act = (int) addslashes(trim($_POST['select_act']));
$match_act = (int) addslashes(trim($_POST['match_act']));
$key = addslashes(trim($_POST['key']));
$table = addslashes(trim($_POST['table']));
if (empty($key) || $key == '') {
exit("请输入查询内容");
}
if (strlen($key) < 4) {
exit("key length!!!");
}
$key= str_replace(array("_","%"),array("\_","\%"), $key);
if($match_act==1){
$key="like $key%";
}elseif($match_act==2){
$key ="='$key'";
}
switch ($select_act) {//查询方式
case 1:$limits = "username" . $key;
break;
case 2:$limits = "email" . $key;
break;
case 3:$limits = "username" . $key . "or email" . $key;
break;
}
//$Field
$sql = "select username,email,`password`,databasename from `$table` where $limits LIMIT 20";
$addRow="";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
while ($rows = mysql_fetch_assoc($result)) {
$addRow.= "addRow(\"{$row['username']}\",\"{$row['email']}\",\"{$row['password']}\",\"{$row['databasename']}\");";
}// end while
}
echo $addRow;
}
第二:无用的代码太多
第三:重复代码
2.合理优化代码
%name% 这样的用不到索引 name%这样的可以用到
%name% 这样的用不到索引 name%这样的可以用到告诉我哪里需要改动好吗
你的SQL最终拼装出来是
select username,email,`password`,databasename from `$table` where username like '$key%' or email like '$key%' LIMIT 20
那么你把这个SQL拿出来在执行并explain一下就知道原因了。另外,你的记录上千万条,那么limit 可能会导致查询变慢,建议分表或则分区
like '$key%' 但只能检索到以 $key 开头的记录
2、既然有分页,LIMIT 20
那就没必要每个表都 select 了,一个表的不够,再 select 下一个表
3、可以一次性将所有的 username,email,`password`,databasename 导入内存表,虽然要花些时间,但因为没有输出,不会高于 30~
只要你不重启系统,则之后的查询都可在瞬间完成