我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL学习笔记(18):SQL优化

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL学习笔记(18):SQL优化

MySQL学习笔记(18):SQL优化

本文更新于2019-08-18,使用MySQL 5.7,操作系统为Deepin 15.4。

目录

  • 优化SQL语句的步骤
    • 通过SHOW STATUS了解SQL语句的执行情况
    • 定位执行效率低下的SQL语句
    • 通过EXPLAIN或DESC分析SQL的执行计划
    • 通过SHOW PROFILES和SHOW PROFILE分析SQL
    • 通过trace分析优化器如何选择执行计划
  • 分析、检查、优化、修复表
  • 常用SQL优化
    • 大批量导入数据
    • 优化INSERT语句
    • 优化ORDER BY语句
    • 优化GROUP BY语句
    • 优化子查询
    • 优化OR条件
    • 优化分页查询
    • 使用SQL提示
  • 常用SQL技巧

通过SHOW STATUS了解SQL语句的执行情况

操作的计数,是对执行次数进行计数,不论提交还是回滚都会累加。

Com_xxx形式的参数表示每个xxx语句执行的次数,对所有的存储引擎都会进行累计,如:

  • Com_select:执行SELECT的次数。
  • Com_insert:执行INSERT的次数,对批量插入的操作只累加一次。
  • Com_update:执行UPDATE的次数。
  • Com_delete:执行DELETE的次数。
  • Com_commit:事务提交的次数。
  • Com_rollback:事务回滚的次数。

Innodb_rows_xxx形式的参数只对InnoDB存储引擎进行累计,其累计的方式也与Com_xxx不同:

  • Innodb_rows_read:执行SELECT返回的行数。
  • Innodb_rows_inserted:执行INSERT插入的行数。
  • Innodb_rows_updated:执行UPDATE更新的行数。
  • Innodb_rows_deleted:执行DELETE删除的行数。

Handler_read_xxx形式的参数可表示索引的使用情况:

  • Handler_read_key:一个行被索引值读的次数。高表示索引被经常使用。
  • Handler_read_rnd_next:在数据文件中读下一个行的次数。高表示索引不经常使用,进行大量的表扫描。

以下参数便于了解数据库的基本情况:

  • Connections:试图连接服务器的次数。
  • Uptime:服务器工作时间。
  • Slow_queries:慢查询次数。

定位执行效率低下的SQL语句

  • 通过慢查询日志定位执行效率低下的SQL语句。
  • 通过SHOW PROCESSLIST查看服务器当前的线程,包括线程的状态、是否锁表等,可以实时查看SQL的执行情况。

通过EXPLAINDESC分析SQL的执行计划

DESCEXPLAIN分析SQL执行计划的使用和作用是一样的。

执行EXPLAIN statement后再执行SHOW WARNINGS,可以看到被优化器改写后真正执行的SQL。

一个执行计划包括若干行,每行包括如下的列:

  • id:值越大越先执行(值越大越位于下方),一样大从上至下执行。

  • select_type:查询类型,可取如下值:

    • DERIVED:派生表的查询。
    • SIMPLE:简单查询,即不使用子查询和UNION的查询。
    • SUBQUERY:子查询。
    • PRIMARY:主查询,即包含子查询的最外层查询,或UNION中的第一个查询。
    • UNION:UNION中的第二个或之后的查询。
  • table:输出结果集的表。

  • partitions:访问的分区。

  • type:访问类型,即在表中查找所需行的方式。

    以下取值性能由最差至最好:

    1. ALL:全表扫描,遍历所有行。
    2. index:索引全扫描,遍历整个索引。
    3. range:索引范围扫描,常见于<<=>>=BETWEEN等操作符。
    4. ref:使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个值的所有记录行。其经常出现在JOIN操作中。
    5. eq_ref:类似ref,区别在于使用唯一索引。其出现在使用PRIMARY KEYUNIQUE INDEX作为关联条件的表连接中。
    6. const/system:单表中最多有一个匹配行,因此这个匹配行中的其他列能被优化器当做常量来使用。如根据PRIMARY KEYUNIQUE INDEX进行过滤的查询。system是const的特例,当表中只有一条记录时的const就为system。
    7. NULL:不用访问表或索引,就能直接得到结果。

    还可取其他的值,如:

    • ref_or_null:与ref类似,区别在于条件中包含对NULL的查询。
    • index_merge:索引合并。
    • unique_subquery:IN后面是一个查询唯一索引字段的子查询。
    • index_subquery:与unique_subquery类型,区别在于IN后面是一个查询非唯一索引字段的子查询。
  • possible_keys:查询时可能使用的索引。

  • key:实际使用的索引。

  • key_len:实际使用到的索引的字节长度。

  • ref:实际使用的索引在其他表的关联字段。如果是常数等值查询,则为const。

  • rows:扫描的行数。

  • filtered:存储引擎返回的数据过滤后,满足查询条件的记录的比例。

  • Extra:执行情况的说明,包括不适合在其他列中显示但是对执行计划非常重要的额外信息。

    • Using filesort:filesort排序,而不是通过索引直接返回排序结果。
    • Using index:覆盖索引扫描,直接访问索引就能获取所需的数据,不需要通过索引回表。
    • Using index condition:使用ICP(Index Condition Pushdown,参看“索引”章节)优化查询,将某些情况下的条件过滤操作下放到存储引擎层完成,降低不必要的IO访问。
    • Using where:优化器除了利用索引加速访问外,还需根据索引回表查询数据。
    • Using union:多次查询后对结果集合并,如使用OR查询。

