华为的一条数据库查询优化笔试题

Question

表有一千万条数据以上,有字段eno,ename,sal,未建立索引,现在要执行select substr(ename,2,2) from emp ,请问怎样修改可以优化查询速度？（可以包括修改查询语句和修改表结构等）

Answer 1

建议楼主去数据库开发板块那里去问一下，那里有csdn的老大。^_^

Answer 2

1. 增加索引。 当查询条件有的时候有效，当然还要看索引的查询条件和索引是否对应，所以这个应该没什么效果
2. 创建视图。 按查询结果创建视图。应该会有所提速吧
3.  增加subEname字段， select subEname from emp。
以上我都不是十分的确定，等着高手指导。

Answer 3

我觉得增加视图应该没什么变化吧 如果增加一个存储substr的字段就是再一次查询的时候会提高速度

Answer 4

一张有1000w级别的table最好别深层次改动表结构（除非优化到么的办法了，那就拆ename字段，那个比较高深且代价昂贵不确定是否能优化）
首先ename加一个索引是完全ok的
然后是查询，每次过滤查询不超过此table所有数据的20%为佳，查询还有个原则就是where后右操作优先
例如：
select * from tab where 1=1 and 2=2
先过滤2=2 再1=1
所以最后优化结果：select substr(t.ename,2,2) from emp t where LENGTH(t.ename)>=3 and t.ename is not nullsubstr() 各个db厂商不知道怎么实现的，如果还能优化可以再此再动动点子，原则还是把substr()集合再提前先缩小一些
以上为按照记忆中优化原则而来，欢迎指正

Answer 5

人家问你
select substr(ename,2,2) from emp
整表查询
你居然说建索引，呵呵，你被人家故意说的“没有索引”骗了。因为这样的整表查询建索引没有用的。如果是经常发生这样的substr(ename,2,2)查询，最好是把这个用一个单独的字段表示

Answer 6

严重同意 这中情况下 index是没用的. 

Answer 7

这个题目面试程序员说明面试者自己就是个狗屁。这是面试架构师的题目。
从1000万条数据库中提取所有记录（从实际情况看，ename >=3的条件基本是全满足）。
即使每条记录只有2byte(如果是双字节是4，UTF-8是6，UTF-16是8)，总数据量是：
20000000byte/1024 约 = 20000m.
20000m/1024 约 = 20G.
你们见过什么样的数据库，什么样的硬件能提供在数据库地址空间内创建这么大的临时结果集？这完全是架构总题，绝对不是结构问题。

Answer 8

20000000byte/1024 约= 20000k
=.=

Answer 9

答:你这个计算题是怎么算的?????
20000000byte/1024 约 = 20000m. 
20000m/1024 约 = 20G. 
你再想想!!

Answer 10

晕，我确实算错了。
但是1000万条数据，直接提取，先不说substr,就说en已经是length =2了，不加任何条件查询看看。这样的数据绝对不是修改结构和优化的问题，应该分区并行处理。

Answer 11

我以前给银行写cobol的时候数据经常是20m记录以上的（测试环境）,生产环境据说一天可以有100m记录以上。
晚上跑批量谁程序没写好一不小心就一夜都跑不完囧。然后被夜班跑批量的人狂bs...
数据库能容纳多少记录和数据库和硬件环境都是有关系的10m记录绝对不算多的。。

Answer 12

另外lz的这个情况建索引是没有用的.substring这种在有没有索引的情况下效率基本一样。比较好的办法就是如果这个字段总是要被分成这样就把这个字段分成两个，然后建索引才行啊。

Answer 13

是啊，其实1000万条记录不算多，正常处理就好了。
以前在IBM小机上的数据库，几个表日产数据100k以上，只要数据库规划得好，性能没有任何问题。
一般都是用的存储阵列，好像分区的用处不大（我自己的理解）

Answer 14

16楼的“如果是经常发生这样的substr(ename,2,2)查询，最好是把这个用一个单独的字段表示”和34楼的“把表分区--------每区10万条-50万条。分100---20个区。数据库会并行查询每个分区。是并行。”合起来应该是一个比较好的解决方案。呵呵，学习中。

Answer 15

50楼总结的很好，不过分区有点麻烦，多个cpu才能发挥并行的效率，而这个并没有提到。如果是单个CPU,切全表扫描（这个sql肯定是了），我想从头到尾读取，和分段读取再合并结果集，没有任何区别。

Answer 16

分区后能分到  select * from 表 （这个表数据当量小于10万是最好的）

Answer 17

首先,这里建立索引是没有用的,因为此查询没有过滤条件.
这道面试题主要考察的是你的设计能力.你要明白,在此数量级的表里substr(ename,2,2)是相当费时的,既然有如此的查询要求,就说明substr(ename,2,2)是有业务需要的,所以在设计表的时候就应该把此信息单独拿出来,增加一个字段subName,以后查询的时候直接select subName from emp就可以了.
在银行电信等大数据量的项目中,表设计是很重要的,但是目前国内很少有优秀的数据库设计人员.谢谢.

Answer 18

16楼的，每年那么多Java程序员面试受挫，有你的一份啊
学习了，呵呵

Answer 19

这个题目真正正确的答案是：
也许出题的人也没有想到，ename的substr(2,2)是什么？最多是26的平方个不重复记录。
而本题查询目的不是统计，也没有排序，所以只是想知道有哪些记录。所以本题除了查寻本身的优化外，还要考虑返回结果的优化。从查询目的而言。结果只要列出不重复的记录
即最多676条记录即可，即使要全部列出，也只要分组计数，count group  by
aa 230
ab 490
ac 212
..........
这样的结果就足够达到查询目的了。