MySQL调优随笔

已于 2023-01-08 15:11:07 修改
阅读量327 收藏 1
点赞数 1
文章标签： mysql 数据库 java
于 2022-08-18 13:35:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/niTaoTaoa/article/details/126400490
版权

零、命令小记

desc 表名; #查看表结构信息
show database 数据库名；#查看该数据库的所有表
show status like “last_quary_cost”: #查询上一次 的查询记录数量（越小越好）
use 数据库名; #进入某个数据库
show tables; # 查看本库所有表名
show variableslike %要查询的字段%;
在这里插入图片描述
添加 global 来设置全局变量

一、性能监控

  1. 使用show profile查询剖析工具 (上手容易)

默认是禁用的，开启 set profiling=1;
查看具体的执行时间 show profiles;
显示所有性能信息 show profile all for query n

  1. 使用performance schema来更加容易的监控mysql（更全面但更复杂）

二、数据类型优化

2.1 优化原则

  1. 更小的通常更好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少

  1. 简单就好 (不要什么数据类型都用字符串存，该用啥用啥)

简单数据类型的操作通常需要更少的CPU周期，例如，
1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址
select INET_ATON(‘192.168.85.111’) 得到
select INET_NTOA(‘3232257391’)得到192.168.85.111

  1. 尽量避免使用 null （ 可以用空字符串替换 ）

如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂.
null 的列改为 not null 带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列.

2.2 实际的数据类型优化

2. 2.1 整型类型 （不指定长度默认11）

tinyint 8 位，smallint 16 位，mediumint 24 位，int 32 位，bigint 64 位
尽量使用满足需求的最小数据类型
比如 int (1) ,实际存10位也不会报错，规定长度只是 规定一下。varchar不一样，规范多少就这么多

2.2.2 字符和字符串类型 （不指定长度默认255）

varchar根据实际内容长度保存数据
char固定长度的字符串

  • 最大长度：255
  • 会自动删除末尾的空格
  • 检索效率、写效率 会比varchar高，以空间换时间

2.2.3 BLOB和TEXT类型（尽量不要用）

MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。
！！！不要用！！！
可以把大文件存在某个路径下，这里存地址

2.2.4 时间戳

1、不要使用字符串类型来存储日期时间数据
2、日期时间类型通常比字符串占用的存储空间小
3、日期时间类型在进行查找过滤时可以利用日期来进行比对
4、日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算
5、使用int存储日期时间不如使用timestamp类型

datetime 精确到毫秒
	占用8个字节
	与时区无关，数据库底层时区配置，对datetime无效
	可保存时间范围大
	不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性
	
timestamp 精确到秒
	占用4个字节
	时间范围：1970-01-01到2038-01-19
	采用整形存储
	依赖数据库设置的时区
	自动更新timestamp列的值
	
date 精确到日期
	占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
	使用date类型还可以利用日期时间函数进行日期之间的计算
	date类型用于保存1000-01-01到9999-12-31之间的日期

2.2.5 使用枚举代替字符串类型

三、合理使用范式和反范式

在企业中很好能做到严格意义上的范式或者反范式，一般需要混合使用

范式
	优点
		更新比反范式快
		很少或者没有重复的数据
		数据小，可以放内存，操作快
	缺点
		需要关联
		
反范式   ( 空间换时间 )
	优点
		数据在同一张表，避免关联
		可以设计有效的索引；
	缺点
		冗余较多，删除数据时候会造成表有些有用的信息丢失

范式设计
在这里插入图片描述
反范式设计 ( 空间换时间 )
在这里插入图片描述

三范式
第一范式：列不可分
第二范式：列间没有传递依赖
第三范式：其他列必须依赖于主键

四、主题的选择

代理主键
	与业务无关的，无意义的数字序列
自然主键  （身份证号）
	事物属性中的自然唯一标识
推荐使用代理主键
	它们不与业务耦合，因此更容易维护
	一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

五、字符集的选择

