共计 3828 个字符，预计需要花费 10 分钟才能阅读完成。
本篇内容主要讲解“MySQL 的查询优化方法”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“MySQL 的查询优化方法”吧!
1、简介
一个好的 web 应用，最重要的一点是有着优秀的访问性能。数据库 MySQL 是 web 应用的组成部分，也是决定其性能的重要部分。所以提升 MySQL 的性能至关重要。
MySQL 性能的提升可分为三部分，包括硬件、网络、软件。其中硬件、网络取决于公司的财力，需要白哗哗的银两，这里就不说啦。软件又细分为很多种，在这里我们通过 MySQL 的查询优化从而达到性能的提升。
最近看了一些关于查询优化的书籍，同时也在网上看一些前辈们写的文章。
以下是自己整理借鉴关于查询优化的一些总结：
回到顶部
2、截取 SQL 语句
1、全面查询日志
2、慢查询日志
3、二进制日志
4、进程列表
SHOW FULL PROCESSLIST;
。。。
回到顶部
3、查询优化基本分析命令
1、EXPLAIN {PARTITIONS|EXTENDED}
2、SHOW CREATE TABLE tab;
3、SHOW INDEXS FROM tab;
4、SHOW TABLE STATUS LIKE‘tab’;
5、SHOW [GLOBAL|SESSION] STATUS LIKE‘’;
6、SHOW VARIABLES
。。。。
ps：我自己都感觉上面都是没任何营养的东西。下面才是真正的干货哈。
回到顶部
4、查询优化几个方向
1、尽量避免全文扫描，给相应字段增加索引，应用索引来查询
2、删除不用或者重复的索引
3、查询重写，等价转换（谓词、子查询、连接查询）
4、删除内容重复不必要的语句，精简语句
5、整合重复执行的语句
6、缓存查询结果
回到顶部
5、索引优化
回到顶部
5.1、索引优点：
1、保持数据的完整性
2、提高数据的查询性能
3、改进表的连接操作（jion）
4、对查询结果进行排序。没索引将会采用内部文件排序算法进行排序，效率较慢
5、简化聚合数据操作
回到顶部
5.2、索引缺点
1、索引需要占用一定的存储空间
2、数据插入、更新、删除时会受索引的影响，性能会降低。因为数据变更索引也需要进行更新
3、多个索引，优化器需要耗时则优选择
回到顶部
5.3、索引选择
1、数据量大时采用
2、数据高度重复时，不采用
3、查询取出数据大于 20%，将采用全文扫描，不用索引
回到顶部
5.4、索引细究
资料查询：
MySQL 中的 InnoDB、MyISAM 都是 B -Tree 类型索引
B-Tree 包含：PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT
B-Tree 类型索引不支持（即字段使用以下符号时，将不采用索引）：
, , =, =, BETWEEN, !=, ,like‘%**’
【在此先介绍一下覆盖索引】
以我自己理解的方式介绍吧。覆盖索引并不是像主键索引、唯一索引一样真实存在，它只是对索引应用某些特定场景的一种定义【另一种理解：查询的列是索引列，因此列被索引覆盖】。它可以突破传统的限制，使用以上操作符，且依然采用索引进行查询。
因为查询的列是索引列，所以不需要读取行，只需要读取列字段数据就可以了。【例如你看一本书，需要找某一内容，刚好那内容出现在目录中，那就不用一页页翻了，直接在目录中定位到第几页查找】
如何激活覆盖索引呢？什么样才是特定场景呢？
索引字段，在 select 中出现就是了。
复合索引还可能有其他的特殊场景。例如，三列复合索引，仅需要在 select、where、group by、order by 中，任意一个地方出现一次复合索引最左边列就可以激活使用覆盖索引了。
查看：
EXPLAIN 中 Extra 显示有 Using index 表示这条语句采用了覆盖索引。
结论：
不建议在查询的时候使用 select*from 进行查询了，应该写需要用的字段，并且增加相应的索引，以提高查询性能。
针对以上操作符实测结果：
1、以 select*from 形式，where 中是 primary key 可以通杀【除 like】（使用主键进行查询）；index 则全不可以。
2、以 select 字段 a from tab where 字段 a《以上操作符》形式测试，结果依然可以使用索引查询。【采用了覆盖索引】
其他索引优化方法:
1、使用索引关键字作为连接的条件
2、复合索引使用
3、索引合并 or and，将涉及到的字段合并成复合索引
4、where、和 group by 涉及字段加索引
回到顶部
6、子查询优化
在 from 中为非相关子查询，可以上拉子查询到父层。在多表连接查询考虑连接代价再选择。
查询优化器对子查询一般采用嵌套执行的方式，即对父查询中的每一行，都执行一次子查询，这样子查询会执行很多次。这种执行方式效率很低。
子查询转化为连接查询优点：
1、子查询不用执行很多次
2、优化器可以根据信息来选择不同的方法和连接顺序
3、子查询的连接条件，过滤条件变成父查询的筛选条件，以提高效率。
优化：
子查询合并，若多个子查询，能合并的尽量合并。
子查询展开，即上拉变成多表查询（时刻保证等价变化）
注意：
子查询展开只能展开简单的查询，若子查询含有聚集函数、GROUP BY、DISTINCT，则不能上拉。
select * from t1 (select*from tab where id 10) as t2 where t1.age 10 and t2.age
select*from t1,tab as t2 where t1.age 10 and t2.age 25 and t2.id
具体步骤：
1、from 与 from 合并，修改相应参数
2、where 与 where 合并，用 and 连接
3、修改相应的谓词（in 改 =）
回到顶部
7、等价谓词重写：
1、BETWEEEN AND 改写为 =、= 之类的。实测：十万条数据，重写前后时间，1.45s、0.06s
2、in 转换多个 or。字段为索引时，两个都能用到索引，or 效率相对 in 好一点
3、name like‘abc%’改写成 name =’abc’and name’abd’;
注意：百万级数据测试，name 没有索引之前 like 比后一种查询快；给字段增加索引后，后面的快一点点，相差不大，因为两种方法在查询的时候都用到了索引。
。。。。
回到顶部
8、条件化简与优化
1、将 where、having（不存在 groupby 和聚集函数时）、join-on 条件能合并的尽量合并
2、删除不必要的括号，减少语法分许的 or 和 and 树层，减少 cpu 消耗
3、常量传递。a=b and b= 2 转换为 a=2 and b=2。尽量不使用变量 a = b 或 a =@var
4、消除没用的 SQL 条件
5、where 等号右边尽量不出现表达式计算；where 中不要对字段进行表达式计算、函数的使用
6、恒等变换、不等式变换。例：测试百万级数据 a b and b 10 变为 a b and a 10 and b 10 优化显著
回到顶部
9、外连接优化
即将外连接转为内连接
优点：
1、优化处理器处理外连接比内连接步骤多且耗时
2、外连接消除后，优化器选择多表连接顺序有更多选择，可以择优而选
3、可以将筛选条件最为严格的表作为外表（连接顺序最前面，是多层循环体的外循环层），
可以减少不必要的 I / O 开销，能加快算法执行的速度。
on a.id=b.id 与 where a.id=b.id 的差别，on 则表进行连接，where 则进行数据对比
注意：前提必须是结果为 NULL 决绝（即条件限制不要 NULL 数据行，语意上是内连接）
优化原则：
精简查询，连接消除，等效转换，去除多余表对象连接
例如：主键 / 唯一键作为连接条件，且中间表列只作为等值条件，可以去掉中间表连接
回到顶部
10、其他查询优化
1、以下将会造成放弃索引查询，采用全文扫描
1.1、where 子句中使用!= 或 操作符 注意：主键支持。非主键不支持
1.2、避免使用 or
经测试，并非是使用了 or 就一定不能使用索引，大多情况下是没用到索引，但还有少数情况是用到的，因此具体情况具体分析。
类似优化：
select * from tab name=’aa’or name=’bb’;
=
select * from tab name=’aa’
union all
select * from tab name=’bb’;
实测：
1、十万数据测试，没任何索引的情况下，上面比下面的查询速率快一倍。
2、三十万数据测试，aa 与 bb 都是单独索引情况下，下面的查询速率比 or 快一点。
1.3、避免使用 not in
not in 一般不能使用索引；主键字段可以
1.4、where 中尽量避免使用对 null 的判断
1.5、like 不能前置百分号 like‘%.com’
解决：
1、若必须使用 % 前置，且数据长度不大，例如 URL，可将数据翻转存入数据库，再来查。LIKE REVERSE‘%.com’;
2、使用覆盖索引
1.6、使用索引字段作为条件的时候，假若是复合索引，则应该使用索引最左边前缀的字段名
2、将 exists 代替 in
select num from a where num in(select num from b)
select num from a where exists(select 1 from b where num=a.num)
一百万条数据，筛选 59417 条数据用时 6.65s、4.18s。没做其他优化，仅仅只是将 exists 替换 in。
3、字段定义是字符串，查询时没带引号，不会用索引，将会进行全文扫描。
到此，相信大家对“MySQL 的查询优化方法”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
