前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 慢查询、 索引、 事务隔离级别

MySQL 慢查询、 索引、 事务隔离级别

作者头像
chenchenchen
发布2019-09-03 11:56:36
2.8K0
发布2019-09-03 11:56:36
举报
文章被收录于专栏:chenchenchen

慢查询

什么是慢查询

MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阀值的语句,阈值指的是运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。long_query_time 的默认值为 10,意思是运行 10秒 以上的语句。默认情况下,MySQL 数据库并不启动慢查询日志,需要我们手动来设置这个参数。 慢查询需要知道的 “点”  企业级开发中,慢查询日志是会打开的。但是这同样会带来一定的性能影响。   慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表   默认的阈值(long_query_time)是 10,这个显然不可用,通常,对于用户级应用而言,我们将它设置为 0.2  慢查询相关的变量 查看变量的 SQL 语句

关于变量的说明  ** 修改变量可以使用命令:setglobal long_query_time =0.2; (更常见的做法是修改 MySQL 的配置 my.cnf) **   ** 日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源。所以,不要将慢查询日志记录到表中。 ** 

慢查询日志的分析工具(优化慢查询) mysqldumpslow M y S Q L 内 置 了 工 具 m y s qld u m p slo w 用 于 解 析 M y S Q L 慢 查 询 日 志 , 并 打 印 其 内 容 摘 要 。

 使用语法

