读高性能mysql的零碎整理3--查询优化_mysql 在偏移量非常大时,limit 10000,20-CSDN博客

本文链接：https://blog.csdn.net/he_yanghui/article/details/119324161

查询性能优化：

是否向数据库请求了不需要的数据
MySQL是否在扫描额外的记录
重构查询的方式：
在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果—而不一定总是需要从MySQL获取一模一样的结果集。有时候，可以将查询转换一种写法让其返回一样的结果,但是性能更好。但也可以通过修改应用代码,用另一种方式完成查询，最终达到一样的目的。
3.1 切分查询，如下sql

DELETE FROM messages WHERE created <DATE_SUB(NON(),INTERVAL 3 MONTH);
--那么可以用类似下面的办法来完成同样的工作:
    
rows_affected = 0
do {
    rows_affected = do_query(
    DELETE FROM messages wHERE created < DATE_SUB(NON(),INTERVAL 3 MONTH)
    LIMIT10000")
} while rows_affected > 0

一次删除一万行数据一般来说是一个比较高效而且对服务器些E影响也最小的做法（如果是事务型引擎，很多时候小事务能够更高效)。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。
3.2 分解关联查询，如下sql

SELECT * FROM tag
J0IN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag=" mysq1';
--可以分解成下面这些查询来代替:
mysq1>SELECT *FROM tag WHERE tag='mysq1';
mysql>SELECT *FROMtag_post WHERE tag_id=1234;
mysql>SELECT * FROMpost wHERE post.id in (123,456,567,9098,89o4);

这样分切的好处：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN()中就可以少几个
ID。另外，对MySQL的查询缓存来说@6，如果关联中的某个表发生了变化,那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。我们后续将详细介绍这点。
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。

4. 查询执行的基础 据图6-1，我们可以看到当向MySQL发送一个请求的时候，MySQL到底做了些什么:
在这里插入图片描述
COUNT()的作用

COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。(关于这个话题，互联网上的一些信息是不够精确的。)
COUNT()的另一个作用是统计结果集的行数。当MySQL
确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT()的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。
我们发现一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样写意义清晰，性能也会很好。

COUNT()优化

有时候可以使用MyISAM在COUNT(*)全表非常快的这个特性，来加速一些特定条件的COUNT()的查询。在下面的例子中，我们使用标准数据库world来看看如何快速查找到所有ID大于5的城市。可以像下面这样来写这个查询:

mysql>SELECT COUNT(*)FRON world.City MHERE ID >5;

这样做可以大大减少需要扫描的行数，是因为在查询优化阶段会将其中的子查询直接当作一个常数来处理.
优化LIMIT分页

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT1000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询:

SELECT film_id,description FROM sakila.film ORDER BY title LINIT 50，5;
--如果这个表非常大，那么这个查询最好改写成下面的样子:
SELECT fi1m.film_id,film.description
FROM sakila.film
    INNERJOIN（
    SELECT film_id FROM sakila.film
    ORDER BY title LIMIT 50,5
) AS lim USING(fi1m_id);
-- 有时候也可以将LIMIT查询转换为已知位置的查询，让 MySQL通过范围扫描获得到对应的结果。
--例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为:
SELECT film_id, description FROM sakila.film 
WHERE position BETHEEN 50 AND 54 ORDER BY position;

优化UNION查询

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

如何配置和维护查询缓存

query_cache_type
是否打开查询缓存。可以设置成OFF、ON或DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。
query_cache_size
查询缓存使用的总内存空间，单位是字节。这个值必须是1024的整数倍，否则MySQL 实际分配的数据会和你指定的略有不同。
query_cache_min_res_unit
在查询缓存中分配内存块时的最小单位。在前面我们已经介绍了这个参数，后面我们还将进一步讨论它。
query_cache_limit
MySQL 能够缓存的最大查询结果。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以只有当结果全部返回后，MySQL才知道查询结果是否超出限制。
如果超出，MySQL则增加状态值Qcache_not_cached，并将结果从查询缓存中删除。如果你事先知道有很多这样的情况发生，那么建议在查询语句中加入SQL_NO_CACHE来避免查询缓存带来的额外消耗。
query_cache_wlock_invalidate
如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果。这个参数默认是OFF，这可能在一定程序上会改变服务器的行为，因为这使得数据库可能返回其他线程锁住的数据。将参数设置成ON,则不会从缓存中读取这类数据，但是这可能会增加锁等待。对于绝大数应用来说无须注意这个细节，所以默认设置通常是没有问题的。
如果不想所有的查询都进入查询缓存，但是又希望某些查询走查询缓存，那么可以将query_cache_type设置成DEMAND，然后在希望缓存的查询中加上SQL_CACHE。这虽然需要在查询中加入一些额外的语法，但是可以让你非常自由地控制哪些查询需要被缓存。相反，如果希望缓存多数查询，而少数查询又不希望缓存，那么你可以使用关键字SQL_NO_CACHE。