字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

  1. 存中文 utf8mb4
    utf8有的中文占两个字节，有的中文占3个字节（中）
  2. 不存中文 latin1 纯拉丁字符，节省大量的存储空间

六、存储引擎的选择（默认InnoDB）

存储引擎（数据文件的组织形式）
在这里插入图片描述
数据文件和索引文件不放在一起为非聚簇索引，否则为聚簇索引

七、适当的数据冗余 （ 物化视图 ）

1.被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
2.这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，
完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的
同时冗余字段也被更新。

八、适当拆分

对于不经常访问的大字段，我们将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。
好处是每个数据块可存储数据条数大大增加，减少物理 IO 次数，大大提高内存中的缓存命中率。

九、执行计划

​可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

执行计划中包含的信息

ColumnMeaning
idThe SELECT identifier
select_typeThe SELECT type
tableThe table for the output row
partitionsThe matching partitions
typeThe join type
possible_keysThe possible indexes to choose
keyThe index actually chosen
key_lenThe length of the chosen key
refThe columns compared to the index
rowsEstimate of rows to be examined
filteredPercentage of rows filtered by table condition
extraAdditional information

id

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

id号分为三种情况：
​		1、如果id相同，那么执行顺序从上到下
​		2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
​		3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
		1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
​		2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
​		3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下，得保证查询至少达到range级别，最好能达到ref

--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

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

key
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

ref
显示索引的哪一列被使用了，如果可能的话，是一个常数

rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

extra
包含额外的信息。

十、通过索引进行优化

  1. mysql不用hash索引的原因是：hash多用于等值查询，而mysql多用于范围查询
  2. mysql不用二叉树为索引的原因是：数据多时层级过深
  3. mysql不用AVL二叉平衡树为索引的原因是：插入删除时调整树代价大
  4. mysql不用红黑树（平衡+变色），红黑树使最长子树深度不超过最短子树的2倍即可，但最终也会因为层级过深影响读取效率。（红黑树规则：黑为头结点，每个路上黑数量一致，不可出现两个连续的红）
  1. mysql不使用B树的原因：

这是B树的结构，内存每次读取4K数据，Innodb是16K，假设每个数据占1KB，则三层结构下，最多也就161616条记录。所以B树采用叶子结点存数据，其余结点仅存索引，则为161000/10161000/1016=4096,0000。则在三层索引下这就是两千万条数据。
在这里插入图片描述
显然这是优点，但同样，这里也是等值查询，不支持范围查找。

  1. InnoDB为什么选择B+树
    显然融合了B树的数据量大，层次低，也支持范围查询，>

InnoDB是数据和索引在同一个文件，而MyISAM是数据文件和索引文件分开的，叶子结点存的不是数据，是数据文件中的位置信息。
注意：创建索引是根据主键创建的，如果没有主键，会选择，唯一键，如果还没有，则会创建一个6位数的row_id
在这里插入图片描述
row_id在orcle可以看到，Mysql看不到在这里插入图片描述

索引分类：

  1. 主键索引（唯一且非空）
  2. 唯一索引（数据库默认创建此索引）
  3. 普通索引
  4. 全文索引（用于text,varchat等）
  5. 组合索引

面试技术名词

  1. 回表：先查找某列的B树，在此树的叶子结点中找到主键，再根据这个主键在这个B+树中找到整行记录。即回到主键的树中找这个数。
  2. 覆盖索引：避免回表。主要用于组合索引。（直接组合索引包括主键和此列，避免了【先查找某列的B树，在此树的叶子结点中找到主键】）
  3. 最左匹配: 最左前缀匹配原则
  4. 索引下推：谓词下推。比如where name=? and age=?，原本是先找name=?，找完再从其中找age=?，优化索引下推后，直接找name=? and age=?

在这里插入图片描述

在这里插入图片描述
如果建立了索引a,b,c
则只有顺序的时候且不缺的情况下都使用到索引，遇到or语句，索引失效，遇到范围查找且当前之前是顺序使用索引，则截止到此列（包含）生效，遇到不遵循最做前缀匹配原则，则只生效之前的。

创建索引