通过SHOW PROFILESSHOW PROFILE分析SQL

profiling默认是关闭的,可通过设置变量@@profiling进行打开或关闭。

SHOW PROFILES结果包括以下字段:

  • Query_ID:查询ID。
  • Duration:查询耗时。
  • Query:查询语句。

SHOW PROFILE [ALL|CPU|{BLOCK IO}|{PAGE FAULTS}|SOURCE][, ...] FOR QUERY query_id(query_id为SHOW PROFILES结果的Query_ID字段)结果包括以下字段:

  • Status:查询执行过程中的状态。各状态含义如下:
    • starting
    • Waiting for query cache lock
    • checking query cache for query
    • checking permissions
    • Opening tables
    • inti
    • System lock
    • optimizing
    • statistics
    • preparing
    • executing
    • Sending data:开始访问数据行并把结果返回客户端,包含大量的磁盘操作。
    • end
    • query end
    • closing tables
    • removing tmp table
    • freeing items
    • storing result in query cache
    • logging slow query
    • cleaning up
  • Duration:耗时。
  • CPU_user
  • CPU_system
  • Context_voluntary
  • Context_involuntary
  • Block_ops_in
  • Block_ops_out
  • Messages_sent
  • Messages_received
  • Page_faults_major
  • Page_faults_minor
  • Swaps
  • Source_function
  • Source_file
  • Source_line

通过trace分析优化器如何选择执行计划

需打开trace,设置格式为JSON,设置trace最大能使用的内存大小。如:

SET @@optimizer_trace="enabled=on";
SET @@end_markers_in_json=on;
SET @@optimizer_trace_max_size=1000000;

执行SELECT * FROM information_schema.OPTIMIZER_TRACE即可得到结果。

ANALYZECHECKOPTIMIZEREPAIR执行期间都会对表进行锁定。

分析表,使得SQL能够生成正确的执行计划。如果感觉实际的执行计划并不符合预期,执行一次分析表可能会解决问题:

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...]

检查表,用于检查表或视图是否有错误。如视图定义中被引用的表不存在:

CHECK TABLE tablename[, ...] [{QUICK|FAST|MEDIUM|EXTENDED|CHANGED}[ ...]]

优化表,可以将表中的空间碎片进行合并。如果已经删除表的很大一部分数据,或已经对含有可变长度行(含有VARCHAR*BLOB*TEXT的列)的表进行很多更改,则应该进行优化表:

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...]

修复表,对坏表进行修复:

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...] [{QUICK|EXTENDED|USE_FRM}[ ...]]

大批量导入数据

对MyISAM存储引擎,可通过关闭和打开非唯一索引的更新提高导入效率:

ALTER TABLE tablename DISABLE KEYS;
# import data
ALTER TABLE tablename ENALBE KEYS;

对InnoDB存储引擎:

  • 因为InnoDB表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以提高导入效率。
  • 在导入数据前执行SET unique_checks=0关闭唯一性校验,在导入结束后执行SET unique_checks=1恢复唯一性校验,可提高导入效率。
  • 如果应用使用自动提交的方式,建议导入前执行SET autocommit=0关闭自动提交,导入结束后执行SET autocommit=1恢复自动提交,可提高导入效率。

