数据库查询 在一个千万级的数据库查寻中，如何提高查询效率？

1、数据库设计方面：

A.为了优化查询，我们应该尽可能避免扫描整个表，并首先考虑在where和order by中涉及的列上建立索引。

B.尽量避免判断where子句中字段的null值，否则引擎将放弃使用索引并扫描整个表，例如:

从t中选择id，其中num为空

您可以在num上设置默认值0，以确保表中的num列没有空值，然后进行如下查询:

从t中选择id，其中num=0

C.并非所有索引对查询都有效。SQL根据表中的数据优化查询。当索引列中有大量重复数据时，查询可能不会使用索引。例如，如果表中有字段sex、男性和女性，即使索引是基于sex构建的，它也不会提高查询效率。

D.索引的数量没有尽可能多。索引可以提高相应选择的效率，但也会降低插入和更新的效率。由于索引可能会在插入或更新期间重新构建，因此需要根据具体情况仔细考虑如何构建索引。一个表中的索引最好不要超过6个。如果索引太多，就需要考虑建立在一些不常使用的列上的索引是否有必要。

E.应该尽可能避免更新索引数据列，因为索引数据列的顺序是表记录的物理存储顺序，一旦列值发生变化，整个表记录的顺序就会被调整，这将消耗相当多的资源。如果应用系统需要频繁更新索引数据列，就需要考虑索引是否应该作为索引来构建。

F.尽可能使用数字字段。如果只包含数字信息的字段不应该尽可能设计成字符，这将降低查询和连接的性能，并增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中的每个字符，但是对于数字类型，它只需要比较一次。

G.尽量用varchar/nvarchar代替char/nchar，因为首先空的存储空间变长，可以节省空的存储空间。其次，对于查询来说，在相对较小的字段中搜索效率明显更高。

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

我避免频繁创建和删除临时表，以减少系统表资源的消耗。

J.临时表并不是不可用的，正确使用临时表可以使一些例程更加有效，例如，当一个大型表或公共表中的一个数据集需要被重复引用时。但是，对于一次性事件，最好使用导出表。

K.创建临时表时，如果一次插入大量数据，可以用select into代替create table，避免创建大量日志，提高速度；如果数据量不大，为了缓解系统表的资源，先创建表，再插入。

长度如果使用临时表，则必须在存储过程结束时显式删除所有临时表，首先截断表，然后删除表，以避免长时间锁定系统表。

2、SQL语句方面：

A.应该尽量避免在where子句中使用它！=或

B.尽量避免在where子句中使用或连接条件，否则引擎将放弃使用索引并扫描整个表，例如:

从t中选择id，其中num=10或num=20

您可以这样查询:

从t中选择id，其中num=10联合所有从t中选择id，其中num=20

C.in和not in也应谨慎使用，否则会导致全表扫描，例如:

从t中选择id，其中num在(1，2，3)中

对于连续值，请使用介于而不是in:

从t中选择id，其中num介于1和3之间

D.以下查询也将导致全表扫描:

从t中选择id，其中名称如“%abc%”

E.如果where子句中使用了参数，它还会导致全表扫描。因为SQL只在运行时解析局部变量，所以优化器不能将访问计划的选择推迟到运行时。必须在编译时选择它。但是，如果访问计划是在编译时建立的，变量的值仍然是未知的，因此它不能用作索引选择的输入项。以下语句将扫描整个表:

从t中选择id，其中num=@num

您可以强制查询改用索引:

用(索引名)从t中选择id，其中num=@num

F.应该尽可能避免对where子句中的字段执行表达式操作，这将导致引擎放弃使用索引并扫描整个表。例如:

从t中选择id，其中num/2=100

应为:

从t中选择id，其中num=100*2

G.应该尽可能避免对where子句中的字段执行函数操作，这将导致引擎放弃使用索引并扫描整个表。例如:

从t中选择id，其中子字符串(名称，1，3)='abc '

--以abc开头的姓名id

从t中选择id，其中datediff(day，createdate，' 2005-11-30 ')= 0 –' 2005-11-30 '

生成的id应更改为:

从t中选择id，其中名称如“abc%”从t中选择id

createdate>。='2005-11-30 '并创建日期& lt'2005-12-1′

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

I .不要写无意义的查询，如果需要生成空表结构:

从t中选择col1、col2为#t，其中1=0

这种代码不会返回任何结果集，但是会消耗系统资源，所以应该改成这样:create table #t(…)

J.很多时候用exists替换是一个不错的选择:

从a中选择num，其中num在(从b中选择num)

替换为以下语句:

从a存在的地方选择num(从b选择1，其中num=a.num)

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

长度尽量避免使用游标，因为游标效率不高。如果游标操作的数据超过10000行，应该考虑重写。

米（meter的缩写））尽量避免向客户端返回大量数据。如果数据量过大，考虑相应的要求是否合理。

名词（noun的缩写）尽量避免大型事务操作，提高系统的并发性。

o、只需要一行数据时使用LIMIT 1；

当您查找一个表时，您已经知道只有一个结果，这仅仅是因为您可能需要转到fetch游标，或者您可能需要检查返回的记录数。

在这种情况下，添加LIMIT 1可以提高性能。这样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续搜索与记录匹配的下一条数据。

p，从不按RAND()排序；

q，避免SELECT *；

r，用ENUM代替VARCHAR？

ENUM类型非常快速和紧凑。其实它装的是TINYINT，但是在外面看起来是字符串。这样，用这个字段来做一些选项列表就相当完美了。

如果您有一个字段，如“性别”、“国家”、“国籍”、“状态”或“部门”，并且您知道这些字段的值是有限和固定的，那么您应该使用ENUM而不是VARCHAR。

s，将IP地址保存为UNSIGNED INT

许多程序员会创建一个VARCHAR(15)字段来保存字符串形式的IP，而不是形状化的IP。如果通过整形存储，只需要4个字节，可以有定长字段。而且在查询上也会给你带来优势，尤其是当你需要在ip1和ip2之间使用WHERE条件:IP的时候。

我们必须使用UNSIGNED INT，因为IP地址将使用整个32位无符号整形

3、Java方面：重点内容

A.创建尽可能少的对象。

B.合理调整系统设计的位置。大量的数据操作必须与少量的数据操作分开。大量的数据操作肯定不是ORM框架做的。,

C.使用jDBC链接数据库操作数据

D.控制内存，让数据流动，而不是全部读入内存然后处理，而是边读边处理；

E.合理使用内存，并缓存一些数据