mysqldumpslow [options] [log_file ...]

 常用选项(options)解释 -g pattern:只显示与模式匹配的语句,大小写不敏感。 -r:反转排序顺序。 -s sort_type:如何排序输出,可选的 sort_type 如下 t:按查询总时间排序。 l:按查询总锁定时间排序。 r:按总发送行排序。 c:按计数排序。 at:按查询时间或平均查询时间排序。 al:按平均锁定时间排序。 ar:按平均行发送排序。 默认情况下,mysqldumpslow 按平均查询时间(相当于-s at)排序。 -t N:是 top n 的意思,即返回前面多少条的数据。 -v:详细模式。

 使用示例 # 显示 2 条结果,且按照查询总时间排序,且过滤 group by 语句 mysqldumpslow -t 2 -s t -g "group by" slow_query_log_file # 按照时间排序的前 10 条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g “left join” slow_query_log_file # 返回记录集最多的 10 个 SQL mysqldumpslow -s r -t 10 slow_query_log_file # 可以结合 more 一起使用,避免一次显示过多 SQL 语句 mysqldumpslow -s r -t 20 slow_query_log_file | more # 访问次数最多的 10 个 SQL mysqldumpslow -s c -t 10 slow_query_log_file ...

 mysqldumpslow 结果信息 Count: 这种类型的语句执行了几次 Time: 这种类型的语句执行的最大时间 Lock: 这种类型语句执行时等待锁的时间 Rows: 单次返回的结果数 Count: 2 Time=3.21s (7s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost 代表的含义是: 执行了 2 次,最大时间是 3.21s,总共花费时间 7s,等待锁的时间是 0s,单次返回的结果数是 1 条记录,2 次总共返回 2 条记 录。

EXPLAIN M y S Q L 提 供 了 E X P L A I N 命 令 , 可 以 对 慢 查 询 ( S E L E C T ) 进 行 分 析 , 并 输 出 S E L E C T 执 行 的 详 细 信 息 。 我 们 可 以 针 对 输 出 的 信 息 对 慢 查 询 语 句 进 行 合 理 的 优 化 。

 使用方法

explain select * from ad_unit_it where it_tag like '%球';

 EXPLAIN 输出信息及解释

mysql> explain select * from ad_unit_it where it_tag like '%球'G*************************** 1. row ************* ************** id: 1 select_type: SIMPLE table: ad_unit_it partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 33.33 Extra: Using where

id: 查询的唯一标识符 select_type: 查询的类型 table: 查询的表 partitions: 匹配的分区 type: join 类型 possible_keys: 查询中可能用到的索引 key: 查询中使用到的索引 key_len: 查询优化器使用了的索引字节数 ref: 哪个字段或常量与 key 一起被使用 rows: 当前的查询一共扫描了多少行(估值) filtered: 查询条件过滤的数据百分比 Extra: 额外信息

select_type: 最常见的查询类型是 SIMPLE, 这表示查询中没有子查询,也没有 UNION 查询 type: 这个字段是判断查询是否高效的重要提示。可以判断查询是全表扫描还是索引扫描。例如:all 表示全表扫描,性能最差; range 表示使用索引范围扫描,通常是 where 条件中带有数学比对的;index 表示全索引扫描,扫描索引而不扫描数据 possible_keys: 表示查询时可能会使用到的索引,但是并不表示一定会使用。真正的使用了哪些索引,由 key 决定 rows:MySQL 优化器会估算此次查询需要扫描的数据记录数(行数),这个值越小,查询效率越高 Extra: 这是查询语句所对应的“额外信息”, 常见的有 : Usingfilesort: 表示 MySQL 需额外的排序操作,不能通过索引顺序达到排序效果。这样的查询应该是需要避免 的,CPU 消耗很高。 Usingwhere: 在查找使用索引的情况下,需要回表去查询所需的数据 Usingindex: 表示查询在索引树中就可查找所需数据,不用扫描表数据文件 Usingtemporary: 查询过程会使用到临时表,常见于 orderby、groupby、join 等场景,性能较低

为什么会产生慢查询 ? 两张比较大的表进行 JOIN,但是没有给表的相应字段加索引 表存在索引,但是查询的条件过多,且字段顺序与索引顺序不一致 对很多查询结果进行 GROUPBY

索引

创 建 索 引 的 目 的 就 是 为 了 加 快 查 询 的 速 度 , 如 果 没 有 索 引 , M y S Q L 在 查 询 时 , 只 能 从 第 一 条 记 录 开 始 然 后 读 完 整 个 表 找 到 匹配 的 行 。 M y S Q L 支 持 多 种 存 储 引 擎 , 不 同 的 引 擎 对 索 引 的 支 持 也 不 相 同 。 我 这 里 只 会 介 绍 B 树 索 引 , 对 应 I n n o D B 存 储 引擎 。

索引类型及操作 索引类型

 普通索引

这 是 最 基 本 的 索 引 类 型 , 支 持 单 列 和 多 列 。 可 以 通 过 以 下 的 几 种 方 式 创 建 :

CREATE INDEX 索引名 ON 表名(列名 1,列名 2,...);

-- 创建索引 ALTER TABLE 表名 ADD INDEX 索引名 (列名 1, 列名 2,...);

-- 修改表 CREATE TABLE 表名 ( [...], INDEX 索引名 (列名 1,列名 2,...) );

-- 创建表时指定索引

 唯一索引

表 示 唯 一 的 , 不 允 许 重 复 的 索 引 , 支 持 单 列 和 多 列 。 注 意 , 如 果 是 多 列 共 同 构 成 唯 一 索 引 , 代 表 的 是 多 列 的 数 据 组 合 是 唯 一 的 。 可 以 通 过 以 下 的 几 种 方 式 创 建 :

CREATE UNIQUE INDEX 索引名 ON 表名(列名 1,列名 2,...);

-- 创建索引 ALTER TABLE 表名 ADD UNIQUE 索引名 (列名 1,列名 2,...);

-- 修改表 CREATE TABLE 表名( [...], UNIQUE 索引名 (列名 1,列名 2,...) );

-- 创建表时指定索 引

 主键索引 主 键 是 特 殊 的 唯 一 索 引 , 同 样 支 持 单 列 和 多 列 , 但 是 必 须 被 指 定 为 P R I M A R Y K E Y 。 注 意 , 每 个 表 中 只 能 有 一 个 主 键 。 可 以 通过 以 下 的 几 种 方 式 创 建 :

CREATE TABLE 表名( [...], PRIMARY KEY (列名 1,列名 2,...) );

-- 创建表的时候指定 ALTER TABLE 表名 ADD PRIMARY KEY (列名 1,列名 2,...);

-- 修改表

索引操作  删除索引

-- 删除 talbe_name 中的索引

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

-- 删除主键索引,因为一个表只可能有一个 PRIMARY KEY 索引,因此不需要指定索引名

ALTER TABLE table_name DROP PRIMARY KEY

 查看索引

show index from table_name;

show keys from table_name;

-- 核心字段的解释

-- Table:表的名称

-- Non_unique:如果索引不能包括重复词,则为 0。如果可以,则为 1

-- Key_name:索引的名称

-Seq_in_index:索引中的列序列号,从 1 开始

-- Column_name:列名称

-- Collation:列以什么方式存储在索引中。在 MySQL 中,‘A’(升 序)或 NULL(无分类)。

-- Cardinality:索引中唯一值的数目的估计值

-- Sub_part:如果列只是被部分地编入索引,则为被编入索引的 字符的数目。如果整列被编入索引,则为 NULL

-- Packed:指示关键字如何被压缩。如果没有被压缩,则为 NULL

-- Null:如果列含有 NULL, 则含有 YES。如果没有,则该列含有 NO

-- Index_type:索引类型(BTREE, FULLTEXT, HASH, RTREE)。

索引实现的原理 索 引 的 最 核 心 思 想 是 通 过 不 断 的 缩 小 数 据 的 范 围 来 筛 选 出 最 终 想 要 的 结 果 ,同 时 把 随 机 事 件 变 成 顺 序 事 件( 二 分 查 找 的 核 心 思 想 ) InnoDB 存储引擎使用 B+ 树来构造索引,之所以使用 B+ 树构造索引,是因为数据和索引都保存在磁盘中,为了提高性能, 每次会把部分数据读入内存来计算。所以,每次查找数据时把磁盘 IO 次数控制在一个很小的数量级是最优的,最好是常数数 量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,B+树应运而生。

B 树和 B+ 树的特性总结 B 树 B 树是一种多路平衡查找树,B 是平衡的意思,即 Balance,m 阶(m>=2)的 B 树有以下特性  树中的每个节点最多有 m 个子节点   除了根节点和叶子节点之外,其他每个节点至少有 m/2 个子节点   所有的叶子节点都在同一层   节点中关键字的顺序按照升序排列  结构图如下所示

B+ 树 B+树是 B 树的一种变体,同样是多路平衡查找树,它与 B 树主要的不同是  非叶子节点不存储数据,只存储索引   叶子节点包含了全部的关键字信息,且叶子节点按照关键字顺序相互连接  结构图如下所示

索引使用的原则 关 于 索 引 的 使 用 原 则 , 美 团 点 评 技 术 团 队 的 文 章 《 M y S Q L 索 引 原 理 及 慢 查 询 优 化 》 里 总 结 的 很 好 , 如 下 : 1. 最左前缀匹配原则,非常重要的原则,mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d 是用不到索引的,如果建立(a,b,d,c)的索引则都可以用 到,a,b,d 的顺序可以任意调整;

2. =和 in 可以乱序,比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql 的查询优化器会帮你优化成索引 可以识别的形式;

3. 尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们 扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0,那可能有人会问,这个比例 有什么经验值吗?使用场景不同,这个值也很难确定,一般需要 join 的字段我们都要求是 0.1 以上,即平均 1 条扫描 10 条记录;

4. 索引列不能参与计算,保持列“干净”,比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很 简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该 写成 create_time = unix_timestamp(’2014-05-29’);

5. 尽量的扩展索引,不要新建索引。比如表中已经有 a 的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

事务隔离级别

什么是事务隔离级别 S Q L 标 准 定 义 了 四 种 隔 离 级 别 , 包 括 了 一 些 具 体 规 则 , 用 来 限 定 事 务 内 外 的 哪 些 改 变 是 可 见 的 , 哪 些 是 不 可 见 的 。 低 级 别 的 隔 离 级 一 般 支 持 更 高 的 并 发 处 理 , 并 拥 有 更 低 的 系 统 开 销 。 四种隔离级别的说明

四 个 级 别 逐 渐 增 强 , 每 个 级 别 解 决 一 个 问 题 。 事 务 级 别 越 高 , 性 能 越 差 , 大 多 数 场 景 r e a d c o m mit t e d 可 以 满 足 需 求 隔离级别与一致性 四 种 隔 离 级 别 采 取 不 同 的 锁 类 型 来 实 现 , 若 读 取 的 是 同 一 个 数 据 的 话 , 就 容 易 发 生 问 题 :  脏读(DrityRead): 某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个 RollBack 了操作, 则后一个事务所读取的数据就会是不正确的。   不可重复读(Non-repeatableread): 在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新 的原有的数据。   幻读(PhantomRead): 在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在 此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。  对应于 MySQL 的四种隔离级别,有可能会产生的问题如下 :

隔离级别的设置 注 意 : 不 同 的 M y S Q L 版 本 , 事 务 隔 离 级 别 对 应 的 变 量 名 也 是 不 同 的 。
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2019年05月22日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 慢查询
  • 索引
  • 事务隔离级别
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档