SQL optimization practices from 35 seconds to 0 seconds and from 0 seconds to 25 seconds

Today, when the project is about to enter beta testing, the data in the database is increased by about 2 million to expose some performance problems. Most of the data is read for a long time. Use SQL profile to find the SQL statement: Select   *   From ( Select Orderbasicinfo. * , Row_number () Over ( Order   By Orderid) As Rownum
From  
Orderbasicinfo Join Agentbasicinfo AB On AB. agentid = Orderbasicinfo. agentid
Join Merchantbasicinfo MB On MB. merchantid = Orderbasicinfo. merchantid
Join Systemcode SC On (SC. codevalueint = Orderbasicinfo. orderstatus And SC. syscodetypeid =   600 )
Where Orderbasicinfo. orderstatus = 3

Currently, only primary keys are created in these tables, and no other indexes are created. We can see that there is an order status condition,
If no index has been created, I have created one for it. As a result, the result is immediately displayed, 0 seconds! Of course, I'm really excited.
However, it is not over yet. When I change the query condition and obtain orderstatus = 3, the query result is delayed until 25 seconds. Why? Originally, orders with orderstatus 3 in the orderbasicinfo table accounted for the vast majority, with a ratio of 99.99%! Therefore, even if a non-clustered index is created, the speed is still slow!
Conclusion:
When creating a non-clustered index, only the frequently used condition fields, and the proportion of fields is small, the non-clustered index will play a good effect, if the effect is not good, we need to weigh the problem of adding, deleting, and modifying indexes and disk space. It is not necessary to create a non-clustered index.