侧边栏壁纸
博主头像
Josue博主等级

  • 累计撰写 72 篇文章
  • 累计创建 93 个标签
  • 累计收到 0 条评论
标签搜索

目 录CONTENT

文章目录
数据库

Mysql高级&优化

Josue
Josue
2022-05-26 / 0 评论 / 0 点赞 / 57 阅读 / 9,948 字 / 正在检测是否收录...
温馨提示：
本文最后更新于 2022-05-26，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

一、MySQL逻辑架构

image-20220525114213118

1.连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tep/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用素引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3.引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取。

4.存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引攀的交互。

二、存储引擎

show ENGINES;查看所有的数据库引擎

show variables like '%storage_engine%查看默认的数据库引擎

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 行锁，操作时只锁某一行，不对其它表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，操作时只锁某一行，不对其他行有影响，适合高并发
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间 小 大
关注点 性能 事务
默认安装 Y Y

三、性能下降的多数原因

  • 索引失效
  • 关联查询太多join
  • 服务器调优及参数设置

四、SQL JOINS

image-20220525153806645

五、 索引优化分析

5.1、定义

**索引：**索引（Index）是帮助 MySQL 高效获取数据的数据结构。

可以得到索引的本质： 索引是数据结构。可以简单理解为 排好序的快速查找数据结构

数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

image-20220525163044515

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个 右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针，这样就可以运用 二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

5.2、优缺点

优势：

  • 提高数据检索的效率，降低数据库的IO成本。
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为 更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的

5.3、MySQL的索引

5.3.1、Btree 索引

MySQL 使用的是 Btree 索引。

image-20220525163613752

【初始化介绍】 一颗 b 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色 所示）：

如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3， P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。 真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。

【查找过程】

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指 针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的， 如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高

5.3.2、B+tree 索引

image-20220525163848874

B+Tree 与 B-Tree 的区别

  • B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
  • 在 B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+树中每个记录 的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B- 树的性能好像要比 B+树好，而在实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据， 这样每个节点可容纳的元素个数比 B-树多，树高比 B-树小，这样带来的好处是减少磁盘访问次数。尽管 B+树找到 一个记录所需的比较次数要比 B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+树的性能可能还会好些，而且 B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有 文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+树的缘故

为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就 越多。相对来说 IO 读写次数也就降低了。
  • B+树的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须 走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

5.3.3、聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储 在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致

image-20220525164118907

聚簇索引的好处： 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多 个数据块中提取数据，所以节省了大量的 io 操作。

聚簇索引的限制： 对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。 由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是 该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用 无序的 id，比如 uuid 这种

5.3.4、 时间复杂度（扩展）

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的 目的在于选择合适算法和改进算法。 时间复杂度是指执行算法所需要的计算工作量，用大 O 表示记为：O(…）

image-20220525164319657

5.4、 Mysql 索引创建及分类

5.4.1、单值索引

概念：即一个索引只包含单个列，一个表可以有多个单列索引

语法：

  • 索表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),
customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name)
);
  • 单独创建索引
CREATE INDEX idx_customer_name ON customer(customer_name);

5.4.2、唯一索引

概念：索引列的值必须唯一，但允许有空值

  • 索表一起创建
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_no)
)
  • 单独建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

5.4.3、主键索引

概念：设定为主键后数据库会自动建立索引，innodb为聚簇索引

  • 索表一起创建
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200), PRIMARY KEY(id)
);
  • 单独建唯一索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
  • 删除建主键索引
ALTER TABLE customer drop PRIMARY KEY ;
  • 修改建主键索引
必须先删除掉(drop)原索引，再新建(add)索引

5.4.4、复合索引

概念：即一个索引包含多个列

  • 索表一起创建
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_name), KEY (customer_no,customer_name)
);
  • 单独建唯一索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

5.4.5、 基本语法

