当前位置:  数据库>其它
本页文章导读:
    ▪MySQL查询优化:连接查询排序limit(join、order by、limit语句)      不知道有没有人碰到过这样恶心的问题：两张表连接查询并limit，SQL效率很高，但是加上order by以后，语句的执行时间变的巨长，效率巨低。   情况是这么一个情况：现在有两张表，team表.........
    ▪（8）连接基数      1、基本的连接基数 oracle有两个公式用于连接基数的计算： 假设我们对表t1和t2进行连接，连接列分别是c1和c2。 Join Selectivity = ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) * ((num_rows(t2) - num.........
    ▪事务在数据库以及.NET代码中的使用       一直感觉事务是很神秘的东西。其实呢，感觉它神秘，主要原因是自己没有用过，等你真正用到它的时候，你就会发现，原来事务也不过如此。下面就跟大家分享一下事务在数据库以及.NE.........

[1]MySQL查询优化:连接查询排序limit(join、order by、limit语句)
    来源: 互联网  发布时间: 2013-11-07

不知道有没有人碰到过这样恶心的问题：两张表连接查询并limit，SQL效率很高，但是加上order by以后，语句的执行时间变的巨长，效率巨低。

 

情况是这么一个情况：现在有两张表，team表和people表，每个people属于一个team，people中有个字段team_id。


下面给出建表语句：

create table t_team
(
id int primary key,
tname varchar(100)
);

create table t_people
(
id int primary key,
pname varchar(100),
team_id int,
foreign key (team_id) references t_team(id)
);

下面我要连接两张表查询出前10个people，按tname排序。

 

于是，一个SQL语句诞生了：select * from t_people p left join t_team t onp.team_id=t.id order by p.pname limit 10; [语句①]


这个是我第一反应写的SQL，通俗易懂，也是大多数人的第一反应。

 

然后来测试一下这个语句的执行时间。

 

首先要准备数据。我用存储过程在t_team表中生成1000条数据，在t_people表中生成100000条数据。（存储过程在本文最后）

 

执行上面那条SQL语句，执行了好几次，耗时在3秒左右。

 

再换两个语句对比一下：

      

1.把order by子句去掉：select * from t_people p left join t_team t on p.team_id=t.id limit10; [语句②]

耗时0.00秒，忽略不计。

 

2.还是使用order by，但是把连接t_team表去掉：select * from t_people p order by p.pname limit 10;  [语句③]

耗时0.15秒左右。


对比发现[语句①]的效率巨低。

 

为什么效率这么低呢。[语句②]和[语句③]执行都很快，[语句①]不过是二者的结合。如果先执行[语句③]得到排序好的10条people结果后，再连接查询出各个people的team，效率不会这么低。那么只有一个解释：MySQL先执行连接查询，再进行排序。


解决方法：如果想提高效率，就要修改SQL语句，让MySQL先排序取前10条再连接查询。

SQL语句：

select * from (select * from t_people p order by p.pname limit 10) p left join t_team t on p.team_id=t.id limit 10; [语句④]


[语句④]和[语句①]功能一样，虽然有子查询，虽然看起来很别扭，但是效率提高了很多，它的执行时间只要0.16秒左右，比之前的[语句①]提高了20倍。

 

这两个表的结构很简单，如果遇到复杂的表结构…我在实际开发中就碰到了这样的问题，使用[语句①]的方式耗时80多秒，但使用[语句④]只需1秒以内。


最后给出造数据的存储过程：

CREATE PROCEDURE createdata()
BEGIN
DECLARE i INT;
START TRANSACTION;
SET i=0;
WHILE i<1000 DO
	INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1));
	SET i=i+1;
END WHILE;
SET i=0;
WHILE i<100000 DO
	INSERT INTO t_people VALUES(i+1,CONCAT('people',i+1),i%1000+1);
	SET i=i+1;
END WHILE;
COMMIT;
END

作者：叉叉哥   转载请注明出处：http://blog.csdn.net/xiao__gui/article/details/8616224



作者：xiao__gui 发表于2013-2-27 10:10:46 原文链接
阅读：45 评论：0 查看评论

    
[2]（8）连接基数
    来源: 互联网  发布时间: 2013-11-07
1、基本的连接基数

oracle有两个公式用于连接基数的计算：

