MySQL优化

1269-郑同学

发表文章数:16

热门标签

首页 » MySQL » 正文

MySQL运行缓慢分析

为什么需要MySQL优化

表的数据量庞大,提高查询速度。

查询过慢的原因

  1. 有规律的速度过慢
    在固定时间固定范围内出现查询速度慢,用户访问量激增
    分库,使用mysql集群处理
  2. 无规律的速度过慢
    跟表中的数据量变化有关系
    对查询语句进行优化

判断查询语句是否需要优化依赖工具

  1. 慢查询记录
    比较时间
  2. explain执行计划
    看索引是否执行
  3. 相关日志文件
    根据用户的查询习惯更改索引

七个查询命令特征(根据执行优先级判定)

from

作用:

  1. 将硬盘上的表文件加载到内存中,生成一个全新的临时表
  2. 定位内存中已经存在的临时表

注意:

  1. 在一个查询语句中,第一个执行的命令永远都是from
  2. from定位的事内存中的一个临时表,这个临时表必须手动指定表名

where

作用:

  1. where命令操作的是由from命令生成的临时表
  2. where命令循环遍历当前临时表中每一个数据行,将满足条件的数据行保存到一个全新的临时表

注意:
由于where命令每次操作的只是一个数据行,因此在where使用过程中,是无法使用聚合函数作为判断条件

group by

作用:

  1. 首先对临时表中数据进行一次排序处理
  2. 然后将具有相同特征的数据行保存到同一个临时表

注意

  1. 多字段分组
    首先,分组字段执行顺序对于查询结果来说没有任何影响;
    然后,从第二个分组字段开始,操作临时表是上一个分组字段生成的临时表
  2. 如果select操作临时表是由group by提供
    此时select将遍历group by生成的每一个临时表,在操作具体临时表时,select只会读取指定字段中第一个数据行内容

having

作用:
负责将group by生成的临时表中不满足条件的临时表进行删除

注意:

  1. having命令不能独立出现,只能出现在group by命令后面
  2. having命令每次操作的是一个临时表,因此选择判断条件应该来自于聚合函数

select

作用:

  1. select操作的临时表,是由from命令或者where命令来提供的。
    select会将指定字段中所有内容读取出来,将读取的内容组成一个全新的临时表
  2. select操作的临时表,是由group by命令或者having命令提供的。
    select将遍历group by生成的每一个临时表,在操作具体临时表时,select只会读取指定字段中第一个数据行内容

order by

作用:
专门针对select生成的临时表中数据行进行排序,将排序后内容组成一个全新的临时表

注意:
如果order by使用字段名进行排序时,字段名可以不出现在select生成的临时表中;
如果order by使用字段顺序进行排序时,索引位置必须在select查询语句中出现

limit

作用:
对临时表中数据行进行截取

查询语句执行特征

  1. 七个查询命令中,除了having命令之外,剩下的六个查询命令执行完毕后都会生成全新的临时表
  2. 七分查询命令中,除了from命令之外,剩下的六个查询命令操作的临时表都是上一个查询命令生成的临时表
  3. 在当前查询命令执行完毕后,mysql自动将上一个查询命令生成的临时表进行销毁处理,在一个查询语句执行完毕后,用户只能看到最后一个查询命令生成的临时表
  4. 在进行多字段分组查询时,从第二个分组字段开始,操作的临时表是上一个分组字段所生成的临时表

需要优化的查询命令

执行速度最慢的: group by

原因:group by执行时首先将临时表中的数据进行排序,然后再进行分组

order by

原因:需要对select生成的临时表数据行进行一次排序,然后将排好顺序的数据行组成一个全新的临时表

where

原因:需要对表中所有数据行进行遍历。如果临时表中数据行过多,导致where执行效率非常慢

limit

原因:如果指定起始行数过大,导致查询速度变慢

多表查询

如果没有指定表文件加载顺序,也会导致查询速度过慢

查询命令执行过慢的原因

  1. 执行时需要对所有的数据进行排序
  2. 执行时需要对表中所有数据行进行遍历
  3. I/O流加载表文件次数

MySQL索引

索引基本介绍

定义:索引存在于对应的索引文件中,索引本质就是一组排好了顺序的一组数据

作用:

  1. 在索引数据中进行排序时,避免再次排序
  2. 在索引数据中对数据行进行定位时,避免全表扫描

