MySQL优化总结
优化 语句执行效率的方法有哪些? 语句性能如何分析?
执行优化的具体步骤:
分析,定位
首先要分析查询速度慢原因;
根据具体问题进行优化:
优化查询过程中的数据访问
优化长难的查询语句
优化特定类型的查询语句
分析 SQL 查询慢的原因:
慢查询日志用于记录查询较慢的 语句,开启后即可记录。
分析慢查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和精力,可> 以使用工具进行分析。如: 等;(后篇介绍此工具的使用)
可以使用 命令开启,开启后服务器上执行的所有语句都会检测消耗的时间,保存到临时表中;
使用命令:
# 开启服务
# 查询所有执行的命令和消耗的时间
# 查看临时表中具体某一条语句的查询时间(可以看出语句执行时间都用在了哪里)
语句示例
命令会返回一些计数器, 会返回服务器级别的计数器;
有时间根据这些计数器,可以猜测出那些操作代价较大或者消耗较高;
命令可以观察出是否有大量的线程处于不正常的状态或特征;
可以详细的分析单条语句的执行情况和索引使用情况(后面单独一篇介绍);
优化查询过程中的数据访问;
访问的数据量太多导致查询性能下降;
确定应用程序是否在检索大量超过需要的数据,可能是太多行或者太多列;
确认 服务器是否在分析大量不必要的数据行;
避免使用以下的 语句;
查询不需要的记录时使用 解决;
多表关联返回全部数据,只取出需要的数据。比如取出 A 表的 时使用 ;
总是取出全部列, 会让优化器无法完成索引覆盖扫描的优化;
重复查询相同的数据时,可以缓存起来,下次查询时直接读取缓存即可;
检查 是否在扫描额外的数据;
使用 进行分析,如果发现查询需要扫描大量的数据,但只返回少量的行,可以使用以下的方法来解决:
使用索引覆盖扫描,把所有用的列都放在索引中,这样存储引擎不需要回表获取对应的行就可以返回结果;
可以该表数据表的结构,修改数据库的范式;
如何优化长难语句;
一个复杂的查询,和多个简单的查询哪个会更好一些呢?
内部每秒能扫描内存中上百万条的数据,相比之下响应数据给客户端时就会慢的多;
使用尽可能少的查询时最好的,但是有时将一个大的查询分解为多个小的查询时很有必要的,方便做缓存;
切分查询
将一个大的查询分解为多个小的查询;
例如:一次性删除 100 w 条数据要不一次删除 1w 暂停一会再删 1w 更加耗费服务器开销;
分解关联查询
可以将一条关联语句分解成多条 来执行,可以让缓存效率更高,执行单个查询可以减少所得竞争;
应用层做关联可以更容易对数据层进行拆分(分表,分库);
优化特定类型的查询语句
优化 查询
中的 会忽略所有的列,直接统计列数,因此不用使用 ,直接使用 ;
中没哟任何条件的 会非常快。
优化方法: 可以使用 查询近似值,用近似值代替 ;
也可以增加汇总表,每次查询时只查询汇总表就可以了,也可以把汇总表信息缓存起来;
优化关联查询
确认 或 字句上的列有索引;
确保 和 中只有一个表中的列,这样才有可能用到索引;
优化 和 语句
这两种查询均可以使用 索引来优化,最有效的优化方法;
关联查询中,使用标识列进行分组效率会更高;
如果不需要 进行 时,可以使用 ; 这样 MySQL 就不会再进行文件排序了,提高查询效率;
优化 分页
首先 偏移量大的时候,查询效率较低;可以记录上次查询的最大ID,下次查询时直接根据该ID来查询;
优化 语句
的效率高于 , 尽量使用 可以在应用层进行去重;
领取专属 10元无门槛券
私享最新 技术干货