DataBase
mysql backup
usage
#mysqldump [options] db_name [tables]
#mysqldump [options] –databases db_name1 [db_name2 db_name3...]
#mysqldump [options] –all-databases
Example
#mysqldump -u root -p mydatabase > db.sql
Specific table
#mysqldump -u root -p mydatabase -tables customer > db.sql
How to restore database
#mysql -u root -p database < db.sql
If want to restore to specific database
mysql -u root -pMyPassword MyDatabase < db.sql
MYSQL limit Optimization-2
The MYSQL optimization is very important. Other most commonly used also most need to optimize are limit. mysql limit has brought enormous convenient to the paging, but the data quantity one is big, the limit performance suddenly drops.
Similarly takes 10 data
select * from temp limit 10000,10
and
select * from temp limit 0,10
It is not a quantity rank.
Now many about limit five optimized criterion, is from the mysql handbook ,You may have a look at this article about Mysql limit:http://www.easemarry.com/blog/mysql-limit-optimization/
In the article uses limit directly, but first gains to offset id then uses limit size to gain the data directly. According to his data, is friends with obviously in uses limit directly. Here my concrete service data is divided two kind of situations to carry on the test.
1st, offset small time.
select * from temp limit 10,10
Repeatedly running , the time maintains between 0.0004-0.0005
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
Repeatedly running , the time maintains between 0.0005-0.0006
Conclusion: offset is small, uses limit to be superior directly. This is the sub-inquiry reason obviously.
2nd, offset great time.
select * from temp limit 10000,10
Repeatedly running , the time maintains between 0.0185-0.0190
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10
Repeatedly running , the time maintains between 0.0061-0.0065,Only the former 1/3. May estimate that offset is bigger, the latter is more superior.
http://www.easemarry.com/blog
MYSQL limit Optimization
select * from table LIMIT 5,10; #return to 6-15th line
select * from table LIMIT 5; #return to 0-5 line
select * from table LIMIT 0,5; #return to 0-5 line
Performance optimization:
In MySQL5.0 limit high performance.
1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;
2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;
Similarly takes 90000 latter 100 records, 1st quickly 2nd quick?
1st has preoccupied the first 90001 records, takes is biggest a ID value to take outset marking, then uses it to be possible fast localization next 100 record
Very obvious 2nd wins. Before looking like limit probably looks like me, not completely imagines such makes the entire table scanning to return to limit the offset+length strip record, this way limit compares MS-SQL Top the performance to enhance many.
Actually 2nd definitely may revise:
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;
Uses the 90000th record directly ID, does not need to undergo the Max operation, because like this makes theoretically the efficiency this high somewhat, but does not look at the effect nearly in the actual use, because itself locates the ID returns is 1 record, Max does not need the operation to be able nearly to obtain the result.
But, since MySQL has limit to be possible the positive governing to take out the record the position, why not simply uses Select * From cyclopedia limit 90000,1? To be how could it not be more succinct? wanted mistakenly, to try like this to know that the result was: 1 row in set (8.88) sec, what kind, suffices scary, let me remember yesterday compared this in 4.1 also to have ” the high score “. Select * should better not use casually, must in line with use anything, chooses anything the principle, the Select field to be more, the field data quantity is bigger, the speed is slower. Have above 2 kind of paging mode which kind 1 strong been more than Shan Xiezhei, although looks like the number of times which probably inquires to be more some, but in fact has received in exchange for the highly effective performance by the small price, was worth.
The 1st kind of plan may use in MS-SQL similarly, moreover possibly is best. Because locates the initial sector depending on principal linkage ID to be always quickest.
Select Top 100 * From cyclopedia Where ID>=(
Select Top 90001 Max(ID) From (
Select ID From cyclopedia Order By ID
) As tmp
)
But no matter realizes the way to PROCEDURE or code, the bottleneck lies in MS-SQL TOP always to throughout return to the first N record, this kind of situation when the data quantity is not big feels not deeply, but if hundred and thousand of ten thousand, the efficiency can definitely be low. Comparatively MySQL limit has superiority many, the execution:
Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1
result respectively is:
90000 rows in set (0.36) sec
1 row in set (0.06) sec
But MS-SQL can only use Select Top 90000 ID From the cyclopedia execution time is 390ms, carries out the similar operating time also to be inferior to MySQL 360ms.
Oracle class – PHP
<?php
class DB_Sql {
var $Debug = false;
var $Home = “/u01/app/oracle/ora90″;
var $Remote = 1;
/**
* http://www.easemarry.com/blog
* This Query will be sent directly after the first connection
* Example:
* var $ConnectQuery=”ALTER SESSION SET nls_date_language=german nls_date_format=’DD.MM.RRRR’”;
* -> Set the date format for this session, this is fine when your ora-role
* cannot be altered
*/
var $ConnectQuery=”;
/**
* Due to a strange error with Oracle 9i, Apache and PHP4
* you don’t need to set the ENV – on my system Apache will change to a zombie, if I don’t set this to FALSE!
* Instead I set these ENV-vars before the startup of apache.
* If unsure try it out, if it works.�
.*/
var $OraPutEnv = true;
var $Database = “”;
var $User = “”;
var $Password = “”;
var $Link_ID = 0;
var $Query_ID = 0;
var $Record = array();
var $Row;
var $Errno = 0;
var $Error = “”;
var $ora_no_next_fetch=false;
/* copied from db_mysql for completeness */
/* public: identification constant. never change this. */
var $type = “oracle”;
var $revision = “Revision: 1.3″;
var $Halt_On_Error = “yes”; ## “yes” (halt with message), “no” (ignore errors quietly), “report” (ignore errror, but spit a warning)
/* public: constructor */
function DB_Sql($query = “”) {
$this->query($query);
}
/* public: some trivial reporting */
function link_id() {
return $this->Link_ID;
}
function query_id() {
return $this->Query_ID;
}
function connect() {
## see above why we do this
if ($this->OraPutEnv) {
PutEnv(”ORACLE_SID=$this->Database”);
PutEnv(”ORACLE_HOME=$this->Home”);
}
if ( 0 == $this->Link_ID ) {
if($this->Debug) {
printf(”<br>Connect()ing to $this->Database…<br>n”);
}
if($this->Remote) {
if($this->Debug) {
printf(”<br>connect() $this->User/******@$this->Database<br>n”);
}
$this->Link_ID=ora_plogon
(”$this->User/$this->Password@$this->Database”,”");
/************** (comment by SSilk)
this dosn’t work on my system:
$this->Link_ID=ora_plogon
(”$this->User@$this->Database.world”,”$this->Password”);
***************/
} else {
if($this->Debug) {
printf(”<br>connect() $this->User, $this->Password <br>n”);
}
$this->Link_ID=ora_plogon(”$this->User”,”$this->Password”);
/* (comment by SSilk: don’t know how this could work, but I leave this untouched!) */
}
if($this->Debug) {
printf(”<br>connect() Link_ID: $this->Link_ID<br>n”);
}
if (!$this->Link_ID) {
$this->halt(”connect() Link-ID == false ” .
”($this->Link_ID), ora_plogon failed”);
} else {
//echo “commit on<p>”;
ora_commiton($this->Link_ID);
}
if($this->Debug) {
printf(”<br>connect() Obtained the Link_ID: $this->Link_ID<br>n”);
}
## Execute Connect Query
if ($this->ConnectQuery) {
$this->query($this->ConnectQuery);
}
}
}
## In order to increase the # of cursors per system/user go edit the
## init.ora file and increase the max_open_cursors parameter. Yours is on
## the default value, 100 per user.
## We tried to change the behaviour of query() in a way, that it tries
## to safe cursors, but on the other side be carefull with this, that you
## don’t use an old result.
##
## You can also make extensive use of ->disconnect()!
## The unused QueryIDs will be recycled sometimes.
function query($Query_String)
{
/* No empty query please. */
if (empty($Query_String))
{
return 0;
}
$this->connect();
$this->lastQuery=$Query_String;
if (!$this->Query_ID) {
$this->Query_ID= ora_open($this->Link_ID);
}
if($this->Debug) {
printf(”Debug: query = %s<br>n”, $Query_String);
printf(”<br>Debug: Query_ID: %d<br>n”, $this->Query_ID);
}
if(!@ora_parse($this->Query_ID,$Query_String)) {
$this->Errno=ora_errorcode($this->Query_ID);
$this->Error=ora_error($this->Query_ID);
$this->halt(”<BR>ora_parse() failed:<BR>$Query_String<BR><small>Snap & paste this to sqlplus!</SMALL>”);
} elseif (!@ora_exec($this->Query_ID)) {
$this->Errno=ora_errorcode($this->Query_ID);
$this->Error=ora_error($this->Query_ID);
$this->halt(”<BR>n$Query_Stringn<BR><small>Snap & paste this to sqlplus!</SMALL>”);
}
$this->Row=0;
if(!$this->Query_ID) {
$this->halt(”Invalid SQL: “.$Query_String);
}
return $this->Query_ID;
}
function next_record() {
if (!$this->ora_no_next_fetch &&
0 == ora_fetch($this->Query_ID)) {
if ($this->Debug) {
printf(”<br>next_record(): ID: %d Row: %d<br>n”,
$this->Query_ID,$this->Row+1);
// more info for $this->Row+1 is $this->num_rows(),
// but dosn’t work in all cases (complicated selects)
// and it is very slow here
}
$this->Row +=1;
$errno=ora_errorcode($this->Query_ID);
if(1403 == $errno) { # 1043 means no more records found
$this->Errno=0;
$this->Error=”";
$this->disconnect();
$stat=0;
} else {
$this->Error=ora_error($this->Query_ID);
$this->Errno=$errno;
if($this->Debug) {
printf(”<br>%d Error: %s”,
$this->Errno,
$this->Error);
}
$stat=0;
}
} else {
$this->ora_no_next_fetch=false;
for($ix=0;$ix<ora_numcols($this->Query_ID);$ix++) {
$col=strtolower(ora_columnname($this->Query_ID,$ix));
$value=ora_getcolumn($this->Query_ID,$ix);
$this->Record[ "$col" ] = $value;
$this->Record[ $ix ] = $value;
#DBG echo”<b>[$col]</b>: $value <br>n”;
}
$stat=1;
}
return $stat;
}
## seek() works only for $pos – 1 and $pos
## Perhaps I make a own implementation, but my
## opinion is, that this should be done by PHP3
function seek($pos) {
if ($this->Row – 1 == $pos) {
$this->ora_no_next_fetch=true;
} elseif ($this->Row == $pos ) {
## do nothing
} else {
$this->halt(”Invalid seek(): Position is cannot be handled by API.<BR>”.
”Only a seek to the last element is allowed in this version<BR>”.
”Difference too big. Wanted: $pos Current pos: $this->Row”);
}
if ($this->Debug) echo “<BR>Debug: seek = $pos<BR>”;
$this->Row=$pos;
}
function lock($table, $mode = “write”) {
if ($mode == “write”) {
$result = ora_do($this->Link_ID, “lock table $table in row exclusive mode”);
} else {
$result = 1;
}
return $result;
}
function unlock() {
return ora_do($this->Link_ID, “commit”);
}
// Important note: This function dosn’t work with Oracle-Database-Links!
// You are free to get a better method. :)
function metadata($table,$full=false) {
$count = 0;
$id = 0;
$res = array();
/*
* Due to compatibility problems with Table we changed the behavior
* of metadata();
* depending on $full, metadata returns the following values:
*
* – full is false (default):
* $result[]:
* [0]["table"] table name
* [0]["name"] field name
* [0]["type"] field type
* [0]["len"] field length
* [0]["flags"] field flags (”NOT NULL”, “INDEX”)
* [0]["format"] precision and scale of number (eg. “10,2″) or empty
* [0]["index"] name of index (if has one)
* [0]["chars"] number of chars (if any char-type)
*
* – full is true
* $result[]:
* ["num_fields"] number of metadata records
* [0]["table"] table name
* [0]["name"] field name
* [0]["type"] field type
* [0]["len"] field length
* [0]["flags"] field flags (”NOT NULL”, “INDEX”)
* [0]["format"] precision and scale of number (eg. “10,2″) or empty
* [0]["index"] name of index (if has one)
* [0]["chars"] number of chars (if any char-type)
* [0]["php_type"] the correspondig PHP-type
* [0]["php_subtype"] the subtype of PHP-type
* ["meta"][field name] index of field named “field name”
* This could used, if you have the name, but no index-num – very fast
* Test: if (isset($result['meta']['myfield'])) {} …
*/
$this->connect();
## This is a RIGHT OUTER JOIN: “(+)”, if you want to see, what
## this query results try the following:
## $table = new Table; $db = new my_DB_Sql; # you have to make
## # your own class
## $table->show_results($db->query(see query vvvvvv))
##
$this->query(”SELECT T.table_name,T.column_name,T.data_type,”.
”T.data_length,T.data_precision,T.data_scale,T.nullable,”.
”T.char_col_decl_length,I.index_name”.
” FROM ALL_TAB_COLUMNS T,ALL_IND_COLUMNS I”.
” WHERE T.column_name=I.column_name (+)”.
” AND T.table_name=I.table_name (+)”.
” AND T.table_name=UPPER(’$table’) ORDER BY T.column_id”);
$i=0;
while ($this->next_record()) {
$res[$i]["table"] = $this->Record[table_name];
$res[$i]["name"] = strtolower($this->Record[column_name]);
$res[$i]["type"] = $this->Record[data_type];
$res[$i]["len"] = $this->Record[data_length];
if ($this->Record[index_name]) $res[$i]["flags"] = “INDEX “;
$res[$i]["flags"] .= ( $this->Record[nullable] == ‘N’) ? ” : ‘NOT NULL’;
$res[$i]["format"]= (int)$this->Record[data_precision].”,”.
(int)$this->Record[data_scale];
if (”0,0″==$res[$i]["format"]) $res[$i]["format"]=”;
$res[$i]["index"] = $this->Record[index_name];
$res[$i]["chars"] = $this->Record[char_col_decl_length];
if ($full) {
$j=$res[$i]["name"];
$res["meta"][$j] = $i;
$res["meta"][strtoupper($j)] = $i;
switch ($res[$i]["type"]) {
case “VARCHAR2″ :
case “VARCHAR” :
case “CHAR” :
$res["php_type"]=”string”;
$res["php_subtype"]=”";
break;
case “DATE” :
$res["php_type"]=”string”;
$res["php_subtype"]=”date”;
break;
case “BLOB” :
case “CLOB” :
case “BFILE” :
case “RAW” :
case “LONG” :
case “LONG RAW” :
$res["php_type"]=”string”;
$res["php_subtype"]=”blob”;
break;
case “NUMBER” :
if ($res[$i]["format"]) {
$res["php_type"]=”double”;
$res["php_subtype"]=”";
} else {
$res["php_type"]=”int”;
$res["php_subtype"]=”";
}
break;
default :
$this->halt(”metadata(): Type is not a valid value: ‘$res[$i][type]‘”);
break;
}
}
if ($full) $res["meta"][$res[$i]["name"]] = $i;
$i++;
}
if ($full) $res["num_fields"]=$i;
# $this->disconnect();
return $res;
}
## THIS FUNCTION IS UNSTESTED!
function affected_rows() {
if ($this->Debug) echo “<BR>Debug: affected_rows=”. ora_numrows($this->Query_ID).”<BR>”;
return ora_numrows($this->Query_ID);
}
## Known bugs: It will not work for SELECT DISTINCT and any
## other constructs which are depending on the resulting rows.
## So you *really need* to check every query you make, if it
## will work with it!
##
## Also, for a qualified replacement you need to parse the
## selection, cause this will fail: “SELECT id, from FROM …”).
## “from” is – as far as I know a keyword in Oracle, so it can
## only be used in this way. But you have been warned.
function num_rows() {
$curs=ora_open($this->Link_ID);
## this is the important part and it is also the HACK!
if (eregi(”^[[:space:]]*SELECT[[:space:]]”,$this->lastQuery) )
{
# This works for all?? cases, including SELECT DISTINCT case.
# We just make select count(*) from original sql expression
# and remove ORDER BY (if any) for speed
# I like regular expressions too ;-)))
$q = sprintf(”SELECT COUNT(*) FROM (%s)”,
@eregi_Replace(”ORDER[[:space:]]+BY[^)]*()*)”, “\1″,
$this->lastQuery)
);
# works also for subselects:
# if (eregi(”[[:space:]]+FROM([[:space:]]+.*[[:space:]]+FROM)”,$this->lastQuery,$r))
# $areplace=$r[1];
# $q=eregi_Replace(”^[[:space:]]*SELECT[[:space:]]+”.
# “.*[[:space:]]+FROM”,
# “SELECT COUNT(*) FROM$areplace”,
# $this->lastQuery);
if ($this->Debug) echo “<BR>Debug: num_rows: $q<BR>”;
ORA_parse($curs,$q);
ORA_exec($curs);
ORA_fetch($curs);
$result = ORA_getcolumn($curs,0);
ORA_close($curs);
if ($this->Debug)
{
echo “<BR>Debug: ID “.$this->QueryID.
” num_rows=”. $result .”<BR>”;
}
return $result;
}
else
{
$this->halt(”Last Query was not a SELECT: $this->lastQuery”);
}
}
function num_fields() {
if ($this->Debug) echo “<BR>Debug: num_fields=”. ora_numcols($this->Query_ID) . “<BR>”;
return ora_numcols($this->Query_ID);
}
function nf() {
return $this->num_rows();
}
function np() {
print $this->num_rows();
}
function f($Name) {
return $this->Record[$Name];
}
function p($Name) {
print $this->Record[$Name];
}
/* public: sequence number */
function nextid($seq_name)
{
$this->connect();
/* Independent Query_ID */
$Query_ID = ora_open($this->Link_ID);
if(!@ora_parse($Query_ID,”SELECT $seq_name.NEXTVAL FROM DUAL”))
{
// There is no such sequence yet, then create it
if(!@ora_parse($Query_ID,”CREATE SEQUENCE $seq_name”)
||
!@ora_exec($Query_ID)
)
{
$this->halt(”<BR> nextid() function – unable to create sequence”);
return 0;
}
@ora_parse($Query_ID,”SELECT $seq_name.NEXTVAL FROM DUAL”);
}
if (!@ora_exec($Query_ID)) {
$this->halt(”<BR>ora_exec() failed:<BR>nextID function”);
}
if (@ora_fetch($Query_ID) ) {
$next_id = ora_getcolumn($Query_ID, 0);
}
else {
$next_id = 0;
}
if ( $Query_ID > 0 ) {
ora_close($Query_ID);
}
return $next_id;
}
function disconnect() {
if($this->Debug) {
echo “Debug: Disconnecting $this->Query_ID…<br>n”;
}
if ( $this->Query_ID < 1 ) {
echo “<B>Warning</B>: disconnect(): Cannot free ID $this->Query_IDn”;
# return();
}
ora_close($this->Query_ID);
$this->Query_ID=0;
}
/* private: error handling */
function halt($msg) {
if ($this->Halt_On_Error == “no”)
return;
$this->haltmsg($msg);
if ($this->Halt_On_Error != “report”)
die(”Session halted.”);
}
function haltmsg($msg) {
printf(”</td></tr></table><br><b>Database error:</b> %s<br>n”, $msg);
printf(”<b>Oracle Error</b>: %s (%s)<br>n”,
$this->Errno,
$this->Error);
}
function table_names() {
$this->connect();
$this->query(”
SELECT table_name,tablespace_name
FROM user_tables”);
$i=0;
while ($this->next_record())
{
$info[$i]["table_name"] =$this->Record["table_name"];
$info[$i]["tablespace_name"]=$this->Record["tablespace_name"];
$i++;
}
return $info;
}
// Some transaction support
// Methods are used in ct_oracle.inc
function begin_transaction()
{
$this->connect();
// Now, disable autocommit
Ora_CommitOff($this->Link_ID);
if ($this->Debug)
{
print “BEGIN TRANSACTION<BR>”;
}
}
function end_transaction()
{
if ($this->Debug)
{
print “BEGIN TRANSACTION<BR>”;
}
$res = 1;
if(!@Ora_Commit($this->Link_ID))
{
Ora_CommitOn($this->Link_ID);
$this->halt(”Unable to finish transaction”);
$res = 0;
}
// Enable autocommit again
Ora_CommitOn($this->Link_ID);
if ($this->Debug)
{
print “END TRANSACTION : $res<BR>”;
}
return $res;
}
}
?>
