数据库的查询优化方法分析

尽量不要使用 or 使用or会引起全表扫描 将大大降低查询效率

alice like % &abigale&% 会使索引不起作用(针对sqlserver)

经过实践验证 charindex()并不比前面加%的like更能提高查询效率 并且charindex()会使索引失去作用（指sqlserver数据库）

字段提取要按照 需多少 提多少 的原则 避免 select * 尽量使用 select 字段 字段 字段 实践证明 每少提取一个字段 数据的提取速度就会有相应的提升 提升的速度还要看您舍弃的字段的大小来判断

order by按聚集索引列排序效率最高 一个sqlserver数据表只能建立一个聚集索引 一般默认为ID 也可以改为其它的字段

能使用exists和not exists尽量使用 避免使用in或not in

能使用表连接尽量使用 避免使用exists和not exists

SET NOCOUNT ON

正确使用UNION和UNION ALL

慎用SELECT DISTINCT

少用游标

使用表的别名(Alias)

当在SQL语句中连接多个表时 请使用表的别名并把别名前缀于每个Column上 这样可以减少解析的时间并减少那些由Column歧义引起的语法错误

尽量少使用游标

原因很简单就是游标的算法是最原始的计算机算法(和for if等语句一样 一条条搜索来算效率极低)

而sql语句用的是集合运算速度则快的多如果用索引速度则很快(用了指针)

创建索引

a 聚集索引:

聚集索引是磁盘存储和逻辑显示是一样的

mssql表的主键一般是聚集索引主键(每一条记录唯一确定)

创建的主键自动会是聚集索引

如有一个非常大的表(有百万行)很长时间磁盘存储上会有类似碎片(磁盘填充率效率低一般是频繁删除造成的)

要提高它的性能的最简洁办法是:把这个表的主键去掉再保存后然后重新设主键再保存

(这个表就会在磁盘上重新整理排序性能当然会提高哟)

b 非聚集索引:

非聚集索引是在外面建立小的附加表(一种树形结构大多数是B或B+树)

读(遍历select等sql语句)表特快但写(updatedelete insert等sql语句)表性能会略微下降

针对数据量大的表建议非聚集索引不要超过 个(节省额外磁盘负担)

不要给类似 性别 列创建索引

死锁:

是指有线程在读一条记录别的线程读这条记录就要等待

在mssql中只要长期占那条记录的线程去掉死锁就会解除

在mssql中锁是针对每一行记录(所以性能不错)

经常产生锁的原因有:

a 在sql语句中使用事务语句(特别是事务中当查询比较耗时)

b 在前台的应用程序的connetion冲突(未关闭)

c 多表联合查询(尤其是在打开大的数据集时)

sql语句优化

a is null not or in 不会用索引

b 避免在索引列上使用计算或函数处理(索引会大失性能) 还有 % 有的甚至会全失索引性能

c SELECT中避免使用 * (宁可把需要字段列出来而不要用*去把所有的字段都列出来)

d 避免相关子查询(select中套select)

e where的条件中 =＞exists＞in (指性能)

f order by group by having distinct 等语句要慎用(因为它们效率不高它们是先把数据到临时表中再进行处理的)

g 聚集索引如有 个字段组成(tt 和tt )tt 在前面where的条件中如只用tt 字段来判断就会用到一半的聚集索引

where的条件中如tt 和tt 字段都用来判断了就会全用到聚集索引

where的条件中如只用tt 字段来判断就会用不到聚集索引了

尽量不要使用TEXT数据类型

除非你使用TEXT处理一个很大的数据 否则不要使用它 因为它不易于查询 速度慢 用的不好还会浪费大量的空间

一般的 VARCHAR可以更好的处理你的数据

尽量不要使用临时表

尽量不要使用临时表 除非你必须这样做 一般使用子查询可以代替临时表 使用临时表会带来系统开销

如果前台的代码你是使用数据库连接池而临时表却自始至终都存在 SQL Server提供了一些替代方案 比如Table数据类型

尽量少使用外键和触发器

因为在mssql中这些功能的性能做得不是很好随便动一下表(它就会到相关的表去搞判断有很多情况并不需要)在后台消耗资源大

lishixinzhi/Article/program/Oracle/201311/16744

应尽量避免全表扫描，首先应考虑在 where 及 order by ,group by 涉及的列上建立索引

可以帮助选择更好的索引和优化查询语句， 写出更好的优化语句。 通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句， 把关键字 EXPLAIN 加到前面， 查看执行计划。例如： explain select * from news

用具体的字段列表代替“*” ， 不要返回用不到的任何字段。

mysql innodb上的理解。

1，不需要的字段会增加数据传输的时间，即使mysql服务器和客户端是在同一台机器上，使用的协议还是tcp，通信也是需要额外的时间。

2，要取的字段、索引的类型，和这两个也是有关系的。举个例子，对于user表，有name和phone的联合索引，select name from user where phone= 12345678912 和 select * from user where phone= 12345678912 ，前者要比后者的速度快，因为name可以在索引上直接拿到，不再需要读取这条记录了。

3，大字段，例如很长的varchar，blob，text。准确来说，长度超过728字节的时候，会把超出的数据放到另外一个地方，因此读取这条记录会增加一次io *** 作。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

使用 procedure analyse()函数对表进行分析， 该函数可以对表中列的数据类型提出优化建议。 能小就用小。 表数据类型第一个原则是： 使用能正确的表示和存储数据的最短类型。 这样可以减少对磁盘空间、 内存、 cpu 缓存的使用。

使用方法： select * from 表名 procedure analyse()

通过拆分表可以提高表的访问效率。 有 2 种拆分方法

1.垂直拆分

把主键和一些列放在一个表中， 然后把主键和另外的列放在另一个表中。 如果一个表中某些列常用， 而另外一些不常用， 则可以采用垂直拆分。

2.水平拆分

根据一列或者多列数据的值把数据行放到二个独立的表中。

创建中间表， 表结构和源表结构完全相同， 转移要统计的数据到中间表， 然后在中间表上进行统计， 得出想要的结果。

选择多核和主频高的 CPU。

使用更大的内存。 将尽量多的内存分配给 MYSQL 做缓存。

4.3.1 使用磁盘阵列

RAID 0 没有数据冗余， 没有数据校验的磁盘陈列。 实现 RAID 0至少需要两块以上的硬盘， 它将两块以上的硬盘合并成一块， 数据连续地分割在每块盘上。

RAID1 是将一个两块硬盘所构成 RAID 磁盘阵列， 其容量仅等于一块硬盘的容量， 因为另一块只是当作数据“镜像”。使用 RAID-0+1 磁盘阵列。 RAID 0+1 是 RAID 0 和 RAID 1 的组合形式。 它在提供与 RAID 1 一样的数据安全保障的同时， 也提供了与 RAID 0 近似的存储性能。

4.3.2 调整磁盘调度算法

选择合适的磁盘调度算法， 可以减少磁盘的寻道时间

对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。 如指定 MySQL 查询缓冲区的大小， 指定 MySQL 允许的最大连接进程数等。

它的作用是存储 select 查询的文本及其相应结果。 如果随后收到一个相同的查询， 服务器会从查询缓存中直接得到查询结果。 查询缓存适用的对象是更新不频繁的表， 当表中数据更改后， 查询缓存中的相关条目就会被清空。

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两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。

---