关于数据库性能优化小经验

；查录音answer表， 把 所有没被评论的分页查询出来按时间排序， 实现是这样， 把 所有的common的表 answer_id  去拿出来，和answer表比较，

以前的sql ： select * from answer a where a.id not in(select b.answer_id from answer b)  and a.....

没有的查出来，如果数据量小，可以接受，现在线上 数据，录音多，评论也多，这种时间负责度 为n  的平方，就会特别慢，我用exists 替换了in ，不行，加了索引也不行，

解决办法：answer表加个 字段是否已 被评论 flag ，先把 线上数据用sql更新  update answer a set a.isComment=1 where EXISTS(select 1 from  answer_comment b where b.answer_id=a.id);

 然后程序Java控制，如果有评论，就把那条录音isComment 更新为1 ，这样查询 就是单表查询，不会有关联了 ，时间 复杂度为1 ；sql ：select a from answer a where a.isComment=1;

第二个接口：返回高分录音  同样是answer 表， answer_Comment , 用户对录音的点评 分为四个维度存在 answer_Comment ,高分录音是4 个维度求和 大于3 的算高分；

之前的sql：select *  from answer a    group by a.id   having a.id in (select  c. answer_id  avg(c.1+c.2+c.3+c.4) >3 from answer_Comment c  )    and a.sum(secounds)>100  这里也是求和 和in 执行时间很慢

大约20 秒了，当我接受这个项目的时候，添加索引 或者基本的sql优化也回力无天，这里又是 计算，又是in ;

解决 办法，还是添加字段，但是这个 计算是实时 的，多了一条评论， 积分 就会改变 ，这里应为 才用了存储过程，每天晚上去统计计算情况在answer表加一个字段 ， 平均分， 更新后，类似于第一个接口 ；

=====================================================================================================================

其实sql 优化那些查询，网上那些办法，什么查询结果需要的字段啊 ，加索引啊，都是空话，我觉得关键在于表的设计 ，尽量减少多表交互太多， 当然不是单表；

关于数据库性能优化小经验

标签：

小编还为您整理了以下内容，可能对您也有帮助：

怎样优化mysql数据库来提高mysql性能(mysql数据库的优化)

优化“mysql数据库”来提高“mysql性能”的方法有：

1、选取最适用的字段属性。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)。

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

3、使用联合(UNION)来代替手动创建的临时表。

MySQL从4.0的版本开始支持UNION查询，它可以把需要使用临时表的两条或更多的SELECT查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务。

要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。

5、锁定表。

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

6、使用外键。

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

如何优化数据库提高数据库的效率

1.SQL优化的原则是：将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。

调整不良SQL通常可以从以下几点切入：

检查不良的SQL，考虑其写法是否还有可优化内容

检查子查询考虑SQL子查询是否可以用简单连接的方式进行重新书写

检查优化索引的使用

考虑数据库的优化器

2.避免出现SELECT*FROMtable语句，要明确查出的字段。

3.在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。

4.查询时尽可能使用索引覆盖。即对SELECT的字段建立复合索引，这样查询时只进行索引扫描，不读取数据块。

5.在判断有无符合条件的记录时建议不要用SELECTCOUNT（*）和selecttop1语句。

6.使用内层限定原则，在拼写SQL语句时，将查询条件分解、分类，并尽量在SQL语句的最里层进行限定，以减少数据的处理量。

7.应绝对避免在orderby子句中使用表达式。

8.如果需要从关联表读数据，关联的表一般不要超过7个。

9.小心使用IN和OR，需要注意In集合中的数据量。建议集合中的数据不超过200个。

10.<>用<、>代替，>用>=代替，<用<=代替，这样可以有效的利用索引。

11.在查询时尽量减少对多余数据的读取包括多余的列与多余的行。

12.对于复合索引要注意，例如在建立复合索引时列的顺序是F1，F2，F3，则在where或orderby子句中这些字段出现的顺序要与建立索引时的字段顺序一致，且必须包含第一列。只能是F1或F1，F2或F1，F2，F3。否则不会用到该索引。

13.多表关联查询时，写法必须遵循以下原则，这样做有利于建立索引，提高查询效率。格式如下selectsum（table1.je）fromtable1table1,table2table2,table3table3where(table1的等值条件（=）)and(table1的非等值条件)and(table2与table1的关联条件)and(table2的等值条件)and(table2的非等值条件)and(table3与table2的关联条件)and(table3的等值条件)and(table3的非等值条件)。

注:关于多表查询时from后面表的出现顺序对效率的影响还有待研究。

14.子查询问题。对于能用连接方式或者视图方式实现的功能，不要用子查询。例如：selectnamefromcustomerwherecustomer_idin(selectcustomer_idfromorderwheremoney>1000)。应该用如下语句代替：selectnamefromcustomerinnerjoinorderoncustomer.customer_id=order.customer_idwhereorder.money>100。

15.在WHERE子句中，避免对列的四则运算，特别是where条件的左边，严禁使用运算与函数对列进行处理。比如有些地方substring可以用like代替。

