一、优化数据访问
1.确认应用程序是否在检索大量超过需要的数据。通常是访问的行太多，但也可能是访问的列太多
2.确认MySQL服务器层是否在分析大量超过需要的数据行。
优化数据的访问（减少无效的数据检索）
二、重构查询方式
1.一个复杂查询还是多个简单查询
2.切分查询
3.分解关联查询
重构查询的方式（将大而复杂的SQL进行重构为小而简单的SQL）
三、查询缓存
查询缓存是要付出代价的，对查询的性能有很大影响，但如果发现付出的代价大于带来的好处，可以考虑关闭查询缓存。默认是关闭的
当开启查询缓存后，所有以SEL开头的语句都会判断查询缓存中是否有缓存结果。如果没有再进行SQL解析、执行。 当返回结果时会判断是否可以进行缓存，如果有特殊函数则不进行缓存结果。
查询缓存的消耗：
1.检查缓存是否命中
2.对查询结果进行缓存
3.修改数据使得缓存失效
查询缓存结果是存储在内存中的，通过几个参数进行分配内存资源
Query_cache_size:查询缓存内存空间总大小
Query_cache_min_res_unit:内存最小单位
内存的分配是有MySQL服务器进行管理的，系统分配内存的操作只在初始化的时候进行分配一次。
查询缓存会导致碎片的情况：
1.多个查询同时进行结果缓存时，两个查询结果之间回收掉未使用的内存空间。
2.某个缓存失效时
实际情况很难评估查询缓存是否真正能够带来性能上的提升，而且对于性能瓶颈不在查询上的系统（如：网络传输瓶颈），查询缓存作用很小。
查询缓存是否能够发挥作用：
1.理论上可以观察打开你会关闭查询缓存时，系统的效率来决定是否开启。
大部分情况下，全局平均效率不能反映真实情况。
2.查询缓存命中率
计算公式：Qcache_hits/(Qcache_hits+Com_select)
很难判断命中率多高是合适的。如果查询缓存能够带来效率提升，大于查询缓存带来的资源消耗，30%的命中率也是对系统有很好的性能提升。
3.命中写入比例(Qcache_hits和Qcache_inserts的比值)
通常最少大于3:1，最好能打到10:1
查询缓存配置：
query_cache_type：OFF/ON/DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。
query_cache_size：总内存空空间，必须是1024的整数倍
query_cache_min_res_unit：分配内存块的最小单位
query_cache_limit：能够缓存的最大查询结果。如果大于这个值，则不进行缓存。如果明确知道查询结果大于这个值，可以使用SQL_NO_CACHE，减少资源消耗
query_cache_wlock_invalidate：如果某个数据表被其他连接锁住，是否仍然从查询缓存中返回结果。
减少碎片：
无法完全避免碎片的产生，但是可以选择合query_cache_min_res_unit，以减少碎片的大小。但是不能太小，太小则要进行更频繁的内存块申请操作。
合适大小计算公式：内存实际消耗（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache（缓存个数），计算平均每个查询缓存的大小
分析和配置查询缓存的流程图：
通用查询缓存优化：
1.用多个小表替代一个大表对查询缓存有好处。
2.批量写入时只需要做一次缓存失效，相比单条写入效率更好。
3.因为缓存空间太大，在过期操作的时候可能会导致服务器僵死。
4.无法在数据库或者表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存。
5.对写密集型应用来说，直接禁用查询缓存性能更好。
6.如果不想所有查询都进入缓存，可以设置query_cache_type为DEMAND。
四、查询优化器提示
HIGH_PRIORITY 和 LOW_PRIORITY：
告诉MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高一些、哪些相对低一些。如果是HIGH_PRIORITY则会调整到所有正在等待表锁 以便修改数据的语句之前。针对有表锁的引擎有效
DELAYED：
立即响应给客户端，只对INSER和REPLACE有效。并非所有引擎都支持，而且会导致函数LAST_INSERT_ID()无效。
STRAIGHT_JOIN：
放在SELECT关键字后面，用于固定查询中表的关联顺序按照语句的顺序进行。
SQL_SMALL_RESULT和SQL_BIG_RESULT：
对SELECT语句有效，告诉MySQL优化去对GROUP BY和DISTINCT查询如何使用临时表排序，SQL_SMALL_RESULT表示结果集很小，在内存的临时表排序。反之则很大，用磁盘临时表排序
SQL_BUFFER_RESULT：
是一种缓存策略，将查询结果放到一个临时表，消耗服务器内存。会尽快的释放表锁
SQL_CACHE和SQL_NO_CACHE：
查询结果是否进行缓存
FOR UPDATE：
对查询加锁提示 select * from test for update;
USE INDEX\IGNORE INDEX和FORCE INDEX：
USE INDEX ：指定使用的索引
IGNORE INDEX ：忽略指定索引
FORCE INDEX ：与use index基本相同，不过会告诉优化器，全表扫描的代价高于索引扫描。
五、优化特定类型的查询
1.Count()
使用近似值：执行计划中的值
汇总表
2.关联查询
确保on或者USING子句中的列上有索引。
在创建索引的时候要考虑到关联的顺序。
关联查询也可以使用组合索引
3.Group BY
如果需要对关联查询做分组，那通常采用查找表的标识列分组的效率会比其他列更高。
4.Limit
尽可能的使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。
"延迟关联"将大大提升查询效率，使MySQL扫描尽可能少的页，获取需要访问的记录后再根据关联列回原表查询需要的所有列。
5.UNION
UNION与UNION ALL的差别，union all不再创建临时表，这样在联合查询时会减少I/O开销
网友评论