MySQL优化

目录

一、概述

---

一、概述

        为什么要学习MySQL优化？求职面试要求具有MySQL优化的能力。

1、查询过慢原因

1.有规律的速度过慢：用户访问量激增--->mysql集群解决

2.无规律速度过慢：跟表中数据量变化有关系--->查询语句优化

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

1.慢查询记录

2.explain执行计划

3.相关日志文件

3、MySql查询语句中七个查询命令特征(根据七个查询命令优先级)

1.from：
    作用：
        1.将硬盘上的表文件加载到内存中，生成一个全新的临时表
        2.定位内存中已经存在的临时表
    注意:
        1.在一个查询语句中，第一个执行命令永远都是from
        2.from定位的是内存中一个临时表，这个临时表必须手动指定表名
2.where:
    作用:
        1.where命令操作的由from命令生成的临时表
        2.where命令循环遍历当前临时表中每一个数据行，将满足条件的数据行保存到一个全新的临时表
    注意:
        1.由于where命令每次操作只是一个数据行，因此在where使用过程中，是无法使用聚合函数作为判断条件
3.group by:
    作用:
        1.首先对临时表中的数据进行一次排序处理
        2.然后将具有相同特征的数据行保存到同一个临时表
    注意:
        1.多字段分组
          首先，分组字段执行顺序对于查询结果来说没有任何影响的
          然后，从第二个分组字段开始，操作临时表是上一个分组字段生成的临时表
        2.如果select操作临时表是由group by提供。
          此时select将遍历group by生成的每一个临时表
          在操作具体临时表时，select只会读取指定字段中第一个数据行内容
4.having:
    作用:
        1.负责将group by生成的临时表中不满足条件的临时表进行删除
    注意:
        1.having命令不能独立出现的，只能出现在group by命令后面
        2.having命令每次操作的是一个临时表，因此选择判断条件应该来自于聚合函数
5.select:
    作用:
        1.select操作的临时表，由from或where命令来提供。
          select将指定字段中所有内容读取出来，将读取的内容组成一个全新的临时表
        2.select操作的临时表，由group by或having命令来提供。
          此时select将遍历group by生成的每一个临时表
          在操作具体临时表时，select只会读取指定字段中第一个数据行内容
6.order by:
    作用:
        1.专门针对select生成的临时表中数据行进行排序，将排序后内容组成一个全新的临时表
    注意:
        1.如果order by使用字段名称进行排序时，字段名可以不出现在select生成的临时表中
          如果order by使用字段顺序进行排序时，索引位置必须在select查询语句中出现
7.limit:
    作用:
        1.对临时表中数据行进行截取

4、查询语句特征

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

5、七个查询命令中，哪些命令执行效率过慢

1.执行速度最慢的:group by
    原因:group by执行时首先将临时表中的数据进行排序，然后再进行分组
2.order by:
    原因:需要对select生成的临时表中的数据行进行一次排序，然后将排好顺序的数据行组成全新的临时表
3.where:
    原因:需要对表中所有数据行进行遍历，如果临时表中数据行过多的话，导致where执行效率非常慢
4.limit:
    原因:如果指定的起始行数过大，导致查询速度的过慢
5.对于多表查询来说，没有指定表文件加载顺序，也会导致查询速度过慢

6、查询命令执行过慢的原因

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

7、索引介绍

1.定义:
    索引存在于对应的索引文件中，索引本质就是排好了顺序的一组数据
2.作用:
    1.在索引数据中进行排序时，避免再次排序
    2.在索引数据中，对数据行定位时，避免全表扫描
3.索引文件的位置
    1.如果当前表依赖于INNODB存储引擎，索引存在FRM文件
    2.如果当前表依赖于MYISAM存储引擎，索引存在MYI文件
4.索引基本操作
    1.查看表文件中已经存在的索引
    SHOW INDEX FROM 表
    2.创建索引
    CREATE INDEX 索引名 ON 表名(字段名)
    3.删除索引
    DROP INDEX 索引名 ON 表名

8、索引分类

1.聚簇索引与非聚簇索引
    聚簇索引:
        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流到硬盘上表文件中定位数据行其他字段内容，因此执行效率相对较慢。
2.主键索引，唯一性索引
    主键索引:
        1.如果当前表文件中字段添加了主键约束，MySql主动的将当前字段上数据进行排序，
        其生成的索引被称为主键索引
    唯一性索引:
        1.如果当前表文件中字段添加了唯一性索引，MySql主动的将当前字段上数据进行排序，
        其生成的索引被称为唯一性索引。
        唯一性索引不包含null
    普通索引:
        1.如果当前表文件中字段上没有添加任何索引，此时在这个字段上创建的索引就是普通索引。
    执行效率:
        主键索引>唯一性索引>普通索引
3.单个索引，复合索引
    单字段索引创建:CREATE INDEX 索引名 ON 表名(字段名)
    复合索引创建:CREATE INDEX 索引名 ON 表名(字段名1,字段名2...)

9、二分查找算法

将一组数组中平均值作为[根节点],将小于[平均值]数据，放到[根节点]左边，将大于[平均值]
数据放在[根节点]右边。
定位数据时，避免整体数据扫描，只需要对一半的数据进行比较。
[缺陷]如果数据量较大时，虽然可以节省将近一半的遍历数据量，但是依然会消耗较多时间。

10、平衡二叉树算法

平衡二叉树算法:
    1.是二分查找算法的升级版。
    2.[特点]
        1.产生[数据节点]与[叶子节点]概念
        2.每一个[数据节点]只能有两个指针。一个指针指向小于当前[数据节点]的数据。
        另一个指针指向大于当前[数据节点]的数据。
    3.[缺点]:
        1.如果索引相关的字段中数据发生变化时，导致平衡二叉树中内容失效，需要重新
        创建这个平衡二叉树。因此维护成本较高。
        2.如果根据多个条件进行查询时，对二叉树进行多次遍历，同时也会产生多个I/O流

11、BTREE算法

BTREE算法:
    1.[介绍]
        1.是平衡二叉树算法升级版
        2.是目前各种数据库在索引上使用的主要算法
    2.[特点]
        1.使用区间数据来代替数据节点上具体的数据，降低维护难度
        2.在连续的叶子节点之间使用指针来进行连接，降低遍历次数

干我们这行，啥时候懈怠，就意味着长进的停止，长进的停止就意味着被淘汰，只能往前冲，直到凤凰涅槃的一天！