假设我们对表t1和t2进行连接，连接列分别是c1和c2。
Join Selectivity = 
      ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) *
      ((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
      greater(num_distinct(t1.c1),num_distinct(t2.c2))      --greater 较大的
Join Cardinality = 
      Join Selectivity * 
      filtered cardinality(t1) * filtered cardinality(t2)
下面对其进行解释。

SQL> create table t1 
  2  as
  3  select
  4  trunc(dbms_random.value(0, 25))filter,
  5  trunc(dbms_random.value(0, 30))join1,
  6  lpad(rownum,10)v1,
  7  from('x',100)        padding
rpad('x',100)        padding
  8  from
  9    all_objects
 10  where 
 11    rownum <= 10000;

表已创建。

SQL> create table t2
  2  as
  3  select
  4  trunc(dbms_random.value(0, 50))filter,
  5  trunc(dbms_random.value(0, 40))join1,
  6  lpad(rownum,10)v1,
  7  rpad('x',100)padding
  8  from
  9  all_objects
 10  where
 11  rownum <= 10000;

表已创建。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't1',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't2',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。
在本例中我们有：
  t1.filter 25个不同的值
  t2.filter 50个不同的值
  t1.join1 30个不同的值
  t2.join1 40个不同的值

由于两个表都有10000行，因此t1的过滤基数将是400（10000行除以不同值的个数25），t2的过滤基数将是200（10000行除以不同值的个数50）。

因为任意一个表中都没有空值，因此根据连接基数公式可以得到：
Join Selectivity =
      ((10000 - 0) / 10000) *
      ((10000 - 0) / 10000) /
      greater(30,40) = 1/40
Join Cardinality = 1/40 * 400 * 200 = 2000

当通过autotrace运行时，可以看到下面的计划：

SQL> set autotrace traceonly explain;
SQL> select t1.v1, t2.v1
  2    from t1, t2
  3   where t1.filter = 1
  4     and t2.join1 = t1.join1
  5     and t2.filter = 1;

执行计划
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 68000 |    67   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 | 68000 |    67   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    33   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    33   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T2"."JOIN1"="T1"."JOIN1")
   2 - filter("T2"."FILTER"=1)
   3 - filter("T1"."FILTER"=1)
其中显示出表t1的过滤基数为400，表t2的过滤基数为200，并且连接基数为2000，与我们预测的结果相同。
下面将测试修改得复杂些，在t1表的连接列上每20行取一个空值，t2表的连接列上每30行取一个控制。
SQL> set autotrace off;
SQL> update t1 set join1 = null
  2  where mod(to_number(v1),20) = 0;

已更新500行。

SQL> commit;

提交完成。

SQL> update t2 set join1 = null
  2  where mod(to_number(v1),30) = 0;

已更新333行。

SQL> commit;

提交完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't1',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't2',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。
Join Selectivity =
      ((10000 - 500) / 10000) *
      ((10000 - 333) / 10000) / 40 = 0.022959125
Join Cardinality = 0.022959125 * 400 * 200 = 1836.73
SQL> set autotrace traceonly explain;
SQL> select t1.v1, t2.v1
  2    from t1, t2
  3   where t1.filter = 1
  4     and t2.join1 = t1.join1
  5     and t2.filter = 1;

执行计划
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1837 | 62458 |    67   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1837 | 62458 |    67   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    33   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    33   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T2"."JOIN1"="T1"."JOIN1")
   2 - filter("T2"."FILTER"=1)
   3 - filter("T1"."FILTER"=1)
可以在连接列上存在空值的基础上，在filter列中再引入一些空值。
SQL> set autotrace off;
SQL> update t1 set filter = null where mod(to_number(v1),50) = 0;

已更新200行。

SQL> update t2 set filter = null where mod(to_number(v1),100) = 0;

已更新100行。

SQL> commit;

提交完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't1',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't2',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。
从前面的示例中已经得到了连接选择率为0.022959125，因此现在要做的就是算出当过滤谓词作用的列上含有空值时每个表的过滤基数。
t1：1/25 * (1000 - 200) = 392
t2：1/50 * (1000 - 100) = 198
Join Cardinality = 0.022959125 * 392* 198 = 1781.995446
SQL> set autotrace traceonly explain;
SQL> select t1.v1, t2.v1
  2    from t1, t2
  3   where t1.filter = 1
  4     and t2.join1 = t1.join1
  5     and t2.filter = 1;

执行计划
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1782 | 60588 |    67   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1782 | 60588 |    67   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   198 |  3366 |    33   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   392 |  6664 |    33   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T2"."JOIN1"="T1"."JOIN1")
   2 - filter("T2"."FILTER"=1)
   3 - filter("T1"."FILTER"=1)
2、实际SQL的连接基数








作者：zq9017197 发表于2013-2-27 17:38:08 原文链接
阅读：0 评论：0 查看评论

    
[3]事务在数据库以及.NET代码中的使用
    来源: 互联网  发布时间: 2013-11-07

 一直感觉事务是很神秘的东西。其实呢，感觉它神秘，主要原因是自己没有用过，等你真正用到它的时候，你就会发现，原来事务也不过如此。下面就跟大家分享一下事务在数据库以及.NET代码中的使用。


先说说什么是事务？

数据库事务(简称: 事务)是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。当事务被提交给了DBMS（数据库管理系统），则DBMS（数据库管理系统）需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态;同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

事务的特性（ACID性质）


原子性(Atomic) 

事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

一致性(Consistency) 

事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。

隔离性(Isolation) 

多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

持久性(Durability) 

已被提交的事务对数据库的修改应该永久保存在数据库中。


下面介绍事务的具体使用。

事务在数据库中的使用

BEGIN TRANSACTION
	--向Table1表中插入记录
	INSERT INTO Table1 VALUES('1','1')
	SET @Err1=@@ERROR
	
	--向Table2表插入记录
	INSERT INTO Table2 VALUES('1','1')
	SET @Err2=@@ERROR

	IF(@Err1=0 AND @Err2=0)
		COMMIT TRANSACTION	--事务提交
	ELSE
		ROLLBACK TRANSACTION	--事务回滚
@@ERROR：完成 Transact-SQL 语句的执行时，如果语句执行成功，则 @@ERROR 设置为 0。若出现一个错误，则返回一条错误信息。@@ERROR 返回此错误信息代码，直到另一条 Transact-SQL 语句被执行。


事务在.NET代码中的使用

首先添加引用usingSystem.Transactions;

 （1）只在D层使用事务

//设定事务的级别
TransactionOptions option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, option))
{
	//标志是否更改成功
	bool flag = false;
	bool flagUpdate = false;
	                
	//更新Table1中数据
	string sqlUpText = "UPDATE Table1 SET actionReason = '1'";
	flagUpdate = sqlHelper.ExecuteNonQuery(sqlUpText,CommandType.Text);
	            
	//更新Table2中数据
	string sqlText = "UPDATE Table2 SET isAvailable ='否' ";
	flag = sqlHelper.ExecuteNonQuery(sqlText, CommandType.Text);
	if (flag && flagUpdate)
	{
	    ts.Complete();
	    return true;
	}
	else {
	    return false;
	}
}

