MySQL高级之SQL调优
2021/10/23 19:10:32
本文主要是介绍MySQL高级之SQL调优，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
如何优化SQL呢？
SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？
慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量
如何能够使用组合索引的SQL？
慢SQL，如何优化，从哪些方面入手？
最左前缀原则是什么？
影响mysql的性能因素
- 业务需求对MySQL的影响(合适合度)
- 存储定位对MySQL的影响
- 系统各种配置及规则数据
- 活跃用户的基本信息数据
- 活跃用户的个性化定制信息数据
- 准实时的统计信息数据
- 其他一些访问频繁但变更较少的数据
- 二进制多媒体数据
- 流水队列数据
- 超大文本数据
- 不适合放进MySQL的数据
- 需要放进缓存的数据
- Schema设计对系统的性能影响
- 尽量减少对数据库访问的请求
- 尽量减少无用数据的查询请求
- 硬件环境对系统性能的影响
性能分析
MySQL Query Optimizer
- MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）
- 当客户端向 MySQL 请求一条 Query命令，解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态
性能下降SQL慢 执行时间长 等待时间长 原因分析
- 查询语句写的烂
- 索引失效（单值、复合）
- 关联查询太多join（设计缺陷或不得已的需求）
- 服务器调优及各个参数设置（缓冲、线程数等）
MySQL常见性能分析手段
在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
性能瓶颈定位
我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：
Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）
Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）
Mysql> show InnoDB status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
Explain(执行计划)
使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
能干嘛？
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
怎么用？
- Explain + SQL语句
- 执行计划包含的信息（如果有分区表的话还会有partitions）
expalin
各字段解释
- id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）
- id相同，执行顺序从上往下
- id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行
- select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）
- SIMPLE ：简单的select查询，查询中不包含子查询或UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
- SUBQUERY：在select或where列表中包含了子查询
- DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
- UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
- UNION RESULT：从UNION表获取结果的select
- table（显示这一行的数据是关于哪张表的）
- type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）
一般来说，得保证查询至少达到range级别，最好到达ref
- system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
- const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
- index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
- ALL：Full Table Scan，将遍历全表找到匹配的行
- possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）
- key
- 实际使用的索引，如果为NULL，则没有使用索引
- 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中
explain-key
- key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
- ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）
- rows（根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）
- Extra（包含不适合在其他列中显示但十分重要的额外信息）
- using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
- Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
- using where：使用了where过滤
- using join buffer：使用了连接缓存
- impossible where：where子句的值总是false，不能用来获取任何元素
- select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
- distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作
explain-demo
- 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】
- 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】
- 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
- 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
- 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
慢查询日志
MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。
- long_query_time 的默认值为10，意思是运行10秒以上的语句
- 默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启
查看开启状态
SHOW VARIABLES LIKE '%slow_query_log%'
开启慢查询日志
- 临时配置：
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;
也可set文件位置，系统会默认给一个缺省文件host_name-slow.log
使用set操作开启慢查询日志只对当前数据库生效，如果MySQL重启则会失效。
- 永久配置
修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入两个配置参数
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 MySQL 的运行帐号的可写权限，一般都将这个目录设置为 MySQL 的数据存放目录；long_query_time=2 中的 2 表示查询超过两秒才记录；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。
可以用 select sleep(4) 验证是否成功开启。
在生产环境中，如果手工分析日志，查找、分析SQL，还是比较费劲的，所以MySQL提供了日志分析工具mysqldumpslow。
通过 mysqldumpslow --help 查看操作帮助信息
- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
- 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
- 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
- 也可以和管道配合使用
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more
也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志
Show Profile 分析查询
通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句的具体执行情况，索引使用等，还可以结合Show Profile命令查看执行状态。
- Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
- 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
- 分析步骤
mysql>show profiles;
-
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
- create tmp table 创建临时表，这个要注意
- Copying to tmp table on disk 把内存临时表复制到磁盘
- locked
-
- 诊断SQL，show profile cpu,block io for query id(上一步前面的问题SQL数字号码)
- 日常开发需要注意的结论
- 是否支持，看看当前的mysql版本是否支持
mysql>Show variables like 'profiling'; --默认是关闭，使用前需要开启
-
- 开启功能，默认是关闭，使用前需要开启
mysql>set profiling=1;
-
- 运行SQL
- 查看结果
查询中哪些情况不会使用索引？
性能优化
索引优化
- 全值匹配我最爱
- 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少IO
- is null ,is not null 也无法使用索引
- like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，
- 字符串不加单引号索引失效
- 少用or，用它来连接时会索引失效
- <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
- 少用Hint强制索引
查询优化
永远小表驱动大表（小的数据集驱动大的数据集）
select * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id
当B表的数据集小于 A 表时，用 in 优于 exists
select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`
当 A 表的数据集小于B表的数据集时，用 exists优于用 in
注意：A表与B表的ID字段应建立索引。
order by关键字优化
- order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序
- MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；
- ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列
- 尽可能在索引列上完成排序操作，遵照索引的最左前缀
- 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序
- 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
- 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序
- 优化策略
- 增大sort_buffer_size参数的设置
- 增大max_lencth_for_sort_data参数的设置
GROUP BY关键字优化
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了
数据类型优化
MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。
- 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。
简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。
尽量避免NULL：通常情况下最好指定列为NOT NULL
这篇关于MySQL高级之SQL调优的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-12-25如何部署MySQL集群资料：新手入门教程
- 2024-12-24MySQL集群部署资料：新手入门教程
- 2024-12-24MySQL集群资料详解：新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习：新手入门教程
- 2024-12-24部署MySQL集群入门：一步一步搭建指南
- 2024-12-07MySQL读写分离入门：轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