alter table 表名 add index 索引名（列1，列2，列3）

聚簇索引和非聚簇索引

聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起

优点：把相关数据保存在一起，访问更快
缺点：插入新行时更新索引时代价很高，需要移动行，有时会面临页分裂；插入速度依赖于插入顺序，按照主键的顺序插入是最快的方式。（大量插入时为了提升效率，避免频繁更新索引，可以先关闭索引，插入完毕后再统一更新索引）
非聚簇索引：数据文件和索引文件分开存放
·
覆盖索引
查询的列就是索引。即查到索引结果，就查完了，不用根据索引结果去查其他的，就避免了回表的过程
在这里插入图片描述
不能仅仅根据sql语句判断是否使用索引
在这里插入图片描述
在这里，id是主键索引，也是用索引的
show index from 表名 ； 查看表的所有索引
desc 表名； 查看表的所有列
在这里插入图片描述
前缀索引
在这里插入图片描述

此处以left(city,7)代替city列，只取前7位。比如原本索引创建需要50字节，控制大小在7位，索引就变的小了

，

在这里插入图片描述
创建前缀索引
alter table 表名 add key(列名（前几位）)

Carrdinallity是基数，是个大致数量。不是准确数字
在这里插入图片描述

此处使用了范围查找，不遵循最左前缀匹配原则，索引失效，所以最后使用了额外文件进行 filesort
在这里插入图片描述

在使用order by 时，如果根据索引列进行asc，则会用到索引，因为索引在创建时默认升序，但如果是多个索引列，同时asd 或 同时 desc 都会用到索引，但有的asc 有的desc 则索引失效，因为同向时要么升序要么降序，不同向时没发使用索引一次遍历。
在这里插入图片描述

在这里插入图片描述

在数据类型不一致时，强制类型转换，会变成全表扫描
比如phone的数据类型是varchar,此处给了int类型，则type为all;
再变回字符类型值时，恢复type为ref
possible_keys： 可能会用到的索引
key:null 实际上没有用
在这里插入图片描述
在这里插入图片描述

优化小细节
1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

select actor_id from actor where actor_id=4;
select actor_id from actor where actor_id+1=5;

  1. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
  2. 使用前缀索引
  3. 使用索引扫描来排序
  4. union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

  1. 范围列可以用到索引

范围条件是：<、>
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

  1. 强制类型转换会全表扫描

explain select * from user where phone=13800001234;
不会触发索引
explain select * from user where phone=‘13800001234’;
触发索引

  1. 更新十分频繁，数据区分度不高的字段上不宜建立索引

更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

  1. 创建索引的列，不允许为null，可能会得到不符合预期的结果
  2. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
  3. 能使用limit的时候尽量使用limit
  4. 单表索引建议控制在5个以内
  5. 单索引字段数不允许超过5个（组合索引）

14 .创建索引的时候应该避免以下错误概念

索引越多越好
过早优化，在不了解系统的情况下进行优化

常用的优先级

system > const > ref > range > index > all

优化思路：当有sql语句执行慢了，先 set profiling=1开启性能监测，执行完sql再执行show profiles查看执行情况。
也可以explain sql语句查看其执行详情，主要看type的优先级，参考上面的优先级，如果是all，则考虑创建索引，或者是有索引失效的情况。

查看索引监控（没有针对单个索引监控，此处打印的是所有的）

这个是索引有问题的

在这里插入图片描述

这个是索引生效的

在这里插入图片描述

在这里插入图片描述
注意，迁移数据的时候为了提高效率，一般先关闭索引，迁移完毕再打开索引

十一、查询优化

1 查询慢的原因：

网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间

2 优化数据访问

一、查询性能低下的主要原因是访问的数据太多，，某些查询不可避免的需要筛选大量的数据，可以通过减少访问量的方式进行优化。
---  比如 确定应用程序是否在检索大量超过需要的数据

在这里插入图片描述
这里是要查第10000到10005这五条数据，查到的rows为16005，全部查了一遍。