（2）事务在B层的使用
B层代码

//首先引用 System.Data和System.Data.SqlClient命名空间
//定义事务执行所使用的链接
SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnDB"].ToString());

//打开连接
sqlCon.Open();

//定义事务
SqlTransaction sqlTran = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
//用try...Catch...finally保证事务在出错时会回滚
try
{
    //向课程表中添加数据
    OptionalCourseScheduleLinkDAL OptionalCourseSchedule = new OptionalCourseScheduleLinkDAL();
    //将新建立的连接和事务一起传回D层
    IsAddOptionalCourseSchedule = OptionalCourseSchedule.AddOptionalCourseSchedule(enOptionalCourseSchedule,sqlCon,sqlTran);

    //更新授课关系表中的单双周
    CourseTeachClassLinkDAL CourseTeachClass = new CourseTeachClassLinkDAL();
    //添加D层的方法，调用sqlHelper中执行事务的方法：ExecNoSelect(string cmdText, SqlParameter[] paras, CommandType cmdType, SqlConnection sqlConns, SqlTransaction sqlTran)
    //将新建立的连接和事务一起传回D层
    IsUpdateCourseTeachClass = CourseTeachClass.UpdateCourseTeachClassOddEven(enCourseTeachClass,sqlCon,sqlTran);

    //若添加和更新有一者返回false，事务回滚
    if (IsAddOptionalCourseSchedule && IsUpdateCourseTeachClass)
    {
        //如果都为真，提交
        sqlTran.Commit();
        sqlCon.Close();
        return true;
    }
    else {
        sqlTran.Rollback();
    }

}
catch (Exception)
{
    //出现异常时，事物回滚
    sqlTran.Rollback();
}

finally {
    sqlCon.Close();
}
return false;
D层代码

