MySQL:优化SQL语句的步骤
本文主要介绍了MySQL性能分析以及explain的使用，包括：组合索引、慢查询分析、MYISAM和INNODB的锁定、MYSQL的事务配置项等索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQ...
目录
优化 SQL 语句的步骤
通过show status命令了解各种sql的执行频率
- mysql 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extend-status 命令获取这些消息。
- show status 命令中间可以加入选项 session（默认） 或 global：
session （当前连接）global （自数据上次启动至今）
# Com_xxx 表示每个 xxx 语句执行的次数。
show status like 'Com_%';
# 或者使用
show status like 'Com_______';Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
| 参数 | 含义 |
|---|---|
| Com_select | 执行 select 操作的次数，一次查询只累加 1。 |
| Com_insert | 执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。 |
| Com_update | 执行 UPDATE 操作的次数。 |
| Com_delete | 执行 DELETE 操作的次数。 |
| Innodb_rows_read | select 查询返回的行数。 |
| Innodb_rows_inserted | 执行 INSERT 操作插入的行数。 |
| Innodb_rows_updated | 执行 UPDATE 操作更新的行数。 |
| Innodb_rows_deleted | 执行 DELETE 操作删除的行数。 |
| Connections | 试图连接 MySQL 服务器的次数。 |
| Uptime | 服务器工作时间. |
| Slow_queries | 慢查询的次数。 |
Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。
慢SQL查询
在开发中，我们要尽量写出高性能的 SQL 语句，但也无法避免一些慢 SQL 语句的出现，或因为疏漏，或因为实际生产环境与开发环境有所区别，这些都是诱因。
面对这种情况，我们可以打开慢 SQL 配置项，记录下都有哪些 SQL 超过了预期的最大执行时间。
首先，我们可以通过以下命令行查询是否开启了记录慢 SQL 的功能，以及最大的执行时间是多少：
Show variables like 'slow_query%';
Show variables like 'long_query_time';
如果没有开启，我们可以通过以下设置来开启：
set global slow_query_log='ON'; // 开启慢 SQL 日志
set global slow_query_log_file='/var/lib/mysql/test-slow.log';// 记录日志地址
set global long_query_time=1;// 最大执行时间mysqldumpslow
如果慢查询日志中记录内容较多，则可以使用Mysql自带的慢查询日志分析工具mysqldumpslow工具来对慢查询日志进行分类汇总。
该工具位于/mysql/bin目录下。
mysqldumpslow将会自动将文本完全一致但变量不同的SQL语句视为同一个语句进行统计，变量值用N来代替。
mysqldumpslow -s r -t 10 /data/dbdata/frem-slow.logshow processlist :
慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
- 1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
- 2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
- 3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
- 4） db列，显示这个进程目前连接的是哪个数据库
- 5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等。更多命令，可以参考：https://dev.mysql.com/doc/refman/5.6/en/thread-commands.html
- 6） time列，显示这个状态持续的时间，单位是秒
- 7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成。更多状态可以参考：https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html
- 8） info列，显示这个sql语句，是判断问题语句的一个重要依据
通过 EXPLAIN 分析 SQL 执行计划
通常，我们在执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，我们可以通过 EXPLAIN 命令来查看这些执行信息。这些执行信息被统称为执行计划。
假设现在我们使用 EXPLAIN 命令查看当前 SQL 是否使用了索引，先通过 SQL EXPLAIN 导出相应的执行计划如下：
EXPLAIN输出字段解释
| 字段 | 含义 |
|---|---|
| id | 每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id |
| select_type | 表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
|
| table | 当前执行计划查询的表，如果给表起别名了，则显示别名信息。 |
| partitions | 访问的分区表信息 |
| type | 表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
|
| possible_keys | 表示查询时，可能使用的索引 |
| key | 表示实际使用的索引 |
| key_len | 索引字段的长度 |
| ref | 当前使用的索引的长度 |
| rows | 查找到记录所扫描的行数 |
| filtered | 查找到所需记录占总扫描记录数的比例 |
| extra | 额外的信息
|
阿里MySQL规约
【推荐】 SQL 性能优化的目标：至少要达到 range 级别， 要求是 ref 级别， 如果可以是 consts最好。
说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（ normal index） 。
3） range 对索引进行范围检索。
反例： explain 表的结果， type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫
通过 Show Profile 分析 SQL 执行性能
上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。
Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。以下是相关命令的注释：
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type 参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示 CPU 的相关开销信息
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置 (行数)
| SWAPS：显示 swap 交换次数的相关开销信息简单总结
通过 select @@have_profiling来显示当前mysql是否支持profile通过 select @@profiling查看profile是否开启如果为0未开启通过 set profiling =1 开启通过show profiles 可以看到每个sql语句执行的时间通过设置 set profiling_history_size=20 增大该存储记录，最大值为 100。通过 show profile for query query_id可以查看该sql语句执行的过程中线程的状态和在每个状态消耗的时间
SHOW PROFILE ALL FOR QUERY 2;的信息还可以通过 SELECT * FROM information_schema.profiling WHERE query_id = 2 ORDER BY seq;获取。
在获取到最消耗时间的状态以后还可以进一步查看mysql使用资源所耗费的时间，比如查看cpu耗费的时间可以用 show profile cpu for query 4;
值得注意的是，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确定的话，可以通过 select @@have_profiling 查询是否支持该功能：
最新的 MySQL 版本是默认开启 Show Profile 功能的，但在之前的旧版本中是默认关闭该功能的，你可以通过 set 语句在 Session 级别开启该功能：
show profiles; 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。
获取到 Query_ID 之后，我们再通过 show profile for query 67; 语句，就能够查看到对应 Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了：
status出现以下情况的建议
| status | 处理建议 |
| System lock | 确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的。
|
| Sending data | 解释：从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据，再发送给客户端，数据量很大时尤其经常能看见。
备注：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量 |
| Sorting result | 正在对结果进行排序，类似Creating sort index，不过是正常表，而不是在内存表中进行排序。
|
| Table lock | 表级锁，没什么好说的，要么是因为MyISAM引擎表级锁，要么是其他情况显式锁表 |
| create sort index | 当前的SELECT中需要用到临时表在进行ORDER BY排序
|
| Creating tmp table | 创建临时表。先拷贝数据到临时表，用完后再删除临时表。消耗内存，数据来回拷贝删除，消耗时间，
|
| converting HEAP to MyISAM | 询结果太大，内存不够，数据往磁盘上搬了。
|
| Copying to tmp table on disk | 把内存中临时表复制到磁盘上，危险！！！
|
通过trace分析优化器如何选择执行计划
Mysql5.6提供了对sql的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。
trace使用：
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";以上是官网的使用介绍
1. 首先打开trace，设置格式为json，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
# 打开trace，设置json格式
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
# 设置内存，避免解析过程中因为默认内存过小而不能够完整显示。
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;2. 接下来执行想做trace的sql语句
#执行sql
SELECT ...; # your query here3. 检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就知道mysql是如何执行sql的了
#检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就知道mysql是如何执行sql的了
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;4. 完成后，关闭trace
# 完成后，关闭trace
SET optimizer_trace="enabled=off";
最后会输出一个跟踪文件。
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `dm_simulator_reserve_config`.`id` AS `id`,`dm_simulator_reserve_config`.`dm_simulator_id` AS `dm_simulator_id`,`dm_simulator_reserve_config`.`reserve_period_type` AS `reserve_period_type`,`dm_simulator_reserve_config`.`reserve_am_start` AS `reserve_am_start`,`dm_simulator_reserve_config`.`reserve_am_end` AS `reserve_am_end`,`dm_simulator_reserve_config`.`reserve_pm_start` AS `reserve_pm_start`,`dm_simulator_reserve_config`.`reserve_pm_end` AS `reserve_pm_end`,`dm_simulator_reserve_config`.`reserve_limit` AS `reserve_limit`,`dm_simulator_reserve_config`.`reserve_period_custom` AS `reserve_period_custom`,`dm_simulator_reserve_config`.`create_by` AS `create_by`,`dm_simulator_reserve_config`.`update_by` AS `update_by`,`dm_simulator_reserve_config`.`create_time` AS `create_time`,`dm_simulator_reserve_config`.`update_time` AS `update_time` from `dm_simulator_reserve_config`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"table": "`dm_simulator_reserve_config`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"rows_estimation": [
{
"table": "`dm_simulator_reserve_config`",
"table_scan": {
"rows": 3,
"cost": 1
} /* table_scan */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`dm_simulator_reserve_config`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 3,
"access_type": "scan",
"resulting_rows": 3,
"cost": 1.6,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 3,
"cost_for_plan": 1.6,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`dm_simulator_reserve_config`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`dm_simulator_reserve_config`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}常用的 SQL 优化
1. 优化分页查询
通常我们是使用 <LIMIT M,N> + 合适的 order by 来实现分页查询，这种实现方式在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能将会非常得糟糕。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性能就越差。
思考题: LIMIT 10000,10 这样的查询, 数据库需要查询 多少条记录?如何优化
这是因为我们在使用 LIMIT 的时候，偏移量 M 在分页越靠后的时候，值就越大，数据库检索的数据也就越多。例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，最后返回 10 条记录。也就是说将会有 10000 条记录被查询出来没有被使用到。
我们模拟一张 10 万数量级的 order 表，进行以下分页查询：
select * from `demo`.`order` order by order_no limit 10000, 20;通过 EXPLAIN 分析可知：该查询使用到了索引，扫描行数为 10020 行，但所用查询时间为 0.018s，相对来说时间偏长了。
-
利用子查询优化分页查询
以上分页查询的问题在于，我们查询获取的 10020 行数据结果都返回给我们了，我们能否先查询出所需要的 20 行数据中的最小 ID 值，然后通过偏移量返回所需要的 20 行数据给我们呢？我们可以通过索引覆盖扫描，使用子查询的方式来实现分页查询：
select * from `demo`.`order` where id> (select id from `demo`.`order` order by order_no limit 10000, 1) limit 20;通过 EXPLAIN 分析可知：子查询遍历索引的范围跟上一个查询差不多，而主查询扫描了更多的行数，但执行时间却减少了，只有 0.004s。这就是因为返回行数只有 20 行了，执行效率得到了明显的提升。
阿里MySQL规约
【推荐】 利用延迟关联或者子查询优化超多分页场景。 说明： MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。
正例： 先快速定位需要获取的 id 段，然后再关联： SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
2. 优化 SELECT COUNT(*)
COUNT() 是一个聚合函数，主要用来统计行数，有时候也用来统计某一列的行数量（不统计 NULL 值的行）。我们平时最常用的就是 COUNT(*) 和 COUNT(1) 这两种方式了，其实两者没有明显的区别，在拥有主键的情况下，它们都是利用主键列实现了行数的统计。
但 COUNT() 函数在 MyISAM 和 InnoDB 存储引擎所执行的原理是不一样的，通常在没有任何查询条件下的 COUNT(*)，MyISAM 的查询速度要明显快于 InnoDB。
这是因为 MyISAM 存储引擎记录的是整个表的行数，在 COUNT(*) 查询操作时无需遍历表计算，直接获取该值即可。而在 InnoDB 存储引擎中就需要扫描表来统计具体的行数。而当COUNT(*)带上 where 条件语句之后，MyISAM 跟 InnoDB 就没有区别了，它们都需要扫描表来进行行数的统计。
如果对一张大表经常做 SELECT COUNT(*) 操作，这肯定是不明智的。那么我们该如何对大表的 COUNT() 进行优化呢？
-
使用近似值
有时候某些业务场景并不需要返回一个精确的 COUNT 值，此时我们可以使用近似值来代替。我们可以使用 EXPLAIN 对表进行估算，要知道，执行 EXPLAIN 并不会真正去执行查询，而是返回一个估算的近似值。
-
增加汇总统计
如果需要一个精确的 COUNT 值，我们可以额外新增一个汇总统计表或者缓存字段来统计需要的 COUNT 值，这种方式在新增和删除时有一定的成本，但却可以大大提升 COUNT() 的性能。
阿里MySQL规约
【强制】 不要使用 count(列名)或 count(常量)来替代
count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 说明： count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
总结
- 如果在开发中确实需要用到count()聚合，那么优先考虑
count(*)，因为mysql数据库本身对于count(*)做了特别的优化处理。有主键或联合主键的情况下，count(*)略比count(1)快一些。 没有主键的情况下count(1)比count(*)快一些。 如果表只有一个字段，则count(*)是最快的。- 使用
count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。- count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引.
3. 优化 SELECT *
我曾经看过很多同事习惯在只查询一两个字段时，都使用 select * from table where xxx 这样的 SQL 语句，这种写法在特定的环境下会存在一定的性能损耗。
MySQL 常用的存储引擎有 MyISAM 和 InnoDB，其中 InnoDB 在默认创建主键时会创建主键索引，而主键索引属于聚族索引，即在存储数据时，索引是基于 B + 树构成的，具体的行数据则存储在叶子节点。
而 MyISAM 默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚族索引，即在存储数据时，索引是基于 B + 树构成的，而叶子节点存储的是主键值。
假设我们的订单表是基于 InnoDB 存储引擎创建的，且存在 order_no、status 两列组成的组合索引。此时，我们需要根据订单号查询一张订单表的 status，如果我们使用 select * from order where order_no='xxx’来查询，则先会查询组合索引，通过组合索引获取到主键 ID，再通过主键 ID 去主键索引中获取对应行所有列的值。
如果我们使用 select order_no, status from order where order_no='xxx’来查询，则只会查询组合索引，通过组合索引获取到对应的 order_no 和 status 的值。如果你对这些索引还不够熟悉。
阿里MySQL规约
【强制】 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度。
说明： 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【推荐】 利用覆盖索引来进行查询操作， 避免回表。 说明： 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这 个目录就是起到覆盖索引的作用。 正例： 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效 果，用 explain 的结果， extra 列会出现： using index。
【推荐】 建组合索引的时候，区分度最高的在最左边。 正例： 如果 where a=? and b=?， a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。 说明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列， 即建立组合索引 idx_d_c。
无需安装部署，在线快速体验 Byzer
更多推荐
2
0- 0
已为社区贡献9条内容
所有评论(0)