优化INSERT语句

  • 当从同一客户端插入很多行时,应尽量使用多个值列表的INSERT语句。
  • 如果从不同客户端插入很多行,可以使用INSERT DELAYED,让INSERT`马上返回(实际上数据被放在MySQL服务器内存队列中)。
  • 将索引文件和数据文件在不同的磁盘存放(利用建表中的选项)。
  • 如果进行批量INSERT,可以增加bulk_insert_buffer_size变量值来提高速度(只对MyISAM表使用)。
  • 当从一个文件装载一个表时,使用LOAD DATA INFILE

优化ORDER BY语句

MySQL有两种排序方式:

  • 通过有序索引扫描直接返回有序数据。在使用EXPLAIN分析时显示为Using index。
  • filesort排序。将返回的数据在sort_buffer_size设置的内存排序区进行排序,至于是否使用磁盘文件和临时表等,取决于MySQL服务器对排序参数的设置和需要排序数据的大小。

对于filesort,MySQL比较查询取出的字段总大小和max_length_for_sort_data,判断使用哪种排序算法:

  • 两次扫描算法(Tow passes):第一次根据条件获取排序字段和行指针信息,并在排序区中排序。第二次根据行指针回表读取记录,可能导致大量随机IO操作。
  • 一次扫描算法(Single Pass):一次性取出满足条件的行的所有字段,然后在排序区排序后直接输出结果。这会导致内存开销比较大。

优化ORDER BY语句应该:尽量减少额外的排序,通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或都是降序。否则肯定需要额外的排序操作,这样就会出现filesort排序。

尽量SELECT必要的字段名,而不是SELECT *所有字段,这样可以减少排序区的使用,提高性能。

优化GROUP BY语句

MySQL会对GROUP BY的所有字段进行排序。如果想避免排序的消耗,可以使用ORDER BY NULL禁止排序。

优化子查询

有些情况下,子查询可以被更有效率的表连接代替。因为表连接不需要在内存中创建临时表。

优化OR条件

对于含有OR的查询,如果要利用索引,则OR之间的每个字段都必需能利用索引。此时,实际是对OR的各个字段分别查询的结果进行UNION操作。

优化分页查询

执行LIMIT offset_start, row_count时,MySQL排序出offset_start+row_count条记录后仅仅返回最后row_count条记录,前面的offset_start条记录都会被丢弃,查询和排序的代价非常高。有两种优化思路:

  • 在索引上完成排序分页的操作,最后根据主键关联回表查询所需的其他列内容。
  • 把查询转换成基于某个位置的查询,使用LIMIT row_count代替LIMIT offset_start, row_count。但这种方式对数据集有特定的要求。

使用SQL提示

SQL提示(SQL HINT)就是在SQL语句中加入一些人为提示来达到优化的目的。

SELECT SQL_BUFFER_RESULT * FROM ...

这个语句强制MySQL生成一个临时结果集。生成后所有表上的锁均被释放,这能在遇到表锁问题或要花很长时间将结果传给客户端时有帮助。

SELECT * FROM tablename USE|IGNORE|FORCE INDEX (indexname[, ...]) WHERE ...

USE INDEX提供希望(实际执行时不一定会被选择)查询时使用的索引,IGNORE INDEX忽略指定的索引,FORCE INDEX强制使用指定的索引。

  • 利用ORDER BY RAND()提取随机行。
  • 利用GROUP BY ... WITH ROLLUP获取更多的分组聚合信息。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL学习笔记(18):SQL优化

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL学习笔记(18):SQL优化

本文更新于2019-08-18,使用MySQL 5.7,操作系统为Deepin 15.4。目录优化SQL语句的步骤通过SHOW STATUS了解SQL语句的执行情况定位执行效率低下的SQL语句通过EXPLAIN或DESC分析SQL的执行计划通过SHOW PRO
MySQL学习笔记(18):SQL优化
2016-01-24

MySQL学习笔记(20):优化MySQL Server

本文更新于2020-04-05,使用MySQL 5.7,操作系统为Deepin 15.4。目录MySQL体系结构MySQL内存优化MyISAM内存优化key_buffer_size设置使用多索引缓存调整中点插入策略调整read_buffer_size和read
MySQL学习笔记(20):优化MySQL Server
2021-04-05

MySQL学习笔记(3):SQL

本文章更新于2020-06-14,使用MySQL 5.7,操作系统为Deepin 15.9。目录DDL语句创建数据库删除数据库修改数据库创建表删除表修改表创建索引删除索引创建视图修改视图删除视图存储过程和函数创建事件修改事件删除事件创建触发器删除触发器DML语
MySQL学习笔记(3):SQL
2022-04-25

MySQL学习笔记(22):应用优化

本文更新于2020-04-05,使用MySQL 5.7,操作系统为Deepin 15.4。目录使用连接池减少对MySQL的访问负载均衡其他优化措施使用连接池对于访问数据库来说,建立连接的代价比较昂贵。减少对MySQL的访问应用应避免对同一数据做重复检索,减少对
MySQL学习笔记(22):应用优化
2020-02-02

MySQL学习笔记(21):优化磁盘IO

本文更新于2020-04-05,使用MySQL 5.7,操作系统为Deepin 15.4。目录使用符号连接分布IO禁止操作系统更新文件的atime属性用裸设备(Raw Device)存放InnoDB共享表空间调整IO调度算法使用磁盘阵列(RAID)RAID卡电
MySQL学习笔记(21):优化磁盘IO
2017-05-19

MySQL学习笔记(15):SQL Mode

本文更新于2019-06-29,使用MySQL 5.7,操作系统为Deepin 15.4。与其他数据库不同,MySQL可以运行在不同的SQL Mode(SQL模式)下。可通过变量@@sql_mode查看或设置当前的SQL Mode。下面是一些常用的SQL Mo
MySQL学习笔记(15):SQL Mode
2015-02-27

MySQL学习笔记(23):SQL安全

本文更新于2019-06-29,使用MySQL 5.7,操作系统为Deepin 15.4。应用可使用PrepareStatement + Bind-Variable来防止SQL注入。已知的非法符号有:()[]--#;="+空格%
MySQL学习笔记(23):SQL安全
2016-09-18

PL/Sql学习笔记

声明变量 一、概述:1、PLSQL 块结构:DECLARE --- 可选变量声明定义BEGIN ---- 必选SQL 和PLSQL 语句EXCEPTION ---- 可选错误处理END;---- 必选constant ----表时声明的是常
2023-06-03

MySQL学习笔记(19):优化数据库对象

本文更新于2019-08-18,使用MySQL 5.7,操作系统为Deepin 15.4。目录优化表的数据类型通过拆分提高表的访问效率逆规范化使用中间表提高统计查询效率一旦数据库对象设计完毕并投入使用,再进行修改就比较麻烦。优化表的数据类型select_sta
MySQL学习笔记(19):优化数据库对象
2016-04-25

MySQL学习笔记

作者: Grey原文地址:MySQL学习笔记说明注:本文中的SQL语句如果用到了特定方言,都是基于MySQL数据库。关于DDLDDL 的英文全称是 Data Definition Language,中文是数据定义语言。它定义了数据库的结构和数据表的结构。在 D
MySQL学习笔记
2015-01-17

MySQL 学习笔记 (一)

1.InnoDB and Online DDLALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;https://dev.mysql.com/doc/refma
2016-12-04

MySQL 学习笔记(五)

mysqldump 与 --set-gtid-purged 设置(1)  mysqldumpThe mysqldump client utility performs logical backups, producing a set of SQL statem
MySQL 学习笔记(五)
2022-01-27

MySQL学习笔记-day01

1、数据库概述及数据准备1.1、SQL概述SQL,全称Structured Query Language,SQL用来和数据库打交道,完成和数据库的通信,SQL是一套标准。(90%以上的SQL都是通用的)。SQL:结构化语言,是一门标准通用的语言。SQL属于高级
MySQL学习笔记-day01
2017-10-15

MySQL学习笔记-day03

1、约束1.1、唯一性约束(unique)唯一性约束修饰的字段具有唯一性,不能重复。但可以为NULL。案例:给某一列添加uniquedrop table if exists t_user;create table t_user( id int, u
MySQL学习笔记-day03
2020-11-18

编程热搜

目录