如何选取优化的sql语句优先优化高并发低消耗的sql从IO消耗优化难度CPU消耗进行比较可以通过慢查询日志查询出来执行慢的语句考虑需要进行优化的sql语句# 找到配置文件‌并进行编辑#‌ Linux/Mac‌: 通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf。‌# Windows‌: 通常位于 MySQL 安装目录下的 my.ini# 开启慢查询日志 (1 或 ON 表示开启)slow_query_log1# 慢查询日志文件路径 (确保 MySQL 运行用户对该目录有写权限)slow_query_log_file/var/log/mysql/mysql-slow.log# 慢查询阈值单位秒 (支持小数如 0.5)long_query_time1# 是否记录没有使用索引的查询 (可选建议生产环境谨慎开启避免日志过大)log_queries_not_using_indexes0一、语句编写优化1.要尽量避免使用select *直接查询需要的字段这样可以提高查询时间 多查出来的数据有些字段即使不需要也会被查询增加数据传输时间SELECT *会强制数据库返回所有列即使查询条件命中了索引优化器也必须回表查询聚集索引获取完整行数据。例如联合索引idx_name_age(name, age)如果只需要name和age可以直接从索引获取但SELECT *会强制回表读取所有字段多一次B树查询2.选择合理的字段类型能用数字类型就不用字符串因为字符的处理往往比数字要慢尽可能使用小的类型比如用bit存布尔值用tinyint存枚举值等。长度固定的字符串字段用char类型。长度可变的字符串字段用varchar类型。金额字段用decimal避免精度丢失问题。3.尽量使用join语句FROM多表写法将连接条件和过滤条件全部混在WHERE中当涉及多表关联时WHERE子句会变得臃肿且难以理解并且语句易出错并且只能实现内连接如果需要实现左连接或右连接必须依赖数据库特有的()或*语法可移植性差MySQL 完全不支持Oracle专属的()外连接语法也不支持SQL Server的*隐式外连接语法。从 SQL Server 2008 开始这种语法已被移除不再支持join 小表驱动大表 因为驱动结果集越大意味着需要循环的次数越多‌INNER JOIN内连接‌仅返回两张表中连接条件完全匹配的行相当于取两表数据的‌交集‌无匹配的行直接被过滤掉。场景查询所有已下单的用户信息多表关联查询商品库存‌**LEFT JOIN左外连接**‌以左侧表为基准保留左表的全部记录右表中找不到匹配的行时对应字段自动填充为NULL。场景查询所有用户及订单情况(包含未下单用户)统计每个部门的员工数量(含空部门)‌**RIGHT JOIN右外连接**‌以右侧表为基准保留右表的全部记录左表中找不到匹配的行时对应字段自动填充为NULL实际开发中RIGHT JOIN很少使用因为LEFT JOIN交换表顺序即可实现相同效果可读性更好SQL 全连接Full Outer Join用于返回左表和右表中的所有记录。当某侧表中没有匹配行时另一侧的列将填充为 NULL。不同数据库对全连接的支持情况不同写法也有所区别适用于 Oracle, SQL Server, PostgreSQL 等MySQL ‌不支持‌FULL OUTER JOIN语法。在 MySQL 中需要通过LEFT JOIN、RIGHT JOIN和UNION来模拟实现join优化建议 1.建立索引 2.增大缓冲区order buffer大小加大max_length_for_sort_data阈值 order by优化建议 1.select字段列表 2.建立索引 3.排序字段选用主表字段不会产生临时表使用从表字段排序会产生临时表查询时间更长4.适当增加冗余字段​ 可以减少大量的连表查询因为多张表的连表查询性能很低 要注意表维护-更新数据,保证数据的一致性​ 例如员工表添加了一列是部门的名称 (违反了第三范式)5.使用between…and…代替使用大于等于和小于等于会使索引失效between…and…虽然是范围查找但可以使用索引6. 匹配前缀的字符串like关键字模糊查询在左侧使用%(‘%keyword’)是无法使用索引的可以将%写在右侧7.在字段上使用函数索引失效可以将函数定义在索引当中mysql语句CREATE INDEX 索引名 ON 表名 ((函数(列名)));8.在不要求数据去重的情况下用union all 代替union应用场景union all汇总多个渠道的订单数据合并多个结构相同的日志表union合并多个来源的用户并去重合并不同查询条件的查询结果需要部分排序的合并查询union获取排重后的数据union all包括重复数据排重的过程需要遍历、排序、比较更耗时更消耗CPU资源union连接的两个语句select的列数需要相同对应的数据类型要可以兼容或是可以自动转换列的顺序必须一致列名不需要一致二、索引设计及优化1.索引失效的情景连接使用or关键字可能导致索引失效like 索引的最左模糊查询%不能放在最前面where条件中 第一个范围查询后的字段范围条件放在了前面容易导致索引失效在条件中使用函数可能引起索引失效条件中使用表达式(、-、*、/)…2.索引优化索引能够显著的提升查询sql的性能但索引数量并非越多越好。 因为表中新增数据时需要同时为它创建索引而索引是需要额外的存储空间的而且还会有一定的性能消耗。单表的索引数量应该尽量控制在5个以内并且单个索引中的字段数最好不要超过5个。mysql使用的B树的结构来保存索引的在insert、update和delete操作时需要更新B树索引。如果索引过多会消耗很多额外的性能。选择区分度高的列‌优先在值分布广泛、重复率低的列上建立索引如用户ID、邮箱等。性别这类只有少数几个值的列索引效果很差。遵循最左前缀原则‌联合索引中查询条件必须包含索引的最左列才能有效利用索引。例如联合索引idx_name_age(name, age)单独查询WHERE age25无法使用该索引但WHERE name张三或WHERE name张三 AND age25都可以复合索引的范围大于单值索引复合索引可以包含单值索引的查询不要在有索引的列上进行运算操作可能会导致查询无法正确使用索引从而影响了查询效率。索引长度越长性能越差CREATEINDEX索引名称ON表名(列名(长度));索引选择性约接近于1越好越适合建立索引SELECTCOUNT(DISTINCTcol_name)/COUNT(*)ASfull_selectivityFROMtable_name;定期清理无用索引及时删除以释放存储空间和减少维护开销ALTERTABLEtable_nameDROPINDEXindex_name;3.MySQL支持多种索引类型‌**主键索引PRIMARY KEY**‌特殊的唯一索引不允许有空值每张表只能有一个。建表时通过PRIMARY KEY关键字定义。‌**唯一索引UNIQUE**‌保证索引列的值唯一但允许有空值一张表可以有多个唯一索引。‌**普通索引INDEX**‌最基本的索引类型没有唯一性限制单纯用于加速查询。-- 普通索引CREATEINDEX索引名ON表名(字段名);-- 唯一索引CREATEUNIQUEINDEX索引名ON表名(字段名);-- 联合索引CREATEINDEX索引名ON表名(字段1,字段2...);ALTERTABLE表名ADDINDEX索引名(字段名);ALTERTABLE表名ADDUNIQUEINDEX索引名(字段名);三、架构与运维层面优化‌读写分离‌主库承载写入请求多个从库分担查询流量避免读写争抢IO资源。‌分库分表‌单表数据量过多时按业务字段做水平分表分散单表的查询压力。‌引入缓存层‌高频低变的查询结果存入Redis直接拦截请求避免穿透到数据库。‌开启慢查询日志‌设置1s为慢查询阈值定期分析慢SQL趋势提前排查潜在性能瓶颈。‌定期索引维护‌重建碎片化严重的索引减少B树页分裂提升索引扫描的连续性。‌执行计划预校验‌新SQL上线前必须通过EXPLAIN分析执行计划禁止全表扫描的SQL直接上线。四、EXPLAIN关键字在查询语句前加上EXPLAIN关键字MySQL会返回执行计划而非实际执行查询。MySQL 8.0及以上版本也支持对UPDATE、DELETE语句使用该关键字分析‌1. 验证索引是否生效‌通过EXPLAIN的key字段验证。如果key为NULL或不是预期的索引说明索引失效。‌2. 验证覆盖索引优化‌通过Extra字段的Using index来验证。如果Extra只显示Using index说明查询完全通过索引完成没有回表。‌3. 验证JOIN优化‌通过EXPLAIN的type字段验证。如果第二张表的type是eq_ref或ref说明JOIN优化成功如果是ALL说明连接字段缺少索引。‌4. 验证排序优化‌通过Extra字段验证。如果Extra没有Using filesort说明排序利用了索引优化成功。字段名核心含义优化参考价值id查询序列号标识SELECT子句的执行顺序id相同从上到下执行id不同数值越大优先级越高用于定位复杂子查询的执行层级select_type查询类型区分简单查询、主查询、子查询、UNION联合查询等不同语句结构table当前行访问的表名确认查询涉及的表对象也可能显示派生表、临时表的标记partitions匹配的分区分区表场景下显示命中的分区名称普通表通常为NULLtype表的访问类型SQL性能核心指标从优到劣排序system const eq_ref ref range index ALL必须避免全表扫描ALLpossible_keys理论上可能用到的索引用于校验索引设计是否合理为NULL说明没有可用索引key实际最终使用的索引验证优化器是否选择了预期的索引为NULL表示未使用任何索引key_len索引使用的字节长度用于判断联合索引实际用到了多少列验证最左前缀原则是否完全生效ref与索引进行比较的列/常量显示索引匹配的来源比如const表示用常量匹配字段名表示关联其他表的列rows预估需要扫描的行数数值越小性能越好是评估查询数据检索量的核心参考filtered存储引擎返回后WHERE条件过滤后的剩余行比例理想值为100%低于10%说明过滤效率极低需要优化索引Extra额外执行信息隐藏关键性能提示比如Using index表示命中覆盖索引Using filesort表示需要额外文件排序Using temporary表示使用临时表优化技巧核心字段‌type字段优化目标将访问类型从ALL全表扫描逐步提升至range或ref级别。操作为WHERE子句中的过滤条件字段添加独立索引避免无索引的全表遍历。效果扫描行数可从百万级压缩至千级查询耗时降低90%以上。‌key字段操作执行EXPLAIN后确认key字段非空且key_len值与索引定义长度匹配。排查若possible_keys有值但key为NULL说明MySQL优化器判定索引效率低于全表扫描需调整索引选择性。‌rows字段操作通过EXPLAIN的rows字段估算扫描行数确保单表查询扫描行数不超过总数据量的10%。优化若扫描行数占比过高调整查询条件缩小过滤范围或添加联合索引减少无效扫描。Extra字段‌消除Using filesort问题现象EXPLAIN结果Extra字段出现该标识代表触发了额外的文件排序操作。优化将ORDER BY的字段加入到查询的联合索引末尾利用索引的有序性避免额外排序。‌消除Using temporary问题现象EXPLAIN结果Extra字段出现该标识代表创建了临时表存储中间结果。优化GROUP BY的字段必须建立索引避免对无索引字段进行分组聚合操作。‌强化Using index操作将查询涉及的所有字段过滤、返回、排序字段组合成联合索引实现覆盖索引。效果完全无需回表访问聚簇索引IO开销降低70%以上。