— 再比如排序时 确定mysql服务器层是否在分析大量超过需要的数据行。
因为如果查到的数据量很少，就可以直接索引排序了，但如果查到的数据量很大，则使用效率及其低下的文件排序。此时再 order by ， 效率自然低了。

二、是否向数据库请求了不需要的数据
1.* 查询不需要的数据*：我们平时开发中看着像mysql只返回我们需要的数据，其实是都返回了，再取前几行结果。
		优化方式 ： 使用 limit  限制 
2. *多表关联时返回全部列*：应该只 select 需要的列，不能盲目 select ，同时多表联查建议使用别名
3. *总是取出全部列*：应该只 select 需要的列，不能盲目 select *
4. 重复查询相同的数据

3 执行过程的优化

一、查询缓存
	在解析一个查询语句之前，如果查询缓存是打开的，那么Mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存（命中率比较低，一般常量表命中率高），那么会在返回结果之前检查用户权限，如果权限没有问题，那么Mysql会跳过所有的阶段，直接从缓存中拿到结果并返回给客户端（在mysql8之后弃用）
二、查询优化处理
	1 语法解析器和预处理
		mysql 通过 关键字 将sql语句进行解析，并生成一棵解析树，mysql解析器将使用mysql语法规则验证和解析查询。如验证使用了错误的关键字 或者  顺序是否正确等等，预处理器会进一步检查解析树是否合法，列如表名和列名是否存在，是否有歧义，还会验证权限等等。
		
	2 查询优化器
			mysql会根据 每个表或索引的页面个数、索引的基数、索引和数据行的长度、索引的分部情况 进行查询优化，但很多情况会选择错误的执行计划，原因是：执行计划的成本估算不等同于实际的执行成本、mysql的最优可能和你想的不一样、mysql不考虑其他并发执行的查询、mysql不考虑不受其控制的操作成本。
		
		优化器的优化策略
			- 静态优化：直接对解析树进行分析，并完成优化
			- 动态优化：与查询的上下文有关，也有可能跟取值、索引对应的行数有关
			- mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估
		
		优化器的优化类型
			- 重新定义关联表的顺序
			- 将外连接 left / right join转化为内连接 inner join ，内连接的效率更高于外连接，因为内连接获取的数据量小。
			- 使用等价变换规则，简化并规划表达式。     a>4&&a<4 优化为 a !=4 
			- 优化count()   min()  max() 等聚合函数，建议使用分组，分组时可以用聚合索引。再者，索引和列可以为空，通常可以帮助Mysql优化这类表达式：例如要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较。
			- 预估并转化为常数表达式:当mysql检测到一个表达式可以转化为常数的时候，会一直把该表达式作为常数进行处理，最终的type为const级别。
			- 索引覆盖扫描，当索引中的列  包含 所有查询中需要使用的列 的时候，可以使用覆盖索引。   
			- 子查询优化：如果需要连接的数据太多，完全可以使用子查询把这些数据先查出来，mysql缓存起来，再做处理。
			- 等值传播 
			
	三、关联查询 join 的三种实现

直接嵌套循环
在这里插入图片描述
利用索引嵌套循环
在这里插入图片描述
使用 join buffer 嵌套循环 （默认开启了join buffer）
在这里插入图片描述
join buffer 会缓存所有参与查询的列，而不是只有 join 的列
这个buffer的size可以调整，最低256kb，最大4G（mysql5.1之前）
show variables like “%join_buffer%”
在这里插入图片描述

这几个 inner join 其实并不是按顺序 inner join 的，mysql在后面执行了优化器
在这里插入图片描述

show status like “last_quary_cost”: 查询上一次 的查询记录数量（越小越好）

加上 straight_join 则不执行优化器，强制按sql顺序inner join
在这里插入图片描述
四、排序优化（order by）
order by 分为两次传输排序和单次传输排序
两次传输排序：先将需要的字段读取出来排序，按原列顺序读，相当于随机IO
单次传输排序：先按排序列读数据，再排序，按此列顺序读，相当于顺序IO；缺点是查的数据太多时，会大量占用缓冲区空间
当需要排序的列的总大小加上orderby的列大小超过 max_length_for_sort_data定义的字节（默认1KB），mysql会选择双次排序，否则选择单次排序。 也可以直接自己设置选择哪一种。

