| 失效链接处理 |
|
高性能：有哪些常见的 SQL 优化手段？ PDF 下载
本站整理下载：
相关截图：
主要内容：
SELECT * 会消耗更多的 CPU。
SELECT * ⽆⽤字段增加⽹络带宽资源消耗，增加数据传输时间，尤其是
⼤字段（如 varchar、blob、text）。
SELECT * ⽆法使⽤ MySQL 优化器覆盖索引的优化（基于 MySQL 优化
器的“覆盖索引”策略⼜是速度极快，效率极⾼，业界极为推荐的查询优化⽅
式）
SELECT <字段列表> 可减少表结构变更带来的影响。
普通的分⻚在数据量⼩的时候耗费时间还是⽐较短的。
如果数据量变⼤，达到百万甚⾄是千万级别，普通的分⻚耗费的时间就⾮常⻓
了。
如何优化呢？ 可以将上述 SQL 语句修改为⼦查询。
我们先查询出 limit 第⼀个参数对应的主键值，再根据这个主键值再去过滤并
limit，这样效率会更快。
阿⾥巴巴《Java 开发⼿册》中也有对应的描述：
不过，⼦查询的结果会产⽣⼀张新表，会影响性能，应该尽量避免⼤量使⽤⼦查
询。
除了⼦查询之外，还以采⽤延迟查询的⽅式来优化。
我们先提取对应的主键，再将这个主键表与原数据表关联。
相关阅读：
⾯试官：⼀千万数据，怎么快速查询？
【得物技术】MySQL 深分⻚优化
阿⾥巴巴《Java 开发⼿册》中有这样⼀段描述：
join 的效率⽐较低，主要原因是因为其使⽤嵌套循环（Nested Loop）来实现关
联查询，三种不同的实现效率都不是很⾼：
Simple Nested-Loop Join ：没有进过优化，直接使⽤笛卡尔积实现 join，
逐⾏遍历/全表扫描，效率最低。
Block Nested-Loop Join ：利⽤ JOIN BUFFER 进⾏优化，性能受到
JOIN BUFFER ⼤⼩的影响，相⽐于 Simple Nested-Loop Join 性能有所提
升。不过，如果两个表的数据过⼤的话，⽆论如何优化，Block Nested-
Loop Join 对性能的提升都⾮常有限。
Index Nested-Loop Join ：在必要的字段上增加索引，使 join 的过程中可
以使⽤到这个索引，这样可以让 Block Nested-Loop Join 转换为 Index
Nested-Loop Join，性能得到进⼀步提升。
实际业务场景避免多表 join 常⻅的做法有两种：
1. 单表查询后在内存中⾃⼰做关联 ：对数据库做单表查询，再根据查询结果进
⾏⼆次查询，以此类推，最后再进⾏关联。
2. 数据冗余，把⼀些重要的数据在表中做冗余，尽可能地避免关联查询。很笨
的⼀张做法，表结构⽐较稳定的情况下才会考虑这种做法。进⾏冗余设计之
前，思考⼀下⾃⼰的表结构设计的是否有问题。
更加推荐第⼀种，这种在实际项⽬中的使⽤率⽐较⾼，除了性能不错之外，还有
如下优势：
1. 拆分后的单表查询代码可复⽤性更⾼ ：join 联表 SQL 基本不太可能被复
⽤。
2. 单表查询更利于后续的维护 ：不论是后续修改表结构还是进⾏分库分表，单
表查询维护起来都更容易。
不过，如果系统要求的并发量不⼤的话，我觉得多表 join 也是没问题的。很多公
司内部复杂的系统，要求的并发量不⾼，很多数据必须 join 5 张以上的表才能查
出来。
知乎上也有关于这个问题的讨论：MySQL 多表关联查询效率⾼点还是多次单表
查询效率⾼，为什么？，感兴趣的可以看看。
阿⾥巴巴《Java 开发⼿册》中有这样⼀段描述：
⽹络上已经有⾮常多分析外键与级联缺陷的⽂章了，个⼈认为不建议使⽤外键主
要是因为对分库分表不友好，性能⽅⾯的影响其实是⽐较⼩的。
存储字节越⼩，占⽤也就空间越⼩，性能也越好。
a.某些字符串可以转换成数字类型存储⽐如可以将 IP 地址转换成整形数据。
数字是连续的，性能更好，占⽤空间也更⼩。
MySQL 提供了两个⽅法来处理 ip 地址
INET_ATON() ： 把 ip 转为⽆符号整型 (4-8 位)
INET_NTOA() :把整型的 ip 转为地址
插⼊数据前，先⽤ INET_ATON() 把 ip 地址转为整型，显示数据时，使⽤ IN
ET_NTOA() 把整型的 ip 地址转为地址显示即可。
b.对于⾮负型的数据 (如⾃增 ID,整型 IP，年龄) 来说,要优先使⽤⽆符号整型来存
储。
⽆符号相对于有符号可以多出⼀倍的存储空间
c.⼩数值类型（⽐如年龄、状态表示如 0/1）优先使⽤ TINYINT 类型。
d.对于⽇期类型来说， DateTime 类型耗费空间更⼤且没有时区信息，建议使⽤
Timestamp。
e.⾦额字段⽤ decimal，避免精度丢失。
f.尽量使⽤⾃增 id 作为主键。
如果主键为⾃增 id 的话，每次都会将数据加在 B+树尾部（本质是双向链表），
时间复杂度为 O(1)。在写满⼀个数据⻚的时候，直接申请另⼀个新数据⻚接着写
就可以了。
如果主键是⾮⾃增 id 的话，为了让新加⼊数据后 B+树的叶⼦节点还能保持有
序，它就需要往叶⼦结点的中间找，查找过程的时间复杂度是 O(lgn)。如果这个
也被写满的话，就需要进⾏⻚分裂。⻚分裂操作需要加悲观锁，想能⾮常低。
不过， 像分库分表这类场景就不建议使⽤⾃增 id 作为主键，应该使⽤分布式 ID
⽐如 uuid 。
相关阅读：数据库主键⼀定要⾃增吗？有哪些场景不建议⾃增？。
UNION 会把两个结果集的所有数据放到临时表中后再进⾏去重操作，更耗时，更
消耗 CPU 资源。
UNION ALL 不会再对结果集进⾏去重操作，获取到的数据包含重复的项。
不过，如果实际业务场景中不允许产⽣重复数据的话，还是可以使⽤ UNION。
对于数据库中的数据更新，如果能使⽤批量操作就要尽量使⽤，减少请求数据库
的次数，提⾼性能。
为了更精准定位⼀条 SQL 语句的性能问题，需要清楚地知道这条 SQL 语句运⾏
时消耗了多少系统资源。 SHOW PROFILE 和 SHOW PROFILES 展示 SQL 语
句的资源使⽤情况，展示的消息包括 CPU 的使⽤，CPU 上下⽂切换，IO 等待，
内存使⽤等。
MySQL 在 5.0.37 版本之后才⽀持 Profiling， select @@have_profiling 命
令返回 YES 表示该功能可以使⽤。
想要使⽤ Profiling，请确保你的 profiling 是开启（on）的状态。
你可以通过 SHOW VARIABLES 命令查看其状态：
也可以通过 SELECT @@profiling 命令进⾏查看：
默认情况下， Profiling 是关闭（off）的状态，你直接通过 SET @@profil
ing=1 命令即可开启。
开启成功之后，我们执⾏⼏条 SQL 语句。执⾏完成之后，使⽤ SHOW PROFILE
S 可以展示当前 Session 下所有 SQL 语句的简要的信息包括 Query_ID（SQL
语句的 ID 编号） 和 Duration（耗时）。
具体能收集多少个 SQL，由参数 profiling_history_size 决定，默认值
为 15，最⼤值为 100。如果设置为 0，等同于关闭 Profiling。
如果想要展示⼀个 SQL 语句的执⾏耗时细节，可以使⽤ SHOW PROFILE 命
令。
SHOW PROFILE 命令的具体⽤法如下：
在执⾏ SHOW PROFILE 命令时，可以加上类型⼦句，⽐如 CPU、IPC、
MEMORY 等，查看具体某类资源的消耗情况：
如果不加 FOR QUERY {n} ⼦句，默认展示最新的⼀次 SQL 的执⾏情况，加了
FOR QUERY {n} ，表示展示 Query_ID 为 n 的 SQL 的执⾏情况。
为了优化慢 SQL ，我们⾸先要找到哪些 SQL 语句执⾏速度⽐较慢。
MySQL 慢查询⽇志是⽤来记录 MySQL 在执⾏命令中，响应时间超过预设阈值
的 SQL 语句。因此，通过分析慢查询⽇志我们就可以找出执⾏速度⽐较慢的
SQL 语句。
出于性能层⾯的考虑，慢查询⽇志功能默认是关闭的，你可以通过以下命令开
启：
设置成功之后，使⽤ show variables like 'slow%'; 命令进⾏查看。
我们故意在百万数据量的表(未使⽤索引)中执⾏⼀条排序的语句：
确保⾃⼰有对应⽬录的访问权限：
查看对应的慢查询⽇志：
我们刚刚故意执⾏的 SQL 语句已经被慢查询⽇志记录了下来：
这⾥对⽇志中的⼀些信息进⾏说明：
Time ：被⽇志记录的代码在服务器上的运⾏时间。
User@Host ：谁执⾏的这段代码。
Query_time ：这段代码运⾏时⻓。
Lock_time ：执⾏这段代码时，锁定了多久。
Rows_sent ：慢查询返回的记录。
Rows_examined ：慢查询扫描过的⾏数。
实际项⽬中，慢查询⽇志通常会⽐较复杂，我们需要借助⼀些⼯具对其进⾏分
析。像 MySQL 内置的 mysqldumpslow ⼯具就可以把相同的 SQL 归为⼀类，
并统计出归类项的执⾏次数和每次执⾏的耗时等⼀系列对应的情况。
找到了慢 SQL 之后，我们可以通过 EXPLAIN 命令分析对应的 SELECT 语
句：
⽐较重要的字段说明：
select_type ：查询的类型，常⽤的取值有 SIMPLE（普通查询，即没有
联合查询、⼦查询）、PRIMARY（主查询）、UNION（UNION 中后⾯的查
询）、SUBQUERY（⼦查询）等。
table ：表示查询涉及的表或衍⽣表。
type ：执⾏⽅式，判断查询是否⾼效的重要参考指标，结果值从差到好
依次是：ALL < index < range ~ index_merge < ref < eq_ref < const <
system。
rows : SQL 要查找到结果集需要扫描读取的数据⾏数，原则上 rows 越少
越好。
......
关于 Explain 的详细介绍，请看这篇⽂章：MySQL 性能优化神器 Explain 使⽤分
析 - 永顺。
正确使⽤索引可以⼤⼤加快数据的检索速度（⼤⼤减少检索的数据量）。
不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据
为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但⼜避免不了为
NULL，建议使⽤ 0,1,true,false 这样语义较为清晰的短值或短字符作为替
代。
被频繁查询的字段 ：我们创建索引的字段应该是查询操作⾮常频繁的字段。
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建⽴
索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利⽤索引的排序，加快
排序查询时间。
被经常频繁⽤于连接的字段 ：经常⽤于连接的字段可能是⼀些外键列，对于
外键列并不⼀定要建⽴外键，只是说该列涉及到表与表的关系。对于频繁被
连接查询的字段，可以考虑建⽴索引，提⾼多表连接查询的效率。
虽然索引能带来查询上的效率，但是维护索引的成本也是不⼩的。 如果⼀个字段
不被经常查询，反⽽被经常修改，那么就更不应该在这种字段上建⽴索引了。
因为索引是需要占⽤磁盘空间的，可以简单理解为每个索引都对应着⼀颗
B+树。如果⼀个表的字段过多，索引过多，那么当这个表的数据达到⼀个体量
后，索引占⽤的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果
是联合索引，多个字段在⼀个索引上，那么将会节约很⼤磁盘空间，且修改数据
的操作效率也会提升。
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那
么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索
引，能够命中前者的查询肯定是能够命中后者的 在⼤多数情况下，都应该尽量扩
展已有的索引⽽不是创建新索引。
前缀索引仅限于字符串类型，较普通索引会占⽤更⼩的空间，所以可以考虑使⽤
前缀索引带替普通索引。
索引失效也是慢查询的主要原因之⼀，常⻅的导致索引失效的情况有下⾯这些：
使⽤ SELECT * 进⾏查询;
创建了组合索引，但查询条件未准守最左匹配原则;
在索引列上进⾏计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询⽐如 like '%abc'; ;
查询条件中使⽤ or，且 or 的前后条件中有⼀个列没有索引，涉及的索引都
不会被使⽤到;
发⽣隐式转换;
......
删除⻓期未使⽤的索引，不⽤的索引的存在会造成不必要的性能损耗 MySQL
5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未
被使⽤
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