16.如果在语句中有notin（in）操作，应考虑用notexists（exists）来重写,最好的办法是使用外连接实现。

17.对一个业务过程的处理，应该使事物的开始与结束之间的时间间隔越短越好，原则上做到数据库的读操作在前面完成，数据库写操作在后面完成，避免交叉。

18.请小心不要对过多的列使用列函数和orderby,groupby等，谨慎使用disti软件开发t。

19.用unionall代替union，数据库执行union操作，首先先分别执行union两端的查询，将其放在临时表中，然后在对其进行排序，过滤重复的记录。

当已知的业务逻辑决定queryA和queryB中不会有重复记录时，应该用unionall代替union，以提高查询效率。

数据更新的效率

1.在一个事物中，对同一个表的多个insert语句应该集中在一起执行。

2.在一个业务过程中，尽量的使insert,update,delete语句在业务结束前执行，以减少死锁的可能性。

数据库物理规划的效率

为了避免I/O的冲突，我们在设计数据库物理规划时应该遵循几条基本的原则(以ORACLE举例)：

table和index分离：table和index应该分别放在不同的tablespace中。

RollbackSegment的分离：RollbackSegment应该放在的Tablespace中。

SystemTablespace的分离：SystemTablespace中不允许放置任何用户的object。（mssql中primaryfilegroup中不允许放置任何用户的object）

TempTablesace的分离：建立单独的TempTablespace，并为每个user指定defaultTempTablespace

避免碎片：但segment中出现大量的碎片时，会导致读数据时需要访问的block数量的增加。对经常发生DML操作的segemeng来说，碎片是不能完全避免的。所以，我们应该将经常做DML操作的表和很少发生变化的表分离在不同的Tablespace中。

当我们遵循了以上原则后，仍然发现有I/O冲突存在，我们可以用数据分离的方法来解决。

连接Table的分离：在实际应用中经常做连接查询的Table，可以将其分离在不同的Taclespace中，以减少I/O冲突。

使用分区：对数据量很大的Table和Index使用分区，放在不同的Tablespace中。

在实际的物理存储中，建议使用RAID。日志文件应放在单独的磁盘中。

数据库如何优化

一、数据库设计优化

1、不要使用游标。

使用游标不仅占用内存，而且还用不可思议的方式锁定表，它们可以使DBA所能做的一切性能优化等于没做。游标里每执行一次fetch就等于执行一次select。

2、创建适当的索引

每当为一个表添加一个索引，select会更快，可insert和delete却大大变慢，因为创建了维护索引需要许多额外的工作。

（1）采用函数处理的字段不能利用索引

（2）条件内包括了多个本表的字段运算时不能进行索引

3、使用事务

对于一些耗时的操作，使用事务可以达到很好的优化效果。

4、小心死锁

按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。 如果某个存储过程先锁定表B，再锁定表A，这可能会导致一个死锁。

5、不要打开大的数据集

6、不要使用服务器端游标

与服务器端游标比起来，客户端游标可以减少服务器和网络的系统开销，并且还减少锁定时间。

7、不要忽略同时修改同一记录的问题

有时候，两个用户会同时修改同一记录，这样，后一个修改者修改了前一个修改者的操作，某些更新就会丢失。处理这种情况，创建一个timestamp字段，在写入前检查它，如果允许，就合并修改，如果存在冲突，提示用户。

8、尽量不要使用text数据类型

除非使用text处理一个很大的数据，否则不要使用它。因为它不易于查询，速度慢，用的不好还会浪费大量的空间。一般varchar可以更好的处理数据。

9、避免在索引列上使用计算

where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。例如：

（低效）select ... from [dept] where [sal]*12>25000; （高效）select ... from [dept] where [sal]>25000/12;1210、不同类型的索引效能是不一样的，应尽可能先使用效能高的

数字类型的索引查找效率高于字符串类型，定长字符串char、nchar的索引效率高于变长字符串varchar、nvarchar的索引。

（低效）select ... from tableName where username=‘张三‘ and age>=21（高效）select ... from tableName where age>=21 and username=‘张三‘12二、SQL语句优化

1、不要使用select *

在select中指定所需要的列，将带来的好处：

（1）减少内存耗费和网络的带宽

（2）更安全

（3）给查询优化器机会从索引读取所有需要的列

2、使用参数查询

主要是防止SQL注入，提高安全性。

3、使用exists或not exists代替in或not in

（高效）select * from [emp] where [empno]>0 and exists (select ‘X‘ from [dept] where [dept].[deptno]=[emp].[deptno] and [loc]=‘MELB‘);（低效）select * from [emp] where [empno]>0 and [deptno] in (select [deptno] from [dept] where [loc]=‘MELB‘);124、is null或is not null操作

判断字段是否为空一般是不会应用索引的，因为索引不索引空值。不能用null作索引，任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器都不允许使用索引。

推荐方案：用其他相同功能的操作运算代替，如：a is not null改为a>0或a>’‘等。

5、<及>操作