count()函数
count(*) 和 count(数字)一样快
count(列名)不统计值为null的记录，count * 和 count (数字)统计值为null的记录

4 优化特点类型的查询

优化关联查询
确保on或using子句上的列有索引，在创建索引是 注意 ！！关联的顺序！！
确保group by 和 order by中的表达式只涉及一个表的列，这样才能用索引。

优化子查询
尽量不要用子查询，用连接 join，因为使用子查询是创建临时表，不如直接用join连接了。

推荐使用自定义变量

@表示用户自定义变量
@@表示系统变量
select @@autocommit; //查询这个系统变量的值

set @i:=1 //设置用户自定义变量，只在本次会话中存在，quit后再登录进来，就没了。（当前会话有效）
select @i;
select @i:=@i+1 //自增1

注意，在赋值和读取变量的时候可能是在查询的不同阶段，一定要加 小括号。

优化limit分页

一张表有280万条数据，查询第100 0000-100 0005条

select * from student limit 1000000,5; # 0.3m

select * from student a join (select id from student limit 100 0000,5) b on a.id=b.id ; # 0.2m

再比如
select film_id.description from film order by title limit 50,5;
select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);
后者扫描的rows是前者的十分之一
select 查询列 from 表名 join (select 索引列 from 表名 limit 50,5);

优化union查询

除非确实需要服务器消除重复行，否则一定要使用union all，如果没有 all 关键字，mysql会在
查询的时候给临时表加上 distinct 的关键字，这个操作的代价很高。

十二、分区表

分区表是由多个底层表组成，这些底层表由句柄对象标识。

1. 底层原理

在对分区表操作时，分区层会先对所有底层表上锁，然后选择对哪个分区进行操作。
当然，并不是所有分区表都是先锁全表，如果支持行级锁，比如innodb，就会在分区层释放对应表锁。

2. 分类

a: 范围分区 range

基于数字范围、时间间隔分区。
根据列只在给定范围内将 行 分配给不同分区

b: 列表分区 list

类似等值查找，把一些固定的值放到一些集合中，查询时等值查找
类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值进行选择。

c: 列分区

list + range
mysql从5.5开始支持column分区，可以认为是range和list的升级版，在5.5之后，可以使用column分区代替range和list,但是column分区只接受普通列不接受表达式。

d: 哈希分区

建立哈希表，比如取模运算。
此代码创建了4个分区 PARTITIONS 4

在这里插入图片描述
基于用户定义的表达式的返回值进行选择分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以保护Mysql中有效的，非负的任何表达式

e: Key分区

在这里插入图片描述
类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值。

f: 子分区

在这里插入图片描述
在分区的基础上，再进行分区后存储。

3. 如何使用分区表

全量扫描数据，不需要任何索引
索引数据，并分离热点数据

如果数据有明显的热点，比如大部分一年访问一次，小部分每天访问，可以把这小部分数据单独放到一个分区表，能够使用索引，也能有效的使用缓存

4. 使用分区表需要注意的问题

a: null值会使分区过滤无效
b: 分区列和索引列不匹配，会导致查询无法进行分区过滤
c: 选择分区的成本可能很高
d: 打开并锁住所有底层表的成本可能很高
e: 维护分区的成本可能很高

十三、服务器参数设置

1. general

在这里插入图片描述

2. 字符相关

在这里插入图片描述

3. connection连接相关

在这里插入图片描述

4. log 日志

在这里插入图片描述

innodb有redo.log和undo.log
而binlog是所有存储引擎都有的。

4.1 redo.log日志

在这里插入图片描述

4.2 undo.log日志

备份日志，写一个相反的语句。

在这里插入图片描述

4.3 binlog日志

在这里插入图片描述

在这里插入图片描述

4.4 数据更新的流程

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

4.5 redo.log的两阶段提交

