2.5W字详解 | 专门为 “数据分析师” 写的 “MySQL优化” 问题,真的好懂多了!(八)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 2.5W字详解 | 专门为 “数据分析师” 写的 “MySQL优化” 问题,真的好懂多了!(八)

③ 索引不能使用不等于(!= <>)或is null (is not null),否则自身以及右侧所有全部失效(针对大多数情况)。复合索引中如果有>,则自身和右侧索引全部失效。

# 针对不是复合索引的情况
explain select * from book where authorid != 1 and typeid =2 ;
explain select * from book where authorid != 1 and typeid !=2 ;

结果如下:

image.png

再观看下面这个案例:


# 删除单独的索引
drop index authorid_index on book;
drop index typeid_index on book;
# 创建一个复合索引
alter table book add index idx_book_at (authorid,typeid);
# 查看执行计划
explain select * from book where authorid > 1 and typeid = 2 ;
explain select * from book where authorid = 1 and typeid > 2 ;


结果如下:

image.png

结论:复合索引中如果有【>】,则自身和右侧索引全部失效。

在看看复合索引中有【<】的情况:

image.png

 我们学习索引优化 ,是一个大部分情况适用的结论,但由于SQL优化器等原因 该结论不是100%正确。一般而言, 范围查询(> < in),之后的索引失效。


④ SQL优化,是一种概率层面的优化。至于是否实际使用了我们的优化,需要通过explain进行推测。

# 删除复合索引
drop index authorid_typeid_bid on book;
# 为authorid和typeid,分别创建索引
create index authorid_index on book(authorid);
create index typeid_index on book(typeid);
# 查看执行计划
explain select * from book where authorid = 1 and typeid =2 ;


结果如下:

image.png

结果分析:我们创建了两个索引,但是实际上只使用了一个索引。因为对于两个单独的索引,程序觉得只用一个索引就够了,不需要使用两个。


 当我们创建一个复合索引,再次执行上面的SQL:


# 查看执行计划
explain select * from book where authorid = 1 and typeid =2 ;


结果如下:

image.png


⑤ 索引覆盖,百分之百没问题

⑥ like尽量以“常量”开头,不要以’%'开头,否则索引失效

explain select * from teacher where tname like "%x%" ;
explain select * from teacher  where tname like 'x%';
explain select tname from teacher  where tname like '%x%';


结果如下:

image.png

结论如下:like尽量不要使用类似"%x%"情况,但是可以使用"x%"情况。如果非使用 "%x%"情况,需要使用索引覆盖。


⑦ 尽量不要使用类型转换(显示、隐式),否则索引失效

explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;


结果如下:

image.png


⑧ 尽量不要使用or,否则索引失效

explain select * from teacher where tname ='' and tcid >1 ;
explain select * from teacher where tname ='' or tcid >1 ;


结果如下:

image.png

注意:or很猛,会让自身索引和左右两侧的索引都失效。


8、一些其他的优化方法

1)exists和in的优化

如果主查询的数据集大,则使用i关键字,效率高。

如果子查询的数据集大,则使用exist关键字,效率高。

select ..from table where exist (子查询) ;
select ..from table where 字段 in  (子查询) ;


2)order by优化

IO就是访问硬盘文件的次数。

using filesort 有两种算法:双路排序、单路排序(根据IO的次数)

MySQL4.1之前默认使用双路排序;双路:扫描2次磁盘(1:从磁盘读取排序字段

,对排序字段进行排序(在buffer中进行的排序)2:扫描其他字段)

MySQL4.1之后默认使用单路排序:只读取一次(全部字段),在buffer中进行排序。但种单路排序会有一定的隐患(不一定真的是“单路/1次IO”,有可能多次IO)。原因:如果数据量特别大,则无法将所有字段的数据一次性读取完毕,因此会进行“分片读取、多次读取”。

注意:单路排序 比双路排序 会占用更多的buffer。

单路排序在使用时,如果数据大,可以考虑调大buffer的容量大小:

# 不一定真的是“单路/1次IO”,有可能多次IO
set max_length_for_sort_data = 1024



 如果max_length_for_sort_data值太低,则mysql会自动从 单路->双路(太低:需要排序的列的总大小超过了max_length_for_sort_data定义的字节数)


① 提高order by查询的策略:

a.选择使用单路、双路 ;调整buffer的容量大小;

b.避免使用select * …(select后面写所有字段,也比写*效率高)

c.复合索引,不要跨列使用 ,避免using filesort

d.保证全部的排序字段,排序的一致性(都是升序或降序)


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
4月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
171 0
|
2月前
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
125 6
|
3月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
100 2
|
4月前
|
人工智能 监控 搜索推荐
实时数据分析:如何利用API优化营销决策
在数字化营销中,实时数据分析是提升决策效率的关键。通过API连接数据源与应用,可快速获取广告、用户行为等实时数据,助力敏捷优化。本文详解如何利用API:从选择集成到实施分析,再到驱动决策,涵盖CTR、ROI计算及A/B测试等实践。结合电商案例,展示如何通过API监控、调整策略以提升销售额。未来,AI与预测API将进一步推动智能化营销。
165 5
|
3月前
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
144 0
|
5月前
|
存储 关系型数据库 MySQL
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
|
5月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
7月前
|
存储 关系型数据库 MySQL
MySQL细节优化:关闭大小写敏感功能的方法。
通过这种方法,你就可以成功关闭 MySQL 的大小写敏感功能,让你的数据库操作更加便捷。
570 19
|
8月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
334 9
|
8月前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
783 9

推荐镜像

更多