数据库SQL优化：百万级数据库优化方案总结

1. 优化查询，尽量避免全表扫描。首先考虑对where和order by涉及的列创建索引。 ？可以使用 NOT NULL 来填充数据库。

备注、描述、评论等可以设置为NULL。对于其他人来说，最好不要使用NULL。

不要以为NULL不需要空间。例如：char(100) 类型。一旦创建了字段，空间就固定了。无论是否输入值（也包括NULL），都会占用100个字符的空间。如果它是可变长度字段，例如 varchar，则零不占用空间。

您可以将num设置为默认值0，以确保表中没有空值，然后像这样查询：

select id from t where num = 0

3。您应该尽量避免在中使用!=或运算符where -子句，否则引擎放弃使用索引并执行全表扫描。

4.应尽量避免在where子句中使用or来链接条件。如果某个字段有索引，而某个字段没有索引，则会导致引擎放弃使用索引而进行全表扫描，例如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

5 。 in 和 not i 也应该谨慎使用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续值，如果可以使用 Between，就不要使用 i：

select id from t where num between 1 and 3

多次使用这是一个很好的选择，可以在以下位置找到：

select num from a where num in(select num from b)

替换为以下语句：

select num from a where exists(select 1 from b where num=a.num)

6.以下查询也会导致全表扫描：

select id from t where name like ‘%abc%’

为了提高效率，您可以考虑整个文本获取。

7.如果where子句中使用了参数，也会导致全表扫描。由于 SQL 仅在运行时解析局部变量，因此优化器无法将访问计划选择推迟到运行时；它必须在编译时做出选择。然而，如果访问计划是在编译时创建的，则变量的值仍然是未知的，不能用作索引选择的输入。例如，以下语句将执行全表扫描：

select id from t where num = @num

您可以更改它以强制搜索使用索引：

select id from t with(index(索引名)) where num = @num

8。您应尽量避免对where子句中的字段进行表达式操作，这将导致直到引擎放弃使用索引。并执行全表扫描。例如：

select id from t where num/2 = 100

应更改为：

select id from t where num = 100*2

9。 应尽量避免对where子句中的字段进行函数操作，这会导致引擎放弃使用索引而进行全表扫描。例如：

select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

应改为：

select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

10。不要在where子句中“=”左侧进行函数、算术运算或其他表达式运算，否则系统可能无法正确使用索引。

11.使用索引字段作为条件时，如果索引是复合索引，必须使用索引中的第一个字段作为条件，以保证系统使用该索引，否则索引将不使用，并且字段顺序应尽可能与索引顺序一致。

12.不要写无意义的问题。例如，如果需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这种代码不会返回任何结果集，但会消耗系统资源。应该改成这样：

create table #t(…)

13.更新语句，如果只改变1、2个字段，不要更新所有字段，否则频繁调用会造成明显的性能消耗，并带来大量日志。 ？

15.从表格中选择数字(*)；这样不带任何条件的计数会导致全表扫描，没有业务意义，所以一定要避免。

16.索引越多越好。索引虽然可以提高对应数组的效率，但是也降低了插入和更新的效率，因为索引可以在插入或更新的过程中重建，那又怎样呢？建立索引需要仔细考虑，并且取决于具体情况。一个表上的索引最好不要超过6个。如果太多，考虑是否有必要对一些不常用的列建立索引。

17.应尽可能避免更新聚集索引数据列，因为聚集索引数据列的顺序就是表记录的物理存储顺序。当列值发生变化时，整个表记录的顺序将会调整。它使用大量资源。如果应用系统频繁需要更新聚集索引数据列，可以考虑是否将索引构建为聚集索引。

18. 尝试使用数字字段。如果字段只包含数字信息，尽量不要将其设计为字符字段。这会降低查询和连接的性能，并增加存储成本。这是因为引擎在处理查询和连接时会逐一比较字符串中的每个字符，而对于数字类型只需比较一次就足够了。

19.尽量使用varchar/nvarchar代替char/nchar，因为主要是变长字段存储空间很小，可以节省存储空间。其次，对于查询来说，在相对较小的领域内搜索效率明显更高。

20.不要在任何地方使用 select * from t ，用特定字段列表替换“*”，并且不返回任何未使用的字段。

21. 尝试使用表变量而不是临时表。如果表变量包含大量数据，请注意索引非常有限（仅限主键索引）。

22.避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并非无用，正确使用它们可以使某些例程更加高效，例如当您需要重复引用大表或经常使用的表中的数据集时。但是，对于一次性事件，最好使用导出表。

23.创建临时表时，如果一次插入的数据量较大，可以使用select into代替create table，避免造成大量日志，提高速度；如果数据量不大，要减轻系统负担。对于表资源，应该先创建表，然后插入。

24.如果使用临时表，必须在存储过程结束时显式删除所有临时表，先truncate表，然后drop表。这可以避免系统板的长期锁定。

25. 尽量避免使用记号笔，因为记号笔的效果较差。如果游标控制的数据超过10000行，就要考虑重写。

26.在使用基于指针的方法或临时表方法之前，应该首先寻找基于集合的解决方案来解决问题。基于集合的方法通常更有效。

27.就像临时表一样，标记也不是没有用的。对小数据集使用 FAST_FORWARD 指针通常比其他逐行处理方法更好，特别是当必须引用多个表来获取所需数据时。在结果集中包含“总计”的例程通常比使用游标更快。如果开发时间允许，请尝试基于指针的方法和基于集合的方法，看看哪种方法效果最好。

28.在所有存储过程和触发器的开头设置 SET NOCOUNT ON，并在结尾设置 SET NOCOUNT OFF。无需在每个存储过程和触发器语句之后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务，提高系统的并发性。

30. 尽量避免向客户端返回大量数据。如果数据量太大，就应该考虑相应的要求是否合理。

真实案例分析：拆分大型DELETE或INSERT语句并批量提交SQL语句

如果您需要在网站上执行大型DELETE或INSERT查询，您必须非常小心，避免您对整个网站进行操作变得没有反应。因为这两个操作都会锁表，所以锁表的时候其他操作都不能进入。

Apache 将有许多子进程或线程。因此，它的工作效率非常高，我们的服务器不希望有太多的子进程、线程和数据库连接。这会占用大量的服务器资源，尤其是内存。

如果你锁表一段时间，比如30秒，那么对于一个访问量较高的站点，这30秒期间积累的访问进程/线程数、数据库连接数和打开文件数不仅可以导致你的WEB服务崩溃，同时也可能导致你的整个服务器立刻挂掉。

所以，如果你有一个大进程，你需要对其进行拆分，而使用 LIMIT oracle(rownum), sqlserver(top) 条件是一个好方法。这是一个 mysql 示例：

while(1){

 　　//每次只做1000条

　　 mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);

 　　if(mysql_affected_rows() == 0){

　　 　　//删除完成，退出！
　　 　　break；
　　}

//每次暂停一段时间，释放表让其他进程/线程访问。
usleep(50000)

}