不是提交完一个再写，也不是先写再提交，是两个都提交了，再写。

在这里插入图片描述

在这里插入图片描述

5. cache

在这里插入图片描述
在这里插入图片描述

6. innodb

值分别为0 1 2 就是选择不同的提交方式

在这里插入图片描述

在这里插入图片描述

7. 锁机制

ISAM

读锁：使该表只能读，不管当前还是其他会话，都只能读不能写，本会话写的时候会报错，其他会话写的时候会写等待

lock table student read;

在这里插入图片描述

unlock tables;
释放锁

有意思的是A表加了读锁，其他表的不能被访问

MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，是自动的，不需要我们手动操作。

在这里插入图片描述
写锁：持有写锁的会话才能写，其余会话会锁起来。

在这里插入图片描述
但是！！！
可以在后面加 local 关键字

在这里插入图片描述
这样就只是锁本会话

在这里插入图片描述
此时左边插入数据，右边可以查，并且可以查到！！！

在这里插入图片描述
但是左边却查不到那个刚插入的数据！！！
这是事务的不同隔离级别，但是ISAM是不支持事务的。

这就是MyISAM的并发插入问题

在这里插入图片描述

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁级争夺：
在这里插入图片描述

Innodb

共享锁：读锁，只能读，不能写

在这里插入图片描述

排他锁：写锁，左边加上排他锁后，其他会话不能再获取共享锁或排他锁了。

在这里插入图片描述

