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

慢查询

什么是慢查询

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 版 本 , 事 务 隔 离 级 别 对 应 的 变 量 名 也 是 不 同 的 。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券