public Boolean AddOptionalCourseSchedule(OptionalCourseScheduleLinkEntity enOptionalCourseScheduleLink, SqlConnection sqlCon, SqlTransaction sqlTran)
{

    //声明一个布尔型变量
    Boolean blnIsResult = false;
    //执行SQL字符串名
    string strSQL = "insert into TBR_OptionalCourseScheduleLink(TeachClassID,RoomID,WorkDay,ClassBegin,ClassEnd) VALUES(@TeachClassID,@RoomID,@WorkDay,@ClassBegin,@ClassEnd)";
    //字符串数组
    SqlParameter[] param = new SqlParameter[]
    {
        new SqlParameter("@TeachClassID",enOptionalCourseScheduleLink.TeachClassID),//虚拟班ID
        new SqlParameter("@RoomID",enOptionalCourseScheduleLink.RoomID),//房间ID
        new SqlParameter("@WorkDay",enOptionalCourseScheduleLink.WorkDay),//星期
        new SqlParameter("@ClassBegin",enOptionalCourseScheduleLink.ClassBegin),//开始时间（如：上午第一节）
        new SqlParameter("@ClassEnd",enOptionalCourseScheduleLink.ClassEnd)//结束时间
           
    };
    //SQL语句类型
    CommandType cmdType = CommandType.Text;
    //SQLHELPER函数返回值
    blnIsResult = sqlHelper.ExecNoSelect(strS      

      
      
    
最新技术文章:
▪gc buffer busy/gcs log flush sync与log file sync    ▪让你的PL/SQL更好用    ▪ADO.NET中的非脱机数据库查询
▪参数job_queue_processes与Oracle jobs    ▪11gR2游标共享新特性带来的一些问题以及_cursor...    ▪_library_cache_advice和latch:shared pool、latch:shared poo...
▪SQL: Date Utility    ▪DB2 分区表增加分区    ▪DB2第一步 — 创建表
▪oracle 数据库    ▪插入10万条记录测试    ▪rebuild index VS. rebuild index online
▪如何处理undo tablespace 表空间太大的问题    ▪ado执行存储过程中包含结果集获取输出参数为...    ▪oracle函数的demo
▪Entity Framework 学习建议及自学资源    ▪存储过程的编写    ▪Linux/Unix shell 自动发送AWR report(二)
▪第二章 Oracle恢复内部原理（基础数据结构）    ▪Redis源码学习之【Tcp Socket封装】    ▪Java Jdbc减少与Oracle之间交互提升批量处理性能...
▪南大通用GBase8a Vs Oracle11g 单机测试亲测    ▪oracle 中行列转换    ▪rhel下安装oracle10g+asm---测试环境搭建
▪Redis系列-主从复制配置    ▪MySQL索引与查询优化    ▪INDEX受到NULL值的影响
▪测试人员的SQL语言 系列    ▪SQL数据库基本语句    ▪MySQL Replication常见错误整理[持续更新...]
▪eclipse下建立esper的demo    ▪把oracle rac 转化为单机数据库    ▪Redis系列-存储篇sorted set主要操作函数小结
▪基本的SQL*Plus报表和命令    ▪druid简单教程    ▪11g调度--scheduler使用
▪EF基础一    ▪db2存储过程中循环语句while do的continue有没有...    ▪oracle 创建DBLINK
▪DB2数据库备份还原    ▪Warning: prerequisite DBD::mysql 1 not found错误解决方...    ▪innotop性能监视mysql，innodb工具
▪数据迁移：DataGuard配置    ▪QX项目实战-19.跨库数据同步    ▪Mysql EXPLAIN
▪Oracle 11g AWR 系列七：Active Session History (ASH) 报...    ▪Oracle 11G新特性（共36个）    ▪父子节点问题
▪OEM简介及按钮乱码问题    ▪NoSql之MongoDB的常用类管理    ▪ORA-39700: database must be opened with UPGRADE option
▪node.js 访问redis数据库，pub/sub    ▪使用DBMS_REDEFINITION在线重定义分区表    ▪SQL Developer 使用问题与解决方法汇总
▪oralce 11g dataguard 概念    ▪ORA-30004 错误处理    ▪oracle分组函数rollup,cube
▪Sql Developer 使用问题与解决方法汇总    ▪Configure Oracle Dataguard Primary-ASM to Physical-ASM    ▪Oracle Data Guard 理论知识
▪Control File 恢复    ▪Oracle数据文件收缩    ▪Oracle 11g AWR 系列五：如何生成 AWR 报告？
▪Wireshark数据包分析实战(第2版)    ▪MySql用户权限控制    ▪db2和oracle查询序列区别
mysql iis7站长之家
▪Oracle中DBMS_RANDOM.STRING 的用法    ▪SQL SERVER无法安装成功，sqlstp.log文件提示[未发...    ▪Data Guard 部署物理备库的 10 大注意事项
▪万能数据库查询分析器使用技巧之（九）    ▪SQL 自定义Split函数    ▪视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的...
▪Data Guard Standby_archive_dest 和 Log_archive_dest_n 的...    ▪机房收费系统数据库设计（一）    ▪利用putty的SSH tunnel连接Oracle
▪DBCA建库偶遇ORA-27125    ▪使用PowerPivot建立简单的分析模型    ▪Linux/Unix shell 自动发送AWR report
▪写入到blob字段的存储过程    ▪关于JDBC中ResultSet接口的一点细节探究    ▪Data Guard 配置 Standby Redo Log
▪linux下redis的安装    ▪windows下redis的安装    ▪手动创建数据库步骤(简单翻译官方文档)
▪Ubuntu安装Mongodb    ▪SQL CLR应用    ▪redis的配置文件参数--详细说明
 


站内导航:


特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

©2012-2021,,E-mail:www_#163.com(请将#改为@)

浙ICP备11055608号-3