MySQL - stay in cache priority for queries
First of all, there are two caches relevant to your query.

The Query cache uses SQL_(NO)_CACHE and is the less important cache.  For production systems with lots of writes, you may as well turn off the QC.  Every write to a table flushes all entries in the QC for the table(s) involved.  That leads to another point:  A large QC can lead to a lot of flushing activity.  Limit query_cache_size to no more than about 50M.  The QC can help with CPU.

The buffer_pool is the important cache for InnoDB.  You should give about 70% of RAM to innodb_buffer_pool_size.  That is where blocks of InnoDB data and indexes are cached.  It helps because it avoids much of the I/O.

Often, the most important thing to do is to identify the slowest queries (using SHOW PROCESSLIST and/or the slowlog) and work on speeding them up.  Sometimes adding an index, sometimes a minor change to a SELECT, etc, can speed up the system far more than tweaking the cache sizes.

The buffer_pool has a way to avoid letting a table scan blow out the cache.  The QC has no mechanism like what you are asking for.

If you do keep the QC on, then set query_cache_type=DEMAND and use SQL_(NO)_CACHE.