索引文件位置:

  1. 如果当前表依赖于INNODB存储引擎,索引存在FRM文件
  2. 如果当前表依赖于MyIsam存储引擎,索引存在MYI文件

索引基本操作:

  1. 查看表文件中已经存在的索引

    SHOW INDEX FROM 表

  2. 创建索引

    CREATE INDEX 索引名 ON 表名(字段名)

  3. 删除索引

    DROP INDEX 索引名 ON 表名

索引分类

  1. 聚簇索引与非聚簇索引
  2. 主键索引与唯一性索引
  3. 单个索引与复合索引

聚簇索引与非聚簇索引

聚簇索引

  1. 只能来自于采用INNODB存储引擎表的数据
  2. MySQL自动将采用INNODB存储引擎表中主键建立索引,这个索引就是聚簇索引
  3. 如果当前表中没有主键,MySQL将会选择一个添加唯一性约束的字段作为聚簇索引
  4. 如果当前表中既没有主键字段,也没有添加唯一性约束的字段,MySQL将随机选取一个字段作为聚簇索引
  5. 在采用INNODB存储引擎的表文件中,必然会存在一个聚簇索引
  6. 在采用INNODB存储引擎的表文件中,只能有一个聚簇索引
  7. 在表文件中其他字段上建立的索引都是非聚簇索引

非聚簇索引

  1. 是由开发人员自行创建的
  2. 对于采用了INNODB存储引擎表,除了一个聚簇索引之外,其他字段上创建的索引都是非聚簇索引
  3. 在采用MyIsam存储引擎的表中,创建的所有索引都是非聚簇索引

两者区别

  1. 聚簇索引:数据节点存储的是当前数据所在的行数以及当前数据所在数据行的内容

    因为聚簇索引的这种结构,所以在查询时可以直接在定位数据节点上读取当前数据所在数据行中的字段信息,不需要使用I/O流到硬盘上表文件中进行读取;

    因此执行效率较快

  2. 非聚簇索引:数据节点存储的是当前数据所在的行数

    由于数据节点中存储的是当前数据所在的行数,没有其相关内容,所以定位之后需要使用I/O流到硬盘上表文件中定位数据行其他字段的内容;

    因此执行效率相对较慢

主键索引与唯一性索引

主键索引

如果当前表文件中字段添加主键约束,MySQL主动将当前字段上的数据进行排序,其生成的索引被称为主键索引

唯一性索引

如果当前表文件中字段添加了唯一性索引,MySQL主动将当前字段上的数据进行排序,其生成的索引被称为唯一性索引

唯一性索引不包含Null

普通索引

如果当前表文件中字段没有添加任何索引,此时在这个字段上创建的索引就是普通索引。

执行效率

主键索引>唯一性索引>普通索引

单字段索引与复合索引

  1. 单字段索引创建

    CREATE INDEX 索引名 ON 表名(字段名)

  2. 复合索引创建

    CREATE INDEX 索引名 ON 表名(字段名1,字段名2,字段名3)

    按字段生成顺序做排序

二分查找算法

将一组数组中平均值作为根结点,将小于平均值数据放在根结点左边,将大于平均值数据放在根结点右边,定位数据时,避免整体数据扫描,只需要对一半的数据进行比较

如果数据量较大时,虽然可以节省将近一半的遍历数据量,但是依然会消耗较多时间

平衡二叉树算法

是二分查找算法的升级版

特点:

  1. 产生数据节点叶子节点概念
  2. 每一个数据节点只能有两个指针,一个指针指向小于当前数据节点的数据,另一个指针指向大于当前数据节点的数据

缺点:

  1. 如果索引相关的字段中数据发生变化时,导致平衡二叉树中内容失效,需要重新创建这个平衡二叉树。因此维护成本较高
  2. 如果根据多个条件进行查询时,对二叉树进行多次遍历,同时也会产生多个I/O流

BTREE算法

介绍

  1. 是平衡二叉树算法升级版
  2. 是目前各种数据库在索引上使用的主要算法

特点

  1. 使用区间数据来代替数据节点上具体的数据,降低维护难度
  2. 在连续叶子节点之间,使用指针来进行连接,降低了遍历次数

Hash算法与Hash索引

在MySQL只有Memory引擎支持哈希索引。

