1、MySQL执行计划调用方式
EXPLAIN SELECT ……
2、执行计划包含的信息
| id | select_type| table | type| possible_keys | key | key_len | ref | rows| filtered | Extra |
2.1、id
包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
2.2、select_type
表示查询中每个select子句的类型（简单 OR复杂）
SIMPLE：查询中不包含子查询或者UNION
PRIMARY:查询中若包含任何复杂的子部分，最外层查询标记为PRIMARY
SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT
2.3、type
表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：
| All | index | range | ref | eq_ref | const,system | null |
由左至右，由最差到最好
2.3.1)ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
2.3.2)Index：Full Index Scan，index与ALL区别为index类型只遍历索引树
2.3.3)Range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
2.3.4)Ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
2.3.5)Eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配;常见于主键或唯一索引扫描
2.3.6)Const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问;如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下,使用system
2.3.7)NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
2.4、possible_keys
指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
2.5、 key
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
Note:查询中若使用了覆盖索引，则该索引仅出现在key列表中
2.6、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
Note:key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
2.7、ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
2.8、rows
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
2.9、Extra
2.9.1)Using index
该值表示相应的select操作中使用了覆盖索引（Covering Index）
覆盖索引（Covering Index）:MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件
2.9.2)Using where
表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集
2.9.3)Using temporary
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
2.9.4)Using filesort
MySQL中无法利用索引完成的排序操作称为“文件排序”
3、MySQL执行计划的局限
3.1)EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
3.2)EXPLAIN不考虑各种Cache
3.3)EXPLAIN不能显示MySQL在执行查询时所作的优化工作
3.4)部分统计信息是估算的，并非精确值
3.5)EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
oracle 跟踪会话
一、跟踪自己的会话或者是别人的会话
1、跟踪自己的会话很简单
Alter session set sql_trace true|false;
或者
exec dbms_session.set_sql_trace(true/false);
2、如果跟踪别人的会话，需要调用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false);
二、整个数据库系统跟踪
1、开启会话跟踪
alter system set events
‘10046 trace name context forever,level 1’;
2、关闭会话跟踪
alter system set events
‘10046 trace name context off’;
3、跟踪会话级别
level 1：跟踪SQL语句，等于sql_trace=true
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件
4、说明：在11g中已经不推荐使用
alter system set sql_trace=true;
alter system set sql_trace=false;
三、查看跟踪文件
1、查看跟踪文件位置（当前会话）
select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p, (select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i, (select value from v$parameter where name = 'user_dump_dest') d;
2、tkprof格式化跟踪文件
tkprof trace_file_name out_file
php页面跳转
我把blog从http://www.66yj.com/blog迁移到http://www.xifenfei.com域名下，当用户访问以前blog地址时，自动跳转到当前blog的对应文章
$url_this=strtolower('http://'.$_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"]);
$go_url=str_replace('http://www.66yj.com/blog','http://www.xifenfei.com',$url_this);
header("location:$go_url");
exit;
shell 备份文件
#!/bin/bash
#文件名
file=$(date +%y%m%d%H%M)
#日志名
logfile=/home/chengfei/backup/log/file.log
echo "------"$(date +%Y-%m-%d%t%A%t%T)" Beginning backup--------" >>${logfile}
#tar打包
tar cvfz /home/chengfei/backup/files/$(date +%y%m%d%H%M).tar.gz /home/chengfei/www/wp-content/uploads/2011/ >>${logfile}
echo "------"$(date +%Y-%m-%d%t%A%t%T)" Ending backup------" >>${logfile}
#发送邮件
tail -50 ${logfile} | mutt -s "Blog附件备份" -a /home/chengfei/backup/files/$(date +%y%m%d%H%M).tar.gz 8chf@163.com
#删除七天以前备份
find /home/chengfei/backup/files/ -type f -mtime +7 -exec rm {} \;
crontab -e
00 04 * * * /home/chengfei/backup/script/backup_file.sh
mysql自动备份脚本
#!/bin/bash
#定义有备份的数据库名
dbname=chengfei
#定义备份数据库时使用的用户名和密码
dbuser=chengfei
dbpasswd=chengfei
#数据库备份的路径
backuppath=/home/chengfei/backup/mysql/
#数据库备份日志文件存储的路径
logfile=/home/chengfei/backup/log/mysql.log
#以当前的时间作为备份的数据库命名。
dumpfile=$(date +%y%m%d%H%M)
#这个函数用来备份数据库
back_db()
{
#将备份的时间、数据库名存入日志
echo "------"$(date +%Y-%m-%d%t%A%t%T)" Beginning database "${dbname}" backup--------" >>${logfile}
#备份数据库，如果有错误信息也记入日志。
/usr/bin/mysqldump -u${dbuser} -p${dbpasswd} ${dbname} >${backuppath}${dumpfile}.sql 2>> ${logfile}
#开始压缩数据文件
echo $(date +%Y-%m-%d%t%A%t%T)" Beginning zip ${backuppath}${dumpfile}.sql" >>${logfile}
#将备份数据库文件库压成ZIP文件，并删除先前的SQL文件。如果有错误信息也记入日志。
tar zcvf ${dumpfile}.tar.gz ${dumpfile}.sql && rm ${dumpfile}.sql 2>> ${logfile}
#将压缩后的文件名存入日志。
echo "backup file name:"${dumpfile}".tar.gz" >>${logfile}
echo -e "-------"$(date +%Y-%m-%d%t%A%t%T)" Ending database "${dbname}" backup-------\n" >>${logfile}
#发送邮件
cat ${logfile} | mutt -s "Blog数据库备份" -a ${dumpfile}.tar.gz 8chf@163.com
}
#这个函数用来删除七天前的数据，注意，这个脚本会删除当前目录下所有的早于七天前的文件
rm_oldfile()
{
#查找出当前目录下七天前生成的文件，并将之删除
find /home/chengfei/backup/mysql -type f -mtime +7 -exec rm {} \;
}
#切换到数据库备份的目录。如果不做这个操作，压缩文件时有可能会错误
cd ${backuppath}
#运行备份数据函数
back_db
#运行删除文件函数
rm_oldfile
添加运行计划
crontab -e
00 05 * * * /home/chengfei/backup/script/backup_mysql.sh
t-sql 更新大批量数据
需求：有两张很大的表，写了一个存储过程处理（加工数据然后插入到一张新表中），因为一次性处理数据量很大，会导致tempdb等资源被耗尽，为了解决这个问题，每次输入两个参数，每次根据两个id的范围处理程序
1、建一张日志表
CREATE TABLE [dbo].[data_log]( [start_time] [datetime] NULL DEFAULT (getdate()), [min_id] [int] NULL, [max_id] [int] NULL, [end_time] [datetime] NULL DEFAULT (getdate() )
2、编写处理过程
CREATE PROCEDURE [dbo].[insert_more](@min_data int,@max_data int)
as
begin
--开始处理
insert into data_log values(getdate(),@min_data,@max_data,getdate())
--处理程序
INSERT INTO test_1
SELECT t1.name,
t2.name,
t1.id
FROM t_1 t1,
t_2 t2
WHERE t1.id = t2.id
AND t1.id<=@max_data
AND t1.id>@min_data
--结束处理
update data_log set end_time=getdate() where min_id=@min_data and max_id=@max_data
end
3、生成批量执行该存储过程语句
CREATE PROCEDURE [dbo].[get_list](@min_id int,@max_id int,@mode int) AS declare @i int declare @max_m int begin set @i=0 while @i<=(@max_id-@min_id)/@mode begin set @max_m=@min_id+(@i+1)*@mode if @max_m>@max_id set @max_m=@max_id print 'EXEC [dbo].[insert_more] @min_data = '+CAST(@min_id+@i*@mode as VARCHAR(50))+',@max_data = '+CAST(@max_m as VARCHAR(50))+';' set @i=@i+1 END end
利用3的过程，输入最小id，最大id，取值间隔，生成需要执行2过程的sql语句，执行这些语句完成操作，通过日志监控操作情况
创建sql server分区表
1、创建数据库
USE Master GO CREATE DATABASE Test_Partitioning ON PRIMARY (NAME='Partitioning_1', FILENAME= 'E:\database\partitions\Partitioning_1.mdf', SIZE=4, MAXSIZE=100, FILEGROWTH=1 ), FILEGROUP FG2 (NAME = 'Partitioning_2', FILENAME = 'E:\database\partitions\Partitioning_2.mdf', SIZE = 4, MAXSIZE=100, FILEGROWTH=1 ), FILEGROUP FG3 (NAME = 'Partitioning_3', FILENAME = 'E:\database\partitions\Partitioning_3.mdf', SIZE = 4, MAXSIZE=100, FILEGROWTH=1 ) GO
2、创建分区函数
Use test_Partitioning GO CREATE PARTITION FUNCTION salesYearPartitions (datetime) AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01') GO
说明：
RIGHT:表示”=”在右边
LEFT:表示”=”在左边
3、创建分区方案
Use test_Partitioning GO CREATE PARTITION SCHEME Test_PartitionScheme AS PARTITION salesYearPartitions TO ([PRIMARY], FG2, FG3 ) GO
4、使用分区创建表
Use test_Partitioning GO CREATE TABLE SalesArchival (SaleTime datetime PRIMARY KEY, ItemName varchar(50)) ON Test_PartitionScheme (SaleTime); GO
5、验证SQL语句
5.1)确定文件组的数量和数据库数据文件的数量
Use test_Partitioning GO -- Confirm Filegroups SELECT name as [File Group Name] FROM sys.filegroups WHERE type = 'FG' GO -- Confirm Datafiles SELECT name as [DB File Name],physical_name as [DB File Path] FROM sys.database_files where type_desc = 'ROWS' GO
5.2)验证分区表上的数据分布
Use test_Partitioning GO select partition_id, index_id, partition_number, Rows FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival' GO
关于日期sql语句
网友请求写几条关于日期的sql语句
1、查询两个日期天数
select trunc(to_date(to_date( ‘2004-3-20 ‘, ‘yyyy-mm-dd ‘)-to_date( ‘2004-3-25 ‘, ‘yyyy-mm-dd ‘),’mm’) ) from dual ;
–trunc函数不用也行，因为日期格式化就是到天
2、查询两个日期的月份
SELECT trunc(months_between(to_date( ‘2004-3-20 ‘, ‘yyyy-mm-dd ‘),SYSDATE)) FROM dual;
–根据需求是截断还是取近似值决定使用floor或者trunc
3、根据生日查询年龄
1）计算年龄（周岁）
select floor(months_BETWEEN(SYSDATE,to_date( ‘2004-4-25 ‘, ‘yyyy-mm-dd ‘))/12) FROM dual;
2）计算年龄（虚岁）
SELECT to_char(SYSDATE,’yyyy’)-to_char(to_date(‘2004-03-04′,’yyyy-mm-dd’),’yyyy’) FROM dual;
主要就是trunc（近似值）和floor（截断）函数使用
ora_rowscn
一、默认情况下
–创建t_orascn测试表
SQL> create table t_orascn(id number);
Table created
–插入两条数据
SQL> insert into t_orascn values(1);
1 row inserted
SQL> insert into t_orascn values(2);
1 row inserted
SQL> commit;
Commit complete
–查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;
ORA_ROWSCN ID
———- ———-
559036 1
559036 2
–更新其中一条数据
SQL> update t_orascn set id=2 where rownum=1;
1 row updated
SQL> commit;
Commit complete
–再查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;
ORA_ROWSCN ID
———- ———-
559669 2
559669 2
–查询更详细信息
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0500180055010000 559669 Update 2
02001C0068010000 559036 Insert 2
02001C0068010000 559036 559669 Insert 1
–查询操作时间
SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn;
TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:01:12 2
2011-04-11 00:01:12 2
–查询数据详细操作时间
SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;
VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0500180055010000 2011-04-11 00:01:12 Update 2
02001C0068010000 2011-04-10 23:59:03 Insert 2
02001C0068010000 2011-04-10 23:59:03 559669 Insert 1
–结论：ora_rowscn在没有默认情况下，如果数据库块中的任何一条记录发生改变，该块中的所有记录的ora_rowscn中对应的scn值都改变
二、创建表含有rowdependencies测试
–创建测试表t_orascn_b
SQL> create table t_orascn_b(id number) rowdependencies;
Table created
SQL> insert into t_orascn_b values(1);
1 row inserted
SQL> insert into t_orascn_b values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select ora_rowscn,id from t_orascn_b;
ORA_ROWSCN ID
———- ———-
560532 1
560532 2
SQL> insert into t_orascn_b values(3);
1 row inserted
SQL> select ora_rowscn,id from t_orascn_b;
ORA_ROWSCN ID
———- ———-
560532 1
560532 2
3
SQL> commit;
Commit complete
SQL> select ora_rowscn,id from t_orascn_b;
ORA_ROWSCN ID
———- ———-
560532 1
560532 2
560555 3
–说明一点：没有提交ora_rowscn不改变(update)或者不存在（insert）
SQL> update t_orascn_b set id=10 where id<2;
1 row updated
SQL> commit;
Commit complete
SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn_b;
TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:15:38 10
2011-04-11 00:12:37 2
2011-04-11 00:13:28 3
SQL>
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0800290054010000 560614 Update 10
0500130056010000 560555 Insert 3
0A00090001010000 560532 Insert 2
0A00090001010000 560532 560614 Insert 1
SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;
VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0800290054010000 2011-04-11 00:15:38 Update 10
0500130056010000 2011-04-11 00:13:28 Insert 3
0A00090001010000 2011-04-11 00:12:37 Insert 2
0A00090001010000 2011-04-11 00:12:37 560614 Insert 1
–结论：如果创建表时指定了rowdependencies，则ora_rowscn是以行为单位变化，而不是块
sql server中查看对象
1、查询所有数据库
select * from sys.databases;
2、查询对象
select * from sysobjects;
3、复制表结构和数据
SELECT * INTO t_1 FROM master.dbo.spt_monitor;
4、查询视图
SELECT * FROM sys.views;
5、查询列
select * from sys.columns;
6、查询索引
select * from sys.indexes;