SQL调优前言一、SELECT 优化一WHERE子句优化1、删除不必要的括号2、常量合并3、常量条件去除4、删除无效或超出范围的值5、优先查询常量表6、优化器的其他优化7、高效查询示例二范围优化1、单部索引范围访问1定义2优化过程2、多部索引范围访问1定义2索引前导列3HASH索引4BTREE索引三索引合并优化1、定义2、交叉访问算法 Using intersect3、联合访问算法 Using union4、排序联合算法 Using sort_union四索引下推优化五外连接优化六IS NULL 优化七ORDER BY 优化1、使用索引进行 ORDER BY2、使⽤filesort进行 ORDER BY八GROUP BY 优化九DISTINCT 优化十函数调用优化二、索引失效一复合索引没有遵守最左原则二WHERE中有OR且条件列没有索引三复合索引中第一个使用范围查询的条件或不以%开头的模糊查询之后的列不使用索引四Like 查询以 % 开头五Where 子句中有表达式或函数六使用 , not in三、索引使用原则结语前言本期将继续介绍MySQL的SQL调优部分内容此次要介绍的是SELECT 优化、索引失效的问题。一、SELECT 优化使用SELECT语句查询数据库中的数据是最频繁的操作优化查询是使用数据库的重中之重又由于程序员编写SQL语句时出于可读性考虑和自身水平的问题提交到MySQL服务器的SQL风格各不相同此时MySQL内置的优化器可以帮助我们完成一部分优化操作了解如何其具体的优化方法有助于写出高性能的SQL语句。一WHERE子句优化本小节虽然介绍的是SELECT 的WHERE 子句优化但同样适用于DELETE 和UPDATE 语句中的 WHERE 子句。下面我们来看以下WHERE 子句的优化场景。1、删除不必要的括号– 优化前((a AND b) AND c OR (((a AND b) AND (c AND d))))– 优化后(a AND b AND c) OR (a AND b AND c AND d)2、常量合并– 优化前(ab AND bc) AND a5– 优化后b5 AND bc AND a53、常量条件去除– 优化前(b5 AND b5) OR (b6 AND 55) OR (b7 AND 56)– 优化后b5 OR b6值得注意的是在 MySQL 8.0.14 及更高版本中对于以上常量的优化发生在准备阶段而不是优化阶段这有助于简化表连接。4、删除无效或超出范围的值– 表结构如下有⼀个TINYINT 类型的列 (1byte)CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);– 优化前SELECT * FROM t WHERE c 256;– 优化后SELECT * FROM t WHERE 1;5、优先查询常量表在MySQL中常量表分为两种类型System表只包含一行数据的表。通常用于优化查询因为其数据是固定的对查询性能影响极小。Const表经过WHERE语句中的限制条件筛选后只包含一行数据的表。这个条件通常是某列等于⼀个常量值该列上通常有索引可能是主键或唯⼀键。MySQL可以通过索引快速定位该行。以下操作都属于常量表操作– 单表中⽤主键查询SELECT * FROM t WHERE primary_key1;– 多表中⽤主键查询SELECT * FROM t1,t2 WHERE t1.primary_key1 AND t2.primary_keyt1.id;在表连接查询中读取任何其他表之前都先读取所有常量表。6、优化器的其他优化提前检测无效的常量表达式并返回空结果集。如果不使用GROUP BY或聚合函数(COUNT() MIN()等)HAVING将与WHERE合并。没有WHERE的单个表上的COUNT(*)直接从MyISAM和MEMORY表的表信息中检索。7、高效查询示例-- 统计所有⾏数SELECTCOUNT(*)FROMtbl_name;-- 聚合函数中使⽤索引列SELECTMIN(key_part1),MAX(key_part1)FROMtbl_name;-- 复合索引时where条件使⽤索引列1聚合函数中使⽤其他索引列SELECTMAX(key_part2)FROMtbl_nameWHEREkey_part1constant;-- 排序时使⽤索引列、复合索引时按索引列的顺序排序SELECT...FROMtbl_nameORDERBYkey_part1,key_part2,...LIMIT10;-- 排序时使⽤索引列复合索引时按索引列的顺序排序可以为不同的列指定排序规则SELECT...FROMtbl_nameORDERBYkey_part1DESC,key_part2DESC,...LIMIT10;二范围优化范围查询是非常常见的查询方式在范围查询的列上建立索引可以有效的提高查询效率。1、单部索引范围访问1定义单部索引范围访问的范围条件定义如下对于 BTREE 和 HASH 索引当使用 、、IN() 操作符时索引部分与常量值的比较是一个范围条件。另外对于 BTREE 索引当使用 、、、、BETWEEN、 操作符时索引部分与常量值的比较是⼀个范围条件。LIKE 的参数是一个不以通配符开头的常量字符串也是⼀个范围条件。对于所有索引类型多个范围条件用 OR 或 AND 组合形成⼀个范围条件。所谓的常量值指的是以下情况之一查询字符串中的常量表连接中 const 表或 system 表中的列由上述类型组成的表达式具体例子如下-- key_col 是表t1中定义的索引列-- key_col与常量区间进⾏⽐较SELECT*FROMt1WHEREkey_col1ANDkey_col10;-- key_col与常量进⾏⽐较并且在⼀个范围中SELECT*FROMt1WHEREkey_col1ORkey_colIN(15,18,20);-- key_col与字符串常量⽐较并且在开头不是通配符的字符串范围中SELECT*FROMt1WHEREkey_colLIKEab%ORkey_colBETWEENbarANDfoo;2优化过程MySQL尝试从 WHERE 子句中为每个可能的索引提取范围条件。在提取过程中不能用于构造范围条件的条件将被删除产⽣重叠范围的条件将被合并产生空范围的条件将被删除。例子如下普通的select语句其中key1是索引列nonkey未被索引列SELECT * FROM t1 WHERE(key1 ‘abc’ AND (key1 LIKE ‘abcde%’ OR key1 LIKE ‘%b’)) OR(key1 ‘bar’ AND nonkey 4) OR(key1 ‘uux’ AND key1 ‘z’);优化过程– 1. 提取原始的WHERE条件(key1 ‘abc’ AND (key1 LIKE ‘abcde%’ OR key1 LIKE ‘%b’)) OR(key1 ‘bar’ AND nonkey 4) OR(key1 ‘uux’ AND key1 ‘z’)– 2. 删除nonkey 4和 key1 LIKE ‘%b’ 因为它们不能⽤于范围扫描, 为了防止条件遗漏用TRUE替换这两个条件(key1 ‘abc’ AND (key1 LIKE ‘abcde%’ OR TRUE)) OR(key1 ‘bar’ AND TRUE) OR(key1 ‘uux’ AND key1 ‘z’)– 3. 合并始终为真或为假的条件(key1 LIKE ‘abcde%’ OR TRUE) – 始终为真(key1 ‘uux’ AND key1 ‘z’) – 始终为假– 4. ⽤常量替换得到如下条件(key1 ‘abc’ AND TRUE) OR (key1 ‘bar’ AND TRUE) OR (FALSE)– 5. 删除不必要的TRUE和FALSE常量(key1 ‘abc’) OR (key1 ‘bar’)– 6. 将重叠的间隔组合成⼀个产⽣⽤于范围扫描的最终条件:(key1 ‘bar’)从优化过程中可见真正用于范围扫描的条件可能比 WHERE 子句中的条件要少。同时范围条件提取算法可以处理任意深度的嵌套AND/OR构造最终输出不依赖于 WHERE 子句中条件出现的顺序。2、多部索引范围访问1定义多部索引范围访问是单部索引范围条件的扩展使用复合索引中的多个索引列进行范围查询。2索引前导列索引前导列通常是指联合索引复合索引中最左边的那一列或几列。且MySQL 使用联合索引时遵循一个非常核心的规则最左前缀法则。现在有一个以下的联合索引ALTERTABLEindex_demoADDINDEXidx_age_gender_name(age,gender,name);在这个索引中age 就是前导列最左列(age, gender) 是前导列组合。具体使用场景如下只要你查询条件里包含了最左边的 age索引就能被用上。WHERE age 20 - 走索引用到了 age。WHERE age 20 AND gender ‘男’ - 走索引用到了 age 和 gender。WHERE age 20 AND gender ‘男’ AND name ‘Tom’ - 走索引用到了全部三列。WHERE age 20 AND name ‘Tom’ - 走索引只用了 age因为跳过了 gendername 只能回表后过滤。3HASH索引HASH 索引的底层是一个哈希表。当你存入一条数据时MySQL 会对索引列的值比如 id 60000做一次哈希运算Hash Function得出一个哈希值比如算出来是 8。然后MySQL 就把这条数据的内存/磁盘地址存到哈希表里第 8 个位置。在查询时如果你查 WHERE id 60000MySQL 不用像 B 树那样一层一层往下找而是直接用同样的哈希算法算一下得到 8然后直接去第 8 个位置拿数据。时间复杂度O(1)常数级别。理论上不管表里有 100 条还是 1 亿条数据等值查询的耗时几乎是一样的极快使用例子CREATETABLEtest_memory(idINT,nameVARCHAR(20),INDEXUSINGHASH(id)-- 显式指定 HASH 索引)ENGINEMEMORY;在多部索引范围访问里对于 HASH 索引有以下条件表达式key_part1 cmp const1AND key_part2 cmp const2AND …AND key_partN cmp constN;以上 const1 , const2 , … 都是常量 cmp 是 、、is NULL 比较运算符之⼀。条件涵盖了所有复合索引列。(也就是说有N个条件每个条件对应⼀个N部索引)例如下面是⼀个由三部分组成的HASH索引的范围条件:key_part1 1 AND key_part2 IS NULL AND key_part3 ‘foo’4BTREE索引在 MySQL 中建索引时如果不指定 USING HASH默认建的就是 BTREE 索引。在 MySQL 的 InnoDB 引擎中虽然建表语句写的是 BTREE但它底层的真实数据结构其实是 B树。BTREE索引可以使⽤ , , IS NULL, , , , , !, , BETWEEN,LIKE (参数是一个不以通配符开头的常量字符串)来确定⼀个范围有如下条件key_part1 ‘foo’ AND key_part2 10 AND key_part3 10当第⼀个条件比较使用 , , IS NULL 时优化器使用索引确定单个区间。当某个条件使⽤ , , , , !, , BETWEEN, LIKE 进⾏范围检索时当前条件使用索引后续的条件全部不使用索引。进行不等于 !, 运算时优化器根据分析结果决定是否使⽤索引如果全表扫描效率更高则不使用索引。如果使用索引将会得到两个区间以上的条件表达式由于key_part2 使⽤于 key_part3不再使用索引可表示为(‘foo’,10,-inf) (key_part1,key_part2,key_part3) (‘foo’,inf,inf)在条件表达式中使用OR如果条件所覆盖的区间不同将使用两个区间的并集如下面所示– 条件表达式(key_part1 1 AND key_part2 2) OR (key_part1 5)– 条件区间(1,-inf) (key_part1,key_part2) (1,2)(5,-inf) (key_part1,key_part2)三索引合并优化1、定义多条件的查询语句中where查询通常会包含多个条件判断以AND或OR操作进行连接。在对一个表进行查询时最多只能利用该表上的⼀个索引其他条件需要在回表查询时进行判断不考虑覆盖索引的情况。当回表的记录数很多时需要进行大量的随机IO这可能导致查询性能下降。此时要用索引合并(Index Merge)来解决该问题。索引合并是通过对⼀个表同时使用多个索引进行条件扫描并将满足条件的多个主键集合取交集或并集后再进行回表可以提升查询效率。索引合并可以检索多个范围扫描的数据行并将结果合并为一个。索引合并仅合并单个表中的索引扫描而不能跨表扫描。合并的结果是多个索引扫描的并集、交集或交集的并集。值得注意的是索引合并不使用于全文索引。索引合并会在 EXPLAIN 输出中索引合并在 type 列中显示为 index_merge 。在Extra字段中显示索引合并的具体算法。示例-- 查询1SELECT*FROMtbl_nameWHEREkey110ORkey220;-- 查询2SELECT*FROMtbl_nameWHERE(key110ORkey220)ANDnon_key30;2、交叉访问算法 Using intersect索引合并交叉访问算法使用于将⼀个 WHERE 子句中的条件转换为多个不同索引的范围条件并结合 AND 一起使用。该算法使用的索引执行同步扫描并返回各索引扫描结果的交集。– ⼆级索引只能使⽤等值查询key_part部分为索引列条件个数正好与所有索引列匹配不能只是部分key_part1 const1 AND key_part2 const2 … AND key_partN constN– 使⽤主键的任何范围条件primary_key const例子-- ⼆级索引是等值查询SELECT*FROMtbl_nameWHEREkey1_part11ANDkey1_part22ANDkey22;-- 主键索引可以使⽤范围查询SELECT*FROMinnodb_tableWHEREprimary_key10ANDkey_col120;3、联合访问算法 Using union索引合并联合访问算法使用于将⼀个 WHERE 子句中的条件转换为多个不同索引的范围条件并结合 OR ⼀起使用。该算法使用的索引执行同步扫描并返回各索引扫描结果的并集。– ⼆级索引只能使⽤等值查询key_part 部分为索引列条件个数正好与所有索引列匹配不能只是部分key_part1 const1 OR key_part2 const2 … OR key_partN constN– 在InnoDB表中使⽤主键的任何范围条件primary_key const例子-- 多个单列普通索引使⽤ORSELECT*FROMt1WHEREkey11ORkey22ORkey33;-- 多个普通索引条件过滤之后使⽤ORSELECT*FROMinnodb_tableWHERE(key11ANDkey22)OR(key3fooANDkey4bar)ANDkey55;4、排序联合算法 Using sort_union联合访问算法只实现了对⼆级(普通)索引进行等值查询但更多的时候需要对⼆级索引进行范围查找。所以索引合并排序联合算法支持了⼆级索引的范围查找。根据索引查询得到主键集合对于每个主键集合进行排序然后求并集这样做的好处是扩展了使用条件增加了使用的范围缺点就是消耗更大了。例子-- ⼆级索引不要求等值查询SELECT*FROMtbl_nameWHEREkey_col110ORkey_col220;-- 使⽤复合索引时不要求所有列都匹配索引SELECT*FROMtbl_nameWHERE(key_col110ORkey_col220)ANDnonkey_col30;四索引下推优化当使用索引进行范围或不以%开头的模糊查询时会先过滤出⼀个范围如果没有索引下推则需要根据主键回表后再判断其他的where条件。如果使用复合索引且其他的条件可以用复合索引中的列判断则在存储引擎层就进行过滤而不用回表查询后再过滤最终会减少回表的次数以提升效率。五外连接优化我们知道MySQL中外连接包括LEFT JOIN 和 RIGHT JOIN。对于一个左外连接ALEFT JOINBjoin_specification , MySQL在实现的过程中有如下规则表B依赖于表A以及表A依赖的所有表。表A依赖于在 LEFT JOIN 条件中使⽤的所有表(表B除外)。LEFT JOIN 条件用于决定如何从表b中检索数据行(不使用WHERE子句中的任何条件)。执行所有标准连接优化先读取依赖的表再读取当前表, 如果存在循环依赖, 则报错。执行所有标准的 WHERE 优化。◦ 如果表A中有一行匹配WHERE条件但表B中没有匹配条件的数据行则生成⼀个额外的B行所有列都设置为NULL。RIGHT JOIN 的实现类似于 LEFT JOIN 只是表的顺序不同。在执行的过程中右连接会被转换为等效的左连接。对于外连接来说表的连接顺序不能随意交换约束条件也不能随意的下推。如果可以将外连接转换为内连接那么就可以简化查询优化过程。所以外连接在转为内连接需要有 “严格的约束条件”并且这些约束条件引用了外连接中可能会补空值的一侧即“可空侧”的表中的列。这种情况下这些约束条件能够将可空侧产生的空值过滤掉使得最终结果等同于内连接的结果。例子-- 查询所有参加考试的学⽣成绩加⼊了WHERE IS NOT NULL 过滤了右表中的NULL记录select*fromstudent stuleftjoinscore sonstu.ids.student_idwheres.scoreisnotnull;-- 查询所有分数⼤于80分以上的成绩加⼊了WHERE s.score 80 过滤了右表中的NULL记录select*fromstudent stuleftjoinscore sonstu.ids.student_idwheres.score80;六IS NULL 优化MySQL可以对 col_name IS NULL 执行与 col_name constant_value 相同的优化。例如MySQL可以使用索引和范围用 IS NULL 来搜索 NULL。– 以下SQL中使用NULL作为条件SELECT * FROM tbl_name WHERE key_col IS NULL;SELECT * FROM tbl_name WHERE key_col NULL;SELECT * FROM tbl_name WHERE key_colconst1 OR key_colconst2 OR key_col IS NULL;要注意的是如果 WHERE 子句对 NOT NULL 的列的添加 col_name IS NULL 条件则该表达式将被优化掉。所以建议在建表的时候为非空的列指定NOT NULL约束。例子id为主键在列a和列b上建立索引droptableifexistst1;createtablet1(idbigintprimarykeyauto_increment,avarchar(10)notnull,bvarchar(10),cvarchar(10),index(a),index(b));showindexfromt1;insertintot1values(null,aaa,bbb,ccc);insertintot1values(null,111,null,null);-- 主键不可能为空执⾏计划中显⽰ Impossible WHERE 表⽰当前条件永远不可能为真select*fromt1whereidisnull;-- ⾮空列不可能为空执⾏计划中显⽰ Impossible WHEREselect*fromt1whereaisnull;-- 条件组合中存在is null 也会被优化掉select*fromt1whereaaaaoraisnull;-- 查询 b列explain中显⽰使⽤了索引下推select*fromt1wherebisnull;-- 查询 c列explain中显⽰使⽤全表扫描select*fromt1wherecisnull;七ORDER BY 优化1、使用索引进行 ORDER BY在对查询结果进行ORDER BY排序时可能会使用到索引由于索引是有序的使用索引可以避免额外的排序。以下为用索引进行 ORDER BY的常见场景在表中建立复合索引 (key_part1, key_part2) 以下的查询可以使用索引来解析ORDER BY部分。排序时直接使用索引列可能使用索引避免额外排序。SELECT * FROM t1 ORDER BY key_part1, key_part2;key_part1 是常数时通过索引访问的所有行都按照key_part2排序 。SELECT * FROM t1 WHERE key_part1 constant ORDER BY key_part2;ORDER BY 对索引列进行升序或降序排序时也可能使用索引避免额外排序两列可以按同一方向排序(都是ASC或者都是DESC)也可以按相反方向排序(一个ASC一个DESC) 。在DESC排序时索引进行反向扫描。– 同是升序SELECT * FROM t1 ORDER BY key_part1 ASC, key_part2 ASC;– 同是降度SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;– 一个降序一个升序SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;key_part1与常量比较并使用key_part1排序可能使用索引避免额外排序。可能使用索引避免额外排序指的是如果查询列表中有不在索引中的列而造成回表查询的效率比直接使用索引排序的效率高优化器不使用索引排序是否使用索引避免额外排序由优化器决定有回表查询时如果WHERE足够充分让回表查询的范围更小使用索引的效率更高那就会使用索引避免额外排序。SELECT * FROM t1 WHERE key_part1 constant ORDER BY key_part1 ASC;SELECT * FROM t1 WHERE key_part1 constant ORDER BY key_part1 DESC;在InnoDB表中由于每个索引都会包含主键当查询列表中只包含主键和索引列时ORDER BY时直接使用索引避免额外排序。SELECT pk, key_part1, key_part2 FROM t1 ORDER BY key_part1, key_part2;以下情况MySQL不使⽤索引来处理ORDER BY但可以使用索引作为WHERE条件。对不同的索引使用ORDER BY– key1, key2是两个不同的普通索引SELECT * FROM t1 ORDER BY key1, key2;不符合最左原则SELECT * FROM t1 ORDER BY key1_part1, key1_part3;WHERE中的索引与ORDER BY 的索引不一致SELECT * FROM t1 WHERE key2constant ORDER BY key1;ORDER BY中对索引列进行转换和运算SELECT * FROM t1 ORDER BY ABS(key);SELECT * FROM t1 ORDER BY -key;2、使⽤filesort进行 ORDER BY如果ORDER BY不能使⽤索引避免额外的排序MySQL将执行filesort文件排序操作。 文件排序会用到额外的内存空间可以通过 sort_buffer_size 来设置排序内存的大小默认大小为 256KB 可以通过选项文件修改。–在mysqld节点下设置sort_buffer_size[mysqld]sort_buffer_size 16M也可以用以下命令查看sort_buffer_size的大小SHOWVARIABLESLIKEsort_buffer_size;如果结果集太大超出 sort_buffer_size 内存限制则文件排序会根据需要使用临时磁盘文件这样会严重影响查询效率。且如果EXPLAIN输出的Extra列包含 Using filesort 时就说明查询语句执行了文件排序需要优化。八GROUP BY 优化在执行GROUP BY分组查询时会先把符合WHERE条件的结果保存在⼀个新创建的临时表中临时表中每个分组字段的所有行都是连续的然后再分离每个组并应用聚合函数(如果有的话)。由于索引本身就是连结的通过使用索引可以避免创建临时表。例子-- 新建表并创建索引createtablet2(c1varchar(10),c2varchar(10),c3varchar(10),indexidx_c1_c2(c1,c2));处于以下分组情况时查询可以使用索引。分组时使用索引列并遵循最左原则– 分组时使⽤索引列并遵循最左原则SELECT c1, c2 FROM t2 GROUP BY c1, c2;对同⼀个索列使用MIN()或MAX()– 对同⼀个索列使用MIN()或MAX()SELECT c1, MIN(c2), MAX(c2) FROM t2 GROUP BY c1;如果使⽤其他聚合函数必须加DISTINCT– 如果使⽤其他聚合函数必须加DISTINCTSELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t2;– 对两个列进⾏COUNT()必须加DISTINCTSELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2,c1) FROM t2;查询列表中的列都在GROUP BY中的列之前之后的索引列在where中引用常量– 查询列表中的列都在GROUP BY中的列之前之后的索引列在where中引用常量SELECT c1, c2 FROM t2 WHERE c1 const GROUP BY c1, c2;SELECT MAX(c3), MIN(c3), c1, c2 FROM t2 WHERE c2 const GROUP BY c1, c2;SELECT c2 FROM t2 WHERE c1 const GROUP BY c1, c2;SELECT c1, c2 FROM t2 WHERE c3 const GROUP BY c1, c2;以下分组查询不使用索引。使用了除MIN()或MAX()之外的聚合函数 (优化器会根据索引匹配程序来决定是否使用索引)– 使用了除MIN()或MAX()之外的聚合函数SELECT c1, SUM(c2) FROM t2 GROUP BY c1;GROUP BY子句中的列没有遵循索引的最左原则– GROUP BY子句中的列没有遵循索引的最左原则SELECT c2 FROM t2 GROUP BY c2, c3;九DISTINCT 优化通常DISTINCT被视为GROUP BY的特殊情况所以GROUP BY查询的优化也适用于DISTINCT子句的查询。例如下面两个查询是等价的-- DISTINCT去重后只有⼀⾏数据SELECTDISTINCTc1,c2,c3FROMt2WHEREc1const;-- GROUP BY 分组后只有⼀⾏数据SELECTc1,c2,c3FROMt2WHEREc1constGROUPBYc1,c2,c3;十函数调用优化MySQL函数分为确定性函数和非确定性函数。多次调用同一个函数并传入相同的参数如果返回不同的结果那么这个函数就是非确定性函数。当查询中使用了非确定性函数则无法使用索引但将非确定性函数的值保存在变量中然后在查询语句中使用则可以使用索引。-- POW()是⼀个确定性的函数查询使⽤索引SELECT*FROMtWHEREidPOW(1,2);-- FLOOR()是⼀个没有确定性的函数查询不使⽤索引SELECT*FROMtWHEREidFLOOR(1RAND()*49);-- ⽤变量接收没有确定性的函数的值SETkeyvalFLOOR(1RAND()*1);-- 使⽤索引EXPLAINSELECT*FROMt1WHEREidkeyval;二、索引失效什么是索引失效简单来说就是设置好了索引却没所有使用到该索引。mysql判断全表扫描⽐索引快时不使用索引常发生在数据量极少的表中或是过滤条件效果不佳时。而索引失效主要会出现在以下的场景中。一复合索引没有遵守最左原则二WHERE中有OR且条件列没有索引三复合索引中第一个使用范围查询的条件或不以%开头的模糊查询之后的列不使用索引四Like 查询以 % 开头五Where 子句中有表达式或函数将 id列的值1后再进行条件过滤。六使用 , not in三、索引使用原则为了减少索引失效提高sql语句的查询效率我们建议在使用索引时遵循以下原则。每张表必须有主键推荐BIGINT类型且自增。DISTINCTORDER BYGROUP BYJOIN条件WHERE条件的列加索引。对频繁进行数据操作的表不要建立太多索引因为维护索引也需要很大的成本。因为每创建一个索引都会有与之对应的索引树对索引数据进行修改时也会同时修改索引树此时索引树越多效率越低。使用复合索引时遵守最左原则。多表JOIN有确定条件时WHERE class_id 1可以分成多个单表查询然后在程序中合并。 不要大量的多表连接一般连接查询的表数量控制在三个以内如果对大量表进行查询建议使用单独的SQL语句。避免在重复值太多的列上建立索引比如大部分都是相同的值查了索引之后还会回表那么还不如全表扫描。使用指定的索引use index(index_name), force index(index_name) 。创建索引时可以指定ASC DESC, create index index_name(col desc, col1 asc);。创建一个复合索引(a, b, b)相当于同时创建了(a), (a,b), (a,bc) 。创建索引之前确保当前数据库实例没有未提交的大事务防止数据锁死。对不常使使用的索引进行清理。删除无用的索引避免对执行计划造成负面的影响。结语以上就是MySQL的调优的全部内容啦。如果喜欢我的内容请点赞、收藏加关注多多支持我以及欢迎各位在评论区讨论交流并指出我的不足谢谢大家。 
长河君 · 2026/11/2 11:17:53 · 阅读 1.2k MySQL进阶教程（五）—— SQL调优（下）
SQL调优前言一、SELECT 优化一WHERE子句优化1、删除不必要的括号2、常量合并3、常量条件去除4、删除无效或超出范围的值5、优先查询常量表6、优化器的其他优化7、高效查询示例二范围优化1、单部索引范围访问1定义2优化过程2、多部索引范围访问1定义2索引前导列3HASH索引4BTREE索引三索引合并优化1、定义2、交叉访问算法 Using intersect3、联合访问算法 Using union4、排序联合算法 Using sort_union四索引下推优化五外连接优化六IS NULL 优化七ORDER BY 优化1、使用索引进行 ORDER BY2、使⽤filesort进行 ORDER BY八GROUP BY 优化九DISTINCT 优化十函数调用优化二、索引失效一复合索引没有遵守最左原则二WHERE中有OR且条件列没有索引三复合索引中第一个使用范围查询的条件或不以%开头的模糊查询之后的列不使用索引四Like 查询以 % 开头五Where 子句中有表达式或函数六使用 , not in三、索引使用原则结语前言本期将继续介绍MySQL的SQL调优部分内容此次要介绍的是SELECT 优化、索引失效的问题。一、SELECT 优化使用SELECT语句查询数据库中的数据是最频繁的操作优化查询是使用数据库的重中之重又由于程序员编写SQL语句时出于可读性考虑和自身水平的问题提交到MySQL服务器的SQL风格各不相同此时MySQL内置的优化器可以帮助我们完成一部分优化操作了解如何其具体的优化方法有助于写出高性能的SQL语句。一WHERE子句优化本小节虽然介绍的是SELECT 的WHERE 子句优化但同样适用于DELETE 和UPDATE 语句中的 WHERE 子句。下面我们来看以下WHERE 子句的优化场景。1、删除不必要的括号– 优化前((a AND b) AND c OR (((a AND b) AND (c AND d))))– 优化后(a AND b AND c) OR (a AND b AND c AND d)2、常量合并– 优化前(ab AND bc) AND a5– 优化后b5 AND bc AND a53、常量条件去除– 优化前(b5 AND b5) OR (b6 AND 55) OR (b7 AND 56)– 优化后b5 OR b6值得注意的是在 MySQL 8.0.14 及更高版本中对于以上常量的优化发生在准备阶段而不是优化阶段这有助于简化表连接。4、删除无效或超出范围的值– 表结构如下有⼀个TINYINT 类型的列 (1byte)CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);– 优化前SELECT * FROM t WHERE c 256;– 优化后SELECT * FROM t WHERE 1;5、优先查询常量表在MySQL中常量表分为两种类型System表只包含一行数据的表。通常用于优化查询因为其数据是固定的对查询性能影响极小。Const表经过WHERE语句中的限制条件筛选后只包含一行数据的表。这个条件通常是某列等于⼀个常量值该列上通常有索引可能是主键或唯⼀键。MySQL可以通过索引快速定位该行。以下操作都属于常量表操作– 单表中⽤主键查询SELECT * FROM t WHERE primary_key1;– 多表中⽤主键查询SELECT * FROM t1,t2 WHERE t1.primary_key1 AND t2.primary_keyt1.id;在表连接查询中读取任何其他表之前都先读取所有常量表。6、优化器的其他优化提前检测无效的常量表达式并返回空结果集。如果不使用GROUP BY或聚合函数(COUNT() MIN()等)HAVING将与WHERE合并。没有WHERE的单个表上的COUNT(*)直接从MyISAM和MEMORY表的表信息中检索。7、高效查询示例-- 统计所有⾏数SELECTCOUNT(*)FROMtbl_name;-- 聚合函数中使⽤索引列SELECTMIN(key_part1),MAX(key_part1)FROMtbl_name;-- 复合索引时where条件使⽤索引列1聚合函数中使⽤其他索引列SELECTMAX(key_part2)FROMtbl_nameWHEREkey_part1constant;-- 排序时使⽤索引列、复合索引时按索引列的顺序排序SELECT...FROMtbl_nameORDERBYkey_part1,key_part2,...LIMIT10;-- 排序时使⽤索引列复合索引时按索引列的顺序排序可以为不同的列指定排序规则SELECT...FROMtbl_nameORDERBYkey_part1DESC,key_part2DESC,...LIMIT10;二范围优化范围查询是非常常见的查询方式在范围查询的列上建立索引可以有效的提高查询效率。1、单部索引范围访问1定义单部索引范围访问的范围条件定义如下对于 BTREE 和 HASH 索引当使用 、、IN() 操作符时索引部分与常量值的比较是一个范围条件。另外对于 BTREE 索引当使用 、、、、BETWEEN、 操作符时索引部分与常量值的比较是⼀个范围条件。LIKE 的参数是一个不以通配符开头的常量字符串也是⼀个范围条件。对于所有索引类型多个范围条件用 OR 或 AND 组合形成⼀个范围条件。所谓的常量值指的是以下情况之一查询字符串中的常量表连接中 const 表或 system 表中的列由上述类型组成的表达式具体例子如下-- key_col 是表t1中定义的索引列-- key_col与常量区间进⾏⽐较SELECT*FROMt1WHEREkey_col1ANDkey_col10;-- key_col与常量进⾏⽐较并且在⼀个范围中SELECT*FROMt1WHEREkey_col1ORkey_colIN(15,18,20);-- key_col与字符串常量⽐较并且在开头不是通配符的字符串范围中SELECT*FROMt1WHEREkey_colLIKEab%ORkey_colBETWEENbarANDfoo;2优化过程MySQL尝试从 WHERE 子句中为每个可能的索引提取范围条件。在提取过程中不能用于构造范围条件的条件将被删除产⽣重叠范围的条件将被合并产生空范围的条件将被删除。例子如下普通的select语句其中key1是索引列nonkey未被索引列SELECT * FROM t1 WHERE(key1 ‘abc’ AND (key1 LIKE ‘abcde%’ OR key1 LIKE ‘%b’)) OR(key1 ‘bar’ AND nonkey 4) OR(key1 ‘uux’ AND key1 ‘z’);优化过程– 1. 提取原始的WHERE条件(key1 ‘abc’ AND (key1 LIKE ‘abcde%’ OR key1 LIKE ‘%b’)) OR(key1 ‘bar’ AND nonkey 4) OR(key1 ‘uux’ AND key1 ‘z’)– 2. 删除nonkey 4和 key1 LIKE ‘%b’ 因为它们不能⽤于范围扫描, 为了防止条件遗漏用TRUE替换这两个条件(key1 ‘abc’ AND (key1 LIKE ‘abcde%’ OR TRUE)) OR(key1 ‘bar’ AND TRUE) OR(key1 ‘uux’ AND key1 ‘z’)– 3. 合并始终为真或为假的条件(key1 LIKE ‘abcde%’ OR TRUE) – 始终为真(key1 ‘uux’ AND key1 ‘z’) – 始终为假– 4. ⽤常量替换得到如下条件(key1 ‘abc’ AND TRUE) OR (key1 ‘bar’ AND TRUE) OR (FALSE)– 5. 删除不必要的TRUE和FALSE常量(key1 ‘abc’) OR (key1 ‘bar’)– 6. 将重叠的间隔组合成⼀个产⽣⽤于范围扫描的最终条件:(key1 ‘bar’)从优化过程中可见真正用于范围扫描的条件可能比 WHERE 子句中的条件要少。同时范围条件提取算法可以处理任意深度的嵌套AND/OR构造最终输出不依赖于 WHERE 子句中条件出现的顺序。2、多部索引范围访问1定义多部索引范围访问是单部索引范围条件的扩展使用复合索引中的多个索引列进行范围查询。2索引前导列索引前导列通常是指联合索引复合索引中最左边的那一列或几列。且MySQL 使用联合索引时遵循一个非常核心的规则最左前缀法则。现在有一个以下的联合索引ALTERTABLEindex_demoADDINDEXidx_age_gender_name(age,gender,name);在这个索引中age 就是前导列最左列(age, gender) 是前导列组合。具体使用场景如下只要你查询条件里包含了最左边的 age索引就能被用上。WHERE age 20 - 走索引用到了 age。WHERE age 20 AND gender ‘男’ - 走索引用到了 age 和 gender。WHERE age 20 AND gender ‘男’ AND name ‘Tom’ - 走索引用到了全部三列。WHERE age 20 AND name ‘Tom’ - 走索引只用了 age因为跳过了 gendername 只能回表后过滤。3HASH索引HASH 索引的底层是一个哈希表。当你存入一条数据时MySQL 会对索引列的值比如 id 60000做一次哈希运算Hash Function得出一个哈希值比如算出来是 8。然后MySQL 就把这条数据的内存/磁盘地址存到哈希表里第 8 个位置。在查询时如果你查 WHERE id 60000MySQL 不用像 B 树那样一层一层往下找而是直接用同样的哈希算法算一下得到 8然后直接去第 8 个位置拿数据。时间复杂度O(1)常数级别。理论上不管表里有 100 条还是 1 亿条数据等值查询的耗时几乎是一样的极快使用例子CREATETABLEtest_memory(idINT,nameVARCHAR(20),INDEXUSINGHASH(id)-- 显式指定 HASH 索引)ENGINEMEMORY;在多部索引范围访问里对于 HASH 索引有以下条件表达式key_part1 cmp const1AND key_part2 cmp const2AND …AND key_partN cmp constN;以上 const1 , const2 , … 都是常量 cmp 是 、、is NULL 比较运算符之⼀。条件涵盖了所有复合索引列。(也就是说有N个条件每个条件对应⼀个N部索引)例如下面是⼀个由三部分组成的HASH索引的范围条件:key_part1 1 AND key_part2 IS NULL AND key_part3 ‘foo’4BTREE索引在 MySQL 中建索引时如果不指定 USING HASH默认建的就是 BTREE 索引。在 MySQL 的 InnoDB 引擎中虽然建表语句写的是 BTREE但它底层的真实数据结构其实是 B树。BTREE索引可以使⽤ , , IS NULL, , , , , !, , BETWEEN,LIKE (参数是一个不以通配符开头的常量字符串)来确定⼀个范围有如下条件key_part1 ‘foo’ AND key_part2 10 AND key_part3 10当第⼀个条件比较使用 , , IS NULL 时优化器使用索引确定单个区间。当某个条件使⽤ , , , , !, , BETWEEN, LIKE 进⾏范围检索时当前条件使用索引后续的条件全部不使用索引。进行不等于 !, 运算时优化器根据分析结果决定是否使⽤索引如果全表扫描效率更高则不使用索引。如果使用索引将会得到两个区间以上的条件表达式由于key_part2 使⽤于 key_part3不再使用索引可表示为(‘foo’,10,-inf) (key_part1,key_part2,key_part3) (‘foo’,inf,inf)在条件表达式中使用OR如果条件所覆盖的区间不同将使用两个区间的并集如下面所示– 条件表达式(key_part1 1 AND key_part2 2) OR (key_part1 5)– 条件区间(1,-inf) (key_part1,key_part2) (1,2)(5,-inf) (key_part1,key_part2)三索引合并优化1、定义多条件的查询语句中where查询通常会包含多个条件判断以AND或OR操作进行连接。在对一个表进行查询时最多只能利用该表上的⼀个索引其他条件需要在回表查询时进行判断不考虑覆盖索引的情况。当回表的记录数很多时需要进行大量的随机IO这可能导致查询性能下降。此时要用索引合并(Index Merge)来解决该问题。索引合并是通过对⼀个表同时使用多个索引进行条件扫描并将满足条件的多个主键集合取交集或并集后再进行回表可以提升查询效率。索引合并可以检索多个范围扫描的数据行并将结果合并为一个。索引合并仅合并单个表中的索引扫描而不能跨表扫描。合并的结果是多个索引扫描的并集、交集或交集的并集。值得注意的是索引合并不使用于全文索引。索引合并会在 EXPLAIN 输出中索引合并在 type 列中显示为 index_merge 。在Extra字段中显示索引合并的具体算法。示例-- 查询1SELECT*FROMtbl_nameWHEREkey110ORkey220;-- 查询2SELECT*FROMtbl_nameWHERE(key110ORkey220)ANDnon_key30;2、交叉访问算法 Using intersect索引合并交叉访问算法使用于将⼀个 WHERE 子句中的条件转换为多个不同索引的范围条件并结合 AND 一起使用。该算法使用的索引执行同步扫描并返回各索引扫描结果的交集。– ⼆级索引只能使⽤等值查询key_part部分为索引列条件个数正好与所有索引列匹配不能只是部分key_part1 const1 AND key_part2 const2 … AND key_partN constN– 使⽤主键的任何范围条件primary_key const例子-- ⼆级索引是等值查询SELECT*FROMtbl_nameWHEREkey1_part11ANDkey1_part22ANDkey22;-- 主键索引可以使⽤范围查询SELECT*FROMinnodb_tableWHEREprimary_key10ANDkey_col120;3、联合访问算法 Using union索引合并联合访问算法使用于将⼀个 WHERE 子句中的条件转换为多个不同索引的范围条件并结合 OR ⼀起使用。该算法使用的索引执行同步扫描并返回各索引扫描结果的并集。– ⼆级索引只能使⽤等值查询key_part 部分为索引列条件个数正好与所有索引列匹配不能只是部分key_part1 const1 OR key_part2 const2 … OR key_partN constN– 在InnoDB表中使⽤主键的任何范围条件primary_key const例子-- 多个单列普通索引使⽤ORSELECT*FROMt1WHEREkey11ORkey22ORkey33;-- 多个普通索引条件过滤之后使⽤ORSELECT*FROMinnodb_tableWHERE(key11ANDkey22)OR(key3fooANDkey4bar)ANDkey55;4、排序联合算法 Using sort_union联合访问算法只实现了对⼆级(普通)索引进行等值查询但更多的时候需要对⼆级索引进行范围查找。所以索引合并排序联合算法支持了⼆级索引的范围查找。根据索引查询得到主键集合对于每个主键集合进行排序然后求并集这样做的好处是扩展了使用条件增加了使用的范围缺点就是消耗更大了。例子-- ⼆级索引不要求等值查询SELECT*FROMtbl_nameWHEREkey_col110ORkey_col220;-- 使⽤复合索引时不要求所有列都匹配索引SELECT*FROMtbl_nameWHERE(key_col110ORkey_col220)ANDnonkey_col30;四索引下推优化当使用索引进行范围或不以%开头的模糊查询时会先过滤出⼀个范围如果没有索引下推则需要根据主键回表后再判断其他的where条件。如果使用复合索引且其他的条件可以用复合索引中的列判断则在存储引擎层就进行过滤而不用回表查询后再过滤最终会减少回表的次数以提升效率。五外连接优化我们知道MySQL中外连接包括LEFT JOIN 和 RIGHT JOIN。对于一个左外连接ALEFT JOINBjoin_specification , MySQL在实现的过程中有如下规则表B依赖于表A以及表A依赖的所有表。表A依赖于在 LEFT JOIN 条件中使⽤的所有表(表B除外)。LEFT JOIN 条件用于决定如何从表b中检索数据行(不使用WHERE子句中的任何条件)。执行所有标准连接优化先读取依赖的表再读取当前表, 如果存在循环依赖, 则报错。执行所有标准的 WHERE 优化。◦ 如果表A中有一行匹配WHERE条件但表B中没有匹配条件的数据行则生成⼀个额外的B行所有列都设置为NULL。RIGHT JOIN 的实现类似于 LEFT JOIN 只是表的顺序不同。在执行的过程中右连接会被转换为等效的左连接。对于外连接来说表的连接顺序不能随意交换约束条件也不能随意的下推。如果可以将外连接转换为内连接那么就可以简化查询优化过程。所以外连接在转为内连接需要有 “严格的约束条件”并且这些约束条件引用了外连接中可能会补空值的一侧即“可空侧”的表中的列。这种情况下这些约束条件能够将可空侧产生的空值过滤掉使得最终结果等同于内连接的结果。例子-- 查询所有参加考试的学⽣成绩加⼊了WHERE IS NOT NULL 过滤了右表中的NULL记录select*fromstudent stuleftjoinscore sonstu.ids.student_idwheres.scoreisnotnull;-- 查询所有分数⼤于80分以上的成绩加⼊了WHERE s.score 80 过滤了右表中的NULL记录select*fromstudent stuleftjoinscore sonstu.ids.student_idwheres.score80;六IS NULL 优化MySQL可以对 col_name IS NULL 执行与 col_name constant_value 相同的优化。例如MySQL可以使用索引和范围用 IS NULL 来搜索 NULL。– 以下SQL中使用NULL作为条件SELECT * FROM tbl_name WHERE key_col IS NULL;SELECT * FROM tbl_name WHERE key_col NULL;SELECT * FROM tbl_name WHERE key_colconst1 OR key_colconst2 OR key_col IS NULL;要注意的是如果 WHERE 子句对 NOT NULL 的列的添加 col_name IS NULL 条件则该表达式将被优化掉。所以建议在建表的时候为非空的列指定NOT NULL约束。例子id为主键在列a和列b上建立索引droptableifexistst1;createtablet1(idbigintprimarykeyauto_increment,avarchar(10)notnull,bvarchar(10),cvarchar(10),index(a),index(b));showindexfromt1;insertintot1values(null,aaa,bbb,ccc);insertintot1values(null,111,null,null);-- 主键不可能为空执⾏计划中显⽰ Impossible WHERE 表⽰当前条件永远不可能为真select*fromt1whereidisnull;-- ⾮空列不可能为空执⾏计划中显⽰ Impossible WHEREselect*fromt1whereaisnull;-- 条件组合中存在is null 也会被优化掉select*fromt1whereaaaaoraisnull;-- 查询 b列explain中显⽰使⽤了索引下推select*fromt1wherebisnull;-- 查询 c列explain中显⽰使⽤全表扫描select*fromt1wherecisnull;七ORDER BY 优化1、使用索引进行 ORDER BY在对查询结果进行ORDER BY排序时可能会使用到索引由于索引是有序的使用索引可以避免额外的排序。以下为用索引进行 ORDER BY的常见场景在表中建立复合索引 (key_part1, key_part2) 以下的查询可以使用索引来解析ORDER BY部分。排序时直接使用索引列可能使用索引避免额外排序。SELECT * FROM t1 ORDER BY key_part1, key_part2;key_part1 是常数时通过索引访问的所有行都按照key_part2排序 。SELECT * FROM t1 WHERE key_part1 constant ORDER BY key_part2;ORDER BY 对索引列进行升序或降序排序时也可能使用索引避免额外排序两列可以按同一方向排序(都是ASC或者都是DESC)也可以按相反方向排序(一个ASC一个DESC) 。在DESC排序时索引进行反向扫描。– 同是升序SELECT * FROM t1 ORDER BY key_part1 ASC, key_part2 ASC;– 同是降度SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;– 一个降序一个升序SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;key_part1与常量比较并使用key_part1排序可能使用索引避免额外排序。可能使用索引避免额外排序指的是如果查询列表中有不在索引中的列而造成回表查询的效率比直接使用索引排序的效率高优化器不使用索引排序是否使用索引避免额外排序由优化器决定有回表查询时如果WHERE足够充分让回表查询的范围更小使用索引的效率更高那就会使用索引避免额外排序。SELECT * FROM t1 WHERE key_part1 constant ORDER BY key_part1 ASC;SELECT * FROM t1 WHERE key_part1 constant ORDER BY key_part1 DESC;在InnoDB表中由于每个索引都会包含主键当查询列表中只包含主键和索引列时ORDER BY时直接使用索引避免额外排序。SELECT pk, key_part1, key_part2 FROM t1 ORDER BY key_part1, key_part2;以下情况MySQL不使⽤索引来处理ORDER BY但可以使用索引作为WHERE条件。对不同的索引使用ORDER BY– key1, key2是两个不同的普通索引SELECT * FROM t1 ORDER BY key1, key2;不符合最左原则SELECT * FROM t1 ORDER BY key1_part1, key1_part3;WHERE中的索引与ORDER BY 的索引不一致SELECT * FROM t1 WHERE key2constant ORDER BY key1;ORDER BY中对索引列进行转换和运算SELECT * FROM t1 ORDER BY ABS(key);SELECT * FROM t1 ORDER BY -key;2、使⽤filesort进行 ORDER BY如果ORDER BY不能使⽤索引避免额外的排序MySQL将执行filesort文件排序操作。 文件排序会用到额外的内存空间可以通过 sort_buffer_size 来设置排序内存的大小默认大小为 256KB 可以通过选项文件修改。–在mysqld节点下设置sort_buffer_size[mysqld]sort_buffer_size 16M也可以用以下命令查看sort_buffer_size的大小SHOWVARIABLESLIKEsort_buffer_size;如果结果集太大超出 sort_buffer_size 内存限制则文件排序会根据需要使用临时磁盘文件这样会严重影响查询效率。且如果EXPLAIN输出的Extra列包含 Using filesort 时就说明查询语句执行了文件排序需要优化。八GROUP BY 优化在执行GROUP BY分组查询时会先把符合WHERE条件的结果保存在⼀个新创建的临时表中临时表中每个分组字段的所有行都是连续的然后再分离每个组并应用聚合函数(如果有的话)。由于索引本身就是连结的通过使用索引可以避免创建临时表。例子-- 新建表并创建索引createtablet2(c1varchar(10),c2varchar(10),c3varchar(10),indexidx_c1_c2(c1,c2));处于以下分组情况时查询可以使用索引。分组时使用索引列并遵循最左原则– 分组时使⽤索引列并遵循最左原则SELECT c1, c2 FROM t2 GROUP BY c1, c2;对同⼀个索列使用MIN()或MAX()– 对同⼀个索列使用MIN()或MAX()SELECT c1, MIN(c2), MAX(c2) FROM t2 GROUP BY c1;如果使⽤其他聚合函数必须加DISTINCT– 如果使⽤其他聚合函数必须加DISTINCTSELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t2;– 对两个列进⾏COUNT()必须加DISTINCTSELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2,c1) FROM t2;查询列表中的列都在GROUP BY中的列之前之后的索引列在where中引用常量– 查询列表中的列都在GROUP BY中的列之前之后的索引列在where中引用常量SELECT c1, c2 FROM t2 WHERE c1 const GROUP BY c1, c2;SELECT MAX(c3), MIN(c3), c1, c2 FROM t2 WHERE c2 const GROUP BY c1, c2;SELECT c2 FROM t2 WHERE c1 const GROUP BY c1, c2;SELECT c1, c2 FROM t2 WHERE c3 const GROUP BY c1, c2;以下分组查询不使用索引。使用了除MIN()或MAX()之外的聚合函数 (优化器会根据索引匹配程序来决定是否使用索引)– 使用了除MIN()或MAX()之外的聚合函数SELECT c1, SUM(c2) FROM t2 GROUP BY c1;GROUP BY子句中的列没有遵循索引的最左原则– GROUP BY子句中的列没有遵循索引的最左原则SELECT c2 FROM t2 GROUP BY c2, c3;九DISTINCT 优化通常DISTINCT被视为GROUP BY的特殊情况所以GROUP BY查询的优化也适用于DISTINCT子句的查询。例如下面两个查询是等价的-- DISTINCT去重后只有⼀⾏数据SELECTDISTINCTc1,c2,c3FROMt2WHEREc1const;-- GROUP BY 分组后只有⼀⾏数据SELECTc1,c2,c3FROMt2WHEREc1constGROUPBYc1,c2,c3;十函数调用优化MySQL函数分为确定性函数和非确定性函数。多次调用同一个函数并传入相同的参数如果返回不同的结果那么这个函数就是非确定性函数。当查询中使用了非确定性函数则无法使用索引但将非确定性函数的值保存在变量中然后在查询语句中使用则可以使用索引。-- POW()是⼀个确定性的函数查询使⽤索引SELECT*FROMtWHEREidPOW(1,2);-- FLOOR()是⼀个没有确定性的函数查询不使⽤索引SELECT*FROMtWHEREidFLOOR(1RAND()*49);-- ⽤变量接收没有确定性的函数的值SETkeyvalFLOOR(1RAND()*1);-- 使⽤索引EXPLAINSELECT*FROMt1WHEREidkeyval;二、索引失效什么是索引失效简单来说就是设置好了索引却没所有使用到该索引。mysql判断全表扫描⽐索引快时不使用索引常发生在数据量极少的表中或是过滤条件效果不佳时。而索引失效主要会出现在以下的场景中。一复合索引没有遵守最左原则二WHERE中有OR且条件列没有索引三复合索引中第一个使用范围查询的条件或不以%开头的模糊查询之后的列不使用索引四Like 查询以 % 开头五Where 子句中有表达式或函数将 id列的值1后再进行条件过滤。六使用 , not in三、索引使用原则为了减少索引失效提高sql语句的查询效率我们建议在使用索引时遵循以下原则。每张表必须有主键推荐BIGINT类型且自增。DISTINCTORDER BYGROUP BYJOIN条件WHERE条件的列加索引。对频繁进行数据操作的表不要建立太多索引因为维护索引也需要很大的成本。因为每创建一个索引都会有与之对应的索引树对索引数据进行修改时也会同时修改索引树此时索引树越多效率越低。使用复合索引时遵守最左原则。多表JOIN有确定条件时WHERE class_id 1可以分成多个单表查询然后在程序中合并。 不要大量的多表连接一般连接查询的表数量控制在三个以内如果对大量表进行查询建议使用单独的SQL语句。避免在重复值太多的列上建立索引比如大部分都是相同的值查了索引之后还会回表那么还不如全表扫描。使用指定的索引use index(index_name), force index(index_name) 。创建索引时可以指定ASC DESC, create index index_name(col desc, col1 asc);。创建一个复合索引(a, b, b)相当于同时创建了(a), (a,b), (a,bc) 。创建索引之前确保当前数据库实例没有未提交的大事务防止数据锁死。对不常使使用的索引进行清理。删除无用的索引避免对执行计划造成负面的影响。结语以上就是MySQL的调优的全部内容啦。如果喜欢我的内容请点赞、收藏加关注多多支持我以及欢迎各位在评论区讨论交流并指出我的不足谢谢大家。