CREATE TABLE testhash(
	fname VARCHAR(50) NOT NULL,
	iname VARCHAR(50) NOT NULL,
	KEY USING HASH(fname)	
) ENGINE = MEMORY;

MySQL会先计算哈希值,并用该值寻找对应的记录指针,最后一步是比较第三方的值是否匹配,以确保就是要查找的行

哈希索引只能进行精准判断,不能进行区间判断

Explain执行计划

explain介绍

MySQL通过explain查看查询语句执行效率。在进行SQL优化的主要手段,通过explain可查看如下信息

  1. 查看表的加载顺序
  2. 查看sql的查询类型
  3. 哪些索引可能被使用,哪些索引实际被使用了
  4. 表之间的引用关系
  5. 一个表中有多少行被优化器查询
  6. 其他额外的辅助信息

id属性介绍

作用:通过id属性指出当前查询语句中相关表文件的加载顺序

含义:

  1. 如果被加载的表文件的id属性内容是相同的,则根据自上而下的顺序进行加载
EXPLAIN SELECT * FROM DEPT JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO;
  1. 如果被加载的表文件id属性内容是不相同的,则id较大的表先被加载,id较小的表后被加载
EXPLAIN SELECT ENAME, SAL, (SELECT DNAME FROM DEPT
														WHERE DEPT.DEPTNO = EMP.DEPTNO)
				FROM EMP;
  1. 如果被加载的表文件id属性内容既有相同又存在不同时,则id较大的表先被加载。id相同的表则按照自上而下的顺序进行加载

select_type属性介绍

对当前查询语句中查询类型进行判断

Simple

当前查询语句是一个简单查询语句,不包含子查询,不包含联合查询,不包含连接查询

Primary

如果执行的是一个包含子查询的查询,或者是一个联合查询。Primary指向的外部查询语句或者是联合查询中第一个子查询语句

联合查询

EXPLAIN
SELECT EMONI, ENAME FROM EMP
UNION
SELECT DEPTNO, DNAME FROM DEPT;

Primary指向第一个EMP表查询;UNION指向DEPT表查询

DEPENDENT SUBQUERY

表示当前查询语句是一个子查询,并且执行条件依赖于外部查询提供的条件

EXPLAIN SELECT ENAME, JOB, SAL, 
(SELECT MAX(SAL) FROM EMP E2 WHERE E2.JOB = EMP.JOB)
FROM EMP;

Primary指向第一个EMP表查询;DEPENDENT SUBQUERY指向E2表查询

SUBQUERY

表示当前查询是一个子查询,并且这个子查询在执行时不需要得到外部查询的帮助

EXPLAIN SELECT ENAME, JOB, SAL,
(SELECT COUNT(*) FROM EMP)
FROM EMP;

Primary指向第一个EMP表查询;SUBQUERY指向emp表查询

type属性介绍

type属性描述mysql对本次查询的评价,是执行计划中一个重要属性。查询语句执行效率从高到低的顺序依次是

  1. system:操作表里只有一行数据,并且数据内容时固定不变的

  2. const:定位数据行时使用了索引。采用主键索引直等方式(where empno = 10)

  3. ref:定位数据行时使用了索引。采用直等方式(where ename = ‘mike’)

    同时满足定位条件的数据行只有一行

  4. range:定位数据行时使用了索引。使用区间定位(where age <=23)

    这种状态实际上是一种不稳定的状态,如果定位的数据行超过了表中总行数30%以上,会导致mysql放弃使用索引定位

  5. index:定位数据行时,没有使用索引(进行全表扫描),但是在已经定位数据行中读取字段时,却使用了索引,也就是说使用select命令时,根据索引读取数据(执行效率较低)

  6. All:全表扫描

SQL优化时,索引级别最低保证达到range级别,最高达到ref级别

possible_keys与key区别

possible_keys:本次查询过程中可能使用到的索引

key:本次查询过程中实际使用到的索引

mysql内部有执行优化器,当它发现查询语句中使用到了多个索引,将会自动选择效率最高的

索引使用规则

  1. 如果索引字段上使用了函数,导致索引失效
  2. 如果索引字段上使用运算,导致索引失效
  3. 如果索引字段上产生了隐式类型转换,导致索引失效
  4. 如果索引字段上进行模糊查询,只支持前置条件模糊查询
标签:

未经允许不得转载:作者:1269-郑同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《MySQL优化》 发布于2020-11-06

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录