关于mysql优化问题的原理和技巧讲解

mysql优化注意点网上资料一大堆,不过个人建议还是先了解原理,然后再去看优化技巧,不仅能让你更好地因地制宜的优化,也能让你对mysql有一个新的认识高度,在此先浅谈mysql的执行过程和sql缓存,后面再更新一下

mysql整个查询执行过程，总的来说分为6个步骤：

1.客户端向mysql服务器发送一条查询请求

2.服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段

3.服务器进行sql解析、预处理、再由优化器生成对应的执行计划

4.mysql根据执行计划，调用存储引擎的api来执行查询

5.将结果返回给客户端，同时缓存查询结果

[sql缓存]

1.原理:sql缓存默认是开启的,query_cache_type='on' 则查询都会走缓存的,在解析一个查询语句前，如果查询缓存是打开的，那么mysql会检查这个查询语句是否命中查询缓存中的数据。如果当

前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行.

2.命中问题:mysql将缓存存放在一个引用表(不要理解成table，可以认为是类似于hashmap的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的、客户端协议版本号等一

些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同(例如：空格、注释)，都会导致缓存不会命中。

3.失效的情况:如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的表，其查询结果都不会被缓存。比如函数now()或者current_date()会因为不同的查询时间，返回不同的查询结

果，再比如包含current_user或者connecion_id()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义

4.缓存的优缺点和注意事项:mysql的查询缓存系统会跟踪查询中涉及的每个表，如果这些表(数据或结构)发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，mysql必须将对应表的所有缓存都设

置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

a.任何的查询语句在开始之前都必须经过检查，即使这条sql语句永远不会命中缓存

b.如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存

是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在上做一些优化，比如：

a.用多个小表代替一个大表，注意不要过度设计

b.批量插入代替循环单条插入

c.合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

d.可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要进行缓存

5.最好的用法:不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为demand，这时只有加入sql_cache的查询才会走缓存，其他查询则不会，这样可以非常*地控制哪些查询需要被缓存

-- 查看是否开启缓存

show variables like '%query_cache%';

-- 设置缓存信息

set global query_cache_type='demand' ;

set global query_cache_size='600000';

-- 不走缓存的sql

select sql_no_cache * from whelp_center_config;

-- 走缓存的sql

select sql_cache * from whelp_center_config where tnt_inst_id='mybkc1cn';

show status like 'qca%';

show status like 'com_sel%'; -- 缓存命中率 ,服务器执行了多少select语句

show status like 'last_query_cost';