数据库的多表大数据查询应如何优化？

1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：\x0d\x0aselect id from t where num is null\x0d\x0a可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：\x0d\x0aselect id from t where num=0\x0d\x0a2.应尽量避免在 where 子句中使用!=或 *** 作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。\x0d\x0a3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：\x0d\x0aselect id from t where num=10 or num=20\x0d\x0a可以这样查询：\x0d\x0aselect id from t where num=10\x0d\x0aunion all\x0d\x0aselect id from t where num=20\x0d\x0a4.in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：\x0d\x0aselect id from t where num in(1,2,3)\x0d\x0a对于连续的数值，能用 between 就不要用 in 了：\x0d\x0aselect id from t where num between 1 and 3\x0d\x0a5.尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。 \x0d\x0a见如下例子： \x0d\x0aSELECT * FROM T1 WHERE NAME LIKE ‘%L%’ \x0d\x0aSELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’ \x0d\x0aSELECT * FROM T1 WHERE NAME LIKE ‘L%’ \x0d\x0a即使NAME字段建有索引，前两个查询依然无法利用索引完成加快 *** 作，引擎不得不对全表所有数据逐条 *** 作来完成任务。而第三个查询能够使用索引来加快 *** 作。\x0d\x0a6.必要时强制查询优化器使用某个索引，如在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：\x0d\x0aselect id from t where num=@num\x0d\x0a可以改为强制查询使用索引：\x0d\x0aselect id from t with(index(索引名)) where num=@num\x0d\x0a7.应尽量避免在 where 子句中对字段进行表达式 *** 作，这将导致引擎放弃使用索引而进行全表扫描。如：\x0d\x0aSELECT * FROM T1 WHERE F1/2=100 \x0d\x0a应改为: \x0d\x0aSELECT * FROM T1 WHERE F1=100*2\x0d\x0aSELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’ \x0d\x0a应改为: \x0d\x0aSELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’\x0d\x0aSELECT member_number, first_name, last_name FROM members \x0d\x0aWHERE DATEDIFF(yy,datofbirth,GETDATE()) >21 \x0d\x0a应改为: \x0d\x0aSELECT member_number, first_name, last_name FROM members \x0d\x0aWHERE dateofbirth ='2005-11-30' and createdate0) \x0d\x0aSELECT SUM(T1.C1) FROM T1WHERE EXISTS( \x0d\x0aSELECT * FROM T2 WHERE T2.C2=T1.C2) \x0d\x0a两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。

多变关联的实现方式有hash join,merge join,nested loop join 方式，具体使用那种内型的连接，主要依据:

1.当前的优化器模式（all_rows和rule)

2.取决于表的大小

3.取决于关联字段是否有索性

4.取决于关联字段是否排序

Hash  join散列连接，优化器选择较小的表（数据量少的表)利用连接键(join key)在内存中建立散列表，将数据存储到hash列表中，然后扫描较大的表

select A.*,B.* from A left join B on a.id=b.id。

先是从A表读取一条记录，用on条件匹配B表的记录，行成n行（包括重复行）如果B表没有与匹配的数据，则select中B表的字段显示为空,接着读取A表的下一条记录，right join类似。

left join基本是A表全部扫描，在表关键中不建议使用子查询作为副表，比如select A.*,B.*from A left join (select * from b where b.type=1 )这样A表是全表扫描，B表也是全表扫描。若果查询慢，可以考虑关联的字段都建索引，将不必要的排序去掉，排序会导致运行慢很多。

主副表条件过滤:

table a(id, type):

id    type

----------------------------------

1      1       

2      1         

3      2   

表b结构和数据

table b(id, class):

id    class

---------------------------------

1      1

2      2

Sql语句1: select a.*, b.* from a left join b on a.id = b.id and a.type = 1

执行结果为:

a.id    a.type    b.id    b.class

----------------------------------------

1        1            1        1

2        1            2        2

3        2

a.type=1没有起作用

sql语句2：

select a.*, b.* from a left join b on a.id = b.id where a.type = 1

执行结果为:

a.id    a.type    b.id    b.class

----------------------------------------

1        1            1        1

2        1            2        2

sql语句3：

select a.*, b.* from a left join b on a.id = b.id and b.class = 1

执行结果为：

a.id    a.type    b.id    b.class

----------------------------------------

1        1            1        1

2        1           

3        2

b.class=1条件过滤成功。

结论:left join中，左表（主表）的过滤条件在on后不起作用，需要在where中添加。右表（副表）的过滤条件在on后面起作用。

Mysql join原理:

Mysql join采用了Nested Loop join的算法，

＃＃＃坐车 回去补充。

---