操作 命令
创建 CREATE [UNIQUE ] INDEX [indexName] ON table_name(colum
删除 DROP INDEX [indexName]
查看 SHOW INDEX FROM table_name
使用Alter命令 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : 该语句添加一个主键，这意味着索引值必须是唯一 的，且不能为 NULL
使用Alter命令 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
使用Alter命令 ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
使用Alter命令 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索 引

5.5、索引创建的时机

5.5.1、适合创建索引的情况

  • 主键自动建立唯一索引；
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题， 组合索引性价比更高
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

5.5.2、 不适合创建索引的情况

  • 表记录太少

  • 经常增删改的表或者字段

  • Where 条件里用不到的字段不创建索引

  • 过滤性不好的不适合建索

六、 Explain 性能分析

概念：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或是表结构的性能瓶颈

用法： Explain+ SQL 语句

Explain 执行后返回的信息

image-20220525174221780

6.1、 id

select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序

  • id 相同，执行顺序由上至下

image-20220525175538877

  • id 不同，id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

image-20220525175559169

  • 有相同也有不同

image-20220525175626620

id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行衍生 = DERIVED

关注点：id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越

6.2、select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

image-20220525180035022

select_type 属性 含义
SIMPLE 简单的 select 查询,查询中不包含子查询或者 UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUERY 无法使用缓存的子查询
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT

6.3、table

这个数据是基于哪张表的。

6.4、type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref

6.4.1、system

表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计

6.4.2、const

表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。

因为只匹配一行数据，所以很快 如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量

image-20220525181440837

6.4.3、 eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

image-20220525181521214

6.4.4、ref

非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

没用索引前：

image-20220525181718381

建立索引后：

image-20220525181738348

6.4.5、range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引

image-20220525183109574

image-20220525183118210

6.4.6、index

出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

image-20220525183154782

6.4.7、all

Full Table Scan，将遍历全表以找到匹配的行。

image-20220525183235301

6.4.8、 index_merge

在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的

image-20220525183322901

6.4.9、 ref_or_null

对于某个字段既需要关联条件，也需要 null 值得情况下。查询优化器会选择用 ref_or_null 连接查询

image-20220525183358440

6.4.10、 index_subquery

利用索引来关联子查询，不再全表扫描。

image-20220525183440946

image-20220525183452177

image-20220525183500615

6.4.11、unique_subquery

该联接类型类似于 index_subquery。 子查询中的唯一索引

image-20220525183532875

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

6.5、 possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用

6.6、key

实际使用的索引。如果为NULL，则没有使用索引。

6.7、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

key_len 字段能够帮你检查是否充分的 利用上了索引。ken_len 越长，说明索引使用的越充分。

image-20220525185256802

image-20220525185316305

如何计算：

  • 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20

  • 如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3,GBK 要乘 2，

  • varchar 这种动态字符串要加 2 个字节

  • 允许为空的字段要加 1 个字节

    第一组：key_len=age 的字节长度+name 的字节长度=4+1 + ( 20*3+2)=5+62=67

    第二组：key_len=age 的字节长度=4+1=5

image-20220525185415048

6.8、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

image-20220525185949489

6.9、rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好

image-20220525190344863

6.10、Extra

其他的额外重要的信息

6.10.1、Using filesort

说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引 完成的排序操作称为“文件排序”。 出现 filesort 的情况

image-20220525191937379

优化后，不再出现 filesort 的情况：

6.10.2、Using temporary

使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by

image-20220525192436749

6.10.3、 Using index

Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组

6.10.4、Using where

表明使用了 where 过滤。

6.10.5、 Using join buffer

image-20220525192852106

使用了连接缓存。

6.10.6、 impossible where

where 子句的值总是 false，不能用来获取任何元组。

image-20220525192941279

6.10.7、select tables optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操 作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在 innodb 中：

image-20220525193034394

在 Myisam 中：

image-20220525193053792

七、索引失效

7.1、全值匹配

查询的字段按照顺序在索引中都可以匹配到！

7.2、 最佳左前缀法则

查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索 引的最左前列开始并且不跳过索引中的列。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无 法被使用。

7.3、不要在索引列上做任何计算

不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描

7.3.1、在查询列上使用了函数

在查询列上使用了函数

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;

EXPLAIN SELECT SQL_NO_CACHE * FROM emp

image-20220525221801498

结论：等号左边无计算！

7.3.2、 在查询列上做了转换

create index idx_name on emp(name);

explain select sql_no_cache * from emp where name=‘30000’;

explain select sql_no_cache * from emp where name=30000;

字符串不加单引号，则会在 name 列上做一次转换

image-20220525222935504

结论：等号右边无转换！

7.4、 索引列上不能有范围查询

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; 
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';

image-20220525223029038

建议：将可能做范围查询的字段的索引顺序放在最后

7.5、尽量使用覆盖索引

即查询列和索引列一致，不要写 select *!

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';

image-20220525224935476

7.6、使用不等于(!= 或者<>)的时候

mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。

image-20220525225046457

7.7、 字段的 is not null 和 is null

image-20220525225553150

is not null 用不到索引，is null 可以用到索引。

7.8、 like 的前后模糊匹配

image-20220525225755371

前缀不能出现模糊匹配

**解决：**使用覆盖索引解决，即查询字段在索引中，避免回表

7.9、 减少使用 or

image-20220525225911004

使用 union all 或者 union 来替代

image-20220525231347386

7.10、口诀

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写*；

不等空值还有 OR，索引影响要注意；

VAR 引号不可丢，SQL 优化有诀窍。

八、Mysql优化Order by

  • 观察，至少跑1天，看看生产的慢SQL情况。
  • 开启慢查询日志，设置值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
  • explain+慢SQL分析
  • show profile
  • 运维经理orDBA，进行SQL数据库服务器的参数调优。
  • 慢查询的开启并捕获
  • explain+慢SQL分析
  • showprofile查询SQL在Mysq1服务器里面的执行细节和生命周期情况
  • SQL数据库服务器的参数调优。

8.1、小表驱动大表

image-20220526115928387

EXISTS：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定
主查询的数据结果是否得以保留

  • 提示

EXISTS（subquem）只返回TRUE或FALSE，因此子查询中的SELECT*也可以是SELECT1或selectx，官方说法是实际执行时会忽略SELECT清单，因此没有

EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。

EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

8.2、order by

  • ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

  • 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缓

  • 如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序

    • 双路算法：读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
    • 单路算法：从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO.但是它会使用更多的空间，因为它把每一行都保存在内存中了。

8.2.1、提高Order By的速度

Order by 时select* 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是：

  • 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法一一单路排序，否则用老算法一一多路排序。
  • 两种算法的数据都有可能超出sortbuffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sortbuffer_size。
  • **尝试提高sortbuffer_size。**不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
  • 尝试提高max_length_for_sort_data。
  • 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sortbuffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率.

九、慢查询日志

91、查看是否开启查看

SHOW VARIABLES LIKE '%slow_query_log%'

9.2、开启男查询

  • 对当前数据库生效，且重启后失效
set GLOBAL slow_query_log=1
  • 永久生效【不推荐】
如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件


slow_query_log=1
file=/var/lib/mysql/my-slow.log

9.3、查看慢查询时间

SHOW VARIABLES LIKE 'long_query_time%'
#默认10s定义为慢查询

#修改阈值时间
set global long_query_time = 3

9.4、查询慢查询记录数

show global status like '%slow_queries%'

9.5、日志分析工具mysqldumslow

  • 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/日志文件-slow.log
  • 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/日志文件-slow.log
  • 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/日志文件-slow.log
  • 另外建议在使用这些命令时结合一和more使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/日志文件-slow.log lmore

9.6、SHOW PROCESSLIS

查询 mysql 进程列表，可以杀掉故障进程。

kill id;

十、批量数据脚本

10.1、建库表

create DATABASE bigData
use bigData

CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, ceo INT NULL , PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 


CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1

10.2、设置参数

在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。 否则会报错

image-20220526132808513

  • 查询：
show variables like 'log_bin_trust_function_creators';
  • 设置
set global log_bin_trust_function_creators=1;

  • 当然，如上设置只存在于当前操作，想要永久生效，需要写入到配置文件中：

    在[mysqld]中加上 log_bin_trust_function_creators=1

10.3、创建随机函数

创建随机函数，保证每条数据都不同。

#创建随机字符串
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`() RETURNS varchar(255) CHARSET utf8mb4
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT REPLACE(UUID(),"-","");
DECLARE return_str VARCHAR(255) DEFAULT '';
set return_str=chars_str;
RETURN return_str;
END


#创建随机数
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END

10.4、创建存储过程

#存储过程1
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(),rand_string(),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END

#存储过程2
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_emp`( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i = i+ 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string() , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END

十一、Show Profile

定义：是mysqi提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

默认情况下处于关闭状态，并保持最近15次的运行结果

11.1、分析步骤

11.1.1、是否支持，看看当前的mysq版本是否支持

11.1.2、开启功能，默认是关闭，使用前需要开启

show VARIABLES like 'profiling'
#8.0默认开启

set profiling=on;

11.1.3、运行SQL

11.1.4、查看结果，showprofiles；

show profiles

image-20220526153226633

  • 诊断SQL，showprofilecpu,blockioforquery上一步前面的问题SQL数字号码；
show profile cpu,block io for query 100

其他参数：

type 释义
ALL 显示所有的开销信息
BLOCK IO 显示块IO相关开销
CONTEXT SWITCHES 上下文切换相关开销
CPU 显示CPU相关开销信息
IPC 显示发送和接收相关开销信息
MEMORY 显示内存相关开销信息
PAGE FAULTS 显示页面错误相关开销信息
SOURCE 显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS 显示交换次数相关开销的信息

11.2、日常注意

  • converting HEAP to MyISAM

    查询结果太大，内存都不够用了往磁盘上搬了。

  • Creating tmp table

    创建临时表

  • Copying to tmp table on disk

    把内存中临时表复制到磁盘，危险！！！

  • locked

十二、全局日志

set global general_log =1;

set globallog_output = 'TABLE';

select # from mysql.general_log;

禁止生产环境使用

十三、Mysql锁机制

13.1、概率

13.1.1、数据操作类型

  • 读锁（共享锁）

    针对同一份数据，多个读操作可以同时进行，互不影响

  • 写锁（排他锁）

    当前写操作没有完成前，回阻断其他写锁和读锁

13.1.2、数据操作的粒度

  • 行锁
  • 表锁

13.2、三锁

表锁、行锁、页锁

13.2.1、表锁

读锁会阻塞写，不会阻塞读。

写锁会把读写都阻塞。

如何分析表锁定

可以通过检查table_locks_waited和tablelocks_immediate状态变量来分析系统上的表锁定。

  • SQL : show status like table%；

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

  • Table_locks_immediate ：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
  • Table locks_waited : 出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；

13.2.2、行锁

  • 行锁支持事务
如何分析行锁定

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量；
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
  • Innodb_row_lock_time_avg：每次等待所花平均时间；
  • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是
Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果礼
手指定优化计划。

13.2.3、间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁：对于键
值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

【危害】
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

十四、主从复制

14.1、 复制的基本原理

  • slave会从 master 读取 binlog 来进行数据同步

image-20220526182226247

  • MySQL 复制过程分成三步
    • master 将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events。
    • slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）
    • slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化

14.2、复制的基本原则

  • 每个 slave 只有一个 master
  • 每个 slave 只能有一个唯一的服务器 ID
  • 每个 master 可以有多个 salve

14.3、复制的最大问题

因为发生多次 IO，存在延时问题

14.4、一主一从常见配置

  • mysql 版本一致且后台以服务运行

  • 主从都配置在[mysqld]结点下，都是小写， 主机修改 my.ini 配置文件

  • 主机配置

主服务器唯一 ID
server-id=1
启用二进制日志
log-bin=自己本地的路径/data/mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
设置不要复制的数据库
binlog-ignore-db=mysql
设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
设置 logbin 格式
binlog_format=STATEMENT（默认

mysql 主从复制起始时，从机不继承主机数据

  • 从机配置文件修改 my.cnf 的[mysqld]栏
#从机服务 id
server-id = 2
#注意 my.cnf 中有 server-id = 1
#设置中继日志
relay-log=mysql-rela
  • 因修改过配置文件，请主机+从机都重启后台 mysql 服务
  • 主机从机都关闭防火墙
  • 主机上建立帐户并授权 slave
#创建用户，并授权
GRANT REPLICATION SLAVE ON *.* TO '备份账号'@'从机器数据库 IP' IDENTIFIED BY '123456';
  • 查询 master 的状态，并记录下 File 和 Position 的
#查询 master 的状态
show master

image-20220526183811356

  • 从机上配置需要复制的主机
#查询 master 的状态
CHANGE MASTER TO MASTER_HOST='主机 IP',MASTER_USER='创建用户名',MASTER_PASSWORD='创建的密码', MASTER_LOG_FILE='File 名字',MASTER_LOG_POS=Position 数字
  • 启动从服务器复制功能
start slave;
show slave status\G
0
MySQL mysql优化
版权归属： Josue
本文链接： https://kakarot.cc/archives/mysql-gao-ji--you-hua
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区