大于或小于一般情况不用调整，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化。如一个表有100万记录，那么执行>2与>=3的效果就有很大区别了。

（低效）select * from [emp] where [deptno]>2;（高效）select * from [emp] where [deptno]>=3;126、like操作

like操作可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用不好则会产生性能上的问题，如lide ‘%5400%’ 这种查询不会引用索引，而like ‘X5400%’ 则会引用范围索引。

7、where后面的条件顺序影响

where子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：

select * from zl_yhjbqk where dy_dj=‘1KV以下‘ and xh_bz=1;

select * from zl_yhjbqk where dy_dj=1 and dy_dj=‘1KV以下‘; 123以上两个查询，两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj=‘1KV以下’条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较。而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

8、用union替换or（适用于索引列）

通常情况下，用union替换where子句中的or将会起到较好的效果。对索引列使用or将造成全表扫描。注意：这个规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择or而降低。下面的例子中loc_id和region上都有建索引。

（低效）select loc_id,loc_desc,begion from location where loc_id=10 or begion=‘MELBOURNE‘; （高效）select loc_id,loc_desc,begion from location where loc_id=10 union select loc_id,loc_desc_begion from location where begion=‘MELBOURNE‘; 129、优化group by

提高group by语句的效率，可以通过将不需要的记录在group by之前过滤掉。

（低效）select [job],avg([sal]) from [emp] group by [job] having job=‘PRESIDENT‘ or job=‘MANAGER‘; （高效）select [job],avg([sal]) from [emp] where [job]=‘PRESIDENT‘ or job=‘MANAGER‘ group by [job];1210、使用存储过程

可以考虑使用存储过程封装那些复杂的SQL语句或业务逻辑，这样有几个好处：

（1）存储过程的执行计划可以被缓存在内存中较长的时间，减少了重新编译的时间。

（2）存储过程减少了客户端和服务器的繁复交互。

（3）如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。

11、用sp_configure ‘query governor cost limit’或者SET QUERY_GOVERNOR_COST_LIMIT来查询消耗的资源。当评估查询消耗的资源超出时，服务器自动取消查询,在查询之前就扼杀掉。SET LOCKTIME设置锁的时间。

12、使用select top或set rowcount来操作的行。

13、如果使用了in或or等时发现查询没有走索引，使用显式申明指定索引： SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN (‘男’，‘女’)。

14、 如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值。存储过程就没有这些动作: 方法：Create procere p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

15、分析select emp_name form employee where salary>3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

三、处理百万级以上数据提高查询速度的方法

1、尽量避免在where子句中使用!=或<>操作符，否则将使引擎放弃使用索引而进行全表扫描。

2、应考虑在where及order by涉及的列上建立索引。

3、尽量避免在where子句中对字段进行null值判断，否则将导致全表扫描。

4、就是避免在where子句中使用or来连接条件，否则将导致全表扫描。

select id from t where num=10 or num=20 改写为select id from t where num=10 union all select id from t where num=20 1235、尽量避免使用前置百分号。

select id from t where name like ‘%abc%‘ 16、in 和not in也要慎用，很多时候可以用exists和not exists，否则会导致全表扫描。

7、如果在where子句中使用参数，也会导致全表扫描。

select id from t where num=@num 可以改为强制查询使用索引

select id from t with(index(索引名)) where num=@num 1238、尽量避免在where子句中对字段进行表达式操作，否则将导致全表扫描。

select id from t where num/2=100 1应改为：

select id from t where num=100*2 19、尽量避免在where子句中对字段进行函数操作，否则将导致全表扫描。

select id from t where substring(name,1,3)=‘abc‘ 1应改为：

select id from t where name like ‘abc%‘ 110、并不是所有索引对查询都有效，SQL根据表中数据来进行查询优化，当索引列有大量数据重复时，SQL查询可能不会去利用索引。

11、索引并不是越多越好，索引提交了select效率，但是降低了insert和update的效率。一个表的索引数最好不要超过6个。

12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每个字符，而对于数字型而言只需要比较一次就够了。

13、尽可能使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间；其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

14、任何地方都不要使用select ，用具体的字段列表代替，不要返回用不到的字段。

15、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就考虑改写。

16、尽量避免大事务操作，提高系统并发能力。

17、利用set rowcount实现高性能的分页。

四、数据库主键选取常见的数据库主键选取方式有：

●自动增长字段● Uniqueidentifier●“COMB（Combine）”类型 1231、 自动增长字段优点：

（1）简单、效率高。 1缺点：

（1）自增一般使用int型，有数据条数的。（2）在数据库进行数据合并时会 比较麻烦。 122、GUID优点：

（1）安全，保证唯一性。（2）不会产生自增字段那样数据合并时的问题。 12缺点：

（1）它的长度是16字节，占用大量存储空间。（2）该数据类型毫无规律，要在上面建立索引很耗时，所以效率要比使用自增字段低。————————————————版权声明：本文为CSDN博主「?好坏皆为经历。」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/LiTangYuan/article/details/96867580

数据库如何优化

标签：数据类型如何方法安全全表扫描csdn通配计算替换