Mysql SQL优化基础

sql的查询优化

• 查询速度为什么这么慢？
• 优化数据访问
• 重构查询的方式
• 查询缓存
• 查询优化器的提示
• 优化特定类型的查询

一、查询速度为什么这么慢？

如果把查询看作是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间。如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行的更快,通常来说,查直询的生命周期大致可以按照顺字来看:从客户端,到服务器,然后再服务器上进行解析,生成执行计划,执行,并返回结果给客户端。其中“执行”可以认为是整个声明周期中最重要的阶段,这其中包括大量为了检素数据到存储引隊的调用以及调用后的数据处理,包括排序、分组等
Mysql使用SHOW PROFILE剖析单条查询的性能和所有的子任务详情，可以看这篇文章：
Mysql使用SHOW PROFILE

二、优化数据访问

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据。大部分性能低下的查询都可以通过减少访词的数据量的方式进行优化对于低效的查询,我们发现通过下面两个步骤来分析总是很有效
1.确认应用程序是否在检索大量超过需要的数据。
　　是否向数据库请求了不需要的数据，有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这会给MySL服务器带来额外的负担,并増加网络开销另外也会消耗应用服务器的CPU和内存资源。如
a. 査询了不需要的记录
　　也就是在查询当中，查询的结果超过了业务当中需要的数据量。例如分页查询却一次返回了所有页的数据，或者业务当中不需要的字段属性也在结果中返回，像SELETE * …这样的操作
b. 多表关联时返回全部列
　　多表关联查询时，像关联的外键会在两张表当中都存在，外键就不要再返回结果中了。例如t_user 的 id 和 t_class表中的外键 user_id，关联查询时user_id列明显就是多余的
c. 总是获得全部列
　　像SELETE * …这样的操作，即使需要返回全部的列，使用SELETE column1,column2 … 都要比SELETE * 性能更好
d. 重复查询相同的数据
　　多次访问的数据，我们使用缓存（Redis）来替代sql的查询
2.确认 MYSQL服务器层是否在分析大量超过需要的数据行。
　　返回的行数和扫描的行数比例存在很大的差异，例如没有使用索引进行查询时，返回的结果只有一条，但是使用的ALL全表扫描。mysql需要从磁盘中把表所有的数据读取出来，然后进行检索。这也是推荐使用索引的原因。扫描的行数和返回的行数比例约大，索引的使用效率就越高，查询越精确！
是否在扫描额外的记录
　　在确定查询只返回需要的数据以后,接下来应该看看查询为了返回结果是否扫描了过多的数据。对于
MYSQL,最简单的衡量查询开销的三个指标如下:
　　①响应时间
　　从发起查询到结果返回总的时间，包过连接，锁的时间，数据传输…
　　②扫描行数
　　③返回行数
　　没有哪个指标能够完美地衠量査询的开销,但它们大致反映了 MYSQL在内部执行查询时需要访问多少
数据,并可以大概椎算出査询运行的时间。这三个指标都会记录到 MYSQL的慢查询日志中,所以检查慢查
询日志是找出扫描行数过多的查询的好方法。

三、重构查询的方式

在优化有问题的查询时,目标应该是找到一个更优的方法获得实际需要的结果,而不一定总是要从MYSQL获取一模一样的结果集。有时候,可以将査询转换一种写法让其返回一样的结果,但性能更好。但也可以通过修改应用代码,用另一种方式完成查询,最终达到一样的目的。
◆一个复杂查询还是多个简单查询
　　从数据库中获取结果，并不一定需要一条SQL一次性获取到数据，像存储过程，或者在业务层多次请求来达到最终结果的目的。例如第一次请求获取用户的id，第二次请求获取用户的订单详情。现在来说，数据库连接的性能消耗是可以忽略不计的，但也要避免很大的for循环请求数据库
◆切分查询
　　切分查询常见的就是分页查询了，在返回结果数据量特别大的情况下，切分查询会比一次性查询返回效果好很多，例如业务需要返回id<=15000 条数据，可以切分成三次5000 条记录，3次切分查询总的消耗会远远小于一次返回的消耗
◆分解关联查询
　　分解关联查询的优势：①让缓存的效率更高②减少锁竟争③应用层做关联④查询效率提升⑤减少元余记录查询

四、查询缓存

引用yongqi_wang的一篇博文：MySQL高级特性四：查询缓存https://blog.csdn.net/yongqi_wang/article/details/86674088

五、查询优化器提示

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：
　　查询结果是否进行缓存。这个是需要设置缓存的状态　query_cache_type：OFF/ON/DEMAND。DEMAND表示只有在查询语句中明确写明SQL_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开销