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