涛涛英语学不进去
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql调优.xmind
07-07
整理的mysql调优资料，适合刚入门的以及面试吹牛逼用，有些地方比较细致，有些地方比较粗糙，主要是一些理论，以及重要的知识点
mysql时间存储使用date还是字符串_数据库如何储存时间？—不要用字符串存储日期...
weixin_33677704的博客
01-19 4444
我们平时开发中不可避免的就是要存储时间，比如我们要记录操作表中这条记录的时间、记录转账的交易时间、记录出发时间等等。你会发现这个时间这个东西与我们开发的联系还是非常紧密的，用的好与不好会给我们的业务甚至功能带来很大的影响。所以，我们有必要重新出发，好好认识一下。1.切记不要用字符串存储日期现在很多对数据库不太了解的新手会这样做，可见，这种存储日期的方式的优点还是有的，就是简单直白，容易上手。但是，...
MYSQL调优MYSQL调优MYSQL调优MYSQL调优MYSQL调优MYSQL调优
06-20
MySQL 调优是数据库管理中的关键环节，目的是提升数据查询效率，减少资源消耗，从而提高整体系统性能。针对给定的问题，我们将分析并提供相应的优化策略。 问题一涉及对表 `user1` 的查询优化。原查询语句如下： ``...
MySQL调优，收录MySQL调优
06-25
MySQL调优，收录MySQL调优
Mysql 调优详解与实践案例
09-08
【MySQL调优详解与实践案例】 在数据库管理中，MySQL调优是一项至关重要的任务，它涉及到提高查询效率、优化数据库结构以及确保系统稳定运行。在MySQL中，`EXPLAIN`是一个强大的工具，可以帮助我们理解查询执行计划...
mysql高阶语句
qq_59169480的博客
07-24 878
高级语法的查询语句。
用 MySQL 批量生成强密码
wzy0623的专栏
07-25 1143
用 MySQL 批量生成强密码
《MySQL DBA 修炼之道》第四章 开发进阶
Trishia的博客
07-25 817
博主学习《MySQL DBA 修炼之道》结合自身理解的总结。主要讲解了范式、反范式、慢SQL查询、事务和锁等。
mysql中where与on区别
m0_63227888的博客
07-24 735
使用ON子句来定义表之间的连接条件。使用WHERE子句来过滤查询结果，或者作为连接后结果的进一步筛选条件。在进行表连接时，应优先使用ON子句来指定连接条件，因为这样可以提高查询效率，避免不必要的全表扫描或笛卡尔积。WHERE子句在连接操作后使用，用于过滤连接后的结果集。
Mysql重大更新，三个版本遭下架！
xiaofan23z的专栏
07-24 622
个时就会触发这个重大bug，7/23日mysql发布重要更新，发布了如下三个版本。书接上文，7/11日开源数据库软件服务商percona发布重要警告，最新的。后面官方确认，确实存在重大bug，但是不是表超过10000个，而是超过。当mysql表超过10000张时重启可能会崩溃。
简单的 mongoDB 学习
qq_19342829的博客
07-31 350
mongodb入门学习整理
社区养老服务小程序的设计
最新发布
Karen198的博客
07-31 272
管理员账户功能包括：系统首页，个人中心，用户管理，服务人员管理，服务产品管理，服务预约管理，服务状态管理，服务退订管理，活动管理，视频管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。服务器：SpringBoot自带 apache tomcat。微信端账号功能包括：系统首页，服务产品，活动，视频，我的。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发工具：IDEA(推荐)开发系统：Windows。
二进制部署k8s集群之master节点和etcd数据库集群（上）
zx52306的博客
07-30 928
【代码】二进制部署k8s集群之master节点和etcd数据库集群（上）
【iOS】——持久化
m0_73974920的博客
07-30 964
因为plist文件不能够保存自定义对象。如果一个字典中保存有自定义对象，如果把这个对象写入到文件当中，它是不会生成 plist文件的。如果对象是NSString、NSDictionary、NSArray、NSData、NSNumber等类型，可以直接用NSKeyedArchiver进行归档和恢复。
已经10岁的K8S，在很多方面仍需努力
u012516914的专栏
07-28 332
/ K8S 已经 10 岁了，但仍然有许多方面要继续努力 /Kubernetes 于 2014 年 6 月推出，自那时起，它在推广云原生应用设计和支持更多微服务部署方面发挥了巨大作用。容器部署的增长非常迅速，而 Kubernetes 对于企业管理这些部署至关重要——根据 CNCF 上一份报告的调查结果，84% 的组织正在使用或评估 Kubernetes，而 66% 的潜在和实际消费者在生产中使用 ...
MySQL-数据操作语言
一直走的很慢的鸭子
07-27 706
DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。
手把手教你安装AoiAWD
swordcloud_xm的博客
07-29 1316
AoiAWD 是一个由Aodzip（安恒信息 海特实验室研究员、HAC战队成员）维护的一个针对于CTF AWD模式的开源项目。专为比赛设计，便携性好，低权限运行的EDR系统。任何人都可以在 GNU AGPL-3.0 许可下使用该项目和分享该项目的源码。AoiAWD地址：https://github.com/DasSecurity-HatLab/AoiAWD。
添加索引导致微服务异常
07-29 1127
执行DDL时，最好使用ONLINE选项，随着oracle版本升级，online状态下可执行的DDL逐步增加，大部分普通DDL上，可以使用ONLINE选项，例如。而在新增索引的时候会有TM锁，而该表又有大量的insert、update等，因此会对这些dml语句造成阻塞。1、对大表新增索引尽量停业务，就算加上online不会造成阻塞，但对性能也有影响，特别是这种性能敏感的系统。2、在新增索引的时候加online参数，而online产生的TM锁时间比较短。加了online后，可以以非独占的方式创建和删除索引。
教务管理平台/高校教务管理系统的设计与实现/教务网站/学生成绩管理系统/学生课程管理系统
weixin_47958760的博客
07-27 806
本毕业设计的内容是设计并且实现一个基于JSP技术的教务管理平台。采用MYSQL为数据库开发平台，SSM框架，Tomcat网络信息服务作为应用服务器。教务管理平台的功能已基本实现，主要有学生、教师、课程信息、课程选择、教师课表、学生成绩、图书信息、考试等管理功能。 论文主要从系统的分析与设计 、数据库设计和系统的详细设计等几个方面来进行论述，系统分析与设计部分主要论述了系统的功能分析、系统的设计思路，数据库设计主要论述了数据库的设计，系统的详细设计部分主要论述了几个主要模块的详细设计过程。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值