是否有更好/更快的方法来定位列中具有最大值的行？
INFORMIX-SE 7.32：
我有一个大约有 5,000 nrows 的事务表。 transaction.ticket_number[INT] 是一列，每次更新特定行时，该列都会更新为下一个可用的连续票证编号。该列具有唯一索引。我目前正在使用以下 SELECT 语句来定位 max(transaction.ticket_num)：
SELECT MAX(transaction.ticket_number) FROM transaction;
由于要更新的行是根据 transaction.fk_id[INT] 进行聚集的，它在其中连接到 customer.pk_id[SERIAL]，该行物理上并不位于交易表的末尾，而是驻留在属于每个特定客户的交易行组内。我选择对属于每个客户的交易进行聚类，因为当我滚动浏览每个客户的交易时，响应时间会更快。是否有一种更快的方法可以使用上述查询来定位 max(transaction.ticket_number) ？..“transaction(ticket_number) 降序的唯一索引”是否会改善访问，或者索引是否从头到尾完全遍历？
INFORMIX-SE 7.32:
I have a transaction table with about 5,000 nrows. The transaction.ticket_number[INT] is a column which gets updated with the next available sequential ticket number every time a specific row is updated. The column is unique indexed. I'm currently using the following SELECT statement to locate the max(transaction.ticket_num):
SELECT MAX(transaction.ticket_number) FROM transaction;
Since the row being updated is clustered acording to the transaction.fk_id[INT], where it is joined to customer.pk_id[SERIAL],the row is not physically located at the end of the transaction table, rather it resides within the group of transaction rows belonging to each particular customer. I chose to cluster the transactions belonging to each customer because response time is faster when I scroll through each customers transaction. Is there a faster way of locating the max(transaction.ticket_number) with the above query?.. Would a 'unique index on transaction(ticket_number) descending' improve access or is the indexed fully traversed from begining to end irrelevantly?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在现代机器上只有 5000 行的表上，您不太可能能够测量各种技术的性能差异，特别是在我相信您面临的单用户场景中。即使 5000 行全部达到最大允许大小（略低于 32 KB），您也将处理 160 MB 的数据，这可以轻松放入计算机的缓存中。实际上，我确信您的行要小得多，并且您永远不需要缓存中的所有数据。
除非您有明显的性能问题，否则请使用票号列上的索引并依靠服务器 (Informix SE) 来完成其工作。如果您有明显的问题，请显示 SET EXPLAIN 输出中的查询计划。然而，SE 性能的调整程度存在重大限制 - 它是安装即用技术，对调整的要求极低。
我不确定 Informix SE 是否支持 Informix Dynamic Server 支持的“FIRST n”（又名“TOP n”）表示法；我相信不会。
On a table of only 5000 rows on a modern machine, you are unlikely to be able to measure the difference in performance of the various techniques, especially in the single-user scenario which I believe you are facing. Even if the 5000 rows were all at the maximum permissible size (just under 32 KB), you would be dealing with 160 MB of data, which could easily fit into the machine's caches. In practice, I'm sure your rows are far smaller, and you'd never need all the data in the cache.
Unless you have a demonstrable performance problem, go with the index on the ticket number column and rely on the server (Informix SE) to do its job. If you have a demonstrable problem, show the query plans from SET EXPLAIN output. However, there are major limits on how much you can tweak SE performance - it is install-and-go technology with minimal demands on tuning.
I'm not sure whether Informix SE supports the 'FIRST n' (aka 'TOP n') notation that Informix Dynamic Server supports; I believe not.
由于 NULLABLE 列和其他因素、索引的使用等，您经常会发现以下内容会更快，但通常只能忽略不计...
我也不确定您是否确实在 [ticket_number] 上有索引？或者你只有一个唯一的约束？约束无助于确定 MAX，但 INDEX 可以。
如果存在以 Ticket_number 作为第一个可索引列的 INDEX：
- 可能会使用索引查找/查找，根本不需要扫描其他值
如果 INDEX 存在且 Ticket_number 不作为第一个可索引列：
- 可能会发生索引扫描，检查索引中的每个唯一条目
如果不存在可用的索引：
- 整个表将被扫描
Due to NULLABLE columns and other factors, use of indexes, etc, you can often find the following would be faster, but normally only negligably...
I'm also uncertain as to whether you actually have an Index on [ticket_number]? Or do you just have a UNIQUE constraint? A constraint won't help determine a MAX, but an INDEX will.
In the event that an INDEX exists with ticket_number as the first indexable column:
- An index seek/lookup would likely be used, not needing to scan the other values at all
In the event that an INDEX exists with ticket_number Not as the first indexable column:
- An index scan would likely occur, checking every single unique entry in the index
In the event that no usable INDEX exists:
- The whole table would be scanned